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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 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
目录
相关文章
|
7天前
|
SQL 关系型数据库 MySQL
MySQL慢查询优化、索引优化、以及表等优化详解
本文详细介绍了MySQL优化方案,包括索引优化、SQL慢查询优化和数据库表优化,帮助提升数据库性能。关注【mikechen的互联网架构】,10年+BAT架构经验倾囊相授。
MySQL慢查询优化、索引优化、以及表等优化详解
|
12天前
|
缓存 监控 关系型数据库
如何优化MySQL查询速度?
如何优化MySQL查询速度?【10月更文挑战第31天】
39 3
|
14天前
|
缓存 关系型数据库 MySQL
如何优化 MySQL 数据库的性能?
【10月更文挑战第28天】
38 1
|
22天前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:百万级数据统计优化实践
【10月更文挑战第21天】 在处理大规模数据集时,传统的单体数据库解决方案往往力不从心。MySQL和Redis的组合提供了一种高效的解决方案,通过将数据库操作与高速缓存相结合,可以显著提升数据处理的性能。本文将分享一次实际的优化案例,探讨如何利用MySQL和Redis共同实现百万级数据统计的优化。
57 9
|
16天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
82 1
|
22天前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:优化百万数据查询的实战经验
【10月更文挑战第13天】 在处理大规模数据集时,传统的关系型数据库如MySQL可能会遇到性能瓶颈。为了提升数据处理的效率,我们可以结合使用MySQL和Redis,利用两者的优势来优化数据查询。本文将分享一次实战经验,探讨如何通过MySQL与Redis的协同工作来优化百万级数据统计。
48 5
|
27天前
|
存储 关系型数据库 MySQL
优化 MySQL 的锁机制以提高并发性能
【10月更文挑战第16天】优化 MySQL 锁机制需要综合考虑多个因素,根据具体的应用场景和需求进行针对性的调整。通过不断地优化和改进,可以提高数据库的并发性能,提升系统的整体效率。
49 1
|
27天前
|
缓存 关系型数据库 MySQL
一文彻底弄懂MySQL优化之深度分页
【10月更文挑战第24天】本文深入探讨了 MySQL 深度分页的原理、常见问题及优化策略。首先解释了深度分页的概念及其带来的性能和资源问题。接着介绍了基于偏移量(OFFSET)和限制(LIMIT)以及基于游标的分页方法,并分析了它们的优缺点。最后,提出了多种优化策略,包括合理创建索引、优化查询语句和使用数据缓存,帮助提升分页查询的性能和系统稳定性。
|
17天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件,其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引(如B-Tree、复合索引)和优化查询语句(如使用EXPLAIN、优化分页查询),可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志,持续优化性能。
47 0
|
26天前
|
存储 监控 关系型数据库
MySQL并发控制与管理:优化数据库性能的关键
【10月更文挑战第17天】MySQL并发控制与管理:优化数据库性能的关键
109 0