【MySQL优化指南：从慢查询到高并发架构设计&&提升查询性能的10大实战技巧】

weixin_74887700

1186人浏览 · 2025-04-08 09:49:50

weixin_74887700 · 2025-04-08 09:49:50 发布

MySQL优化指南：提升查询性能的10大实战技巧

第一部分：性能诊断工具

1.1 慢查询分析

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 记录超过1秒的查询
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 分析慢查询（pt-query-digest）
pt-query-digest /var/log/mysql/slow.log > analysis.txt
### 1.1 慢查询日志
```sql
-- 开启慢查询
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

1.2 EXPLAIN分析

EXPLAIN SELECT * FROM orders WHERE user_id = 123;

二、索引优化黄金法则

2.1 索引设计原则

-- 覆盖索引优化
ALTER TABLE products 
ADD INDEX idx_category_price (category_id, price) 
INCLUDE (stock, create_time);

-- 最左前缀法则验证
EXPLAIN SELECT * FROM logs 
WHERE year=2023 AND month=4; -- 命中复合索引(year,month,day)

2.2 索引失效场景

-- 索引失效案例
SELECT * FROM users 
WHERE LEFT(name, 3) = 'ali'; -- 索引失效

-- 优化方案
SELECT * FROM users 
WHERE name LIKE 'ali%'; -- 使用索引

## 一、性能诊断工具链

### 1.1 慢查询分析
```sql
-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 记录超过1秒的查询
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 分析慢查询（pt-query-digest）
pt-query-digest /var/log/mysql/slow.log > analysis.txt

1.2 EXPLAIN实战

-- 复杂查询分析
EXPLAIN 
SELECT o.*, u.name 
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.amount > 1000
ORDER BY o.create_time DESC
LIMIT 10;

关键指标解读：

type: ref：使用非唯一索引查找
rows: 12345：扫描行数需优化
Extra: Using filesort：需要创建排序索引

二、索引优化黄金法则

2.1 索引设计原则

-- 覆盖索引优化
ALTER TABLE products 
ADD INDEX idx_category_price (category_id, price) 
INCLUDE (stock, create_time);

-- 最左前缀法则验证
EXPLAIN SELECT * FROM logs 
WHERE year=2023 AND month=4; -- 命中复合索引(year,month,day)

2.2 索引失效场景

-- 索引失效案例
SELECT * FROM users 
WHERE LEFT(name, 3) = 'ali'; -- 索引失效

-- 优化方案
SELECT * FROM users 
WHERE name LIKE 'ali%'; -- 使用索引

三、查询语句优化技巧

3.1 避免全表扫描

-- 优化前（全表扫描）
SELECT COUNT(*) FROM orders 
WHERE status = 'completed';

-- 优化后（索引统计）
ALTER TABLE orders 
ADD INDEX idx_status (status);

SELECT COUNT(*) FROM orders 
WHERE status = 'completed';

3.2 分页查询优化

-- 传统分页（深分页问题）
SELECT * FROM articles 
ORDER BY id DESC 
LIMIT 10000, 10;

-- 优化方案（游标分页）
SELECT * FROM articles 
WHERE id < 10000 
ORDER BY id DESC 
LIMIT 10;

四、架构级优化方案

4.1 读写分离实战

# ProxySQL配置
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES 
(1, 'master-node', 3306),  -- 写节点
(2, 'slave-node1', 3306),  -- 读节点
(2, 'slave-node2', 3306);  -- 读节点

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

4.2 分库分表策略

-- 按用户ID分表（4库8表）
CREATE TABLE users_01 (
  id BIGINT AUTO_INCREMENT,
  name VARCHAR(50),
  PRIMARY KEY (id)
) ENGINE=InnoDB;

-- 路由规则（Java示例）
public String getTableName(Long userId) {
  return "users_" + (userId % 8);
}

五、存储引擎深度对比

5.1 InnoDB vs MyISAM

性能对比数据：

场景	InnoDB QPS	MyISAM QPS
100并发写入	8500	3200
复杂JOIN查询	1200	1800
全文检索	500	2500

六、实战案例：电商系统优化

6.1 订单表优化

-- 原始表结构（单表5000万数据）
CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  user_id INT,
  product_id INT,
  amount DECIMAL(10,2),
  create_time DATETIME
);

-- 优化后架构
CREATE TABLE orders_2024Q1 (
  id BIGINT,
  user_id INT,
  product_id INT,
  amount DECIMAL(10,2),
  create_time DATETIME,
  PRIMARY KEY (id, create_time)
) PARTITION BY RANGE (YEAR(create_time)) (
  PARTITION p0 VALUES LESS THAN (2023),
  PARTITION p1 VALUES LESS THAN (2024),
  PARTITION p2 VALUES LESS THAN (2025)
);

优化成果：

