Mysql第六天 查询 1

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: <div class="markdown_views"><p>查询的一般流程是 客户端到服务器,这之间有网络。 在服务器上进行解析,生成执行计划,执行。并且返回给客户端。 执行中又会包含大量的调用存储引擎检索数据,以及检索后的处理比如排序等。 <br>总体来说,时间一般花费在网络、CPU计算、生成统计信息、执行计划,锁等待,内存不足时的I/O操作等等。</p><p>先

查询的一般流程是 客户端到服务器,这之间有网络。 在服务器上进行解析,生成执行计划,执行。并且返回给客户端。 执行中又会包含大量的调用存储引擎检索数据,以及检索后的处理比如排序等。
总体来说,时间一般花费在网络、CPU计算、生成统计信息、执行计划,锁等待,内存不足时的I/O操作等等。

先说两个用于查看性能指标的sql.

SELECT @@profiling;
SET profiling = 1;
select count(*) from test;
show profiles;
// 来查看语句的执行时间,这个能够最直观的看到sql的快慢

第二个语句是:

explain select * from biz_pay_task where jd_order_id=42596246804;

其结果如下:
+—-+————-+————–+——+—————+—————+———+——-+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+————–+——+—————+—————+———+——-+——+——-+
| 1 | SIMPLE | biz_pay_task | ref | i_jd_order_id | i_jd_order_id | 8 | const | 1 | |
+—-+————-+————–+——+—————+—————+———+——-+——+——-+
主要是select_type 、key 、rows、type 这几个选项

  • key 使用了哪个索引
  • rows mysql评估的可能需要检索的数量
  • type对应了查询所使用的类型,比如All代表全表扫描,ref代表索引扫描,还会有范围扫描、唯一索引扫描等等。最好都能够达到ref的级别。

通常可以从如下几个方面进行考虑

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

  • 没有使用LIMIT,而是查处了大量的数据,只是用了前几行
  • 多表关联时返回全部列,这样会有很多重复列,最好明确指定
  • 默认使用select * 尽量只返回需要的列

是否扫描了额外的记录

这个不是很好确定,通常加上合适的索引之后就能够减少扫描的数量,但是对于分组统计类的sql却不能使用索引的方式了。一般我们可以有如下的方式优化:

  • 使用覆盖索引
  • 该表库表结构,使用单独的汇总表
  • 重写复杂查询,让Mysql优化器能够以更好的方式执行这个查询

重构查询方式

一个复杂查询还是多个简单查询

这个冲突放大了就是多个简单sql语句,然后在代码里计算,还是用存储过程把所有计算都完成。
以前认为数据库查询计算很快,而跟客户端之间的通信的开销是很大的。但是现在可能会越来越考虑可用性,重用性等,一个复杂查询变得不那么重要了。

切分查询

每个sql的功能完全一样,但是只是完成一小部分。
最经典的使用方式是分页,也就是使用limit关键字, 可以分页查或者是分页删除。
特别是删除,因为会占用事务日志和锁,因此更有必要使用分页。我们可以用下面的伪代码来表示分页删除:

rows_affected = 0;
do{
    rows_affected = do_query(DELETE FROM test LIMIT 10000)
} while rows_affected > 0;

分解关联查询

把join分解为多个查询,比如:

