Mysql优化-全面详解(学习总结---从入门到深化)

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: 在程序的运行过程中,我们会发现这样的一个现象,随着程序运行 时间的不断推移以及数据量越来越大,程序响应的时间逐渐变慢, 程序变得卡顿,但最开始的时候并不是这样的,那是什么原因导致 的呢?

Sql性能下降的原因

2345_image_file_copy_587.jpg

在程序的运行过程中,我们会发现这样的一个现象,随着程序运行 时间的不断推移以及数据量越来越大,程序响应的时间逐渐变慢, 程序变得卡顿,但最开始的时候并不是这样的,那是什么原因导致 的呢?

性能下降的原因

2345_image_file_copy_588.jpg

sql性能下降的原因有哪些?

1、索引失效

2、服务器性能不足

3、sql编写不合理

Sql读取顺序

2345_image_file_copy_589.jpg

我们在编写sql代码时的顺序和Mysql内部读取sql时的顺序是不一样 的,因此有必要搞清楚Mysql读取sql语句的顺序。

2345_image_file_copy_590.jpg

整体过程

先对多表进行关联,根据条件找出符合的记录

在符合的记录基础上进行where条件过滤

对筛选出的记录进行分组操作

分组完成后再进行having操作,过滤出满足条件的数据

对取出的记录进行排序

再按照分页条件取出要显示的数据

1.下面关于sql读取顺序正确的是?from->where->group by->select->order by->limit

explain执行计划

2345_image_file_copy_591.jpg

Mysql 使用 explain 关键字可以模拟优化器执行 sql 语句,我们就 能够知道 Mysql 会如何处理sql,可以根据 explain 的分析结果和 Mysql 底层数据结构优化 sql。不同Mysql 版本可能有差别,但差别 不会很大。

执行示例:

EXPLAIN SELECT hco.co_id,hco.patient_name FROM
his_care_order hco LEFT JOIN
his_care_order_item hcoi ON hco.co_id =
hcoi.co_id

2345_image_file_copy_592.jpg

2345_image_file_copy_593.jpg

1、 id:select查询的序号,表示执行select操作时表的顺序。 id相同按顺序走

EXPLAIN SELECT * FROM `his_care_order` o
,`his_care_order_item` i WHERE
o.co_id=i.co_id

id不同,序号大的先执行

EXPLAIN SELECT * FROM `his_care_order` o
WHERE co_id=(SELECT co_id FROM
(SELECT co_id FROM `his_care_order_item`
WHERE item_id='ITEM1465223982444838912')T )

id相同,不同同时存在

EXPLAIN SELECT * FROM sys_dept d,(SELECT
dept_id FROM sys_user GROUP BY dept_id)t
WHERE t.dept_id=d.dept_id

2、select_type

查询类型,主要用于区别普通查询、联合查询、子查询 simple:简单select查询,查询中不包含子查询或union;

SELECT * FROM `his_care_order`

PRIMARY:主键查询

SELECT * FROM `his_care_order`
WHERE co_id=(SELECT co_id FROM
`his_care_order_item` WHERE
item_id='ITEM1465859053631700992')

SUBQUERY:where条件包含了子查询

EXPLAIN SELECT * FROM `his_care_order`
WHERE co_id=(SELECT co_id FROM
`his_care_order_item` WHERE
item_id='ITEM1465859053631700992')

DERIVED:from的表中包含子查询,被标记为derived(衍 生),把子查询的结果放在临时表中

SELECT * FROM `his_care_order` a,(SELECT
co_id FROM `his_care_order_item`  GROUP BY
co_id)b
WHERE a.co_id=b.co_id

1、table

显示这一行的数据是来自于哪张表的

2、partitions

如果查询是基于分区的话,会显示查询访问的分区

3、type

访问类型,按性能从低到高依次排列为

ALL:全表扫描,一定要优化

index:它和All都是扫描全表,但index是从索引中读取表,All 是从硬盘中读取

range:只检索给定范围的行,key列显示使用了哪个索引, between and或in等查询

ref:非唯一性索引扫描,本质上也是一种索引访问

eq-ref:唯一性索引扫描,对于每个索引键只有一条记录与之匹 配

const:通过索引一次就找到了,常见于primary或unique索引 查找

system:表中只有一行记录(系统表),很少出现 NULL:不需要访问表

4、possible_keys:

显示可能应用在这张表中的索引,一个或多 个,查询涉及的字段上若建立了索引则会列出来,但不一定被使用

5、key:

它和possible_keys的关系,理论上应该用到哪些索引,实 际上用到了哪些索引

6、key_len:

索引使用的字节数,key_len显示的值为索引字段的最 大可能长度

7、ref:

索引是否被引用到,用到了哪几个索引

8、rows:

根据表统计信息及索引使用情况,估算所需读取的记录 行数

9、filtered:

