MySQL性能优化：让你的数据库飞起来

数据库，是Web应用的核心，也是最常见的性能瓶颈。

一个性能好的数据库，查询速度快，并发能力强，能支撑大量用户的访问；一个性能差的数据库，查询速度慢，并发能力弱，稍微有点流量，就会卡顿，甚至崩溃。数据库的性能，直接决定了Web应用的性能上限。

MySQL，作为最流行的开源关系型数据库，被广泛应用于各种Web应用中。但很多开发者，对MySQL的性能优化，了解不多，只会写简单的SQL，不知道如何优化查询，如何设计表结构，如何配置服务器，导致数据库性能很差，影响了整个应用的性能。

我自己，做了多年Web开发，和MySQL打了很多年交道。从最初的只会写简单的增删改查，到后来关注SQL优化，再到现在能够系统地进行数据库性能优化，一路走来，踩了不少坑，也总结了不少方法。我见过太多的应用，因为数据库设计不合理、SQL写得差、没有索引、配置不当，而导致性能问题，其实，只要稍微优化一下，性能就能提升好几倍，甚至几十倍。

2015年，MySQL 5.6已经非常成熟，MySQL 5.7也已经发布（2015年10月发布GA版），性能有了很大提升。InnoDB存储引擎，已经成为默认的存储引擎，取代了MyISAM。各种性能优化工具和方法，也已经非常成熟。

今天，分享MySQL性能优化，从SQL优化、索引优化、表结构设计、配置优化、架构优化等多个维度，全方位讲解MySQL性能优化的方法和技巧，帮你让你的数据库飞起来。

性能优化的原则

在讲具体的优化方法之前，先讲几个数据库性能优化的基本原则。

1. 先测量，再优化

和PHP性能优化一样，数据库优化，也不能靠猜测，而要靠数据。

在优化之前，先用工具测量，找到性能瓶颈在哪里。是SQL查询慢？还是索引不合理？还是表结构设计有问题？还是服务器配置不当？还是并发太高？

只有找到了真正的瓶颈，才能有针对性地优化，事半功倍。如果靠猜测优化，可能会优化了不该优化的地方，浪费时间和精力，甚至可能引入新的问题。

常用的MySQL性能测量工具：

• 慢查询日志（Slow Query Log）：记录执行时间超过指定阈值的SQL，是找到慢查询的最常用工具
• EXPLAIN：分析SQL的执行计划，查看索引使用情况、扫描行数、连接类型等
• SHOW PROFILE：分析SQL执行的各个阶段的耗时
• Performance Schema：MySQL的性能监控架构，可以监控各种性能指标
• MySQL Workbench：MySQL官方的图形化工具，有性能监控和分析功能
• pt-query-digest：Percona Toolkit中的慢查询分析工具，能分析慢查询日志，找出最耗时的SQL
• New Relic、Datadog等APM工具：应用性能监控工具，可以监控数据库查询性能

2. 优化瓶颈，而不是全部

数据库优化，也要遵循"二八定律"：80%的性能问题，来自20%的SQL或表。

所以，优化的时候，要聚焦于那20%的瓶颈SQL和表，而不是全部。优化瓶颈，能以最小的成本，获得最大的性能提升。

比如，如果一个网站，80%的数据库时间，都花在几个慢查询上，那么优化这几个慢查询，就能获得最大的性能提升；而如果去优化那些只占5%时间的简单查询，效果就很有限。

3. 权衡优化的成本和收益

数据库优化，也是有成本的。有些优化，需要修改大量SQL和表结构，增加复杂度，但性能提升有限；有些优化，只需要简单修改，就能获得显著的性能提升。

所以，优化的时候，要权衡成本和收益。优先做那些成本低、收益高的优化；对于成本高、收益低的优化，可以考虑不做，或者延后做。

比如，加索引，通常是成本低、收益高的优化，应该优先做；而分库分表，是成本高、复杂度高的优化，应该在确实需要的时候才做。

4. 优化后要验证

优化完成后，要再次测量，验证优化是否有效，是否引入了新的问题。

有些优化，可能在测试环境有效，但在生产环境无效；有些优化，可能提升了查询性能，但降低了写入性能；有些优化，可能引入了新的bug。

所以，优化后，一定要验证，确保优化是有效的、安全的。

5. 不要过度优化

不要为了一点点性能提升，而把数据库设计搞得很复杂，降低可维护性。比如，为了减少一次查询，而把数据冗余到很多表中，导致数据不一致的风险增加；为了提升查询速度，而加太多索引，导致写入性能下降。

性能优化，要权衡成本和收益，在性能和可维护性之间，找到平衡点。

SQL优化

SQL优化，是数据库性能优化中，最常见，也是最有效的优化方式。很多时候，一条写得差的SQL，可能会比写得好的SQL，慢几十倍，甚至几百倍。

1. 避免SELECT *

