- 论坛徽章:
- 27
|
回复 52# 小版主杀手
mysiam的插入要比innodb快很多,但是innodb的删除却比myisam块- mysql> INSERT INTO dns.domain_tmp(begin_ip,end_ip,pool_id,region_id) SELECT distinct c.begin_ip,c.end_ip,t.pool_id,t.region_id FROM dns.tid365 as ti inner join topology as t on ti.tid=t.id inner join cidr as c on t.region_id=c.region_id order by t.weight asc
- -> ;
- Query OK, 182789 rows affected (10.19 sec)
- Records: 182789 Duplicates: 0 Warnings: 0
- --innodb
- mysql> INSERT INTO dns.domain_tmp(begin_ip,end_ip,pool_id,region_id) SELECT distinct c.begin_ip,c.end_ip,t.pool_id,t.region_id FROM dns.tid364 as ti inner join topology as t on ti.tid=t.id inner join cidr as c on t.region_id=c.region_id order by t.weight desc;
- Query OK, 182789 rows affected (9.02 sec)
- Records: 182789 Duplicates: 0 Warnings: 0
- mysql> call deal_domain();Query OK, 0 rows affected (7.89 sec)
- --myisam
- mysql> INSERT INTO dns.domain_tmp(begin_ip,end_ip,pool_id,region_id) SELECT distinct c.begin_ip,c.end_ip,t.pool_id,t.region_id FROM dns.tid364 as ti inner join topology as t on ti.tid=t.id inner join cidr as c on t.region_id=c.region_id order by t.weight desc;
- Query OK, 182789 rows affected (2.71 sec)
- Records: 182789 Duplicates: 0 Warnings: 0
- mysql> call deal_domain();
- Query OK, 0 rows affected (8.60 sec)
- --innodb
- mysql> INSERT INTO dns.domain_tmp(begin_ip,end_ip,pool_id,region_id) SELECT distinct c.begin_ip,c.end_ip,t.pool_id,t.region_id FROM dns.tid364 as ti inner join topology as t on ti.tid=t.id inner join cidr as c on t.region_id=c.region_id order by t.weight desc;
- Query OK, 182789 rows affected (10.46 sec)
- Records: 182789 Duplicates: 0 Warnings: 0
- mysql> call deal_domain();Query OK, 0 rows affected (6.70 sec)
复制代码 我在博客里还有一些关于myisam和innodb的对比测试
http://blog.chinaunix.net/uid-11121450-id-98144.html
http://blog.chinaunix.net/uid-11121450-id-98146.html
http://blog.chinaunix.net/uid-11121450-id-98140.html
但是在实际过程中,数据量在百万左右的表,无论用什么引擎,若是sql语句是随机变化的话(这样每次mysql都会重新解析sql语句和不命中缓存的),并发查询效率也就不到100个每秒。
另外对应sql语句随机变化,而数据量少于1万的查询的话,建索引反而并发效率不如不建索引 |
评分
-
查看全部评分
|