MySQL进阶-索引深度原理与设计

一、索引底层结构为什么索引能让查询“飞起来”我们常说“索引是数据库的指南针”但很少有人想过这个“指南针”到底是怎么工作的MySQL的索引底层核心是「B Tree」结构InnoDB引擎默认这也是所有索引知识点的基础必须吃透。1.1 B Tree 核心原理图文解析首先要明确MySQL的InnoDB引擎索引的底层存储结构是B Tree而非B Tree、红黑树等。为什么选择B Tree核心原因是它更适合磁盘存储能减少磁盘I/O次数提升查询效率。先看B Tree的核心结构简化模型B Tree 结构分为3层根节点、非叶子节点、叶子节点根节点最顶层存储索引关键字的范围指向非叶子节点非叶子节点中间层不存储具体数据只存储索引关键字和指针用于引导查询方向叶子节点最底层存储所有索引关键字和对应的数据地址InnoDB中是数据本身并且叶子节点之间用双向链表连接方便范围查询。举个通俗的例子假设我们有一张用户表user主键是id索引底层的B Tree结构就像一本“字典目录”——根节点告诉你“id 1-1000在第1个非叶子节点”非叶子节点告诉你“id 1-100在第1个叶子节点”叶子节点直接找到id对应的用户数据不用逐行扫描全表。1.2 B Tree 与 B Tree 的核心区别避坑重点很多人会混淆B Tree和B Tree这里用表格清晰区分避免理解偏差对比维度B TreeB TreeMySQL InnoDB默认数据存储位置非叶子节点、叶子节点都存储数据只有叶子节点存储数据非叶子节点只存索引关键字叶子节点连接无连接范围查询需回溯双向链表连接范围查询效率极高磁盘I/O效率低非叶子节点存数据占用空间大单次I/O读取关键字少高非叶子节点仅存关键字单次I/O能读取更多关键字减少I/O次数查询效率不稳定可能在非叶子节点找到数据也可能需要到叶子节点稳定所有查询都必须到叶子节点查询时间可预测1.3 聚簇索引 vs 非聚簇索引核心区分必懂InnoDB引擎中索引分为聚簇索引和非聚簇索引两者的底层存储和查询逻辑差异极大也是面试和实战中的高频考点。1聚簇索引Clustered Index核心定义索引与数据存储在一起索引的叶子节点就是数据本身不需要额外查找数据地址。特点与规则InnoDB中主键索引就是聚簇索引如果没有主键InnoDB会自动选择一个唯一非空索引作为聚簇索引如果也没有会隐式创建一个自增主键作为聚簇索引一张表只有一个聚簇索引因为数据只能有一种存储顺序查询效率极高因为找到索引就找到了数据无需额外I/O。示例用户表user主键id是聚簇索引查询SELECT * FROM user WHERE id 100通过B Tree找到id100的叶子节点直接获取该节点的所有用户数据name、age、phone等。2非聚簇索引Secondary Index核心定义索引与数据分离索引的叶子节点存储的是“索引关键字 聚簇索引关键字”而非数据本身查询时需要通过聚簇索引再查找一次数据即“回表查询”。特点与规则普通索引、唯一索引、联合索引等都属于非聚簇索引一张表可以有多个非聚簇索引查询效率比聚簇索引低因为多了一次回表操作。示例用户表user给name字段建普通索引非聚簇索引查询SELECT * FROM user WHERE name 张三流程如下通过name索引的B Tree找到name张三对应的叶子节点获取到该记录的聚簇索引关键字id再通过聚簇索引id的B Tree找到对应的叶子节点获取用户的所有数据回表查询。1.4 回表查询与覆盖索引优化核心技巧从上面的非聚簇索引查询示例我们知道了“回表查询”会增加一次I/O影响查询效率。那如何避免回表答案就是「覆盖索引」。1回表查询定义非聚簇索引查询时索引叶子节点没有存储完整数据需要通过聚簇索引再次查询数据的过程就是回表查询。痛点多一次磁盘I/O数据量越大回表越耗时。示例回表示例-- 给name建非聚簇索引查询字段包含非索引字段需要回表 SELECT id, name, age FROM user WHERE name 张三;解析name索引的叶子节点只存储name和id聚簇索引关键字没有age字段所以需要通过id回表获取age字段。2覆盖索引定义查询的所有字段都包含在当前索引的叶子节点中无需回表直接从索引中获取所有需要的数据。核心优势避免回表减少I/O大幅提升查询效率是SQL优化的常用技巧。示例覆盖索引示例-- 给name建非聚簇索引查询字段只有id和name都在索引中无需回表 SELECT id, name FROM user WHERE name 张三;解析name索引的叶子节点存储了name和id查询的字段正好是这两个所以直接从索引中获取数据无需回表。小技巧如果需要查询多个字段可以建立「联合索引」将查询字段都包含在联合索引中实现覆盖索引。比如查询name和age可以建立联合索引(name, age)这样查询SELECT name, age FROM user WHERE name 张三就无需回表。1.5 索引高度与性能关系底层细节索引的高度指的是B Tree的层数根节点到叶子节点的层数。InnoDB的索引高度通常是2-4层这也是索引查询高效的关键原因。为什么高度不会太高InnoDB的索引页大小是16KB每个非叶子节点存储的是“索引关键字 指针”假设每个关键字指针占用16字节那么一个16KB的索引页可以存储16*1024 / 16 1024个关键字指针。按这个计算2层B Tree根节点1024个指针每个指针指向一个叶子节点每个叶子节点存储1024条数据总数据量1024*1024≈100万条3层B Tree总数据量1024*1024*1024≈10亿条也就是说即使是10亿条数据索引高度也只有3层查询时只需要3次磁盘I/O根节点→非叶子节点→叶子节点效率极高。注意索引高度越高磁盘I/O次数越多查询效率越低。所以要避免索引碎片过多导致索引高度升高。1.6 索引页与碎片维护重点索引页InnoDB中索引和数据都是以“页”为单位存储的每页大小默认16KB索引页就是存储索引数据的页。索引碎片当频繁执行插入、删除、更新操作时索引页会出现空闲空间导致索引页数量增加索引高度升高这就是索引碎片。碎片的危害增加磁盘I/O次数降低查询效率占用更多磁盘空间。解决办法定期整理索引碎片后面会讲具体操作。二、InnoDB 索引结构详解实战重点前面我们讲了索引的底层结构这一部分聚焦InnoDB引擎的具体索引结构包括主键索引、二级索引、联合索引等这些都是实战中最常用的索引类型必须逐个吃透。2.1 主键索引聚簇索引前面已经简单介绍过这里再补充细节和实战规范主键索引的叶子节点存储的是完整的行数据而非数据地址主键的选择建议使用自增主键INT/BIGINT避免使用UUID。原因自增主键会按顺序插入不会导致索引页分裂减少碎片UUID是随机值插入时会打乱索引顺序频繁导致索引页分裂产生大量碎片降低性能。示例创建自增主键索引-- 推荐自增主键 CREATE TABLE user ( id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, age INT, phone VARCHAR(20) ); -- 不推荐UUID主键易产生碎片 CREATE TABLE user ( id VARCHAR(36) PRIMARY KEY DEFAULT UUID(), name VARCHAR(50) NOT NULL );2.2 二级索引普通索引、唯一索引二级索引就是我们常说的普通索引、唯一索引都属于非聚簇索引底层结构相同核心区别是“是否允许重复值”。1普通索引Normal Index定义最基础的索引没有任何约束允许索引字段出现重复值、NULL值。适用场景频繁用于查询条件但不需要唯一约束的字段如name、age、address等。创建示例-- 给name字段建普通索引 CREATE INDEX idx_user_name ON user(name); -- 查看索引 SHOW INDEX FROM user;2唯一索引Unique Index定义索引字段的值必须唯一不允许重复但允许NULL值注意多个NULL值不算重复。适用场景需要保证字段唯一性的场景如phone、email等。创建示例-- 给phone字段建唯一索引 CREATE UNIQUE INDEX idx_user_phone ON user(phone); -- 插入重复phone会报错 INSERT INTO user (name, phone) VALUES (张三, 13800138000); INSERT INTO user (name, phone) VALUES (李四, 13800138000); -- 报错Duplicate entry 13800138000 for key idx_user_phone注意唯一索引和主键索引的区别主键索引不允许NULL值且一张表只有一个唯一索引允许NULL值一张表可以有多个。2.3 联合索引Composite Index联合索引就是将多个字段组合在一起创建的索引是实战中最常用、也最容易踩坑的索引类型核心是「最左前缀原则」。1联合索引的底层结构联合索引的B Tree结构是按照“第一个字段→第二个字段→第三个字段”的顺序排序的。比如创建联合索引(idx_name_age)索引的排序规则是先按name排序name相同的情况下再按age排序。示例用户表中name张三的有3条记录age分别是20、25、30那么联合索引(idx_name_age)的叶子节点排序为(张三,20) → (张三,25) → (张三,30)。2最左前缀原则必背、必懂核心定义联合索引的查询必须从“最左边的字段”开始且不能跳过中间字段否则索引会失效。举个例子创建联合索引(idx_name_age_phone)我们来分析不同查询条件的索引命中情况查询SQL索引命中情况原因SELECT * FROM user WHERE name 张三;命中索引全匹配最左前缀从最左边的name字段开始查询符合最左前缀原则SELECT * FROM user WHERE name 张三 AND age 25;命中索引匹配前两个字段从name开始不跳过中间字段符合原则SELECT * FROM user WHERE name 张三 AND age 25 AND phone [phone];命中索引全匹配匹配所有联合索引字段效率最高SELECT * FROM user WHERE age 25;索引失效未匹配最左前缀跳过了最左边的name字段不符合原则SELECT * FROM user WHERE name 张三 AND phone [phone];仅命中name部分phone部分失效跳过了中间的age字段phone部分无法命中索引重点坑联合索引的字段顺序直接影响索引的命中效率.2.4 索引下推ICPIndex Condition Pushdown索引下推是MySQL 5.6及以上版本新增的优化特性核心作用是“减少回表次数”提升查询效率尤其适合联合索引的查询场景。先理解“无索引下推”的查询流程通过联合索引找到所有符合最左前缀条件的索引记录将这些索引记录回表获取完整行数据对完整行数据进行过滤WHERE后的其他条件。“有索引下推”的查询流程通过联合索引找到所有符合最左前缀条件的索引记录在索引层面直接过滤掉不符合WHERE后其他条件的记录无需回表只将符合条件的索引记录回表获取完整行数据。示例创建联合索引(idx_name_age)查询SELECT * FROM user WHERE name LIKE 张% AND age 20无索引下推先找到所有name LIKE 张%的索引记录全部回表再过滤age 20的记录有索引下推找到name LIKE 张%的索引记录后直接在索引层面过滤age ≤ 20的记录只将age 20的记录回表减少回表次数。开启/关闭索引下推默认开启-- 开启索引下推 SET optimizer_switch index_condition_pushdownon; -- 关闭索引下推 SET optimizer_switch index_condition_pushdownoff;2.5 索引选择性与基数索引设计关键索引选择性和基数决定了索引的效率——选择性越高、基数越大索引效果越好。1索引基数Cardinality定义索引字段中不重复值的数量。比如user表中name字段有1000条记录其中不重复的name有800个那么name索引的基数就是800。特点基数越接近表的总记录数索引选择性越好基数越小索引效果越差比如性别字段只有男、女两个值基数极小建索引几乎没有意义。2索引选择性Selectivity定义索引基数 / 表总记录数取值范围是(0,1]。选择性越接近1索引效果越好越接近0索引效果越差。计算公式$$\text{选择性} \frac{\text{索引基数}}{\text{表总记录数}}$$示例user表总记录数1000id字段主键基数1000选择性1000/10001索引效果最好name字段基数800选择性800/10000.8索引效果较好gender字段男/女基数2选择性2/10000.002索引效果极差不建议建索引。实战建议创建索引时优先选择选择性高≥0.3的字段选择性极低的字段如性别、状态不建议建索引反而会增加写入负担。三、索引类型与适用场景实战选型指南除了前面讲的普通索引、唯一索引、联合索引MySQL还有前缀索引、全文索引、空间索引等不同索引有不同的适用场景选型错误会导致性能问题下面逐个详解。3.1 普通索引Normal Index前面已详细介绍这里补充适用场景总结适用频繁作为查询条件且字段无需唯一约束、选择性较高的场景示例用户表的name、address字段商品表的category_id字段注意不要给所有查询字段都建普通索引过多索引会降低插入、更新、删除的效率。3.2 唯一索引Unique Index适用场景总结适用需要保证字段唯一性的场景且字段允许NULL值示例用户表的phone、email字段订单表的order_no字段注意如果字段不允许NULL值且是表的唯一标识建议用主键索引而非唯一索引。3.3 前缀索引Prefix Index核心定义对字符串字段的“前N个字符”创建索引而非整个字符串用于解决“长字符串字段建索引占用空间大、效率低”的问题。适用场景字符串字段较长如varchar(255)且前N个字符的选择性较高比如身份证号、手机号、邮箱。创建示例给邮箱字段建前缀索引取前10个字符-- 邮箱字段较长取前10个字符建前缀索引 CREATE INDEX idx_user_email_prefix ON user(email(10)); -- 查询时只要条件匹配前10个字符就能命中索引 SELECT * FROM user WHERE email LIKE test123456%;关键技巧前缀长度的选择要平衡“选择性”和“空间占用”——前缀长度越长选择性越高但占用空间越大前缀长度越短占用空间越小但选择性可能越低。如何确定合适的前缀长度-- 计算不同前缀长度的选择性选择选择性接近完整字段的最短长度 SELECT COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS len5, COUNT(DISTINCT LEFT(email, 8)) / COUNT(*) AS len8, COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS len10, COUNT(DISTINCT email) / COUNT(*) AS full_len FROM user;比如查询结果中len10的选择性和full_len接近就选择前缀长度10。注意前缀索引无法用于ORDER BY、GROUP BY也无法实现覆盖索引因为只存储了前缀字符。3.4 全文索引FULLTEXT Index核心定义用于全文搜索支持对字符串字段进行“关键词匹配”而非精确匹配或模糊匹配%xxx%效率比LIKE模糊匹配高得多。适用场景需要对长文本进行搜索的场景如文章内容、商品描述、评论等。创建示例给文章表的content字段建全文索引-- 创建全文索引 CREATE FULLTEXT INDEX idx_article_content ON article(content); -- 全文搜索MATCH...AGAINST -- 方式1自然语言搜索默认 SELECT * FROM article WHERE MATCH(content) AGAINST(MySQL 索引 优化); -- 方式2布尔搜索支持AND、OR、NOT等逻辑 SELECT * FROM article WHERE MATCH(content) AGAINST(MySQL -优化 IN BOOLEAN MODE); -- 解析表示必须包含-表示不包含即搜索包含MySQL但不包含优化的文章注意事项全文索引仅支持CHAR、VARCHAR、TEXT类型的字段MySQL的全文索引有“停止词”如the、a、的、了等这些词不会被索引搜索时也不会匹配全文索引的搜索效率远高于LIKE %关键词%后者会全表扫描。3.5 空间索引Spatial Index核心定义用于空间数据类型如GEOMETRY、POINT、LINESTRING等的索引用于快速查询空间位置相关的数据如地图坐标、地理位置等。适用场景地理信息系统GIS相关开发比如查询“距离某个坐标5公里内的店铺”。创建示例简单了解-- 创建空间索引需先将字段设为空间类型 CREATE TABLE shop ( id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, location POINT NOT NULL, -- 空间类型点坐标 SPATIAL INDEX idx_shop_location (location) -- 空间索引 ); -- 插入空间数据 INSERT INTO shop (name, location) VALUES (便利店, ST_GeomFromText(POINT(116.403874 39.914885))); -- 查询距离某个坐标500米内的店铺 SELECT * FROM shop WHERE ST_Distance_Sphere(location, ST_GeomFromText(POINT(116.403874 39.914885))) 500;说明空间索引在普通业务开发中使用较少了解即可进阶阶段无需深入。四、联合索引设计原则实战避坑核心联合索引是实战中最常用的索引类型也是最容易踩坑的地方。设计联合索引核心要遵循5个原则只要记住这些原则就能避免大部分索引失效问题提升查询效率。原则1最左匹配原则重中之重前面已经详细讲过这里再强调联合索引的查询必须从最左边的字段开始不能跳过中间字段否则索引会失效。实战建议将查询频率最高、最靠左的字段放在联合索引的最前面。反例查询条件经常是WHERE age 25 AND name 张三却创建联合索引(idx_age_name)此时查询时因为name在age后面无法命中索引需先匹配age再匹配name但查询条件是先age后name看似可以但如果有其他查询条件容易失效。正例创建联合索引(idx_name_age)无论查询条件是WHERE name 张三还是WHERE name 张三 AND age 25都能命中索引。原则2等值查询在前范围查询在后联合索引中如果有字段用于范围查询如、、BETWEEN、LIKE xxx%要将其放在联合索引的最右边否则范围查询后面的字段无法命中索引。示例创建联合索引(idx_name_age_phone)分析不同查询条件正例WHERE name 张三 AND age 20 AND phone [phone]解析name是等值查询age是范围查询phone在范围查询后面此时phone字段无法命中索引只有name和age能命中但如果将phone放在age前面age是范围查询phone同样无法命中所以范围查询必须放最后。反例WHERE name 张三 AND age 25解析name是范围查询放在最前面age在后面此时age无法命中索引只有name能命中。原则3区分度高的列放前面区分度即索引选择性高的字段放在联合索引的前面能更快地过滤数据减少后续查询的行数。示例用户表中name字段的区分度0.8高于age字段的区分度0.3创建联合索引时应将name放在前面即idx_name_age而非idx_age_name。原因name区分度高能快速过滤掉大部分不相关的记录后续age的查询只需在少量记录中进行效率更高。原则4避免冗余索引冗余索引就是“一个索引可以被另一个索引覆盖”冗余索引不仅占用磁盘空间还会降低插入、更新、删除的效率必须避免。示例冗余索引创建了联合索引(idx_name_age)又创建了普通索引(idx_name)——idx_name就是冗余索引因为idx_name_age的最左前缀是name查询name时idx_name_age也能命中无需单独建idx_name创建了联合索引(idx_name_age_phone)又创建了联合索引(idx_name_age)——idx_name_age是冗余索引因为idx_name_age_phone包含了idx_name_age的所有字段。判断冗余索引的核心如果索引A的所有字段都是索引B的最左前缀且索引B的字段包含索引A的所有字段那么索引A就是冗余索引。原则5联合索引优化排序避免Using filesort当查询中包含ORDER BY、GROUP BY时如果排序/分组的字段正好是联合索引的字段按索引顺序MySQL会直接利用索引进行排序避免出现Using filesort文件排序效率极低。示例创建联合索引(idx_name_age)分析排序场景正例SELECT * FROM user WHERE name 张三 ORDER BY age;解析name是等值查询age是联合索引的第二个字段MySQL会直接利用索引的排序避免Using filesort。反例SELECT * FROM user WHERE name 张三 ORDER BY phone;解析phone不是联合索引的字段MySQL无法利用索引排序会出现Using filesort效率低下。技巧如果需要排序将排序字段加入联合索引且保持排序顺序与索引顺序一致升序/降序。五、索引失效场景必背、必避坑最让人头疼的问题明明建了索引查询却全表扫描typeALL这就是索引失效了。下面总结7种最常见的索引失效场景每个场景都配示例确保你能避开。场景1对索引列进行运算、函数操作核心MySQL无法识别“运算后的索引列”会放弃索引进行全表扫描。示例失效-- 给id建了主键索引对id进行运算索引失效 SELECT * FROM user WHERE id 1 100; -- 给name建了普通索引对name使用函数索引失效 SELECT * FROM user WHERE SUBSTRING(name, 1, 2) 张三;优化方案避免对索引列进行运算、函数操作将运算、函数操作移到查询值上。-- 优化后索引命中 SELECT * FROM user WHERE id 100 - 1; SELECT * FROM user WHERE name LIKE 张三%;场景2索引列发生类型隐式转换核心索引列的类型与查询值的类型不一致MySQL会进行隐式转换导致索引失效。示例失效-- 给phone建了普通索引phone是VARCHAR类型查询值是数字隐式转换索引失效 SELECT * FROM user WHERE phone [phone]; -- 优化后查询值与索引列类型一致索引命中 SELECT * FROM user WHERE phone [phone];常见隐式转换场景VARCHAR → INT、DATE → STRING一定要保证查询值的类型与索引列类型一致。场景3使用 !、、IS NOT NULL 操作符核心MySQL对 !、、IS NOT NULL 的查询无法高效利用索引会进行全表扫描特殊情况除外如索引选择性极高。示例失效-- 给name建了普通索引使用!索引失效 SELECT * FROM user WHERE name ! 张三; -- 使用IS NOT NULL索引失效 SELECT * FROM user WHERE name IS NOT NULL;优化方案尽量避免使用这些操作符可通过业务逻辑调整比如用范围查询替代。场景4LIKE 前置模糊查询%xxx核心LIKE 查询中前缀是%如%张三MySQL无法利用索引的排序特性会进行全表扫描后缀模糊张三%、前后模糊%张三%中只有后缀模糊能命中索引。示例失效与命中-- 给name建了普通索引 SELECT * FROM user WHERE name LIKE %张三; -- 前置模糊索引失效 SELECT * FROM user WHERE name LIKE 张三%; -- 后缀模糊索引命中 SELECT * FROM user WHERE name LIKE %张三%; -- 前后模糊索引失效优化方案前置模糊查询可使用全文索引替代如果必须用LIKE可考虑前缀索引但效果有限。场景5OR 条件中有非索引列核心OR 连接的多个条件中如果有一个条件的字段没有建索引那么整个查询都会全表扫描所有索引都会失效。示例失效-- 给name建了索引age没有建索引OR条件导致索引失效 SELECT * FROM user WHERE name 张三 OR age 25;优化方案要么给OR条件中的所有字段都建索引要么将OR查询拆分成两个独立的查询UNION ALL。-- 优化方案1给age建索引 CREATE INDEX idx_user_age ON user(age); SELECT * FROM user WHERE name 张三 OR age 25; -- 索引命中 -- 优化方案2拆分成UNION ALL SELECT * FROM user WHERE name 张三 UNION ALL SELECT * FROM user WHERE age 25;场景6联合索引不满足最左前缀原则前面已详细讲过这里再举一个典型反例-- 创建联合索引(idx_name_age_phone) SELECT * FROM user WHERE age 25; -- 跳过name索引失效 SELECT * FROM user WHERE name 张三 AND phone [phone]; -- 跳过agephone部分失效场景7使用 NOT IN / NOT EXISTS部分场景核心NOT IN / NOT EXISTS 的查询在某些场景下如子查询数据量大MySQL无法利用索引会进行全表扫描如果子查询数据量小可能会命中索引。示例可能失效-- 给user_id建了索引NOT IN可能导致索引失效 SELECT * FROM order WHERE user_id NOT IN (1,2,3);优化方案用LEFT JOIN ... IS NULL 替代 NOT IN / NOT EXISTS效率更高。-- 优化后索引命中 SELECT o.* FROM order o LEFT JOIN user u ON o.user_id u.id WHERE u.id IS NULL;六、索引维护实战必备操作索引不是建完就不管了定期维护索引能避免碎片、冗余保证索引的高效性。下面介绍4个常用的索引维护操作实战中必须掌握。6.1 查看索引查看表的所有索引了解索引的类型、字段、基数等信息用于排查索引问题。-- 查看指定表的所有索引 SHOW INDEX FROM user; -- 查看索引的详细信息简化版 SHOW INDEX FROM user\G; -- \G 用于纵向显示更清晰关键字段解读Key_name索引名称Column_name索引对应的字段Cardinality索引基数Index_type索引类型BTREE即B TreeNull是否允许NULL值。6.2 建立/删除索引规范建立和删除索引要遵循规范避免影响业务。1建立索引规范避免给选择性极低的字段建索引如性别、状态避免冗余索引长字符串字段优先用前缀索引批量建索引时避免在业务高峰期操作建索引会锁表影响写入。创建索引的3种方式-- 方式1创建表时直接建索引 CREATE TABLE user ( id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, phone VARCHAR(20) UNIQUE, -- 唯一索引 INDEX idx_user_name (name) -- 普通索引 ); -- 方式2ALTER TABLE创建 ALTER TABLE user ADD INDEX idx_user_age (age); ALTER TABLE user ADD UNIQUE INDEX idx_user_phone (phone); -- 方式3CREATE INDEX创建 CREATE INDEX idx_user_address ON user(address);2删除索引规范删除索引前先确认该索引没有被查询使用可通过慢查询日志、执行计划排查避免删除主键索引除非有替代主键批量删除索引时避免在业务高峰期操作。删除索引的2种方式-- 方式1ALTER TABLE删除 ALTER TABLE user DROP INDEX idx_user_name; -- 方式2DROP INDEX删除 DROP INDEX idx_user_age ON user; -- 注意删除主键索引的方式不同 ALTER TABLE user DROP PRIMARY KEY;6.3 索引碎片整理前面提到频繁插入、删除、更新会产生索引碎片导致索引效率下降需要定期整理。整理碎片的2种方式1ALTER TABLE ... ENGINE InnoDB推荐该方式会重建表和索引清理碎片同时优化表结构适用于InnoDB引擎。-- 整理user表的索引碎片 ALTER TABLE user ENGINE InnoDB;注意该操作会锁表建议在业务低峰期执行如凌晨。2OPTIMIZE TABLE适用于MyISAM引擎InnoDB 下执行OPTIMIZE TABLE实际上会被映射为ALTER TABLE … ENGINE InnoDB效果一致。OPTIMIZE TABLE user;结论日常维护统一用ALTER TABLE ... ENGINE InnoDB即可。3何时需要整理碎片表频繁大量 DELETE、UPDATE、INSERT执行计划变慢、扫描行数异常上升磁盘空间占用明显虚高数据量不大但表文件很大。6.4 索引监控与冗余索引清理1如何发现冗余索引满足以下任一条件即为冗余索引已有联合索引(a,b,c)又建了单列索引(a)已有联合索引(a,b,c)又建了(a,b)多个索引作用完全相同只是名称不同。示例冗余CREATE INDEX idx_name ON user(name); CREATE INDEX idx_name_age ON user(name,age);idx_name完全冗余可直接删除。2查看索引使用情况MySQL 5.7 / 8.0通过sys.schema_unused_indexes查看从未被使用过的索引SELECT * FROM sys.schema_unused_indexes WHERE table_schema 你的库名;这些索引只占用写入性能无查询收益优先删除。3清理原则先删冗余、再删未使用、最后删低频使用删除前务必通过慢查询、执行计划确认无业务使用禁止一次性删除大量索引分批操作。七、索引设计实战总结7.1 单列索引设计规范优先给 WHERE 条件字段建索引而非 SELECT 字段选择性 0.3 再建索引性别、状态类字段不建索引字符串字段避免直接建全长索引优先前缀索引禁止给频繁更新的字段滥用索引更新代价高禁止一张表建超过 10 个索引一般控制在 3–6 个最佳。7.2 联合索引设计黄金口诀等值在前范围在后区分度高放在最左排序分组放进索引避免冗余最左匹配7.3 索引与 SQL 编写规范禁止对索引列做函数 / 运算 / 隐式转换禁止like %xxx前置模糊OR 条件必须全部带索引否则拆 UNION ALL尽量使用覆盖索引避免回表范围查询后字段无法使用索引放末尾。7.4 主键设计规范必须使用自增 BIGINT 主键禁止 UUID / 随机字符串主键字段建议无业务含义只做物理存储顺序禁止频繁修改主键避免复合主键除非是中间关联表。