New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
mysql 证明为什么用limit时,offset很大会影响性能 #117
Comments
不知用完整的字段列表取代*,结果会不会有变化。 |
木有懂,完整的字段和*有什么区别 |
mark |
get~但是好想知道查询原理 为什么用inner join那个方法会快这么多 |
额,看来我讲的不清楚:sob: |
学习了,虽然没遇到过,挺好的。 |
为什么inner join只会取limit pn,rn中的rn次数据页,不是子查询的吗,有点不太理解这里 |
能用explain 看下两个sql的执行计划吗? |
mysql> explain select * from test where val=4 limit 300000,5;
+----+-------------+-------+------------+------+---------------+------+---------+-------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+--------+----------+-------+
| 1 | SIMPLE | test | NULL | ref | val | val | 4 | const | 899712 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+-------+--------+----------+-------+
1 row in set, 1 warning (0.01 sec)
mysql> explain select * from test a inner join (select id from test where val=4 limit 300000,5) b on a.id=b.id;
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+--------+----------+-------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 300005 | 100.00 | NULL |
| 1 | PRIMARY | a | NULL | eq_ref | PRIMARY | PRIMARY | 8 | b.id | 1 | 100.00 | NULL |
| 2 | DERIVED | test | NULL | ref | val | val | 4 | const | 899712 | 100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+--------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)
@linfasfasf |
思考一天后,说一下我的理解,那个子查询快的原因主要是select的内容是索引,查找的内容是可以直接通过索引得到,是比较快的;没有子查询的那个select *的话虽然用到了索引,但是select了包含不是索引的内容,无法直接索引直接获得,是需要通过索引间接查找相关的内容。 |
mark |
关键是用到了索引覆盖 |
看完这两篇文章,大致就能理解楼主说的东西了 Mysql聚簇索引和索引覆盖 一、myisam与innodb引擎索引文件的异同: 1.myisam中, 主索引和次索引都指向物理行(磁盘位置); 2.innodb的主索引文件上,直接存放该行数据,称为聚簇索引,次索引指向对主键的引用; 注意: innodb来说 1.主键索引既存储索引值,又在叶子中存储行的数据 2.如果没有主键,则会Unique key做主键 3.如果没有unique,则系统生成一个内部的rowid做主键 4.像innodb中主键的索引结构中,既存储了主键值,又存储了行数据,这种结构称为“聚簇索引” 聚簇索引优劣: 优势: 根据主键查询条目比较少时,不用回行(数据就在主键节点下) 劣势: 如果碰到不规则数据插入时,造成频繁的页分裂,插入速度变慢 高性能索引策略 :对于innodb而言,因为节点下有数据文件,因此节点的分裂将会比较慢。因此对于innodb的主键尽量用整型,而且是递增的整型,如果是无规律的数据,将会产生的页的分裂,影响速度。 二、索引覆盖: 索引覆盖:是指如果查询的列恰好是索引的一部分,那么查询只需要在索引文件上进行,不需要回行到磁盘再找数据,这种查询速度非常快,这个现象称为“索引覆盖”。 三、索引覆盖实验: create table A { id varchar(64) primary key, ver int, ... } 条件:在id、ver上有联合索引,表中有几个很长的字段,总共100000条数据 问题:为什么select id from A order by id特别慢?而select id from A order by id,ver特别快? 原因: 1.如果是myisam引擎的话,会将id和ver都存放在索引文件中,所以order by id和order by id,ver不会出现速度上的差别,两次都产生索引覆盖,所以判断引擎为innodb; 2.由于innodb是聚簇索引,主索引id文件上,存放了该行的数据,当表中某个字段的数据很大时,在硬盘上一个数据块所能存放的行数就变少,所以数据块变多。当order by id时,会扫描很多个不同的数据块,导致性能降低。而order by id,ver为联合索引(次索引),次索引不用扫描很大的数据量,并且只筛选id,产生索引覆盖,所以速度快很多。 实验步骤: 1.首先查看是否开启profiling功能:SHOW VARIABLES LIKE '%pro%'; 2.开启profiling:SET profiling=1; 3.查看sql语句执行结果:SHOW profiles; 4.建立数据表: CREATE TABLE
PRIMARY KEY ( KEY ) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE
PRIMARY KEY ( KEY ) ENGINE=innodb DEFAULT CHARSET=utf8; 5.创建php文件批量插入数据: $mysqli = new mysqli("127.0.0.1", "root", "", "test"); $mysqli->query("set names utf8"); $str = str_repeat('m', 3000); for ($i=1;$i<=10000;$i++) { $id = dechex($i); $sql = sprintf("insert into t8 values ('%s',%d,'%s','%s','%s')", $i,$i,$str,$str,$str); $mysqli->query($sql); } echo "insert success"; $mysqli->close(); ?> 6.分别执行t7和t8,查看sql语句执行结果: 作者:杍劼 |
get了。讲的很细节,赞。 |
select id from test limit 30000,10。这个我理解上,并没有查询条件,走覆盖索引原因是什么呢 |
@yancanchen 聚簇索引,哈哈 |
肯定会有人问:既然一开始是利用索引的,为什么不先沿着索引叶子节点查询到最后需要的5个节点,然后再去聚簇索引中查询实际数据。这样只需要5次随机I/O,类似于下面图片的过程 对呀,为什么mysql要做这300000次无意义的随机I/O,好奇葩 |
如果 where 条件里,除了val,还有另一个非索引的字段,比如 where val=4 and source = 1,这两种查询都一样吧? |
讲的很好,赞 |
你好 总感觉不应该这样啊 现在mysql版本都这么高了 还没有修复 early row lookup的问题吗 ? https://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/ |
可是貌似真的是这样,,, @javakinglzh |
楼主能想到查看information_schema下的innodb_buffer表真的很赞。 |
实在感谢 |
如果需求是 包含where 条件的全表扫描,是否可以通过order by id后,每次获取最大的id,作为下次查询的起始id 避免offset的使用 |
@gdgy 可以的 |
感觉如果 底层存储使用innodb ,表id autoincreament 时,offset感觉可以不用了 |
@gdgy 是的,如果id可以很方便的查出来的话 |
@zhangyachen 我按你的步骤测试了一下,可结果和你的不同,感觉其实还是能用到索引的 select count(*) from test;
explain: id select_type table type key key_len ref row filtered |
如果执行: |
一个SQL优化小技巧,延迟关联,在查询第一阶段使用覆盖索引 |
why does not limit use late row lookup? select * from table order by rand() limit 10; |
👍 |
理解了一下,不确定是否正确仅供参考
|
回表会耗时,能理解。 按网上说的 SQL 执行顺序,
在第 3 步中,由于使用 *,无法触发索引覆盖,会进行回表操作。 假设满足 也就是: 但在实际执行的结果中, 对此我不理解,希望各位能指点一下。 |
首先说明一下MySQL的版本:
表结构:
id为自增主键,val为非唯一索引。
灌入大量数据,共500万:
我们知道,当limit offset rows中的offset很大时,会出现效率问题:
为了达到相同的目的,我们一般会改写成如下语句:
时间相差很明显。
为什么会出现上面的结果?我们看一下
select * from test where val=4 limit 300000,5;
的查询过程:类似于下面这张图:
像上面这样,需要查询300005次索引节点,查询300005次聚簇索引的数据,最后再将结果过滤掉前300000条,取出最后5条。MySQL耗费了大量随机I/O在查询聚簇索引的数据上,而有300000次随机I/O查询到的数据是不会出现在结果集当中的。
肯定会有人问:既然一开始是利用索引的,为什么不先沿着索引叶子节点查询到最后需要的5个节点,然后再去聚簇索引中查询实际数据。这样只需要5次随机I/O,类似于下面图片的过程:
其实我也想问这个问题。
证实
下面我们实际操作一下来证实上述的推论:
为了证实
select * from test where val=4 limit 300000,5
是扫描300005个索引节点和300005个聚簇索引上的数据节点,我们需要知道MySQL有没有办法统计在一个sql中通过索引节点查询数据节点的次数。我先试了Handler_read_*系列,很遗憾没有一个变量能满足条件。我只能通过间接的方式来证实:
InnoDB中有buffer pool。里面存有最近访问过的数据页,包括数据页和索引页。所以我们需要运行两个sql,来比较buffer pool中的数据页的数量。预测结果是运行
select * from test a inner join (select id from test where val=4 limit 300000,5) b on a.id=b.id;
之后,buffer pool中的数据页的数量远远少于select * from test where val=4 limit 300000,5;
对应的数量,因为前一个sql只访问5次数据页,而后一个sql访问300005次数据页。select * from test where val=4 limit 300000,5
可以看出,目前buffer pool中没有关于test表的数据页。
可以看出,此时buffer pool中关于test表有4098个数据页,208个索引页。
select * from test a inner join (select id from test where val=4 limit 300000,5) b on a.id=b.id
为了防止上次试验的影响,我们需要清空buffer pool,重启mysql。
运行sql:
我们可以看明显的看出两者的差别:第一个sql加载了4098个数据页到buffer pool,而第二个sql只加载了5个数据页到buffer pool。符合我们的预测。也证实了为什么第一个sql会慢:读取大量的无用数据行(300000),最后却抛弃掉。
而且这会造成一个问题:加载了很多热点不是很高的数据页到buffer pool,会造成buffer pool的污染,占用buffer pool的空间。
遇到的问题
innodb_buffer_pool_dump_at_shutdown
和innodb_buffer_pool_load_at_startup
,这两个选项能够控制数据库关闭时dump出buffer pool中的数据和在数据库开启时载入在磁盘上备份buffer pool的数据。参考资料:
欢迎大家关注我的知乎账号:https://www.zhihu.com/people/zhangyachen
或者可以关注下我的公众号,获得最及时的更新:
The text was updated successfully, but these errors were encountered: