[转贴] mysql数据库索引查询优化的分享

原创 Linux操作系统作者：alert.an 时间：2009-08-08 10:43:59 0 删除编辑

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

转自：http://tech.techweb.com.cn/thread-417760-1-1.html

问题描述：& H& R5 s& @9 V( C3 U0 A

我们要访问的表是一个非常大的表，四千万条记录，id是主键，program_id上建了索引。/ @0 _. n$ D& {2 ~7 }: g
执行一条SQL:
select * from program_access_log where program_id between 1 and 4000

这条SQL非常慢。+ B$ ~( M  }6 f0 _
我们原以为处理记录太多的原因，所以加了id限制，一次只读五十万条记录4 S3 r7 b0 y4 Q2 n) E
select * from program_access_log where id between 1 and 500000 and program_id between 1 and 4000( S. C. P4 z1 I

但是这条SQL仍然很慢，速度比上面一条几乎没有提升。
Mysql处理50万条记录的表，条件字段还建了索引，这条语句应该是瞬间完成的。
/ y/ M% t+ o6 x/ y
+ x. E7 o2 }' P( J6 l
问题分析：; A# v! `8 S2 o6 h( `$ u, I4 G7 [, }

这张表大约容量30G，数据库服务器内存16G，无法一次载入。就是这个造成了问题。% z  L5 h7 m2 q3 r0 R% a
这条SQL有两个条件，ID一到五十万和Program_id一到四千，因为program_id范围小得多，mysql选择它做为主要索引。4 _5 x$ ^1 d0 s; b1 s0 n
先通过索引文件找出了所有program_id在1到4000范围里所有的id，这个过程非常快。
接下来要通过这些id找出表里的记录，由于这些id是离散的，所以mysql对这个表的访问不是顺序读取。
而这个表又非常大，无法一次装入内存，所以每访问一条记录mysql都要重新在磁盘上定位并把附近的记录都载入内存，大量的IO操作导致了速度的下降。- ]% {: f' @- v6 B
问题解决方案：' E7 }; a( J; X0 `
1. 以program_id为条件对表进行分区5 W/ Q# O  K. M
2. 分表处理，每张表的大小不超过内存的大小; E- Z: ~# \9 `' f' d: x4 ?
然而，服务器用的是mysql5.0，不支持分区，而且这个表是公共表，无法在不影响其它项目的条件下修改表的结构。
所以我们采取了第三种办法：) ]. I3 C* X- ?+ o) S
select * from program_access_log where id between 1 and 500000 and program_id between 1 and 15000000
" f( J4 l0 W9 L- v" a9 m, }
现在program_id的范围远大于id的范围，id被当做主要索引进行查找，由于id是主键，所以查找的是连续50万条记录，速度和访问一个50万条记录的表基本一样
总结：+ S& T( C4 g# _1 A# u3 t" D
这是一个在千万笔记录表中由于使用了索引导致了数据查找变慢的问题，有一定的典型性和大家交流下！

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/123747/viewspace-611652/ ，如需转载，请注明出处，否则将追究法律责任。