mysql

高性能Mysql笔记-第五章(2)

5.3.7 使用索引扫描来做排序

Mysql生成有序结果的两种方法: (详见第7章)

  1. 排序操作.
  2. 按索引顺序扫描.

// explain结果中的type,如果为index,则使用第二种方法,按索引顺序扫描.
按索引顺序扫描工作流程:(Innodb二级索引)

  1. 顺序扫描索引,获取主键指针;
  2. 回表查询相应主键的数据行.
    如果索引是覆盖索引,则可以避免上述第二步,减少一次磁盘IO,并且避免大范围扫描时的大量随机IO.

正确示例:
索引为(a,b,c)
示例1:

1
2
select a,b,c from t
where a='xxx' ORDER BY b,c

组合where条件和order by条件,而且都是顺序(ASC). 此外如果组合后是索引的最左前缀,也是可以利用索引进行排序的.

错误示例
示例2:

1
2
select a,b,c from t
where a='xxx' order by b DESC,c ASC.

由于顺序和建立索引的不同,因此不能利用到索引进行排序.
如果需要按不同方向排序, 可以通过存储这列的相反数或者反转值, 满足这种需求.

示例3:

1
2
select a,b,c from t
where a>'xxx' order by b,c

由于a是范围查询,因此无法利用索引的其他列(b和c).

5.3.8 压缩(前缀压缩)索引

1
仅对于MyISAM引擎.

MyISAM使用前缀压缩来减少索引的大小,从而让更多的索引可以放入内存中.

  • 压缩对象:

    1
    2
    1. 字符串(默认设置)
    2. 整数(用参数设置)

  • 压缩方法:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    1. 完全保存索引块的第一个值;
    2. 从第二个值开始,保存:(相同前缀字节数,剩余部分)

    例如原始数据为:
    1. perform
    2. performance

    压缩后:
    1. perform (完整保存)
    2. 7,ance  (相同前缀字节数,剩余部分)

  • 压缩优点:
    使用更少空间;

  • 压缩缺点:
    某些操作变慢: 因为每个值的压缩前缀都依赖前面的值,所以MyISAM查找时无法二分查找. 正序扫描不受影响,倒序扫描性能变慢.
    块中查找某一行的平均耗时: 扫描半个索引块.

  • 压缩索引使用场景:
    IO密集型查询.(减少传输量,可能只需要1/10的磁盘大小)

  • 压缩索引不适用的场景:
    CPU密集型查询.(因为扫描需要随机查找)

5.3.9 冗余和重复索引

  • 背景:
    MySQL允许在列上创建多个索引.

  • 重复索引:
    相同列,顺序相同,类型相同的索引.
    (例: 同一列上建立主键和唯一索引,事实上唯一限制和主键限制都是通过索引实现的,事实上建立了重复索引)

  • 冗余索引:
    前缀索引与索引冗余.
    (例: 索引(A,B)与索引(A)冗余.)

处理:

  1. 重复索引应该移除;
  2. 冗余索引应该考虑移除:
    (1) 如果原来的索引已经太长到影响性能,可以保留冗余索引
    (2) 如果(1)不成立,应该尽量移除冗余索引.

索引太多的缺点:

  1. 写入速度变慢;(可以通过禁用索引,然后延迟build索引优化)
  2. 查询时mysql需要选择索引,索引多的话需要比较的索引也变多,影响查询计划的
    生成速度.
  3. 存储索引成本增加.

5.3.10 未使用的索引

检查各个索引的使用频率:

  1. 打开userstates参数;(默认是关闭的)
  2. mysql服务器正常运行一段时间;(可以考虑使用benchmark)
  3. 查询Information_schema.Index_statistics,查到每个索引的使用频率.

5.3.11 索引和锁

1
以下讨论Innodb引擎

