MySQL中的联合索引(也叫组合索引)遵循最左匹配原则，即在创建联合索引时，查询条件必须从索引的最左边开始，否则索引不会被使用。在联合索引的情况下，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序。
例如，假设有一个表t_employees，它有一个联合索引(first_name, last_name)。
([email protected])[superdb]> create table t_employees as select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY from employees;
Query OK, 91 rows affected (0.06 sec)
Records: 91 Duplicates: 0 Warnings: 0
([email protected])[superdb]> select * from t_employees;
+-------------+-------------+-------------+----------+
| EMPLOYEE_ID | FIRST_NAME | LAST_NAME | SALARY |
+-------------+-------------+-------------+----------+
| 116 | Shelli | Baida | 2900.00 |
| 117 | Sigal | Tobias | 2800.00 |
| 118 | Guy | Himuro | 2600.00 |
| 119 | Karen | Colmenares | 2500.00 |
| 120 | Matthew | Weiss | 8000.00 |
| 121 | Adam | Fripp | 8200.00 |
| 122 | Payam | Kaufling | 7900.00 |
| 123 | Shanta | Vollman | 6500.00 |
| 124 | Kevin | Mourgos | 5800.00 |
| 125 | Julia | Nayer | 3200.00 |
| 126 | Irene | Mikkilineni | 2700.00 |
| 127 | James | Landry | 2400.00 |
| 128 | Steven | Markle | 2200.00 |
| 129 | Laura | Bissot | 3300.00 |
| 130 | Mozhe | Atkinson | 2800.00 |
| 131 | James | Marlow | 2500.00 |
| 132 | TJ | Olson | 2100.00 |
| 133 | Jason | Mallin | 3300.00 |
| 134 | Michael | Rogers | 2900.00 |
| 135 | Ki | Gee | 2400.00 |
| 136 | Hazel | Philtanker | 2200.00 |
| 137 | Renske | Ladwig | 3600.00 |
| 138 | Stephen | Stiles | 3200.00 |
| 139 | John | Seo | 2700.00 |
| 140 | Joshua | Patel | 2500.00 |
| 141 | Trenna | Rajs | 3500.00 |
| 142 | Curtis | Davies | 3100.00 |
| 143 | Randall | Matos | 2600.00 |
| 144 | Peter | Vargas | 2500.00 |
([email protected])[superdb]> alter
table t_employees add constraint pk_t_employees_id primary key(EMPLOYEE_ID);
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
([email protected])[superdb]> create index indx_t_employees_nameinfo on t_employees(FIRST_NAME,LAST_NAME);
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
([email protected])[superdb]> show index from t_employees;
+-------------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t_employees | 0 | PRIMARY | 1 | EMPLOYEE_ID | A | 91 | NULL | NULL | | BTREE | | | YES | NULL |
| t_employees | 1 | indx_t_employees_nameinfo | 1 | FIRST_NAME | A | 79 | NULL | NULL | YES | BTREE | | | YES | NULL |
| t_employees | 1 | indx_t_employees_nameinfo | 2 | LAST_NAME | A | 91 | NULL | NULL | | BTREE | | | YES | NULL |
+-------------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.01 sec)
1、满足联合索引最左匹配原则
以下查询会使用这个联合索引：
SELECT * FROM t_employees WHERE first_name = ‘James’;
SELECT * FROM t_employees WHERE first_name = ‘James’ AND last_name = ‘Marlow’;
([email protected])[superdb]> explain SELECT * FROM t_employees WHERE first_name = 'James';
+----+-------------+-------------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t_employees | NULL | ref | indx_t_employees_nameinfo | indx_t_employees_nameinfo | 83 | const | 2 | 100.00 | NULL |
+----+-------------+-------------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
([email protected])[superdb]> explain SELECT * FROM t_employees WHERE first_name = 'James' and LAST_NAME='Marlow';
+----+-------------+-------------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | t_employees | NULL | ref | indx_t_employees_nameinfo | indx_t_employees_nameinfo | 185 | const,const | 1 | 100.00 | NULL |
+----+-------------+-------------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
但是，下面的查询不会使用联合索引：
SELECT * FROM t_employees WHERE LAST_NAME=‘Marlow’;
([email protected])[superdb]> explain SELECT * FROM t_employees WHERE LAST_NAME='Marlow';
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t_employees | NULL | ALL | NULL | NULL | NULL | NULL | 91 | 10.00 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
因为它们没有从索引的最左边开始。在联合索引的情况下，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序。
下面的查询会使用联合索引
([email protected])[superdb]> explain SELECT * FROM t_employees WHERE LAST_NAME='Marlow' and first_name = 'James';
+----+-------------+-------------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | t_employees | NULL | ref | indx_t_employees_nameinfo | indx_t_employees_nameinfo | 185 | const,const | 1 | 100.00 | NULL |
+----+-------------+-------------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配。需要注意的是，因为有查询优化器，所以 first_name,last_name 字段在 where 子句的顺序并不重要
2、联合索引不遵循最左匹配原则，也是走全扫描二级索引树
我们都知道联合索引要遵循最左匹配才能走索引，但是如果数据库表中的字段都是索引的话，即使查询过程中，没有遵循最左匹配原则，也是走全扫描二级索引树(type=index)
如下的表结构及查询
([email protected])[superdb]> create table t_emplist as select EMPLOYEE_ID,FIRST_NAME,LAST_NAME from employees;
Query OK, 91 rows affected (0.10 sec)
Records: 91 Duplicates: 0 Warnings: 0
([email protected])[superdb]> alter table t_emplist add constraint pk_t_emplist_id primary key(EMPLOYEE_ID);
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
([email protected])[superdb]> create index indx_t_emplist_nameinfo on t_emplist(FIRST_NAME,LAST_NAME);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
如下面的查询会使用联合索引，但不是最左匹配原则
SELECT * FROM t_emplist WHERE LAST_NAME=‘Marlow’;
([email protected])[superdb]> explain SELECT * FROM t_emplist WHERE LAST_NAME='Marlow';
+----+-------------+-----------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | t_emplist | NULL | index | indx_t_emplist_nameinfo | indx_t_emplist_nameinfo | 185 | NULL | 91 | 10.00 | Using where; Using index |
+----+-------------+-----------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.01 sec)
如果数据库表中的字段只有主键+二级索引，那么即使查询的where条件不满足最左匹配原则，也不会走全表扫描（type=all），而是走全扫描二级索引树(type=index)。
关键还是看数据表中的字段及索引情况。
标签：NAME,employees,192.168,左匹配,索引,MySQL,NULL,type From： https://blog.csdn.net/zxrhhm/article/details/140240993