Mysql使用 之 开发篇

Mysql中关于外键的选择？

外键的介绍：

Mysql支持外键索引的存储引擎只有Innodb，在创建外键的时候，要求父表必须有对应的索引，子表在创建时也会自动创建对应的索引。在创建索引时可以指定在删除更新父表时对子表进行相关的操作，包括RESTRICT、 CASCADE、SET NULL、NO ACTION。 其中RESTRICT和NO ACTION相同，限制在子表有关联业务的情况下，父表不能更新，CASCADE表示父表在更新或者删除时，更新或者删除子表对应的记录，SET NULL表示父表在更新或者删除的时候，子表将相关的数据SET NULL，选择最后两种方法的时候需要谨慎，防止因为错误的操作导致数据丢失。

为什么不推荐使用外键约束？

在阿里的开发规范当中也存在这么一条约束：

【强制】不得使用外键与级联，一切外键概念必须在应用层解决。

但是为什么要这样做？一般在询问原因的时候，可能会回答：

每次在做delete或者update的时候都需要考虑外键约束，会导致在开发的时候很痛苦，测试数据极为不 方便。

首先我们明确一点，外键约束是一种约束，这个约束的存在，会保证表间数据的关系“始终完整”。因此， 外键约束的存在，并非全然没有优点。 比如使用外键，可以 保证数据的完整性和一致性，级联操作方便 ，将数据完整性判断托付给了数据库完成，减少了程序的代码量 。

然而，鱼和熊掌不可兼得。外键是能够保证数据的完整性，但是会给系统带来很多缺陷。正是因为这些缺陷， 才导致我们不推荐使用外键。具体如下：

①性能问题： 假设一张表名为user_tb。那么这张表里有两个外键字段，指向两张表。那么，每次往user_tb表里插入数 据，就必须往两个外键对应的表里查询是否有对应数据。如果交由程序控制，这种查询过程就可以控制在我们手 里，可以省略一些不必要的查询过程。但是如果由数据库控制，则是必须要去这两张表里判断。

②并发问题： 在使用外键的情况下，每次修改数据都需要去另外一个表检查数据,需要获取额外的锁。若是在高并发大流量事务 场景，使用外键更容易造成死锁。

③扩展性问题： 这里主要是分为两点： 做平台迁移方便，比如你从Mysql迁移到Oracle，像触发器、外键这种东西，都可以利用框架本身的特性来 实现，而不用依赖于数据库本身的特性，做迁移更加方便。

④分库分表方便，在水平拆分和分库的情况下，外键是无法生效的。将数据间关系的维护，放入应用程序中，为将来 的分库分表省去很多的麻烦。

⑤技术问题： 使用外键，其实将应用程序应该执行的判断逻辑转移到了数据库上。那么这意味着一点，数据库的性能开销 变大了，那么这就对DBA的要求就更高了。很多中小型公司由于资金问题，并没有聘用专业的DBA，因此他们会选 择不用外键，降低数据库的消耗。相反的，如果该约束逻辑在应用程序中，发现应用服务器性能不够，可以加机 器，做水平扩展。如果是在数据库服务器上，数据库服务器会成为性能瓶颈，做水平扩展比较困难。

tips：

在建立外键的情况下，如果导入多个表的数据，可以设置为暂时关闭外键的检查，set FOREIGN_KEY_CHECKS = 0;

执行完成之后再执行 set FOREIGN_KEY_CKHECKS = 1。可以通过show table status 来查看数据库中外键的信息。

 

数据类型的选择：

char 与 varchar：

char是固定长度的字符串，varchar是可变长度的字符串。由于char是固定长度的，所以他的处理速度会快得多，但是会浪费一定的存储空间。适用于那些长度不变，对查询速度有较高要求的字段。但是在innodb存储引擎中建议使用varchar数据类型，内部的行存储没有区分固定长度和可变长度（所有的数据行都是指向数据列值的头指针）因此在本质上使用固定长度的char并不一定比使用可变长度的varchar类型性能要高。因此主要的性能因素是数据行使用的总存储量，由于char使用的空间大于varchar。因此使用varchar是比较好的。

Text 与 BloB

在保存大量的文本字段的时候使用Text 或者 blob。二者的区别是：text保存字符数据，例如文章或者评论信息   blob用来保存二进制数据 比如照片。

大量的使用text blob可能会导致性能问题。特别是执行了大量的删除操作的时候，会在数据表中留下大量的空洞，后面的数据在插入的时候就会有性能的损失。可以使用 OPTIMIZE TABLE 对表进行数据整理。

对于大文本字段的使用优化：

• 一般情况下不建议去直接查询大文本的字段
• 可以使用前缀索引来实现对大文本的模糊匹配。
• 可以考虑将大文本的字段单独的保存到另一张表中
• 可以考虑将大文本的字段经过MD5函数生成散列值（数据型的散列值有很高的在执行效率）将散列值保存到数据库中，但是这种方式只适用于精确匹配。

 

浮点数和定点数的问题：

浮点数指包含小数部分的数值，float double real 当一个数插入到浮点类型的字段时，如果超过了当前的精度，插入的值会在四舍五入之后再插入。

定点数与浮点数不同，decimal numberic 定点数实际上是以字符串的形式来保存的，所以定点数可以由更高的精度。

（浮点数可能存在精度上的误差。对于高精度的数据应该使用定点数来进行保存）

 

时间类型的选择：

mysql中支持的时间类型有  date  time  dateTime  timeStamp

在实际的使用中 使用最小的合适的数据类型，例如 如果只需要记录年份  可以使用year

如果使用的需要记录年月日时分秒  而且记录的年代比较久远 使用dateTime。timestamp可使用的时间范围比dateTime要小。

如果记录的日期需要在不同的时区来进行展示 使用 timestamp，可以与时区相对应。