满足查询条件记录数量的比例,是百分比,不是具体 的记录数,这个值越大越好,它依赖于统计信息,并不是很准确

10、Extra:

Using filesort(文件排序,表示mysql无法利用索引完 成排序操作)

Using temporary(使用了临时表保存中间结果,常见 于order by和group by)

Using index(如果同时出现Using where,表明索引用 来执行索引键值的查 找,如果没有出现Using where,表 明索引用来读取数据而非执行查找)

Using where(使用了where过滤)

Using join buffer(使用了连接缓存)

impossible where(where子句的值总是false,不能用 来获取记录)

1.下面关于explain命令中id列的说法错误的是? id列是按正序排列的

2.下面关于explain命令中type列的说法正确的是?

2.1 type列包含ALL, index, range, ref, eq_ref, const, system, NULL等多种类型

2.2 按性能,All最差,NULL最高

2.3 在实际开发中至少要达到range



相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
4天前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:百万级数据统计优化实践
【10月更文挑战第21天】 在处理大规模数据集时,传统的单体数据库解决方案往往力不从心。MySQL和Redis的组合提供了一种高效的解决方案,通过将数据库操作与高速缓存相结合,可以显著提升数据处理的性能。本文将分享一次实际的优化案例,探讨如何利用MySQL和Redis共同实现百万级数据统计的优化。
29 9
|
4天前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:优化百万数据查询的实战经验
【10月更文挑战第13天】 在处理大规模数据集时,传统的关系型数据库如MySQL可能会遇到性能瓶颈。为了提升数据处理的效率,我们可以结合使用MySQL和Redis,利用两者的优势来优化数据查询。本文将分享一次实战经验,探讨如何通过MySQL与Redis的协同工作来优化百万级数据统计。
21 5
|
9天前
|
存储 关系型数据库 MySQL
优化 MySQL 的锁机制以提高并发性能
【10月更文挑战第16天】优化 MySQL 锁机制需要综合考虑多个因素,根据具体的应用场景和需求进行针对性的调整。通过不断地优化和改进,可以提高数据库的并发性能,提升系统的整体效率。
15 1
|
9天前
|
缓存 关系型数据库 MySQL
一文彻底弄懂MySQL优化之深度分页
【10月更文挑战第24天】本文深入探讨了 MySQL 深度分页的原理、常见问题及优化策略。首先解释了深度分页的概念及其带来的性能和资源问题。接着介绍了基于偏移量(OFFSET)和限制(LIMIT)以及基于游标的分页方法,并分析了它们的优缺点。最后,提出了多种优化策略,包括合理创建索引、优化查询语句和使用数据缓存,帮助提升分页查询的性能和系统稳定性。
|
12天前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:百万数据量的优化实录
【10月更文挑战第6天】 在现代互联网应用中,随着用户量的增加和业务逻辑的复杂化,数据量级迅速增长,这对后端数据库系统提出了严峻的挑战。尤其是当数据量达到百万级别时,传统的数据库解决方案往往会遇到性能瓶颈。本文将分享一次使用MySQL与Redis协同优化大规模数据统计的实战经验。
55 3
|
12天前
|
NoSQL 关系型数据库 BI
记录一次MySQL+Redis实现优化百万数据统计的方式
【10月更文挑战第13天】 在处理百万级数据的统计时,传统的单体数据库往往力不从心,这时结合使用MySQL和Redis可以显著提升性能。以下是一次实际优化案例的详细记录。
66 1
|
16天前
|
关系型数据库 MySQL Java
Django学习二:配置mysql,创建model实例,自动创建数据库表,对mysql数据库表已经创建好的进行直接操作和实验。
这篇文章是关于如何使用Django框架配置MySQL数据库,创建模型实例,并自动或手动创建数据库表,以及对这些表进行操作的详细教程。
47 0
Django学习二:配置mysql,创建model实例,自动创建数据库表,对mysql数据库表已经创建好的进行直接操作和实验。
|
17天前
|
Java 关系型数据库 MySQL
springboot学习五:springboot整合Mybatis 连接 mysql数据库
这篇文章是关于如何使用Spring Boot整合MyBatis来连接MySQL数据库,并进行基本的增删改查操作的教程。
28 0
springboot学习五:springboot整合Mybatis 连接 mysql数据库
|
17天前
|
Java 关系型数据库 MySQL
springboot学习四:springboot链接mysql数据库,使用JdbcTemplate 操作mysql
这篇文章是关于如何使用Spring Boot框架通过JdbcTemplate操作MySQL数据库的教程。
17 0
springboot学习四:springboot链接mysql数据库,使用JdbcTemplate 操作mysql
|
8天前
|
存储 监控 关系型数据库
MySQL并发控制与管理:优化数据库性能的关键
【10月更文挑战第17天】MySQL并发控制与管理:优化数据库性能的关键
25 0