转载 建立索引查询慢的解决办法

ohno

此文章来自互联网， 自己收藏的。 忘记出自谁的原创了。今天转载发上来大家分享下！


问题描述：


我们要访问的表是一个非常大的表，四千万条记录，id是主键，program_id上建了索引。
执行一条SQL:

select * from program_access_log where program_id between 1 and 4000
这条SQL非常慢。
我们原以为处理记录太多的原因，所以加了id限制，一次只读五十万条记录

select * from program_access_log where id between 1 and 500000 and program_id between 1 and 4000
但是这条SQL仍然很慢，速度比上面一条几乎没有提升。
Mysql处理50万条记录的表，条件字段还建了索引，这条语句应该是瞬间完成的。




问题分析：


这张表大约容量30G，数据库服务器内存16G，无法一次载入。就是这个造成了问题。
这条SQL有两个条件，ID一到五十万和Program_id一到四千，因为program_id范围小得多，mysql选择它做为主要索引。
先通过索引文件找出了所有program_id在1到4000范围里所有的id，这个过程非常快。
接下来要通过这些id找出表里的记录，由于这些id是离散的，所以mysql对这个表的访问不是顺序读取。
而这个表又非常大，无法一次装入内存，所以每访问一条记录mysql都要重新在磁盘上定位并把附近的记录都载入内存，大量的IO操作导致了速度的下降。

问题解决方案：
1. 以program_id为条件对表进行分区
2. 分表处理，每张表的大小不超过内存的大小
然而，服务器用的是mysql5.0，不支持分区，而且这个表是公共表，无法在不影响其它项目的条件下修改表的结构。
所以我们采取了第三种办法：

select * from program_access_log where id between 1 and 500000 and program_id between 1 and 15000000
现在program_id的范围远大于id的范围，id被当做主要索引进行查找，由于id是主键，所以查找的是连续50万条记录，速度和访问一个50万条记录的表基本一样

总结：
这是一个在千万笔记录表中由于使用了索引导致了数据查找变慢的问题，有一定的典型性和大家交流下！

voxxu

不错，谢谢分享

james.liu

这个应该是个不成功的教学例子 。

首先问题从“select * from program_access_log where program_id between 1 and 4000”到最后的“select * from program_access_log where id between 1 and 500000 and program_id between 1 and 15000000”

到底要查询怎么样的信息呢？

james.liu

第二点：扯了半天，说因为“由于这些id是离散的，所以mysql对这个表的访问不是顺序读取。”+“而这个表又非常大，无法一次装入内存”。而导致查询慢。

无法一次装入内存指的是表无法全部装入内存（这个表是内存表？）？还是指索引？（已经知道id了，和索引应该也没啥关系，也就是直接拿数据的事情）


我觉得第一次慢是数据太多，第二次慢还是数据太多。


欢迎ls两位和yueliangdao0608 来拍砖，其他人来拍砖。

justin033

这个应该是个不成功的教学例子 。

首先问题从“select * from program_access_log where program_id bet ...
james.liu 发表于 2010-03-09 21:22

  
支持！按LZ这例子，根本没解决分表后业务逻辑变复杂，如联合查询问题，而且查询的速度也没得到真正的解决。这样查询直接造成多个分表联合查询。最好建个对program_id建table_router从尔直接定位范围，再通过ID查询

qmail-czw

不错！谢谢分享