高性能可扩展mysql 笔记（六） SQL执行计划及分页查询优化、分区键统计

ゞ浴缸里的玫瑰 2023-06-06 07:59 10阅读 0赞

### 个人博客网：[https://wushaopei.github.io/][https_wushaopei.github.io]    (你想要这里多有) ###

### 常见业务处理 ###

## 一、使用数据库处理常见业务： ##

案例： 如何对评论进行分页展示

*   使用 EXPLAIN 获得sql的执行计划

EXPLAIN 
    	SELECT customer_id,title,content
              from `product_comment`
              where audit_status = 1
    	  and product_id = 199726
    	  LIMIT 0,5;

> ** SELECT  UPDATE  INSERT  REPLACE  DELETE**

## 二、执行计划 ##

### 1、执行计划分析 ###

执行计划能告诉我们什么？

> 1.  SQL如何使用索引
> 2.  联接查询的执行顺序
> 3.  查询扫描的数据行数

执行计划中的内容：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70][]

## 三、执行计划内容的作用分析及示例 ##

### 1、 ID列 ###

执行计划中的id列的意义：

>  *    ID列中的数据为一组数字，表示执行SELECT语句的顺序
>  *    ID值相同时，执行顺序由上至下
>  *    ID值越大优先级越高，越先被执行

**查看执行计划**：

EXPLAIN SELECT
    	c.`category_name`,
    	a.`product_name`,
    	b.`title`
    FROM
    	product_info a
    JOIN product_comment b ON a.`product_id` = b.`product_id`
    JOIN product_category c ON c.`category_id` = a.`one_category_id`;

截图：

![20191010144953571.png][]

**复杂sql查看执行计划：**

EXPLAIN
    select title
    from product_comment
    WHERE product_id in (
    	SELECT max(product_id)
    	from product_info
    	WHERE one_category_id in (select min(category_id) from product_category)
      )

截图：

![20191010145056377.png][]

**组查询sql查看执行计划：**

EXPLAIN
    SELECT title
    from (
    	SELECT one_category_id,max(product_id)AS pid
    		from product_info
    	  GROUP BY one_category_id
    		) a  JOIN product_comment b on a.pid = b.`product_id`

截图：

![20191010145219423.png][]

### 2、SELECT\_TYPE列 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 1][]

**执行计划案例1：**

EXPLAIN 
    SELECT title
    FROM product_comment
    WHERE
      product_id IN (
    	SELECT max(product_id)
    	FROM product_info
    	WHERE 
    	one_category_id IN (
    		SELECT min(category_id)
    		FROM	product_category
    		)
    	);

![20191010145551742.png][]

补充：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 2][]

### 3、TABLE列 ###

作用： 输出数据行所在的表的名称

>  *  <unionM,N>由ID为M,N查询union产生的结果集
>  *  <derivedN>/<subqueryN>由ID为N的查询产生的结果

**执行计划案例1：**

EXPLAIN
    select product_category.`category_name`,product_info.`product_name`,product_comment.`title`
    from product_info
    JOIN product_comment on product_info.`product_id`=product_comment.`product_id`
    JOIN product_category on product_category.`category_id`=product_info.`one_category_id`

![2019101015000449.png][]

### 4、PARTITIONS列 ###

**作用**： 对于分区表，显示查询的分区ID

对于非分区表，显示为NULL

**执行计划案例：**

EXPLAIN
    SELECT * 
    FROM `crn`.`customer_login_log`
    where customer_id = 1

![2019101015082117.png][]

以用户登录日志为例，应该使用用户表的用户id作为分区条件进行数据的存储和归档，这样有利于将同一个用户的所有数据写入到同一个分区区间，有利于避免查询登录日志时会对大表进行查询过程中对其他用户的登录日志进行过滤而导致的效率损耗！

### 5、TYPE列 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 3][]

### 6、Extra列 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 4][]

### 7、POSSIBLE\_KEYS列 ###

①指出MySQL能使用那些索引来优化查询

②查询列所涉及到的列上的索引都会被列出，但不一定会被使用

### 8、KEY列 ###

①查询优化器优化查询实际所使用的索引

②如果没有可用的索引，则显示为NULL

③如查询使用了覆盖索引，则该索引仅出现在Key列中

### 9、KEY\_LEN 列 ###

①表示索引字段的最大长度

②Key\_len的长度由字段定义计算而来，并非数据的实际长度

### 10、Ref列 ###

表示哪些列或常量被用于查找索引列上的值

### 11、rows列 ###

①表示MySQL通过索引统计信息，估算的所需读取的行数

②Rows值的大小是个统计抽样结果，并不十分准确

### 12、Filtered列 ###

①表示返回结果的行数占需读取行数的百分比

②Filtered列的值越大越好

③Filtered列的值依赖说统计信息

## 四、执行计划的限制 ##

①无法展示存储过程，触发器，UDF对查询的影响

②无法使用EXPLAIN对存储过程进行分析

③早期版本的MySQL只支持对SELECT语句进行分析

### 五、优化分页查询示例      ###

需求: 根据audit\_status及product\_id 创建联合索引，这里需要明确哪一个值放在联合索引的左侧，

由右上角的执行比率知道，使用product\_id作为索引放在组合索引左侧是最合适的，因为其比值最接近于1。

创建执行计划：

EXPLAIN
    SELECT customer_id,title,content
    from product_comment
    where audit_status=1
    AND product_id=199727
    limit 0,5

