从SQL Server到MySql(5) : 高性能的MySql

高效地选择和使用索引有很多种方式, 其中有些是针对特殊案例的优化方法, 有些则是针对特定行为的优化.

1 独立的列

• 如果查询中的列不是独立的, 则MySql 就不会使用索引.
• 索引列不能是表达式的一部分: where col1+1 = 5,也不能是函数的参数: To_Days(col1).
  • 这是由于在存储引擎层, 并不能执行这些行数和运算行为(它们都只能在服务层进行).
• 简化where 条件: 始终将索引列单独放在比较符号的一侧.

2 前缀索引

2.1 索引的选择性

• 不重复的索引值(基数)/表的记录总数(T).
  • 范围: 1/T ~ 1.
  • 选择性越高则查询效率越高: 选择性高的索引可以让MySql 在查找�时过滤掉更多的行.

2.2 选择合适的前缀索引

• 在索引很长的字符串时, 会让索引变慢且大.
  • 当内存中已经存放不下索引时, 只能在硬盘中存储索引. 硬盘相比于内存效率很低.
• 只索引开始的部分字符, 可以节约索引空间, 但会降低索引的选择性.
  • 相比于整体, 部分字符的重复概率更高.
• 要选择足够长的前缀以保证较高的选择性, 同时又不能太长(节约空间).
  • 方式1: 比较最常见的值的列表和最常见的前缀列表.
    • > select count(*) as cnt, city
> From people group by city order by cnt desc limit 10;
    • > select count(*) as cnt, left(city,4) as pref
> From people group by pref order by cnt desc limit 10;
    • 如果选择的前缀(left(city,4))比常见值的次数多的过多, 则增加前缀的长度.
    • 直到两个select 的结果相近时, 说明该长度下的前缀选择性较高.
  • 方式2: 计算完整列的选择性, 并使前缀的选择性接近于完整的列的选择性.
    • 完整列的选择性: > select count(distinct city)/count(*) from people;
    • 各前缀的选择列: > select count(distinct left(city,3))/count(*) as pre3
> select count(distinct left(city,4))/count(*) as pre4
> select count(distinct left(city,5))/count(*) as pre5
> from people;
    • 从各个preN的结果中,选择一个接近适中的(当再增加前缀长度, 选择性提升幅度过小的).

2.3 创建前缀索引

> Alter Table people add Key(city(5));

• 前缀索引的缺点:
  • 无法使用前缀索引做Order by/Group by,或覆盖扫描.
  • 在选择前缀索引时, 除了考虑平均选择性, 同时也需要考虑最坏情况下的选择性.

3 多列索引

• 常见的错误是, 为每个列创建独立的索引, 或者按照错误的顺序创建多列索引.

  • 还有诸如 " 把where 条件里面的列都建上索引".

• MySql 5.0 引入了"索引合并(index merge)"的策略.

  • 可以使用表上的多个单列索引来定位指定的行.
  • 但出现这种情况, 说明应该创建更加符合查询条件下的索引, 如多个列的and, 需要一个包含所有相关列的多列索引.

• 选择合适的索引列顺序

  • 正确的顺序依赖于使用该索引的查询, �并需要考虑如何更好地满足排序和分组的需求.

4 聚簇索引

4.1 聚簇索引

• 聚簇索引不仅仅是一种索引类型, 同时也是数据的存储方式.
  • 实际上是在同一结构中保存了B-Tree索引和数据行.
  • 数据行存放在叶子页(leaf page), 节点页值包含了索引列.
  • 一个表只能有一个聚簇索引(因为只能有一个数据存储).

4.2 选择聚簇索引

• 一些数据库允许选择那个索引作为聚簇索引, 而Innodb �默认使用"主键"列作为聚簇索引.
  • 如果没有定义主键,Innodb 会选择一个唯一的非空索引代替.
  • 若不存在, Innodb 会隐式定义个主键作为聚簇索引.

4.3 优势

• 把相关数据保存在一起.
• 数据访问更快. 最大限度地提高了I/O密集型应用的性能.
• 覆盖索引扫描的查询可以直接使用页节点中的主键值.

4.4 缺点

