MySQL 中索引是如何实现的，有哪些类型的索引，如何进行优化索引

标签：sort 如何查询回表索引 MySQL 排序

MySQL 中的索引

MySQL 中的索引

前言

上篇文章聊完了 MySQL 中的锁，这里接着来看下 MySQL 中的索引。

一般当我们数据库中的某些查询比较慢的时候，正常情况下，一顿分析下来，大多数我们会考虑对这个查询加个索引，那么索引是如何工作的呢？为什么索引能加快查询的速度，下面来具体的分析下。

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

索引的优点:

1、索引大大减少了服务器需要扫描的数据量；

2、索引可以帮助服务器避免排序和临时表；

3、索引可以将随机 I/O 变成顺序 I/O。

如何评价一个索引，Relational Database Index Design and the Optimizers 一书介绍了如何评价一个索引是否符合某个查询的三个星际判断标准：

1、一星：索引将相关的记录放在一起就评定为一星；

2、二星：如果索引中的数据顺序和查找中的排序顺序一致就评定为二星；

3、三星：如果索引中的列包含了查询中需要的全部列就评定为三星。

建索引的几大原则

1、最左前缀匹配原则，非常重要的原则，mysql 会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如 a = 1 and b = 2 and c > 3 and d = 4 如果建立 (a,b,c,d) 顺序的索引，d 是用不到索引的，如果建立 (a,b,d,c) 的索引则都可以用到，a,b,d 的顺序可以任意调整；

2、= 和 in 可以乱序，比如 a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式；

3、尽量选择区分度高的列作为索引，区分度的公式是 count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要 join 的字段我们都要求是 0.1 以上，即平均 1 条扫描 10 条记录；

4、索引列不能参与计算，保持列“干净”，比如 from_unixtime(create_time) = ’2014-05-29’ 就不能使用到索引，原因很简单，b+ 树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成 create_time = unix_timestamp(’2014-05-29’)；

5、尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加 (a,b) 的索引，那么只需要修改原来的索引即可。

索引的实现

InnoDB 支持三种索引模型：

1、哈希索引；

2、全文索引；

3、B+ 树索引。

哈希索引

哈希表也称为散列，是一种以键-值 (key-value) 存储数据的结构。输入查询的 key,就能找到对应的 value。哈希的思路很简单，把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。

当然会存在哈希冲突，对个 key 在经过哈希算法处理后可能出现在哈希表中的同一个槽位，当出现哈希冲突的时候，可以使用链表来解决。这样发生冲突的数据都放入到链表中。在进行数据查询的时候，首先找到哈希表中的槽位，然后在链表中依次遍历找到对应的值。

哈希表的这种结构适合于等值查询的场景，在最优场景的下的时间复杂度能达到 O(1)。

哈希索引的缺点

1、因为是哈希表存储的是 Hash 运算之后的 Hash值，所以它只能用于等值的查询，范围查询在哈希索引中不支持；

2、无法利用索引排序，索引中存储的只是 Hash 计算之后的 Hash 值，对于排序，索引本身无法支持；

3、组合索引不能利用部分索引，也就是不支持最左匹配原则，对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用；

4、哈希索引需要进行回表查询，因为哈希索引存储的都是哈希值和行指针，所以不能避免使用索引来避免读取行。

InnoDB 中还有一个特殊的功能叫做"自适应哈希索引(adaptive hash index)"。当 InnoDB 注意到某些索引值被使用的非常频繁时，它会在内存中基于 B+tree 索引之上在创建一个哈希索引，这样就能让 B+tree 索引也有哈希索引快速查找的优点了，这是一个完全自动，内部的过程，用户无法控制或者配置，不过这个功能可以手动关闭。

InnoDB 中的自适应 Hash 相当于是“索引的索引”，采用 Hash 索引存储的是 B+ 树索引中的页面的地址。这也就是为什么可以称自适应 Hash 为索引的索引。采用自适应 Hash 索引目的是可以根据 SQL 的查询条件加速定位到叶子节点，特别是当 B+ 树比较深的时候，通过自适应 Hash 索引可以提高数据的检索效率。同时对于非聚簇索引中的查询，自适应哈希也能减少回表的次数。

全文索引

