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
这里假设表一共有三列，我们以Col1为主键，上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。
在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：
image
MyISAM中索引检索的过程为：首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。
物理存储方式
- 表名.frm：表定义
- 表名.MYD：数据
- 表名.MYI：索引
2.2 InnoDB索引实现
InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
下图为InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键。
image
InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。定义在Col3上的一个辅助索引示意图如下：
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;
飞一般的感觉！
网友评论