【数据库】涉及索引的问题

索引分类

索引的作用是：排列好次序，使得查询时可以快速找到。

唯一索引/非唯一索引、主键索引（主索引）、聚集索引/非聚集索引、组合索引。

1、唯一索引

是在表上一个或者多个字段组合建立的索引，这个或者这些字段的值组合起来在表中不可以重复。

如下表中，为 学号 建立索引

学号             姓名
-----------------------
001              张三
002              李四

2、非唯一索引

是在表上一个或者多个字段组合建立的索引，这个或者这些字段的值组合起来在表中可以重复，不要求唯一。

如下表中，为Score建立索引，可不唯一：
Score     |     Name
--------------------------
98                  张三
98                  李四
96                  王五

3、主键索引（主索引）

是唯一索引的特定类型。表中创建主键时自动穿件的索引。一个表中只能建立一个主索引。

4、聚集索引（聚簇索引）

表中记录的物理顺序与键值的索引顺序相同。一个表中只能有一个聚集索引。

 

扩展：聚集索引与非聚集索引的区别？分别在什么情况下使用？

聚集索引和非聚集索引的根本区别：是表中记录的物理顺序和索引的排列顺序是否一致。

聚集索引的表中记录的物理顺序与索引的排列顺序一致。

优点是查询速度快，因为一旦具有第一个索引值的记录被找到，具有连续索引值的记录也一定物理的紧跟其后。

缺点是对表进行修改速度较慢，这是为了保持表种的记录的物理顺序与索引顺序一致，而把记录插入到数据也的响应位置，必须在数据页中进行数据重排，降低了执行速度。在插入新纪录时数据文件为了维持B+ Tree的特性而频繁的分裂调整，十分低效。

建议使用聚集索引的场合：

1. 某列包含了小数目的不同值。
2. 排序和范围查找。

非聚集索引的记录的物理顺序和索引的顺序不一致。

其他方面的区别：

1、聚集索引和非聚集索引都才用了B+树的结构，但非聚集索引的叶子层并不与实际的数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针的方式。聚集索引的叶节点就是数据节点，而非聚集索引的叶节点仍然是索引节点。

2、非聚集索引添加记录时，不会引起数据顺序的重组。

         看上去聚簇索引的效率明显要低于非聚簇索引，因为每次使用辅助索引检索都要经过两次B+ 树查找，这不是多此一举吗？聚簇索引的优势在哪？

1. 由于行数据和叶子结点存储在一起，这样主键和行数是一起被载入内存的，找到叶子结点就可以立刻将行数据返回了，如果按照主键ID来组织数据，获得数据更快。
2. 辅助索引使用主键作为“指针”，而不是使用地址值作为指针的好处是，减少了当出现行移动或者数据页分裂时，辅助索引的维护工作，InnoDB在移动行时无需更新辅助索引中的这个“指针”。也就是说行的位置会随着数据库里数据的修改而发生变化，使用聚簇索引就可以保证不管这个主键B+ 数的节点如何变化，辅助索引树都不受影响。

建议使用非聚集索引的场合：

1. 此列包含了大数目的不同值；
2. 频繁更新的列

5、组合索引（联合索引）

基于多个字段而创建的索引就称为组合索引。

 

建立索引的优点：

• 大大加快数据的检索速度
• 创建唯一性索引，保证数据库表中每一行数据的唯一性
• 加速表和表之间的连接
• 在使用分组和排序自居进行数据检索时，可以显著减少查询中分组和排序的时间。

建立索引的缺点：

• 索引需要占用物理空间
• 当对表中的数据进行增加、删除、修改的时候，索引需要去动态的维护，降低了数据的维护速度

 

建立索引的几大原则

1）最左前缀匹配原则：MySQL会一直向右匹配知道遇到范围查询（>、<、between、like)就停止匹配，范围查询会导致组合索引半生效

比如：a = 1 and b = 2 and c > 3 and d = 4，如果建立（a,b,c,d）顺序的索引，c可以用到索引，d是用不到索引的，如果建立（a,b,d,c）的索引则都可以用到，a，b，d的顺序可以任意调整。where范围查询要放在最后（这不绝对，但可以利用一部分索引）。
特别注意：and之间的部分可以乱序，比如a = 1 and b = 2 and c = 3建立（a，b，c）索引可以任意顺序，MySQL的查询优化器会帮你优化成索引可以识别的形式。      where中有or出现还是会遍历全表。

2）尽量选择区分度高的字段作为索引，某字段的区分度的公式是count(distinct col) / count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一索引的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0。在性别字段上增加索引，并不能明显加快检索速度。

3）不再索引列做运算或者使用函数

4）尽量扩展索引，不要新建索引。比如表中已经有a的索引，现在要加（a，b）的索引，那么只需要修改原来的索引就可以。

5）where子句中经常使用的字段应该创建索引，分组字段或者排序字段应该创建索引，两个表的连接应该创建索引。

6）like模糊查询中，右模糊查询（321%）会使用索引，而%321和%321%会放弃索引而使用全局扫描。