4米行表中的简单查询速度慢 -- mysql 领域 和 performance 领域 和 optimization 领域 和 query-performance 领域 dba 相关 的问题

Simple query is slow on 4M-rows table


3
vote

问题

中文

我在制作一个mysql表 pageviews ,其中4米行记录了帖子上用户的浏览量。我需要知道特定用户已读取哪个帖子,但此请求最多需要15秒才能执行:

  SELECT post_id FROM pageviews WHERE user_id = 981 GROUP BY post_id   

以下是执行计划:

  mysql> EXPLAIN SELECT post_id FROM visits WHERE user_id = 981 GROUP BY post_id; +----+-------------+--------+------+---------------+---------+---------+-------+-------+----------------------------------------------+ | id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows  | Extra                                        | +----+-------------+--------+------+---------------+---------+---------+-------+-------+----------------------------------------------+ |  1 | SIMPLE      | visits | ref  | user_id       | user_id | 5       | const | 54696 | Using where; Using temporary; Using filesort | +----+-------------+--------+------+---------------+---------+---------+-------+-------+----------------------------------------------+   

我不确定如何寻找慢化的原因:也许表格没有很好地配置,MySQL Server不良好调整,其他查询锁定的东西,...或许只有4米行是一个很好的尺寸开始分区。