![20191010152515675.png][]

**初步优化，创建联合索引：**

CREATE INDEX idx_productID_auditStats on product_comment(product_id,audit_status)

经过添加索引优化后的执行计划执行结果：

![20191010152623529.png][]

其查询效率明显提高，由type列可知，由原来的ALL进行全表扫描查询降为非唯一索引查询。

**进一步优化分页查询**

SELECT t.customer_id,t.title,t.content
    from (
        SELECT `comment_id`
           from product_comment
           where product_id=199727 AND audit_status=1 LIMIT 0,5
    ) a JOIN product_comment t
    ON a.comment_id = t.comment_id;

**优化说明：** 先通过分页查询获取到对应数据的comment\_id，此时的查询不会对其他字段进行查询返回，默认可以通过主键索引进行查询，效率极高；然后再讲查询到的a.commetn\_id作为临时子表再与product\_comment进行comment\_id的匹配查询，此时直接通过comment\_id进行查询返回包含comment\_id在内的其他的字段。这种查询方式在IO上能节约很多的资源，当数据量上万时，效率依然不会受到太大影响。

### 六、如何删除重复数据 ###

删除评论表中对同一订单同一商品的重复评论，只保留最早的一条

步骤一：查看是否存在对于一订单同一商品的重复评论

select order_id,product_id,COUNT(*)
    from product_comment
    GROUP BY order_id,product_id HAVING COUNT(*)>1

步骤二：备份product\_comment表

创建备份表：

CREATE TABLE `mc_productdb`.bak_product_comment_161022
    LIKE `mc_productdb`.product_comment;

同步表数据：

INSERT INTO `mc_productdb`.`bak_product_comment_161022`
    select * from `mc_productdb`.`product_comment`;

步骤三：删除同一订单的重复评论

DELETE a
    FROM product_comment a
    JOIN (
    	SELECT order_id,product_id,MIN(comment_id) AS comment_id 
    	FROM product_comment
    	GROUP BY order_id,product_id
    	HAVING COUNT(*)>=2
    ) b ON a.order_id=b.order_id AND a.product_id=b.product_id
    AND a.comment_id> b.comment_id

### 七、进行分区间统计 ###

需求：统计消费总金额大于1000元的，800到1000元的，500到800元的，以及500元以下的人数

SELECT count(CASE WHEN IFNULL(total_money,0) >=1000 THEN a.customer_id END) AS '大于1000'
        ,count(CASE WHEN IFNULL(total_money,0) >=800 AND IFNULL(total_money,0)<1000  
        THEN a.customer_id END) AS '800-1000'
        ,count(CASE WHEN IFNULL(total_money,0) >=800 AND IFNULL(total_money,0)<800  
        THEN a.customer_id END) AS '500-800'
        ,count(CASE WHEN IFNULL(total_money,0) <500 THEN a.customer_id END) '小于500'
    from mc_userdb.customer_login a
    LEFT JOIN
    ( SELECT customer_id,SUM(order_money) AS total_money
        from mc_orderdb.`order_master` GROUP BY customer_id) b
    ON a.customer_id=b.customer_id

###            ![20191010160838121.png][] ###

### 八、捕获有问题的SQL ###

核心：利用执行计划优化查询

> **如何找到需要优化的SQL呢？** 答案：慢查询日志

启用mysql慢查日志

>  set global slow\_query\_log\_file  = /sql\_log/slow\_log.log;
> 
>  set global log\_queries\_not\_using\_indexes = on;
> 
>         未使用索引的SQL记录日志
> 
>  set global long\_query\_time = 0.001;
> 
>        抓取执行超过多少时间的SQL(秒)
> 
>  set global low\_query\_log = on;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 5][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 6][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 7][]

[https_wushaopei.github.io]: https://wushaopei.github.io/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70]: /images/20230601/c308add38c8943bd91d97f06e28282f5.png
[20191010144953571.png]: /images/20230601/fa1cf9a0a5064b749c018344ab57fc8a.png
[20191010145056377.png]: /images/20230601/23f782e75aaf4451a3108778d6d3ce2d.png
[20191010145219423.png]: /images/20230601/32f7ebb9c7244957b2630c00b602a66d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 1]: /images/20230601/5816c773619b435ba1e0724e05b2b396.png
[20191010145551742.png]: /images/20230601/f468308dd7f94c079dfd315c68b1703d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 2]: /images/20230601/4ad981d637d54fbc9b66f13583584f0b.png
[2019101015000449.png]: /images/20230601/db3bbec9dd934209affb815bb2c1053f.png
[2019101015082117.png]: /images/20230601/5e2b9761efc74477b7e5dbc99cc34ee8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 3]: /images/20230601/1836c44c86a840b5afe30720c639502b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 4]: /images/20230601/7a9566082cce43909afb64c3ae8839b5.png
[20191010152515675.png]: /images/20230601/490e6bb8a6cb4928a12ee73ef13517f2.png
[20191010152623529.png]: /images/20230601/b8895a2ba2704498b3f8e53e33143d42.png
[20191010160838121.png]: /images/20230601/b421e06763c74b22a3a59a3851e2a101.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 5]: /images/20230601/3869ac21015745ba92d43e692df4482b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 6]: /images/20230601/ccff002845ab40c0bc85d879f12781c4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 7]: /images/20230601/e5e82ffbf66e4d7e9be8d3eb1893d828.png