MySQL库表结构优化

　　全文大约【2083】字，不说废话，只讲可以让你学到技术、明白原理的纯干货!本文带有丰富案例及配图视频，让你更好的理解和运用文中的技术概念，并可以给你带来具有足够启迪的思考......

　　一. 数据库结构优化

　　数据库的库表结构优化包含很多内容，例如：字段类型优化、垂直分表、水平分表、表分区、分库等内容。下面小编就带各位小伙伴一起分析下具体内容。

　　1.字段类型优化

　　字段类型优化的目的是将字段类型设置的更合理一些，小编总结的规则如下：

　　● 尽量将表字段定义为NOT NULL约束，这时由于在MySQL中含有空值的列很难进行查询优化，NULL值会使索引以及索引的统计信息变得很复杂。

　　● 对于只包含特定类型的字段，可以使用enum、set 等数据类型。

　　● 数值型字段的比较比字符串的比较效率高得多，字段类型尽量使用最小、最简单的数据类型。例如IP地址可以使用int类型。

　　● 尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED。但对整数类型指定宽度，比如INT(11)，没有任何用，因为指定的类型标识范围已经确定。

　　● VARCHAR的长度只分配真正需要的空间。

　　● 尽量使用TIMESTAMP而非DATETIME，但TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的范围小得多，而且TIMESTAMP的值因时区不同而不同。

　　● 单表不要有太多字段，建议在20以内。

　　● 合理的加入冗余字段可以提高查询速度。

　　2.垂直拆分表(垂直切割)

　　垂直拆分按照字段进行拆分，其实就是把组成一行的多个列分开放到不同的表中，这些表具有不同的结构，拆分后的表具有更少的列。例如用户表中的一些字段可能经常访问，可以把这些字段放进一张表里。另外一些不经常使用的信息就可以放进另外一张表里。

　　插入的时候使用事务，也可以保证两表的数据一致。缺点也很明显，由于拆分出来的两张表存在一对一的关系，需要使用冗余字段，而且需要join操作。但是我们可以在使用的时候可以分别取两次，这样的来说既可以避免join操作，又可以提高效率。

　　2.1 拆分时机:

　　单表字段过多, 并且不需要一次性查询返回展示到一个页面上。

　　2.2 拆分方法:

　　一个页面需要展示的数据字段, 尽量放在一张表中. 如果拆分成多个表, 那么这多个表的关系是1:1:1:1......的关系。

　　2.3 优点 :

　　拆分后查询单表性能得到提升, 速度快。

　　2.4 缺点:

　　如果拆分后, 需要查询跨多张表的数据就会用到join连接查询, 查询速度会比没拆分前慢。

　　3.水平拆分表(水平切割)

　　水平拆分按照行进行拆分，常见的就是分库分表。以用户表为例，可以取用户ID，然后对ID取10的余数，将用户均匀的分配进这 0-9这10个表中。查找的时候也按照这种规则，又快又方便。

　　有些表业务关联比较强，那么可以使用按时间划分的。例如每天的数据量很大，需要每天新建一张表。这种业务类型就是需要高速插入，但是对于查询的效率不太关心。表越大，插入数据所需要索引维护的时间也就越长。

　　3.1 拆分时机:

　　MySQL单表大于500万条数据, 就需要考虑水平分表, 因为单表数据量越大查询越慢, 查询效率成指数级下降。

　　3.2 拆分方法:

　　根据业务拆分:

　　根据时间范围拆分, 例如: 订单数据, 按照订单创建时间, 一个月一张表。

　　按照区间分为拆分, 例如: 按照表自增id, 1-300万条一张表。

　　按照业务分类拆分, 例如: 用户表按照男女拆分。

　　hash分表:

　　通过一个原始目标的ID或者名称通过一定的hash算法计算出数据存储表的表名，然后访问相应的表。普通hash算法和一致性hash算法均可。

　　一般企业利用工具来做水平分表：

　　服务端分库分表: MyCat, corbar等。

　　客户端分库分表: shardingJDBC, ShardingSphere等。

　　3.3 优点:

　　提高了查询效率, 主要是在大量数据情况, 提高了查询的命中率。

　　3.4 缺点:

　　提高了复杂度. 在数据量不大的情况, 水平分表体现不出好处。

　　4.表分区

　　4.1 目的:

　　表分区小编给大家简明扼要的说就是大量数据操作, 会有磁盘IO跟不上的情况, 所以表分区, 就是将保存在硬盘上的数据文件放到一块或者多快硬盘上, 以提高数据库的IO性能。

　　分区适用于例如日志记录，查询少。一般用于后台的数据报表分析。对于这些数据汇总需求，需要很多日志表去做数据聚合，我们能够容忍1s到2s的延迟，只要数据准确能够满足需求就可以。

　　MySQL主要支持4种模式的分区：Range分区、List预定义列表分区，Hash 分区，Key键值分区。

　　4.2 录入使用key键值分区

　　小编举个栗子：

CREATE TABLE `test2` (
  `id` int(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(100) DEFAULT NULL COMMENT '名称',
  `state` int(1) DEFAULT NULL COMMENT '状态',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY KEY (id)
PARTITIONS 10;