导读
本文深入介绍枚举类型EUNM和集合类型SET。测试基于InnoDB存储引擎上,对MySQL数据库枚举类型ENUM的字段进行DDL变更操作,是否需要重新创建表呢?对数据库的事务处理有何影响?对数据库的数据服务提供有何性能影响?通过本文了解下。
(一) 系统环境
硬件:DELL R510 10块盘做的RAID5,上面跑了几十个虚拟机
操作系统:CentOS release 5.5 (Final)
MySQL数据库:5.5.15-log
InnoDB存储引擎:plugin-InnoDB 1.1.8
(二) 测试数据准备
鉴于篇幅的缘由,单独作为一篇文章分享在www.mysqlops.com网站上,请点击MySQL数据库之数据类型集合类型和枚举类型测试环境
(三) 枚举类型ENUM字段DDL操作
a. 增加枚举类型字段定义的默认值属性
root@localhost : mysqlops 02:35:51> ALTER TABLE mysqlops_set_enum MODIFY Work_Option enum(‘DBA’,'SA’,'C++’,'JavaScript’,'NA’,'QA’,'Java’,'PHP’,'other’,”) NOT NULL DEFAULT ‘DBA’;Query OK, 20017251 rows affected (2 min 7.76 sec)Records: 20017251 Duplicates: 0 Warnings: 0
小结:
枚举类型字段,由允许NULL值且无默认值的定义属性,变更为不允许存储NULL值和制定枚举类型字段的默认值,这个过程需要表级锁,锁住表堵塞其他事务性操作,与其他数据类型的字段属性变更是一样的。
b. 修改枚举类型字段定义的默认值
root@localhost : mysqlops 02:38:19> ALTER TABLE mysqlops_set_enum MODIFY Work_Option enum(‘DBA’,'SA’,'C++’,'JavaScript’,'NA’,'QA’,'Java’,'PHP’,'other’,”) NOT NULL DEFAULT ”;Query OK, 0 rows affected (0.00 sec)Records: 0 Duplicates: 0 Warnings: 0
小结:
枚举类型字段已经存在默认值,只是修改默认值为不同默认值的操作,是不需要重新建表与锁表,也是与其他数据类型字段的字段属性变更一样。
c. 修改枚举类型字段定义的默认值,且新默认值不在枚举列表中
root@localhost : mysqlops 02:39:15> ALTER TABLE mysqlops_set_enum MODIFY Work_Option enum(‘DBA’,'SA’,'C++’,'JavaScript’,'NA’,'QA’,'Java’,'other’,”) NOT NULL DEFAULT ‘iphone’;ERROR 1067 (42000): Invalid default value for ‘Work_Option’
小结:
若是给枚举类型字段指定的默认值,没有在枚举类型值域列表中出现,则会出现SQL语法错误,导致SQL语句执行失败。
d. 修改枚举类型字段定义,尾部追加枚举元素
root@localhost : mysqlops 02:39:27> ALTER TABLE mysqlops_set_enum MODIFY Work_Option enum(‘DBA’,'SA’,'C++’,'JavaScript’,'NA’,'QA’,'Java’,'PHP’,'other’,”,’Python’) NOT NULL DEFAULT ‘DBA’;Query OK, 0 rows affected (0.00 sec)Records: 0 Duplicates: 0 Warnings: 0
小结:
为枚举类型字段的值域列表增加枚举元素,若是尾部追加的方式,则不需要表级锁,可以非常快就可以完成DDL变更。
e. 修改枚举类型字段定义,调整枚举元素的顺序
root@localhost : mysqlops 02:38:10> SELECT * FROM mysqlops_set_enum WHERE Work_Option=1 LIMIT 1; +———-+————-+——————-+ | ID | Work_Option | Work_City | +———-+————-+——————-+ | 12017252 | DBA | guangzhou,tianjin | +———-+————-+——————-+ 1 row in set (3.22 sec)root@localhost : mysqlops 02:39:39> ALTER TABLE mysqlops_set_enum MODIFY Work_Option enum(‘JavaScript’,'DBA’,'SA’,'C++’,'NA’,'QA’,'Java’,'PHP’,'other’,”,’Python’) NOT NULL DEFAULT ‘DBA’; Query OK, 20017251 rows affected (2 min 10.75 sec) Records: 20017251 Duplicates: 0 Warnings: 0 root@localhost : mysqlops 02:42:01> SELECT * FROM mysqlops_set_enum WHERE Work_Option=1 LIMIT 1; +———-+————-+——————-+ | ID | Work_Option | Work_City | +———-+————-+——————-+ | 12017252 | JavaScript | guangzhou,tianjin | +———-+————-+——————-+ 1 row in set (3.22 sec)
小结:
对枚举类型字段的值域列表元素顺序进行调整,会发现:
f. 修改枚举类型字段定义,删除某个枚举元素
root@localhost : mysqlops 02:05:24> SELECT COUNT(*) FROM mysqlops_set_enum WHERE Work_Option=”; +———-+ | COUNT(*) | +———-+ | 4017251 | +———-+ 1 row in set (5.00 sec) root@localhost : mysqlops 02:05:40> SELECT * FROM mysqlops_set_enum limit 6000000,1; +———+————-+———-+ | ID | Work_Option | Work_City | +———+————-+———-+ | 6000001 | PHP | dalian | +———+————-+———-+ 1 row in set (1.38 sec) root@localhost : mysqlops 02:06:55> SELECT COUNT(*) FROM mysqlops_set_enum WHERE Work_Option=’PHP’; +———-+ | COUNT(*) | +———-+ | 2000000 | +———-+ 1 row in set (5.11 sec) root@localhost : mysqlops 02:45:40> ALTER TABLE mysqlops_set_enum MODIFY Work_Option enum(‘JavaScript’,'DBA’,'SA’,'C++’,'NA’,'QA’,'Java’,'other’,”,’Python’) NOT NULL DEFAULT ‘DBA’; Query OK, 20017251 rows affected, 65535 warnings (2 min 11.71 sec) Records: 20017251 Duplicates: 0 Warnings: 2000000 root@localhost : mysqlops 02:54:01> SHOW WARNINGS; +———+——+——————————————————-+ | Level | Code | Message | +———+——+——————————————————-+ | Warning | 1265 | Data truncated for column ‘Work_Option’ at row 4017252 | | Warning | 1265 | Data truncated for column ‘Work_Option’ at row 4017253 | | Warning | 1265 | Data truncated for column ‘Work_Option’ at row 4017254 | | Warning | 1265 | Data truncated for column ‘Work_Option’ at row 4017255 | | Warning | 1265 | Data truncated for column ‘Work_Option’ at row 4017256 | | Warning | 1265 | Data truncated for column ‘Work_Option’ at row 4017257 | | Warning | 1265 | Data truncated for column ‘Work_Option’ at row 4017258 | | Warning | 1265 | Data truncated for column ‘Work_Option’ at row 4017259 | | Warning | 1265 | Data truncated for column ‘Work_Option’ at row 4017260 | root@localhost : mysqlops 02:56:18> SELECT COUNT(*) FROM mysqlops_set_enum WHERE Work_Option=”; +———-+ | COUNT(*) | +———-+ | 6017251 +———-+ 1 row in set (4.68 sec) root@localhost : mysqlops 02:56:48> SELECT * FROM mysqlops_set_enum WHERE ID=6000001; +———+————-+———-+ | ID | Work_Option | Work_City | +———+————-+———-+ | 6000001 | | dalian | +———+————-+———-+ 1 row in set (0.00 sec)
小结:
对于枚举类型字段中已存储某枚举元素的数据,再删除枚举类型ENUM字段值域列表中某个枚举值,则会出现:
g. 上述DDL变更操作之后的表结构
root@localhost : mysqlops 02:57:30> 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’,'hangzhou’,'shenzhen’,'guangzhou’,'xiamen’,'tianjin’,'qingdao’,'dalian’,'xian’,'other’) NOT NULL DEFAULT ‘shanghai’, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=20017252 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
(四) 枚举类型ENUM字段数据库索引创建与删除
a. 枚举类型字段无创建索引条件的SQL语句执行计划
root@localhost : mysqlops 03:40:35> EXPLAIN SELECT * FROM mysqlops_set_enum WHERE Work_Option=4 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数据库使用索引条件要求的SQL语句,也无索引信息可用,也即MySQL数据库枚举类型字段值域列表中的存储序列编号,无法做到替代索引的作用,也即依然需要显式创建数据库索引,加速数据查找速度。
b. 为枚举类型字段创建索引
root@localhost : mysqlops 03:40:59> ALTER TABLE mysqlops_set_enum ADD INDEX idx_Work_Option_enum(Work_Option);Query OK, 0 rows affected (1 min 14.31 sec)Records: 0 Duplicates: 0 Warnings: 0
小结:
mysql数据库枚举类型字段上创建普通索引,也是需要表级锁、创建临时表等方式实现,并没有什么内部特殊的机制可使用。
c. 枚举类型字段有索引条件的SQL语句执行计划
root@localhost : mysqlops 03:42:53> EXPLAIN SELECT * FROM mysqlops_set_enum WHERE Work_Option=4 LIMIT 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: mysqlops_set_enum type: ref possible_keys: idx_Work_Option_enum key: idx_Work_Option_enum key_len: 1 ref: const rows: 3927162 Extra: Using where 1 row in set (0.00 sec)
小结:
mysql数据库枚举类型字段创建索引之后,若是根据枚举类型字段进行数据查找,且WHERE子句符合正确写法和枚举值所占比例符合使用索引的要求,即可根据索引数据完成数据查找。
d. 删除枚举类型字段上的索引
root@localhost : mysqlops 03:44:22> ALTER TABLE mysqlops_set_enum DROP INDEX idx_Work_Option_enum;Query OK, 0 rows affected (0.80 sec)Records: 0 Duplicates: 0 Warnings: 0
小结:
MySQL5.5.X版本数据库对于普通索引的删除操作,还是非常好的支持,并不需要创建临时表等操作,对于枚举类型字段上的索引也是同样适用的,关于这方面的文章可参考MySQL 5.5版本对普通索引增删性能的优化。
(五) 总结
通过上述对MySQL数据库表枚举类型字段的定义属性和索引方面的DDL变更操作,观察对枚举类型字段存储的数据影响,可以得出下列结论:
a. MySQL数据库枚举类型字段与其他数据类型一样,进行DDL变更操作可能产生的影响;
b. MySQL数据库枚举类型字段的DDL变更操作,属于枚举类型字段特有的内容: