原文传送门：https://blog.csdn.net/tian330726/article/details/88713086
一、基础规范
二、命名规范
三、字段设计规范
四、索引设计规范
五、SQL查询规范
一、基础规范
- 使用InnoDB 存储引擎
没有特殊要求（即Innodb无法满足的功能如：列存储，存储空间数据等）的情况下，所有表必须使用Innodb存储引擎（mysql5.5之前默认使用Myisam，5.6以后默认的为Innodb）Innodb 支持事务，支持行级锁，更好的恢复性，高并发下性能更好
- 表字符集使用utf8mb4
默认使用 utf8mb4 字符集，数据库排序规则使用 utf8mb4_general_ci，采用 utf8 编码的 MySQL 无法保存占位是 4 个字节的 Emoji 表情。为了使后端的项目全面支持客户端输入的 Emoji 表情，升级编码为 utf8mb4 是最佳解决方案；
兼容性更好，统一字符集可以避免由于字符集转换产生的乱码，不同的字符集进行比较前需要进行转换会造成索引失效
- 所有表都需要添加注释
使用comment从句添加表和列的备注 从一开始就进行数据字典的维护
- 单表数据量建议控制在500万以内
500万并不是MySQL数据库的限制，过大会造成修改表结构，备份，恢复都会有很大的问题，可以用历史数据归档（应用于日志数据），分库分表（应用于业务数据）等手段来控制数据量大小
- 不在数据库中存储图、文件等大的二进制数据
通常文件很大，会短时间内造成数据量快速增长，数据库进行数据库读取时，通常会进行大量的随机IO操作，文件很大时，IO操作很耗时 通常存储于文件服务器，数据库只存储文件地址信息
- 尽量做到冷热数据分离，减小表的宽度
MySQL限制每个表最多存储4096列，并且每一行数据的大小不能超过65535字节 减少磁盘IO,保证热数据的内存缓存命中率（表越宽，把表装载进内存缓冲池时所占用的内存也就越大,也会消耗更多的IO） 更有效的利用缓存，避免读入无用的冷数据 经常一起使用的列放到一个表中（避免更多的关联操作）
- 禁止在线上做数据库压力测试
- 禁止测试、开发环境直连数据库
二、命名规范
- MySQL有配置参数lower_case_table_names=1，即库表名以小写存储，大小写不敏感。如果是0，则库表名以实际情况存储，大小写敏感；如果是2，以实际情况存储，但以小写比较
- 如果大小写混合使用，可能存在abc，Abc，ABC等多个表共存，容易导致混乱
- 字段名显示区分大小写，但实际使用时不区分，即不可以建立两个名字一样但大小写不一样的字段
- 为了统一规范， 库名、表名、字段名使用小写字母，不允许
-号
2、库名、表名、字段名禁止超过32个字符，需见名知意，建议使用名词不是动词
库名、表名、字段名支持最多64个字符，但为了统一规范、易于辨识以及减少传输量，禁止超过32个字符
3、库名、表名、字段名禁止使用MySQL保留字
当库名、表名、字段名等属性含有保留字时，SQL语句必须用反引号引用属性名称，这将使得SQL语句书写、SHELL脚本中变量的转义等变得非常复杂。
形如：tmp_user_account_20190313
形如：bak_user_account_20190313
6、主键索引名为 pk_ 字段名；唯一索引名为 uk _ 字段名；普通索引名则为 idx_ 字段名
pk_ 即 primary key；uk_ 即 unique key；idx_ 即 index 的简称
7、在不同的库或表中，要保证所有存储相同数据的列名和列类型必须一致
一般作为关联列，如果查询时关联列类型不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低
正例：user_task / force_project / trade_config
三、字段设计规范
列的字段类型越大，建立索引占据的空间就越大，导致一个页中的索引越少，造成IO次数增加，影响性能
- 业务中选择性很少的状态
status、类型type等字段推荐使用tinytint或者smallint类型节省存储空间 - 能用
int的就不用char或者varchar - 能用
tinyint的就不用int - 使用 UNSIGNED 存储非负数值
- 使用
tinyint来代替enum和boolean - 存储 ip 最好用
int存储而非char(15)
通过MySQL函数inet_ntoa和inet_aton来进行转化。IPv6地址目前没有转化函数，需要使用DECIMAL或两个BIGINT来存储
SELECT INET_ATON('209.207.224.40'); 3520061480
SELECT INET_NTOA(3520061480); 209.207.224.40
- 表中的自增列（
auto_increment属性），推荐使用bigint类型
blob，text是为了存储极大的字符串而设计的数据类型，采用二进制与字符串方式存储，该数据类型不能设置默认值、不便于排序、不便于建立索引， varchar 的性能会比 text 高很多，如果非要使用，建议将这种数据分离到单独的拓展表中
- 无法使用日期函数计算比较
- 字符串存储要占更多的内存空间，
datetime(8字节)和timestamp(本身是以int存储，占4字节,范围:1970-01-01 00:00:01到2038-01-19 03:14:07) TIMESTAMP记录经常变化的更新/创建/发布/日志时间等，并且是近来的时间，够用，可免时区处理DATETIME记录生日、纪念事件、超出TIMESTAMP的时间，记得时区处理
4、用 DECIMAL 代替 FLOAT 和 DOUBLE 存储精确浮点数
Decimal类型为精准浮点数，float 和 double 在存储的时候，存在精度损失的问题，很可能在值的比较时，得到不正确的结果。如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数分开存储。
- NULL的列使用索引，索引统计，值都更加复杂，MySQL更难优化
- NULL需要更多的存储空间
- NULL只能采用IS NULL或者IS NOT NULL，而在=/!=/in/not in时有大坑
- 牵扯到国家代号，可能出现+/-/()等字符，例如+86
- 手机号不会用来做数学运算
- varchar可以模糊查询，例如like ‘138%’
- 字段长度固定，或者长度近似的业务场景，适合使用char，能够减少碎片，查询性能高
- 字段长度相差较大，或者更新较少的业务场景，适合使用varchar，能够减少空间
建议在应用层实现外键的逻辑， 外键与级联更新不适合高并发场景，降低插入性能，大并发下容易产生死锁
10、整形定义中不添加长度，比如使用INT，而不是INT[4]
值类型括号后面的数字只是表示宽度而跟存储范围没有关系
核心表（如用户表，金钱相关的表）必须有行数据的创建时间字段create_time和最后更新时间字段update_time，便于查问题
四、索引设计规范
索引其实就是一种数据结构，（哈希表、树等等）不同类型的索引有着不同的数据结构和功能。
MySQL的查询速度依赖良好的索引设计，因此索引对于高性能至关重要。合理的索引会加快查询速度，不合理的索引会降低速度
- 加速查询速度
- 维护数据的约束性（完整性、一致性）
对于加速查询，使用索引不一定是最好的选择。小表就直接全表扫描，中到大表就建索引，超大表就分区分表。其实主要就要索引带来的好处和维护索引的成本之间的权衡。
1、单表的索引数建议不超过 5 个，单个索引中的字段数建议不超过 5 个
太多就起不到过滤作用了，索引也占空间，管理起来也耗资源
不要索引blob/text等字段，不要索引大型字段，这样做会让索引占用太多的存储空间
前缀索引就是对文本的前几个字符建立索引，前缀索引能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)
- 表必须有主键
- 不使用更新频繁的列
- 尽量不选择字符串列
- 不使用UUID MD5 HASH
- 默认使用非空的唯一键
- 建议选择自增或发号器
- UPDATE、DELETE 语句的 WHERE 条件列
- ORDER BY、GROUP BY、DISTINCT 的字段
- 多表 JOIN 的字段
- 覆盖索引可以避免Innodb表进行索引的二次查询，把随机IO变成顺序IO，加快查询效率
- 选择筛选性更优的字段放在最前面，比如单号、userid等，type，status等筛选性一般不建议放在最前面
- 索引根据左前缀原则，当建立一个联合索引(a,b,c)，则查询条件里面只有包含(a)或(a,b)或(a,b,c)的时候才能走索引，(a,c)作为条件的时候只能使用到a列索引，所以这个时候要确定a的返回列一定不能太多，不然语句设计就不合理，(b,c)则不能走索引
6、业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引
不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明
显的； 另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生
7、InnoDB 和 MyISAM 存储引擎表，索引类型必须为BTREE
MEMORY表可以根据需要选择 HASH 或者 BTREE 类型索引
- MYISAM 存储引擎索引长度的总和不能超过 1000 字节
- BLOB 和 TEXT 类型的列只能创建前缀索引
- 使用不等于 (!= 或者 <>) 的时候, MYSQL 无法使用索引
- 过滤字段使用函数运算 (如 abs (column)) 后, MYSQL无法使用索引
- join语句中join条件字段类型不一致的时候MYSQL无法使用索引
- 使用 LIKE 操作的时候如果条件以通配符开始 (如 ‘%abc…’)时, MYSQL无法使用索引。
- 使用非等值查询的时候, MYSQL 无法使用 Hash 索引
五、SQL查询规范
- 无法索引覆盖，回表操作，增加 io
- 额外的内存负担，大量冷数据灌入
innodb_buffer_pool_size，降低查询命中率 - 额外的网络传输开销
在保证数据不会有误的前提下，能确定结果集数量时，多使用limit，尽快的返回结果。
3、涉及到复杂sql时，务必先参考已有索引设计，先explain
- 简单SQL拆分，不以代码处理复杂为由
- 比如 OR 条件： f_phone=’10000’ or f_mobile=’10000’，两个字段各自有索引，但只能用到其中一个。可以拆分成2个sql，或者union all
- 先explain的好处是可以为了利用索引，增加更多查询限制条件
in 的值不要超过 500 个， in 操作可以更有效的利用索引，or 大多数情况下很少能利用到索引
- 会把表中所有符合条件的数据装载到内存中，然后在内存中对所有数据根据随机生成的值进行排序，并且可能会对每一行都生成一个随机值，如果满足条件的数据集非常大，就会消耗大量的 CPU 和 IO 及内存资源
- 推荐在程序中获取一个随机值，然后从数据库中获取数据的方式
对列进行函数转换或计算时会导致无法使用索引
不推荐：where date(create_time)='20190101'
推荐：where create_time >= '20190101' and create_time < '20190102'
7、不要使用 count(列名)或 count(常量)来替代 count(*)
count(*)是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关
count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行
以学生和成绩的关系为例，学生表中的 student_id是主键，那么成绩表中的 student_id则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新， 即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群； 级联更新是强阻塞，存在数据库更新风暴的风险； 外键影响数据库的插入速度
若实在避免不了，需要仔细评估 in 后边的集合元素数量，控制在 1000 个之内
需要 join 的字段，数据类型必须绝对一致；多表关联查询时，保证被关联的字段需要有索引；即使双表 join 也要注意表索引、 SQL 性能
11、SELECT语句不要使用UNION，推荐使用UNION ALL
UNION子句个数限制在5个以内。因为union all不需要去重，节省数据库资源，提高性能
不推荐 SELECT * FROM table ORDER BY TIME DESC LIMIT 10000，10;
原因：会导致大量的io，因为MySQL使用的是提前读取策略
推荐：SELECT * FROM table WHERE TIME < last_TIME ORDER BY TIME DESC LIMIT 10.
SELECT * FROM table inner JOIN (SELECT id FROM table ORDER BY TIME LIMIT 10000，10) as t USING(id)
MySQL分页查询的性能优化 — 详细说明
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
