mysql5.6 sql设计规范_「MySQL」 - SQL设计规范

苦于知乎没有后台文章管理页面，只能把相关文章手动做成索引。

一、数据库命名规范

A、对象名称使用小写字母、下划线分割Windows默认情况下无法建立大写库名

Linux大小写敏感，MySQL数据文件(库名、表名、表别名严格区分大小写)Linux查询大小写敏感

Windows查询大小写不敏感

MySQL 5.6，lower_case_table_names，0：表示区分大小写、1：表示不区分大小写

MySQL 5.7，lower_case_table_names，2：表示区分大小写、1：表示不区分大小写

统一命名使用全小写字母和下划线分割

列名与列表名在所有情况下忽略大小写

B、对象名禁止使用MySQL关键字/保留字使用关键字，建表时不会报错，CURD时会提示SQL语句错误对于SQL语句中的关键字，写SQL时需要单引号包围，该要求难以统一，还是不使用关键字作为表字段

C、对象命名做到见名知意，限定在32个字符范围内字符过长增加网络传输开销

D、临时表，tmp前缀、日期作为后缀，方便筛选、倒库、清理

E、备份表，bak前缀、日期作为后缀，方便筛选、倒库、清理大表，导出为SQL文件进行备份

F、存储相同数据的列名和列类型必须一致关联列，类型不一致会导致隐式类型转换，引起索引失效，降低查询效率

二、基本设计规范

A、统一使用InnoDB存储引擎(5.6之后为默认)TODO MySQL不同存储引擎比较

B、统一使用utf8字符集只存储中文字符，可以使用GBK、GB2312

对于emoj需要使用utf8mb4TODO utf8和utf8mb4之前还有争论，关注讨论结论

编码转换容易导致乱码，以及索引失效

C、所有表和字段都需添加注释建库时维护好数据字典

D、控制数据量大小，控制在500w行InnoDB未做最大行限制，受限于存储设备和文件系统

分库分表TODO 分库分表策略

E、谨慎使用MySQL分区表分区表在物理上表现为多个文件，逻辑上表现为一个文件需要把多个物理文件分布于磁盘阵列，才能提高IO利用率

减少跨分区查询

更倾向于使用物理分表方式管理海量数据

F、减小表宽度、尽量做到冷热数据分离MySQL限制单表最多存储4096列

每行数据大小不超过65535 BYTE = 64K

控制宽度，对列进行垂直拆分

减少IO，保留热数据的内存缓存命中率

使用字段进行查询，避免读入无用的冷数据

G、禁止在表中建立预留字段预留字段难以做到见名知意

预留字段难以确认存储类型修改类型，会引起全表锁定

修改表字段代价 >> 增加表字段

H、禁止在数据库存储图片、文件等bin数据

I、禁止在线上库做压力测试

J、禁止从开发环境、测试环境直连生产环境数据库

三、索引设计规范

A、不滥用、乱用索引建议单表索引不超过5个

提高查询效率，降低插入和更新效率

MySQL优化器，会评估索引，生成最优执行计划，索引过多，导致生成执行计划时间过长，降低效率

B、InnoDB表，必须有一个主键 - B树InnoDB使用主键按照顺序组织表结构

如果没有主键，会按照表顺序，选择第一个非空唯一索引组织表结构

如果没有符合以上规则的，MySQL会自动生成6BYTE主键(性能不佳)、

不适用频繁更新的列作为主键

不使用联合索引作为主键

不使用UUID、MD5、HASH、字符串作为主键(无法保证顺序增长)

建议选择/使用自增ID列

TODO 索引于B树

C、常见索引列SELECT、UPDATE、DELETE语句中WHERE从句中的列

包含ORDER BY、GROUP BY、DISTINCT中的列

多表的JOIN关联列

D、索引列顺序联合索引从左向右使用

区分度最高的列(主键、唯一)，置于联合索引的最左侧

字段长度小的列置于联合索引的最左侧元素占用空间小，内存页中元素多，索引速度快

使用最频繁的列，置于联合索引的最左侧

避免冗余和重复索引

对于频繁的查询，优先考虑使用覆盖索引(所有的列都建立索引)避免InnoDB索引的二次查找

把随机IO变为顺序IO加快查询效率

避免使用外键外键用于保证数据的参照完整性，建议置于业务端实现

外键影响父表和子表的写操作，从而降低性能

不建议使用外键约束，但一定在表与表之间的关联键上建立索引

上述描述中有一条联合索引最左侧，使用数据区分度高的列。关于区分度，唯一值和总行数比值，区分度越高(接近1)。

SELECT

COUNT(DISTINCT cat_1)/COUNT(*) AS cat_1_rate,

COUNT(DISTINCT cat_6)/COUNT(*) AS cat_6_rate

FROM OuterCooperationDB.product_core;