select * from Student s JOIN grade g on s.gradeId = g.id where s.name="张三";
// 可以改写为:
SELECT * FROM Student s where s.name="张三";
SELECT * FROM Grade g where g.id in (#上面查出的结果#);

这样看起来一模一样,并且还会增加连接次数。但是却能带来如下的好处:

  • 让缓存效率更高。 如果第一个查询已经执行过,那么就可以跳过。
  • 减少锁竞争
  • 在应用层做关联,可以对数据库进行拆分,获得更好的扩展性
  • 使用IN()代替关联查询,本身会比关联查询更高效
  • 在应用层可以重用第一次的查询结果,比如做缓存。

查询执行基础

这里写图片描述

  • 客户端发送请求给服务器
  • 服务器先查询缓存,如果命中直接返回
  • 如果没命中进行解析及预处理,再由优化器生成执行计划
  • 根据执行计划调用API来执行查询
  • 将结果返回给客户端

Mysql 客户端与服务器端的通信

半双工的通信方式,决定了不能限制流量,发出请求后只能等待结果。
下面的参数能够设置接收包的大小,太小了,可能导致请求失败

show VARIABLES like '%max_allowed_packet%';
//my.cnf
max_allowed_packet = 20M
// 命令
set global max_allowed_packet = 2*1024*1024*10

通常使用mysql的客户端包,都是从mysql服务器中获取了sql中返回的所有数据,并且缓存,之后操作的都是缓存中的数据。 这样有个问题是如果结果集过大有可能内存溢出。
JDBC可以用如下的办法来不使用这种返回的方式:

    stmt = (com.mysql.jdbc.Statement) con.createStatement();
    stmt.setFetchSize(1);
    // 打开流方式返回机制
    stmt.enableStreamingResults();
    // 类似利用mysql机制的方法还有:setLocalInfileInputStream ,可以跟LOAD DATA LOCAL INFILE一起快速插入

连接状态

SHOW FULL PROCESSLIST;

+———+——+———————-+——————–+———+——+——-+———————–+
| Id | User | Host | db | Command | Time | State | Info |
+———+——+———————-+——————–+———+——+——-+———————–+
| 1897957 | root | 192.168.147.34:60520 | biz | Sleep | 172 | | NULL |
通过State能够看到连接线程的状态。
Sleep是线程等待客户端发送新请求
Query是正在查询,等等。

查询缓存

是通过大小写敏感的 Hash查找来实现的,因此只要sql改动了一点就不能命中了

词法分析与预处理

分析称解析树, 其中如果存在语法错误则直接返回

查询优化器

生成执行计划。
每个sql语句都可能有多种执行计划,mysql使用预判的方式来估算最小成本的计划。下面的语句可以看一下mysql的估算结果:

SELECT SQL_NO_CACHE COUNT(*) FROM biz_pay_task;
SHOW STATUS LIKE 'Last_query_cost';

返回的是mysql认为的要做多少个页的随机查找才能完成任务。

mysql通常有如下的优化方式:

  • 重定义关联表顺序
  • 等价变换规则: 移除恒等,合并比较等等,比如 1=1 AND a > 5会转化为 a > 5
  • 优化COUNT(), MIN(), MAX()
    MIN(),MAX()分别对应B-Tree的索引最前与最后,基本相当于常量引用的效率了
EXPLAIN select MAX(jd_order_id) FROM biz_pay_task;
// 结果: Extra : Select tables optimized away

表示启用了此项优化。
COUNT(),需要存储引擎支持,比如有的存储引擎可以直接返回这个变量,不用去数

  • 预估并转化为常量表达式
// 查出一个订单的扩展字段之:第三方订单号。
EXPLAIN SELECT b.id, o.third_order_id FROM biz_pay_task b INNER JOIN order_snap o ON o.virtual_order_id = b.id WHERE b.id = 1;

结果:
+—-+————-+——-+——-+—————+———+———+——-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+———+———+——-+——+————-+
| 1 | SIMPLE | b | const | PRIMARY | PRIMARY | 8 | const | 1 | Using index |
| 1 | SIMPLE | o | ALL | NULL | NULL | NULL | NULL | 61 | Using where |
+—-+————-+——-+——-+—————+———+———+——-+——+————-+
可以看到是转化为两次查询,第一次是主键查询,第二次是一个where 查询,第二个where查询的时候会直接使用o.virtual_order_id=1来进行替换。使用常量值。

另外上面说的MIN()的情况也应该属于这一种

  • 覆盖索引扫描
  • 提前终止查询
    比如Limit,到指定的位置就不往下查找了
    比如下旬一个不存在的数据,从索引上就直接返回了,不会去查数据
EXPLAIN SELECT b.id
 FROM biz_pay_task b WHERE b.id = -1;
 // Extra是:Impossible WHERE noticed after reading const tables 

比如NOT EXIST, LEFT JOIN, lift join再来个例子:
查询很有赠品的订单
SELECT b.order_id FROM order LEFT JOIN order_sku o ON order.order_id = o.order_id WHERE o.skuName IS NULL;
这个查询会找到第一个skuName 为NULL之后进入下一个订单,而不会全部扫描。

其实跟Java的return, break; continue这种语法有点像。

  • IN mysql会对其内容进行排序,使用二分查找的方式,这样比其他的数据库要好,其他基本上都是跟多个OR是等价的。

关联查询

Mysql中对于关联查询的操作很简单,就是嵌套循环。即先遍历左边中符合条件的,然后根据每一个左表符合条件的去查右表中的内容。

包括子查询 也是使用的这种方式。

Mysql在执行时会把sql语句转化为执行树,是一颗左侧深度优先的数,如下图:
这里写图片描述

关联查询优化
主要是对于内联的操作。因为有很多情况内联的表的顺序不重要,因此mysql可能会改变遍历顺序优先遍历数据很少的表。
举个栗子:

EXPLAIN SELECT v.id, o.third_order_id FROM virtual_order v
 INNER JOIN order_snap o  ON v.id = o.virtual_order_id;
// 这个sql跟 order_snap o INNER JOIN virtual_order v的效果是一样的。执行的时候能够看到:

+—-+————-+——-+——–+—————+———+———+——————————–+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——–+—————+———+———+——————————–+——+————-+
| 1 | SIMPLE | o | ALL | NULL | NULL | NULL | NULL | 344 | |
| 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 8 | virtual_biz.o.virtual_order_id | 1 | Using index |
+—-+————-+——-+——–+—————+———+———+——————————–+——+————-+
能够看到先执行的o表, 因为mysql的优化点在于选择驱动表,其会简单的选择数据少的为驱动表。

但是如果是很多表关联的情况下, 因为组合会很多所以有可能会转为其他的方式进行选择。通常不建议关联很多表

此外我们可以指定连接的顺序,选择驱动表。 使用STRAIGHT JOIN 关键字。 这样我们可以保证驱动表是我们想要的,比如我们要尽量使得排序行为在驱动表中,这样就会使查询更快。

排序优化

基于索引排序, 使用快排,如果内存不够则先对数据分块,然后每块分别排序,最后merge.

此外有两种排序算法
当不超过max_length_for_sort_data时,使用单次传输,否则时两次传输。
单次传输时新版本才有的,会加载所有的列进行排序,这样减少I/O,增加占用内存
两次传输,第一次加载排序列,排序,排好后再去拿其他数据。这样减少占用空间,但是会增加很多随机I/O。

当关联查询需要排序时,如果在驱动表上,则会先排序。
不在则会先计算关联结果,然后放到临时表中,再进行排序。

查询执行引擎

生成的执行计划是一个数据结构。
执行过程会通过api调用很多次存储引擎。

返回结果给客户端

返回结果集时会判断能否缓存,如果可以会先缓存。
结果是增量返回的,因此在API端调用的时候可以设置,是否增量接收。

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
相关文章
|
19天前
|
SQL 缓存 监控
MySQL缓存机制:查询缓存与缓冲池优化
MySQL缓存机制是提升数据库性能的关键。本文深入解析了MySQL的缓存体系,包括已弃用的查询缓存和核心的InnoDB缓冲池,帮助理解缓存优化原理。通过合理配置,可显著提升数据库性能,甚至达到10倍以上的效果。
|
21天前
|
SQL 存储 关系型数据库
MySQL体系结构详解:一条SQL查询的旅程
本文深入解析MySQL内部架构,从SQL查询的执行流程到性能优化技巧,涵盖连接建立、查询处理、执行阶段及存储引擎工作机制,帮助开发者理解MySQL运行原理并提升数据库性能。
|
2月前
|
SQL 人工智能 关系型数据库
如何实现MySQL百万级数据的查询?
本文探讨了在MySQL中对百万级数据进行排序分页查询的优化策略。面对五百万条数据,传统的浅分页和深分页查询效率较低,尤其深分页因偏移量大导致性能显著下降。通过为排序字段添加索引、使用联合索引、手动回表等方法,有效提升了查询速度。最终建议根据业务需求选择合适方案:浅分页可加单列索引,深分页推荐联合索引或子查询优化,同时结合前端传递最后一条数据ID的方式实现高效翻页。
133 0
|
3天前
|
SQL 关系型数据库 MySQL
MySQL的查询操作语法要点
以上概述了MySQL 中常见且重要 的几种 SQL 查询及其相关概念 这些知识点对任何希望有效利用 MySQL 进行数据库管理工作者都至关重要
39 15
|
20天前
|
SQL 监控 关系型数据库
SQL优化技巧:让MySQL查询快人一步
本文深入解析了MySQL查询优化的核心技巧,涵盖索引设计、查询重写、分页优化、批量操作、数据类型优化及性能监控等方面,帮助开发者显著提升数据库性能,解决慢查询问题,适用于高并发与大数据场景。
|
21天前
|
SQL 关系型数据库 MySQL
MySQL入门指南:从安装到第一个查询
本文为MySQL数据库入门指南,内容涵盖从安装配置到基础操作与SQL语法的详细教程。文章首先介绍在Windows、macOS和Linux系统中安装MySQL的步骤,并指导进行初始配置和安全设置。随后讲解数据库和表的创建与管理,包括表结构设计、字段定义和约束设置。接着系统介绍SQL语句的基本操作,如插入、查询、更新和删除数据。此外,文章还涉及高级查询技巧,包括多表连接、聚合函数和子查询的应用。通过实战案例,帮助读者掌握复杂查询与数据修改。最后附有常见问题解答和实用技巧,如数据导入导出和常用函数使用。适合初学者快速入门MySQL数据库,助力数据库技能提升。
|
29天前
|
存储 关系型数据库 MySQL
使用命令行cmd查询MySQL表结构信息技巧分享。
掌握了这些命令和技巧,您就能快速并有效地从命令行中查询MySQL表的结构信息,进而支持数据库维护、架构审查和优化等工作。
167 9
|
21天前
|
SQL 监控 关系型数据库
MySQL高级查询技巧:子查询、联接与集合操作
本文深入解析了MySQL高级查询的核心技术,包括子查询、联接和集合操作,通过实际业务场景展示了其语法、性能差异和适用场景,并提供大量可复用的代码示例,助你从SQL新手进阶为数据操作高手。
|
10月前
|
缓存 关系型数据库 MySQL
MySQL索引策略与查询性能调优实战
在实际应用中,需要根据具体的业务需求和查询模式,综合运用索引策略和查询性能调优方法,不断地测试和优化,以提高MySQL数据库的查询性能。
566 66
|
2月前
|
人工智能 Java 关系型数据库
Java的时间处理与Mysql的时间查询
本文总结了Java中时间与日历的常用操作,包括时间的转换、格式化、日期加减及比较,并介绍了MySQL中按天、周、月、季度和年进行时间范围查询的方法,适用于日常开发中的时间处理需求。

推荐镜像

更多