想搞懂MySQL索引？光看这篇肯定不够^ ^

什么是索引？

索引是一个排序的数据结构，根据索引字段对记录进行排序存储，并存储记录对应的磁盘地址，以协助快速查询、更新数据库表中记录，索引的实现通常使用Hash或者B+树。

索引在MySQL中是一个数据库对象，以文件的形式存在，属于表空间的一个组成部分，所以它是要占据物理空间的，也需要数据库去维护。

索引的类型

主键索引（自动创建索引）：数据列不允许重复，不允许为NULL，一个表只能有一个主键。只要设置了某个字段为主键，那么MySQL会自动为其建立一个索引。

唯一索引（手动创建索引）：数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。

普通索引（手动创建索引）：基本的索引类型，没有唯一性的限制，允许为NULL值。

联合索引（手动创建索引）：即将几个字段组合在一起作为索引，遵循最左匹配原则。

如何建立索引

主键索引

因为主键索引是自动建立的，所以主键索引的建立和删除就是主键的建立和删除。

使用 ALTER 命令添加和删除主键

主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下：

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

你也可以使用 ALTER 命令删除主键：

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

删除主键时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。

唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

创建索引

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))  
 
);  

普通索引

创建索引

这是最基本的索引，它没有任何限制。它有以下几种创建方式：

CREATE INDEX indexName ON tableName(colName)
CREATE INDEX indexName ON tableName(colName1,colName2...)//创建联合索引

修改表结构(添加索引)

ALTER table tableName ADD INDEX indexName(columnName)

创建表的时候直接指定

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

删除索引的语法

DROP INDEX [indexName] ON mytable; 

使用ALTER 命令添加和删除索引

有四种方式来添加数据表的索引：

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):

  该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

• ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。

• ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list): 该语句指定了索引为 FULLTEXT ，用于全文索引。

为什么要使用索引

当我们从数据库查询数据时，例如"SELECT id FROM table WHERE age = 18;"，有两种方式：

• 全表扫描：即查询整张表中所有的记录，将所有记录从磁盘中一一取出，和查询条件（age=18）进行一一对比，然后返回满足条件的记录，这样做会消耗大量时间，并造成大量磁盘I/O操作
• 索引查询：假如给age这个字段建立了索引，那么数据库就会建立一个索引，把age字段和记录的引用指针（指向磁盘地址）存在索引里面。在查找的时候，就会直接去索引里面找，因为索引使用的都是查询高效的数据结构，所以查询效率会比全表扫描快很多

索引的底层实现

InnoDB中使用的索引算法只有两种，Hash和B树（底层实际是B+树）。

Hash索引

Hash索引的底层就是Hash表，主要就是通过Hash算法将索引字段转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。

Hash索引因为使用Hash表作为底层数据结构，所以只适合等值查询的情况（例如id=20），而无法进行范围查询。

B+树

B+树的一个节点可以存储多个索引，树高就比较低，一般可以维持在3层，这样只进行3次IO操作就可以得到数据；非叶子节点只存索引，叶子节点存索引跟行数据引用指针；叶子结点之间由指针连接，相当于一个链表，所以非常适合区间查找数据。

B+树有以下性质：

• n棵子tree的节点包含n个索引，不用来保存数据而是保存数据的索引
• 所有的叶子结点中包含了全部字段的信息，及指向含这些字段记录的指针，且叶子结点本身依字段的大小自小而大顺序链接
• B+ 树中，数据对象的插入和删除仅在叶节点上进行
• B+树有2个头指针，一个是树的根节点，一个是最小关键码的叶节点

Hash索引和B+树索引有什么区别或者说优劣呢?

Hash索引进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。B树索引底层实现是B+树，对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。

那么可以看出他们有以下的不同：

• Hash索引进行等值查询更快(一般情况下)，但是却无法进行范围查询

• Hash索引不支持使用索引进行排序

• Hash索引不支持模糊查询以及多列索引的最左前缀匹配。原理也是因为hash函数的不可预测。AAAA和AAAAB的索引没有相关性

• Hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件(聚簇索引，覆盖索引等)的时候可以只通过索引完成查询

• Hash索引虽然在等值查询上较快，但是不稳定。性能不可预测，当某个键值存在大量重复的时候，发生哈希碰撞，此时效率可能极差。而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低

因此，在大多数情况下，直接选择B+树索引可以获得稳定且较好的查询速度。而不需要使用Hash索引。

B树和B+树的区别

• 在B树中，你可以将键和值存放在内部节点和叶子节点；但在B+树中，内部节点都是键，没有值，叶子节点同时存放键和值。
• B+树的叶子节点由指针连成一条链，增强了区间查找的性能，而B树的叶子节点各自独立。

