MySQL VS PostgreSQL 的alter table
Contents
版本
PostgreSQL
[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
mysql> select version();
+-------------------------------------+
| version() |
+-------------------------------------+
| 5.7.12-1~exp1+deb.sury.org~trusty+1 |
+-------------------------------------+
1 row in set (0.00 sec)
mysql>
测试表
PostgreSQL:
[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:
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
[local]:5432 sky@sky=# ALTER TABLE wb_status add column iii_id int;
ALTER TABLE
Time: 1.599 ms
MySQL
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
[local]:5432 sky@sky=# ALTER TABLE wb_status add column ddd_id int not null default 0;
ALTER TABLE
Time: 2223.007 ms
MySQL
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来说，也是很快的.