Loading

09-索引优化分析(1)

1. 导致性能下降的原因

  1. 查询语句写的烂 → SQL 优化
  2. 索引（单值、复合）失效 → 索引建立
  3. 关联查询太多 Join // 设计缺陷、不得已的需求 → SQL 优化
  4. 服务器调优及各个参数设置（缓冲、线程数等）→ 调整 my.cnf
  5. 数据过多 → 分库分表

2. JOIN 查询

3. 什么是索引？

索引（Index）是帮助 MySQL 高效获取数据的数据结构。

索引本质就是一种数据结构，可以简单理解为“排好序的快速查找数据结构”。一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以「索引文件」的形式存储在磁盘上。

我们知道，数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找（linear search），这种复杂度为 O(n) 的算法在数据量很大时显然是糟糕的，好在计算机科学的发展提供了很多更优秀的查找算法，例如二分查找（binary search）、二叉树查找（binary tree search）等。如果稍微分析一下会发现，每种查找算法都只能应用于特定的数据结构之上，例如二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上，但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织），所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向) 数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是“索引”。

下图为一种可能的索引方式示例。为了加快 Col2 的查找，可以维护一个下图所示的「二叉查找树」，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

虽然上图举的例子是一个货真价实的索引，但是实际的数据库系统几乎没有使用「二叉查找树」或其进化品种「红黑树」实现的。我们平时所说的索引，如果没有特别指明，都是指「B+Tree (多路查找树)」。其中，聚集索引、次要索引、覆盖索引、复合索引、前缀索引、唯一索引均是默认使用 B+Tree 索引，统称“索引”。当然，除了 B+Tree 这种类型的索引之外，还有哈希索引(hash index) 等。

优势：

  • 类似图书馆建书目索引，大大提高数据检索的效率，降低数据库的 IO 成本；
  • 通过索引对数据进行排序，降低数据排序的成本，降低了 CPU 的消耗；
  • 在实现数据的参考完整性方面可以加速表与表之间的连接；
  • 在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间。

劣势：

  • 创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加；
  • 当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度；
  • 索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间；
  • 索引只是提高效率的一个因素，如果你的 MySQL 有大数据量的表，都需要花时间研究建立最优秀的索引或优化查询语句。

【补充】http://blog.codinglabs.org/articles/theory-of-mysql-index.html

4. B-Tree 和 B+Tree

索引是在 MySQL 的「存储引擎层」中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型的。MySQL 目前提供了以下 4 种索引：

  • BTREE 索引： 最常见的索引类型，大部分索引都支持 B 树索引；
  • HASH 索引：只有 Memory 引擎支持，能以 O(1) 时间进行查找，但是失去了有序性，使用场景简单；
  • R-tree 索引：空间索引是 MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍；
  • Full-Text 索引：全文索引也是 MyISAM 的一个特殊索引类型，主要用于全文索引，InnoDB 从 MySQL5.6 版本开始支持全文索引。

4.1 B-Tree 结构

目前大部分数据库系统及文件系统都采用 B-Tree 或其变种 B+Tree 作为索引结构。BTree 又叫“多路平衡搜索树”，一颗 m 叉的 B-Tree 特性如下：

  • 树中每个节点最多包含 m 个孩子；
  • 除根节点与叶子节点外，每个节点至少有 [ceil(m/2)] 个孩子；
  • 若根节点不是叶子节点，则至少有两个孩子；
  • 所有的叶子节点都在同一层；
  • 每个非叶子节点由 n 个 key 与 n+1 个指针组成，其中 [ceil(m/2)-1] <= n <= m-1。

以 5 叉 B-Tree 为例，key 的数量：公式推导 [ceil(m/2)-1] <= n <= m-1。所以 2 <= n <=4，而当 n>4 时，中间节点分裂到父节点，两边节点分裂。

现以插入 C N G A H E K Q M F W L T Z D P R X Y S 数据为例，演变过程如下：

到此，该 B-Tree 就已经构建完成了， B-Tree 和二叉树相比， 查询数据的效率更高， 因为对于相同的数据量来说，B-Tree 的层级结构比二叉树小，因此搜索速度快。

