MySQL索引详解（结合了其他各大佬的文章）

概念

• 索引是 MySQL 数据库中的重要的数据结构之一，它是数据库性能调优技术的基础，常用于实现数据的快速检索。
• 索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一一对应关系的有序表。
• 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

MySQL数据访问方式

在 MySQL 中，通常有以下两种方式访问数据库表的行数据：

• 顺序访问
  顺序访问是在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。
  实现方式简单，但是效率低。
• 索引访问
  索引访问时通过遍历索引来访问表中记录行的方式。使用这种方式的前提是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快捷地查找到数据。索引存储了指定列数据值的指针，根据指定的排序算法对这些指针排序。

我用了一张大小为3.22M大小，有22527行数据的表做了一个简单的测试，number字段被设置为索引，type_number是和number字段同类型的字段，分别以这两个字段为条件查询：



可以看到执行时间相差还是很大的

索引的类型

（1）常规索引（normal）

MySQL中默认的索引类型，没有任何限制。
创建索引：
CREATE INDEX indexName ON mytable(username(length));
如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
添加索引：
ALTER table tableName ADD INDEX indexName(columnName)
创建表的时候直接指定：

CREATE TABLE mytable(  

ID INT NOT NULL,   

username VARCHAR(16) NOT NULL,  

INDEX [indexName] (username(length))  

);  

（2）唯一索引（UNIQUE）

列值必须唯一，但是可以有一个null值。
创建索引：
CREATE UNIQUE INDEX indexName ON mytable(username(length));
添加索引：
ALTER table mytable ADD UNIQUE [indexName] (username(length))
创建表的时候直接指定：

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
UNIQUE [indexName] (username(length))  
 
); 

（3）全文索引（FULLTEXT）

MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

CREATE FULLTEXT INDEX indexName ON mytable(username(length));

（4）主键索引

不允许重复，不允许空值，主键id默认的索引类型。一般创建表的时候设置了主键，则默认给该主键设置主键索引。

（5）组合索引

用多个列组合构建的索引，这多个列中的值不允许有空值，使用最左匹配原则。

删除索引：

DROP INDEX [indexName] ON mytable;

（6）覆盖索引
一个包含查询所需字段的索引称为“覆盖索引”，如select a,b from table1 where c=1;
建立联合索引（a, b, c），这样MySQL只需要通过索引就可以返回查询所需要的数据，而不必在查到索引之后进行回表操作，减少IO，提高了效率。

索引的方式

索引的方式和存储引擎有关，每种存储引擎所支持的索引方式不一定完全相同。MySQL中常用的索引方式分为以下两类：
（1）BTree索引
参考：MySQL索引原理及BTree（B-/+Tree）结构详解
目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构，至于为什么他的效率这么高，请看为什么BTree/B+Tree作为数据库或者文件索引效率很高
不同的存储引擎以不同的方式实现BTree索引，性能也各不相同。
例如：MyISAM使用前缀压缩技术使得索引更小，但InnoDB则按照原始的数据格式存储索引。再如MyISAM通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行（下面的聚簇索引和非聚簇索引再深入讨论）。
BTree分为B-Tree和B+Tree（B-Tree的变种）
在讨论BTree之前，先来看看一种非常简单的索引方式（当然，数据库肯定没有采用这种方式，太过简单无法满足要求，这里只是为了后边的理解做铺垫）
下图中左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在O(log2n)O(log2n)的复杂度内获取到相应数据。

