首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >从一个翻页查询说起

从一个翻页查询说起

作者头像
跑马溜溜的球
发布2020-12-30 15:55:57
3930
发布2020-12-30 15:55:57
举报
文章被收录于专栏:日积月累1024日积月累1024

1. 问题

有如下数据表:

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `nick` varchar(255) NOT NULL DEFAULT '',
  `image_url` varchar(255) NOT NULL DEFAULT '',
  `exp` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '经验值',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `nick` (`nick`),
  KEY `exp` (`exp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

表中有500万左右的数据,我们执行了下面一个sql(一个稍微变态的翻页操作)。

select * from user order by id limit 2000000, 10;

发现花了2.99秒! 这不能接受啊。 explain一下,结果如下:

+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | index | NULL          | PRIMARY | 4       | NULL | 2000010 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------+

这用到了索引啊,怎么还这么慢呢?

等等,都说MyISAM引擎读数据快,我们把表引擎换下试试!于是alter table, 再次执行

select * from user order by id limit 2000000, 10;

发现花了11.92秒!explain一下,结果如下:

+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+
|  1 | SIMPLE      | user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 5858607 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+

纳尼!filesort都出来了,主键上的索引是假的么!?

  • 当offset巨大时,为什么查询会慢变?
  • 为什么同样的sql, myisam居然比innodb慢这么多?

在解答上面的问题之前,让我们先来学习一下MyISAM和InnoDB索引原理。

2. MyISAM和InnoDB索引原理

2.1 MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图:

image
image

这里假设表一共有三列,我们以Col1为主键,上图是一个MyISAM表的主索引(Primary key)示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。

在MyISAM中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引,则此索引的结构如下图所示:

image
image

MyISAM中索引检索的过程为:首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址,读取相应数据记录。

物理存储方式
  • 表名.frm:表定义
  • 表名.MYD:数据
  • 表名.MYI:索引

2.2 InnoDB索引实现

InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。

下图为InnoDB主索引(同时也是数据文件)的示意图,可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键。

image
image

InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说,InnoDB的所有辅助索引都引用主键作为data域。定义在Col3上的一个辅助索引示意图如下:

image
image

聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。

此外对于InnoDB,不建议使用过长的字段作为主键,因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。

物理存储方式
  • 表名.frm:表定义
  • 表名.ibd:私有表空间,存储该表的数据、索引、和插入缓冲BitMap页。
  • ibdata1:共享表空间,存储其他类的数据如回滚(undo)信息、系统事务信息等。

2.3 两者不同点

  • InnoDB必须有主键,MyISAM可以没有主键。
  • MyISAM索引文件和数据文件是分离的,InnoDB表数据文件本身就是按B+Tree组织的一个索引结构。
  • InnoDB的辅助索引data域存储相应记录主键的值而不是地址。MyISAM的辅助索引data域存放的是数据记录的地址。

3. 问题解答

1. 为什么offset巨大时查询会变慢?

这是由limit实现机制决定的。limit M, N的实现是先选出M+N条满足条件的数据,再扔掉前M条。所以当M巨大时,准确的说是当M+N巨大时,Mysql就要取出巨大的数据,因此会变慢。

2. 为什么不同的引擎对问题中的sql执行会不同?

我们再看下问题中的sql

select * from user order by id limit 2000000, 10;

sql中要求对于数据按id(主索引)排序,再选出第2000001~2000010条。

对于InnoDB, 数据就是按主索引的顺序组织在一起的,因此只要按索引对文件进行顺序遍历即可。也正是这个原因,我们在explain中,可以看到此查询使用了PRIMARY作为索引,处理的数据行数为2000010。

对于MyISAM, 因为我们要取的数据量巨大,而所选的列为*, 无法被索引数据覆盖(索引中只有id),此时mysql认为按索引遍历取数据很可能引发大量磁盘随机读事件,效率可能还不及将所有数据直接读入,再排序。因此,我们看到在explain时,出现了"Using filesort", 处理的行数为5858607,也就是我们数据表的总大小。

这也符合了手册中的说法

If the index does not contain all columns accessed by the query, the index is used only if index access is cheaper than other access methods.

3. 索引覆盖查询列时,sql会变快么?

我们将sql改为:

select id from user order by id limit 2000000, 10;
  • MyISAM:0.45秒:主索引在单独的文件里,遍历起来非常快,然后直接从索引取数据,当然非常快。
  • InnoDB:1.22秒:主索引和数据文件在一起,遍历主索引基本等同于遍历文件,但取的数据少,速度上还是要比之前快一点的。

4. 对于MyISAM,如果取的数据少,会用索引替代filesort么?

我们将sql中的offset减少一个数量级,改为20w, 在MyISAM下执行,只要0.05s。explain结果如下:

+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | user | NULL       | index | NULL          | PRIMARY | 4       | NULL | 200010 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+

可见,mysql的确是对是否该用索引进行的估计,成本小时还是会用索引的。

5. InnoDB如果不用主索引会怎么样呢?

我们使用exp作为索引,将sql改为

select id from user order by exp limit 2000000, 10;

explain结果如下:

+----+-------------+-------+------------+-------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | index | NULL          | exp  | 4       | NULL | 2000010 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+---------+----------+-------------+

选列为id,为什么能用到exp索引呢?回忆下第2部分InnoDB的索引结构就明白了,因为InnoDB的辅助索引里data域存储的是主键值!

将选列改为*

select * from user order by exp limit 2000000, 10;

执行时间44.46s! explain可以看到,使用了filesort。原因大家应该可以自己想到了。

6. 对于limit M, N的优化

本质就是使用将limit M, N转化为条件约束,减少遍历操作,至少要避免filesort。 比如上面的sql改为:

select * from user where id >= (select id from user order by id limit 2000000, 1)order by id limit 10;

主会快很多。explain时会发现,无论是主查询还是子查询都用到了索引。

当然,更好经济方法是在连续翻页时,记录下本次翻到的最大id,在下次翻页时直接使用。

如果你的数据库主键是连续的,就更方便了,sql可以改为:

select * from user where id > 2000000 and id<=2000010 order by id limit 10;

飞一般的感觉!

4. 参考及推荐阅读

本文参与 腾讯云自媒体分享计划,分享自作者个人站点/博客。
原始发表:2020-12-28 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 1. 问题
  • 2. MyISAM和InnoDB索引原理
    • 2.1 MyISAM索引实现
      • 2.2 InnoDB索引实现
        • 2.3 两者不同点
          • 1. 为什么offset巨大时查询会变慢?
          • 2. 为什么不同的引擎对问题中的sql执行会不同?
          • 3. 索引覆盖查询列时,sql会变快么?
          • 4. 对于MyISAM,如果取的数据少,会用索引替代filesort么?
          • 5. InnoDB如果不用主索引会怎么样呢?
          • 6. 对于limit M, N的优化
      • 3. 问题解答
      • 4. 参考及推荐阅读
      相关产品与服务
      云数据库 SQL Server
      腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
      领券
      问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档