生产数据库是在Amazon RDS

 创建表`PageViews`(   `ID` int(11)不是null auto_increment,   `user_id` int(11)默认为null,   `post_id` int(11)默认为null,   `created_at` datetime not null,   主键(`id`),   关键`post_id`(`post_id`),   关键`user_id`(`user_id`),   关键`created_at`(`created_at`),   约束`fk_444839aa76ed395`外键(`user_id`)引用`用户(`id`),   约束`visits_ibfk_2`外键(`post_id`)引用`posts`(`id`) )Engine = InnoDB Auto_Increment = 4587432默认charset = utf8 collat​​e = utf8_unicode_ci; 
英文原文

I have in production a MySQL table pageviews with 4M rows that records page views of users on posts. I need to know which posts a specific user have read, but this request takes up to 15 seconds to execute:

SELECT post_id FROM pageviews WHERE user_id = 981 GROUP BY post_id 

Here is the execution plan:

mysql> EXPLAIN SELECT post_id FROM visits WHERE user_id = 981 GROUP BY post_id; +----+-------------+--------+------+---------------+---------+---------+-------+-------+----------------------------------------------+ | id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows  | Extra                                        | +----+-------------+--------+------+---------------+---------+---------+-------+-------+----------------------------------------------+ |  1 | SIMPLE      | visits | ref  | user_id       | user_id | 5       | const | 54696 | Using where; Using temporary; Using filesort | +----+-------------+--------+------+---------------+---------+---------+-------+-------+----------------------------------------------+ 

I'm not sure how to look for the cause of the slowness: maybe the table is not well configured, the mysql server not well tuned, other queries locking stuff, ... Or maybe just 4M rows is a good size to start partitioning.

Production database is on Amazon RDS

 CREATE TABLE `pageviews` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `user_id` int(11) DEFAULT NULL,   `post_id` int(11) DEFAULT NULL,   `created_at` datetime NOT NULL,   PRIMARY KEY (`id`),   KEY `post_id` (`post_id`),   KEY `user_id` (`user_id`),   KEY `created_at` (`created_at`),   CONSTRAINT `FK_444839EAA76ED395` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),   CONSTRAINT `visits_ibfk_2` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4587432 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 
           
 
 

回答列表

7
 
vote
vote
最佳答案
 

除@ tombom的建议外, (user_id, post_id) 而不是(或另外,索引越索引)user_id和post_id的索引简化查询,可能摆脱文件排名和临时表,加上覆盖索引的好处。

如果您有足够大的缓冲池并且查询相对频繁,则这可能会显着降低查询执行。

如果在这样做时,查询仍然很慢,你需要做(pre)缓存才能加快查询执行。

 

In addition to @tombom's suggestions, creating an index on (user_id, post_id) instead of (or in addition, but the less indexes the better) separate indexes on user_id and post_id will simplify the query, probably getting rid of the filesort and temporary table, plus giving you the benefits of a covering index.

This will probably lower the query execution significantly if you have a large enough buffer pool and the query is relatively frequent.

If after doing that, the query is still slow, you will need to do (pre)caching in order to speed up the query execution.

 
 
2
 
vote

为什么有 id 根本?为什么不具有 PRIMARY KEY (user_id, post_id)

为什么 user_id post_id 为nullable?他们不应该是 NOT NULL

@jynus是对覆盖索引的正确索引,但是如果您按照我的建议更改pk,则不需要单独的索引。

innodb_buffer_pool_size 通常应为可用RAM的70%。

我没有看到如何(前)缓存是有用的。即使你能做到,它也会碰到其他街区,从而减慢了他人。

 

Why have an id at all? Why not have PRIMARY KEY (user_id, post_id)?

Why have user_id and post_id nullable? Shouldn't they be NOT NULL?

@jynus is right about a covering index, but if you change the PK as I suggest, that separate index won't be necessary.

innodb_buffer_pool_size should normally be 70% of available RAM.

I don't see how (pre)caching would be useful. Even if you could do it, it would be bumping out other blocks, thereby slowing down others.

 
 

相关问题

1  太多的文本字段导致记录太大  ( Too many text fields leads to record too big ) 
我有一个带有多个9 TEXT 列的表,每个列都包含JSON数据。我通常 UPDATE 立即所有列。我似乎无法添加第10个 TEXT 列。 表是innodb。 我该怎么办? ...

0  MySQL + Myisam +需要有关检查表声明的信息  ( Mysqlmyisamneed information regarding check table statement ) 
mysql检查表语句检查a表或表格错误。 该语句是否在检查表之前将自动锁扣放在表中是否有错误? 语句是否执行只读操作? 我已从MySQL工作台(同一表副本/复制)执行的检查表命令继续,我得到 host3 检查表格语句的错误消息,读超时间隔(以秒为单位):[30] 此操作是否可以损坏我的副本/复制? (...

3  第二个索引对顺序表中的时间戳字段的影响  ( Implications of a second index on a timestamp field in a sequential table ) 
我有一个几个千兆字节的mysql表,大致〜100米行。我顺序地存储数据,使得时间戳增加随着ID也增加。由于时间戳的排序和过滤如此慢,因此我经常只需使用ID来过滤日期范围。如果我知道星期三的数据以ID = 87000000开始,那周四的数据在ID = 90000000开始时,我可以通过在这两个ID之间过滤来找到周三的所...

3  可以将已经计算的列重用到MySQL中的查询中?  ( Is possible to reuse a already calculatesd column into a query in mysql ) 
我有一个用复杂的列查询,我想在另一列中使用此列结果,例如: users_mentors2 但他的查询返回错误: users_mentors3 如何重用已经计算的字段? ...

0  MySQL单独的电子邮件和手机从用户表中避免额外的未使用空间使用情况  ( Mysql separate email and cellphone from user table to avoid extra unused space u ) 
我有一个关于把一些字符串(固定长度和变量(可能始终为null))的问题或分开它们(因为空间使用即使它是null)... 例如,我的用户使用手机号码或电子邮件地址 这是用于手机号码: cellphone_country_code (SMALLINT) (UNSIGNED) cellphone_num ...

0  加入多对多表(来自主键表)  ( Joining into many to many table from primary key table ) 
这是我所遇到的多远,虽然我不认为它可以用1个sql-statege完成,但我只想确认它是否有可能只有1语句: SELECT * FROM users INNER JOIN users_mentors ON users_mentors.id=users.mentoruser_id INNER JOIN mentor...

1  为什么这个查询给出了错误的输出?  ( Why this query giving wrong output ) 
我想按评级1,2等找到审查组,以便这样做,所以我写下面的查询,但这给出了错误的信息。 这是查询: SELECT CASE ur.rating WHEN ur.rating between 0.5 and 1 THEN 1 WHEN ur.rating between...

1  如何沿着几张表使用相同的时间戳?  ( How to use the same timestamp along several tables ) 
让我们说一个有2个表,两者都有一个datetime列。 在表A上,在DateTime列中,假设,TS1是非空的,默认为Current_Timestamp和更新Current_timestamp; 另一个表,b,datetime列,令人露地,ts2是非null; 两个表上的DateTime值必须相同(A.Ts1 =...

0  mysql 8角色 - 我可能很清楚一些明显的东西  ( Mysql 8 roles im probably missing something obvious ) 
我正在尝试在mysql 8中使用角色,并失败。我已经阅读了文件,据我所知,我正在做对。因为这是一个"你好世界" 的水平尝试,我认为我错过了非常明显的东西,但我无法弄清楚。任何帮助都将受到高度赞赏。 这是我想要做的。 CREATE USER 'reader'@'localhost' IDENTIFIED BY 'r...

1  根据多个连接更新MySQL InnoDB表  ( Updating mysql innodb table based on multiple joins ) 
我有一个表格的表(略微简化)结构 CREATE TABLE `oak_relation` ( `o_id` int(10) unsigned NOT NULL, `k_id` bigint(20) unsigned NOT NULL, `initial` float unsigned NOT NULL, ...

0  慢查询不使用其中一个表中的索引  ( Slow query not using index in one of the tables ) 
我的rails表结构是这样的:a 1. FirstParty>Individual:2 2. FirstParty:3>FPRep:2>Individual 3. Incident>Vehicle:2>RegisteredOwner>Individual3 0 has_many 1. FirstParty>In...

0  默认值的列作为时间戳的总和  ( Column with default value as sum of timestamp ) 
我有一个有2列的表( JobDate , RecordTime ) -------------------------------------- JobDate | RecordTime | SumCol -------------------------------------- 2019-07...

1  如何在PHP中运行在循环中的选择查询?  ( How to run a select query within while loop in php ) 
在 SELECT 通过PHP查询时循环查询,我需要运行MySQL Abcdefghijklmnabcdefghijklmn9 查询作为 ERROR: terminating logical replication worker due to timeout0 但这不起作用。我不能 ERROR: termin...

1  具有多种类型的帖子表的数据库设置  ( Database setup for posts table with multiple types ) 
努力将其设置为速度和可扩展性。 所以基本上我有一堆帖子,这取决于它的类型是什么,它可以具有我需要存储的不同的输入值。那么我想到的是在"帖子" 表中将共同值存储在一起,并将所有特定值存储在单独类型的特定表中......是可扩展的吗?特别是当我想拉说所有帖子? 我思考的是什么: 表: posts: id, typ...

0  MySQL - 选项卡分隔的空白字段 - 分隔的.txt文件导致负载数据infile中的错误  ( Mysql blank fields in tab delimited txt file causing errors in load data infi ) 
我正在使用Windows机器上的MySQL Workbench使用MySQL 8.0。 我在将Excel文件导入MySQL数据库的大量问题。我认为问题是我的数据包含空白空间或句点 . ,以表示某些位置的缺点。 我已以Excel Spread-Sheet的形式收到数据。正如我所阅读的那样,使用CSV格式可能有时会导致...




© 2021 it.wenda123.org All Rights Reserved. 问答之家 版权所有


Licensed under cc by-sa 3.0 with attribution required.