手机浏览 RSS 2.0 订阅 膘叔的简单人生 , 腾讯云RDS购买 | 超便宜的Vultr , 注册 | 登陆

Mysql数据库索引查询优化的分享

首页 > DataBase >

这是昨天SAE分享的一篇文章,开始的时候,我看了一遍,发现好象没有什么特别的内容,但再仔细看的时候,发现居然可以这样做。。。

     问题描述:

      我们要访问的表是一个非常大的表,四千万条记录,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万条记录的表基本一样

      总结:

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

-----------

看我标红的那一段,原来还能够这样做,以前真的没有注意过,也从来没有想过,先利用主键做一次过滤。这样效率会好很多啊

上述内容来自:http://ourmysql.com/archives/108?f=wb,该网站还有很多技巧值得一看。




本站采用创作共享版权协议, 要求署名、非商业和保持一致. 本站欢迎任何非商业应用的转载, 但须注明出自"易栈网-膘叔", 保留原始链接, 此外还必须标注原文标题和链接.

Tags: sae, mysql, 索引

« 上一篇 | 下一篇 »

只显示10条记录相关文章

2条记录访客评论

可以试试看select id from program_access_log where program_id between 1 and 4000, 用index only查询

Post by 劣头翔 on 2013, January 6, 9:45 PM 引用此文发表评论 #1

请问program_id的最大值是不是就等于4000?如果最大值不是4000,那么写between 1 and 15000000就有问题。确定就是4000才能这么写。 但如果确定是4000,为何写between来当条件啊?直接program_id大于等于1,用ID做排序和分页读取不就完了?

Post by yhustc on 2012, December 29, 5:45 PM 引用此文发表评论 #2


发表评论

评论内容 (必填):