MySQL数据库之枚举数据类型ENUM的DDL变更测试

栏目: 数据库 · 发布时间: 5年前

内容简介:针对四种数据类型:布尔类型BOOL或称布尔类型BOOLEAN、微整型TINYTINT、枚举类型ENUM、集合类型SET,我们已经分多篇文章篇幅给出详细的介绍与功能测试数据,接下来我们深入介绍枚举类型EUNM和集合类型SET。测试基于InnoDB存储引擎上,对MySQL数据库枚举类型ENUM的字段进行DDL变更操作,是否需要重新创建表呢?对数据库的事务处理有何影响?对数据库的数据服务提供有何性能影响?(一)硬件:DELL R510  10块盘做的RAID5,上面跑了几十个虚拟机

【导读】

针对四种数据类型:布尔类型BOOL或称布尔类型BOOLEAN、微整型TINYTINT、枚举类型ENUM、集合类型SET,我们已经分多篇文章篇幅给出详细的介绍与功能测试数据,接下来我们深入介绍枚举类型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数据库数据类型之ENUM、SET、BOOL/BOOLEAN、TINYINT

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

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

    MySQL数据库数据类型之集合类型SET测试总结

    MySQL数据库之数据类型集合类型和枚举类型测试环境

    备注:MySQL数据库数据类型之集合类型ENUM测试总结、MySQL数据库数据类型之枚举类型、集合类型和布尔类型的应用场景,将在下周一(2012-03-19日)分享到本站点,那么这次四种特殊数据类型系列的介绍技术文章将全部完成,期待大家整体阅读与指出其中可能存在的文字等错误,感谢大家的支持!

    觉得文章有用?立即:和朋友一起 共学习 共进步!

    建议继续学习:

    1. MySQL数据库之布尔类型、枚举类型和集合类型的应用场景详解    (阅读:5080)
    2. MySQL数据库数据类型之枚举类型ENUM测试总结    (阅读:3400)
    3. MySQL数据库数据类型之ENUM、SET、BOOL/BOOLEAN、TINYINT    (阅读:2161)
    4. ORACLE 11g新特性-允许DDL锁等待DML锁    (阅读:1940)
    5. 用 JS 枚举质数    (阅读:1621)
    6. MySQL数据库之数据类型集合类型和枚举类型测试环境    (阅读:1527)
    7. MySQL数据库之集合类型SET的DDL变更测试总结    (阅读:1127)
    8. java enum枚举类型用法小结    (阅读:804)

    QQ技术交流群:445447336,欢迎加入!

    扫一扫订阅我的微信号:IT技术博客大学习


    以上所述就是小编给大家介绍的《MySQL数据库之枚举数据类型ENUM的DDL变更测试》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!

    查看所有标签

    猜你喜欢:

    本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

    UML用户指南

    UML用户指南

    [美] Grady Booch、James Rumbaugh、Ivar Jacobson / 邵维忠、麻志毅、马浩海、刘辉 / 人民邮电出版社 / 2006-6 / 49.00元

    《UML用户指南》(第2版)是UML方面最权威的一本著作,三位作者是面向对象方法最早的倡导者,是UML的创始人。本版涵盖了UML 2.0。书中为具体的UML特征提供了参考,描述了一个使用UML进行开发的过程,旨在让读者掌握UML的术语、规则和语言特点,以及如何有效地使用这种语言,知道如何应用UML去解决一些常见的建模问题。《UML用户指南》(第2版)由7个部分组成,而且每章都描述了一个特定UML特......一起来看看 《UML用户指南》 这本书的介绍吧!

    RGB转16进制工具
    RGB转16进制工具

    RGB HEX 互转工具

    随机密码生成器
    随机密码生成器

    多种字符组合密码

    RGB HSV 转换
    RGB HSV 转换

    RGB HSV 互转工具