MySQL:优化具有范围和拼减的查询 -- mysql 领域 和 performance 领域 和 optimization 领域 和 index-tuning 领域 和 query-performance 领域 dba 相关 的问题

MySQL: Optimize query with a range and distinct count


1
vote

问题

中文

我在一个大表上有一个有数百万行的查询,如下所示:

  SELECT     COUNT(         DISTINCT clicks.tx_id     ) AS unique_clicks_count ,     clicks.offer_id FROM     clicks WHERE     clicks.offer_id = 5     AND created_at > '2014-11-27 18:00:02' ;   

create_at是一个时间戳。我有一个用于使用的复合索引(Reply_id,created_at)。以下是解释:

  |  1 | SIMPLE      | clicks | range | clicks_created_at_index,clicks_offer_id_created_at_index | clicks_offer_id_created_at_index | 8       | NULL | 215380 | Using index condition; Using MRR |   
  1. 牢记范围,我需要能够计算不同的 /storage-s01/mysql0 的索引,很可能覆盖tx_id?

  2. 在没有的情况下,索引看起来像指定 /storage-s01/mysql1 ,而是执行 /storage-s01/mysql2

英文原文

I have a query on a large table with millions of rows that looks like this:

SELECT     COUNT(         DISTINCT clicks.tx_id     ) AS unique_clicks_count ,     clicks.offer_id FROM     clicks WHERE     clicks.offer_id = 5     AND created_at > '2014-11-27 18:00:02' ; 

Created_at is a timestamp. I have a compound index on (offer_id, created_at) which gets used. The following is the explain:

|  1 | SIMPLE      | clicks | range | clicks_created_at_index,clicks_offer_id_created_at_index | clicks_offer_id_created_at_index | 8       | NULL | 215380 | Using index condition; Using MRR | 
  1. Keeping in mind the range, what kind of index would I need to be able to count the distinct tx_id's efficiently, most likely which covers tx_id as well?

  2. What would the index look like without specifying clicks.offer_id = 5, and instead doing GROUP BY offer_id?

              
         
         

回答列表

1
 
vote

你有最好的索引。它是正确的顺序,解释说"使用索引" ,这意味着它读取索引来获取答案,并且不必达到数据。

(进一步解决所有评论......)

请注意,需要读取大约200k行(索引)来执行计数。许多行需要时间。

INDEX(offer_id, created_at) INDEX(offer_id, created_at, tx_id) - 显然您使用的是innodb,tx_id是主键。 PK包括在每个辅助密钥中,因此这两个索引规范几乎是相同的。

索引中列的顺序通常是重要的。这在这里确实很重要。字段必须按此顺序:(1)所有"=" 条件(Reply_ID),(2)一个范围(创建),(3)所有其他字段以任何顺序以"使用索引" (tx_id)。

如果您没有 offer_id = 5 ,请按照上面的模式,获取(1)(空集),(2)(created_id),(3)(tx_id) - 即, INDEX(created_at, tx_id) 。请注意,索引都没有适用于其他查询。

没有种类的分区将有助于表现。您不需要二维索引(如在两个范围内);你有"=" 和"范围" ,所以"复合索引" 效果最佳。

我怀疑"使用MRR" (多级读优化)有效地替换"使用临时" 和"使用文件代" 通常会用于<代码> ABCDEFGHIJKLMNABCDEFGHIJKLMN9 。

 

You have the best index there is. It is in the right order, and the EXPLAIN says "Using index", which means that it read the index to get the answer, and did not have to reach into the data.

(To further address all the comments...)

Note that it needed to read about 200K rows (of the index) to do the count. That many rows takes time.

INDEX(offer_id, created_at) versus INDEX(offer_id, created_at, tx_id) -- Apparently you are using InnoDB and tx_id is the PRIMARY KEY. The PK is included in every secondary key, so these two index specifications are virtually identical.

Order of the columns in an INDEX usually matters. And it does matter here. The fields must be in this order: (1) all the the "=" conditions (offer_id), (2) one range (created_id), and (3) all the other fields to make it "Using index", in any order (tx_id).

If you did not have offer_id = 5, follow the above pattern and get (1) (empty set), (2) (created_id), and (3) (tx_id) -- That is, INDEX(created_at, tx_id). Note that neither index works well for the other query.

No kind of PARTITIONing would help performance at all. You don't need a 2-dimensional index (as in two ranges); you have "=" and "range", so a 'compound index' works best.

I suspect that "Using MRR" (Multi-Range Read Optimization) effectively replaces "Using temporary" and "Using filesort" would might normally be used for DISTINCT.

 
 
       
       

相关问题

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

0  无法加入一个新鲜的开始Mariadb Galera Cluster  ( Cant join a freshley started mariadb galera cluster ) 
我是我第一次聚类体验。我需要创建一个MariaDB集群。 Currenlty我有两个服务器,但我计划将来增加1个。 现在,我有: maz-pihole(带有mariadb 10.4.6的Debian 9的OS):IP地址192.168.250.111 - 我想成为主要主节点 Dar-Pihole(覆盆子PI上的...

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 ...

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

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

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 + 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之间过滤来找到周三的所...

2  如何将行转换为mysql上的列并查询?  ( How to convert rows into columns and query on them in mysql ) 
我有三个表患者,其中包含我患者的名称,控制,它代表了可以为每个疾病绘制的UI控制,以及 controlsvalues 表包含为每位患者提交的控件的值 让我们有一些数据 患者 表 |ID | Name | |-----------| | 1 | Ara | | 2 | Sada | 控件 表 ...

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格式可能有时会导致...

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...

3  4米行表中的简单查询速度慢  ( Simple query is slow on 4m rows table ) 
我在制作一个mysql表 pageviews ,其中4米行记录了帖子上用户的浏览量。我需要知道特定用户已读取哪个帖子,但此请求最多需要15秒才能执行: SELECT post_id FROM pageviews WHERE user_id = 981 GROUP BY post_id 以下是执行计划: ...




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


Licensed under cc by-sa 3.0 with attribution required.