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

41次阅读

共计 2544 个字符,预计需要花费 7 分钟才能阅读完成。

背景

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

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 许可协定。转载请注明出处!

正文完
 0