【最左匹配原则和索引下推】mysql查询优化

表结构和索引如下图：

select id, username, age, email from ums_member where username='wufan';

select id, username, age, email from ums_member where age=22;

select id, username, age, email from ums_member where email='wufan@qq.com';

以上3条sql语句哪个走了索引？

执行计划：

mysql> explain select id, username, age, email from ums_member where username='wufan'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ums_member
   partitions: NULL
         type: ref
possible_keys: idx_username_age_email
          key: idx_username_age_email
      key_len: 194
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index

mysql> explain select id, username, age, email from ums_member where age=22 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ums_member
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_username_age_email
      key_len: 498
          ref: NULL
         rows: 9
     filtered: 11.11
        Extra: Using where; Using index
        
mysql> explain select id, username, age, email from ums_member where email='785764645@qq.com' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ums_member
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_username_age_email
      key_len: 498
          ref: NULL
         rows: 9
     filtered: 11.11
        Extra: Using where; Using index

三条sql语句都走了组合索引(username, age, email) ; 只有第一条sql符合最左匹配原则；其它2条sql也走组合索引；是由于组合索引中都包含了

查询的字段信息；sql优化器帮我们进行了走索引优化；

最左匹配原则

再来看以下3条sql语句；都还会走索引吗？

select * from ums_member where username='wufan';

select * from ums_member where age=22;

select * from ums_member where email='wufan@qq.com';

查看执行计划：

mysql> explain select * from ums_member where username='wufan'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ums_member
   partitions: NULL
         type: ref
possible_keys: idx_username_age_email
          key: idx_username_age_email
      key_len: 194
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from ums_member where age=22 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ums_member
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from ums_member where email='wufan@qq.com'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ums_member
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

很明显只有第一条sql符合最左匹配原则走了索引；最左匹配原则是根据索引顺序(username, age, email)从左到右依次匹配；若是最左一个没有匹配上，其它的索引都将不会匹配上；若第一个匹配上；第二个没有匹配上， 也不会走第三个；

即：

where username='wufan' and age=22                            -- 匹配username, age
where username='wufan' and age=22 and email='wufan@qq.com'   -- (username, age, email)全匹配
where age=22 and email='wufan@qq.com'                        -- 不匹配该组合索引
where username='wufan' and email='wufan@qq.com'   			 -- 只匹配username

但若是某个索引列开始进行范围和右模糊条件筛选， 后续的索引列将无法使用索引匹配

-- 只匹配username
mysql> explain select * from ums_member where username like 'wu%' and age = 22 and email= 'wufan@qq.com'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ums_member
   partitions: NULL
         type: range
possible_keys: idx_username_age_email
          key: idx_username_age_email
      key_len: 498
          ref: NULL
         rows: 2
     filtered: 11.11
        Extra: Using index condition
        
-- 只匹配username和age
mysql> explain select * from ums_member where username = 'wufan' and age >= 22 and email= 'wufan@qq.com'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ums_member
   partitions: NULL
         type: range
possible_keys: idx_username_age_email
          key: idx_username_age_email
      key_len: 498
          ref: NULL
         rows: 1
     filtered: 11.11
        Extra: Using index condition

注意： 若开始的索引列使用全模糊匹配，索引会失效；使用全表扫描！

mysql> explain select * from ums_member where username like '%wu%' and age = 22 and email= 'wufan@qq.com'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ums_member
   partitions: NULL
         type: ALL  -- 全表扫描
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9
     filtered: 11.11
        Extra: Using where -- 使用where进行条件过滤

索引下推(Index Condition Pushdown)：

索引下推是mysql5.6及之后版本新增的优化

SELECT * FROM ums_member WHERE username="lisi" and age>=20

sql执行时；先匹配name; 将name和age数据从存储引擎中加载到service层； 在service层进行age>=20过滤；

索引下推：是在存储层先匹配name; 在根据匹配到的数据过滤age>=20的数据；最终的数据加载到service层； 访问service层获取数据；由于数据量减少，所以减少IO；

mysql> explain SELECT * FROM ums_member WHERE username="lisi" and age>=20 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ums_member
   partitions: NULL
         type: range
possible_keys: idx_username_age_email
          key: idx_username_age_email
      key_len: 196
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition  -- 代表使用了索引下推

索引下推必须是组合索引；普通索引只有一个字段信息；不会进行索引下推；

总的来说， 索引下推就是是把service层数据匹配过滤处理下放到存储层进行；从而减少IO操作!