聚簇索引和非聚簇索引

image.png

聚簇索引

聚簇索引的特点：

  1. 按主键值的大小进行记录和页的排序:

    • 数据页(叶子节点)里的记录是按照主键值从小到大排序的一个单向链表。
    • 数据页(叶子节点)之间也是是按照主键值从小到大排序的一个双向链表。
    • B+树中同一个层的页目录也是按照主键值从小到大排序的一个双向链表。

  2. B+树的叶子节点存储的是完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。


    具有这两种特性的B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。这种聚簇索引并不需要我们在MySQL语句中显式的使用INDEX语句去创建。InnoDB存储引擎会自动的为我们创建聚簇索引。 在InnoDB存储引擎中，聚簇索引就是数据的存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引。
    **

    二级索引(复制索引,非聚簇索引)

    聚簇索引只能在搜索条件是主键值时才能发挥作用，因为B+树中的数据都是按照主键进行排序的。当我们想以别的列作为搜索条件时我们可以多建几棵B+树，不同的B+树中的数据采用不同的排序规则。
    二级索引与聚簇索引有几处不同：
    1. 按指定的索引列的值来进行排序
    2. 叶子节点存储的不是完整的用户记录，而只是索引列+主键。
    3. 目录项记录中不是主键+页号，变成了索引列+页号。
    4. 在对二级索引进行查找数据时，需要根据主键值去聚簇索引中再查找一遍完整的用户记录，这个过程叫做回表

密集索引和稀疏索引

image.png

  • 密集索引文件中的每个索引项都应一个记录数

主键索引(聚簇索引): 叶子节点存的是整行数据 是 密集索引

  • 稀疏索引文件只为某些记录数建立一个索引项

非主键索引(二级索引): 叶子节点内容是主键的值, 是稀疏索引

B+树

目录项记录的唯一性

我们需要保证在B+树的同一层内节点的目录项记录除页号这个字段以外是唯一的。所以对于二级索引的内节点的目录项记录的内容实际上是由三个部分构成的：

  • 索引列的值
  • 主键值
  • 页号

    B+树索引总结

  1. 每个索引都对应一棵B+树。用户记录都存储在B+树的叶子节点，所有目录记录都存储在非叶子节点。
    2. InnoDB存储引擎会自动为主键（如果没有它会自动帮我们添加）建立聚簇索引，聚簇索引的叶子节点包含完整的用户记录。
    3. 可以为指定的列建立二级索引，二级索引的叶子节点包含的用户记录由索引列 + 主键组成，所以如果想通过二级索引来查找完整的用户记录的话，需要通过回表操作，也就是在通过二级索引找到主键值之后再到聚簇索引中查找完整的用户记录。
    4. B+树中每层节点都是按照索引列值从小到大的顺序排序而组成了双向链表，而且每个页内的记录（不论 是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单链表。如果是联合索引的话，则页面和记录先按照联合索引前边的列排序，如果该列值相同，再按照联合索引后边的列排序。
    5. 通过索引查找记录是从B+树的根节点开始，一层一层向下搜索。由于每个页面都按照索引列的值建立了页目录，所以在这些页面中的查找非常快。

    索引的优缺点

    索引优势

    IO成本优势

    类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本

    CPU消耗低

    通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

    索引缺点

    空间上的代价

    一个索引都为对应一棵B+树，树中每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，所以一个索引也是会占用磁盘空间的。

    时间上的代价

    索引是对数据的排序，那么当对表中的数据进行增、删、改操作时，都需要去维护修改内容涉及到的B+树索引。所以在进行增、删、改操作时可能需要额外的时间进行一些记录移动，页面分裂、页面回收等操作来维护好排序。

如何建立索引

考虑索引选择性

索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数的比值：
选择性 = 基数 / 记录数
选择性的取值范围为(0, 1]，选择性越高的索引价值越大。如果选择性等于1，就代表这个列的不重复值和表记录数是一样的，那么对这个列建立索引是非常合适的，如果选择性非常小，那么就代表这个列的重复值是很多的， 不适合建立索引。

考虑前缀索引

用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。
前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于ORDER BY和GROUP BY操作，也不能用于覆盖索引。**

建立索引总结

哪些情况建立索引

  1. 主键自动建立唯一索引
  2. 频繁作为查询条件的字段应该创建索引
  3. 查询中与其它表关联的字段，外键关系建立索引
  4. 频繁更新的字段不适合创建索引—-因为每次更新不单单是更新了记录还会更新索引
  5. 单键/组合索引的选择问题，who? (在高并发下倾向创建组合索引)
  6. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
  7. 查询中统计或者分组字段
  8. 利用索引字符串值的前缀(————-)

  9. 尽量使用覆盖索引进行查询，避免回表带来的性能损耗。(————-)

    哪些情况不需要建立索引

  10. 表记录太少

  11. Where条件里用不到的字段不创建索引
  12. 经常增删改的表—-数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。
  13. 注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

// 网上摘取
https://blog.csdn.net/Abysscarry/article/details/80792876
三、结论
通俗理解：
利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引 不同于使用两个单独的索引。复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不姓，电话簿将没有用处。

所以说创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。

重点：

多个单列索引在多条件查询时优化器会选择最优索引策略，可能只用一个索引，也可能将多个索引全用上！ 但多个单列索引底层会建立多个B+索引树，比较占用空间，也会浪费一定搜索效率，故如果只有多条件联合查询时最好建联合索引！

最左前缀原则：

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

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

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

有人说where查询是按照从左到右的顺序，所以筛选力度大的条件尽量放前面。网上百度过，很多都是这种说法，但是据我研究，mysql执行优化器会对其进行优化，当不考虑索引时，where条件顺序对效率没有影响，真正有影响的是是否用到了索引！

联合索引本质：

当创建(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、联合索引比对每个列分别建索引更有优势，因为索引建立得越多就越占磁盘空间，在更新数据的时候速度会更慢。另外建立多列索引时，顺序也是需要注意的，应该将严格的索引放在前面，这样筛选的力度会更大，效率更高。
————————————————
版权声明：本文为CSDN博主「深寒丶」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/Abysscarry/article/details/80792876