1. 索引基础概念与重要性
说明： 索引是数据库表中用于快速定位数据的数据结构。
-- 创建一张简单表，没有索引
CREATE TABLE users (
id INT NOT NULL,
username VARCHAR(50),
email VARCHAR(100),
PRIMARY KEY (id)
);
-- 查询无索引情况下全表扫描示例
EXPLAIN SELECT * FROM users WHERE username = 'alice';
运行EXPLAIN会显示type为ALL，说明全表扫描，性能差。
2. MySQL存储引擎对索引支持差异
-- 查看表的存储引擎
SHOW TABLE STATUS WHERE Name = 'users';
-- 创建MyISAM和InnoDB表示例
CREATE TABLE myisam_table (
id INT PRIMARY KEY,
data VARCHAR(100)
) ENGINE=MyISAM;
CREATE TABLE innodb_table (
id INT PRIMARY KEY,
data VARCHAR(100)
) ENGINE=InnoDB;
InnoDB支持事务和聚簇索引，MyISAM不支持事务且索引结构不同。
3. MySQL索引数据结构及工作原理
-- 查看当前索引
SHOW INDEX FROM users;
-- 创建一个B+树索引（普通索引）
CREATE INDEX idx_username ON users(username);
-- 查看索引后，使用EXPLAIN
EXPLAIN SELECT * FROM users WHERE username = 'alice';
B+树结构索引加速等值查询和范围查询。
4. 详细索引类型解析
-- 主键索引（创建表时）
CREATE TABLE products (
product_id INT PRIMARY KEY,
name VARCHAR(100)
);
-- 唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);
-- 普通索引
CREATE INDEX idx_name ON users(username);
-- 全文索引（MyISAM或InnoDB 5.6+）
CREATE FULLTEXT INDEX idx_content ON articles(content);
全文索引用于全文搜索。
5. 主键索引与聚簇索引深度解析
-- InnoDB主键索引自动聚簇，查看原理示意（无SQL）
-- 通过辅助索引查询演示
CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id INT,
order_date DATE,
INDEX idx_user_id (user_id)
);
-- 查询时通过辅助索引回表
EXPLAIN SELECT * FROM orders WHERE user_id = 123;
辅助索引叶子节点存储主键，回表查询整行。
6. 复合索引设计与最左前缀原则
-- 创建复合索引
CREATE INDEX idx_user_date ON orders(user_id, order_date);
-- 查询使用索引
EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND order_date = '2025-06-21';
-- 只使用最左前缀
EXPLAIN SELECT * FROM orders WHERE user_id = 123;
-- 不能单独使用user_id后面的字段索引
EXPLAIN SELECT * FROM orders WHERE order_date = '2025-06-21';
查询只能利用从最左边开始连续的索引列。
7. 索引失效全面分析与解决方案
-- 使用函数导致索引失效
EXPLAIN SELECT * FROM users WHERE LOWER(username) = 'alice';
-- 优化方案，避免函数
EXPLAIN SELECT * FROM users WHERE username = 'alice';
-- 前置通配符失效
EXPLAIN SELECT * FROM users WHERE username LIKE '%lice';
-- 优化使用全文索引
CREATE FULLTEXT INDEX idx_username_ft ON users(username);
SELECT * FROM users WHERE MATCH(username) AGAINST('lice' IN BOOLEAN MODE);
函数和前置通配符会导致索引失效。
8. 覆盖索引与回表机制详解
-- 覆盖索引示例
CREATE INDEX idx_username_email ON users(username, email);
-- 查询只用索引字段，无需回表
EXPLAIN SELECT username, email FROM users WHERE username = 'alice';
-- 查询需要回表
EXPLAIN SELECT email FROM users WHERE username = 'alice';
覆盖索引避免访问数据行，提升效率。
9. 索引与事务、锁机制的关系
-- 开启事务
START TRANSACTION;
-- 通过索引更新数据，减少锁范围
UPDATE orders SET order_date = '2025-06-22' WHERE order_id = 1001;
-- 未使用索引，可能导致表锁或范围锁
UPDATE orders SET order_date = '2025-06-22' WHERE user_id = 123;
COMMIT;
索引有助于减少锁冲突，提升并发。
10. EXPLAIN详解及执行计划优化
-- 查看执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 123;
-- 使用ANALYZE EXPLAIN（MySQL 8.0+）
ANALYZE EXPLAIN SELECT * FROM orders WHERE user_id = 123;
分析type、rows、possible_keys等列来判断索引使用效率。
11. 索引的维护与监控工具
-- 查看索引信息
SHOW INDEX FROM orders;
-- 统计表和索引信息
ANALYZE TABLE orders;
-- 优化表和索引
OPTIMIZE TABLE orders;
-- 查看索引IO等待情况
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage WHERE OBJECT_NAME='orders';
定期维护索引保证查询效率。
12. 索引优化实战案例解析
-- 情况：订单按用户和日期筛选慢
-- 解决：创建复合索引
CREATE INDEX idx_user_date ON orders(user_id, order_date);
-- 验证效果
EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND order_date BETWEEN '2025-01-01' AND '2025-06-30';
实战中合理设计索引，显著提升查询性能。
13. 附录：索引相关SQL语法大全
-- 创建索引
CREATE INDEX idx_col ON table(col);
-- 创建唯一索引
CREATE UNIQUE INDEX idx_unique_col ON table(col);
-- 删除索引
DROP INDEX idx_col ON table;
-- 查看索引
SHOW INDEX FROM table;
-- 查看执行计划
EXPLAIN SELECT * FROM table WHERE col='value';
如需具体业务场景代码示例、脚本自动化或针对某类查询的索引设计方案，告诉我！
发表回复