SELECT 会查询所有字段，即使不需要的字段也会查询，浪费内存和网络带宽。而且，SELECT 无法使用覆盖索引，可能会导致回表查询，性能更差。

只查询需要的字段，能减少数据传输，提升性能，也更容易使用覆盖索引。

不好的写法：

SELECT * FROM users WHERE id = 1;
SELECT * FROM posts WHERE category_id = 1;

好的写法：

SELECT id, name, email FROM users WHERE id = 1;
SELECT id, title, created_at FROM posts WHERE category_id = 1;

2. 用EXISTS代替IN

在某些情况下，EXISTS比IN性能更好，特别是当子查询的结果集很大时。

EXISTS是找到一条匹配的记录就返回，不需要扫描全部结果；而IN需要先执行子查询，得到全部结果，再进行匹配。

不好的写法：

SELECT * FROM posts WHERE user_id IN (SELECT id FROM users WHERE status = 1);

好的写法：

SELECT * FROM posts p WHERE EXISTS (SELECT 1 FROM users u WHERE u.id = p.user_id AND u.status = 1);

注意：这不是绝对的，在某些情况下，IN可能比EXISTS好。要根据实际情况，用EXPLAIN分析，选择性能更好的写法。MySQL 5.6及以上版本，对IN子查询有优化，性能已经不错了。

3. 避免在WHERE子句中对字段进行函数操作或运算

在WHERE子句中，对索引字段进行函数操作或运算，会导致索引失效，全表扫描。

不好的写法：

-- 对字段使用函数，索引失效
SELECT * FROM posts WHERE YEAR(created_at) = 2015;
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
-- 对字段进行运算，索引失效
SELECT * FROM orders WHERE price * 1.1 > 100;

好的写法：

-- 用范围查询代替函数
SELECT * FROM posts WHERE created_at >= '2015-01-01' AND created_at < '2016-01-01';
-- 存储时就存小写，查询时用小写
SELECT * FROM users WHERE email = 'test@example.com';
-- 把运算放到常量那边
SELECT * FROM orders WHERE price > 100 / 1.1;

4. 避免模糊查询的前置通配符

LIKE查询中，如果通配符%在前面，会导致索引失效，全表扫描。

不好的写法：

-- 前置通配符，索引失效
SELECT * FROM users WHERE name LIKE '%张%';
SELECT * FROM posts WHERE title LIKE '%MySQL%';

好的写法：

-- 后置通配符，可以使用索引
SELECT * FROM users WHERE name LIKE '张%';
SELECT * FROM posts WHERE title LIKE 'MySQL%';

如果确实需要全文模糊查询，应该使用全文索引（FULLTEXT INDEX），或者使用搜索引擎（如Elasticsearch、Sphinx），而不是用LIKE '%xxx%'。

5. 避免OR连接多个条件

OR连接多个条件时，如果其中有一个条件没有索引，就会导致全表扫描。

不好的写法：

SELECT * FROM posts WHERE category_id = 1 OR user_id = 2;

好的写法：

-- 用UNION代替OR
SELECT * FROM posts WHERE category_id = 1
UNION
SELECT * FROM posts WHERE user_id = 2;

或者，给所有OR条件的字段都加上索引，MySQL也可能会使用索引合并（index merge）。但用UNION，通常更可靠。

6. 用UNION ALL代替UNION

UNION会对结果集进行去重，需要排序和比较，性能较差；而UNION ALL不去重，直接合并结果集，性能更好。

如果确定两个结果集没有重复数据，应该用UNION ALL代替UNION。

不好的写法：

SELECT id, title FROM posts WHERE category_id = 1
UNION
SELECT id, title FROM posts WHERE user_id = 2;

好的写法：

SELECT id, title FROM posts WHERE category_id = 1
UNION ALL
SELECT id, title FROM posts WHERE user_id = 2;

7. 避免子查询，尽量用JOIN

在MySQL中，子查询的性能，通常不如JOIN。特别是相关子查询（子查询中引用了外层查询的字段），性能很差。

不好的写法：

-- 相关子查询，性能差
SELECT p.*, (SELECT name FROM users u WHERE u.id = p.user_id) AS user_name
FROM posts p;

好的写法：

-- 用JOIN代替子查询
SELECT p.*, u.name AS user_name
FROM posts p
LEFT JOIN users u ON u.id = p.user_id;

注意：MySQL 5.6及以上版本，对子查询有优化，会自动把某些子查询转换为JOIN，性能已经不错了。但用JOIN，通常更直观，也更容易优化。

8. 分页优化

大数据量的分页，LIMIT offset, count会越来越慢，因为MySQL需要扫描offset条记录，然后丢弃。

不好的写法：

-- offset越大越慢
SELECT * FROM posts ORDER BY id LIMIT 100000, 10;

好的写法：

