Mysql数据库索引查询优化的分享
八月 11th, 2008 Posted in MySQL < by Michael Field >
问题描述：
我们要访问的表是一个非常大的表，四千万条记录，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万条记录的表基本一样
总结：
这是一个在千万笔记录表中由于使用了索引导致了数据查找变慢的问题，有一定的典型性和大家交流下！
八月 11th, 2008 at 3:03 下午
类似的案例可以考虑子查询的方式，有时候会有意想不到的效果。
参考：http://hi.baidu.com/thinkinginlamp/blog/item/17476d22d66876a14623e81d.html
八月 11th, 2008 at 3:59 下午
几位老大,有没有my.cnf优化的资料!我近期需要部属5，6套MYSQL 主备结构，硬件都为两个至强四核CPU+8G内存！
八月 11th, 2008 at 7:24 下午
To myfreeke：
主要用来做什么应用？那些方面需要优化？
IO、查询、索引？
八月 13th, 2008 at 5:39 下午
比如我们会有几个主从做用户auth库,其它的库用来存放用户数据,比如好友关系等!
索引文件比较数据文件大!
八月 13th, 2008 at 5:40 下午
主专门用来做写操作,从专门提供给程序读操作!
八月 13th, 2008 at 11:53 下午
使用mysql-proxy实现读写分离搭配主从数据库
配置安装见：
http://www.hiadmin.com/mysql-proxy-%e7%bc%96%e8%af%91%e5%ae%89%e8%a3%85%e6%96%87%e6%a1%a3-by-centos44-up/
实战操作:
http://www.hiadmin.com/mysql-proxy%e8%af%bb%e5%86%99%e5%88%86%e7%a6%bb%e5%ae%9e%e6%88%98/
八月 14th, 2008 at 8:30 上午
开发说proxy的性能不强,现在说要使用主备模式!可能他们应该也没有实际测试过!我们的框架并发可能会比较大,用户量平均在线也有几十万人
八月 14th, 2008 at 9:35 上午
proxy的性能测试我们一直没做.因为在应用上还有一定的问题.
如果能从程序内直接支持读写分离,那是最好的.
毕竟再加一层proxy是由于开发初期没有做好规划后期来补救的措施.
本身可能成为单点故障点或者瓶颈.
如果用户量很大.还需要考虑数据分片的问题.
最好是能在前期把这些工作都做掉.否则后期再改.一个是时间很急迫.第二是修改两非常大.
八月 14th, 2008 at 3:26 下午
可以使用select * from program_access_log use index (id)来指定索引，不知楼主用的什么数据引擎，个人估计是myisam，换一个引擎也测一下看
八月 19th, 2008 at 5:01 下午
这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引。
十二月 3rd, 2008 at 4:53 下午
楼主,后面的select * from program_access_log where id between 1 and 500000 and program_id between 1 and 15000000语句虽然快了,但program_id是1 and 15000000的啊,我要的是program_id是在1-4000的啊