关于 B-Tree 有一系列有趣的性质，例如一个度为 d 的 B-Tree，设其索引 N 个 key，则其树高 h 的上限为 log_d((N+1)/2))，检索一个 key，其查找节点个数的渐进复杂度为 O(log_dN))。从这点也可以看出，B-Tree 是一个非常有效率的索引数据结构。

4.2 B+Tree 结构

B-Tree 有许多变种，其中最常见的是 B+Tree，例如 MySQL 就普遍使用 B+Tree 实现其索引结构。B+Tree 与 B-Tree 的区别为：

  • n 叉 B+Tree 最多含有 n 个 key，而 B-Tree 最多含有 n-1 个 key；
  • B+Tree 的叶子节点保存 key 的所有信息，依 key 大小顺序排列；
  • 所有的非叶子节点都可以看作是 key 的索引部分，其不存储 data，只存储 key；
  • 由于并不是所有节点都具有相同的域，因此 B+Tree 中叶节点和内节点一般大小不同。这点与 B-Tree 不同，虽然 B-Tree 中不同节点存放的 key 和指针可能数量不一致，但是每个节点的域和上限是一致的，所以在实现中 B-Tree 往往对每个节点申请同等大小的空间。

由于 B+Tree 只有叶子节点保存 data 信息，查询任何 key 都要从 root 走到叶子。所以 B+Tree 的查询效率更加稳定。

一般来说，B+Tree 比 B-Tree 更适合实现外存储索引结构，具体原因与外存储器原理及计算机存取原理有关。

4.3 MySQL 中的 B+Tree

MySQL 索引数据结构对经典的 B+Tree 进行了优化。在原 B+Tree 的基础上，每个叶子节点增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的 B+Tree，提高区间访问的性能。

MySQL 中的 B+Tree 索引结构示意图：

做这个优化的目的是为了提高区间访问的性能，例如图中如果要查询 key 为从 17 到 65 的所有数据记录，当找到 17 后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提高了区间查询效率。

4.4 Why B-/+Tree?

上文说过，红黑树等数据结构也可以用来实现索引，但是文件系统及数据库系统普遍采用 B-/+Tree 作为索引结构，这一节将结合计算机组成原理相关知识讨论 B-/+Tree 作为索引的理论基础。

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以「索引文件」的形式存储在磁盘上。这样的话，索引查找过程中就要产生磁盘 I/O 消耗，相对于内存存取，I/O 存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘 I/O 操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘 I/O 的存取次数。

下面先介绍内存和磁盘存取原理，然后再结合这些原理分析 B-/+Tree 作为索引的效率。

a. 主存存取原理

目前计算机使用的主存基本都是随机读写存储器（RAM），现代 RAM 的结构和存取原理比较复杂，这里本文抛却具体差别，抽象出一个十分简单的存取模型来说明 RAM 的工作原理。

从抽象角度看，主存是一系列的存储单元组成的矩阵，每个存储单元存储固定大小的数据。每个存储单元有唯一的地址，现代主存的编址规则比较复杂，这里将其简化成一个二维地址：通过一个行地址和一个列地址可以唯一定位到一个存储单元。上图展示了一个 4 x 4 的主存模型。

主存的存取过程如下：

  • 当系统需要读取主存时，则将〈地址信号〉放到「地址总线」上传给主存，主存读到〈地址信号〉后，解析信号并定位到指定存储单元，然后将此存储单元数据放到「数据总线」上，供其它部件读取。
  • 写主存的过程类似，系统将要写入〈单元地址〉和〈数据〉分别放在「地址总线」和「数据总线」上，主存读取两个总线的内容，做相应的写操作。

这里可以看出，主存存取的时间仅与存取次数呈线性关系，因为不存在机械操作，两次存取的数据的“距离”不会对时间有任何影响，例如，先取 A0 再取 A1 和先取 A0 再取 D3 的时间消耗是一样的。

b. 磁盘存取原理

上文说过，索引一般以文件形式存储在磁盘上，索引检索需要磁盘 I/O 操作。与主存不同，磁盘 I/O 存在机械运动耗费，因此磁盘 I/O 的时间消耗是巨大的。

下图是磁盘的整体结构示意图。

