Mysql索引的使用-组合索引+跳跃条件

阳光穿透心脏的1/2处 2021-06-24 14:37 561阅读 0赞

关于MYSQL组合索引的使用，官方对下面的例子的说法是可以使用索引：

KEY（key\_part1,key\_part2,key\_part3）  
select .... from table where key\_part1='xxx' and key\_part3='yyy';

从MYSQL的执行计划看，确实也是使用索引;

但在实际的优化过程中，我们只是简单的关注是否使用了这个索引是不够的。

\[@more@\]

我们需要关注的是:  
对key\_part3这个关键字过滤的时候，是否用到了索引？  
  
下面我们来创建一个例子：  
CREATE TABLE \`im\_message\_201001\_12\` (  
\`msg\_id\` bigint(20) NOT NULL default '0',  
\`time\` datetime NOT NULL,  
\`owner\` varchar(64) collate latin1\_bin NOT NULL,  
\`other\` varchar(64) collate latin1\_bin NOT NULL,  
\`content\` varchar(8000) collate latin1\_bin default NULL,  
PRIMARY KEY (\`msg\_id\`),  
KEY \`im\_msg\_own\_oth\_tim\_ind\` (\`owner\`,\`other\`,\`time\`)  
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1\_bin;  
  
查询语句:  
select count(distinct concat('ab',content)) dis ,count(\*) all from im\_message\_201001\_12   
where   
owner='huaniaoyuchong83'   
and time between '2010-01-01 00:00:00' and '2010-02-01 00:00:00' ;

我们看到，查询的条件，对索引来说是跳跃的。   
这对ORACLE来说并不是难事。SQL优化器会在索引里完成对time字段的过滤。  
用HINT：/\*+INDEX\_SS(TABLE INDEX\_NAME)\*/ 可以来辅助。  
但对MYSQL来说，你可能并不知道，是什么时候对time字段进行过滤的。  
当然我们希望是通过索引来过滤TIME字段。这样最后回表的次数就会少一些。

在测试过程中，我们通过观察MYSQL的Innodb\_buffer\_pool\_read\_requests（逻辑读）变量的变化，来推测结果。  
注意以下查询过程中，条件time的变化，以及变量Innodb\_buffer\_pool\_read\_requests的变化

\#\#\#\#\#\#\#测试环境：  
OS：RHEL 4.7 X86\_64  
MYSQL 5.0.51a / 5.1.40

请在开始下面测试前，运行：  
select count(distinct concat('c',content)),count(\*) from im\_message\_201001\_11 where owner='huaniaoyuchong83' ；  
以让所有结果都在CACHE里；

\#\#\#\#\#\#\#开始第一次测试  
show session status like 'Innodb\_buffer\_pool\_read\_requests';   
\+----------------------------------+-----------+  
| Variable\_name | Value |  
\+----------------------------------+-----------+  
| Innodb\_buffer\_pool\_read\_requests | 136566076 |   
\+----------------------------------+-----------+  
1 row in set (0.02 sec)  
  
select count(distinct concat('c',content)),count(\*) from im\_message\_201001\_11 where owner='huaniaoyuchong83' and time between '2010-01-01 00:00:00' and '2010-02-01 00:00:00' ;   
  
\+-------------------------------------+----------+  
| count(distinct concat('c',content)) | count(\*) |  
\+-------------------------------------+----------+  
| 35644 | 44397 |   
\+-------------------------------------+----------+  
1 row in set (1.40 sec)  
  
show session status like 'Innodb\_buffer\_pool\_read\_requests';   
\+----------------------------------+-----------+  
| Variable\_name | Value |  
\+----------------------------------+-----------+  
| Innodb\_buffer\_pool\_read\_requests | 136742193 |   
\+----------------------------------+-----------+  
1 row in set (0.02 sec)  
  
select 136742193-136566076 ;  
\+---------------------+  
| 136742193-136566076 |  
\+---------------------+  
| 176117 |   
\+---------------------+  
1 row in set (0.00 sec)

\#\#\#\#\#\#\#开始第二次测试  
show session status like 'Innodb\_buffer\_pool\_read\_requests';   
\+----------------------------------+-----------+  
| Variable\_name | Value |  
\+----------------------------------+-----------+  
| Innodb\_buffer\_pool\_read\_requests | 136742194 |   
\+----------------------------------+-----------+  
1 row in set (0.02 sec)  
  
select count(distinct concat('c',content)),count(\*) from im\_message\_201001\_11 where owner='huaniaoyuchong83' and time between '2010-01-01 00:00:00' and '2010-01-05 00:00:00' ;   
  
\+-------------------------------------+----------+  
| count(distinct concat('c',content)) | count(\*) |  
\+-------------------------------------+----------+  
| 3679 | 4097 |   
\+-------------------------------------+----------+  
1 row in set (0.74 sec)  
  
show session status like 'Innodb\_buffer\_pool\_read\_requests';   
\+----------------------------------+-----------+  
| Variable\_name | Value |  
\+----------------------------------+-----------+  
| Innodb\_buffer\_pool\_read\_requests | 136916032 |   
\+----------------------------------+-----------+  
1 row in set (0.01 sec)  
  
select 136916032-136742194;  
\+---------------------+  
| 136916032-136742194 |  
\+---------------------+  
| 173838 |   
\+---------------------+  
1 row in set (0.00 sec)

\#\#\#\#\#\#\#开始第三次测试

show session status like 'Innodb\_buffer\_pool\_read\_requests';   
\+----------------------------------+-----------+  
| Variable\_name | Value |  
\+----------------------------------+-----------+  
| Innodb\_buffer\_pool\_read\_requests | 136916033 |   
\+----------------------------------+-----------+  
1 row in set (0.01 sec)  
  
select count(distinct concat('c',content)),count(\*) from im\_message\_201001\_11 where owner='huaniaoyuchong83' and time between '2010-01-01 00:00:00' and '2010-01-01 00:00:00' ;   
  
\+-------------------------------------+----------+  
| count(distinct concat('c',content)) | count(\*) |  
\+-------------------------------------+----------+  
| 0 | 0 |   
\+-------------------------------------+----------+  
1 row in set (0.85 sec)  
  
show session status like 'Innodb\_buffer\_pool\_read\_requests';   
\+----------------------------------+-----------+  
| Variable\_name | Value |  
\+----------------------------------+-----------+  
| Innodb\_buffer\_pool\_read\_requests | 137086323 |   
\+----------------------------------+-----------+  
1 row in set (0.01 sec)  
  
select 137086323-136916033;  
\+---------------------+  
| 137086323-136916033 |  
\+---------------------+  
| 170290 |   
\+---------------------+  
1 row in set (0.00 sec)

\#\#\#\#\#\#\#开始第四次测试

show session status like 'Innodb\_buffer\_pool\_read\_requests';   
\+----------------------------------+-----------+  
| Variable\_name | Value |  
\+----------------------------------+-----------+  
| Innodb\_buffer\_pool\_read\_requests | 137086324 |   
\+----------------------------------+-----------+  
1 row in set (0.02 sec)  
  
select count(\*) from im\_message\_201001\_11 where owner='huaniaoyuchong83' and time between '2010-01-01 00:00:00' and '2010-02-01 00:00:00' ;   
\+----------+  
| count(\*) |  
\+----------+  
| 44397 |   
\+----------+  
1 row in set (0.05 sec)  
  
show session status like 'Innodb\_buffer\_pool\_read\_requests';   
\+----------------------------------+-----------+  
| Variable\_name | Value |  
\+----------------------------------+-----------+  
| Innodb\_buffer\_pool\_read\_requests | 137092204 |   
\+----------------------------------+-----------+  
1 row in set (0.01 sec)  
  
select 137092204-137086324 ;  
\+---------------------+  
| 137092204-137086324 |  
\+---------------------+  
| 5880 |   
\+---------------------+  
1 row in set (0.00 sec)

\#\#\#\#\#\#\#开始第五次测试

show session status like 'Innodb\_buffer\_pool\_read\_requests';   
\+----------------------------------+-----------+  
| Variable\_name | Value |  
\+----------------------------------+-----------+  
| Innodb\_buffer\_pool\_read\_requests | 137092205 |   
\+----------------------------------+-----------+  
1 row in set (0.01 sec)  
  
select count(\*) from im\_message\_201001\_11 where owner='huaniaoyuchong83' ;  
\+----------+  
| count(\*) |  
\+----------+  
| 44397 |   
\+----------+  
1 row in set (0.04 sec)  
  
show session status like 'Innodb\_buffer\_pool\_read\_requests';   
\+----------------------------------+-----------+  
| Variable\_name | Value |  
\+----------------------------------+-----------+  
| Innodb\_buffer\_pool\_read\_requests | 137098085 |   
\+----------------------------------+-----------+  
1 row in set (0.01 sec)  
  
select 137098085-137092205 ;  
\+---------------------+  
| 137098085-137092205 |  
\+---------------------+  
| 5880 |   
\+---------------------+  
1 row in set (0.00 sec)

\#\#\#\#\#\#\#开始第六次测试

show session status like 'Innodb\_buffer\_pool\_read\_requests';   
\+----------------------------------+-----------+  
| Variable\_name | Value |  
\+----------------------------------+-----------+  
| Innodb\_buffer\_pool\_read\_requests | 137098131 |   
\+----------------------------------+-----------+  
1 row in set (0.02 sec)  
  
select count(\*) from im\_message\_201001\_11 where owner='huaniaoyuchong83' and time between '2010-01-01 00:00:00' and '2010-01-05 00:00:00' ;   
\+----------+  
| count(\*) |  
\+----------+  
| 4097 |   
\+----------+  
1 row in set (0.05 sec)  
  
show session status like 'Innodb\_buffer\_pool\_read\_requests';   
\+----------------------------------+-----------+  
| Variable\_name | Value |  
\+----------------------------------+-----------+  
| Innodb\_buffer\_pool\_read\_requests | 137104011 |   
\+----------------------------------+-----------+  
1 row in set (0.01 sec)  
  
select 137104011-137098131;  
\+---------------------+  
| 137104011-137098131 |  
\+---------------------+  
| 5880 |   
\+---------------------+  
1 row in set (0.00 sec)

\#\#\#\#\#\#\# 分析结果

前三次查询，从索引检索后需要回表：  
time 结果行数 逻辑读  
30天 44397 176117  
5天 4097 173838  
1天 0 170290   
  
后三次查询，从索引检索后不需要回表  
time 结果行数 逻辑读  
30天 44397 5880  
无time条件 44397 5880  
5天 4097 5880  
  
从数据来看，  
select count(\*) 这样的查询，有或者没有time条件，逻辑读是一样，都不用回表。  
这里也说明这种情况MYSQL是用索引进行time字段的过滤。  
  
select count(distinct concat('c',content)),count(\*), 这样的查询，用到了索引以外的字段，是必需回表的。   
但通过逻辑读发现，不管查询结果是多少行，逻辑读都差不多，在17W左右。  
特别是结果行为0时，如果是通过索引过滤time，那么逻辑读应该接近5900,而不是17W。  
这也说明，这种情况下，MYSQL没有使用索引来对TIME字段进行过滤;  
  
所以MYSQL对相同WHERE条件的查询，还采用了不同的优化程序；但MS这个优化有点问题。  
  
  
对这样的索引，需要优化，可以。 调整索引顺序(\`owner\`,\`time\`,\`other\`)。  
但是这仅仅是对一个SQL的优化。   
你还要考虑到系统里还有很多其他类似的SQL需要用到这个索引。 所以在优化时，需要评估所有的SQL。