测试数据:https://github.com/datacharmer/test_db

1. 数据库调优的七个维度

从上到下,效果越来越差,成本越来越高

  • 业务需求
    • 勇敢的对不合理的需求说不
    • 拨乱反正
  • 系统架构
    • 做架构设计的时候,应充分考虑业务的实际情况,考虑好数据库的各种选择
  • SQL 及索引
    • 根据需求编写良好的 SQL,并去创建足够高效的索引
  • 表结构
    • 设置良好的结构
  • 数据库参数设置
    • 设置合理的数据库性能参数
  • 系统配置
    • 操作系统提供了各种资料使用策略,设置合理的配置,以便数据库充分利用资源
  • 硬件
    • 选择什么配置的机器

2. 慢查询日志

参数作用默认值
log_out_put日志输出到哪儿,默认 FILE,表示文件,设置成TABLE,则将日志记录到 mysql.slow_log 中,也可设置多种格式,比如 FILE, TABLEFILE
long_query_time执行时间超过这么久才记录到慢查询日志,单位秒,可使用小数表示小于秒的时间10
log_queries_not_using_indexes是否要将未使用索引的 SQL 记录到慢查询日志中,此配置会无视 long-query-time 的的配置,生产环境建议关闭,开发环境建议开启OFF
log_throttle_queries_not_using_indexes和 log_queries_not_using_indexes 配合使用,如果 log_queries_not_using_indexes 打开,则该参数将限制每分钟写入的、未使用索引的 SQL 数量0
min_examined_row_limit扫描行数至少达到这么多才记录到慢查询日志0
log_slow_admin_statements是否要记录管理语句,默认关闭,管理语句包括 ALTER TABLE,ANALYZE TABLE,CHECK TABLE,CREATE INDEX,DROP INDEX,OPTIMIZE TABLE,and REPAIR TABОFF
slow_query_log_file指定慢查询日志文件路径datadir 目录下
log_slow_slave_statements该参数在从库上设置,决定是否记录在复制过程中超过 long_query_time 的 SQL,如果 binlog 格式是 row,则该参数无效OFF
log_slow_extra当 log_output=FILE 时,是否要记录额外信息(MySQL 8.0.14 开始提供),对 log_output=TABLE 的结果无影响OFF
2.1 使用方式
  • 永久修改,修改配置文件 my.cnf,在 [mysqld] 下添加参数,重启 MySQL
  • 临时修改,例
set global log_output = 'FILE,TABLE';
set global slow_query_log = 'ON';
set global long_query_time = 0.001;
set global log_queries_not_using_indexes = 'ON'

注意:设置 long_query_time 需要断开连接才能生效

2.2 日志存放位置
  • 表:select * from mysql.slow_log;
  • 文件:show variables like '%slow_query_log_file%';
2.3 分析慢查询日志
  • mysqldumpslow:mysqldumpslow -s t -t 10 -a DESKTOP-63C43K6-slow.log
  • pt-query-digest

3. Explain 使用

ColumnJSON NameMeaning
idselect_id该语句的唯一标识
select_typeNone查询类型
tabletable_name这行数据对应的表名
partitionspartitions匹配的分区
typeaccess_type连接类型
possible_keyspossible_keys可能使用的索引
keykey实际使用的索引
key_lenkey_length索引的长度
refref索引的哪一列被引用了
rowsrows估计要扫描的行
filteredfiltered符合查询条件的数据百分比
ExtraNone附加信息
3.1 select_type
select_type ValueJSON NameMeaning
SIMPLENone简单查询(未使用 UNION 或子查询)
PRIMARYNone嵌套查询时最外层的查询
UNIONNoneUNION 中的第二个或后面的 SELECT 语句
DEPENDENT UNIONdependent (true)UNION 中的第二个或后面的 SELECT 语句,取决于外部查询
UNION RESULTunion_resultUNION 的结果
SUBQUERYNone子查询中的第一个 SELECT
DEPENDENT SUBQUERYdependent (true)子查询中的第一个 SELECT,取决于外部查询
DERIVEDNone用来表示包含在 FROM 子句的子查询中的 SELECT,MySQL 会递归执行并将结果放到一个临时表中,MySQL 内部将其称为是 Derived table(派生表),因为该临时表是从子查询派生出来的
DEPENDENT DERIVEDdependent (true)依赖于另一个表的派生表
MATERIALIZEDmaterialized_from_subquery物化子查询
UNCACHEABLE SUBQUERYcacheable (false)子查询,结果无法缓存,必须针对外部查询的每一行重新评估
UNCACHEABLE UNIONcacheable (false)UNION 属于UNCACHEABLE SUBQUERY 的第二个或后面的查询
3.2 type