-- 游标分页（基于上一页的最后一条记录的ID）
SELECT * FROM posts WHERE id < 100000 ORDER BY id DESC LIMIT 10;

-- 延迟关联（先查ID，再关联查询详情）
SELECT p.* FROM posts p
INNER JOIN (SELECT id FROM posts ORDER BY id LIMIT 100000, 10) t ON p.id = t.id;

另外，要限制最大页数，不允许翻到太后面。大多数用户，只会看前几页，不需要翻到第10000页。

9. 批量操作

批量插入、批量更新、批量删除，比逐条操作，性能好很多。因为逐条操作，每次都要开启事务、写日志、刷新磁盘，开销很大；而批量操作，只需要一次事务，一次日志，一次磁盘刷新，开销小很多。

不好的写法：

// 逐条插入，性能差
foreach ($users as $user) {
    $pdo->query("INSERT INTO users (name, email) VALUES ('$user[name]', '$user[email]')");
}

好的写法：

// 批量插入，性能好
$values = [];
foreach ($users as $user) {
    $values[] = "('" . $pdo->quote($user['name']) . "', '" . $pdo->quote($user['email']) . "')";
}
$sql = "INSERT INTO users (name, email) VALUES " . implode(',', $values);
$pdo->query($sql);

注意：批量操作时，要注意SQL的长度，不要一次插入太多数据，导致SQL过长。可以分批，每批100-1000条。

10. 使用预处理语句

预处理语句（Prepared Statements），不仅能防止SQL注入，还能提升性能。

预处理语句，会把SQL语句发送给MySQL编译，然后可以多次执行，只需要传递参数。对于多次执行的相同SQL，预处理语句能减少MySQL编译的开销，提升性能。

而且，预处理语句，能减少SQL注入的风险，是更安全的写法。

示例：

// 使用PDO预处理
$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
foreach ($users as $user) {
    $stmt->execute([':name' => $user['name'], ':email' => $user['email']]);
}

索引优化

索引，是数据库查询优化的最重要手段。合适的索引，能让查询速度提升几个数量级；而不合适的索引，不仅不能提升性能，还会降低写入性能，浪费存储空间。

1. 索引的类型

主键索引（PRIMARY KEY）：主键自动创建索引，唯一且非空。一张表只能有一个主键索引。

唯一索引（UNIQUE）：值唯一，可以为空。一张表可以有多个唯一索引。

普通索引（INDEX/KEY）：最基本的索引，没有唯一性限制。

联合索引（复合索引）：多个字段组成的索引，遵循"最左前缀原则"。

全文索引（FULLTEXT）：用于全文搜索，支持自然语言搜索和布尔搜索。

2. 索引的使用原则

在WHERE、JOIN、ORDER BY、GROUP BY的字段上建索引：这些字段，是查询中经常用到的字段，建索引能显著提升查询性能。

区分度高的字段，适合建索引：区分度，是指不同值的数量占总记录数的比例。区分度越高，索引的效果越好。比如，用户ID、邮箱、手机号等，区分度很高，适合建索引；而性别、状态等，只有几个值，区分度很低，不适合建索引。

联合索引，把区分度高的字段放前面：联合索引，遵循最左前缀原则，查询时，会从最左边的字段开始匹配。把区分度高的字段放前面，能更快地过滤数据。

不要过度建索引：索引，会增加写入的开销（INSERT、UPDATE、DELETE时，需要更新索引），也会占用存储空间。一张表，索引不是越多越好，一般3-5个索引比较合适。

定期检查索引使用情况，删除未使用的索引：随着业务的变化，有些索引可能不再使用了，应该及时删除，减少写入开销和存储空间。

3. 最左前缀原则

联合索引，遵循最左前缀原则。比如，有一个联合索引(a, b, c)，那么以下查询，能使用索引：

• WHERE a = 1
• WHERE a = 1 AND b = 2
• WHERE a = 1 AND b = 2 AND c = 3
• WHERE a = 1 AND c = 3（只能用到a的索引，c用不到）

而以下查询，不能使用索引：

• WHERE b = 2（没有最左边的a）
• WHERE c = 3（没有最左边的a）
• WHERE b = 2 AND c = 3（没有最左边的a）

所以，建联合索引时，要把查询中最常用的字段，放最左边。

4. 覆盖索引

如果一个索引，包含了查询需要的所有字段，那么MySQL就不需要回表查询数据，直接从索引中就能获取所有数据，这就是覆盖索引。覆盖索引，能显著提升查询性能。

示例：

-- 有联合索引 (category_id, created_at, title)
SELECT id, title, created_at FROM posts WHERE category_id = 1 ORDER BY created_at DESC;

这个查询，需要的字段（id、title、created_at），都在索引中（id是主键，InnoDB中主键会自动包含在二级索引中），所以MySQL可以直接从索引中获取数据，不需要回表，性能很好。

