sqlite查询慢虽然它应该快 -- optimization 领域 和 sqlite 领域 dba 相关 的问题

Sqlite query slow whilst it should be fast


0
vote

问题

中文

我有以下查询:

  SELECT breakType,date,blockname,modname,metadata,nbtdata,x,y,z   FROM BlocksBrokenByExplosion   WHERE dimension=0     AND y>60     AND y<70     AND x>150     AND x<250     AND z>150     AND z<250     AND date > '2015-05-08 00:10:00'   

具有查询计划:

  "0","0","0","SEARCH TABLE BlocksBrokenByExplosion   USING INDEX BlocksBrokenByExplosion_Indexes (dimension=? AND y>? AND y<?)"   

我使用的索引是:

    CREATE INDEX 'BlocksBrokenByExplosion_Indexes' ON          'BlocksBrokenByExplosion' ('dimension' ASC,'y' ASC,'x' ASC, 'z' ASC)   

分析显示以下信息:

    "BlocksBrokenByExplosion","BlocksBrokenByExplosion_Indexes","78193 78193 2114 25 2"   

问题我面临着

此查询需要200ms才能执行并返回4409行,在我看来非常差的表现,但我忍不住感受到这可以优化。

表仅包含78.000行,并根据查询计划使用索引。

问题

如何优化此查询,以便将以更可接受的速度执行,或者是最好的我将使用sqlite?

并且任何人都知道为什么不根据查询计划使用x和z索引?

编辑1

我通过重新重新格式化此表单

来刮掉30ms
  SELECT * FROM   (SELECT breakType,date,blockname,                          modname,                          metadata,                          nbtdata,                          x,                          y,                          z    FROM BlocksBrokenByExplosion indexed BY BlocksBrokenByExplosion_IndexesX    WHERE dimension=0      AND x>150      AND x<250      AND dimension = 0      AND date > '2015-05-08 00:10:00') WHERE z>150   AND z<250   AND y>60   AND y<70   

和索引

  CREATE INDEX 'BlocksBrokenByExplosion_IndexesX' ON      'BlocksBrokenByExplosion' ('x' ASC)   

所以现在它在170ms中运行,但这仍然比我想要的更慢。我的目标至少是50毫秒...所以我仍然可以向提示和技巧开放如何使这更快地制作。

英文原文

I have the following query:

SELECT breakType,date,blockname,modname,metadata,nbtdata,x,y,z   FROM BlocksBrokenByExplosion   WHERE dimension=0     AND y>60     AND y<70     AND x>150     AND x<250     AND z>150     AND z<250     AND date > '2015-05-08 00:10:00' 

With a query plan:

"0","0","0","SEARCH TABLE BlocksBrokenByExplosion   USING INDEX BlocksBrokenByExplosion_Indexes (dimension=? AND y>? AND y<?)" 

The index I am using is:

  CREATE INDEX 'BlocksBrokenByExplosion_Indexes' ON          'BlocksBrokenByExplosion' ('dimension' ASC,'y' ASC,'x' ASC, 'z' ASC) 

Analyze shows the following information:

  "BlocksBrokenByExplosion","BlocksBrokenByExplosion_Indexes","78193 78193 2114 25 2" 

The problem i'm facing

This query takes 200ms to execute and return 4409 rows which is very poor performance in my opinion and I can't help but feeling this can be optimised.

The table only contains 78.000 rows and according to the query plan the index is used.

The question

How do I optimise this query so it will execute in more acceptable speed, or is this the best I am going to get with sqlite?

And does anyone know why the x and z indexes are not being used according to the query plan?

EDIT 1

I managed to shave off 30ms by reformatting the query in this form

SELECT * FROM   (SELECT breakType,date,blockname,                          modname,                          metadata,                          nbtdata,                          x,                          y,                          z    FROM BlocksBrokenByExplosion indexed BY BlocksBrokenByExplosion_IndexesX    WHERE dimension=0      AND x>150      AND x<250      AND dimension = 0      AND date > '2015-05-08 00:10:00') WHERE z>150   AND z<250   AND y>60   AND y<70 

And index

CREATE INDEX 'BlocksBrokenByExplosion_IndexesX' ON      'BlocksBrokenByExplosion' ('x' ASC) 

So now it runs in 170ms, but that's still slower than i'd want it. My target is 50ms at least... So i'm still open to tips and tricks how to make this faster.

     

回答列表

1
 
vote
vote
最佳答案
 

多个范围查找不能用正常(b树)索引进行优化。

必须创建一个 r树索引用于坐标。

 

Multiple ranges lookups cannot be optimized with normal (B-tree) indexes.

You have to create an R-tree index for your coordinates.

 
 
     
     
0
 
vote

您的MultiPart索引正在覆盖4列,但支持Seek的唯一列是第一列。尾随列(y,x,z)可用于查找行,但这可能不是最佳的。

如果搜索最佳的列是 y 然后使第一列进行。索引都是关于统计数据,它指示如何使用索引。所以,也许这更有效:

    CREATE INDEX 'BlocksBrokenByExplosion_Indexes' ON          'BlocksBrokenByExplosion' ('y' ASC,'x' ASC, 'z' ASC', dimension' ASC)   

当然,正确的答案取决于您的数据,每列的基数,以及哪个索引列提供最佳结果。

 

Your multipart index is covering 4 columns, but the only column that supports a seek is the first column. The trailing columns (y, x, z) can be used to find the rows, but this may not be optimal.

If the column best for a search is y then make that the first column. Indexes are all about statistics which directs how the index can be used. So, perhaps this would be more effective:

  CREATE INDEX 'BlocksBrokenByExplosion_Indexes' ON          'BlocksBrokenByExplosion' ('y' ASC,'x' ASC, 'z' ASC', dimension' ASC) 

