schema与数据类型优化

选择正确的数据类型对于获得高性能至关重要。

几个简单的原则：

在保证正确的前提下，选择更小的类型。应该尽量使用可以正确存储数据的最小数据类型 (1)。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和 CPU缓存，并且处理时需要的CPU周期也更少。

简单数据类型的操作通常需要更少的CPU周期。整型比字符串性能更高，因为字符集和排序规则使字符串比较比整型更复杂，例子：一个是应该使用Mysql的内建类型而不是字符串储存日期和时间，另外一个是应该用 整型存储IP地址。

最好指定列为not null，除非null有用处。

0)NULL的缺点？

Mysql很难优化包含NULL的列，因为可为NULL的列使得索引，索引统计和值比较都更复杂。

(1)可为NULL的列需要更多的储存空间，NULL列在Mysql里需要特殊处理。

(2)可为NULL的列在被索引时，每个索引记录需要一个额外的字节。

(3)在MyISAM中NULL列可能导致固定大小的索引变为可变大小的索引。

要避免为可为NULL的列添加索引

InnoDB的一点不同之处：InnoDB会使用不同的bit储存NULL值，MyISAM不会。

为列选择数据类型的步骤：

确定合适的大类型：数字，字符串，时间。

选择具体类型，很多Mysql的数据类型可以储存相同类型的数据，只是存储的长度和范围不一样，允许的精度不同，或者需要的物理空间不同。相同大类型的不同子类型有时也有一些特殊的行为和属性。

栗子：datetime和timestamp列都可以储存相同类型的数据：时间和日期，精确到秒，但是timestamp只使用了datetime一半的储存空间。

Mysql为了兼容性支持了跟多别名，如integer，bool，numeric。它们只是别名，若在建表时使用了别名，然后用show create table检查，会发现Mysqk报告的是基本类型，而不是别名。

3.1.1 整数类型

有两种类型的数字：整数和实数。

整数类型：tinyint（8位），smallint（16位），mediumint（24位），int（32位），bigint（64位）。它们可以存储的值的范围从−2（N−1）到2（N−1）−1，其中N是 存储空间的位数。

整数类型有可选的unsigned属性，表示非负。这大致可以使 正数的上限提高一倍。

有符号和无符号类型使用相同的存储空间，并具有相同的性能，因 此可以根据实际情况选择合适的类型。这些类型决定了Mysql是在内存和磁盘中是怎么保存数据的。然而，整数计算使用64位的bigint，即使在32位环境中也是如此。

Mysql可以为整数类型指定宽度，例如int(11)，这不会限制值的合法范围，只是规定了交互工具中显示字符的个数，仅此而已。

3.1.2 实数类型

实数是带有小数部分的数字，除了小数，它也可以使用decimal储存比biginnt还大的整数。Mysql既支持精确类型，也支持不精确类型。

FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似运算。

DECIMAL用于储存精确的小数，现代版本的DECIMAL支持精确运算，DECIMAL只是一个储存类型。

CPU不支持DECIMAL的直接运算，精确的运算是通过Mysql服务器自身用算法做到的。因为CPU支持原生浮点运算，所以原生浮点运算效率更高。

浮点类型和DECIMAL类型都可以指定精度。对于DECIMAL列，可以指定小数点前后所允许的最大位数，这回影响到空间消耗，现代MYSQL都会将数字打包保存到一个二进制字符串中。

DECIMAL只是一个储存类型，在计算时会转化为DOUBLE类型。因为DECIMAL需要额外的空间和运算开销，所以在需要对小数进行精确运算时才使用DECIMAL。

在数据量较大时，可以考虑使用BIGINT代替DECIMAL，比如储存金额，先乘以100后再存，取时再除以100。

3.1.3 字符串类型

每个字符串列都可以定义自己的字符集和排序规则。这些东西在很大程度上会影响性能。

