Mysql索引

什么是索引？

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则在表中搜索所有的行相比，索引有助于更快地获取信息
通俗的讲，索引就是数据的目录，就像看书一样，假如我想看第三章第四节的内容，如果有目录，我直接翻目录，找到第三章第四节的页码即可。如果没有目录，我就需要将从书的开头开始，一页一页翻，直到翻到第三章第四节的内容。

索引的优点：

通过创建唯一索引，可以保证每一行数据的唯一性
可以大大提高查询速度
可以加速表与表的连接
可以显著的减少查询中分组和排序的时间
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点：

创建索引需要时间，后期创建的索引，创建开销时间与表数据量呈正相关
创建索引时，需要对表加锁，在锁表的同时，可能会影响到其他的数据操作
索引需要磁盘的空间进行存储，如果针对单表创建了大量的索引，可能比数据文件更快达到大小上限
当对表中的数据进行CRUD的时，也会触发索引的维护，而维护索引需要时间，可能会降低数据操作的性能。

索引设计的原则
不应该：

索引不是越多越好。索引太多，维护索引需要时间，同时索引也需要占用磁盘资源
频繁更新的数据，不宜建索引。 数据频繁更新，触发索引频频维护，降低写速度
数据量小的表没必要建立索引。数据量过小，建索引等于多此一举，还增加了操作复杂度

应该：

重复率小的列建议生成索引。因为重复数据少，索引树查询更有效率
数据具有唯一性，建议生成唯一性索引。在数据库的层面，保证数据正确性
频繁group by、order by的列建议生成索引。可以大幅提高分组和排序效率
经常用于查询条件的字段建议生成索引。通过索引查询，速度更快

MySQL索引的分类
我们根据对以列属性生成的索引大致分为两类：

• 单列索引
  以该表的单个列，生成的索引树，就称为该表的单列索引
• 组合索引
  以该表的多个列组合，一起生成的索引树，就称为该表的组合索引

然后单列索引又有具体细的划分：

• 主键索引
  以该表主键生成的索引树，就称为该表的主键索引
• 唯一索引
  以该表唯一列生成的索引树，就称为该表的唯一索引
• 普通索引
  以该表的普通列（非主键，非唯一列）生成的索引树，就称为该表的普通索引
• 全文索引
  全文索引通常使用大文本对象的列去构建索引，索引底层实现是FULLINDEX

最左前缀匹配原则

在MySQL建立组合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从组合索引的最左边开始匹配

聚簇索引

• 聚簇索引又称聚集索引。我们将根据键值对数据库表中的数据进行排序存储，并将相关的信息聚簇在一起索引就叫聚簇索引(该顺序是物理上连续的存储空间的顺序)
• 针对MySQL而言，聚簇索引只存在于InnoDB中，再具体些，如果有主键，一般指代的是InnoDB每个表的主键索引

聚簇索引的特性

• 聚簇索引定义了数据存储在表中的顺序，该表的数据有且仅以一种方式排序。因此，每个表只能有一个聚簇索引。在RDBMS中，在存在主键的情况下，主键索引就是该聚簇索引
• 聚簇索引其中一个大特征就是将索引和数据存储在同一个文件中，既叶子结点不仅保存键的信息，还保存了位于同一行其他列的信息，简而言之，聚簇索引的叶子结点保存的是一个完整行记录数据
• 同时我们也能知道聚簇索引是一种有序索引，它的具体实现可以是稠密索引，也可以是稀疏索引

那些索引属于聚簇索引？

InnoDB和MyISAM之间，只有InnoDB支持聚簇索引
若一张表存在主键，则以该主键列生聚簇索引树
若一张表没有主键，则MySQL会找到该表的第一个唯一非空列的索引作为聚簇索引
如以上条件皆不满足，InnoDB会在内部生成一个名为GEN_CLUST_INDEX隐式聚簇索引。该索引是基于一个名为DB_ROW_ID的隐藏字段，通常称之隐式主键。

聚簇索引的优点

