导读
java.util.arraylist。
本文深入介绍枚举类型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字段值域列表中某个枚举值,则会出现:
存在多少条要删除的枚举值记录数,就会产生多少条警告信息(注:警告信息最大值65535条);
被删除枚举值对应的字段的记录值,会发生截断,并且用空字符串值填充;
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变更操作,属于枚举类型字段特有的内容:
枚举类型字段的枚举数据值域列表,以尾部追加枚举元素值的方式,不会出现锁表等;
枚举类型字段的枚举数据值域列表中,若是调整枚举类型枚举元素值的顺序,会导致数据库表存储的数据出现错乱对照关系,以及需要锁表等操作;
删除枚举类型字段的枚举数据值域列表中,某个枚举元素值,会导致数据库表已存储的数据行出现截断,以及需要锁表等操作;
枚举类型字段内部的枚举数据与存储序号之间的对照关系,不会能起到MySQL数据库表索引的功能;
枚举类型字段存储的数据值,则是枚举类型枚举元素的序列编号,而不是真实的字符串数据,而是通过其内部对照表的方式转换而实现的;
往期精彩
MySQL史上最全性能优化方式
技术核心 | MySQL性能结构优化原理
MySQL DBA基本知识点梳理和查询优化
出处:https://blogread.cn/it/wap/article/5069
公司简介 | 恩墨学院 | 招聘 | DTCC | 数据技术嘉年华 | 免费课程 | 入驻华为严选商城
zCloud | SQM | Bethune Pro2 | zData一体机 | Mydata一体机 | ZDBM 备份一体机
Oracle技术架构 | 免费课程 | 数据库排行榜 | DBASK问题集萃 | 技术通讯
升级迁移 性能优化 | 智能整合 | 安全保障 |
云和恩墨大讲堂 | 一个分享交流的地方
长按,识别二维码,加入万人交流社群
请备注:云和恩墨大讲堂
版权声明:本站所有资料均为网友推荐收集整理而来,仅供学习和研究交流使用。
工作时间:8:00-18:00
客服电话
电子邮件
admin@qq.com
扫码二维码
获取最新动态