当前位置：首页 > news >正文

索引设计实操SQL + 案例 + 练习

news 2026/5/28 3:54:36

前言

索引是数据库性能优化的核心手段，合理设计索引能大幅提升查询效率。本节从索引选型、复合索引规则、索引失效场景、三大高频概念四个维度讲解，搭配可运行 SQL、实战案例与课后练习，上手即用。

一、基础测试表

执行以下 SQL 创建测试表，本节所有案例、练习均基于该表：

CREATETABLE`user`(idINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(20),ageINT,phoneVARCHAR(11),cityVARCHAR(30),create_timeDATETIME);

二、判断哪些字段适合/不适合建索引

2.1 适合建立索引的字段

WHERE后面常用的查询条件字段：name、age、city
多表关联JOIN的外键字段：如user_id这类关联字段
经常用于ORDER BY排序、GROUP BY分组的字段
唯一性高的字段：手机号、身份证号等重复数据极少的字段

2.2 不建议建立索引的字段

重复值极高的字段：性别、状态等少量枚举类型字段
频繁更新修改的字段：索引会随数据更新同步维护，加重数据库开销
数据量小、业务中极少查询的字段

三、复合索引 & 最左前缀原则

3.1 创建复合索引

-- 创建 (name, age) 复合索引CREATEINDEXidx_name_ageONuser(name,age);

3.2 可以正常命中索引（符合最左前缀）

-- 仅匹配复合索引最左侧首列，正常走索引SELECT*FROMuserWHEREname='张三';-- 依次匹配左侧所有字段，正常走索引SELECT*FROMuserWHEREname='张三'ANDage=25;

3.3 无法命中索引（违背最左前缀，索引失效）

-- 跳过最左侧 name，直接查询后续字段，索引完全失效SELECT*FROMuserWHEREage=25;

3.4 拓展练习

需求：创建复合索引idx_city_time(city,create_time)，分别写出可走索引、索引失效的 SQL 语句。

参考答案

-- 1. 创建指定复合索引CREATEINDEXidx_city_timeONuser(city,create_time);-- 可命中索引SELECT*FROMuserWHEREcity='上海';SELECT*FROMuserWHEREcity='上海'ANDcreate_time>'2026-01-01';-- 索引失效（跳过首列 city）SELECT*FROMuserWHEREcreate_time>'2026-01-01';

四、索引失效常见场景（实操演示）

日常开发高频踩坑点，以下写法都会导致索引失效，触发全表扫描：

-- 1. 左侧模糊匹配 / 全模糊匹配，索引失效SELECT*FROMuserWHEREnameLIKE'%李';-- 2. 索引字段发生隐式类型转换，索引失效-- phone 为字符串类型，使用数字查询，类型不匹配SELECT*FROMuserWHEREphone=13800138000;-- 3. 索引列参与运算、函数计算，索引失效SELECT*FROMuserWHEREage+10=30;-- 4. OR 连接的字段中，存在无索引字段，整体索引失效SELECT*FROMuserWHEREname='李四'ORcity='北京';

五、三大面试高频索引概念 + 实例讲解

5.1 索引覆盖

概念：查询所需的所有字段，全部存在于索引中，数据库无需回表查询原始数据，查询效率极高。

-- 先创建复合索引CREATEINDEXidx_name_ageONuser(name,age);-- 查询字段 name、age 都在索引内，触发索引覆盖，无需回表SELECTname,ageFROMuserWHEREname='张三';

5.2 最左前缀原则

概念：使用复合索引时，查询条件必须优先匹配索引最左侧字段，不能跳跃、颠倒顺序，否则索引失效。

5.3 索引下推

概念：InnoDB 引擎特性，在索引层先完成数据筛选，再回表查询，减少回表数据行数，提升性能。

-- 索引为 (name,age)-- 先在索引层过滤 name='王五' 且 age>20 的数据，再回表，即索引下推SELECT*FROMuserWHEREname='王五'ANDage>20;

六、课后实操练习题

基于上方user表完成以下练习：

为用户表合理创建单值索引、复合索引；
分别写出 3 条索引生效SQL、3 条索引失效SQL；
手写一条索引覆盖查询语句。

参考答案

-- 1. 创建索引-- 单值索引CREATEINDEXidx_phoneONuser(phone);-- 复合索引CREATEINDEXidx_city_nameONuser(city,name);-- 2. 索引生效 SQL（3条）SELECT*FROMuserWHEREphone='13800138000';SELECT*FROMuserWHEREcity='广州';SELECT*FROMuserWHEREcity='广州'ANDname='赵六';-- 索引失效 SQL（3条）SELECT*FROMuserWHEREnameLIKE'%王%';SELECT*FROMuserWHEREcity+''='深圳';SELECT*FROMuserWHEREname='钱七'ORage=30;-- 3. 索引覆盖查询语句-- 利用 idx_city_name 索引，只查询索引内字段SELECTcity,nameFROMuserWHEREcity='广州';