• 聚簇索引可以将相关的数据紧密的关联起来，存储在相邻的连续物理空间，利于范围查询，比如将相关的数据存放在一个叶子结点上，既一个结点的多个关键字对应的数据都存储在一个数据页中，范围查询时，磁盘一次Load出即可，降低IO操作次数，比如针对MAX, MIN, COUNT等聚集函数都有很好的作用。
• 聚簇索引将数据和索引存储在同一个数据文件。 既聚簇索引的叶子结点不仅存放键的信息，还存储相关其他列的完全数据。当查询走聚簇索引，不需要中间人跳转，直接就可以获得目的数据，查询效率更快

聚簇索引的缺点

• 依赖于有序的数据: 因为聚簇索引是顺序存储的，如果多次的插入操作是以非顺序的方式执行，那么最终聚簇索引需要不断的维护这个顺序，这是需要一定性能消耗的。
• 更新代价大: 当聚簇索引中的主键发生更改时，可能需要重新维护顺序，迫使物理空间的交换，所以聚簇索引需要更长的时间来更新记录
• 回表: 支持聚簇索引的存储引擎的辅助键索引的查询结果只是一个中间结果，还需要通过中间结果到聚簇索引上二次查询，操作相对繁琐

非聚簇索引的优点

• 因为一张表只能有一个聚簇索引，而非聚簇索引则可以有多个。所以非聚簇索引允许我们对一张表建立多个索引，提高数据库的查询性能
• 非聚簇索引占用空间小，因为叶子结点不存储真实数据，所以非聚簇索引相比聚簇索引更小。
• 在部分查询中，可以利用覆盖索引的特性，加快查询速度，直接从辅助键索引中获得想要的数据，而不需要做二次查询。
• 非聚簇索引只需要一次遍历，便可得到数据地址。支持聚簇索引的存储引擎会导致其辅助键索引查询的结果只是一个中间结果，还需要通过该中间结果在聚簇索引再遍历一次。而不支持聚簇索引的存储引擎，只有非聚簇索引，非聚簇索引只需要一次遍历，即可得到真实数据的地址

非聚簇索引的缺点

• 非聚集索引只能按逻辑顺序存储数据，并不允许以物理空间连续的方式对数据行进行顺序存储。既非聚簇索引一个叶子结点内部的所有关键字仅仅是逻辑顺序的维护。一个结点对应真实数据在数据文件中可能并非按连续物理空间存储的。 相对聚簇索引的查询，IO次数可能更多，查询性能更低
• 相比聚簇索引，范围查询更慢，因为聚簇索引的范围查询可以让磁盘一次load出整个结点的数据线性遍历。虽然非聚簇索引的同叶子结点之间的关键字也是逻辑顺序存储，也可以线性遍历，但每线性遍历一个关键字都需要中间再跳转到另一个地方(InnoDB下的聚簇索引)遍历或(MyISAM下的数据文件)访问 。这个中间过程实际都是不同的IO操作，可能触发磁盘不同盘块的数据读取。所以本质还是会造成大量的IO操作。
• 每当聚簇索引的主键值更新时，可能会触发非聚簇索引的更新，因为非聚簇索引的叶子结点可能存放的是主键信息（比如InnoDB）
• 每当数据文件中的数据发生更新时, 也可能会触发非聚簇索引的更新，因为可能会导致非聚簇索引叶子结点的数据地址发生改变（比如MyISAM）
• 回表
• 也依赖于有序的数据

为什么InnoDB存储引擎一定要有聚簇索引呢？

因为如果我们的SQL条件是一个非主键列的数据，那么在底层索引查询中，很可能需要跨树查询，既两次查询。
既InnoDB的辅助键索引的叶子结点并不存储行数据，而条件值对应的该主键值。然后我们需要根据辅助键索引查询到的主键值，再去聚簇索引中查询所以非主键索引包含两次查找，一次是查找次级索引自身，然后能再查找主键

非聚簇索引
什么是非聚簇索引？

非聚簇索引将数据存储在一个位置，将索引存储在另一个位置，索引包含指向该数据位置的指针。这样的一个索引就是非聚簇索引，一个表中可以包含多个非聚簇索引

非聚簇索引的特征

