九游会旗舰厅推网是J9.CM - theheartofthewoods.com

2020-04-19由程序员日记发表于数据库教程 浏览7次

九游会旗舰厅推网是J9.CM2021最强娱乐公司,提供安全无忧娱乐平台,中文网址通道最强渠道之一,让您享受娱乐,免去后顾之忧发现有个mysql库的cpu使用比较厉害,分析后发现是以下的sql导致:

<pre class="brush:sql;gutter:true;">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的执行计划:

<pre class="brush:sql;gutter:true;">+----+-------------+---------+------------+-------+----------------------------------------------------------+---------+---------+------+-------+----------+-------------+
| 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秒即可返回数据。执行计划如下:

<pre class="brush:sql;gutter:true;">+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+-------+----------+---------------------------------------+
| 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,使得不走主键

<pre class="brush:sql;gutter:true;">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秒即可返回数据,执行计划如下:选对了索引

<pre class="brush:sql;gutter:true;">+----+-------------+---------+------------+-------+----------------------------------------------------------+----------------------+---------+------+-------+----------+---------------------------------------+
| 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)

<pre class="brush:sql;gutter:true;">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秒,从执行计划看,虽然没有使用主键,但是仍然选错了索引

<pre class="brush:sql;gutter:true;">+----+-------------+---------+------------+-------+----------------------------------------------------------+-------------------------------------+---------+------+--------+----------+------------------------------------+
| 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 |
+----+-------------+---------+------------+-------+----------------------------------------------------------+-------------------------------------+---------+------+--------+----------+------------------------------------+

删除索引devabce_sn_id_c2b22ec1_fk_iclock_sn,再次测试上面的语句,就可以选对索引了。

针对mysql不能选对正确索引,查了一下官方信息,发现有个类似的bug:

MySQL没有选择正确的索引教程

暂时怀疑是该bug所导致。

因为不是自己的库,还得等待下周同事来确认。