（1.1）B-Tree
为了描述B-Tree，首先定义一条数据记录为一个二元组[key, data]，key为记录的键值，对于不同数据记录，key是互不相同的；data为数据记录除key外的数据。那么B-Tree是满足下列条件的数据结构：
d为大于1的一个正整数，称为B-Tree的度。
h为一个正整数，称为B-Tree的高度。
每个非叶子节点由n-1个key和n个指针组成，其中d<=n<=2d。
每个叶子节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针，叶节点的指针均为null 。
所有叶节点具有相同的深度，等于树高h。
key和指针互相间隔，节点两端是指针。
一个节点中的key从左到右非递减排列。
所有节点组成树结构。
每个指针要么为null，要么指向另外一个节点。
如果某个指针在节点node最左边且不为null，则其指向节点的所有key小于v(key1)v(key1)，其中v(key1)v(key1)为node的第一个key的值。
如果某个指针在节点node最右边且不为null，则其指向节点的所有key大于v(keym)v(keym)，其中v(keym)v(keym)为node的最后一个key的值。
如果某个指针在节点node的左右相邻key分别是keyikeyi和keyi+1keyi+1且不为null，则其指向节点的所有key小于v(keyi+1)v(keyi+1)且大于v(keyi)v(keyi)。
下图是一个d=2的B-Tree示意图：

（1.2）B+Tree
B-Tree有许多变种，其中最常见的是B+Tree，例如MySQL就普遍使用B+Tree实现其索引结构。
与B-Tree相比，B+Tree有以下不同点：
每个节点的指针上限为2d而不是2d+1。
内节点不存储data，只存储key；叶子节点不存储指针。

（1.3）带有顺序访问指针的B+Tree
一般在数据库系统或文件系统中使用的B+Tree结构都在经典B+Tree的基础上进行了优化，增加了顺序访问指针。

在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能，因为存储引擎不再需要进行全表扫描来获取需要的数据。例如上图中如果要查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。

（2）哈希索引
参考：MySQL哈希索引
Hash索引是将索引键通过Hash运算之后，将 Hash运算结果的Hash值和所对应的行指针信息存放于一个Hash表中。
Hash索引结构的特殊，导致其检索效率非常高，索引的检索可以一次定位，不像B-Tree索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以Hash索引的查询效率要远高于BTree索引。
哈希表也为散列表，又直接寻址改进而来。在哈希的方式下，一个元素k处于h(k)中，即利用哈希函数h，根据关键字k计算出槽的位置。函数h将关键字域映射到哈希表T[0…m-1]的槽位上。

上图中哈希函数h有可能将两个不同的关键字映射到相同的位置，这叫做碰撞，在数据库中一般采用链接法来解决。在链接法中，将散列到同一槽位的元素放在一个链表中，如下图所示：

（1）Hash索引仅仅能满足"=",“IN"和”<=>"查询，不能使用范围查询
由于Hash索引比较的是进行Hash运算之后的Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的Hash算法处理之后的Hash值的大小关系并不能保证和Hash运算前完全一样。
（2）Hash索引无法被用来进行数据的排序操作
由于Hash索引中存放的是经过Hash计算之后的Hash值，而且Hash值的大小关系并不一定和Hash运算前的键值完全一样，hash索引并不是按照索引顺序存储的，所以无法利用索引的数据来进行任何排序运算。
（3）Hash索引不能利用部分索引键查询
对于组合索引，Hash索引在计算Hash值的时候是组合索引键合并后再一起计算Hash值，而不是单独计算Hash值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash索引也无法被利用。
（4）Hash索引在任何时候都不能避免表扫描
前面已经知道，Hash索引是将索引键通过Hash运算之后，将 Hash运算结果的Hash值和所对应的行指针信息存放于一个Hash表中，由于不同索引键存在相同Hash值，所以即使取满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。
（5）Hash索引遇到大量Hash值相等的情况后性能并不一定就会比BTree索引高
对于选择性比较低的索引键，如果创建Hash索引，那么将会存在大量记录指针信息存于同一个Hash值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

在Mysql中InnoDB引擎有一个特殊的功能叫做自适应哈希索引，他会在内存中基于B-Tree索引的基础上面创建一个哈希索引，这让B-Tree索引页具备了一些哈希索引的优点。
在《高性能的Mysql》这本树中，作者举了一个自定义哈希索引的列子。假设我们在一个表中大量存储了URL，而且需要根据URL来进行查找。因为URL比较长，这个时候如果我们使用B-Tree索引来，索引会非常的大。解决的办法是在列表中增加一个列，用来存放URL的哈希值，可以通过CRC32对URL进行计算，并存放在列表中，在查找的时候通过CRC32对url进行计算匹配列表中的hash值。