非聚簇索引是一个与聚簇索引想向的概念。针对MySQL而言，可以说InnoDB的辅助键索引，以及MyISAM的主、辅索引都是非聚簇索引
非聚簇索引只存储键与指针，不存储数据，所以非聚簇索引的叶子结点仅保存数据的地址(MyISAM)或是其主键信息(InnoDB)。
使用非聚簇索引进行查询，最终会定位到叶子结点，得到数据地址或主键信息，。然后还要根据获得的地址或主键信息进一步定位到数据，通常作为中间人的作用。

说白了，InnoDB下的辅助键索引和MyISAM下的主、辅键索引都属于非聚簇索引，仅仅只有InnoDB下的主键索引（唯一非空列索引…之后细节忽视）才属于聚簇索引

稠密索引

• 稠密索引的真实数据是按顺序储存的
• 为每一个键都创建一个索引记录
• 每个索引记录都包含键本身和指向实际数据的指针
• 因为每个键都有索引，所以可以直接通过索引就找到目的键对应的数据
  

稀疏索引

• 稀疏索引的真实数据是按顺序存储的
• 只为部分的键创建索引记录
• 当在稀疏索引中查找某个目的键时，通常会通过索引，先找到小于或等于目的键的其他键的数据项，既通过索引找到比目的键值要小的数据项（如果目的键有索引，就直接找到目的键的数据）。然后在数据项按顺序遍历(线性)，直到找到目的键的数据记录。
  

稀释索引怎么找目的数据？

要找字段值为1003的数据，就要先找到小于或等于1003的键，比如1001。
通过1001的索引，我们找到1001所在的行数据，然后线性向下遍历两次，我们就找到了1003所在的行数据啦！
这也是稠密和稀疏索引的前提，就是有序

稠密索引和稀疏索引的优缺点

• 相对某列键而言，稠密索引对每个数据都建有索引，要查询起来，直接快速。但是因为要为每个数据都建立对应的索引，所以需要比较大的空间资源
• 而稀疏索引因为只针对部分数据建立索引，所以空间资源占用小，但是查询效率相对比较慢

覆盖索引
什么是覆盖索引？

在InnoDB中，只需要从辅助键索引中就可以查询到最终想要的数据结果，而不需要再从聚簇索引中二次查询。这么的一个技术手段，我们就称之为覆盖索引。
覆盖索引在MySQL中，仅仅是针对InnoDB存储引擎而言的。准确的说，是针对聚簇索引和非聚簇索引共存的情况下才能起作用的。
覆盖索引并不是一种索引类型，而是一种技术手段。

举个应用覆盖索引的粟子!

在InnoDB存储引擎的表中：
比如我们在某个表建立了一个普通组合索引(col1,col2,col3)，由三个列组成。那么我们的select col1,col2,col3 from table where col1 = xxx;语句肯定会有覆盖索引的技术加持。只进行了一次(col1,col2,col3)辅助键索引，我们就可以得到(col1,col2,col3)三列数据的结果，自然也就不需要再拿到相关数据的主键，再跑到聚簇索引二次查询
比如我们在某个表，以col1字段建议一个普通索引，那么我们的select col1 from table where col1 = xxx;, 也会得到覆盖索引的技术加持。

哈希索引
MySQL支持哈希索引，但是局限于部分的存储引擎，既MEMORY/HEAP和NDB。而常见的InnoDB和MyISAM则并不支持。

• 虽然InnoDB不支持哈希索引，但是它依然有曲线救国的手段，就是支持一种伪哈希索引的方式，变相支持哈希索引。
• 这样的伪哈希索引，我们叫它为自适应哈希索引。但这个自适应哈希索引并不是由我们人为控制建立的。而InnoDB存储引擎引擎自动优化创建，不受人为干预的
• 但是我们可以通过参考来确认InnoDB是否开启自适应哈希索引模式

什么是自适应哈希索引？