全文索引就是将存储于数据库中的整本书或整篇文章中的任意内容信息找出来的技术，可以根据需求获取全文中的有关文章，节，段，句，词等信息，也能进行统计和分析。

InnoDB 最早是不支持存储全文索引的，想要使用全文索引就要选用 MySIAM 存储引擎,从版本 1.2.x 开始才增加了全文索引支持。

全文索引一般使用倒排索引(inverted index)实现，倒排索引和 B+ 树索引一样，也是一种索引结构。

倒排索引在辅助表 (auxiliary table) 中存储了单词与单词自身在一个或多个文档中所在位置之间的映射。这样当全文索引，匹配到对应的单词就能知道对应的文档位置了。

倒排索引是区别于正排索引的概念：

正排索引：是以文档对象的唯一 ID 作为索引，以文档内容作为记录的结构。

倒排索引：Inverted index，指的是将文档内容中的单词作为索引，将包含该词的文档 ID 作为记录的结构。

倒排索引中文档和单词的关联关系，通常使用关联数据实现，主要有两种实现方式：

1、inverted file index: 会记录单词和单词所在的文档 ID 之间的映射；

2、full inverted index: 这种方式记录的更详细，除了会记录单词和单词所在的文档 ID 之间的映射，还会记录单词所在文档中的具体位置。

下面来举个栗子

DocumentId 表示全文索引中文件的 id, Text 表示存储的内容。这些存储的文档就是全文索引需要查询的对象。

inverted file index

可以看到关联中，记录了单词和 DocumentId 的映射，这样通过对应的单词就能找到，单词所在的文档，不用一个个遍历文档了。

full inverted index

这种方式和上面的 inverted file index 一样也会记录单词和文档的映射，只不过记录的更详细了，还记录了单词在文档中的具体位置。

相比于 inverted file index 优点就是定位更精确，缺点也是很明显，需要用更多的空间。

InnoDB 存储引擎支持全文索引采用 full inverted index 的方式，将(DocumentId，Position) 视为一个 ilist。

因此在全文检索的表中，一共有两列，一列是 word 字段，另一个是 ilist，并且在 word 字段上设有索引。

记录单词和 DocumentId 的映射关系的表称为 Auxiliary Table（辅助表）。

辅助表是存在与磁盘上的持久化的表，由于磁盘 I/O 比较慢，因此提供 FTS Index Cache（全文检索索引缓存）来提高性能。FTS Index Cache 是一个红黑树结构，根据（word, list）排序，在有数据插入时，索引先更新到缓存中，而后 InnoDB 存储引擎会批量进行更新到辅助表中。

B+ 树索引

B+ 树就是传统意义上的索引，这是目前关系型数据库中查找最为常用和最为有效的索引。B+ 树构造的索引类似于二叉树，根据键值快速 (Key Value) 快速找到数据。

有一点需要注意的是，B+ 树索引并不能找到给定键值具体的行。B+ 树索引能找到的只是被查找数据行所在的页。然后把页读入到内存中，再在内存中查找，找到查询的目标数据。

B+ 树是 B 树的变种，这里需要了解下 B 树。

为什么要引入 B 树或者 B+ 树呢？

红黑树等其它的数据结构也可以用来实现索引，为什么要使用 B 树或者 B+ 树，简单点讲就是为了减少磁盘的 I/O。

一般来说，索引本身的数据量很大，全部放入到内存中是不太现实的，因此索引往往以索引文件的形式存储在磁盘中，磁盘 I/O 的消耗相比于内存中的读取还是大了很多的，在机械硬盘时代，从磁盘随机读一个数据块需要10 ms左右的寻址时间。

为了让一个查询尽量少地读磁盘，就需要减少树的高度，就不能使用二叉树，而是使用 N 叉树了，这样就能在遍历较少节点的情况下也就是较少 I/O 查询的情况下找到目标值。

比如一个二叉树，访问底部数据需要进行4次 I/O 操作。

如果使用4叉树，那么树的层架就会变矮，这时候只需要进行3次 I/O 操作。

数据量越来越大，N 叉树的效果更明显，在有相同数据的情况下，对于二叉树，能够大大缩小树的高度。

