Mysql库表结构优化

一、数据类型

1.够用就好

遵循够用就好的原则，选择能够容纳数据的最小数据类型。比如设计一张表一般都会有is_delete字段，该字段一般就0和1两个取值，用TINYINT绰绰有余。

2.简单最好

遵循简单原则，选择能够容纳数据的最简单的数据类型。比如id用BIGINT而不用VARCHAR，使用Mysql自带的DATETIME来存储时间，而不是VARCHAR。

3.尽量不为NULL

对于NULL的列，在Mysql中会特殊处理。在建立索引时，更应该避免使用NULL，可以选择给出一个默认值。

4.尽量用精确数值

浮点数在计算时会丢失精度，而DECIMAL计算时代价更高昂。在存储金额字段时可以考虑将数据转成最小货币单位的形式，使用BIGINT来存储数据，比如保存以厘为单位的金额数据。

5.尽量用CHAR

如果一个字段是CHAR类型，当我们明确知道它的取值长度，且波动不大的情况下，尽量使用CHAR固定长度。如果该字段取值范围比较大，那用VARCHAR更合适，应该它更节约空间。

二、反范式设计

1.范式化设计

1NF 要求字段的原子性，2NF要求非主属性对码必须是完全依赖，3NF消除非主属性的传递依赖，BCNF排除了任何属性对码的传递依赖与部分依赖。范式化的设计可以使得每张表逻辑清晰，重复数据更少，操作更快。但是范式化设计的表在查询时可能会需要关联表，这是一个很大的缺点。

2.反范式化与范式化结合

在设计表时，先满足范式化设计，然后根据实际的应用场景去添加冗余字段。从依赖表中冗余一些字段以方便查询是比较常见的操作。但是引来新的问题，更新数据时意味着要更新不止一份数据。

三、表设计

1.汇总表

有时候需要一张完整的汇总表来提高查询需求，以避免大量的GROUP BY 或COUNT操作。比如统计场景下，经常会有查询一段时间内某种订单的数量，总价等。为了避免这类查询拖慢整个订单表的响应，可以建立一张汇总表，汇总类的查询直接走汇总表，而不会影响原表。
汇总表数据的维护可以选择定期维护，比如一个小时重新汇总一次，或者在写订单时进行实时维护。一般来讲，如果对数据的实时性要求不高，选择定期重建是比较好的方式。
重建汇总表时，需要保证原来的数据可用，这就需要通过影子表来实现。影子表指的是创建一张与原表结构相同的表，将重建的数据写入影子表，最后将两张表的名字交换。

DROP TABLE IF EXISTS t_summary_new, t_summary_old;
--创建新表t_summary_new
CREATE TABLE t_summary_new LIKE t_summary;
--重建的数据写入t_summary_new
--重建完成后将旧表与新表重命名
RENAME TABLE t_summary TO t_summary_old, t_summary_new TO t_summary;

2.计数器表

有时候我们会遇到计数的需求，比如统计某个网站的访问量，点击量，文件的下载次数。在并发量比较小的时候，只需要在原表的基础上加个字段，然后每次执行加一操作就可以了，但是在并发量特别高的时候，由于更新的都是同一行数据，全局串行，行锁冲突严重，响应速度变慢。这时候就需要将计数字段单独拆成一张表，并且将一个计数器拆分成n个slot，每次在执行加一操作时，随机选择一个slot进行加一，想要知道计数总和，将所有的slot加起来就可以了。

--一张表里面可以存在多个计数器，一个计数器存在多个槽位，每次计数是更新某个计数器的某个槽位，
--而需要某个计数器的值时，需要对该计数器的所有槽位求和
--创建计数器表
CREATE TABLE t_counter(
	id BIGINT UNSIGNED PRIMARY AUTO_INCREMENT COMMENT '主键id', 
	counter_id BIGINT UNSIGNED NOT NULL COMMENT '计数器id',  
	slot TINYINT UNSIGNED NOT NULL COMMENT '当前槽位',
	count BIGINT UNSIGNED NOT NULL COMMENT '当前槽位的计数器'
)
--在计数器表创建成功之后需要初始化（插入）所有的计数器和对应的槽位，之后才能真正的执行计数操作。
--更新某个数据的计数时像下面这样做
UPDATE t_counter SET count = count + 1 WHERE slot = RAND() * 100 AND count_id = #{counterId}
--获取一个计数器的计数值
SELECT SUM(count) FROM t_counter WHERE counter_id = #{counterId}

四、总结

本篇文章总结了一些常见的设计优化。增加冗余字段，创建汇总表，这些都是为了提高读查询的速度，同时也增加了写查询的负担，并且增加了额外的开发工作量。在互联网应用中，大部分请求都是读多写少，为了让读更快，增加写操作的难度是能够容忍的。