MySQL 开发规范与使用技巧总结

命名规范

1. 库名, 表名, 字段名必须使用小写字母, 并采用下划线分割.

a)MySQL 有配置参数 lower_case_table_names, 不可动态更改, Linux 系统默认为 0, 即库表名以实际情况存储, 大小写敏感. 如果是 1, 以小写存储, 大小写不敏感. 如果是 2, 以实际情况存储, 但以小写比较.

b)如果大小写混合使用, 可能存在 abc,Abc,ABC 等多个表共存, 容易导致混乱.

c)字段名显示区分大小写, 但实际使用不区分, 即不可以建立两个名字一样但大小写不一样的字段.

d)为了统一规范, 库名, 表名, 字段名使用小写字母.

2. 库名, 表名, 字段名禁止超过 32 个字符.

库名, 表名, 字段名支持最多 64 个字符, 但为了统一规范, 易于辨识以及减少传输量, 禁止超过 32 个字符.

3. 使用 INNODB 存储引擎.

INNODB 引擎是 MySQL5.5 版本以后的默认引擘, 支持事务, 行级锁, 有更好的数据恢复能力, 更好的并发性能, 同时对多核, 大内存, SSD 等硬件支持更好, 支持数据热备份等, 因此 INNODB 相比 MyISAM 有明显优势.

4. 库名, 表名, 字段名禁止使用 MySQL 保留字.

当库名, 表名, 字段名等属性含有保留字时, SQL 语句必须用反引号引用属性名称, 这将使得 SQL 语句书写, SHELL 脚本中变量的转义等变得非常复杂.

5. 禁止使用分区表.

分区表对分区键有严格要求; 分区表在表变大后, 执行 DDL,SHARDING, 单表恢复等都变得更加困难. 因此禁止使用分区表, 并建议业务端手动 SHARDING.

6. 建议使用 UNSIGNED 存储非负数值.

同样的字节数, 非负存储的数值范围更大. 如 TINYINT 有符号为 -128-127, 无符号为 0-255.

7. 建议使用 INT UNSIGNED 存储 IPV4.

用 UNSINGED INT 存储 IP 地址占用 4 字节, CHAR(15)则占用 15 字节. 另外, 计算机处理整数类型比字符串类型快. 使用 INT UNSIGNED 而不是 CHAR(15)来存储 IPV4 地址, 通过 MySQL 函数 inet_ntoa 和 inet_aton 来进行转化. IPv6 地址目前没有转化函数, 需要使用 DECIMAL 或两个 BIGINT 来存储.

例如:

SELECT INET_ATON('209.207.224.40'); 3520061480SELECT INET_NTOA(3520061480);
209.207.224.40

8. 强烈建议使用 TINYINT 来代替 ENUM 类型.

ENUM 类型在需要修改或增加枚举值时, 需要在线 DDL, 成本较高; ENUM 列值如果含有数字类型, 可能会引起默认值混淆.

9. 使用 VARBINARY 存储大小写敏感的变长字符串或二进制内容.

VARBINARY 默认区分大小写, 没有字符集概念, 速度快.

10.INT 类型固定占用 4 字节存储

例如 INT(4)仅代表显示字符宽度为 4 位, 不代表存储长度. 数值类型括号后面的数字只是表示宽度而跟存储范围没有关系, 比如 INT(3)默认显示 3 位, 空格补齐, 超出时正常显示, Python,Java 客户端等不具备这个功能.

11. 区分使用 DATETIME 和 TIMESTAMP.

存储年使用 YEAR 类型. 存储日期使用 DATE 类型. 存储时间 (精确到秒) 建议使用 TIMESTAMP 类型.

DATETIME 和 TIMESTAMP 都是精确到秒, 优先选择 TIMESTAMP, 因为 TIMESTAMP 只有 4 个字节, 而 DATETIME8 个字节. 同时 TIMESTAMP 具有自动赋值以及自动更新的特性. 注意: 在 5.5 和之前的版本中, 如果一个表中有多个 timestamp 列, 那么最多只能有一列能具有自动更新功能.

如何使用 TIMESTAMP 的自动赋值属性?

a)自动初始化, 而且自动更新:

column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP

b)只是自动初始化:

column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP

c)自动更新, 初始化的值为 0:

column1 TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP

d)初始化的值为 0:

column1 TIMESTAMP DEFAULT 0

12. 所有字段均定义为 NOT NULL.

a)对表的每一行, 每个为 NULL 的列都需要额外的空间来标识.

b)B 树索引时不会存储 NULL 值, 所以如果索引字段可以为 NULL, 索引效率会下降.

c)建议用 0, 特殊值或空串代替 NULL 值.

MySQL 使用技巧

1. 将大字段, 访问频率低的字段拆分到单独的表中存储, 分离冷热数据.

有利于有效利用缓存, 防止读入无用的冷数据, 较少磁盘 IO, 同时保证热数据常驻内存提高缓存命中率.

2. 禁止在数据库中存储明文密码.

采用加密字符串存储密码, 并保证密码不可解密, 同时采用随机字符串加盐保证密码安全.

3. 表必须有主键, 推荐使用 UNSIGNED 自增列作为主键.

表没有主键, INNODB 会默认设置隐藏的主键列; 没有主键的表在定位数据行的时候非常困难, 也会降低基于行复制的效率.

