1 索引模型

1.1 哈希表

哈希的思路很简单，把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。不可避免地，多个 key 值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表。哈希表的结构做范围查询的时候需要做全表扫描，只适用于等值查询的场景，比如 Memcached及其他一些 NoSQL 引擎。

1.2 有序数组

有序数组在等值查询和范围查询场景中的性能就都非常优秀。如果仅仅看查询效率，有序数组就是最好的数据结构了。但是，在需要更新数据的时候就麻烦了，你往中间插入一个记录就必须得挪动后面所有的记录，成本太高。所以，有序数组索引只适用于静态存储引擎

1.3 搜索树

二叉搜索树的特点是：父节点左子树所有结点的值小于父节点的值，右子树所有结点的值大于父节点的值。这样如果你要查 ID_card_n2 的话，按照图中的搜索顺序就是按照 UserA -> UserC -> UserF -> User2 这个路径得到。这个时间复杂度是O(log(N))。当然为了维持 O(log(N)) 的查询复杂度，你就需要保持这棵树是平衡二叉树。

为了做这个保证，更新的时间复杂度也是O(log(N))。但是实际上大多数的数据库存储却并不使用二叉树。其原因是，索引不止存在内存中，还要写到磁盘上。考虑到磁盘随机读取数据块的寻址时间，搜索树一般比较矮胖，也就是减少寻址时间。

2 InnoDB索引模型

2.1 简介

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。每一个索引在 InnoDB 里面对应一棵 B+ 树。

2.2 二叉树的演进

B＋树是由二叉树不断演化过来的。大致的演化路线如下：

二叉查找树
平衡二叉树
B树
B+树

2.2.1 二叉查找树

二叉查找树一棵空树，或满足以下特点的二叉树

若任意节点的左子树不空，则左子树上所有节点的值均小于它根节点的值；
若任意节点的右子树不空，则右子树上所有节点的值均大于它根节点的值；
任意节点的左、右子树也分别为二叉查找树；
没有键值相等的节点。

注意

我们很容易发现，这颗二叉查找树的查找效率跟顺序查找差不多。主要原因是树的高度比较高，左右子树不平衡。我们需要这颗二叉查找树是平衡的，于是就演化出了平衡二叉查找树。

2.2.2 平衡二叉树 AVL

是一棵二叉查找树。
必须满足任何节点的两个子树的高度最大差为1

注意

平衡二叉树的查询效率很高，但是插入和更新节点后需要通过左旋和右旋来维护树的平衡有一定的开销。一般用于内存结构对象中，所以维护的开销相对较小。
如果数据量巨大，无法维护在内存中。我们往往需要把数据保存在磁盘上。由于平衡二叉树的高度比较高，就需要进行多次磁盘I/O。这显然是非常低效的。

我们需要降低树的高度，多叉树是一个很好的演化方向，于是就演化出了B树。

2.2.3 B树

一棵m阶的B树有以下特性:

每个结点，至多有m棵子树，即至多含m-1个关键字
若根结点非叶子结点，则至少有两棵子树
除根结点外，所有非叶子结点至少有⌈m/2⌉，即至少含有⌈m/2⌉-1个关键字
所有叶子结点都出现在同一层次上，并不带信息（即NULL）。即绝对平衡

注意

B树降低了树的高度，但是无法进行区间查找，于是演化出了B+树。

2.2.4 B+树

B树与B+树的异同点：

B+树由在B树的基础上，增加叶节点上的双向指针，可以进行顺序查找。
内部节点只存放关键字和孩子指针，因此最大化了内部节点的分支因子(也就是树的阶树更多，高度更低)。
有k个子树的中间节点包含有k个元素（B树中是k-1个元素）

优势：层级更低，IO 次数更少；每次都需要查询到叶子节点，查询性能稳定；叶子节点形成有序链表，范围查询方便

3 索引分类

3.1 聚簇索引

mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)，两棵树的示例示意图如下。

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。
非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

3.2 覆盖索引

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。（不需要回表）

3.3 最左前缀索引

这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

3.3.1 场景分析

数据准备

mysql> alter table SUser add index index1(email);
或
mysql> alter table SUser add index index2(email(6));

3.3.1.1 存储空间

由于 email(6) 这个索引结构中每个邮箱字段都只取前 6 个字节（即：zhangs），所以占用的空间会更小，这就是使用前缀索引的优势。

3.3.1.2 扫描次数

