MySQL性能调优与架构设计--第九章MySQL数据库Schema设计的性能优化

很多人都认为性能是在通过编写代码（程序代码或者是数据库代码）的过程中优化出来的，其实这
是一个非常大的误区。真正影响性能最大的部分是在设计中就已经产生了的，后期的优化很多时候所能
够带来的改善都只是在解决前妻设计所遗留下来的一些问题而已，而且能够解决的问题通常也比较有限。






适度冗余-让Query尽两减少Join：
为了让我们的Query执行计划尽可能的最优化，最直接有效的方式就是尽量减少Join，而要减少Join，我们就不可避免的需要通过表字段的冗余来实现。
在大部分应用系统中，类似于查询频繁但是更新较少的数据非常非常多，很多时
候如果我们一味的追求范式化理论的Schema设计在高性能要求的系统中是非常不合适的。我个人认为，
数据库的规范化理论其实质是在概念上的单一化，虽然规范后的数据库中的表一般都较小，使表中相关
列最少。这虽然可能在某些情况下增强了数据库的可维护性，但在系统要完成一些数据的查询检索时，
可能要用复杂的Join才能实现，这势必会造成查询检索的性能低下。如果我们通过拆分Join，通过多次
简单的查询来在应用中实现Join逻辑，那所带来的网络开销将会是非常巨大的。




大字段垂直分拆-summary表优化：
大字段垂直拆分策略相对于前面介绍的适度冗余策略在做法上可以说产不多是完全相反的做法。适度冗
余策略是将别的表中的字段拿过来在自己身上也存一份数据，而大字段垂直拆分简单来说就是将自己身
上的字段拆分出去放在另外（单独）的表里面。
其实并没有任何矛盾，前面我们将别人的字段那过来，是因为我们很多时候的查询需要使用该字
段，为了减少Join带来的性能消耗才拿过来的。而我们将大字段拿出去，也是将一些我们在大部分查询
中并不需要使用该字段的时候才会拿出去。而且，在我们拿出去之前，我们肯定会通过全面的评估比较
之后才能做出拆分出去的决定。
大且访问频率低的字段。
实际上，在有些时候，我们甚至都不一定非要大字段才能进行垂直分拆。在有些场景下，有的表中
大部分字段平时都很少访问，而其中的某几个字段却是访问频率非常高。对于这种表，也非常适合通过
垂直分拆来达到优化性能的目的。




大表水平分拆-基于类型的分拆优化：
demo：
首先，置顶信息和其他讨论帖完全不会产生任何关联交互；
其次，置顶信息的变化相对于其他讨论帖来说变化很少；
再次，置顶信息的访问频率非常高；
最后，置顶信息的量和普通讨论帖来比非常之少；
通过上面的这几个分析，如果我们将置顶信息单独存放在普通讨论帖之外的其他表里面，首先不会
带来什么附加的性能消耗，而且可以使每次检索置顶信息的成本都有所下降。由于访问频率非常的高，
则因为每次检索置顶信息的成本下降而得到较大的节省。数量少而且变化不怎么频繁的特点则非常适合
使用MySQL的QueryCache，而如果和普通讨论帖在一起由于普通讨论帖的频繁变化带来group_message
表相关的QueryCache失效问题会让他无法使用QueryCache功能。


统计表-准实时优化：
简单来说就是通过定时统计数据来替代实时统计查询。
什么类型的统计信息适合通过准实时统计表来优化实现？
首先，统计信息的准确性要求并不是特别的严格；
其次，统计信息对时间并不是太敏感；
再次，统计信息的访问非常频繁，重复执行较多；
最后，参与统计数据量较大；








合适的数据类型：
优化数据类型提高性能的主要原理在于以下几个方面：
1. 通过选用更“小”的数据类型减少存储空间，使查询相同数据需要的IO资源降低；
2. 通过合适的数据类型加速数据的比较；


类型（同义词） 存储长度 最小值（无符号） 最大值（无符号）
整型数字
TINYINT 1 －128（0） 127（255）
SMALLINT 2 -32768（0） 32767（65535）
MEDIUMINT 3 -8388608（0） 8388607（16777215）
INT（INTEGER） 4 -2147483648（0） 2147483647（4294967295）
BIGINT 8 -9223372036854775808
（0）
9223372036854775807
（18446744073709551615）


小数支持
FLOAT[(M[,D])] 4or8 -3.402823466E+38-1.175494351E-38
0
1.175494351E-38～3.402823466E+38
DOUBLE[(M[,D])]（RE
AL,
DOUBLEPRECISION）
8
-1.7976931348623157E+308～-2.2250738585072014E-
308;
0
2.2250738585072014E-308～
1.7976931348623157E+308


