Mysql高级4-索引的使用规则
一、最左前缀法则如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列，如果跳跃某一列，索引将部分失效（后面的字段索引失效）
示例1：account_transaction表中创建一个联合索引，使用method字段+trader_staff_id字段+operator_staff_id字段三个字段当做联合索引
mysql> <strong>create index mto on</strong><strong> account_transaction(method, trader_staff_id, operator_staff_id);</strong>
Query OK, 0 rows affected (5.29 sec)
Records: 0Duplicates: 0Warnings: 0
mysql><strong> show index from account_transaction;
</strong>+---------------------+------------+-------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------------------+------------+-------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| account_transaction | 0 | PRIMARY | 1 | id | A | 2067077 | NULL | NULL | | BTREE | | | YES | NULL |
| account_transaction | 1 | trade_index | 1 | trade_no | A | 2249115 | NULL | NULL | | BTREE | | | YES | NULL |
| <strong>account_transaction | 1 | mto | 1 | method | A | 3 | NULL | NULL | | BTREE | | | YES | NULL</strong> |
|<strong> account_transaction | 1 | mto | 2 | trader_staff_id | A | 31046 | NULL | NULL | | BTREE | | | YES | NULL</strong> |
| <strong>account_transaction | 1 | mto | 3 | operator_staff_id | A | 15847 | NULL | NULL | | BTREE | | | YES | NULL </strong> |
+---------------------+------------+-------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
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：同时按顺序使用三个字段查询一条数据
mysql> <strong>select * from account_transaction where method="CASH" and trader_staff_id=275 and operator_staff_id=12;
</strong>+--------+--------------------+--------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+
| id | trade_no | type | method | time | payment | out_trade_no | amount | balance | trader_staff_id | operator_staff_id | device_id | remark |
+--------+--------------------+--------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+
| 24 | 156384428075000275 | TOP_UP | CASH | 2019-07-23 01:11:20.221977 | LOCAL_ACCOUNT | |10000 | 10000 | 275 | 12 | 6 | |
| 747793 | 157370375171000275 | TOP_UP | CASH | 2019-11-14 03:55:51.480417 | LOCAL_ACCOUNT | |10000 | 11000 | 275 | 12 | 6 | |
+--------+--------------------+--------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+
2 rows in set (0.00 sec)
mysql> explain select * from account_transaction where method="CASH" and trader_staff_id=275 and operator_staff_id=12;
+----+-------------+---------------------+------------+------+---------------+------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key| <strong>key_len</strong> | ref | rows | filtered | Extra |
+----+-------------+---------------------+------------+------+---------------+------+---------+-------------------+------+----------+-------+
|1 | SIMPLE | account_transaction | NULL | ref| mto | mto| <strong>70</strong> | const,const,const | 2 | 100.00 | NULL|
+----+-------------+---------------------+------------+------+---------------+------+---------+-------------------+------+----------+-------+
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 两个字段作为查询条件
mysql> <strong>select * from account_transaction where method="CASH" and trader_staff_id=275</strong><strong>;</strong>
+--------+--------------------+--------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+
| id | trade_no | type | method | time | payment | out_trade_no | amount | balance | trader_staff_id | operator_staff_id | device_id | remark |
+--------+--------------------+--------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+
| 24 | 156384428075000275 | TOP_UP | CASH | 2019-07-23 01:11:20.221977 | LOCAL_ACCOUNT | |10000 | 10000 | 275 | 12 | 6 | |
| 747793 | 157370375171000275 | TOP_UP | CASH | 2019-11-14 03:55:51.480417 | LOCAL_ACCOUNT | |10000 | 11000 | 275 | 12 | 6 | |
+--------+--------------------+--------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+
2 rows in set (0.00 sec)
mysql> <strong>explain select * from account_transaction where method="CASH" and trader_staff_id=275</strong><strong>;</strong>
+----+-------------+---------------------+------------+------+---------------+------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key| key_len | ref | rows | filtered | Extra |
+----+-------------+---------------------+------------+------+---------------+------+---------+-------------+------+----------+-------+
|1 | SIMPLE | account_transaction | NULL | ref| mto | mto| 66 | const,const | 2 | 100.00 | NULL|
+----+-------------+---------------------+------------+------+---------------+------+---------+-------------+------+----------+-------+
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查询
mysql> <strong>explain select * from account_transaction where method="CASH" and operator_staff_id=12</strong><strong>;</strong>
+----+-------------+---------------------+------------+------+---------------+------+---------+-------+-------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key| key_len | ref | rows| filtered | Extra |
+----+-------------+---------------------+------------+------+---------------+------+---------+-------+-------+----------+-----------------------+
|1 | SIMPLE | account_transaction | NULL | ref| mto | mto| 62 | const | 39916 | 10.00 | Using index condition |
+----+-------------+---------------------+------------+------+---------------+------+---------+-------+-------+----------+-----------------------+
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查询
mysql> <strong>select * from account_transaction where trader_staff_id=275 and operator_staff_id=12;
</strong>+---------+--------------------+---------------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------------------------+
| id | trade_no | type | method | time | payment | out_trade_no | amount | balance | trader_staff_id | operator_staff_id | device_id | remark |
+---------+--------------------+---------------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------------------------+
| 24 | 156384428075000275 | TOP_UP | CASH | 2019-07-23 01:11:20.221977 | LOCAL_ACCOUNT | |10000 | 10000 | 275 | 12 | 6 | |
|747793 | 157370375171000275 | TOP_UP | CASH | 2019-11-14 03:55:51.480417 | LOCAL_ACCOUNT | |10000 | 11000 | 275 | 12 | 6 | |
| 1993075 | 160454902688000275 | REFUND | WEB | 2020-11-05 04:03:46.980204 | LOCAL_ACCOUNT | |-3200 | 0 | 275 | 12 | 43 | |
| 3764809 | 162122330931000275 | TOP_UP | CHEQUE | 2021-05-17 03:48:29.748154 | LOCAL_ACCOUNT | |10000 | 10000 | 275 | 12 | 6 | |
| 4791205 | 162856536047000275 | CONSUME_LUNCH | WEB | 2021-08-04 04:46:17.000000 | LOCAL_ACCOUNT | | 200 | 9400 | 275 | 12 | 35 | 管理后台补充消费 |
| 4791211 | 162856542884000275 | CONSUME_LUNCH | WEB | 2021-08-05 04:46:17.000000 | LOCAL_ACCOUNT | | 200 | 9200 | 275 | 12 | 35 | 管理后台补充消费 |
| 4791217 | 162856543723000275 | CONSUME_LUNCH | WEB | 2021-08-06 04:46:17.000000 | LOCAL_ACCOUNT | | 200 | 9000 | 275 | 12 | 35 | 管理后台补充消费 |
+---------+--------------------+---------------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------------------------+
11 rows in set (4.58 sec)
mysql> <strong>explain select * from account_transaction where trader_staff_id=275 and operator_staff_id=12;
</strong>+----+-------------+---------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | <strong>key| key_len</strong> | ref| rows | filtered | Extra |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|1 | SIMPLE | account_transaction | NULL | ALL| NULL | <strong>NULL | NULL</strong>| NULL | 2249115 | 1.00 | Using where |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec) 说明1：查询时间为4.58秒，比之前用时多了很多
说明2：通过explain执行计划，可以发现该查询语句没有使用索引，是因为不符合最左前缀原则，即索引的最左边的method也就是第一索引列，这一列必须要使用，是触发组合索引的前缀。
案例5：包含最左前缀，但是最左前缀不在最前面
mysql> <strong>select * from account_transaction where trader_staff_id=275 and operator_staff_id=12 and method=</strong><strong>"CASH";</strong>
+--------+--------------------+--------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+
| id | trade_no | type | method | time | payment | out_trade_no | amount | balance | trader_staff_id | operator_staff_id | device_id | remark |
+--------+--------------------+--------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+
| 24 | 156384428075000275 | TOP_UP | CASH | 2019-07-23 01:11:20.221977 | LOCAL_ACCOUNT | |10000 | 10000 | 275 | 12 | 6 | |
| 747793 | 157370375171000275 | TOP_UP | CASH | 2019-11-14 03:55:51.480417 | LOCAL_ACCOUNT | |10000 | 11000 | 275 | 12 | 6 | |
+--------+--------------------+--------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+
2 rows in set (0.00 sec)
mysql> <strong>explain select * from account_transaction where trader_staff_id=275 and operator_staff_id=12 and method=</strong><strong>"CASH";</strong>
+----+-------------+---------------------+------------+------+---------------+------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key| key_len | ref | rows | filtered | Extra |
+----+-------------+---------------------+------------+------+---------------+------+---------+-------------------+------+----------+-------+
|1 | SIMPLE | account_transaction | NULL | ref| mto | mto| 70 | const,const,const | 2 | 100.00 | NULL|
+----+-------------+---------------------+------------+------+---------------+------+---------+-------------------+------+----------+-------+
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) 说明2，在搜索条件中的trader_staff_id是一个范围查询使用的">"，因为trader_staff_id在创建索引的时候在第2顺序，所以该查询语句中，处于第三个字段的operator_staff_id字段就失效了，所以key_len是66
mysql> <strong>show index from</strong><strong> account_transaction;</strong>
+---------------------+------------+-------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------------------+------------+-------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| account_transaction | 0 | PRIMARY | 1 | id | A | 2067077 | NULL | NULL | | BTREE | | | YES | NULL |
| account_transaction | 1 | trade_index | 1 | trade_no | A | 2249115 | NULL | NULL | | BTREE | | | YES | NULL |
| account_transaction | 1 | mto | 1 | method | A | 3 | NULL | NULL | | BTREE | | | YES | NULL |
| account_transaction | 1 | mto | 2 | trader_staff_id | A | 31046 | NULL | NULL | | BTREE | | | YES | NULL |
| account_transaction | 1 | mto | 3 | operator_staff_id | A | 15847 | NULL | NULL | | BTREE | | | YES | NULL |
+---------------------+------------+-------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
说明3：如果在不影响业务的时候，最好使用">="或者"
页:
[1]