MySQL数据库之集合类型SET的DDL变更测试总结 -- MySQL -- IT技术博客大学习 -- 共学习共进步！

您现在的位置：首页 --> MySQL --> MySQL数据库之集合类型SET的DDL变更测试总结

MySQL数据库之集合类型SET的DDL变更测试总结

浏览:1507次出处信息

【导读】

针对四种数据类型：布尔类型BOOL或称布尔类型BOOLEAN、微整型TINYTINT、枚举类型ENUM、集合类型SET，我们已经分多篇文章篇幅给出详细的介绍与功能测试数据，接下来我们深入介绍枚举类型EUNM和集合类型SET。测试基于InnoDB存储引擎上，对MySQL数据库集合类型SET的字段进行DDL变更操作，是否需要重新创建表呢？对数据库的事务处理有何影响？对数据库的数据服务提供有何性能影响？

(一) 系统环境

硬件：DELL R510 10块盘做的RAID5，上面跑了几十个虚拟机

操作系统：CentOS release 5.5 (Final)

MySQL数据库：5.5.15-log

InnoDB存储引擎：plugin-InnoDB 1.1.8

(二) 测试数据准备

鉴于测试数据准备需要用到MySQL函数和MySQL存储过程，SQL代码段需要占据文章大量的篇幅，不利于读者阅读关于集合类型SET字段上的DDL变更测试，此部分内容单整理成一篇文章MySQL数据库之数据类型集合类型和枚举类型测试环境。

(三) 集合类型SET字段DDL变更

a) 去掉集合类型字段定义的默认值属性

root@localhost : mysqlops 03:01:38> ALTER TABLE mysqlops_set_enum MODIFY Work_City  SET('shanghai','beijing','hangzhou','shenzhen','guangzhou','xiamen','tianjin','qingdao','dalian','xian','other') NOT NULL;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

小结：

MySQL数据库集合类型字段定义属性为制定了非NULL且有缺省的默认值，对其进行DDL变更，单独去掉缺省的默认值属性，不会出现锁表、重建表等操作，这符合MySQL数据库表DDL变更的特性。

b) 去掉集合类型字段定义的NOT NULL 属性

root@localhost : mysqlops 03:04:48> ALTER TABLE mysqlops_set_enum MODIFY Work_City  SET('shanghai','beijing','hangzhou','shenzhen','guangzhou','xiamen','tianjin','qingdao','dalian','xian','other');
Query OK, 20017251 rows affected (2 min 4.40 sec)
Records: 20017251  Duplicates: 0  Warnings: 0

小结：

MySQL数据库表字段的非NULL属性，修改为默认NULL属性，则会导致锁表与表重建，对于MySQL数据库集合类型字段也同样存在此问题。

c) 增加集合类型字段定义的默认值

root@localhost : mysqlops 03:07:04> ALTER TABLE mysqlops_set_enum MODIFY Work_City  SET('shanghai','beijing','hangzhou','shenzhen','guangzhou','xiamen','tianjin','qingdao','dalian','xian','other') NOT NULL DEFAULT 'shanghai';
Query OK, 20017251 rows affected (2 min 4.79 sec)
Records: 20017251  Duplicates: 0  Warnings: 0

小结：

MySQL数据库表字段属性为NULL，修改为非NULL且有指定的缺省默认值，集合类型字段的DDL变更，也会导致锁表、重新创建等。

d) 修改集合类型字段定义的默认值属性

root@localhost : mysqlops 03:11:44> ALTER TABLE mysqlops_set_enum MODIFY Work_City  SET('shanghai','beijing','hangzhou','shenzhen','guangzhou','xiamen','tianjin','qingdao','dalian','xian','other') NOT NULL DEFAULT 'beijing';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

小结：

对MySQL数据库表集合类型字段属性为非NULL且有缺省的默认值，仅仅是修改集合类型字段缺省的默认，MySQL数据库不会出现锁表等情况。

e) 修改集合类型字段定义的默认值，且新默认值不在集合列表中

root@localhost : mysqlops 03:12:42> ALTER TABLE mysqlops_set_enum MODIFY Work_City  SET('shanghai','beijing','hangzhou','shenzhen','guangzhou','xiamen','tianjin','qingdao','dalian','xian','other') NOT NULL DEFAULT 'suzhou';
ERROR 1067 (42000): Invalid default value for 'Work_City'

