聚簇索引和非聚簇索引（相关小知识点）

前言

终于有时间写写博客，记录下聚簇索引与非聚簇索引的相关小知识点。

知识点

1、聚簇索引和非聚簇索引的各自适用场景？

2、聚簇索引和非聚簇索引的优劣势？

优势：

• 叶子节点会存储数据，找到叶子节点就找到了数据行，无需回表；

• 对于辅助索引，使用主键作为指针而不是地址值，，减少了出现行移动或者数据页分裂时辅助索引的维护工作；

• 在排序场景下，由于聚簇索引的物理位置和数据行的逻辑位置的有序性，效率更高；

• 范围查找适用聚簇索引；

劣势：

• 维护索引代价比较昂贵，移动行时会形成碎片；

• 主键是随机值时，使用聚簇索引会比全表扫描更慢，因为每次插入数据都有可能会出现大量行移动；

• 主键较大时，不要使用聚簇索引，因为此时辅助索引也会变得更大（所有辅助索引叶子节点的值都是主键值）

3、主键使用自增id的好处？

聚簇索引的数据的物理存放顺序和索引顺序一致，也就是说，在磁盘上也是连续存储的，这样可以减少内存碎片，同时减少数据物理地址的调整，这里需要注意的是Myisam的主索引和辅助索引实际上是一样的（个人理解），都是非聚簇索引，其物理地址实际是不相邻的，在查找时可能会有多次IO，如图：

                                                             

但是，当涉及到大数据量的排序（注意是大数据量的排序）和全表扫描时，还是Myisam比较适用，因为索引占空间较小，而排序操作是需要在内存中完成的。

4、索引的优化

• 尽量选择较小的数据类型，这样节点存储的key就多，树的高度就小，从而可以减少IO次数；

• 选择离散度高的列作为索引，越有利于数据的查找，当离散到一定程度就是全表扫描；

• 联合索引尽量把离散度高的、索引长度小的放前面

5、索引失效场景

• like以%开头，如like “%1232%”；

• not in和<>操作会使索引失效，因为b+tree本质是二分查找树，无法根据这些操作符做出选择；

• select尽量使用指定列查询，避免使用select *，这样可以使用到覆盖索引；

• 联合索引不符合最左匹配时会使索引失效；

• 最左匹配原则从最左一列开始，直至遇到范围查询停止，其右边的列都无法用到索引；

总结

回表不一定会更慢，使用索引也不一定比全表快，怎么用好索引是门很深的学问。