mysql 索引

什么时索引

索引是为了加速对表中数据行的检索而创建的一种分散存储的数据结构；
一个表的单个索引最多可有16个字段。最大索引长度是256个字节；
一般在产生慢查询时，要加索引。

查询索引

show index from user; -- 查询user表的索引
show keys from user; -- 查询user表的关联键

索引的优缺点

• 优点

1、索引能极大的减少存储引擎需要扫描的数据量；
2、索引可以把随机IO变成顺序IO；
3、索引可以帮助我们在进行分组、排序等操作时，避免使用临时表；
4、索引用来快速地寻找那些具有特定值的记录，所有MySQL索引都以B+树的形式保存。
5、如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。

• 缺点

1、索引表占据物理空间
2、数据表中的数据增加、修改、删除的同时需要去动态维护索引表，降低了数据的维护速度

索引失效

1、在where后使用or，导致索引失效（尽量少用or）;
2、使用like ，like查询是以%开头，以%结尾不会失效;
		使用 match(字段名..) against(‘关键字’) 代替  name like ‘%asd%’
		match('name ') against(‘asd’) <==>name like ‘%asd%’
		select * from articles where match(name,age) against ( 'database')
3、不符合最左原则;
4、如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引;
5、 使用in导致索引失效;
6、使用mysql内部函数导致索引失效,可能会导致索引失效;
7、如果MySQL估计使用索引比全表扫描更慢，则不使用索引;

---

索引原理

磁盘IO与预读

前面提到了访问磁盘，那么这里先简单介绍一下磁盘IO和预读，磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为**寻道时间、旋转延迟、传输时间**三个部分：
	1、寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；
	2、旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；
	3、传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。

当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内；
因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到；
每一次IO读取的数据我们称之为一页(page)；
具体一页有多大数据跟操作系统有关，一般为4k或8k;
也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。

为什么不使用二叉树

• 它太深了

数据处的（高）深度决定着他的IO操作次数，IO操作耗时大

• 它太小了

每一个磁盘块（节点/页）保存的数据量太小了
没有很好的利用操作磁盘IO的数据交换特性，
也没有利用好磁盘IO的预读能力（空间局部性原理），从而带来频繁的IO操作

B树

b树 每个节点都存储数据点，叶子节点无链表；
b+树 只有叶子存储数据 并且有链表。

B+树

一般关系型数据库使用B+树来做索引，NoSQL数据库用哈希来做索引。

数据库选用B+树的最主要原因：

B+树还有一个最大的好处，方便扫库；

B树必须 用中序遍历 的方法按序扫库，
而B+树直接从叶子结点挨个扫一遍就完了；
	
B+树支持range-query非常方便（b+tree 数据节点存在指针，所以范围查找不需要多次查找。），
而B树不支持。这是数据库选用B+树的最主要原因。

如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO；在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

b+树性质

为什么每个数据项，即索引字段要尽量的小?

通过上面的分析，我们知道IO次数取决于b+数的高度h；
假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N；
当数据量N一定的情况下，m越大，h越小；
而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，
如果数据项占的空间越小，数据项的数量越多，树的高度越低。
这就是为什么每个数据项，即索引字段要尽量的小

比如int占4字节，要比bigint8字节少一半。
这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，
一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。
当数据项等于1时将会退化成线性表。

索引的最左匹配特性

当b+树的数据项是复合的数据结构，
比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，
比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，
如果name相同再依次比较age和sex，最后得到检索的数据；
但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，
因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。
比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，
但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 
这个是非常重要的性质，即索引的最左匹配特性。

---

索引的分类

1、唯一索引：

表上一个字段或者多个字段的组合建立的索引，这些字段组合起来能够确定唯一，允许存在空值（只允许存在一条空值）

2、非唯一索引：

表上一个字段或者多个字段的组合建立的索引，可以重复，不需要唯一

3、主键索引：

（主索引）根据主键pk_clolum（length）建立索引，不允许重复，不允许空值；

4、聚合索引：

表中记录的物理顺序与键值的索引顺序相同

5、非聚合索引：

表中记录的物理顺序与键值的索引顺序无关

6、全文索引：

在某个字段设置全文索引后，根据特定语法查找满足条件的字段;

1、全文搜索在 MySQL 中是一个 FULLTEXT 类型索引。FULLTEXT 索引在 MySQL 5.6 版本之后支持 InnoDB，而之前的版本只支持 MyISAM 表。
2、目前只有char、varchar，text 列上可以创建全文索引。
3、 like “value%” 可以使用索引，但是对于 like “%value%” 这样的方式，执行全表查询
4、使用方法是 match(字段名…) against(‘关键字’) 代替 name like ‘%asd%’

match('name ') against(‘asd’) <==>name like ‘%asd%’
select * from articles where match(name,age) against ( ‘database’)

7、普通索引：

	用表中的普通列构建的索引，没有任何限制

8、组合索引：

	用多个列组合 构建的索引，但是在使用过程中有诸多规则，遵循最左前缀原则，顺序至关重要

9、Hash索引（Memory存储引擎）:

	是通过索引列的值计算出hashCode,之后在相应的物理位置存取索引列的值，由于hashCode的唯一性，因此Hash索引不能进行范围查找或者是顺序查找。

---

聚合索引与非聚合索引

聚合索引与非聚合索引是一种存储方式，而不是一种单独的索引类型
两者相同点就是通过B+树结构索引

聚合索引（InnoDB存储引擎需要）

定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引，又称聚集索引，聚簇索引。

聚合索引描述

