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