日常工作中，笔者会不定期的优化mysql。尤其是在618、双11大促备战时，需要对所有重要系统进行健康度检查，mysql慢日志是非常重要的一项必查项，从实践来看多数mysql的性能问题都是由sql查询语句和索引设计引起的。

从笔者的观察来看，日常工作中研发同学大部分时间忙于需求开发，很少关注这块的内容，只有出现问题了才会查资料。掌握的知识点也比较散，基本上属于现学现卖的状态，甚至有些同学在调优时完全是在碰运气。按道理来讲数据库的设计应该被重视起来，从最终的设计并随着业务数据量的增加随时关注随时优化，可惜现状并不是这样。

鉴于以上原因，笔者抽时间整理了一个专题系列-索引，大概分8章，从底层结构到上层实现说下通用的索引原理。最后阐述下mysql在索引方面的特点，希望可以能把一些同学领进门，达到知其然并知其所以然的状态。因为这块内容比较多也比较专业，笔者水平有限，文中如有错误，欢迎留言反馈，以免带偏，也欢迎大家补充。

一、索引介绍

1.1、索引优化

索引优化时，问题一般集中在以下三种情况：1、没有足够多的索引；2、没有有效的索引；3、索引列的顺序不对。这三种情况也是此系列要讲述的重点。同是提一下一些“误区”，之所以称为误区是因为在没有特定背景的情况下，以下几种经验之谈都不成立。

• 索引层级不要超过5层：这主要是影响磁盘空间，综合现在的硬件水平来看这个问题可以忽略不计，但在一些纯B端应用，因为受服务器数量的影响还是需要考虑下性能成本问题的；
• 单表的索引数不要超过6个：这个是数据库软件的限制，在早期oracle数据库上会有此限制，但mysql等就不会存在这个限制。但读者也要清楚的知道，索引数据过多会影响写的性能；
• 不应该索引不稳定的列：一般认为更新速度为10次/秒的列才称为不稳定列。因为索引行是按索引键的顺序存储的，所以当索引键中有一列被更新时，DBMS可能不得不把相应的行从旧的索引位置移到新的位置来保持这一顺序，所以不稳定的列不建议被索引。在实际开发中，这个问题不可能避免（比如按状态分页查询），但可以把频繁变化的列放在索引最末端来减缓这个问题，这也是后续笔者要着重说明的一块很重要的内容；

1.2、索引结构

表和索引行都存储在页中，一个页的大小一般分为4K和8K，同样的也可以自已设置，页的大小仅仅决定了一个页可以存储多少个索引行、表行以及一共需要多少个页来存储表或者索引。每个页都会预留出一定比例的空闲空间，以满足向其添加新的表行或索引行的需求。缓冲池和I/O活动也都是基于页的，例如一次将一个完整的页进读取到缓冲池，这意味着一次I/O会读入多条记录到缓冲池，也可以认为读入多个页到缓冲池中。

非叶子页通常包含着一个（可能被截断的）键值，以及一个指向下一层及页的指针，该键值是下一层级页中的最大键值。多个索引层级按照这一方式逐层建立，直到只剩下一个页，我们把这个页叫作根页，它位于索引结构的最上层。这种组织方式的索引被称为B树索引（一种平衡树索引），通过这种索引结构时查找任何一条索引记录时需要访问的非叶子页的数量是一样的。

表页

表页的大小限定了表行的最大长度。通常，一个表行必须能够存入一个表页中，一个索引行也必须能够存入一个叶子页中。如果一张表的平均行长大于表页大小的三分之一，那么空间利用率将很糟。例如，一个4KB大小的页只能存储一个长度为2100字节的行。无法利用的空间问题对于索引更为严重。通常情况下，一个表页中只包含一张表中的数据，如果可以保存多张表的数据的话那么查询的效率可能会很快，这种方式叫表聚簇。

聚簇

索引中行的顺序与表中行的顺序是否一致，越一致查询速度越快，当不一致时数据库可通过重组功能保证其顺序的一致性。这个重组功能多数情况下不能由端上的程序控制，即使可以也不建议，原因是不可控。所以聚簇比例一般不需要太考虑，默认就是一致的就好。

索引行

索引行在评估访问路径的时候是一个非常有用的概念，对于一个唯一索引，字段的值从表中复制到索引上，并加上一个指向表中记录的指针（指针存放的页以及它在页中的位置）。通常表页的编号是这个指针的组成部分，对于一个非唯一索引，实际存储方式是一个具体的数据值后带着多个指针，即一个指针数组。索引有列的限制，也有定长和变长的限制，其中定长和变长会影响存储空间。

