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

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了数据量增加导致MySQL执行计划改变，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6744字，纯文字阅读大概需要10分钟。

内容图文

mysql服务器连接数满了，登录服务器查看，确实满了，好吧，首先增加连接数到2500，暂时提供对外服务。连接继续升高，又快达到250

收到运维同学电话，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优化器执行的依据。

推荐阅读：

分析MySQL慢查询日志的好工具--mysqlsla

MySQL前缀索引导致的慢查询

【体系结构】MySQL 日志文件--慢查询日志

MySQL调优三步曲(慢查询、explain profile)

内容总结

以上是互联网集市为您收集整理的数据量增加导致MySQL执行计划改变全部内容，希望文章能够帮你解决数据量增加导致MySQL执行计划改变所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端