掌握这个几个建表的 tips，没人敢说你是数据库小白

版权声明：本文为博主原创文章，未经博主允许不得转载。

        最近在做金融数据库相关的数据库SQL审核工作，期间发现非常多不符合基本规范的SQL。和相关的开发沟通后，发现很多的开发，甚至工作了很多年的高工对于数据库建表的基本规范可能都不太清楚，及时了解基本的规范，也不知道为什么要这么干。有的同事还以为我是在故意为难他，当我把范例的SQL交给他，并做基本解释后，便让他豁然开朗。甚至对我表示非常感谢。感谢的理由是之前没有人对他说过必须这么做以及为什么要这么做。本文不从范式角度，也不从数据库全局库表设计的角度来讨论。而是基于对数据库中创建表最基本的规范，让每个开发的同事和朋友都能快速的掌握创建表的基本规范。

下面是作为一个DBA总结的针对创建表的几个基本规范，希望各位开发的同事能了解和使用这些规范来建立表结构。

1、创建表的时候必须指定主键，并且主键建立后最好不要再有数据修改的需求

mysql从5.5版本开始默认使用innodb引擎，innodb表是聚簇索引表，也就是说数据通过主键聚集（ 主键下存储该行的数据，索引指向主键值），如下图所示：

正是由于这种解构，如果后续对主键对应的值进行修改，就会导致索引节点的频繁分裂，性能会下降非常厉害。因此推荐开发的同事们使用和业务没有任何关联的自增id来做主键（切记不要使用uuid来做主键），此外也可以考虑使用其他的方式来生产自增的ID，比如使用Twitter的snowflake算法或者zk的DistributedAtomicLong来间接实现。使用自增主键而不是uuid做主键的优点主要有如下几点：

a、占用的数据量更小

b、数据顺序递增，不会导致索引节点的频繁分裂

c、数字类型比字符类型效率更高

正确招式：使用自增id作为主键

2、创建表选择字段的时候，在符合业务需求的情况下尽量小，数据类型尽量简单

数据类型尽量简单很好理解，比如，使用数字类型要比使用字符类型效率更高，因为不涉及到校对规则和字符集。而字段尽量小，说的是在满足业务需求的情况下，尽可能小。这么做的好处是：更小的字段类型占用更少的内存，占用更少的磁盘空间，占用更少的磁盘IO，以及占用更少的带宽。举个例子，如果一个varchar(50)的字段，不管你存储了几个字符，在查询的时候仍然需要申请50 byte的内存

3、创建表的时候显式申明存储引擎

这个没什么说的，使用什么引擎就申明什么引擎，防止被默认。有句话说得好：如果你不选择，那你就被选择，被选择的结果不一定是你自己想要的。命运还是要掌握的自己手里。

正确招式：显式申明ENGINE=xxxx；

4、创建表的时候显式申明字符集

如果不不想被乱码困扰，就老老实实的申明字符集，还是那句话：如果你不选择，那你就被选择，被选择的结果不一定是你自己想要的。命运还是要掌握的自己手里。

正确的招式：显式申明DEFAULT CHARSET=xxxx；

5、创建表的时候对经常要查询的列添加索引或者组合索引

索引直接影响后面的查询性能，尤其是数据量越大的时候，影响越明显。作为一个从事DBA生涯超过5年的DBA，遇到过无数次由于没有添加索引，导致的线上故障，请各位开发和DBA的同事谨记，一定要对经常要查询的列添加索引或者组合索引，防止线上事故的发生。

正确招式：建表的时候就添加对应INDEX

6、创建表的时候对字段和表添加COMMENT

这个主要是方便后续的维护，之前在小公司做DBA的时候，接手数据库时由于没有任何的COMMENT，导致完全不知道对应的表和字段是做什么用的，不得不经常麻烦开发的同事解释。这个小技巧用一个经典的广告词来解释就是：你好，我也好。

正确招式：对列和表都添加COMMENT做详细说明；

7、创建表的时候不要添加drop操作

有的开发同事在创建表之前喜欢添加, DROP TABLE IF EXISTS TABLEXXXX; 然后再来个CREATE  TABLE，建议不要这么干，因为我之前遇到过由此带来的线上故障。将线上正在使用的表drop掉了。建议大家创建表的时候修改为：CREATE TABLE IF NOT EXISTS TABLEXXX .......；

正确招式：CREATE TABLE IF NOT EXISTS TABLEXXX .......；

8、创建表的时候，字段尽量不要为NULL

解决办法就是设置字段为NOT NULL，并设置字段的默认值。字段尽量不要为NULL的原因如下：

a、NULL需要占用额外的空间存储；

b、进行比较的时候会更复杂，还会导致你select (column)的时候不准确

c、含有NULL值的列，会对SQL优化产生影响，尤其是组合索引中

具体NULL会带来的问题大家可以查阅：https://dev.mysql.com/doc/refman/5.7/en/problems-with-null.html

正确招式：NOT NULL DEFAULT 'xxxxx'；

附上1个机器简单的标准SQL 范例，大家创建表的时候可以参照下面的范例SQL来创建，范例如下：

CREATE DATABASE IF NOT EXISTS `dev_ops_db`;

CREATE TABLE  IF NOT EXISTS `dev_ops_db`.`monitor_table_holiday` (

`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',

`holiday_date` date NOT NULL default '1999-01-01' COMMENT '节假日日期',

`holiday_name` varchar(36) NOT NULL default '' COMMENT '节假日名称',

PRIMARY KEY (`id`),

KEY `holiday_date` (`holiday_date`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='节假日数据表';