时间戳

首页 > 解决方案 > Mysql，慢平均查询

mysql - Mysql，慢平均查询

问题描述

我有一个单表 book_log Mysql 5.7

+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| book_id    | int(11)  | YES  | MUL | NULL    |                |
| type       | int(11)  | NO   | MUL | NULL    |                |
| value      | int(11)  | YES  |     | NULL    |                |
| created_at | datetime | NO   |     | NULL    |                |
+------------+----------+------+-----+---------+----------------+

书桌与系列连接（一个系列可以有很多书）

创建表信息：

book_log | CREATE TABLE `book_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `book_id` int(11) DEFAULT NULL,
  `type` int(11) NOT NULL,
  `value` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_7E42115316A2B381` (`book_id`),
  KEY `IDX_TYPE` (`type`),
  KEY `IDX_ME` (`book_id`,`type`) USING BTREE,
  CONSTRAINT `FK_7E42115316A2B381` FOREIGN KEY (`book_id`) REFERENCES `book` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1158962 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |


book  | CREATE TABLE `book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `series_id` int(11) DEFAULT NULL,
  `language_id` int(11) DEFAULT NULL,
  `position` int(11) NOT NULL,
  `dir` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_CBE5A3315278319C` (`series_id`),
  KEY `IDX_CBE5A33182F1BAF4` (`language_id`),
  CONSTRAINT `FK_CBE5A3315278319C` FOREIGN KEY (`series_id`) REFERENCES `series` (`id`),
  CONSTRAINT `FK_CBE5A33182F1BAF4` FOREIGN KEY (`language_id`) REFERENCES `language` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=55022 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

我为给定系列制作平均值

select AVG(value)
from book_log
         join book b on book_log.book_id = b.id
where type = 20 and b.series_id = ?;

解释 ：

+----+-------------+----------+------------+------+------------------------------+----------------------+---------+----------------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys                | key                  | key_len | ref            | rows | filtered | Extra       |
+----+-------------+----------+------------+------+------------------------------+----------------------+---------+----------------+------+----------+-------------+
|  1 | SIMPLE      | b        | NULL       | ref  | PRIMARY,IDX_CBE5A3315278319C | IDX_CBE5A3315278319C | 5       | const          |  212 |   100.00 | Using index |
|  1 | SIMPLE      | book_log | NULL       | ref  | IDX_7E42115316A2B381,IDX_ME  | IDX_7E42115316A2B381 | 5       | bdd.b.id       |   33 |   100.00 | NULL        |
+----+-------------+----------+------------+------+------------------------------+----------------------+---------+----------------+------+----------+-------------+

或者

select AVG(value)
from book_log
where type = 20 AND book_id IN (
    select id from book where series_id = ?
);

解释 ：

+----+-------------+----------+------------+------+--------------------------------------+----------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys                        | key                  | key_len | ref               | rows | filtered | Extra       |
+----+-------------+----------+------------+------+--------------------------------------+----------------------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | book     | NULL       | ref  | PRIMARY,IDX_CBE5A3315278319C         | IDX_CBE5A3315278319C | 5       | const             |  212 |   100.00 | Using index |
|  1 | SIMPLE      | book_log | NULL       | ref  | IDX_7E42115316A2B381,IDX_TYPE,IDX_ME | IDX_7E42115316A2B381 | 5       | bdd.book.id       |   33 |     3.72 | Using where |
+----+-------------+----------+------------+------+--------------------------------------+----------------------+---------+-------------------+------+----------+-------------+

这些查询有 10 973 个结果，count(*) 需要 42 毫秒，但 avg 查询需要超过 1 秒。

我不明白为什么这么长。任何想法 ？

谢谢。

标签： mysql

解决方案

您可以期望与COUNT(*)或 一样快或SUM(somecol)更快AVG(othercolumn)。为什么？根据 SQL 规则，数据库服务器将应用任何产生正确答案的优化。COUNT(*)对其进行了一些严重的优化。

但是做算术的聚合函数；相反，他们必须检查每条记录。所以，慢一点。

您可以为查询创建专门构建的索引。

它是这个：

ALTER TABLE book_log
 ADD INDEX type_id_val 
           (type, book_id, val)

我为索引选择了这些列，因为您的查询在索引中搜索了一个type分词。在找到所选类型的第一行后，MySQL 可以仅通过索引而不是表进行范围扫描。所以，更快。它被称为覆盖索引。

推荐阅读