VARCHAR和CHAR是两种最主要的字符串类型。不幸的是，很难精确 地解释这些值是怎么存储在磁盘和内存中的，因为这跟存储引擎的具体 实现有关。存储引擎存储CHAR或者VARCHAR值的方式在内存中和在磁盘上可能不一样，所 以MySQL服务器从存储引擎读出的值可能需要转换为另一种存储格式。

(1)varchar

varchar类型用于储存可变长的字符串。它比定长类型更节省空间！

varchar使用1或2个额外字节记录字符串长度，在update将varchar变为更长时，需要做额外的工作，若这是内存页或磁盘页没有更多的空间可以储存，那这时不同的储存引擎有不同的办法：

MyISAM会将行拆成不同的片段储存，InnoDB则需要分裂页来使行放进页内。

使用VARCHAR的合理情况：

（1）字符串的最大长度远大于平均长度。

（3）使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节进行储存。

（4）现代Mysql在存取varchar类型时会保存末尾空格。

InnoDB可以把过长的varchar储存为BLOB。

(2)char

char类型是定长的。当保存char时，Mysql会自动删除末尾空格。char会根据需要在头部填充空格以方便比较。

（2）储存长度相近的字符串，例如，CHAR非常适合储存像MD5这样的固定长度的字符串。

（3）储存经常变更的数据，比VARCAHR更好。，因为定长的CHAR类型不容易产 生碎片。对于非常短的列，CHAR比VARCHAR在存储空间上也更有效率。

数据如何存储取决于存储引擎，并非所有的存储引擎都会按照相同 的方式处理定长和变长的字符串。Memory引擎只支持定长的行，即使 有变长字段也会根据最大长度分配最大空间(7)。不过，填充和截取空格 的行为在不同存储引擎都是一样的，因为这是在MySQL服务器层进行 处理的。

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

二进制字符适用场景：

（2）对字节码比较而不是字符比较

当需要存储二进制数据，并且希望MySQL使用字节码而不是字符 进行比较时，这些类型是非常有用的。二进制比较的优势并不仅仅体现 在大小写敏感上。MySQL比较BINARY字符串时，每次按一个字节，并 且根据该字节的数值进行比较。因此，二进制比较比字符比较简单很 多，所以也就更快。

字符类型是：TINYTEXT，SMALLTEXT，TEXT，MEDIUMETXT，LONGTEXT。

二进制类型是：TINYBLOB，SMALLBLOB，BLOB，MEDIUMBLOB，LONGBLOB。

BLOB是SMALLBLOB的同义词，TEXT是SMALLTEXT的同义词。

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

BLOB与TEXT的不同之处：

（2）BLOB类处储存的是二进制数据。

（2）BLOB类型没有字符集和排序规则。

Mysql对BLOB和TEXT的排序规则与其他类型不同：：它只对每 个列的最前max_sort_length字节而不是整个字符串做排序。如果只需 要排序前面一小部分字符，则可以减小max_sort_length的配置，或者 使用ORDER BY SUSTRING（column，length）。

Mysql不能将BLOB和TEXT列的全部长度的字符串进行索引，也不能使用这些索引消除排序。

使用枚举(ENUM)代替字符串类型

Mysql在储存枚举列时非常紧凑，会根据列表值的数量压缩到1-2个字节，Mysql会在内部将每个值在列表中表示为整数，并且在表的.frm文件中保存"数字-字符串"映射关系的"查找表"。

使用 ‘+ 0'的方式可以使字段显示为数字。枚举的默认排序规则是按照定义时的顺序，可以使用FIELD()函数来改变排序规则，不过这会导致Mysql无法利用索引消除排序。frm文件是放在/var/lib/mysql/数据库名/中的。

枚举的缺点是在使用ALTER TABLE改变枚举值时代价非常大。

Mysql能储存的最小时间粒度是秒。关于日期和时间的类型有YEAR，DATE，TIME，DATETIME，TIMESTAMP。

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

默认情况下，Mysql使用一种可排序的，无歧义的格式显示DATETIME的值，如"2008-08-08 22:30:00"。这是ASNI标准定义的日期和时间表示方法。