扫描方式由快到慢

  • sytem:该表只有一行(等于系统表),system 是 const 类型的特殊
  • const:命中主键和唯一索引,最多返回一条数据,const 查询非常快,因为它只读取一次
  • eq_ref:当使用了索引的全部组成部分,并且索引是主键或唯一索引才会使用该类型
  • ref:当满足索引的最左前缀规则,或者命中普通索引时才会发生
  • fulltext:全文索引
  • ref_or_null:该类型类似于 ref,但是 MySQL 会额外搜索哪些行包含 NULL,这种类型常见于解析子查询
  • index_merge:此类型使用了索引合并优化,表示一个查询里面用到了多个索引
  • unique_subquery:该类型和 eq_ref 类似,但是使用了 IN 查询,且子查询是主键或者唯一索引
  • index_subquery:和 unique_subquery 类似,只是子查询使用的是非唯一索引
  • range:只检索给定范围的行
  • index:与 ALL 的区别是 index 类型只遍历索引树
  • ALL:遍历全表以找到匹配的行

4. SQL 性能分析

  • information_schema

从 MySQL8.0 开始,这个表已经开始被废弃

  • performance_schema
# 查看是否开启性能监控
SELECT * FROM performance_schema.setup_actors;
# 开启相关监控项
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE '%statement/%';
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE '%stage/%';
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_statements_%';
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_stages_%';
# 要执行的 SQL
SELECT * FROM salaries;
# 获取语句的 EVENT_ID
SELECT EVENT_ID, TRUNCATE ( TIMER_WAIT / 1000000000000, 6 ) AS Duration, SQL_TEXT FROM PERFORMANCE_SCHEMA.events_statements_history_long WHERE SQL_TEXT LIKE '%salaries%'
# 性能分析
SELECT event_name AS Stage, TRUNCATE ( TIMER_WAIT / 1000000000000, 6 ) AS Duration FROM PERFORMANCE_SCHEMA.events_stages_history_long WHERE NESTING_EVENT_ID = 268

5. OPTIMIZER_TRACE

6. 数据库诊断命令

  • SHOW PROCESSLIST:查看当前正在运行的进程
  • SHOW STATUS:查看服务器相关信息
  • SHOW VARIABLES:查看 MySQL 的变量
  • SHOW TABLE STATUS:查看表以及视图的状态
  • SHOW INDEX:查看索引相关信息
  • SHOW ENGINE:展示有关存储引擎的相关信息
  • SHOW MASTER STATUS:展示有并 master binlog 文件的相关信息
  • SHOW SLAVE STATUS:展示 slave 线程的相关信息
  • SHOW PROCEDURE:查看存储过程
  • SHOW FUNCTION STATUS:查看函数
  • SHOW TRIGGERS:查看触发器的基本信息
  • SHOW WARNINGS:显示最后一个执行的语句所产生的错误、警告和通知
  • SHOW ERRORS:显示最后一个执行语句所产生的错误
  • SHOW BINARY LOGS:获取 binlog 文件列表
  • SHOW BINLOG EVENTS:只查看第一个 binlog 文件的内容
  • SHOW RELAYLOG EVENTS:查看 relaylog 文件内容

7. JOIN 优化