为什么MyIASM和InnoDB创建的表索引默认都是B+树？

我们可以想见，有序数据结构且仅用于查找的话，肯定是树比较方便。那么先假设底层用的是查找二叉树，节点是一种key-value类型， key保存数据的值，value保存该数据的引用指针。

但是二叉树有个严重的问题是，假如建立索引的列里面的值是正序的，二叉树就会退化成链表，查询效率从O(logN)降低到了O(N)，跟没建索引一样，而且还额外带来了维护索引的磁盘开销。

那我们来考虑一下红黑树。红黑树固然因为其变色和自旋操作可以防止退化为链表，但是随着数据量的增大，树高也在增大， 可能也得查个几十次，这就意味着需要从磁盘中加载几十次的数据到内存中，众所周知磁盘操作是非常非常非常慢的，用作数据库这种需要高速查找数据的场景肯定裂开。

那有没有只要查3-5次就能找到的数据结构呢？有，B树（B-Tree）。

红黑树一个节点只能存一个数据，并且最多只能有两个子节点，而B树它一个节点可以存多个数据，一个节点可以有多个子节点，一般会把树高维持在3-5层，这样只要做3-5次IO操作就能查到数据。索引和行记录的指针是存放在一起的，所以找到索引就可以找到数据，因此如果把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率。这种特性使得B树在特定数据重复多次查询的场景中更加高效。

而MySQL底层改进了B树，使用B+树来建立索引。B+树有以下特点：

• 非叶子节点不存储数据，只存储索引，每个节点可以放更多的索引
• 叶子节点包含所有的索引字段
• 叶子节点用指针连接，提高区间访问的性能

我们来谈谈为什么用B+树而不用B树。

首先，B树它在所有节点都存了数据，数据需要一定的存储空间，而整个节点的存储空间是固定的（InnoDB为16KB），那么就导致存储的索引变少了，相应的树高就变高了，所需要的IO操作次数就变多了，十分影响性能；而B+树在非叶子节点不存数据，所有的空间都用来放索引，这样每个节点的索引就变多了，树高就能稳定控制在3层。

其次，B树由于所有节点都存储了数据，那么它在区间查找时（即查找某个范围之内的数据所有数据）就需要进行一次中序遍历；而B+树只有叶节点才有数据，并且叶节点的数据是双向链表的数据结构，定位到首个数据就可以通过链表快速遍历获取需要的区间数据。

再者，因为B+树最后都要定位到叶子节点，时间消耗很稳定，都是做2次IO获取到叶节点再通过链表遍历获取数据，比B树稳定。

联合索引及最左匹配原则

最左匹配原则：最左优先，以联合索引最左边的索引字段为起点任何连续的索引都能匹配上，同时遇到范围查询(>、<、between、like)就会停止匹配。

例如：b = 2 如果建立(a,b)顺序的索引，是匹配不到(a,b)索引的；但是如果查询条件是a = 1 and b = 2或者a=1(又或者是b = 2 and b = 1)就可以，因为优化器会自动调整a,b的顺序。再比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，因为c字段是一个范围查询，它之后的字段会停止匹配。如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整

我们上面讲到了联合索引的创建，现在假如我们创建了一个联合索引：

CREATE INDEX index ON table(name,age);

那么：

SELECT * FROM student WHERE name='小明';
SELECT * FROM student WHERE age=18;
SELECT * FROM student WHERE age=1+ AND name='小明';

这三句会不会走索引呢？

根据最左匹配原则，很多人都会回答，第一句和第三句会走索引，第二句不会。但事实上三句都会走索引！那么底层究竟是如何运行的呢？

我们单拿出第二句和第三句使用EXPLAIN进行解析：

EXPLAIN SELECT * FROM student WHERE age=1;
EXPLAIN SELECT * FROM student WHERE age=1 AND name='小明';

我们会看到它们返回的执行计划中，type字段的值分别是index和ref。

index：mysql会对该索引进行扫描，只要是索引，或者某个复合索引的一部分，mysql都可能会采用index类型的方式扫描。但是要注意，这个时候这个索引是没有排序的，这就意味着它即便走了索引效率一样很低。

ref：这个就是我们正常所理解的高速查询的索引，因为只有有序的索引才会使用ref去查询。

不是说索引底层是有序的数据结构吗？为什么还有索引是无序的呢？

单列索引确实是有序的，但是联合索引就不一定了。比如上面的name和age索引，InnoDB首先会根据name来排序，然后在name相等的时候才会根据age来排序。

