• 售前

  • 售后

热门帖子
入门百科

mysql查询时offset过大影响性能的缘故起因和优化详解

[复制链接]
冷专问 显示全部楼层 发表于 2021-10-26 13:14:53 |阅读模式 打印 上一主题 下一主题
前言
mysql查询使用select命令,配合limit,offset参数可以读取指定范围的记载。本文将先容mysql查询时,offset过大影响性能的缘故起因及优化方法。
准备测试数据表及数据
1.创建表
  1. CREATE TABLE `member` (
  2. `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  3. `name` varchar(10) NOT NULL COMMENT '姓名',
  4. `gender` tinyint(3) unsigned NOT NULL COMMENT '性别',
  5. PRIMARY KEY (`id`),
  6. KEY `gender` (`gender`)
  7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
复制代码
2.插入1000000条记载
  1. <?php
  2. $pdo = new PDO("mysql:host=localhost;dbname=user","root",'');
  3. for($i=0; $i<1000000; $i++){
  4. $name = substr(md5(time().mt_rand(000,999)),0,10);
  5. $gender = mt_rand(1,2);
  6. $sqlstr = "insert into member(name,gender) values('".$name."','".$gender."')";
  7. $stmt = $pdo->prepare($sqlstr);
  8. $stmt->execute();
  9. }
  10. ?>
  11. mysql> select count(*) from member;
  12. +----------+
  13. | count(*) |
  14. +----------+
  15. | 1000000 |
  16. +----------+
  17. 1 row in set (0.23 sec)
复制代码
3.当前数据库版本
  1. mysql> select version();
  2. +-----------+
  3. | version() |
  4. +-----------+
  5. | 5.6.24 |
  6. +-----------+
  7. 1 row in set (0.01 sec)
复制代码
分析offset过大影响性能的缘故起因
1.offset较小的情况
  1. mysql> select * from member where gender=1 limit 10,1;
  2. +----+------------+--------+
  3. | id | name  | gender |
  4. +----+------------+--------+
  5. | 26 | 509e279687 |  1 |
  6. +----+------------+--------+
  7. 1 row in set (0.00 sec)
  8. mysql> select * from member where gender=1 limit 100,1;
  9. +-----+------------+--------+
  10. | id | name  | gender |
  11. +-----+------------+--------+
  12. | 211 | 07c4cbca3a |  1 |
  13. +-----+------------+--------+
  14. 1 row in set (0.00 sec)
  15. mysql> select * from member where gender=1 limit 1000,1;
  16. +------+------------+--------+
  17. | id | name  | gender |
  18. +------+------------+--------+
  19. | 1975 | e95b8b6ca1 |  1 |
  20. +------+------------+--------+
  21. 1 row in set (0.00 sec)
复制代码
当offset较小时,查询速度很快,服从较高。

2.offset较大的情况
  1. mysql> select * from member where gender=1 limit 100000,1;
  2. +--------+------------+--------+
  3. | id  | name  | gender |
  4. +--------+------------+--------+
  5. | 199798 | 540db8c5bc |  1 |
  6. +--------+------------+--------+
  7. 1 row in set (0.12 sec)
  8. mysql> select * from member where gender=1 limit 200000,1;
  9. +--------+------------+--------+
  10. | id  | name  | gender |
  11. +--------+------------+--------+
  12. | 399649 | 0b21fec4c6 |  1 |
  13. +--------+------------+--------+
  14. 1 row in set (0.23 sec)
  15. mysql> select * from member where gender=1 limit 300000,1;
  16. +--------+------------+--------+
  17. | id  | name  | gender |
  18. +--------+------------+--------+
  19. | 599465 | f48375bdb8 |  1 |
  20. +--------+------------+--------+
  21. 1 row in set (0.31 sec)
复制代码
当offset很大时,会出现服从问题,随着offset的增大,执行服从降落。

分析影响性能缘故起因
  1. select * from member where gender=1 limit 300000,1;
复制代码
因为数据表是InnoDB,根据InnoDB索引的布局,查询过程为:
      
  • 通过二级索引查到主键值(找出全部gender=1的id)。  
  • 再根据查到的主键值通过主键索引找到相应的数据块(根据id找出对应的数据块内容)。  
  • 根据offset的值,查询300001次主键索引的数据,末了将之前的300000条扬弃,取出末了1条。
不外既然二级索引已经找到主键值,为什么还须要先用主键索引找到数据块,再根据offset的值做偏移处理呢?
假如在找到主键索引后,先执行offset偏移处理,跳过300000条,再通过第300001条记载的主键索引去读取数据块,如许就能进步服从了。
假如我们只查询出主键,看看有什么不同
  1. mysql> select id from member where gender=1 limit 300000,1;
  2. +--------+
  3. | id  |
  4. +--------+
  5. | 599465 |
  6. +--------+
  7. 1 row in set (0.09 sec)
复制代码
很显着,假如只查询主键,执行服从对比查询全部字段,有很大的提升。  
推测
只查询主键的情况

因为二级索引已经找到主键值,而查询只须要读取主键,因此mysql会先执行offset偏移利用,再根据后面的主键索引读取数据块。
须要查询全部字段的情况

因为二级索引只找到主键值,但其他字段的值须要读取数据块才能获取。因此mysql会先读出数据块内容,再执行offset偏移利用,末了扬弃前面须要跳过的数据,返回后面的数据。

证明
InnoDB中有buffer pool,存放近来访问过的数据页,包罗数据页和索引页。
为了测试,先把mysql重启,重启后检察buffer pool的内容。
  1. mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('primary','gender') and TABLE_NAME like '%member%' group by index_name;
  2. Empty set (0.04 sec)
复制代码
可以看到,重启后,没有访问过任何的数据页。
查询全部字段,再检察buffer pool的内容
  1. mysql> select * from member where gender=1 limit 300000,1;
  2. +--------+------------+--------+
  3. | id  | name  | gender |
  4. +--------+------------+--------+
  5. | 599465 | f48375bdb8 |  1 |
  6. +--------+------------+--------+
  7. 1 row in set (0.38 sec)
  8. mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('primary','gender') and TABLE_NAME like '%member%' group by index_name;
  9. +------------+----------+
  10. | index_name | count(*) |
  11. +------------+----------+
  12. | gender  |  261 |
  13. | PRIMARY |  1385 |
  14. +------------+----------+
  15. 2 rows in set (0.06 sec)
复制代码
可以看出,此时buffer pool中关于member表有1385个数据页,261个索引页。

重启mysql清空buffer pool,继续测试只查询主键
  1. mysql> select id from member where gender=1 limit 300000,1;
  2. +--------+
  3. | id  |
  4. +--------+
  5. | 599465 |
  6. +--------+
  7. 1 row in set (0.08 sec)
  8. mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('primary','gender') and TABLE_NAME like '%member%' group by index_name;
  9. +------------+----------+
  10. | index_name | count(*) |
  11. +------------+----------+
  12. | gender  |  263 |
  13. | PRIMARY |  13 |
  14. +------------+----------+
  15. 2 rows in set (0.04 sec)
复制代码
可以看出,此时buffer pool中关于member表只有13个数据页,263个索引页。因此淘汰了多次通过主键索引访问数据块的I/O利用,进步执行服从。
因此可以证明,mysql查询时,offset过大影响性能的缘故起因是多次通过主键索引访问数据块的I/O利用。(注意,只有InnoDB有这个问题,而MYISAM索引布局与InnoDB不同,二级索引都是直接指向数据块的,因此没有此问题 )。

InnoDB与MyISAM引擎索引布局对比图
这里写图片描述

优化方法
根据上面的分析,我们知道查询全部字段会导致主键索引多次访问数据块造成的I/O利用。
因此我们先查出偏移后的主键,再根据主键索引查询数据块的全部内容即可优化。
  1. mysql> select a.* from member as a inner join (select id from member where gender=1 limit 300000,1) as b on a.id=b.id;
  2. +--------+------------+--------+
  3. | id  | name  | gender |
  4. +--------+------------+--------+
  5. | 599465 | f48375bdb8 |  1 |
  6. +--------+------------+--------+
  7. 1 row in set (0.08 sec)
复制代码
附:MYSQL limit,offset 区别
  1. SELECT
  2.   keyword
  3. FROM
  4.   keyword_rank
  5. WHERE
  6.   advertiserid='59'
  7. order by
  8.   keyword
  9. LIMIT 2 OFFSET 1;
复制代码
比如这个SQL ,limit后面跟的是2条数据,offset后面是从第1条开始读取
  1. SELECT
  2.   keyword
  3. FROM
  4.   keyword_rank
  5. WHERE
  6.   advertiserid='59'
  7. ORDER BY
  8.   keyword
  9. LIMIT 2 ,1;
复制代码
而这个SQL,limit后面是从第2条开始读,读取1条信息。

这两个万万别搞混哦。
总结
以上就是这篇文章的全部内容了,希望本文的内容对各人的学习或者工作具有肯定的参考学习价值,假如有疑问各人可以留言交换,谢谢各人对脚本之家的支持。

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作