通过数据库建表实战来理解数据库知识

为什么有建表的策略

在后端工作中，需要不断的建库和建表，来满足业务。
后五个可以先不用管（用不到）

1.名字
通俗易懂 ：否则别人半天都不知道表是干啥的。
尽量使用小写：视觉上小写更符合人的认知。
分隔符_：表示详细的内容。如果一个表使用了另一个表的主键，可以在另一张表的名后面，加_id或_sys_no
前缀：比如加索引
普通索引和联合索引，其实是一类。在建立该类索引时，可以加ix_前缀，比如：ix_product_status。
唯一索引，可以加ux_前缀，比如：ux_product_code。

2.字段类型
如果每个人用字段类型差距都很大，那么怎么用字段类型呢。

所以选择一个合适的字段类型，还是非常重要的一件事情。
以下原则可以参考一下：
尽可能选择占用存储空间小的字段类型，在满足正常业务需求的情况下，从小到大，往上选。
如果字符串长度固定，或者差别不大，可以选择char类型。如果字符串长度差别较大，可以选择varchar类型。
是否字段，可以选择bit类型。
枚举字段，可以选择tinyint类型。
主键字段，可以选择bigint类型。
金额字段，可以选择decimal类型。
时间字段，可以选择timestamp或datetime类型。

时间格式的数据有：date、datetime和timestamp等等可以选择。
字符类型的数据有：varchar、char、text等可以选择。
数字类型的数据有：int、bigint、smallint、tinyint等可以选择。

3.字段长度 以及个数
前面我们已经定义好了字段名称，选择了合适的字段类型，接下来，需要重点关注的是字段长度了。
比如：varchar(20)，biginit(20)等。
那么问题来了，varchar代表的是字节长度，还是字符长度呢？
答：在mysql中除了varchar和char是代表字符长度之外，其余的类型都是代表字节长度。
字段长度最好 20个以内

4.主键
在创建表的时候 要使用主键，因为主键效率高。

主键的最好习惯：
此外，主键还是天然的唯一索引，可以根据它来判重。
在单个数据库中，主键可以通过AUTO_INCREMENT，设置成自动增长的。
除MySQL强制实施的规则外，应该坚持的几个普遍认为的最好习惯为：

1、不更新主键列的值

2、不重用主键列的值

3、不在主键列中使用可能会更改的值(例如，如果使用一个名字作为主键以标识某个供应商，应该供应商合并和更改其名字时，必须更改这个主键)

5.存储引擎
在mysql5.1以前的版本，默认的存储引擎是myslam，而mysql5.1以后的版本，默认的存储引擎变成了innodb。
之前我们还在创建表时，还一直纠结要选哪种存储引擎？

存储引擎是数据库的底层软件组织，数据库管理系统使用存储引擎创建，更新，删除，和查询数据。不同的存储引擎提供了不同的存储机制，索引技巧。
myslam 特点：不支持数据库事务，行级别锁和外键，因此在插入数据或更新数据的时候，需要锁住表，效率比较低。
总之，就是查询速度快，更新数据慢且不支持事务。
==重点 == InnoDB 为Mysql 提供了事务支持，回滚，崩溃修复能力，多版本并发（mvcc），事务安全的操作。
底层的存储结构为b+树，b+树的每个节点都对应，innodb的一个page，page大小是固定，一般为16kb。
总之，适用于有如下需求的场景中：
1.经常有数据需要更新的表，适合多重处理并发。
2.支持事物。
3.支持灾难恢复。
4.支持外键约束。
5.支持自增属性。

myslam的索引和数据分开存储，而有利于查询，但它不支持事务和外键等功能。
而innodb虽说查询性能，稍微弱一点，但它支持事务和外键等，功能更强大一些。
以前的建议是：读多写少的表，用myslam存储引擎。而写多读多的表，用innodb。
但虽说mysql对innodb存储引擎性能的不断优化，现在myslam和innodb查询性能相差已经越来越小。
所以，建议我们在使用mysql8以后的版本时，直接使用默认的innodb存储引擎即可，无需额外修改存储引擎。

6.字段 NOT NULL
在创建字段时，需要选择该字段是否允许为NULL。
我们在定义字段时，应该尽可能明确该字段NOT NULL。
为什么呢？
我们主要以innodb存储引擎为例，myslam存储引擎没啥好说的。
主要有以下原因：
在innodb中，需要额外的空间存储null值，需要占用更多的空间。
null值可能会导致索引失效。
null值只能用is null或者is not null判断，用=号判断永远返回false。
因此，建议我们在定义字段时，能定义成NOT NULL，就定义成NOT NULL。
但如果某个字段直接定义成NOT NULL，万一有些地方忘了给该字段写值，就会insert不了数据。

7.外键
在mysql中，是存在外键的。
外键存在的主要作用是：保证数据的一致性和完整性。
如果只有两张表的关联还好，但如果有十几张表都建了外键关联，每删除一次主表，都需要同步删除十几张子表，很显然性能会非常差。
因此，互联网系统中，一般建议不使用外键。因为这类系统更多的是为了性能考虑，宁可牺牲一点数据一致性和完整性。
除了外键之外，存储过程和触发器也不太建议使用，他们都会影响性能。

8.索引
在建表时，除了指定主键索引之外，还需要创建一些普通索引。
后面查表的时候，效率更高。
但索引字段也不能建的太多，可能会影响保存数据的效率，因为索引需要额外的存储空间。
建议单表的索引个数不要超过：5个。
如果在建表时，发现索引个数超过5个了，可以删除部分普通索引，改成联合索引。
顺便说一句：在创建联合索引的时候，需要使用注意最左匹配原则，不然，建的联合索引效率可能不高。