Mysql第七天查询优化2

骑猪看日落 2022-08-02 09:52 175阅读 0赞

接上文，其他的一些Mysql对于查询的优化，或者Mysql的不足，我们应该注意怎样优化。

### 关联子查询 ###

Mysql的子查询实现比较有问题， 特别是对于IN(子查询)，这样的方式。  
比如:查询一个订单表中，所有支付方式为4也就是京东网银钱包的订单：

EXPLAIN SELECT * FROM virtual_order vo WHERE vo.jd_order_id IN (SELECT jd_order_id FROM biz_pay_task p WHERE p.pay_type=4);

SELECT * FROM virtual_order vo WHERE EXISTS (SELECT jd_order_id FROM biz_pay_task p WHERE p.pay_type=4 AND vo.jd_order_id=p.jd_order_id);

这意味着，先对vo进行了全表扫描，然后再对每一个jd\_order\_id去执行子查询，看是否返回true  
EXISTS关键字决定了 其子查询是否能够有返回结果。

这种情况下，我们期望怎样执行呢？当然是先执行子查询，有结果了之后利用IN的二分查找来加快检索速度。  
这样的话，我们就可以使用GROUP\_CONCAT()来手工拼一个IN里面的内容，  
或者改写为如下的内连接：

SELECT * FROM virtual_order vo INNER JOIN biz_pay_task p USING(jd_order_id) WHERE p.pay_type=4 ;

但是关联子查询并不是一定比左连，内联等慢的。  
比如NOT EXISTS的情况下，会有终止返回的优化，还是有可能变得比较快。 针对于这种，最好的方式就是进行测试，得到实际的执行结果，来进行判断。

### UNION ALL的限制 ###

(SQL1) UNION ALL (SQL2) LIMIT 20;  
这种情况会把 SQL1和SQL2中的所有记录放到临时表中然后去LIMIT20.比较好的做法是。分别LIMIT 如下：  
(SQL1 LIMIT 20) UNION ALL (SQL2 LIMIT 20) LIMIT 20;

### 等值传递 ###

当遇到ON, USING的时候会诱发等值传递的优化，但是如果要传递的是IN()，或者其他比较复杂的等值时，有可能传递给内层之后影响内层的性能。

### 不能并行执行 ###

### 没有哈希关联 ###

可以使用自建哈希字段加索引

### 松散索引扫描 ###

也就是跳着扫描。比如有一个聚合索引(a, b)如果WHERE中只有b的条件，那么因为用不到这个索引，就会变成全表扫描。 我们期望的是先从a的索引开始扫描，然后对于a的没一个索引键中因为b是有序的，因此这个时候又可以对于每个键中的b的区间范围进行索引扫描。比如如下的结构：  
|a|b|  
|1|1|  
|1|2|  
|1|3|  
|1|4|  
|2|1|  
|2|2|  
|2|3|  
|2|4|  
WHERE b between 2 and 3 扫描方式变为a1-b2-b3-a2-b2-b3，这样跳着扫描可以减少扫描行数，大幅度增加性能。  
Mysql的主要应用掉在于GROUP BY 的语句中。Extra字段显示”Using index for group-by”  
这里有一个更好的说明： [请点击][Link 1]

### 最大值和最小值优化 ###

SELECT MIN(id) from user WHERE name=”wzj”;  
这里会做全表扫描。 但是如果是从主键开始扫描的话，可以快很多。Mysql自己不能完成这种行为，但是我们可以手工的完成它，比如使用如下的语句：  
SELECT id FROM user USE INDEX(PRIMARY) WHERE name=”wzj” LIMIT 1;

### 查询优化器的提示 hint ###

主要是认为的控制一下Mysql的优化过程，列举一些，更具体的需要看Mysql的官方文档了。

*  DELAYED 插入数据放入缓冲区，提高对客户端的响应
 *  STRAIGHT\_JOIN 这个之前使用过，可以强制指定驱动表
 *  SQL\_SMALL\_RESULT 和SQL\_BIG\_RESULT 告诉优化器对于SELECT GROUPBY这种的返回集的大概大小，如果小则用内存而不用临时表
 *  SQL\_CACHE 告诉优化器这个结果存在缓存中
 *  USE INDEX 这个告诉优化器使用或者不适用哪些索引
 *  还有一些配置项，之前也已经讲过了，有深度太多跳过选择执行计划的等等。  
    另外随着版本的更新可能会引起不一样的行为，因此最好每次升级都进行测试。可以使用Percona Tookit 的pt-upgrade工具来检查结果。