外层循环的表是驱动表,内层循环的表是被驱动表

  • 小表驱动大表
  • 如果有 where 条件,应当要能够使用索引,并尽可能地减少外尝循环的数据量
  • join 的字段尽量创建索引
  • 尽量减少扫描的行数
  • 参数 join 的表不要太多
  • 如果被驱动的表的 join 字段用不了索引,且内存较为充足,可以考虑把 join_buffer 设置得大一些

8. LIMIT 优化

  • 覆盖索引 + join
select * from employees e inner join (select emp_no from employees limit 300000, 10) t on e.emp_no = t.emp_no;
  • 覆盖索引 + 子查询
select * from employees where emp_no >= (select emp_no from employees limit 300000, 1) limit 10;
  • 范围查询 + limit 语句

前提:拿到上一页的主键最大值

select * from employees where emp_no >= 100100 limit 10;
  • 如果可以获取起始主键值和结束主键值
select * from employees where emp_no between 100110 and 100120;
  • 禁止传入过大的页码

因为页面有筛选条件存在,所以我们可以限制只能查询前几千条数据,已经足够了

9. COUNT 优化

9.1 实验结论
  • count(*) 和 count(1) 一样
  • count(*) 会选择最小的非主键索引,如果不存在任何非主键索引,则会使用主键
  • count(*) 不会排除掉该字段值为 null 的行,count(字段) 会排除
  • 对于不带查询条件的 count(*) 语句,MyISAM 和 InnoDB(MySQL >= 8.0.13),都做了优化
  • 如果没有特殊需求,尽量使用 count(*)
9.2 如何优化
  • 创建一个更小的非主键索引
  • 把数据库引擎换成 MyISAM,一般不使用
  • 汇总表
  • 缓存结果
  • sql_calc_found_rows
select sql_calc_found_rows * from salaries;
select found_rows() as salary_count;
  • information_schema
select TABLE_ROWS from information_schema.TABLES where TABLE_SCHEMA = 'employees' and TABLE_NAME = 'salaries'

好处:不操作 salaries 表,无论它有多少数据,都可以迅速地返回结果
缺点:返回的不是准确值,只是估算值

  • show table status,同上
  • explain,同上

10. ORDER BY 优化

10.1 rowid 排序(常规排序)
  1. 从表中获取满足 WHERE 条件的记录
  2. 对于每条记录,将记录的主键及排序键(id,order_column)取出放入 sort buffer(由 sort_buffer_size 控制)
  3. 如果 sort_buffer 能存放所有满足条件的(id,order_column),则进行排序,否则 sort_buffer 满后,排序(快速排序算法
    )并写到临时文件
  4. 若排序中产生了临时文件,需要利用归并排序算法,从而保证有序
  5. 循环执行上述过程,直到所有满足条件的记录全部参与排序
  6. 扫描排好序的(id,order_column)对,并利用 id 去取 SELECT 需要返回的其他字段
  7. 返回结果集

特点:

  • 看 sort_buffer 是否能存放结果集里面的所有(id,order_column),如果不满足,就会产生临时文件
  • 一次排序需要两次 IO
    • 第 2 步:把(id,order_column)扔到 sort_buffer
    • 第 6 步:通过 id 去获取需要返回的其他字段,由于返回结果是按照 order_column 排序的,所以 id 是乱序的,会存在随机 IO 的问题,MySQL 内部针对这种情况做了个优化,在用 id 取数据之前排序并放到一个缓存里面,这个缓存大小由 read_rnd_buffer_size 控制,接着再去取记录,从而把随机 IO 转换成顺序 IO
10.2 全字段排序(优化排序)
  • 直接取出SQL中需要的所有字段,放到 sort_buffer
  • 由于 sort_buffer 已经包含了查询需要的所有字段,因此在 sort_buffer 中排序完成后可直接返回

好处:性能的提升,无需两次 IO
缺点:一行数据占用的空间一般比 rowid 排序多,如果 sort_buffer 比较小,容易导致临时文件
如果选择:使用 max_length_for_sort_data,当 ORDER BY SQL 中出现字段总长度小于该值,使用全字段排序,否则使用 rowid 排序

