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

一、最左前缀法则

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

  示例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,就会使用索引,而不会进行回表二次查询

 

十二、索引的设计原则

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

热门相关:倾心之恋:总裁的妻子   富贵不能吟   神算大小姐   戏精老公今天作死没   美人图2008