上一篇文章我们通过实操得出了一个结论：在单个索引的情况下，列的散列度越高，创建索引后的查询效果越明显。那么在查询条件中有多个的情况下，我们要怎么创建索引呢？答案是创建联合索引。首先我们来看一个查询语句：
- 查询user_name 和user_password
SELECT user_name,user_password from sys_user where user_name = 'zhangsan0' and user_password = '1234560'
上面是user_name 和 user_password 联合查询。在没有使用任何索引的情况下：
image.png
EXPLAIN SELECT user_name,user_password from sys_user where user_name = 'zhangsan0' and user_password = '1234560'
image.png
上面在没有使用任何索引的情况下，几乎是全表扫描。ok，现在我们来优化这个查询。
我们先创建单独的索引，也就是为
user_name 和 user_password 单独创建索引，不使用联合索引。
-
user_name单独创建索引
SELECT user_name,user_password from sys_user where user_name = 'zhangsan0' and user_password = '1234560'
SELECT user_name,user_password from sys_user where user_password = '1234560' and user_name = 'zhangsan0'
上面的语句是在给
user_name创建了索引的前提下执行的，发现两条sql 语句执行的结果查询效果相差无几，基本没有任何差别。说明：在and的情况下，条件的前后顺序在有索引的情况下，对查询结果没有影响。那是因为mysql 的底层对sql 语句在执行的时候做了优化，具体我们后面分析。
查询结果.png
- explain
image.png
image.png
explain 两条语句，发现结果一样，所以两条语句的执行效果是一样的。上面查询效果优化明显，是因为为
user_name 创建了普通索引，同时在where 条件使用了user_name 作为查询条件。如果我们在查询的时候，不使用user_name 作为查询条件的话，那么查询效率一样低下。
SELECT user_name,user_password from sys_user where user_password = '1234560'
结果是肯定的，因为上面这个语句没有命中索引，没有使用到索引。
-
user_password创建普通索引
上面查询语句我们指定，where条件中有两个user_name = xxxxx and user_password = xxxx。为了解决使用user_password作为条件查询慢的问题，我们再为user_password创建索引
image.png
-
执行查询
image.png
SELECT user_name,user_password from sys_user where user_password = '1234560'
image.png
SELECT user_name,user_password from sys_user where user_name = 'zhangsan0' and user_password = '1234560'
根据上面两条sql 语句执行的情况看，在查询时都命中了索引。
- 多条件查询创建联合索引
上面 使用
user_name和user_password作为查询条件时，创建了两个索引，那么在mysql 底层就维护了两个B+tree 。如果在多个查询条件中，没有用到 单个条件查询时，也就是说user_name或者user_password没有单独作为查询条件使用，建议使用联合索引。
- 创建联合索引
idx_user_name_user_password
联合索引.png
注意：联合索引，
user_name在user_password的左边，也就是在构建B+tree 时，在一个节点中user_name在user_password的左边。懵逼的话，没关系，我们先看案例
- 查询条件中命中
user_name。使用联合索引
image.png
SELECT user_name,user_password FROM sys_user where user_name = 'zhangsan0'
-
explain
image.png
如图，我们在查询中使用了联合索引。
- 查询条件使用
user_password，不能使用联合索引
SELECT user_name,user_password FROM sys_user where user_password = '1234560'
image.png
-
explain
未命中索引.png
上面查询结果显示我们在查询时，未使用到联合索引。原因就在一个B+tree 中，user_name 为被查询，无法判断后面节点的走向，虽然建立了索引，但是无法使用。后面我们在分析 B+tree 的数据结构时在详细说明。于是就有了 联合索引最左匹配原则。啥意思，接下来就来说一下。
- 能够使用到联合索引的情况
SELECT user_name,user_password FROM sys_user where user_name = 'zhangsan0'
SELECT user_name,user_password FROM sys_user where user_name = 'zhangsan0' and user_password = '1234560'
SELECT user_name,user_password FROM sys_user where user_password = '1234560' and user_name = 'zhangsan0'
ok，上面都是可以使用到联合索引的情况，都一个共同的查询条件user_name。注意，我们创建的索引的时候，user_name 在user_password 的左边。联合索引的最左匹配原则就是 在查询条件中 必须要命中最左边的字段（user_name）或者说最左边字段所在的索引。
- 注意
最左匹配原则是指的命中索引中最左的那个字段就ok，不是要求 字段（user_name）一定要在 sql 语句的最左。下面的这两句在命中索引的时候是一样的。
SELECT user_name,user_password FROM sys_user where user_password = '1234560' and user_name = 'zhangsan0'
SELECT user_name,user_password FROM sys_user where user_name = 'zhangsan0' and user_password = '1234560'
- 无法命中联合索引的情况
SELECT user_name,user_password FROM sys_user where user_password = '1234560'
SELECT user_name,user_password FROM sys_user where user_name = 'zhangsan0' OR user_password = '1234560'
SELECT user_name,user_password FROM sys_user where user_password = '1234560' or user_name = 'zhangsan0'
SELECT user_name,user_password FROM sys_user where user_password = '1234560' 未使用user_name 自然无法命中。
SELECT user_name,user_password FROM sys_user where user_name = 'zhangsan0' OR user_password = '1234560' or 不走索引的原因，暂时未明白，如有读者明白，还望不吝赐教。
网友评论