所以 B 树和 B+ 树就被慢慢演变而来了。

自平衡二叉树虽然能保持查询操作的时间复杂度在 O(logn)，但是因为它本质上是一个二叉树，每个节点只能有 2 个子节点，那么当节点个数越多的时候，树的高度也会相应变高，这样就会增加磁盘的 I/O 次数，从而影响数据查询的效率。

为了解决降低树的高度的问题，后面就出来了 B 树，它不再限制一个节点就只能有 2 个子节点，而是允许 M 个子节点 (M>2)，从而降低树的高度。

为什么 MySQL 用的是 B+ 树而不是 B 树呢，这里来看下区别？

B-tree 和 B+ 树最重要的区别是 B+ 树只有叶子节点存储数据，其他节点用于索引，而 B-tree 对于每个索引节点都有 Data 字段。

B 树简单的讲就是一种多叉平衡查找树，它类似于普通的平衡二叉树。不同的是 B-tree 允许每个节点有更多的子节点，这样就能大大减少树的高度。

B-Tree 结构图中可以看到每个节点中不仅包含数据的 key 值，还有 data 值。而每一个页的存储空间是有限的，如果 data 数据较大时将会导致每个节点（即一个页）能存储的 key 的数量很小，当存储的数据量很大时同样会导致 B-Tree 的深度较大，增大查询时的磁盘 I/O 次数，进而影响查询效率。在 B+Tree 中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储 key 值信息，这样可以大大加大每个节点存储的 key 值数量，降低 B+Tree 的高度。

B+ 树相比与 B 树：

1、非叶子节点只存储索引信息；

2、所有叶子节点都有一个链指针，所以B+ 树可以进行范围查询；

3、数据都放在叶子节点中。

索引的分类

下面聊的索引，如果没有特殊说明，都是基于 InnoDB 存储引擎来分析的。

从物理角度可分为

1、聚簇索引（clustered index）；

2、非聚簇索引（non-clustered index）。

聚簇索引（clustered index）

聚簇索引并不是一种单独的索引类型，而是一种数据存储的方式。在 InnoDB 中聚簇索引实际上是在同一个结构中保存了 B+ 索引和数据行。

聚簇字面意思就是数据行和索引紧紧在一起的存储在一起。因为一个所以只能和一个数据存储在一起，所以一个表中只有一个聚簇索引。

下面这里来讨论下 InnoDB 中聚簇索引的实现。

InnoDB 中必有要求有聚簇索引的存在，默认会在主键上建立聚簇索引，如果没有主键字段，表中第一个非空唯一索引将会被建立聚簇索引，在前面两者都没有的情况下，InnoDB 将自动生成一个隐式的自增 id 列，并在此列上建立聚簇索引。

聚簇索引的优点

数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快。

缺点

1、插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于 InnoDB 表，一般都会定义一个自增的 ID 列为主键；

2、更新聚簇索引的代价很高，因为会强制 InnoDB 将每个更新的行移动到新的位置；

3、二级索引访问可能需要经过两次查找。

二级索引中保存的不是指向行的物理位置的指针，而是行的主键值，这就意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子结点获取对应的主键值，然后根据这个值去聚簇索引中找到对应的行。所以有两次的查找过程，这种叫做回表操作，在 InnoDB 中，自适应哈希索引能够减少这样重复的工作。

非聚簇索引（non-clustered index）

非聚簇索引也叫二级索引或者辅助索引，辅助索引叶子节点存储的不是具体的行数据，而是行的主键值，所以使用辅助索引会面临二次查找的问题，也就是回表。存储引擎需要找到二级索引的叶子结点获取对应的主键值，然后根据这个值去聚簇索引中找到对应的行。所以有两次的查找过程，这种就叫做回表查询，在 InnoDB 中，自适应哈希索引能够减少这样重复的工作。

联合索引

联合索引指对表上多个列进行索引，联合索引的创建方法和单列索引的创建方法一样，不同的是联合索引有多个索引列。

联合索引中有一个很重要的原则就是最左匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

create table t
(
id int auto_increment primary key,
a int,
b int,
key index_a_b (a,b)
)ENGINE=INNODB;

比如上面的建立了 a 和 b 的联合索引，来看下下面几种查询：

