MySQL中的索引知识点总结

如果没有索引，MySQL在进行查询的时候是进行全表查询，查找到满足条件的记录就添加到搜索结果集合，速度显然很慢。但是添加了索引，MySQL可以直接在索引列中进行查找，由于索引采用的是B+树，具有稳定的对数时间复杂度，所以能够快速定位到满足条件的数据记录。

一、索引种类

1、普通索引（index）：是最基本的索引，它没有任何限制。

2、唯一索引（unique）：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

3、主键索引（primary key）：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。

4、组合索引（也叫联合索引，复合索引，多列索引）：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀匹配原则。

5、全文索引（fulltext）：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。

二、索引生效条件

假设index（a,b,c）

1、最左前缀匹配：模糊查询时，使用%匹配时：’a%‘会使用索引，’%a‘不会使用索引

2、条件中有or，索引不会生效

3、a and c，a生效，c不生效

4、b and c，都不生效

5、a and b > 5 and c,a和b生效，c不生效

三、索引的缺点

1、降低更新表的速度

2、会占用磁盘空间的索引文件

四、最左前缀匹配原则

最左前缀匹配原则的重要特性：

1、对于联合索引，MySQL 会一直向右匹配直到遇到范围查询（> ， < ，between，like）就停止匹配。

2、= 和 in 可以乱序。

3、如果建立的索引顺序是 （a，b），那么直接采用 where b = 5 这种查询条件是无法利用到索引的。

五、最左前缀匹配原则的成因

MySQL建立联合索引的规则是这样的，首先根据联合索引中最左边的、也就是第一个字段进行排序，在第一个字段排序的基础上，再对联合索引中后面的第二个字段进行排序，依此类推。

第一个字段是绝对有序的，从第二个字段开始是无序的，这就解释了为什么直接使用第二字段进行条件判断用不到索引了

六、聚集索引和非聚集索引

01、聚集（clustered）索引，也叫聚簇索引

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

02、非聚集索引

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

• 辅助索引，也叫非聚集索引。

• 非聚集索引可以分成普通索引，唯一索引，全文索引。

InnoDB和MyISAM都是使用B+树作为索引结构，但是InnoDB的索引为非聚集索引，而MyISAM的索引为聚集索引。

03、区别

1. 两者的根本区别是表记录的排列顺序和与索引的排列顺序是否一致。

2. 聚集索引查询数据速度快，插入数据速度慢；非聚集索引反之。

3. 聚集索引的叶节点就是数据节点，而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块，需要回表进行第二次查找。

4. 聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个。

5. 聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续。

七、索引合并

索引合并访问方法可以在查询中对一个表使用多个索引，对它们同时扫描，并且合并结果。具体来说，就是在SQL语句中使用and、or或者union来连接多个索引条件。但是，索引合并也有两个不足：（1）对于复杂的where子语句，包含了很深的and/or 嵌套，那么不会使用到索引合并技术；（2）不适用于全文索引。

八、覆盖索引

如果一个索引包含（或覆盖）所有需要查询的字段的值，称为“覆盖索引”。即只需扫描索引而无须回表。可以用覆盖索引来解决非聚集索引需要回表进行第二次查找的问题。