Chinaunix首页 | 论坛 | 博客
  • 博客访问: 416440
  • 博文数量: 43
  • 博客积分: 613
  • 博客等级: 中士
  • 技术积分: 756
  • 用 户 组: 普通用户
  • 注册时间: 2008-09-01 11:25
文章分类

全部博文(43)

文章存档

2016年(1)

2015年(5)

2014年(5)

2013年(14)

2012年(18)

分类: Mysql/postgreSQL

2013-01-10 09:43:31

表test如下:

  1. | Field | Type | Null | Key | Default | Extra |
  2. +-------------+-----------------------+------+-----+---------+----------------+
  3. | pid | int(10) unsigned | NO | PRI | NULL | auto_increment |
  4. | tid | mediumint(8) unsigned | NO | MUL | 0 | |
  5. | showtime | int(11) | YES | | 0 | |


数据量200w+

原来有个复合索引建立在tid和showtime上的。
现有个sql
SELECT * FROM test  WHERE AND tid='47992' AND pid >=1660250 and showtime>0  ORDER BY p.showtime,p.pid LIMIT 0, 40;
于是将索引修改如下(tid为最左前缀不能改,有别的sql用到tid+showtime):
ALTER TABLE test ADD INDEX idx_tid_showtime_pid(tid,showtime,pid);
执行sql效率很低,请看explain

  1. +----+-------------+----------------+-------+----------------------+----------------------+---------+------+--------+-------------+
  2. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  3. +----+-------------+----------------+-------+----------------------+----------------------+---------+------+--------+-------------+
  4. | 1 | SIMPLE | test | range | idx_tid_showtime_pid | idx_tid_showtime_pid | 8 | NULL | 123444 | Using where |
  5. +----+-------------+----------------+-------+----------------------+----------------------+---------+------+--------+-------------+


感觉效率依然很低,通过slow-log记录可以看到执行时间在0.7秒以上。
测试了下去掉showtime条件后只使用tid和pid的复合索引效率是很高的,于是再次修改索引:
ALTER TABLE test ADD INDEX idx_tid_pid_showtime(tid,pid,showtime);
这样效率大大提高,执行时间降到了0.1以下。

  1. +----+-------------+-------+-------+---------------------------------------------------------------------------------------+----------------------+---------+------+------+-----------------------------+
  2. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  3. +----+-------------+-------+-------+---------------------------------------------------------------------------------------+----------------------+---------+------+------+-----------------------------+
  4. | 1 | SIMPLE | test | range | idx_tid_showtime_pid,idx_tid_pid_showtime | idx_tid_pid_showtime | 12 | NULL | 3290 | Using where; Using filesort |
  5. +----+-------------+-------+-------+---------------------------------------------------------------------------------------+----------------------+---------+------+------+------

    以上问题,从我个人理解上是这样的,idx_tid_showtime_pid(tid,showtime,pid)这条索引满足了使用索引order by的条件,所以需要扫描123444行数据。
    而 idx_tid_pid_showtime(tid,pid,showtime)这条索引的列顺序和order by的顺序不一样,排序无法使用索引,而where条件使用索引效率很好,只扫描了3290行数据,即使加上排序的时间也很快。
 
    由此可见索引的添加也不能一味的以字段顺序去适应排序的顺序,虽然省略了排序所花的时间,但是在其他的开销上远大于少量数据排序带来的影响。

阅读(2321) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~