小结：

修改MySQL数据库集合类型字段缺省的默认值，为集合类型字段值域列表中不存在的集合元素，MySQL数据库回报错，并且集合类型字段的DDL变更SQL语句执行失败。

f) 修改集合类型字段定义，尾部追加集合元素

root@localhost : mysqlops 03:12:52> ALTER TABLE mysqlops_set_enum MODIFY Work_City  SET('shanghai','beijing','hangzhou','shenzhen','guangzhou','xiamen','tianjin','qingdao','dalian','xian','other','nanchang') NOT NULL DEFAULT 'xian';
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

小结：

修改MySQL数据库集合类型字段的值域列表，为值域列表增加集合元素，以值域列表尾部追加的方式，这类DDL变更，不会导致MySQL数据库出现锁表等情况。

g) 修改集合类型字段定义，调整集合元素的顺序

root@localhost : mysqlops 02:57:42> SELECT * FROM mysqlops_set_enum WHERE Work_City=4 LIMIT 1;
+----+-------------+-----------+
| ID | Work_Option | Work_City |
+----+-------------+-----------+
| 44 |             | hangzhou  |
+----+-------------+-----------+

1 row in set (0.00 sec)

root@localhost : mysqlops 03:13:03> ALTER TABLE mysqlops_set_enum MODIFY Work_City  SET('shanghai','beijing','xiamen','hangzhou','shenzhen','guangzhou','tianjin','qingdao','dalian','xian','other','nanchang') NOT NULL DEFAULT 'xian';
Query OK, 20017251 rows affected (2 min 20.34 sec)
Records: 20017251  Duplicates: 0  Warnings: 0

备注：

DDL变更调整了集合元素 ‘xiamen’ 在集合元素值域列表中的位置，由原来存储序号32，调整为新的存储序号4，而涉及对比的集合元素 ‘hangzhou’ 由存储序号4，调整为新的存储序号8。

root@localhost : mysqlops 03:14:10> SELECT * FROM mysqlops_set_enum WHERE  Work_City=4 LIMIT 1;
+----+-------------+-----------+
| ID | Work_Option | Work_City |
+----+-------------+-----------+
| 12 |             | xiamen    |
+----+-------------+-----------+
1 row in set (0.00 sec)

root@localhost : mysqlops 03:15:05> SELECT * FROM mysqlops_set_enum WHERE ID=44;
+----+-------------+-----------+
| ID | Work_Option | Work_City |
+----+-------------+-----------+
| 44 |             | hangzhou  |
+----+-------------+-----------+
1 row in set (0.00 sec)

root@localhost : mysqlops 03:16:14> SELECT * FROM mysqlops_set_enum WHERE ID=44 and Work_City=4;
Empty set (0.00 sec)

root@localhost : mysqlops 03:17:18> SELECT * FROM mysqlops_set_enum WHERE ID=44 and Work_City=8;
+----+-------------+-----------+
| ID | Work_Option | Work_City |
+----+-------------+-----------+
| 44 |             | hangzhou  |
+----+-------------+-----------+
1 row in set (0.00 sec)

root@localhost : mysqlops 03:18:28> SELECT * FROM mysqlops_set_enum WHERE  Work_City=1 LIMIT 1;
+----+-------------+-----------+
| ID | Work_Option | Work_City |
+----+-------------+-----------+
|  1 |             | shanghai  |
+----+-------------+-----------+
1 row in set (0.00 sec)

小结：

我们事先随机显示了一条集合元素等于“hangzhou”的记录，自增序列唯一主键值为：44，方便我们对比MySQL数据库集合类型字段DDL变更之后的数据变化，总结如下：

l MySQL数据库集合类型字段的定义属性的集合元素顺序调整，会导致MySQL数据库申请表级锁，并且锁表、创建临时文件等操作；

l 被DDL变更调整顺序的集合元素，其在MySQL数据库集合类型字段属性存储序号发生变化；

l MySQL数据库集合类型字段的定义属性的集合元素顺序调整，导致受其影响的集合元素，字段定义属性中存储顺序都发生变化，数据库表对应的记录值的存储序号也发生变化；

