mysql如何确认复合索引有使用到哪些索引字段的测试

一、基本信息
MariaDB [lots]> select @@version;
+---------------------+
| @@version           |
+---------------------+
| 10.0.20-MariaDB-log |
+---------------------+
1 row in set (0.00 sec)

mysql> show variables like 'optimizer_switch';\G;
| optimizer_switch |

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_conditi

on_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_mat

ch_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hash

ed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on |

mysql> show create table order_test\G
*************************** 1. row ***************************
       Table: order_test
Create Table: CREATE TABLE `order_test` (
  `ONWAY_ID` varchar(255) DEFAULT NULL,
  `CUSTOMERORDERNO` varchar(255) DEFAULT NULL,
  `TOORDERNO` varchar(255) DEFAULT NULL,
  `CONTRACTNO` varchar(255) DEFAULT NULL,
  `EXPRESSCODE` varchar(255) DEFAULT NULL,
  `EXPRESSNAME` varchar(255) DEFAULT NULL,
  `EXPRESSNO` varchar(255) DEFAULT NULL,
  `NODETYPE` varchar(255) DEFAULT NULL,
  `ACCEPTTIME` varchar(255) DEFAULT NULL,
  `ADDRESS` varchar(255) DEFAULT NULL,
  `NODESTATUS` varchar(255) DEFAULT NULL,
  `ACCEPTBY` varchar(255) DEFAULT NULL,
  `REMARK` varchar(255) DEFAULT NULL,
  `BRANCHTYPE` varchar(255) DEFAULT NULL,
  `BRANCHCODE` varchar(255) DEFAULT NULL,
  `BRANCHNAME` varchar(255) DEFAULT NULL,
  `PACKINGORDERN` varchar(255) DEFAULT NULL,
  `CONTACT` varchar(255) DEFAULT NULL,
  `TEL` varchar(255) DEFAULT NULL,
  KEY `order_test_ix1` (`CUSTOMERORDERNO`,`CONTRACTNO`,`TOORDERNO`,`EXPRESSNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

MariaDB [lots]> select count(*) from order_test;
+----------+
| count(*) |
+----------+
|   137591 |
+----------+

二、测试步骤(注意SQL中的信息作了屏蔽)

场景一：where c1=x and c2=x and c4>x and c3=x

MariaDB [lots]> explain select * from order_test where CUSTOMERORDERNO='aaaaaaaa' and CONTRACTNO ='aaaaaaaa' and EXPRESSNO>'aaaaaaaa' and
    -> TOORDERNO='bbbbb';
+------+-------------+------------+-------+----------------+----------------+---------+------+------+-------------------------------------------+
| id   | select_type | table      | type  | possible_keys  | key            | key_len | ref  | rows | Extra                                     |
+------+-------------+------------+-------+----------------+----------------+---------+------+------+-------------------------------------------+
|    1 | SIMPLE      | order_test | range | order_test_ix1 | order_test_ix1 | 3072    | NULL |    1 | Using index condition; Rowid-ordered scan |
+------+-------------+------------+-------+----------------+----------------+---------+------+------+-------------------------------------------+
1 row in set (0.00 sec)
 小结：上面执行计划的key_len显示3072，计算下是怎么得出来的，由于CUSTOMERORDERNO、CONTRACTNO、TOORDERNO和EXPRESSNO字段都varchar(255)，且允许为空，按如下规则：
1.索引字段中，对于变长数据类型，比如：varchar，除了是否为空的标记外，还需要有长度信息，需要占用2个字节；
2.同时还需要考虑表所使用的字符集，不同的字符集，gbk编码的为一个字符2个字节，utf8编码的一个字符3个字节;
所以算出：key_len的大小为：
255*3+255*3+255*3+255*3+1+2+1+2+1+2+1+2=3072
所以这个场景用到了索引的四个字段。

场景二：where c1=x and c2=x and c4=x order by c3 
MariaDB [lots]> explain select * from order_test where CUSTOMERORDERNO='aaaaaaaa' and CONTRACTNO ='aaaaaaaa' and
    -> EXPRESSNO='aaaaaaaa' order by TOORDERNO;

+------+-------------+------------+------+----------------+----------------+---------+-------------+------+------------------------------------+
| id   | select_type | table      | type | possible_keys  | key            | key_len | ref         | rows | Extra                              |
+------+-------------+------------+------+----------------+----------------+---------+-------------+------+------------------------------------+
|    1 | SIMPLE      | order_test | ref  | order_test_ix1 | order_test_ix1 | 1536    | const,const |    1 | Using index condition; Using where |
+------+-------------+------------+------+----------------+----------------+---------+-------------+------+------------------------------------+
1 row in set (0.00 sec)
 小结：上面执行计划的key_len显示1536，计算下是怎么得出来的，由于CUSTOMERORDERNO、CONTRACTNO字段都varchar(255)，且允许为空，按如下规则：
1.索引字段中，对于变长数据类型，比如：varchar，除了是否为空的标记外，还需要有长度信息，需要占用2个字节；
2.同时还需要考虑表所使用的字符集，不同的字符集，gbk编码的为一个字符2个字节，utf8编码的一个字符3个字节;
所以算出：key_len的大小为：
255*3+255*3+1+2+1+2=1536
所以这个场景用到了索引的CUSTOMERORDERNO、CONTRACTNO字段，符合符合索引的最左原则。

场景三：where c1=x and c4=x group b c3,c2

MariaDB [lots]> explain select * from order_test where CUSTOMERORDERNO='aaaaaaaa' and  EXPRESSNO='aaaaaaaa' group by TOORDERNO,CONTRACTNO;

+------+-------------+------------+------+----------------+----------------+---------+-------+------

+---------------------------------------------------------------------+
| id   | select_type | table      | type | possible_keys  | key            | key_len | ref   | rows | Extra                                                            

  |
+------+-------------+------------+------+----------------+----------------+---------+-------+------

+---------------------------------------------------------------------+
|    1 | SIMPLE      | order_test | ref  | order_test_ix1 | order_test_ix1 | 768     | const |    1 | Using index condition; Using where; Using temporary; Using

filesort |
+------+-------------+------------+------+----------------+----------------+---------+-------+------

+---------------------------------------------------------------------+
1 row in set (0.00 sec)
 小结：上面执行计划的key_len显示768，计算下是怎么得出来的，由于CUSTOMERORDERNO字段varchar(255)，且允许为空，按如下规则：
1.索引字段中，对于变长数据类型，比如：varchar，除了是否为空的标记外，还需要有长度信息，需要占用2个字节；
2.同时还需要考虑表所使用的字符集，不同的字符集，gbk编码的为一个字符2个字节，utf8编码的一个字符3个字节;
所以算出：key_len的大小为：
255*3+2+1+2=768
所以这个场景用到了索引的CUSTOMERORDERNO字段，符合符合索引的最左原则。

场景四:where c1=? And c5=? Order by c2,c3
MariaDB [lots]> explain select * from order_test where CUSTOMERORDERNO='aaaaaaaa' and NODETYPE='2'
    -> order by CONTRACTNO,TOORDERNO;
+------+-------------+------------+------+----------------+----------------+---------+-------+------+------------------------------------+
| id   | select_type | table      | type | possible_keys  | key            | key_len | ref   | rows | Extra                              |
+------+-------------+------------+------+----------------+----------------+---------+-------+------+------------------------------------+
|    1 | SIMPLE      | order_test | ref  | order_test_ix1 | order_test_ix1 | 768     | const |    1 | Using index condition; Using where |
+------+-------------+------------+------+----------------+----------------+---------+-------+------+------------------------------------+
1 row in set (0.00 sec)

 小结：上面执行计划的key_len显示768，计算下是怎么得出来的，由于CUSTOMERORDERNO字段varchar(255)，且允许为空，按如下规则：
1.索引字段中，对于变长数据类型，比如：varchar，除了是否为空的标记外，还需要有长度信息，需要占用2个字节；
2.同时还需要考虑表所使用的字符集，不同的字符集，gbk编码的为一个字符2个字节，utf8编码的一个字符3个字节;
所以算出：key_len的大小为：
255*3+2+1+2=768
所以这个场景用到了索引的CUSTOMERORDERNO字段，符合符合索引的最左原则

场景五：where c1=? And c2=? And c5=? Order by c2,c3

MariaDB [lots]> explain select * from order_test where CUSTOMERORDERNO='aaaaaaaa' and CONTRACTNO ='aaaaaaaa' and NODETYPE='2'
    -> order by CONTRACTNO,TOORDERNO;
+------+-------------+------------+------+----------------+----------------+---------+-------------+------+------------------------------------+
| id   | select_type | table      | type | possible_keys  | key            | key_len | ref         | rows | Extra                              |
+------+-------------+------------+------+----------------+----------------+---------+-------------+------+------------------------------------+
|    1 | SIMPLE      | order_test | ref  | order_test_ix1 | order_test_ix1 | 1536    | const,const |    1 | Using index condition; Using where |
+------+-------------+------------+------+----------------+----------------+---------+-------------+------+------------------------------------+
1 row in set (0.00 sec)
 小结：上面执行计划的key_len显示1536，计算下是怎么得出来的，由于CUSTOMERORDERNO字段varchar(255)，且允许为空，按如下规则：
1.索引字段中，对于变长数据类型，比如：varchar，除了是否为空的标记外，还需要有长度信息，需要占用2个字节；
2.同时还需要考虑表所使用的字符集，不同的字符集，gbk编码的为一个字符2个字节，utf8编码的一个字符3个字节;
所以算出：key_len的大小为：
255*3+255*3+1+2+1+2=1536
所以这个场景用到了索引的CUSTOMERORDERNO字段，符合索引的最左原则。

总结：
1.可以根据执行计划的ken_len来判断复合索引中字段是否有使用，计算规则如下：
 当索引字段为定长数据类型，比如char，int，datetime，需要有是否为空的标记，这个标记需要占用1个字节；对于变长数据类型，比如：varchar，除了是否为空的标记外，还需要有长度

信息，需要占用2个字节；同时还需要考虑表所使用的字符集，不同的字符集，gbk编码的为一个字符2个字节，utf8编码的一个字符3个字节;
2.单表排序时，order by后面字段顺序需要与复合索引中字段顺序一致时，才能用索引，符合索引的最左原则，且select后面字段需为索引字段的部分或全部。