用户
 找回密码
 入住 CI 中国社区
搜索
查看: 21503|回复: 21
收起左侧

[优化] 通过索引优化含ORDER BY的MySQL语句的经验分享

[复制链接]
发表于 2009-8-14 22:48:57 | 显示全部楼层 |阅读模式
看到社区里面大家非常踊跃的讨论关于MySQL优化,特别是Order by优化的问题[via],我就将自己在平时项目中积累的一些经验和大家分享一下。一家之言,未免有误,欢迎拍砖,仅供参考。

关于建立索引的几个准则:
1、合理的建立索引能够加速数据读取效率,不合理的建立索引反而会拖慢数据库的响应速度。
2、索引越多,更新数据的速度越慢。
3、尽量在采用MyIsam作为引擎的时候使用索引(因为MySQL以BTree存储索引),而不是InnoDB。但MyISAM不支持Transcation。
4、当你的程序和数据库结构/SQL语句已经优化到无法优化的程度,而程序瓶颈并不能顺利解决,那就是应该考虑使用诸如memcached这样的分布式缓存系统的时候了。
5、习惯和强迫自己用EXPLAIN来分析你SQL语句的性能。

一个很容易犯的错误:

不要在选择的栏位上放置索引,这是无意义的。应该在条件选择的语句上合理的放置索引,比如where,order by。

例子:

SQL复制代码
SELECT id,title,content,cat_id FROM article WHERE cat_id = 1;
复制代码


上面这个语句,你在id/title/content上放置索引是毫无意义的,对这个语句没有任何优化作用。但是如果你在外键cat_id上放置一个索引,那作用就相当大了。

几个常用ORDER BY语句的MySQL优化:

1、ORDER BY + LIMIT组合的索引优化。如果一个SQL语句形如:

SQL复制代码
SELECT [column1],[column2],.... FROM [TABLE] ORDER BY [sort] LIMIT [offset],[LIMIT];
复制代码


这个SQL语句优化比较简单,在[sort]这个栏位上建立索引即可。

2、WHERE + ORDER BY + LIMIT组合的索引优化,形如:

SQL复制代码
SELECT [column1],[column2],.... FROM [TABLE] WHERE [columnX] = [VALUE] ORDER BY [sort] LIMIT [offset],[LIMIT];
复制代码


这个语句,如果你仍然采用第一个例子中建立索引的方法,虽然可以用到索引,但是效率不高。更高效的方法是建立一个联合索引(columnX,sort)

3、WHERE + IN + ORDER BY + LIMIT组合的索引优化,形如:

SQL复制代码
SELECT [column1],[column2],.... FROM [TABLE] WHERE [columnX] IN ([value1],[value2],...) ORDER BY [sort] LIMIT [offset],[LIMIT];
复制代码


这个语句如果你采用第二个例子中建立索引的方法,会得不到预期的效果(仅在[sort]上是using index,WHERE那里是using where;using filesort),理由是这里对应columnX的值对应多个。

这个语句怎么优化呢?我暂时没有想到什么好的办法,于是就用了一个愚蠢的办法,那就是将这个语句用UNION分拆,然后建立第二个例子中的索引:

SQL复制代码
SELECT [column1],[column2],.... FROM [TABLE] WHERE [columnX]=[value1] ORDER BY [sort] LIMIT [offset],[LIMIT]
UNION
SELECT [column1],[column2],.... FROM [TABLE] WHERE [columnX]=[value2] ORDER BY [sort] LIMIT [offset],[LIMIT]
UNION
……
复制代码


4、不要再WHERE和ORDER BY的栏位上应用表达式(函数),比如:

SELECT * FROM [table] ORDER BY YEAR(date) LIMIT 0,30;

5、WHERE+ORDER BY多个栏位+LIMIT,比如

SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10;

对于这个语句,大家可能是加一个这样的索引:(x,y,uid)。但实际上更好的效果是(uid,x,y)。这是由MySQL处理排序的机制造成的。

以上例子你在实际项目中应用的时候,不要忘记在添加索引后,用EXPLAIN看看效果。

(暂时只想到这么多,我想到了再来更新,欢迎大家添加)

评分

参与人数 3威望 +9 收起 理由
siek + 2 赞一个! 索引神马的真心不大理解。.
kazaff + 2 索引是我的痛
Hex + 5 精品文章

查看全部评分

发表于 2009-8-14 23:19:11 | 显示全部楼层
确实学到了东西,以前对于 mysql 优化接触的不多。
发表于 2009-8-15 08:44:00 | 显示全部楼层
合理的索引能够大幅度的提升读取性能。

之前我优化了同事的数据库,性能一下子提升700%。
发表于 2009-9-5 22:33:06 | 显示全部楼层
700%太吹牛了吧!! 3# 沧蓝
发表于 2009-9-9 09:07:02 | 显示全部楼层
我信!我信 ~
发表于 2009-9-22 17:21:53 | 显示全部楼层
本帖最后由 renwuxun 于 2009-9-22 17:35 编辑
700%太吹牛了吧!! 3# 沧蓝
netkuxia 发表于 2009-9-5 22:33

这种话你都说的出口,700%也就7倍,相当不夸张,当数据量无限增加时,有良好索引的读操作时间和没索引的读操作时间的比值趋向于零(理论上是这样的,忽略了硬件局限性)

还有关于楼主的帖子,
其实楼主的索引已经搞得相当到家了,其他的不敢说楼主了,只是给个建议吧,这时候楼主如果能够把重心转移到一个更抽象的层面,比如数据结构,设计理论啊什么的或许更有利于自身进步(实践需要正确理论的指导,这句话的意义我体会到了,可惜有点晚)。
若是还在想尽办法使你的第3个句子跑的更快,那就没有必要了,这个句子永无法优化到令人愉快的程度。但是我想说一点,我也主攻php的,4年(时间不长,但这又何妨?)的应用开发中从社区、网店、cms、blog、以及b2b平台...其中从来没有出现过“where in ...”,因为几乎所有的where in...理论上都可以通过修改数据结构而绕过。所以我一直觉得凶悍的设计价值高于极限的索引。

说的有点唬人了,实时上我写php都还没有摆脱ide,但话说回来,我没有任何理由去摆脱ide,其他任何我会的语言都一样(除了js)。

最后一点,劝诫所有的代码搬运工(特别是我自己,回学校去吧),多学点数学吧,否则你的我的他(她)的水平很难有质提升……

评分

参与人数 1威望 +3 收起 理由
Hex + 3 精辟+受益匪浅

查看全部评分

发表于 2009-9-24 17:48:28 | 显示全部楼层
不错的文章explain
发表于 2009-11-6 21:24:36 | 显示全部楼层
好帖子~学习了,真希望多出现这种帖子~
发表于 2010-5-11 21:49:12 | 显示全部楼层
这么高质量的文章现在才看到,
差点错过
发表于 2010-5-20 17:00:23 | 显示全部楼层
顶楼主。SQL优化,说多久都不过分。

本版积分规则