既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,涵盖了95%以上大数据知识点,真正体系化!
由于文件比较多,这里只是将部分目录截图出来,全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频,并且后续会持续更新
N越大,MySQL需扫描更多数据定位到具体的N行,这会耗费大量的I/O成本和时间成本。
为什么上面的SQL写法扫描数据会慢?
- t是个索引组织表,key idx_kid_type(kid,type)
符合kid=3 and type=1 的记录有很多行,我们取第 9,10行。
select * from t where kid =3 and type=1 order by id desc 8,2;
对于Innodb,根据 idx_kid_type
二级索引里面包含的主键去查找对应行。
对百万千万级记录,索引大小可能和数据大小相差无几,cache在内存中的索引数量有限,而且二级索引和数据叶子节点不在同一物理块存储,二级索引与主键的相对无序映射关系,也会带来大量随机I/O请求,N越大越需遍历大量索引页和数据叶,需要耗费的时间就越久。
由于上面大分页查询耗时长,是否真的有必要完全遍历“无效数据”?
若需要:
limit 8,2
跳过前面8行无关数据页的遍历,可直接通过索引定位到第9、10行,这样是不是更快?
这就是延迟关联的核心思想:通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据,而非通过二级索引获取主键再通过主键遍历数据页。
通过如上分析可得,通过常规方式进行大分页查询慢的原因,也知道了提高大分页查询的具体方法。
=====================================================================
简单的 limit 子句。limit 子句声明如下:
SELECT * FROM table LIMIT
[offset,] rows | rows OFFSET offset
limit 子句用于指定 select 语句返回的记录数,注意:
offset
指定第一个返回记录行的偏移量,默认为0
初始记录行的偏移量是0,而非1
rows
指定返回记录行的最大数量
rows
为 -1 表示检索从某个偏移量到记录集的结束所有的记录行。
若只给定一个参数:它表示返回最大的记录行数目。
select * from orders_history where type=8 limit 1000,10;
从 orders_history
表查询offset: 1000开始之后的10条数据,即第1001条到第1010条数据(1001 <= id <= 1010)。
数据表中的记录默认使用主键(id)排序,上面结果等价于:
select * from orders_history where type=8
order by id limit 10000,10;
三次查询时间分别为:
3040 ms
3063 ms
3018 ms
针对这种查询方式,下面测试查询记录量对时间的影响:
select * from orders_history where type=8 limit 10000,1;
select * from orders_history where type=8 limit 10000,10;
select * from orders_history where type=8 limit 10000,100;
select * from orders_history where type=8 limit 10000,1000;
select * from orders_history where type=8 limit 10000,10000;
三次查询时间:
查询1条记录:3072ms 3092ms 3002ms
查询10条记录:3081ms 3077ms 3032ms
查询100条记录:3118ms 3200ms 3128ms
查询1000条记录:3412ms 3468ms 3394ms
查询10000条记录:3749ms 3802ms 3696ms
在查询记录量低于100时,查询时间基本无差距,随查询记录量越来越大,消耗时间越多。
针对查询偏移量的测试:
select * from orders_history where type=8 limit 100,100;
select * from orders_history where type=8 limit 1000,100;
select * from orders_history where type=8 limit 10000,100;
select * from orders_history where type=8 limit 100000,100;
select * from orders_history where type=8 limit 1000000,100;
三次查询时间如下:
查询100偏移:25ms 24ms 24ms
查询1000偏移:78ms 76ms 77ms
查询10000偏移:3092ms 3212ms 3128ms
查询100000偏移:3878ms 3812ms 3798ms
查询1000000偏移:14608ms 14062ms 14700ms
随着查询偏移的增大,尤其查询偏移大于10万以后,查询时间急剧增加。
这种分页查询方式会从DB的第一条记录开始扫描,所以越往后,查询速度越慢,而且查询数据越多,也会拖慢总查询速度。
=================================================================
- 前端加缓存、搜索,减少落到库的查询操作
比如海量商品可以放到搜索里面,使用瀑布流的方式展现数据
- 优化SQL 访问数据的方式
直接快速定位到要访问的数据行。推荐使用"延迟关联"的方法来优化排序操作,何谓"延迟关联" :通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据。
- 使用书签方式 ,记录上次查询最新/大的id值,向后追溯 M行记录
优化前
explain SELECT id, cu_id, name, info, biz_type, gmt_create, gmt_modified,start_time, end_time, market_type, back_leaf_category,item_status,picuture_url FROM relation where biz_type =‘0’ AND end_time >=‘2014-05-29’ ORDER BY id asc LIMIT 149420 ,20;
±—±------------±------------±------±--------------±------------±--------±-----±-------±----+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------------±------±--------------±------------±--------±-----±-------±----+
| 1 | SIMPLE | relation | range | ind_endtime | ind_endtime | 9 | NULL | 349622 | Using where; Using filesort |
±—±------------±------------±------±--------------±------------±--------±-----±-------±----+
执行时间:
20 rows in set (1.05 sec)
优化后:
explain SELECT a.* FROM relation a, (select id from relation where biz_type =‘0’ AND end_time >=‘2014-05-29’ ORDER BY id asc LIMIT 149420 ,20 ) b where a.id=b.id;
±—±------------±------------±-------±--------------±--------±--------±-----±-------±------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------------±-------±--------------±--------±--------±-----±-------±------+
| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 20 | |
| 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 8 | b.id | 1 | |
| 2 | DERIVED | relation | index | ind_endtime | PRIMARY | 8 | NULL | 733552 | |
±—±------------±------------±-------±--------------±--------±--------±-----±-------±------+
执行时间:
20 rows in set (0.36 sec)
优化后 执行时间 为原来的1/3 。
首先获取符合条件的记录的最大 id和最小id(默认id是主键)
select max(id) as maxid ,min(id) as minid
from t where kid=2333 and type=1;
根据id 大于最小值或者小于最大值进行遍历。
select xx,xx from t where kid=2333 and type=1
and id >=min_id order by id asc limit 100;
select xx,xx from t where kid=2333 and type=1
and id <=max_id order by id desc limit 100;
当遇到延迟关联也不能满足查询速度的要求时
SELECT a.id as id, client_id, admin_id, kdt_id, type, token, created_time, update_time, is_valid, version FROM t1 a, (SELECT id FROM t1 WHERE 1 and client_id = ‘xxx’ and is_valid = ‘1’ order by kdt_id asc limit 267100,100 ) b WHERE a.id = b.id;
100 rows in set (0.51 sec)
使用延迟关联查询数据510ms ,使用基于书签模式的解决方法减少到10ms以内 绝对是一个质的飞跃。
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,涵盖了95%以上大数据知识点,真正体系化!
由于文件比较多,这里只是将部分目录截图出来,全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频,并且后续会持续更新
in set (0.51 sec)
使用延迟关联查询数据510ms ,使用基于书签模式的解决方法减少到10ms以内 绝对是一个质的飞跃。
[外链图片转存中…(img-jR9IIjTf-1715289770533)]
[外链图片转存中…(img-ZwNd3RMz-1715289770534)]
[外链图片转存中…(img-xUrcZEly-1715289770534)]
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,涵盖了95%以上大数据知识点,真正体系化!
由于文件比较多,这里只是将部分目录截图出来,全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频,并且后续会持续更新