索引的作用和意义——mysql

对操作的影响：

　　对于select: 适当的索引的确能够提升查询速度

　　对于insert: 并不会用到索引，还会增加维护索引的时间

　　对于update: 如果更新索引列，不会提高，少量更新非索引列，会有适当的提高，并且更新索引列，索引还需要重新维护

　　对于delete: 如果删除的条件上用到了索引，会提高，否则不会

 

索引的概述：

　　MySQL中的索引类型有很多，比如B+树索引、哈希索引、聚簇索引、覆盖索引，这几个比较常见而且使用较多的，还有空间数据索引、全文索引等。 

 

　　B+ Tree索引特点：
　　　　B+树适用于全键值、键值范围和键前缀查找（最左前缀）。

　　特点：叶子到根结点距离相等，索引树是有序的。

　　限制：

　　　　如果不是按索引最左列开始查找，无法使用索引。

　　　　不能跳过索引中的列。只能按索引列从左到右查询，否则无法使用索引。

　　　　查询中有某个列使用范围查询，右边的索引列都无法使用索引优化查询。 

 

　　哈希索引 

　　　　【哈希索引】：基于哈希表的数据结构实现的，需要精确匹配索引所有列的查询才有效，存储引擎会对每一行数据的每一列计算一个哈希码。

　　　　解决哈希冲突：如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目。索引只需存储对应的哈希值，其结构十分紧凑，查找速度快。　

　　特点：
　　　　索引只包含哈希值和行指针，不存储字段值，所以不能使用索引值来避免读取行（回表查询），因为访问内存中的行速度快，这一点影响倒不大。

　　　　数据并不是按索引值顺序存储，所以无法用于排序。

　　　　不支持部分索引列值匹配查找，因为是使用索引列的全部内容计算哈希值的。比如，在列（A，B）上建立哈希索引，若只查询A，则无法使用索引。

　　　　只支持等值比较查询，包括=、in()、<=>.

　　　　当出现hash冲突，存储引擎必须遍历链表中所有行指针，逐行比较直到符合条件。

　　　　当某列的哈希冲突很多时候，如果要从表中删除一行，需要遍历对应哈希值的链表的每一行，找到并删除其引用，代价很大。

 

　　聚簇索引

　　　　实际是一种数据存储方式。聚簇：表示数据行和相邻的键值紧凑地存储在一起。InnoDB的聚簇索引是在同一结构中保存了B-Tree索引和数据行。一个表只能有一个聚簇索引（即：主键，具体参考：https://www.cnblogs.com/sfzlstudy/p/15945146.html）

　　　　

　　　　优点：

　　　　　　a、可以把相关的数据保存在一起，比如实现电子邮箱，可以根据用户ID来聚集数据，减少每封邮件都要进行一次IO的操作。

　　　　　　b、数据访问更快，因为聚簇索引将索引和数据保存在同一个B-Tree中。

　　　　　　c、使用覆盖索引扫描查询可以直接使用叶节点中的主键值。

　　　　缺点：

　　　　　　a、插入速度严重依赖插入顺序，按主键顺序插入速度最快。

　　　　　　b、更新聚簇索引代价很高，因为强制InnoDB将每个被更新的行移动到新的位置。并且移动行可能导致页分裂问题，导致表占用更多的磁盘空间。

　　　　　　c、二级索引可能更大，因为其叶子结点包含了引用行的主键列。且访问需要两次索引查找。

 

　　覆盖索引

　　　　如果一个索引包含（覆盖）所有需要查询的字段的值，这个索引成为覆盖索引。Mysql只能使用B-Tree做覆盖索引。（和聚集所有关联）

　　

　　　　索引可以用来提高查询效率，那如果能够直接通过索引获取数据，就不用回表查询，更加高效。因此覆盖索引就提供了这样的性能。

　　　　好处：

　　　　　　a、无需回表查询数据，在索引的叶子节点可以获取数据。索引条目远小于数据行大小，可以极大减少数据 访问量。

　　　　　　b、索引是按照列值顺序存储的，对于密集IO的范围查询，比随机读取行数据的IO少的多。

　　　　　　c、InnoDB的二级索引保存行的主键值，如果二级主键覆盖查询，可以避免主键索引的二次查询。