MySQL没有选择正确的索引

2020-04-19 12:57 abce 阅读(551) 评论(0) 编辑收藏举报

发现有个mysql库的cpu使用比较厉害，分析后发现是以下的sql导致：

SELECT
    `devabce`.`id`,
    `devabce`.`sn_id`,
    `devabce`.`mmmcontent`,
    `devabce`.`mmmcommitttme`,
    `devabce`.`mmmtransttme`,
    `devabce`.`mmmoverttme`,
    `devabce`.`mmmreturn`,
    `devabce`.`reserved` 
FROM
    `devabce` 
WHERE
    (
        `devabce`.`sn_id` = 'ADBG182760058'
        AND ((
                `devabce`.`mmmoverttme` IS NULL
                AND `devabce`.`mmmtransttme` IS NULL
                ) 
        OR ( `devabce`.`mmmoverttme` IS NULL AND `devabce`.`mmmtransttme` IS NOT NULL AND `devabce`.`mmmtransttme` > '2020-04-18 18:40:40.466628' ))) 
ORDER BY
    `devabce`.`id` ASC
    LIMIT 200;

该语句单次执行时间长达376.424870秒。而且是执行比较频繁的语句。

查看该sql的执行计划：

+----+-------------+---------+------------+-------+----------------------------------------------------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys                                            | key     | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+---------+------------+-------+----------------------------------------------------------+---------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | devabce | NULL       | index | devabce_sn_id_c2b22ec1_fk_iclock_sn,idx_snid_ottme_tttme | PRIMARY | 4       | NULL | 59125 |     0.07 | Using where |
+----+-------------+---------+------------+-------+----------------------------------------------------------+---------+---------+------+-------+----------+-------------+

从执行计划可以看到，优化器选择使用了主键。

查看该表，表中有数据2千多万条；索引idx_snid_ottme_tttme是建立在列(sn_id、mmmoverttme、mmmtransttme)，而且该索引的选择性很好。按道理应该使用该索引，而不是使用主键。

首先尝试对表analyze，然后再次执行，发现还是使用了主键。索引仍然没有选对。鉴于目前不方便对表执行optimze。就尝试使用hint，看看执行效果。

1.使用use index(idx_snid_ottme_tttme)

执行大约0.3秒即可返回数据。执行计划如下：

+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+-------+----------+---------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys        | key                  | key_len | ref  | rows  | filtered | Extra                                 |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+-------+----------+---------------------------------------+
|  1 | SIMPLE      | devabce | NULL       | range | idx_snid_ottme_tttme | idx_snid_ottme_tttme | 100     | NULL | 84826 |   100.00 | Using index condition; Using filesort |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+-------+----------+---------------------------------------+

2.修改原sql，使得不走主键

SELECT
    `devabce`.`id`,
    `devabce`.`sn_id`,
    `devabce`.`mmmcontent`,
    `devabce`.`mmmcommitttme`,
    `devabce`.`mmmtransttme`,
    `devabce`.`mmmoverttme`,
    `devabce`.`mmmreturn`,
    `devabce`.`reserved` 
FROM
    `devabce` 
WHERE
    (
        `devabce`.`sn_id` = 'ADBG182760058'
        AND ((
                `devabce`.`mmmoverttme` IS NULL
                AND `devabce`.`mmmtransttme` IS NULL
                ) 
        OR ( `devabce`.`mmmoverttme` IS NULL AND `devabce`.`mmmtransttme` IS NOT NULL AND `devabce`.`mmmtransttme` > '2020-04-18 18:40:40.466628' ))) 
ORDER BY
    `devabce`.`id`+0 ASC
    LIMIT 200;

执行0.3秒即可返回数据，执行计划如下：选对了索引

+----+-------------+---------+------------+-------+----------------------------------------------------------+----------------------+---------+------+-------+----------+---------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys                                            | key                  | key_len | ref  | rows  | filtered | Extra                                 |
+----+-------------+---------+------------+-------+----------------------------------------------------------+----------------------+---------+------+-------+----------+---------------------------------------+
|  1 | SIMPLE      | devabce | NULL       | range | devabce_sn_id_c2b22ec1_fk_iclock_sn,idx_snid_ottme_tttme | idx_snid_ottme_tttme | 100     | NULL | 84826 |   100.00 | Using index condition; Using filesort |
+----+-------------+---------+------------+-------+----------------------------------------------------------+----------------------+---------+------+-------+----------+---------------------------------------+

3.使用IGNORE INDEX(PRIMARY)

SELECT
    `devabce`.`id`,
    `devabce`.`sn_id`,
    `devabce`.`mmmcontent`,
    `devabce`.`mmmcommitttme`,
    `devabce`.`mmmtransttme`,
    `devabce`.`mmmoverttme`,
    `devabce`.`mmmreturn`,
    `devabce`.`reserved` 
FROM
    `devabce` IGNORE INDEX(PRIMARY)
WHERE
    (
        `devabce`.`sn_id` = 'ADBG182760058'
        AND ((
                `devabce`.`mmmoverttme` IS NULL
                AND `devabce`.`mmmtransttme` IS NULL
                ) 
        OR ( `devabce`.`mmmoverttme` IS NULL AND `devabce`.`mmmtransttme` IS NOT NULL AND `devabce`.`mmmtransttme` > '2020-04-18 18:40:40.466628' ))) 
ORDER BY
    `devabce`.`id` ASC
    LIMIT 200;

执行了大约4秒，从执行计划看，虽然没有使用主键，但是仍然选错了索引

+----+-------------+---------+------------+-------+----------------------------------------------------------+-------------------------------------+---------+------+--------+----------+------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys                                            | key                                 | key_len | ref  | rows   | filtered | Extra                              |
+----+-------------+---------+------------+-------+----------------------------------------------------------+-------------------------------------+---------+------+--------+----------+------------------------------------+
|  1 | SIMPLE      | devabce | NULL       | range | devabce_sn_id_c2b22ec1_fk_iclock_sn,idx_snid_ottme_tttme | devabce_sn_id_c2b22ec1_fk_iclock_sn | 82      | NULL | 429784 |     3.97 | Using index condition; Using where |
+----+-------------+---------+------------+-------+----------------------------------------------------------+-------------------------------------+---------+------+--------+----------+------------------------------------+