索引的种类

索引的种类

索引有哪几种类型？

1. 主键索引：数据列不允许重复，不允许为NULL，一个表只有一个主键。
2. 唯一索引：数据列不允许重复，允许为NULL，一个表允许多个列创建唯一索引。
3. 普通索引：基本的索引类型，没有唯一性的限制，允许为NULL值。
4. 全文索引：是目前搜索引擎使用的一种关键技术，对文本的内容进行分词、搜索。
5. 覆盖索引：查询列要被创建的索引覆盖，不必读取数据行。
6. 组合索引：多列值组成一个索引，用于组合搜索，效率大于索引合并。

主键索引和非主键索引的区别？

比如这个表(对k建立非主键索引)：

主键索引和非主键索引的示意图如下：

其中Rx表示的是整行数据。而k索引里面存的是ID。

所以主要的区别就是主键索引的叶子节点存放的是整行数据，而非主键索引的叶子结点存放的是主键的值。

另外，主键索引也成为聚簇索引，而非主键索引被成为非聚簇索引或者二级索引。

另外一个区别是，非主键索引查找到数据之后，需要回表查询数据。

聚簇索引和非聚簇索引的区别？

聚簇索引：索引项的排序方式和表中的数据记录排序方式一直的索引。也就是说聚簇索引的顺序就是数据的物理存储顺序。他会根据聚簇索引键的顺序来存储表中的数据，即对标的数据按照索引键的顺序进行排序，然后存储到磁盘上，因为数据在物理存放时只有一种排列方式，所以一个表只能有一个聚簇索引。(找到索引了就找到需要的数据，那么这个索引就是聚簇索引，所以主键就是聚簇索引。)

非聚簇索引：索引数据和物理存储顺序不同。(索引的存储和数据的存储是分开的，也就是说找到了索引，但是没有找到数据，需要根据索引上的值在回表查询，所以非聚簇索引也叫辅助索引。)

什么是覆盖索引？

select * from T where k between 3 and 5

​ 比如查找k的范围为3-5，这时候因为k有索引，所以先根据k的索引找到对应的ID，然后再去主键索引上面搜索具体的信息，有回表。

select ID from T where k between 3 and 5

这个时候因为ID值已经在K索引树上了，因为可以直接提供查询结果，不需要回表。

使用覆盖索引的好处？

不需要回表 由于覆盖索引可以减少树的搜索次数，显著提升查询性能。

什么是最左前缀原则？

对于该表，如果按照name字段建立索引的话，采用B+树的话，大概索引如下：

如果进行模糊查找的话，查找name以张开头的所有人的ID，select ID from table where name like '张%'.

由于在B+树结构的索引中，叶子节点是一个有序链表，所以当我们定位到ID为100的张一之后，可以直接向右遍历所有张开头的人，直到条件不满足为止。这种定位到最左边，然后向右遍历寻找的方式就是最左前缀原则。

比如：一个(a,b,c)的组合索引：

1. 通过a,b条件查询能不能使用或命中这个索引？-----能
2. 通过b,c条件查询能不能使用或命中这个索引？-----不能
3. 原因：索引文件具有B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

主键索引和唯一索引的区别？

创建主键的时候会自动给主键添加索引，且该索引为唯一索引，即主键一定是唯一索引，但是一张表里可以有多个唯一索引，所以唯一索引不一定是主键。

普通索引和唯一索引的区别？

普通索引：普通索引的任务是加快对数据的访问速度。因此应该只为那些经常出现在查询条件where或者排序条件order by中的数据列创建索引。

唯一索引：普通索引可以包含重复的值。而唯一索引里面不可以包含重复的值，在为这个数据列创建索引的时候就应该用关键字unique把他定义为唯一索引。

定义成为唯一索引的好处：

1. 简化了MySQL对这个索引的管理工作，这个索引也因此比较有效率
2. MySQL在有新纪录插入数据表的时候，自动检查新纪录的这个字段是否已经在某个记录上出现过，如果是的话，MySQL会拒绝插入的那条新纪录。也就是说唯一索引可以保证数据记录的唯一性。

唯一复合索引？

紧接着上个例子，如果创建一个唯一复合索引的话：

CREATE UNIQUE INDEX uni_user_info_pass ON user_info(name,pass);

执行插入操作：

insert into user_info value(600003. "chen","password",23);

如果数据库中已经存在name和pass一样的数据的话，就会拒绝插入。

换句话说，如果是复合型索引，并且是唯一的，只有当唯一复合索引的字段都相同的时候会进行唯一约束的作用。

什么是组合索引(复合索引)？组合索引和普通索引的区别？

当建立复合索引index(c1,c2,c3)就相当于创建了以下三个索引：

index(c1)

index(c1, c2);

index(c1, c2, c3)顺序无关。

索引可以覆盖多个数据列，如像INDEX(columnA，columnB)索引。这种索引的特点是MySQL可以有选择地使用一个这样的索引。如果查询操作只需要用到columnA数据列上的一个索引，就可以使用复合索引INDEX(columnA,columnB)。不过，这种用法仅适用于在复合索引中排列在前的数据列组合。比如说，INDEX(A，B，C)可以当做A或(A,B)的索引来使用，但不能当做B、C或(B，C)的索引来使用。