要使用覆盖索引，就要避免SELECT *，只查询需要的字段，并且让这些字段，都包含在索引中。

5. 索引失效的场景

以下场景，会导致索引失效：

• 在索引字段上使用函数或运算（如YEAR(created_at)、price * 1.1）
• 模糊查询前置通配符（如LIKE '%xxx%'）
• 隐式类型转换（如字符串字段用数字查询，或数字字段用字符串查询）
• OR连接的条件中，有一个没有索引
• 联合索引不满足最左前缀原则
• 使用!=、<>、NOT IN等负向查询（不一定失效，但优化器可能选择全表扫描）
• MySQL优化器认为全表扫描比索引更快（如小表，或查询返回大部分数据）

6. 查看索引使用情况

用EXPLAIN分析SQL的执行计划，查看索引使用情况：

EXPLAIN SELECT * FROM posts WHERE category_id = 1 ORDER BY created_at DESC;

关键字段：

• type：连接类型，从好到差：system > const > eq_ref > ref > range > index > ALL。ALL是全表扫描，需要优化。
• key：实际使用的索引。如果为NULL，说明没有使用索引。
• rows：扫描的行数。越少越好。
• Extra：额外信息。Using index表示使用了覆盖索引；Using where表示使用了WHERE过滤；Using filesort表示需要额外排序，需要优化；Using temporary表示使用了临时表，需要优化。

表结构设计优化

表结构设计，是数据库性能优化的基础。一个设计合理的表结构，能从根本上提升性能；而一个设计不合理的表结构，即使SQL和索引优化得再好，性能也有限。

1. 选择合适的数据类型

选择合适的数据类型，能节省存储空间，提升查询性能。

整数类型：

• TINYINT：1字节，范围-128到127（或0到255无符号），适合状态、年龄等小整数
• SMALLINT：2字节，范围-32768到32767，适合较小的整数
• MEDIUMINT：3字节，范围-8388608到8388607
• INT：4字节，范围-21亿到21亿，最常用的整数类型
• BIGINT：8字节，范围很大，适合主键、大整数

选择最小的、能满足需求的数据类型。比如，状态字段，用TINYINT就够了，不要用INT；用户ID，如果不会超过21亿，用INT就够了，不要用BIGINT。

字符串类型：

• CHAR(n)：定长字符串，最多255字符。查询速度快，但浪费空间。适合长度固定的字段，如MD5值、邮编。
• VARCHAR(n)：变长字符串，最多65535字节。节省空间，但查询稍慢。适合长度变化的字段，如标题、名称。
• TEXT：大文本，最多65535字节。不能有默认值，不能全部索引。适合长文本，如文章内容。
• MEDIUMTEXT：中等文本，最多16MB。
• LONGTEXT：长文本，最多4GB。

VARCHAR的长度，要根据实际需求设置，不要设置得太大。比如，标题字段，用VARCHAR(200)就够了，不要用VARCHAR(1000)。

日期时间类型：

• DATE：日期，3字节，格式'YYYY-MM-DD'
• TIME：时间，3字节，格式'HH:MM:SS'
• DATETIME：日期时间，8字节，格式'YYYY-MM-DD HH:MM:SS'，范围'1000-01-01'到'9999-12-31'
• TIMESTAMP：时间戳，4字节，格式'YYYY-MM-DD HH:MM:SS'，范围'1970-01-01'到'2038-01-19'，会自动转换时区

2015年，推荐用DATETIME，因为范围更大，不受2038年限制。TIMESTAMP因为有2038年问题，不推荐使用（虽然MySQL 5.6之后有改进，但还是有隐患）。

其他类型：

• DECIMAL(m, d)：精确小数，适合金额、价格等需要精确计算的字段。不要用FLOAT或DOUBLE存金额，因为会有精度问题。
• ENUM：枚举类型，适合状态、类型等只有几个固定值的字段。但要注意，修改ENUM的值，需要ALTER TABLE，在大数据量表上会锁表。
• BOOLEAN：布尔类型，实际上是TINYINT(1)，0表示false，非0表示true。

2. 尽量使用NOT NULL

尽量把字段设置为NOT NULL，并设置默认值。

NULL字段，会占用更多的存储空间（InnoDB中，NULL需要额外的1位来标记），也会让索引、比较、排序更复杂，影响性能。而且，NULL在查询时，容易出问题（如WHERE field = NULL永远不会匹配，要用WHERE field IS NULL）。

所以，除非确实需要NULL（如可选字段），否则，尽量用NOT NULL，并设置默认值。比如，状态字段，默认0；计数字段，默认0；字符串字段，默认空字符串。

3. 合理使用范式和反范式

范式化设计，能减少数据冗余，保证数据一致性，但可能会导致多表JOIN，影响查询性能。

反范式化设计，通过冗余数据，减少JOIN，提升查询性能，但会增加数据冗余，可能导致数据不一致，也会增加写入的开销。

