高性能MySQL:选择标识符(identifier)

发布网友 发布时间：2023-05-20 02:44

我来回答

共1个回答

热心网友 时间：2024-11-16 03:38

　　选择标识符（identifier）

　　为标识列（identifier column）选择合适的数据类型非常重要 一般来说更有可能用标识列与其他值进行比较（例如 在关联操作中） 或者通过标识列寻找其他列 标识列也可能在另外的表中作为外键使用 所以为标识列选择数据类型时 应该选择跟关联表中的对应列一样的类型（正如我们在本章早些时候所论述的一样 在相关的表中使用相同的数据类型是个好主意 因为这些列很可能在关联中使用）

　　当选择标识列的类型时 不仅仅需要考虑存储类型 还需要考虑MySQL 对这种类型怎么执行计算和比较 例如 MySQL 在内部使用整数存储ENUM 和SET 类型 然后在做比较操作时转换为字符串

　　一旦选定了一种类型 要确保在所有关联表中都使用同样的类型 类型之间需要精确匹配 包括像UNSIGNED 这样的属性注 混用不同数据类型可能导致性能问题 即使没有性能影响 在比较操作时隐式类型转换也可能导致很难发现的错误 这种错误可能会很久以后才突然出现 那时候可能都已经忘记是在比较不同的数据类型

　　在可以满足值的范围的需求 并且预留未来增长空间的前提下 应该选择最小的数据类型 例如有一个state_id 列存储美国各州的名字注 就不需要几千或几百万个值 所以不需要使用INT TINYINT 足够存储 而且比INT 少了 个字节 如果用这个值作为其他表的外键 个字节可能导致很大的性能差异 下面是一些小技巧 整数类型

　　整数通常是标识列最好的选择 因为它们很快并且可以使用AUTO_INCREMENT

　　ENUM 和SET 类型

　　对于标识列来说 EMUM 和SET 类型通常是一个糟糕的选择 尽管对某些只包含固定状态或者类型的静态 定义表 来说可能是没有问题的 ENUM 和SET 列适合存储固定信息 例如有序的状态 产品类型 人的性别

　　举个例子 如果使用枚举字段来定义产品类型 也许会设计一张以这个枚举字段为主键的查找表（可以在查找表中增加一些列来保存描述性质的文本 这样就能够生成一个术语表 或者为网站的下拉菜单提供有意义的标签） 这时 使用枚举类型作为标识列是可行的 但是大部分情况下都要避免这么做

　　字符串类型如果可能 应该避免使用字符串类型作为标识列 因为它们很消耗空间 并且通常比数字类型慢 尤其是在MyISAM 表里使用字符串作为标识列时要特别小心

　　MyISAM 默认对字符串使用压缩索引 这会导致查询慢得多 在我们的测试中 我们注意到最多有 倍的性能下降

　　对于完全 随机 的字符串也需要多加注意 例如MD （） SHA （） 或者UUID（） 产生的字符串 这些函数生成的新值会任意分布在很大的空间内 这会导致INSERT 以及一些SELECT 语句变得很慢注 :

　　因为插y 入值会随机地写到索引的不同位置 所以使得INSERT语句更慢 这会导致页* 磁盘随机访问 以及对于聚簇存储引擎产生聚簇索引碎片 关于这一点第 章有更多的讨论

　　SELECT语句会变得更慢 因为逻辑上相邻的行会分布在磁盘和内存的不同地方

　　随机值导致缓存对所有类型的查询语句效果都很差 因为会使得缓存赖以工作的访问局部性原理失效 如果整个数据集都一样的 热 那么缓存任何一部分特定数据到内存都没有好处 如果工作集比内存大 缓存将会有很多刷新和不命中

　　如果存储UUID 值 则应该移除 符号 或者更好的做法是 用UNHEX（） 函数转换UUID 值为 字节的数字 并且存储在一个BINARY（ ） 列中 检索时可以通过HEX（）函数来格式化为十六进制格式

　　UUID（） 生成的值与加密散列函数例如SHA （） 生成的值有不同的特征 UUID 值虽然分布也不均匀 但还是有一定顺序的 尽管如此 但还是不如递增的整数好用

　　当心自动生成的schema

　　我们已经介绍了大部分重要数据类型的考虑（有些会严重影响性能 有些则影响较小） 但是我们还没有提到自动生成的schema 设计有多么糟糕

　　写得很烂的schema 迁移程序 或者自动生成schema 的程序 都会导致严重的性能问题 有些程序存储任何东西都会使用很大的VARCHAR 列 或者对需要在关联时比较的列使用不同的数据类型 如果schema 是自动生成的 一定要反复检查确认没有问题

　　对象关系映射（ORM）系统（以及使用它们的 框架 ）是另一种常见的性能噩梦 一些ORM 系统会存储任意类型的数据到任意类型的后端数据存储中 这通常意味着其没有设计使用更优的数据类型来存储 有时会为每个对象的每个属性使用单独的行 甚至使用基于时间戳的版本控制 导致单个属性会有多个版本存在

　　这种设计对开发者很有吸引力 因为这使得他们可以用面向对象的方式工作 不需要考虑数据是怎么存储的 然而 对开发者隐藏复杂性 的应用通常不能很好地扩展 我们建议在用性能交换开发人员的效率之前仔细考虑 并且总是在真实大小的数据集上做测试 这样就不会太晚才发现性能问题

      返回目录 高性能MySQL

       编辑推荐

       ASP NET MVC 框架揭秘

       Oracle索引技术

       ASP NET开发培训视频教程