我的编程空间,编程开发者的网络收藏夹
学习永远不晚

MYSQL(四)查询性能优化

短信预约 -IT技能 免费直播动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号立即预约

请填写图片验证码后获取短信验证码

看不清楚,换张图片

免费获取短信验证码

MYSQL(四)查询性能优化

优化数据访问

1.是否向数据库请求了不需要的数据

解决方式:

A. 查询后加limit

B. Select后写需要的列而不是*

2. 是否扫描了额外的数据

数据库的访问方式速度由慢到快:全表扫描,索引扫描,范围扫描,唯一索引查询,常数引用

MYSQL Explain命令 的type(数据库引擎访问表的方式):Const > ref > range > index > all

1. const 常数引用

如果是根据主键查询,将会将查询转化为一个常数,只取出确定的一行数据。是最快的一种。

2. Ref

查找条件列使用了索引而且不为主键和unique(值允许重复),只取出确定值的数据,可能多行。

3. ref_eq 唯一索引查询

ref_eq 与 ref相比,这种类型的查找结果集只有一个

4. range 范围扫描

索引或主键,在某个范围内时

4. index 索引扫描

仅仅只有索引被扫描

5. all 全表扫描


一般mysql应用where条件的方式由好到坏:

1. 在索引中使用where条件过滤,这是在存储引擎层完成;

2. 使用索引覆盖扫描,直接从索引中过滤不需要的数据并返回结果,这是在mysql服务器层完成,无需再回表查询(在extra中出现using index)

3. 从数据表中返回数据,然后过滤不满足条件的数据,在服务器层完成,mysql需要先从数据表读出记录然后过滤(在extra中出现using where)

好的索引可以让查询使用合适的访问类型,减少扫描的数据行数。


执行查询的基础:

    1. 客户端发送一条查询给服务器

    2. 服务器先检查缓存,如果命中缓存,立刻返回结果

    3. 服务器进行sql解析,预处理,再由优化器生成对应执行计划

    4. Mysql根据优化器生成的执行计划,调用存储引擎API执行查询计划

    5. 将结果返回给客户端


第一步(客户端发送一条查询给服务器):

Mysql客户端与服务器之间的通信是半双工的,要么由服务器向客户端发送数据,要么由客户端向服务器发送数据,不能同时进行;

所以为了进行流量控制,客户端发送查询语句过长时,超过max_allowed_packet参数,服务器会抛出相应错误。

客户端从服务器获取数据时,多数连接mysql的库函数都可以获得全部结果集并缓存到内存里,mysql需要等所有数据都发给客户端才能释放这条查询所占用的资源;

第三步(服务器进行sql解析、预处理、查询优化):

首先,通过关键字将sql语句进行解析,生成一颗“解析树”;

解析器验证语法规则;

预处理器检查解析树是否合法,验证权限;

查询优化器使用优化策略生成一个最优的执行计划:

    1. 重新定义关联表的顺序

    2. 将外连接转化为内连接

    3. 优化count(),min(),max()(根据b-tree只读取第一条或最后一条数据)

    4. 预估并转化为常数表达式

    5. 提前终止查询

    6. 列表in()的比较(将in列表的数据先排序,通过二分查找确定值是否满足条件)

生成一个执行计划——指令树:因为mysql的关联从一张表开始嵌套,所以执行计划是一颗左侧深度优先的树。

第四步(调用存储引擎API执行查询计划)

查询优化器在服务器层,而统计信息(每个表或索引有多少页,每个表的每个索引的基数是多少,数据行和索引长度,索引的分布信息等)在存储引擎层;

MYSQL执行关联查询方式:

Mysql认为任何一次查询都是一次关联,并不仅仅一次查询关系到两张表时。

在MySQL 中,只有一种 Join 算法,就是 Nested Loop Join嵌套迭代。

 MYSQL(四)查询性能优化

Simple Nested-Loop Join简单嵌套循环:从驱动表中取出R1匹配S表所有列,然后R2,R3,直到将R表中的所有数据匹配完,然后合并数据,可以看到这种算法要对S表进行RN次访问,虽然简单,但是相对来说开销还是太大了。

Index Nested-Loop Join索引嵌套循环:由于非驱动表上有索引,所以比较的时候不再需要一条条记录进行比较,而可以通过索引来减少比较,从而加速查询。

优化:

选择记录数少的作为驱动表;

优先优化NestedLoop的内层循环;

保证被驱动表上Join条件字段已经被索引


Mysql查询优化器的局限性

1.关联子查询

使用in加子查询,性能非常糟糕

//未完

2. 最大值和最小值

对于max()和min()查询,mysql的优化并不好,如:

Select min(actor_id) from sakila.actor where first_name = “pene”;

因为first_name字段上没有索引,所以mysql会进行一次全表扫描;

一个优化办法是:(使mysql进行主键扫描)

select actor_id from sakila.actor use index(primary) where first_name = “pene” limit 1;

