JAVA面试高级技术栈-04-MySql优化

wlowrd

已于 2024-06-25 11:24:33 修改

阅读量1.4k

点赞数 9

CC 4.0 BY-SA版权

文章标签： java 面试 mysql 开发语言

于 2024-03-27 16:41:02 首次发布

本文链接：https://blog.csdn.net/qq_46133097/article/details/137081593

JAVA面试高级技术栈-04-MySql优化

文章目录

JAVA面试高级技术栈-04-MySql优化

MySQL整个查询的过程

在介绍Mysql之前我们先了解一下MySql的查询过程

客户端向 MySQL 服务器发送一条查询请求

服务器首先检查查询缓存，如果命中缓存，则返回存储在缓存中的结果。否则进入下一阶段

服务器进行 SQL 解析、预处理、再由优化器生成对应的执行计划

MySQL 根据执行计划，调用存储引擎的 API 来执行查询

将结果返回给客户端，同时缓存查询结果

注意：只有在8.0之前才有查询缓存，8.0之后查询缓存被去掉了

MySql优化

首先我们要清楚为什么进行优化,在了解这个问题之前我们先了解一下存储引擎

存储引擎

存储引擎是MySQL的核心，是数据库底层软件组织，数据库使用存储引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁级别、事务等功能。存储引擎是基于表的，而非数据库(因此可以在创建的时候指定存储引擎。)

在这里插入图片描述

myisam存储

不支持事务、也不支持外键，使用表级锁控制并发的读写操作，支持全文索引。MyISAM引擎强调快速读取操作，主要用于高负载的select，对事务完整性没有要求的应用可以用这个引擎来创建表。

如果表对事务要求不高，同时是以查询和添加为主的，我们考虑使用myisam存储引擎，比如bbs 中的发帖表，回复表

当您的库中删除了大量的数据后，您可能会发现数据文件尺寸并没有减小。这是因为删除操作后在数据文件中留下碎片所致。

需要定时进行碎片整理（因为删除的数据还是存在）：optimize table table_name; (OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。)

InnoDB存储

InnoDB是MySQL5.5版本之后的默认存储引擎，它是为了达到处理巨大数据量的最大性能而设计的，其CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的。

对事务要求高，保存的数据都是重要数据，我们建议使用INN0DB,比如订单表，账号表.

MyISAM和INNODB的区别：

1.事务安全
2.查询和添加速度
3.支持全文索引
4.锁机制
5.MyISAM不支持外键，INNODB 支持外键.

Mermory存储

Memory存储引擎通过在内存中创建临时表来存储数据。每个表实际对应一个磁盘文件，该文件的文件名和表名是相同的，类型为.frm。该磁盘文件只存储表的结构，而数据存储在内存中，所以使用该种引擎的表拥有极高的插入、更新和查询效率。由于所存储的数据保存在内存中，如果mysqld进程发生异常、重启或计算机关机等等都会造成这些数据的消失。

比如我们数据变化频繁，不需要入库，同时又频繁的查询和修改，我们考虑使用memory

我们可以在创建表时指定存储引擎

-- 创建表的时候指定存储引擎，默认是 InnoDB
CREATE TABLE test_table(
id int primary key  auto_increment,
name varchar(128) NOT NULL
)ENGINE  = MyISAM;

那么我们到底为什么进行MySql优化

案例 1：索引优化

问题：一个电子商务网站上的产品搜索查询很慢，因为没有为产品名称和描述字段创建索引。
优化：创建产品的名称和描述字段的索引，这显著加快了搜索查询的速度，因为 MySQL 现在可以使用索引来快速查找相关记录。

案例 2：查询优化

问题：一个在线论坛上的热门帖子列表查询非常耗时，因为它使用了嵌套循环来获取帖子信息、作者信息和帖子评论数。
优化：将查询重写为使用连接和聚合函数，这将多个查询合并为一个更有效率的查询。

案例 3：表结构优化

问题：一个社交媒体应用程序中的用户表非常大，并且由于冗余数据而导致更新和删除操作缓慢。
优化：将用户数据拆分为多个表，例如个人信息表、联系信息表和活动表。这减少了冗余并提高了更新和删除操作的速度。

案例 4：缓存优化

问题：一个博客网站上的热门页面经常加载缓慢，因为每次加载页面时都必须从数据库中检索相同的静态内容。
优化：使用缓存机制将热门页面的 HTML 输出存储在内存中，从而避免了对数据库的重复访问并显著提高了页面加载速度。

案例 5：连接池优化

问题：一个高流量网站经常遇到数据库连接超时，因为连接池太小，无法处理大量并发请求。
优化：增加连接池的大小，这为网站提供了更多的可用的数据库连接，减少了连接超时并提高了应用程序的吞吐量。

这些只是 MySQL 优化可以带来显著性能提升的一些具体案例。通过仔细分析应用程序的瓶颈并针对特定需求进行优化，可以显著改善应用程序性能、用户体验和整体系统效率。

SQL性能分析

SQL性能下降原因：

1. 查询语句写的烂
1. 索引失效（数据变更）
1. 关联查询太多join（设计缺陷或不得已的需求）
1. 服务器调优及各个参数设置（缓冲、线程数等）

通常SQL调优过程：

1. 观察，至少跑1天，看看生产的慢SQL情况。
1. 开启慢查询日志，设置阙值，比如超过5秒钟的就是慢SQL，并将它抓取出来。
1. explain + 慢SQL分析。
1. show profile。
1. 运维经理 or DBA，进行SQL数据库服务器的参数调优。

#查看 慢查询日志相关的配置
show variables like '%query%'

# 开启慢查询
set global slow_query_log = 1;

# 设置慢查询的时间阀值。
#  我们为了能看到慢查询日志的效果，把这个时间设置的小一些。
#  执行超过这个时间的sql会被保存到慢查询日志中： slow_query_log_file的值就是慢查询日志文件的路径
set long_query_time = 1;

# Explain语法执行计划的语法其实非常简单： 在 SQL 查询的前面加上 EXPLAIN 关键字
#Explain不会真的执行sql，只是告诉你"自己将会按照这个方式执行sql"，让你做参考优化的
EXPLAIN select * from test_table

#当前的MySQL版本是否支持show profile
show variables like 'profiling';
#开启
show variables like 'profiling%';

总结：