索引基础知识总结

定义

　　一句话总结，索引是一个排好序的用于快速查找的数据结构。这句话说明了索引的三个特点，第一个是有序的，已经将索引列数据排好序了；第二个是快速查找，这就意味着使用索引可以快速定位到符合条件的数据；第三个是一个数据结构。我们平时使用 SQL 语句查询数据时，比如执行 select * from student where name = "张三" ;那么它会去存储数据的磁盘上一行一行进行 name 字段的判断，如果符合就返回，不符合就跳过，这样在数据量较大时查询效率就很低，所以索引的使用就可以在数据量很大时快速定位到数据。因为索引也是需要占用磁盘空间的，所以如果要查的数据量并不是很大时就没有必要使用索引，否则效率会没有提升甚至降低，而且还浪费了磁盘空间。

 

结构

　　索引共有三种数据结构，Hash、B树，B+ 树。其中 B+ 树是默认的，也是使用最广泛的。

Hash

　　hash 结构就是以哈希表的方式进行存储。

优点：数据量小时，发生 hash 冲突的次数很少，查找效率是非常高，因为 hash 冲突次数少大部分数据都存储在哈希表的数组下标上，并且如果是链地址法解决哈希冲突的话产生的链表长度也不会太长，而数组的数据因为内存连续所以查询效率自然就高。

缺点：1、数据量大时，hash 冲突的次数就变多，形成链表的长度也会变长，而链表遍历是低效的，所以查找数据就会很慢；2、因为 hash 表是没有顺序的，所以不能进行一些范围查询例如 >，< 等,也不能进行 like 模糊查询。

绝大多数情况使用的索引都是 B+ 树或 B 树结构的，所以开头说索引的定义是排好序的快速查询结构就排除了 Hash 结构的索引。

 

B树

B 树是多叉树的一种，相比于二叉树它的特点是每个父节点可能包含多个子节点，而它的数据也是存储在各个节点上。每个节点的左子结点小于当前节点所携带数据的值，右子节点大于当前节点的值。每次查询从根节点出发进行判断。

　　优点：有序，查询高效，尤其是靠近根节点的数据比如上图中的 20 ，40会很快查到。

　　缺点：遍历效率低，遍历时每次都需要从根节点出发查找每个值所在的位置。

 

B+树（默认）

 B+树相比于 B 树改善了遍历效率，它是将数据统一存储在叶子节点上，并且对于相邻的叶子节点以链表的形式连接，这样在遍历时就不需要每次都从根节点开始。

优点：遍历速度快，查询速度快。 

缺点：单个数据查询时效率不一定有 B 树高。

 

 

不同存储引擎下的实现

上面说得是索引的基本结构，因为一般都是使用 B+ 树实现的，所以这里也用 B+ 来讲解在 MyISAM 与 InnoDB 中的不同实现。

MyISAM

在 MyISAM 中，主键索引和非主键索引的结构是一样，叶子节点存储的都是最终行数据所在的磁盘地址，当找到符合的条件地址后需要去磁盘地址上找到对应的行数据。

索引和数据存储：索引和数据是用两种文件格式存储的，数据使用的是 .MYD结尾的文件存储的，而索引是使用 .MYI结尾的文件存储的（.frm 是表结构文件）。  数据文件和配图转自【MySQL】索引原理（二）：B+Tree索引的实现，MyISAM和InnoDB 。

不足：5.6之前的 MyISAM 相比于 InnoDB 多了全文索引，同时内部维护了一个计数器，对于 conut 函数可以直接读取（不含where等条件筛选时）。模糊查询效率会比 InnoDB 高。但是其不支持事务，没有行级锁，同时 5.6之后 InnoDB 也引入了全文索引，所以 MyISAM 慢慢被遗弃了。

 

InnoDB（默认）

在 InnoDB 中将索引分为聚簇索引与非聚簇索引。

聚簇索引默认是该表主键对应的索引；如果该表没有主键，那么会从该表中选择一个唯一非空的列作为聚簇索引；而如果表中也没有唯一非空的列，那么就会 InnoDB 就会自己维护一个聚簇索引，但是这样遍历的效率就会低一些。

非聚簇索引指得就是除聚簇索引外的其他索引。

 

