MySQL是2016年最流行的开源关系型数据库，几乎所有的Web应用都在使用MySQL。但是，随着数据量的增长和访问量的增加，MySQL的性能问题会逐渐显现：查询变慢、响应超时、CPU飙升、连接数满。这时候，MySQL性能优化就显得尤为重要。

MySQL性能优化是一个系统工程，涉及索引、SQL、表结构、配置、缓存、架构等多个层面。很多开发者一遇到性能问题，就想着换硬件、分库分表，但是往往忽略了最基础的索引和SQL优化。实际上，大部分性能问题，通过合理的索引设计和SQL优化就能解决。

这些年，我做过不少MySQL性能优化的工作，从慢查询分析到索引优化，从配置调优到分库分表，积累了不少经验。今天就来系统地讲解MySQL性能优化实战，从基础的索引优化到高级的架构设计，帮助你打造高性能的数据库应用。

一、MySQL性能优化概述

1. 性能优化的层次

MySQL性能优化可以分为以下几个层次，从易到难，从基础到高级：

  1. SQL和索引优化：最基础、最有效、成本最低的优化。80%的性能问题可以通过SQL和索引优化解决。
  2. 表结构优化：合理的数据类型、字段设计、表结构，减少数据冗余，提升查询效率。
  3. 配置优化：调整MySQL配置参数，充分利用硬件资源。
  4. 缓存策略：使用Redis、Memcached等缓存，减少数据库访问。
  5. 读写分离：主从复制，读操作走从库，写操作走主库，提升并发能力。
  6. 分库分表：数据量很大时，水平拆分数据库和表，分散压力。
  7. 硬件升级：增加CPU、内存、SSD，提升硬件性能。

优化的原则是：先做低成本、高收益的优化（SQL和索引），再做高成本、复杂的优化（分库分表、硬件升级）。

2. 性能优化的步骤

  1. 发现问题：通过监控、慢查询日志、用户反馈，发现性能问题。
  2. 定位瓶颈：分析慢查询，使用EXPLAIN查看执行计划，找到性能瓶颈。
  3. 分析原因：分析是索引问题、SQL问题、配置问题还是架构问题。
  4. 制定方案：根据原因制定优化方案。
  5. 实施优化：实施优化方案，注意备份和测试。
  6. 验证效果：优化后验证性能是否提升，是否有副作用。
  7. 持续监控：持续监控性能，及时发现新问题。

3. 性能优化的原则

  • 数据尽量少：查询只返回需要的字段和行数，避免SELECT *和全表扫描。
  • 索引要合理：为常用查询条件建立合适的索引，避免过多索引影响写入性能。
  • 配置要合适：根据硬件和业务特点调整配置，不要盲目调大参数。
  • 缓存要充分：热点数据尽量缓存，减少数据库访问。
  • 架构要合理：根据业务规模选择合适的架构，不要过度设计，也不要设计不足。
  • 优化要持续：性能优化不是一次性的，需要持续监控和优化。

二、索引优化

索引是MySQL性能优化中最重要、最基础的部分。合理的索引能让查询速度提升几个数量级。

1. 索引的类型

MySQL支持多种索引类型：

  • B-Tree索引：最常用的索引类型，InnoDB和MyISAM默认使用。B-Tree索引适合等值查询、范围查询、排序。
  • 哈希索引：基于哈希表，只适合等值查询，不适合范围查询和排序。Memory引擎支持，InnoDB的自适应哈希索引（AHI）是内部实现。
  • 全文索引：用于全文搜索，MyISAM和InnoDB（5.6+）支持。适合CHAR、VARCHAR、TEXT类型。
  • 空间索引：用于地理空间数据类型（GEOMETRY、POINT等），MyISAM支持。
  • 聚簇索引：InnoDB的主键索引就是聚簇索引，索引和数据存储在一起。
  • 二级索引（非聚簇索引）：除了主键索引之外的索引，存储的是主键值，需要回表查询数据。