索引可以让查询锁定更少的行:

  1. 减少内存使用;
  2. 减少锁争用,提高并发度.

  • 早期Mysql:
    事务提交后才释放锁;

  • 新版本Mysql:
    服务器端过滤掉行后,释放没用到的行锁.

  • 示例

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    Set autoCommit=0;-- 默认是每句都是一个事务
    -- 这里改成显式提交。
    BEGIN；
    Explain
    select actor_id from t
    where actor_id<5 and actor_id!=1 
    FOR Update; -- 加上排他锁

    Extra列：
    Using Where;Using index

    从Extra列可以看出，虽然使用了索引，但依然有Using Where，意思是使用索引从存储引擎获取到一些数据行，传送给服务器后，依然要使用Where条件进行过滤。因此这个查询锁住的并不只是id为2-4的行。
    （TODO： 验证锁住的范围，以及索引类型是否为主键的影响）

Innodb不同索引对应的锁：

  1. 主键索引： 排他锁（写锁）；
  2. 二级索引： 共享锁（读锁）。

5.4 索引案例

5.4.1 支持多种过滤条件

也就是支持Where条件。
作为过滤条件的列需要考察的3个属性，按重要性如下：

  1. 查询频率；
  2. 相关查询是否是范围查询，或者列的取值是否可枚举。
  3. 选择性。

设计索引的原则：

  1. 查询频率高，则应该加入索引；
  2. 如果是范围查询，则如果作为联合索引的一列，应该放在后面；（因为最左前缀无法利用范围查询后的列）
    如果是可枚举列，则查询语句中可以使用In (‘male’,’female’)绕过列顺序限制，让Mysql始终能够应用相关索引；
  3. 满足前两个条件后，应该把选择性高的列放在联合索引的前面。

案例：
查询频率高的列，组成联合索引：
(sex,country,age)

由于sex的枚举值少，可以放在最前面。
由于age值一般是范围查询，所以应该放在最后面。

  • 注意事项：
  1. 避免使用多个范围查询；
  2. 通过枚举列(IN条件)，让Mysql尽量使用到索引。

5.4.3 优化排序

  • 示例1：

    1
    2
    3
    select * from profiles
    where sex='M' 
    order by rating limit 10

    可以考虑建立索引(sex,rating)来优化查询。

  • 示例2：

    1
    2
    3
    select * from profiles
    where sex='M'
    order by rating limit 10000,10

    这个查询的性能难点在于偏移量太大。
    Mysql需要花费大量的时间扫描需要丢弃的数据。

解决方案：

  1. 预先计算+缓存；
  2. 覆盖索引+延迟关联。

方案2的代码如下：

1
2
3
4
5
6
7
select * from profiles AS　ａ　
join
    (select id from profiles
    where sex='M' 
    order by rating limit 10000,10
    ) as b 
on a.id=b.id

5.5 维护索引和表

相关内容包括：

  1. 找到并修复损坏的表；
  2. 维护准确的索引信息；
  3. 减少碎片。

5.5.1 找到并修复损坏的表

  • 相关命令：
    1
    2
    3
    4
    5
    6
    7
    -- 检查：
    Check Table xxx
    -- 修复方法1：
    Repair Table xxx
    -- 修复方法2：(重建表)
    Alter table innodb_tbl engine=innodb;
    -- 注意此处引擎类型应该与原先一致。

5.5.2 维护索引信息

  • 相关命令：
    1
    2
    -- 重新生成统计信息：
    Analyze Table xxx

Innodb引擎则不在磁盘存储索引统计信息，而是通过随机的索引抽样访问获取信息存储在内存。
相关参数：
innodb_stats_sample_pages
设置样本页的数量。

5.5.3 减少索引和数据的碎片

1
以下针对B-Tree索引。

碎片的类型：

  1. 行碎片；数据行被分散存储到多个地方。
  2. 行间碎片： 顺序的行被存储到多个地方；
  3. 剩余空间碎片： 数据页中有大量空余空间。

相关命令：

1
2
Optimize table xxx
-- 或者可以重建表/索引，也可以消除碎片。

推荐文章