+------------+------------+| cat_1_rate | cat_6_rate |

+------------+------------+| 0.0002 | 0.0381 |

+------------+------------+

四、字段设计规范

A、优先选择符合存储需要的最小数据类型将字符串转化为数字类型存储(INET_AION、IP->Integer；INET_NTOA、Integer->IP)

对于非负整数，优先使用无符号整型存储(id)

VARCHAR(N)代表的是字符数，不是BYTE数VARCHAR(255)，可以保存255中文字符，使用UTF8，实际占用765BYTE

过大长度消耗更多的内存

B、避免使用TEXT、BLOB数据类型TinyText、Text(64K)、MidumText、LongText

进行排序等查询，无法使用内存临时表，而必须使用磁盘临时表

读取数据时，需要二次查询

非用不可的情况，可以把BLOB和TEXT拆分到单独的拓展表中

C、避免使用ENUM数据类型

D、尽可能把所有列定义为NOT NULL索引NULL列，需要额外的空间，要占用更多的空间

进行比较和计算时，需要对NULL值做特别的处理

E、使用TIMESTAMP或DATETIME类型存储时间禁止使用字符串存储日期型数据无法用日期函数进行计算和比较

使用字符串存储占用更多的空间

TIMESTAMP(4BYTE)，DATETIME(8BYTE)TIMESTAMP范围小，1970-01-01 00:00:01 - 2038-01-19 03:14:07

DATETIME范围大

F、金额相关类型，使用decimal类型存储float、double不精确

decimal精确decimal占用空间由宽度决定

可以存储比bigint更大的整数数据

五、SQL开发规范

A、建议使用预编译语句进行数据库操作重复使用执行计划、减少编译所需时间

传递参数比传递SQL语句，减少网络IO

避免动态SQL导致的注入问题

mysql> PREPARE stmt

-> FROM 'SELECT SQRT(POW(?,2) + POW(?, 2)) AS hypotenuse';

mysql> SET @a = 3;

mysql> SET @b = 4;

mysql> EXECUTE stmt USING @a, @b;

mysql> DEALLOCATE PREPARE stmt;

B、避免类型隐式转换常见于WHERE从句，列类型与参数类型不一致，可能出现隐式转换

隐式转换导致索引失效

C、充分利用表中已存在的索引避免使用双%的查询条件，如a like %.log%

避免使用前置%的查询条件；对于后置%，可以利用列上索引

一个SQL只能利用联合索引中的一列进行范围查询

使用LEFT JOIN或NOT EXISTS来优化NOT IN查询

D、程序连接不同数据库，需使用不同账号，禁止跨库查询为数据库迁移和分库分表留出余地

减低业务耦合度

避免权限过大而产生的安全风险

E、禁止使用SELECT *进行查询避免消耗CPU和网络IOTEXT类型还会进行二次读取

无法使用覆盖索引

减少表结构变更对程序带来的影响

F、禁止使用不含字段列表的INSERT语句减少表结构变更对程序带来的影响

G、避免使用子查询子查询结果集无法使用索引，结果集数据量大则严重影响效率

子查询会产生临时表，消耗CPU和IO资源，引起慢查询

子查询可读性更高，但是会影响性能，可以优化为JOIN操作

H、避免使用JOIN关联太多的表每JOIN一个表会多占用关联内存(join_buffer_size)

MySQL最多允许61个表，建议不超过5个

I、减少通数据库交互次数数据库更适合批量操作提高SQL处理效率

J、使用IN代替OR语句IN不要超过500个

IN操作可以有效利用索引

K、禁止使用ORDER BY RAND()进行随机排序会把所有符合条件的数据加载到内存中，进行排序通过业务代码完成随机数据获取

L、禁止WHERE从句中对列进行函数转换和计算导致索引失效，WHERE DATE(createtime) = '20180925'

可以变通SQL，继续使用列索引，WHERE createtime >= '20180925' AND createtime < '20180926'

M、(明显不会有重复值时)使用UNION ALL而不是UNION进行结果集合并UNION会把所有数据放入临时表进行去重操作

N、拆分复杂大SQL为多个小SQL进行查询MySQL不支持一个SQL的多CPU并行计算

SQL拆分后可以通过并行执行提高处理效率

六、数据库操作行为规范(开发、运维)

A、对于超过百万行的批量写操作，需要分批多次进行操作binlog日志为row格式时会产生大量的日志

大批量写操作，可能会导致严重的主从延迟

涉及事务，对大量数据进行锁定，产生大量阻塞，导致可用链接被消耗

B、对于大表结构修改pt-online-schema-change工具进行修改

TODO 原理

避免大表修改产生的主从延迟

避免对表字段修改时的锁表

C、禁止为程序使用账号赋予super权限(维护)遵循最小权限原则

原则上不具有DROP权限