MySQL调优

发表于 分类于 阅读次数： 本文字数： 3.4k 阅读时长 ≈ 12 分钟

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

数据库调优的七个维度

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

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

慢查询日志

参数 作用 默认值
log_out_put 日志输出到哪儿，默认 FILE，表示文件，设置成TABLE，则将日志记录到 mysql.slow_log 中，也可设置多种格式，比如 FILE, TABLE FILE
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
使用方式
  • 永久修改，修改配置文件 my.cnf，在 [mysqld] 下添加参数，重启 MySQL
  • 临时修改，例
1
2
3
4
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 需要断开连接才能生效

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

Explain 使用

Column JSON Name Meaning
id select_id 该语句的唯一标识
select_type None 查询类型
table table_name 这行数据对应的表名
partitions partitions 匹配的分区
type access_type 连接类型
possible_keys possible_keys 可能使用的索引
key key 实际使用的索引
key_len key_length 索引的长度
ref ref 索引的哪一列被引用了
rows rows 估计要扫描的行
filtered filtered 符合查询条件的数据百分比
Extra None 附加信息
select_type
select_type Value JSON Name Meaning
SIMPLE None 简单查询（未使用 UNION 或子查询）
PRIMARY None 嵌套查询时最外层的查询
UNION None UNION 中的第二个或后面的 SELECT 语句
DEPENDENT UNION dependent (true) UNION 中的第二个或后面的 SELECT 语句，取决于外部查询
UNION RESULT union_result UNION 的结果
SUBQUERY None 子查询中的第一个 SELECT
DEPENDENT SUBQUERY dependent (true) 子查询中的第一个 SELECT，取决于外部查询
DERIVED None 用来表示包含在 FROM 子句的子查询中的 SELECT，MySQL 会递归执行并将结果放到一个临时表中，MySQL 内部将其称为是 Derived table（派生表），因为该临时表是从子查询派生出来的
DEPENDENT DERIVED dependent (true) 依赖于另一个表的派生表
MATERIALIZED materialized_from_subquery 物化子查询
UNCACHEABLE SUBQUERY cacheable (false) 子查询，结果无法缓存，必须针对外部查询的每一行重新评估
UNCACHEABLE UNION cacheable (false) UNION 属于UNCACHEABLE SUBQUERY 的第二个或后面的查询
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：遍历全表以找到匹配的行

SQL 性能分析

  • information_schema

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

  • performance_schema
1
2
3
4
5
6
7
8
9
10
11
12
13
# 查看是否开启性能监控
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

OPTIMIZER_TRACE

数据库诊断命令

  • 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 文件内容

JOIN 优化

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

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

LIMIT 优化

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

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

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

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

COUNT 优化

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

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

  • show table status，同上
  • explain，同上

ORDER BY 优化

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
全字段排序（优化排序）
  • 直接取出SQL中需要的所有字段，放到 sort_buffer
  • 由于 sort_buffer 已经包含了查询需要的所有字段，因此在 sort_buffer 中排序完成后可直接返回

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

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

    VARCHAR(255) “yes”：不打包 255字节，打包 2+3字节

优化 Order By
  • 利用索引，防止 filesort 的发生
  • 如果发生了 filesort，并且没办法避免，想办法优化 filesort
如何优化 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（排序时最多取多少字节）

GROUP BY 优化

性能依次递减

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

表结构设计优化

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

索引调优

字段的调优

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

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

1
2
3
4
5
6
7
8
9
10
索引选择性 = 不重复的索引值/数据表的总记录数

# 完整列的选择性
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；无法使用覆盖索引
单列索引和组合索引
  • SQL 存在多个条件，多个单列索引，会使用索引合并
  • 如果出现索引合并，往往说明索引不够合理
  • 如果 SQL 暂时没有性能问题，暂时可以不管
  • 组合索引要注意索引列顺序
覆盖索引

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

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

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

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 子句无法使用索引

冗余索引（针对 B-Tree 和 B+Tree）

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