1. MySQL索引基础概念与核心原理1.1 索引的本质与数据结构索引的本质是数据库引擎为了加速数据检索而创建的有序数据结构。就像图书馆的图书目录卡片通过建立特定字段的快速查找路径避免全表扫描的低效操作。MySQL中最常用的索引类型是B树结构这是经过多年验证最适合磁盘存储的平衡查找树。B树索引具有几个关键特性所有数据都存储在叶子节点非叶子节点仅存储键值叶子节点通过指针连接形成有序链表树的高度通常维持在3-4层保证千万级数据也能在3-4次IO内定位注意虽然哈希索引理论上具有O(1)的查询复杂度但InnoDB引擎中只有显式创建的内存哈希表才会生效默认的自适应哈希索引仅作为内部优化手段。1.2 索引类型全景图MySQL支持多种索引类型每种都有特定的适用场景索引类型存储引擎支持特点PRIMARY KEY所有引擎唯一且非空表的主标识InnoDB会将其作为聚簇索引UNIQUE INDEX所有引擎保证列值唯一性允许NULL值INDEX/KEY所有引擎普通二级索引无唯一性约束FULLTEXTInnoDB/MyISAM全文检索专用索引支持MATCH AGAINST语法SPATIALMyISAM地理空间数据索引组合索引所有引擎多列联合索引遵循最左前缀原则1.3 聚簇索引与二级索引InnoDB引擎的索引设计尤为精妙聚簇索引表数据按照主键顺序物理存储主键索引的叶子节点直接包含完整行数据二级索引叶子节点存储的是主键值而非数据指针需要回表查询这种设计带来两个重要影响主键查询性能极高只需一次索引查找二级索引查询需要额外的主键查找除非索引覆盖-- 查看表的索引信息 SHOW INDEX FROM employees; -- 查看索引使用情况 EXPLAIN SELECT * FROM employees WHERE last_name Smith;2. 高性能索引策略精要2.1 索引列选择黄金法则选择索引列需要考虑以下因素高选择性原则区分度高的列优先计算公式选择性 COUNT(DISTINCT column) / COUNT(*)当选择性 0.2 时通常值得建索引常用WHERE条件频繁作为查询条件的列连接字段JOIN操作中使用的列排序/分组字段ORDER BY和GROUP BY子句中的列2.2 多列索引设计策略组合索引的设计需要遵循最左前缀原则索引(a,b,c)可以支持a|ab|abc组合查询但无法支持b|c|bc查询等值查询优先将等值条件列放在组合索引左侧范围列靠右范围查询(,,BETWEEN)的列尽量放在右侧排序优化ORDER BY的列尽量包含在索引中且顺序一致-- 良好设计的组合索引示例 CREATE INDEX idx_emp_dept_hire ON employees(department_id, hire_date, salary); -- 可以高效支持以下查询 SELECT * FROM employees WHERE department_id 10 AND hire_date 2020-01-01 ORDER BY salary;2.3 覆盖索引的妙用当索引包含查询所需的所有字段时引擎无需回表即可完成查询这种索引覆盖能极大提升性能-- 原始查询需要回表 SELECT * FROM products WHERE category electronics; -- 优化为覆盖索引查询 CREATE INDEX idx_cat_name_price ON products(category, product_name, price); SELECT product_name, price FROM products WHERE category electronics;覆盖索引的优势减少IO操作只需读取索引数据避免二次查找特别是对于TEXT/BLOB字段对统计查询特别有效3. 索引失效的典型场景与解决方案3.1 索引失效的七大杀手隐式类型转换-- user_id是varchar类型但用数字查询 SELECT * FROM users WHERE user_id 10086; -- 失效 SELECT * FROM users WHERE user_id 10086; -- 有效函数操作索引列SELECT * FROM orders WHERE DATE_FORMAT(create_time,%Y-%m) 2023-01; -- 失效 -- 应改为范围查询 SELECT * FROM orders WHERE create_time BETWEEN 2023-01-01 AND 2023-01-31;前导模糊查询SELECT * FROM products WHERE name LIKE %apple%; -- 全表扫描 SELECT * FROM products WHERE name LIKE apple%; -- 可以使用索引OR条件不当使用-- 当OR两侧条件都有索引时会使用索引合并否则失效 SELECT * FROM logs WHERE id 100 OR content LIKE %error%; -- 可能失效不符合最左前缀-- 索引是(idx_type_status) SELECT * FROM articles WHERE status 1; -- 无法使用索引使用NOT、!、SELECT * FROM members WHERE status ! active; -- 通常失效索引列参与计算SELECT * FROM transactions WHERE amount 100 500; -- 失效3.2 解决方案与优化技巧使用EXPLAIN分析EXPLAIN SELECT * FROM orders WHERE total_amount 1000; -- 查看type列const ref range index ALL强制索引使用SELECT * FROM orders FORCE INDEX(idx_total) WHERE total_amount 1000;索引提示SELECT * FROM orders USE INDEX(idx_status) WHERE status shipped;优化查询重写-- 原始低效查询 SELECT * FROM products WHERE price * 0.8 100; -- 优化后 SELECT * FROM products WHERE price 100 / 0.8;4. 索引性能测试方法论4.1 基准测试工具链sysbench# 安装 sudo apt-get install sysbench # 执行OLTP测试 sysbench oltp_read_write --db-drivermysql --mysql-hostlocalhost \ --mysql-port3306 --mysql-usertest --mysql-passwordtest \ --mysql-dbsbtest --tables10 --table-size1000000 preparemysqlslapmysqlslap --userroot --password --hostlocalhost \ --concurrency50 --iterations10 --querySELECT * FROM employees WHERE hire_date 2000-01-01自定义测试脚本-- 创建测试表 CREATE TABLE index_test ( id INT AUTO_INCREMENT PRIMARY KEY, data VARCHAR(255), create_time DATETIME, INDEX idx_data (data), INDEX idx_time (create_time) ); -- 填充测试数据(100万行) DELIMITER // CREATE PROCEDURE populate_test_data() BEGIN DECLARE i INT DEFAULT 0; WHILE i 1000000 DO INSERT INTO index_test(data, create_time) VALUES (CONCAT(data-, FLOOR(RAND()*1000)), DATE_ADD(2020-01-01, INTERVAL FLOOR(RAND()*1000) DAY)); SET i i 1; END WHILE; END // DELIMITER ; CALL populate_test_data();4.2 性能对比测试方案无索引 vs 有索引-- 测试无索引查询 SELECT SQL_NO_CACHE * FROM index_test WHERE data data-123; -- 添加索引后测试 ALTER TABLE index_test ADD INDEX idx_data(data); SELECT SQL_NO_CACHE * FROM index_test WHERE data data-123;不同索引类型对比-- B-tree索引 SELECT SQL_NO_CACHE * FROM index_test WHERE create_time BETWEEN 2021-01-01 AND 2021-12-31; -- 哈希索引(需使用MEMORY引擎) CREATE TABLE index_test_hash LIKE index_test; ALTER TABLE index_test_hash ENGINEMEMORY; INSERT INTO index_test_hash SELECT * FROM index_test LIMIT 100000; ALTER TABLE index_test_hash ADD INDEX idx_hash USING HASH(data); SELECT SQL_NO_CACHE * FROM index_test_hash WHERE data data-123;索引合并测试-- 使用两个独立索引 EXPLAIN SELECT * FROM index_test WHERE data data-123 OR create_time 2022-01-01; -- 使用组合索引 ALTER TABLE index_test ADD INDEX idx_data_time(data, create_time); EXPLAIN SELECT * FROM index_test WHERE data data-123 AND create_time 2022-01-01;4.3 性能监控指标通过以下命令监控索引性能-- 查看索引使用统计 SELECT * FROM sys.schema_index_statistics WHERE table_schema your_db AND table_name your_table; -- 查看未使用索引 SELECT * FROM sys.schema_unused_indexes; -- 监控索引效率 SELECT OBJECT_NAME, INDEX_NAME, ROWS_READ, ROWS_INSERTED, ROWS_UPDATED, ROWS_DELETED FROM performance_schema.table_io_waits_summary_by_index_usage WHERE OBJECT_SCHEMA your_db;5. 高级索引优化技巧5.1 索引跳跃扫描(MySQL 8.0)MySQL 8.0引入的索引跳跃扫描优化可以在某些情况下突破最左前缀限制-- 组合索引(idx_gender_age) CREATE INDEX idx_gender_age ON employees(gender, age); -- 8.0前只能使用gender条件 SELECT * FROM employees WHERE age 30; -- 无法使用索引 -- 8.0可以触发跳跃扫描 EXPLAIN SELECT * FROM employees WHERE age 30; -- 输出显示使用了index_skip_scan5.2 降序索引优化MySQL 8.0支持真正的降序索引对特定排序场景有显著优化-- 创建降序索引 CREATE INDEX idx_salary_desc ON employees(salary DESC); -- 降序查询将直接使用索引 SELECT * FROM employees ORDER BY salary DESC LIMIT 100;5.3 函数索引(MySQL 8.0)通过函数索引可以解决字段运算导致的索引失效问题-- 创建函数索引 CREATE INDEX idx_month_created ON orders((MONTH(create_time))); -- 使用函数索引查询 SELECT * FROM orders WHERE MONTH(create_time) 12;5.4 索引隐藏与可见性MySQL 8.0允许设置索引可见性而不必删除索引-- 使索引不可见(优化器将忽略) ALTER TABLE employees ALTER INDEX idx_name INVISIBLE; -- 恢复可见 ALTER TABLE employees ALTER INDEX idx_name VISIBLE;6. 索引维护与管理6.1 索引碎片整理随着数据修改索引会产生碎片影响性能-- 查看碎片情况 SELECT table_name, index_name, ROUND(stat_value * innodb_page_size / 1024 / 1024, 2) size_mb, ROUND(data_size / 1024 / 1024, 2) data_mb, ROUND((stat_value * innodb_page_size - data_size) / 1024 / 1024, 2) frag_mb FROM mysql.innodb_index_stats JOIN information_schema.INNODB_SYS_TABLESPACES ON (table_name CONCAT(table_schema, /, name)) WHERE database_name your_db AND stat_name size; -- 重建表整理碎片 ALTER TABLE employees ENGINEInnoDB; -- 在线重建索引(MySQL 5.7) ALTER TABLE employees DROP INDEX idx_name, ADD INDEX idx_name(last_name);6.2 索引使用监控长期监控索引使用情况-- 开启性能模式 UPDATE performance_schema.setup_consumers SET ENABLED YES WHERE NAME LIKE %events_statements%; -- 查询索引使用统计 SELECT OBJECT_NAME, INDEX_NAME, COUNT_READ, COUNT_FETCH FROM performance_schema.table_io_waits_summary_by_index_usage WHERE OBJECT_SCHEMA your_db ORDER BY COUNT_READ DESC;6.3 索引生命周期管理建立索引管理流程新功能上线前评估索引需求生产环境监控索引使用定期审查低效/冗余索引使用pt-index-usage工具分析慢查询日志# 使用pt-index-usage分析慢查询 pt-index-usage /var/log/mysql/mysql-slow.log -u root -p