MySQL查询速度优化笔记


作者:dzer <email:358654744@qq.com blog:dzer.me>
创建时间:2015/12/18 16:50:30 
最后修改时间:2015/12/18 16:50:33

背景:
今天发现工作汇报有个接口速度非常慢,经查看程序是因为sql语句用了in,在开发时因为 in(num1, num2, num3.....)的数目很少,所以没发现速度问题,当in几百上千个时,速度很慢很慢.
例如: SELECT * FROM tb_XX1 AS a WHERE a.id IN (num1, num2, num3.....);

解决MySQL in效率的方法:

经google,发现有两种解决方法,

一个是子查询,例如:

SELECT * FROM tb_XX1 AS a WHERE a.id IN ( SELECT b.id FROM tb_XX2 AS b where b.name = 'XXX');

二个是关联查询,例如:

SELECT * FROM tb_XX AS a INNER JOIN tb_XX2 AS b ON b.id = a.id where b.name = 'XXX';

至于这两个方法哪个速度更快,众说纷纭,根据不同场景使用吧。
当修改后发现速度只是快了一点点,并没有质的飞越。

mysql> SELECT COUNT( DISTINCT  `t`.`id` )
    -> FROM  `tb_workreport`  `t`
    -> LEFT OUTER JOIN  `tb_seller_member`  `reuser` ON (  `reuser`.`id` =  `t`.`reuser_id` )
    -> LEFT OUTER JOIN  `tb_workreport_content`  `content` ON (  `content`.`report_id` =  `t`.`id` )
    -> LEFT OUTER JOIN  `tb_seller_member`  `member` ON (  `member`.`id` =  `t`.`createuid` )
    -> WHERE ((((t.seller_id =219) AND (t.reuser_id =1135)) AND (t.reuser_status =0))
    -> OR
    -> (t.id IN (SELECT r.id FROM tb_workreport_reminduser AS r WHERE r.user_id
=1135)));
+-----------------------------+
| COUNT( DISTINCT  `t`.`id` ) |
+-----------------------------+
|                        2414 |
+-----------------------------+
1 row in set (44.37 sec)

经查数据表结构发现,查询的字段没有建索引,建索引后,结果令人惊讶

+-----------------------------+
| COUNT( DISTINCT  `t`.`id` ) |
+-----------------------------+
|                        2414 |
+-----------------------------+
1 row in set (0.03 sec)

从44秒 直接降为 0.03!!!
回头想为什么in(num1, num2, num3.....)这种方式相效率很慢,是因为这种方式不能利用索引,而子查询和关联查询可以利用到索引
从而说明,正确的索引是多么的重要,索引不是越多越好,是根据查询语句来建立索引

建立索引原则:查询频繁 区分度高 长度小 尽量能覆盖常用字段

建立索引方式

  1. 添加PRIMARY KEY(主键索引)
    mysql> ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
    
  2. 添加UNIQUE(唯一索引)
    mysql> ALTER TABLE `table_name` ADD UNIQUE (`column`)
    
  3. 添加INDEX(普通索引)
    mysql> ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
    
  4. 添加FULLTEXT(全文索引)
    mysql> ALTER TABLE `table_name` ADD FULLTEXT (`column`)
    
  5. 添加多列索引
    mysql> ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
    

问题解决了,但其他表的索引是否也需要修改,怎样判断是否该加索引,我觉得最好的方式是开启MySQL慢查询,通过实际查询的sql来建立索引。

开启MySQL慢查询方式

mysql> show variables like '%quer%';

我们可以看到当前log_slow_queries状态为OFF, 说明当前并没有开启慢查询.

+-------------------------------+------------------------------------------------+
| Variable_name                 | Value                                          |
+-------------------------------+------------------------------------------------+
| ft_query_expansion_limit      | 20                                             |
| have_query_cache              | YES                                            |
| log_queries_not_using_indexes | OFF                                            |
| log_slow_queries              | OFF                                            |
| long_query_time               | 10.000000                                      |
| query_alloc_block_size        | 8192                                           |
| query_cache_limit             | 1048576                                        |
| query_cache_min_res_unit      | 4096                                           |
| query_cache_size              | 0                                              |
| query_cache_type              | ON                                             |
| query_cache_wlock_invalidate  | OFF                                            |
| query_prealloc_size           | 8192                                           |
| slow_query_log                | OFF                                            |
| slow_query_log_file           | /chroot/data/mysql/var/jstufec34mq7qv-slow.log |
+-------------------------------+------------------------------------------------+
14 rows in set (0.00 sec)

开启慢日志查询

mysql> set global log_slow_queries = ON;
Query OK, 0 rows affected, 1 warning (0.30 sec)

开启sql语句日志记录

mysql> set global slow_query_log = ON;
Query OK, 0 rows affected (0.00 sec)

设置慢查询时间(大于该时间就记录日志)

mysql> set global long_query_time = 1;
Query OK, 0 rows affected (0.04 sec)

设置日志文件保存路径(记住先建立该日志文件并有写入权限)

mysql> set global slow_query_log_file = '/chroot/wwwlogs/mysql_slow.log';
Query OK, 0 rows affected (0.00 sec)

测试一条sql,打开日志文件,完整记录了sql语句和执行时间

TCP Port: 3306, Named Pipe: MySQL
Time                 Id Command    Argument
# Time: 151218 15:38:07
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 46.449903  Lock_time: 0.002003 Rows_sent: 1  Rows_examined: 13680973
use fefe;
SET timestamp=1450424287;
SELECT COUNT( DISTINCT  `t`.`id` ) 
FROM  `tb_workreport`  `t` 
LEFT OUTER JOIN  `tb_seller_member`  `reuser` ON (  `reuser`.`id` =  `t`.`reuser_id` ) 
LEFT OUTER JOIN  `tb_workreport_content`  `content` ON (  `content`.`report_id` =  `t`.`id` ) 
LEFT OUTER JOIN  `tb_seller_member`  `member` ON (  `member`.`id` =  `t`.`createuid` ) 
WHERE ((((t.seller_id =219) AND (t.reuser_id =1135)) AND (t.reuser_status =0))
OR 
(t.id IN (SELECT r.id FROM tb_workreport_reminduser AS r WHERE r.user_id =1135)));

通过日志来分析查询慢的原因,修改查询语句,建立合适的索引,这才是正确的方法。

MySQL还有很多方法来分析原因;
比如:
explain 分析单条语句

mysql> explain sql语句

使用show profiles使用分析sql性能

mysql> set profiling=1
mysql> show profiles;

常常是最后一把钥匙打开了门