在实际应用中，要权衡范式和反范式，根据业务需求，选择合适的设计。

• 经常需要JOIN查询的字段，可以考虑冗余到主表中，减少JOIN。比如，文章表中，冗余用户名和头像，不需要每次都JOIN用户表。
• 但冗余字段，要注意数据一致性。如果用户修改了用户名，需要同步更新所有文章表中的冗余用户名。可以用触发器、应用层同步、或定时任务来保证一致性。
• 对于经常变化的字段，不适合冗余，因为同步更新的成本太高。

4. 大表拆分

当一张表的数据量很大（如超过1000万条），查询和写入性能，都会下降。这时候，可以考虑拆分表。

水平拆分（分表）：把一张表的数据，按照某个维度（如ID范围、时间、哈希），拆分到多张表中。每张表的结构相同，数据不同。

比如，文章表，可以按ID范围拆分：posts01000万、posts1000万2000万；也可以按时间拆分：posts2015、posts2016；也可以按用户ID哈希拆分：posts0到posts63（64张表）。

水平拆分，能减少单表的数据量，提升查询和写入性能。但会增加应用层的复杂度（需要路由到正确的表），跨表查询和聚合也比较麻烦。

垂直拆分：把一张表的字段，拆分到多张表中。把不常用的、大的字段，拆分到单独的表中。

比如，文章表，可以把文章内容（content，大字段）拆分到postcontents表中，文章主表只存id、title、excerpt、categoryid等常用字段。这样，主表更小，查询更快；只有在查看文章详情时，才去查询内容表。

垂直拆分，能减少单表的字段数和数据量，提升常用查询的性能。但会增加JOIN的需求。

5. 避免在数据库中存储大文件

不要在数据库中存储大文件（如图片、视频、文档）。大文件，会让表变得很大，查询很慢，备份和恢复也很麻烦。

应该把大文件，存储在文件系统或对象存储（如阿里云OSS、腾讯云COS、七牛云）中，数据库中只存储文件的路径或URL。

配置优化

MySQL的配置，对性能影响很大。合理的配置，能充分利用服务器资源，提升性能。

1. InnoDB缓冲池（innodbbufferpool_size）

这是InnoDB最重要的配置参数。缓冲池，是InnoDB用来缓存表数据和索引的内存区域。缓冲池越大，能缓存的数据就越多，磁盘IO就越少，性能就越好。

建议：设置为物理内存的50%-70%。如果服务器只跑MySQL，可以设置为70%-80%。

比如，服务器有8G内存，可以设置innodbbufferpoolsize = 5G；有16G内存，可以设置innodbbufferpoolsize = 10G。

注意：不要设置得太大，要给操作系统和其他进程，留出足够的内存。

2. InnoDB日志文件（innodblogfile_size）

InnoDB日志文件（redo log），用于崩溃恢复。日志文件越大，崩溃恢复的时间越长，但需要刷盘的次数越少，写入性能越好。

建议：设置为256M-1G。MySQL 5.6默认是48M，太小了，建议调大。

比如，innodblogfile_size = 256M或512M。

注意：修改这个参数，需要先停止MySQL，删除旧的日志文件（iblogfile0、iblogfile1），再启动MySQL，否则会报错。

3. InnoDB日志缓冲（innodblogbuffer_size）

日志缓冲，是InnoDB用来缓存redo log的内存区域。缓冲越大，在事务提交前，需要刷盘的次数越少，写入性能越好。

建议：设置为8M-64M。默认是8M，对于大多数应用足够了。如果有很多大事务，可以调大到16M或32M。

4. 事务提交刷盘策略（innodbflushlogattrx_commit）

这个参数，控制事务提交时，redo log的刷盘策略，对写入性能和数据安全，影响很大。

• 0：每秒刷一次盘，事务提交时不刷盘。性能最好，但崩溃时可能丢失1秒的数据。
• 1：每次事务提交都刷盘。最安全，不会丢失数据，但性能最差。默认值。
• 2：每次事务提交都写到操作系统缓冲区，每秒刷一次盘。性能较好，崩溃时（MySQL崩溃但操作系统不崩溃）不会丢失数据，但操作系统崩溃可能丢失1秒数据。

建议：

• 如果对数据安全要求很高（如金融、支付），用1。
• 如果对性能要求高，能接受少量数据丢失（如博客、论坛），用2。
• 0不推荐，因为MySQL崩溃就可能丢数据。

5. 每个表独立表空间（innodbfileper_table）

这个参数，控制InnoDB是否为每张表创建独立的表空间文件（.ibd文件）。

• ON：每张表有独立的.ibd文件。可以回收空间（TRUNCATE或DROP表时，空间会还给操作系统），也方便迁移和备份。
• OFF：所有表的数据，都存在共享表空间（ibdata1）中。表删除后，空间不会还给操作系统，ibdata1会越来越大。