TIMESTAMP类型保存了从1970年1月1日午夜至今以来的秒数，TIMESTAMP只使用了4个字节，只能表示1970到2038年，Mysql可以使用FROM_UNIXTIME()函数将时间戳转换为日期，并提供UNIX_TIMESTAMP()函数把日期转换为Unix时间戳。TIMESTAMP的存储格式在各个版本都是一样的。

TIMESTAMP显示的值依赖与时区。Mysql服务器，操作系统，以及客户端链接都有时区设置。

TIMESTAMP列默认为not null，这时若不指定具体值，Mysql会添加一个默认的当前值。这也和其他的数据类型不一样。

（3）date

DATE类型用于具有日期部分但没有时间部分的值。date保存精度到天。MySQL DATE以'YYYY-MM-DD'格式检索和显示值。支持的范围是 '1000-01-01'到 '9999-12-31'。

（4）timestamp与datetime相同点

DATETIME和TIMESTAMP 都可以跟小数部分（可以精确到微秒）。MySQL在插入到一个DATETIME或一 TIMESTAMP列时，列中的值的任何小数部分都被存储而不是被丢弃。包含小数部分时，DATETIME和TIMESTAMP的格式都是'YYYY-MM-DD hh:mm:ss[.fraction]'，小数部分应始终与其余时间使用小数点分隔。

（5）timestamp与datetime的区别

a）DATETIME的默认值为null；TIMESTAMP的字段默认不为空（not null）,默认值为当前时间（CURRENT_TIMESTAMP），如果不做特殊处理，并且update语句中没有指定该列的更新值，则默认更新为当前时间。

这个区别就解释了为什么平时我们都不用可以管这个字段就能自动更新了，因为多数时候用的是timestamp；而此处用的是datetime，不会有自动更新当前时间的机制，所以需要在上层手动更新该字段

b）DATETIME使用8字节的存储空间，TIMESTAMP的存储空间为4字节。因此，TIMESTAMP比DATETIME的空间利用率更高。

这个区别解释了为啥timestamp类型用的多

c）两者的存储方式不一样 ，对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。而对于DATETIME，不做任何改变，基本上是原样输入和输出。

d）两者所能存储的时间范围不一样

timestamp所能存储的时间范围为：’1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’；

datetime所能存储的时间范围为：’1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’。

CURRENT_TIMESTAMP为什么能用于datetime类型?

在mysql 5.6之前的版本，CURRENT_TIMESTAMP只能用于timestamp类型，

5.6版本之后，CURRENT_TIMESTAMP也能用于datetime类型了

select version()查了一下数据库发现确实版本是5.6.29

BIT

可以使用BIT列在一列中存储一个或多个true/false值。BIT（1）定 义一个包含单个位的字段，BIT（2）存储2个位，依此类推。BIT列的最 大长度是64个位。

BIT的储存因储存引擎而异。MyISAM会打包存储所有的BIT 列，所以17个单独的BIT列只需要17个位存储（假设没有可为NULL 的列），这样MyISAM只使用3个字节就能存储这17个BIT列。其他 存储引擎例如Memory和InnoDB，为每个BIT列使用一个足够存储的 最小整数类型来存放，所以不能节省存储空间。

Mysql把BIT类型当作字符串类型，而不是数字类型。

字符串加b前缀代表二进制，BIT默认解析出来的是字符串

SET

SET类型可以储存更多的true/false值，有效的利用了储存空间，并且Mysql有FIND_IN_SET和FIELD()这样的函数，能方便的在查询中使用。

SET的主要缺点是改变列的定义的代价高:ALTER TABLE,这对大表是非常昂贵的操作，另外，无法在SET列上建立索引。

为标识列选择合适的数据类型非常重要。一般来说更有可能用标识列与其他值进行比较（例如，在关联操作中），或者通过标识列寻找其他列。标识列也可能在另外的表中作为外键使用， 所以为标识列选择数据类型时，应该选择跟关联表中的对应列一样的类型，包括像UNSIGNED这样的附加属性。

