3.3. 高性能的索引策略

• 查询语句中使用索引，索引列不能是表达式的一部分。

  前缀索引、索引选择性

• 前缀索引：索引只使用列的前一部分字符，适用于列值为较长字符串、BLOB、TEXT 类型列时。
• 决定合适的前缀长度：1. 查询完整列值的列表。查询部分列的列表，不断增大前缀长度，直到查询结果与完整查询类似，2.计算完整列的选择性，计算不通前缀长度列的选择性，直到与完整列类似。

1.方法示例


2.方法示例

• 前缀索引因并非完全列，故顺序并非完全列的顺序，所以不支持 order by 、group by、排序、范围查询

多列索引

• MySQL5.0 后支持索引合并策略
• 索引合并：使用多个单列索引分别进行查询，将查询结果取并集或交集。

  合适的索引列顺序

• 索引定义中列的顺序决定索引中的排序。
• 不考虑排序和分组时，选择性最高的列放在第一位是较好的。

  聚簇索引

• 聚簇索引：B Tree 索引的叶子节点为数据行。
• 因为数据行无法存在于两个不同的地方，故一张表只能有一个聚簇索引。
• InnoDB 通过主键列构建聚簇索引，没有主键时会选择一个非空唯一索引代替，如果没有这样的索引，则隐式定义一个主键作为聚簇索引。

  聚簇索引相较于 B Tree 优点

• 数据访问更快：索引与行放在一起，当然快~

  聚簇索引缺点

• 插入速度依赖于插入顺序：因为聚簇索引数据为顺序存储，如果乱序插入，会导致已排序行的位置移动。
• 更新操作时，行可能会移动到新的位置，比如更新主键会移动本行，更新本行字段变长，其它行会向后移动。
• 插入、更新操作，可能会发生页分裂的情况。（因为页满）
• 行稀疏、页分裂时会导致全表扫描变慢。

  二级索引

• 叶子节点包含主键列
• 二级索引访问数据行需要两次索引查找
• InnoDB 中自适应哈希索引可以减少二级索引查找。

  InnoDB、MyISAM 数据分布对比

  MyISAM

• 按照数据插入顺序存储在磁盘上
• 行定长，磁盘占用空间相同
• MyISAN 数据分布图
  
• MyISAM 索引图（主键、非主键相同）
  

  InnoDB

• 主键索引图（非主键，叶子节点为主键值）
  
• 按主键顺序插入行：设置主键列为 auto_increment
• InnoDB 默认最大填充因子为 15/16，当达到这个值时，下一条记录会写入新的页。

  覆盖索引

• 覆盖索引：索引包含查询字段的值。
• 覆盖索引避免了对主库索引的而二次查询，但是增加了二级索引需要的空间。
• MySQL 不能在索引中执行通配符开头的 LIKE 操作，只支持基本的大于小于等于，非通配符开头的 LIKE 操作可以转化为简单的比较操作，所以可以使用。

  索引排序

• 使用索引进行排序是不会发生文件排序
• 查询关联多张表，只有使用第一个表的索引时才可以使用索引排序。

  前缀压缩索引

• MyISAM 支持
• 完全保存第一列的值，后续列仅保存部分前缀，以减少空间使用，前缀匹配成功时需要进行完全值匹配，会增加 cpu 压力。

  索引和锁

• 适当使用索引会只需要的行及竞争少量的锁。
• 5.1 前 InnoDB 事务提交后释放锁，5.1 后，服务端排除不需要的行后即释放这些行的锁。
• InnoDB 二级索引使用读锁，访问主键时使用写锁。