国产无码综合区,色欲AV无码国产永久播放,无码天堂亚洲国产AV,国产日韩欧美女同一区二区

<thead id="ubzs4"><font id="ubzs4"><ul id="ubzs4"></ul></font></thead>

Mysql高級4-索引的使用規(guī)則

2年前作者：Se7eN_HOU分類：Toy博客閱讀(14)違法舉報

這篇具有很好參考價值的文章主要介紹了Mysql高級4-索引的使用規(guī)則。希望對大家有所幫助。如果存在錯誤或未考慮完全的地方，請大家不吝賜教，您也可以點擊"舉報違法"按鈕提交疑問。

一、最左前綴法則

　　如果索引了多列（聯(lián)合索引），要遵守最左前綴法則。最左前綴法則指的是查詢從索引的最左列開始，并且不跳過索引中的列，如果跳躍某一列，索引將部分失效（后面的字段索引失效）

　　示例1：account_transaction表中創(chuàng)建一個聯(lián)合索引，使用method字段+trader_staff_id字段+operator_staff_id字段三個字段當(dāng)做聯(lián)合索引

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是一個聯(lián)合索引，里面包含了三個字段method,trader_staff_id,operator_staff_id三個字段。

　　說明2：method是第1索引字段，即也是最左索引，trader_staff_id 是第2索引， operator_staff_id 是第3索引，這個順序很重要！

?文章來源地址http://www.zghlxwxcb.cn/news/detail-609307.html

　　案例1：同時按順序使用三個字段查詢一條數(shù)據(jù)

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秒以內(nèi)

　　說明2：使用explain關(guān)鍵字查詢執(zhí)行計劃，該查詢使用的key是mto 即剛創(chuàng)建的聯(lián)合索引，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執(zhí)行計劃，可以查看使用的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有使用，所以依然觸發(fā)了mto索引。

　　說明2：key_len=62說明索引字段又變短了，那是因為從跳過的trader_staff_id字段，所以trader_staff_id及之后的索引字段就失效，案例2中的key_len是66，而現(xiàn)在又變成了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執(zhí)行計劃，可以發(fā)現(xiàn)該查詢語句沒有使用索引，是因為不符合最左前綴原則，即索引的最左邊的method也就是第一索引列，這一列必須要使用，是觸發(fā)組合索引的前綴。

?

　　案例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發(fā)現(xiàn)依然觸發(fā)了mto索引，雖然最左前綴沒有在最左邊，但是只要出現(xiàn)了就可以，復(fù)合最左前綴法則。

?

二、范圍查詢

　　聯(lián)合查詢索引中，出現(xiàn)范圍查詢(>,<),則在范圍查詢字段在索引中靠后的索引字段都會失效

　　案例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在創(chuàng)建索引的時候在第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：如果在不影響業(yè)務(wù)的時候，最好使用">="或者"<="，這樣就可以保證索引的正常使用? ?

?

三、索引列運算

　　案例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 直接查詢的時候，會觸發(fā)trade_index索引

　　說明2：先對 trade_no 字段做字符串截取，在查詢的時候，則沒有觸發(fā)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秒以內(nèi)，并且觸發(fā)了trade_index索引。

　　說明2：第二個查詢使用了3.52秒，沒有觸發(fā)索引，因為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 字段，做后面數(shù)據(jù)的模糊查詢，通過explain 執(zhí)行計劃分析，可以看出，執(zhí)行了 trade_index 索引，并且執(zhí)行時間在0.00秒以內(nèi)

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 字段，做前面數(shù)據(jù)的模糊查詢，通過 explain 執(zhí)行計劃分析，可以看出，并沒有執(zhí)行索引，索引執(zhí)行時間長達4.86秒

?

六、or鏈接的條件

　　如果查詢條件中用到了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 不是索引，并且出現(xiàn)在了 or 的條件中，通過 explain 執(zhí)行計劃分析，可以看出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 都出現(xiàn)or連接的語句，但是這兩個都是索引字段，仍然會觸發(fā)索引的效果。只有or連接的字段中有非索引字段時才會無效

?

七、數(shù)據(jù)分布影響

　　如果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執(zhí)行時使用了primary索引

