657   MySQL

一,在InnoDB引擎上测试
1,检查表格引擎,这里是InnoDB

mysql>  show table status from gd where name = 'app_channel_code_info';
+-----------------------+--------+---------+------------+--------+----------------+-------------+
| Name                  | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | 
+-----------------------+--------+---------+------------+--------+----------------+-------------+
| app_channel_code_info | InnoDB |      10 | Compact    | 163371 |            343 |    56180736 |               
+-----------------------+--------+---------+------------+--------+----------------+-------------+
1 row in set (0.19 sec)

2,不加条件进行全表count(*),用时0.93秒

mysql> SELECT count(*) FROM `app_channel_code_info`;
+----------+
| count(*) |
+----------+
|   169666 |
+----------+
1 row in set (0.93 sec)

3,添加条件进行count(*),用时0.20秒

mysql> SELECT count(*) FROM `app_channel_code_info` where update_time > '20120427172452';
+----------+
| count(*) |
+----------+
|   169628 |
+----------+
1 row in set (0.20 sec)

 

 

二,在MyISAM上测试
1,更改表格引擎,并检查,这里已经改为MyISAM

mysql> alter table `app_channel_code_info` engine=MyISAM;
Query OK, 169666 rows affected (15.49 sec)
Records: 169666  Duplicates: 0  Warnings: 0

mysql> show table status from gd where name = 'app_channel_code_info';
+-----------------------+--------+---------+------------+--------+----------------+-------------+
| Name                  | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | 
+-----------------------+--------+---------+------------+--------+----------------+-------------+
| app_channel_code_info | MyISAM |      10 | Dynamic    | 169666 |            171 |    29058532 | 
+-----------------------+--------+---------+------------+--------+----------------+-------------+
1 row in set (0.01 sec)

2,不加条件进行count(*),用时0.01秒

mysql> SELECT count(*) FROM `app_channel_code_info`;
+----------+
| count(*) |
+----------+
|   169666 |
+----------+
1 row in set (0.01 sec)

3,添加条件进行count(*),用时0.22秒;

mysql> SELECT count(*) FROM `app_channel_code_info` where update_time > '20120427172452';
+----------+
| count(*) |
+----------+
|   169628 |
+----------+
1 row in set (0.22 sec)

 

 

三,结论:
1,不加条件进行全表count(*),MyISAM引擎用时0.01秒,而InnoDB用时0.93秒,前者明显优于后者;这是因为InnoDB会进行全表扫描;
2,添加条件进行count(*),MyISAM用时0.22秒,InnoDB用时0.20,效率相当;




Leave a Reply

Your email address will not be published. Required fields are marked *