【MySQL学习笔记】03、高性能索引策略

目录

 

独立的列

如果确定长VARCHAR的索引长度

后缀索引

多列索引

索引的列顺序

聚簇索引

覆盖索引：

压缩索引：

冗余和重复索引：

索引与锁：

小结：

---

独立的列

使用索引查询时格式中索引必须是独立的一列，不可为表达式的一部分或者函数的参数：如WHERE id+1<4 无法使用索引，而WHERE id<3则可以

如果确定长VARCHAR的索引长度

• 太长浪费空间拖累性能，太短重复率高无法有效筛选，可以由选择性（或计算重复个数，同理）决定
• 选择性 = 按长度为n索引的不同的索引个数/总数   如"a" "ab" "abc" n=1时选择性 = 1/3 ，n=2时选择性 = 2/3。以选择性接近整体索引的选择性且增加n后选择性增加不明显为最佳
• 有时需要考虑最快情况，比如学号SA19XXXX,前面重复位数过多
   

后缀索引

可以使用触发器实现，VARCHAR存储时反转，实现后缀--->前缀，从而用前缀索引，例如qq邮箱，反转后查找moc.qq前缀索引

多列索引

• AND：交的时候，需要一个包含参与交的所有列的索引，无法使用单个列索引
• OR：会耗费大量计算资源，需要平衡索引能筛去的部分和索引的消耗，尽量选用选择性高的索引进行OR操作

索引的列顺序

通常使用选择性高的排在前面，注意区别系统中大量公共的索引，当一个索引筛去的部分极小的时候此时索引的作用近乎于0，避免创建这样的索引，如匿名用户同一id索引

聚簇索引

• InnoDB在聚簇索引同时保存B+Tree索引和数据行，即每个叶节点 = 索引+行值（二级索引不是）

• 二级索引使用的时候注意它有个默认的primary key索引项，若使用时附加主键的条件，注意最左前缀，如id（primary key），name，age，对于name,age的二级索引实际为（name，age，id），如果条件为WHERE name = "zhangsan" ORDER BY id，则无法使用id的索引，因为不可跳过age

• 通过primary key聚集数据

• 可以减少大量磁盘I/O

• 可以加快查询速度，行值不用返回表中查找

• 插入、更新索引的代价可能很高：插入可能导致页分裂

• 如果更换key会导致大量时间更新索引

• 使用InnoDB尽量按照主键顺序插入，可以增加缓存命中，减少I/O，并且减少页分裂的次数，减少碎片的产生

• 如果没有聚集的数据可以使用Auto_increment使得数据呈现一定的聚集效果增加插入删除效率

• 聚簇索引和二级索引图如下：

覆盖索引：

• 覆盖索引 = 该索引包含所有需要查询的列
• 好处：所有需要查询的数据均可以在索引中找到，不需要回表，如name可以在name索引中找到，age可以在age索引中找到。并且InnoDB的二级索引还附带主键的值，可以在索引里查询主键
• 覆盖索引必须存储数据值，所有mysql的覆盖索引是B+Tree索引
• 在执行Like的操作中无法使用覆盖索引

压缩索引：

• MyISAM使用压缩索引
• 压缩方法："abc","abcde"可存储为"abc","3,de"
• 压缩后的索引会增加查找索引的时间（不再有序，无法二分，逆序查询更慢），但是一次查找可以获得的索引数会大大增多，从而减少了磁盘的I/O

冗余和重复索引：

• 冗余索引：重复创建索引，如primary key默认创建，而后自己重复创建
• 重复索引：创建已被包含索引，如创建了索引（A,B），则索引（A）为重复索引

索引与锁：

索引可以让查询锁定更少的行，前提是在存储引擎层过滤掉不需要的行

小结：

由于索引的存储规则在存储引擎层实现，而查询的过滤条件在服务层获得，由于无法改变存储的方式并且存储引擎层只开放特定的API进行连接，所以只能在存储数据时或书写查询条件前充分考虑存储引擎层的实现才能更好使用索引