数据库优化-单机优化：三范式、反三范式、使用合适引擎

原创已于 2023-02-21 20:25:32 修改 · 726 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #java

于 2020-10-05 00:21:34 首次发布

数据库专栏收录该内容

5 篇文章

订阅专栏

本文介绍了数据库优化的必要性，如高并发、海量数据处理和高可用性需求，并详细阐述了SQL优化的步骤，包括表结构、索引、SQL代码的改进。提到了单机和集群优化的方法，如分表分库、读写分离和使用合适的存储引擎。此外，还讲解了如何通过监控和分析SQL执行情况来定位和解决慢查询问题，以及如何开启和使用慢查询日志。文章最后讨论了数据库设计中的范式理论和反范式设计，以及如何根据业务场景选择适合的存储引擎。

优化关系型的数据库原因：

1、高并发读写需求购物双11 亿人一起浏览下单，一台数据库最大连接数是有限的，这时候就需要集群和分布式。
2、海量数据的高效率读写，比如京东的商品用户等，每一张表有上亿条数据的时候，读写效率比较低，这时候就需要采用分表分库。
3、动态添加服务器一台数据库服务器扩充到多台时，不下电情况是很难做到的。
单点故障：一台数据库服务器挂了。业务就中断，期望去找还好的数据库继续提供服务。这时候就需要数据库的高扩展性和可用性，可以采用集群优化。

优化sql的步骤

定位慢sql
分析慢sql
给出解决方案（优化）
优化方案
单机（表结构、索引、Sql(代码问题)）：
表
合适表结构 3ＮＦ和反3NF
合适引擎
索引
分表(垂直分表和水平分表)
Sql优化技巧
多机（IO瓶颈）：
集群（读写分离，让多台服务器提供服务）
分布式（把不同的业务分给不同的集群处理）
其他优化方案
缓存 es 页面静态化

- 测试：

准备两个数据库400万数据进行测试

- 查询数据库(mysql)基本状态
运行多久

show status like ‘uptime’;
CRUD执行次数
show status like ‘%Com_%’
CRUD次数
show status like ‘%Com_select%’
show status like ‘%Com_insert%’
show status like ‘%Com_update%’
show status like ‘%Com_delete%’
Show session/global status like ‘%Com_select%’
show [session|global] status like … 如果你不写[session|global] 默认是session 会话(指取出当前窗口的执行)，如果你想看所有(从mysql 启动到现在)，则应该 global。
查询所有连接数
show status like ‘connections’
查看服务器响应的最大连接数
show status like ‘Max_used_connections’
通过查询（工作环境）最大并发连接数可以配置作为我们配置mysql最大连接数的依据。通常，mysql的最大连接数默认是100, 最大可以达到16384（理论上）。
查询慢查询次数
show status like ‘slow_queries’
查看和修改慢查询时间阈值
show variables like ‘long_query_time’ //可以显示当前慢查询时间
set long_query_time=1 ;//可以修改慢查询时间
set GLOBAL long_query_time=0.6；全局修改慢查询时间
注意：
直接修改global 的long_query_time 之后在当前的的窗口中是没有效果的，在新打开的窗口中才会有效果。如果想让本窗口也有效果的话，不用加 global关键字。
把慢查询记录到日志中
bin\mysqld.exe --safe-mode --slow-query-log [mysql5.5 可以在my.ini指定]
注意事项：
1 必须在mysql的安装目录执行，因为执行的使用依赖于my.ini（C:\Program Files\MySQL\MySQL Server 5.7）,
2 慢查询日志会输出到data.dir（datadir=C:/ProgramData/MySQL/MySQL Server 5.7\Data）

什么时候开启慢查询？

         系统中所有sql都执行一遍，才能判断是否有慢sql。什么时候开启能覆盖所有sql执行？
      开发者自验：
           开发完成后，需要统一打包，统一部署，统一验证。
      测试人员测试：
           测试人员需要测试所有功能。
      项目上线：开一段时间,把它关了.或者不开
           用户用了所有功能。

explain(分析sql语句)

explain sql语句；
EXPLAIN select * from emp WHERE empno=4099030;

通过 explain 语句可以分析，mysql如何执行你的sql语句.

在这里插入图片描述

找到原因以后就解决

一、单机优化

遵循三范式

1NF：表的列具有原子性，不可再分解。
即列的信息，不能分解.只要数据库是关系型数据库(mysql/oracle/db2/sysbase/sql server)，就自动的满足1NF.关系型数据库中是不允许分割列的。

2NF：表的记录是唯一的，我们使用主键来实现。
3NF：表中不要有冗余数据, 就是说，表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放

设计合适的反三范式：

反3NF :没有冗余的数据库表未必是最好的数据库表，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是：在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。

比如浏览次数、评论次数等，为了提高效率，直接在在外键表添加浏览数。方便查询！
注意：当一个表数据发生改变是冗余字段也需要发生改变，可以使用触发器，提高效率，不用操作两个或者多个表。使用CREATE TRIGGER 来创建触发器。这里就不多说了，百度很多案例。。。
数据库触发器：https://www.cnblogs.com/zh-1721342390/p/9602941.html