高性能Mysql＿笔记2（Schema(模式)和数据类型优化）

mysql-basice

在数据库中，schema（发音 “skee-muh” 或者“skee-mah”，中文叫模式）是数据库的组织和结构，schemas 和schemata都可以作为复数形式。模式中包含了schema对象，可以是表(table)、列(column)、数据类型(data type)、视图(view)、存储过程(stored procedures)、关系(relationships)、主键(primary key)、外键(foreign key)等。

MySQL支持的数据类型非常多，遵循以下几个原则可以对数据类型做出更好的选择。

• 更小的更好：一般情况下,应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。

• 简单就好：简单数据类型的操作通常需要更少的CPU周期。

• 尽量避免NULL：通常情况下最好指定列为NOT NULL，除非真的需要存储NULL值。

  • 因为可为NULL的列使得索引、索引统计和值比较都更复杂。

  • 可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理。

  • 当可为NULL的列被索引时，每个索引记录需要一个额外的字节，在 MyISAM里甚至还可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引。

2.1整数类型

• 有两种数字类型：整数和实数

• 整数的数据类型有:TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT。分别使用8,16，24，32，64位存储空间。它们可以存储的值的范围从-2^(N-1)到2(N-1)-1，其中N是存储空间的位数。

• 整数类型有可选的UNSIGNED属性,表示不允许负值,可以存储的范围是0 ~ 255。

• MySQL可以为整数类型指定宽度，例如INT(11)，对大多数应用这是没有意义的。对于存储和计算来说，INT(1)和INT(20)是相同的。它不会限制值的合法范围,只是规定了MySQL的一些交互工具（例如MySQL命令行客户端)用来显示字符的个数。

2.2实数类型

实数是带有小数部分的数字。然而，它们不只是为了存储小数部分，也可以使用DECIMAL存储比BIGINT还大的整数。MySQL既支持精确类型，也支持不精确类型。

DECIMAL 数据类型介绍:

MySQL DECIMAL数据类型用于在数据库中存储精确的数值。我们经常将DECIMAL数据类型用于保留精确精度的列，例如会计系统中的货币数据。

要定义数据类型为 DECIMAL 的列，请使用以下语法：

column_name  DECIMAL(P,D);
#金额列可以存储 6 位数字，小数点后 2 位；因此，金额列的范围是从 9999.99 到 -9999.99。
amount DECIMAL(6,2);

在上面的语法中：

• P 是表示有效位数的精度。P 的范围是 1 到 65。（早期的mysql版本是254个数字）

• D 是表示小数点后位数的刻度。D 的范围是 0 和 30。 MySQL 要求 D 小于或等于 (<=) P。

MySQL 允许我们使用以下语法：

column_name DECIMAL(P);

这相当于：

column_name DECIMAL(P,0);

在这种情况下，该列不包含小数部分或小数点。

此外，我们甚至可以使用以下语法。

column_name DECIMAL;

在这种情况下，P 的默认值为 10.

MySQL 十进制存储：MySQL 分别为整数和小数部分分配存储。MySQL 使用二进制格式来存储DECIMAL值。它将 9 个数字打包成 4 个字节。对于每个部分，存储每个 9 位的倍数需要 4 个字节。

例如：DECIMAL(18,9)小数点两边将各存储9个数字，一共使用9个字节:小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节。

2.3字符串类型

2.3.1VARCHAR和 CHAR类型

VARCHAR和 CHAR是两种最主要的字符串类型。

• VARCHAR

  • VARCHAR类型用于存储可变长字符串，是最常见的字符串数据类型。它比定长类型更节省空间，因为它仅使用必要的空间（例如，越短的字符串使用越少的空间)。有一种情况例外，如果 MySQL表使用ROw_FORMAT=FIXED创建的话，每一行都会使用定长存储，这会很浪费空间。

  • VARCHAR需要使用1或2个额外字节记录字符串的长度：如果列的最大长度小于或等于255字节,则只使用1个字节表示，否则使用2个字节。假设采用latin1字符集，一个VARCHAR(10)的列需要11个字节的存储空间。VARCHAR(1000)的列则需要1002个字节，因为需要2个字节存储长度信息。

