编程之路

  • home
  • article
  • class
  • label
  • utils

  • 搜索
aop 边缘计算 框架 物联网 敏捷开发 团队 压力测试 Markdown 学习方法 学习 产品 规范 日志 微服务 壁纸 开发平台 Java 全栈 前端 开发规范 MQTT CentOS 镜像站 IntelliJ IDEA FreeMarker UML 计算机 软件 Tomcat Netty Web Service Docker Dubbo Kafka NoSQL Redis 消息队列 RocketMQ RabbitMQ ActiveMQ 分布式事务 Spring 队列 Java 高级 GC JVM HTTP 网络安全 算法 设计模式 Spring Cloud Web SpringMVC 线程池 并发 锁🔒 多线程 Git Java 集合 Java 基础 MyBatis 数据库 MySQL Java 基础面试题 Java Nginx Linux Spring Boot

MySQL核心应用开发规范

发表于 2022-11-17 | 分类于 数据库 | 0 | 阅读次数 137

核心原则:

1、定义宽表 执行show table status:

​ 1.1、当avg_row_length的值超过100个字节时(定义为宽表),需要看看怎么优化;

​ 1.2、当Data_free的值大于0,表明有碎片;

2、表越小越好

​ 2.1、表越小IO压力越小

​ 2.2、窄表数据量大性能也好(比如5亿数据)

​ 2.3、宽表数据量小有可能性能也不好(比如50万)

​ 2.4、实验测算,只有一个int类型,单表可存9.7亿,b+tree的根节点到叶子io也只有三次;

3、请求要足够高效 3.1、事务要尽快提交或回滚;(修改、删除数据会产生行锁,或者产生表锁,将导致其他sql或者事务被阻塞)

3.2、监控:

​ 3.2.1、可以监控MySQL的线程状态,监控InnoDB的事务状态,一般是大于5秒 3.2.2、设置修改、锁定行数的阈值,比如大于10行 可以避免潜在长时间锁或者事务SQL运行的风险

​ 3.2.3、检查或者监控SQL注入的风险(例如:SLEEP函数,UNION ALL函数)

重要的细节

​ 1、schema设计原则

​ 1.1、尽量小的原则

​ 1.2、禁止使用外键「增加行锁」(高并发不建议)

​ 1.3、自增INT/BIGINT主键(InnoDB引擎表)「备注:CHAR或者UUID主键,数据存储的瞬息离散随机,会造成数据页在存储时有大量的碎片,造成磁盘浪费,性能下降」

​ 1.4、字符集和库表的设计要一致(mysql实例-database-数据库表-字段-存储过程-event)否者报错。eg:utf8:3个字节,utf8mb4:4个字节,join类型转换无法走索引。

​ 1.5、MySQL尽可能高效的建议 表的维度:

​ 1.5.1、单表数据量尽量不超过5000万;

​ 1.5.2、单表的物理大小不要超过20个G

​ 1.5.3、索引的数量不要超过5个 实例维度:

​ 1.5.4、总的大小不要超过500G

​ 1.5.5、总表数量不超过5000个(包括分区表)

2、库表字段设计规范

​ 2.1、每个表建议不超过50个字段

​ 2.2、优先选择utf8mb4字符集(支持移动终端的emoji符号,表情包)

​ 2.3、严禁在数据库中明文存储用户的一些核心数据(最好要单向加密)

​ 2.4、用好INT数据类型(最好加上UNSIGNED,可扩大使用范围)「金额用途,扩大N倍,用bigint或者int来存储」

​ 2.5、遇到BLOB、TEXT字段,尽量要拆出去,再用主键做关联

​ 2.6、字符类型尽可能采用varchar的数据类型(灵活、高效)「char占用空间固定」,最好不要变长更新(会照成磁盘碎片)

​ 2.7、日期时间数据建议采用datetime(0000-9999年)类型(比timestamp「1970-2038年」多一个字节),5.6以后多个datetime数据类型自动更新为当前时间,5.6之前只有一个可以可以更新为当前时间

3、SQL开发建议

​ 3.1、多表join时,join列的数据类型要一致(长度、类型,字符集)

​ 3.2、多表join时,把过滤后结果集较小的表作为驱动表(统一采用inner join让优化器自动优化,如果优化器优化错误,可以采用straight_join强制执行顺序 ) 3.3、【建议】在查询的where条件中用上函数或表达式要8.0版本(8.0支持函数索引、表达式索引、支持不可见索引、支持倒叙索引)

​ 3.4、不要看到where条件中出现的列就直接创建索引

​ 3.5、尽可能不要去执行select*操作(会导致io代价高)

​ 3.6、不要执行like ’%x%‘,可以用like ’x%'前缀索引

​ 3.7、尽量不要用 ‘!=’ 条件(扫描的数据量超过20%~30%范围时,会把执行计划变成全表扫描,不管有无索引)「但不是所有的都会如此,具体问题具体分析,比如反例:图3、图4」 3.8、如果能确定返回结果数量的话,最好加上limit n「优化器通常会再进一步优化,取到以后会立即换回,不再继续扫描」

​ 3.9、优先使用union all,代替union(减少临时表的生成「5.6之前也会生成临时表」)

​ 3.10、所有SQL都要通过SQL审核系统检查符合标准后才能上线 。

# MySQL
服务器防火墙
“一把梭:REST API 全用 POST”
  • 文章目录
  • 站点概览
Adrian

Adrian

曙光在头上,不抬起头,便永远只能看见物质的闪光。

119 日志
11 分类
67 标签
RSS
Creative Commons
Links
  • 美团技术团队
  • 阮一峰
  • 程序猿DD
  • SpringBoot 中文社区
  • 在线文档
  • Bean Searcher
  • OkHttps
  • Grails
  • Sa-Token
  • 程序员的进击之路
  • bugstack 虫洞栈
  • Java 全栈知识体系
  • Gobrs-Async
  • 查询网
  • 微信开放社区
  • 物联网技术指南
  • emqx
  • 看云
  • 深圳核酸检测点查询
  • Hutool
  • Spring
  • V2EX
  • v-charts
  • Vert.x 官方文档
  • Vert.x 官方文档中文翻译
  • 极客时间
  • Apache RocketMQ 开发者指南
  • 知了
  • 阿里云知行动手实验室
  • Learn Git Branching
  • Spring Boot 教程
  • 未读代码
  • 如梦技术
  • jpom
  • Cubic
  • Easy-Es
  • bing-wallpaper
  • solon
  • LuatOS
  • ThingsBoard
  • Linux 中国◆开源社区
  • Apache Dubbo
  • Jenkins
  • 技术文章摘抄
  • VueJS
  • MapStruct
  • elasticsearch 中文社区
  • Apollo(阿波罗)
  • TiKV文档
  • Chrome插件分享
  • 一步步搭建物联网系统(教你设计物联网系统)
  • 全栈增长工程师指南
  • 程序员的自我修养
  • Pro Git(中文版)
  • 学习 Web 开发
  • 极客教程
  • PingCAP 文档中心
  • 酷壳
  • Refactoring Guru 网站
  • 学习 Java 语言
  • smart-doc
  • mybatis-plus
  • 字母哥博客
0%
© 2023 Adrian
由 Halo 强力驱动
|
主题 - NexT.Gemini v5.1.4