一个磁盘由大小相同且同轴的圆形盘片组成，磁盘可以转动（各个磁盘必须同步转动）。在磁盘的一侧有磁头支架，磁头支架固定了一组磁头，每个磁头负责存取一个磁盘的内容。磁头不能转动，但是可以沿磁盘半径方向运动（实际是斜切向运动），每个磁头同一时刻也必须是同轴的，即从正上方向下看，所有磁头任何时候都是重叠的（不过目前已经有多磁头独立技术，可不受此限制）。

下图是磁盘结构的示意图。

盘片被划分成一系列同心环，圆心是盘片中心，每个同心环叫做一个磁道，所有半径相同的磁道组成一个柱面。磁道被沿半径线划分成一个个小的段，每个段叫做一个扇区，每个扇区是磁盘的最小存储单元。为了简单起见，我们下面假设磁盘只有一个盘片和一个磁头。

当需要从磁盘读取数据时，系统会将〈数据逻辑地址〉传给磁盘，磁盘的控制电路按照寻址逻辑将〈逻辑地址〉翻译成〈物理地址〉，即确定要读的数据在哪个磁道、哪个扇区。为了读取这个扇区的数据，需要将磁头放到这个扇区上方，为了实现这一点，磁头需要移动对准相应磁道，这个过程叫做“寻道”，所耗费时间叫做“寻道时间”，然后磁盘旋转将目标扇区旋转到磁头下，这个过程耗费的时间叫做“旋转时间”。

c. 局部性原理与磁盘预读

由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘 I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的《局部性原理》：

当一个数据被用到时，其附近的数据也通常会马上被使用。

程序运行期间所需要的数据通常比较集中。由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高 I/O 效率。

预读的长度一般为「页」的整倍数。「页」是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每一个存储块称为一页（在许多操作系统中，页的大小通常为 4k），主存和磁盘以「页」为单位交换数据。当程序要读取的数据不在主存中时，会触发一个“缺页异常”，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

d. B(+)树索引性能分析

上文说过一般使用磁盘 I/O 次数评价索引结构的优劣。

先从 B-Tree 分析，根据 B-Tree 的定义，可知检索一次最多需要访问 h 个节点。数据库系统的设计者巧妙利用了「磁盘预读原理」，将一个节点的大小设为等于一个页，这样每个节点只需要一次 I/O 就可以完全载入。为了达到这个目的，在实际实现 B-Tree 还需要使用如下技巧：

每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个 node 只需一次 I/O。

B-Tree 中一次检索最多需要 h-1 次 I/O（根节点常驻内存），渐进复杂度为 O(h)=O(log_dN)。一般实际应用中，出度 d 是非常大的数字，通常超过 100，因此 h 非常小（通常不超过 3）。

综上所述，用 B-Tree 作为索引结构效率是非常高的。

而红黑树这种结构，h 明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的 I/O 渐进复杂度也为 O(h)，效率明显比 B-Tree 差很多。

上文还说过，B+Tree 更适合外存索引，原因和内节点出度 d 有关。从上面分析可以看到，d 越大索引的性能越好，而出度的上限取决于节点内 key 和 data 的大小：d_{max} = floor(pagesize / (keysize + datasize + pointsize))

floor 表示向下取整。由于 B+Tree 内节点去掉了 data 域，因此可以拥有更大的出度，拥有更好的性能。

4.5 Why B+Tree?

为什么说 B+ 树比 B 树更适合实际应用中操作系统的文件索引和数据库索引？

  1. B+ 树的磁盘读写代价更低。因为 B+ 树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比 B 树多，树高比 B 树矮，这样带来的好处是减少磁盘访问次数。B+ 树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对 B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说 IO 读写次数也就降低了。
  2. B+ 树的查询效率更加稳定。由于非终端结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。而且 B+ 树的叶子节点使用指针连接在一起，就形成了带有顺序指针的 B+Tree，提高区间访问的性能（例如查看一个目录下的所有文件，一个表中的所有记录等），这也是很多数据库和文件系统使用 B+ 树的缘故。

5. MySQL 索引实现

在 MySQL 中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，下面将主要讨论 B+Tree 是如何具体实现为 MySQL 中索引，同时将结合 MyISAM 和 InnoDB 存储引擎介绍「非聚集索引」和「聚集索引」两种不同的索引实现形式。

5.1 聚集索引