查询响应时间从12s降至230ms
写入吞吐量提升4倍
磁盘空间节省62%

七、生产级配置模板

7.1 my.cnf优化

[mysqld]
innodb_buffer_pool_size = 12G
innodb_log_file_size = 2G
max_connections = 2000
query_cache_type = 0
tmp_table_size = 256M
slow_query_log = 1
long_query_time = 0.5

7.2 连接池配置

// HikariCP配置（Java）
spring.datasource.hikari.maximumPoolSize=20
spring.datasource.hikari.minimumIdle=5
spring.datasource.hikari.idleTimeout=30000
spring.datasource.hikari.maxLifetime=1800000

八、常见问题解决方案

8.1 死锁处理

-- 查看最近死锁
SHOW ENGINE INNODB STATUS\G

-- 死锁日志分析
LATEST DETECTED DEADLOCK
------------------------
2024-04-17 14:25:32 0x7f3a18
*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 456, OS thread handle 123456789, query id 789 updating
UPDATE orders SET status = 'paid' WHERE id = 1001

8.2 主从延迟优化

# 查看延迟状态
SHOW SLAVE STATUS\G

# 优化策略
1. 启用并行复制：slave_parallel_workers=4
2. 分离业务查询：读写分离
3. 调整binlog格式：ROW -> MIXED

附：性能监控命令

# 实时监控
mysqladmin -uroot -p status
vmstat 1 5
sysbench oltp_read_write run

# 容量规划
SELECT 
  table_name AS `Table`,
  round(((data_length + index_length) / 1024 / 1024), 2) `Size (MB)`
FROM information_schema.TABLES;

三、查询语句优化

3.1 避免SELECT *

-- 优化前
SELECT * FROM logs WHERE date > '2023-01-01';
-- 优化后
SELECT id, message FROM logs WHERE date > '2023-01-01';

四、架构设计优化

4.1 读写分离

# ProxySQL配置
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1, 'master', 3306);
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (2, 'slave1', 3306);
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (2, 'slave2', 3306);

五、框架对比：InnoDB vs MyISAM

5.1 特性对比

特性	InnoDB	MyISAM
事务支持	✔️	❌
行级锁	✔️	表级锁
全文索引	5.6+支持	✔️
崩溃恢复	✔️	❌

六、实战案例：电商系统优化

6.1 分库分表

-- 按用户ID分表
CREATE TABLE users_01 (...) ENGINE=InnoDB;
CREATE TABLE users_02 (...) ENGINE=InnoDB;

七、常见问题解决

7.1 死锁处理

SHOW ENGINE INNODB STATUS;

第二部分：性能诊断工具链

1.1 慢查询分析

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 记录超过1秒的查询
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 分析慢查询（pt-query-digest）
pt-query-digest /var/log/mysql/slow.log > analysis.txt

1.2 EXPLAIN实战

-- 复杂查询分析
EXPLAIN 
SELECT o.*, u.name 
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.amount > 1000
ORDER BY o.create_time DESC
LIMIT 10;

关键指标解读：

type: ref：使用非唯一索引查找
rows: 12345：扫描行数需优化
Extra: Using filesort：需要创建排序索引

二、索引优化黄金法则

2.1 索引设计原则

-- 覆盖索引优化
ALTER TABLE products 
ADD INDEX idx_category_price (category_id, price) 
INCLUDE (stock, create_time);

-- 最左前缀法则验证
EXPLAIN SELECT * FROM logs 
WHERE year=2023 AND month=4; -- 命中复合索引(year,month,day)

2.2 索引失效场景

-- 索引失效案例
SELECT * FROM users 
WHERE LEFT(name, 3) = 'ali'; -- 索引失效

-- 优化方案
SELECT * FROM users 
WHERE name LIKE 'ali%'; -- 使用索引

三、查询语句优化技巧

3.1 避免全表扫描

-- 优化前（全表扫描）
SELECT COUNT(*) FROM orders 
WHERE status = 'completed';

-- 优化后（索引统计）
ALTER TABLE orders 
ADD INDEX idx_status (status);

SELECT COUNT(*) FROM orders 
WHERE status = 'completed';

3.2 分页查询优化

-- 传统分页（深分页问题）
SELECT * FROM articles 
ORDER BY id DESC 
LIMIT 10000, 10;

-- 优化方案（游标分页）
SELECT * FROM articles 
WHERE id < 10000 
ORDER BY id DESC 
LIMIT 10;

四、架构级优化方案

4.1 读写分离实战

# ProxySQL配置
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES 
(1, 'master-node', 3306),  -- 写节点
(2, 'slave-node1', 3306),  -- 读节点
(2, 'slave-node2', 3306);  -- 读节点

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

4.2 分库分表策略

