乐趣区

关于后端:MySQL-设计与查询规范

jiezi

背景

设想一下本人是一名伐木工人，手里有林场里最好的斧子，因而你是工作效率最高的。忽然有一天场里来了个采购的，他把一种新的砍树工具——链锯——给夸到了天上去。你也买了一把，不过你不懂得怎么用。你估摸着依照本人原来善于的砍树办法，把链锯鼎力地挥向树干……

MySQL 这个工具也是一样，设计规范就是的一个很好的工具阐明。即对立了命名格调，又能够让新人疾速上手。

本文的次要内容能够在网上找到相似的版本，然而在一些细节点又稍微不同。基于多年 MySQL 应用教训，基于利用与 MySQL 的通盘考虑（视 MySQL 为低配版本的：Bigtable + KV），才有了这些细节上的调整。

命名

防止应用 MySQL 关键词 作为 db / table / field / index 名称

  • DB

    • 应用我的项目名作为前缀，“_db”作为后缀；分库增加后缀 8 位宽度的数字，数字从 0 开始
    • 格调：由下划线宰割的小写英文字母组成
    • DB 名称总长度小于 42 个字符

  • Table

    • “_db”作为后缀；分表增加后缀 8 位宽度的数字，数字从 0 开始
    • 格调：由下划线宰割的小写英文字母组成
    • 表名称总长度小于 48 个字符

  • Field

    • 主键对立定义为：id BIGINT UNSIGNED NOT NULL
    • 指向其余表主键的字段以“_id”后缀结尾
    • 格调：由下划线宰割的小写英文字母组成

  • Index

    • 应用“idx_”作为前缀；索引字段名字、程序组合为名称
    • 格调：由下划线宰割的小写英文字母组成

  • Comment

    • 纯英文单词正文所有字段

DB

  • 应用 Innodb 存储引擎

    Innodb 反对事务，反对行级锁，更好的恢复性，高并发下性能更好

  • 应用 utf8mb4_unicode_ci 编码

    兼容性更好，对立字符集能够防止因为字符集转换产生的乱码，不同的字符集进行比拟前须要进行转换会造成索引生效

Table

  • 应用 utf8mb4_unicode_ci 编码

  • 每张表必须显式定义主键

    1. 数据的存储程序和主键的程序是雷同的
    2. 不要应用更新频繁的列作为主键，不要应用 UUID、MD5、HASH、字符串等无奈保证数据的程序增长的字段作为主键

  • 尽量管制单表数据量的大小，倡议管制在 1000 万 以内

    1. 该量级数据量查问性能较好
    2. 能够用历史数据归档，分库分表等伎俩来管制单表数据量

  • 宽表尽量拆分为索引表和内容表以进步查问性能

    1. MySQL 限度每个表最多存储 4096 列，并且每一行数据的大小不能超过 65535 字节 缩小磁盘 IO，保障热数据的内存缓存命中率
    2. 表越宽，装载进内存缓冲池时所占用的内存也就越大, 也会耗费更多的 IO，更无效的利用缓存，防止读入无用的冷数据

  • 审慎应用 JOIN

    1. 应用层缓存效率更高，能够在多种查问场景复用缓存
    2. 在应用层做关联，能够更容易对数据库进行拆分，更容易做到高性能和可扩大
    3. 查问效率晋升。应用 ID 查问，能够让 MySQL 依照主键索引程序查问，相比关联要更稳固高效

  • 审慎应用 MySQL 分区表

    分区表在物理上体现为多个文件，在逻辑上体现为一个表 审慎抉择分区键，跨分区查问效率可能更低 倡议采纳物理分表的形式治理大数据

  • 不要应用外键

    1. MySQL 外键实现比较简单毛糙，性能不好
    2. MySQL 作为后端存储，不在 MySQL 上搁置任何计算逻辑
    3. 如果依赖于在 MySQL 服务器上运行的计算逻辑，进行数据库 / 表分片将十分艰难

Field

  • 优先选择合乎存储须要的最小的数据类型

    列的字段越大，索引时所须要的空间越大，磁盘单页存储的索引节点数越少，遍历时 IO 次数就越多，索引性能也就越差

    办法：
    1）将字符串转换成数字类型存储，如：将 IP 地址转换成整形数据（inet_aton / inet_ntoa）
    2）对于非负型的数据（如自增 ID、整型 IP）来说，要优先应用无符号整型来存储

  • 存储雷同数据的列名和列类型必须统一

    如果查问时关联列类型不统一会主动进行数据类型隐式转换，会造成列上的索引生效，导致查问效率升高

  • 尽可能把所有列定义为 NOT NULL

    • NULL 占用额定的空间来保留
    • NULL 须要非凡解决，可能会导致应用程序异样
    • NULL MySQL 索引统计和值比拟更简单

  • 防止应用 ENUM 类型

    • 批改 ENUM 值须要应用 ALTER 语句
    • ENUM 类型的 ORDER BY 操作效率低，须要额定操作
    • 禁止应用数值作为 ENUM 的枚举值

  • 禁止在数据库中存储长文本、图片，文件等大数据

    MySQL 内存长期表不反对 TEXT、BLOB 大数据类型，如果查问中蕴含这样的数据，在排序等操作时，就不能应用内存长期表，必须应用磁盘长期表进行

    而且对于这种数据，MySQL 还是要进行二次查问，会使 SQL 性能变得很差，然而不是说肯定不能应用这样的数据类型

  • 禁止建设预留字段

    • 预留字段的命名很难做到见名识义
    • 预留字段无奈确认存储的数据类型，所以无奈抉择适合的类型
    • 对预留字段类型的批改，会对表进行锁定

Index

  • 限度每张表上的索引数量，倡议单张表索引不超过 5 个

    MySQL 优化器优化查问时，会依据统计信息，对候选索引来进行评估，以生成出一个最好的执行打算，如果同时有很多个索引都能够用于查问，就会减少 MySQL 优化器生成执行打算的工夫，同样会升高查问性能

Stored Programs

  • 禁止应用 mysql 视图，存储过程，触发器，自定义函数

Queries

  • 禁止直连生产环境，手工删除和批改生产数据

  • 禁止应用 SELECT * 必须应用 SELECT < 字段列表 > 查问

    可缩小表构造变更对应用程序的影响

  • 禁止应用不含字段列表的 INSERT 语句

    正确：INSERT INTO tbl(c1,c2,c3) VALUES (a,b,c);
    谬误：INSERT INTO VALUES (a,b,c);

  • WHERE 从句中禁止对列进行函数转换和计算

    对列进行函数转换或计算时会导致无奈应用索引。

    正确：WHERE create_time >= 20190101 AND create_time < 20190102
    谬误：WHERE DATE(create_time)=20190101

  • 不会有反复值时应用 UNION ALL 而不是 UNION

    UNION 将后果集的所有数据放到长期表后再去重
    UNION ALL 不会再对后果集进行去重

参考链接：
https://www.cnblogs.com/hucho…

本文作者 ：cyningsun
本文地址 ：https://www.cyningsun.com/06-…
版权申明：本博客所有文章除特地申明外，均采纳 CC BY-NC-ND 3.0 CN 许可协定。转载请注明出处！

退出移动版