10.3 打包字段排序
  • MySQL 5.7 引入
  • 全字段模式的优化,工作原理一样,但是将字段紧密地排列在一起而不是使用固定长度空间

VARCHAR(255) "yes":不打包 255字节,打包 2+3字节

10.4 优化 Order By
  • 利用索引,防止 filesort 的发生
  • 如果发生了 filesort,并且没办法避免,想办法优化 filesort
10.5 如何优化 filesort
  • 调大 sort_buffer_size,减少/避免临时文件、归并操作
    • optimizer trace 中 num_initial_chunks_spilled_to_disk 的值
    • sort_merge_passes 变量的值
  • 调大 read_rnd_buffer_size,让一次顺序 IO 返回的结果更多
  • 设置合理的 max_length_for_sort_data 的值
    • 一般不建议随意调整
  • 调小 max_sort_length(排序时最多取多少字节)

11. GROUP BY 优化

性能依次递减

  • 松散索引扫描(Loose Index Scan)
  • 紧凑索引扫描(Tight Index Scan)
  • 临时表(Temporary table)

12. 表结构设计优化

  • 字段少而精,建议 20 个以内
    • 把常用的字段放到一起
    • 把不常用的字段独立出去
    • 大字段(TEXT/BLOB/CLOB 等等)独立出去
  • 尽量用小型字段
    • 用数字替代字符串
  • 避免使用允许为 NULL 的字段
    • 允许为 NULL 字段很难查询优化
    • 允许为 NULL 字段的索引需要额外空间
  • 合理平衡范式和冗余
  • 如果数据量非常大,考虑分库分表

13. 索引调优

13.1 长字段的调优

新增一个字段,使用函数计算 hash 值(CRC32 或者 FNV64),存储起来,并将该字段添加索引,查询时使用 first_name_hash = CRC32('chenkaixin12121') 查询即可

缺点:like 无法使用该方式进行优化
解决方案:添加前缀索引

索引选择性 = 不重复的索引值/数据表的总记录数

# 完整列的选择性
select count(distinct first_name)/count(*) from employees;
# 计算前缀索引的长度
select count(distinct left(first_name, 8))/count(*) from employees;
# 添加索引
alter table employees add key (first_name(8))

注意:无法做 order by, group by;无法使用覆盖索引
13.2 单列索引和组合索引
  • SQL 存在多个条件,多个单列索引,会使用索引合并
  • 如果出现索引合并,往往说明索引不够合理
  • 如果 SQL 暂时没有性能问题,暂时可以不管
  • 组合索引要注意索引列顺序
13.3 覆盖索引

对应索引 X,SELECT 的字段只需从索引就能获得,而无需到表数据里获取,这样的索引就叫覆盖索引

  • 覆盖索引能提交SQL的性能
  • SELECT 尽量只返回想要的字段(使用覆盖索引,减少网络传输的开销)
13.4 重复索引

索引是有开销的,增删改的时候,索引的维护开销,索引越多,开销越大,条件允许的情况下,尽量少创建索引

explain select * from salaries where from_date = '1986-06-26' order by emp_no;
索引 index(from_date): type=ref extra=null,使用了索引
索引 index(from_date) 某种意义上来说就相当于 index(from_date, emp_no)

修改索引 index(from_date,to_date) 再次执行
explain select * from salaries where from_date = '1986-06-26' order by emp_no;
索引 index(from_date,to_date): type=ref extra=Using filesort 说明 order by 子句无法使用索引
索引 index(from_date, to_date) 某种意义上来说就相当于index(from_date, to_date, emp_no),不符合最左前缀原则,所以 order by 子句无法使用索引

13.5 冗余索引(针对 B-Tree 和 B+Tree)

如果已经存在索引 index(A,B),又创建了 index(A),那么 index(A) 就是 index(A,B) 的冗余索引

Q.E.D.


盛年不重来,一日难再晨。