Mysql大数据库测试和一些用法

 

1：全表前100W条的所有数据

----------------------------------------------------

[SQL] select * from e_enterprise limit 1000000

 

影响的数据栏: 0

时间: 38.219ms

 

 

2：全表前100W条的id数据

----------------------------------------------------

[SQL] select id from e_enterprise limit 1000000

 

影响的数据栏: 0

时间: 1.187ms

 

 

3：全表前100W条的count(id)数据

----------------------------------------------------

[SQL] select count(id) from e_enterprise limit 1000000

 

影响的数据栏: 0

时间: 0.563ms

 

 

4：全表前100W条的count(*)数据

----------------------------------------------------

[SQL] select count(*) from e_enterprise limit 1000000

 

影响的数据栏: 0

时间: 0.531ms

 

数据实时的不一致，即使数据不变的查询的时间也不一样

 

在印象中count(id)总比count(*)要快的，但是我看了一下测试结果竟然count(*)更快，id是一个主键，怎么会这样呢？

 

下面是测试数据，数据说明了一切

----------------------------------------------------

[SQL] select count(id) from e_enterprise limit 1000000;

影响的数据栏: 0

时间: 0.578ms

 

[SQL] 

select count(*) from e_enterprise limit 1000000;

影响的数据栏: 0

时间: 0.532ms

----------------------------------------------------

[SQL] select count(*) from e_enterprise;

影响的数据栏: 0

时间: 0.532ms

 

[SQL] 

select ROW_COUNT() from e_enterprise;

影响的数据栏: 0

时间: 1.282ms

--------------------------------------

[SQL] select count(1) from e_enterprise limit 1000000;

影响的数据栏: 0

时间: 0.532ms

 

[SQL] 

select count(*) from e_enterprise limit 1000000;

影响的数据栏: 0

时间: 0.531ms

 

测试：500万条数据，表有聚集索引（id+时间字段），还有其它字段 

 

count(*)与count(id)：运行结果一样，速度几乎相同，均为13 " 

count(*)与count(其他非空字段)：非空字段无索引，结果相同，均为13 " 

count(*)与count(其他有空的字段)：有空字段无索引，前者13 "，后者1分多钟 

 

可见： 

若id不为空，count(*)与count(id)效率差不多 

若id有空，count(*)比count(id)记录要多，但效率却要高，原因是count(*)直接统计记录总数，而count(id)还要判断id的值是否为空，因此降低了效率 

 

下面是一些Mysql的用法(可能很少用)

 

1. 取得关于 information_schema的基本信息

查询所有的数据库  show databases;  

2.use information_schema; 

3.查询所有的表show tables;  

4.超过1000行的表 

select concat(table_schema,'.',table_name) as table_name,table_rows  

from information_schema.tables where table_rows > 1000 order by table_rows desc; 

5.没有主键的表

SELECT CONCAT(t.table_name,".",t.table_schema) as table_name  

FROM information_schema.TABLES t  

LEFT JOIN information_schema.TABLE_CONSTRAINTS tc  

ON t.table_schema = tc.table_schema  

AND t.table_name = tc.table_name  

AND tc.constraint_type = 'PRIMARY KEY'  

WHERE tc.constraint_name IS NULL  

AND t.table_type = 'BASE TABLE';   

 

下面是执行多个delete from 表 与 delete from 表 where id in (个数相同) 测试性能比较结果如下

Mysql5.1 window xp下

1：数据一千多左右

 

[SQL] delete  from qa_questions where id  = 100;

影响的数据栏: 0

时间: 0.015ms

 

[SQL] 

delete  from qa_questions where id  = 101;

影响的数据栏: 1

时间: 0.032ms

 

[SQL] 

delete  from qa_questions where id  = 102;

影响的数据栏: 0

时间: 0.015ms

 

[SQL] 

delete  from qa_questions where id  = 103;

影响的数据栏: 1

时间: 0.016ms

 

[SQL] 

delete  from qa_questions where id  = 104;

影响的数据栏: 1

时间: 0.015ms

 

[SQL] 

delete  from qa_questions where id  = 105;

影响的数据栏: 1

时间: 0.015ms

 

[SQL] 

delete  from qa_questions where id  = 106;

影响的数据栏: 1

时间: 0.016ms

 

[SQL] 

delete  from qa_questions where id  = 107;

影响的数据栏: 1

时间: 0.016ms

 

[SQL] 

delete  from qa_questions where id  = 108;

影响的数据栏: 1

时间: 0.015ms

 

[SQL] 

delete  from qa_questions where id  = 109;

影响的数据栏: 1

时间: 0.015ms

 

[SQL] 

delete  from qa_questions where id in(

200,201,202,203,204,205,206,207,208,209

)

 

 

影响的数据栏: 9

时间: 0.016ms

分析：执行一条的where id = 与where id in(xx,xx,xx,.......) 时间差 千分之一

2：数据量32万左右

[SQL] delete  from e_template where id  = 100;

影响的数据栏: 1

时间: 0.078ms

 

[SQL] 

delete  from e_template where id  = 101;

影响的数据栏: 1

时间: 0.031ms

 

[SQL] 

delete  from e_template where id  = 102;

影响的数据栏: 1

时间: 0.016ms

 

[SQL] 

delete  from e_template where id  = 103;

影响的数据栏: 1

时间: 0.032ms

 

[SQL] 

delete  from e_template where id  = 104;

影响的数据栏: 1

时间: 0.016ms

 

[SQL] 

delete  from e_template where id  = 105;

影响的数据栏: 1

时间: 0.031ms

 

[SQL] 

delete  from e_template where id  = 106;

影响的数据栏: 1

时间: 0.016ms

 

[SQL] 

delete  from e_template where id  = 107;

影响的数据栏: 1

时间: 0.000ms

 

[SQL] 

delete  from e_template where id  = 108;

影响的数据栏: 1

时间: 0.032ms

 

[SQL] 

delete  from e_template where id  = 109;

影响的数据栏: 1

时间: 0.000ms

 

[SQL] 

delete  from e_template where id in(

200,201,202,203,204,205,206,207,208,209

)

 

影响的数据栏: 10

时间: 0.016ms

 

分析：从三十多万的数据可以看到  id  = 100;时间: 0.078ms 而 where id in(

200,201,202,203,204,205,206,207,208,209) 只用了 0.016ms，此时有的where id  = 是where id in(xx,xx,xx,....)消耗时间的4倍多

所有的删除加起来执行效率id=是in的  78+31+16+32+16+31+16+0+32+0/ 16倍，id=消耗的时间大约是where in的15倍多时间

3：我感觉数据量越大1000万以上越能体现 where id in 的优势。数据量大id = 反而没有id in(xx,xx,xx,...)删除多个性能好了。(其实数据1000多和三十二万的效率来讲 id = 差不多都是 id in的十几倍耗费时间，三十二万这种差距相比数据量1k又大多了。)