2. 索引的设计原则

  • 最左前缀原则：联合索引遵循最左前缀原则，查询条件必须从索引的最左列开始，才能使用索引。
  • 选择区分度高的列：区分度高（不同值多）的列适合建索引，如用户ID、邮箱；区分度低（不同值少）的列不适合，如性别、状态。
  • 索引列尽量小：数据类型小的列索引也小，IO效率高。优先用整数，少用字符串。
  • 覆盖索引：索引包含查询需要的所有字段，不需要回表，性能更好。
  • 避免过多索引：每个索引都会占用空间，降低写入性能。一般单表索引不超过5个。
  • 为排序和分组建索引：ORDER BY和GROUP BY的字段如果有索引，可以避免文件排序（filesort）和临时表。

3. 联合索引与最左前缀

联合索引是指多个字段组成的索引。例如，INDEX idxnameage (name, age)。

联合索引遵循最左前缀原则：查询条件必须从索引的最左列开始，才能使用索引。

例如，索引(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 c=3 ❌ 不使用索引
  • WHERE a=1 AND c=3 ⚠️ 只使用a部分，c无法使用索引

注意：MySQL 5.6+支持索引下推（ICP），可以在索引层面过滤条件，减少回表。

4. 索引失效的场景

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

  • 索引列使用函数或运算：WHERE YEAR(create_time)=2016、WHERE id+1=2
  • 隐式类型转换：字符串列用数字查询，如WHERE phone=13800138000（phone是字符串）
  • 使用LIKE以%开头：WHERE name LIKE '%张%'（'张%'可以使用索引）
  • 使用OR连接非索引列：WHERE a=1 OR b=2（如果b没有索引，整个查询不使用索引）
  • NOT IN、!=、<>：可能不使用索引（取决于优化器）
  • IS NOT NULL：可能不使用索引
  • 联合索引不满足最左前缀：如前面所述

5. 覆盖索引

覆盖索引是指索引包含查询需要的所有字段，不需要回表查询数据。

例如，表有索引(name, age)，查询：

SELECT name, age FROM users WHERE name='张三';

这个查询的字段（name, age）都在索引中，不需要回表，就是覆盖索引，性能很好。

但是，如果查询：

SELECT name, age, email FROM users WHERE name='张三';

email不在索引中，需要回表查询，就不是覆盖索引。

利用覆盖索引，可以避免回表，大幅提升查询性能。

6. 索引的查看与维护

-- 查看表的索引
SHOW INDEX FROM table_name;

-- 添加索引
ALTER TABLE table_name ADD INDEX index_name (column1, column2);
ALTER TABLE table_name ADD UNIQUE INDEX index_name (column);
ALTER TABLE table_name ADD PRIMARY KEY (column);
ALTER TABLE table_name ADD FULLTEXT INDEX index_name (column);

-- 删除索引
ALTER TABLE table_name DROP INDEX index_name;
ALTER TABLE table_name DROP PRIMARY KEY;

-- 分析索引（更新索引统计信息）
ANALYZE TABLE table_name;

-- 检查表和索引
CHECK TABLE table_name;

-- 优化表（整理碎片，回收空间）
OPTIMIZE TABLE table_name;

三、SQL查询优化

索引是基础，但是SQL写得不好，有索引也用不上。SQL优化是性能优化的关键环节。

1. 慢查询分析

首先要开启慢查询日志，找到慢SQL：

-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询日志（my.cnf配置）
-- slow_query_log = ON
-- slow_query_log_file = /var/log/mysql/slow.log
-- long_query_time = 2  -- 超过2秒的查询记录
-- log_queries_not_using_indexes = ON  -- 记录未使用索引的查询

-- 临时开启（当前会话）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

分析慢查询日志可以用mysqldumpslow工具：

# 按查询时间排序，显示前10条
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 按访问次数排序
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

也可以用pt-query-digest（Percona Toolkit）做更详细的分析。

2. EXPLAIN执行计划

使用EXPLAIN查看SQL的执行计划，分析性能瓶颈：

EXPLAIN SELECT * FROM users WHERE name='张三';
EXPLAIN EXTENDED SELECT ...;  -- 显示更多信息
EXPLAIN FORMAT=JSON SELECT ...;  -- JSON格式（MySQL 5.6+）

EXPLAIN输出的关键字段：

  • id：查询序号，id越大越先执行，id相同从上到下执行。
  • select_type：查询类型

- SIMPLE：简单查询，无子查询和UNION - PRIMARY：主查询（最外层） - SUBQUERY：子查询 - DERIVED：派生表（FROM中的子查询） - UNION：UNION中的第二个或后面的查询 - UNION RESULT：UNION的结果

  • table：访问的表。
  • type：访问类型（性能从好到差）

- system：表只有一行（系统表） - const：通过主键或唯一索引等值查询，最多匹配一行 - eq_ref：联表查询时，使用主键或唯一索引 - ref：使用非唯一索引等值查询 - range：索引范围查询（BETWEEN、IN、>、<等） - index：全索引扫描（扫描整个索引） - ALL：全表扫描（最差，需要优化）

  • possible_keys：可能使用的索引。
  • key：实际使用的索引。NULL表示没有使用索引。
  • key_len：使用的索引长度（字节），可以判断使用了联合索引的哪些列。
  • ref：索引比较的列或常量。
  • rows：估算需要扫描的行数（越少越好）。
  • Extra：额外信息

- Using index：使用了覆盖索引（好） - Using where：使用了WHERE过滤 - Using temporary：使用了临时表（需要优化，常见于GROUP BY） - Using filesort：使用了文件排序（需要优化，常见于ORDER BY） - Using join buffer：使用了连接缓存（联表优化） - Impossible WHERE：WHERE条件永远为假

优化目标：避免ALL（全表扫描），避免Using temporary和Using filesort，尽量使用索引。

3. 常见SQL优化技巧

（1）避免SELECT 只查询需要的字段，避免SELECT 。SELECT *会返回所有字段，增加网络传输和内存消耗，而且无法使用覆盖索引。

-- 不好
SELECT * FROM users WHERE id=1;

-- 好
SELECT id, name, email FROM users WHERE id=1;

（2）分页优化

深分页（LIMIT 100000, 20）性能很差，因为需要扫描100020行然后丢弃前100000行。

优化方法：

-- 普通分页（深分页慢）
SELECT * FROM articles ORDER BY id LIMIT 100000, 20;

-- 优化1：使用覆盖索引+子查询
SELECT * FROM articles WHERE id >= (
    SELECT id FROM articles ORDER BY id LIMIT 100000, 1
) LIMIT 20;

-- 优化2：使用WHERE条件（记录上一页最后一个ID）
SELECT * FROM articles WHERE id > 100000 ORDER BY id LIMIT 20;

（3）避免在索引列上使用函数和运算

-- 不好（索引失效）
SELECT * FROM users WHERE YEAR(create_time)=2016;
SELECT * FROM users WHERE id+1=2;

-- 好（使用范围查询）
SELECT * FROM users WHERE create_time >= '2016-01-01' AND create_time < '2017-01-01';
SELECT * FROM users WHERE id=1;

（4）避免隐式类型转换

-- 不好（phone是字符串，用数字查询会导致隐式类型转换，索引失效）
SELECT * FROM users WHERE phone=13800138000;

-- 好
SELECT * FROM users WHERE phone='13800138000';

（5）LIKE优化

-- 不好（以%开头，索引失效）
SELECT * FROM users WHERE name LIKE '%张%';

-- 好（以非%开头，可以使用索引）
SELECT * FROM users WHERE name LIKE '张%';

-- 如果需要全文搜索，使用全文索引或搜索引擎（Elasticsearch）

（6）OR优化

-- 不好（如果b没有索引，整个查询不使用索引）
SELECT * FROM users WHERE name='张三' OR email='zhangsan@example.com';

-- 优化1：确保两个条件都有索引
-- 优化2：用UNION代替OR
SELECT * FROM users WHERE name='张三'
UNION
SELECT * FROM users WHERE email='zhangsan@example.com';

（7）IN和EXISTS

-- IN适合子查询结果集小的情况
SELECT * FROM articles WHERE user_id IN (SELECT id FROM users WHERE status=1);

-- EXISTS适合子查询结果集大的情况（外表小，子查询表大）
SELECT * FROM articles a WHERE EXISTS (
    SELECT 1 FROM users u WHERE u.id=a.user_id AND u.status=1
);

MySQL 5.6+优化器会自动选择，但是了解原理有助于优化。

（8）GROUP BY和ORDER BY优化

-- 避免Using temporary和Using filesort
-- 为GROUP BY和ORDER BY的字段建立索引
SELECT category_id, COUNT(*) FROM articles GROUP BY category_id;
-- 如果category_id有索引，可以避免临时表

-- ORDER BY使用索引
SELECT * FROM articles ORDER BY create_time DESC LIMIT 20;
-- 如果create_time有索引，可以避免filesort

（9）批量操作

-- 不好（循环单条插入，性能差）
foreach ($data as $row) {
    INSERT INTO table VALUES (...);
}

-- 好（批量插入）
INSERT INTO table VALUES (...), (...), (...);

-- 批量更新（用CASE或临时表）
UPDATE table SET 
    field = CASE id 
        WHEN 1 THEN 'value1' 
        WHEN 2 THEN 'value2' 
    END
WHERE id IN (1,2);

（10）避免大事务 大事务会长时间占用锁，导致其他查询阻塞，还可能导致主从延迟。

  • 事务尽量短小
  • 避免在事务中做耗时操作（如调用外部API）
  • 批量操作分批提交
  • 避免在事务中SELECT ... FOR UPDATE锁定过多行

四、表结构优化

合理的表结构设计是性能优化的基础。

1. 数据类型选择

  • 越小越好：在满足需求的前提下，选择最小的数据类型。小数据类型占用空间少，索引也小，IO效率高。
  • 整数优先：整数比字符串处理快，占用空间小。能用整数就不用字符串（如用TINYINT表示状态，不用VARCHAR）。
  • 避免NULL：NULL字段会占用额外空间，索引和比较更复杂。尽量用NOT NULL DEFAULT ''或0。
  • 字符串长度合理：VARCHAR长度根据实际需求设置，不要盲目设VARCHAR(255)。
  • 时间类型：用DATETIME或TIMESTAMP，不要用字符串存储时间。TIMESTAMP占用4字节，DATETIME占用8字节（5.6+）。
  • DECIMAL：精确小数用DECIMAL，不要用FLOAT/DOUBLE（有精度问题）。

2. 字段设计

  • 避免过多字段：单表字段不要太多（一般不超过30-40个），过多字段会影响性能。
  • 大字段分离：TEXT、BLOB等大字段单独建表，避免影响主表查询性能。
  • 避免冗余：减少数据冗余，但是适当的冗余可以减少联表查询（反范式设计）。
  • 预留扩展：预留一些扩展字段，但是不要过多。

3. 主键设计

  • 自增主键：InnoDB推荐使用自增整数主键，插入性能好，聚簇索引有序。
  • 避免UUID主键：UUID是字符串，无序，插入性能差，索引大。如果需要全局唯一ID，可以用雪花算法（Snowflake）生成有序整数ID。
  • 主键不要修改：主键是聚簇索引，修改主键会导致数据移动，性能差。

4. 字符集

  • 统一字符集：数据库、表、字段的字符集统一，避免乱码和联表查询时的字符集转换。
  • utf8mb4：2016年推荐使用utf8mb4（支持emoji和所有Unicode字符），不要用utf8（MySQL的utf8最多3字节，不支持emoji）。
  • 排序规则：utf8mb4unicodeci（准确）或utf8mb4generalci（稍快）。

5. 存储引擎

  • InnoDB：2016年MySQL默认存储引擎，支持事务、行级锁、外键、崩溃恢复，适合大多数场景。
  • MyISAM：不支持事务，表级锁，崩溃后可能损坏，2016年已经不推荐使用。
  • Memory：内存存储，速度快，但是数据不持久，适合缓存和临时数据。

2016年推荐统一使用InnoDB。

五、配置优化

MySQL配置优化需要根据硬件和业务特点调整，以下是一些重要的配置参数（my.cnf）。

1. InnoDB配置

[mysqld]
# InnoDB缓冲池大小（最重要的参数，建议设为物理内存的50-70%）
innodb_buffer_pool_size = 4G

# 缓冲池实例数（多个实例减少锁竞争，每个实例至少1G）
innodb_buffer_pool_instances = 4

# InnoDB日志文件大小（建议256M-1G，大日志减少checkpoint，但是崩溃恢复慢）
innodb_log_file_size = 256M

# InnoDB日志缓冲区（16M-64M）
innodb_log_buffer_size = 16M

# 每次提交是否刷日志（1=最安全，每次提交刷盘；0=每秒刷盘，性能好但可能丢1秒数据；2=写到OS缓存，每秒刷盘）
innodb_flush_log_at_trx_commit = 1

# 数据文件刷盘方式（O_DIRECT绕过OS缓存，避免双缓存，推荐）
innodb_flush_method = O_DIRECT

# 每个表独立表空间（推荐，方便管理和回收空间）
innodb_file_per_table = 1

# 并发线程数（一般设为CPU核数*2）
innodb_thread_concurrency = 8

# IO容量（SSD设高一些，如2000；SAS硬盘设200）
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

# 自适应哈希索引（开启，InnoDB自动为热点页建立哈希索引）
innodb_adaptive_hash_index = 1

2. 连接和缓存

# 最大连接数（根据业务设置，一般200-1000，不要设太大）
max_connections = 500

# 连接超时（秒）
wait_timeout = 600
interactive_timeout = 600

# 查询缓存（MySQL 5.6+不推荐使用，5.7已废弃，8.0已移除）
query_cache_type = 0
query_cache_size = 0

# 临时表大小
tmp_table_size = 64M
max_heap_table_size = 64M

# 排序缓冲区（每个连接）
sort_buffer_size = 2M

# 连接缓冲区
join_buffer_size = 2M

# 读缓冲区
read_buffer_size = 1M
read_rnd_buffer_size = 1M

3. 其他配置

# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# 默认存储引擎
default-storage-engine = InnoDB

# 跳过域名解析（用IP授权，提升连接速度）
skip-name-resolve

# 慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

# 错误日志
log_error = /var/log/mysql/error.log

# 二进制日志（主从复制和恢复用）
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW  # 行格式，推荐
expire_logs_days = 7  # 日志保留7天
max_binlog_size = 100M

注意：配置优化需要根据实际情况调整，不要盲目复制配置。调整后要测试，观察性能变化。

六、缓存策略

数据库是系统的瓶颈，缓存是提升性能的有效手段。

1. 缓存的层次

  • 应用层缓存：在应用代码中缓存数据（如本地变量、静态变量），速度最快，但是不能跨进程共享。
  • 分布式缓存：使用Redis、Memcached等分布式缓存，跨应用共享，速度快，是最常用的缓存层。
  • 数据库缓存：MySQL的查询缓存（不推荐）、InnoDB缓冲池（缓存数据页和索引页）。
  • CDN缓存：静态资源（图片、CSS、JS）使用CDN缓存，减轻服务器压力。

2. 缓存策略

  • Cache Aside（旁路缓存）：最常用的模式。读：先读缓存，缓存没有就读数据库，然后写入缓存。写：先更新数据库，然后删除缓存。
  • Read Through：读时由缓存层负责加载数据库，应用只访问缓存。
  • Write Through：写时同时写缓存和数据库，由缓存层负责。
  • Write Behind：写时只写缓存，异步批量写数据库，性能好但是可能丢数据。

推荐使用Cache Aside模式，简单可靠。

3. 缓存的注意事项

  • 缓存穿透：查询不存在的数据，缓存和数据库都没有，每次都查数据库。解决：缓存空值（设置短过期时间）、布隆过滤器。
  • 缓存雪崩：大量缓存同时过期，请求全部打到数据库。解决：过期时间加随机值、多级缓存、服务降级。
  • 缓存击穿：热点key过期，大量并发请求打到数据库。解决：互斥锁（只让一个请求查数据库）、永不过期（逻辑过期）。
  • 数据一致性：缓存和数据库的数据一致性是难点。推荐：更新数据库后删除缓存（而不是更新缓存），设置合理的过期时间，最终一致性。
  • 缓存预热：系统启动时提前加载热点数据到缓存，避免冷启动时数据库压力大。
  • 缓存降级：缓存不可用时，有降级方案（如直接查数据库、返回默认值、服务降级），避免系统崩溃。

七、读写分离与分库分表

当单库单表无法满足性能需求时，需要考虑架构层面的优化。

1. 读写分离

读写分离是指主库负责写操作，从库负责读操作，通过主从复制同步数据。

  • 优点：分散读压力，提升并发能力；从库可以做备份和报表。
  • 缺点：主从延迟（从库数据可能不是最新的）；增加架构复杂度。
  • 适用场景：读多写少的应用（大部分Web应用）。

实现方式：

  • 应用层：代码中判断读写，分别连接主从库。
  • 中间件：使用MyCat、Atlas、ProxySQL等中间件，自动路由读写。

注意：对于实时性要求高的读（如刚写完就读），应该走主库，避免主从延迟导致数据不一致。

2. 分库分表

当单表数据量很大（千万级以上），查询和写入性能下降时，需要分库分表。

  • 垂直分库：按业务模块分库，如用户库、订单库、商品库。减少单库压力，业务解耦。
  • 垂直分表：按字段分表，把不常用的大字段分到扩展表。减少单表字段数，提升查询性能。
  • 水平分库：把同一个表的数据按某种规则（如用户ID哈希）分到不同的库。分散单库压力。
  • 水平分表：把同一个表的数据按某种规则分到同一个库的不同表。分散单表压力。

分片规则：

  • 范围分片：按ID范围或时间范围分片，如ID 1-1000万在表1，1000万-2000万在表2。优点：扩容方便；缺点：热点问题（新数据都在一个分片）。
  • 哈希分片：按分片键哈希取模，如user_id % 16。优点：数据均匀；缺点：扩容麻烦（需要重新哈希）。
  • 一致性哈希：解决哈希分片扩容问题，扩容时只影响部分数据。

分库分表的挑战：

  • 跨分片查询（需要聚合多个分片的结果）
  • 跨分片事务（分布式事务，如2PC、TCC、Saga）
  • 全局唯一ID（雪花算法等）
  • 扩容和数据迁移
  • 运维复杂度增加

注意：分库分表是最后的手段，不要过早分库分表。先做SQL和索引优化、缓存、读写分离，实在不行再分库分表。

八、性能监控与工具

1. 监控指标

  • QPS/TPS：每秒查询数/事务数，衡量数据库负载。
  • 连接数：当前连接数、活跃连接数，避免连接数满。
  • 慢查询数：慢查询数量，发现性能问题。
  • InnoDB状态：缓冲池命中率、行锁等待、死锁数等。
  • 主从延迟：主从复制延迟，避免数据不一致。
  • 磁盘IO：IO使用率、等待时间，发现IO瓶颈。
  • CPU使用率：CPU是否成为瓶颈。
  • 内存使用率：内存是否足够，是否有交换（swap）。

2. 常用工具

  • mysqladmin：MySQL自带管理工具，查看状态、进程等。

``bash mysqladmin -u root -p status mysqladmin -u root -p processlist mysqladmin -u root -p extended-status ``

  • SHOW STATUS：查看MySQL状态变量。

``sql SHOW STATUS LIKE 'Threads%'; SHOW STATUS LIKE 'InnoDB%'; SHOW STATUS LIKE 'Slow_queries'; ``

  • SHOW PROCESSLIST：查看当前连接和执行的SQL。

``sql SHOW PROCESSLIST; SHOW FULL PROCESSLIST; -- 显示完整SQL ``

  • INFORMATION_SCHEMA：信息数据库，查询表、索引、进程等信息。

```sql -- 查看表大小 SELECT tablename, tablerows, datalength, indexlength FROM informationschema.tables WHERE tableschema='dbname' ORDER BY data_length DESC;

-- 查看没有主键的表 SELECT tableschema, tablename FROM informationschema.tables WHERE tableschema NOT IN ('mysql','informationschema','performanceschema') AND tablename NOT IN (SELECT tablename FROM informationschema.tableconstraints WHERE constraint_type='PRIMARY KEY'); ```

  • Performance Schema：MySQL 5.5+的性能监控引擎，详细监控事件和资源消耗。
  • mysqldumpslow：分析慢查询日志。
  • pt-query-digest：Percona Toolkit的慢查询分析工具，功能强大。
  • pt-index-usage：分析索引使用情况，找出未使用的索引。
  • Nagios/Zabbix/Prometheus：开源监控系统，监控MySQL性能。

九、常见性能问题与解决方案

1. 查询慢

  • 检查是否有索引，EXPLAIN查看执行计划
  • 优化SQL，避免索引失效
  • 增加合适的索引
  • 使用覆盖索引
  • 分页优化
  • 缓存热点数据

2. 连接数满

  • 检查是否有慢查询占用连接
  • 优化慢查询，减少连接持有时间
  • 调整max_connections（不要盲目调大）
  • 使用连接池（应用层）
  • 检查是否有连接泄漏（应用未关闭连接）

3. CPU飙升

  • 检查是否有大量排序、分组、临时表（优化SQL和索引）
  • 检查是否有全表扫描（增加索引）
  • 检查是否有死循环或复杂计算
  • 检查并发是否过高（限流、缓存）

4. IO瓶颈

  • 检查是否有大量随机IO（优化索引，减少回表）
  • 检查innodbbufferpool_size是否足够（缓存数据页，减少IO）
  • 使用SSD硬盘（随机IO性能远好于机械硬盘）
  • 分库分表，分散IO压力
  • 读写分离，分散读IO

5. 死锁

  • 查看死锁日志：SHOW ENGINE INNODB STATUS;
  • 分析死锁的SQL和事务
  • 统一加锁顺序（避免不同事务以不同顺序加锁）
  • 事务尽量短小，减少锁持有时间
  • 降低隔离级别（如从REPEATABLE READ降到READ COMMITTED）
  • 避免大事务批量更新

6. 主从延迟

  • 检查主库写入是否过多（分库分表分散写入）
  • 检查从库性能是否不足（升级从库硬件）
  • 使用并行复制（MySQL 5.6+支持库级并行，5.7+支持事务级并行）
  • 检查网络是否稳定
  • 避免在从库执行大查询（阻塞SQL线程）
  • 使用半同步复制（保证至少一个从库收到）

总结

MySQL性能优化是一个系统工程，涉及索引、SQL、表结构、配置、缓存、架构等多个层面。

本文从MySQL性能优化概述、索引优化（索引类型、设计原则、最左前缀、索引失效、覆盖索引）、SQL查询优化（慢查询分析、EXPLAIN执行计划、常见优化技巧）、表结构优化（数据类型、字段设计、主键、字符集、存储引擎）、配置优化（InnoDB配置、连接缓存、其他配置）、缓存策略（缓存层次、策略、注意事项）、读写分离与分库分表、性能监控与工具、常见问题与解决方案等方面，系统讲解了MySQL性能优化实战。

MySQL性能优化的核心要点：

  1. 索引是基础：合理设计索引，避免索引失效，利用覆盖索引
  2. SQL是关键：避免SELECT *、深分页、索引列函数、隐式类型转换
  3. 表结构要合理：小数据类型、避免NULL、自增主键、utf8mb4
  4. 配置要合适：innodbbufferpool_size是最重要的参数
  5. 缓存要充分：热点数据缓存，减少数据库访问
  6. 架构要演进：读写分离、分库分表（最后的手段）
  7. 监控要持续：持续监控性能，及时发现问题
  8. 优化要持续：性能优化不是一次性的，需要持续进行

记住优化的原则：先做低成本、高收益的优化（SQL和索引），再做高成本、复杂的优化（分库分表、硬件升级）。不要过早优化，也不要忽视基础优化。

MySQL性能优化是每个后端开发者的必备技能。希望本文能帮助你掌握MySQL性能优化的方法和技巧，打造高性能的数据库应用。