Mysql高级4-索引的使用规则

我爱牛皮糖

一、最左前缀法则

　　如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列，如果跳跃某一列，索引将部分失效（后面的字段索引失效）
　　示例1：account_transaction表中创建一个联合索引，使用method字段+trader_staff_id字段+operator_staff_id字段三个字段当做联合索引

1. mysql> <strong>create index mto on</strong><strong> account_transaction(method, trader_staff_id, operator_staff_id);</strong>
2. Query OK, 0 rows affected (5.29 sec)
3. Records: 0  Duplicates: 0  Warnings: 0
5. mysql><strong> show index from account_transaction;
6. </strong>+---------------------+------------+-------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
7. | Table               | Non_unique | Key_name    | Seq_in_index | Column_name       | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
8. +---------------------+------------+-------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
9. | account_transaction |          0 | PRIMARY     |            1 | id                | A         |     2067077 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
10. | account_transaction |          1 | trade_index |            1 | trade_no          | A         |     2249115 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
11. | <strong>account_transaction |          1 | mto         |            1 | method            | A         |           3 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL  </strong>     |
12. |<strong> account_transaction |          1 | mto         |            2 | trader_staff_id   | A         |       31046 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL</strong>       |
13. | <strong>account_transaction |          1 | mto         |            3 | operator_staff_id | A         |       15847 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL      </strong> |
14. +---------------------+------------+-------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
15. 5 rows in set (0.00 sec)

复制代码

　　说明1：mto是一个联合索引，里面包含了三个字段method,trader_staff_id,operator_staff_id三个字段。
　　说明2：method是第1索引字段，即也是最左索引，trader_staff_id 是第2索引， operator_staff_id 是第3索引，这个顺序很重要！
 
　　案例1：同时按顺序使用三个字段查询一条数据

1. mysql> <strong>select * from account_transaction where method="CASH" and trader_staff_id=275 and operator_staff_id=12;
2. </strong>+--------+--------------------+--------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+
3. | id     | trade_no           | type   | method | time                       | payment       | out_trade_no | amount | balance | trader_staff_id | operator_staff_id | device_id | remark |
4. +--------+--------------------+--------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+
5. |     24 | 156384428075000275 | TOP_UP | CASH   | 2019-07-23 01:11:20.221977 | LOCAL_ACCOUNT |              |  10000 |   10000 |             275 |                12 | 6         |        |
6. | 747793 | 157370375171000275 | TOP_UP | CASH   | 2019-11-14 03:55:51.480417 | LOCAL_ACCOUNT |              |  10000 |   11000 |             275 |                12 | 6         |        |
7. +--------+--------------------+--------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+
8. 2 rows in set (0.00 sec)
10. mysql> explain select * from account_transaction where method="CASH" and trader_staff_id=275 and operator_staff_id=12;
11. +----+-------------+---------------------+------------+------+---------------+------+---------+-------------------+------+----------+-------+
12. | id | select_type | table               | partitions | type | possible_keys | key  | <strong>key_len</strong> | ref               | rows | filtered | Extra |
13. +----+-------------+---------------------+------------+------+---------------+------+---------+-------------------+------+----------+-------+
14. |  1 | SIMPLE      | account_transaction | NULL       | ref  | mto           | mto  | <strong>70  </strong>    | const,const,const |    2 |   100.00 | NULL  |
15. +----+-------------+---------------------+------------+------+---------------+------+---------+-------------------+------+----------+-------+
16. 1 row in set, 1 warning (0.00 sec)

复制代码

　　说明1：使用method,trader_staff_id,operator_staff_id三个字段作为查询条件，查询时间0.00秒以内
　　说明2：使用explain关键字查询执行计划，该查询使用的key是mto 即刚创建的联合索引，key_len是70长度。记住这个长度，我们在后面还会用到。
 
　　案例2：使用 method 和 trader_staff_id 两个字段作为查询条件

