mysql 的诡异现象，CPU 100%，order by 主键提示 sending data

深藏阁楼爱情的钟 2022-06-13 03:50 128阅读 0赞

mysql 单表数据量 50万，执行查询一直CPU爆表，按照hash(id) 分表 partitions 10份。  
问题依旧出现，新问题出现了 `select * from tb where status in(1,2) and type=1 order by id desc limit 1` 提示`sending data`

## 诡异 ##

MariaDB [db2_zz]> select count(tid) from zz_info where status in(1,2) and c2=12176;
    +------------+
    | count(tid) | +------------+
    | 4167 | +------------+
    1 row in set (0.01 sec)
    
    MariaDB [db2_zz]> select max(uptime) from zz_info where status in(1,2) and c2=12176;
    +-------------+
    | max(uptime) | +-------------+
    | 1498126645 | +-------------+
    1 row in set (4.80 sec)
    
    MariaDB [db2_zz]> select max(tid) from zz_info where status in (1,2) and c2=12176;
    +----------+
    | max(tid) | +----------+
    | 1364323 | +----------+
    1 row in set (0.00 sec)
    
    MariaDB [db2_zz]> SELECT max(tid) FROM `zz_info` WHERE (`status` IN (1, 2)) AND (`d1`='25') LIMIT 1;
    +----------+
    | max(tid) | +----------+
    | 1636927 | +----------+
    1 row in set (1.59 sec)

字段`status` 和 `d1` 都有加单独索引，给 `status` `d1` 加复合索引之后，问题改善，但是一用上 `order by` 主键就不行，`sending data`。

MariaDB [db2_zz]> SELECT max(id) FROM `zz_info` WHERE (`status` IN (1, 2)) AND (`d1`='25') LIMIT 1;
    +----------+
    | max(tid) | +----------+
    | 1636927 | +----------+
    1 row in set (0.11 sec)

## 猜想 ##

mysql 分表 `partition by hash(id) partitions 10` 之后用主键排序性能更差，还不如使用索引来排序快，最后只能改为 `select max(主键)` 加上 符合索引来解决问题。

有更好的方法吗？欢迎探讨 coconets@163.com