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的信息
[root@lineqi ~]# vi /etc/my.cnf
[mysqld]
innodb_buffer_pool_load_at_startup=off
innodb_buffer_pool_dump_at_shutdown=off
[root@lineqi ~]# systemctl stop mysqld
[root@lineqi ~]# 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、尽量以自增字段作为主键索引，其他索引数量尽量少。