MySQL索引图文操作指南：从原理到实战的深度解析 一、索引的本质：数据结构的物理化呈现 MySQL索引本质上是存储引擎为加速数据检索而构建的物理数据结构

    以InnoDB引擎为例，其默认采用的B+树索引结构通过多级节点实现高效定位：根节点存储索引范围，枝节点存储中间值，叶节点直接指向数据行

    这种设计使得范围查询、排序操作可利用索引的有序性，避免全表扫描

     1.1 B+树与哈希索引的对比 -B+树索引：支持范围查询、排序操作，适用于高频检索场景

    例如电商平台的商品价格区间筛选，可通过B+树索引快速定位目标数据

     -哈希索引：仅支持等值查询，适用于用户登录等精确匹配场景

    InnoDB自适应哈希索引会自动为热点数据页建立哈希结构，加速等值查询

     1.2索引的物理存储 -聚簇索引：InnoDB表的主键索引即为聚簇索引，数据行按主键顺序存储

    例如用户表的主键索引，数据与索引存储在同一物理页中

     -非聚簇索引：二级索引存储主键值而非数据行，需回表查询

    例如商品表的分类索引，查询时需通过主键回表获取完整数据

     二、索引类型实战：从单列到复合的构建策略 2.1 单列索引：高频查询的加速利器 sql --创建用户表并添加索引 CREATE TABLE users( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL, email VARCHAR(100) NOT NULL, INDEX idx_username(username) ); -- 通过EXPLAIN验证索引使用 EXPLAIN SELECT - FROM users WHERE username = test_user; 执行结果：type=ref，`key=idx_username`，表明索引生效

     2.2复合索引：多条件查询的优化方案 sql --创建订单表并添加复合索引 CREATE TABLE orders( order_id INT PRIMARY KEY, user_id INT NOT NULL, order_date DATE NOT NULL, amount DECIMAL(10,2), INDEX idx_user_date(user_id, order_date) ); --复合索引生效查询 EXPLAIN SELECTFROM orders WHERE user_id =1001 AND order_date >= 2023-01-01; 关键原则：复合索引需遵循最左前缀原则，跳过首列会导致索引失效

     2.3唯一索引：数据完整性的保障 sql --创建邮箱表并添加唯一索引 CREATE TABLE emails( email_id INT PRIMARY KEY, email_addr VARCHAR(100) NOT NULL, UNIQUE INDEX idx_email(email_addr) ); --尝试插入重复数据将报错 INSERT INTO emails VALUES(1, test@example.com); INSERT INTO emails VALUES(2, test@example.com); --报错：Duplicate entry 2.4 全文索引：文本检索的突破方案 sql --创建文章表并添加全文索引 CREATE TABLE articles( article_id INT PRIMARY KEY, title VARCHAR(255), content TEXT, FULLTEXT INDEX idx_content(title, content) ); -- 全文索引查询示例 SELECTFROM articles WHERE MATCH(title, content) AGAINST(数据库优化 IN NATURAL LANGUAGE MODE); 注意事项：全文索引需配合MATCH-AGAINST语法，不支持LIKE模糊查询

     三、索引失效场景：避开性能陷阱 3.1函数操作破坏索引有序性 sql --索引失效示例 EXPLAIN SELECT - FROM users WHERE YEAR(create_time) =2023; --优化方案：改用范围查询 EXPLAIN SELECTFROM users WHERE create_time BETWEEN 2023-01-01 AND 2023-12-31; 3.2隐式类型转换导致索引失效 sql --索引失效示例（user_id为INT类型） EXPLAIN SELECT - FROM users WHERE user_id = 1001; --优化方案：保持类型一致 EXPLAIN SELECT - FROM users WHERE user_id =1001; 3.3 OR条件与最左前缀原则 sql --索引失效示例（复合索引(a,b,c)） EXPLAIN SELECT - FROM table WHERE b = 2 AND c =3; --优化方案：拆分为UNION查询 EXPLAIN SELECT - FROM table WHERE a = 1 AND b =2 AND c =3 UNION ALL SELECT - FROM table WHERE a = 1 AND b =2; 四、索引维护：性能监控与优化 4.1索引使用情况分析 sql --查询未使用索引 SELECT - FROM sys.schema_unused_indexes WHERE object_schema = your_database; --删除30天未使用的索引 DELETE FROM mysql.innodb_index_stats WHERE stat_name = n_diff_pfx01 AND stat_value =0 AND last_update < NOW() - INTERVAL30 DAY; 4.2碎片整理与表重建 sql --重建表以整理碎片 ALTER TABLE large_table ENGINE = InnoDB; -- 或使用pt-online-schema-change工具在线操作 pt-online-schema-change --alter ADD INDEX idx_new(column) D=your_database,t=large_table --execute; 4.3 自适应哈希索引监控 sql SHOW ENGINE INNODB STATUSG --查找Hash table size和Number of index pages used for the hash index 五、索引设计原则：平衡的艺术 1.选择性优先：选择性=DISTINCT值/总行数，选择性高的列更适合建索引

    例如用户表的国家字段选择性20%，性别字段0.01%，但组合索引可过滤99.99%数据