「聚集索引」并不是一种单独的索引类型，而是一种数据存储方式。术语‘聚集’表示数据行和相邻的键值聚集的存储在一起，即：数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同（InnoDB 的数据文件本身就是索引文件，而 MyISAM 数据文件和索引文件是分开存放的）。

如下图，左侧的索引就是「聚集索引」，因为数据行在磁盘的排列和索引排序保持一致。表中 id 和物理地址是保持一致顺序的，id 较大的行，其物理地址也比较靠后。

由于数据物理存储排序方式只能有一种，因此对应的「聚集索引」只能有一个（一般情况下就是该表的主键）。如果某索引不是「聚集索引」，则表中的行物理顺序与索引顺序不匹配，与「非聚集索引」相比，「聚集索引」有着更快的检索速度。

按照「聚集索引」排列顺序，在查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的 IO 操作。

对于 MySQL 数据库目前只有 InnoDB 数据引擎支持「聚集索引」，而 MyISAM 并不支持「聚集索引」。为了充分利用「聚集索引」的聚集特性，InnoDB 表的主键列尽量选用有序的顺序 id，而不建议用无序的 id，比如 uuid。

自增主键和 uuid 作为主键的区别？

由于主键使用了聚集索引，如果主键是自增 id，那么对应的数据一定也是相邻地存放在磁盘上的，写入性能比较高。如果是 uuid 的形式，频繁的插入会使 InnoDB 频繁地移动磁盘块，写入性能就比较低了。

5.2 MyISAM 索引实现

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

这里设表一共有三列，假设我们以 Col1 为主键，则上图是一个 MyISAM 表的主索引（Primary key）示意。可以看出 MyISAM 的索引文件仅仅保存数据记录的地址。在 MyISAM 中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求 key 是唯一的，而辅助索引的 key 可以重复。如果我们在 Col2 上建立一个辅助索引，则此索引的结构如下图所示：

同样也是一棵 B+Tree，data 域保存数据记录的地址。因此，MyISAM 中索引检索的算法为首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出其 data 域的值，然后以 data 域的值为地址，读取相应数据记录。

MyISAM 的索引方式也叫做“非聚集”的，之所以这么称呼是为了与 InnoDB 的「聚集索引」区分。

5.3 InnoDB 索引实现

虽然 InnoDB 也使用 B+Tree 作为索引结构，但具体实现方式却与 MyISAM 截然不同。

第一个重大区别是：InnoDB 的数据文件本身就是索引文件

从上文知道，MyISAM 索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在 InnoDB 中，表数据文件本身就是按 B+Tree 组织的一个索引结构，这棵树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。

上图是 InnoDB 主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做「聚集索引」。因为 InnoDB 的数据文件本身要按主键聚集，所以 InnoDB 要求表必须有主键（MyISAM 可以没有），如果没有显式指定，则 MySQL 系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键，这个字段长度为 6 个字节，类型为长整形。

第二个与 MyISAM 索引的不同是：InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址。

换句话说，InnoDB 的所有辅助索引都引用主键作为 data 域。例如，下图为定义在 Col3 上的一个辅助索引：

这里以英文字符的 ASCII 码作为比较准则。「聚集索引」这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索 2 遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了 InnoDB 的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在 InnoDB 中不是个好主意，因为 InnoDB 数据文件本身是一颗 B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整，十分低效，而使用“自增字段”作为主键则是一个很好的选择。

6. 索引分类&语法

6.1 单值索引

即一个索引只包含单个列，一个表可以有多个单列索引。

  • 随表一起建索引
    CREATE TABLE customer (
    id INT(10) UNSIGNED AUTO_INCREMENT,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200),
    PRIMARY KEY(id),
    KEY (customer_name)
);
  • 单独建单值索引
    CREATE INDEX idx_customer_name ON customer(customer_name);
  • 删除索引
    DROP INDEX idx_customer_name ON customer;

6.2 唯一索引

索引列的值必须唯一，但允许有空值，NULL 可出现多次。

  • 随表一起建索引
    CREATE TABLE customer (
    id INT(10) UNSIGNED AUTO_INCREMENT,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200),
    PRIMARY KEY(id),
    KEY (customer_name)
    UNIQUE (customer_no)
);
  • 单独建唯一索引
    CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);
  • 删除索引
    DROP INDEX idx_customer_no ON customer;

