解决MySQL in效率的方法

解决MySQL in效率的方法：

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

经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;