|
设计规范 |
原因 |
|
所有表必须使用Innodb存储引擎 |
没有特殊要求（即Innodb无法满足的功能如：列存储，存储空间数据等）的情况下，所有表必须使用Innodb存储引擎 （mysql5.5之前默认使用Myisam，5.6以后默认的为Innodb） Innodb 支持事务，支持行级锁，更好的恢复性，高并发下性能更好 |
|
数据库和表的字符集统一使用UTF8 |
兼容性更好，统一字符集可以避免由于字符集转换产生的乱码，不同的字符集进行比较前需要进行转换会造成索引失效 |
|
禁止在表中建立预留字段 |
预留字段的命名很难做到见名识义 预留字段无法确认存储的数据类型，所以无法选择合适的类型 对预留字段类型的修改，会对表进行锁定 |
|
禁止在数据库中存储图片，文件等大的二进制数据 |
通常文件很大，会短时间内造成数据量快速增长，数据库进行数据库读取时，通常会进行大量的随机IO操作，文件很大时，IO操作很耗时 通常存储于文件服务器，数据库只存储文件地址信息 |
|
禁止在数据库中存储明文密码 |
采用加密字符串存储密码，并保证密码不可解密 |
|
所有表和字段都需要添加注释 |
使用comment从句添加表和列的备注，可以增加数据表和字段的可读性，减少维护难度 |
|
表必须有主键，推荐使用UNSIGNED自增列作为主键 |
表没有主键，INNODB会默认设置隐藏的主键列；没有主键的表在定位数据行的时候非常困难，也会降低基于行复制的效率 |
|
为表的所有字段都添加NOT NULL属性 |
使用NULL值会存在每一行都会占用额外存储空间、数据迁移容易出错、聚合函数计算结果偏差等问题 |
|
核心表必须有行数据的
（如用户表，金钱相关的表） |
便于维护和检查问题 |
|
尽量控制单表数据量的大小 |
单表数据量过大会造成修改表结构，备份，恢复都会有很大的问题 可以用历史数据归档（应用于日志数据），分库分表（应用于业务数据）等手段来控制数据量大小 |
|
使用预编译语句 |
只传参数，比传递SQL语句更高效 一次解析，多次使用 降低SQL注入概率 |
|
避免隐式转换 |
会导致索引失效 |
|
合理的使用分页 |
传入的参数限制分页展示的页数 只能点击上一页、下一页 采用延迟关联 |
|
拒绝大SQL语句，拆分成小SQL语句 |
充分利用QUERY CACHE 充分利用多核CPU |
|
程序应有捕获SQL异常的处理机制 |
避免出错导致的数据不同步 必要时通过rollback显式回滚。打印标准错误，设置超时时间，程序重新连接 |
|
谨慎使用MySQL分区表 |
分区表在物理上表现为多个文件，在逻辑上表现为一个表 谨慎选择分区键，跨分区查询效率可能更低 建议采用物理分表的方式管理大数据 |
|
尽量做到冷热数据分离，减小表的宽度 (将大字段、访问频率低的字段拆分到单独的表中存储) |
MySQL限制每个表最多存储4096列，并且每一行数据的大小不能超过65535字节 减少磁盘IO,保证热数据的内存缓存命中率（表越宽，把表装载进内存缓冲池时所占用的内存也就越大,也会消耗更多的IO） 更有效的利用缓存，避免读入无用的冷数据 经常一起使用的列放到一个表中（避免更多的关联操作） |
|
合理使用覆盖索引减少IO，避免排序 |
覆盖索引能从索引中获取需要的所有字段，从⽽而避免回表进行二次查找，节省IO。 |