6.3 主键索引

设定为主键后数据库会自动建立索引，InnoDB 为聚簇索引。

  • 随表一起建索引
    CREATE TABLE customer (
    id INT(10) UNSIGNED AUTO_INCREMENT,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200),
    PRIMARY KEY(id),
);
  • 单独建主键索引
    ALTER TABLE customer ADD PRIMARY KEY customer(customer_no);
  • 删除建主键索引
    ALTER TABLE customer DROP PRIMARY KEY;

6.4 复合索引

即一个索引包含多个列。

  • 随表一起建索引
    CREATE TABLE customer (
    id INT(10) UNSIGNED AUTO_INCREMENT,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200),
    PRIMARY KEY(id),
    KEY (customer_name)
    UNIQUE (customer_no)
    KEY (customer_no, customer_name)
);
  • 单独建索引
    CREATE INDEX idx_no_name ON customer(customer_no, customer_name);
  • 删除索引
    DROP INDEX idx_no_name ON customer;

6.5 其他语法

  • 创建：CREATE [UNIQUE] INDEX [indexName] ON table_name(column)
  • 删除：DROP INDEX [indexName] ON table_name
  • 查看：SHOW INDEX FROM table_name\G
  • ALTER TABLE 方式来添加数据表的索引
    • ALTER TABLE tbl_name ADD PRIMARY KEY (column_list) 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为 NULL；
    • ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)该语句创建索引的值必须是唯一的（除了 NULL 外，NULL 可能会出现多次）；
    • ALTER TABLE tbl_name ADD INDEX index_name (column_list) 添加普通索引，索引值可出现多次；
    • ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list) 该语句指定了索引为 FULLTEXT，用于全文索引。

7. 索引设计原则

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

哪些情况需要创建索引？

  • 对查询频次较高，且数据量比较大的表建立索引；
  • 索引字段的选择，最佳候选列应当从 WHERE 子句的条件中提取，如果 WHERE 子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合；
  • 使用唯一索引（主键自动建立唯一索引），区分度越高，使用索引的效率越高；
  • 使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的 I/O 效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升 MySQL 访问索引的 I/O 效率；
  • 单键/组合索引的选择问题， 组合索引性价比更高。利用最左前缀，N 个列组合而成的组合索引，那么相当于是创建了 N 个索引，如果查询时 WHERE 子句中使用了组成该索引的前几个字段，那么这条查询 SQL 可以利用组合索引来提升查询效率；
  • 查询中与其它表关联的字段，外键关系建立索引；
  • 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度；
  • 查询中统计或者分组字段。

哪些情况不要创建索引？

  • 表记录太少；
  • 经常增删改的表或者字段。索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等 DML 操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低 DML 操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL 也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价；
  • WHERE 条件里用不到的字段不创建索引；
  • 过滤性不好的字段（数据列包含许多重复的内容）不适合建索引。索引的选择性是指索引列中不同的值的数目与表中记录数的比。如果一个表中有 2000 条记录，表索引列有 1980 个不同的值，那么这个索引的选择性就是 1980/2000=0.99。一个索引的选择性越接近于 1，这个索引的效率就越高。

8. 性能分析 EXPLAIN

8.1 简述

使用 EXPLAIN 关键字可以模拟服务层的 Optimizer(优化器) 执行 SQL 查询语句，即：通过查看执行计划，从而知道 MySQL 是如何处理你的 SQL 语句的 → 你就可以分析出查询语句或是表结构的性能瓶颈。

语法结构 EXPLAIN + SQL语句，「执行计划」包含的信息如下：

  • 表的读取顺序
  • 数据读取操作的操作类型
  • 哪些索引可以使用
  • 哪些索引被实际使用
  • 表之间的引用
  • 每张表有多少行被物理查询

8.2 字段说明

a. id

select 查询的序列号，包含一组数字，表示查询中执行 select 子句或操作表的顺序。

  • id 相同，执行顺序由上至下。 // 搭配 table 列进行观察可知，执行顺序为 t1 → t3 → t2
  • id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行。 // 搭配 table 列可知，执行顺序为 t3 → t2 → t1
  • id 相同和不同，同时存在。id 相同的部分，可以认为是一组，从上往下顺序执行；在所有组中，id 值越大，优先级越高，越先执行。 // 执行顺序为 t3 → 衍生表 derived2 (2 表示由 id =2 的查询衍生出来的表；type 肯定是 all，因为衍生的表没有建立索引) → t2