聚簇索引和非聚簇索引

参考：MySQL索引原理及BTree（B-/+Tree）结构详解
MyISAM和InnoDB引擎实现的索引结构不同，MyISAM实现了非聚簇索引，InnoDB实现了聚簇索引。
按照索引的键是否为主键来分为“主索引”和“辅助索引”，使用主键键值建立的索引称为“主索引”，其它的称为“辅助索引”。因此主索引只能有一个，辅助索引可以有很多个。

MyISAM实现的非聚簇索引

• 非聚簇索引顺序与数据物理排列顺序无关。
• 非聚簇索引的主索引和辅助索引几乎是一样的，只是主索引不允许重复，不允许空值，他们的叶子结点的key都存储指向键值对应的数据的物理地址。
• 非聚簇索引的数据表和索引表是分开存储的。

MyISAM引擎使用带有顺序访问指针的B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：

这里设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

InnoDB实现的聚簇索引

• 聚簇索引的顺序就是数据的物理存储顺序。
• 聚簇索引的主索引的叶子结点存储的是键值对应的数据本身，辅助索引的叶子结点存储的是键值对应的数据的主键键值。因此主键的值长度越小越好，类型越简单越好。
• 聚簇索引的数据和主键索引存储在一起。
• 聚簇索引的数据是根据主键的顺序保存。

聚簇索引和非聚簇索引的差异

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。
（1）
第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。**因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），**如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
（2）
第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，以前面的Col3上的一个辅助索引为例，其结构图为：

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

聚簇索引和非聚簇索引小结

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非常单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

索引的使用原则

下文提到的索引是否被使用，可以在查询语句前加explain然后分析结果
（1）最左匹配原则
组合索引中提到过最左匹配原则，即对于组合索引，where后面的条件可以不是组合索引中的每一项，只要查询的条件中用到了最左边的索引列,索引就会被使用。
比如
create index index_a1_a2 on user(a1,a2);
执行
select * from user where a1 = 1
组合索引index_a1_a2会生效
执行
select * from user where a2 = 1
组合索引index_a1_a2不会生效
（2）通配符%不能位于首位
create index index_name on user(name);
这个查询语句中，index_name不会生效

explain select * from user where name like '%张';

这个查询语句中，index_name会生效

explain select * from user where name like '张%';

（3）大文本的索引
如果对大的文本进行搜索,使用全文索引而不是使用like
（4）or条件的索引使用原则
如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及到的索引都不会被用到。也就是or的左右条件都应该是索引列
（5）字符型要用’'
如果是字符型，传入的是数字，必须加上’'把数字引起来索引才会起作用
（6）!=/<>上使用索引无效
从B+Tree的结构上看，是不支持!=索引的。

索引的选取

本部分参考：https://blog.youkuaiyun.com/u012954706/article/details/81241049

• 越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。
• 简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。
• 尽量避免NULL：应该指定列为NOT nuLL,在MySQL中， 含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。

不适合创建索引的场景

• 在查询中很少使用的列，因为对很少使用的列建立索引反而会降低了系统的维护速度和增大了空间需求。
• 对于那 些只有很少数据值的列也不应该增加索引。因为本来结果集合就是相当于全表查询了，所以没有必要。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比 例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
• 对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。或者建立称全文索引。
• 当修改性能操作的表大于检索操作时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

适合创建索引的字段

表的主键、外键必须有索引；外键是唯一的，而且经常会用来查询

• 数据量超过300的表应该有索引
• 经常与其他表进行连接的表，在连接字段上应该建立索引；经常连接查询，需要有索引（MySQL5.6后自动为外键建立索引）
• 经常用到排序的列上，因为索引已经排序
• 经常用在范围内搜索的列上创建索引，因为索引已经排序了，其指定的范围是连续的

索引的优缺点

优点
提高查询速度
缺点
创建和维护索引需要耗费时间
降低更新和写入速度
费磁盘空间