MySQL是最流行的开源关系型数据库，很多开发者每天都在用，但真正懂MySQL优化的人不多。

你是不是也遇到过这些问题：

  • 网站访问量一大，数据库就慢，页面加载要好几秒
  • 某个SQL查询特别慢，查一次要好几秒
  • 数据库CPU占用很高，不知道是哪个查询导致的
  • 连接数经常爆满，应用连不上数据库
  • 加了索引还是慢，不知道为什么

这些都是MySQL优化要解决的问题。很多开发者觉得MySQL优化很高深，是DBA的事情，和自己无关。其实不然，MySQL优化是每个后端开发者都应该掌握的技能，因为大部分性能问题都是SQL和索引的问题，而这些是开发者写出来的。

本文是一篇MySQL 8.0优化的入门指南，从零开始，带你了解MySQL优化的核心知识：索引原理、SQL优化、表结构设计、配置优化、慢查询分析、锁和事务等。内容实用，循序渐进，适合MySQL优化初学者。

本文基于MySQL 8.0版本，大部分内容也适用于MySQL 5.7。

一、MySQL优化的整体思路

在讲具体的优化技巧之前，先说说MySQL优化的整体思路。很多人一上来就改配置、加索引，结果效果不好，甚至越改越差。正确的优化思路应该是：

1. 先定位问题，再优化。

优化的第一步是定位问题，搞清楚瓶颈在哪里。是SQL慢？还是CPU高？还是内存不够？还是IO瓶颈？还是连接数不够？

不同的问题有不同的解决方案，不能盲目优化。比如SQL慢，你改配置是没用的，需要优化SQL和索引；CPU高，可能是某个复杂查询导致的，需要优化查询；IO瓶颈，可能是索引没建好，或者查询了太多数据。

定位问题的工具：

  • SHOW PROCESSLIST：查看当前正在执行的线程，看哪些SQL在跑
  • EXPLAIN：分析SQL的执行计划，看索引使用情况
  • 慢查询日志：记录执行时间超过阈值的SQL
  • SHOW STATUS：查看MySQL的各种状态指标
  • performance_schema：MySQL的性能监控schema，能查看详细的性能数据
  • 第三方监控工具：比如Prometheus+Grafana、Zabbix、阿里云RDS的监控等

2. 优先优化SQL和索引，其次是表结构，最后才是配置和硬件。

MySQL优化的优先级：

  1. SQL和索引优化：这是最有效、成本最低的优化方式，80%的性能问题都能通过优化SQL和索引解决。
  2. 表结构设计优化：合理的表结构能减少数据冗余，提高查询效率。
  3. 配置优化：合理的配置能充分利用硬件资源，提高MySQL的性能。
  4. 硬件升级：最后才是升级硬件，这是成本最高的方式，而且如果SQL和索引没优化好，升级硬件效果也有限。

很多人一上来就改配置、升级硬件，结果花了很多钱，效果不好。正确的做法是先优化SQL和索引，这是性价比最高的优化方式。

3. 优化是一个持续的过程，不是一次性的。

业务在发展，数据在增长，访问量在变化，今天优化好了，过一段时间可能又出现新的性能问题。所以优化是一个持续的过程，需要定期监控、定期分析、定期优化。

建立监控和告警机制，及时发现性能问题；定期分析慢查询日志，找出需要优化的SQL；定期 review 表结构和索引，清理无用的索引，添加需要的索引。

4. 不要过度优化，要权衡收益和成本。

优化不是越多越好，要权衡收益和成本。比如，为了一个很少执行的查询，加了好几个索引，结果写操作变慢了，存储空间增加了，这就得不偿失了。

优化的时候要考虑：

  • 这个SQL执行频率高不高？如果一天只执行一次，慢一点也没关系
  • 优化的收益有多大？从10秒优化到1秒，收益很大；从100ms优化到50ms，收益就小很多
  • 优化的成本有多大？加索引会增加写开销和存储空间，改表结构可能需要锁表
  • 有没有副作用？优化会不会影响其他功能？会不会引入新的bug？

综合考虑收益和成本，选择最合适的优化方案。

二、索引优化：MySQL优化的核心

索引是MySQL优化的核心，80%的性能问题都和索引有关。理解索引的原理，学会正确地使用索引，是MySQL优化最重要的技能。

什么是索引？

索引是一种数据结构，用来快速查找表中的记录。就像书的目录一样，有了目录，你可以快速找到想要的内容，而不用一页一页地翻。

MySQL中最常用的索引是B+树索引，InnoDB存储引擎使用的就是B+树索引。B+树是一种平衡多路查找树，它的特点是：

  • 非叶子节点只存储键值和指针，不存储数据
  • 叶子节点存储所有的数据，并且叶子节点之间用双向链表连接
  • 树的高度很低，通常3-4层就能存储几千万条数据

