1. 为什么MySQL索引是性能优化的第一道门槛第一次在生产环境遇到慢查询时我盯着那个5秒的查询时间不知所措。直到打开EXPLAIN看到Using filesort的红色警告才意识到没有正确使用索引的代价有多大。MySQL索引就像图书馆的目录系统——没有它我们只能进行全表扫描Full Table Scan这种最笨重的数据检索方式。B树作为MySQL索引的标准数据结构其优势在于三层高度即可支撑千万级数据假设每页16KB单条索引记录16字节单页可存约1000条记录所有数据都存储在叶子节点且叶子节点通过指针相连完美支持范围查询查询时间复杂度稳定在O(log n)但索引也是一把双刃剑。上周我们一个核心接口突然超时排查发现是新加的联合索引字段顺序与查询条件不匹配。这种索引失效的情况在复杂业务系统中尤为常见也是本文要重点剖析的问题场景。2. B树索引的物理实现细节2.1 InnoDB的索引组织方式InnoDB存储引擎采用聚簇索引Clustered Index结构其特点令人惊叹-- 表结构 CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(100), age INT, INDEX idx_age (age) ) ENGINEInnoDB;在这个表中主键索引的叶子节点直接包含完整行数据而二级索引如idx_age的叶子节点存储的是主键值。这种设计带来两个重要特性通过主键查询只需一次索引扫描二级索引查询需要回表Bookmark Lookup关键提示这也是为什么建议使用自增主键——随机主键会导致频繁的页分裂实测插入性能可能相差10倍以上2.2 索引页的内部结构每个16KB的索引页包含文件头38字节记录页号、前后页指针等页目录Slots对记录进行分组实现页内二分查找行记录实际索引数据按索引键排序存储通过SHOW ENGINE INNODB STATUS可以看到索引的物理统计信息其中PAGE_SIZE和NUMBER_RECORDS是重点监控指标。3. 索引失效的七种致命场景3.1 最左前缀原则破坏这是联合索引最常见的失效场景-- 联合索引为 (a,b,c) SELECT * FROM table WHERE b 1 AND c 2; -- 失效 SELECT * FROM table WHERE a 1 AND c 2; -- 部分使用(a)我在金融系统中见过一个典型案例交易记录表有(date, account_id)的联合索引但查询条件只有account_id导致全表扫描800万条记录。3.2 隐式类型转换陷阱当字段类型与条件类型不匹配时-- user_id是varchar类型 SELECT * FROM users WHERE user_id 10086; -- 发生类型转换这种问题在PHP等弱类型语言开发的系统中尤为常见。解决方案是使用CAST(10086 AS CHAR)或保持类型一致。3.3 函数操作导致索引失效任何对索引列的函数操作都会使索引失效SELECT * FROM orders WHERE DATE(create_time) 2023-01-01; -- 全表扫描 -- 应改为 SELECT * FROM orders WHERE create_time BETWEEN 2023-01-01 00:00:00 AND 2023-01-01 23:59:59;3.4 范围查询阻断后续索引范围查询, , LIKE等会阻断联合索引后续字段的使用-- 索引(a,b,c) SELECT * FROM table WHERE a 1 AND b 10 AND c 2; -- 只能用到a,b3.5 使用OR条件非覆盖索引的OR条件通常导致全表扫描-- name有索引age无索引 SELECT * FROM users WHERE name 张三 OR age 25;解决方案是改用UNION ALLSELECT * FROM users WHERE name 张三 UNION ALL SELECT * FROM users WHERE age 25 AND name ! 张三;3.6 IS NULL/IS NOT NULL的特殊性在MySQL 5.7版本中通过优化ref_or_null访问方法IS NULL条件可以使用索引-- 索引列允许NULL时 SELECT * FROM users WHERE name IS NULL; -- 可以使用索引3.7 索引选择性不足当索引列不同值很少时如性别、状态字段优化器可能选择全表扫描。经验值是选择性不同值数量/总行数低于10%时索引效果较差。4. 高级索引优化策略4.1 三星索引设计原则理想的索引应该满足第一星WHERE条件匹配索引列减少扫描范围第二星ORDER BY/GROUP BY使用索引排序避免filesort第三星SELECT列包含在索引中避免回表示例-- 查询SELECT name FROM users WHERE age20 ORDER BY create_time; -- 三星索引 ALTER TABLE users ADD INDEX idx_age_create_time_name (age, create_time, name);4.2 索引跳跃扫描(Index Skip Scan)MySQL 8.0引入的新特性当联合索引前导列不同值较少时-- 索引(gender, age) SELECT * FROM users WHERE age 20; -- 8.0可以跳跃扫描gender4.3 降序索引优化对于需要逆序扫描的场景-- 8.0支持真正的降序索引 ALTER TABLE orders ADD INDEX idx_create_time (create_time DESC);4.4 索引合并优化当WHERE条件包含多个单列索引时MySQL可能使用Index Merge-- 有index(a)和index(b) SELECT * FROM table WHERE a 1 OR b 2;但要注意只适用于OR条件合并操作本身有性能开销通常不如设计合适的联合索引高效5. 生产环境实战案例5.1 电商商品搜索优化原始查询SELECT * FROM products WHERE category_id 5 AND price BETWEEN 100 AND 500 AND status 1 ORDER BY sales_volume DESC LIMIT 20;优化方案创建(category_id, status, price, sales_volume)的联合索引使用延迟关联减少回表SELECT p.* FROM products p JOIN ( SELECT id FROM products WHERE category_id 5 AND status 1 AND price BETWEEN 100 AND 500 ORDER BY sales_volume DESC LIMIT 20 ) tmp ON p.id tmp.id;5.2 社交平台Feed流优化分页查询的深度分页问题-- 传统分页 SELECT * FROM posts WHERE user_id 123 ORDER BY create_time DESC LIMIT 10000, 20;优化方案使用游标分页SELECT * FROM posts WHERE user_id 123 AND create_time 2023-06-01 00:00:00 ORDER BY create_time DESC LIMIT 20;确保有(user_id, create_time)的联合索引6. 索引监控与维护6.1 索引使用情况分析通过performance_schema监控-- 查看未使用的索引 SELECT * FROM sys.schema_unused_indexes; -- 索引使用统计 SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage;6.2 索引碎片整理定期检查表碎片-- 碎片率超过30%建议优化 SELECT table_name, index_name, ROUND(stat_value * innodb_page_size / 1024 / 1024, 2) size_mb, stat_description FROM mysql.innodb_index_stats WHERE stat_name size AND database_name your_db;优化方法-- Online DDL方式 ALTER TABLE your_table ENGINEInnoDB; -- pt-online-schema-change工具更安全6.3 索引成本计算原理优化器选择索引的依据索引成本 I/O成本读取索引页 CPU成本记录比较可以通过EXPLAIN FORMATJSON查看详细成本计算{ query_cost: 1.20, cost_info: { read_cost: 1.00, eval_cost: 0.20, prefix_cost: 1.20, data_read_per_join: 16K } }7. 特殊索引类型与应用7.1 全文索引的实战技巧MySQL 5.6的InnoDB全文索引ALTER TABLE articles ADD FULLTEXT INDEX ft_idx (title, body); -- 自然语言搜索 SELECT * FROM articles WHERE MATCH(title, body) AGAINST(数据库优化 IN NATURAL LANGUAGE MODE); -- 布尔搜索 SELECT * FROM articles WHERE MATCH(title, body) AGAINST(MySQL -Oracle IN BOOLEAN MODE);性能提示当数据量超过100万时考虑改用Elasticsearch等专业搜索引擎7.2 空间索引(R-Tree)地理数据查询优化-- 创建空间索引 ALTER TABLE locations ADD SPATIAL INDEX(pt); -- 查找5公里内的点 SELECT * FROM locations WHERE ST_Distance_Sphere(pt, POINT(116.404, 39.915)) 5000;7.3 函数索引(MySQL 8.0)对计算列建立索引-- 创建计算列 ALTER TABLE users ADD COLUMN name_upper VARCHAR(100) AS (UPPER(name)); -- 建立索引 CREATE INDEX idx_name_upper ON users(name_upper);8. 索引设计的最佳实践单表索引数量控制通常不超过5-6个避免写操作性能下降字段选择原则高选择性字段优先常用WHERE条件字段经常ORDER BY/GROUP BY的字段长度控制对长字符串使用前缀索引ALTER TABLE logs ADD INDEX idx_url(url(100));避免冗余索引如已有(a,b)索引单独的a索引就是冗余的定期审查使用pt-index-usage工具分析索引使用情况在最近一次系统优化中我们通过删除3个未使用的冗余索引使写入TPS提升了15%。这提醒我们索引不是越多越好精准的索引设计才是王道。