• CHAR

  • CHAR数据类型是 MySQL 中的定长字符类型。通常CHAR使用指定要存储的最大字符数的长度声明类型。例如，CHAR(20)最多可容纳 20 个字符。

  • 如果要存储的数据是固定大小的，则应使用CHAR数据类型。VARCHAR与这种情况相比，您将获得更好的性能。

  • 数据类型的长度CHAR可以是 0 到 255 之间的任何值。当存储一个CHAR值时，MySQL 用空格填充它的值到你声明的长度。当您查询该CHAR值时，MySQL 会删除尾随空格。

  • CHAR适合存储很短的字符串，或者所有值都接近同一个长度。例如，CHAR非常适合存储密码的MD5值，因为这是一个定长的值。对于经常变更的数据，CHAR也比VARCHAR更好，因为定长的CHAR类型不容易产生碎片。

与CHAR和VARCHAR类似的类型还有BINARY和VARBINARY，它们存储的是二进制字符串。二进制字符串跟常规字符串非常相似，但是二进制字符串存储的是字节码而不是字符。填充也不一样:MySQL填充BINARY采用的是\0（零字节)而不是空格，在检索时也不会去掉填充之。

2.3.2BLOB和TEXT类型

BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。

BLOB 主要存储图片、音频信息等，而 TEXT 只能存储纯文本文件。但由于现在图片和音频越来越多，检索起来也不方便，所以都不放在数据库，一般放在专门的文件存储服务器上。

• 与其他类型不同，MySQL把每个BLOB和TEXT值当作一个独立的对象处理。存储引擎在存储时通常会做特殊处理。当BLOB和TEXT值太大时，InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后在外部存储区域存储实际的值。

• BLOB和TEXT家族之间仅有的不同是BLOB类型存储的是二进制数据，没有排序规则或字符集，而TEXT类型有字符集和排序规则。

• TEXT

  • TEXT对于存储可能占用 1 字节到 4 GB 的长格式文本字符串很有用。

  • MySQL 提供了四种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT.

    • TINYTEXT– 255 字节（255 个字符）

    • TEXT– 64KB（65,535 个字符）

    • MEDIUMTEXT– 16MB（16,777,215 个字符）

    • LONGTEXT– 4GB（4,294,967,295 个字符）

2.4日期和时间类型

MySQL可以使用许多类型来保存日期和时间值，例如YEAR和DATE。

DATETIME和TIMESTAMP

除了特殊行为之外，通常也应该尽量使用TIMESTAMP，因为它比DATETIME空间效率更高。

• DATETIME

  • 这个类型能保存大范围的值，从1001年到9999年，精度为秒。它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关。使用8个字节的存储空间。

  • 默认情况下，MySQL 以一种可排序的、无歧义的格式显示DATETIME值，例如“2008-01-16 22:37:08”。这是ANSI 标准定义的日期和时间表示方法。

• TIMESTAMP

  • TIMESTAMP值的范围从'1970-01-01 00:00:01' UTC到'2038-01-19 03:14:07' UTC。

  • 当将值插入TIMESTAMP表中时，MySQL 会将其从连接的时区转换为 UTC 以进行存储。当查询一个TIMESTAMP值时，MySQL 会将 UTC 值转换回您连接的时区。请注意，这种转换不会发生在其他时间数据类型上

2.5选择标识符

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

一旦选定了一种类型，要确保在所有关联表中都使用同样的类型。

• 在可以满足值的范围的需求，并且预留未来增长空间的前提下，应该选择最小的数据类型；

  • 整数类型：整数通常是标识列最好的选择

  • ENUM和SET类型：对于标识列来说，EMUM和SET类型通常是一个糟糕的选择

  • 字符串类型：应该尽量避免使用字符串类型作为标识列

2.6特殊的数据类型

某些类型的数据并不直接与内置类型一致。低于秒级精度的时间戳就是一个例子。

