大橙子网站建设,新征程启航

为企业提供网站建设、域名注册、服务器等服务

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

关于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。


新闻标题:Mysql索引的使用-组合索引+跳跃条件
标题URL:http://dzwzjz.com/article/pgsjie.html
在线咨询
服务热线
服务热线:028-86922220
TOP