最左前缀原则：
顾名思义是最左优先，以最左边的为起点任何连续的索引都能匹配上，
注：如果第一个字段是范围查询需要单独建一个索引
注：在创建联合索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。这样的话扩展性较好，比如 userid 经常需要作为查询条件，而 mobile 不常常用，则需要把 userid 放在联合索引的第一位置，即最左边

同时存在联合索引和单列索引(字段有重复的)，这个时候查询mysql会怎么用索引呢？

这个涉及到mysql本身的查询优化器策略了，当一个表有多条索引可走时, Mysql 根据查询语句的成本来选择走哪条索引；

有人说where查询是按照从左到右的顺序，所以筛选力度大的条件尽量放前面。网上百度过，很多都是这种说法，但是据我研究，mysql执行优化器会对其进行优化，当不考虑索引时，where条件顺序对效率没有影响，真正有影响的是是否用到了索引！如果使用联合索引，那么where条件也要尽量根据联合索引的顺序来，如果不按照顺序来，索引也同样会用到，但是在执行前，SQL优化器也会将条件调整为联合索引的顺序，既然可以直接避免这种情况，就没必要再让SQL优化器去处理，毕竟处理也是有开销的。

联合索引本质：
当创建(a,b,c)联合索引时，相当于创建了(a)单列索引，(a,b)联合索引以及(a,b,c)联合索引
想要索引生效的话,只能使用 a和a,b和a,b,c三种组合；当然，我们上面测试过，a,c组合也可以，但实际上只用到了a的索引，c并没有用到！
注：这个可以结合上边的 通俗理解 来思考！

其他知识点：
1、需要加索引的字段，要在where条件中
2、数据量少的字段不需要加索引；因为建索引有一定开销，如果数据量小则没必要建索引(速度反而慢)
3、如果where条件中是OR关系，联合索引不起作用
4、联合索引比对每个列分别建索引更有优势，因为索引建立得越多就越占磁盘空间，在更新数据的时候速度会更慢。另外建立多列索引时，顺序也是需要注意的，应该将严格的索引放在前面，这样筛选的力度会更大，效率更高。

什么是前缀索引？

前缀索引是面对这样的一个场景，当需要索引一个比较长的字符比如BLOB或者text的时候，这样会使索引很慢。为了解决这种查找比较慢的话，一般有两种做法：第一种是改用hash索引。另外一种就是使用字符串的前几个字符作为索引即前缀索引。

主要思路就是选择足够长的前缀以保证较高的选择性，同时又不能太长(造成空间浪费)

所谓选择性，是指不重复的索引数量除以总记录数，范围是(0,1]，唯一索引之所以查询效率高，是因为它的选择性等于1。

缺点：MySQL中无法使用前缀索引进行ORDER BY和GROUP BY，也无法用来进行覆盖扫描

最左前缀原则和最左匹配原则

最左前缀原则：最左优先，以最左边的为起点，任何连续的索引都能匹配上。

1. 如果第一个字段是范围查询需要单独建一个索引
2. 在创建多列索引时，根据业务需求，where子句中最频繁的一列放在最左侧。

比如：

当创建(a,b,c)复合索引时，想要索引生效的话，只能使用 a和ab、ac和abc三种组合！

实例：以下是常见的几个查询：

mysql>SELECT `a`,`b`,`c` FROM A WHERE `a`='a1' ; //索引生效
mysql>SELECT `a`,`b`,`c` FROM A WHERE `b`='b2' AND `c`='c2'; //索引失效
mysql>SELECT `a`,`b`,`c` FROM A WHERE `a`='a3' AND `c`='c3'; //索引生效，实际上值使用了索引a

扩展：想要索引最大化的使用需要至少建几个索引？

答：需要建立复合索引：bc

1. 建立联合索引测试：

联合索引的顺序为：sex，age，name

SELECT * FROM user where sex="3"; #使用索引
SELECT * FROM user where age="4"; #未使用索引
SELECT * FROM user where name="2"; #未使用索引
SELECT * FROM user where sex="2" and age="3"; #使用索引
SELECT * FROM user where sex="2" and age="3" and name="4"; #使用索引
SELECT * FROM user where age="3" and name="4";  #未使用索引
SELECT * FROM user where sex="2" and name="4";  #使用索引

#这个在3.2最后边解释，为什么
explain SELECT * FROM index_demo.user where age="2" and sex="3"; #使用索引

值得注意的是，where sex=“2” and name=“4” 这个相当于只有sex使用到了索引的，

2. 如果索引字段有两个 如果索引有两个字段：sex，age

explain SELECT * FROM index_demo.user where sex="3"; #使用索引
explain SELECT * FROM index_demo.user where age="4"; #未使用索引
explain SELECT * FROM index_demo.user where sex="2" and age="3"; #使用索引
explain SELECT * FROM index_demo.user where age="3" and sex="4";  #使用索引

---

参考

mysql聚簇索引 和主键的区别_一分钟明白MySQL聚簇索引和非聚簇索引

MySQL主键索引(聚簇索引)与非主键索引(二级索引)

索引(index)_普通索引、唯一索引和复合索引.索引查询

普通索引和联合索引区别

【mysql索引】之前缀索引

什么是最左前缀原则？什么是最左匹配原则?