Mysql索引篇——Day01

什么是索引？

当我们在阅读书中的某个内容时，你会选择一页一页去翻还是直接去看目录呢？
答案肯定是去看目录啊，书中的目录充当的就是数据库索引的角色，形象的说索引就是帮助存储引擎快速获取数据的一种数据结构。
那么什么时存储引擎呢？说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据的实现方法。Mysql的存储引擎有MyISAM、InnoDB、Memory，其中 InnoDB 是在 MySQL 5.5 之后成为默认的存储引擎。

MySQL的结构图：

索引的分类

• 按数据结构分：B+树索引、Hash索引、Full-text索引
• 按物理存储分：聚簇索引（主键索引）、二级索引（辅助索引）
• 按字段特性分：主键索引、唯一索引、普通索引、前缀索引
• 按字段个数分：单列索引、联合索引

按数据结构分

从数据结构的角度看，MySQL常见的索引有B+树索引，Hash索引，Full-text索引。
每一种存储引擎支持的索引类型不一定相同。

Innodb在MySQL5.5之后成为MySQL的默认储存引擎，B+树索引类型也是MySQL存储引擎采用最多的索引类型。
在创建表时，Innodb存储引擎会根据不同的场景选择不同的列作为索引：

• 如果有主键，默认会使用主键作为聚簇索引的索引键；
• 如果没有主键，会选择第一个不为null且值唯一的列作为聚簇索引的索引键；
• 在上面两个都没有的情况下，Innodb会自动生成一个隐式自增id作为聚簇索引的索引键。

其他索引都称为辅助索引，也被称为二级索引或者非聚簇索引。创建的主键和二级索引默认使用的是B+树索引。

数据存储时，在B+树中长什么样子呢？
B+树是一种多叉树，叶子节点才存放数据，非叶子节点只存储索引，而且每个节点里的数据是按主键的顺序进行存放的。每一层父节点的索引值都会出现在下层节点的索引值中，因此在叶子节点中，包含了所有索引值的信息，并且每个叶子节点都有两个指针，分别指向下一个叶子节点和上一个叶子节点，形成一个双向链表。

B+树存储千万级的数据只需要3-4层高度就可以满足，这意味着千万级的表查询最多需要3、4次io，所以B+树来说，最大的优势在于查询效率很高。

主键索引的 B+Tree 和二级索引的 B+Tree 区别

• 主键索引的B+树的叶子节点存放的时实际的数据，所有完整的用户记录都存放在主键索引的B+树的叶子节点中；
• 二级索引的B+树叶子节点存放的是主键值，而不是实际的数据。
  
  这里我们将编号设置成二级索引，那么就需要将存储的图就需要将id和编号交换位置，也就是绿色和橙色交换位置，当我们查询编号时，会先检索二级索引找到对应的叶子节点，然后获取主键值，再通过主键索引中的B+树查询到对应的叶子节点，然后获取整行数据，这个过程叫做回表，也就是说，要查两个B+树才能查到数据，当然，如果查询的数据可以再二级索引中找到，那么就不需要再回表查询。这种可以在二级索引中可以查询到的过程就叫做“覆盖索引”，也就是只要一个B+树就可以查到所需要的数据。

为什么Mysql Innodb选择B+树作为索引的数据结构？

1. B+树 vs B树
   B+树只在叶子节点存储数据，而B树的非叶子节点也要存储数据，所以单个B+树的数据量更小，在相同的磁盘io次数下，能查询更多的节点。

2. B+树 vs 二叉树
   对于有N个叶子节点的B+树，其搜索复杂度为O(logdN)，其中d表示节点允许的最大子节点的个数。
   在实际应用中，d是大于100的，这样就保证了，即使数据达到千万级别时候，B+树的高度依然维持在3~4层左右，也就是说以此数据查询只需要做3、4次磁盘io就能达到目标。但是二叉树的子节点只能是两个，意味着搜索的复杂度为O(logN)，这已经比B+树高出不少，因此二叉树检索到的目标数据所经历的磁盘io次数更多。

3. B+树 vs Hash
   Hash在做等值查询的时候效率特别快，时间复杂度为O(1)，但是Hash表不适合做范围查询，它更适合做等值查询，这也是B+树索引要比Hash表索引有着更广泛的使用场景。

按物理存储分

• 主键索引的B+树叶子节点存放的是实际的数据，所有完整的用户记录都存放在主键索引的B+树的叶子节点中；
• 二级索引的B+树存放是主键的值而不是实际的数据。

所以在查询的时候使用了二级索引，如果查询的数据能在二级索引中查询到，那么就不需要回表，这个过程就叫做索引覆盖。如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键的值后，再检索主键索引，这个过程叫做回表。

按字段特性分类

从字段特性的角度来看，索引分为主键索引、唯一索引、普通索引、前缀索引。

按字段个数分类

从字段个数的角度来看，索引分为单列索引、联合索引（复合索引）。

建立在单列上的索引称为单列索引，比如主键索引；
建立在多列上的索引称为联合索引；

什么时候需要创建索引/不需要创建索引？

索引最大的好处就是提高查询的速度，但是索引也有缺点：

1. 需要占用物理空间，数量越大，占用空间越大；
2. 创建索引和维护索引需要耗费时间，这种时间随着数据量的增加而增大；
3. 会降低表的增删改效率，因为每次增删改索引，B+树为了维护索引的有序性，都需要进行动态维护。

什么时候使用索引

