教程【免费下载链接】school-of-sreAt LinkedIn, we are using this curriculum for onboarding our entry-level talents into the SRE role.项目地址https://gitcode.com/gh_mirrors/sc/school-of-sre点击查看免费下载本指南是 LinkedIn School of SRE 课程中 Relational Databases 模块的实战章节面向 SRE 与后端工程师系统讲解如何定位慢查询、读懂 MySQL 优化器给出的执行计划并通过合理的索引设计把全表扫描优化为索引查找。读完本文你将掌握慢查询日志的配置与聚合分析、EXPLAIN 输出中关键字段的解读方法以及单列索引、复合索引与 JOIN 场景下的索引设计原则能够在真实业务中独立完成一轮「发现问题 → 分析计划 → 建立索引 → 验证收益」的查询性能调优闭环。为什么查询性能是关系型数据库的生死线查询性能是关系型数据库最关键的方面之一。如果调优不当SELECT查询会变得越来越慢最终同时拖垮应用和 MySQL 服务器本身。当业务表增长到百万甚至千万行级别时一条没有索引支撑的查询可能从毫秒级退化到秒级直接影响线上用户体验和数据库负载。性能调优的核心任务只有两个识别慢查询——找到那些执行时间超过阈值的 SQL 语句改善慢查询——通过重写 SQL或在其涉及的表上建立合适的索引来提升执行效率。本文使用经典的employees示例数据库包含约 30 万行员工数据与约 284 万行薪资数据该数据集在课程 SELECT Query 一节中被反复使用所有示例均可直接在你的 MySQL 实例上复现。配合课程 Lab 提供的 Docker 一键实验环境你可以跟着本文逐步操作。第一步开启慢查询日志锁定可疑 SQL慢查询日志的工作原理慢查询日志Slow Query Log记录了执行耗时超过配置参数long_query_time所设阈值的 SQL 语句。这些语句就是优化的候选对象。MySQL 官方文档对其的定位十分明确它是排查性能问题的第一手工具。开启慢查询日志涉及以下四个核心配置参数变量说明示例值slow_query_log开启或关闭慢查询日志ONslow_query_log_file慢查询日志文件的位置/var/lib/mysql/mysql-slow.loglong_query_time阈值时间。执行时间超过该值的查询会被记录到慢查询日志中5log_queries_not_using_indexes与慢查询日志配合开启后即使执行时间低于long_query_time未使用任何索引的查询也会被记录到慢查询日志ON其中long_query_time的单位是秒可以设置为小数以捕获亚秒级慢查询。课程 Lab 中给出的my.cnf配置是标准的生产级写法$ cat custom/my.cnf [mysqld] # These settings apply to MySQL server # You can set port, socket path, buffer size etc. # Below, we are configuring slow query settings slow_query_log1 slow_query_log_file/var/log/mysqlslow.log long_query_time1说明以上配置位于[mysqld]段下写入/etc/mysql/conf.d/目录即可生效。slow_query_log1与slow_query_logON等价。更多关于 MySQL 配置文件的加载路径如/etc/my.cnf、启动参数与运行时动态开启方式可参考课程 operations.md 中的说明——慢查询日志既可以在配置文件中启用也可以通过 SQL 语句动态开启且默认仅错误日志error log开启其余日志按需启用以节省 I/O 与存储。在实际调优会话中本文使用以下更激进的参数组合以便捕获更多可疑查询slow_query_log开启long_query_time设为0.3300 毫秒log_queries_not_using_indexes开启。一组真实的慢查询探测用例在employees数据库上执行以下 5 条查询查询 1按姓氏精确查找员工SELECT * FROM employees WHERE last_name Koblick查询 2查找薪资不低于 100000 的记录SELECT * FROM salaries WHERE salary 100000查询 3按职称查找记录SELECT * FROM titles WHERE title Manager查询 4查找 1995 年入职的员工SELECT * FROM employees WHERE year(hire_date) 1995查询 5按入职年份分组统计每年最高薪资SELECT year(e.hire_date), max(s.salary) FROM employees e JOIN salaries s ON e.emp_nos.emp_no GROUP BY year(e.hire_date)执行结果非常有意思查询1、3、4的执行时间都在 300ms 以内但检查慢查询日志时这三条查询同样被记录了——因为log_queries_not_using_indexes开启后未使用任何索引的查询即使很快也会被记入日志查询2 和 5则不仅执行时间超过 300ms同样没有使用任何索引。这说明慢查询日志 ≠ 只记录慢的查询。当log_queries_not_using_indexes开启时它还会记录所有性能隐患——那些迟早会随着数据量增长而变慢的全表扫描。用 mysqldumpslow 聚合分析慢查询日志MySQL 自带了慢查询日志分析工具mysqldumpslowPercona 生态中则有功能更丰富的pt-query-digest它会把日志中结构相似、仅参数不同的查询聚合到一行统计非常适合快速掌握慢查询的整体分布mysqldumpslow /var/lib/mysql/mysql-slow.log上图为课程文档提供的mysqldumpslow真实输出。可以看到它按查询模板聚合统计每行格式为Count: 执行次数 Time: 总耗时平均耗时 Lock: 总锁时间平均锁时间 Rows: 总返回行数平均返回行数, 执行用户来源例如select year(e.hire_date), max(s.salary) from employees e join salaries s on e.emp_nos.emp_no group by N——执行 1 次耗时 2.43s返回 16 行select * from salaries where salary N——执行 3 次累计耗时 0.71s返回 94709 行注意该查询虽然单次平均仅 0.2s但结果集巨大select * from employees where last_nameS——执行 1 次耗时 0.14s返回 187 行属于未用索引但很快的典型。注意输出中的细节mysqldumpslow默认会把实际值替换为占位符——数字替换为N字符串替换为S从而把同一形状的查询聚合在一起。如果希望看到真实参数值可以加-a选项但代价是当同类查询使用了不同参数值时输出行数会显著增加不利于快速概览。课程 Lab 的 Workshop 3 还演示了慢查询日志的实时观察方法在容器内执行select sleep(3);后用tail -f /var/log/mysqlslow.log可以看到日志逐条追加包含Query_time、Lock_time、Rows_sent、Rows_examined等关键字段。生产环境中这些字段是判断查询是否需要优化的直接依据。第二步用 EXPLAIN 读懂 MySQL 的执行计划EXPLAIN 是什么EXPLAIN命令可以加在任何需要分析的查询前它描述的是查询的执行计划——MySQL 优化器将如何理解和执行这条查询。EXPLAIN适用于SELECT、INSERT、UPDATE和DELETE语句会告诉我们表是如何被连接的、是否使用了索引、使用了哪个索引等关键信息。理解EXPLAIN输出的基本字段是判断查询性能的核心技能。课程 operations.md 中还提到了EXPLAIN ANALYZE——它在前者基础上额外展示执行成本cost、实际返回行数与真实耗时适合对单条查询做深度的执行后分析。来看一个具体例子mysql EXPLAIN SELECT * FROM salaries WHERE salary 100000; ----------------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ----------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | salaries | NULL | ALL | NULL | NULL | NULL | NULL | 2838426 | 10.00 | Using where | ----------------------------------------------------------------------------------------------------------------- 1 row in set, 1 warning (0.00 sec)关键字段解读对上面的输出需要重点理解以下字段Partitions——执行查询时考虑的分区数量。该字段仅在表被分区partitioned时有效未分区表为NULL。Possible_keys——优化器在创建执行计划时考虑过的索引列表。为NULL说明没有任何候选索引可用。Key——执行查询时实际使用的索引。为NULL表示最终没有使用任何索引。Rows——执行期间检查examined的行数。这是衡量查询成本最直观的数字。Filtered——被检查的行中最终被保留进结果集的百分比。最理想、最优化的情况是 100。Extra——MySQL 如何评估查询的附加信息例如是否只使用WHERE子句匹配目标行、是否使用索引Using index、是否使用临时表Using temporary、是否文件排序Using filesort等。对上述salaries查询的结论非常清晰表无分区没有任何候选索引因此也没有使用任何索引优化器检查了2838426 行超过 280 万行其中只有10%进入最终结果集Extra 显示仅靠WHERE子句逐行匹配目标行——这是一次典型的全表扫描type 为 ALL。检查 280 万行只为找出 10% 的记录显然不是好的执行计划。课程 Lab 的 Workshop 2 给出了同类型对比对first_name Sachin的查询EXPLAIN显示type: ALL、检查 299113 行而EXPLAIN ANALYZE进一步给出量化数据——预估成本 30143.55实际首行耗时 28.284ms、全量耗时 3952.428ms。这组数字生动地说明了全表扫描的真实代价。第三步建立索引把全表扫描变成索引查找索引为什么能加速查询索引用于加速按给定列值选取相关行的过程。没有索引时MySQL 从第一行开始遍历整张表寻找匹配行表行数越多操作成本越高。有了索引后MySQL 可以确定数据应从哪个位置开始查找而无需读取整张表。几个与索引相关的重要概念主键本身也是一种索引它是所有索引中最快的且与表数据一起存储InnoDB 中即聚簇索引二级索引Secondary Index存储在表数据之外用于进一步增强 SQL 语句的性能索引大多以 B-Tree 存储但存在例外空间索引使用 R-Tree内存表Memory 引擎使用哈希索引。课程 concepts.md 中进一步补充了索引的适用边界在大表上只取少量行、MIN/MAX 类查询等场景中索引收益明显但对于写密集负载、以全表扫描为主的访问模式、或需要访问大量行的查询索引并不能带来收益甚至可能拖慢写入。创建索引的两种方式建表时创建——如果事先知道哪些列会承载最多的WHERE条件可以在创建表时直接在这些列上建立索引修改表结构时创建——为了改善某个问题查询的性能对已包含数据的表使用ALTER或CREATE INDEX命令建立索引。该操作不会阻塞表但耗时取决于表的规模。回到上一节的salaries查询扫描 280 万行只为取其中 10% 的记录显然不合理。解决方案是在salaries表的salary列上建立索引CREATE INDEX idx_salary ON salaries(salary)两种写法等价ALTER TABLE salaries ADD INDEX idx_salary(salary)验证索引效果EXPLAIN 前后对比建立索引后同样的查询其执行计划发生了质变mysql EXPLAIN SELECT * FROM salaries WHERE salary 100000; --------------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | --------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | salaries | NULL | ref | idx_salary | idx_salary | 4 | const | 13 | 100.00 | NULL | --------------------------------------------------------------------------------------------------------------- 1 row in set, 1 warning (0.00 sec)对比前后两次输出维度索引前索引后typeALL全表扫描ref索引查找possible_keys / keyNULLidx_salaryrows检查行数283842613filtered10.00%100.00%ExtraUsing whereNULL现在使用的索引正是新建的idx_salary优化器只需检查13 行且全部进入结果集查询执行时间也从700ms 以上降至几乎可以忽略。这正是索引价值的直观体现从 280 万行的全表扫描变成 13 行的索引点查。第四步复合索引与最左前缀原则为什么需要复合索引再看一个例子按first_name和last_name的组合条件精确查找员工但业务上也可能只按last_name查询。mysql EXPLAIN SELECT * FROM employees WHERE last_name Dredge AND first_name Yinghua; ----------------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ----------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299468 | 1.00 | Using where | ----------------------------------------------------------------------------------------------------------------- 1 row in set, 1 warning (0.00 sec)此时虽然employees表只有约 30 万行查询执行很快但结果集仅占被检查行的1%——如果数据量达到百万甚至千万级这就是灾难。正确的做法是在last_name和first_name上建立复合索引而不是分别建立两个单列索引。CREATE INDEX idx_last_first ON employees(last_name, first_name)复合索引立即生效mysql EXPLAIN SELECT * FROM employees WHERE last_name Dredge AND first_name Yinghua; --------------------------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | --------------------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | employees | NULL | ref | idx_last_first | idx_last_first | 124 | const,const | 1 | 100.00 | NULL | --------------------------------------------------------------------------------------------------------------------------- 1 row in set, 1 warning (0.00 sec)最左前缀原则列顺序决定索引的可用性建立复合索引时我们把last_name放在first_name之前原因是优化器在评估查询时会从索引的最左前缀开始匹配。例如一个三列复合索引idx(c1, c2, c3)其可用的搜索组合依次为(c1)(c1, c2)(c1, c2, c3)即如果你的WHERE子句中只有first_name这个索引将不会生效。验证如下mysql EXPLAIN SELECT * FROM employees WHERE first_name Yinghua; ----------------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ----------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299468 | 10.00 | Using where | ----------------------------------------------------------------------------------------------------------------- 1 row in set, 1 warning (0.00 sec)而只包含last_name的WHERE条件则正常工作key 为idx_last_firstkey_len 从 124 缩短为 66说明只使用了复合索引的前半段mysql EXPLAIN SELECT * FROM employees WHERE last_name Dredge; --------------------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | --------------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | employees | NULL | ref | idx_last_first | idx_last_first | 66 | const | 200 | 100.00 | NULL | --------------------------------------------------------------------------------------------------------------------- 1 row in set, 1 warning (0.00 sec)设计要点复合索引的列顺序应按照查询的实际使用频率来排——把最常作为独立查询条件、且选择性最高的列放在最前面。上例中选择last_name在前正是因为业务既会按last_name first_name查询也会单独按last_name查询。第五步JOIN 查询的索引陷阱与优化从复制表开始还原问题现场为了演示 JOIN 场景中难以定位的性能痛点课程文档先创建了不带主键的表副本CREATE TABLE employees_2 LIKE employees; CREATE TABLE salaries_2 LIKE salaries; ALTER TABLE salaries_2 DROP PRIMARY KEY;这里刻意去掉了salaries_2表的主键目的是模拟真实世界中驱动表上缺失索引的情形——employees_2与salaries_2表都是employees、salaries的副本但salaries_2失去了emp_no上的主键索引。此时执行以下 JOIN 查询mysql SELECT e.first_name, e.last_name, s.salary, e.hire_date FROM employees_2 e JOIN salaries_2 s ON e.emp_nos.emp_no WHERE e.last_nameDredge; 1860 rows in set (4.44 sec)结果集 1860 行耗时约4.5 秒——这种多表联查又慢的场景仅凭肉眼很难判断瓶颈在哪个表。必须借助EXPLAIN。由于查询涉及两张表EXPLAIN输出中会有 2 条记录mysql EXPLAIN SELECT e.first_name, e.last_name, s.salary, e.hire_date FROM employees_2 e JOIN salaries_2 s ON e.emp_nos.emp_no WHERE e.last_nameDredge; ------------------------------------------------------------------------------------------------------------------------------------------ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ------------------------------------------------------------------------------------------------------------------------------------------ | 1 | SIMPLE | s | NULL | ALL | NULL | NULL | NULL | NULL | 2837194 | 100.00 | NULL | | 1 | SIMPLE | e | NULL | eq_ref | PRIMARY,idx_last_first | PRIMARY | 4 | employees.s.emp_no | 1 | 5.00 | Using where | ------------------------------------------------------------------------------------------------------------------------------------------ 2 rows in set, 1 warning (0.00 sec)读懂 JOIN 的执行顺序EXPLAIN 输出中多行记录按求值顺序排列先评估salaries_2表别名为s再评估employees_2表别名为e并完成连接。可以看到salaries_2的 type 为ALLpossible_keys 为NULL——扫描了几乎全部 2837194 行employees_2通过主键PRIMARY以eq_ref方式被连接但它的filtered只有 5%且WHERE子句对应的idx_last_first索引没有被用作访问路径。也就是说查询虽然用WHERE子句过滤了最终结果集但WHERE对应的索引在employees_2表上并未被利用驱动表的全表扫描成了真正的瓶颈。修复 JOIN 的钥匙为连接列建立索引课程文档指出一个通用规律如果 JOIN 连接的两个索引具有相同的数据类型连接速度总是更快。因此在salaries_2表的连接列emp_no上建立索引CREATE INDEX idx_empno ON salaries_2(emp_no)再次查看执行计划mysql EXPLAIN SELECT e.first_name, e.last_name, s.salary, e.hire_date FROM employees_2 e JOIN salaries_2 s ON e.emp_nos.emp_no WHERE e.last_nameDredge; -------------------------------------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | -------------------------------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | e | NULL | ref | PRIMARY,idx_last_first | idx_last_first | 66 | const | 200 | 100.00 | NULL | | 1 | SIMPLE | s | NULL | ref | idx_empno | idx_empno | 4 | employees.e.emp_no | 9 | 100.00 | NULL | -------------------------------------------------------------------------------------------------------------------------------------- 2 rows in set, 1 warning (0.00 sec)新索引带来的改变是全方位的连接顺序被优化器反转employees_2变为第一张评估的表它通过idx_last_first对应WHERE子句只检查 200 行filtered 达到 100%salaries_2通过新索引idx_empno以ref方式连接每行仅检查 9 行两张表的检查行数从 280 万级骤降到数百级。查询实际耗时从 4.5 秒下降到 0.02 秒约 225 倍提升mysql SELECT e.first_name, e.last_name, s.salary, e.hire_date FROM employees_2 e JOIN salaries_2 s ON e.emp_nos.emp_no WHERE e.last_nameDredge\G 1860 rows in set (0.02 sec)经验总结JOIN 性能问题的排查顺序——① 用EXPLAIN找出先被评估全表扫描的驱动表② 检查WHERE子句涉及的列是否有可用索引③ 检查连接列ON 条件两侧是否都有索引且数据类型一致④ 为缺失的索引执行CREATE INDEX再复跑EXPLAIN验证执行计划与耗时。实战总结一条完整的查询性能调优流水线把本文的方法串起来就得到 SRE 日常最常用的一条调优流水线开启慢查询日志设置slow_query_logON、合适的long_query_time如 0.3~1 秒并开启log_queries_not_using_indexes捕捉全表扫描聚合分析用mysqldumpslow或 Percona 的pt-query-digest对日志做模板化聚合找出耗时最多、执行最频繁、结果集最大的查询模板单条剖析对候选 SQL 执行EXPLAIN必要时用EXPLAIN ANALYZE看真实 cost 与耗时重点看type、key、rows、filtered、Extra五列判断是全表扫描还是索引缺失设计索引单列条件建单列索引多列条件按最左前缀原则建复合索引把最常独立查询的列放前面JOIN 场景确保连接列两侧都有同类型的索引验证收益复跑EXPLAIN对比rows与filtered再实测查询耗时确认调优闭环完成。对于想动手实践的读者课程 Lab 提供了完整的 Docker 环境mysql:8容器 custom/my.cnf挂载 employees示例数据导入其中 Workshop 2 演示了用EXPLAIN/EXPLAIN ANALYZE剖析查询、创建索引并复测的完整过程Workshop 3 演示了慢查询日志的实时查看——建议按顺序完成将本文的知识转化为肌肉记忆。关于更底层的原理可进一步阅读本模块的 MySQL 架构了解优化器在服务器层中的位置与 InnoDB了解缓冲池、B-Tree 索引与自适应哈希索引如何支撑查询加速若查询量持续增长还可结合 MySQL Replication 的读扩展方案从架构层面分摊查询压力。赞分享教程【免费下载链接】school-of-sreAt LinkedIn, we are using this curriculum for onboarding our entry-level talents into the SRE role.项目地址https://gitcode.com/gh_mirrors/sc/school-of-sre点击查看免费下载相关推荐ZeroTierOne 如何备份与复制网络控制器的 controller.d 数据目录ZeroTierOne 如何备份与复制网络控制器的 controller.d 数据目录 自托管 ZeroTier 网络控制器network controll教程school-of-sre数据库性能优化索引设计与查询调优全攻略school of sre数据库性能优化索引设计与查询调优全攻略 数据库性能优化是软件可靠性工程SRE的核心技能之一直接影响系统响应速度和用户体验。当业教程LinkedIn School of SRE数据库SQL查询性能优化实战指南LinkedIn School of SRE数据库SQL查询性能优化实战指南 前言 在关系型数据库应用中查询性能是决定系统响应速度和用户体验的关键因素。本文教程上一篇witty-opencode安全策略权限管理、配置验证与故障恢复的完整指南下一篇tarsier性能优化技巧提升大规模集群监控效率的5个实用方法创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考