之所以这样分是因为聚簇索引的结构与非聚簇索引的结构不同，非聚簇索引结构的 B+树叶子结点存储的是主键值和当前的索引列的值。而聚簇索引结构的 B+树叶子结点存储的就是对应的行数据。如果使用聚簇索引进行查询只需要在一个索引结构中进行查询就可以得到这一行的所有数据，所以在 InnoDB 中使用主键查找数据效率非常高；而如果使用非聚簇索引进行查询那么在查出主键值后还需要去主键所在的索引结构中进行回表查询（没有发生索引覆盖时），也就是比直接通过聚簇索引查询多了一次查询。

索引和数据存储：InnoDB 索引的数据因为直接存储在聚簇索引所在的 B+ 树中，所以只有一种格式的文件，文件以 .ibd 结尾。

Innodb 结构的主键一般推荐设置为自增的主键，因为如果设置为 UUID 的，那么每次插入都可能会在原有的数据之间，因为 InnoDB 管理数据的最小磁盘单位是数据页，如果要插入的数据页没有合适位置插入，就会将此条记录以及后面的记录全部另开一个数据页存储，造成 "页分裂"，从而影响了效率；而如果是自增，那么只会在原有数据后面新增，不会造成这样的问题。

但如果业务字段有唯一字段且字段长度也不长那么也可以将其设置成主键，因为如果抛开所有的业务列而单独创建一列作为主键列，那么每次使用业务字段查询时都必须进行一次回表查询，换句话说就是会使 "覆盖索引" 失效，而使用热点字段作为主键可以减少一次回表查询。

另外需要注意的是数据页是磁盘记录的最小操作单位，所以聚簇索引所在的 B+树叶子结点存储的并不是对应的行记录，而是行记录所在的页，上面那张图只是用来理解聚簇索引与非聚簇索引的关系的，聚簇索引的结构应该如下

 在找到叶子结点后还需要去数据页中进一步进行查询，直到找到最终的行记录。如果对数据页不清楚可以查看 博客。

 

种类

1、主键索引（Primary key）。一张表的主键默认就是一个主键索引。

2、唯一索引（Unique Index）。某一列的值是唯一的可以使用唯一索引。与普通索引的区别是在查询数据时，唯一索引查到数据后就可以直接返回，而普通索引需要继续向后查直到查到的值不等于目标值。添加数据时唯一索引需要判断是否已存在相同值的记录，而普通索引不需要。

3、普通索引（Index（列名））。

4、全文索引（Fulltext Index）。5.6 之前是 MyISAM 专属的，5.6 开始 InnoDB 也引入了，适用于在数据量大的场景进行模糊查询。

5、联合索引（Index（列名，列名））。为多列设置一个索引，这些列在筛选时都可以用到这个索引，提升效率，同时减小了索引的占用空间。联合索引的使用必须遵守最左匹配原则，否则相应的列索引就会失效。

最左匹配原则：在查询时会先从联合索引的最左列开始匹配，如果某列没有出现或者出现了范围查询，那么后面的列都不会用到索引。

注意：1、这里的范围查询是指 >、<（这种不含有 = 情况的），如果是 like，那么只要它的开头不是 "%"、"_"  通配符就和 in、<=、>= 一样符合最左匹配原则，而如果是通配符开头的，那么它就和 not in 一样失效，后面的列也会失效。比如联合索引(name ,age)，where name like '张%' and age =20；这个name 与age 都会用到索引，而如果是 where name like '%三' and age =20；那么 name 与 age 都不会用到索引。

　　    2、and 条件的列可以改变位置，比如对于联合索引(name,age)，那么where age>20 and name like '张%'； 在mysql 解析时会将 name 优化到 age 条件的前面保持最左匹配原则，age 与 name 都会用到索引。

6、前缀索引（Index（列名(截取长度)））。其实就是前面几种索引的一种特殊变种。对于某个字段长度过长，同时前几位的数据段就可以代表当前值的重复水平。那么就可以创建前缀索引来减小该索引的占用空间。需要注意的是非主键列的前缀索引会使覆盖索引失效。因为前缀索引存储的值只是最终列数据的部分值，所以在匹配到对应的记录后还需要去比较完整值，而如果是非主键列就需要回表查询才能验证。也正因为要验证，所以前缀索引截取的长度应该使每个数据的唯一率和原数据一致来避免过多的回表验证。这个长度的获取可以通过这种方式：首先查询 select count(*)/count(distinct 列名)，然后不断调整参数 select count(*)/count(distinct 列名(截取长度))，当结果无限接近于前面的值就取该长度作为前缀索引的截取字段。

