在设计数据库表结构的时候,通常情况下每张表结构都有一个字段作为ID,因为 ID会被用来做查询,JOIN,FK等操作,所以ID设计的好坏对性能的影响很大。
在为ID选择合适的类型的时候不仅需要考虑这种类型在数据库中存储所占用的空间,还需要考虑该类型在计算或者是值比较时的特性,例如BIT类型存储的时候是二进制的形式,而在数字计算的上下文时,会被转换成对应的十进制形式。
对ID进行JOIN操作或者是被用来作为其他表的FK时,涉及的字段类型要保持完全的一致,即使类型都是整数,Unsigned/Signed这样的微小细节也要保持一致,否则在性能方面可能会有意想不到的性能问题,这种问题往往又是难以被发现的。如果使用的Storage Engine 是InnoDB,则可以有效的防止关联字段类型不匹配的问题,如果类型不匹配则会抛出 异常“ERROR 1005 (HY000): Can’t createtable”(VARCHAR(10)和VARCHAR(20)会被认为类型是完全相同的)。
创建ID时,要遵守“尽量最小类型原则”(在满足以后需求变化的情况下,要选择占用存储空间最小的类型),例如 有一张存储全国省份的表,对于这张种需求而言,TINYINT比INT更适合作为ID的类型,虽然选用TINYINT只是节省了3个BYTE, 但是在进行JOIN操作或者是作为FK时,性能可能会有很大的提升。
实际的应用开发中,ID的类型往往是多样的,例如INT,VARCHAR是比较常用的两种类型,但是有些情况下也可以使用ENUM作为ID的类型,下面的内容分别对这几种类型进行描述
整数类型
因为整数类型是一种简单的类型,对比于字符串它的处理更简单速度更快,而且它还支持AUTO_INCREMENT特性,所以一般情况下它是作为ID的首选类型
字符串
因为MySQL处理字符串要比整数消耗更多的存储/内存空间,而且速度也相对较慢,所以应该尽量避免使用字符串作为ID的类型。
在无法避免字符串作为ID的类型时,往往会采用“随机值”的策略来生成ID的值,常用的方法包括UUID,MD5, SHA1.在这种情况下需要额外注意,因为随机生成的ID值可能会带来以下三个问题:
(1) 在执行INSERT语句时意味着索引值会被任意写到索引表的不同位置,这种对于磁盘的随机访问行为,将会导致插入操作的变慢,
(2) 在执行SELECT操作时,因为逻辑相关的索引也被会被任意写到索引表的不同位置,也会导致查询的变慢。
(3) 对于数据库提供的CACHE机制而言,这种随机值的策略产生的影响也是比较大的。因为CACHE往往是基于“热区”的原理来实现的,比如说索引表中有100个索引,你执行了多次的查询的结果都是索引表中的20-30这部分的索引,那么 MySQL Serve为了提高查询效率会把20-30这部分索引的值放到CACHE中,如果你又有一些查询的结果是在30-40 之间的,那么Server会对CACHE进行更新。如果是基于随机的索引,意味着“热区”的区间将会很大,这将导致CACHE的命中率受到影响,而且也会导致CACHE的不断刷新。
如果存储UUID 值,则应该移除“-”符号;或者更好的做法是,用UNHEX() 函数转换UUID 值为16 字节的数字,并且存储在一个BINARY(16) 列中。检索时可以通过HEX()函数来格式化为十六进制格式。
枚举
对于ID来说,EMUM 和SET 类型通常是一个糟糕的选择,尽管对某些只包含固定状态或者类型的静态“定义表”来说可能是没有问题的。ENUM 和SET 列适合存储固定信息,例如有序的状态、产品类型、人的性别。没有特殊需求,应该放弃这个选择
分享到:
相关推荐
《高性能MySQL(第3版)》是MySQL 领域的经典之作,拥有广泛的影响力。第3版更新了大量的内容,不但涵盖了最新MySQL5.5版本的新特性,也讲述了关于固态盘、高可扩展性设计和云计算环境下的数据库相关的新内容,原有的...
高性能mysql第三版高性能mysql第三版高性能mysql第三版
高性能Mysql,高清PDF,带目录,mysql机构与历史,mysql基准测试
高性能MYSQL第三版 高性能MYSQL第三版 高性能MYSQL第三版 高性能MYSQL第三版 KINDLE版,
高性能mysql PDF 中英文合集 高性能mysql PDF 中英文合集
高性能mysql源码
高性能MySQL(第三版)
高性能mysql第三版,中文版本。该版本支持编辑,比较清晰~欢迎大家下载。高性能mysql第三版,中文版本。该版本支持编辑,比较清晰~欢迎大家下载。高性能mysql第三版,中文版本。该版本支持编辑,比较清晰~欢迎...
高性能MySQL(第3版) 中文PDF带目录清晰版 《高性能MySQL(第3版)》是MySQL 领域的经典之作,拥有广泛的影响力。第3 版更新了大量的内容,不但涵盖了最新MySQL 5.5版本的新特性,也讲述了关于固态盘、高可扩展性设计...
高性能MySQL(第三版)全书共分为16章和6个附录,内容涵盖mysql架构和历史…………
高性能MySQL_第3版_超清中文带目录版[PDF] 序 I 前言 III 第1章:MySQL架构 1 第2章:寻找瓶颈:基准测试(Benchmarking)与性能分析(Profiling) 32 第3章:架构优化和索引 80 第4章:查询性能优化 152 第5章:...
高性能mysql电子书 高性能mysql电子书 高性能mysql电子书
高性能mysql第三版.part1.rar 高性能mysql第三版.part1.rar 高性能mysql第三版.part1.rar 高性能mysql第三版.part1.rar 高性能mysql第三版.part1.rar 高性能mysql第三版.part1.rar 高性能mysql第三版.part1.rar 高...
高性能 MYSQL 第三版中文 高性能 MYSQL 第三版中文
高性能MySQL(第3版),包含azw3,epub,mobi格式
第1章 mysql 架构与历史 1 第2章 mysql 基准测试 35 第3章 服务器性能剖析 67 第4章 schema 与数据类型优化 111 第5章 创建高性能的索引 141 第6章 查询性能优化 195 第7章 mysql 高级特性 259 第8章 优化服务器设置...
高性能MySQL 绝对的msyql高性能优化的经典书籍, java程序员必读
高性能mysql第三版