-- 按用户ID分表（4库8表）
CREATE TABLE users_01 (
  id BIGINT AUTO_INCREMENT,
  name VARCHAR(50),
  PRIMARY KEY (id)
) ENGINE=InnoDB;

-- 路由规则（Java示例）
public String getTableName(Long userId) {
  return "users_" + (userId % 8);
}

五、存储引擎深度对比

5.1 InnoDB vs MyISAM

性能对比数据：

场景	InnoDB QPS	MyISAM QPS
100并发写入	8500	3200
复杂JOIN查询	1200	1800
全文检索	500	2500

六、实战案例：电商系统优化

6.1 订单表优化

-- 原始表结构（单表5000万数据）
CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  user_id INT,
  product_id INT,
  amount DECIMAL(10,2),
  create_time DATETIME
);

-- 优化后架构
CREATE TABLE orders_2024Q1 (
  id BIGINT,
  user_id INT,
  product_id INT,
  amount DECIMAL(10,2),
  create_time DATETIME,
  PRIMARY KEY (id, create_time)
) PARTITION BY RANGE (YEAR(create_time)) (
  PARTITION p0 VALUES LESS THAN (2023),
  PARTITION p1 VALUES LESS THAN (2024),
  PARTITION p2 VALUES LESS THAN (2025)
);

优化成果：

查询响应时间从12s降至230ms
写入吞吐量提升4倍
磁盘空间节省62%

七、生产级配置模板

7.1 my.cnf优化

[mysqld]
innodb_buffer_pool_size = 12G
innodb_log_file_size = 2G
max_connections = 2000
query_cache_type = 0
tmp_table_size = 256M
slow_query_log = 1
long_query_time = 0.5

7.2 连接池配置

// HikariCP配置（Java）
spring.datasource.hikari.maximumPoolSize=20
spring.datasource.hikari.minimumIdle=5
spring.datasource.hikari.idleTimeout=30000
spring.datasource.hikari.maxLifetime=1800000

八、常见问题解决方案

8.1 死锁处理

-- 查看最近死锁
SHOW ENGINE INNODB STATUS\G

-- 死锁日志分析
LATEST DETECTED DEADLOCK
------------------------
2024-04-17 14:25:32 0x7f3a18
*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 456, OS thread handle 123456789, query id 789 updating
UPDATE orders SET status = 'paid' WHERE id = 1001

8.2 主从延迟优化

# 查看延迟状态
SHOW SLAVE STATUS\G

# 优化策略
1. 启用并行复制：slave_parallel_workers=4
2. 分离业务查询：读写分离
3. 调整binlog格式：ROW -> MIXED

附：性能监控命令

# 实时监控
mysqladmin -uroot -p status
vmstat 1 5
sysbench oltp_read_write run

# 容量规划
SELECT 
  table_name AS `Table`,
  round(((data_length + index_length) / 1024 / 1024), 2) `Size (MB)`
FROM information_schema.TABLES;

MySQL优化指南：提升查询性能的实战技巧

一、性能诊断工具链

1.1 慢查询分析

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 记录超过1秒的查询
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 分析慢查询（pt-query-digest）
pt-query-digest /var/log/mysql/slow.log > analysis.txt

1.2 EXPLAIN分析

-- 复杂查询分析示例
EXPLAIN 
SELECT o.*, u.name 
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.amount > 1000
ORDER BY o.create_time DESC
LIMIT 10;

关键指标解读：

type: ref：使用非唯一索引查找
rows: 12345：扫描行数需优化
Extra: Using filesort：需要创建排序索引

二、索引优化黄金法则

2.1 索引设计原则

-- 覆盖索引优化
ALTER TABLE products 
ADD INDEX idx_category_price (category_id, price) 
INCLUDE (stock, create_time);

-- 复合索引验证
EXPLAIN SELECT * FROM logs 
WHERE year=2023 AND month=4; -- 命中复合索引(year,month,day)

2.2 索引失效场景

-- 索引失效案例
SELECT * FROM users 
WHERE LEFT(name, 3) = 'ali'; -- 索引失效

-- 优化方案
SELECT * FROM users 
WHERE name LIKE 'ali%'; -- 使用索引

三、查询语句优化技巧

3.1 避免全表扫描

-- 优化前（全表扫描）
SELECT COUNT(*) FROM orders WHERE status = 'completed';

-- 优化后（索引统计）
ALTER TABLE orders ADD INDEX idx_status (status);
SELECT COUNT(*) FROM orders WHERE status = 'completed';

3.2 分页查询优化

-- 传统分页（深分页问题）
SELECT * FROM articles ORDER BY id DESC LIMIT 10000, 10;

-- 优化方案（游标分页）
SELECT * FROM articles 
WHERE id < 10000 ORDER BY id DESC LIMIT 10;

四、架构级优化方案

4.1 读写分离实战