select id,name,email from SUser where email='zhangssxyz@xxx.com';

如果使用的是 index1（即 email 整个字符串的索引结构），执行顺序是这样的：

从 index1 索引树找到满足索引值是’zhangssxyz@xxx.com’的这条记录，取得 ID2 的值；
到主键上查到主键值是 ID2 的行，判断 email 的值是正确的，将这行记录加入结果集；
取 index1 索引树上刚刚查到的位置的下一条记录，发现已经不满足 email='zhangssxyz@xxx.com’的条件了，循环结束。
这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。

如果使用的是 index2（即 email(6) 索引结构），执行顺序是这样的：

从 index2 索引树找到满足索引值是’zhangs’的记录，找到的第一个是 ID1；
到主键上查到主键值是 ID1 的行，判断出 email 的值不是’zhangssxyz@xxx.com’，这行记录丢弃； 
取 index2 上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出 ID2，再到 ID 索引上取整行然后判断，这次值对了，将这行记录加入结果集；
重复上一步，直到在 idxe2 上取到的值不是’zhangs’时，循环结束。在这个过程中，要回主键索引取 4 次数据，也就是扫描了 4 行。
使用前缀索引后，可能会导致查询语句读数据的次数变多。

注意使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

3.3.2 前缀索引的影响

前面我们说了使用前缀索引可能会增加扫描行数，这会影响到性能。其实，前缀索引的影响不止如此，我们再看一下另外一个场景。

select id,email from SUser where email='zhangssxyz@xxx.com';

select id,name,email from SUser where email='zhangssxyz@xxx.com';

index1（即 email 整个字符串的索引结构）的话，可以利用覆盖索引，从 index1 查到结果后直接就返回了，不需要回到 ID 索引再去查一次。
如果使用 index2（即 email(6) 索引结构）的话，就不得不回到 ID 索引再去判断 email 字段的值。即使你将 index2 的定义修改为 email(18) 的前缀索引，这时候虽然 index2 已经包含了所有的信息，但 InnoDB 还是要回到 id 索引再查一下，因为系统并不确定前缀索引的定义是否截断了完整信息。

注意

使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。

3.4 索引下推

MySQL 5.6 引入的索引下推优化（index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

案例分析：联合索引（name, age）为例。如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是 10 岁的所有男孩”。那么，SQL 语句是这么写的：

mysql> select * from tuser where name like '张%' and age=10 and ismale=1;

在 (name,age) 索引里面我特意去掉了 age 的值，这个过程 InnoDB 并不会去看 age 的值，只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此，需要回表 4 次。(没有索引下推)

InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。在我们的这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。

4 索引查询过程

数据准备

数据表

mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

索引树

4.1 查询过程

select id from T where k=5

查询语句在索引树上查找的过程，先是通过 B+ 树从树根开始，按层搜索到叶子节点，也就是图中右下角的这个数据页，然后可以认为数据页内部通过二分法来定位记录。

对于普通索引来说，查找到满足条件的第一个记录 (5,500) 后，需要查找下一个记录，直到碰到第一个不满足 k=5 条件的记录。
对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

4.1.1 数据页查询

InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。如果下一个记录在数据页中，则查询性能差异几乎可以忽略不计算；如果不在，需要从磁盘中读取数据页，这个过程稍微复杂一些。

4.1.2 数据页更新

4.1.2.1 change buffer

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数innodb_change_buffer_max_size来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

4.1.2.2 merge

将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行merge操作。

4.1.2.3 应用场景

因为 merge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

4.1.2.4 扩展

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要插入 (4,400) 这个记录，就要先判断现在表中是否已经存在 k=4 的记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。

5 索引的更新过程

这个记录要更新的目标页在内存中。这时，InnoDB 的处理流程如下：

对于唯一索引来说，找到 3 和 5 之间的位置，判断到没有冲突，插入这个值，语句执行结束；
对于普通索引来说，找到 3 和 5 之间的位置，插入这个值，语句执行结束。

这样看来，普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的 CPU 时间。

第二种情况是，这个记录要更新的目标页不在内存中。这时，InnoDB 的处理流程如下：

对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。

将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

6 索引优化选择

数据准备

数据表

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB;

数据

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into t values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。考虑的主要因素包括：

索引区分度
扫描行数
是否使用临时表
是否排序

6.1 基数 (索引区分度)

6.1.1 简介

MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。

6.1.2 计算过程

