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

MySQL,使用,规则,高级 · 浏览次数 : 367

小编点评

**索引的设计原则** *数据量较大，且查询比较频繁的表建立索引针对于常作为查询条件(where)、排序（order by）、分组（group by）操作的字段建立索引尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度高，使用索引的效率越高如果是字符串类型的字段，字段的长度较长，可以针对字段的特点，建立前缀索引尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省储存空间，避免回表，提高查询效率要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。如果索引列不能储存NULL值，请在创建表时使用NOT NULL约束它，当优化器知道每列是否包含NULL值时，它可以更好地确定那个索引最有效地用于查询。

正文

一、最左前缀法则

　　如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列，如果跳跃某一列，索引将部分失效（后面的字段索引失效）

　　示例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连接的条件中有非索引字段，则在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提示：是优化数据的一个重要手段，简单来说，就是在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，就会使用索引，而不会进行回表二次查询

十二、索引的设计原则

针对于数据量较大，且查询比较频繁的表建立索引
针对于常作为查询条件(where)、排序（order by）、分组（group by）操作的字段建立索引
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度高，使用索引的效率越高
如果是字符串类型的字段，字段的长度较长，可以针对字段的特点，建立前缀索引
尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省储存空间，避免回表，提高查询效率
要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
如果索引列不能储存NULL值，请在创建表时使用NOT NULL约束它，当优化器知道每列是否包含NULL值时，它可以更好地确定那个索引最有效地用于查询。