性能优化

查询优化

慢查询日志

开启慢查询日志

SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = ON;

查看慢查询配置

SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

分析慢查询

mysqldumpslow -s t /var/log/mysql/slow.log
pt-query-digest /var/log/mysql/slow.log

查询优化建议

1. 避免 SELECT *

SELECT * FROM users;

SELECT id, username, email FROM users;

2. 使用 LIMIT

SELECT * FROM users LIMIT 100;

3. 避免子查询，使用 JOIN

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

SELECT u.* FROM users u
INNER JOIN orders o ON u.id = o.user_id;

4. 使用 EXISTS 替代 IN

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

5. 批量插入

INSERT INTO users (username) VALUES ('张三');
INSERT INTO users (username) VALUES ('李四');

INSERT INTO users (username) VALUES ('张三'), ('李四'), ('王五');

表结构优化

选择合适的数据类型

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    status TINYINT UNSIGNED,
    age TINYINT UNSIGNED,
    username VARCHAR(50),
    description TEXT
);

范式与反范式

范式化

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    department_id INT
);

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

反范式化

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    department_id INT,
    department_name VARCHAR(50)
);

垂直拆分

将大表拆分为多个小表：

CREATE TABLE users_basic (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

CREATE TABLE users_profile (
    user_id INT PRIMARY KEY,
    avatar VARCHAR(200),
    bio TEXT
);

水平拆分

按规则拆分数据：

CREATE TABLE users_0 (id INT PRIMARY KEY, username VARCHAR(50));
CREATE TABLE users_1 (id INT PRIMARY KEY, username VARCHAR(50));
CREATE TABLE users_2 (id INT PRIMARY KEY, username VARCHAR(50));

缓存优化

查询缓存

SHOW VARIABLES LIKE 'query_cache%';
SET GLOBAL query_cache_size = 67108864;
SET GLOBAL query_cache_type = ON;

缓存使用建议

SELECT SQL_CACHE * FROM users WHERE id = 1;
SELECT SQL_NO_CACHE * FROM users WHERE id = 1;

应用层缓存

• Redis
• Memcached
• 本地缓存

连接池优化

连接池参数

SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'wait_timeout';
SHOW STATUS LIKE 'Threads%';

优化配置

[mysqld]
max_connections = 500
wait_timeout = 28800
interactive_timeout = 28800

InnoDB 优化

缓冲池配置

SHOW VARIABLES LIKE 'innodb_buffer_pool%';

[mysqld]
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4

日志配置

SHOW VARIABLES LIKE 'innodb_log%';

[mysqld]
innodb_log_file_size = 512M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1

刷新策略

[mysqld]
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

服务器优化

内存配置

[mysqld]
key_buffer_size = 256M
table_open_cache = 2000
thread_cache_size = 100

临时表配置

[mysqld]
tmp_table_size = 64M
max_heap_table_size = 64M

排序缓冲区

[mysqld]
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 1M

监控工具

SHOW STATUS

SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Connections';
SHOW GLOBAL STATUS LIKE 'Slow_queries';

SHOW PROCESSLIST

SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

Performance Schema

USE performance_schema;
SHOW TABLES;

Sys Schema

USE sys;
SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_index_statistics;

性能分析工具

EXPLAIN

EXPLAIN SELECT * FROM users WHERE username = '张三';
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE username = '张三';

EXPLAIN ANALYZE (MySQL 8.0.18+)

EXPLAIN ANALYZE SELECT * FROM users WHERE username = '张三';

PROFILING

SET profiling = ON;
SELECT * FROM users WHERE username = '张三';
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

下一步学习

• 备份恢复 - 掌握备份恢复
• 安全管理 - 学习安全管理