MYSQL 慢查询优化一例

一个SQL从13.45S优化到0.14S的过程
#SQL原型+执行计划
mysql> desc SELECT s.*,st.tag_name FROM fanwe_share as s
INNER JOIN fanwe_share_category as sc ON sc.share_id = s.share_id
INNER JOIN fanwe_share_tags as st ON st.share_id = s.share_id
INNER JOIN fanwe_goods_category_tags as gct ON gct.cate_id = sc.cate_id
WHERE gct.cate_id in (2,14,15,16,17,18) AND s.is_index=1 AND s.status=1
GROUP BY s.share_id
ORDER BY s.sort desc,s.share_id desc
 limit 30;
+----+-------------+-------+--------+-------------------------+----------+---------+---------------------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------------+----------+---------+---------------------+------+-----------------------------------------------------------+
| 1 | SIMPLE | gct | range | cate_id | cate_id | 2 | NULL | 101 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | sc | ref | share_id,cate_id | cate_id | 4 | shop520.gct.cate_id | 18 | Using where |
| 1 | SIMPLE | s | eq_ref | PRIMARY,status,is_index | PRIMARY | 4 | shop520.sc.share_id | 1 | Using where |
| 1 | SIMPLE | st | ref | share_id | share_id | 4 | shop520.sc.share_id | 19 | |
+----+-------------+-------+--------+-------------------------+----------+---------+---------------------+------+-----------------------------------------------------------+
4 rows in set (0.00 sec)

#查询时间：
30 rows in set (13.45 sec)

#分析：
1：s.*,是否必须要全部字段，在正常情况下不允许写*，产生的问题有2个：1）获取不必要的数据，占用内存，网络字段，2）无法使用到覆盖索引。建议只取需要的字段
2：Using where; Using index; Using temporary; Using filesort，查询是从s开始的，group by,order by 字段也是s表，执行计划确用gct表做驱动表，导致分组，排序不能使用索引

#修改，强制连接顺序如下，执行计划如下：
mysql> desc sELECT sql_no_cache s.*,st.tag_name FROM fanwe_share as s
    -> STRAIGHT_JOIN fanwe_share_category as sc ON sc.share_id = s.share_id
    -> INNER JOIN fanwe_share_tags as st ON st.share_id = s.share_id
    -> INNER JOIN fanwe_goods_category_tags as gct ON gct.cate_id = sc.cate_id
    -> WHERE gct.cate_id in (2,14,15,16,17,18) AND s.is_index=1 AND s.status=1
    -> GROUP BY s.share_id
    -> ORDER BY s.share_id desc,s.sort desc limit 30;
+----+-------------+-------+------+-------------------------+----------+---------+--------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-------------------------+----------+---------+--------------------+------+----------------------------------------------+
| 1 | SIMPLE | s | ref | PRIMARY,status,is_index | is_index | 2 | const | 381 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | st | ref | share_id | share_id | 4 | shop520.s.share_id | 19 | |
| 1 | SIMPLE | sc | ref | share_id,cate_id | share_id | 4 | shop520.s.share_id | 12 | |
| 1 | SIMPLE | gct | ref | cate_id | cate_id | 2 | shop520.sc.cate_id | 13 | Using where; Using index |
+----+-------------+-------+------+-------------------------+----------+---------+--------------------+------+----------------------------------------------+
4 rows in set (0.00 sec)

#查询时间如下：
30 rows in set (1.61 sec)
#分析：
1：已经由s表进行驱动，但是没有使用正确的索引去分组排序，所以产生了Using where; Using temporary; Using filesort，现在要把这个问题解决。
2：is_index就2个值，排序用的是share_id,新建索引需要把分组，排序字段包含进去

#添加索引
mysql> alter table fanwe_share add index idx_share_id_is_index2(share_id,sort,is_index);Query OK, 149462 rows affected (9.85 sec)Records: 149462 Duplicates: 0 Warnings: 0

#查看执行计划：
mysql> desc sELECT sql_no_cache s.*,st.tag_name FROM fanwe_share as s STRAIGHT_JOIN fanwe_share_category as sc ON sc.share_id = s.share_id INNER JOIN fanwe_share_tags as st ON st.share_id = s.share_id INNER JOIN fanwe_goods_category_tags as gct ON gct.cate_id = sc.cate_id WHERE gct.cate_id in (2,14,15,16,17,18) AND s.is_index=1 AND s.status=1 GROUP BY s.share_id ORDER BY s.share_id desc,s.sort desc limit 30;
+----+-------------+-------+-------+------------------------------------------------+------------------------+---------+---------------------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+------------------------------------------------+------------------------+---------+---------------------+------+------------------------------+
| 1 | SIMPLE | s | index | PRIMARY,status,is_index,idx_share_id_is_index2 | idx_share_id_is_index2 | 9 | NULL | 90 | Using where; Using temporary |
| 1 | SIMPLE | st | ref | share_id | share_id | 4 | shop520.s.share_id | 19 | |
| 1 | SIMPLE | sc | ref | share_id,cate_id | share_id | 4 | shop520.st.share_id | 12 | Using where |
| 1 | SIMPLE | gct | ref | cate_id | cate_id | 2 | shop520.sc.cate_id | 13 | Using where; Using index |
+----+-------------+-------+-------+------------------------------------------------+------------------------+---------+---------------------+------+------------------------------+
4 rows in set (0.00 sec)

#额外的排序操作已经没有了。
#执行时间：
30 rows in set (0.14 sec)

#此库下表存储引擎是MYISAM，检查下最重要的系统参数
mysql> show global variables like '%key_buffer_size%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| key_buffer_size | 16777216 |
+-----------------+----------+
1 row in set (0.00 sec)

#系统内存为1.6G，上面有跑PHP程序和数据库。
#可以把key_buffer_size调到521M
mysql> set global key_buffer_size=512*1024*1024;
Query OK, 0 rows affected (0.05 sec)