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 apple\G *************************** 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) >
##########################################
igoodful@qq.com
