模式和数据类型优化方法

架构是数据库对象的集合。这个集合包含了表、视图、存储过程、索引等各种对象，为了区分不同的集合，需要给不同的集合赋予不同的名称。默认情况下，一个用户对应一个集合，用户的schema名称等于用户名，作为用户的默认schema。所以模式集合看起来像用户名。

如果把数据库想象成一个仓库，仓库有很多个房间（schema），一个schema代表一个房间，表可以看成每个房间的一个储物柜，用户就是所有者每个模式的。 ，有权操作数据库中的每个房间，这意味着数据库中映射的每个用户都拥有每个模式的密钥（roo米）。 SQL Server和Oracle mysql有区别

4.1选择优化的数据类型

原则：

1． 遍数越小越好，尽量使用能够正确存储数据的最小数据类型（占用更少的磁盘内存和CPU缓存，需要更少的CPU周期来处理：更快），但可以覆盖数据，如果无法保存那就尴尬了

2.简单就是好：简单类型（CPU周期少），使用MySQL内置类型存储时间，整数类型存储IP，整数类型比字符便宜（字符集和排序规则使字符更复杂）

*）null列占用存储空间较多，需要mysql进行特殊处理

*）null使得索引、索引统计、价值比较更加复杂；当可为空列建立索引时，每个索引记录都需要额外的字节

例外：InnoDB 使用单独的位来存储null，所以对于Sparse数据（很多值都是null）有很好的空间效率，不适合MyISAM

4.1.1 Integer类型【参考】

Integer整数

tinyint（8位存储空间）smallint(16)mediumint(24)int(32)bigint(64)

1。存储值的范围：，N为存储空间的位数

3。无论有没有符号，使用相同的存储空间和相同的性能

可以是整数指定宽度，例如INT(11)，对于大多数应用程序来说是没有意义的。它不限制合法值的范围。它仅指定交互式工具显示的字符数。对于存储和计算来说，int(1)和int(20)是一样的；

实数：带小数

float和double，mysql使用duble作为内部浮点数的类型计算

decimal：存储精确的小数，由mysql服务器本身实现，decimal(18,9)18位，9位小数，9个字节(前4位和后4位各1)

当数据量很大时，考虑使用bigint代替，乘以小数位数对应倍数需要存储的货币单位数据

浮点：

建议：只指定类型和变量精度（mysql）。这些精度是非标准的，mysql在保存时会悄悄选择类型或者对值进行四舍五入

存储相同范围的值时，比小数占用的空间更少。 Float4字节存储double8字节（精度范围更高精度）

4.1.3 String类型

varchar和char：

h4>

前提：innodb和myisam引擎，最重要的字符串类型

磁盘存储：存储方式存储引擎的d必须与内存和磁盘中的不同，因此mysql服务器从引擎获取数据。该值需要转换为格式

varchar:

1。存储可变字符串，相比固定长度可以节省空间（只使用必要的空间），但如果表使用row_format=fixed，行会以固定长度存储

2.需要额外使用1/2个字节来记录字符串长度； 1）列的最大长度<=255字节，用1个字节表示，否则用2个字节，2）使用latinl字符集，varchar(10)列需要11个字节的存储空间，varchar(1000)1002个字节，2存储长度信息的字节

3.节省存储空间，有利于性能；但更新可能会使该行比原来的长，需要额外的工作

合适的情况：

1）字符 字符串的最大长度是多少大于平均长度； 2) 列更新较少（不用担心碎片）； 3）使用UTF-8字符串，每个字符使用不同的字节数存储

char：

1。固定长度，根据长度分配空间，全部删除末尾的空格；长度不够，空间填充

2.存储空间效率更高，char(1)只存储Y N值1字节，varchar 2字节，以及一条记录长度

适合情况：

1）适合存储很短的字符串； 2）或所有值接近相同长度； 3）频繁变化的数据，存储不易碎片

对应空格与存储：

char类型存储时，删除尾部空格;数据如何存储取决于存储引擎，Memory引擎只支持定长Line（最大长度分配空间）

binary, varbinary：存储二进制字符串, < strong>字节码，长度不够gh, \0 来弥补（不是空格）在检索时不会消失

大方是不明智的：varchar(5) 和 varchar(100) 存储“地狱”空间开销相同，长列消耗内存较多

blob和text：大数据分别以二进制和字符模式存储，属于两种不同的数据类型：字符类型：tinytext、smalltext、text， mediumtext、longtext，对应的二进制类型有tinyblob、Smallblob、blob、mediumblob、longblob，两种类型唯一的区别是：blob类型存储的是二进制，没有排序规则或字符集，text有字符串排序规则；

MySQL 将把每个 blob 与 Text 结合起来，将其视为一个独立对象。存储引擎在存储的时候会做特殊的处理。当值太大时，innoDB使用特殊的外部存储区域来存储它。此时每个值需要在行中为1到4。字节存储一个指针，然后外部存储实际值；

mysql 对它们的列进行排序：仅对每列的前 max_sort_length 字节进行排序；并且无法对具有列全长的字符串进行索引。这些索引不能用于消除排序；

如果解释执行计划的额外内容包含using tempor：此查询使用隐式临时表

使用枚举而不是字符串类型

定义时指定取值范围。 1 到 255 个成员的枚举需要 1 个字节的存储空间。对于 256 至 65535 个成员，需要 2 个字节的存储空间。最多可有 65535 名成员。 ENUM类型只能从成员中选一个； 类似于set

可以将不重复的固定字符串存储在预定义的集合中。 MySQL在存储枚举时，会根据列表值的个数将其压缩为1/2字节，每个值都会在内部进行压缩。列表中的位置保存为整数（从1、必须做一次查找才能转换为字符串，开销，小列表是可控的)并且在表的.frm中维护了“number-string”的“查找表” " 文件中的映射关系；