当选择选择标识符时，不仅仅需要考虑储存类型，还需要考虑Mysql对这种类型怎么执行计算和比较的。例如：Mysql在内部使用整数储存ENUM和SET类型，然后在做比较操作时转换为字符串。

如果使用的是InnoDB储存引擎，将不能在数据类型不是完全匹配的情况下创建外键，否则会报错！

因为使用不相同的类型时，即使没有性能问题，在比较操作时隐式的类型转换也可能导致很难发现的错误。这种错误可能会很久之后才会出现，那时已经忘记是在比较不同的数据类型！

在满足值的范围的要求，并且预留未来增长空间的前提下，应该选择最小的数据结构！

小技巧：

整数类型通常是标识列最好的选择，因为他很快并且可以使用AUTO_INCREMENT。

ENUM和SET不能选择作为选择标识符。

字符串类型。如果可能，应该避免使用字符串类型作为标识列，因为它们很耗空间，并且通常比数字类型慢。

应该使用无符号整数储存IP地址，Mysql提供了INET_ATON()和INET_ATOA()函数在这两种表示方法之间转换。

3.2  Mysql schema设计中的陷阱

太多的列

Mysql的储存引擎API在工作时，需要在工作时在服务器层和储存引擎曾之间进行行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。若使用了非常宽的表，只有一小部分列会被使用，这时转换代价就非常高。

需要注意的地方：

太多的关联：Mysql限制了每个关联操作最多只能操作61张表。一条经验是若希望查询执行的快并且并发执行的好，单个查询最好在12个表以内做关联。

太多的枚举：这种设计很凌乱，并且需要添加枚举值时使用ALTER TABLE代价是非常大的。

NULL：即使要使用空值，也要使用0,空字符串等代替

注意：Mysql会在索引中储存NULL值，Oracle则不会。

3.3  范式和反范式

对于给定的数据通常都有很多种表示法，从完全范式化到完全的反范式化。

范式化的数据库，每个事实数据都会出现并且只出现一次，相反，反范式化的数据库中，信息是冗余的，可能会储存在多个地方。

并不是范式越高越好，范式高了，必然联系就多了，联系太多反而降低了查询的效率。

1NF的定义为：符合1NF的关系中的每个属性都不可再分。

(个人浊见，那些范式公式短时间看不懂，暂且这么理解吧)

对于写密集的场景，范式化设计很有帮助。范式的优点：

（1）范式化的更新操作通常比反范式要快。

（2）较好的范式化，重复数据很少或没有，所以只需修改很少的数据。

（3）范式化的表更小，可以更好的放在内存中，所以操作更快。

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

范式化的主要缺点：就是需要关联。也有可能使一些索引策略无效。

反范式化的表可以避免关联。反范式化对查询友好，因为不需要关联，即使没用索引，最差的全表扫描也是顺序IO，这种情况在数据比内存大时比关联要快很多。

单独的表也能使用更有效的索引策略。

3.3.3 混用范式化和反范式化

对经常变更的数据进行范式化，对经常需要查询的数据进行适当的反范式化。完全范式化和完全反范式化在实际中基本没有，是实验室中的东西。

3.3.4 数据库三大范式

数据库的设计范式是数据库设计所需要满足的规范，满足这些规范的数据库是简洁的、结构明晰的，同时，不会发生插入（insert）、删除（delete）和更新（update）操作异常。

1.第一范式（1NF）:列不可再分

1.每一列属性都是不可再分的属性值，确保每一列的原子性。

2.两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据。

2.第二范式（2NF）属性完全依赖于主键

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主键。

3.第三范式（3NF）属性不依赖于其它非主属性，属性直接依赖于主键

数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系。像：a-->b-->c  属性之间含有这样的关系，是不符合第三范式的。

总结：三大范式只是一般设计数据库的基本理念，可以建立冗余较小、结构合理的数据库。如果有特殊情况，当然要特殊对待，数据库设计最重要的是看需求跟性能，需求>性能>表结构。所以不能一味的去追求范式建立数据库。

3.4  缓存表和汇总表

