MySQL SQL优化
SQL 优化是数据库性能调优的核心环节。合理的 SQL 优化可以显著提升数据库性能，减少资源消耗。1、查询性能分析
1.1 识别慢查询
-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- 超过2秒的查询
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
-- 查看慢查询
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
-- 使用性能模式监控
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;1.2 使用 EXPLAIN 分析查询
-- 基本分析
EXPLAIN SELECT * FROM users WHERE age > 30;
-- 详细分析（MySQL 8.0+）
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;
-- JSON格式输出
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age > 30;1.3 解读 EXPLAIN 结果
关键字段解读：
[*]type：访问类型，从好到坏：system > const > eq_ref > ref > range > index > ALL
[*]key：实际使用的索引
[*]rows：预估扫描行数
[*]Extra：额外信息
Using index：覆盖索引
Using where：使用 WHERE 过滤
Using temporary：使用临时表
Using filesort：使用文件排序
2、索引优化策略
2.1 选择合适的索引列
-- 为高频查询条件创建索引
CREATE INDEX idx_user_status ON orders(user_id, status);
-- 为排序和分组字段创建索引
CREATE INDEX idx_created_at ON orders(created_at);
-- 使用覆盖索引
CREATE INDEX idx_covering ON users(name, age, email);2.2 避免索引失效
-- 1. 避免在索引列上使用函数
-- 错误：SELECT * FROM users WHERE YEAR(created_at) = 2023;
-- 正确：SELECT * FROM users WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';
-- 2. 避免 LIKE 以通配符开头
-- 错误：SELECT * FROM users WHERE name LIKE '%John%';
-- 正确：SELECT * FROM users WHERE name LIKE 'John%';
-- 3. 避免对索引列进行运算
-- 错误：SELECT * FROM users WHERE age + 1 > 30;
-- 正确：SELECT * FROM users WHERE age > 29;
-- 4. 注意 OR 条件的使用
-- 错误：SELECT * FROM users WHERE name = 'John' OR age = 30;
-- 正确：SELECT * FROM users WHERE name = 'John'
-- UNION
-- SELECT * FROM users WHERE age = 30;2.3 索引优化技巧
-- 使用前缀索引
SELECT
COUNT(DISTINCT LEFT(name, 5)) / COUNT(*) AS selectivity_5,
COUNT(DISTINCT LEFT(name, 10)) / COUNT(*) AS selectivity_10
FROM users;
CREATE INDEX idx_name_prefix ON users(name(10));
-- 使用索引合并
SET SESSION optimizer_switch = 'index_merge=on';3、查询语句优化
3.1 SELECT 优化
-- 只选择需要的列
-- 错误：SELECT * FROM users;
-- 正确：SELECT id, name, email FROM users;
-- 使用 LIMIT 限制结果集
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;
-- 避免 SELECT DISTINCT 滥用
-- 错误：SELECT DISTINCT * FROM users;
-- 正确：SELECT DISTINCT department_id FROM users;3.2 WHERE 优化
3.2.1 基础
-- 使用等值查询优先
SELECT * FROM users WHERE id = 100;
-- 避免使用 != 或 <>
SELECT * FROM users WHERE status IN ('active', 'inactive');
-- 使用 BETWEEN 代替范围查询
SELECT * FROM orders WHERE amount BETWEEN 100 AND 500;3.2.2 避免索引失效的写法
失效场景错误示例优化示例函数操作索引字段WHERE YEAR(created_at) = 2024WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'隐式类型转换WHERE phone = 13800138000WHERE phone = '13800138000'模糊查询以%开头WHERE username LIKE '%张三%'WHERE username LIKE '张三%'使用OR连接非索引字段WHERE id = 1 OR age = 20拆分查询或为age建索引3.2.3 优化范围查询
范围条件（>,'2024-01-01';-- 低效：范围条件在左侧，右侧字段无法使用索引SELECT * FROM `order` WHERE created_at > '2024-01-01' AND user_id = 100;3.3 JOIN 优化
[*]确保连接字段建立索引（尤其是外键字段）。
[*]小表驱动大表（左表数据量应小于右表，减少匹配次数）。
[*]避免JOIN过多表（建议不超过 5 张表）。
优化前：
-- 复合索引 (user_id, created_at)
-- 有效：范围条件在右侧
SELECT * FROM `order` WHERE user_id = 100 AND created_at > '2024-01-01';
-- 低效：范围条件在左侧，右侧字段无法使用索引
SELECT * FROM `order` WHERE created_at > '2024-01-01' AND user_id = 100;优化后：
-- 无索引，多表连接效率低
SELECT * FROM `order` o
JOIN `user` u ON o.user_id = u.id
JOIN `order_item` oi ON o.id = oi.order_id;3.4 GROUP BY 和 ORDER BY 优化
[*]ORDER BY和GROUP BY的字段应包含在索引中，避免额外排序（Using filesort）。
[*]复合索引中，排序字段需按同一方向（全升序或全降序）
优化前：
-- 为连接字段建索引
ALTER TABLE `order` ADD INDEX `idx_user_id` (`user_id`);
ALTER TABLE `order_item` ADD INDEX `idx_order_id` (`order_id`);
-- 只查询必要字段
SELECT o.order_no, u.username, oi.product_id
FROM `order` o
JOIN `user` u ON o.user_id = u.id
JOIN `order_item` oi ON o.id = oi.order_id;优化后：
-- 无索引，产生Using filesort
SELECT * FROM `order` WHERE user_id = 100 ORDER BY created_at;3.5 子查询优化
-- 创建包含排序字段的复合索引
ALTER TABLE `order` ADD INDEX `idx_user_created` (`user_id`, `created_at`);
-- 索引覆盖排序，无filesort
SELECT id, order_no FROM `order` WHERE user_id = 100 ORDER BY created_at;4、表结构优化
4.1 选择合适的数据类型
[*]优先使用小字段类型（如INT代替BIGINT，VARCHAR(20)代替VARCHAR(255)）。
[*]时间用DATETIME（需时区转换）或TIMESTAMP（自动时区转换，范围小）。
[*]枚举类型用ENUM（如状态字段：ENUM('active', 'inactive')）。
优化前：
-- 使用 JOIN 代替子查询
-- 错误：SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
-- 正确：SELECT u.* FROM users u JOIN orders o ON u.id = o.user_id;
-- 使用 EXISTS 代替 IN
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
-- 将子查询转换为连接
SELECT u.*, o.order_count
FROM users u
JOIN (SELECT user_id, COUNT(*) as order_count FROM orders GROUP BY user_id) o
ON u.id = o.user_id;优化后：
CREATE TABLE `user` (
`id` BIGINT PRIMARY KEY,-- 无需大整数
`status` VARCHAR(20),-- 状态值有限，可用ENUM
`create_time` VARCHAR(50)-- 应用层转换，效率低
);4.2 分表分库
当单表数据量超过 1000 万行时，需考虑拆分：
[*]水平分表：按行拆分（如按用户 ID 哈希、按时间范围）。
[*]垂直分表：按列拆分（如将大文本字段拆分到独立表）。
示例：按时间水平分表
CREATE TABLE `user` (
`id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
`status` ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP
);4.3 分区表优化
-- 订单表按年份拆分
CREATE TABLE `order_2023` (LIKE `order` INCLUDING ALL);
CREATE TABLE `order_2024` (LIKE `order` INCLUDING ALL);5、服务器参数
通过调整 MySQL 配置文件（my.cnf或my.ini）提升性能
-- 按范围分区
CREATE TABLE sales (
id INT AUTO_INCREMENT,
sale_date DATE,
amount DECIMAL(10,2),
PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE (YEAR(sale_date)) (
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024)
);
-- 按列表分区
CREATE TABLE users (
id INT AUTO_INCREMENT,
region VARCHAR(20),
name VARCHAR(100),
PRIMARY KEY (id, region)
) PARTITION BY LIST COLUMNS(region) (
PARTITION p_north VALUES IN ('Beijing', 'Tianjin'),
PARTITION p_south VALUES IN ('Guangzhou', 'Shenzhen'),
PARTITION p_west VALUES IN ('Chengdu', 'Chongqing')
);6、总结
MySQL SQL 优化是一个系统工程，需要从多个层面综合考虑：
[*]查询层面：编写高效的SQL，避免反模式
[*]索引层面：合理设计索引，使用覆盖索引
[*]结构层面：优化数据类型，适当反规范化
[*]系统层面：调整配置参数，优化硬件资源
[*]架构层面：考虑读写分离、分库分表
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！
页:
[1]