MySQL聯合索引最左匹配原則

MySQL中的聯合索引(也叫組合索引)遵循最左匹配原則，即在創建聯合索引時，查詢條件必須從索引的最左邊開始，否則索引不會被使用。在聯合索引的情況下，數據是按照索引第一列排序，第一列數據相同時才會按照第二列排序。

例如，假設有一個表t_employees，它有一個聯合索引(first_name, last_name)。

(root@192.168.80.85)[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(root@192.168.80.85)[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 |(root@192.168.80.85)[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(root@192.168.80.85)[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(root@192.168.80.85)[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’;

(root@192.168.80.85)[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)(root@192.168.80.85)[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’;

(root@192.168.80.85)[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)

因為它們沒有從索引的最左邊開始。在聯合索引的情況下，數據是按照索引第一列排序，第一列數據相同時才會按照第二列排序。

下面的查詢會使用聯合索引

(root@192.168.80.85)[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)

如下的表結構及查詢

(root@192.168.80.85)[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(root@192.168.80.85)[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(root@192.168.80.85)[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’;

(root@192.168.80.85)[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)