那么第二句，只用age去查询数据的话，虽然会走索引，但是因为数据相对于age是无序的，所以它只能一条一条从头查到尾，跟没走索引一样。

第三句的话，因为name是等值查询（name=‘小明’），所以此时数据相对于age来说就是有序的，这样就走了ref高效索引查询。

而age=1 AND name='小明’和name=‘小明’ AND age=1这两句执行的结果是一样的，但是效率不同，所以优化器就会计算它们的效率，选择效率最高的一条来执行。

聚簇索引和非聚簇索引

聚簇索引

索引和行数据引用指针放在同一个节点的索引叫做聚簇索引，根据索引可以直接找到行数据。

聚簇索引一般是主键索引，如果没有主键，则把第一个NOT NULL的唯一索引作为聚簇索引，如果也没有，就采用Innodb存储引擎为每行数据内置的6字节ROWID作为聚集索引。

每张表只有一个聚集索引，因为聚集索引在精确查找和范围查找方面良好的性能表现（相比于普通索引和全表扫描），聚集索引就显得弥足珍贵，聚集索引选择还是要慎重的（一般不会让没有语义的自增id充当聚集索引）。

非聚簇索引

索引和主键放在同一个节点的索引叫做非聚簇索引，需要根据索引找到主键，再根据主键索引找到行数据（回表）。

非聚簇索引一定要回表吗？

并不是，因为非聚簇索引的节点中存储了主键和索引字段，假设只要查询主键或索引字段，就不用回表：

SELECT id,age,name FROM table WHERE age = 20;

假设id为主键，age和name为联合索引，不需要回表就可以查询到结果。为age和name建立索引叫做索引覆盖，假如业务中经常需要查询某些字段，应该把它们加入索引，可以提高查询效率，同时避免回表。

索引设计的原则

1. 最适合建立索引的字段应该是经常出现在WHERE后面的字段、或是连接子句中的字段
2. 考虑某字段中中值的分布，字段的基数越大，索引效果越好。例如id每个都不同，很容易区分各行，适合建索引；而性别只有两个，不论搜索哪个值都会得出大约一半的数据，没必要建索引
3. 使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度（即前缀索引，语法：index(field(10))，使用字段值的前10个字符建立索引，默认是使用字段的全部内容建立索引），这样能够节省大量索引空间。
4. 不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引字段越多，这个时间就会越长。所以只保持需要的索引有利于查询即可
5. 注意最左前缀匹配原则，组合索引非常重要的原则
6. 更新频繁字段不适合创建索引
7. 尽量扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
8. 有外键的数据字段一定要建立索引
9. 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引
10. 对于定义为text、image和bit的数据类型的列不要建立索引

索引也不是万能的

索引需要空间来存储，也需要定期维护， 每当有记录在表中增减或索引列被修改时，索引本身也会被修改。 这意味着每条记录的INSERT，DELETE，UPDATE将为此多付出几次磁盘I/O。 因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。

使用索引查询不一定能提高查询性能，索引范围查询(INDEX RANGE SCAN)适用于两种情况:

• 基于一个范围的检索，一般查询返回结果集小于表中记录数的30%
• 基于非唯一性索引的检索

什么情况下索引会失效？

• 在where子句中进行null值判断的话会导致引擎放弃索引而产生全表扫描

  SELECT id FROM table WHERE name is null;
  

• 在where子句中使用!= ,<=>这样的符号会导致引擎放弃索引而产生全表扫描

  SELECT id FROM table WHERE name != '小明';
  

• 在where子句中使用or来连接条件,如果两个字段中有一个没有索引的话,引擎会放弃索引而产生全表扫描

  SELECT id FROM table WHERE name = '小明' or age = 18;
  

• 在where子句中=的左边使用表达式操作或者函数操作

  SELECT id FROM table WHERE age / 2 = 18;
  SELECT id FROM table WHERE SUBSTRING(name,1,2) = '小明';
  

• 可以使用like，但是只能后模糊查询

  SELECT id FROM table WHERE name LIKE '%小明%';//索引失效，全表扫描
  SELECT id FROM table WHERE name LIKE '小明%';//索引成功
  

• 联合查询的最左匹配原则

• 避免在where子句中使用in，使用in分为两种情况

• • 走索引的

SELECT id FROM table WHERE name IN ('小明');//等价于name = '小明'

• • 不走索引的

SELECT id FROM table WHERE num IN ('小明，小红');

百万级别或以上的数据如何删除

在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。

1. 所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）
2. 然后删除其中无用数据（此过程需要不到两分钟）
3. 删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。
4. 与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。