首页 > 数据库 >mysql更改执行计划_数据量增加导致MySQL执行计划改变

mysql更改执行计划_数据量增加导致MySQL执行计划改变

时间:2022-08-24 11:05:08浏览次数:96  
标签:product gift promo MySQL cost 数据量 mysql id

收到运维同学电话,MySQL服务器连接数满了,登录服务器查看,确实满了,好吧,首先增加连接数到2500,暂时提供对外服务。连接继续升高,又快达到2500。发现有大量的查询时间将近到了1200秒,大量的长连接堆积,导致连接数攀升,看来还是sql的问题。在这些长连接中,发现这样的sql

SELECT product_id,gift_id,gift_original_price,gift_count, FROM promo_xxx WHERE promotion_id IN (589994,589994) AND product_id IN (22569455) AND is_valid=1;

mysql> explain SELECT product_id,gift_id,gift_original_price,gift_count FROM promo_gift WHERE promotion_id IN (589994,589994) AND product_id IN (22569455) AND is_valid=1;

+----+-------------+-----------------+------+---------------+------+---------+------+--------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-----------------+------+---------------+------+---------+------+--------+-------------+

| 1 | SIMPLE | promo_gift_list | ALL | id_promo_gift | NULL | NULL | NULL | 249188 | Using where |

+----+-------------+-----------------+------+---------------+------+---------+------+--------+-------------+

1 row in set (0.04 sec)

mysql> show index from promo_gift;

+-----------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+-----------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| promo_gift_list | 0 | PRIMARY | 1 | id | A | 261184 | NULL | NULL | | BTREE | | |

| promo_gift_list | 0 | id_promo_gift | 1 | promotion_id | A | 1140 | NULL | NULL | YES | BTREE | | |

| promo_gift_list | 0 | id_promo_gift | 4 | product_id | A | 261184 | NULL | NULL | YES | BTREE | | |

狗血的sql,竟然走全表扫描,但是promotion_id有索引啊,为什么没有走索引呢?而且以前建立的索引,走的好好的,今天怎么就出现问题了,这是一个问题

那我们可以通过last_query_cost 查看sql消耗

mysql>SELECT product_id,gift_id,gift_original_price,gift_count FROM promo_gift WHERE promotion_id IN (589994,589994) AND product_id IN (22569455) AND is_valid=1;

mysql>show status like 'last_query_cost';

+-----------------+--------------+

| Variable_name | Value |

+-----------------+--------------+

| Last_query_cost | 52626.599000 |

+-----------------+--------------+

1 row in set (0.00 sec)

不走索引,那我们强制使用索引

mysql> explain SELECT product_id,gift_id,gift_original_price,gift_count FROM promo_gift force index(id_promo_gift) WHERE promotion_id IN (589994,589994) AND product_id IN (22569455) AND is_valid=1;

+----+-------------+-----------------+-------+---------------+---------------+---------+------+--------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-----------------+-------+---------------+---------------+---------+------+--------+-------------+

| 1 | SIMPLE | promo_gift_list | range | id_promo_gift | id_promo_gift | 5 | NULL | 124594 | Using where |

+----+-------------+-----------------+-------+---------------+---------------+---------+------+--------+-------------+

1 row in set (0.02 sec)

嗯,加上索引了,那么sql消耗怎么样呢?

mysql> SELECT product_id,gift_id,gift_original_price,gift_count FROM promo_gift force index(id_promo_gift) WHERE promotion_id IN (589994,589994) AND product_id IN (22569455) AND is_valid=1;

+------------+----------+---------------------+------------+

| product_id | gift_id | gift_original_price | gift_count |

+------------+----------+---------------------+------------+

| 22569455 | 23230046 | 147.00 | 1 |

+------------+----------+---------------------+------------+

1 row in set (0.40 sec)

mysql> show status like 'last_query_cost';

+-----------------+---------------+

| Variable_name | Value |

+-----------------+---------------+

| Last_query_cost | 174432.609000 |

+-----------------+---------------+

1 row in set (0.00 sec)

我们发现如果使用这个索引,sql消耗174432.609000>52626.599000,mysql优化器认为使用这个id_promo_gift索引,sql消耗是非常大的,这就是mysql执行不使用这个索引的原因。

后来开发人员说,昨天晚上这个表增加了11万多的数据,嗯,数据量增加,mysql执行计划改变。那好吧,单独product_id列再加一个索引。

mysql> alter table promo_gift_list add index product_id(product_id);

