版本

PostgreSQL

1
2
3
4
5
6
7
8
[local]:5432 sky@sky=# select version();
                                                  version                                                  
-----------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6beta4 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.3) 4.8.4, 64-bit
(1 row)
Time: 0.407 ms
[local]:5432 sky@sky=#

MySQL

1
2
3
4
5
6
7
8
9
10
mysql> select version();
+-------------------------------------+
| version()                           |
+-------------------------------------+
| 5.7.12-1~exp1+deb.sury.org~trusty+1 |
+-------------------------------------+
1 row in set (0.00 sec)
mysql>

测试表

PostgreSQL:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
[local]:5432 sky@sky=# \d wb_status;
                                           Table "public.wb_status"
         Column         |            Type             |                       Modifiers                        
------------------------+-----------------------------+--------------------------------------------------------
 id                     | integer                     | not null default nextval('wb_status_id_seq'::regclass)
 sid                    | bigint                      | not null default '0'::bigint
 idstr                  | character varying(64)       | not null default ''::character varying
 mid                    | bigint                      | default '0'::bigint
 user_id                | bigint                      | 
 user_screen_name       | character varying(32)       | not null default ''::character varying
 user_profile_image_url | character varying(128)      | not null default ''::character varying
 text                   | character varying(512)      | not null default ''::character varying
 source                 | character varying(256)      | not null default ''::character varying
 thumbnail_pic          | character varying(256)      | not null default ''::character varying
 bmiddle_pic            | character varying(256)      | not null default ''::character varying
 original_pic           | character varying(256)      | not null default ''::character varying
 retweeted_status_id    | character varying(32)       | not null default ''::character varying
 geo                    | character varying(256)      | default ''::character varying
 reposts_count          | integer                     | default 0
 comments_count         | integer                     | default 0
 attitudes_count        | integer                     | default 0
 visible                | character varying(64)       | not null default ''::character varying
 pic_urls               | character varying(1024)     | not null default ''::character varying
 create_at              | timestamp without time zone | not null default now()
 update_at              | timestamp without time zone | not null default now()
 ad                     | character varying(256)      | not null default ''::character varying
 is_deleted             | smallint                    | not null default '0'::smallint
[local]:5432 sky@sky=# SELECT count(1) from wb_status;
  count  
---------
 1370996
(1 row)
Time: 84.686 ms

MySQL:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
mysql> desc wb_status;
+------------------------+---------------+------+-----+---------------------+-----------------------------+
| Field                  | Type          | Null | Key | Default             | Extra                       |
+------------------------+---------------+------+-----+---------------------+-----------------------------+
| id                     | int(11)       | NO   | PRI | NULL                | auto_increment              |
| sid                    | bigint(20)    | NO   | UNI | 0                   |                             |
| idstr                  | varchar(64)   | NO   |     |                     |                             |
| mid                    | bigint(20)    | YES  |     | 0                   |                             |
| user_id                | bigint(20)    | YES  | MUL | NULL                |                             |
| user_screen_name       | varchar(64)   | NO   |     |                     |                             |
| user_profile_image_url | varchar(128)  | NO   |     |                     |                             |
| text                   | varchar(512)  | NO   | MUL |                     |                             |
| source                 | varchar(256)  | NO   |     |                     |                             |
| thumbnail_pic          | varchar(256)  | NO   |     |                     |                             |
| bmiddle_pic            | varchar(256)  | NO   |     |                     |                             |
| original_pic           | varchar(256)  | NO   |     |                     |                             |
| retweeted_status_id    | varchar(32)   | NO   |     |                     |                             |
| geo                    | varchar(256)  | YES  |     |                     |                             |
| reposts_count          | int(11)       | YES  |     | 0                   |                             |
| comments_count         | int(11)       | YES  |     | 0                   |                             |
| attitudes_count        | int(11)       | YES  |     | 0                   |                             |
| visible                | varchar(64)   | NO   |     |                     |                             |
| pic_urls               | varchar(1024) | NO   |     |                     |                             |
| create_at              | timestamp     | NO   |     | 1998-07-01 00:00:00 |                             |
| update_at              | timestamp     | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| ad                     | varchar(256)  | NO   |     |                     |                             |
| is_deleted             | tinyint(4)    | NO   |     | 0                   |                             |
mysql> select count(1) from wb_status;
+----------+
| count(1) |
+----------+
|  1370996 |
+----------+
1 row in set (0.44 sec)
mysql>

Alter table add column

不带默认值,不带not null

PostgreSQL

1
2
3
[local]:5432 sky@sky=# ALTER TABLE wb_status add column iii_id int;
ALTER TABLE
Time: 1.599 ms

MySQL

1
2
3
mysql> alter table wb_status add column iii_id int;
Query OK, 1370996 rows affected (14 min 7.60 sec)
Records: 1370996  Duplicates: 0  Warnings: 0

alter table add column not null default 0

PostgreSQL

1
2
3
[local]:5432 sky@sky=# ALTER TABLE wb_status add column ddd_id int not null default 0;
ALTER TABLE
Time: 2223.007 ms

MySQL

1
2
3
4
5
6
mysql> alter table wb_status add column eee_id int not null default 0;
Query OK, 1370996 rows affected (12 min 31.77 sec)
Records: 1370996  Duplicates: 0  Warnings: 0
mysql>

结论

alter table xx add column如果不加default默认值，则PG是最快的。只需要在字典里添加一条数据，而对MySQL来说无论有没有，它都要重写整个表及数据.

不过，即使有not null default，PostgreSQL相对于MySQL来说，也是很快的.