l MySQL数据库集合类型字段的定义属性的某个集合元素顺序调整，对其他存储序号未改变的集合元素，对其对应表字段存储的数据序号编号也未发生变化；

h) 修改集合类型字段定义，删除某个集合元素

删除集合元素”shenzhen”之前的数据库表数据部分参照信息

root@localhost : mysqlops 02:07:28> SELECT COUNT(*) FROM mysqlops_set_enum WHERE Work_city='';
+----------+
| COUNT(*) |
+----------+
|   200559 |
+----------+

root@localhost : mysqlops 02:20:39> SELECT COUNT(*) FROM  mysqlops_set_enum WHERE Work_City='shenzhen';
+----------+
| COUNT(*) |
+----------+
|   600160 |
+----------+
1 row in set (7.82 sec)

root@localhost : mysqlops 02:25:00> SELECT * FROM mysqlops_set_enum WHERE Work_city='shenzhen' limit 1;
+-----+-------------+-----------+
| ID  | Work_Option | Work_City |
+-----+-------------+-----------+
| 117 |             | shenzhen  |
+-----+-------------+-----------+
1 row in set (0.00 sec)

执行删除集合元素值“shenzhen”的DDL变更

root@localhost : mysqlops 03:16:05> ALTER TABLE mysqlops_set_enum MODIFY Work_City  SET('shanghai','beijing','xiamen','hangzhou','guangzhou','tianjin','qingdao','dalian','xian','other','nanchang') NOT NULL DEFAULT 'xian';
Query OK, 20017251 rows affected, 65535 warnings (2 min 22.09 sec)
Records: 20017251  Duplicates: 0  Warnings: 3801649

root@localhost : mysqlops 03:18:48> SHOW WARNINGS;
+---------+------+--------------------------------------------------+
| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning | 1265 | Data truncated for column 'Work_City' at row 11  |
| Warning | 1265 | Data truncated for column 'Work_City' at row 19  |
| Warning | 1265 | Data truncated for column 'Work_City' at row 34  |
| Warning | 1265 | Data truncated for column 'Work_City' at row 41  |
| Warning | 1265 | Data truncated for column 'Work_City' at row 47  |
| Warning | 1265 | Data truncated for column 'Work_City' at row 50  |
| Warning | 1265 | Data truncated for column 'Work_City' at row 62  |
| Warning | 1265 | Data truncated for column 'Work_City' at row 69  |
| Warning | 1265 | Data truncated for column 'Work_City' at row 76  |
| Warning | 1265 | Data truncated for column 'Work_City' at row 82  |

比对删除集合元素”shenzhen”之后的数据库表数据变化

root@localhost : mysqlops 03:19:14> SELECT COUNT(*) FROM mysqlops_set_enum WHERE Work_city='';
+----------+
| COUNT(*) |
+----------+
|   800719 |
+----------+
1 row in set (6.29 sec)

root@localhost : mysqlops 03:24:09> SELECT * FROM mysqlops_set_enum WHERE ID=117;
+-----+-------------+-----------+
| ID  | Work_Option | Work_City |
+-----+-------------+-----------+
| 117 |             |           |
+-----+-------------+-----------+
1 row in set (0.00 sec)

小结：

DDL变更SQL语句删除MySQL数据库集合类型字段定义属性的某个集合元素，且该集合元素有对应的数据行存储于数据库表中，将会导致：

l MySQL数据库表需要表级别锁，进行锁住表、重建表等操作；

l 被删除集合元素，对应的数据库表记得行字段的值，将会出现截断，以空字符串替代；

l 被删除集合元素之后的集合元素存储序号发生变化；

l 数据库表中集合类型字段的数据值，为保证对应集合类型字段定义属性中的集合元素存储序号，也会做相应的调整；

(四) 基于集合类型SET字段的索引

a) 完成DDL变更之后的数据库表结构

