Mysql查询优化checklist

摘要

本文是一份 Mysql 数据表的创建和优化checklist，含表设计、索引的创建及使用原则、SQL 优化以及一些配置、事务、架构层的优化手段。
本文会持续更新，文末附更新记录。

数据表设计

在满足业务需求的前提下：

数值类型优于字符类型。
字符类型越短越好。
定长字符使用。 CHAR，变长字符使用 VARCHAR。
关联查询较多时，可以考虑在表中增加冗余字段，以空间换时间。
使用非字符类型做主键。
热点字段可以考虑从逻辑上降低并发度，将一行记录拆分为多行。比如账户额度，可以拆分成多条记录，更新时随机选择一条记录更新。

索引创建

创建索引，需要考虑：

尽量使用 NOT NULL 约束。
该字段值的重复度。重复度高，一般不创建索引。特殊情况是，虽然重复度高，但比例相差大，为了查找比例最小的那部分数据，而创建索引。
联合索引的字段数量不要过多。
联合索引的字段之间，关联性尽可能低。
利用索引优化 GROUP BY、 ORDER BY 字段。这两列不同时，可以创建联合索引。
WHERE 经常查询的字段应该创建索引。
DISTINCT 字段创建索引。
表连接字段创建索引。
表记录很少时，不需要创建索引。
频繁更新的字段，尽量不要创建索引。
不要滥用索引。索引过多，会提高索引的维护成本，降低优化器选择索引的评估效率。可以借助 pt-query-digest 统计索引的使用频率。也可以查询 performance_schema 的 table_io_waits_summary_by_index_usage 表，这个表统计了每个索引的 IO 等待事件，COUNT_STAR 是事件次数。

索引使用优化

WHERE 条件中，不要对字段值进行函数运算。
遵循最左前缀原则。
有多个 WHERE 查询条件，区分度最大的字段尽可能放在左侧。
使用覆盖索引。
使用索引下推，需 Mysql 版本大于 5.6。SET optimizer_switch = 'index_condition_pushdown=on'。
有多个 WHERE 查询条件时，OR 条件的一个字段没有索引，该查询语句就不会使用索引。
LIKE 模糊查询的字符串使用 % 开头，不会使用索引。

SQL优化

EXISTS 和 IN 子查询，使用小表驱动大表。主表更大时，使用 IN；主表更小时，使用 EXISTS。（有一点需要注意，IN 会忽略子查询中是 NULL 的记录，EXISTS 会查出 NULL 的记录）
当优化器选择了错误的索引，首先尝试重新统计索引信息 analyze table [table]，其次可以使用 FORCE INDEX(column) 语法、删除错误的索引或重写 SQL 语句来优化。
统计全表数据行时，COUNT(*) ≈ COUNT(1) > COUNT(字段)，COUNT(*)优先选用更短的二级索引。

其他

配置方面还有这些优化项：

增加缓冲池大小，开启多个缓冲池，InnoDB 的配置项是 innodb_buffer_pool_size、innodb_buffer_pool_size，MyISAM 的配置项是 key_buffer_size。
开启自适应哈希索引，配置项是 innodb_adaptive_hash_index，默认是开启状态。
查询优化器的一些基础配置可以根据物理机的配置进行修改，以提升优化器评估索引代价的准确度。比如 io_block_read_cost 从磁盘中读取一页数据的代价，修改后通过 FLUSH OPTIMIZER_COSTS 更新到内存。更多参数见 mysql.server_cost、mysql.engine_cost 表。

事务的锁会大大影响数据库的查询性能，因此需要：
3. 对长事务进行监控和优化。如果可以，将长事务拆分为短事务。
4. 在一个事务中，如果涉及多个行锁，将最可能造成锁冲突的锁放在最后。
5. 可以考虑在中间件或业务层控制同时操作热点数据行的线程数。

架构方面：
6. 主从架构，读写分离。rpl_semi_sync_master_wait_for_slave_count 配置主从同步的强一致的从库数量。
7. 分库分表。

数据库问题定位相关

慢查询：

-- 查看慢查询是否开启和日志位置
show variables like '%slow_query_log';

-- 开启慢查询
set global slow_query_log='ON';

-- 查看慢查询阈值（单位：s）
show variables like '%long_query_time%';

-- 修改慢查询阈值
set global long_query_time = 3;

PROFILE：

-- 查看PROFILE是否开启
show variables like 'profiling';

-- 开启PROFILE
set profiling = 'ON';

-- 查看当前会话的PROFILES
show profiles;

-- 查看上一个查询开销
show profile;

-- 查看指定QUERY ID的开销
show profile for query 2;
show profile cpu, block io for query 2;

有一点需要注意，SHOW PROFILE 命令将被弃用，可以从 information_schema 中的 profiling 数据表进行查看。

EXPLAIN 语句的 type 类型显示索引的使用情况。一般需要重点优化 all(全表扫描) 和 index(全索引表扫描)。
其他的值：
range 代表采用了索引范围扫描。
index_merge 代表使用了两个及以上的索引，最后取了交集或并集。
ref 表示采用了非唯一索引，或者唯一索引的非唯一性前缀。
eq_ref、const 使用了主键或唯一索引。
system 一般用于 MyISAM 或 Memory 表。

更新记录

2020.09.28 第一版。