1. mysql> <strong>select * from account_transaction where method="CASH" and trader_staff_id=275</strong><strong>;</strong>
2. +--------+--------------------+--------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+
3. | id     | trade_no           | type   | method | time                       | payment       | out_trade_no | amount | balance | trader_staff_id | operator_staff_id | device_id | remark |
4. +--------+--------------------+--------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+
5. |     24 | 156384428075000275 | TOP_UP | CASH   | 2019-07-23 01:11:20.221977 | LOCAL_ACCOUNT |              |  10000 |   10000 |             275 |                12 | 6         |        |
6. | 747793 | 157370375171000275 | TOP_UP | CASH   | 2019-11-14 03:55:51.480417 | LOCAL_ACCOUNT |              |  10000 |   11000 |             275 |                12 | 6         |        |
7. +--------+--------------------+--------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+
8. 2 rows in set (0.00 sec)
10. mysql> <strong>explain select * from account_transaction where method="CASH" and trader_staff_id=275</strong><strong>;</strong>
11. +----+-------------+---------------------+------------+------+---------------+------+---------+-------------+------+----------+-------+
12. | id | select_type | table               | partitions | type | possible_keys | key  | key_len | ref         | rows | filtered | Extra |
13. +----+-------------+---------------------+------------+------+---------------+------+---------+-------------+------+----------+-------+
14. |  1 | SIMPLE      | account_transaction | NULL       | ref  | mto           | mto  | 66      | const,const |    2 |   100.00 | NULL  |
15. +----+-------------+---------------------+------------+------+---------------+------+---------+-------------+------+----------+-------+
16. 1 row in set, 1 warning (0.00 sec)

复制代码

　　说明1：通过explain执行计划，可以查看使用的key仍然是mto,但是key_len只有66，比上一条的key_len少了4位。说明operator_staff_id的索引失效，并且operator_staff_id的长度为4
 
　　案例3：使用method+operator_staff_id查询

1. mysql> <strong>explain select * from account_transaction where method="CASH" and operator_staff_id=12</strong><strong>;</strong>
2. +----+-------------+---------------------+------------+------+---------------+------+---------+-------+-------+----------+-----------------------+
3. | id | select_type | table               | partitions | type | possible_keys | key  | key_len | ref   | rows  | filtered | Extra                 |
4. +----+-------------+---------------------+------------+------+---------------+------+---------+-------+-------+----------+-----------------------+
5. |  1 | SIMPLE      | account_transaction | NULL       | ref  | mto           | mto  | 62      | const | 39916 |    10.00 | Using index condition |
6. +----+-------------+---------------------+------------+------+---------------+------+---------+-------+-------+----------+-----------------------+
7. 1 row in set, 1 warning (0.00 sec)

复制代码

　　说明1：使用了method+operator_staff_id作为查询条件，跳过了trader_staff_id字段，但是最左前缀method有使用，所以依然触发了mto索引。
　　说明2：key_len=62说明索引字段又变短了，那是因为从跳过的trader_staff_id字段，所以trader_staff_id及之后的索引字段就失效，案例2中的key_len是66，而现在又变成了62，说明trader_staff_id的索引长度也为4
　　　　
　　案例4：使用trader_staff_id + operator_staff_id查询

1. mysql> <strong>select * from account_transaction where trader_staff_id=275 and operator_staff_id=12;
2. </strong>+---------+--------------------+---------------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------------------------+
3. | id      | trade_no           | type          | method | time                       | payment       | out_trade_no | amount | balance | trader_staff_id | operator_staff_id | device_id | remark                   |
4. +---------+--------------------+---------------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------------------------+
5. |      24 | 156384428075000275 | TOP_UP        | CASH   | 2019-07-23 01:11:20.221977 | LOCAL_ACCOUNT |              |  10000 |   10000 |             275 |                12 | 6         |                          |
6. |  747793 | 157370375171000275 | TOP_UP        | CASH   | 2019-11-14 03:55:51.480417 | LOCAL_ACCOUNT |              |  10000 |   11000 |             275 |                12 | 6         |                          |
7. | 1993075 | 160454902688000275 | REFUND        | WEB    | 2020-11-05 04:03:46.980204 | LOCAL_ACCOUNT |              |  -3200 |       0 |             275 |                12 | 43        |                          |
8. | 3764809 | 162122330931000275 | TOP_UP        | CHEQUE | 2021-05-17 03:48:29.748154 | LOCAL_ACCOUNT |              |  10000 |   10000 |             275 |                12 | 6         |                          |
9. | 4791205 | 162856536047000275 | CONSUME_LUNCH | WEB    | 2021-08-04 04:46:17.000000 | LOCAL_ACCOUNT |              |    200 |    9400 |             275 |                12 | 35        | 管理后台补充消费         |
10. | 4791211 | 162856542884000275 | CONSUME_LUNCH | WEB    | 2021-08-05 04:46:17.000000 | LOCAL_ACCOUNT |              |    200 |    9200 |             275 |                12 | 35        | 管理后台补充消费         |
11. | 4791217 | 162856543723000275 | CONSUME_LUNCH | WEB    | 2021-08-06 04:46:17.000000 | LOCAL_ACCOUNT |              |    200 |    9000 |             275 |                12 | 35        | 管理后台补充消费         |
12. +---------+--------------------+---------------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------------------------+
13. 11 rows in set (4.58 sec)
15. mysql> <strong>explain select * from account_transaction where trader_staff_id=275 and operator_staff_id=12;
16. </strong>+----+-------------+---------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
17. | id | select_type | table               | partitions | type | possible_keys | <strong>key  | key_len</strong> | ref  | rows    | filtered | Extra       |
18. +----+-------------+---------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
19. |  1 | SIMPLE      | account_transaction | NULL       | ALL  | NULL          | <strong>NULL | NULL  </strong>  | NULL | 2249115 |     1.00 | Using where |
20. +----+-------------+---------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
21. 1 row in set, 1 warning (0.00 sec)