B+树索引的查找效率很高，从根节点到叶子节点，只需要3-4次IO就能找到数据。

聚簇索引和非聚簇索引。

InnoDB的索引分为聚簇索引（主键索引）和非聚簇索引（二级索引）。

  • 聚簇索引：主键索引，叶子节点存储的是整行数据。一个表只能有一个聚簇索引，因为数据只能按一种顺序物理存储。
  • 非聚簇索引：二级索引，叶子节点存储的是主键值，而不是整行数据。通过二级索引查找数据的时候，先找到主键值，再通过主键索引找到整行数据，这个过程叫"回表"。

回表是需要额外IO的，所以如果能通过二级索引直接获取需要的数据（覆盖索引），就不需要回表，查询效率会更高。

索引的最左前缀原则。

联合索引（多个字段组成的索引）遵循最左前缀原则：查询的时候，必须从索引的最左边的字段开始，并且不能跳过中间的字段。

比如，有一个联合索引idxab_c(a, b, c)：

  • WHERE a = 1：能用到索引
  • WHERE a = 1 AND b = 2：能用到索引
  • WHERE a = 1 AND b = 2 AND c = 3：能用到索引
  • WHERE b = 2：用不到索引，因为没有从最左边的a开始
  • WHERE a = 1 AND c = 3：只能用到a的索引，c用不到，因为跳过了b
  • WHERE a > 1 AND b = 2：a能用范围查询，b用不到，因为范围查询之后的字段用不到索引

理解最左前缀原则，才能正确地创建和使用联合索引。

什么时候需要建索引？

不是所有字段都需要建索引，索引也有代价：

  • 索引占用存储空间
  • 插入、更新、删除数据的时候，需要同时维护索引，降低写操作的性能
  • 过多的索引会让优化器选择索引的时候变慢

所以，索引要建在合适的字段上：

  1. WHERE条件中的字段：经常出现在WHERE条件中的字段，尤其是等值查询的字段
  2. JOIN连接的字段：表连接的字段，建索引能大大提高连接效率
  3. ORDER BY排序的字段：排序的字段，如果有索引，可以利用索引的有序性，避免文件排序
  4. GROUP BY分组的字段：分组的字段，如果有索引，可以利用索引的有序性，避免临时表
  5. DISTINCT去重的字段：去重的字段，如果有索引，可以利用索引快速去重

什么时候不需要建索引？

  1. 数据量小的表：表只有几百几千条数据，全表扫描也很快，不需要建索引
  2. 区分度低的字段：比如性别（男/女）、状态（0/1），区分度很低，建索引效果不好，优化器可能会选择全表扫描
  3. 经常更新的字段：字段更新很频繁，维护索引的代价很大
  4. 很少出现在查询条件中的字段：字段很少被查询，建了也用不上
  5. 字符串很长的字段：比如varchar(500)，建索引占用空间大，查询效率低，可以考虑前缀索引

索引使用的注意事项。

  1. 避免在索引字段上使用函数或运算：比如WHERE YEAR(createtime) = 2021，用了函数，索引就用不到了。可以改成WHERE createtime >= '2021-01-01' AND create_time < '2022-01-01'。
  2. 避免隐式类型转换：比如字段是varchar类型，查询的时候用数字WHERE phone = 13800138000，会发生隐式类型转换，索引用不到。应该用字符串WHERE phone = '13800138000'。
  3. 避免使用前导通配符的LIKE：比如WHERE name LIKE '%张三%'，前导通配符，索引用不到。如果是WHERE name LIKE '张三%'，就能用到索引。
  4. OR条件的索引使用：OR条件两边的字段都要有索引，才能用到索引。如果有一个字段没有索引，整个查询就会全表扫描。可以考虑用UNION代替OR。
  5. 联合索引的字段顺序：联合索引的字段顺序很重要，区分度高的字段放前面，等值查询的字段放前面，范围查询的字段放后面。
  6. 避免冗余索引：比如有了索引(a, b)，就不需要再建索引(a)了，因为(a, b)可以满足a的查询。
  7. 定期清理无用索引：有些索引可能从来没被使用过，占用空间，还影响写性能，可以定期清理。

EXPLAIN：分析SQL的执行计划。

要判断SQL有没有用到索引，索引用得对不对，最常用的工具是EXPLAIN。在SQL前面加上EXPLAIN，就能看到SQL的执行计划。