索引组织表

如果一个表的行不是特别长，那么可以考虑将表上所有的列复制到索引上，以加快select时的执行速度。如此一来，代价是会变得冗余，这个功能是由数据库的参数设置了，如果关闭掉此功能会减少磁盘空间的占用。

游标

游标是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。需要逐条处理数据的时候，比如对结果集当前行做修改，游标显得十分重要。然而尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

1.3、存储引擎

数据库存储引擎是数据库底层软件组织，数据库管理系统(DBMS)使用数据引擎进行创建、查询、 更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同 的存储引擎，还可以 获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引 擎。存储引擎主要有: 1. MyIsam , 2. InnoDB, 3. Memory, 4. Archive, 5. Federated 。

InnoDB(B+树)

InnoDB 底层存储结构为 B+树， B 树的每个节点对应 innodb 的一个 page，page 大小是固定的， 一般设为 16k。其中非叶子节点只有键值，叶子节点包含完成数据。适用场景: 1)经常更新的表，适合处理多重并发的更新请求。 2)支持事务。3)可以从灾难中恢复(通过 bin-log 日志等)。 4)外键约束。只有他支持外键。 5)支持自动增加列属性 auto_increment。

​TokuDB(Fractal Tree-节点带数据)

TokuDB 底层存储结构为 Fractal Tree,Fractal Tree 的结构与 B+树有些类似, 在 Fractal Tree 中，每一个 child 指针除了需要指向一个 child 节点外，还会带有一个 Message Buffer ，这个 Message Buffer 是一个 FIFO 的队列，用来缓存更新操作。 例如，一次插入操作只需要落在某节点的 Message Buffer 就可以马上返回了，并不需要搜索到叶 子节点。这些缓存的更新会在查询时或后台异步合并应用到对应的节点中。

TokuDB 在线添加索引，不影响读写操作, 非常快的写入性能， Fractal-tree 在事务实现上有优 势。 他主要适用于访问频率不高的数据或历史数据归档。

​MyIASM

MyIASM 是 MySQL 默认的引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键， 因此当 INSERT(插入)或 UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。 ISAM 执行读取操作的速度很快，而且不占用大量的内存和存储资源。在设计之初就预想数据组织成有固定长度的记录，按顺序存储的。---ISAM 是一种静态索引结构。 缺点是它不 支持事务处理。

Memory

Memory(也叫 HEAP)堆内存:使用存在内存中的内容来创建表。每个 MEMORY 表只实际对应 一个磁盘文件。MEMORY 类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用 HASH 索引。但是一旦服务关闭，表中的数据就会丢失掉。 Memory 同时支持散列索引和 B 树索 引，B 树索引可以使用部分查询和通配查询，也可以使用<,>和>=等操作符方便数据挖掘，散列索 引相等的比较快但是对于范围的比较慢很多。

1.4、常见的索引规则

• ​选择唯一索引：唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录；
• 为经常需要排序、分组和联合操作的字段建立索引:
• 为常作为查询条件的字段建立索引；
• 限制索引的数目：越多的索引，会使更新表变得很浪费时间；
• 尽量使用数据量少的索引，如果索引的值很长，那么查询的速度会受到影响；
• 尽量使用前缀来索引；如果索引字段的值很长，最好使用值的前缀来索引；
• 删除不再使用或者很少使用的索引；
• 最左前缀匹配原则，非常重要的原则；
• 尽量选择区分度高的列作为索引，区分度的公式是表示字段不重复的比例；
• 索引列不能参与计算，保持列“干净”；
• 带函数的查询不参与索引，尽量的扩展索引，不要新建索引。​

1.5、缓冲池和磁盘I/O

设计缓冲池目的是使用内存来最小化磁盘活动，是提高性能的一种手段，毕竟相对内存从磁盘读取数据的代价还是比较高的，每一个缓冲池可以存放许多页。缓冲池管理器将尽力确保经常使用的数据被保存在池中，以避免一些不必要的磁盘读。如果是从磁盘读取，则还要考虑磁盘的负载等因素，相对前者一般从磁盘加到载缓冲池就要耗时10ms左右，但现在的多数磁盘都会提供一个缓存区，可以认为其耗时可以忽略不计，完整的数据读取过程如下，同时也介绍一个通用的评估公式，在数据库设计时会用到；

