如果索引了多列（聯合索引），要遵守最左字首法則。最左字首法則指的是查詢從索引的最左列開始，並且不跳過索引中的列，如果跳躍某一列，索引將部分失效（後面的欄位索引失效）
範例1：account_transaction表中建立一個聯合索引，使用method欄位+trader_staff_id欄位+operator_staff_id欄位三個欄位當做聯合索引
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：同時按順序使用三個欄位查詢一條資料
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 兩個欄位作為查詢條件
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查詢
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查詢
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：包含最左字首，但是最左字首不在最前面
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;
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
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
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：不要再索引列上進行運算操作，索引將失效
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：
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是字串型別的，但是並沒有加「」。
如果僅僅是尾部模糊匹配，索引不會失效，如果是頭部模糊匹配，索引會失效
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秒以內
mysql> select * from account_transaction where trade_no like "%95941000265";
+----+--------------------+--------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+
| 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 "%95941000265";
+----+-------------+---------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 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連線的中的索引欄位會失效
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秒
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評估使用索引比全表更慢，則不使用索引
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語句中加入一些人為的提示來達到優化操作的目的
案例1：trade_no欄位有一個單獨的索引，現在在對trade_no+amonut做一個聯合索引，看兩個索引都滿足的時候，會使用哪一個索引
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 兩個欄位。
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 :建議資料庫使用哪一個索引，如果你建議的效率不高，有可能不會被系統採納
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：告訴資料庫不要使用哪一個索引
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：告訴資料庫必須要走哪一個索引
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 *
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,影響查詢效率，此時可以只將字串的一部分字首，建立索引，這樣可以大大節約索引空間，從而提高索引效率。
語法：
create index 索引名 on table_name(column(n));
說明1：這裡和建立索引的語法幾乎一致，就是在column處擷取了前n位當做索引
字首長度：可以由業務和索引的確定性來決定。
確定性：是指不重複的索引值和資料表的記錄總數的比值，索引確定性越高則查詢效率越高。
唯一索引的確定性是1，這是最好的索引確定性，效能也是最好的
計算參考公式如下：
select count(distinct 索引欄位)/count(*) from 表名；
或者
select count(distinct substring(索引欄位，n,m))/count(*) from 表名；
說明1：n一般等於1代表從第1個位置開始擷取
說明2：m代表擷取幾位，可視業務而定。
案例1：
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個字元
mysql> select count(distinct trade_no)/count(*) from account_transaction;
+-----------------------------------+
| count(distinct trade_no)/count(*) |
+-----------------------------------+
| 1.0000 |
+-----------------------------------+
說明2：通過查詢 trade_no 不重複值與資料量的總值比為1，說明trade_no資料都是不重複的資料
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個字元的空間。
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位元作為字首索引
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位元。
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 的單列索引即可
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兩個欄位查詢
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這個欄位搜尋是肯定要回表查詢，這樣就進行了二次查詢，效能不高了
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，就會使用索引，而不會進行回表二次查詢