Mysql复合索引

当索引由多个字段组成时，称为复合索引或组合索引。当where谓词条件包含非等值匹配的操作时，如>，< ，!= 等，索引中此字段之后的谓词条件将不会使用索引。

例如:索引abc_ck由字段(a,b,c)组成。谓词条件为where a=‘aaa’ and b>100 and c=‘ccc’

此查询a，b字段可以使用到索引，c字段无法使用索引。

在表emps的(gender,birth_date,first_name)字段上创建一个组合索引

mysql> create index gbn_ck on emps(gender,birth_date,first_name);
Query OK, 0 rows affected (2.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

显示创建的索引

mysql> show indexes from emps;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| emps  |          0 | PRIMARY   |            1 | id          | A         |      299150 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| emps  |          1 | emp_no_uk |            1 | emp_no      | A         |      299150 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| emps  |          1 | gbn_ck    |            1 | gender      | A         |           1 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| emps  |          1 | gbn_ck    |            2 | birth_date  | A         |        9402 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| emps  |          1 | gbn_ck    |            3 | first_name  | A         |      291233 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
5 rows in set (0.02 sec)

查看索引三个字段=值条件的执行计划

mysql> desc format=tree select * from emps where gender='F' and birth_date = '1964-06-02' and first_name = 'Bezalel';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Index lookup on emps using gbn_ck (gender='F', birth_date=DATE'1964-06-02', first_name='Bezalel'), with index condition: (emps.gender = 'F')  (cost=0.35 rows=1)
 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

#using gbn_ck (gender='F', birth_date=DATE'1964-06-02', first_name='Bezalel')
#表示查询用到了gbn_ck索引，括号中列出的是使用到了索引中的哪几个字段
#此查询使用了索引中的3个字段

查看索引三个字段中间字段为非=值匹配的执行计划

mysql> desc format=tree select * from emps where gender='F' and birth_date > '1964-06-02' and first_name = 'Bezalel';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                    |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Index range scan on emps using gbn_ck over (gender = 'f' AND '1964-06-02' < birth_date), with index condition: ((emps.first_name = 'Bezalel') and (emps.gender = 'F') and (emps.birth_date > DATE'1964-06-02'))  (cost=2706 rows=6013)
 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

#gbn_ck over (gender = 'f' AND '1964-06-02' < birth_date)
#表明使用到了gbn_ck所以，括号中只有前两个字段，说明只使用到了索引的前两个字段

查看未使用索引第一个字段=值匹配的执行计划

mysql> desc format=tree select * from emps where birth_date = '1964-06-02' and first_name = 'Bezalel';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                          |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: ((emps.first_name = 'Bezalel') and (emps.birth_date = DATE'1964-06-02'))  (cost=30163 rows=2992)
    -> Table scan on emps  (cost=30163 rows=299150)
 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

#Table scan on emps
#表明使用的是全表扫描

总结：

1.复合索引要满足最左前缀原则才能使用到索引，即不能忽略索引左边字段而单独使用后面的字段。
2.如果符合索引的字段where条件上有困难谓词，即使用了非=值匹配，则复合所以中此字段之后的字段不会用到索引。
3.执行计划中使用了复合索引的哪几个字段可以通过树形结构的执行计划查看。