Query OK, 0 rows affected (6.45 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> explain SELECT product_id,gift_id,gift_original_price,gift_count FROM promo_gift_list WHERE promotion_id IN (589994,589994) AND product_id IN (22569455) AND is_valid=1 AND IFNULL(is_delete,0)!=1;

+----+-------------+-----------------+------+--------------------------+------------+---------+-------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-----------------+------+--------------------------+------------+---------+-------+------+-------------+

| 1 | SIMPLE | promo_gift_list | ref | id_promo_gift,product_id | product_id | 5 | const | 2 | Using where |

+----+-------------+-----------------+------+--------------------------+------------+---------+-------+------+-------------+

1 row in set (0.00 sec)

使用了刚才新加的索引 product_id

mysql> SELECT product_id,gift_id,gift_original_price,gift_count FROM promo_gift_list WHERE promotion_id IN (589994,589994) AND product_id IN (22569455) AND is_valid=1 AND IFNULL(is_delete,0)!=1;

+------------+----------+---------------------+------------+

| product_id | gift_id | gift_original_price | gift_count |

+------------+----------+---------------------+------------+

| 22569455 | 23230046 | 147.00 | 1 |

+------------+----------+---------------------+------------+

1 row in set (0.00 sec)

mysql> show status like 'last_query_cost';

+-----------------+----------+

| Variable_name | Value |

+-----------------+----------+

| Last_query_cost | 2.399000 |

+-----------------+----------+

1 row in set (0.01 sec)

sql消耗降到了2.399000,ok,问题解决。连接数很快从1000多降到100以内。

数据量的增加导致了mysql执行计划的改变,那么mysql的cost是怎么计算的呢?

cost=io_cost+cpu_cost

cpu_cost位于mysql上层,处理返回的记录所花开销,io_cost存储引擎层,读取也没的IO开销。最直接的方式last_query_cost记录sql的cost。查看last_query_cost可以初步判断sql的cost,明白mysql优化器执行的依据。
参考网址见:https://blog.csdn.net/weixin_35980267/article/details/113223849

标签:product,gift,promo,MySQL,cost,数据量,mysql,id
From: https://www.cnblogs.com/lipengsheng-javaweb/p/16619073.html

相关文章

  • 设置Mysql数据库允许远程连接
    Mysql数据库用户权限设置1.进入容器dockerexec-itmysql_test/bin/bash注意:由于我是通过docker安装的数据库,所以在操作之前需要进入容器,直接安装在本机的用户可以跳......
  • Linux删除MySQL
    查看进程rpm-qa|grep-imysql删除进程rpm-ev--nodeps+进程服务删除mysql文件夹find/-namemysql删除文件夹rm-rf+文件夹路径删除配置文件rm......
  • mysql 建库、建用户、授权命令
    版本5.7CREATEDATABASE   `databasename`;      CREATEUSER'username'@'%'IDENTIFIEDBY'password';      GRANTALLPRIVILEGESON`databas......
  • Linux安装mysql8.0
    MySQL配置MySQL8安装源sudorpm-Uvhhttps://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm安装MySQL8(提示下载包,选y,后面有其他提示都选y)sudoyu......
  • 基于Docker做MySQL主从搭建与Django的读写分离
    基于Docker做MySQL主从搭建主从的作用:写数据数据时使用主库,从库只用来读数据,这样做能够减少数据库压力,主从搭建可以一主一从,也可以是一主多从。mysql主从配置的流程如图......
  • Mysql 一主一从
    1.主从原理1.1主从介绍所谓mysql主从就是建立两个完全一样的数据库,其中一个为主要使用的数据库,另一个为次要的数据库,一般在企业中,存放比较重要的数据的数据库服务器......
  • docker中的mysql中文乱码解决办法
    博主最近在做谷粒商城,因为要使用docker安装mysql,但是由于安装的时候没有指定mysql的数据库的utf8格式,导致插入的时候就出现了中文是问号的情况,到处百度终于解决,于是打......
  • springboot+mybatis-plus-join+mysql实现连表查询
    1.简介  Mybatis是目前比较主流的持久层框架,使用非常广泛。Mybatis-Plus是基于Mybatis增强工具包,越来越受到开发人员的喜爱。  在使用Mybatis-Plus开发时,简单的crud......
  • ERROR 2006 (HY000): MySQL server has gone away
    sourcesql文件的时候,报错如下:ERROR2006(HY000):MySQLserverhasgoneaway 经查阅导致该error的原因很多,具体分析了我的问题是语句太长了,修改max_allowed_packet......
  • liunx 安装 mysql 8.0 ,centos 7
    下载地址:MySQL::DownloadMySQLCommunityServerhttps://dev.mysql.com/downloads/mysql/ 下载完整的打包程序,然后解压   解压命令:tar-xvfmysql-8.0.30-......