1、SELECT * FROM t WHERE a=1 AND b=3 这样 a 和 b 的查询会命中联合索引 index_a_b；

2、SELECT * FROM t WHERE a=1 这样 a=3 的查询会命中联合索引 index_a_b；

3、SELECT * FROM t WHERE b=3 AND a=1 这样 a 和 b 的查询也会命中联合索引 index_a_b；

4、SELECT * FROM t WHERE b=3 这样 b=3 是不会命中联合索引 index_a_b，因为 b 位于联和索引 index_a_b 第二个位置，不满足最左匹配原则；

来看下联合索引中的最左匹配原则的实现

可以看到联合索引中 index_a_b (a,b) a 是有顺序的，所以索引 a 列是可以使用这个联合索引的，索引 b 列只是相对索引 a 列是有序的，本身是无序，所以单索引 b 列是不能使用这个联合索引的。

同时因为联合索引在第一个键值固定的情况下，第二个键值进行了排序，所以适合下面的查询

SELECT * FROM t WHERE a=6 ORDER BY b desc

联合索引中索引字段的先后顺序该如何选择，栗如 index_a_b (a,b) 这个联合索引，创建的时候，应该将索引列 a 放前面还是索引 b 列放前面呢？

有一个原则就是：将选择性最高的列放到最前面。

选择性最高值得是数据的重复值最少，因为区分度高的列能够很容易过滤掉很多的数据。组合索引中第一次能够过滤掉很多的数据，后面的索引查询的数据范围就小了很多了。

栗如，一个性别字段，值只有两种男或者女，这种区分度就很低了，搜索的话也只能够过滤掉一半的数据，数据量大的时候，这种效果是不明显的。

这里有一个区分度的公式 count(distinct col)/count(*) 表示字段不重复的比例，比例越大扫描的记录书越少，唯一键的区分都是1，例如性别等的字段在数据量很大的情况下接近于0。这个值多少是个标准呢？使用场景不同，这个值也很难确定，一般需要 join 的字段我们都要求是 0.1 以上，即平均 1 条扫描 10 条记录。

覆盖索引

索引确实能够提高查询的效率，但二级索引会有某些情况会存在二次查询也就是回表的问题，这种情况合理的使用覆盖索引，能够提高索引的效率，减少回表的查询。

覆盖索引将需要查询的值建立联合索引，这样索引中就能包含查询的值，这样查询如果只查询索引中的值和主键值就不用进行二次查询了，因为当前索引中的信息已经能够满足当前查询值的请求。

回表查询

回表查询可以理解为二级索引的查询，先定位主键然后，在定位行记录的过程，它的性能相较于一次就定位到数据的查询，效率更低。

一般建立的索引，不管是单列索引还是联合索引，一个索引就对应一课独立的 B+ 树，索引 B+ 树的节点仅仅包含了索引中几个常见的字段以及主键值。

如果根据索引查找到了需要的数据，如果查询的值仅仅是索引中的值和主键值，那么这时候是不需要进行二次查询的，也就是回表查询，因为当前索引中的信息已经能够满足当前查询值的请求，如果查询的字段是还有其他的字段，这种情况，索引中的值不能覆盖了，就需要二次查询了，通过主键值去聚簇索引中找到对应的行，然后返回。

所以说非聚簇索引一定会回表查询吗，答案是否定的，这涉及到查询语句所要求的字段是否全部命中了索引，如果是，那么就不需要回表查询。

explain 使用

参见文章MySQL学习----explain查看一条sql 的性能

索引优化

索引下推

索引下推(index condition pushdown)，是 MySQL5.6 开始支持的一种根据索引进行查询的优化方式。

索引下推，主要是用来通过减少回表的次数，提高查询的性能。

索引条件下推 (ICP) 是针对 MySQL 使用索引从表中检索行的情况的优化。如果没有 ICP，存储引擎会遍历索引以定位基表中的行，并将它们返回给 MySQL 服务器，MySQL 服务器会评估这些 WHERE 行的条件。启用 ICP 后，如果 WHERE仅使用索引中的列可以评估部分条件，则 MySQL 服务器会推送这部分条件 WHERE 条件下降到存储引擎。然后，存储引擎使用索引条目评估推送的索引条件，只有在满足条件时才会从表中读取行。ICP 可以减少存储引擎必须访问基表的次数和 MySQL 服务器必须访问存储引擎的次数。

