【MySQL】数据库建表策略与数据优化策略

【MySQL】数据库建表策略与数据优化策略

一、选择优化的数据类型原则

MySQL支持的数据类型很多，以下几个原则有助于类型选择：

1、最小数据类型原则

应该尽可能使用可以正确存储数据的最小类型数据。更小的数据通常更快，因为占用更小磁盘、内存、CPU缓存，CPU周期更少。

但是要确保没有低估需要存储的值的范围，因为在schema中很多地方增加数据类型的范围是很耗时的操作。

2、简单操作原则

简单数据类型操作会使用更少的CPU周期，例如整形比字符操作的代价低，例如时间的存储上，是使用MySQL内置类型还是字符串。

3、尽量避免NULL

很多表包含可以为NULL的列，即使应用程序并不需要保存NULL。因为可为null是列的默认属性，最好可以制定列为NOT NULL。除非真的需要存储NULL值。

因为如果查询中包含有NULL值的列，对MySQL很难优化，因为有NULL对索引与索引统计和值的比较更加复杂。

可为null值的列会用更多的存储空间，在MySQL中需要特殊的处理。当可以为null的列被索引的时候，每个索引记录需要存储一个额外的字节，在MyISAM里面甚至会导致固定大小的索引变成可变大小的索引。

通常将NULL改为NOT NULL 性能提升较小，所以没有必要在调优的时候先在schema中修改这种情况，但是如果计划在某列建索引，应该尽量避免设计为可为null值的列。

也有例外，InnoDB使用单独的位bit存储NULL值，所以稀疏数据有很好的空间效率，不适用于MyISAM

二、各个数据类型分析

1、整数类型

两种整数类型：整数(whole number)和实数(real number)。

可以使用以下几种数据类型：TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT。分别使用8， 16， 24， 32，64位存储。

整数类型有可选的UNSIGNED属性，表示不允许负值。

有符号和无符号类型使用相同的存储空间，并且性能类似，可以根据实际情况选择合适的数据。

2、实数类型

实数是带有小数部分的数字，不只是为了存储小数部分，可以使用DECIMAL存储比BIGINT更大的整数。

FLOAT和DOUBLE支持使用标准浮点运算来进行近似计算。

DECIMAL类型用于存储精确的小数，但是CPU无法直接对DECIMAL类型计算 所以MySQL服务器自身实现了decimal的高精度计算，相对而言比cpu直接计算原生浮计算，所以cpu计算会更快。

因为decimal需要额外的空间计算开销，所以应该尽量只在对小数进行精确计算才用decimal–例如财务数据存储，数据量大的时候使用BIGINT代替DECIMAL，将需要存储的货币单位小数乘以相应的倍数就可以。假设数据要精确到万分之一，那么就金额乘以100w结果存在bigint中，就可以避免浮点计算不精确和decimal精确计算代价太高的问题。

3、字符串类型

（1）varchar与char

①varchar：VARCHAR类型用于存储可变字符串，比定长字符串更加节省空间。varchar利用1-2个字节记录字符串长度。

②char：定长。char会利用采用空格填充，char适合存储很短的字符串。例如MD5值。

（2）BLOB与TEXT

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

他们分别属于两组不同的数据类型家族

①字符类型：TINYTEXT, SMALLTEXT(与TEXT同义), TEXT, MEDIUMTEXT, LONGTEXT

②对应的二进制类型：TINYBLOB, SMALLBLOB(与BLOB同义), BLOB, MEDIUMBLOB, LONGBLOB.

Memery引擎不支持BLOB与TEXT，最好的解决方法就是经历避免使用BLOB与TEXT。实在无法避免就是BLOB字段中使用SUBSTRING(colum, length)将列值转化为字符串。

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

mysql > CREATE TABLE enum_test( e ENUM('fish', 'apple', 'dog') NOT NULL);
	> INSERT INTO enum_test(e) VALUES('fish'), ('dog'), ('apple');

（4）日期和时间类型

MySQL可以使用许多类型来保存日期和时间值，例如YEAR和DATE。MySQL能够存储的最小时间粒度为秒（mariaDB支持微秒级别的时间类型），MySQL也可以用微秒级别临时运算。

大部分存储时间类型都没有替代品，因此没有什么是最佳虚线则。MySQL有两种时间类型datetime和timestamp。有一些区别：

DATETIME：可以保存大范围的值：1001年到9999年，精度为秒。将日期与时间封装到格式YYYYMMDDHHMMSS的整数中，与时区无关，使用8个字节存储。

TIMESTAMP：保存了从1970年1月1日午夜（格林威治时间）以来的秒数。和UNIX时间戳是一样。但是TIMESTAMP只用4个字节保存，所以范围比DATETIME小很多。而MYSQL提供了FROM_UNIXTIME()函数，将UNIX时间戳转化为日期，并提供了UNIX_TIMESTAMP()函数将日期转化为UNIX时间戳。

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

（5）位数据类型

BIT：可以在一列中存储一个或多个true/false。应该谨慎使用BIT甚至是避免。如果想要一个bit中存储可以使用CHAR(0),保存空值或空字符串。

