mysql 索引执行顺序_一个mysql索引顺序优化的案例

sql:

select imsi from g_businessimsi where status='0' and channel='xiaomi' and expirdate

这么一个需要频繁执行的sql，感觉性能不太理想，g_businessimsi表字段如下：

mysql> show index from g_businessimsi;

+----------------+------------+-----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table          | Non_unique | Key_name        | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+----------------+------------+-----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| g_businessimsi |          0 | PRIMARY           |            1 | imsi                 | A         |       35559 |     NULL | NULL   |      | BTREE      |         |               |

| g_businessimsi |          0 | msisdn_index     |            1 | msisdn             | A         |       35559 |     NULL | NULL   |      | BTREE      |         |               |

| g_businessimsi |          1 | Index_cloudimsi |            1 | cloudimsi         | A         |               2 |     NULL | NULL   | YES  | BTREE      |         |               |

| g_businessimsi |          1 | index_c               |            1 | channel           | A         |                4 |     NULL | NULL   | YES  | BTREE      |         |               |

| g_businessimsi |          1 | index_c               |            2 | status              | A         |                4 |     NULL | NULL   |      | BTREE      |         |               |

| g_businessimsi |          1 | index_c               |            3 | expirdate         | A         |                4 |     NULL | NULL   | YES  | BTREE      |         |               |

| g_businessimsi |          1 | index_c               |            4 | lastmodifytime | A         |         8889 |     NULL | NULL   | YES  | BTREE      |         |               |

+----------------+------------+-----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

7 rows in set (0.00 sec)

1、执行计划如下

mysql> explain select imsi from g_businessimsi where status='0' and channel='xiaomi' and expirdate

-> order by lastmodifytime asc limit 1;

+----+-------------+----------------+-------+---------------+---------+---------+------+-------+------------------------------------------+

| id | select_type | table          | type  | possible_keys | key     | key_len | ref  | rows  | Extra                                    |

+----+-------------+----------------+-------+---------------+---------+---------+------+-------+------------------------------------------+

|  1 | SIMPLE      | g_businessimsi | range | index_c       | index_c | 218     | NULL | 17779 | Using where; Using index; Using filesort |

+----+-------------+----------------+-------+---------------+---------+---------+------+-------+------------------------------------------+

1 row in set (0.00 sec)

2、看看profile

mysql> show profile for query 1;

+----------------------+----------+

| Status               | Duration |

+----------------------+----------+

| starting             | 0.000108 |

| checking permissions | 0.000015 |

| Opening tables       | 0.000035 |

| System lock          | 0.000015 |

| init                 | 0.000033 |

| optimizing           | 0.000024 |

| statistics           | 0.000128 |

| preparing            | 0.000026 |

| executing            | 0.000004 |

| Sorting result       | 0.067427 |

| Sending data         | 0.000156 |

| end                  | 0.000012 |

| query end            | 0.000007 |

| closing tables       | 0.000009 |

| freeing items        | 0.000188 |

| logging slow query   | 0.000004 |

| cleaning up          | 0.000004 |

+----------------------+----------+

17 rows in set (0.00 sec)

可以看到，sorting result花费了很多时间，上面也使用了filesort。

调整下索引顺序，将顺序调整为：lastmodifytime,expirdate,status,channel

再看执行计划：

mysql> explain select imsi from g_businessimsi where status='0' and channel='xiaomi' and expirdate

-> order by lastmodifytime asc limit 1;

+----+-------------+----------------+-------+---------------+---------+---------+------+------+--------------------------+

| id | select_type | table          | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |

+----+-------------+----------------+-------+---------------+---------+---------+------+------+--------------------------+

|  1 | SIMPLE      | g_businessimsi | index | NULL          | index_c | 227     | NULL |    1 | Using where; Using index |

+----+-------------+----------------+-------+---------------+---------+---------+------+------+--------------------------+

1 row in set (0.00 sec)

没有filesort了，

mysql> show profile for query 6;

+----------------------+----------+

| Status               | Duration |

+----------------------+----------+

| starting             | 0.000144 |

| checking permissions | 0.000010 |

| Opening tables       | 0.000047 |

| System lock          | 0.000012 |

| init                 | 0.000039 |

| optimizing           | 0.000030 |

| statistics           | 0.000024 |

| preparing            | 0.000019 |

| executing            | 0.000007 |

| Sorting result       | 0.000006 |

| Sending data         | 0.000104 |

| end                  | 0.000007 |

| query end            | 0.000009 |

| closing tables       | 0.000008 |

| freeing items        | 0.000022 |

| logging slow query   | 0.000002 |

| cleaning up          | 0.000003 |

+----------------------+----------+

17 rows in set (0.00 sec)

时间主要用于sending data，比较正常了。

到这里，不用多说，这个sql的执行时间有了显著缩短，在使用覆盖索引(covering index)的时候，有很多注意事项，写sql的时候不可太随意，否则性能差异巨大。

关于索引顺序问题，在《高性能mysql》的5.3.4“选择合适的索引列顺序”一节中有很经典的描述，注意两点：

1、如果没有order by和group by，将选择性最高的列放在索引第一列往往OK

2、如果有order by和group by，这就要综合权衡了，一般来说，避免排序更重要。

ps:Extra里面出现using index表示此次sql执行用的是覆盖索引。什么是覆盖索引？就是查询的时候，只使用二级索引就可以搞定，不需要回到主键索引去获取其它列的数据。