[[MySQL]] 最左前缀匹配原则详解1. 什么是最左前缀匹配原则最左前缀匹配原则是 MySQL联合索引组合索引的重要规则。简单来说当使用联合索引查询时MySQL 会从索引的最左列开始匹配不能跳过中间列。例如创建联合索引CREATEINDEXidx_userONuser(name,age,city);索引顺序name → age → cityMySQL 会按照这个顺序组织索引数据。因此可以匹配name name age name age city不能直接匹配age city age city2. 联合索引为什么遵循最左匹配MySQL InnoDB 的索引底层结构是BTree。假设存在联合索引(name,age,city)索引排序规则第一层按照 name 排序name 相同按照 age 排序age 相同按照 city 排序例如张三 18 北京 张三 20 上海 李四 18 广州 李四 25 深圳可以快速找到张三 张三 20岁 张三 20岁 上海但是无法直接找到18岁 北京因为索引首先按照 name 排序。3. 最左匹配示例创建索引CREATEINDEXidx_userONuser(name,age,city);3.1 完全匹配索引SQLSELECT*FROMuserWHEREname张三ANDage20ANDcity北京;匹配顺序name → age → city可以使用完整联合索引。3.2 只匹配第一个字段SQLSELECT*FROMuserWHEREname张三;匹配name符合最左原则。可以使用索引。3.3 匹配前两个字段SQLSELECT*FROMuserWHEREname张三ANDage20;匹配name → age可以使用索引。4. 哪些情况无法充分使用索引4.1 跳过最左字段SQLSELECT*FROMuserWHEREage20;索引(name, age, city)查询age但是缺少nameMySQL 无法快速定位索引范围。通常无法使用该联合索引。4.2 跳过中间字段SQLSELECT*FROMuserWHEREname张三ANDcity北京;索引(name, age, city)匹配name跳过age再查询city结果MySQL 可以使用name这一部分索引。但是city无法继续利用索引定位。原因联合索引必须按照name → age → city顺序匹配。5. 范围查询影响索引匹配这是面试中经常考的问题。索引(name,age,city)SQLSELECT*FROMuserWHEREname张三ANDage18ANDcity北京;执行过程先匹配name然后age 18由于 age 是范围查询 BETWEEN LIKE 前缀查询MySQL 后续无法继续精确匹配city所以name √ age √ city ×记忆范围查询之后索引匹配通常停止。6. LIKE 查询是否使用索引可以使用索引SELECT*FROMuserWHEREnameLIKE张%;原因因为知道开头张可以定位索引范围。无法使用索引SELECT*FROMuserWHEREnameLIKE%张;原因不知道开头字符。MySQL 无法确定搜索范围。通常会导致全表扫描。7. WHERE 条件顺序影响索引吗很多人认为WHEREage20ANDname张三不会走索引。实际上不会。MySQL 优化器会自动调整查询条件。例如索引(name,age)下面两个 SQLWHEREname张三ANDage20和WHEREage20ANDname张三最终都会按照name → age使用索引。真正影响索引的是是否满足最左匹配规则。8. 如何设计联合索引8.1 高频查询字段优先例如经常查询SELECT*FROMorderWHEREuser_id100ANDstatus1;可以建立INDEX(user_id,status)8.2 区分度高的字段优先例如字段gender只有男 女区分度较低。字段user_id通常唯一。区分度更高。8.3 不要创建过多索引索引越多优点查询更快缺点占用空间降低新增数据速度更新数据需要维护索引9. 面试总结Q什么是最左前缀匹配原则回答MySQL 使用联合索引时会按照索引字段从左到右匹配查询条件。如果缺少最左字段后续字段通常无法使用索引。Q联合索引(a,b,c)哪些可以使用可以a a,b a,b,c不能充分使用b c b,cQ为什么不能跳过中间字段因为 BTree 索引按照a → b → c排序。缺少 a 时无法确定 b 的查找范围。Q范围查询为什么影响后面的字段因为范围查询只能确定一个范围。例如age20之后的数据顺序无法继续精确定位。总结最左前缀匹配原则记住三句话联合索引从最左字段开始匹配不能跳过中间字段遇到范围查询后续字段通常无法继续使用索引口诀从左往右匹配不能跳过遇到范围就停止。
[[MySQL]] 最左前缀匹配原则详解1. 什么是最左前缀匹配原则最左前缀匹配原则是 MySQL联合索引组合索引的重要规则。简单来说当使用联合索引查询时MySQL 会从索引的最左列开始匹配不能跳过中间列。例如创建联合索引CREATEINDEXidx_userONuser(name,age,city);索引顺序name → age → cityMySQL 会按照这个顺序组织索引数据。因此可以匹配name name age name age city不能直接匹配age city age city2. 联合索引为什么遵循最左匹配MySQL InnoDB 的索引底层结构是BTree。假设存在联合索引(name,age,city)索引排序规则第一层按照 name 排序name 相同按照 age 排序age 相同按照 city 排序例如张三 18 北京 张三 20 上海 李四 18 广州 李四 25 深圳可以快速找到张三 张三 20岁 张三 20岁 上海但是无法直接找到18岁 北京因为索引首先按照 name 排序。3. 最左匹配示例创建索引CREATEINDEXidx_userONuser(name,age,city);3.1 完全匹配索引SQLSELECT*FROMuserWHEREname张三ANDage20ANDcity北京;匹配顺序name → age → city可以使用完整联合索引。3.2 只匹配第一个字段SQLSELECT*FROMuserWHEREname张三;匹配name符合最左原则。可以使用索引。3.3 匹配前两个字段SQLSELECT*FROMuserWHEREname张三ANDage20;匹配name → age可以使用索引。4. 哪些情况无法充分使用索引4.1 跳过最左字段SQLSELECT*FROMuserWHEREage20;索引(name, age, city)查询age但是缺少nameMySQL 无法快速定位索引范围。通常无法使用该联合索引。4.2 跳过中间字段SQLSELECT*FROMuserWHEREname张三ANDcity北京;索引(name, age, city)匹配name跳过age再查询city结果MySQL 可以使用name这一部分索引。但是city无法继续利用索引定位。原因联合索引必须按照name → age → city顺序匹配。5. 范围查询影响索引匹配这是面试中经常考的问题。索引(name,age,city)SQLSELECT*FROMuserWHEREname张三ANDage18ANDcity北京;执行过程先匹配name然后age 18由于 age 是范围查询 BETWEEN LIKE 前缀查询MySQL 后续无法继续精确匹配city所以name √ age √ city ×记忆范围查询之后索引匹配通常停止。6. LIKE 查询是否使用索引可以使用索引SELECT*FROMuserWHEREnameLIKE张%;原因因为知道开头张可以定位索引范围。无法使用索引SELECT*FROMuserWHEREnameLIKE%张;原因不知道开头字符。MySQL 无法确定搜索范围。通常会导致全表扫描。7. WHERE 条件顺序影响索引吗很多人认为WHEREage20ANDname张三不会走索引。实际上不会。MySQL 优化器会自动调整查询条件。例如索引(name,age)下面两个 SQLWHEREname张三ANDage20和WHEREage20ANDname张三最终都会按照name → age使用索引。真正影响索引的是是否满足最左匹配规则。8. 如何设计联合索引8.1 高频查询字段优先例如经常查询SELECT*FROMorderWHEREuser_id100ANDstatus1;可以建立INDEX(user_id,status)8.2 区分度高的字段优先例如字段gender只有男 女区分度较低。字段user_id通常唯一。区分度更高。8.3 不要创建过多索引索引越多优点查询更快缺点占用空间降低新增数据速度更新数据需要维护索引9. 面试总结Q什么是最左前缀匹配原则回答MySQL 使用联合索引时会按照索引字段从左到右匹配查询条件。如果缺少最左字段后续字段通常无法使用索引。Q联合索引(a,b,c)哪些可以使用可以a a,b a,b,c不能充分使用b c b,cQ为什么不能跳过中间字段因为 BTree 索引按照a → b → c排序。缺少 a 时无法确定 b 的查找范围。Q范围查询为什么影响后面的字段因为范围查询只能确定一个范围。例如age20之后的数据顺序无法继续精确定位。总结最左前缀匹配原则记住三句话联合索引从最左字段开始匹配不能跳过中间字段遇到范围查询后续字段通常无法继续使用索引口诀从左往右匹配不能跳过遇到范围就停止。