EXPLAIN输出的关键字段：

  • id：查询的序号，复杂查询有多个id
  • select_type：查询类型，比如SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）、DERIVED（派生表）等
  • table：查询的表
  • type：访问类型，性能从好到差：system > const > eq_ref > ref > range > index > ALL。如果是ALL，就是全表扫描，需要优化。
  • possible_keys：可能用到的索引
  • key：实际用到的索引，如果是NULL，就是没用到索引
  • key_len：用到的索引长度，可以判断用到了联合索引的几个字段
  • ref：索引比较的列或常量
  • rows：预估需要扫描的行数，越少越好
  • Extra：额外信息，比如Using index（覆盖索引）、Using where（WHERE过滤）、Using filesort（文件排序，需要优化）、Using temporary（临时表，需要优化）等

通过EXPLAIN，你可以知道SQL有没有用到索引，用到了哪个索引，扫描了多少行，有没有文件排序和临时表。这是SQL优化最重要的工具，一定要学会用。

三、SQL优化：写出高效的SQL

索引建好了，还要写出高效的SQL，才能充分发挥索引的作用。很多时候，索引建了，但SQL写得不好，索引用不到，还是慢。

*1. 只查询需要的字段，避免SELECT 。**

SELECT 会查询所有字段，增加网络传输和内存开销，而且如果有覆盖索引的话，SELECT 会导致回表，降低查询效率。

应该只查询需要的字段，比如SELECT id, name, email FROM users WHERE ...，而不是SELECT * FROM users WHERE ...。

而且，明确查询字段，也有利于使用覆盖索引。如果查询的字段都在索引里，就不需要回表了，查询效率会高很多。

2. 分页查询优化。

分页查询是很常见的场景，但当页码很大的时候，LIMIT 100000, 10会很慢，因为MySQL需要扫描100010行，然后丢弃前100000行，只返回10行。

优化方法：

  • 延迟关联：先通过索引查询出需要的id，再通过id关联查询整行数据。比如SELECT * FROM users WHERE id IN (SELECT id FROM users WHERE ... LIMIT 100000, 10)，或者用JOIN的方式。
  • 记录上一页的最后一个id，用范围查询：比如上一页最后一个id是100000，下一页就查询WHERE id > 100000 LIMIT 10，这样就能利用主键索引，效率很高。这种方式适合id连续的场景，而且只能上一页下一页，不能跳页。
  • 限制最大页码：很多业务场景下，用户不会翻到很后面的页，可以限制最大页码，比如最多翻100页，避免深分页查询。

3. 避免在WHERE条件中对字段做运算或函数。

前面说过，在索引字段上使用函数或运算，会导致索引失效。比如：

  • WHERE YEAR(createtime) = 2021 → 改成 WHERE createtime >= '2021-01-01' AND create_time < '2022-01-01'
  • WHERE price * 2 = 100 → 改成 WHERE price = 50
  • WHERE LOWER(name) = 'zhangsan' → 如果name字段不区分大小写，直接 WHERE name = 'zhangsan'

4. 用EXISTS代替IN，或者用JOIN代替子查询。

子查询有时候效率不高，尤其是IN子查询，MySQL的优化器对IN子查询的处理有时候不好。

可以考虑用EXISTS代替IN，或者用JOIN代替子查询。比如：

-- IN子查询
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE status = 1)

-- 用EXISTS代替
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 1)

-- 用JOIN代替
SELECT DISTINCT u.* FROM users u JOIN orders o ON u.id = o.user_id WHERE o.status = 1

具体用哪种方式，要看实际情况，可以用EXPLAIN分析一下，选择效率最高的。MySQL 8.0对子查询的优化有了很大提升，很多时候子查询和JOIN的效率差不多，但还是建议用EXPLAIN验证一下。

5. 批量操作，减少数据库交互次数。

批量插入、批量更新、批量删除，比循环单条操作效率高很多，因为减少了数据库交互次数和事务开销。

比如批量插入：

-- 循环单条插入（慢）
INSERT INTO users (name, email) VALUES ('张三', 'zhangsan@example.com');
INSERT INTO users (name, email) VALUES ('李四', 'lisi@example.com');

-- 批量插入（快）
INSERT INTO users (name, email) VALUES ('张三', 'zhangsan@example.com'), ('李四', 'lisi@example.com');

批量更新也可以用CASE WHEN的方式，或者用临时表。但要注意，批量操作的数据量不要太大，一次几百条比较合适，太大可能会导致锁表或者事务太长。

6. 避免大事务。

大事务会导致很多问题：

  • 锁持有时间长，影响并发
  • 回滚时间长
  • 主从延迟大
  • undo log膨胀

应该尽量把大事务拆成小事务，比如批量更新10000条数据，可以分成10次，每次1000条，每次一个事务。

7. 合理使用LIMIT，避免查询大量数据。

查询的时候，尽量用LIMIT限制返回的行数，避免查询大量数据。比如列表页，每页10条、20条，不要一次查询几千条。

