前言索引是数据库性能优化的核心手段合理设计索引能大幅提升查询效率。本节从索引选型、复合索引规则、索引失效场景、三大高频概念四个维度讲解搭配可运行 SQL、实战案例与课后练习上手即用。一、基础测试表执行以下 SQL 创建测试表本节所有案例、练习均基于该表CREATETABLEuser(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张三ANDage25;3.3 无法命中索引违背最左前缀索引失效-- 跳过最左侧 name直接查询后续字段索引完全失效SELECT*FROMuserWHEREage25;3.4 拓展练习需求创建复合索引idx_city_time(city,create_time)分别写出可走索引、索引失效的 SQL 语句。参考答案-- 1. 创建指定复合索引CREATEINDEXidx_city_timeONuser(city,create_time);-- 可命中索引SELECT*FROMuserWHEREcity上海;SELECT*FROMuserWHEREcity上海ANDcreate_time2026-01-01;-- 索引失效跳过首列 citySELECT*FROMuserWHEREcreate_time2026-01-01;四、索引失效常见场景实操演示日常开发高频踩坑点以下写法都会导致索引失效触发全表扫描-- 1. 左侧模糊匹配 / 全模糊匹配索引失效SELECT*FROMuserWHEREnameLIKE%李;-- 2. 索引字段发生隐式类型转换索引失效-- phone 为字符串类型使用数字查询类型不匹配SELECT*FROMuserWHEREphone13800138000;-- 3. 索引列参与运算、函数计算索引失效SELECT*FROMuserWHEREage1030;-- 4. OR 连接的字段中存在无索引字段整体索引失效SELECT*FROMuserWHEREname李四ORcity北京;五、三大面试高频索引概念 实例讲解5.1 索引覆盖概念查询所需的所有字段全部存在于索引中数据库无需回表查询原始数据查询效率极高。-- 先创建复合索引CREATEINDEXidx_name_ageONuser(name,age);-- 查询字段 name、age 都在索引内触发索引覆盖无需回表SELECTname,ageFROMuserWHEREname张三;5.2 最左前缀原则概念使用复合索引时查询条件必须优先匹配索引最左侧字段不能跳跃、颠倒顺序否则索引失效。5.3 索引下推概念InnoDB 引擎特性在索引层先完成数据筛选再回表查询减少回表数据行数提升性能。-- 索引为 (name,age)-- 先在索引层过滤 name王五 且 age20 的数据再回表即索引下推SELECT*FROMuserWHEREname王五ANDage20;六、课后实操练习题基于上方user表完成以下练习为用户表合理创建单值索引、复合索引分别写出 3 条索引生效SQL、3 条索引失效SQL手写一条索引覆盖查询语句。参考答案-- 1. 创建索引-- 单值索引CREATEINDEXidx_phoneONuser(phone);-- 复合索引CREATEINDEXidx_city_nameONuser(city,name);-- 2. 索引生效 SQL3条SELECT*FROMuserWHEREphone13800138000;SELECT*FROMuserWHEREcity广州;SELECT*FROMuserWHEREcity广州ANDname赵六;-- 索引失效 SQL3条SELECT*FROMuserWHEREnameLIKE%王%;SELECT*FROMuserWHEREcity深圳;SELECT*FROMuserWHEREname钱七ORage30;-- 3. 索引覆盖查询语句-- 利用 idx_city_name 索引只查询索引内字段SELECTcity,nameFROMuserWHEREcity广州;
索引设计 实操SQL + 案例 + 练习
发布时间：2026/5/28 3:48:03
前言索引是数据库性能优化的核心手段合理设计索引能大幅提升查询效率。本节从索引选型、复合索引规则、索引失效场景、三大高频概念四个维度讲解搭配可运行 SQL、实战案例与课后练习上手即用。一、基础测试表执行以下 SQL 创建测试表本节所有案例、练习均基于该表CREATETABLEuser(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张三ANDage25;3.3 无法命中索引违背最左前缀索引失效-- 跳过最左侧 name直接查询后续字段索引完全失效SELECT*FROMuserWHEREage25;3.4 拓展练习需求创建复合索引idx_city_time(city,create_time)分别写出可走索引、索引失效的 SQL 语句。参考答案-- 1. 创建指定复合索引CREATEINDEXidx_city_timeONuser(city,create_time);-- 可命中索引SELECT*FROMuserWHEREcity上海;SELECT*FROMuserWHEREcity上海ANDcreate_time2026-01-01;-- 索引失效跳过首列 citySELECT*FROMuserWHEREcreate_time2026-01-01;四、索引失效常见场景实操演示日常开发高频踩坑点以下写法都会导致索引失效触发全表扫描-- 1. 左侧模糊匹配 / 全模糊匹配索引失效SELECT*FROMuserWHEREnameLIKE%李;-- 2. 索引字段发生隐式类型转换索引失效-- phone 为字符串类型使用数字查询类型不匹配SELECT*FROMuserWHEREphone13800138000;-- 3. 索引列参与运算、函数计算索引失效SELECT*FROMuserWHEREage1030;-- 4. OR 连接的字段中存在无索引字段整体索引失效SELECT*FROMuserWHEREname李四ORcity北京;五、三大面试高频索引概念 实例讲解5.1 索引覆盖概念查询所需的所有字段全部存在于索引中数据库无需回表查询原始数据查询效率极高。-- 先创建复合索引CREATEINDEXidx_name_ageONuser(name,age);-- 查询字段 name、age 都在索引内触发索引覆盖无需回表SELECTname,ageFROMuserWHEREname张三;5.2 最左前缀原则概念使用复合索引时查询条件必须优先匹配索引最左侧字段不能跳跃、颠倒顺序否则索引失效。5.3 索引下推概念InnoDB 引擎特性在索引层先完成数据筛选再回表查询减少回表数据行数提升性能。-- 索引为 (name,age)-- 先在索引层过滤 name王五 且 age20 的数据再回表即索引下推SELECT*FROMuserWHEREname王五ANDage20;六、课后实操练习题基于上方user表完成以下练习为用户表合理创建单值索引、复合索引分别写出 3 条索引生效SQL、3 条索引失效SQL手写一条索引覆盖查询语句。参考答案-- 1. 创建索引-- 单值索引CREATEINDEXidx_phoneONuser(phone);-- 复合索引CREATEINDEXidx_city_nameONuser(city,name);-- 2. 索引生效 SQL3条SELECT*FROMuserWHEREphone13800138000;SELECT*FROMuserWHEREcity广州;SELECT*FROMuserWHEREcity广州ANDname赵六;-- 索引失效 SQL3条SELECT*FROMuserWHEREnameLIKE%王%;SELECT*FROMuserWHEREcity深圳;SELECT*FROMuserWHEREname钱七ORage30;-- 3. 索引覆盖查询语句-- 利用 idx_city_name 索引只查询索引内字段SELECTcity,nameFROMuserWHEREcity广州;
