1. MySQL索引基础概念解析索引是MySQL数据库中用于加速数据检索的关键数据结构它类似于书籍的目录能够帮助数据库引擎快速定位到表中的特定数据行。在MySQL中索引主要基于B树B-Tree的一种变体实现这种数据结构具有平衡查找的特性能够保证在最坏情况下依然保持O(log n)的查询效率。注意虽然索引能显著提升查询性能但每个额外的索引都会增加写入操作的开销因为每次INSERT、UPDATE或DELETE操作都需要维护索引结构。MySQL支持多种索引类型包括普通索引INDEX最基本的索引类型没有特殊约束唯一索引UNIQUE确保索引列的值唯一主键索引PRIMARY KEY特殊的唯一索引不允许NULL值全文索引FULLTEXT用于全文搜索组合索引复合索引在多个列上建立的索引2. 索引创建方法与语法详解2.1 创建表时定义索引最直接的索引创建方式是在建表语句中直接定义。以下是包含各种索引类型的完整建表示例CREATE TABLE user ( id int(11) NOT NULL AUTO_INCREMENT, username varchar(50) NOT NULL, email varchar(100) NOT NULL, phone varchar(20), created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), -- 主键索引 UNIQUE KEY idx_email (email), -- 唯一索引 KEY idx_username (username), -- 普通索引 KEY idx_created_username (created_at, username) -- 复合索引 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;2.2 使用ALTER TABLE添加索引对于已存在的表可以使用ALTER TABLE语句添加索引-- 添加普通索引 ALTER TABLE user ADD INDEX idx_phone (phone); -- 添加唯一索引 ALTER TABLE user ADD UNIQUE INDEX idx_username_unique (username); -- 添加复合索引 ALTER TABLE user ADD INDEX idx_email_created (email, created_at);2.3 使用CREATE INDEX语句CREATE INDEX是专门用于创建索引的语法实际上是ALTER TABLE的简写形式-- 创建普通索引 CREATE INDEX idx_phone ON user (phone); -- 创建唯一索引 CREATE UNIQUE INDEX idx_username_unique ON user (username); -- 创建复合索引 CREATE INDEX idx_email_created ON user (email, created_at);提示在大型表上创建索引可能会锁表并影响业务建议在业务低峰期执行或者使用在线DDL工具如pt-online-schema-change。3. 索引设计的最佳实践3.1 选择合适的索引列不是所有列都适合建立索引通常应考虑以下列WHERE子句中频繁使用的列JOIN操作中使用的列ORDER BY、GROUP BY子句中使用的列具有高选择性的列即列中不同值的数量多3.2 复合索引的设计原则复合索引多列索引的设计需要遵循最左前缀原则索引(a,b,c)可以用于查询条件为a、a,b或a,b,c的情况但无法用于单独查询b或c的情况设计复合索引时应将选择性高的列放在前面考虑查询频率和排序需求避免创建过多复合索引一般不超过5个3.3 索引优化的常见误区过度索引每个额外的索引都会增加写入开销和维护成本索引列顺序不当复合索引列顺序应考虑查询模式索引列使用函数如WHERE YEAR(create_time) 2023会导致索引失效使用!或NOT IN这些操作通常无法使用索引索引列参与计算如WHERE price*2 100会使索引失效4. 索引性能分析与优化4.1 使用EXPLAIN分析查询EXPLAIN是分析查询性能的关键工具可以显示MySQL如何使用索引EXPLAIN SELECT * FROM user WHERE username john;重点关注以下列type从最好到最差依次为system const eq_ref ref range index ALLpossible_keys可能使用的索引key实际使用的索引rows预估需要检查的行数Extra额外信息如Using index表示使用了覆盖索引4.2 索引维护与监控定期检查未使用的索引SELECT * FROM sys.schema_unused_indexes WHERE object_schema your_database;监控索引使用情况SELECT * FROM sys.schema_index_statistics WHERE table_schema your_database;4.3 索引重建与优化随着数据变化索引可能会变得碎片化可以定期优化-- 重建表索引 ALTER TABLE user ENGINEInnoDB; -- 或优化表 OPTIMIZE TABLE user;5. 高级索引技巧与场景应用5.1 覆盖索引优化当查询的所有列都包含在索引中时MySQL可以直接从索引获取数据而无需回表-- 假设有索引(username, email) SELECT username, email FROM user WHERE username john;5.2 索引条件下推(ICP)MySQL 5.6支持索引条件下推可以在存储引擎层过滤数据-- 假设有索引(a,b) SELECT * FROM table WHERE a x AND b LIKE %y%;5.3 函数索引(MySQL 8.0)MySQL 8.0支持函数索引可以基于表达式创建索引-- 创建函数索引 ALTER TABLE user ADD INDEX idx_lower_username ((LOWER(username))); -- 使用函数索引 SELECT * FROM user WHERE LOWER(username) john;5.4 不可见索引(MySQL 8.0)可以创建不可见索引进行测试不影响现有查询-- 创建不可见索引 ALTER TABLE user ADD INDEX idx_test (phone) INVISIBLE; -- 切换索引可见性 ALTER TABLE user ALTER INDEX idx_test VISIBLE;6. 常见问题解决方案6.1 索引失效的常见场景隐式类型转换如字符串列与数字比较-- phone是varchar类型 SELECT * FROM user WHERE phone 123456; -- 索引失效前导通配符LIKE %xxx无法使用索引SELECT * FROM user WHERE username LIKE %john%; -- 索引失效OR条件不当使用OR两侧条件需都有索引-- 假设只有username有索引 SELECT * FROM user WHERE username john OR phone 123; -- 索引失效6.2 大表添加索引的实践方案对于大型生产表添加索引需要谨慎先在测试环境验证索引效果使用pt-online-schema-change等工具在线添加在业务低峰期执行监控服务器资源使用情况6.3 唯一索引与重复数据处理处理已有重复数据时添加唯一索引-- 1. 先检查重复数据 SELECT email, COUNT(*) FROM user GROUP BY email HAVING COUNT(*) 1; -- 2. 处理重复数据(根据业务需求决定保留哪条) -- 3. 添加唯一索引 ALTER TABLE user ADD UNIQUE INDEX idx_email_unique (email);7. 不同存储引擎的索引特性7.1 InnoDB索引特点使用B树索引结构主键索引是聚簇索引数据存储在叶子节点二级索引存储主键值而非数据指针支持行级锁和MVCC7.2 MyISAM索引特点使用B树索引结构主键索引和非主键索引结构相同索引和数据分开存储只支持表级锁7.3 内存引擎索引特点默认使用HASH索引支持可选的B树索引数据完全存储在内存中服务器重启后数据丢失8. 索引与分区表的协同使用当表数据量非常大时可以考虑分区与索引结合CREATE TABLE large_table ( id bigint(20) NOT NULL AUTO_INCREMENT, user_id int(11) NOT NULL, created_at datetime NOT NULL, data text, PRIMARY KEY (id, created_at), KEY idx_user_id (user_id) ) ENGINEInnoDB PARTITION BY RANGE (YEAR(created_at)) ( PARTITION p2020 VALUES LESS THAN (2021), PARTITION p2021 VALUES LESS THAN (2022), PARTITION p2022 VALUES LESS THAN (2023), PARTITION pmax VALUES LESS THAN MAXVALUE );分区表索引使用注意事项分区键必须是主键的一部分全局索引和本地索引的选择查询条件应包含分区键以实现分区裁剪9. 索引与查询重写技巧有时可以通过重写查询来更好地利用索引9.1 范围查询优化-- 原始查询(可能无法有效使用索引) SELECT * FROM orders WHERE YEAR(order_date) 2023; -- 优化后查询(可以使用索引) SELECT * FROM orders WHERE order_date 2023-01-01 AND order_date 2024-01-01;9.2 OR条件优化-- 原始查询(可能导致索引失效) SELECT * FROM products WHERE category_id 5 OR price 100; -- 优化为UNION查询(可以分别使用索引) SELECT * FROM products WHERE category_id 5 UNION SELECT * FROM products WHERE price 100;9.3 分页查询优化-- 低效的分页(偏移量大时性能差) SELECT * FROM users ORDER BY id LIMIT 10000, 20; -- 优化后的分页(使用索引覆盖) SELECT * FROM users WHERE id (SELECT id FROM users ORDER BY id LIMIT 10000, 1) ORDER BY id LIMIT 20;10. 索引监控与长期维护策略建立完善的索引监控体系定期审查索引使用情况SELECT * FROM sys.schema_index_statistics;识别冗余索引SELECT * FROM sys.schema_redundant_indexes;设置索引使用监控-- 开启performance_schema UPDATE performance_schema.setup_consumers SET ENABLED YES WHERE NAME LIKE %index%;建立索引变更流程测试环境验证影响评估变更窗口选择回滚方案准备文档化索引策略记录每个索引的创建目的关联的查询模式创建时间和维护责任人