Mysql高级4-索引的使用规则
2023-07-28 14:34:08来源：博客园
一、最左前缀法则
如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列，如果跳跃某一列，索引将部分失效（后面的字段索引失效）
(资料图片)
示例1：account_transaction表中创建一个联合索引，使用method字段+trader_staff_id字段+operator_staff_id字段三个字段当做联合索引
mysql> create index mto onaccount_transaction(method, trader_staff_id, operator_staff_id);Query OK, 0 rows affected (5.29 sec)Records: 0 Duplicates: 0 Warnings: 0mysql>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 fromaccount_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 fromaccount_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 onaccount_transaction (trade_no, amount);Query OK, 0 rows affected (15.70 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show index fromaccount_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>descaccount_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(*) fromaccount_transaction;+-----------------------------------+| count(distinct trade_no)/count(*) |+-----------------------------------+| 1.0000 |+-----------------------------------+
说明2：通过查询 trade_no 不重复值与数据量的总值比为1，说明trade_no数据都是不重复的数据
mysql> select count(distinct substring(trade_no,1,18))/count(*) fromaccount_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(*) fromaccount_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 fromaccount_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 fromaccount_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值时，它可以更好地确定那个索引最有效地用于查询。标签：
金辉集团回应“工资同销售回款率挂钩”：子虚乌有
中国中药：预计上半年净利润同比增长45%-55%
特斯拉锂供应商Piedmont Lithium在田纳西州工厂取得进展