• 字段有唯一限制，比如商品编号；
• 经常用于where查询条件字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。
• 经常用于Group By 和 Order By 的字段，这样查询的时候就不需要再去做一次排序，因为我们已经知道建立索引之后再B+树种记录的数据都是排好序的。

什么时候不需要建立索引

• where条件，Group By，Order By里面用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要建立索引的，以为所以索引式会创建额外空间的。
• 字段中存在大量重复字段，不需要创建索引，比如性别字段，如果数据库中的记录分布均匀，那么无论搜索哪个值都会得到一半的数据。这些情况下还不如不用索引，因为MySQL自带的查询优化器，可能会直接进行全表扫描。

优化索引的方法

这里说一下几种常见优化索引的方法：

前缀索引优化；
覆盖索引优化；
主键索引最好是自增的；
防止索引失效；

前缀索引优化

前缀索引顾名思义就是使用某个字段中字符串的前几个字符建立索引，那我们为什么需要使用前缀来建立索引呢？

使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。

不过，前缀索引有一定的局限性，例如：

order by 就无法使用前缀索引；
无法把前缀索引用作覆盖索引；

覆盖索引优化

覆盖索引是指 SQL 中 query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作。

假设我们只需要查询商品的名称、价格，有什么方式可以避免回表呢？

我们可以建立一个联合索引，即「商品ID、名称、价格」作为一个联合索引。如果索引中存在这些数据，查询将不会再次检索主键索引，从而避免回表。

所以，使用覆盖索引的好处就是，不需要查询出包含整行记录的所有信息，也就减少了大量的 I/O 操作。

主键索引最好是自增的

我们在建表的时候，都会默认将主键索引设置为自增的，具体为什么要这样做呢？又什么好处？

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

如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为每次插入一条新记录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高。

如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。

举个例子，假设某个数据页中的数据是1、3、5、9，且数据页满了，现在准备插入一个数据7，则需要把数据页分割为两个数据页：

出现页分裂时，需要将一个页的记录移动到另外一个页，性能会受到影响，同时页空间的利用率下降，造成存储空间的浪费。

而如果记录是顺序插入的，例如插入数据11，则只需开辟新的数据页，也就不会发生页分裂：

因此，在使用 InnoDB 存储引擎时，如果没有特别的业务需求，建议使用自增字段作为主键。

另外，主键字段的长度不要太大，因为主键字段长度越小，意味着二级索引的叶子节点越小（二级索引的叶子节点存放的数据是主键值），这样二级索引占用的空间也就越小。

索引最好设置为 NOT NULL

为了更好的利用索引，索引列要设置为 NOT NULL 约束。有两个原因：

• 第一原因：索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化，因为可为 NULL 的列会使索引、索引统计和值比较都更复杂，比如进行索引统计时，count 会省略值为NULL 的行。

• 第二个原因：NULL 值是一个没意义的值，但是它会占用物理空间，所以会带来的存储空间的问题，因为 InnoDB 存储记录的时候，如果表中存在允许为 NULL 的字段，那么行格式 (opens new window)中至少会用 1 字节空间存储 NULL 值列表，如下图的紫色部分：

防止索引失效

用上了索引并不意味着查询的时候会使用到索引，所以我们心里要清楚有哪些情况会导致索引失效，从而避免写出索引失效的查询语句，否则这样的查询效率是很低的。

这里简单说一下，发生索引失效的情况：

当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
当我们在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效；
联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。
我上面说的是常见的索引失效场景，实际过程中，可能会出现其他的索引失效场景，这时我们就需要查看执行计划，通过执行计划显示的数据判断查询语句是否使用了索引。

如下图，就是一个没有使用索引，并且是一个全表扫描的查询语句。

对于执行计划，参数有：

possible_keys 字段表示可能用到的索引；
key 字段表示实际用的索引，如果这一项为 NULL，说明没有使用索引；
key_len 表示索引的长度；
rows 表示扫描的数据行数。
type 表示数据扫描类型，我们需要重点看这个。
type 字段就是描述了找到所需数据时使用的扫描方式是什么，常见扫描类型的执行效率从低到高的顺序为：

All（全表扫描）；
index（全索引扫描）；
range（索引范围扫描）；
ref（非唯一索引扫描）；
eq_ref（唯一索引扫描）；
const（结果只有一条的主键或唯一索引扫描）。

在这些情况里，all 是最坏的情况，因为采用了全表扫描的方式。index 和 all 差不多，只不过 index 对索引表进行全扫描，这样做的好处是不再需要对数据进行排序，但是开销依然很大。所以，要尽量避免全表扫描和全索引扫描。

range 表示采用了索引范围扫描，一般在 where 子句中使用 < 、>、in、between 等关键词，只检索给定范围的行，属于范围查找。从这一级别开始，索引的作用会越来越明显，因此我们需要尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式。

ref 类型表示采用了非唯一索引，或者是唯一索引的非唯一性前缀，返回数据返回可能是多条。因为虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。

eq_ref 类型是使用主键或唯一索引时产生的访问方式，通常使用在多表联查中。比如，对两张表进行联查，关联条件是两张表的 user_id 相等，且 user_id 是唯一索引，那么使用 EXPLAIN 进行执行计划查看的时候，type 就会显示 eq_ref。

const 类型表示使用了主键或者唯一索引与常量值进行比较，比如 select name from product where id=1。

需要说明的是 const 类型和 eq_ref 都使用了主键或唯一索引，不过这两个类型有所区别，const 是与常量进行比较，查询效率会更快，而 eq_ref 通常用于多表联查中。