建议：设置为ON。MySQL 5.6及以上版本，默认就是ON。

6. 连接数（max_connections）

最大连接数，控制MySQL同时接受的最大客户端连接数。

建议：根据服务器配置和应用需求设置。一般设置为100-1000。

不要设置得太大，因为每个连接，都会占用一定的内存（每个连接约占1M-几M内存），连接太多，可能会导致内存不足。

可以用SHOW STATUS LIKE 'Maxusedconnections'查看历史最大连接数，根据这个值，合理设置max_connections。

7. 排序缓冲（sortbuffersize）

排序缓冲，用于ORDER BY和GROUP BY的排序。如果排序的数据，能放在排序缓冲中，就可以在内存中排序，不需要使用临时文件（filesort），性能更好。

建议：设置为256K-4M。默认是256K。不要设置得太大，因为每个连接，都会分配独立的sort_buffer，连接多时，会占用大量内存。

8. 临时表大小（tmptablesize和maxheaptable_size）

这两个参数，控制内存临时表的大小。如果临时表的大小，超过了这个值，就会把临时表，写到磁盘上，性能下降。

建议：设置为64M-256M。两个参数，要设置成一样的值。

比如，tmptablesize = 128M，maxheaptable_size = 128M。

9. 查询缓存（querycachetype和querycachesize）

查询缓存，会缓存SELECT查询的结果，下次相同的查询，直接返回缓存结果，不需要执行SQL。

但是，查询缓存，有很多问题：

• 只要表有任何更新（INSERT、UPDATE、DELETE），这个表的所有查询缓存，都会失效。对于更新频繁的表，查询缓存命中率很低，反而会影响性能。
• 查询缓存，需要额外的内存和CPU开销。
• MySQL 8.0已经移除了查询缓存。

建议：2015年，MySQL 5.6/5.7，可以开启查询缓存，但要评估命中率。如果命中率低，就关闭。对于大多数Web应用（更新频繁），建议关闭查询缓存。

query_cache_type = 0
query_cache_size = 0

10. 字符集

推荐使用utf8mb4字符集，支持完整的Unicode（包括emoji表情）。

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'

注意：不要用utf8，因为MySQL的utf8不是真正的UTF-8，最多只支持3字节，不支持emoji等4字节字符。要用utf8mb4。

架构优化

当单台MySQL服务器，无法满足性能需求时，就需要进行架构优化。

1. 主从复制

主从复制，是把一台MySQL服务器（主库）的数据，复制到另一台或多台MySQL服务器（从库）。

主库，负责写操作（INSERT、UPDATE、DELETE）；从库，负责读操作（SELECT）。通过读写分离，提升读性能。

优点：

• 读写分离，提升读性能
• 数据备份，从库可以作为备份
• 高可用，主库挂了，可以提升从库为主库

缺点：

• 主从延迟，从库的数据，可能比主库晚一点
• 增加架构复杂度
• 需要应用层支持读写分离

2. 读写分离

在主从复制的基础上，应用层把写操作，路由到主库；把读操作，路由到从库。

可以用中间件（如MySQL Proxy、Atlas、MyCat、ProxySQL）来实现读写分离，也可以在应用层自己实现。

注意：对于实时性要求高的读操作（如刚写完就需要读），应该走主库，避免主从延迟的问题。

3. 分库分表

当单库单表的数据量太大，无法满足性能需求时，就需要分库分表。

分库：把一个库的数据，拆分到多个库中。可以按业务模块分库（如用户库、订单库、商品库），也可以按某个维度分库（如按用户ID哈希）。

分表：把一张表的数据，拆分到多张表中。前面表结构设计部分，已经讲过水平拆分和垂直拆分。

分库分表，能显著提升性能和容量，但会大大增加架构复杂度和应用层复杂度。跨库JOIN、分布式事务、全局ID、跨库查询聚合等，都是需要解决的问题。

只有在单库单表确实无法满足需求时，才考虑分库分表。不要过早分库分表，因为复杂度太高。

4. 引入缓存

在数据库前面，加一层缓存（如Redis、Memcached），把热点数据，缓存到内存中，减少数据库查询。

缓存，是提升Web应用性能，最有效的手段之一。大多数读多写少的应用，都适合用缓存。

注意：缓存，会带来缓存穿透、缓存雪崩、缓存击穿等问题，需要处理。前面PHP性能优化部分，已经讲过缓存的问题和解决方案。

5. 使用搜索引擎

对于全文搜索、复杂搜索、聚合搜索等场景，MySQL的性能，可能不够。这时候，可以引入搜索引擎（如Elasticsearch、Sphinx、Solr），专门处理搜索。

把数据，同步到搜索引擎中，搜索请求，走搜索引擎，而不是MySQL。这样，能提升搜索性能，也能减轻MySQL的压力。

其他优化技巧

