- 论坛徽章:
- 1
|
本帖最后由 yakczh_cu 于 2013-11-18 11:51 编辑
测试环境
Intel(R) Core(TM) i3-3210 CPU @ 3.20GHz
DDR3 mem 4g
ST500DM002-1BD142 500g
xp sp3
mysqld 5.6.13
建表- CREATE TABLE `test1` (
- `id` bigint(20) NOT NULL AUTO_INCREMENT,
- `subject` varchar(200) NOT NULL,
- `content` text NOT NULL,
- `ctime` int(10) NOT NULL,
- PRIMARY KEY (`id`)
- ) ENGINE=MyISAM DEFAULT CHARSET=utf8
复制代码 生成100w测试数据- <?php
- $id=1;
- $flag=1;
- $db=mysqli_connect("localhost:3306",'root','','bench');
-
-
- while( $id < 100 * 10000 ){
- $rand=rand(10000,99999);
-
- $now=time();
- $sql = "insert into test1 (id,subject,content,ctime) values($id,'测试标题_$rand','测试内容_$rand',$now);";
- if($flag){
- mysqli_query($db,'set names utf8');
- $flag=0;
- }
- //echo $sql;
- mysqli_query($db,$sql);
- $id++;
- }
-
- mysqli_close($db);
复制代码 用like 查询- <?php
-
- $start=microtime(true);
- $db=mysqli_connect("localhost:3306",'root','','bench');
- mysqli_query($db,'SET NAMES UTF8');
- $rand=rand(10000,99999);
- $sql="SELECT * from test1 where subject like '%$rand';";
- echo $sql;
- if ($result = mysqli_query($db,$sql)) {
- printf("Select returned %d rows.\n", $result->num_rows);
- //print_r($result);
- while($row= mysqli_fetch_array($result)){
- print_r($row['content']);
- }
- /* free result set */
- $result->close();
- }
- echo "\n elapse ";
- echo microtime(true)-$start;
复制代码 测试结果
在200万条数据以内,查询时间在1s以内
然后以200w以步长增加数据,查询时间基本上是线性增长
200w 1.03s
400w 2.34s
600w 3.25s
800w 4.02s
1000w 4.76s
|
|