网站后台更新文章 前台不显示,有支付功能网站用的编程语言,网站平台专业开发制作app,建e室内设计网cad良好的数据库 schema 设计和合理的数据类型选择是 SQL 获得高性能的基石。
一、选择优化的数据类型
MySQL 支持的数据类型非常多#xff0c;选择正确的数据类型对于获得高性能至关重要。不管存储哪种类型的数据#xff0c;下面几个简单的原则都有助于做出更好的选择。
1. …良好的数据库 schema 设计和合理的数据类型选择是 SQL 获得高性能的基石。
一、选择优化的数据类型
MySQL 支持的数据类型非常多选择正确的数据类型对于获得高性能至关重要。不管存储哪种类型的数据下面几个简单的原则都有助于做出更好的选择。
1. 更小的通常更好 一般情况下应该尽量使用可以正确存储数据的最小数据类型。例如只需要存储数值 0 ~ 200tinyint unsigned 更好。更小的数据类型通常更快因为它们占用更少的磁盘、内存和 CPU 缓存并且处理时需要的 CPU 周期也更少。 2. 简单就好 简单数据类型的操作通常需要更少的 CPU 周期。例如整型比字符操作代价更低因为字符集和校对规则排序规则使字符比较比整型比较更复杂。这里有两个例子一个是应该使用 MySQL 内建的类型而不是字符串来存储日期和时间另外一个是应该用整型存储 IP 地址。 3. 尽量避免 NULL 很多表都包含可为 NULL空值的列即使应用程序并不需要保存 NULL 也是如此这是因为可为 NULL 是列的默认属性。即如果定义表结构时没有指定列为 NOT NULL默认都是允许为 NULL 的。通常情况下最好指定列为 NOT NULL除非真的需要存储 NULL 值。 如果查询中包含可为 NULL 的列对 MySQL 来说更难优化因为可为 NULL 的列使得索引、索引统计和值比较逗更复杂。可为 NULL 的列会使用更多的存储空间在 MySQL 里也需要特殊处理。当可为 NULL 的列被索引时每个索引记录需要一个额外的字节在 MyISAM 里甚至还可能导致固定大小的索引变成可变大小的索引。 通常把可为 NULL 的列改为 NOT NULL 带来的性能提升比较小所以调优时没有必要首先在现有 schema 中查找并修改掉这种情况除非确定这会导致问题。但是如果计划在列上建索引就应该尽量避免设计成可为 NULL 的列。 当然也有例外例如InnoDB 使用单独的位bit存储 NULL 值所以对于稀疏数据即很多值为 NULL只有少数行的列有非 NULL 值有很好的空间效率。但这一点不适用于 MyISAM。 那么如何为列选择合适的数据类型呢分两步
首先确定合适的大类型数字、字符串、时间等。这通常是很简单的。选择具体类型。很多 MySQL 的数据类型可以存储相同类型的数据只是存储的长度和范围不一样、允许的精度不同或者需要的物理空间磁盘和内存空间不同。相同大类型的不同子类型数据有时也有一些特殊的行为和属性。
我们只讨论基本的数据类型。MySQL 为了兼容性支持很多别名例如 INTEGER、BOOL以及 NUMERIC。他们都只是别名。这些别名可能令人不解但不会影响性能。如果建表时采用数据类型的别名然后用 SHOW CREATE TABLE 检查会发现 MySQL 报告的是基本类型而不是别名。
1.1 整数类型
有两种类型的数字整数whole number和实数real number。如果存储整数可以使用这几种整数类型TINYINTSMALLINTMEDIUMINTINTBIGINT。分别使用 8163264位存储空间。他们可以存储的值范围从 到 其中 N 是存储空间的位数。
整数类型有可选的 UNSIGNED 属性表示不允许负值这大致可以使正数的上限提高一倍。例如TINYINT UNSIGNED 可以存储的范围是 0~255而 TINYINT 的存储范围是 -128~127。
有符号和无符号类型使用相同的存储空间并具有相同的性能因此可以根据实际情况选择合适的类型。
MySQL 可以为整数类型指定宽度例如 INT(11)对大多数应用这是没意义的它不会限制值的合法范围只是规定了 MySQL 的一些交互工具例如 MySQL 命令行客户端用来显示字符的个数。对于存储和计算来说INT(1) 和 INT(20) 是相同的。 注你选择的整数类型决定 MySQL 是怎么在内存和磁盘中保存数据的。然而整数计算一般使用 64 位的 BIGINT 整数即使在 32 位环境也是如此。一些聚合函数除外他们使用 DECIMAL 或 DOUBLE 进行计算。 1.2 实数类型
实数是带有小数部分的数字。他们不只是为了存储小数部分也可以使用 DECIMAL 存储比 BIGINT 还大的整数。MySQL 既支持精确类型也支持不精确类型。
FLOAT 和 DOUBLE 类型支持使用标准的浮点运算进行近似计算。
DECIMAL 类型用于存储精确的小数在 MySQL 5.0 和更高版本DECIMAL 类型支持精确计算。对于 DECIMAL 列可以指定小数点前后所允许的最大位数。这会影响列的空间消耗。例如DECIMAL(18,9) 小数点两边将各存储 9 个数字一共使用 9 个字节小数点前的数字用 4 个字节小数点后的数字用 4 个字节小数点本身占 1 个字节。MySQL 5.0 和更高版本中的 DECIMAL 类型允许最多 65 个数字。
浮点类型在存储同样范围的值时通常比 DECIMAL 使用更少的空间。FLOAT 使用 4 个字节存储。DOUBLE 占用 8 个字节。
因为需要额外的空间和计算开销所以应该尽量只在对小数进行精确计算时才使用 DECIMAL例如存储财务数据等。
1.3 字符串类型
MySQL 支持多种字符串类型每种类型还有很多变种。
1.3.1 VARCHAR 和 CHAR 类型
VARCHAR 和 CHAR 是两种最主要的字符串类型。
VARCHAR VARCHAR 类型用于存储可变长字符串是最常见的字符串数据类型。 需要使用 1 或 2 个额外字节记录字符串的长度如果列的最大长度小于或等于 255 字节则只使用 1 个字节表示否则使用 2 个字节。 它比定长类型更节省空间因为它仅使用必要的空间例如越短的字符串使用越少的空间所以对性能也有帮助。 但由于 VARCHAR 行是变长的在 UPDATE 时可能使行变得比原来更长这就导致需要做额外的工作。 以下情况使用 VARCHAR 是合适的 字符串列的最大长度比平均长度大很多列的更新很少所以碎片不是问题使用了像 UTF-8 这样复杂的字符集每个字符都使用不同的字节数进行存储。 CHAR CHAR 类型是定长的MySQL 总是根据定义的字符串长度分配足够的空间。 CHAR 适合存储很短的字符串或者所有值都接近同一个长度。 以下情况使用 CHAR 是合适的 CHAR 非常适合存储密码的 MD5 值因为这是一个定长的值。对于经常变更的数据CHAR 也比 VARCHAR 更好因为定长的 CHAR 类型不容易产生碎片。对于非常短的列CHAR 比 VARCHAR 在存储空间上也更有效率。例如用 CHAR(1) 来存储只有 Y 和 N 的值如果采用单字节字符集只需要一个字节但是 VARCHAR(1) 却需要两个字节因为还有一个记录长度的额外字节。 1.3.2 BLOB 和 TEXT 类型
BLOB 和 TEXT 都是为存储很大的数据而设计的字符串数据类型分别采用二进制和字符方式存储。
BLOB 和 TEXT 之间仅有的不同是 BLOB 类型存储的是二进制数据没有排序规则或字符集而 TEXT 类型有字符集和排序规则。
MySQL对 BLOB 和 TEXT 列进行排序与其他类型是不同的它只对每个列的最前 max_sort_length 字节而不是整个字符串排序。
尽量避免使用 BLOB 和 TEXT 类型。
1.4 日期和时间类型
MySQL 可以使用许多类型来保存日期和时间值例如 YEAR 和 DATE等。其中 DATETIME 和 TIMESTAMP 是 MySQL 提供的两种比较相似的保存时间的数据类型可以精确到秒。他们两者究竟如何选择呢
下面我们来简单对比一下二者。
1.4.1 时区信息
DATETIME 类型是没有时区信息的时区无关。DATETIME 类型保存的时间都是当前会话所设置的时区对应的时间。
TIMESTAMP 和时区有关。TIMESTAMP 类型字段的值会随着服务器时区的变化而变化自动换算成相应的时间说简单点就是在不同时区查询到同一条记录此字段的值会不一样。
下面实际演示一下
建表 SQL 语句
CREATE TABLE time_zone_test (id bigint(20) NOT NULL AUTO_INCREMENT,date_time datetime DEFAULT NULL,time_stamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (id)
) ENGINEInnoDB DEFAULT CHARSETutf8;插入数据
INSERT INTO time_zone_test(date_time,time_stamp) VALUES(NOW(),NOW());查看数据
select date_time,time_stamp from time_zone_test;结果
------------------------------------------
| date_time | time_stamp |
------------------------------------------
| 2020-01-11 09:53:32 | 2020-01-11 09:53:32 |
------------------------------------------我们修改当前会话的时区
set time_zone8:00;再次查看数据
------------------------------------------
| date_time | time_stamp |
------------------------------------------
| 2020-01-11 09:53:32 | 2020-01-11 17:53:32 |
------------------------------------------1.4.2 占用空间
下图是 MySQL 日期类型所占的存储空间 在 MySQL 5.6.4 之前DATETIME 和TIMESTAMP 的存储空间是固定的分别是 8 字节和 4 字节。但是从 MySQL 5.6.4 开始他们的存储空间会根据毫秒精度的不同而变化DATETIME 的范围是 5~8 字节TIMESTAMP 的范围是 4~7 字节。
1.4.3 表示范围
TIMESTAMP 表示的时间范围更小只能到 2038 年
DATETIME1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.499999TIMESTAMP1970-01-01 00:00:01.000000 ~ 2038-01-19 03:14:07.499999
1.4.4 数值时间戳是更好的选择吗
很多时候我们也会使用 INT 或者 BIGINT 类型的数值也就是数值时间戳来表示时间。
这种存储方式具有 TIMESTAMP 类型所具有的一些有点并且使用它进行日期排序以及对比等操作的效率会更高跨系统也很方便。缺点也很明显就是数据的可读性太差了无法直观的看到具体时间。
MySQL 中时间到底怎么存储才好DATETIMETIMESTAMP还是数值时间戳
并没有一个银弹很多程序员会觉得数值型时间戳是真的好效率又高还各种兼容但是很多人有觉得它表现的不够直观。
除了特殊行为之外通常也应该尽量使用 TIMESTAMP因为它比 DATETIME 空间效率更高。但每种方式都有各自的优势根据实际场景选择最合适的才是王道。
下面再对这三种方式做一个简单的对比以供大家在实际开发中选择合适的存储时间的类型 1.5 位数据类型
MySQL 有少数几种存储类型使用紧凑的位存储数据。所有这些位类型不管底层存储格式和处理方式如何从技术上来说都是字符串类型。
BIT
BIT 列的最大长度是 64 位MySQL 把 BIT 当作字符串类型而不是数字类型。当检索 BIT(1) 的值时结果是一个包含二进制 0 或 1 值的字符串而不是 ASCII 码的 “0” 或 “1”。然而在数字上下文的场景中检索时结果将是位字符串转换成的数字。如果需要和另外的值比较结果一定要记得这一点。例如如果存储一个值 b 00111001二进制值等于 57到 BIT(8) 的列并且检索它得到的内容是字符码为 57 的字符串。也就是说得到 ASCII 码为 57 的字符 “9”。但是在数字上下文场景中得到的是数字 57 这是相当令人费解的所以我们应该谨慎使用 BIT 类型对于大部分应用最好避免使用这种类型。
SET
如果需要保存很多 true/false 值可以考虑合并这些列到一个 SET 数据类型它在 MySQL 内部是以一系列打包的位的集合来表示的。这样就有效地利用了存储空间并且 MySQL 有像 FIND_IN_SET() 和 FIELD() 这样的函数方便地在查询中使用。它的主要缺点是改变列的定义的代价较高需要 ALTER TABLE这对大表来说是非常昂贵的操作。一般来说也无法在 SET 列上通过索引查找。
1.6 选择标识符identifier
为标识列identifier column选择合适的数据类型非常重要一般来说更有可能用标识列与其他值进行比较例如在关联操作中或者通过标识列寻找其他列。
整数通常是标识列最好的选择因为他们很快并且可以使用 AUTO_INCREMENT。千万不要使用 ENUM 和 SET 类型作为标识列应尽量避免使用字符串作为标识列因为它们很消耗空间并且通常比数字类型慢。
1.7 特殊类型数据
某些类型的数据并不直接与内置类型一致。低于秒级精度的时间戳就是一个例子。
另外一个例子是一个 IPv4 地址。人们经常使用 VARCHAR(15) 的列来存储 IP 地址。然而它们实际上是 32 位无符号整数不是字符串。用小数点将地址分成四段的表示方法只是为了让人们阅读容易。所以应该用无符号整数存储 IP 地址。MySQL 提供 INET_ATON() 和 INET_NTOA() 函数在这两种表示方法之间转换。
二、schema 设计中的陷阱
2.1 太多的列 MySQL 的存储引擎 API 工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。 如果计划使用数千个字段必须意识到服务器的性能特征会有一些不同即查询会变得很慢。 2.2 太多的关联 MySQL 限制了每个关联操作最多只能有 61 张表。经验法则希望查询执行得快速且并发性好单个查询最好在 12 个表以内做关联。 2.3 全能的枚举 注意防止过度使用枚举ENUM。CREATE TABLE ..(country enum(,1,2,3,...,31)) 这种模式的 schema 设计非常糟糕。当需要再枚举列表中增加一个新的国家时就要做一次 ALTER TABLE 操作。在 MySQL 5.0 以及更早的版本中 ALTER TABLE 是一种阻塞操作。 2.4 变相的枚举 枚举ENUM列允许在列中存储一组定义值中的单个值集合SET列则允许在列中存储一组定义值中的一个或多个值。例子CREATE TABLE ...(is_default set(Y,N) NOT NULL default N)如果这里真和假两种情况不会同时出现那么毫无疑问应该使用枚举列代替集合列。 2.5 NULL 的替换方案 前面说了尽量不使用 NULL我们可以使用 -10空字符串来代替。但是不要每个地方都这样用有时候使用替代方案可能会导致编写代码和业务场景变得更加复杂比如其他部门都用 NULL我们使用 -1这样很多调用部分都得做出相应的修改。 在具体的场景可以具体分析并不是说 NULL 就不能用了。 三、范式和反范式
对于任何给定的数据通常都有很多种表示方法从完全的范式化到完全的反范式化以及两者的这种。在范式化的数据库中每个事实数据会出现并且只出现一次。相反在反范式化的数据库中信息是冗余的可能会存储在多个地方。
3.1 范式的优点和缺点 当为性能问题而寻求帮助时经常会被建议对 schema 进行范式化设计尤其是写密集的场景。这通常是个好建议。因为范式化通常能够带来以下好处 范式化的更新操作通常比反范式化要快。当数据较好的范式化时就只有很少或者没有重复数据所以只需要修改更少的数据。范式化的表通常更小可以更好的放在内存里所以执行操作会更快。很少有多余的数据意味着检索列表数据时更少需要 DISTINCT 或者 GROUP BY 语句。 范式化设计的 schema 的缺点是通常需要关联。这不但代价昂贵也可能使一些索引策略无效。 3.2 反范式的优点和缺点 反范式化设计的优点有 反范式化的 schema 因为所有数据都在一张表中可以很好的避免关联。可以有效避免随机I/O。单独的表也能使用更有效的索引策略。 反范式化的缺点是数据存储冗余修改数据时可能发生不一致。 3.3 混用范式化和反范式化
范式化和反范式化的 schema 各有优劣怎么选择最佳的设计 事实上完全的范式化和完全的反范式化 schema 都是实验室里才有的东西在真实世界中很少会这么极端地使用。 在实际应用中经常需要混用可能使用部分范式化的 schema、缓存表以及其他技巧。 四、小结
良好的 schema 设计原则是普遍适用的但 MySQL 有它自己的实现细节要注意。概括来说尽可能保持任何东西小而简单总是好的。MySQL 喜欢简单需要使用数据库的人应该也同样会喜欢简单的原则
尽量避免多度设计例如会导致极其复杂查询的 schema 设计或者有很多列的表设计很多的意思是介于有点多和非常多之间。使用小而简单的合适数据类型除非真实数据模型中有确切的需要否则应该尽可能地避免使用 NULL 值。尽量使用相同的数据类型存储相似或相关的值尤其是要在关联条件中使用的列。注意可变长字符串其在临时表和排序时可能导致悲观的按最大长度分配内存。尽量使用整型定义标识列。避免使用 MySQL 已经遗弃的特性例如指定浮点数的精度或者整数的显示宽度。小心使用 ENUM 和 SET。虽然它们用起来很方便但是不要滥用否则有时候会变成陷阱。最好避免使用 BIT。范式是好的但是反范式大多数情况下意味着重复数据有时也是必须的并且能带来好处。
参考
MySQL 官方文档https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html
