摘要：记录一些日常工作中优化过的案例
表字段的字符集和排序规则不一致引起索引失效
有个朋友说他有个SQL，两表join的字段都有索引的，为什么把left join两边的表换下顺序后，执行计划相差甚大。他实际查询耗时第一个是0.03秒，第二个是60秒。执行计划如下：
进一步查看执行计划扩展信息：
为什么无法使用索引呢？随后问他要来了表结构，看出了问题所在：
随即修改其中一个字符集和另一个一致即可。执行计划正常：
创建联合索引的顺序不合理引起的SQL查询慢
表结构
CREATE TABLE `m_stats_base` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `yearmonth` varchar(8) COLLATE utf8_bin DEFAULT NULL, `school_id` varchar(32) COLLATE utf8_bin DEFAULT NULL, `school_name` varchar(100) COLLATE utf8_bin DEFAULT NULL, `user_id` varchar(32) COLLATE utf8_bin NOT NULL, `real_name` varchar(32) COLLATE utf8_bin DEFAULT NULL, `identity_id` varchar(32) COLLATE utf8_bin DEFAULT NULL, `icon` varchar(64) COLLATE utf8_bin DEFAULT '0', `add_time` datetime not null DEFAULT '1971-01-01', ... ... PRIMARY KEY (`id`), KEY `idx_addtime` (`add_time`,`identity_id`) ) ENGINE=InnoDB AUTO_INCREMENT=8617729 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
|
开发反馈该SQL查询耗时：3秒
mysql> explain select COUNT(*) from m_stats_base where add_time < '2018-01-01' and identity_id='TEACHER'; +----+-------------+--------------+-------+---------------+-------------+---------+------+---------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+-------+---------------+-------------+---------+------+---------+--------------------------+ | 1 | SIMPLE | m_stats_base | range | idx_addtime | idx_addtime | 6 | NULL | 3003236 | Using where; Using index | +----+-------------+--------------+-------+---------------+-------------+---------+------+---------+--------------------------+ 1 row in set (0.00 sec) mysql> select COUNT(*) from m_stats_base where add_time < '2018-01-01' and identity_id='TEACHER'; +----------+ | COUNT(*) | +----------+ | 627038 | +----------+ 1 row in set (3.00 sec)
|
解决办法：调整联合索引字段顺序,将等值字段放前面范围字段放后面
mysql> alter table m_stats_base add key idx_identifityid_addtime(identity_id,add_time),drop KEY `idx_addtime`; Query OK, 0 rows affected (46.23 sec) Records: 0 Duplicates: 0 Warnings: 0 查看执行计划，可看到扫描的数据从300多W减少到120W，实际查询耗时：1秒内 mysql> explain select COUNT(*) from m_stats_base where add_time < '2018-01-01' and identity_id='TEACHER'; +----+-------------+--------------+-------+--------------------------+--------------------------+---------+------+---------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+-------+--------------------------+--------------------------+---------+------+---------+--------------------------+ | 1 | SIMPLE | m_stats_base | range | idx_identifityid_addtime | idx_identifityid_addtime | 105 | NULL | 1224698 | Using where; Using index | +----+-------------+--------------+-------+--------------------------+--------------------------+---------+------+---------+--------------------------+ 1 row in set (0.00 sec) mysql> select COUNT(*) from m_stats_base where identity_id='TEACHER' and add_time < '2018-01-01'; +----------+ | COUNT(*) | +----------+ | 627038 | +----------+ 1 row in set (0.97 sec)
|
当where条件中有范围条件和等值条件组合的时候，mysql会将SQL改写改写为等值条件放前面，范围条件放后面：
mysql> explain extended select COUNT(*) from m_stats_base where add_time < '2018-01-01' and identity_id='TEACHER'; +----+-------------+--------------+-------+--------------------------+--------------------------+---------+------+---------+----------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+-------+--------------------------+--------------------------+---------+------+---------+----------+--------------------------+ | 1 | SIMPLE | m_stats_base | range | idx_identifityid_addtime | idx_identifityid_addtime | 105 | NULL | 1224698 | 100.00 | Using where; Using index | +----+-------------+--------------+-------+--------------------------+--------------------------+---------+------+---------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> show warnings; +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select count(0) AS `COUNT(*)` from `xueledb`.`m_stats_base` where ((`xueledb`.`m_stats_base`.`identity_id` = 'TEACHER') and (`xueledb`.`m_stats_base`.`add_time` < '2018-01-01')) | +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
|