# ProxySQL配置
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES 
(1, 'master-node', 3306),  -- 写节点
(2, 'slave-node1', 3306),  -- 读节点
(2, 'slave-node2', 3306);  -- 读节点

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

4.2 分库分表策略

-- 按用户ID分表（8表示例）
CREATE TABLE users_01 (
  id BIGINT AUTO_INCREMENT,
  name VARCHAR(50),
  PRIMARY KEY (id)
) ENGINE=InnoDB;

-- 路由规则（Java示例）
public String getTableName(Long userId) {
  return "users_" + (userId % 8);
}

五、存储引擎深度对比

5.1 InnoDB vs MyISAM

性能对比数据：

场景	InnoDB QPS	MyISAM QPS
100并发写入	8500	3200
复杂JOIN查询	1200	1800
全文检索	500	2500

六、实战案例：电商系统优化

6.1 订单表优化

-- 原始表结构（单表5000万数据）
CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  user_id INT,
  product_id INT,
  amount DECIMAL(10,2),
  create_time DATETIME
);

-- 优化后架构（按年分区）
CREATE TABLE orders_2024Q1 (
  id BIGINT,
  user_id INT,
  product_id INT,
  amount DECIMAL(10,2),
  create_time DATETIME,
  PRIMARY KEY (id, create_time)
) PARTITION BY RANGE (YEAR(create_time)) (
  PARTITION p0 VALUES LESS THAN (2023),
  PARTITION p1 VALUES LESS THAN (2024),
  PARTITION p2 VALUES LESS THAN (2025)
);

优化成果：

查询响应时间从12s降至230ms
写入吞吐量提升4倍
磁盘空间节省62%

七、生产级配置模板

7.1 my.cnf优化

[mysqld]
innodb_buffer_pool_size = 12G
innodb_log_file_size = 2G
max_connections = 2000
query_cache_type = 0
tmp_table_size = 256M
slow_query_log = 1
long_query_time = 0.5

7.2 连接池配置（Java）

spring.datasource.hikari.maximumPoolSize=20
spring.datasource.hikari.minimumIdle=5
spring.datasource.hikari.idleTimeout=30000
spring.datasource.hikari.maxLifetime=1800000

八、常见问题解决方案

8.1 死锁处理

-- 查看最近死锁
SHOW ENGINE INNODB STATUS\G

-- 死锁日志分析示例：
LATEST DETECTED DEADLOCK
------------------------
2024-04-17 14:25:32 0x7f3a18
*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 0 sec starting index read
UPDATE orders SET status = 'paid' WHERE id = 1001

8.2 主从延迟优化

# 查看延迟状态
SHOW SLAVE STATUS\G

# 优化策略：
1. 启用并行复制：slave_parallel_workers=4
2. 分离业务查询：读写分离
3. 调整binlog格式：ROW -> MIXED

附：性能监控命令

# 实时监控
mysqladmin -uroot -p status
vmstat 1 5
sysbench oltp_read_write run

# 容量规划
SELECT 
  table_name AS `Table`,
  round(((data_length + index_length) / 1024 / 1024), 2) `Size (MB)`
FROM information_schema.TABLES;

天工造物局

北京人形旗下天工造物具身智能开源社区，聚焦具身天工与慧思开物两大平台

更多推荐

deck.gl TripsLayer 出租车轨迹动画示例实战：从示例运行到着色器原理

TripsLayer 是 deck.gl 中用于渲染**随时间流动的轨迹动画**（如出租车行程、物流路径、迁徙路线）的核心图层。本文以仓库中的最小独立示例 [examples/website/trips](https://link.gitcode.com/i/1442af8f442f4cff39ff546e050fceb5) 为骨架，带你完成从安装依赖、启动应用、理解曼哈顿出租车数据格式，到深入

天工造物局

Foundry Anvil 中 eth_simulateV1 的 Blob 交易与 Blob Gas 字段规范化：原理、实现与测试验证

本篇技术指南以 [anvil-simulate-blob-transactions.md](https://link.gitcode.com/i/3d2ebe227262de169335eb290af80da8) 这条 changelog 条目为起点，深入剖析 Foundry Anvil 对 `eth_simulateV1` RPC 返回结果中 blob（EIP-4844）交易与 blob gas

天工造物局

LMCache CLI 实战：用 `lmcache kvcache clear` 管理运行中服务的 KV Cache 状态

本文是一份针对 LMCache 官方 CLI 命令 `lmcache kvcache` 的完整技术指南。该命令用于在**运行中的 LMCache 服务（MP HTTP server）**上管理 KV cache 状态，当前提供 `clear` 子命令，用于清空 L1（CPU 内存）层中缓存的全部 KV 数据。读完本文，你将掌握该命令的完整参数体系、退出码语义、终端/JSON/静默三种输出模式，并能