b. select_type

查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。

  • 【SIMPLE】简单的 SELECT 查询，查询中不包含子查询或者 UNION；
  • 【PRIMARY】查询中包含任何复杂的子查询，最外层查询被标记为 PRIMARY；
  • 【DERIVED】在 FROM 列表中包含的子查询被标记为 DERIVED(衍生)，MySQL 会递归执行这些子查询，把结果放在临时表里；
  • 【SUBQUERY】在 SELECT 或 WHERE 列表中包含的子查询；
  • 【DEPENDENT SUBQUERY】在 SELECT 或 WHERE 列表中包含的子查询，子查询基于外层（比 SUBQUERY 就多个 IN）；
  • 【UNCACHEABLE SUBQUREY】无法被缓存的子查询（e.g. 查询语句中有系统变量）；
  • 【UNION】若第二个 SELECT 出现在 UNION 之后，则被标记为 UNION；若 UNION 包含在 FROM 子句的子查询中，外层 SELECT 被标记为 DERIVED；
  • UNION RESULT： 从 UNION 表获取结果的 SELECT；

c. table

对应行正在访问哪一个表，表名或者别名，可能是临时表或者 union 合并结果集。

  • 如果是具体的表名，则表明从实际的物理表中获取数据，也可以是表的别名；
  • 表名是 derivedN 的形式，表示使用了 id 为 N 的查询产生的衍生表；
  • 当有 UNION RESULT 的时候，表名是 union <n1, n2> 等的形式，n1,n2 表示参与 union 的 id。

d. type

表示 MySQL 在表中找到所需行的方式，又称“访问类型”，是较为重要的一个指标。

结果值从最好到最坏依次是：null > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range(尽量保证) > index > ALL

常用的有：system > const > eq_ref > ref > range > index > all。

  • 【system】是 const 类型的特例，当查询的表只有一行的情况下（等于系统表）是 system；
  • 【const】表示通过索引一次就找到了，const 用于比较 Primary Key 或者 Unique Key（因为只匹配一行数据，所以很快）。如将主键置于 WHERE 列表中，MySQL 就能将该查询转换为一个常量；
  • 【eq_ref】类似 ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配。简单来说，就是多表连接中使用 Primary Key 或者 Unique Key 作为关联条件。如下示例中，t1 全表扫描，对于每一个 t1.id，t2 均只有一条记录与匹配；
  • 【ref】非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体；
  • 【range】只检索给定范围的行，使用一个索引来选择行。key 列显示使用了哪个索引，一般就是在你的 WHERE 语句中出现了 between、<、>、in 等的查询，这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
  • 【index】Full Index Scan，index 与 all 区别为 index 类型只遍历索引树。这通常比 all 快，因为索引文件通常比数据文件小（也就是说虽然 all 和 index 都是读全表，但 index 是从索引中读取的，而 all 是从硬盘中读的）；
  • 【all】Full Table Scan，MySQL 将遍历全表以找到匹配的行；
  • 【index_merge】在查询过程中需要多个索引组合使用，通常出现在有 or 的关键字的 SQL 中；
  • 【ref_or_null】
  • 【index_subquery】利用索引来关联子查询，不再全表扫描；
  • 【unique_subquery】该联接类型类似于 index_subquery，子查询中的唯一索引。

const 与 eq_ref 的区别：简单地说 const 是直接按“主键”或“唯一键”读取；eq_ref 用于关联表查询的情况，按关联表的“主键”或“唯一键”联合查询。

  • const
    • 该表最多有一个匹配行，在查询开始时读取。由于只有一行，因此该行中列的值可以被优化器的其余部分视为常量。const 表非常快，因为它们只读一次。
    • const 用于将“主键”或“唯一索引”的所有部分与「常量值」进行比较。在下面的查询中，tbl_name 可以用作 const 表。
      SELECT * FROM tbl_name WHERE primary_key=1;