如果确实需要查询大量数据（比如导出数据），可以分批查询，每次查询一部分，处理完再查询下一部分，避免一次性加载太多数据到内存。

8. UNION和UNION ALL的选择。

UNION会对结果去重，需要排序和去重操作，开销大；UNION ALL不去重，直接合并结果，开销小。

如果确定结果没有重复，或者不需要去重，就用UNION ALL，不要用UNION。

9. 排序优化。

ORDER BY排序，如果能利用索引的有序性，就不需要文件排序（Using filesort），效率会高很多。

优化排序的方法：

  • 在排序字段上建索引
  • 联合索引的字段顺序和排序顺序一致
  • 避免对不同的字段用不同的排序方向（比如ORDER BY a ASC, b DESC），MySQL 8.0之前不支持索引的降序扫描，会导致文件排序。MySQL 8.0支持了降序索引，可以建(a ASC, b DESC)的索引。
  • 分页排序的时候，深分页的排序很慢，可以用前面说的延迟关联或记录上一页最后一个值的方式优化

10. 分组优化。

GROUP BY分组，如果能利用索引的有序性，就不需要临时表（Using temporary），效率会高很多。

优化分组的方法：

  • 在分组字段上建索引
  • 如果不需要排序，可以加ORDER BY NULL，避免MySQL对分组结果排序
  • 用覆盖索引，查询的字段都在索引里，不需要回表

四、表结构设计优化

好的表结构设计是数据库性能的基础，表结构设计不好，后面再怎么优化SQL和索引都事倍功半。

1. 选择合适的数据类型。

数据类型的选择原则：

  • 越小越好：在满足需求的前提下，选择越小的数据类型。比如年龄，用TINYINT就够了，不要用INT。小的数据类型占用空间小，处理速度快，索引也更小。
  • 越简单越好：简单的数据类型处理开销小。比如用INT存IP地址，而不是用VARCHAR；用DATETIME存时间，而不是用字符串。
  • 避免NULL：NULL字段会占用额外的空间，查询的时候优化器处理更复杂，索引也不好用。尽量给字段设置默认值，避免NULL。当然，有些场景确实需要NULL（比如表示"未知"），那就用NULL，不要为了避免NULL而用特殊值（比如-1、''），那样可能更麻烦。

常用数据类型的选择：

  • 整数：TINYINT（1字节，-128~127）、SMALLINT（2字节，-32768~32767）、MEDIUMINT（3字节）、INT（4字节）、BIGINT（8字节）。根据数值范围选择最小的够用的类型。
  • 字符串：CHAR（定长，适合长度固定的，比如手机号、身份证号）、VARCHAR（变长，适合长度不固定的，比如姓名、地址）。VARCHAR要设置合理的长度，不要太大。
  • 时间：DATETIME（8字节，范围大，与时区无关）、TIMESTAMP（4字节，范围小，与时区有关）。一般用DATETIME就好，MySQL 5.6之后DATETIME也支持毫秒了。
  • 小数：DECIMAL（精确小数，适合金额）、FLOAT/DOUBLE（近似小数，不适合金额）。金额一定要用DECIMAL，不要用FLOAT/DOUBLE，会有精度问题。
  • 布尔：用TINYINT(1)，0表示false，1表示true。
  • 枚举：如果字段的取值是固定的几个，可以用ENUM类型，或者用TINYINT加注释。ENUM类型存储紧凑，查询快，但修改枚举值需要ALTER TABLE，比较麻烦。

2. 主键设计。

InnoDB的表是聚簇索引表，主键索引的叶子节点存储整行数据，所以主键的设计很重要。

主键设计的原则：

  • 自增主键：推荐用自增的BIGINT作为主键，这样插入数据的时候，主键是递增的，数据是顺序插入的，不会导致页分裂和碎片，插入性能好。
  • 不要用UUID作为主键：UUID是无序的，插入的时候会导致随机IO，页分裂，插入性能差，而且UUID占用空间大（36字节），二级索引也会变大。如果需要全局唯一ID，可以用雪花算法（Snowflake）生成有序的ID。
  • 不要用业务字段作为主键：业务字段可能会变化，或者有重复，不适合作为主键。比如手机号、邮箱，虽然唯一，但可能会变，而且是字符串，占用空间大。
  • 主键不要太大：主键越小越好，因为二级索引的叶子节点存储的是主键值，主键大了，所有二级索引都会变大。

