1. MySQL索引基础与核心概念当数据库表中的数据量达到百万级时没有索引的查询就像在图书馆无目录的藏书堆里找一本特定书籍。MySQL索引本质上是一种特殊的数据结构它能以空间换时间的方式大幅提升数据检索效率。1.1 索引的物理实现原理InnoDB存储引擎默认使用B树结构实现索引这种多路平衡查找树具有以下特性所有数据都存储在叶子节点且叶子节点间通过指针相连形成链表非叶子节点只存储键值和子节点指针树的高度通常维持在3-4层千万级数据也只需3次IO即可定位B树相比哈希索引的优势在于支持范围查询而哈希索引仅适合等值查询。这也是为什么WHERE create_time BETWEEN 2023-01-01 AND 2023-12-31这类条件必须使用B树索引。1.2 索引的类型与适用场景主键索引(PRIMARY KEY)ALTER TABLE users ADD PRIMARY KEY (id);每个表只能有一个自动创建聚簇索引InnoDB值不允许NULL且必须唯一唯一索引(UNIQUE KEY)CREATE UNIQUE INDEX idx_email ON users(email);保证列值的唯一性允许NULL值但NULL值也需唯一常用于业务唯一约束字段普通索引(INDEX)CREATE INDEX idx_name ON users(name);最基本的索引类型无唯一性约束适合高频查询但无需唯一的列组合索引(Composite Index)ALTER TABLE orders ADD INDEX idx_composite (user_id, status, create_time);多列联合构建的索引遵循最左前缀原则字段顺序直接影响索引效果注意全文索引(FULLTEXT)在MySQL 5.6的InnoDB上才支持适用于文本内容的模糊搜索但性能不如专业搜索引擎。2. 索引创建实战指南2.1 单列索引创建标准流程步骤1分析查询模式-- 查看慢查询日志中的TOP SQL SHOW VARIABLES LIKE slow_query_log%;步骤2验证字段选择性-- 计算字段的选择性越接近1越好 SELECT COUNT(DISTINCT status)/COUNT(*) AS selectivity FROM orders;步骤3创建索引-- 基本语法 CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON tbl_name (col_name [(length)] [ASC|DESC],...) [INDEX_TYPE] [WITH PARSER parser_name] [COMMENT string] -- 实际示例为status字段创建普通索引 CREATE INDEX idx_status ON orders(status);步骤4验证索引效果-- 使用EXPLAIN分析执行计划 EXPLAIN SELECT * FROM orders WHERE status shipped;2.2 组合索引设计黄金法则法则1最左前缀原则-- 对于INDEX(a,b,c) WHERE a1 AND b2 -- 能使用索引 WHERE b2 AND c3 -- 不能使用索引法则2基数高的列放左边-- 用户表有gender(基数2)和age(基数70) -- 应该将age放在更左侧 CREATE INDEX idx_age_gender ON users(age, gender);法则3覆盖索引优化-- 设计包含所有查询字段的索引 CREATE INDEX idx_covering ON orders(user_id, status, amount); -- 这样查询就无需回表 SELECT user_id, status FROM orders WHERE user_id100;2.3 特殊场景索引创建前缀索引大文本字段-- 为VARCHAR(255)的address字段创建前20字符索引 CREATE INDEX idx_address ON customers(address(20));函数索引MySQL 8.0-- 为日期格式化查询创建函数索引 CREATE INDEX idx_date_format ON orders((DATE_FORMAT(create_time,%Y-%m)));降序索引MySQL 8.0-- 针对时间倒序查询场景 CREATE INDEX idx_time_desc ON logs(create_time DESC);3. 索引优化与避坑指南3.1 索引失效的七大陷阱隐式类型转换-- user_id是VARCHAR但传入数字 SELECT * FROM users WHERE user_id 100; -- 索引失效使用否定操作符SELECT * FROM products WHERE status ! active; -- 无法走索引前导通配符SELECT * FROM articles WHERE title LIKE %mysql%; -- 全表扫描索引列参与运算SELECT * FROM orders WHERE YEAR(create_time) 2023; -- 函数导致失效OR条件使用不当-- 解决方案改为UNION ALL SELECT * FROM users WHERE age18 OR name张三;违反最左前缀原则-- 对于INDEX(a,b,c): SELECT * FROM table WHERE b1 AND c2; -- 无法使用索引统计信息过时-- 定期执行ANALYZE TABLE更新统计信息 ANALYZE TABLE orders;3.2 索引维护最佳实践监控索引使用率-- 查看未使用的索引 SELECT * FROM sys.schema_unused_indexes;定期重建碎片化索引-- InnoDB表的索引重建 ALTER TABLE orders ENGINEInnoDB;控制索引数量-- 单个表索引建议不超过5-6个 -- 可通过以下查询评估现有索引 SELECT table_name, index_name, stat_value FROM mysql.innodb_index_stats WHERE stat_namesize AND database_nameyour_db;4. 高级索引应用场景4.1 分页查询优化方案低效写法深分页问题SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;优化方案1延迟关联SELECT t.* FROM orders t JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 10) tmp ON t.id tmp.id;优化方案2基于上次ID-- 假设上次获取的最大ID是999990 SELECT * FROM orders WHERE id 999990 ORDER BY id LIMIT 10;4.2 索引合并优化当查询条件涉及多个独立索引时MySQL可能使用Index Merge-- 需要分别在last_name和first_name上有索引 EXPLAIN SELECT * FROM employees WHERE last_nameSmith OR first_nameJohn;可通过optimizer_switch控制此行为SET optimizer_switchindex_mergeoff; -- 禁用索引合并4.3 自适应哈希索引InnoDB会自动为频繁访问的索引页建立哈希索引-- 查看AHI使用情况 SHOW ENGINE INNODB STATUS\G -- 在Buffer Pool部分查找Hash table size可通过参数调整innodb_adaptive_hash_indexON -- 默认开启 innodb_adaptive_hash_index_parts8 -- 分区数减少锁争用5. 生产环境索引管理策略5.1 在线DDL操作MySQL 5.6的Online DDL-- 添加索引而不锁表INPLACE算法 ALTER TABLE orders ADD INDEX idx_amount (amount), ALGORITHMINPLACE, LOCKNONE;pt-online-schema-change工具pt-online-schema-change \ --alterADD INDEX idx_email (email) \ Dyour_db,tusers \ --execute5.2 索引变更风险评估使用EXPLAIN验证-- 先获取当前执行计划作为基准 EXPLAIN FORMATJSON SELECT * FROM orders WHERE statuscompleted;使用optimizer_trace分析SET optimizer_traceenabledon; SELECT * FROM orders WHERE amount 1000; SELECT * FROM information_schema.optimizer_trace; SET optimizer_traceenabledoff;5.3 索引生命周期管理索引版本控制-- 使用注释记录索引创建目的 CREATE INDEX idx_phone ON customers(phone) COMMENT 用于登录验证 创建于2023-08 by 张工程师;定期审查脚本-- 生成索引审查报告 SELECT table_name, index_name, GROUP_CONCAT(column_name ORDER BY seq_in_index) AS columns, index_type, CASE non_unique WHEN 1 THEN YES ELSE NO END AS is_unique, index_comment FROM information_schema.statistics WHERE table_schema DATABASE() GROUP BY table_name, index_name;在多年的MySQL优化实践中我发现最容易被忽视的是索引的维护成本——每个索引都会降低INSERT/UPDATE/DELETE性能。建议每月进行一次索引使用率审查对于三个月内未被使用的索引应该考虑删除。同时组合索引的字段顺序需要根据实际查询模式不断调整这是一个持续优化的过程。