在保存衍生的冗余数据时，对性能最好的办法是保存在原数据的同一张表中，使用缓存表和汇总表虽然带来了少量的脏数据，但是对于避免原表便复杂，提升原表的实时操作效率是很有帮助的。

缓存表是表示储存那些从原表中获取的数据。比如保存使用GROUP BY语句聚合数据的表。

1,物化视图

物化视图其实是预先计算并且储存在磁盘上的表，可以通过各种各样的策略 更新。Mysql并不支持原生物化视图。但是使用第三方工具可以实现这种功能。

2,计数器表

如果在表中保存计数器，则更新操作很可能碰到并发问题。创建一张独立的表储存计数器是个好主意。

3,更快的读和更慢的写

为了提升查询速度，经常需要一些额外索引，冗余列和缓存表，这些在加快了读的同时，也增加了写的负担，也需要额外的维护任务。写操作变慢还增加了并发难度。所以要尽量将数据分清楚，哪些需要经常写，哪些需要经常读。

3.5  加快ALTER TABLE操作的速度

Mysql执行大部分修改表的操作的方法是：用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表，这无疑浪费时间。

ALTER TABLE操作将导致Mysql服务中断。

两种解决方法：

一是先在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换。

二是使用"影子拷贝"，其技巧是用要求的表结构创建一个和原表无关的新表，然后通过重命名和删除操作交换两张表。

不是所有的ALTER TABLE操作都是引起表重建！

例如修改列的默认值使用ALTER COLUMN而不使用MODIFY COLUMN操作，ALTER COLUMN操作会仅仅修改.frm文件

1,只修改.frm文件

下面这些操作不需要重建表：

移除(不是增加)一个列的AUTO_INCREMENT属性。

增加，移除，或更改ENUM和SET常量，若移除的是一个已存在的常量，查询会返回一个空字符串。

基本的技巧是为想要的表结构创建一个新的.frm文件，然后替换掉已存在.frm文件：

1.创建一张有相同结构的空表，并进行所需要的修改。

2.执行FLUSH TABLES WITH READ LOCK.这将关闭所有正在使用的表，并且禁止任何表被打开。

4.执行UNLOCK TABLES释放第二步的锁。

2,快速创建MyISAM索引

为了高效地将数据载入到MyISAM表中，一个常用的技巧是先禁用索引，载入数据，然后启用索引。这样构建索引的工作被延迟到数据完全载入后。这样也可以对排序数据进行索引。

禁用索引使用：DISABLE KEYS。

启用索引使用：ENABLE KEYS。

但这种方式对唯一索引无效。因为在MyISAM中需要对每一行检查唯一性。一旦索引大小超过了有效内存大小，那载入操作就会很慢。

3.6  主键的选择

非分布式架构直接套用自增id做主键

小规模分布式架构用uuid或者自增id+步长做主键

大规模分布式架构用自建的id生成器做主键，参考twitter的snowflake算法

一.自增id

1.性能消耗

从上面的原理可以得知，Mysql会按照键值的大小进行顺序存放，如果我们设置自增id为主键，这个时候主键是按照一种紧凑的接近顺序写入的方式进行存储数据。

如果我们用其他字段作为主键的话，此时Mysql不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多额外的开销，同时频繁的移动、分页操作造成了大量的碎片。

2.资源消耗

根据mysql官方的文档，非聚集（二级）索引都包含主键索引的列，所以如果主键太大，非聚集索引会占用更多的磁盘空间。

How Secondary Indexes Relate to the Clustered Index

二.uuid或者自增id+步长

小规模分布式在数据量不大，使用成本最低的方式就直接用uuid，或者自增id+步长的方式，省时省力。

当数据量比较大，又是分布式架构的时候，可能我们需要考虑各种分库分表方案了，这个时候就不能贪图方便，必须有更好更长远的方案来替代。自建id生成器，可以保证全局唯一，可以参考snowflake的算法方案，具体实施也可以根据自身业务进行调整算法。唯一麻烦的就是id生成器的高可用问题，需要多加注意。