• 什么是哈希表，相信我们大家都知道，通过O(1)的时间复杂度，我们就可以查询到想要的数据，但是需要付出O(n)的空间复杂度代价。这也是InnoDB不支持哈希索引的原因之一
• 那么什么是自适应哈希索引呢？说白了，它也是哈希索引，但是它不为表中的所有数据都建立索引。而是有选择性的为一些热点数据建立哈希索引。
  既Innodb存储引擎会监控对某表的辅助键索引查找情况，如果发现某辅助键索引被频繁访问，既代表某些关键字是热数据，于是这些数据则会被放入哈希索引中，由此让特定频繁被访问的热点数据可以享受到哈希索引O(1)的速度。
• 至此，我们可以知道InnoDB并不支持我们自建哈希索引，但是在某些情况下哈希索引的效率的确很高，于是InnoDB自己就为热点数据维护一套哈希索引，因为这套哈希索引并不是为所有数据建立的，而是由InnoDB动态监控，自行维护的，为部分热点数据优化速度而生。所以又名自适应哈希索引，以示区别。既自适应哈希索引是存储引擎自身的优化手段，并非提供出来给用户使用的索引类型。

InnoDB的表数据存放在哪

• 我们知道，MySQL下的索引根据以列属性进行分类，可以分为主键索引和辅助键索引。在InnoDB下，主键索引采用的是聚簇索引，辅助键索引采用的是非聚簇索引。

• 主键索引的叶子结点的每个关键字对应的数据，存放的都是完整的行数据
  辅助键索引的叶子结点的每个关键字对应的数据，存放的都是主键信息

InnoDB的数据实际存储在哪？

我们知道InnoDB的主键索引就是聚簇索引，聚簇索引的叶子结点存放的都是完整的数据。这就很容易的推断出，一张表的主键索引就已经一字不差的存放了整张表的所有数据。既该索引文件也是该表的直接数据存储文件
既InnoDB下，一张表的所有数据都是存放在该表的聚簇索引叶子结点中的。

索引是越建越多，越好吗？

• 假设我们一个表的字段很多，但是实际行记录很少。如果我们为每个列都生成一个索引，这很容易就导致该表的索引文件远大于行记录数据本身，这就造成了一定的资源浪费，在占据大量磁盘资源的基础上，还因为行数据量太小，索引根本起不来作用。
• 建立的索引越多，说明需要维护的索引树越多。每当新增，删除一个行记录，或是更新单行或多行的列数据，都需要对涉及的索引树进行维护。而维护的过程也是需要有性能消耗的，在涉及索引过多的情况下，每次的数据库写操作都需要耗费大量的时间， 这就大大降低了数据库写的性能

所以我们要注意索引原则中的三大不应该:

索引不是越多越好。 索引太多，维护索引需要时间，同时索引也需要占用磁盘资源
频繁更新的数据，不宜建索引。 数据频繁更新，触发索引频频维护，降低写速度
数据量小的表没必要建立索引。 数据量过小，建索引等于多此一举，还增加了操作复杂度
不经常使用的列不要建立索引。

索引失效的场景

• 模糊搜索，左模糊或全模糊都会导致索引失效，比如’%key’和’%key%‘。但是右模糊是可以利用索引的，比如’key%’
• 隐式类型转换，比如select * from where name = xxx , name是字符串类型，但是没有加引号，所以是由MySQL隐式转换的，所以会让索引失效
• 当语句中带有or的时候，比如select * from table where name=‘snailmann’ or age=20, 此时OR 有⼀边的条件字段没有索引时，索引失效
• 不符合联合索引的最左前缀匹配， (A,B,C)的联合索引，你只where了C或B或只有B,C
• 在 where 当中索引列参加了运算，索引失效
• 在where当中索引列使⽤了函数，索引失效

const

SELECT * from single_table WHERE id = 1438

通过主键或者唯一二级索引列与常数的等值比较来定位一条记录是非常快的，这种方法叫const （常数级的，代价可以忽略的）。
如果主键或唯一二级索引由多个列构成，则只有在索引列中的每一个列都与常数进行比较时，这个const方法才有效。
对于唯一二级索引列来说，在查询列为null时，如下：

SELECT * from single_table WHERE key2 IS Null

因为唯一二级索引列并不限制null值的数量，所以上述语句可能访问到多条记录。也就是说上面这个语句不可以使用const方法。

ref

SELECT * from single_table WHERE key1 = 'abc'

