MySQL 之数据库优化
1、查询连接MySQL服务器的次数
mysql> show status like ‘connections‘; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Connections | 3 | +---------------+-------+ 1 row in set (0.01 sec)
2、查询MySQL服务器的运行时间
mysql> show status like ‘uptime‘; #单位为秒 +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Uptime | 158 | +---------------+-------+ 1 row in set (0.00 sec)
3、查询操作的次数
mysql> show status like ‘com_select‘; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Com_select | 1 | +---------------+-------+ 1 row in set (0.00 sec)
4、插入操作的次数
mysql> show status like ‘com_insert‘; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Com_insert | 2 | +---------------+-------+ 1 row in set (0.00 sec)
5、更新操作的次数
mysql> show status like ‘com_update‘; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Com_update | 3 | +---------------+-------+ 1 row in set (0.00 sec)
6、删除操作的次数
mysql> show status like ‘com_delete‘; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Com_delete | 1 | +---------------+-------+ 1 row in set (0.00 sec)
7、查询MySQL服务器的慢查询次数
mysql> show status like ‘slow_queries‘; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Slow_queries | 15 | +---------------+-------+ 1 row in set (0.00 sec)
二、对SQL语句进行分析
1、使用explain关键字进行分析
mysql> explain select * from user\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user #表名 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL #使用哪个列或常数与索引一起使用来查询记录 rows: 3 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec)
上面的select_type解释如下:
Select_type:表示select语句的类型 其中simple 是简单查询(不包括连接查询和子查询) Primary 主查询 Union 连接查询;
2、利用索引来提高查询效率
mysql> explain select * from stu_info where s_id=3\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: stu_info partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 3 #需要查询三行才能查到(这个表数据总共也就三行) filtered: 33.33 Extra: Using where 1 row in set, 1 warning (0.00 sec) mysql> create index index_1 on stu_info(s_id); # 创建索引 mysql> explain select * from stu_info where s_id=3\G # 再次查询 *************************** 1. row *************************** id: 1 select_type: SIMPLE table: stu_info partitions: NULL type: ref possible_keys: index_1 #使用的是哪个索引名称 key: index_1 key_len: 5 ref: const rows: 1 #创建索引后,查询1行就查到可 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec)
使用索引注意事项如下:
- 做索引了之后,用 like ‘xx%’ %不在第一位查询效率最高;
- 若使用多字段索引,除了第一字段查询最快,其余不会按索引来,索引不生效;
- 若创建索引所设置的字段,查询索引组合 or 左右边的值都是属于索引设置字段下的值。
关于使用索引,可参考:MySQL 之索引类型
三、profiling分析查询
通过慢日志查询可以知道哪些SQL语句执行效率低下,通过explain我们可以得知SQL语句的具体执行情况,索引使用等,还可以结合show命令查看执行状态。如果觉得explain的信息不够详细,可以同通过profiling命令得到更准确的SQL执行消耗系统资源的信息。 profiling默认是关闭的。可以通过以下语句查看:
1、查看profiling是否开启
mysql> show variables like ‘%profiling%‘; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | have_profiling | YES | | profiling | OFF | # OFF表示未开启 | profiling_history_size | 15 | +------------------------+-------+ 3 rows in set (0.00 sec) mysql> select @@profiling; +-------------+ | @@profiling | +-------------+ | 0 | # 0表示未开启 +-------------+ 1 row in set, 1 warning (0.00 sec)
2、开启profiling
mysql> set profiling=1; # 开启 Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> select @@profiling; # 查看是否开启 +-------------+ | @@profiling | +-------------+ | 1 | +-------------+ 1 row in set, 1 warning (0.00 sec)
3、执行要测试的SQL语句
mysql> select * from stu_info; +------+--------+--------+ | s_id | s_name | s_into | +------+--------+--------+ | 1 | 2 | 3 | | 3 | 4 | 5 | | 2 | 4 | 5 | +------+--------+--------+ 3 rows in set (0.00 sec)
4、查看SQL语句对应的ID,对其进行分析
mysql> show profiles; +----------+------------+------------------------+ | Query_ID | Duration | Query | +----------+------------+------------------------+ | 1 | 0.00016900 | select @@profiling | | 2 | 0.00018425 | select * from bank | | 3 | 0.00018475 | select * from stu_info | +----------+------------+------------------------+ 3 rows in set, 1 warning (0.00 sec) mysql> show profile for query 3; #查询sql语句的详细分析 +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000058 | | checking permissions | 0.000005 | | Opening tables | 0.000022 | | init | 0.000015 | | System lock | 0.000006 | | optimizing | 0.000002 | | statistics | 0.000008 | | preparing | 0.000007 | | executing | 0.000001 | | Sending data | 0.000036 | | end | 0.000002 | | query end | 0.000004 | | closing tables | 0.000004 | | freeing items | 0.000008 | | cleaning up | 0.000008 | +----------------------+----------+ 15 rows in set, 1 warning (0.00 sec)
在上面命令的返回结果中,status是profile里的状态,duration是status状态下的耗时,因此我们关注的就是哪个状态最耗时,这些状态中哪些可以优化,当然也可以查看更多的信息,比如:CPU等。语法如下:
mysql> show profile block io for query 3\G mysql> show profile all for query 3\G
除了上面的block io和all以外,还可以换成cpu(显示用户cpu时间、系统cpu时间)、ipc(显示发送和接收相关开销信息)、page faults(显示页面错误相关开销信息)、swaps(显示交换次数相关开销的信息)。
注意:测试完成之后,记得要关闭调试功能,以免影响数据库的正常使用。
注意:测试完成之后,记得要关闭调试功能,以免影响数据库的正常使用。
注意:测试完成之后,记得要关闭调试功能,以免影响数据库的正常使用。
四、对数据库表结构进行优化
对数据库表结构的优化大概可以从以下几个方面着手:
- 将字段很多的表分解成多个表,尽量避免表字段过多;
- 增加中间表,合理增加冗余字段;
- 优化插入记录的速度;
- 在插入数据之前禁用索引,会让创建索引不生效,命令: ALTER TABLE table_name DISABLE KEYS;
- 根据实际情况来定,在插入记录之前禁用唯一性检查,命令:set unique_checks=0;
- 多条插入数据的命令最好整合为一条;
- 使用load data infle批量插入数据。
- 对于innodb引擎的表来说,以下几点可以进行优化:
- 禁用唯一性检查:set unique_checks=0;
- 禁用外键检查:set foreign_key_checks=0;
- 禁用自动提交:set autocommit=0;
分析表,检查表和优化表
所谓分析表,就是分析关键字的分布,检查表就是检查是否存在错误,优化表就是删除或更新造成的空间浪费。
1、分析表
分析表可以一次分析一个或多个表,在分析期间只能读,不能进行插入和更新操作。分析表的语法如下:
mysql> analyze table stu_info; +----------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------------+---------+----------+----------+ | mysql.stu_info | analyze | status | OK | +----------------+---------+----------+----------+ 1 row in set (0.01 sec)
对于上述返回的结果解释:Table是表名 ,op执行的操作是什么, msg_type 信息级别(status是正常状态,info是信息,note注意,warning警告,error错误), msg_text 是显示信息。
2、检查表
检查是否存在错误,关键字统计,检查视图是否有错误 Check table 表名 option ={quick |fast | medium|extended |changed} Quick 不扫描行,不检查错误连接 Fast 只检查没有被正确关闭的表 Medium 扫描行验证被删除的连接是有效的,也可以计算各行的关键字校验和。 Extended 对每行所有关键字进行全面的关键字查找,Changed 只检查上次检查后被更改的表和没有被正确关闭的表,Option只对myisam 有效 对innodb表无效,在执行时会给表加上只读锁。
mysql> check table stu_info; +----------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------------+-------+----------+----------+ | mysql.stu_info | check | status | OK | +----------------+-------+----------+----------+ 1 row in set (0.00 sec)
3、优化表
消除删除或更新造成的空间浪费,命令语法格式为:Optimize [local |no_write_to_binlog] table tb1_name …., 优化myisam的表和innodb的表都有效, 但是只能优化表中的varchar\text\blob数字类型, 执行过程中上只读锁。
mysql> optimize table stu_info\G *************************** 1. row *************************** Table: mysql.stu_info Op: optimize Msg_type: note Msg_text: Table does not support optimize, doing recreate + analyze instead *************************** 2. row *************************** Table: mysql.stu_info Op: optimize Msg_type: status Msg_text: OK 2 rows in set (0.04 sec)