用主建索引查询,因为b-tree是按照主键顺序排序,所以limit 1 = min(actor_id),查找索引直到复合where条件的第一条数据


免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

MYSQL(四)查询性能优化

下载Word文档到电脑,方便收藏和打印~

下载Word文档

猜你喜欢

mysql-查询性能优化

1、不要取出全部列,取出全部列,会让优化器无法完成索引覆盖扫描这类优化,还会为服务器带来额外的I/O、内存和CPU的消耗。应该严格禁止SELECT * 的写法。MySQL使用如下三种方式应用WHERE条件,从好到坏依次为:  1.1 在索引中使用WHERE条件
mysql-查询性能优化
2021-02-18

SQL查询优化-MySQL 性能调优

  在进行库表结构设计时,我们要考虑到以后的查询要如何的使用这些表,同样,编写 SQL 语句的时候也要考虑到如何使用到目前已经存在的索引,或是如何增加新的索引才能提高查询的性能。  想要对存在性能问题的查询进行优化,需要能够找到这些查询,下面先看下如何获取有性能问题的SQL。  如何设计最优的数据库表结构,如何建立最好
SQL查询优化-MySQL 性能调优
2024-04-18

如何优化MySQL的查询性能?

如何优化MySQL的查询性能?MySQL是一款广泛应用于Web开发的关系型数据库管理系统。然而,在处理大量数据和复杂查询时,MySQL的查询性能可能会受到影响,从而导致应用程序的响应时间变慢。为了提高MySQL的查询性能,我们可以采取以下几
2023-10-22

MySQL查询性能优化索引下推

目录前言1. 索引下推的作用2. 案例实践3. 索引下推配置4. 索引下推原理剖析5. 索引下推应用范围前言前面已经讲了mysql的其他查询性能优化方式,没看过可以去了解一下:MySQL查询性能优化七种方式索引潜水MySQL查询性能优
2022-08-16

MySQL高性能索引策略和查询性能优化

前缀索引和索引选择性有时候需要索引很长的字符,这会让索引变得大且慢。一个策略是模拟哈希索引。通常可以索引开始的部分字符,这样可以大大解约索引空间,提高索引效率。但这样会降低索引的选择性。索引的选择性:不重复的索引值(也成为基数)和数据表的记录总数比值。索引的选
MySQL高性能索引策略和查询性能优化
2017-05-17

MySQL 索引优化与查询性能提升

MySQL 索引的优化是提升数据库查询性能的重要手段。通过合理设计索引、优化查询条件以及采取适当的优化策略,可以显著提高 MySQL 数据库的查询效率和整体性能。

MySQL数据库查询性能优化策略

优化查询使用Explain语句分析查询语句 Explain 用来分析 SELECT 查询语句,开发人员可以通过分析 Explain 结果来优化查询语句。 通过对查询语句的分析,可以了解查询语句的执行情况,找出查询语句执行的瓶颈,从而优化查询
2022-05-25

如何优化 PHP 中的 MySQL 查询性能?

可以通过以下方式优化 mysql 查询性能:建立索引,将查找时间从线性复杂度降至对数复杂度。使用 prepared statements,防止 sql 注入并提高查询性能。限制查询结果,减少服务器处理的数据量。优化连接查询,包括使用适当的连
如何优化 PHP 中的 MySQL 查询性能?
2024-05-11

MySQL查询性能优化的实战技巧

MySQL查询性能优化是一个复杂的过程,涉及到多个方面。以下是一些实战技巧,可以帮助你提高MySQL查询的性能:使用索引:索引是提高查询性能的关键。确保在经常用于查询的列上创建索引,并且索引应该是有意义的,即索引列应该是有序的。同时,要注意
MySQL查询性能优化的实战技巧
2024-10-21

mysql查询性能优化的方法是什么

MySQL查询性能优化的方法包括以下几种:创建合适的索引:通过在查询中使用索引,可以大大提高查询的性能。确保在查询中使用最适合的索引类型,避免全表扫描。优化查询语句:避免使用SELECT *,只选择需要的列;使用JOIN语句代替子查询;避免
mysql查询性能优化的方法是什么
2024-05-14

mysql优化——查询优化

这一篇mysql优化是注重于查询优化,根据mysql的执行情况,判断mysql什么时候需要优化,关于数据库开始阶段的数据库逻辑、物理结构的设计结构优化不是本文重点,下次再谈。 查看mysql语句的执行情况,判断是否需要进行优化 当感觉操作数据库查询语句速度
mysql优化——查询优化
2016-09-05

mysql查询优化

select * from a where id in (select id from b)等价于:for select id from bfor select 8 from a where a.id = b.id当b表数据必须小于a表数据时,in优于exis
mysql查询优化
2022-02-02

MySQL查询性能优化武器之链路追踪

目录前言1. 查看optimizer trace配置2. 开启optimizer trace3. 线上问题复现3. 使用optimizer trace前言mysql优化器可以生成Explain执行计划,我们可以通过执行计划查看是否使用了索
2022-08-08

编程热搜

目录