计算采用的是采样统计的方式。采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。在 MySQL 中，有两种存储索引统计的方式，可以通过设置参数 innodb_stats_persistent 的值来选择：

设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是 20，M 是 10。
设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是 8，M 是 16。

6.2 扫描行数

对于一个具体的语句来说，优化器还要判断，执行这个语句本身要扫描多少行。我们再一起看看优化器预估的，下面两个语句的扫描行数是多少。

为什么放着扫描 37000 行的执行计划不用，却选择了扫描行数是 100000 的执行计划呢？

如果使用索引 a，每次从索引 a 上拿到一个值，都要回到主键索引上查出整行数据，这个代价优化器也要算进去的。而如果选择扫描 10 万行，是直接在主键索引上扫描的，没有额外的代价。优化器会估算这两个选择的代价，从结果看来，优化器认为直接扫描主键索引更快。当然，从执行时间看来，这个选择并不是最优的。这个策略没什么问题，只是估算行数有点误差。

6.3 临时表

6.3.1 简介

临时表与内存表

内存表，指的是使用 Memory 引擎的表，建表语法是 create table … engine=memory。这种表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在。除了这两个特性看上去比较“奇怪”外，从其他的特征上看，它就是一个正常的表。
临时表，可以使用各种引擎类型。如果是使用 InnoDB 引擎或者 MyISAM 引擎的临时表，写数据的时候是写到磁盘上的。当然，临时表也可以使用 Memory 引擎。

6.3.2 特性

建表语法是 create temporary table …
一个临时表只能被创建它的 session 访问，对其他线程不可见。由于临时表只能被创建它的 session 访问，所以在这个 session 结束的时候，会自动删除临时表
不同线程可以创建同名的临时表。
临时表可以与普通表同名。session A 内有同名的临时表和普通表的时候，show create 语句，以及增删改查语句访问的是临时表。
show tables 命令不显示临时表。

6.3.3 应用场景

数据准备

create table t1(id int primary key, a int, b int, index(a));
delimiter ;;
create procedure idata()
begin
  declare i int;

  set i=1;
  while(i<=1000)do
    insert into t1 values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

6.3.3.1 Union

1. 执行SQL

(select 1000 as f) union (select id from t1 order by id desc limit 2);

2. explain

3. 执行流程

创建一个内存临时表，这个临时表只有一个整型字段 f，并且 f 是主键字段。
执行第一个子查询，得到 1000 这个值，并存入临时表中。
执行第二个子查询：拿到第一行 id=1000，试图插入临时表中。但由于 1000 这个值已经存在于临时表了，违反了唯一性约束，所以插入失败，然后继续执行；取到第二行 id=999，插入临时表成功。
从临时表中按行取出数据，返回结果，并删除临时表，结果中包含两行数据分别是 1000 和 999。

这里的内存临时表起到了暂存数据的作用，而且计算过程还用上了临时表主键 id 的唯一性约束，实现了 union 的语义。

6.3.3.2 Union ALL

union 改成 union all 的话，就没有了“去重”的语义。这样执行的时候，就依次执行子查询，得到的结果直接作为结果集的一部分，发给客户端。因此也就不需要临时表了。

6.3.3.3 Group By

1. 执行的SQL

select id%10 as m, count(*) as c from t1 group by m;

2. explain

3. 执行流程

创建内存临时表，表里有两个字段 m 和 c，主键是 m；
扫描表 t1 的索引 a，依次取出叶子节点上的 id 值，计算 id%10 的结果，记为 x；如果临时表中没有主键为 x 的行，就插入一个记录 (x,1);如果表中有主键为 x 的行，就将 x 这一行的 c 值加 1
遍历完成后，再根据字段 m 做排序，得到结果集返回给客户端。

4. 排序（内存是快速排序，磁盘是归并排序）

内存临时表的大小是有限制的，参数 tmp_table_size 就是控制这个内存大小的，默认是 16M。超过上限，内存临时表会转成磁盘临时表，磁盘临时表默认使用的引擎是 InnoDB。

5. 优化

select id%10 as m, count(*) as c from t1 group by m;

不论是使用内存临时表还是磁盘临时表，group by 逻辑都需要构造一个带唯一索引的表，执行代价都是比较高的。如果表的数据量比较大，上面这个 group by 语句执行起来就会很慢，我们有什么优化的方法呢？