简单点讲就是在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

举个栗子

假设有一个表包含了一个人的住址信息，然后在其上建立了一个索引：

INDEX ( age , lastname )

然后我们执行以下查询：

SELECT * FROM people
WHERE age = 24
AND name LIKE '%小%';

因为 name 使用了通配符开头的 like，就需要全表扫描了。所以上面的联合索引，只命中了索引 age。

在没有索引下推之前：MySQL 就首先通过 age 索引定位查询的数据，然后命中一部分数据，之后 name 会在这些数据中进行全数据的扫描，首先通过 id 回表查询到对应的数据，然后在对比字段值。

有了索引下推：可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

没有添加索引下推

索引下推优化之后

这样可以看到经过索引下推的优化，原本需要进行 4 次的回表查询，优化之后只需要 2 次的回表查询了。

ICP 的一些使用限制：

1、当 SQL 需要全表访问时，ICP 的优化策略可用于 range, ref, eq_ref, ref_or_null 类型的访问数据方法；

2、支持 InnoDB 和 MyISAM 表；

3、ICP 只能用于二级索引，不能用于主索引；

4、并非全部 WHERE 条件都可以用 ICP 筛选，如果 WHERE 条件的字段不在索引列中，还是要读取整表的记录到 Server 端做 WHERE 过滤；

5、ICP 的加速效果取决于在存储引擎内通过 ICP 筛选掉的数据的比例；

6、MySQL 5.6 版本的不支持分表的 ICP 功能，5.7 版本的开始支持；

7、当 SQL 使用覆盖索引时，不支持 ICP 优化方法。

给字符串字段加索引

给字符串创建所以可以考虑使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

实际上，在创建索引的时候，需要关注区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。因此，我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。

可以使用下面这个语句，算出这个列上有多少个不同的值：

select count(distinct email) as L from SUser;

然后，依次选取不同长度的前缀来看这个值，比如我们要看一下4~7个字节的前缀索引，可以用这个语句：