关系数据库主要依靠主键区分不同的记录,主键又有自然主键和代理主键之分。

　　自然主键：指业务相关,由用户指定,且能唯一标识数据库中的任意一条记录。就是充当主键的字段本身具有一定的含义，是构成记录的组成部分，比如学生的学号，除了充当主键之外，同时也是学生记录的重要组成部分。

　　代理主键：是指与业务无关且能唯一标识数据库中记录,一般是数据库自动生成的。就是充当主键的字段本身不具有业务意义，只具有主键作用，比如自动增长的ID。

　　在Hibernate中应使用代理主键。在Hibernate中,Hibernate依靠对象表示来区分不同的持久化，而对象标识符则可以通过Hibernate内置的表示生成器来产生。

3.7  字符集的选择

3.7.1 Mysql支持的字符集

MySQL服务器可以支持多种字符集，不同的字段都可以使用不同的字符集。

查看所有可用字符集：

show character set;

select * from information_schema.character_sets;

MySQL的字符集包括字符集（CHARACTER）和校对规则（COLLATION）两个概念。其中字符集定义MySQL存储字符串的方式，校对规则用来定义比较字符串的方式。字符集和校对规则是一对多的关系。

注：目前线上服务器上用utf8mb4 和utf8mb4_bin。

　　校对规则可用 show collation like ‘xxx’ 查看。

　　校对规则命名约定：已其相关的字符集名开始，通常包括一个语言名，并且以_ci(大小写敏感)、_cs（大小写不敏感）或者_bin（二院，即比较是基于字符编码的值而与language无关，区分大小写）结束。

3.7.2 MySQL字符集的设置

MySQL的字符集和校对规则有4个级别的默认设置：服务器级、数据库级、表级和字段级。

1）服务器字符集和校对规则

[mysqld]

character-set-server=utf8mb4

mysqld --character-set-server=utf8mb4

shell> cmake . –DDEFAULT_CHARSET=utf8mb4

查看当前服务器的字符集和校对规则：

show variables like ‘character_set_server’;

show variables like ‘collation_server’;

如果没有设置则默认是latin 1字符集。（MySQl5.6版本）

2）数据库字符集和校对规则

在数据库创建的时候指定，也可以在创建完数据库后通过alter database 命令进行修改。

查看当前数据库字符集和校对规则：

show variables like ‘character_set_database’;

show variable like ‘collation_database’;

3）表字符集和校对规则

在建表的时候可以指定字符集和校对规则，也可以通过alter table 命令进行修改（一般不用）。如果没有指定，则使用数据库的规则。

查看表的字符集和校对规则：show create table table_name;

4）列字符集和校对规则

在建表的时候可以指定字符集和校对规则，也可以通过alter table 命令进行修改（一般不用）。如果没有指定，则使用数据库的规则。

查看表的字符集和校对规则：show create table table_name;

5）连接字符集和校对规则

　　以上4中设置方式，确定的事数据保存字符集和校对规则，对于实际应用访问来说，还存在客户端和服务器之间交互的字符集和校对规则的设置。

客户端和服务器的交互操作，MySQL提供了三个不同的character_set_client、character_set_connection和character_set_result,分别表客户端、连接和返回结果的字符集。通常情况下需要确保三者一样。可以用set names utf8mb4；命令来同时设置三个参数。

　　另一个简便方法：在my.cnf里设置客户端

[mysql]

default-character-set=utf8mb4 

　　如果在应用开始阶段没有正确的选择字符集，在运行一段时间后才发现存在不能满足要求需要调整，又不想丢弃这段时间的数据，那么就要进行字符集的修改。字符集的修改不能直接通过 “alter database character set ‘xxx” 或者”alter table table_name character set xxx”命令进行调整，这两个命令都没有更新已有记录的字符集，而只是对新创建的表或者记录生效。

　　字符集修改，需要先导出数据，经过适当的调整重新导入后才可完成。

下面模拟将latin1字符集的数据库修改成utf8mb4字符集的数据库。

1）导出表结构：