• 如果数据全部放在内存中,那么访问顺序就不重要了. 聚簇索引也就没有了优势.
• 插入速度严重依赖于插入顺序.
  • 按主键顺序插入的速度最快.
  • 否则应该在插入后, 使用OPTIMIZE TABLE 来重新组织表.
  • 最好使用单调增加的列作为聚簇键.
• 更新聚簇索引列的代价很高, 会产生很多需要移动位置的行.
• 在插入新行或需要移动行时,会导致"页分裂"(page split).
  • 将一页分裂为两页面来容纳新行, 导致更多的磁盘空间占用.
  • 可能导致全表扫描变慢.
• 二级索引的叶子节点包含了引用行的主键列, 所以可能会很大.
• 二级索引访问需要两次索引查找.
• 对于高并发工作负载, 按主键插入会导致明显的争用.
  • 另一个争用的热点是Auto_Increment 锁机制.
  • 此时,可更改innodb_autoinc_lock_mode 配置.

5 覆盖索引

• 一个包含了所有需要查询的字段的值, 就称之为"覆盖索引".
  • 查询只需扫描索引,而无需回表.
• 只能使用B-Tree索引做覆盖索引.
  • 其它索引都不存储索引列的值.
• 限制: 不允许将过滤条件传到存储引擎层.
  • 把数据从存储引擎拉到服务器层,再根据查询条件过滤.
  • 例如,不能在索引中执行like, 只能做最简单的最左前缀匹配的like.
  • 未来的改进: 可以把查询发送到数据上.
    • 索引条件推送.index condition pushdown.

6 使用索引扫描来做排序

6.1 生成有序结果的两种方式

• 通过order by 排序操作.
• 按索引顺序扫描.
  • 如果explain 出来的type 列的值为"index", 说明使用了索引做排序.

6.2 索引排序的条件

• 只有当索引的列顺序和order by 子句的顺序完全一致, 并且所有列的排序方向都一样时.
• 如果查询需要关联多张表, 则只有当order by 子句引用的字段全部为第一个表时.

6.3 索引设计的建议

• 可以使用同一个索引既满足排序,又用于查找行.
• 如果可能, 设计索引时应该尽可能同时满足这两种任务.

7 压缩(前缀压缩) 索引

• 保存数据的方式
  • 完全保存索引块中的第一个值, 然后将其它值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分.
  • 例如: [perform], [performance]. 会被存储为 [perform], [7,ance].
• 节省空间, 代价是更慢的操作.
  • 因为每个值的前缀都依赖前面的值, 所以无法在索引块使用二分查找而只能从头开始扫描.
  • 在CPU 密集型应用中不适用.
• 默认只压缩字符串, 但可配置对整数做压缩.

8 冗余和重复索引

8.1 重复索引

• 在相同列上按相同的顺序创建的相同类型的索引.
  • MySql 需要单独维护重复的索引, 并在优化查询时也需要逐个地进行考量.
  • 所以, 应避免并在发现后删除重复索引.
  • 常见的场景: Unique(ID), Index(ID).
    • MySql 的Unique 和Index 都是通过索引实现的, 这就相当于创建了三个重复索引.

8.2 冗余索引

• 对于索引(A,B), 索引(A) 就是冗余索引.
  • 对于B-Tree 索引来说, (A,B) 也可以当做(A)来使用.
  • 而(B,A) 和(B) 不是(A,B) 的冗余索引.
• 多数情况下, 尽量扩展已有的索引而不是创建新的索引.
  • 当扩展已有的索引会导致其变得太大时, 也会需要冗余索引.
• 表中的索引越多,则插入的速度越慢.
  • 特别是当新增索引后导致达到了内存瓶颈时.

9 索引和锁

Innodb 只有在访问行的时候才会对其加锁.

• 索引能够减少访问的行数, conger减少锁的数量.
• 这必须当在存储引擎层能够过滤掉所有不需要的数据行时才起效.
  • 由于在服务层才能应用where 子句(explain 信息中的using where), 而此时已经无法避免锁定行了.
  • 早期版本里, 只有在事务提交后才能释放锁.
  • 5.1 之后, 可以在服务层过滤掉行后就释放锁.
• 即使使用了索引, 也可能锁住一些不需要的数据.
  • 但如果不能使用索引查找和锁定行的话, 会导致全表扫描并锁住所有行, 性能更差.