• 26
  • 4月

建立测试数据表

CREATE TABLE `milliontest` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT ,
`name` CHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,
`content` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
`time` INT( 11 ) NOT NULL DEFAULT '0',
`lastmodify` DATETIME NOT NULL,
PRIMARY KEY ( `id` )
) ENGINE = Myisam

用上面的语句创建一个测试用的表。表中包含5个字段,自增主键ID,标题名字name,内容content以及创建时间time与最后编辑时间lastmodify,其中time字段用时间戳而编辑时间为年月日格式。由于目前表内没有任何数据,接下来当然是往表里灌数据,灌上100W(唬人用)。

我写了一个php脚本来完成这个任务。代码如下,各位大大不要耻笑:

set_time_limit(0);
$conn = mysql_connect('localhost', 'root', '');
mysql_select_db('test');

$titles 	= array(
'任天堂3DS发售,2000RMB兲朝币',
 '明猪昨天买了飞行俱乐部', '前天第一次玩街霸网战虐人了!'
);
$contents	= array(
'据美国国家地理网站报道,美国“国家地理新闻”网站刊登了过去一周的精彩太空图片。
这些图片集中展现了极光、土卫一“米玛斯”、Abell 383星系团以及好似绿龙般的SH 
2-235星云等壮观景象',
'由于北极气候变暖,大量永冻土带融化,流入海洋。新研究发现,北极部分地区的永
久冻结带每年遭侵蚀的程度最多达到100英尺(约合30米)。');

$max = 1000000;
mysql_query("set names utf8");

for($i = 0; $i < $max; $i++) {
	$tn	= array_rand($titles);
	$cn	= array_rand($contents);
	$time	= time();
	$lastmodify = date('Y-m-d H:i:s');
	$res = mysql_query('insert into milliontest set name="'.$titles[$tn].'",
 content="'.$contents[$cn].'", time="'.$time.'", lastmodify="'.$lastmodify.'"');
	file_put_contents('million_log.txt', $i);
}

花了20多分钟时间数据库里终于有100w的数据了,泪流满面。那么下面就打开mysql终端开始华丽的测试一下。

select * from milliontest where time=1303812283;
/*略去搜索结果*/
146 rows in set (8.16 sec)

一条简单的查询就花了小小的8秒钟时间哦,要是100个人同时访问某个页面而同时运行了这条简单的语句,结果会怎么样咩? :s1hiahia

这里我又做了另一个测试,注意跟上面那条sql语句的区别:

select * from milliontest where time='1303812283';
/*略去搜索结果*/
146 rows in set (55.98 sec)

看到这查询时间,我自己都扭曲了,我只是查询一个想要的结果而已,却花56秒近1分钟的时间来等待查询。所以小提一下sql语句中各位注意一下引号吧。
又做了几组测试,时间多在8秒波动,有空各位自己慢慢测~

现在给time字段加一个索引

mysql> alter table milliontest add index (`time`);
Query OK, 1000000 rows affected (33.88 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

建完索引,继续做一下上面的测试吧,嘻嘻。 :s1kila

mysql> select * from milliontest where time='1303812291';

615 rows in set (0.05 sec)

加了索引后这次花了0.05秒,相对之前的数据,速度提升相当多吧哈哈哈。不过这么简单的查询却花了0.05秒,对于并发量大的时候估计也不乐观咩,咱们试试去掉引号?

mysql> select * from milliontest where time=1303812283;

146 rows in set (0.00 sec)

爽吧!花费时间0.00! :s1nihaha

体会到了索引带来的好处了吧,不过话说我突然想起来,跑去看了一下这张表占的磁盘容量,泪流满面我没找到wamp这个软件吧索引文件丢到哪去了,总之之前我在自己ubuntu下做的测试好像加表数据占了磁盘800M还是多少,忘了

分页的SQL优化(limit优化)

之前在网上看的时候看到很多关于limit优化的文章,不小心看到一篇分页优化,哎哟看完那篇文章之后,我突然恍然大悟,附我跟主管的某段对话:

主管:好像现在后台有点慢?
我:估计是数据正在批量入库的缘故,不快。

但事后我重新测试了下后台,速度还是NN慢。刚好想到某篇分页测试的文章,提到:
select id,name from milliontest limit 0,20;的速度与select id,name from milliontest limit N,20;
不一样,查询结果的速度取决于N,也就是N越大,速度越慢。
嘴巴说没什么用,还是看事实。

mysql> select id,name from milliontest limit 0,20;
20 rows in set (0.00 sec)
而
mysql> select id,name from milliontest limit 100000,20;
20 rows in set (0.25 sec)
再看
mysql> select id,name from milliontest limit 500000,20;
20 rows in set (1.05 sec)

明显查询时间有了小增长吧。后面的时间更恐怖哟。所以很多论坛之类的,数据太久的就不显示出来了,或者加缓存,那是其他的技术咯。
本文还是回过头来写点简单的,利用上面提到的索引。

select id from milliontest order by id desc limit 500019,20;
20 rows in set (0.50 sec)

速度比之前的1.05秒快了相当多吧。(这里刚刚碰到个问题,感觉0.5秒的时间还是很慢,问了下同事,说是我本地PC硬件也有一方面问题,不知道还有没有谁知道还这条语句还能如何优化?)
这样将该页的数据ID都取出来,然后再使用

select id,name from milliontest where id in (499981,499980,499979,499978);
4 rows in set (0.00 sec)

来取得相关信息,速度飞快,这样就解决了查看分页很大的时候,锁数据表的问题。

我们可以explain一下上面这条sql:

mysql> explain select id,name from milliontest where id in (499981,499980,499979
,499978);
+----+-------------+-------------+-------+---------------+---------+---------+--
----+------+-------------+
| id | select_type | table       | type  | possible_keys | key     | key_len | r
ef  | rows | Extra       |
+----+-------------+-------------+-------+---------------+---------+---------+--
----+------+-------------+
|  1 | SIMPLE      | milliontest | range | PRIMARY       | PRIMARY | 4       | N
ULL |    4 | Using where |
+----+-------------+-------------+-------+---------------+---------+---------+--
----+------+-------------+
1 row in set (0.00 sec)

网上挺常看到使用union来替代in语句。不过个人不喜欢那么长的语句,球抽打。

使用联合索引

话说现实项目中不需要条件的查询真的太少啦,一般都得加一个或者X个条件来查询。这时候咱们就得建个联合索引来干掉它!

现在我们试试给它加个组合索引。

mysql> alter table milliontest add index (`name`,`lastmodify`);
Query OK, 1000000 rows affected (37.91 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

话说杯具了,本来想拿语句:

select id,name,time,lastmodify from milliontest 
where name="明猪昨天买了飞行俱乐部" and lastmodify="2011-04-26 10:04:43";

上面这条语句使用了组合索引,因此查询速度应该不慢。当然,数据量重复度比较高的字段不宜建索引,有时反而比无索引更慢,请自行搜索一下哪些情况下不宜建立索引。

来做个测试,发现在公司windows下的终端悲剧的敲不了中文,那么测试作罢。

另外,假如咱们建立了索引(name,time),事实上则相当于是建立了(name,time),(name)两个索引,传说中的最佳左前缀。也就是,当sql条件中使用到了name,time或者name的时候,索引都会生效,假如只是用了time,索引不会生效。

不知道能不能帮到一些初识索引的人。就这样啦,难得哥哥我写篇技术文。泪目..

PS:以后可能我也会在这生活博客写一些自己技术方面的小心得什么的,各位不喜的就可以直接吐槽路过啦~

PS2:好久没连续两天写博客了...

PS3:这篇文章是今晚下班之后再公司搞出来的哦,昨天叫房东今晚过来收房租,我却留在公司玩,悲剧,刚刚房东打我电话问我什么时候会到的时候害我一直道歉。房东一直说没事。我继续道歉,房东继续**** :s1niuqu

PS4: 因为博客没安装代码高亮插件,我瞬间把pre标签的样式,好像这样代码也蛮好看的 :s1haixiu

» 你可以 留言, 或者 返回 到你的站点.

43 回复

  1. lovee :

    技术白表示鸭梨很大 :pstaisi

    [回复]

    油饼小明猪 回复:

    @lovee, 咦?今天不准备吐槽一下? 8O

    [回复]

  2. EmiNarcissus :

    :han 代码优化神马的很头疼….自己对数据库设计和实现好多方面是几乎是0,有时间真要好好补补了…

    [回复]

    油饼小明猪 回复:

    @EmiNarcissus, 其实我也什么都不会啦..杯具。一起加油吧 :roll:

    [回复]

  3. 卜卜口の :

    :D 拜读! :tt 注意到mysql这里的瓶颈之后果断的研究静态化去了…目前压力好大

    [回复]

    油饼小明猪 回复:

    @卜卜口の, 静态化是个非常好的办法啊。 :?:

    [回复]

  4. 万戈 :

    猪猪一直很D调,其实一直很牛拜

    [回复]

    油饼小明猪 回复:

    @万戈, 会扭摆我现在就不会这么杯具了.. :(

    [回复]

  5. 美女图片 :

    哇!楼主更新速度真是神马哟!呵呵!差点跟不上评论了了·········

    [回复]

    油饼小明猪 回复:

    @美女图片, :s1shani

    [回复]

  6. wettuy :

    :han 低调的牛掰啊

    [回复]

    油饼小明猪 回复:

    @wettuy, 不在低调中爆发,就在低调中灭亡 :s1fanyanla

    [回复]

  7. 美女图片 :

    嘿嘿·······那么热的天,是挺热的····

    [回复]

  8. 电商圈 :

    重要的字段都是要索引的。。

    [回复]

    油饼小明猪 回复:

    @电商圈, 这个..你是如何判断一个字段他是重要的。文章标题这个字段就很重要,但真的有必要给它加索引嘛?还是得看项目中是否有这个需要的

    [回复]

  9. 关于简单的页面半静态化缓存 - 卜卜口の猫窝 :

    […] 看到了明猪关于mysql执行效率的文章之后,果断的去折腾静态化缓存去了。 […]

  10. 人好哇! :

    我表示,这种测试我一年前就做了 :jing 各种索引很欢乐~~ :pstganbadie
    不过我还有遇到LEFT JOIN两个顺序换一换,时间能查几十万倍的 :||
    于是我果断表示MySQL就是一傲娇,一不小心就跟你闹别扭。。还有,把一个复杂的SQL语句拆成两句,可能执行时间比一句要快几千倍,也有可能会慢几万倍!!! 8O

    [回复]

    油饼小明猪 回复:

    @人好哇!, 这是因为left join的联表结果取决于左边那个表。两个顺序一换,有时候查询出来的结果也不一样的。 :)

    [回复]

    人好哇! 回复:

    @油饼小明猪, 所以玩SQL很欢乐,哈哈哈,真的很欢乐~~ :!:

    [回复]

  11. mm美女 :

    其实一直很牛拜
    :hong

    [回复]

  12. 美女图片 :

    哈哈!我又来关顾咯!

    [回复]

  13. 余姚安装监控 :

    :idea: 学习了

    [回复]

  14. 美女超人 :

    :D :D :D 楼主 你好赞哟

    [回复]

  15. 性感美女写真 :

    = = 我得了“看了代码头痛症”·····

    [回复]

  16. 掌柜的马甲 :

    这个mark一下,看着好熟悉啊…

    [回复]

  17. small2 :

    之前大概浏览了这篇文章没注意看,呃呃,其实呢你那个加不加引号的对比根本原因在于“`time` INT( 11 ) NOT NULL DEFAULT ‘0’,”这个字段本身的定义!你定义为数值查询时候按字符串肯定中间多一步转换处理,所以较慢。另外,INT等数值型定义NOT NULL之后如果默认值不写的话就默认为DEFAULT 0。你这个写法明显是lzj式写法。。。PS:为啥不用TIMESTAME

    [回复]

    small2 回复:

    @small2, PS:为啥不用TIMESTAME???我怎么写这个阿啊啊啊,吧我评论删除了吧:)

    [回复]

    油饼小明猪 回复:

    @small2, 哈哈哈,好一个lzj式的写法。以前一直很赞同你之前的说法,但是之前我发现,其实你多给一个默认值,mysql在你没有给定特定值的时候就少了一步判断,所以定义NOT NULL类型的时候,最好还是给它一个默认值 :idea:

    [回复]

  18. Bee君 :

    乃难得技术文呢,可惜呆对这些是一点都不懂的天然呆呢~~┭┮﹏┭┮

    [回复]

    油饼小明猪 回复:

    @Bee君, 学学去 :(

    [回复]

    Bee君 回复:

    @油饼小明猪, 学来干嘛啊!!!会修电脑有神马用啊!混蛋!

    [回复]

    油饼小明猪 回复:

    @Bee君, 好人修电脑,坏人床上搞 :oops:

    [回复]

  19. tojary :

    装个memcached测试看看吧 :lol:

    [回复]

    油饼小明猪 回复:

    @tojary, 我买的服务器哪有权限啦,只能在自己主机玩玩 :(

    [回复]

  20. zmfish :

    :lol: 表示sql神马都不懂

    [回复]

    油饼小明猪 回复:

    @zmfish, :roll:

    [回复]

  21. mm美女 :

    我又来关顾咯!
    :x

    [回复]

  22. 亦念 :

    10w条就已经会出现压力了……
    100w记录只放一张表不是明智的方式……
    MyISAM的表你都嫌大?!你看看InnoDB的……

    [回复]

    油饼小明猪 回复:

    @亦念, 嫌大倒是没有,只是对于我这4个盘符全部都是红颜色的电脑来说,200M的数据文件我都感觉压力好大。InnoDB自然不必说了,8过在公司基本用的都是myisam表,innodb用的少,以后要向亦念大大学习 :hong

    [回复]

    卜卜口の 回复:

    @油饼小明猪, :oops: 握爪! 全红+1 而且最近还报废个硬盘

    [回复]

  23. mfkof :

    我会MySQL登录语句我自豪 :P !

    [回复]

  24. 某浩 :

    好文章啊!难得有心能测试得这么深入~

    [回复]

  25. 超人 :

    有点儿意思 :lol:

    [回复]

发表评论:

:| :cry: :idea: :o 8) :arrow: :tt :8| :x :hong :( :han :) :P :love :lol: :twisted: :? :evil: :D :dao :mrgreen: ;) :?: :!: :roll: :moe :oops: :|| :jing 8O :pstaisi :pstganbadie :pstwuli :pstheihua more »

 

 


0.20962500572205