复制代码

　　说明1：查询时间为4.58秒，比之前用时多了很多
　　说明2：通过explain执行计划，可以发现该查询语句没有使用索引，是因为不符合最左前缀原则，即索引的最左边的method也就是第一索引列，这一列必须要使用，是触发组合索引的前缀。
 
　　案例5：包含最左前缀，但是最左前缀不在最前面

1. mysql> <strong>select * from account_transaction where trader_staff_id=275 and operator_staff_id=12 and method=</strong><strong>"CASH";</strong>
2. +--------+--------------------+--------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+
3. | id     | trade_no           | type   | method | time                       | payment       | out_trade_no | amount | balance | trader_staff_id | operator_staff_id | device_id | remark |
4. +--------+--------------------+--------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+
5. |     24 | 156384428075000275 | TOP_UP | CASH   | 2019-07-23 01:11:20.221977 | LOCAL_ACCOUNT |              |  10000 |   10000 |             275 |                12 | 6         |        |
6. | 747793 | 157370375171000275 | TOP_UP | CASH   | 2019-11-14 03:55:51.480417 | LOCAL_ACCOUNT |              |  10000 |   11000 |             275 |                12 | 6         |        |
7. +--------+--------------------+--------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+
8. 2 rows in set (0.00 sec)
10. mysql> <strong>explain select * from account_transaction where trader_staff_id=275 and operator_staff_id=12 and method=</strong><strong>"CASH";</strong>
11. +----+-------------+---------------------+------------+------+---------------+------+---------+-------------------+------+----------+-------+
12. | id | select_type | table               | partitions | type | possible_keys | key  | key_len | ref               | rows | filtered | Extra |
13. +----+-------------+---------------------+------------+------+---------------+------+---------+-------------------+------+----------+-------+
14. |  1 | SIMPLE      | account_transaction | NULL       | ref  | mto           | mto  | 70      | const,const,const |    2 |   100.00 | NULL  |
15. +----+-------------+---------------------+------------+------+---------------+------+---------+-------------------+------+----------+-------+
16. 1 row in set, 1 warning (0.00 sec)

复制代码

　　　　说明1：通过explain发现依然触发了mto索引，虽然最左前缀没有在最左边，但是只要出现了就可以，复合最左前缀法则。
 
二、范围查询


　　联合查询索引中，出现范围查询(>, explain select * from account_transaction where trader_staff_id>275 and operator_staff_id=12 and method="CASH";+----+-------------+---------------------+------------+-------+---------------+------+---------+------+-------+----------+----------------------------------+| id | select_type | table               | partitions | type  | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                            |+----+-------------+---------------------+------------+-------+---------------+------+---------+------+-------+----------+----------------------------------+|  1 | SIMPLE      | account_transaction | NULL       | range | mto           | mto  | 66      | NULL | 37708 |    10.00 | Using index condition; Using MRR |+----+-------------+---------------------+------------+-------+---------------+------+---------+------+-------+----------+----------------------------------+1 row in set, 1 warning (0.01 sec)[/code]　　说明2，在搜索条件中的trader_staff_id是一个范围查询使用的">"，因为trader_staff_id在创建索引的时候在第2顺序，所以该查询语句中，处于第三个字段的operator_staff_id字段就失效了，所以key_len是66

1. mysql> <strong>show index from</strong><strong> account_transaction;</strong>
2. +---------------------+------------+-------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3. | Table               | Non_unique | Key_name    | Seq_in_index | Column_name       | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
4. +---------------------+------------+-------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
5. | account_transaction |          0 | PRIMARY     |            1 | id                | A         |     2067077 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
6. | account_transaction |          1 | trade_index |            1 | trade_no          | A         |     2249115 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
7. | account_transaction |          1 | mto         |            1 | method            | A         |           3 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
8. | account_transaction |          1 | mto         |            2 | trader_staff_id   | A         |       31046 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
9. | account_transaction |          1 | mto         |            3 | operator_staff_id | A         |       15847 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
10. +---------------------+------------+-------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

复制代码

　　说明3：如果在不影响业务的时候，最好使用">="或者"