• 数据读取源优化级：1、从DBMS缓冲池；2、从磁盘缓存读；3、从磁盘读；
• 数据读类型：1、顺序读；2、辅助顺序读（按游标分块并发）；3、辅助随机读（预读）；
• 数据据扫描类型：1、全表扫描；2、全索引扫描；3、索引片扫描；4、通过聚簇索引扫描

二、SQL处理过程

从数据库接收指令到最终返回数据，多数数据一般都会经过以下三个步骤，如下图所示，图中小的黑体字是每一步的核心功能。

1.1、优化器

优化器可以看会一段处理逻辑，输入是原始的sql语句，输出是访问路径。访问路径中的内容包括使用哪一个索引、索引的访问方式、排序方式等内容，如果缺少访问路径中缺少必要的项，则需要回表查询。对于一个简单的select操作，最重要的决定是索引的选择（或是在多索引访问下的多个索引的选择）及其使用方式（匹配列、过滤列、顺序预读），另外连接的方式及表访问顺序也同样重要，下面是访问路径中一些比较重要的概念：

• 索引片及匹配列：可以理解为where语句后的查询条件，如果数据范围比较窄，会先确定一个区间，这也意味着会减少同步读的性能开销；索引片大小的最重要的度量参数就是需要扫描的索引记录数，即匹配组合谓词的过滤因子与总行数的乘积。一般情况下索引行数等于记录行数，但也有例外的情况，比如有些数据库并不会为null值创建索引，即使是索引列。这个需要特别注意把null做为查询条件时；
• 索引过滤及过滤列；有时候，列可能既存在于where子句中，也存在于索引中，但这个列却不能参与索引片的定义，不过这些列仍然能够减少回表进行同步读的次数，所以这些列仍扮演着很重要的角色。我们称这些列为过滤列。判断为过滤列的方法可以简单表示如下：比如 where a=? and b>? and c=?，这里a是匹配列，b和c是过滤列。这也是多条件查询时，索引只会命中where后的第一个条件的原因；

1. 在where子句中，该列是否至少拥有一个足够简单的谓词（连接符，比如=、like、in等，后续会详细介绍，此处先知道这个概念即可）与之对应？如果有，那么这个列就是匹配列。如果没有，那么这个列及其后面的索引列都是非匹配列，即此条sql语句不会走索引；
2. 如果该谓词是一个范围谓词，那么剩余的索引列都是非匹配列；
3. 对于最后一个匹配列之后的索引列，如果拥有一个足够简单的谓词与其对应，那么该列为过滤列。

1.2、过滤因子

即满足条件的记录行数点所有数据总量的比例，这个值越小越好，它会影响索引片厚度。它主要依赖于数据列的分布情况。比如性别字段，在某些环境中可能man要大于women。所以这里受限于两个因素一个是总记录数，另一个是数据分布情况。另一个可参考的指标是平均过滤因子=1/不同值的个数。最差情况下是实际的过滤因子比平均过滤因子的值更大。

举例来说比如组合查询时：where city=? and name=?。如果city与name没有相关性，那么组合过滤因子就是这两个数相乘。如果有相关性，则一般要比无相关性要低很多。这里的过滤因子需要综合考虑索引片厚度问题。比如where city=? and name=?中city和name都是索引，可以简单认为过滤因子=city*name，如果where city=? and age=?，这里age不是索引列，那么过滤因子=city，索引设计可能需要优化， 因为查询age时需要回表。

假如说数据库的优化只考虑两个因子：表结构和数据量，那么优化器针对的是表结构，过滤因子针对的就是数据量。

1.3、物化结果集

物化结果集意味着执行必要的数据库访问来构建结果集。在最好的情况下，这只需要简单地从数据库缓冲池向应用程序返回一条记录。在最坏的情况下，数据库管理系统需要发起大量的磁盘读取。当一个select语句只查询出一条记录时，优化器必须在select请求被执行的时候就物化结果记录。

而另一方面，当结果集可能有多条记录而需要使用游标时，有两种可供选择的方式：1.数据库管理系统在open cursor时物化整个结果集（或者至少在第一次fetch的时候）。2.每次fetch物化一条记录。这里只需要记住一个知识点即可：对结果集排序意味着，即使只需要提取一条记录，也必须物化整个结果集合，除非order by或group by的字段满足结果集的排序需求，这里有一个点就是设计业务查询时最好不要用多条件排序。