Mysql第六天查询 1

清疚 2022-08-02 06:37 177阅读 0赞

查询的一般流程是 客户端到服务器，这之间有网络。 在服务器上进行解析，生成执行计划，执行。并且返回给客户端。 执行中又会包含大量的调用存储引擎检索数据，以及检索后的处理比如排序等。  
总体来说，时间一般花费在网络、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;

*  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（）代替关联查询，本身会比关联查询更高效
 *  在应用层可以重用第一次的查询结果，比如做缓存。

### 查询执行基础 ###

![这里写图片描述][20150714180115241]

*  客户端发送请求给服务器
 *  服务器先查询缓存，如果命中直接返回
 *  如果没命中进行解析及预处理，再由优化器生成执行计划
 *  根据执行计划调用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;

另外上面说的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语句转化为执行树，是一颗左侧深度优先的数，如下图：  
![这里写图片描述][20150716103542749]

***关联查询优化***  
主要是对于内联的操作。因为有很多情况内联的表的顺序不重要，因此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端调用的时候可以设置，是否增量接收。

[20150714180115241]: /images/20220731/a681804034c54f429c25dc932727370b.png
[20150716103542749]: /images/20220731/f8a720fce7da45429c851cce39cee61b.png