一、最左前缀法则
如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列，如果跳跃某一列，索引将部分失效（后面的字段索引失效）
示例1：account_transaction表中创建一个联合索引，使用method字段 trader_staff_id字段 operator_staff_id字段三个字段当做联合索引
代码语言：javascript复制mysql> create index mto on account_transaction(method, trader_staff_id, operator_staff_id);
Query OK, 0 rows affected (5.29 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from account_transaction;
--------------------- ------------ ------------- -------------- ------------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- --------- ------------
| 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 |
--------------------- ------------ ------------- -------------- ------------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- --------- ------------
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：同时按顺序使用三个字段查询一条数据
代码语言：javascript复制mysql> select * from account_transaction where method="CASH" and trader_staff_id=275 and operator_staff_id=12;
-------- -------------------- -------- -------- ---------------------------- --------------- -------------- -------- --------- ----------------- ------------------- ----------- --------
| 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 | 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：使用method,trader_staff_id,operator_staff_id三个字段作为查询条件，查询时间0.00秒以内
说明2：使用explain关键字查询执行计划，该查询使用的key是mto 即刚创建的联合索引，key_len是70长度。记住这个长度，我们在后面还会用到。
案例2：使用 method 和 trader_staff_id 两个字段作为查询条件
代码语言：javascript复制mysql> select * from account_transaction where method="CASH" and trader_staff_id=275;
-------- -------------------- -------- -------- ---------------------------- --------------- -------------- -------- --------- ----------------- ------------------- ----------- --------
| 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;
---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------------- ------ ---------- -------
| 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查询
代码语言：javascript复制mysql> explain select * from account_transaction where method="CASH" and operator_staff_id=12;
---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------- ------- ---------- -----------------------
| 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查询
代码语言：javascript复制mysql> select * from account_transaction where trader_staff_id=275 and operator_staff_id=12;
--------- -------------------- --------------- -------- ---------------------------- --------------- -------------- -------- --------- ----------------- ------------------- ----------- --------------------------
| 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> explain select * from account_transaction where trader_staff_id=275 and operator_staff_id=12;
---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ --------- ---------- -------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ --------- ---------- -------------
| 1 | SIMPLE | account_transaction | NULL | ALL | NULL | NULL | NULL | NULL | 2249115 | 1.00 | Using where |
---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ --------- ---------- -------------
1 row in set, 1 warning (0.00 sec)说明1：查询时间为4.58秒，比之前用时多了很多
说明2：通过explain执行计划，可以发现该查询语句没有使用索引，是因为不符合最左前缀原则，即索引的最左边的method也就是第一索引列，这一列必须要使用，是触发组合索引的前缀。
案例5：包含最左前缀，但是最左前缀不在最前面
代码语言：javascript复制mysql> select * from account_transaction where trader_staff_id=275 and operator_staff_id=12 and method="CASH";
-------- -------------------- -------- -------- ---------------------------- --------------- -------------- -------- --------- ----------------- ------------------- ----------- --------
| 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 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 | ref | mto | mto | 70 | const,const,const | 2 | 100.00 | NULL |
---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------------------- ------ ---------- -------
1 row in set, 1 warning (0.00 sec)说明1：通过explain发现依然触发了mto索引，虽然最左前缀没有在最左边，但是只要出现了就可以，复合最左前缀法则。
二、范围查询
联合查询索引中，出现范围查询(>,<),则在范围查询字段在索引中靠后的索引字段都会失效
案例1：查询method="CASH" and trader_staff_id<257 and operator_staff_id=12;
代码语言：javascript复制mysql> show index from account_transaction;
--------------------- ------------ ------------- -------------- ------------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- --------- ------------
| 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 |
--------------------- ------------ ------------- -------------- ------------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- --------- ------------ 说明1：mto索引字段中method的索引顺序是1，trader_staff_id的索引顺序是2，operator_staff_idde的索引字段是3
代码语言：javascript复制mysql> 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
代码语言：javascript复制mysql> 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 | 70 | NULL | 37718 | 10.00 | Using index condition; Using MRR |
---- ------------- --------------------- ------------ ------- --------------- ------ --------- ------ ------- ---------- ----------------------------------
1 row in set, 1 warning (0.00 sec)说明3：如果在不影响业务的时候，最好使用">="或者"<="，这样就可以保证索引的正常使用
三、索引列运算
案例1：不要再索引列上进行运算操作，索引将失效
代码语言：javascript复制mysql> select * from account_transaction where trade_no = "156384395941000265";
---- -------------------- -------- -------- ---------------------------- --------------- -------------- -------- --------- ----------------- ------------------- ----------- --------
| id | trade_no | type | method | time | payment | out_trade_no | amount | balance | trader_staff_id | operator_staff_id | device_id | remark |
---- -------------------- -------- -------- ---------------------------- --------------- -------------- -------- --------- ----------------- ------------------- ----------- --------
| 16 | 156384395941000265 | TOP_UP | CASH | 2019-07-23 01:05:59.102933 | LOCAL_ACCOUNT | | 10000 | 10000 | 265 | 12 | 6 | |
---- -------------------- -------- -------- ---------------------------- --------------- -------------- -------- --------- ----------------- ------------------- ----------- --------
1 row in set (0.00 sec)
mysql> explain select * from account_transaction where trade_no = "156384395941000265";
---- ------------- --------------------- ------------ ------ --------------- ------------- --------- ------- ------ ---------- -------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- --------------------- ------------ ------ --------------- ------------- --------- ------- ------ ---------- -------
| 1 | SIMPLE | account_transaction | NULL | ref | trade_index | trade_index | 62 | const | 1 | 100.00 | NULL |
---- ------------- --------------------- ------------ ------ --------------- ------------- --------- ------- ------ ---------- -------
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from account_transaction where substring(trade_no, 16,3) = "265";
---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ --------- ---------- -------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ --------- ---------- -------------
| 1 | SIMPLE | account_transaction | NULL | ALL | NULL | NULL | NULL | NULL | 2249115 | 100.00 | Using where |
---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ --------- ---------- -------------
1 row in set, 1 warning (0.01 sec)说明1：通过 trade_no 直接查询的时候，会触发trade_index索引
说明2：先对 trade_no 字段做字符串截取，在查询的时候，则没有触发trader_index索引
四、字符串查询不加引号，索引失效
案例1：
代码语言：javascript复制mysql> select * from account_transaction where trade_no = "156384395941000265";
---- -------------------- -------- -------- ---------------------------- --------------- -------------- -------- --------- ----------------- ------------------- ----------- --------
| id | trade_no | type | method | time | payment | out_trade_no | amount | balance | trader_staff_id | operator_staff_id | device_id | remark |
---- -------------------- -------- -------- ---------------------------- --------------- -------------- -------- --------- ----------------- ------------------- ----------- --------
| 16 | 156384395941000265 | TOP_UP | CASH | 2019-07-23 01:05:59.102933 | LOCAL_ACCOUNT | | 10000 | 10000 | 265 | 12 | 6 | |
---- -------------------- -------- -------- ---------------------------- --------------- -------------- -------- --------- ----------------- ------------------- ----------- --------
1 row in set (0.00 sec)
mysql> explain select * from account_transaction where trade_no = "156384395941000265";
---- ------------- --------------------- ------------ ------ --------------- ------------- --------- ------- ------ ---------- -------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- --------------------- ------------ ------ --------------- ------------- --------- ------- ------ ---------- -------
| 1 | SIMPLE | account_transaction | NULL | ref | trade_index | trade_index | 62 | const | 1 | 100.00 | NULL |
---- ------------- --------------------- ------------ ------ --------------- ------------- --------- ------- ------ ---------- -------
1 row in set, 1 warning (0.00 sec)
mysql> select * from account_transaction where trade_no = 156384395941000265;
---- -------------------- -------- -------- ---------------------------- --------------- -------------- -------- --------- ----------------- ------------------- ----------- --------
| id | trade_no | type | method | time | payment | out_trade_no | amount | balance | trader_staff_id | operator_staff_id | device_id | remark |
---- -------------------- -------- -------- ---------------------------- --------------- -------------- -------- --------- ----------------- ------------------- ----------- --------
| 16 | 156384395941000265 | TOP_UP | CASH | 2019-07-23 01:05:59.102933 | LOCAL_ACCOUNT | | 10000 | 10000 | 265 | 12 | 6 | |
---- -------------------- -------- -------- ---------------------------- --------------- -------------- -------- --------- ----------------- ------------------- ----------- --------
1 row in set (3.52 sec)
mysql> explain select * from account_transaction where trade_no = 156384395941000265;
---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ --------- ---------- -------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ --------- ---------- -------------
| 1 | SIMPLE | account_transaction | NULL | ALL | trade_index | NULL | NULL | NULL | 2249115 | 10.00 | Using where |
---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ --------- ---------- -------------
1 row in set, 3 warnings (0.01 sec)说明1：第一个查询使用了0.00秒以内，并且触发了trade_index索引。
说明2：第二个查询使用了3.52秒，没有触发索引，因为trade_no是字符串类型的，但是并没有加“”。
五、模糊查询
如果仅仅是尾部模糊匹配，索引不会失效，如果是头部模糊匹配，索引会失效
代码语言：javascript复制mysql> select * from account_transaction where trade_no like "15638439594%";
---- -------------------- -------- -------- ---------------------------- --------------- -------------- -------- --------- ----------------- ------------------- ----------- --------
| id | trade_no | type | method | time | payment | out_trade_no | amount | balance | trader_staff_id | operator_staff_id | device_id | remark |
---- -------------------- -------- -------- ---------------------------- --------------- -------------- -------- --------- ----------------- ------------------- ----------- --------
| 16 | 156384395941000265 | TOP_UP | CASH | 2019-07-23 01:05:59.102933 | LOCAL_ACCOUNT | | 10000 | 10000 | 265 | 12 | 6 | |
---- -------------------- -------- -------- ---------------------------- --------------- -------------- -------- --------- ----------------- ------------------- ----------- --------
1 row in set (0.00 sec)
mysql> explain select * from account_transaction where trade_no like "15638439594%";
---- ------------- --------------------- ------------ ------- --------------- ------------- --------- ------ ------ ---------- -----------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- --------------------- ------------ ------- --------------- ------------- --------- ------ ------ ---------- -----------------------
| 1 | SIMPLE | account_transaction | NULL | range | trade_index | trade_index | 62 | NULL | 1 | 100.00 | Using index condition |
---- ------------- --------------------- ------------ ------- --------------- ------------- --------- ------ ------ ---------- -----------------------
1 row in set, 1 warning (0.00 sec)说明1：使用 trade_no 字段，做后面数据的模糊查询，通过explain 执行计划分析，可以看出，执行了 trade_index 索引，并且执行时间在0.00秒以内
代码语言：javascript复制mysql> select * from account_transaction where trade_no like "�941000265";
---- -------------------- -------- -------- ---------------------------- --------------- -------------- -------- --------- ----------------- ------------------- ----------- --------
| id | trade_no | type | method | time | payment | out_trade_no | amount | balance | trader_staff_id | operator_staff_id | device_id | remark |
---- -------------------- -------- -------- ---------------------------- --------------- -------------- -------- --------- ----------------- ------------------- ----------- --------
| 16 | 156384395941000265 | TOP_UP | CASH | 2019-07-23 01:05:59.102933 | LOCAL_ACCOUNT | | 10000 | 10000 | 265 | 12 | 6 | |
---- -------------------- -------- -------- ---------------------------- --------------- -------------- -------- --------- ----------------- ------------------- ----------- --------
1 row in set (4.86 sec)
mysql> explain select * from account_transaction where trade_no like "�941000265";
---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ --------- ---------- -------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ --------- ---------- -------------
| 1 | SIMPLE | account_transaction | NULL | ALL | NULL | NULL | NULL | NULL | 2249115 | 11.11 | Using where |
---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ --------- ---------- -------------
1 row in set, 1 warning (0.00 sec)说明2：使用 trader_no 字段，做前面数据的模糊查询，通过 explain 执行计划分析，可以看出，并没有执行索引，索引执行时间长达4.86秒
六、or链接的条件
如果查询条件中用到了or,并且or连接的条件中有非索引字段，则在or连接的中的索引字段会失效
代码语言：javascript复制mysql> select * from account_transaction where id = 10 or amount=1;
---- -------------------- -------- -------- ---------------------------- --------------- -------------- -------- --------- ----------------- ------------------- ----------- --------
| id | trade_no | type | method | time | payment | out_trade_no | amount | balance | trader_staff_id | operator_staff_id | device_id | remark |
---- -------------------- -------- -------- ---------------------------- --------------- -------------- -------- --------- ----------------- ------------------- ----------- --------
| 10 | 156384373961000258 | TOP_UP | CASH | 2019-07-23 01:02:19.892943 | LOCAL_ACCOUNT | | 10000 | 10000 | 258 | 12 | 6 | |
---- -------------------- -------- -------- ---------------------------- --------------- -------------- -------- --------- ----------------- ------------------- ----------- --------
1 row in set (1.60 sec)
mysql> explain select * from account_transaction where id = 10 or amount=1;
---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ --------- ---------- -------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ --------- ---------- -------------
| 1 | SIMPLE | account_transaction | NULL | ALL | PRIMARY | NULL | NULL | NULL | 2249115 | 10.00 | Using where |
---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ --------- ---------- -------------
1 row in set, 1 warning (0.00 sec)说明1：用or分割开的条件，如果or在条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到
说明2：在 select 语句中 id 是主键索引，但是 amount 不是索引，并且出现在了 or 的条件中，通过 explain 执行计划分析，可以看出possible_keys可能使用到的索引是primary主键索引，但是实际key这一列却是NULL,说明 primary 主键索引失效，查询用时1.6秒
代码语言：javascript复制mysql> select * from account_transaction where id = 10 or trade_no="156384395941000265";
---- -------------------- -------- -------- ---------------------------- --------------- -------------- -------- --------- ----------------- ------------------- ----------- --------
| id | trade_no | type | method | time | payment | out_trade_no | amount | balance | trader_staff_id | operator_staff_id | device_id | remark |
---- -------------------- -------- -------- ---------------------------- --------------- -------------- -------- --------- ----------------- ------------------- ----------- --------
| 10 | 156384373961000258 | TOP_UP | CASH | 2019-07-23 01:02:19.892943 | LOCAL_ACCOUNT | | 10000 | 10000 | 258 | 12 | 6 | |
| 16 | 156384395941000265 | TOP_UP | CASH | 2019-07-23 01:05:59.102933 | LOCAL_ACCOUNT | | 10000 | 10000 | 265 | 12 | 6 | |
---- -------------------- -------- -------- ---------------------------- --------------- -------------- -------- --------- ----------------- ------------------- ----------- --------
2 rows in set (0.01 sec)
mysql> explain select * from account_transaction where id = 10 or trade_no="156384395941000265";
---- ------------- --------------------- ------------ ------------- --------------------- --------------------- --------- ------ ------ ---------- -----------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- --------------------- ------------ ------------- --------------------- --------------------- --------- ------ ------ ---------- -----------------------------------------------
| 1 | SIMPLE | account_transaction | NULL | index_merge | PRIMARY,trade_index | PRIMARY,trade_index | 4,62 | NULL | 2 | 100.00 | Using union(PRIMARY,trade_index); Using where |
---- ------------- --------------------- ------------ ------------- --------------------- --------------------- --------- ------ ------ ---------- -----------------------------------------------
1 row in set, 1 warning (0.00 sec)说明3：虽然 id 和 trade_no 都出现or连接的语句，但是这两个都是索引字段，仍然会触发索引的效果。只有or连接的字段中有非索引字段时才会无效
七、数据分布影响
如果mysql评估使用索引比全表更慢，则不使用索引
代码语言：javascript复制mysql> explain select * from account_transaction where id = 10;
---- ------------- --------------------- ------------ ------- --------------- --------- --------- ------- ------ ---------- -------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- --------------------- ------------ ------- --------------- --------- --------- ------- ------ ---------- -------
| 1 | SIMPLE | account_transaction | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
---- ------------- --------------------- ------------ ------- --------------- --------- --------- ------- ------ ---------- -------
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from account_transaction where id = 100000000;
---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- --------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- --------------------------------
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table |
---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- --------------------------------
1 row in set, 1 warning (0.00 sec)说明1：第一条select执行时使用了primary索引
说明2：第二条select执行时没有使用索引
说明3：因为 account_transaction 整张表就200万条数据，而现在让我搜索id=100000000的数据，数据库会认为还没有直接全表检索块，所以就放弃使用了索引
八、SQL提示
SQL提示：是优化数据的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的
案例1：trade_no字段有一个单独的索引，现在在对trade_no amonut做一个联合索引，看两个索引都满足的时候，会使用哪一个索引
代码语言：javascript复制mysql> show index from account_transaction;
--------------------- ------------ ------------- -------------- ------------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- --------- ------------
| 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 |
--------------------- ------------ ------------- -------------- ------------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- --------- ------------
5 rows in set (0.01 sec)
mysql> create index tm on account_transaction (trade_no, amount);
Query OK, 0 rows affected (15.70 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from account_transaction;
--------------------- ------------ ------------- -------------- ------------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- --------- ------------
| 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 |
| account_transaction | 1 | tm | 1 | trade_no | A | 2249115 | NULL | NULL | | BTREE | | | YES | NULL |
| account_transaction | 1 | tm | 2 | amount | A | 2249115 | NULL | NULL | | BTREE | | | YES | NULL |
--------------------- ------------ ------------- -------------- ------------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- --------- ------------
7 rows in set (0.01 sec)说明1：创建tm索引，包含了 trade_no 和 amount 两个字段。
代码语言：javascript复制mysql> explain select * from account_transaction where trade_no="156384395941000265";
---- ------------- --------------------- ------------ ------ ---------------- ------------- --------- ------- ------ ---------- -------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- --------------------- ------------ ------ ---------------- ------------- --------- ------- ------ ---------- -------
| 1 | SIMPLE | account_transaction | NULL | ref | trade_index,tm | trade_index | 62 | const | 1 | 100.00 | NULL |
---- ------------- --------------------- ------------ ------ ---------------- ------------- --------- ------- ------ ---------- -------
1 row in set, 1 warning (0.00 sec)说明2：在使用 trade_no 查询数据时，可能使用到的索引有 trade_index , tm 两个索引，最终SQL选择了 trade_index 索引
案例2： use index :建议数据库使用哪一个索引，如果你建议的效率不高，有可能不会被系统采纳
代码语言：javascript复制mysql> explain select * from account_transaction use index(tm) where trade_no="156384395941000265";
---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------- ------ ---------- -------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------- ------ ---------- -------
| 1 | SIMPLE | account_transaction | NULL | ref | tm | tm | 62 | const | 1 | 100.00 | NULL |
---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------- ------ ---------- -------
1 row in set, 1 warning (0.00 sec)说明1：很高兴SQL接收了我的建议
案例3：ignore index：告诉数据库不要使用哪一个索引
代码语言：javascript复制mysql> explain select * from account_transaction ignore index(tm) where trade_no="156384395941000265";
---- ------------- --------------------- ------------ ------ --------------- ------------- --------- ------- ------ ---------- -------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- --------------------- ------------ ------ --------------- ------------- --------- ------- ------ ---------- -------
| 1 | SIMPLE | account_transaction | NULL | ref | trade_index | trade_index | 62 | const | 1 | 100.00 | NULL |
---- ------------- --------------------- ------------ ------ --------------- ------------- --------- ------- ------ ---------- -------
1 row in set, 1 warning (0.00 sec)案例4：force index：告诉数据库必须要走哪一个索引
代码语言：javascript复制mysql> explain select * from account_transaction force index(tm) where trade_no="156384395941000265";
---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------- ------ ---------- -------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------- ------ ---------- -------
| 1 | SIMPLE | account_transaction | NULL | ref | tm | tm | 62 | const | 1 | 100.00 | NULL |
---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------- ------ ---------- -------
1 row in set, 1 warning (0.00 sec)九、覆盖索引
尽量使用覆盖索引，即查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到，这时应该尽量减少select *
代码语言：javascript复制mysql> 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 | 70 | NULL | 37718 | 10.00 | Using index condition; Using MRR |
---- ------------- --------------------- ------------ ------- --------------- ------ --------- ------ ------- ---------- ----------------------------------
1 row in set, 1 warning (0.01 sec)
mysql> explain select trader_staff_id, operator_staff_id, method 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 | 70 | NULL | 37718 | 10.00 | Using where; Using index |
---- ------------- --------------------- ------------ ------- --------------- ------ --------- ------ ------- ---------- -------------------------- 说明1：mto是一个组合索引，索引字段1是mehtod,索引字段2是trader_staff_id,索引字段3是operator_staff_id。
说明2：在使用select * 作为查询条件是，Extra字段显示是Using index condition; Using MRR,即查找使用了索引，但是需要回表查询索引以外的字段数据。
说明3：在使用的搜索字段整好是组合索引的三个字段的时候，Extra字段显示为：Using where; Using index，即查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表 查询数据
十、前缀索引
当字段类型为字符串（varchar,text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO,影响查询效率，此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。
语法：
代码语言：javascript复制create index 索引名 on table_name(column(n));说明1：这里和创建索引的语法几乎一致，就是在column处截取了前n位当做索引
前缀长度：可以由业务和索引的确定性来决定。
确定性：是指不重复的索引值和数据表的记录总数的比值，索引确定性越高则查询效率越高。
唯一索引的确定性是1，这是最好的索引确定性，性能也是最好的
计算参考公式如下：
代码语言：javascript复制select count(distinct 索引字段)/count(*) from 表名；或者
代码语言：javascript复制select count(distinct substring(索引字段，n,m))/count(*) from 表名；说明1：n一般等于1代表从第1个位置开始截取
说明2：m代表截取几位，可视业务而定。
案例1：
代码语言：javascript复制mysql> desc account_transaction;
------------------- ------------- ------ ----- --------- ----------------
| Field | Type | Null | Key | Default | Extra |
------------------- ------------- ------ ----- --------- ----------------
| id | int | NO | PRI | NULL | auto_increment |
| trade_no | varchar(20) | NO | MUL | NULL | |
| type | varchar(20) | NO | | NULL | |
| method | varchar(20) | NO | MUL | NULL | |
| time | datetime(6) | NO | | NULL | |
| payment | varchar(20) | NO | | NULL | |
| out_trade_no | varchar(20) | NO | | NULL | |
| amount | int | NO | | NULL | |
| balance | int | NO | | NULL | |
| trader_staff_id | int | NO | | NULL | |
| operator_staff_id | int | NO | | NULL | |
| device_id | varchar(10) | NO | | NULL | |
| remark | varchar(50) | NO | | NULL | |
------------------- ------------- ------ ----- --------- ----------------
13 rows in set (0.01 sec)说明1：通过表结构可以看到 trade_no 长度为20，也就是说，我在创建 trade_index 索引的时候，索引里面记录的每一条数据都是占20个字符
代码语言：javascript复制mysql> select count(distinct trade_no)/count(*) from account_transaction;
-----------------------------------
| count(distinct trade_no)/count(*) |
-----------------------------------
| 1.0000 |
----------------------------------- 说明2：通过查询 trade_no 不重复值与数据量的总值比为1，说明trade_no数据都是不重复的数据
代码语言：javascript复制mysql> select count(distinct substring(trade_no,1,18))/count(*) from account_transaction;
---------------------------------------------------
| count(distinct substring(trade_no,1,18))/count(*) |
---------------------------------------------------
| 1.0000 |
--------------------------------------------------- 说明3：通过查询 trade_no 的前18位字符来和全部数据比值也为1，即说明trade_no数据的前18位也都是不重复，那么我在建立索引的时候只使用前18位就可以，这样就比使用整个trader_no字段，每个数据节省2个字符的空间。
代码语言：javascript复制mysql> select count(distinct substring(trade_no,1,17))/count(*) from account_transaction;
---------------------------------------------------
| count(distinct substring(trade_no,1,17))/count(*) |
---------------------------------------------------
| 0.9994 |
---------------------------------------------------
1 row in set (4.80 sec)说明4：当我们截取前17个字符作为索引的时候，不重复率占总比值为0.994，也就是说会有少量的重复数据量，这是我们就可以选取前18位作为前缀索引
代码语言：javascript复制mysql> create index idx_trade_no_18 on account_transaction(trade_no(18));
Query OK, 0 rows affected (22.06 sec)
Records: 0 Duplicates: 0 Warnings: 0说明5：在 account_transaction 表中创建名为 idx_trade_no_18 的索引，采用的是 trade_no 前18位。
代码语言：javascript复制mysql> show index from account_transaction;
--------------------- ------------ ----------------- -------------- ------------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- --------- ------------
| 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 |
| account_transaction | 1 | tm | 1 | trade_no | A | 2249115 | NULL | NULL | | BTREE | | | YES | NULL |
| account_transaction | 1 | tm | 2 | amount | A | 2249115 | NULL | NULL | | BTREE | | | YES | NULL |
| account_transaction | 1 | idx_trade_no_18 | 1 | trade_no | A | 2249115 | 18 | NULL | | BTREE | | | YES | NULL |
--------------------- ------------ ----------------- -------------- ------------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- --------- ------------
8 rows in set (0.01 sec)
mysql> explain select * from account_transaction use index(idx_trade_no_18) where trade_no="156384395941000265";
---- ------------- --------------------- ------------ ------ ----------------- ----------------- --------- ------- ------ ---------- -------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- --------------------- ------------ ------ ----------------- ----------------- --------- ------- ------ ---------- -------------
| 1 | SIMPLE | account_transaction | NULL | ref | idx_trade_no_18 | idx_trade_no_18 | 56 | const | 1 | 100.00 | Using where |
---- ------------- --------------------- ------------ ------ ----------------- ----------------- --------- ------- ------ ---------- -------------
1 row in set, 1 warning (0.00 sec)说明6：使用idx_trade_no_18索引
十一、单列索引与联合索引
单列索引：即一个索引包含单个列
联合索引：即一个索引包含了多个列
在业务场景下，如果存在多个查询条件，考虑针对查询字段建立索引时，建议建立联合索引，而非单列索引
案例1：我们分别给trade_no和amout创建单列索引，然后再创建一个这两个字段的联合索引，比较系统会默认使用哪一个索引。
因为 trade_no 的单列索引和联合索引已经创建好了，这里只需要在创建一个 amount 的单列索引即可
代码语言：javascript复制mysql> show index from account_transaction;
--------------------- ------------ ----------------- -------------- ------------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- --------- ------------
| 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 |
| account_transaction | 1 | tm | 1 | trade_no | A | 2249115 | NULL | NULL | | BTREE | | | YES | NULL |
| account_transaction | 1 | tm | 2 | amount | A | 2249115 | NULL | NULL | | BTREE | | | YES | NULL |
| account_transaction | 1 | idx_trade_no_18 | 1 | trade_no | A | 2249115 | 18 | NULL | | BTREE | | | YES | NULL |
| account_transaction | 1 | idx_amount | 1 | amount | A | 174 | NULL | NULL | | BTREE | | | YES | NULL |
--------------------- ------------ ----------------- -------------- ------------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- --------- ------------
9 rows in set (0.00 sec)根据trade_no和amount两个字段查询
代码语言：javascript复制mysql> select trade_no, amount from account_transaction where trade_no="156384395941000265" and amount=10000;
-------------------- --------
| trade_no | amount |
-------------------- --------
| 156384395941000265 | 10000 |
-------------------- --------
1 row in set (0.00 sec)
mysql> explain select trade_no, amount from account_transaction where trade_no="156384395941000265" and amount=10000;
---- ------------- --------------------- ------------ ------ ------------------------------------------- ------------- --------- ------- ------ ---------- -------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- --------------------- ------------ ------ ------------------------------------------- ------------- --------- ------- ------ ---------- -------------
| 1 | SIMPLE | account_transaction | NULL | ref | trade_index,tm,idx_trade_no_18,idx_amount | trade_index | 62 | const | 1 | 5.00 | Using where |
---- ------------- --------------------- ------------ ------ ------------------------------------------- ------------- --------- ------- ------ ---------- -------------
1 row in set, 1 warning (0.00 sec)说明1：根据 explain 执行计划语句显示，可以使用的索引有四个，但是只是用trade_index，这说明amount这个字段搜索是肯定要回表查询，这样就进行了二次查询，性能不高了
代码语言：javascript复制mysql> explain select trade_no, amount from account_transaction use index(tm) where trade_no="156384395941000265" and amount=10000;
---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------------- ------ ---------- -------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------------- ------ ---------- -------------
| 1 | SIMPLE | account_transaction | NULL | ref | tm | tm | 66 | const,const | 1 | 100.00 | Using index |
---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------------- ------ ---------- -------------
1 row in set, 1 warning (0.00 sec)说明2：这个使用Extra显示Using index，就会使用索引，而不会进行回表二次查询
十二、索引的设计原则
- 针对于数据量较大，且查询比较频繁的表建立索引
- 针对于常作为查询条件(where)、排序（order by）、分组（group by）操作的字段建立索引
- 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度高，使用索引的效率越高
- 如果是字符串类型的字段，字段的长度较长，可以针对字段的特点，建立前缀索引
- 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省储存空间，避免回表，提高查询效率
- 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
- 如果索引列不能储存NULL值，请在创建表时使用NOT NULL约束它，当优化器知道每列是否包含NULL值时，它可以更好地确定那个索引最有效地用于查询。
