MySQL InnoDB 索引、分页、访问计数等优化

编辑于 2016-12-17

* 移动设备下, 可左滑手指以查看较宽代码

mysql

提到 SQL 优化, 易想到随用随取、加索引等常见方法. 然而网上充斥着很多有疏漏甚至错误的文档, 这里集中记录并修正那些错误.

参考:《Optimization, Backups and Replication High Performance MySQL》

2016.12 修改:增加内容.

InnoDB 概览


关于 InnoDB 的更多信息, 请看我的另一篇文章: MySQL InnoDB 浅析:事务隔离级别和死锁

- InnoDB 表基于 聚簇索引 建立, 它对主键查询有很高的性能. 不过他的二级索引(非主键索引) 中必须包含主键列, 所以如果主键很大的话, 其他的列会很大. 因此, 主键应该尽可能的小.

- InnoDB 的存储格式具有平台无关性.

- InnoDB 是目前 MySQL 唯一支持热备份的存储引擎.

- 相对于 MyISAM, 有更好的可靠性和选择性(功能), 因此现在作为默认的存储引擎.

- 行级锁基于索引,如果不走索引会锁表.

数据类型优化

- 尽量避免 NULL. 很多表都可包含为 NULL (空值) 的列, 即使程序不需要保存 NULL 也是如此. 空值占用空间, 不走索引. 所以通常最好指定列为 NOT NULL, 除非真的需要存储 NULL.

- 有符号数和无符号数相差一半的存储范围, 且具有相同的性能, 可根据实际情况选择

- 为整数指定宽度, 如 INT(11), 对大多数应用这是没有意义的: 它不会限制值的合法范围, 只是规定了一些交互工具用来显示字符的个数. 对于存储和计算来说, INT(1) 和 INT(20) 是相同的.

- 对于DECIMAL , 它不同于 DOUBLE, 它可以进行精确计算, 因此需要额外的开销. 可以考虑使用 BIGINT 来代替它存储财务数据, 比如存"分"而不是存"元".

独立列索引

独立的列, 指的是索引列不能是表达式的一部分.

两个常见的错误:

SELECT id FROM a WHERE id + 1 = 5;
SELECT ... WHERE TO_DAYS(..)

关于 like 语句:like “%aaa%” 不会使用索引而 like “aaa%” 可以使用索引.

当数据类型出现隐式转化(如 varchar 不加单引号的话可能会自动转换为 int 型),不会使用索引.

MySQL 5.5 以前,主键列需放在 WHERE 后面的第一个位置,否则用不到索引.

前缀索引

对于长文本的索引, 只索引前面几个字符来提高性能. 例如在基于 MySQL 来存储 SESSION 时, 采用长度为 8 的前缀索引通常能显著提高性能, 并且这种方法对上层应用透明.

多列索引

一个常见的错误是, 为每个列创建独立的索引. 这很可能并不是最好的选择.

从 SHOW CREATE TABLE 中很容易看到这种情况

CREATE TABLE t (
    c1 INT,
    c2 INT,
    c3 INT,
    KEY(c1),
    KEY(c2),
    KEY(c3)
);

实际上, 在 MySQL 5.0 后引入了一种 "索引合并" (index merge) 策略, 一定程度上可以使用多个单列索引来定位指定的行.

该如何做更好? 以下面的查询为例:

SELECT * FROM payment 
WHERE staff_id = 2 AND customer_id = 584;

这时候应该创建一个 多列索引 (或叫联合索引), 如 KEY(staff_id, customer_id)

那么, 是应该创建 staff_id (商品号), customer_id (客户号) 还是颠倒一下顺序? 其实取决于谁的选择性高. 根据经验, 应该将 customer_id 放在前面, 因为对应 customer_id 条件值的数量更少.

组合索引的第一个字段必须出现在查询组句中,这个索引才会被用到.

聚簇索引

聚簇索引(也叫聚集索引) 并不是一个单独的索引类型, 而是一种数据存储方式. 在 InnoDB 中, 聚簇索引在同一个结构中保存了B-Tree索引和数据行. 一个表只能有一个聚簇索引.InnoDB 将主键作为聚簇索引, 如果没有定义主键, 它会选择一个唯一的非空索引替代, 如果还没有, InnoDB 会隐式地定义一个主键来作为聚簇索引.