3. 字段设计。

  • 不要有太多字段：单表字段不要太多，一般建议不超过50个。字段太多，行数据大，查询效率低，维护也麻烦。如果字段太多，可以考虑垂直拆分，把不常用的字段拆到扩展表。
  • 避免大字段：TEXT、BLOB等大字段，占用空间大，查询的时候会影响性能。如果有大字段，考虑单独存到扩展表，或者存在对象存储（比如OSS）里，数据库只存URL。
  • 字段要有注释：每个字段都要有注释，说明字段的含义、取值范围、单位等。这对后期维护很重要。
  • 时间字段：一般要有createtime和updatetime字段，记录创建时间和更新时间。createtime可以设置DEFAULT CURRENTTIMESTAMP，updatetime可以设置ON UPDATE CURRENTTIMESTAMP。

4. 字符集和排序规则。

MySQL 8.0默认的字符集是utf8mb4，排序规则是utf8mb40900ai_ci。推荐用utf8mb4，支持emoji和所有Unicode字符。不要用utf8（也就是utf8mb3），它最多只支持3字节，不支持emoji。

排序规则推荐用utf8mb40900aici（MySQL 8.0默认），或者utf8mb4generalci。ai表示不区分重音，ci表示不区分大小写。如果需要区分大小写，可以用utf8mb40900ascs。

注意，表和字段的字符集要一致，连接的字符集也要一致，避免乱码和隐式转换。

5. 表的拆分。

当单表数据量很大的时候（比如超过几千万条），查询性能会下降，这时候可以考虑分表。

分表的方式：

  • 水平分表：按某个字段（比如用户ID、时间）把数据分散到多个表中。比如按用户ID取模，分到user0到user9共10个表。水平分表能解决单表数据量过大的问题，但会增加查询和维护的复杂度。
  • 垂直分表：把不常用的字段、大字段拆到扩展表中。比如用户表，把基本信息存在user表，把详细信息存在userinfo表，用userid关联。垂直分表能减少单表的行大小，提高查询效率。

分表是一个比较复杂的话题，需要根据业务场景选择合适的分表策略。在数据量还不是特别大的时候，优先用索引和SQL优化，不要过早分表，分表会增加很多复杂度。

五、配置优化：让MySQL充分利用硬件

SQL和索引优化好了，接下来是配置优化。合理的配置能让MySQL充分利用硬件资源，提高性能。

MySQL的配置文件是my.cnf（Linux）或my.ini（Windows），修改后需要重启MySQL生效。有些配置可以在线修改，用SET GLOBAL命令。

下面介绍一些重要的配置项，基于MySQL 8.0，InnoDB存储引擎。

1. 内存相关配置。

  • innodbbufferpool_size：InnoDB缓冲池大小，这是最重要的配置项。缓冲池用来缓存表数据和索引，缓冲池越大，能缓存的数据越多，磁盘IO越少，性能越好。

- 建议设置为物理内存的50%-70%，如果是专用数据库服务器，可以设到70%-80%。 - 比如服务器内存16G，可以设为10G-12G；内存32G，可以设为20G-24G。 - 不要设得太大，要给操作系统和其他进程留足够的内存。

  • innodbbufferpool_instances：缓冲池实例个数，多个实例可以减少并发竞争。一般设为4-8个，每个实例至少1G。比如缓冲池16G，可以设为8个实例，每个2G。
  • innodblogbuffer_size：日志缓冲大小，事务提交前，redo log先存在缓冲里，再刷到磁盘。一般设为16M-64M，大事务多的话可以设大一点。
  • sortbuffersize：排序缓冲大小，每个线程排序的时候会分配这个大小的缓冲。不要设太大，因为是每个线程都分配的，连接多了会占用很多内存。一般设为256K-1M，默认256K。
  • joinbuffersize：连接缓冲大小，同样是每个线程分配的。一般设为256K-1M。
  • tmptablesize和maxheaptable_size：临时表大小，GROUP BY、DISTINCT等操作会用到临时表。这两个值要设成一样，一般设为64M-256M。如果临时表超过这个大小，会转成磁盘临时表，性能下降。

2. IO相关配置。

  • innodbflushlogattrx_commit：redo log刷盘策略，很重要的配置，影响性能和数据安全。

- 0：每秒刷一次盘，事务提交不刷盘。性能最好，但宕机会丢失1秒的数据。 - 1：每次事务提交都刷盘。最安全，不会丢数据，但性能最差。默认值。 - 2：每次事务提交写到操作系统缓冲，每秒刷一次盘。性能和安全的折中，宕机最多丢失1秒数据，比0安全。 - 如果对数据安全要求高（比如金融系统），设为1；如果对性能要求高，能容忍少量数据丢失，可以设为2；一般不建议设为0。

  • sync_binlog：binlog刷盘策略。