此时对应的扫描区间为[‘abc’, ‘abc’]。（abc可能有多个）
这种搜索条件为二级索引列与常数进行等值比较，形成的单点扫描区间，这种方法为ref。

• 在二级索引列允许存储NULL值时，无论是普通的二级索引还是唯一二级索引，他们的索引列并不限制NULL值的数量，所以在执行包含“key is null”的命令时，最多只能使用ref访问方法，而不能使用const方法。
• 对于索引列中包含多个列的二级索引来说，只要最左边连续的列是与常数进行等值比较，就可以采用ref方法。
• 如果索引列最左边连续的列不全部是等值比较的话，他的访问方法就不是ref。如下:
  SELECT * from single_table WHERE key_part1 = 'abc' AND key_part2 > ''dfs
  这里索引列是（key_part1, key_part2, key_part3）

ref_or_null
查询某个二级索引列时，也查询该列中值为null的记录。

SELECT * from single_table WHERE key1 = 'abc' AND key1 IS Null

range
使用索引执行查询时，对应的扫描区间为若干个单点扫描区间或者范围扫描区间的方法称为range。
（仅包含一个单点扫描区间或者范围扫描区间不能称为range，（-无穷，+无穷）也不是range）。

index

SELECT key_part1, key_part2, key_part3 from single_table WHERE key_part2 = 'abc'

由于key_part2不是联合索引key_part最左侧的索引列，所以无法形成合适的范围区间。但是它符合以下条件：

• 他的查询列表只有key_part1, key_part2, key_part3，而索引idx_key_part又包含这三个列；
• 搜索条件只有key_part2 = ‘abc’，这个列也包含在idx_key_part中。

也就是说可以直接遍历idx_key_part的所有二级索引记录。针对每一个二级索引记录都判断key_part2 = 'abc’是否成立。
由于二级索引记录比聚簇索引小得多，而且这个过程也不需要回表，所以快。这种就是index方法。
另外：当通过全表扫描对使用InnoDB存储引擎的表执行查询，如果添加了ORDER BY主键的语句，这也是index方法。

all
全表扫描

索引条件下推
索引下推是 MySQL 5.6 版本中提供的一项索引优化功能，可以在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。
适用条件

• 需要整表扫描的情况。比如：range, ref, eq_ref, ref_or_null 。适用于InnoDB 引擎和 MyISAM 引擎的查询。（5.6版本不适用分区表查询，5.7版本后可以用于分区表查询）。
• 对于InnDB引擎只适用于二级索引，因为InnDB的聚簇索引会将整行数据读到InnDB的缓冲区，这样一来索引条件下推的主要目的减少IO次数就失去了意义。因为数据已经在内存中了，不再需要去读取了。
• 引用子查询的条件不能下推。
• 调用存储过程的条件不能下推，存储引擎无法调用位于MySQL服务器中的存储过程。
• 触发条件不能下推。

创建索引的注意事项

1. 选择合适的字段创建索引：
   不为 NULL 的字段 ： 索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
   被频繁查询的字段 ： 我们创建索引的字段应该是查询操作非常频繁的字段。
   被作为条件查询的字段 ： 被作为 WHERE 条件查询的字段，应该被考虑建立索引。
   频繁需要排序的字段 ： 索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
   被经常频繁用于连接的字段 ： 经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

2. 被频繁更新的字段应该慎重建立索引。
   虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

3. 尽可能的考虑建立联合索引而不是单列索引。
   因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

4. 注意避免冗余索引 。
   冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

5. 考虑在字符串类型的字段上使用前缀索引代替普通索引。
   前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引

使用索引的一些建议

• 对于中到大型表索引都是非常有效的，但是特大型表的话维护开销会很大，不适合建索引
• 避免 where 子句中对字段施加函数，这会造成无法命中索引。
• 在使用 InnoDB 时使用与业务无关的自增主键作为主键，即使用逻辑主键，而不要使用业务主键。
• 删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗 MySQL 5.7 可以通过查询 sys 库的 schema_unused_indexes 视图来查询哪些索引从未被使用
• 在使用 limit offset 查询缓慢时，可以借助索引来提高性能