数据量增加导致mysql执行计划改变解决

首页

数据库

mysql教程

数据量增加导致mysql执行计划改变解决_MySQL

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 01, 2016 pm 01:31 PM

mysql服务器电话计划

bitsCN.com

数据量增加导致mysql执行计划改变解决

收到运维同学电话，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 &#39;last_query_cost&#39;;+-----------------+--------------+| 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 &#39;last_query_cost&#39;;+-----------------+---------------+| 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: 0mysql> 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 &#39;last_query_cost&#39;;+-----------------+----------+| 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优化器执行的依据。

bitsCN.com

声明

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn

MySQL的位置：数据库和编程Apr 13, 2025 am 12:18 AM

MySQL在数据库和编程中的地位非常重要，它是一个开源的关系型数据库管理系统，广泛应用于各种应用场景。1）MySQL提供高效的数据存储、组织和检索功能，支持Web、移动和企业级系统。2）它使用客户端-服务器架构，支持多种存储引擎和索引优化。3）基本用法包括创建表和插入数据，高级用法涉及多表JOIN和复杂查询。4）常见问题如SQL语法错误和性能问题可以通过EXPLAIN命令和慢查询日志调试。5）性能优化方法包括合理使用索引、优化查询和使用缓存，最佳实践包括使用事务和PreparedStatemen

MySQL：从小型企业到大型企业Apr 13, 2025 am 12:17 AM

MySQL适合小型和大型企业。1)小型企业可使用MySQL进行基本数据管理，如存储客户信息。2)大型企业可利用MySQL处理海量数据和复杂业务逻辑，优化查询性能和事务处理。

幻影是什么读取的，InnoDB如何阻止它们（下一个键锁定）？Apr 13, 2025 am 12:16 AM

InnoDB通过Next-KeyLocking机制有效防止幻读。1）Next-KeyLocking结合行锁和间隙锁，锁定记录及其间隙，防止新记录插入。2）在实际应用中，通过优化查询和调整隔离级别，可以减少锁竞争，提高并发性能。

mysql：不是编程语言，而是...Apr 13, 2025 am 12:03 AM

MySQL不是一门编程语言，但其查询语言SQL具备编程语言的特性：1.SQL支持条件判断、循环和变量操作；2.通过存储过程、触发器和函数，用户可以在数据库中执行复杂逻辑操作。

MySQL：世界上最受欢迎的数据库的简介Apr 12, 2025 am 12:18 AM

MySQL是一种开源的关系型数据库管理系统，主要用于快速、可靠地存储和检索数据。其工作原理包括客户端请求、查询解析、执行查询和返回结果。使用示例包括创建表、插入和查询数据，以及高级功能如JOIN操作。常见错误涉及SQL语法、数据类型和权限问题，优化建议包括使用索引、优化查询和分表分区。

MySQL的重要性：数据存储和管理Apr 12, 2025 am 12:18 AM

MySQL是一个开源的关系型数据库管理系统，适用于数据存储、管理、查询和安全。1.它支持多种操作系统，广泛应用于Web应用等领域。2.通过客户端-服务器架构和不同存储引擎，MySQL高效处理数据。3.基本用法包括创建数据库和表，插入、查询和更新数据。4.高级用法涉及复杂查询和存储过程。5.常见错误可通过EXPLAIN语句调试。6.性能优化包括合理使用索引和优化查询语句。