1. MySQL性能分析与索引优化实战指南作为一名常年与MySQL打交道的数据库工程师我处理过太多因索引不当导致的性能问题。记得去年双十一大促前某电商平台的订单查询接口突然从200ms飙升到8秒整个技术团队熬了三个通宵才定位到问题根源——一个缺失的联合索引。今天我就结合这类实战案例系统梳理MySQL性能分析的核心方法和索引优化的黄金法则。2. MySQL性能分析三板斧2.1 慢查询日志捕捉性能杀手慢查询日志是DBA的听诊器通过修改my.cnf配置启用slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 # 超过1秒的查询 log_queries_not_using_indexes 1 # 记录未走索引的查询关键分析工具是mysqldumpslow这个Perl脚本能归类慢查询mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log输出示例会显示Count: 5 Time3.21s (16s) Lock0.00s (0s) Rows1000.0 (5000) SELECT * FROM orders WHERE user_idN AND statuspending这表示该查询执行了5次平均耗时3.21秒且每次返回1000行——显然需要优化。注意生产环境建议将long_query_time设为0.5秒以下很多性能问题就藏在500ms-1s这个区间的查询中2.2 EXPLAIN执行计划解密拿到慢查询后用EXPLAIN看执行计划EXPLAIN SELECT * FROM products WHERE category_id5 AND price100;重点关注的列type从优到差依次为 system const eq_ref ref range index ALLkey实际使用的索引rows预估扫描行数Extra常见危险信号Using filesort需要额外排序Using temporary用了临时表Using where存储引擎层过滤2.3 性能模式(Performance Schema)MySQL 5.7推荐使用performance_schema它能监控更细粒度的性能数据-- 查看最耗时的SQL事件 SELECT sql_text, SUM_TIMER_WAIT/1000000000 AS latency_sec FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;3. 索引设计核心原理3.1 B树索引的物理结构MySQL索引采用B树结构其特点所有数据存储在叶子节点非叶子节点只存键值叶子节点通过指针连接形成链表3层B树可支撑约2000万数据假设每页16KB主键8B通过innodb_page_size可查看页大小SHOW VARIABLES LIKE innodb_page_size;3.2 索引选择性与基数选择性计算公式选择性 不重复值数量 / 总记录数高选择性字段更适合建索引。通过SHOW INDEX查看基数SHOW INDEX FROM users;Cardinality列显示估算的唯一值数量这个值通过采样统计得来有时需要手动更新ANALYZE TABLE users;3.3 聚簇索引与二级索引InnoDB的聚簇索引特点主键作为聚簇索引若未定义主键选择第一个UNIQUE NOT NULL索引都没有则隐式创建6字节的row_id二级索引查询需要回表-- 假设name是二级索引 SELECT * FROM users WHERE name张三;执行过程在name索引树找到张三的主键ID用主键ID到聚簇索引查找完整记录4. 实战索引优化策略4.1 联合索引的最左前缀原则创建联合索引ALTER TABLE orders ADD INDEX idx_status_created(status, created_at);以下查询能利用索引SELECT * FROM orders WHERE statusshipped; SELECT * FROM orders WHERE statusshipped AND created_at2023-01-01;但以下查询不能完全利用索引SELECT * FROM orders WHERE created_at2023-01-01;经验将高选择性字段放在联合索引左侧但也要考虑查询频率。我曾将user_id放在status前虽然user_id选择性更高但80%查询都带status条件调整后QPS提升了3倍4.2 索引下推(ICP)MySQL 5.6引入的索引下推优化可以在索引遍历时就进行条件过滤-- 假设有索引(name, age) SELECT * FROM users WHERE name LIKE 张% AND age20;无ICP时先通过name找到所有张姓记录再回表查age20 有ICP时在索引内部就过滤age20减少回表次数通过optimizer_switch控制SET optimizer_switchindex_condition_pushdownon;4.3 覆盖索引优化当索引包含所有查询字段时无需回表-- 创建覆盖索引 ALTER TABLE products ADD INDEX idx_category_price_name(category_id, price, name); -- 以下查询可以直接从索引获取数据 SELECT name, price FROM products WHERE category_id5;通过EXPLAIN的Extra列看到Using index即表示使用了覆盖索引。5. 索引失效的七大陷阱5.1 隐式类型转换常见于字符串字段用数字查询-- mobile是varchar类型 SELECT * FROM users WHERE mobile13800138000; -- 索引失效 SELECT * FROM users WHERE mobile13800138000; -- 走索引5.2 函数操作索引列-- 假设created_at有索引 SELECT * FROM orders WHERE DATE(created_at)2023-01-01; -- 失效 -- 应改为范围查询 SELECT * FROM orders WHERE created_at2023-01-01 00:00:00 AND created_at2023-01-02 00:00:00;5.3 前导通配符LIKESELECT * FROM products WHERE name LIKE %手机%; -- 全表扫描 SELECT * FROM products WHERE name LIKE 小米%; -- 可能走索引对于全文搜索需求应该使用FULLTEXT索引ALTER TABLE products ADD FULLTEXT INDEX ft_idx_name(name); SELECT * FROM products WHERE MATCH(name) AGAINST(手机);5.4 OR条件不当使用-- 假设name有索引而age没有 SELECT * FROM users WHERE name张三 OR age20; -- 全表扫描 -- 优化方案1改用UNION SELECT * FROM users WHERE name张三 UNION SELECT * FROM users WHERE age20; -- 优化方案2为age添加索引6. 高级索引调优技巧6.1 索引跳跃扫描(MySQL 8.0)对于联合索引(a,b)即使查询条件没有a也可能走索引-- 有索引(gender, age) SELECT * FROM users WHERE age20; -- 执行计划可能显示typerangekeyidx_gender_age原理是优化器会枚举gender的所有值如M,F相当于执行SELECT * FROM users WHERE genderM AND age20 UNION ALL SELECT * FROM users WHERE genderF AND age20;6.2 降序索引优化MySQL 8.0支持真正的降序索引ALTER TABLE orders ADD INDEX idx_created_desc(created_at DESC);对于以下查询效率更高SELECT * FROM orders ORDER BY created_at DESC LIMIT 100;6.3 不可见索引测试索引删除影响时可以先设为不可见ALTER TABLE users ALTER INDEX idx_name INVISIBLE; -- 观察业务影响后决定是否删除 ALTER TABLE users ALTER INDEX idx_name VISIBLE;7. 索引监控与维护7.1 索引使用统计通过sys库查看索引使用情况SELECT * FROM sys.schema_index_statistics WHERE table_schemayour_db;7.2 冗余索引检测使用pt-index-usage工具分析pt-index-usage /var/log/mysql/mysql-slow.log --hostlocalhost --userroot7.3 索引碎片整理定期优化表减少碎片-- 查看碎片率 SELECT table_name, index_name, ROUND(stat_value * innodb_page_size / 1024 / 1024, 2) AS size_mb, ROUND(stat_value * 100 / table_rows, 2) AS frag_ratio FROM mysql.innodb_index_stats WHERE stat_namesize AND database_nameyour_db; -- 重建索引 ALTER TABLE orders ENGINEInnoDB;8. 真实案例电商系统索引优化某电商平台商品表结构CREATE TABLE products ( id BIGINT PRIMARY KEY, category_id INT, merchant_id INT, price DECIMAL(10,2), stock INT, is_on_sale TINYINT, created_at DATETIME, modified_at DATETIME, INDEX idx_category (category_id), INDEX idx_merchant (merchant_id) );问题查询SELECT * FROM products WHERE category_id3 AND is_on_sale1 AND price BETWEEN 100 AND 500 ORDER BY modified_at DESC LIMIT 20 OFFSET 0;优化步骤分析发现需要扫描3万行然后排序创建联合索引ALTER TABLE products ADD INDEX idx_cate_sale_price_mod( category_id, is_on_sale, price, modified_at );修改查询为SELECT * FROM products WHERE category_id3 AND is_on_sale1 AND price100 AND price500 ORDER BY modified_at DESC LIMIT 20 OFFSET 0;优化后执行时间从1200ms降到28ms 1. MySQL性能分析与索引优化实战指南作为一名常年与MySQL打交道的数据库工程师我处理过太多因索引不当导致的性能问题。记得去年双十一大促前某电商平台的订单查询接口突然从200ms飙升到8秒整个技术团队熬了三个通宵才定位到问题根源——一个缺失的联合索引。今天我就结合这类实战案例系统梳理MySQL性能分析的核心方法和索引优化的黄金法则。2. MySQL性能分析三板斧2.1 慢查询日志捕捉性能杀手慢查询日志是DBA的听诊器通过修改my.cnf配置启用slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 # 超过1秒的查询 log_queries_not_using_indexes 1 # 记录未走索引的查询关键分析工具是mysqldumpslow这个Perl脚本能归类慢查询mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log输出示例会显示Count: 5 Time3.21s (16s) Lock0.00s (0s) Rows1000.0 (5000) SELECT * FROM orders WHERE user_idN AND statuspending这表示该查询执行了5次平均耗时3.21秒且每次返回1000行——显然需要优化。注意生产环境建议将long_query_time设为0.5秒以下很多性能问题就藏在500ms-1s这个区间的查询中2.2 EXPLAIN执行计划解密拿到慢查询后用EXPLAIN看执行计划EXPLAIN SELECT * FROM products WHERE category_id5 AND price100;重点关注的列type从优到差依次为 system const eq_ref ref range index ALLkey实际使用的索引rows预估扫描行数Extra常见危险信号Using filesort需要额外排序Using temporary用了临时表Using where存储引擎层过滤2.3 性能模式(Performance Schema)MySQL 5.7推荐使用performance_schema它能监控更细粒度的性能数据-- 查看最耗时的SQL事件 SELECT sql_text, SUM_TIMER_WAIT/1000000000 AS latency_sec FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;3. 索引设计核心原理3.1 B树索引的物理结构MySQL索引采用B树结构其特点所有数据存储在叶子节点非叶子节点只存键值叶子节点通过指针连接形成链表3层B树可支撑约2000万数据假设每页16KB主键8B通过innodb_page_size可查看页大小SHOW VARIABLES LIKE innodb_page_size;3.2 索引选择性与基数选择性计算公式选择性 不重复值数量 / 总记录数高选择性字段更适合建索引。通过SHOW INDEX查看基数SHOW INDEX FROM users;Cardinality列显示估算的唯一值数量这个值通过采样统计得来有时需要手动更新ANALYZE TABLE users;3.3 聚簇索引与二级索引InnoDB的聚簇索引特点主键作为聚簇索引若未定义主键选择第一个UNIQUE NOT NULL索引都没有则隐式创建6字节的row_id二级索引查询需要回表-- 假设name是二级索引 SELECT * FROM users WHERE name张三;执行过程在name索引树找到张三的主键ID用主键ID到聚簇索引查找完整记录4. 实战索引优化策略4.1 联合索引的最左前缀原则创建联合索引ALTER TABLE orders ADD INDEX idx_status_created(status, created_at);以下查询能利用索引SELECT * FROM orders WHERE statusshipped; SELECT * FROM orders WHERE statusshipped AND created_at2023-01-01;但以下查询不能完全利用索引SELECT * FROM orders WHERE created_at2023-01-01;经验将高选择性字段放在联合索引左侧但也要考虑查询频率。我曾将user_id放在status前虽然user_id选择性更高但80%查询都带status条件调整后QPS提升了3倍4.2 索引下推(ICP)MySQL 5.6引入的索引下推优化可以在索引遍历时就进行条件过滤-- 假设有索引(name, age) SELECT * FROM users WHERE name LIKE 张% AND age20;无ICP时先通过name找到所有张姓记录再回表查age20 有ICP时在索引内部就过滤age20减少回表次数通过optimizer_switch控制SET optimizer_switchindex_condition_pushdownon;4.3 覆盖索引优化当索引包含所有查询字段时无需回表-- 创建覆盖索引 ALTER TABLE products ADD INDEX idx_category_price_name(category_id, price, name); -- 以下查询可以直接从索引获取数据 SELECT name, price FROM products WHERE category_id5;通过EXPLAIN的Extra列看到Using index即表示使用了覆盖索引。5. 索引失效的七大陷阱5.1 隐式类型转换常见于字符串字段用数字查询-- mobile是varchar类型 SELECT * FROM users WHERE mobile13800138000; -- 索引失效 SELECT * FROM users WHERE mobile13800138000; -- 走索引5.2 函数操作索引列-- 假设created_at有索引 SELECT * FROM orders WHERE DATE(created_at)2023-01-01; -- 失效 -- 应改为范围查询 SELECT * FROM orders WHERE created_at2023-01-01 00:00:00 AND created_at2023-01-02 00:00:00;5.3 前导通配符LIKESELECT * FROM products WHERE name LIKE %手机%; -- 全表扫描 SELECT * FROM products WHERE name LIKE 小米%; -- 可能走索引对于全文搜索需求应该使用FULLTEXT索引ALTER TABLE products ADD FULLTEXT INDEX ft_idx_name(name); SELECT * FROM products WHERE MATCH(name) AGAINST(手机);5.4 OR条件不当使用-- 假设name有索引而age没有 SELECT * FROM users WHERE name张三 OR age20; -- 全表扫描 -- 优化方案1改用UNION SELECT * FROM users WHERE name张三 UNION SELECT * FROM users WHERE age20; -- 优化方案2为age添加索引6. 高级索引调优技巧6.1 索引跳跃扫描(MySQL 8.0)对于联合索引(a,b)即使查询条件没有a也可能走索引-- 有索引(gender, age) SELECT * FROM users WHERE age20; -- 执行计划可能显示typerangekeyidx_gender_age原理是优化器会枚举gender的所有值如M,F相当于执行SELECT * FROM users WHERE genderM AND age20 UNION ALL SELECT * FROM users WHERE genderF AND age20;6.2 降序索引优化MySQL 8.0支持真正的降序索引ALTER TABLE orders ADD INDEX idx_created_desc(created_at DESC);对于以下查询效率更高SELECT * FROM orders ORDER BY created_at DESC LIMIT 100;6.3 不可见索引测试索引删除影响时可以先设为不可见ALTER TABLE users ALTER INDEX idx_name INVISIBLE; -- 观察业务影响后决定是否删除 ALTER TABLE users ALTER INDEX idx_name VISIBLE;7. 索引监控与维护7.1 索引使用统计通过sys库查看索引使用情况SELECT * FROM sys.schema_index_statistics WHERE table_schemayour_db;7.2 冗余索引检测使用pt-index-usage工具分析pt-index-usage /var/log/mysql/mysql-slow.log --hostlocalhost --userroot7.3 索引碎片整理定期优化表减少碎片-- 查看碎片率 SELECT table_name, index_name, ROUND(stat_value * innodb_page_size / 1024 / 1024, 2) AS size_mb, ROUND(stat_value * 100 / table_rows, 2) AS frag_ratio FROM mysql.innodb_index_stats WHERE stat_namesize AND database_nameyour_db; -- 重建索引 ALTER TABLE orders ENGINEInnoDB;8. 真实案例电商系统索引优化某电商平台商品表结构CREATE TABLE products ( id BIGINT PRIMARY KEY, category_id INT, merchant_id INT, price DECIMAL(10,2), stock INT, is_on_sale TINYINT, created_at DATETIME, modified_at DATETIME, INDEX idx_category (category_id), INDEX idx_merchant (merchant_id) );问题查询SELECT * FROM products WHERE category_id3 AND is_on_sale1 AND price BETWEEN 100 AND 500 ORDER BY modified_at DESC LIMIT 20 OFFSET 0;优化步骤分析发现需要扫描3万行然后排序创建联合索引ALTER TABLE products ADD INDEX idx_cate_sale_price_mod( category_id, is_on_sale, price, modified_at );修改查询为SELECT * FROM products WHERE category_id3 AND is_on_sale1 AND price100 AND price500 ORDER BY modified_at DESC LIMIT 20 OFFSET 0;优化后执行时间从1200ms降到28ms