MySQL开发规范

MySQL开发规范

基于阿里数据库设计规范扩展而来 ： https://yq.aliyun.com/articles/709387

参考，58到家MySQL军规升级版 ： https://www.jianshu.com/p/c077581693fb

基础规范

1. 表存储引擎必须使用InnoDB

   MySQL常见的三种存储引擎（storage_engine） ： InnoDB、MyISAM、MEMORY

   • 存储引擎就是指表的类型以及表在计算机上的存储方式

   • 通过“SHOW ENGINES”语句来查看 MySQL中的存储引擎

     特性	innoDB	MyISAM	Memory
     事务安全	支持	无	无
     存储限制	64T	有	有
     空间使用	高	低	低
     内存使用	高	低	高
     插入数据的速度	低	高	高
     读取数据的速度	低	高	高
     对外键的支持	支持	无	无
     全文索引	不支持	支持

   • InnoDB存储引擎

     • InnoDB给MySQL的表提供了事务处理、回滚、崩溃修复能力和多版本并发控制的事务安全
     • InnoDB存储引擎总支持AUTO_INCREMENT。自动增长列的值不能为空，并且值必须唯一。MySQL中规定自增列必须为主键
     • InnoDB还支持外键（FOREIGN KEY）。外键所在的表叫做子表，外键所依赖（REFERENCES）的表叫做父表，父表中被子表外键关联的字段必须为主键。
     • InnoDB中，创建的表的表结构存储在*.frm*文件中（我觉得是frame的缩写吧）。数据和索引存储在innodb_data_home_dir和innodb_data_file_path定义的表空间中
     • InnoDB的优势在于提供了良好的事务处理、崩溃修复能力和并发控制。缺点是读写效率较差，占用的数据空间相对较大。

   • MyISAM存储引擎

     • MyISAM的表存储成3个文件。文件的名字与表名相同。拓展名为frm、MYD、MYI。其实，frm文件存储表的结构；MYD文件存储数据，是MYData的缩写；MYI文件存储索引，是MYIndex的缩写
     • 表支持3种不同的存储格式。包括静态型、动态型和压缩型。其中，静态型是MyISAM的默认存储格式，它的字段是固定长度的；动态型包含变长字段，记录的长度不是固定的；压缩型需要用到myisampack工具，占用的磁盘空间较小。
     • MyISAM的优势在于占用空间小，处理速度快。缺点是不支持事务的完整性和并发性。

   • MEMORY存储引擎

     • 表实际对应一个磁盘文件。该文件的文件名与表名相同，类型为frm类型。该文件中只存储表的结构。而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。
     • MEMORY默认使用哈希索引。速度比使用B型树索引快。当然如果你想用B型树索引，可以在创建索引时指定。
     • MEMORY用到的很少，因为它是把数据存到内存中，如果内存出现异常就会影响数据。如果重启或者关机，所有数据都会消失。因此，基于MEMORY的表的生命周期很短，一般是一次性的。

   • 怎样选择存储引擎

     • 同一个数据库也可以使用多种存储引擎的表。如果一个表要求比较高的事务处理，可以选择InnoDB。这个数据库中可以将查询要求比较高的表选择MyISAM存储。如果该数据库需要一个用于查询的临时表，可以选择MEMORY存储引擎。

2. 表字符集默认使用utf8，必要时候使用utf8mb4

   character set 和 collation 的理解

   • character set (字符集) ：我们常看到的UTF-8、GB2312、GB18030都是相互独立的character set
   • collation (对比方法) ： 用于指定数据集如何排序，以及字符串的比对规则
   • 每个 character set 会对应一定数量的collation ，可以通过SQL语句 show collation; 查看，collation名字的规则可以归纳为这两类：
     • <character set>_<language/other>_<ci/cs>
       • language/other : 同一个character set的不同 language/other 的区别在于排序、字符串对比的准确度（相同两个字符在不同国家的语言中的排序规则可能是不同的）以及性能
       • ci 是case insensitive的缩写，cs是case sensitive的缩写。即指定大小写是否敏感
     • <character set>_bin :
       • utf8_bin 是将字符串中的每一个字符用二进制数据存储，区分大小写

   mysql中utf8和utf8mb4区别 ：

   • utf8 编码最大字符长度为 3 字节 ， mb4就是most bytes 4的意思 ，
   • 应该总是使用 ut