SET：如果需要保存很多true/false，可以考虑合并这些列到一个set数据类型。主要缺点是改变列的定义代价很高，需要ALTER TABLE.对于大表是很高的代价。一般来说 set列是无法通过索引查找的。

一种替代SET的方法是用一个整数包装一系列的位。

（6）选择标识符(identifier)

为标识列(identifier column)选择合适的数据类型非常重要。一般来说更有可能使用标识列与其他值进行比较，例如在关联操作时，或通过标识列寻找其他列。

标识列也可能在另外的表中作为外键使用。

当选择标识列的类型时，不仅仅需要考虑存储类型，还需要考虑MySQL对于这种类型怎么执行计算和比较。

整数类型：整数类型是标识的最好的选择，因为他们很快而且可以使用AUTO_INCREMENT

避免使用ENUM与SET类型，字符串类型（消耗空间还慢）。

三、MySQL的schema设计中的陷阱

1、陷阱一：太多的列

MySQL存储引擎API工作的时候需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务层将缓冲解码为各个列。从行缓冲中将编码过的列转化为行数据结构的操作代价很高。如果使用非常宽的表，但是只有一小部分的列会被使用，这时候转换代价就很高。

2、陷阱二：太多的关联

“实体-属性-值”EAV设计模式是一个糟糕的设计模式。尤其是在MySQL下工作情况很差。eva需要很多自关联，mysql每个关联操作最多只可以有61个表。如果希望查询执行的快速并且并发好，单个插叙最好在12个表以内做关联

3、陷阱三：全能的枚举

注意防止过度使用枚举。

4、陷阱四：变相的枚举

枚举列循序在列中存储一组定义值中的单个值。SET允许在列中存储一组定义值中的一个或多个值。这种时候可能比较容易出现混乱。

crate table (
	is_default set('Y', 'N') NOT NULL default 'N'
)

如果这里真假不同时出现则应该使用枚举列代替集合列。

5、陷阱五：不要强行不用NULL

虽然用NULL会带来上文中的一些问题，但是如果强行不用NULL用其他数字代替，会导致编码过于复杂带来问题，因此合适的时候也要果断用NULL。

四、范式和反范式

1、范式的优点和缺点

范式化设计，尤其是写密集的场景通常是好建议：

范式化优点：

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

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

（3）范式化的表通常更小，可以更好的放在内存里，所以执行操作会更快

（4）很少有多余的数据意味着检索列表数据是更少使用DISTINCT或GROUP BY语句。

非范式化的结构中必须使用DISTINCT或GROUP BY才能获得一份唯一的部门列表。但是如果部门是一张单独的表，直接查询就可以。

范式化点：

通常需要关联，稍微复杂的查询语句在符合范式的schema上都可能至少需要关联一次。代价昂贵，也使得一些索引策略无效。

2、反范式化的优点和缺点

反范式化的schema因为数据都在一张表中，可以很好的避免关联。

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

例如：一网站，允许用户发送消息，并且有一些用户是付费用户，现在察看用户最近10条信息，如果范式化的结构并且索引了发送日期字段published查询看上去：

mysql > SELECT message_text, user_name
    -> FROM message
    -> INNER JOIN user ON message.user_id = user.id
    -> WHERE user.account_type='premiumv'
    -> ORDER BY message.publishd DESC LIMIT 10;

MySQL需要扫描message的published字段索引。

另外一种执行从user表开始。反范式化组织数据，两张表字段合并，增加一个索引(account_type, published)可以不通过关联写出查询

mysql > SELECT MESSAGE_TEXT, USER_NAME
      > FROM user_message
      > WHERE accout_type = 'premium'
      > ORDER BY published DESC
      > LIMIT 10;

3、混用范式化与反范式化

在实际应用中很少极端化处理，我们可能使用部分范式化的schema、缓存表、等等。

从父表冗余数据到子表排序的需要，范式化的schema里面通过作者名字对消息做排序代价很高，但是㝉在message表中缓存author_name字段并且建好索引，就可以高效完成排序。

4、缓存表和汇总表

（1）缓存表、汇总表、影子表

提升性能比较好的方法是在同一张表中保存衍生的冗余数据，有时候也需要创建一张完全独立的汇总表或缓存表（满足检索需要）如果可以允许少量脏数据，是很好的方法。

此处：缓存表汇总表非官方定义，此处明确一下我们的含义：

缓存表：表示存储可以比较简单从schema其他表中获取（但是每次获取速度比较慢）的数据的表（例如，逻辑上荣誉的数据）

汇总表：表示保存是用GROUPBY语句聚合数据的表（例如数据不是逻辑荣誉的）

使用缓存表和汇总表的时候，必须决定是实时维护数据还是定期重建，那个更好依赖于应用程序，但是以定期重建并不只是节约资源，也可以保持表不会有很多碎片以及有完全顺序组织的索引。

当重建汇总表与缓存表的时候，通常需要保证数据在操作的时候仍然可用，引出“影子表”