每张使用 InnoDB 作为存储引擎的表都有一个特殊的索引称为聚集索引，它保存着每一行的数据，
通常，选择B+树作为存储结构。
通常，聚集索引就是主键索引。
为了得到更高效的查询、插入以及其他的数据库操作的性能，
你必须理解 InnoDB 引擎是如何使用聚集索引来优化常见的查找和 DML 操作。
	1、如果你的表定义了一个主键，InnoDB 就使用它作为聚集索引。
		因此，尽可能的为你的表定义一个主键，
		如果实在没有一个数据列是唯一且非空的可以作为主键列，建议添加一个自动递增列作为主键列。
	2、如果你的表没有定义主键，InnoDB 会选择第一个唯一非空索引来作为聚集索引 。

	3、如果你的表既没有主键，又没有合适的唯一索引，
		InnoDB 内部会生成一个隐式聚集索引 —— GEN_CLUST_INDEX，该索引建立在由 rowid 组成的合成列上。
		数据行根据 InnoDB 分配的 rowid 排序，rowid 是一个 6 字节的字段，随着数据插入而单调递增。
		也就是说，数据行根据 rowid 排序实际上是根据插入顺序排序。
		
在InnoDb上会选择自增字段作为主键，是为了维持B+树的分裂特性，顺序添加到当前索引的后续位置，
当达到最大就会分裂产生新的页，也不需要移动原有的顺序。

聚合索引主索引和辅助索引，主索引叶子结点存储键值对应的数据本身，辅助索引叶子结点存储主键键值：
		由于辅助索引存储的是主键键值，因此按照辅助索引搜索的时候需要检索两遍，
		第一遍找到对应的主键，第二遍在主索引到达叶子结点中找到数据。

聚合索引如何提升效率

通过聚集索引来访问一行数据是非常快的，这是因为所有的行数据和索引在同一页上。
如果表特别大，相较于行数据和索引在不同页上存储结构（比如 myisam 引擎），这将大大节省磁盘 I/O 资源。

二级索引和聚集索引如何关联

除了聚集索引外的其他索引类型都属于二级索引。
在 InnoDB 中，二级索引中的每个记录都包含该行的主键列，以及二级索引指定的列；
聚集索引中，InnoDB 通过主键值来查询数据行。

如果主键过长，二级索引就需要更大的空间，因此，使用短的主键列是很有利的。

对于二级索引，叶子节点并不包含行记录的全部数据。
叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签 —— 相应行数据的聚集索引键。

如果在一棵高度为 3 的二级索引树中查找数据，那需要对这颗二级索引树遍历3次找到指定聚集索引键。
如果聚集索引树的高度同样为 3 ，那么还需要对聚集索引树进行 3 次查找，最终找到一个完整的行数据所在的页，
因此一共需要 6 次逻辑 IO 访问以得到最终的一个数据页。

非聚合索引（MyIsam）

定义：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

非聚合索引描述

由于物理顺序与索引顺序不同，因此每一个叶子节点存储的是指向键值对应的数据的物理地址（数据记录的地址）

非聚簇索引的数据表和索引表是分开存储的

获取数据的方式是首先根据B+树获取索引，取出对应数据记录的地址，之后再去读取相应的数据记录

只有在MyISAM中才能使用FULLTEXT索引。(mysql5.6以后innoDB也支持全文索引)

辅助索引的意义：如果给出的查询条件不是主键，此时就使用辅助索引，并且使用辅助索引不需要使用主索引

使用聚合索引的场景：

某列包含小数目的不同值
排序和范围索引（主键递增）

使用非聚合索引的场景：

某列包含大数目的不同值（因为在叶子节点不需要去保存数据，只需要保存地址）
频繁更新的列，因为非聚集索引添加记录时，不会引起数据顺序的重组

---

全文索引

一般不适用mysql的全文索引，而是使用全文检索框架代替
全文检索框架 ： Lucene，Solr , ES(ElasticSearch)

---

组合索引（覆盖索引）

基于多个字段创建的索引就是组合索引。

组合索引规则：

最左原则：
	索引是key index (a,b,c). 可以支持a | a  ,b| a , b , c 3种组合进行查找，但不支持 b,c进行查找。
	当最左侧字段是常量引用时，索引就十分有效。
	
	电话簿中利用姓名查找人，姓和名分别是不同的列，
	知道姓电话簿有用，
	知道姓知道名电话簿有用，
	知道名不知道姓电话簿无用

---

index Key：索引数据范围

下边界

MySQL利用=、>=、> 来确定下边界（first key），
利用最左原则，首先判断第一个索引键值在where条件中是否存在，
如果存在，则判断比较符号，如果为(=,>=)中的一种，加入下边界的界定，然后继续判断下一个索引键，
如果存在且是(>)，则将该键值加入到下边界的界定，停止匹配下一个索引键；如果不存在，直接停止下边界匹配。

上边界

上边界（last key）和下边界（first key）类似，
首先判断是否是否是(=,<=)中的一种，如果是，加入界定，继续下一个索引键值匹配，
如果是(<)，加入界定，停止匹配

---

Index Filter ：用于过滤索引查询范围中不满足查询条件的记录

Index Filter的提取规则：
	同样从索引列的第一列开始，检查其在where条件中是否存在：
	若存在并且where条件仅为 =，则跳过第一列继续检查索引下一列，下一索引列采取与索引第一列同样的提取规则；
	若where条件为 >=、>、<、<= 其中的几种，则跳过索引第一列，将其余where条件中索引相关列全部加入到Index Filter之中；
	若索引第一列的where条件包含 =、>=、>、<、<= 之外的条件，则将此条件以及其余where条件中索引相关列全部加入到Index Filter之中；
	若第一列不包含查询条件，则将所有索引相关条件均加入到Index Filter之中。

以上index Key和Index Filter都是通过索引列来实现的，而Table Filter是针对非索引列

Table Filter ：无法使用索引过滤，使用表过滤

提取规则：所有不属于索引列的查询条件，均归为Table Filter之中。
where索引过程：

---