### 优化特定类型的查询 ###

#### 优化COUNT() ####

*  COUNT(fieldname) 统计字段中不为NULL的值
 *  COUNT(\*) 统计所有行，这个的性能要好一些
 *  MYISAM之所以COUNT(\*)快，是因为实时更新了总行数，但是对于加了WHERE的就不快了。
 *  利用MYISAM很快的特性，对于WHERE b>5,可以使用COUNT(\*) - WHERE b < 5的特性，这样可以加快一些性能。
 *  使用估算值，在特定场景下，可以使用去除DISTINCT，或者是WHERE后面对于结果集影响很小的条件，只求出估算值
 *  更复杂的方式可以使用汇总表，索引覆盖，Memcached这样的外部缓存。快速，精确和简单，最多只能满足其二，所以优化肯定会有一个限度

#### 优化关联查询 ####

*  却表ON和USING子句的列上有索引， 创建索引时考虑好关联的顺序，只需要在驱动表上创建索引
 *  确保任何GROUP BY和ORDER BY 表达式只涉及一个表的列，这样才能使用索引
 *  注意不要使用全连，用内联和外联

#### 优化子查询 ####

尽量用关联代替  
Mysql5.6中已经自带了这种优化，因此可以不是很关注了

#### 优化LIMIT分页 ####

*  利用覆盖索引，先查出有索引的列，然后使用连接操作
 *  再有是先记录下当前查找到的最后的位置，然后再下一次查找是上WHERE子句过滤掉这个位置之前的值。比如SELECT \* FROM user ORDER BY id DESC LIMIT 20;  
    这个时候得到最小的id是10000，那么下面就可以使用SELECT \* FROM user WHERE id < 10000 ORDER BY id DESC LIMIT 20;来得到下20行，这样子来提升效率。
 *  其他的方法，还可以使用冗余表，只包含主键和需要排序的数据列。

#### 分页优化 ####

*  SQL\_CALC\_FOUND\_ROWS 这个hint并不能真正的提高效率，其还是要统计所有的行的数量
 *  使用EXPLAIN语句中的rows结果来做估算
 *  读取21条，看是否有第21条来决定是否有下一页
 *  读取1000条，其他的数据使用更多按钮来展示。

#### UNION优化 ####

*  把优化的语句放到各个子查询中
 *  确定只有必须要去除重复行的时候采用UNION,否则使用UNION ALL，免得对所有结果进行DISTINCT
    
    #### 静态查询分析 ####
    
    Percona 的pt-query-advisor

### 例子 ###

#### 构建队列表 ####

mysql并不是最好的选择，一般会有如下两个问题要解决：

*  增长。 这种可以使用归档的方式
 *  并发。 即有可能多个用户操作同一个任务，为了避免种种情况，可以加锁，但是加锁效率比较低。  
    对于增长可以归档，可以分片。  
    对于并发，我们可以使用乐观锁  
    或者使用如下的优化，我们一般基本的做法可以是这个样子的：给任务表增加一个woner字段。 然后执行任务的时候：

BEGIN;
    SELECT id FROM task WHERE owner = 0 AND status=0 LIMIT 10 FOR UPDATE;
    -- result 1,2
    // 代码中dosomething.
    UPDATE task SET status=1 , owner = CONNECTON_ID() WHERE id IN (--result);
    COMMIT;

这样会造成使用相同索引的相同查询有锁定， 可以使用如下的方式代替：

BEGTIN;
    UPDATE task set status=1 , owner=CONNECTION_ID() WHERE owner = 0 AND status=0 LIMIT 10;
    COMMIT;
    SET AUTOCOMMIT = 0;
    SELECT id FROM task where owner = CONNECTION_ID() AND status = 1;
    // do something

这种可以大幅的减少锁的时间，但是后续的处理可能会比较多，比如如果执行具体的事情失败，则还需要回写内容。  
上面的这种也稍显复杂，最好还是使用悲观锁。

或者是直接使用redis，是一个更好的队列容器。

#### 查询两点之间的距离 ####

这个主要是需要注意，什么情况下使用索引。显然转化为矩形的情况下会使用索引。 使用矩形得出晓得数据集之后再进行精确计算。

[Link 1]: http://www.cnmiss.cn/?p=373