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

朱雀 2022-06-11 01:46 218阅读 0赞

转自 [http://www.starming.com/index.php?action=plugin&v=wave&tpl=t&nav=10&pg=7&gid=76&tid=3868][http_www.starming.com_index.php_action_plugin_v_wave_tpl_t_nav_10_pg_7_gid_76_tid_3868]

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

下载:  [code.txt][]

select  \*  from   program\_access\_log   where   program\_id   between   1   and   4000

这条SQL非常慢。  
我们原以为处理记录太多的原因，所以加了id限制，一次只读五十万条记录

下载:  [code.txt][]

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，不支持分区，而且这个表是公共表，无法在不影响其它项目的条件下修改表的结构。  
所以我们采取了第三种办法：

下载:  [code.txt][]

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_www.starming.com_index.php_action_plugin_v_wave_tpl_t_nav_10_pg_7_gid_76_tid_3868]: http://www.starming.com/index.php?action=plugin&v=wave&tpl=t&nav=10&pg=7&gid=76&tid=3868
[code.txt]: http://www.hiadmin.com/wp-content/plugins/coolcode/coolcode.php?p=571&download=code.txt