还有就是对于 IPv4 地址。人们经常使用VARCHAR(15)列去存储IP地址。然而，它们实际上是32位无符号整数，不是字符串。用小数点将地址分成4段的表示方法只是为了让人们阅读容易。所以应该用无符号整数存储IP地址。MySQL 提供了 INET_ATON() 和 INET_NTOA() 函数在这两种表示方法之间转换。

MySQL 5.6 版本之后，也有了解释 IPv6 地址的方法，就是INET6_ATON() 和 INET6_NTOA() 函数

参考CSDN博主菲fay

INET_NTOA函数——把整型的ip转为字符串式的地址

插入数据前，先用inet_aton把ip地址转为整型，显示数据时，使用inet_ntoa把整型的ip地址转为字符串格式的地址显示即可。

INSERT INTO `ip_table`(`id`,`ip`) VALUES 
(1,inet_aton('192.168.1.1')),
(2,inet_aton('192.168.1.2')),
(3,inet_aton('192.168.1.3')),
(4,inet_aton('192.168.1.4')),
(5,inet_aton('192.168.1.5'));

image.png

转化为我们熟悉的IP地址

SELECT id,INET_NTOA(ip) AS ip FROM ip_table ;

image.png

2.7范式和反范式

2.7.1三大范式和反范式

• 第一范式

  • 确保数据表中每列(字段)的原子性。

  • 如果数据表中每个字段都是不可再分的最小数据单元，则满足第一范式。

  • 例如：user用户表，包含字段id,username,password

• 第二范式

  • 在第一范式的基础上更进一步，目标是确保表中的每列都和主键相关。

  • 如果一个关系满足第一范式，并且除了主键之外的其他列，都依赖于该主键，则满足第二范式。

  • 例如：一个用户只有一种角色，而一个角色对应多个用户。则可以按如下方式建立数据表关系，使其满足第二范式。

  • user用户表，字段id,username,password,role_id

  • role角色表，字段id,name

  • 用户表通过角色id(role_id)来关联角色表

• 第三范式

  • 在第二范式的基础上更进一步，目标是确保表中的列都和主键直接相关，而不是间接相关。

  • 例如：一个用户可以对应多个角色，一个角色也可以对应多个用户。则可以按如下方式建立数据表关系，使其满足第三范式。

  • user用户表，字段id,username,password

  • role角色表，字段id,name

  • user_role用户-角色中间表，id,user_id,role_id

  • 像这样，通过第三张表(中间表)来建立用户表和角色表之间的关系，同时又符合范式化的原则，就可以称为第三范式。

• 反范式化

  • 反范式化指的是通过增加冗余或重复的数据来提高数据库的读性能。

  • 例如：在上例中的user_role用户-角色中间表增加字段role_name。

  • 反范式化可以减少关联查询时，join表的次数。

2.7.2范式的优点和缺点

• 优点

  • 范式化的更新操作通常比反范式化要快。

  • 当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据。范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快。

  • 很少有多余的数据意味着检索列表数据时更少需要DISTINCT（去重）或者GROUP BY（分组）语句。还是前面的例子:在非范式化的结构中必须使用DISTINCT或者GROUP BY才能获得一份唯一的部门列表，但是如果部门(DEPARTMENT）是一张单独的表，则只需要简单的查询这张表就行了。

• 缺点

  • 范式化设计的schema（模式）的缺点是通常需要关联。稍微复杂一些的查询语句在符合范式的schema上都可能需要至少一次关联，也许更多。这不但代价昂贵，也可能使一些索引策略无效。例如，范式化可能将列存放在不同的表中，而这些列如果在一个表中本可以属于同一个索引。

2.7.3反范式的优点和缺点

• 优点

  • 反范式化的schema（模式）因为所有数据都在一张表中，可以很好地避免关联。

    • 如果不需要关联表，则对大部分查询最差的情况——即使表没有使用索引一—是全表扫描。当数据比内存大时这可能比关联要快得多，因为这样避免了随机IO.（全表扫描基本上是顺序IO的）

  • 可以设计出有效的索引，将会使查询非常高效。

• 缺点

  • 表格内的冗余较多，删除数据时候会造成表有些有用的信息丢失。