4. 禁止冗余索引.

索引是双刃剑, 会增加维护负担, 增大 IO 压力.(a,b,c),(a,b), 后者为冗余索引. 可以利用前缀索引来达到加速目的, 减轻维护负担.

5. 禁止重复索引.

primary key a;uniq index a; 重复索引增加维护负担, 占用磁盘空间, 同时没有任何益处.

6. 不在低基数列上建立索引, 例如 "性别".

大部分场景下, 低基数列上建立索引的精确查找, 相对于不建立索引的全表扫描没有任何优势, 而且增大了 IO 负担.

7. 合理使用覆盖索引减少 IO, 避免排序.

覆盖索引能从索引中获取需要的所有字段, 从而避免回表进行二次查找, 节省 IO.

INNODB 存储引擎中, secondary index(非主键索引, 又称为辅助索引, 二级索引)没有直接存储行地址, 而是存储主键值.

如果用户需要查询 secondary index 中所不包含的数据列, 则需要先通过 secondary index 查找到主键值, 然后再通过主键查询到其他数据列, 因此需要查询两次. 覆盖索引则可以在一个索引中获取所有需要的数据, 因此效率较高.

例如 SELECT email,uid FROM user_email WHERE uid=xx, 如果 uid 不是主键, 适当时候可以将索引添加为 index(uid,email), 以获得性能提升.

8. 用 IN 代替 OR.SQL 语句中 IN 包含的值不应过多, 应少于 1000 个.

IN 是范围查找, MySQL 内部会对 IN 的列表值进行排序后查找, 比 OR 效率更高.

9. 表字符集使用 UTF8, 必要时可申请使用 UTF8MB4 字符集.

a)UTF8 字符集存储汉字占用 3 个字节, 存储英文字符占用一个字节.

b)UTF8 统一而且通用, 不会出现转码出现乱码风险.

c)如果遇到 EMOJ 等表情符号的存储需求, 可申请使用 UTF8MB4 字符集.

10. 用 UNION ALL 代替 UNION.

UNION ALL 不需要对结果集再进行排序.

11. 禁止使用 order by rand().

order by rand()会为表增加一个伪列, 然后用 rand()函数为每一行数据计算出 rand()值, 然后基于该行排序, 这通常都会生成磁盘上的临时表, 因此效率非常低. 建议先使用 rand()函数获得随机的主键值, 然后通过主键

获取数据.

12. 建议使用合理的分页方式以提高分页效率.

假如有类似下面分页语句:

SELECT * FROM table ORDER BY TIME DESC LIMIT 10000,10;

这种分页方式会导致大量的 io, 因为 MySQL 使用的是提前读取策略.

推荐分页方式:

SELECT * FROM table WHERE TIME

13.SELECT 只获取必要的字段, 禁止使用 SELECT *.

减少网络带宽消耗;

能有效利用覆盖索引;

表结构变更对程序基本无影响.

14.SQL 中避免出现 now(),rand(),sysdate(),current_user()等不确定结果的函数.

语句级复制场景下, 引起主从数据不一致; 不确定值的函数, 产生的 SQL 语句无法利用 QUERY CACHE.

15. 采用合适的分库分表策略. 例如千库十表, 十库百表等.

采用合适的分库分表策略, 有利于业务发展后期快速对数据库进行水平拆分, 同时分库可以有效利用 MySQL

的多线程复制特性.

16. 减少与数据库交互次数, 尽量采用批量 SQL 语句.

使用下面的语句来减少和 db 的交互次数:

a)INSERT ... ON DUPLICATE KEY UPDATEb)REPLACE INTOc)INSERT IGNORE d)INSERT INTO VALUES()

17. 拆分复杂 SQL 为多个小 SQL, 避免大事务.

简单的 SQL 容易使用到 MySQL 的 QUERY CACHE; 减少锁表时间特别是 MyISAM; 可以使用多核 CPU.

18. 对同一个表的多次 alter 操作必须合并为一次操作.

MySQL 对表的修改绝大部分操作都需要锁表并重建表, 而锁表则会对线上业务造成影响. 为减少这种影响, 必须把对表的多次 alter 操作合并为一次操作. 例如, 要给表 t 增加一个字段 b, 同时给已有的字段 aa 建立索引,

通常的做法分为两步:

alter table t add column b varchar(10); 然后增加索引: alter table t add index idx_aa(aa); 正确的做法是: alter table t add column b varchar(10),add index idx_aa(aa);

19. 避免使用存储过程, 触发器, 视图, 自定义函数等.

这些高级特性有性能问题, 以及未知 BUG 较多. 业务逻辑放到数据库会造成数据库的 DDL,SCALE OUT,

SHARDING 等变得更加困难.

20. 禁止有 super 权限的应用程序账号存在.

安全第一. super 权限会导致 read only 失效, 导致较多诡异问题而且很难追踪.

21. 不要在 MySQL 数据库中存放业务逻辑.

数据库是有状态的服务, 变更复杂而且速度慢, 如果把业务逻辑放到数据库中, 将会限制业务的快速发展. 建议把业务逻辑提前, 放到前端或中间逻辑层, 而把数据库作为存储层, 实现逻辑与存储的分离.

来源: http://database.51cto.com/art/201910/605001.htm