入职了一家新公司，第一天团队就遇到了生产环境棘手的 SQL 性能问题。首战立个威，索引小王子的称号不是白拿的。其实文章是基于上家公司 DBA 给研发同学的培训，做了一些整理。帮助 MySQL 常规性能问题快速定位，并提供解决思路。实战中性能问题会比较复杂，理论、规范、技术都会涉及比较多，留了一些TODO项待慢慢完善。

1. 定位数据库性能问题

1.1 命令定位

show processlist用于显示哪些线程正在运行。如果您 root 权限，您可以看到所有线程。否则，您只能看到登录的用户自己的线程，通常只会显示 100 条如果想看跟多的可以使用 full 修饰show full processlist;

mysql>show full processlist;

其中 state 的状态十分关键，下表列出 state 主要状态和描述：

1.2 慢查询日志定位

TODO

2. 分析数据库性能问题

2.1 explain 执行分析计划

举个今天遇到的例子，收到反馈某查询语句执行耗时 90 多秒。执行分析计划如下：

根据结果里的“Type”字段可以清晰的看到 card_record 和 access_card 表未使用到任何索引，走了全表扫描。(Type 为 All)

我们执行以下脚本，给 access_card 表 card_num 字段加上索引：
ALTER TABLE access_card ADD INDEX idx_card_record_cardnum ( card_num );

再次执行：

可发现，目前该语句再无走全表的情况，全部走了索引。去掉 explain 执行该 SQL，时间减到了 0.001s。

2.2 show profile 命令分析

TODO

2.3 show status 命令分析

TODO

3. 解决数据库性能问题

3.1 数据库建模规范

3.1.1 数据库字符集选择

非面向国际化的系统数据库应使用 GBK 字符集（双字节表示一个汉字），而不应该使用 UTF-8 字符集（三个字节表示一个汉字）

3.1.2 数据长度选择

应根据业务需求选择合适的字段长度，不应大幅过长

3.1.3 主键、外键、约束

每个表强制性必须要有主键
不应使用外键，外键由程序端维护数据一致性（考虑外键更新成本）
能建立唯一约束、非空约束的必须建立对应约束

3.1.4 数据类型选择

能以数值类型存储的必须以数值类型存储，能以时间类型存储必须以时间类型存储，剩余的考虑使用字符串类型

整数类型（原则：尽量使用无符号的，尽量使用占用空间小的类型）

字符串类型（原则：MYisam 引擎可以使用 CHAR 字符类型，InnoDB 引擎不应该使用 CHAR 数据类型应使用 VARCHAR 数据类型代替）

时间类型（原则：尽量选择占用空间小的子类型，不需要存储 1970 年以后的日期都应选择 TIMESTAMP 类型，比如 CREATE_TIME、UPDATE_TIME）

3.1.5 字段拆分

TEXT 、MEDIUMTEXT、LONGTEXT 等数据类型应拆分为单独一张表，以提高 DML 的效率

3.1.6 范式和反范式与多表关联性能权衡

1、能不单独拆分表的尽量不拆分，一对多、多对多关系除外
2、范式遵循应控制在 3 张表关联之内

3.2 索引

1、应在 WHERE 限制条件的字段、表关联字段、排序字段、分组字段建立索引
2、不应过度索引，比如在第一点列出的字段外建立索引
3、不应重复在相同字段建立索引
4、应在基数大重复值少的字段建立索引，不能在基数小重复值多的字段建立索引
5、能建立唯一索引的必须建立唯一索引
6、使用短索引提高索引效率
Id
10001
10101
10201
10301
10401
10501
……
在此 ID 上建立索引: create index idx_tab on (id(3)), 前三个字符就已足够使用字段唯一基数最大了

7、索引字段使用函数、表达式的导致索引失效，例如：id + 2 = 100 、lower(name) = ‘jimmy’ 导致 WHERE 字段不能使用索引
8、使用 IS NULL 导致索引失效，例如：where name is null 导致不能使用到索引
9、隐式转换可能导致不能使用到索引，例如 where id = ‘1’ 或 where a.id = CAST(b.id AS CHAR)，所以在设计表时应注意表关联字段必须为同一类型，传入的查询值也必须和字段类型相同
10、like 模糊查询%后匹配可以使用到索引，%前匹配不能使用到索引，例如：name like ‘%jimmy%’ 和 name like ‘%jimmy’ 不能使用到索引，name like ‘jimmy%’ 可以使用到索引，一定要使用前匹配请使用搜索引擎实现
11、复合索引查询时一定要带前导列才能使用到索引，
例如：create index idx_user on (id,name,age)
查询 1：select x from user where id =1
查询 2：select x from user where id =1 and name = ‘jimmy’
查询 3：select x from user where id =1 and age=18
查询 4：select x from user where id =1 and name=’jimmy’ and age=18
可以使用到索引。
查询 5：select x from user where name = ‘jimmy’
查询 6：select x from user where age=18
查询 7：select x from user where name = ‘jimmy’ and age=18

3.3 分区

分区表优势：
1、改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
2、增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；
3、均衡 I/O：可以把不同的分区映射到不同磁盘以平衡 I/O，改善整个系统性能。

3.3.1 Range 分区

TODO

3.3.2 List 分区

TODO

3.3.3 Key 分区

TODO

3.4 多表关联

多表关联最优原则：
1、 表关联次数越少效率越高
2、 驱动表是所有表中过滤完后记录最少的那个
3、 驱动表过滤完后的记录要比被驱动表的记录少
4、 以减少循环次数为优化目标
5、 被驱动表关联字段一定要有索引

3.5 HINT

TODO

3.6 数据库锁

TODO

3.7 SQL 编写规范

TODO

3.8 读写分离

1、当数据库负载达到 2 时或数据量超过 1 个亿应使用读写分离策略
2、读写分离策略解决性能问题最简单、迅速，架构改动最少，水平拓展最容易
3、Mysql 读写分离中需要关注主从延迟问题，对于数据延迟敏感的应用应强制读主库
4、非常适合读占最主要的业务类型的系统，不适合写占多数的系统

3.9 分库分表

1、当数据库负载达到 5 时或数据量超过 5 个亿应使用分库分表策略
2、分库策略有垂直分库和水平分库：
垂直分库适用于模块之间耦合不太紧密系统，尽量避免大数据量跨库查询
水平分库适用于可以根据业务规则来分片的字段，例如：商家 ID、省份 ID
3、分库分表和读写分离可以同时使用