select 
  count(distinct left(email,4)）as L4,
  count(distinct left(email,5)）as L5,
  count(distinct left(email,6)）as L6,
  count(distinct left(email,7)）as L7,
from SUser;

当然，使用前缀索引很可能会损失区分度，所以你需要预先设定一个可以接受的损失比例，比如 5%。然后，在返回的 L4~L7 中，找出不小于 L * 95% 的值，假设这里 L6、L7 都满足，你就可以选择前缀长度为 6。

不过需要注意的是使用了前缀索引就不能使用覆盖索引对查询性能的优化了。

如果前缀索引的区分度不是很高，还有没有其他的方法了呢？

1、使用倒序存储；

例如身份证信息，前几位大部分都是相同的，区分度不高，但是最后面即为有很高的区分度，那么就可以把身份证的信息，倒序存储。

select field_list from t where id_card = reverse('input_id_card_string');

2、使用 hash 字段；

可以在表上新建一个字段，用来保存身份证的校验码，同时在该字段上建立索引。

每次插入数据除了记录省份证信息，还把身份证的 hash 码，也存储起来，这样查询的使用，通过这个 hash 码，就能很快的找到身份证信息了。

不过因为 hash 码会存在相同的情况，所以查询的条件本上还需要带上身份证号码。

select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

MySQL 中的 count 查询

MySQL中的 count(*) 的实现方式

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；
而 InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

InnoDB 中 count(*)、count(主键id)和count(1) ，返回的都是满足条件的结果集的总行数，count(字段)，则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。

count(主键id): InnoDB 引擎会遍历整张表，把每一行的 id 值取出来，返回给 server 层，server层拿到 id 后，判断是不可能为空的，就按行累加；

count(1): InnoDB引擎遍历整张表，但不取值。server层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加；

count(字段):

1、如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；

2、如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。

count(*): 这个统计专门做了优化，不用取值，count(*) 肯定不是 null，按行累加。

所以按照效率排序 count(字段)<count(主键id)<count(1)≈count(*)。

MySQL 中的 order by

MySQL 中的排序是我们经常用到的操作，下面来研究下排序的实现过程。

CREATE TABLE `t_user_city` (
  `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;

insert into t_user_city values(1, "郑州", "小明",12,"郑州大街6666号201");
insert into t_user_city values(2, "杭州", "小红",20,"杭州大街6666号201");
insert into t_user_city values(3, "杭州", "小白",19,"杭州大街6666号201");
insert into t_user_city values(4, "上海", "张三",24,"上海大街6666号201");
insert into t_user_city values(5, "上海", "李四",25,"上海大街6666号201");
insert into t_user_city values(6, "上海", "王五",26,"上海大街6666号201");
insert into t_user_city values(7, "上海", "王六",29,"上海大街6666号201");
insert into t_user_city values(8, "郑州", "小明001",12,"郑州大街6666号201");
insert into t_user_city values(9, "郑州", "小明002",12,"郑州大街6666号201");
insert into t_user_city values(10, "郑州", "小明003",12,"郑州大街6666号201");

可以看到上面的表，是有一个 city 字段的索引。

select city,name,age from t_user_city where city='上海' order by name limit 1000  ;

来分析下排序的过程

全字段排序

MySQL 会给每个查询线程分配一个用于排序的内存: sort_buffer。

通过上面查询的栗子来看下MySQL 中是如何使用 sort_buffer 来进行排序的。

1、首先 MySQL 会给米一个查询线程分配一快大小为 sort_buffer_size 的排序内存 sort_buffer，放入查询和排序的字段，所以字段 city,name,age 都会被放入到排序内存 sort_buffer 中；

2、查询首先使用索引 city 来确定查询的数据，然后查询到的数据都会通过查询到的主键 id 进行一次回表操作，查询到 city,name,age 字段，然后放入到 sort_buffer 中；

3、所有的数据都放入到排序内存 sort_buffer 之后，会根据排序字段对 sort_buffer 中的数据进行排序；

4、按照排序结果取前 1000 行返回给客户端，整个排序结束。

如果查询的数据量很大，sort_buffer 内存中就放不下了，这时候就需要使用磁盘临时文件辅助排序。

使用的就是外部排序，一般使用归并排序。使用外部排序的时候，MySQL 会将排序的文件分成 N 份，每一份单独排序后放入到一个临时文件中，然后再把这 N 个有序文件合成一个有序的大文件。

如果 sort_buffer_size 超过了需要排序的数据量的大小，number_of_tmp_files 就是0，表示排序可以直接在内存中完成。

在排序的文件在一定额度的情况下，如果 sort_buffer_size 越小，那么借助于磁盘排序徐的时候，需要的临时文件也就越多，发生 I/O 的次数也就越多，性能也就越差。

可以通过下面的命令来查看一个排序语句是否使用了临时文件

/* 打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 执行语句 */
select city,name,age from t_user_city where city='上海' order by name limit 1000  ;

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

/* @b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 计算Innodb_rows_read差值 */
select @b-@a;

这里来重点看下

"filesort_summary": {
	"memory_available": 262144,
	"key_size": 512,
	"row_size": 654,
	"max_rows_per_buffer": 15,
	"num_rows_estimate": 15,
	"num_rows_found": 4, // 参与排序的行
	"num_initial_chunks_spilled_to_disk": 0, // 表示产生了多少个文件用于外部排序，如果为0，说明没有外部排序
	"peak_memory_used": 32800,
	"sort_algorithm": "std::sort",
	"sort_mode": "<fixed_sort_key, packed_additional_fields>"
}

sort_mode

MySQL 有 3 种排序模式

1、< sort_key, rowid > 对应的是 MySQL 4.1 之前的“原始排序模式”，即 rowid 排序；

2、< sort_key, additional_fields > 对应的是 MySQL 4.1 以后引入的“修改后排序模式”即全字段排序；

3、< sort_key, packed_additional_fields > 是 MySQL 5.7.3 以后引入的进一步优化的”打包数据排序模式”，是对全字段排序的优化。对于额外字段数据类型为：CHAR、VARCHAR、以及可为 NULL 的固定长度数据类型，其字段值长度是可以被压缩的。例如，在无压缩的情况下，字段数据类型为VARCHAR(255)，当字段值只有 3 个字符时，却需要占用 sort buffer 255 个字符长度的内存空间大小；而在有压缩的情况下，字段值为 3 个字符，却只占用 3 个字符长度 + 2 个字节长度标记的内存空间大小；当字段值为 NULL 时，只需通过一个位掩码来标识。

rowid 排序

全字段排序会把字段放入到在 sort_buffer 或临时文件中进行排序，但是当查询的返回的字段很多，那么 sort_buffer 中要放入的字段很多，那么就意味着能够放下的条数很少了，需要生成的临时文件就会变多，排序的性能会变差。

如何优化呢？MySQL 中使用了 rowid 排序来解决这种场景。

rowid 排序原理的大致思路就是，不会将 SQL 语句中 select 后面的所有字段都放入到 sort_buffer 中，而是只会将需要被排序的字段和主键 id 放入到 sort_buffer 中，对应到本文的例子中就是：将 name 字段和主键 id 字段放入到 sort_buffer 中。

那么 MySQL 判断单行长度的标准是什么呢？

通过 max_length_for_sort_data 字段，MySQL 中专门用于控制排序的行数据长度的字段，如果超过了这个长度，那么就会使用 rowid 排序算法了。

来看下 rowid 排序的过程

1、首先 MySQL 会为应用进程分配一块内存大小为 sort_buffer_size 的内存，然后确定放入的字段，假定这时候字段 city,name,age 的长度之和已超过了 max_length_for_sort_data 的限制，这时候就需要用到 rowid 排序了，这时候放入到 sort_buffer 中的字段只有要排序的列 name 字段和主键 id;

2、查询首先使用索引 city 来确定查询的数据，然后查询到的数据都会通过查询到的主键 id 进行一次回表操作(第一次回表)，查询到的 name,id 字段放入到 sort_buffer 中；

3、所有的数据都放入到排序内存 sort_buffer 之后，会根据排序字段对 sort_buffer 中的数据进行排序；

4、遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name和age 三个字段返回给客户端(第二次回表)，排序结束。

可以看到相比于相比全字段排序而言，rowid 排序的多了一次回表的查询操作。

总结下来就是

如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。

如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。

这也就体现了 MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。

对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。

MySQL 之所以需要生成临时表，并且在临时表上做排序操作，其原因是原来的数据都是无序的。如何优化，在排序的字段上建索引。因为索引是有序的，给排序字段建立索引，就能用到索引的有序性来排序了。

联合索引

针对上面排序的两种方式，想要拥有更好的查询性能，我们可以考虑加联合索引，例如上面的栗子，我们可以考虑给 city,name 建立联合索引，因为索引是有序的，这样既能满足查询的优化需求，也能满足排序的需求。

这是最优解吗？

因为查询的 age 字段不在索引内，所以查询排序之后还是需要进行回表操作的。

这个查询的最优解就是建立 city,name,age 字段的联合索引也叫覆盖索引，这样通过索引就能返回所有查询的值了。

主键选择自增还是使用 UUID

主键是用自增还是 UUID 呢？

主键索引最好是自增的。

InnoDB 创建主键索引默认为聚簇索引，数据被存放在了 B+Tree 的叶子节点上。也就是说，同一个叶子节点内的各个数据是按主键顺序存放的，因此，每当有一条新的数据插入时，数据库会根据主键将其插入到对应的叶子节点中。

1、如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页；

2、如果使用非自增主键（如uuid），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到索引页的随机某个位置，此时 MySQL 为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成索引碎片，得到了不够紧凑的索引结构，后续不得不通过 OPTIMIZE TABLE 来重建表并优化填充页面。

总结

1、索引的优点:

1、索引大大减少了服务器需要扫描的数据量；
2、索引可以帮助服务器避免排序和临时表；
3、索引可以将随机 I/O 变成顺序 I/O。

2、InnoDB 中模式的索引模型是 B+ 树索引；

3、B 树简单的讲就是一种多叉平衡查找树，它类似于普通的平衡二叉树。不同的是 B-tree 允许每个节点有更多的子节点，这样就能大大减少树的高度；

4、聚簇索引；