　　說明2：第二條select執(zhí)行時沒有使用索引

　　說明3：因為 account_transaction 整張表就200萬條數(shù)據(jù)，而現(xiàn)在讓我搜索id=100000000的數(shù)據(jù)，數(shù)據(jù)庫會認為還沒有直接全表檢索塊，所以就放棄使用了索引

?

八、SQL提示

　　SQL提示：是優(yōu)化數(shù)據(jù)的一個重要手段，簡單來說，就是在SQL語句中加入一些人為的提示來達到優(yōu)化操作的目的

　　案例1：trade_no字段有一個單獨的索引，現(xiàn)在在對trade_no+amonut做一個聯(lián)合索引，看兩個索引都滿足的時候，會使用哪一個索引

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：創(chuàng)建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 查詢數(shù)據(jù)時，可能使用到的索引有 trade_index , tm 兩個索引，最終SQL選擇了 trade_index 索引

?

　　案例2：?use index :建議數(shù)據(jù)庫使用哪一個索引，如果你建議的效率不高，有可能不會被系統(tǒng)采納

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：告訴數(shù)據(jù)庫不要使用哪一個索引

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：告訴數(shù)據(jù)庫必須要走哪一個索引

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)

?

?

九、覆蓋索引

　　盡量使用覆蓋索引，即查詢使用了索引，并且需要返回的列，在該索引中已經(jīng)全部能夠找到，這時應(yīng)該盡量減少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,即查找使用了索引，但是需要回表查詢索引以外的字段數(shù)據(jù)。

　　說明3：在使用的搜索字段整好是組合索引的三個字段的時候，Extra字段顯示為：Using where; Using index，即查找使用了索引，但是需要的數(shù)據(jù)都在索引列中能找到，所以不需要回表查詢數(shù)據(jù)

　　

十、前綴索引

　　當(dāng)字段類型為字符串（varchar,text等）時，有時候需要索引很長的字符串，這會讓索引變得很大，查詢時，浪費大量的磁盤IO,影響查詢效率，此時可以只將字符串的一部分前綴，建立索引，這樣可以大大節(jié)約索引空間，從而提高索引效率。

　　語法：

create index 索引名 on table_name(column(n));

　　說明1：這里和創(chuàng)建索引的語法幾乎一致，就是在column處截取了前n位當(dāng)做索引

　　前綴長度：可以由業(yè)務(wù)和索引的確定性來決定。

　　確定性：是指不重復(fù)的索引值和數(shù)據(jù)表的記錄總數(shù)的比值，索引確定性越高則查詢效率越高。

　　唯一索引的確定性是1，這是最好的索引確定性，性能也是最好的

　　計算參考公式如下：

select count(distinct 索引字段)/count(*) from 表名；

　　或者

select count(distinct substring(索引字段，n,m))/count(*) from 表名；

　　說明1：n一般等于1代表從第1個位置開始截取

　　說明2：m代表截取幾位，可視業(yè)務(wù)而定。

　　案例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：通過表結(jié)構(gòu)可以看到 trade_no 長度為20，也就是說，我在創(chuàng)建 trade_index 索引的時候，索引里面記錄的每一條數(shù)據(jù)都是占20個字符

mysql> select count(distinct trade_no)/count(*) from account_transaction;
+-----------------------------------+
| count(distinct trade_no)/count(*) |
+-----------------------------------+
|                            1.0000 |
+-----------------------------------+

　　說明2：通過查詢 trade_no 不重復(fù)值與數(shù)據(jù)量的總值比為1，說明trade_no數(shù)據(jù)都是不重復(fù)的數(shù)據(jù)　　　　

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位字符來和全部數(shù)據(jù)比值也為1，即說明trade_no數(shù)據(jù)的前18位也都是不重復(fù)，那么我在建立索引的時候只使用前18位就可以，這樣就比使用整個trader_no字段，每個數(shù)據(jù)節(jié)省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：當(dāng)我們截取前17個字符作為索引的時候，不重復(fù)率占總比值為0.994，也就是說會有少量的重復(fù)數(shù)據(jù)量，這是我們就可以選取前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 表中創(chuàng)建名為 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索引

　　

十一、單列索引與聯(lián)合索引

　　單列索引：即一個索引包含單個列