root@localhost : mysqlops 03:25:42> SHOW CREATE TABLE mysqlops_set_enum\\G
*************************** 1. row ***************************
Table: mysqlops_set_enum
Create Table: CREATE TABLE `mysqlops_set_enum` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Work_Option` enum('JavaScript','DBA','SA','C++','NA','QA','Java','other','','Python') NOT NULL DEFAULT 'DBA',
`Work_City` set('shanghai','beijing','xiamen','hangzhou','guangzhou','tianjin','qingdao','dalian','xian','other','nanchang') NOT NULL DEFAULT 'xian',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=20017252 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

b) MySQL数据库集合类型字段无创建索引时，SQL语句执行计划

root@localhost : mysqlops 03:34:59> EXPLAIN SELECT * FROM mysqlops_set_enum WHERE Work_City=8 LIMIT 1\\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: mysqlops_set_enum
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 20017710
Extra: Using where
1 row in set (0.00 sec)

小结：

MySQL数据库表集合类型字段没有创建索引时，集合类型字段作为数据查找条件，完全符合MySQL数据库使用索引的规则，依然是全表扫描，说明集合类型字段定义属性的内部存储序列编号，不能起到数据库索引的功效。

c) MySQL数据库集合类型字段创建索引

root@localhost : mysqlops 03:35:44> ALTER TABLE mysqlops_set_enum ADD INDEX idx_work_city_set(Work_City);
Query OK, 0 rows affected (1 min 32.40 sec)
Records: 0  Duplicates: 0  Warnings: 0

小结：

为MySQL数据库表集合类型字段创建索引，与文章MySQL 5.5版本对普通索引增删性能的优化描述一样，需要锁表、重新创建表等操作，集合类型字段创建索引也同样需要这样做。

d) MySQL数据库集合类型字段有索引时，SQL语句执行计划

root@localhost : mysqlops 03:38:04> EXPLAIN SELECT * FROM mysqlops_set_enum WHERE Work_City=8 LIMIT 1\\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: mysqlops_set_enum
type: ref
possible_keys: idx_work_city_set
key: idx_work_city_set
key_len: 2
ref: const
rows: 2024666
Extra: Using where
1 row in set (0.00 sec)

小结：

MySQL数据库表集合类型字段创建数据库索引之后，同样的数据查找SQL语句，则会根据索引条件查找数据，而不是全表扫描。

e) MySQL数据库集合类型字段上的索引删除

root@localhost : mysqlops 03:44:59> ALTER TABLE mysqlops_set_enum DROP INDEX idx_work_city_set;
Query OK, 0 rows affected (0.82 sec)
Records: 0  Duplicates: 0  Warnings: 0

小结：

MySQL数据库表集合类型字段上的索引删除DDL变更，同样会导致MySQL数据库使用表级锁，锁表、重新创建表等一系列操作。

(五) 总结

对MySQL数据库集合类型的字段定义属性，进行了定义属性默认值去除、NOT NULL 修改为NULL 、默认为NULL修改为NOT NULL和缺省默认值、修改缺省的默认值、尾部追加新的集合元素、调整集合元素的位置、为集合元素字段创建索引、观察数据查找SQL语句执行计划、删除索引等一系列的DDL操作，以及观察数据库表中的集合数据变化，我们得出下列结论：

a) 集合类型字段的DDL变更，对MySQL数据库的影响基本上与其他数据类型字段的DDL变更类似；

b) 集合类型字段的集合元素顺序调整，会导致受影响的集合元素存储编号调整，对应的数据库表中的数据编号也会被修改；

c) 集合类型字段的集合元素顺序调整，不会跟枚举类型字段一样，出现数据库表数据对照关系的紊乱；

d) 集合类型字段的集合元素与表存储的顺序编号之间的对照关系，无法起到索引查找数据的功能。需要时，推荐为数据库表集合类型字段创建索引；

e) 鉴于集合类型字段的集合元素顺序调整，会导致数据库表锁住等操作，建议不要轻易调整集合元素的顺序；

f) 集合类型字段的集合元素增加，最好尾部追加的方式增加，否则需要锁表等一系列操作，影响数据库表的事务处理；

g) 删除集合类型字段定义的某个集合元素，会导致数据库表中对应的数据行发生截断行为，并且用空字符串替代；

h) 集合类型字符定义属性，最好申明为NOT NULL，且有缺省的默认值数据；

数据类型系列讲解文章列表：

MySQL数据库数据类型之ENUM、SET、BOOL/BOOLEAN、TINYINT

MySQL数据库之数据类型BOOL/BOOLEAN与TINYINT测试总结

MySQL数据库数据类型之枚举类型ENUM测试总结