mysql的最左匹配规则

#####################################

举例说明：

root@10.10.10.10(test) > select * from apple;                                        
+----+------+------+--------+
| id | uuid | name | title  |
+----+------+------+--------+
|  1 |    1 | glc  | iphone |
|  2 |    1 | glc  | iphone |
|  3 |    1 | glc  | iphone |
|  4 |    2 | glc  | iphone |
|  5 |    2 | glc  | iphone |
|  6 |    2 | glc  | iphone |
|  7 |    2 | wjl  | iphone |
|  8 |    2 | wjl  | iphone |
|  9 |    2 | wjl  | iphone |
| 10 |    2 | wjl  | imac   |
| 11 |    2 | wjl  | imac   |
| 12 |    2 | wjl  | imac   |
| 13 |    2 | glc  | imac   |
| 14 |    2 | glc  | imac   |
| 15 |    2 | glc  | imac   |
| 16 |    3 | glc  | imac   |
| 17 |    3 | glc  | imac   |
| 18 |    3 | glc  | imac   |
| 19 |    2 | glc  | imac   |
| 20 |    2 | glc  | imac   |
| 21 |    2 | glc  | imac   |
| 22 |    2 | zd   | imac   |
| 23 |    2 | zd   | imac   |
| 24 |    2 | zd   | imac   |
| 25 |    1 | zd   | imac   |
| 26 |    1 | zd   | imac   |
| 27 |    1 | zd   | imac   |
+----+------+------+--------+
27 rows in set (0.01 sec)

Thu Apr 22 12:08:37 2021
root@10.10.10.10(test) > show create table appleG                                   
*************************** 1. row ***************************
       Table: apple
Create Table: CREATE TABLE `apple` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `uuid` bigint(20) NOT NULL DEFAULT '0' COMMENT '用户id',
  `name` varchar(64) NOT NULL DEFAULT '' COMMENT '商品名称',
  `title` varchar(64) NOT NULL DEFAULT '' COMMENT '标题',
  PRIMARY KEY (`id`),
  KEY `idx_uuid_name` (`uuid`,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

Thu Apr 22 12:08:41 2021
root@10.10.10.10(test) > desc select * from  apple  where uuid=1;                    
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | apple | NULL       | ref  | idx_uuid_name | idx_uuid_name | 8       | const |    6 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Thu Apr 22 12:08:54 2021
root@10.10.10.10(test) > desc select * from  apple  where uuid=1 and name='glc';
+----+-------------+-------+------------+------+---------------+---------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key           | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | apple | NULL       | ref  | idx_uuid_name | idx_uuid_name | 266     | const,const |    3 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Thu Apr 22 12:09:06 2021
root@10.10.10.10(test) > desc select * from  apple  where name='glc';           
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | apple | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   27 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Thu Apr 22 12:10:04 2021
root@10.10.10.10(test) > desc select uuid from  apple  where name='glc';  
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | apple | NULL       | index | NULL          | idx_uuid_name | 266     | NULL |   27 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

Thu Apr 22 12:10:17 2021
root@10.10.10.10(test) > desc select uuid,name from  apple  where name='glc';
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | apple | NULL       | index | NULL          | idx_uuid_name | 266     | NULL |   27 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

Thu Apr 22 12:10:25 2021
root@10.10.10.10(test) > desc select name from  apple  where name='glc';     
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | apple | NULL       | index | NULL          | idx_uuid_name | 266     | NULL |   27 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

Thu Apr 22 12:10:31 2021
root@10.10.10.10(test) > desc select title from  apple  where name='glc';     
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | apple | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   27 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Thu Apr 22 12:10:43 2021
root@10.10.10.10(test) > desc select uuid,name,title from  apple  where name='glc';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | apple | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   27 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Thu Apr 22 12:10:59 2021
root@10.10.10.10(test) > 

理解：KEY `idx_uuid_name` (`uuid`,`name`)

一般而言，查询的where条件必须有uuid才能走idx_uuid_name索引，比如 where uuid=1, where uuid=2 and name='glc'

但是当where条件为联合索引后面的字段时，那么也不一定不会走该idx_uuid_name索引，那么什么情况下会走呢？

当select的字段为该联合索引的字段的时候，比如

select uuid from apple where name='glc';

select uuid,name from apple where name='glc';

select namefrom apple where name='glc';

select count(*)  from apple where name='glc';

select count(uuid)  from apple where name='glc';

select max(uuid)  from apple where name='glc';

select min(uuid)  from apple where name='glc';

root@10.10.10.10(test) > desc select count(*) from  apple  where name='glc';               
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | apple | NULL       | index | NULL          | idx_uuid_name | 266     | NULL |   27 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

Thu Apr 22 12:20:17 2021
root@10.10.10.10(test) > desc select count(uuid) from  apple  where name='glc'; 
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | apple | NULL       | index | NULL          | idx_uuid_name | 266     | NULL |   27 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

Thu Apr 22 12:20:25 2021
root@10.10.10.10(test) > desc select count(distinct uuid) from  apple  where name='glc';
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                                            |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------------------------------+
|  1 | SIMPLE      | apple | NULL       | range | idx_uuid_name | idx_uuid_name | 266     | NULL |   28 |   100.00 | Using where; Using index for group-by (scanning) |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Thu Apr 22 12:20:33 2021
root@10.10.10.10(test) > desc select max(uuid) from  apple  where name='glc';              
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | apple | NULL       | index | NULL          | idx_uuid_name | 266     | NULL |   27 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

Thu Apr 22 12:20:49 2021
root@10.10.10.10(test) > desc select min(name) from  apple  where name='glc';       
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | apple | NULL       | index | NULL          | idx_uuid_name | 266     | NULL |   27 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

Thu Apr 22 12:20:59 2021
root@10.10.10.10(test) >

##########################################