SELECT * FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2;
  • eq_ref
    • 读取本表中和关联表表中的每行组合成的一行。除了 system 和 const 类型之外，这是最好的联接类型。当连接使用索引的所有部分时，索引是“主键”或“唯一非 NULL 索引”时，将使用该值。
    • eq_ref 可用于使用 = 运算符比较的索引列。比较值可以是「常量值」或「使用此表之前读取的表中的列的表达式」。在下面的示例中，MySQL 可以使用 eq_ref 连接(JOIN) ref_table 来处理。
      SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

eq_ref 与 ref 类型的区别：

  • eq_ref
    • 想象你有两张表。表 A 包含列 (id，text)，其中 id 是主键。表 B 具有相同的列 (id，text)，其中 id 是主键。
      表A包含以下数据：
1, Hello
2, How are

表B有以下数据：
1, world!
2, you?
    • 想象一下 eq_ref 为 A 和 B 之间的 JOIN：select A.text, B.text where A.ID = B.ID。这个 JOIN 非常快，因为对于表 A 中扫描的每一行，表 B 中只能有一行满足 JOIN 条件。一个，不超过一个。那是因为 B.id 是独一无二的。如下是一个伪代码，它说明了服务器端的处理。
      for (rowA in A)
{
    if (existsInBRowWithID(rowA.id)
    {
        addToResult(rowA.text, getRowInBWithID(rowA.id).text);
        break;
    }
}
  • ref
    • 现在想象另一个带有列 (id，text) 的表 C，其中 id 是索引但非 UNIQUE。表 C 具有以下数据：
      1, John!
1, Jack!
    • 想象一下 ref 作为 A 和 C 之间的 JOIN：select A.text, C.text where A.ID = C.ID。如下是一个伪代码，它说明了服务器端的处理；此 JOIN 不如前一个快，因为对于表 A 中扫描的每一行，表 C 中有几个可能的行，它们可以满足 JOIN 条件(上面的循环中没有中断)。那是因为 C.ID 不是独一无二的。
      for (rowA in A)
{
    foreach (rowC in C)
    {
        if (rowA.id == rowC.id)
        {
            addToResult(rowA.text, rowC.text);
        }
    }
}

e. %key%

[possible_keys] 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。[key] 表示实际使用的索引；如果为 NULL，则没有使用索引。

查询中若使用了覆盖索引，即索引关联字段和查询的 select 字段重叠的情况：

[key_len] 表示 !WHERE! 后面的筛选条件命中索引的长度，命中索引所关联的字段越多，长度越长。可通过该列计算查询中使用的索引的长度。

  1. 一般地，key_len 等于索引列类型字节长度，例如 int 类型为 4 bytes，bigint 为 8 bytes；
  2. 如果是字符串类型，还需要同时考虑字符集因素，例如 utf8 字符集 1 个字符占 3 个字节，gbk 字符集 1 个字符占 2 个字节；
  3. 若该列类型定义时允许 NULL，其 key_len 还需要再加 1 bytes；
  4. 若该列类型为变长类型，例如 VARCHAR（TEXT/BLOB 不允许整列创建索引，如果创建部分索引也被视为动态列类型），其 key_len 还需要再加 2 bytes。

字符集会影响索引长度、数据的存储空间，为列选择合适的字符集；变长字段需要额外的 2 个字节，固定长度字段不需要额外的字节。而 NULL 都需要 1 个字节的额外空间，所以以前有个说法：索引字段最好不要为 NULL，因为 NULL 让统计更加复杂，并且需要额外一个字节的存储空间。

ken_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即 key_len 是根据表定义计算而得，不是通过表内检索出的。

f. ref

显示索引的哪一列被使用了，即哪些列或常量(const) 被用于查找索引列上的值。

g. rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录需要读取的行数。

h. extra

包含不适合在其他列中显示但十分重要的额外信息

  • 【Using filesort】说明 order by 字段没用上索引；
  • 【Using temporary】说明 group by 没用上索引 // 通常出现 temporary，也会出现 filesort，因为 group by 也包含排序操作（先排序后分组）；
  • 【Using Index】
  • 【Using join buffer】说明两表关联字段没用上索引；
  • 【Impossible where】
  • 【Select tables optimized away】

8.3 案例

a. 查询顺序

b. 单表优化

c. 两表优化

d. 三表优化

posted @ 2020-11-09 17:37  tree6x7  阅读(133)  评论(0)  编辑  收藏  举报