mysqldump –uroot –p –default-character-set=utf8mb4 –d database_name > createtab.sql；

-d 表示指导处表结构，不导出数据。

2）手工修改createtab.sql中表结构定义中的字符集为新字符集。

3）确保记录不在更新，导出所有记录（锁表导出）

mysqldump –uroot –p –quick –no-create-info –extended-insert –default-character-set=latin1 database_name > data.sql

--quick 用于转储大的表。他强制mysqldump从服务器一次一行地检索表中的行而不是一次检索所有行，并在输出前将他缓存在内存中。

--extended-insert 使用包括几个values列表的多行insert 语法。这样使转储文件更小，重载文件时可以加速插入。

--no-create-info 不导出建表语句

--default-character-set=latin1 按照原有的字符集导出所有数据

4）打开data.sql，将set names latin1修改成set names utf8mb4

5）使用新的字符集创建新的数据库

create database databasename default charset utf8mb4;

6）创建表，执行createtab.sql

mysql –uroot –p database_name < createtab.sql

7）导入数据

mysql –uroot –p database_name < data.sql

3.8  适当的数据冗余

1.被频繁引用且只能通过Join 2张(或者更多)大表的方式才能得到的独立小字段。

2.这样的场景由于每次Join仅仅只是为了取得某个小字段的值，Join到的记录又大，会造成大量不必要的I0,完全可以通过空间换取时间的方式来优化。不过，冗余的同时需要确保数据的一致性不会遭到破坏， 确保更新的同时冗余字段也被更新。

3.9  适当拆分

3.9.1 拆分前需要做的事情：

第一步：采用分布式缓存redis、memcached等降低对数据库的读操作。

第二步：如果缓存使用过后，数据库访问量还是非常大，可以考虑数据库读、写分离原则。

第三步：当我们使用读写分离、缓存后，数据库的压力还是很大的时候，这就需要使用到数据库拆分了。

数据库拆分原则：就是指通过某种特定的条件，按照某个维度，将我们存放在同一个数据库中的数据分散存放到多个数据库（主机）上面以达到分散单库（主机）负载的效果。

3.9.2 第一步，首选垂直拆分

一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同的数据库上面，这样也就将数据或者说压力分担到不同的库上面 。

比如淘宝中期开始的数据库端按照业务垂直拆分：按照业务交易数据库、用户数据库、商品数据库、店铺数据库等进行拆分。

采用垂直拆分

优点：

1.拆分后业务清晰，拆分规则明确。

2.系统之间整合或扩展容易。

3.数据维护简单。

缺点：

1.部分业务表无法join，只能通过接口方式解决，提高了系统复杂度。

2.受每种业务不同的限制存在单库性能瓶颈，不易数据扩展跟性能提高。

3.事务处理复杂。

3.9.3 第二步：其次才是水平拆分

水平拆分的典型场景就是大家熟知的分库分表。

垂直拆分后遇到单机瓶颈，可以使用水平拆分。相对于垂直拆分的区别是：垂直拆分是把不同的表拆到不同的数据库中，而水平拆分是把同一个表拆到不同的数据库中。

相对于垂直拆分，水平拆分不是将表的数据做分类，而是按照某个字段的某种规则来分散到多个库之中，每个表中包含一部分数据。简单来说，我们可以将数据的水平切分理解为是按照数据行的切分，就是将表中 的某些行切分到一个数据库，而另外的某些行又切分到其他的数据库中。

分库分表需要涉及到对应的SQL路由规则主库备库等，例如：淘宝设计了一套TDDL来解决这些问题，应用端只需配置对应的规则即可，对应用端的没有任何侵入的设计。

水平拆分，总之，一般先分库，如果分库后查询仍然慢，于是按照分库的思想开始做分表的工作数据库采用分布式数据库（所有节点的数据加起来才算是整体数据），文件系统采用分布式文件系统任何强大的单一服务器都满足不了大型系统持续增长的业务需求，数据库读写分离随着业务的发展最终也将无法满足需求，需要使用分布式数据库及分布式文件系统来支撑。