影子表：在一张真实表背后创建的表，当完成了建表操作后，可用通过一个原子的重命名操作，借还影子表和原表。如果需要重建一张表，可用先创建一张新表，填充数据后，用新表与真实表切换。

mysql > DROP TABLE IF EXISTS my_summary_new my_summary_old;
	> CREATE TABLE my_summary_new LIKE my_summary
	> RENAME TABLE my_summary TO my_summary_old, my_summary_new TO my_summary;

将my_summary这个名字分配给新建的表之前，将原始的my_summary重命名为my_summary_old，就可以在下一次重建之前保留旧版本的数据。

（2）计数器表

应用在表中保存计数器，更新表的时候有并发问题，计数器在Web应用中很常见。可以通过用这种表的缓存保存一个用户的朋友数，文件下载次数，创建一张表独立的保存计数器通常是个好主意，可以使得计数器表小而且块，独立的表也可以避免查询缓存失败。

假设只有一个计数器表，只有一行数据，记录网站点击次数：

mysql > CREATE TABLE hit_counter (
	> cnt int unsigned not null
	> ) ENGINE=InnoDB

每次的网站点击都会导致对计数器进行更新：

mysql > UPDATE hit_counter SET cnt = cnt + 1;

问题：任何想要更新这一行的事物，这条记录都有一个全局互斥的锁(mutex)。使得事务只能串行执行，想要获取高并发性能，可以将计数器保存在多个行中，每次随机选择一行进行更新：

mysql > CREATE TABLE hit_count (
	> slot tinyint unsigned not null primary key,
	> ) ENGINE=InnoDB

预先在这个表中增加100行数据，现在选择一个随机的slot(槽)进行更新

mysql > UPDATE hit_counter SET cnt = cnt + 1 WHERE slot = RAND() * 100;

想要获得统计结果。需要使用以下聚合查询：

mysql > SELECT SUM(cnt) FROM hit_counter;

一个常见的需求是，每间隔一段时间开始一个新的计时器。（例如每天一个）。则需要再修改一下这个表的设计：

mysql > CREATE TABLE daily_hit_counter (
	> day date not null,
	> slot tinyint unsigned not null,
	> cnt int unsigned not null,
	> primary key(day, slot)
	> ) ENGINE=InnoDB;

这个场景里，不用预先生成行，用ON DUPLICATE KEY UPDATE代替：

mysql > INSERT INTO daily_hit_counter(day, slot, cnt)
	> VALUES(CURRENT_DATE, RAND() * 100, 1)
	> ON DUPLICATE KEY UPDATE cnt = cnt + 1;

如果希望减少表的行数，避免表太大，可以写一个周期执行的任务，合并所有的结果到0号槽，并且删除其他槽：

mysql > UPDATE daily_hit_counter as c
	> INNER JOIN (
	> 	SELECT day, SUM(cnt) AS cnt, MIN(slot) AS mslot
	> 	FROM daily_hit_counter
	>   GROUP BY day
	> ) AS x USING(day)
	> SET c.cnt = IF(c.slot = x.mslot, x.cnt, 0),
	>     c.slot = IF(c.slot = x.mslot, 0, c.slot);
mysql > DELET FROM daily_hit_counter WHERE slot <> 0 AND cnt = 0;

五、加快ALTER TABLE操作的速度

两种技巧：

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

2、影子拷贝：表结构创建一张和源表无关的新表，通过重命名和删表操作交换两张表。

不是所有的的ALTER TABLE都会引起表重建：

例如两种方法改变或删除一个列的默认值：

慢：

mysql > ALTER TABLE sakila.film
	> MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5;

这个语句拷贝了整个表到一张新表，除了这个属性其他列的属性都没改，代价很高。

快：使用ALTER COLUMN 改变列默认值，直接修改.frm文件不涉及表数据所以操作快

mysql > ALTER TABLE sakila.film
 	> ALTER COLUMN rental_duration SET DEFAULT 5;

语句直接修改.frm文件不涉及表数据所以操作快。

1、只修改.frm文件

下面操作是有可能不用重建表：

移除一个列的AUTO_INCREAMENT属性
增加、移除、或更改ENUM和SET常量。

2、快速创建MyISAM索引

高效载入数据到MyISAM表中。先禁用索引，载入数据，然后重启所以。

六、总结

良好的schema设计原则是普遍使用的，但是MySQL有自己实现细节。简单原则：

1、尽量避免过度设计，例如会导致查询复杂的schema设计，或者有很多列的表设计
2、使用小而合适的数据类型，尽可能避免null值
3、尽量使用相同数据类型存储相似的值
4、注意可变长字符串，在临时表和排序的时候按照最大长度分配内存
5、尽量使用整数定义标识列
6、避免使用MySQL遗弃的特性
7、小心使用ENUM与SET

范式是好的，但是反范式优势也必要且有好处。

最后ALTER TABLE令人痛苦，大部分情况会锁表并且重建表。大部分场景常规方法是，备份机器执行ALTER并在完成后切换为主库。