MySQL索引原理与SQL执行优化实战指南

1. MySQL SQL执行规则深度解析1.1 SQL语句的生命周期当一条SQL语句进入MySQL服务器时会经历完整的处理流程连接器建立连接并验证权限查询缓存MySQL 8.0已移除检查是否命中缓存分析器语法解析和词法分析优化器生成执行计划执行器调用存储引擎接口执行结果返回将结果集返回客户端重要提示MySQL 8.0移除了查询缓存功能因为在实际生产环境中命中率往往低于预期且维护缓存带来的开销大于收益。1.2 执行计划详解通过EXPLAIN命令可以查看SQL的执行计划关键列包括列名说明优化关注点type访问类型最好达到range级别key实际使用的索引检查是否使用预期索引rows预估扫描行数数值越小越好Extra额外信息避免出现Using filesort常见的type类型从优到劣排序system const eq_ref ref range index ALL1.3 优化器工作原理MySQL优化器基于成本模型选择执行计划考虑因素包括索引选择性表统计信息临时表创建成本排序操作开销优化器有时会做出错误决策这时可以通过以下方式干预-- 强制使用特定索引 SELECT * FROM table FORCE INDEX(index_name) WHERE ... -- 忽略特定索引 SELECT * FROM table IGNORE INDEX(index_name) WHERE ...2. MySQL索引核心原理2.1 BTree索引结构MySQL索引采用BTree数据结构具有以下特点所有数据存储在叶子节点非叶子节点只存键值叶子节点通过指针连接形成链表树的高度通常为3-4层可支撑千万级数据与B-Tree的对比优势范围查询效率更高磁盘IO次数更少更适合数据库场景2.2 索引类型详解2.2.1 主键索引(Clustered Index)数据按主键物理排序存储每个表必须有且只有一个主键索引建议使用自增整型作为主键2.2.2 二级索引(Secondary Index)叶子节点存储主键值而非数据查询需要回表操作包括普通索引、唯一索引等2.2.3 复合索引遵循最左前缀原则索引列顺序影响使用效果示例INDEX(a,b,c)可以支持a、a,b、a,b,c查询2.3 索引使用场景适合创建索引的情况WHERE条件中的列JOIN关联列ORDER BY排序字段高选择性字段区分度高不建议创建索引的情况频繁更新的列数据量小的表区分度低的列如性别3. 高级索引优化策略3.1 索引下推(ICP)MySQL 5.6引入的优化技术允许在存储引擎层过滤数据-- 传统方式先通过name索引找到所有张%记录再回表检查age30 -- 使用ICP在索引层就过滤name和age减少回表次数 SELECT * FROM users WHERE name LIKE 张% AND age 30;启用条件需要range、ref、eq_ref或ref_or_null访问方式仅适用于InnoDB和MyISAM引擎3.2 覆盖索引优化当查询所需列都包含在索引中时可以避免回表-- 创建复合索引 ALTER TABLE orders ADD INDEX idx_order(date, customer_id, amount); -- 以下查询可以直接使用索引数据 SELECT date, customer_id, amount FROM orders WHERE date BETWEEN 2023-01-01 AND 2023-01-31;3.3 MRR优化Multi-Range Read优化通过以下方式提升性能收集索引扫描得到的主键按主键顺序排序批量回表读取数据启用设置SET optimizer_switchmrron,mrr_cost_basedoff;4. 索引失效场景与排查4.1 常见索引失效情况违反最左前缀原则-- 索引INDEX(a,b,c) WHERE b 1 AND c 2 -- 无法使用索引使用函数或运算WHERE YEAR(create_time) 2023 -- 索引失效类型转换-- phone字段是varchar类型 WHERE phone 13800138000 -- 隐式类型转换导致失效使用OR条件WHERE a 1 OR b 2 -- 除非a和b都有索引4.2 索引使用情况监控通过performance_schema监控索引使用-- 查看未使用的索引 SELECT * FROM sys.schema_unused_indexes; -- 查看索引统计信息 SELECT * FROM mysql.innodb_index_stats WHERE table_name your_table;5. 实战索引优化案例5.1 电商订单查询优化原始查询SELECT * FROM orders WHERE user_id 1001 AND status completed ORDER BY create_time DESC LIMIT 10;优化方案创建复合索引(user_id, status, create_time)使用覆盖索引技巧SELECT id FROM orders WHERE user_id 1001 AND status completed ORDER BY create_time DESC LIMIT 10; -- 然后通过主键获取完整数据减少排序数据量 SELECT * FROM orders WHERE id IN (...);5.2 分页查询优化低效写法SELECT * FROM large_table LIMIT 1000000, 10;优化方案使用索引覆盖延迟关联SELECT t.* FROM large_table t JOIN ( SELECT id FROM large_table WHERE some_indexed_column value ORDER BY id LIMIT 1000000, 10 ) AS tmp ON t.id tmp.id;记录上次查询位置适合有序数据-- 记住上一次查询的最后一个ID SELECT * FROM large_table WHERE id last_id ORDER BY id LIMIT 10;6. MySQL索引维护与管理6.1 索引碎片整理随着数据修改索引会产生碎片影响性能-- 查看表碎片情况 SELECT table_name, data_free/1024/1024 AS frag_mb FROM information_schema.tables WHERE engine InnoDB; -- 重建表会锁表 ALTER TABLE orders ENGINEInnoDB; -- 在线DDL方式MySQL 5.6 ALTER TABLE orders ALGORITHMINPLACE, LOCKNONE, REBUILD;6.2 索引统计信息更新优化器依赖统计信息做决策-- 手动更新统计信息 ANALYZE TABLE orders; -- 查看统计信息 SHOW INDEX FROM orders;6.3 索引设计最佳实践单表索引数量建议不超过5-6个复合索引列数不超过5列避免冗余索引如已有(a,b)索引再建a索引就是冗余定期使用pt-index-usage工具分析索引使用情况实际经验在大型电商系统中通过合理索引设计我们曾将关键查询从2秒优化到50毫秒以下。关键在于理解业务查询模式而不是盲目添加索引。