时间类型
DATETIME 8 1001-01-0100:00:00 9999-12-3123:59:59
DATE 3 1001-01-01 9999-12-31
TIME 3 00:00:00 23:59:59
YEAR 1 1001 9999
TIMESTAMP 4 1970-01-0100:00:00


对于数字的存储，一般使用到浮点型数据的场合也不应该太多。主要出于两个原因，一个是浮点型
数据本身实际上是一个并不精确的数字，只是一个近似值，另一个原因就是完全可以通过乘以一个固定
的系数转换为整型数据来存放。这样不仅可以解决数据不精确的问题，同时也让数据的处理更为高效。


时间存储格式总类并不是太多，我们常用的主要就是DATETIME，DATE和TIMESTAMP这三种了。从存
储空间来看TIMESTAMP最少，四个字节，而其他两种数据类型都是八个字节，多了一倍。而TIMESTAMP的
缺点在于他只能存储从1970年之后的时间，而另外两种时间类型可以存放最早从1001年开始的时间。如
果有需要存放早于1970年之前的时间的需求，我们必须放弃TIMESTAMP类型，但是只要我们不需要使用
1970年之前的时间，最好尽量使用TIMESTAMP来减少存储空间的占用。


类型 存储占用最大空间
CHAR[(M)] 255characters(independentofcharset)
VARCHAR[(M)] 65535bytesor255characters
TINYTEXT[(M)] 255characters（sigle-byte）
TEXT[(M)] 65535characters（sigle-byte）
MEDIUMTEXT[(M)] 16777215characters（sigle-byte）
LONGTEXT[(M)] 4294967295characters（sigle-byte）


CHAR[(M)]类型属于静态长度类型，存放长度完全以字符数来计算，所以最终的存储长度是基于字符
集的，如latin1则最大存储长度为255字节，但是如果使用gbk则最大存储长度为510字节。CHAR类型
的存储特点是不管我们实际存放多长数据，在数据库中都会存放M个字符，不够的通过空格补上，M默认
为1。虽然CHAR会通过空格补齐存放的空间，但是在访问数据的时候，MySQL会忽略最后的所有空格，所
以如果我们的实际数据中如果在最后确实需要空格，则不能使用CHAR类型来存放。在MySQL5.0.3之前的
版本中，如果我们定义CHAR的时候M值超过255，MySQL会自动将CHAR类型进行转换为可以存入对应数
据量的TEXT类型，如CHAR(1000)会自动转换为TEXT，CHAR(10000)则会转为MEDIUMTEXT。而从
MySQL5.0.3开始，所有超过255的定义MySQL都会直接拒绝并给出错误信息，不再自动转换。


VARCHAR[(M)]属于动态存储长度类型，仅存占用实际存储数据的长度。其存放的最大长度与MySQL
版本有关，在5.0.3之前的版本VARCHAR以字符数控制最存储的最大长度，最大只能存放255个字符，占
用存储空间的实际大小与字符集有关。但是从5.0.3开始，VARCHAR的最大存储限制已经更改为字节数限
制了，扩展到可以存放65535bytes的数据，不同的字符集可能存放的字符数并不一样。也就是说，在
MySQL5.0.3之前的版本，M所代表的是字符数，而从5.0.3版本开始，M的代表意思已经是字节数了。
VARCHAR的存储特点是不管我们设定M为多大的值，真正占用的存储空间都只有我们所存入的实际数据的
大小，和CHAR不同的是VARCHAR会保留我们存入数据最后的空格，也就是说我们存入是什么样，MySQL
返回给我们的也会是什么样。在VARCHAR类型字段的数据中，MySQL会在每个VARCHAR数据中使用1个或
者2个字节用来存放VARCHAR数据的实际长度，当我们的实际数据在255字节之内的时候，会使用1字节
来存放实际长度，而大于255字节的时候，则需要使用2字节来存放。


TINYTEXT，TEXT，MEDIUMTEXT和LONGTEXT这四种类型同属于一种存储方式，都是动态存储长度类
型，不同的仅仅是最大长度的限制。四种类型的定义都是通过最大字符数来限制，但是他们的字符数限
制实际上是可以理解为字节数限制的，因为当我们使用多字节字符集的时候，实际能存放的字符书并没
最大字符数那么多，而是以单字节字符来计算的字符数。此外，由于是动态存储长度类型，所以和
VARCHAR一样，每个字段数据之前都需要一个存放实际长度的空间。TINYTEXT需要1个字节来存放，TEXT
需要2个字节，MEDIUMTEXT和LONGTEXT则分别需要3个和4个字节来存放实际数据长度。实际上，出了
MySQL内 嵌 的 最 大 长 度 限制 之 外 ， 他 们 还 受 到 客 户 端 与 服 务 器 端 的 网 络 通 信 缓 冲 区 最 大 值
（max_allowed_packet）的限制。






