02_1_Mysql_sql优化-优快云博客

本文链接：https://blog.youkuaiyun.com/worinimmp/article/details/147191391

执行计划与SQL优化指南
一、explain工具深度解析

基本用法
在MySQL中，通过EXPLAIN命令分析SQL执行计划：
EXPLAIN SELECT * FROM users WHERE age > 25 ORDER BY create_time DESC;
关键字段解析
字段说明type访问类型（性能排序：system > const > ref > range > index > ALL）possible_keys可能使用的索引key实际使用的索引rows预估扫描行数Extra附加信息（Using filesort/Using temporary需重点关注）常见问题诊断：
type=ALL：全表扫描，需添加索引
Using filesort：未使用索引排序
Using temporary：创建了临时表

二、阿里巴巴索引优化六大原则

最左前缀原则
示例：
联合索引(a,b,c)生效场景：
WHERE a=1 AND b>2 – ✅ 使用a,b列
ORDER BY a,b,c – ✅ 覆盖排序
WHERE a=1 AND c=3 – ❌ 只能使用a列
覆盖索引优化
优化前：
SELECT * FROM orders WHERE user_id=1001;

优化后：
ALTER TABLE orders ADD INDEX idx_cover(user_id, order_no, amount);
SELECT user_id, order_no, amount FROM orders WHERE user_id=1001; – 通过索引直接获取数据

索引下推（ICP）
MySQL 5.6+特性，将WHERE条件过滤下推到存储引擎层，减少回表次数。
避免冗余索引
错误示例：
INDEX idx_a (a),
INDEX idx_ab (a,b) – idx_a冗余，可删除
前缀索引设计
ALTER TABLE logs ADD INDEX idx_content(content(20)); – 对长文本取前20字符
隐式类型转换陷阱
– user_id为VARCHAR类型时：

SELECT * FROM users WHERE user_id = 10086; – ❌ 类型转换导致索引失效
SELECT * FROM users WHERE user_id = ‘10086’; – ✅

三、实战优化案例
案例背景
慢查询日志发现耗时800ms的SQL：
SELECT * FROM orders
WHERE status = ‘SHIPPING’
AND create_time BETWEEN ‘2023-01-01’ AND ‘2023-06-30’
ORDER BY amount DESC
LIMIT 1000;

优化步骤
执行计划分析

EXPLAIN SELECT… – 显示type=ALL，Using filesort

索引优化

ALTER TABLE orders ADD INDEX idx_status_time_amount(status, create_time, amount);

SQL改写

SELECT * FROM orders
WHERE status = ‘SHIPPING’
AND create_time >= ‘2023-01-01’
AND create_time <= ‘2023-06-30’ – 避免BETWEEN可能导致的优化器误判
ORDER BY amount DESC
LIMIT 1000;

验证效果

执行时间从800ms降至35ms
执行计划显示
type=range，
Using index

四、持续优化建议
定期检查慢查询日志

SHOW VARIABLES LIKE ‘slow_query_log%’;

#性能调优阶段开启慢日志查询，后面关闭
SET GLOBAL slow_query_log = ON;

使用
FORCE INDEX谨慎
仅在优化器选择错误时临时使用
统计信息更新

大数据变更后执行：
ANALYZE TABLE orders;

索引有效性监控

通过sys.schema_unused_indexes视图查找无用索引

优化箴言
“索引不是越多越好，而是越精准越好。执行计划是SQL优化的地图，读懂它才能找到性能的宝藏。”

固定装备与MySQL性能调优指南

一、索引核心原理与类型剖析

索引类型对比
索引类型数据结构适用场景限制条件B+树索引多路平衡树范围查询（>、<、BETWEEN）、排序默认索引类型，支持所有引擎Hash索引哈希表精确等值查询（=、IN）仅Memory引擎支持，不支持排序全文索引倒排索引文本关键词搜索（MATCH AGAINST）仅MyISAM/InnoDB支持，需配置示例：创建不同类型索引
– B+树索引（默认）

CREATE INDEX idx_age ON users(age);
– 全文索引（需文本类型字段）

ALTER TABLE articles ADD FULLTEXT ft_content(content);

索引的三大视角
视角核心要点物理存储B+树叶子节点存储数据页，非叶子节点存储索引键和指针，支持快速磁盘IO逻辑结构主键索引（聚簇索引）包含完整数据行，二级索引存储主键值使用角度通过EXPLAIN分析访问类型（type）和索引覆盖情况

索引查找步骤
索引选择：优化器根据统计信息选择成本最低的索引
索引定位：通过B+树定位到叶子节点
回表查询：二级索引需根据主键回聚簇索引取完整数据
结果过滤：应用WHERE条件过滤数据（索引下推可提前过滤）

二、MySQL索引高级策略

主键设计原则
使用自增整数（避免页分裂）
禁止业务字段修改（防止索引树频繁调整）
– 错误示例：使用UUID作为主键

CREATE TABLE orders (
id VARCHAR(36) PRIMARY KEY, – ❌ 随机写入导致性能下降
amount DECIMAL(10,2)
);

联合索引优化
最左前缀原则进阶示例：
– 联合索引 (city, age, gender)

SELECT * FROM users
WHERE city=‘北京’ AND age=30 – ✅ 使用city+age列
WHERE city=‘上海’ ORDER BY age – ✅ 排序优化
WHERE age=25 – ❌ 无法命中索引

覆盖索引实战
优化前（需回表）：
EXPLAIN SELECT * FROM products WHERE category=‘electronics’; – Using where

优化后（覆盖索引）：
ALTER TABLE products ADD INDEX idx_cover(category, name, price);
EXPLAIN SELECT category, name FROM products WHERE category=‘electronics’; – Using index

三、MySQL性能调优实战