- 0：由操作系统决定什么时候刷盘。性能好，但不安全。 - 1：每次事务提交都刷binlog。最安全，性能差。默认值是1（MySQL 5.7之后）。 - N：每N次事务提交刷一次盘。 - 和innodbflushlogattrx_commit配合，双1（都设为1）是最安全的，但性能最差。如果对性能要求高，可以适当调整。

  • innodbfileper_table：每个表一个独立表空间。建议设为ON（默认就是ON），这样每个表有自己的.ibd文件，方便管理和回收空间。
  • innodbiocapacity和innodbiocapacity_max：InnoDB的IO能力，影响脏页刷新和merge insert buffer的速度。根据磁盘的IOPS设置，普通SAS盘设为200，SSD盘可以设为1000-4000。
  • innodbreadiothreads和innodbwriteiothreads：读写IO线程数。默认是4，CPU核数多的话可以设大一点，比如8-16。

3. 连接相关配置。

  • max_connections：最大连接数。根据应用的并发量设置，不要设太大，因为每个连接都占用内存。一般设为500-2000。如果连接数不够，应用会报"Too many connections"错误。

- 可以用SHOW STATUS LIKE 'Maxusedconnections'查看历史最大连接数，根据这个调整max_connections。

  • waittimeout和interactivetimeout：非交互连接和交互连接的超时时间。默认是8小时，太长了，很多空闲连接占用资源。可以设为300-600秒（5-10分钟），让空闲连接自动断开。
  • maxconnecterrors：最大连接错误次数，超过这个次数会被禁止连接。默认是100，可以设大一点，比如1000，避免因为网络波动导致被禁。
  • threadcachesize：线程缓存大小，缓存的线程可以被新连接复用，减少创建线程的开销。一般设为50-200。

4. 其他重要配置。

  • defaultstorageengine：默认存储引擎，设为InnoDB。
  • charactersetserver和collationserver：默认字符集和排序规则，设为utf8mb4和utf8mb40900aici。
  • sql_mode：SQL模式，影响SQL的语法和行为。MySQL 8.0默认是严格模式，建议保持默认，不要去掉严格模式，严格模式能提前发现数据问题。
  • longquerytime：慢查询阈值，单位秒。超过这个时间的SQL会被记录到慢查询日志。一般设为1秒，或者0.5秒。
  • slowquerylog：慢查询日志开关，设为ON，开启慢查询日志。
  • slowquerylog_file：慢查询日志文件路径。
  • logqueriesnotusingindexes：记录没有使用索引的查询，即使执行时间没超过阈值。建议开启，方便发现需要加索引的查询。但注意，这个可能会记录很多小表的全表扫描，可以根据需要开启。

配置优化不是一蹴而就的，需要根据实际情况调整。建议先了解每个配置项的含义，再根据服务器的硬件和业务特点调整。不要盲目照搬网上的配置，因为每个服务器的硬件和业务都不一样。

六、慢查询分析：找出需要优化的SQL

慢查询日志是MySQL优化的重要工具，它能记录执行时间超过阈值的SQL，帮你找出需要优化的SQL。

开启慢查询日志。

在my.cnf中配置：

slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = ON

也可以在线开启：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = ON;

分析慢查询日志。

慢查询日志是文本文件，可以直接看，但如果日志很大，直接看效率很低。推荐用工具分析：

  1. mysqldumpslow：MySQL自带的慢查询分析工具，简单易用。

- mysqldumpslow -s c -t 10 /var/log/mysql/slow.log：按执行次数排序，显示前10条 - mysqldumpslow -s t -t 10 /var/log/mysql/slow.log：按总执行时间排序，显示前10条 - mysqldumpslow -s at -t 10 /var/log/mysql/slow.log：按平均执行时间排序，显示前10条

  1. pt-query-digest：Percona Toolkit中的工具，功能更强大，能生成详细的分析报告。

- pt-query-digest /var/log/mysql/slow.log：分析慢查询日志，生成报告 - 报告包含：查询的总次数、总时间、平均时间、95%时间、最小最大时间、调用的用户和主机、EXPLAIN信息等

  1. 阿里云RDS的慢查询分析：如果用的是云数据库，一般自带慢查询分析功能，能在控制台上看到慢查询的统计和详情，很方便。

优化慢查询的步骤。

  1. 找出执行频率高、总时间长的SQL，这些是优化的重点，优化它们收益最大。
  2. 用EXPLAIN分析SQL的执行计划，看有没有全表扫描、文件排序、临时表，索引用得对不对。
  3. 根据执行计划，优化SQL或者添加索引。
  4. 优化后，再用EXPLAIN验证，确认执行计划变好了。
  5. 在测试环境测试，确认优化后结果正确，性能提升。
  6. 上线后，观察慢查询日志，确认这个SQL不再慢了。

慢查询优化是一个持续的过程，建议定期（比如每周）分析一次慢查询日志，及时发现和优化慢SQL。

七、锁和事务：理解并发控制

