【MySQL数据库】第四章解读：Schema与数据类型优化（上）

高性能的基石：良好的逻辑、物理设计，根据系统要执行的查询语句设计schema

本章关注MySQL数据库设计，介绍mysql数据库设计与其他关系型数据库管理系统的区别

schema：【源】

schema就是数据库对象的集合，这个集合包含了各种对象如：表、视图、存储过程、索引等。为了区分不同的集合，就需要给不同的集合起不同的名字，默认情况下一个用户对应一个集合，用户的schema名等于用户名，并作为该用户缺省schema。所以schema集合看上去像用户名。

如果把database看作是一个仓库，仓库很多房间（schema），一个schema代表一个房间，table可以看作是每个房间中的储物柜，user是每个schema的主人，有操作数据库中每个房间的权利，就是说每个数据库映射的user有每个schema（房间）的钥匙。 SQL server和Oracle mysql有别

原则：

1、更小的通过更好，尽量使用可正确存储数据的最小的数据类型（占更少的磁盘 内存 CPU缓存，处理时需要CPU周期更少：更快），但能罩得住数据，存不下就尴尬了

2、简单就好：简单类型（更少CPU周期），使用MySQL内建类型存时间，整型存ip，整型较字符代价低（字符集和校对排序规则使字符较复杂）

3、尽量避免null：最好指定为not null

*）null列使用更多的存储空间，mysql里需要特殊处理

*）null使索引、索引统计和值比较更复杂；可为null的列被索引时，每个索引记录需额外的字节

例外：InnoDB使用单独位bit存储null,so对于稀疏数据（很多值为null）有很好的空间效率，不适合MyISAM

4.1.1整数类型【参考】

整数whole number

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

1、存储值的范围：

set

创建表时，就指定SET类型的取值范围 ：属性名 SET(&＃39;值1&＃39;,&＃39;值2&＃39;,&＃39;值3&＃39;...,&＃39;值n&＃39;)，“值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上通索引查找

在整数列按位操作：

代替set的方式：使用整数包装一系列的位：可把8个位包装到tinyint中，且按位操作来使用，为位定义名称常量来简化这个工作，但是这样查询语句较难写且难理解

4.1.6选择标识符identifier

标识列：自增长列【源】

1）可不用手动插入值，系统提供默认序列值;2)不要求和主键搭配 ; 3)要求是unique key；

4）一个表最多一个；5）类型只能是数值；5）可通过set auto_increment_increment=3;

选择标识列类型时

考虑存储类型、mysql对这种类型怎么执行计算和比较，确定后确保在all关联表中使用same类型，类型间要精确匹配；

技巧：

1、整数类型：整数通常最好的选择，很快且可使用auto_increment

2、enum和set类型，存储固定信息

3、字符串：避免，耗空间较数字慢，myisam表特别小心（默认对字符串压缩使用、查询慢）

1）完全“随机”字符串MD5/SHA1/UUID函数生成的新值 会任意分布在很大的空间内，导致insert及部分的select变慢：插入值随机的写到索引的不同位置，insert变慢(页分裂 磁盘随机访问 聚簇索引碎片）；select变慢、逻辑上相邻的行分布在磁盘和内存不同的地方；随机值导致缓存对all类型的查询语句效果都变差（使缓存赖以工作的访问局部性原理失效）

聚簇索引，实际存储的循序结构与数据存储的物理结构一致，通常来说物理顺序结构只有一种，一个表的聚簇索引也只能有一个，通常默认都是主键，设置了主键，系统默认就为你加上了聚簇索引；【源】

非聚簇索引记录的物理顺序与逻辑顺序没有必然的联系，与数据的存储物理结构没有关系；一个表对应的非聚簇索引可以有多条，根据不同列的约束可以建立不同要求的非聚簇索引；

2）存储uuid，移除-符号，或者用unhex转换uuid值为16字节的数字，且存储在binary（16）列中，检索时通过hex函数格式化为16进制格式;

UUID生成的值与加密散列函数(sha1)生成的值不同特征：uuid分布不均匀，有一定顺序，不如递增整数

当心自动生成的schema：

严重性能问题，很大的varchar、关联列不同的类型；

orm会存储任意类型的数据到任意类型的后端数据存储中，并没有设计使用更优的类型存储，有时为每个对象每个属性使用单独行，设置使用基于时间戳的版本控制，导致单个属性会有多个版本存在；权衡

4.1.7特殊类型数据：空

相关文章：

【MySQL数据库】第三章解读：服务器性能剖析（上）

【MySQL数据库】第三章解读：服务器性能剖析 （下）

以上就是【MySQL数据库】第四章解读：Schema与数据类型优化（上）的详细内容，更多请关注 第一PHP社区 其它相关文章！