要解决 group by 语句的优化问题，你可以先想一下这个问题：执行 group by 语句为什么需要临时表？group by 的语义逻辑，是统计不同的值出现的个数。但是，由于每一行的 id%10 的结果是无序的，所以我们就需要有一个临时表，来记录并统计结果。

方案一：

核心问题：控制输入的数据有序就可以不使用临时表，而且不需要排序。

InnoDB 的索引，就可以满足这个输入有序的条件。在 MySQL 5.7 版本支持了 generated column 机制，用来实现列数据的关联更新。你可以用下面的方法创建一个列 z，然后在 z 列上创建一个索引（如果是 MySQL 5.6 及之前的版本，你也可以创建普通列和索引，来解决这个问题）。

alter table t1 add column z int generated always as(id % 100), add index(z);
select z, count(*) as c from t1 group by z;

explain

方案二

在 group by 语句中加入SQL_BIG_RESULT这个提示（hint），就可以告诉优化器：这个语句涉及的数据量很大，请直接用磁盘临时表。MySQL 的优化器一看，磁盘临时表是 B+ 树存储，存储效率不如数组来得高。所以，既然你告诉我数据量很大，那从磁盘空间考虑，还是直接用数组来存吧。

select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;

执行流程就是这样的：

初始化 sort_buffer，确定放入一个整型字段，记为 m；
扫描表 t1 的索引 a，依次取出里面的 id 值, 将 id%100 的值存入 sort_buffer 中；扫描完成后，对 sort_buffer 的字段 m 做排序（如果 sort_buffer 内存不够用，就会利用磁盘临时文件辅助排序）；
排序完成后，就得到了一个有序数组。根据有序数组，得到数组里面的不同值，以及每个值的出现次数。

6. 总结

如果对 group by 语句的结果没有排序要求，要在语句后面加 order by null；
尽量让 group by 过程用上表的索引，确认方法是 explain 结果里没有 Using temporary 和 Using filesort；
如果 group by 需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大 tmp_table_size 参数，来避免用到磁盘临时表；
如果数据量实在太大，使用 SQL_BIG_RESULT 这个提示，来告诉优化器直接使用排序算法得到 group by 的结果。

6.4 排序

数据准备

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

执行的具体SQL如下：

select city,name,age from t where city='杭州' order by name limit 1000  ;

6.4.1 全字段排序

6.4.1.1 explain

6.4.1.2 执行流程

初始化 sort_buffer，确定放入 name、city、age 这三个字段；
从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是上图中的 ID_X；
到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；
从索引 city 取下一个记录的主键 id；
重复步骤 3、4 直到 city 的值不满足查询条件为止，对应的主键 id 也就是图中的 ID_Y；
对 sort_buffer 中的数据按照字段 name 做快速排序；按照排序结果取前 1000 行返回给客户端。

6.4.1.3 扩展

Extra 这个字段中的“Using filesort”表示的就是需要排序，MySQL 会给每个线程分配一块内存用于排序，称为sort_buffer。
“按 name 排序”这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数sort_buffer_size。sort_buffer_size，就是 MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。外部排序一般使用归并排序算法。

6.4.2 rowid排序

6.4.2.1 排序算法转换

SET max_length_for_sort_data = 16;

max_length_for_sort_data，是 MySQL 中专门控制用于排序的行数据的长度的一个参数。它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法。

6.4.2.2 执行流程

初始化 sort_buffer，确定放入两个字段，即 name 和 id；
从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；
到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；
从索引 city 取下一个记录的主键 id；
重复步骤 3、4 直到不满足 city='杭州’条件为止，也就是图中的 ID_Y；
对 sort_buffer 中的数据按照字段 name 进行排序；
遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。

注意：如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行

6.4.3 临时表排序

数据准备

mysql> CREATE TABLE `words` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `word` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=0;
  while i<10000 do
    insert into words(word) values(concat(char(97+(i div 1000)), char(97+(i % 1000 div 100)), char(97+(i % 100 div 10)), char(97+(i % 10))));
    set i=i+1;
  end while;
end;;
delimiter ;

call idata();

6.4.3.1 内存临时表

执行SQL，随机排序取前 3 个。虽然这个 SQL 语句写法很简单，但执行流程却有点复杂的。如下：

mysql> select word from words order by rand() limit 3;

Extra 字段显示 Using temporary，表示的是需要使用临时表；Using filesort，表示的是需要执行排序操作。因此这个 Extra 的意思就是，需要临时表，并且需要在临时表上排序。