MySQL是多用户并发访问的数据库，锁和事务是并发控制的基础。理解锁和事务，能帮你写出更高效、更安全的SQL，避免死锁和并发问题。

事务的ACID特性。

事务是一组SQL操作，要么全部成功，要么全部失败。事务有四个特性（ACID）：

  • 原子性（Atomicity）：事务中的操作要么全部成功，要么全部失败回滚。
  • 一致性（Consistency）：事务执行前后，数据保持一致状态。
  • 隔离性（Isolation）：多个事务并发执行时，互不干扰。
  • 持久性（Durability）：事务提交后，数据永久保存，即使宕机也不会丢失。

InnoDB支持事务，MyISAM不支持事务，所以一般都用InnoDB。

事务的隔离级别。

SQL标准定义了四种事务隔离级别：

  1. 读未提交（Read Uncommitted）：能读到其他事务未提交的数据。会有脏读、不可重复读、幻读问题。一般不用。
  2. 读已提交（Read Committed，RC）：只能读到其他事务已提交的数据。解决了脏读，还有不可重复读和幻读问题。Oracle、PostgreSQL等数据库的默认级别。
  3. 可重复读（Repeatable Read，RR）：同一个事务内，多次读取同一数据结果一致。解决了脏读和不可重复读，还有幻读问题。MySQL InnoDB的默认级别，InnoDB用间隙锁（Next-Key Lock）在RR级别下解决了幻读问题。
  4. 串行化（Serializable）：事务串行执行，完全隔离。解决了所有问题，但性能最差，并发低。一般不用。

MySQL默认是可重复读（RR），这个级别已经能满足大部分业务需求了。如果有特殊需求，可以调整隔离级别，但一般不建议改默认级别。

InnoDB的锁类型。

InnoDB的锁分为很多种：

  • 按粒度分：表锁、行锁、间隙锁、Next-Key Lock（行锁+间隙锁）
  • 按模式分：共享锁（S锁，读锁）、排他锁（X锁，写锁）
  • 按意向分：意向共享锁（IS）、意向排他锁（IX）

简单理解：

  • 共享锁（S锁）：读操作加的锁，多个事务可以同时加S锁，互斥X锁。
  • 排他锁（X锁）：写操作加的锁，只有一个事务能加X锁，互斥S锁和X锁。
  • 行锁：锁住某一行，并发度高。
  • 间隙锁：锁住索引之间的间隙，防止幻读。
  • Next-Key Lock：行锁+间隙锁，锁住行和前面的间隙，InnoDB默认的行锁算法。

什么时候会加锁？

  • SELECT ... FOR UPDATE：加X锁
  • SELECT ... LOCK IN SHARE MODE：加S锁
  • INSERT、UPDATE、DELETE：加X锁
  • 普通SELECT：不加锁（快照读，MVCC）

注意，UPDATE和DELETE的时候，如果WHERE条件没有索引，会导致全表扫描，锁住所有行，相当于表锁，并发性能很差。所以，更新和删除操作一定要在WHERE条件的字段上建索引。

死锁。

死锁是指两个或多个事务互相等待对方释放锁，导致都无法继续执行。

死锁的四个必要条件：互斥、持有并等待、不可剥夺、循环等待。

避免死锁的方法：

  1. 加锁顺序一致：所有事务都按相同的顺序加锁，避免循环等待。
  2. 尽量缩短事务：事务越短，锁持有时间越短，死锁概率越低。
  3. 避免大事务：大事务持有锁时间长，容易死锁。
  4. 降低隔离级别：RC级别比RR级别锁少，死锁概率低。
  5. 为查询建索引：避免全表扫描锁全表。
  6. 用乐观锁代替悲观锁：比如用版本号字段，更新的时候检查版本号。

如果发生死锁，MySQL会自动回滚其中一个事务，另一个事务继续执行。应用层需要捕获死锁异常，重试事务。

MVCC（多版本并发控制）。

MVCC是InnoDB实现隔离级别的机制，它通过保存数据的多个版本，让读操作不加锁，读写不冲突，提高并发性能。

简单理解：

  • 每行数据有隐藏字段：事务ID（trxid）、回滚指针（rollpointer）。
  • 每次修改数据，都会生成一个新版本，旧版本保存在undo log中。
  • 读操作的时候，根据事务的隔离级别，读取合适的版本。
  • 快照读（普通SELECT）：读的是快照版本，不加锁。
  • 当前读（SELECT ... FOR UPDATE、INSERT、UPDATE、DELETE）：读的是最新版本，加锁。

MVCC让读写不冲突，大大提高了并发性能。这也是InnoDB比MyISAM并发性能好的重要原因。

八、MySQL 8.0的新特性

最后，简单介绍一下MySQL 8.0的一些重要新特性，这些特性能帮助我们更好地优化数据库。