1. 定期优化表

用OPTIMIZE TABLE，整理表碎片，回收空间，提升性能。特别是对于经常有DELETE和UPDATE的表，容易产生碎片。

OPTIMIZE TABLE posts;

注意：OPTIMIZE TABLE会锁表，在大数据量表上，可能需要很长时间。应该在低峰期执行。

2. 定期分析表

用ANALYZE TABLE，更新表的索引统计信息，让优化器能选择更优的执行计划。

ANALYZE TABLE posts;

3. 慢查询日志分析

开启慢查询日志，定期分析，找出慢查询，进行优化。

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1  # 执行时间超过1秒的SQL，记录到慢查询日志
log_queries_not_using_indexes = 1  # 记录没有使用索引的查询

用pt-query-digest分析慢查询日志：

pt-query-digest /var/log/mysql/slow.log

4. 合理使用事务

事务，能保证数据一致性，但也会锁定数据，影响并发性能。

• 事务要尽量短，不要在事务中做耗时的操作（如发送邮件、调用API、循环处理大量数据）
• 选择合适的事务隔离级别，隔离级别越高，并发性能越差
• 避免长事务，长事务会导致undo log膨胀，锁等待时间长
• 批量操作，使用事务，能提升性能（因为每次INSERT都会提交事务，批量操作只提交一次）

5. 避免锁等待

InnoDB使用行级锁，但在某些情况下，会升级为表锁，或者导致锁等待。

• 避免在事务中，更新大量行，会锁定很多行，影响并发
• 避免长时间不提交事务，会导致锁不释放
• 合理使用索引，避免因为没有索引，而导致全表扫描，锁定很多行
• 对于热点行（如库存计数），可以考虑用缓存，减少数据库锁竞争

总结

MySQL性能优化要点：

