mysql 数据几十万 CPU过高

Question

一个数据表数据有几十万的样子 在查询页面反复的时候 CPU特别高基本爆满
SQl语句
select count(*) from emp where catid <> 3 and cid =2; 用户做分页数量
select * from emp limit 1,10; 本页显示的

Answer 1

You don’t access the index at all, you read the hard disk data directly

Answer 2

Send the sql statement.

Answer 3

InnoDB does not directly save the total number of data in the table. select count(*) from emp; requires scanning the index. Repeated searches will of course consume CPU. select count(*) from emp;要扫一遍索引，反复查当然要耗CPU。

我的测试表有两千万数据，没缓存时count(*)

My test table has 20 million data. Without cache, count(*) takes 15 seconds, and with cache, it takes 3 seconds.

show table status where Name = 'your table name'

You can get the approximate number of rows. 🎜

Answer 4

This can be done using delayed association query (index coverage query)