1. 窗口函数。

MySQL 8.0支持了窗口函数（Window Function），比如ROWNUMBER()、RANK()、DENSERANK()、SUM() OVER()等。窗口函数能解决很多以前需要用子查询或者变量才能解决的问题，比如分组取Top N、累计求和、排名等，SQL更简洁，性能也更好。

2. CTE（公共表表达式）。

MySQL 8.0支持了CTE（Common Table Expression），用WITH关键字定义临时结果集，可以在查询中多次引用。CTE让复杂查询更清晰，可读性更好，而且递归CTE能解决层级查询的问题（比如组织架构、树形结构）。

3. 降序索引。

MySQL 8.0支持了降序索引，索引中的字段可以指定降序排列。以前的索引都是升序的，ORDER BY DESC的时候需要反向扫描，或者文件排序。有了降序索引，混合排序（比如ORDER BY a ASC, b DESC）也能用到索引了。

4. 隐藏索引。

MySQL 8.0支持了隐藏索引（Invisible Index），可以把索引设为隐藏，优化器不会使用这个索引，但索引还是正常维护的。这个功能很有用，你想删除一个索引，但又不确定删除后会不会有影响，可以先把它设为隐藏，观察一段时间，如果没有问题再真正删除。如果有问题，再设为可见即可，不用重建索引。

5. 直方图。

MySQL 8.0支持了直方图（Histogram），可以对字段的数据分布做统计，帮助优化器更准确地估算查询的行数，选择更优的执行计划。尤其是对于区分度低的字段（比如性别、状态），直方图能让优化器更准确地判断查询的行数。

6. 更快的DDL。

MySQL 8.0对DDL做了很多优化，比如ALTER TABLE ... ADD COLUMN可以瞬间完成（INSTANT算法），不需要拷贝表，不影响在线业务。以前加字段需要拷贝表，大表加字段要很久，还会锁表，现在瞬间就能完成。

7. 性能提升。

MySQL 8.0在性能上有很大提升，比如查询优化器改进、InnoDB性能提升、连接管理优化等。官方测试显示，MySQL 8.0比MySQL 5.7在高并发下性能提升很多。

8. 其他新特性。

  • 角色（Role）管理：可以创建角色，给角色授权，再把角色赋给用户，权限管理更方便。
  • 资源组（Resource Group）：可以把线程分配到不同的CPU核，控制资源使用。
  • 增强的JSON功能：MySQL 8.0对JSON类型做了很多增强，支持更多JSON函数。
  • 二进制日志的改进：binlog的性能和安全性提升。

MySQL 8.0是一个里程碑式的版本，有很多实用的新特性和性能提升。如果还在用MySQL 5.7或者更早的版本，建议升级到MySQL 8.0。

九、写在最后

MySQL优化是一个很大的话题，本文只是入门指南，介绍了最核心的知识：索引优化、SQL优化、表结构设计、配置优化、慢查询分析、锁和事务。这些是MySQL优化的基础，掌握了这些，就能解决大部分性能问题。

MySQL优化的核心思路是：先定位问题，再优化；优先优化SQL和索引，其次是表结构，最后才是配置和硬件；优化是持续的过程；不要过度优化，要权衡收益和成本。

索引是MySQL优化的核心，要理解B+树索引、聚簇索引和非聚簇索引、最左前缀原则，知道什么时候建索引，什么时候不建索引，以及索引使用的注意事项。EXPLAIN是分析SQL执行计划最重要的工具，一定要学会用。

SQL优化要注意：只查询需要的字段，避免SELECT *；分页查询优化；避免在索引字段上做运算或函数；批量操作；避免大事务；合理使用LIMIT；排序和分组优化。

表结构设计要选择合适的数据类型，合理设计主键和字段，注意字符集和排序规则，数据量大的时候考虑分表。

配置优化要根据服务器硬件和业务特点调整，最重要的是innodbbufferpool_size，其他配置根据需要调整。不要盲目照搬网上的配置。

慢查询日志是找出需要优化的SQL的重要工具，要开启慢查询日志，定期分析，及时优化慢SQL。

锁和事务是并发控制的基础，要理解事务的ACID特性、隔离级别、InnoDB的锁类型、死锁的避免，以及MVCC的原理。

最后，MySQL优化是一个实践的过程，光看理论是不够的，要在实际工作中多练习、多分析、多总结。遇到性能问题，不要慌，按照定位问题、分析原因、优化验证的步骤来，一步步解决。

希望这篇入门指南能帮你打开MySQL优化的大门，让你写出更高效的SQL，搭建更稳定的数据库。

最后用一句话结束本文："MySQL优化没有银弹，只有不断地学习、实践和总结。"愿我们都能成为数据库优化的高手，让我们的应用又快又稳。