建立测试数据表

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个人同时访问某个页面而同时运行了这条简单的语句，结果会怎么样咩？

这里我又做了另一个测试，注意跟上面那条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

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

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！

体会到了索引带来的好处了吧，不过话说我突然想起来，跑去看了一下这张表占的磁盘容量，泪流满面我没找到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：这篇文章是今晚下班之后再公司搞出来的哦，昨天叫房东今晚过来收房租，我却留在公司玩，悲剧，刚刚房东打我电话问我什么时候会到的时候害我一直道歉。房东一直说没事。我继续道歉，房东继续****

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

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