Of course, the correct answer depends on your data, the cardinality of each column, and which index columns give the best result.

 
 
         
         

相关问题

0  详尽的SQL存储PROC验证使插入慢  ( Exhaustive sql stored proc validation make insert slow ) 
所以基本上我们的团队正在研究一个模块来从Excel文件中检索行值并在批处理作业期间插入它。 要求如下: - 每个列应完成详尽验证,并且验证错误消息应根据要求规范描述 例如:由于日期格式无效/超过最大长度,列[x]中的错误。 - 每个列中的错误应插入数据库(不是日志文件) - 插入应按行排列,没有散装。任何故障...

1  在PostgreSQL中优化查询,它试图匹配字符串并匹配时间戳范围  ( Optimizing query in postgresql that tries to match a string and matches a timest ) 
我在PostgreSQL中构建了一个数据库,用于财务数据,表格如下所示: create table fin_data( brokerid text, stock int, holding bigint, stake float, value bigint, ...

1  MySQL查询优化组与MAX [已关闭]  ( Mysql query optimization group by with max ) 
关闭。这个问题是 off-topic 。它目前不接受答案。 想要改进这个问题?更新问题,所以它是主题用于数据库管理员堆栈交换。 closed 1...

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 以下是执行计划: ...

-1  优化查询或分为两个查询和流程结果分别?  ( Optimize query or split in two queries and process result separately ) 
我有这个db架构: 因为您可能会注意到表之间没有关系,并且不会在未来。如果我运行以下查询,我将获得大量行: SELECT COUNT(*) FROM `territory`; => 6112 rows SELECT COUNT(*) FROM `group`; => 13669 rows SELECT CO...

3  为什么CBO消除在此查询中的“截然不同”,而不是“群体”?  ( Why does the cbo eliminate distinct and not group by in this query ) 
CBO选择消除 distinct 在"慢速" 查询中 - 据推测,由于外部 group by ,它可以说明不需要操作。 我的问题是: 1)为什么它选择这样做 - 在这种情况下,我看不到花费的原因和计划中的预测基数 2)如果选择消除 distinct ,为什么不适用相同的逻辑并消除 group by ? 测试用:...

1  数据库设计建议数据刮擦/仓库应用程序?  ( Database design suggestions for a data scraping warehouse application ) 
我正在研究数据仓库类型的数据库设计,涉及每天大量的插入。数据归档将进一步用于生成报告。我将有一个用户列表(例如用户设置的200万),我需要监控与它们相关的日常社交网络活动。 例如,让一组100用户说U1,U2,...,U100。 我需要将他们的日常状态计数插入到我的数据库中。 考虑对用户U1的总状态计数于6月30日 ...

0  DB设计付款表  ( Db design payments table ) 
我正在研究使用DB的桌面应用程序存储信息I.E(客户,房屋,专业人士,付款)。因此,该申请必须接受客户到公司,公司向客户(如果是退款),公司向所有者到公司的主人。此时我正在考虑这个付款表(有更多的列,数量,数量)。 | id | int(11) | NO | PRI | NULL...

3  谓词的直接价值产生不太好的计划  ( Direct values on predicate produce not so good plan ) 
我正在使用stackoverflow转储来运行一些测试。 特别是,我正在查询此表: 我已创建此索引: 我正在运行以下查询(只是强制索引来测试替代方案) 我得到以下exec计划的高成本(66.63)。 这些是运行此查询后的IO统计信息: 然后,我通过提供变量而不是直接值来运行相同的查询 ...

0  优化大(宽)树的单向同步  ( Optimizing one way syncing of large wide tree ) 
我处理几棵树的大小(它们都有〜8-10的深度),但它们不是不寻常的组成〜50-100K节点。 此刻,树木使用简单的parent_id fks存储。 在程序中创建/生成树,然后需要保存在数据库中。捕获是我们无法擦除前一棵树(如果存在相同类型,如果存在),我们需要更新它以反映新(已更改)的树。偶尔这意味着交换整个树(如果...

0  优化器逻辑树:不同的树类型的顺序?  ( Optimizer logical trees order of the different tree types ) 
基于来自TraceFlags 8605,8606和8607的观察输出(以及I-One的评论),我将其作为生成逻辑树的顺序。 转换 输入 简体 加入折叠 在项目标准化之前 项目标准化后 输出 在转换和简化的顺序之外,我没有发现我的评估是正确的独立验证。 是正确的顺序? ...

1  MySQL(InnoDB)更新以Where和条款的值更新设置性能  ( Mysql innodb update set performance with value in where and clause ) 
如果执行了更新查询,其中设置值的值也是where子句的一部分,以设置的列的附加和语句的形式,是否有重大的性能增益?是否有其他好处可以以这种方式更新一组记录? 例如, UPDATE table1 SET value1='foo' WHERE value2='bar' AND value1 != 'foo'; ...

7  Mariadb MySQL调谐器报告混乱  ( Mariadb mysql tuner report confusing ) 
我想请您澄清关于Mariadb数据库的MySQLTuner的报告。 mysqltuner被调用--nogood flag! >> MySQLTuner 1.7.1 - Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and d...

3  为什么Where子句不会在视图的查询中被推下来?  ( Why the where clause is not pushed down in the views query ) 
与postgres 9.4,我经常做以下查询: SELECT DISTINCT ON(recipient) * FROM messages LEFT JOIN identities ON messages.recipient = identities.name WHERE timestamp BETWEEN ti...

1  MySQL:优化具有范围和拼减的查询  ( Mysql optimize query with a range and distinct count ) 
我在一个大表上有一个有数百万行的查询,如下所示: SELECT COUNT( DISTINCT clicks.tx_id ) AS unique_clicks_count , clicks.offer_id FROM clicks WHERE clicks....




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