优化数据库分页(limit):
在偏移量非常大的时候,例如 LIMIT 10000,20 这样的查询,这时MySQL需要查询10020条记录然后
只返回最后20条,前面的10000条记录都将被抛弃,这样的代价是非常高的。如果所有的页面被访问的
频率都相同,那么这样的查询平均需要访问半个表的数据。要优化这种查询,要么是在页面中限制分页
的数量,要么是优化大偏移量的性能。
回表的速度是很慢的(因为表中的数据存储在磁盘中,磁盘IO的速度很慢,现代计算机一次磁盘IO的时间大概为0.02s~0.04s)
覆盖索引:
select字段和where 字段在一颗索引上上全覆盖,不用回表,速度更快。
延迟关联、覆盖索引优化:
(假设city字段使用索引)
分析下面sql语句:
select id,name,work from table where city = "上海" order by salary limit 100000,20;
1、通过city索引树上找到全部值为“上海”的数据的id
2、取得id后回表,取得id,name,work,salary数据后将字段放入sort buffer进行排序(如果数据量过大则使用file sort)
3、对salary进行快速排序完成后,取出前100020 条数据后抛弃前100000条数据,按分页条件给用户返回20条记录。
这里的回表至少需要回表100000条数据。
使用延迟关联优化后
select id,name,work from table
inner join
(select id from table where city="上海" order by salary limit 100000,20) using(id);
1、通过city索引树上找到全部值为“上海”的数据,通过覆盖索引取得id,salary字段
2、将取得的字段进行排序(sort buffer / file sort)
3、取出排序后的前100020条数据抛弃前100000条数据,按分页条件取20条记录id。
4、取得id后进行回表查询这20条记录,最后返回用户20条记录。
这里的回表只需要回表20条数据。
参考:
什么是MySQL的覆盖索引
mysql 链表排序_MySQL 排序的艺术:你真的懂 Order By 吗?
Mysql 延迟关联