　　聯(lián)合索引：即一個索引包含了多個列

　　在業(yè)務(wù)場景下，如果存在多個查詢條件，考慮針對查詢字段建立索引時，建議建立聯(lián)合索引，而非單列索引

　　案例1：我們分別給trade_no和amout創(chuàng)建單列索引，然后再創(chuàng)建一個這兩個字段的聯(lián)合索引，比較系統(tǒng)會默認使用哪一個索引。

　　因為 trade_no 的單列索引和聯(lián)合索引已經(jīng)創(chuàng)建好了，這里只需要在創(chuàng)建一個 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)

　　根據(jù)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：根據(jù) explain 執(zhí)行計劃語句顯示，可以使用的索引有四個，但是只是用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，就會使用索引，而不會進行回表二次查詢

?

十二、索引的設(shè)計原則

針對于數(shù)據(jù)量較大，且查詢比較頻繁的表建立索引
針對于常作為查詢條件(where)、排序（order by）、分組（group by）操作的字段建立索引
盡量選擇區(qū)分度高的列作為索引，盡量建立唯一索引，區(qū)分度高，使用索引的效率越高
如果是字符串類型的字段，字段的長度較長，可以針對字段的特點，建立前綴索引
盡量使用聯(lián)合索引，減少單列索引，查詢時，聯(lián)合索引很多時候可以覆蓋索引，節(jié)省儲存空間，避免回表，提高查詢效率
要控制索引的數(shù)量，索引并不是多多益善，索引越多，維護索引結(jié)構(gòu)的代價也就越大，會影響增刪改的效率。
如果索引列不能儲存NULL值，請在創(chuàng)建表時使用NOT NULL約束它，當(dāng)優(yōu)化器知道每列是否包含NULL值時，它可以更好地確定那個索引最有效地用于查詢。

?

?

到了這里，關(guān)于Mysql高級4-索引的使用規(guī)則的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！

本文來自互聯(lián)網(wǎng)用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務(wù)，不擁有所有權(quán)，不承擔(dān)相關(guān)法律責(zé)任。如若轉(zhuǎn)載，請注明出處：如若內(nèi)容造成侵權(quán)/違法違規(guī)/事實不符，請點擊違法舉報進行投訴反饋，一經(jīng)查實，立即刪除！

分享到：

領(lǐng)支付寶紅包贊助服務(wù)器費用

