mysql分页性能问题
数据库版本
mysql> select version();
±-----------+
| version() |
±-----------+
| 5.7.29-log |
±-----------+
测试表
mysql> show create table user\G;
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE user (id int(11) NOT NULL AUTO_INCREMENT,name varchar(100) DEFAULT NULL,score int(11) NOT NULL,crt_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY name_score (name,score),
KEY crt_time (crt_time)
) ENGINE=InnoDB AUTO_INCREMENT=5000000 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
说明：ID为主健，crt_time为非空索引
生成测试数据
create or replace procedure insert_record(in num int)
begin
declare v_id integer default 1;
while v_id<num do
insert into user values(v_id,concat(‘name’,v_id),v_id+100,date_sub(now(),interval v_id SECOND));
set v_id=v_id+1;
end while;
end
;
set autocommit=0;
call insert_record(5000000);
commit;
通常进行分页操作，查询前面几页速度非常快，查询最后几页速度下降得非常明天
从offset 为4900000开始查询，显示5条记录花费时间27s
mysql> select * from user where crt_time>‘2020-05-15 06:19:41’ limit 4900000,5;
Empty set (27.84 sec)
下面语句实现相同的功能，花费时间为3s，性能相差9倍
select * from user a inner join (select id from user where crt_time>‘2020-05-15 06:19:41’ limit 4900000,5) b on a.id=b.id;
Empty set (3.16 sec)
为了什么性能差距这么大呢？
第一条查询语句：先根据二级索引crt_time过虑前4900000条记录，再从主键过滤4900000记录，才能获取到相应的记录，
第二条查询语句：先根据二级索引crt_time过虑前4900000条记录，并返回 满足条件的5个主键ID，再根据主键ID查询所需要的列，关少了回表查询的次数
主键的组成部分：主键字段值 +行记录地址
普通索引的组成部分：字段值 +主健字段
怎么确认呢，通过查询mysql.innodb_index_stats可看到每个索引由哪些字段组成
怎么查看两条语句使用的索引情况
每次测试时需要清除buffer_pool的信息
[[email protected] ~]# vi /etc/my.cnf
[mysqld]
innodb_buffer_pool_load_at_startup=off
innodb_buffer_pool_dump_at_shutdown=off
[[email protected] ~]# systemctl stop mysqld
[[email protected] ~]# systemctl start mysqld
select * from user where crt_time>‘2020-05-15 06:19:41’ limit 4900000,5;
第一条查询语句使用的索引情况
mysql> select index_name,count() from information_schema.INNODB_BUFFER_PAGE t where t.index_name in (‘PRIMARY’,‘crt_time’) and table_name=’test.user’ group by index_name;
±-----------±---------+
| index_name | count() |
±-----------±---------+
| crt_time | 9 |
| PRIMARY | 7852 |
select * from user a inner join (select id from user where crt_time>‘2020-05-15 06:19:41’ limit 4900000,5) b on a.id=b.id;
第二条查询语句使用的索引情况
select index_name,count() from information_schema.INNODB_BUFFER_PAGE t where t.index_name in (‘PRIMARY’,‘crt_time’) and table_name=’test.user’ group by index_name;
±-----------±---------+
| index_name | count() |
±-----------±---------+
| crt_time | 1130 |
| PRIMARY | 3 |
±-----------±---------+
总结
1：分页性能问题，可以通过减少回表来提升性能
2：mysql的主键的页子节点存储的行记录
3、mysql的二级索引的页子节点存储主健的ID
4、尽量以自增字段作为主键索引，其他索引数量尽量少。