语句的详细执行流程如下：

创建一个临时表。这个临时表使用的是 memory 引擎，表里有两个字段，第一个字段是 double 类型，为了后面描述方便，记为字段 R，第二个字段是 varchar(64) 类型，记为字段 W。并且，这个表没有建索引。
从 words 表中，按主键顺序取出所有的 word 值。对于每一个 word 值，调用 rand() 函数生成一个大于 0 小于 1 的随机小数，并把这个随机小数和 word 分别存入临时表的 R 和 W 字段中，到此，扫描行数是 10000。
现在临时表有 10000 行数据了，接下来你要在这个没有索引的内存临时表上，按照字段 R 排序。
初始化 sort_buffer。sort_buffer 中有两个字段，一个是 double 类型，另一个是整型。从内存临时表中一行一行地取出 R 值和位置信息，分别存入 sort_buffer 中的两个字段里。这个过程要对内存临时表做全表扫描，此时扫描行数增加 10000，变成了 20000。
在 sort_buffer 中根据 R 的值进行排序(rowid排序)。注意，这个过程没有涉及到表操作，所以不会增加扫描行数。
排序完成后，取出前三个结果的位置信息，依次到内存临时表中取出 word 值，返回给客户端。这个过程中，访问了表的三行数据，总扫描行数变成了 20003。

6.4.3.2 磁盘临时表

tmp_table_size这个配置限制了内存临时表的大小，默认值是 16M。如果临时表大小超过了 tmp_table_size，那么内存临时表就会转成磁盘临时表。磁盘临时表使用的引擎默认是 InnoDB，是由参数internal_tmp_disk_storage_engine控制的。

除了全字段、rowid排序之外MySQL5.6引入了优先队列排序算法。其实，我们现在的 SQL 语句，只需要取 R 值最小的 3 个 rowid。但是，如果使用归并排序算法的话，虽然最终也能得到前 3 个值，但是这个算法结束后，已经将 10000 行数据都排好序了。也就是说，后面的 9997 行也是有序的了。但，我们的查询并不需要这些数据是有序的。所以，想一下就明白了，这浪费了非常多的计算量。

优先队列算法，就可以精确地只得到三个最小值，执行流程如下：

对于这 10000 个准备排序的 (R,rowid)，先取前三行，构造成一个堆；
取下一个行 (R’,rowid’)，跟当前堆里面最大的 R 比较，如果 R’小于 R，把这个 (R,rowid) 从堆中去掉，换成 (R’,rowid’)；
重复第 2 步，直到第 10000 个 (R’,rowid’) 完成比较。

这个流程结束后，我们构造的堆里面，就是这个 10000 行里面 R 值最小的三行。然后，依次把它们的 rowid 取出来，去临时表里面拿到 word 字段，返回给客户端。

【提示】使用优先队列的前提是在内存中排序，如果排序数据超出限制还是要使用归并的，即sort_buffer_size参数的限制。

【MySQL】索引

1 索引模型

1.1 哈希表

1.2 有序数组

1.3 搜索树

2 InnoDB索引模型

2.1 简介

2.2 二叉树的演进

2.2.1 二叉查找树

2.2.2 平衡二叉树 AVL

2.2.3 B树

2.2.4 B+树

3 索引分类

3.1 聚簇索引

3.2 覆盖索引

3.3 最左前缀索引

3.3.1 场景分析

3.3.1.1 存储空间

3.3.1.2 扫描次数

3.3.2 前缀索引的影响

3.4 索引下推

4 索引查询过程

4.1 查询过程

4.1.1 数据页查询

4.1.2 数据页更新

4.1.2.1 change buffer

4.1.2.2 merge

4.1.2.3 应用场景

4.1.2.4 扩展

5 索引的更新过程

6 索引优化选择

6.1 基数 (索引区分度)

6.1.1 简介

6.1.2 计算过程

6.2 扫描行数

6.3 临时表

6.3.1 简介

6.3.2 特性

6.3.3 应用场景

6.3.3.1 Union

6.3.3.2 Union ALL

6.3.3.3 Group By

6.4 排序

6.4.1 全字段排序

6.4.1.1 explain

6.4.1.2 执行流程

6.4.1.3 扩展

6.4.2 rowid排序

6.4.2.1 排序算法转换

6.4.2.2 执行流程

6.4.3 临时表排序

6.4.3.1 内存临时表

6.4.3.2 磁盘临时表