MySQL數(shù)據(jù)庫進階第二篇（索引，SQL性能分析，使用規(guī)則）
本篇博客深入詳細地介紹了數(shù)據(jù)庫索引的概念和重要性。內(nèi)容包含：索引的概念和目標、索引的優(yōu)點與缺點。此外，博客還深入解析了三種主要的索引結(jié)構(gòu)：B-Tree、B+Tree和Hash，提供了詳細的結(jié)構(gòu)解析和優(yōu)化方法，并通過插圖進一步增強了理解。博客的部分內(nèi)容專注于對B-Tr
2024年02月21日
瀏覽(114)
聯(lián)合索引，最左匹配，范圍查詢
定義聯(lián)合索引是MySQL中常用的索引類型之一，它是由多個列組合而成的索引。聯(lián)合索引可以幫助優(yōu)化查詢，提高查詢效率，尤其是在多個列同時參與查詢時。最左匹配是指在聯(lián)合索引中，如果查詢條件中只涉及到聯(lián)合索引中的最左側(cè)列，那么可以利用該聯(lián)合索引進行快速匹
2023年04月09日
瀏覽(27)
索引：索引知識重復(fù)習(xí)，什么是索引、索引的類型、建立索引及【最左匹配原則】、Explain查看sql的執(zhí)行計劃
開干在關(guān)系數(shù)據(jù)庫中，索引是一種單獨的、物理的對數(shù)據(jù)庫表中一列或多列的值進行排序的一種存儲結(jié)構(gòu)，它是某個表中一列或若干列值的集合和相應(yīng)的指向表中物理標識這些值的數(shù)據(jù)頁的邏輯指針清單。索引的作用相當(dāng)于圖書的目錄，可以根據(jù)目錄中的頁碼快速找到所需的
2023年04月09日
瀏覽(26)
【MySQL高級】——InnoDB索引&MyISAM索引
??MySQL官方對索引的定義為：索引（Index）是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。索引的本質(zhì)：索引是數(shù)據(jù)結(jié)構(gòu)。你可以簡單理解為“排好序的快速查找數(shù)據(jù)結(jié)構(gòu)”，滿足特定查找算法。這些數(shù)據(jù)結(jié)構(gòu)以某種方式指向數(shù)據(jù)，這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)的基礎(chǔ)上實現(xiàn) 高級查找
2023年04月27日
瀏覽(20)
一篇搞定MySQL索引長度（key_len）計算規(guī)則
MySQL索引長度（key_len）計算 ?計算規(guī)則索引字段：沒有設(shè)置 NOT NULL，則需要加 1 個字節(jié)。定長字段： tinyint 占 1 個字節(jié)、 int 占 4 個字節(jié)、 bitint 占 8 個字節(jié)、 date 占 3 個字節(jié)、 datetime 占 5? 個字節(jié)、 char(n) 占 n 個字節(jié)。變長字段： varchar (n) 占 n 個字符 + 2 個字節(jié) 。注意（
2024年02月07日
瀏覽(14)
6大設(shè)計規(guī)則-迪米特法則
tip: 作為程序員一定學(xué)習(xí)編程之道，一定要對代碼的編寫有追求，不能實現(xiàn)就完事了。我們應(yīng)該讓自己寫的代碼更加優(yōu)雅，即使這會費時費力。相關(guān)規(guī)則：推薦：體系化學(xué)習(xí)Java（Java面試專題） 1.6大設(shè)計規(guī)則-接口隔離原則 2.6大設(shè)計原則-里氏替換原則 3.6大設(shè)計規(guī)則-開閉原則
2024年02月07日
瀏覽(13)
Mysql高級知識-------索引
mysql索引的創(chuàng)建，新增，刪除，查看 MySQL官方對索引的定義是：索引（Index）是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu) 。索引最形象的比喻就是圖書的目錄。注意只有在大量數(shù)據(jù)中查詢時索引才顯得有意義。在MySQL中索引是在存儲引擎層實現(xiàn)的``，而不是在服務(wù)器層實現(xiàn)的，
2023年04月26日
瀏覽(28)
MySQL高級篇——索引簡介
??作者簡介：數(shù)學(xué)與計算機科學(xué)學(xué)院學(xué)生、分享學(xué)習(xí)經(jīng)驗、生活、努力成為像代碼一樣有邏輯的人 ??個人主頁：阿芒的主頁 MySQL官方對索引定義：索引（Index）是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。索引的本質(zhì)：索引是數(shù)據(jù)結(jié)構(gòu)。索引的目的：提高查詢效率，可以類比
2023年04月08日
瀏覽(18)
索引的數(shù)據(jù)結(jié)構(gòu)(MySql高級)
索引是存儲引擎用于快速找到數(shù)據(jù)記錄的一種數(shù)據(jù)結(jié)構(gòu)，就好比一本教科書的目錄部分，通過目錄中找到對應(yīng)文章的頁碼，便可快速定位到需要的文章. MySQL中也是一樣的道理，進行數(shù)據(jù)查找時，首先查看查詢條件是否命中某條索引，符合則通過索引查找相關(guān)數(shù)據(jù)，如果不符合
2024年01月18日
瀏覽(21)
Mysql高級3-索引的結(jié)構(gòu)和分類
1.1 索引的介紹索引index：是幫助 Mysql? 高效獲取數(shù)據(jù)? 的? 有序的數(shù)據(jù)結(jié)構(gòu) ，在數(shù)據(jù)之外，數(shù)據(jù)庫系統(tǒng)維護著的滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式引用(指向)數(shù)據(jù)，這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實現(xiàn)高級查找算法，這種數(shù)據(jù)結(jié)構(gòu)就是索引 1.2 索引的優(yōu)缺
2024年02月15日
瀏覽(31)

<tbody id="mio1f"><font id="mio1f"><ul id="mio1f"></ul></font></tbody>

<nobr id="mio1f"></nobr>

<div id="mio1f"></div>

<button id="mio1f"><th id="mio1f"></th></button>