本文详细介绍了索引的工作过程、类型、实现方式，包括B+树和哈希索引，并提供了索引优化的建议，强调了最左前缀原则和避免全表扫描的重要性。
索引的那些事儿
1.1 简介
索引是为了加速对表中的数据行的检索而创造的一种分散存储的数据结构，索引（index）也叫做键（key）
1.2 索引的工作过程
在MySQL中，存储引擎用类似书籍目录的实现方法使用索引，其先在索引中查找对应的值，然后根据匹配的索引记录找到对应的数据行，最后将数据结果集返回给客户端。
1.3 索引的类型
在MySQL中，通常我们所指的索引类型，有以下几种：
常规索引（index或key），也叫普通索引，它可以常规地提高查询效率。一张数据表中可以有多个常规索引。常规索引是使用最普遍的索引类型，如果没有明确指明索引的类型，我们所说的索引都是指常规索引。
主键索引（Primary Key），也简称主键。它可以提高查询效率，并提供唯一性约束。一张表中只能有一个主键。被标志为自动增长的字段一定是主键，但主键不一定是自动增长。一般把主键定义在无意义的字段上（如：编号），主键的数据类型最好是数值。
唯一索引（Unique Key），可以提高查询效率，并提供唯一性约束。一张表中可以有多个唯一索引。
全文索引（Full Text），可以提高全文搜索的查询效率，一般使用Sphinx替代。但Sphinx不支持中文检索，Coreseek是支持中文的全文检索引擎，也称作具有中文分词功能的Sphinx。
外键索引（Foreign Key），简称外键，它可以提高查询效率，外键会自动和对应的其他表的主键关联。外键的主要作用是保证记录的一致性和完整性。
注意：只有InnoDB存储引擎的表才支持外键。
联合索引,MySQL中的索引可以以一定顺序引用多列，这种索引叫作联合索引。如User表的name和city加联合索引就是(name,city)，当使用联合索引时会遵循最左前缀原则。
最左前缀原则（了解）
最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。如下：
select * from user where name=xx and city=xx ; ／／可以命中索引
select * from user where name=xx ; // 可以命中索引
select * from user where city=xx ; // 无法命中索引
这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，如 city= xx and name ＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。
由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDER BY子句也遵循此规则。
冗余索引（了解）
冗余索引指的是索引的功能相同，能够命中就肯定能命中 ，那么 就是冗余索引如（name,city ）和（name ）这两个索引就是冗余索引，能够命中后者的查询肯定是能够命中前者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。
1.4 索引的实现
mysql的索引是由存储引擎来实现，不同的存储引擎实现方式不同。
mysql官方文档给出的不同存储引擎对索引的支持
| 存储引擎 | 支持的索引类型 |
|---|---|
| InnoDB | BTREE |
| MyISAM | BTREE |
| MEMORY/HEAP | BTREE,HASH |
| NDB | BTREE, HASH |
InnoDB不支持哈希索引，但提供了自适应哈希索引的功能，会在某些情况下开启这一功能来提高数据的访问速度。
自适应哈希索引（了解）
Innodb存储引擎会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，二级索引成为热数据，建立哈希索引可以带来速度的提升，则：
自适应hash索引功能被打开，经常访问的二级索引数据会自动被生成到hash索引里面去(最近连续被访问三次的数据)，自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快。
1.4.1 B+树 索引
为什么选择B+树而不是其他数据结构？
二叉树：普通的二叉树不是绝对平衡的，它有可能会形成一个链表，这样就失去了二叉树的优势，需要遍历查找，性能查。
平衡二叉树：平衡二叉树如果在数据量很大的情况下，这棵树的高度很可能成千上万，因此它的IO次数也会很频繁，会严重影响性能。每一个节点保存的数据量太小，没有利用好操作磁盘IO的数据交换特性（4K）。
注：操作系统磁盘IO的数据交换一次默认是4KB大小，但是我们的节点里面存储的数据远远小于4KB，即我们进行了一次IO但是没有完全利用这次IO的数据交换大小，造成浪费。
B-树：下图是一个3路的平衡查找树（即一个节点最多可以有3-1=2个元素）,可以看出同样的高度，它比平衡二叉树存储的数据多得多，减少了IO次数，同时每次IO获取的数据也更多，提升了IO效率。
B+树：
B+树(加强版多路平衡查找树)有以下几个特点：
采用闭合区间
非叶子节点不保存数据，只保存关键字和子节点的引用
关键字对应的数据保存在叶子节点中
叶子节点是顺序排列的，并且相邻的节点具有顺序引用的关系
采用B+树的原因：
拥有多路的优势
扫表能力强
磁盘IO能力强
排序能力强
查询能力更稳定
这里解释下为什么说B+Trees的查询能力更稳定：
B-Trees可能扫秒到第一层就返回，也可能扫秒到最后一层才返回。可能很快也可能很慢。
B+Trees每次都要扫面到最后一层，因此速度更加稳定。
B树的查找插入和删除参考： https://blog.csdn.net/endlu/article/details/51720299
存储引擎中的B树实现
Myisam
非聚簇索引，数据和索引分别存储。
索引文件xx.MYI
数据文件xx.MYD
叶子节点保存的是引用地址而非数据
InnoDB
聚簇索引，数据和索引保存在一起
文件xx.ibd
在叶子节点保存对应的所有数据
以主键索引来组织数据，没有主键的话，会帮我们隐式创建主键索引
辅助索引不存地址，存主键，这样便于维护
聚簇索引和非聚簇索引
分析了MySQL的索引结构的实现原理，然后我们来看看具体的存储引擎怎么实现索引结构的，MySQL中最常见的两种存储引擎分别是MyISAM和InnoDB，分别实现了非聚簇索引和聚簇索引。
首先要介绍几个概念，在索引的分类中，我们可以按照索引的键是否为主键来分为“主索引”和“辅助索引”，使用主键键值建立的索引称为“主索引”，其它的称为“辅助索引”。因此主索引只能有一个，辅助索引可以有很多个。
MyISAM——非聚簇索引
MyISAM存储引擎采用的是非聚簇索引，非聚簇索引的主索引和辅助索引几乎是一样的，只是主索引不允许重复，不允许空值，他们的叶子结点的key都存储指向键值对应的数据的物理地址。
非聚簇索引的数据表和索引表是分开存储的。
非聚簇索引中的数据是根据数据的插入顺序保存。因此非聚簇索引更适合单个数据的查询。插入顺序不受键值影响。
只有在MyISAM中才能使用FULLTEXT索引。(mysql5.6以后innoDB也支持全文索引)
InnoDB——聚簇索引
聚簇索引的主索引的叶子结点存储的是键值对应的数据本身，辅助索引的叶子结点存储的是键值对应的数据的主键键值。因此主键的值长度越小越好，类型越简单越好。
聚簇索引的数据和主键索引存储在一起。
聚簇索引的数据是根据主键的顺序保存。因此适合按主键索引的区间查找，可以有更少的磁盘I/O，加快查询速度。但是也是因为这个原因，聚簇索引的插入顺序最好按照主键单调的顺序插入，否则会频繁的引起页分裂，严重影响性能。
在InnoDB中，如果只需要查找索引的列，就尽量不要加入其它的列，这样会提高查询效率。
使用主索引的时候，更适合使用聚簇索引，因为聚簇索引只需要查找一次，而非聚簇索引在查到数据的地址后，还要进行一次I/O查找数据。
因为聚簇辅助索引存储的是主键的键值，因此可以在数据行移动或者页分裂的时候降低成本，因为这时不用维护辅助索引。但是由于主索引存储的是数据本身，因此聚簇索引会占用更多的空间。
聚簇索引在插入新数据的时候比非聚簇索引慢很多，因为插入新数据时需要检测主键是否重复，这需要遍历主索引的所有叶节点，而非聚簇索引的叶节点保存的是数据地址，占用空间少，因此分布集中，查询的时候I/O更少，但聚簇索引的主索引中存储的是数据本身，数据占用空间大，分布范围更大，可能占用好多的扇区，因此需要更多次I/O才能遍历完毕。
聚簇索引的辅助索引的叶子节点的data存储的是主键的值，主索引的叶子节点的data存储的是数据本身，也就是说数据和索引存储在一起，并且索引查询到的地方就是数据（data）本身，那么索引的顺序和数据本身的顺序就是相同的；
而非聚簇索引的主索引和辅助索引的叶子节点的data都是存储的数据的物理地址，也就是说索引和数据并不是存储在一起的，数据的顺序和索引的顺序并没有任何关系，也就是索引顺序与数据物理排列顺序无关。
1.4.2 哈希索引
哈希索引是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。
从上面的图来看，B+树索引和哈希索引的明显区别是：
如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；
从示意图中也能看到，如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；
同理，哈希索引也没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；
哈希索引也不支持多列联合索引的最左匹配规则；
B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。
哈希碰撞：不同的输入得到了同一个哈希值，就发生了"哈希碰撞"（collision），如上图的John Smith与Sandra Dee
1.5建索引的几大规则
-
最左前缀匹配原则，数据库会一直向右匹配直到遇到范围查询就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的；
-
=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序；
-
尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，所以最好别对这些字段创建索引；
-
索引列不能参与计算，保持列“干净”，因为B+树中存的都是数据表中的字段值，但进行检索的时候，需要把所有元素都应用函数才能比较，显然成本太大；
-
尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可；
-
索引要建立在查询频繁的字段上。这是因为，如果这些列很少用到，那么有无索引并不能明显改变查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
1.6索引优化
-
应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描； 例子：select id from t where num is null
-
应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描；
-
应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描；（可以使用 union 来代替 or）
-
in 和 not in 也要慎用，因为in会使系统无法使用索引，而只能直接搜索表中的数据，对于连续的数值，能用 between 就不要用 in；
-
尽量避免在索引过的字符数据中，使用非打头字母搜索，这也使得引擎无法利用索引； 比如：SELECT * FROM T1 WHERE NAME LIKE ‘%L%’
-
应尽量避免在 where 子句中对字段进行表达式操作或者函数操作，都会导致引擎放弃使用索引而进行全表扫描； 比如：SELECT * FROM T1 WHERE SUBSTING(NAME,2,1) = ’L’
参考：
扫一扫
286
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