其他常用类型：
类型 存储占用最大空间
BIT[(M)] (M+7)/8bytes,最大(64+7)/8
SET('v1','v2'...) 1,2,4 or 8 bytes（取决于存储值的数目，最大64个值）
ENUM('v1','v2'...) 1 or 2 bytes （取决于存储值的数目，最大65535个值）


对于BIT类型，M表示每个值的bits数目，默认为1，最大为64bits。对于MySQL来说这是一个新
的类型，因为从MySQL5.0.3才开始真正实现（在之前实际上是TINYINT（1）），而且仅仅支持MyISAM
存储引擎，但是从MySQL5.0.5开始Memory，Innodb和NDB Cluster存储引擎也开始“支持”了。在
MyISAM中，BIT的存储空间很小，是真正的实现了通过bit来存储，但是在其他的一些存储引擎中就不一
样了，因为他们是转换为最小的INT类型存储的，所以占用的空间也没有节省，还不如直接使用INT类的
数据类型存放来得直观。


对于SET和ENUM类型，主要内容基本处于较少变化状态且值比较少的字段。虽然这两个字段所占用
的存储空间都较少，但是由于在使用方面较其他的数据类型要略为复杂一些，所以在实际环境中一般使
用还是较少。






选择优化的数据类型：
更小通常更好；
简单就好；越简单的数据类型，需要的CPU周期就越少。
尽量避免NULL；（除非真的要保存NULL，否则就把列定义为NOT NULL）MySQL难以优化引用了可空列的查询，它会使索引、索引统计和值更加复杂。
整数：对于存储和计算，INT(1)和INT(20)是一样的。
实数：实数有分数部分。在MySQL 5.0及以上版本中，服务器自身进行了DECIMAL运算，因为CPU并不支持对它进行直接计算。浮点运算会快一点，因为计算直接在CPU上进行。
字符串类型；CHAR是固定长度的。MySQL总是为特定数量的字符分配足够的空间。当保存CHAR值的时候，MySQL会去掉任何末尾的空格。在进行比较的时候，空格会被填充到字符串末尾。
CHAR在存储很短的字符串或长度近似相同的字符串的时候很有用。例如，CHAR适合用存储用户密码MD5哈希值，他的长度总是一样的。对于经常改变的值，CHAR也好于CARCHAR，因为固定长度的行不容易产生碎片。对于很短的列，CHAR的效率也高于VARCHAR。
BLOB和TEXT类型：BLOB和TEXT分别以二进制和字符形式保存大量数据。唯一的区别就是BLOB保存的是二进制数据，没有字符集和排序规则。
使用ENUM代替字符串类型：MySQL在内部把每个值都保存为整数，以表示值在列表中的位置，并且还保留了一份“查找表（Lookup Table）”来表示整数和字符串在表的.frm文件中的映射关系。
日期和时间类型：通常应该使用TIMESTAMP，因此它比DATETIME更节约空间。可以使用BIGINT类型并且把它以毫秒的精度保存为时间戳格式，或者使用DOUBLE保存秒的分数部分。
位集数据类型：BIT避免使用该类型。SET上也不能使用索引进行查找。


选择标示符：
整数类型：整数通常是标示符的最佳选择，因为他速度快，并且能使用AUTO_INCREMENT。
ENUM和SET通常不适合用作标识符。
字符串类型：要尽可能的避免使用字符串来做标示符。（使用了压缩索引的MyISAM表性能要慢6倍）
（如果保持UUID值，就应该移除其中的短横线，更好的办法是使用UHEX()把UUID值转化为16字节的数字，并把他保持在BINARY(16)列中。）






规范的对象命名:
1、数据库和表名应尽可能和所服务的业务模块名一致；
2、服务于同一子模块的一类表尽量以子模块名（或部分单词）为前缀或后缀；
3、表名应尽量包含与所存放数据相对应的单词；
4、字段名称也尽量保持和实际数据相对应
5、索引名称尽量包含所有的索引键字段名或者缩写，且各字段名在索引名中的顺序应与索引键在
索引中的索引顺序一致，且尽量包含一个类似于idx或者ind之类的前缀或者后缀，以表名
其对象类型是索引，同时还可以包含该索引所属表的名称；
6、约束等其他对象也应该尽可能包含所属表或其他对象的名称，以表名各自关系。