优化 MySQL 语句的十个建议
发布时间:2012-05-08 07:14:36
Jaslabs的Justin Silverton列出了十条有关优化MySQL查询的语句,我不得不对此发表言论,因为这个清单非常非常糟糕。另外一个Mike也同样意识到了。所以在这个博客中,我要做两件事情,第一,指出为什么这个清单很糟糕,第二,列出我的清单,希望我的比较好些。继续看吧,无畏的读者们!
(译者注:作者借这个题目反讽另一篇同名的文章)
为什么那个清单很糟糕
1.他的力气没使对地方
我们要遵循的一个准则就是如果你要优化代码时,应该先找出瓶颈在哪。然而Silverton先生的力气没有用对地方。我认为60%的优化是基于清楚 理解SQL和数据库基础的。你需要知道join和子查询的区别,列索引,以及如何将数据规范化等等。另外的35%的优化是需要清楚数据库选择时的性能表 现,例如COUNT(*)可能很快也可能很慢,要看你选用什么数据库引擎。还有一些其他要考虑的因素,例如数据库在什么时候不用缓存,什么时候存在硬盘上 而不存在内存中,什么时候数据库创建临时表等等。剩下的5%就很少会有人碰到了,但Silverton先生恰好在这上面花了大量的时间。我从来就没用过 SQL_SAMLL_RESULT。
2.很好的问题,但是很糟糕的解决方法
Silverton先生提出了一些很好的问题。MySQL针对长度可变的列如TEXT或BLOB,将会使用动态行格式(dynamic row format),这意味着排序将在硬盘上进行。我们的方法不是要回避这些数据类型,而是将这些数据类型从原来的表中分离开,放入另外一个表中。下面的 schema可以说明这个想法:
CREATE TABLE posts (
id int UNSIGNED NOT NULL AUTO_INCREMENT,
author_id int UNSIGNED NOT NULL,
created timestamp NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE posts_data (
post_id int UNSIGNED NOT NULL.
body text,
PRIMARY KEY(post_id)
);
3. 有点匪夷所思……
他的许多建议都是让人非常吃惊的,譬如“移除不必要的括号”。你这样写SELECT * FROM posts WHERE (author_id = 5 AND published = 1),还是这样写SELECT * FROM posts WHERE author_id = 5 AND published = 1 ,都不重要。任何比较好的DBMS都会自动进行识别做出处理。这种细节就好像C语言中是i++快些还是++i快些。真的,如果你把精力都花在这上面了,那就不用写代码了。
我的列表
看看我的列表是不是更好吧。我先从最普遍的开始。
1.建立基准,建立基准,建立基准!
如果需要做决定的话,我们需要数据说话。什么样的查询是最糟的?瓶颈在哪?我什么情况下会写出糟糕的查询?基准测试可以让你模拟高压情况,然后借助 性能测评工具,可以让你发现数据库配置中的错误。这样的工具有supersmack, ab, SysBench。这些工具可以直接测试你的数据库(譬如supersmack),或者模拟网络流量(譬如ab)。
2.性能测试,性能测试,性能测试!
那么,当你能够建立一些高压情况之后,你需要找出配置中的错误。这就是性能测评工具可以帮你做的了。它可以帮你发现配置中的瓶颈,不论是在内存中,CPU中,网络中,硬盘I/O,或者是以上皆有。
你要做的第一件事就是开启慢查询日志(slow query log),装上mtop。这样你就能获取那些恶意的入侵者的信息了。有需要运行10秒的查询语句正在破坏你的应用程序吗?这些家伙会展示给你看他的查询语句是怎么写的。
在你发现那些很慢的查询语句后,你需要用MySQL自带的工具,如EXPLAIN,SHOW STATUS,SHOW PROCESSLIST。它们会告诉你资源都消耗在哪了,查询语句的缺陷在哪,譬如一个有三次join子查询的查询语句是否在内存中进行排序,还是在硬盘 上进行。当然你也应该使用测评工具如top,procinfo,vmstat等等获取更多系统性能信息。
3.减小你的schema
在你开始写查询语句之前,你需要设计schema。记住将一个表装入内存所需要的空间大概是行数*一行的大小。除非你觉得世界上的每个人都会在你的 网站注册2兆8000亿次的话,否则你不需要采用BITINT作为你的user_id。同样的,如果一个文本列是固定大小的话(譬如US邮编,通常 是”XXXXX-XXXX”的形式),采用VARCHAR的话会给每行增加多余的字节。
有些人对数据库规范化不以为意,他们说这样会形成相当复杂的schema。然而适当的规范化会减少化冗余数据。(适当的规范化)就意味着牺牲少许性 能,换取整体上更少的footprint,这种性能换取内存在计算机科学中是很常见的。最好的方法是IMO,就是开始先规范化,之后如果性能需要的话,再 反规范化。你的数据库将会更逻辑化,你也不用过早的进行优化。(译者注,这一段我不是很理解,可能翻译错了,欢迎纠正。)
4.拆分你的表
通常有些表只有一些列你是经常需要更新的。例如对于一个博客,你需要在许多不同地方显示标题(如最近的文章列表),只在某个特定页显示概要或者全文。水平垂直拆分是很有帮助的:
CREATE TABLE posts (
id int UNSIGNED NOT NULL AUTO_INCREMENT,
author_id int UNSIGNED NOT NULL,
title var char( 128 ),
created timestamp NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE posts_data (
post_id int UNSIGNED NOT NULL,
teaser text,
body text,
PRIMARY KEY(post_id)
);
上面的schema是对读数据进行的优化。经常要访问的数据存在一个表中,那些不经常访问的数据放在另一个。被拆分后,不经常访问的数据占据更少的 内存。你也可以优化写数据,经常更新的数据放在一个表,不经常更新的放在另一个表。这可以使缓存更高效,因为MySQL不需要让没有更新过的数据移出缓 存。
猜你喜欢
- 程序还不是很精简,以后再修改,程序所用的数据库为-- “冯志宏”-- 所写的--“追捕”--软件中所带IP数据库和“国华软件 Guohua
- 今天一个域名查询系统出现故障,该系统是用的ASP调用XMLHTTP获取whois库的数据,具体错误如下: msxml3.dll 错
- 将近两个多月没有敲linux命令,手指头不听话了。该再次温习卖油翁的故事了。曾为oracle如何启动多个实例运行纠结过,不服输心理。当时在一
- 一个网站能切换不同的CSS风格大家应该都了解,像众所周知的腾讯在今年改版时也增加了切换皮肤的功能。根据时间自动调整站点风格是不错的想法,这种
- 英文原文:The seven rules of Unobtrusive JavaScript原文地址:http://icant.co.uk/
- 你是否曾为表单设计感到过沮丧或不知所措呢?接下来三篇文章,希望能彻底改变你的看法,真正爱上Web表单设计。首先感谢Luke Wroblews
- 三、支持XML的公司和它们的开发工具 尽管XML还处在开发阶段,其标准正在由W3C组织制定,但是已经有许多公司表示全力支持XML,并开发了不
- 人们很容易忽视图像img标签的alt属性。然而,它的重要性也无法体现出来,它是有利于网页的accessibility and&nb
- 一直希望自己对Web设计的色彩把握的更好些,所以手头上就备了一份图片版的配色表,对色彩的搭配有了很多的提高,今天发现了这套非常完整的Web版
- UNIX时间戳转换为日期用函数FROM_UNIXTIME()select FROM_UNIXTIME(1156219870);日期
- 如何远程注册DLL?试试下面的代码:<% Response.Buffer = True %&g
- “用户体验(User Experience,简称UX 或 UE)是一种纯主观的在用户使用一个产品(服务)的过程中建立起来的心理感受。因为它是
- 一、多行函数又称组合函数(Group Functions)、聚合函数 1、 Types of Group Functions avg、cou
- 网页广告 Banner 设计图文手册:采用以下要点来改善你的BANNER。广告并不便宜。 确信你的广告被第一时间读到。使用像这样的Sans
- 1、查看数据库中有哪些用户? select username from all_users;2、查看数据库中有哪些DBA用户? select
- 这几天一直在看《Pro JavaScript Techniques》,书中有不少优美、健壮代码,让我不得不惊叹老外对语言这东西的研究程度之深
- 做项目的时候,一位同事导数据的时候,不小心把一个表中的数据全都搞重了,也就是说,这个表里所有的记录都有一条重复的。这个表的数据是千万级的,而
- 说到 IE 的 bug,一个臭名昭著的例子是它对于“盒模型”的错误解释:在 IE5.x 以及 Quirks 模式的 IE6/7 中,将 bo
- 本文介绍了保护MySQL数据库中重要数据受外部攻击的六个注意事项,以减少面临的风险。与自动的数据库备份不同,对系统管理员来说,保护数据免受未
- 将数据库中的信息存储至XML文件中:save.asp<!-- #include file="adovbs