聚簇索引的重要优点

可以把相关数据保存在一起. 例如可以根据用户 ID 来聚集数据, 减少磁盘等 I/O.

基于此索引的访问速度更快. 聚簇索引将索引和数据保存在同一个 B-Tree 中, 因此比常规索引更快.

利于使用覆盖索引(后面会提到)

缺点

最大限度提高 I/O 密集型应用性能, 但如果全部保存在内存中, 访问的顺序没有那么重要, 聚簇索引失去优势.

插入速度严重依赖于插入顺序. 按照主键顺序插入是最快的, 如果不是这样, 加载完成后用 OPTIMIZE TABLE 重新组织一下表.

更新聚簇索引的代价很高. 因为会把行移动到新位置.

可能导致全表扫描变慢, 尤其是行比较稀疏的情况, 或者页分裂导致数据存储不连续的时候.

二级索引需要两次查找 (下面会说明).

为什么 二级索引 (这里指非聚簇索引, 即非主键) 需要二次索引查找? 前面提到过, 二级索引保存的不是指向行物理位置的指针, 而是行的主键值.

这意味着通过二级索引查找行, 存储引擎需要查找二级索引对应的主键值, 然后根据此值通过聚簇索引查找到对应的行.

InnoDB 通过自适应哈希索引能减少这样的重复工作.

哈希索引

所谓哈希索引,即基于哈希表构建的索引,具有足够快的查询和插入速度,但是不能排序,也不能支持一定范围内的查询. 自适应哈希索引,就是在常用到的 B-Tree 索引上创建一个哈希索引,这是一个完全自动的,用户无法配置的行为 (但可以完全关闭).

对于长字符串,可以创建自定义哈希索引,用户直接用 CRC32 取哈希值 (得到一个整数) 再基于此整数查找,会比查找原字符串快很多.

覆盖索引

如果一个索引包含(或者说覆盖)所有需要查询的字段的值, 就称之为"覆盖索引". 显然, 这样能提高查询的性能.

使用 延迟关联 (deferred join) 可以扩展覆盖索引的使用范围.

后面的小节展示其具体使用方法.

在 MySQL 5.6 和以后, 可能会自动的使用延迟关联来使用覆盖索引, 但相对于手动 SQL 优化, 性能仍有较大差距(测试于MySQL 5.7).

其他索引类别

  • R-Tree 索引

    用于从所有维度索引数据(如地理数据),但目前在 MySQL 中实现不完善,且只有 MyISAM 支持.

  • 全文索引

    查找的是文本的关键字,与 B-Tree 索引不会有冲突,在 5.6 之前只有 MyISAM 支持.

  • 分形树索引

    在第三方存储引擎TokuDB上实现,具有 B-Tree 的很多优点,也避免了一些缺点. 对 InnoDB 的讨论同样适用于 TokuDB.

JOIN 索引

如果你的应用程序有很多 JOIN 查询,你应该确认两个表中 Join 的字段是被建过索引的。这样,MySQL 内部会启动为你优化 Join 的 SQL 语句的机制. 而且,这些被用来 Join 的字段,应该是相同的类型的, 对于那些 STRING 类型,还需要有相同的字符集才行.

对于 LEFT JOIN,则可尽量用 JOIN 替代,因为 MySQL 在 LEFT JOIN 时通过驱动表 (前面的表) 的结果集作为循环基础数据,然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据,然后合并结果. JOIN 则会自动选择小的驱动表,LEFT JOIN 不会.

基于索引的排序

只有当索引的列和 ORDER BY 子句的顺序一致, 并且所有的列的排序方向 (正序和逆序) 一致时, 才能利用索引进行排序. 如果需要关联多张表, 则只有当 ORDER BY 子句引用的字段全部为第一个表时, 才能使用索引进行排序.

优化 COUNT 查询