1. 优化原则：先测量再优化（慢查询日志EXPLAIN SHOW PROFILE Performance Schema pt-query-digest APM工具）、优化瓶颈而不是全部（二八定律80%问题来自20%SQL或表）、权衡成本收益（优先成本低收益高的如加索引，分库分表成本高延后做）、优化后验证（确保有效安全不引入新问题）、不过度优化（性能和可维护性找平衡）
2. SQL优化：避免SELECT （只查需要字段减少传输易使用覆盖索引）、用EXISTS代替IN（EXISTS找到匹配就返回不需要扫描全部，MySQL5.6对IN有优化需用EXPLAIN分析）、避免WHERE中对字段函数操作或运算（YEAR(created_at) LOWER(email) price1.1导致索引失效，用范围查询存储小写运算放常量端）、避免模糊查询前置通配符（LIKE '%xxx%'索引失效，用后置通配符或全文索引搜索引擎）、避免OR连接多条件（一个没索引就全表扫描，用UNION代替或给所有OR字段加索引用索引合并）、用UNION ALL代替UNION（UNION去重需排序比较性能差，确定无重复用UNION ALL）、避免子查询尽量用JOIN（相关子查询性能差，MySQL5.6有优化但JOIN更直观易优化）、分页优化（LIMIT offset大了慢用游标分页基于ID或延迟关联先查ID再关联，限制最大页数）、批量操作（批量插入更新删除比逐条性能好，一次事务一次日志一次磁盘刷新，注意SQL长度分批100-1000条）、使用预处理语句（防注入+性能，多次执行相同SQL减少编译开销）
3. 索引优化：索引类型（主键PRIMARY KEY唯一UNIQUE普通INDEX联合索引全文FULLTEXT）、使用原则（WHERE JOIN ORDER BY GROUP BY字段建索引，区分度高的字段适合如用户ID邮箱手机号，性别状态区分度低不适合，联合索引区分度高放前面，不要过度建索引3-5个合适，定期检查删除未使用索引）、最左前缀原则（联合索引(a,b,c)查询从最左开始匹配，a=1、a=1 AND b=2、a=1 AND b=2 AND c=3能用，b=2、c=3、b=2 AND c=3不能用，常用字段放最左）、覆盖索引（索引包含查询需要的所有字段不需要回表，避免SELECT *只查需要字段让字段包含在索引中）、索引失效场景（字段函数运算、前置通配符、隐式类型转换、OR一个没索引、不满足最左前缀、负向查询!= <> NOT IN不一定失效、优化器认为全表扫描更快如小表或返回大部分数据）、查看索引使用（EXPLAIN分析type从好到差system>const>eq_ref>ref>range>index>ALL，ALL全表扫描需优化；key实际使用索引NULL没使用；rows扫描行数越少越好；Extra中Using index覆盖索引Using filesort额外排序需优化Using temporary临时表需优化）
4. 表结构设计优化：选择合适数据类型（整数TINYINT/SMALLINT/MEDIUMINT/INT/BIGINT选最小满足需求的；字符串CHAR定长快但浪费适合固定长度，VARCHAR变长节省空间适合变化长度，TEXT大文本不能默认值不能全部索引；日期DATETIME 8字节范围大推荐，TIMESTAMP 4字节有2038问题不推荐；DECIMAL精确小数适合金额不要用FLOAT DOUBLE；ENUM适合固定值但修改需ALTER；BOOLEAN=TINYINT(1)）、尽量使用NOT NULL（NULL占更多空间索引比较排序复杂，查询容易出问题=NULL永远不匹配要用IS NULL，除非确实需要否则用NOT NULL设默认值）、合理使用范式和反范式（范式减少冗余保证一致但多JOIN影响性能，反范式冗余减少JOIN提升查询但冗余可能不一致增加写入开销，权衡选择，经常JOIN的字段可冗余如文章表冗余用户名头像，经常变化的字段不适合冗余，用触发器应用层同步定时任务保证一致性）、大表拆分（水平拆分按ID范围时间哈希分表减少单表数据量但增加应用复杂度跨表查询麻烦；垂直拆分把不常用大字段如文章content拆到单独表，主表更小查询更快，查看详情才查内容表但增加JOIN）、避免在数据库存大文件（图片视频文档存文件系统或对象存储OSS COS七牛，数据库只存路径URL）
5. 配置优化：InnoDB缓冲池innodbbufferpoolsize（最重要，物理内存50-70%，只跑MySQL可70-80%，不要太大留内存给OS）、日志文件innodblogfilesize（256M-1G，默认48M太小，修改需停MySQL删旧日志文件）、日志缓冲innodblogbuffersize（8M-64M，默认8M够，大事务可调大）、事务提交刷盘innodbflushlogattrxcommit（0每秒刷性能最好崩溃丢1秒数据不推荐；1每次提交刷最安全性能差默认；2每次提交写OS缓冲每秒刷性能较好MySQL崩溃不丢OS崩溃可能丢，博客论坛用2金融支付用1）、独立表空间innodbfilepertable（ON每张表独立.ibd可回收空间方便迁移备份，MySQL5.6默认ON）、连接数maxconnections（100-1000根据服务器和需求，不要太大每个连接占内存，用Maxusedconnections查看历史最大）、排序缓冲sortbuffersize（256K-4M默认256K，不要太大每个连接独立分配）、临时表大小tmptablesize和maxheaptablesize（64M-256M设一样，超过就写磁盘性能下降）、查询缓存（更新频繁的表命中率低反而影响性能，MySQL8.0已移除，大多数Web应用建议关闭querycachetype=0 querycache_size=0）、字符集用utf8mb4（支持完整Unicode包括emoji，不要用utf8最多3字节不支持4字节）
6. 架构优化：主从复制（主库写从库读，读写分离提升读性能，数据备份高可用，但主从延迟增加复杂度）、读写分离（写路由主库读路由从库，用中间件Atlas MyCat ProxySQL或应用层实现，实时性要求高的读走主库避免延迟）、分库分表（单库单表无法满足时才做，分库按业务模块或维度，分表水平垂直，提升性能容量但大大增加复杂度，跨库JOIN分布式事务全局ID跨库查询都是问题，不要过早分库分表）、引入缓存（Redis Memcached缓存热点数据减少数据库查询，读多写少适合，处理缓存穿透雪崩击穿）、使用搜索引擎（全文复杂聚合搜索用Elasticsearch Sphinx Solr，数据同步到搜索引擎搜索请求走搜索引擎，提升搜索性能减轻MySQL压力）
7. 其他优化：定期优化表OPTIMIZE TABLE（整理碎片回收空间，会锁表低峰期执行）、定期分析表ANALYZE TABLE（更新索引统计信息让优化器选更优执行计划）、慢查询日志分析（开启slowquerylog longquerytime=1 logqueriesnotusingindexes，用pt-query-digest分析找出慢查询优化）、合理使用事务（事务尽量短不在事务中做耗时操作，选合适隔离级别，避免长事务，批量操作用事务提升性能）、避免锁等待（避免事务更新大量行，避免长时间不提交，合理用索引避免全表扫描锁很多行，热点行用缓存减少锁竞争）

MySQL性能优化，是一个系统工程，涉及SQL、索引、表结构、配置、架构等多个方面。没有银弹，需要根据实际情况，测量、分析、优化、验证，持续迭代。

但大多数时候，我们不需要做很复杂的优化，只需要把基础做好：写好SQL、建好索引、设计好表结构、配置好服务器，就能解决80%的性能问题。

"数据库性能，决定了Web应用的性能上限。"希望这篇文章，能帮你掌握MySQL性能优化的方法和技巧，让你的数据库飞起来。

最后，记住：先测量，再优化；优化瓶颈，而不是全部；权衡成本和收益；优化后验证。 这是MySQL性能优化的金科玉律，也是所有性能优化的通用原则。