将一个数字存储到一个ENUM中，该数字被视为索引值，并且存储的值为该索引对应的枚举成员value：在 ENUM 字符串中存储数字是不明智的，因为它可能会扰乱你的思维； ENUM 值根据列规范中列出的顺序进行排序。 （ENUM 值按其索引号排序。）例如，对于 ENUM("a", "b") "a" 排名在 "b" 之后，但对于 ENUM("b", "a")， "b" 排名在 之后之前的“a”。空字符串排在非空字符串之前，NULL 值排在所有其他枚举值之前。阻止意外的结果，建议按字母顺序定义ENUM列表。您还可以使用GROUP BY CONCAT(col)按字母顺序而不是按索引值排序。 [来源]

排序时，按照表创建的顺序排序（应该是）；枚举最糟糕的部分：字符串列表是固定的，必须使用alter table来添加或删除字符串；在‘查表’时使用整数主键避免基数与字符串的值关联；

4.1.4日期和时间

datetime：取值范围广​​1001 9999 s YYYYMMDDHHMMSS 8 个字节，与时区无关

默认，以可排序、明确的格式显示日期时间：2008-01-02 22:33:44

时间戳：1970 2038，自1970年以来的秒数1 1，时区4字节

from_unixtime将unix时间戳转换为日期，unix_timestamp将日期转换为unix时间戳

第一个t的值未指定 imestamp 列插入时，设置为当前时间，插入记录时，默认更新第一个时间戳列的值。时间戳类不为空。 尽可能使用时间戳（空间效率高）；

可以使用bigint类型存储精细级别的时间戳，或者使用double存储秒后的小数部分，或者使用MariaDB代替MySQL；

4.1.5位

位：mysql5.0

以前与tinyint同义，新特性

bit（1）单个位字段，位 (2) 2 位，最大长度 64 位

行为存储 引擎有所不同。 MyISAM打包并存储所有BIT列（17个单独的位列只需要17位存储，myisam3字节就可以了）。其他引擎，Memory 和 innoDB，使用足以存储每个位列的最小整数。 type来存储，不节省存储空间；

mysql把bit当成字符串类型，检索bit(1)值，结果是一个字符串containing 二进制0/1，数字 上下文场景检索，将字符串转换为数字，大多数应用，最好避免使用；

设置

创建表时，指定SET类型取值范围：属性名SET('值1','值2','值3'...,'值n')，“值n”参数代表列表中的第n个值，以及这些值末尾的空格会被系统直接删除，字段元素顺序系统会自动按照定义的顺序显示重复项并只保存一次。

基本形式与ENUM类相同类型。 SET 类型的值可以是列表中一个元素或多个元素的组合。获取多个元素时，用逗号分隔。 SET类型的值最多只能是64个元素的组合。根据成员的不同存储也不同：[参考，同enum]

集合1～8个成员，占用1个字节。 9到16个成员的集合，占用2个字节。 17到24个成员的集合，占用3个字节。 25到32个成员的集合，占用4个字节。 33到64个成员的集合，占用8个字节。 

如果需要维护大量的 true 和 false 值，可以考虑将这些列合并成一个 set 类型，mysql 内部将其表示为一系列打包的位 （有效利用存储空间）并且mysql有find_in_set和field函数，方便在查询时使用；

缺点：改变列的定义代价昂贵，需要alter table ，并且无法通过set通过索引进行搜索< /p>

对整数列进行按位运算：

代替set：使用整数包裹一系列位：8位可以打包成tinyint并用于按位运算。为位定义名称常量简化了这项工作，但这使得查询语句更加不同很难写和理解

4.1.6 选择标识符

标识列：自增列[来源]

1）无需插入值​​手动，系统提供默认序列值； 2）不需要匹配主键； 3）要求是唯一的key；

4）每桌最多1个； 5）类型只能是数字； 5）可以通过set auto_increment_increment=3;

选择identity列类型时

考虑存储类型和mysql为此如何进行计算以及两种类型的比较。确定后，确保所有关联表中使用相同的类型，且类型必须准确匹配；

提示：

1、整数类型：整数通常是最好的选择，速度快并且可以使用auto_increment

2。枚举和集合类型，存储固定信息

3.字符串：避免，消耗空间比数字慢，小心myisam表（默认字符串压缩，查询慢）

1）完全“随机”的字符串MD5/SHA1/UUID函数生成的新值会在空间内大面积任意分布，导致插入和一些选择减慢：插入值随机写入索引中的不同位置，插入变慢（页分割磁盘随机访问聚集索引碎片）； select 变得更慢，逻辑上相邻的行分布在磁盘和内存上的不同位置；随机值导致缓存对于所有类型的查询语句变得不太有效（使得访问局部性原则缓存所依赖的 >无效）

聚集索引，实际存储的顺序结构与数据存储的物理结构一致 。一般来说，物理序列结构只有一种。 ，一张表只能有一个聚集索引，通常是def更改为主键。如果你设置了主键，系统会默认为你添加聚集索引； [来源]

非聚集索引 聚集索引记录的物理顺序与逻辑顺序不一定相关，与数据存储的物理结构无关；一张表对应可以有多个非聚集索引，可以根据不同列的约束建立不同的要求。非聚集索引；

2）存储uuid，去掉-符号，或者使用unhex将uuid值转换为16字节的数字，存储在二进制（16）列中，检索时使用hex函数采用十六进制格式格式化；

UUID生成的值与加密哈希函数（sha1）生成的值不同：uuid分布不均匀，且有一定的顺序，因此最好增大整数