如果 COUNT() 括号指定了列或者表达式, 则统计的是这个表达式有值的结果数(不是 NULL).

COUNT(*) 并不是我们的猜想那样扩展成所有的列, 而是直接统计所有的行数.这样写意义清晰, 性能也很好.

可以使用估计值, 如每次写操作时维护一个状态表, 或者从 schema 表中读取估计值:

SELECT table_rows 
FROM information_schema.tables 
WHERE table_name = table_name


关于 MyISAM 的神话: 其实它仅在没有 WHERE 条件下才非常快.

优化 LIMIT 分页

一个很常见又恶心的问题是, 当 LIMIT 偏移量非常大的时候, 例如 LIMIT 10000, 20 这样的查询, MySQL 需要查询 10020 条结果然后只返回最后 20 条, 前面的 10000 条被抛弃.优化此类分页查询最简单的办法是 覆盖索引 扫描, 而不是查询所有的列.

我们先进行前面提到过的覆盖索引扫描, 然后根据需要做一次关联再返回所需的列.偏移量很大的时候, 这样做的效率提升非常大.

考虑下面的查询:

SELECT * FROM a 
ORDER BY id LIMIT 50, 5;

如果这个表非常大, 改成:

SELECT * FROM a
INNER JOIN (
    SELECT id FROM a 
    ORDER BY id LIMIT 50, 5
) AS b USING(id);

这就是 延迟关联 (前面 覆盖索引 小节提到过). 这样大大提升查询效率 (3 万行的表能提升 5 倍, 行越多提升越大).

过大的偏移量会导致 MySQL 扫描大量不需要的行然后抛弃掉. 如果可以使用书签记录上次取数据的位置, 下次就能直接从该书签记录的位置开始扫描, 避免使用 OFFSET.

例如, 如果主键是递增的, 可以这样:

首先使用下面的查询获得第一组结果:

SELECT * FROM a 
ORDER BY id DESC LIMIT 20;

上面返回主键为 x 到 x + 20 的记录, 那么下一页就可以从 x + 20 开始:

SELECT * FROM a
WHERE id < x + 20
ORDER BY id DESC LIMIT 20;

上述方法仅适合简易分页,即只有「上一页」「写一页」的情况。如果要做复杂分页,需要为每一页计算偏移值.

其他方法包括预先计算的汇总表、关联到一个冗余表(只包含主键和需要排序的数据列)、只返回部分页面等.

对访问计数表的优化

假如有个计数器表,只有一行,网站每次点击都导致更新:

UPDATE hit_counter SET cnt = cnt + 1;

对于任何更新它的事务,它有一个全局的互斥锁 (行锁),会使得事务只能串行. 要获得更好的并发性能,可以将记录保存到多行,每次随机选择一行更新:

UPDATE hit_counter SET cnt = cnt + 1 WHERE id = RAND() * 100;

一个常见的需求是每天开始一个计数器, 新建以下表:

CREATE TABLE daily_hit_counter (
  day date not null,
  id int unsigned not null,
  cnt int unsigned not null,
  primary key(day, id)
) ENGINE=InnoDB

在这种场景下可以不预先生成行,而用ON DUPLICATE KEY UPDATE (对于唯一索引或主键,不存在则新增,存在则更新) 代替:

INSERT INTO daily_hit_counter(day, id, cnt)
VALUES(CURRENT_DATE(), RAND() * 100, 1)
ON DUPLICATE KEY UPDATE cnt = cnt + 1;

如果希望减少行数,可以写一个定期执行的任务,整合到 id 为 0 的行,并删除 id 非 0 的行:

UPDATE daily_hit_counter AS c 
  INNER JOIN (
    SELECT day, SUM(cnt) AS cnt, MIN(id) as mid
    FROM daily_hit_counter GROUP by day
  ) AS x USING(day)
SET
c.cnt = IF(c.id = x.mid, x.cnt, 0),
c.id  = IF(c.id = x.mid, 0, c.id);

DELETE FROM daily_hit_counter WHERE id != 0 AND cnt = 0;

未归类

使用 TRUNCATE TABLE 语句可以更快的删除表 (不可逆).