mysql order by uptime desc 之后 CPU 100%

Question

1、order by
mysql 数据库单表数据量达到了70万（主键ID 1 ~ 200万）之后，使用 uptime 排序就会CPU爆表，有时候 200% 。字段 uptime int(11) not null，用来存放数据更新的时间戳，已经加了单独索引，已经 按照主键 hash 分区 10个表，innodb。2、统计表数据记录总数
当表的数据量达到了100万以上，然后使用 select count(主键) 来统计 行数，执行特别慢 select count(id) from tbname。有无替代方法

Answer 1

1、order by
mysql 数据库单表数据量达到了70万（主键ID 1 ~ 200万）之后，使用 uptime 排序就会CPU爆表，有时候 200% 。字段 uptime int(11) not null，用来存放数据更新的时间戳，已经加了单独索引，已经 按照主键 hash 分区 10个表，innodb。sql列出来2、统计表数据记录总数
当表的数据量达到了100万以上，然后使用 select count(主键) 来统计 行数，执行特别慢 select count(id) from tbname。有无替代方法可以去系统表拿近似的count数据

Answer 2

1、order by
mysql 数据库单表数据量达到了70万（主键ID 1 ~ 200万）之后，使用 uptime 排序就会CPU爆表，有时候 200% 。字段 uptime int(11) not null，用来存放数据更新的时间戳，已经加了单独索引，已经 按照主键 hash 分区 10个表，innodb。排序是很消耗cpu的，而建索引可以减少排序，但是你建了索引，mysql不一定会去用，这才是问题。
你用这个命令，看一下执行计划，看有没有用索引：
explain 你的sql另外，表分区技术，一般情况下并不能有效的加快查询速度，除非你把你的10个分区，放到不同的物理硬盘上，通过硬件来提高速度。你建的10个hash分区，举个简单的例子，你要uptime查前1000条数据，而这1000条数据，如果平均分布在10个分区中，也就是每个分区100条，那么要查到这么多的数据，就要从10个分区去取数据，这样反而会更慢。2、统计表数据记录总数
当表的数据量达到了100万以上，然后使用 select count(主键) 来统计 行数，执行特别慢 select count(id) from tbname。有无替代方法你用的是innodba所以没办法直接从元数据中取，正常情况下是非常快的count(主键)，但是因为你做了hash分区，所以可能反而更慢

Answer 3

SQL order by uptime 排序 的explain 结果如下：
MariaDB [db2_dalu]> explain SELECT `tid`,`uid`, `d2`.`name` AS `d2name`, `c2`.`name` AS `c2name`, `uname`, `title`, `d2`, `c2`, `addtime`, `uptime`,  LEFT(content,220) abstract 
FROM `pre_info` 
LEFT JOIN (SELECT `id`, `name` FROM `pre_area` WHERE `level`=2) `d2` ON  d2.id=d2  
LEFT JOIN (SELECT `id`, `name` FROM `pre_info_cat` WHERE `level`=2) `c2` ON  c2.id=c2  
WHERE (`c2`='13028') AND (`d2`='73') AND (`status` IN (0, 1, 2, -2)) 
ORDER BY `uptime` DESC LIMIT 10 OFFSET 50;+------+-------------+--------------+--------+-----------------------------------------------------------------------------+-----------------------+---------+----------------------+------+-----------------------------+
| id   | select_type | table        | type   | possible_keys                                                               | key                   | key_len | ref                  | rows | Extra                       |
+------+-------------+--------------+--------+-----------------------------------------------------------------------------+-----------------------+---------+----------------------+------+-----------------------------+
|    1 | SIMPLE      | pre_info     | range  | idx-info-status,idx-info-c2-status,idx-info-d2-status,idx-info-c2-d2-status | idx-info-c2-d2-status | 10      | NULL                 | 1557 | Using where; Using filesort |
|    1 | SIMPLE      | pre_area     | eq_ref | PRIMARY,idx-area-level                                                      | PRIMARY               | 4       | db2_lulu.pre_info.d2 |    1 | Using where                 |
|    1 | SIMPLE      | pre_info_cat | eq_ref | PRIMARY,idx-info-cat-level                                                  | PRIMARY               | 4       | db2_lulu.pre_info.c2 |    1 | Using where                 |
+------+-------------+--------------+--------+-----------------------------------------------------------------------------+-----------------------+---------+----------------------+------+-----------------------------+
3 rows in set (0.00 sec)
如果说表分区会导致更慢，那这样表分区有何用途呢

Answer 4

主键 hash 分区
---------------------- 从查询来看，条件跟主键没关系，也就是查询还是得从 10个分区中拿数据，order by 也跟主键没关系，所以充分说明你的分区没有意义
分区的意义是要让操作控制在尽量省的分区，也就是要访问那些分区，应该能够从查询中直接识别出来

Answer 5

你这个查询，把索引调整为 c2,d2,status,uptime desc 应该就快了

Answer 6

SQL order by uptime 排序 的explain 结果如下：
MariaDB [db2_dalu]> explain SELECT `tid`,`uid`, `d2`.`name` AS `d2name`, `c2`.`name` AS `c2name`, `uname`, `title`, `d2`, `c2`, `addtime`, `uptime`,  LEFT(content,220) abstract 
FROM `pre_info` 
LEFT JOIN (SELECT `id`, `name` FROM `pre_area` WHERE `level`=2) `d2` ON  d2.id=d2  
LEFT JOIN (SELECT `id`, `name` FROM `pre_info_cat` WHERE `level`=2) `c2` ON  c2.id=c2  
WHERE (`c2`='13028') AND (`d2`='73') AND (`status` IN (0, 1, 2, -2)) 
ORDER BY `uptime` DESC LIMIT 10 OFFSET 50;+------+-------------+--------------+--------+-----------------------------------------------------------------------------+-----------------------+---------+----------------------+------+-----------------------------+
| id   | select_type | table        | type   | possible_keys                                                               | key                   | key_len | ref                  | rows | Extra                       |
+------+-------------+--------------+--------+-----------------------------------------------------------------------------+-----------------------+---------+----------------------+------+-----------------------------+
|    1 | SIMPLE      | pre_info     | range  | idx-info-status,idx-info-c2-status,idx-info-d2-status,idx-info-c2-d2-status | idx-info-c2-d2-status | 10      | NULL                 | 1557 | Using where; Using filesort |
|    1 | SIMPLE      | pre_area     | eq_ref | PRIMARY,idx-area-level                                                      | PRIMARY               | 4       | db2_lulu.pre_info.d2 |    1 | Using where                 |
|    1 | SIMPLE      | pre_info_cat | eq_ref | PRIMARY,idx-info-cat-level                                                  | PRIMARY               | 4       | db2_lulu.pre_info.c2 |    1 | Using where                 |
+------+-------------+--------------+--------+-----------------------------------------------------------------------------+-----------------------+---------+----------------------+------+-----------------------------+
3 rows in set (0.00 sec)
如果说表分区会导致更慢，那这样表分区有何用途呢不是没用，而是的用到地方。就像用高射炮打蚊子，显然不是高射炮没用，而是没用对地方