延伸问题：对于前面几位重复率高的列创建索引如何优化？如身份证号。

1、将该列的值全部倒序存储，因为后面几位的数据是随机的，所以唯一性比较好。而在查询时将结果倒序展示。优点：占用空间小、CPU 消耗低（reverse 函数复杂度比 crc32 函数低）

select field_list from t where id_card = reverse('input_id_card_string');

2、增加字符串哈希值列并创建索引，也就是将身份证号通过 crc32 函数转成一个哈希值，然后存在新增的创建索引的列中，在查询时优先判断哈希列，如果相等再匹配原列。优点：执行效率更稳定

select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

 

使用

操作

创建：1、create  索引类型  索引名  on  表名(列名)，如 create  index  idx_name  on  student(name) 　　　　2、Alter  table  表名  add  索引类型  索引名(列名)，如 alter  table  student  add  index  idx_name(name)

删除：1、drop  index  索引名  on  表名，如 drop  index  idx_name  on  student　　　　　　2、Alter  table  表名  drop  index  索引名，如 alter  table  student  drop  index  idx_name

对于主键只能先移除 "主键身份" 再删除。 alter  table  表名  modify  主键列名   int;　然后执行删除操作： Alter  table  表名  drop  index  primary key;。

 

使用场景

因为索引会占用空间，所以需要满足特定条件才推荐使用索引。

1、数据量大。数据量小不会提升效率甚至会降低效率。

2、读取操作多，修改少。每次对索引列数据的修改都需要去修改索引对应的结构，如果一张表的修改次数大于读取次数，那么使用索引反倒会使程序总体的效率降低。

3、重复数据少，列数据种类多。如果对 sex 性别列创建索引，那么索引的作用也不大甚至会降低效率。

 

失效场景

1、对索引列筛选值是该列所包含的大部分值时，就会优化成全表查询，跳过索引来保证更好的效率。比如说 age 字段都是大于0 的，而在 where 筛选条件为 age>0，那么这个筛选就会被优化成全表查询。这一条很容易被忽视，也经常出现，比如使用 in、>、>=、<、<= 等等都可能出现有时用到索引，换一个检索条件值就用不到。这是因为mysql 底层进行了诸多优化，很多情况都会判断为使用全表扫描效率更高，所以会优化成全表扫描。

2、对索引列进行 is null、is not null 判断可能会失效。当该列本身就设置为非空的，那么由于1的作用is not null直接就被优化成全表查询，索引失效，而is null 则会生效；如果列可以为空，那么is null 和 is not null 还是会根据该列的null 值多少来判断，如果null 值几乎没有，那么和前面的情况一样，如果几乎都是 null 值，那么is null 就会优化成全表扫描，而 is not null 则会用到索引，而两者数据差不多则两者都会用到索引。

3、对索引列使用函数或表达式。这个失效并不是一定就会没有用上索引，如果可以不用回表那么优化器可能还会选择使用索引。

4、模糊查询 like 时不以具体值为开头，而是以 "%"、"_" 通配符开头。

5、对索引列使用  !=、not in 。索引中存储的是最终的数据或地址，如果是查询不等于某个值必然是需要进行全表扫描的。

6、索引列进行了自动类型转换。比如 phone 字段类型是 varchar，而在查询的时候没有使用单引号， 那么执行后也能得到最终结果，但是却没有走索引。这是因为在数据比较时会调用函数将两者类型变成一致才能比较。

7、使用 or 如果左右条件有一方没有用到索引时那么用到索引的那一方索引也会失效。

8、对于联合索引没有遵守最左匹配原则。

9、参与查询的两张表的字符集不同。原因和第6条一样，也是因为需要调用函数使数据字符集一致才能比较

　　优化：1、将两表的字符集转成一致
　　　　    2、如果表处于上线状态且数据量特别大。可以修改SQL，主动对驱动表的数据类型进行转换，避免默认将被驱动表进行类型转换。
　　　　　 如：
　　　　　　select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;
　　　　　可以优化成
　　　　　　select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;
　　　　　tradelog 是驱动表，通过它计算出的 tradeid 相当于一个常量，对常量使用表达式不会影响其他索引列，所以会生效

 

所谓的全表扫描指的是按主键顺序一条一条扫描。