blocks|key|1372658|text|在(+customer_id，created_at+)上创建一个复合索引，而不是在ID和Created上创建两个单独的索引。这样，索引引擎就可以使用where子句的两个部分，而不是只希望得到一个子句。直接跳到客户ID，然后直接跳到所需的日期，然后给出结果。它应该非常快。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1372659|其他后续行动。我听到您关于拥有多个索引的评论，但请将它们添加到主索引中，例如|1372660|(+customer_id、created_at、updated_at、completion_time+)|1372661|然后，在查询中可以始终在where子句中包含一些有关索引的帮助。例如，我不知道你的具体数据。在某个给定点创建一条记录。更新和完成时间将始终在此之后。从创建到完成(最坏的情况)需要多长时间……2天，10天，90天？|1372662|where
+++++++customerID+=+?
+++AND+created_at+>=+date+-+10+days
+++AND+updated_at+>=+date+-1|code-block|syntax|javascript|1372663|同样，只是一个例子，但是如果一个人有1000个订单，并且周转时间相对较短，您可以跳到最近的订单，然后找到在该时间段内更新的订单。同样，只有一个选项作为单个索引与3个、4个或更多索引。|1372664|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|S|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|T|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|U|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Instead of two single indexes on ID and Created, create a single composite index on ( customer_id, created_at ). This way the index engine can use BOTH parts of the where clause instead of just hoping to get the one. Jump right to the customer ID, then jump directly to the date desired, then gives results. it SHOULD be very fast.

Additional Follow-up.
I hear your comment about having multiple indexes, but add those into the main one, just after such as 

( customer_id, created_at, updated_at, completion_time )

Then, in your queries could always include some help on the index in the where clause. For example, and I don't know your specific data. A record is created at some given point. The updated and completion time will always be AFTER that. How long does it take (worst-case scenario) from a creation to completion time... 2 days, 10 days, 90 days?

<pre><code>where
 customerID = ?
 AND created_at &gt;= date - 10 days
 AND updated_at &gt;= date -1
</code></pre>

Again, just an example, but if a person has 1000's of orders and relatively quick turn-around time, you could jump to those most recent and then find those updated within the time period.. Again, just an option as a single index vs 3, 4 or more indexes.

blocks|key|1372681|text|我要做的第一个优化是对表的访问：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1372682|create+index+ix1+on+`order`+(customer_id,+created_at);|code-block|syntax|javascript|1372683|然后，如果查询仍然很慢，我会尝试将您选择的列附加到索引中。例如，如果要选择列order_id、amount和status|offset|length|style|CODE|1372684|create+index+ix1+on+`order`+(customer_id,+created_at,+
++order_id,+amount,+status);|1372685|第二种策略可能是有益的，但您需要对其进行测试，以找出它在您的特定情况下所带来的性能改进。|1372686|第二种策略的最大改进是它只遍历辅助索引，避免遍历回表的主要聚集索引(这可能会耗费时间)。|1372687|entityMap^0|0|0|12|8|1B|6|1I|6|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@$I|X|J|Y|K|L]|$I|Z|J|10|K|L]|$I|11|J|12|K|L]]|9|@]|A|$]]|$1|M|3|N|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|14|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|15|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|16|8|@]|9|@]|A|$]]]|T|$]]

The first optimization I would do is on the access to the table:

<pre><code>create index ix1 on `order` (customer_id, created_at);
</code></pre>

Then, if the query is still slow I would try appending the columns you are selecting to the index. If, for example, you are selecting the columns <code>order_id</code>, <code>amount</code>, and <code>status</code>:

<pre><code>create index ix1 on `order` (customer_id, created_at, 
 order_id, amount, status);
</code></pre>

This second strategy could be beneficial, but you'll need to test it to find out what performance improvement it peoduces in your particular case. 

The big improvement of this second strategy is that it walks the secondary index only, by avoiding to walk back to the primary clustered index of the table (that can be time consumming).

blocks|key|3868222|text|看起来你正在处理一个增长非常快的表，我应该考虑将这个频繁的查询转移到一个冷表或副本。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3868223|还有一点是，您是否考虑过按customer_id分区。我不太理解查询customer_id+=+10背后的业务逻辑。如果是多租户应用程序，可以尝试partition。|3868224|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Seems you are dealing a very quick growing table, I should consider moving this frequent query to a cold table or replica. 

One more point is that did you consider partition by customer_id. I am not quite understand the business logic behind to query customer_id = 10. If it's multi tenancy application, try partition.

blocks|key|1553251|text|对于此查询：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1553252|SELECT+o.*
FROM+`order`+o
WHERE+o.customer_id+=+10+AND
++++++created_at+>=+NOW()+-+INTERVAL+1+DAY;|code-block|syntax|javascript|1553253|我的第一个倾向是在(customer_id,+created_at)上建立一个综合指数--正如其他人所建议的那样。|offset|length|style|CODE|1553254|但是，您似乎每天都有大量的数据和许多插入。这意味着分区加上一个索引。适当的分区应该在created_at上，可能是每天一次，以及user_id的索引。|1553255|典型的查询将访问两个最新的分区。因为您的查询集中在最近的数据上，所以这也减少了索引占用的内存，这可能是总体上的好处。|1553256|entityMap^0|0|0|9|P|0|16|A|1S|7|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@$I|V|J|W|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|X|8|@$I|Y|J|Z|K|L]|$I|10|J|11|K|L]]|9|@]|A|$]]|$1|O|3|P|5|6|7|12|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|13|8|@]|9|@]|A|$]]]|R|$]]

For this query:

<pre><code>SELECT o.*
FROM `order` o
WHERE o.customer_id = 10 AND
 created_at &gt;= NOW() - INTERVAL 1 DAY;
</code></pre>

My first inclination would be a composite index on <code>(customer_id, created_at)</code> -- as others have suggested.

But, you appear to have a lot of data and many inserts per day. That suggests partitioning plus an index. The appropriate partition would be on <code>created_at</code>, probably on a daily basis, along with an index for <code>user_id</code>.

A typical query would access the two most recent partitions. Because your queries are focused on recent data, this also reduces the memory occupied by the index, which might be an overall benefit.

blocks|key|1372733|text|这种技术应该比所有其他答案都要好，尽管可能只有很小的差距：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1372734|而不是像这样索引orders：|offset|length|style|CODE|1372735|PRIMARY+KEY(order_id)+++--+AUTO_INCREMENT
INDEX(customer_id,+...)++--+created_at,+and+possibly+others|code-block|syntax|javascript|1372736|执行此操作可将行“聚集”在一起：|1372737|PRIMARY+KEY(customer_id,+order_id)
INDEX+(order_id)+++--+to+keep+AUTO_INCREMENT+happy|1372738|然后，您可以根据需要选择以customer_id开头的更多索引。或者不是。|1372739|另一个问题--您将如何处理20K行？对于客户来说，这是一个很大的问题，尤其是人类类型的客户。如果你继续使用它，难道你不能做一个更复杂的查询，做更多的工作，返回更少的行吗？这可能会更快。|1372740|entityMap^0|0|8|6|0|0|0|0|D|B|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@$D|Y|E|Z|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|10|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|11|8|@]|9|@]|A|$]]|$1|O|3|P|5|J|7|12|8|@]|9|@]|A|$K|L]]|$1|Q|3|R|5|6|7|13|8|@$D|14|E|15|F|G]]|9|@]|A|$]]|$1|S|3|T|5|6|7|16|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|17|8|@]|9|@]|A|$]]]|V|$]]

This technique should be better than all the other answers, though perhaps by only a small amount:

Instead of <code>orders</code> being indexed thus:

<pre><code>PRIMARY KEY(order_id) -- AUTO_INCREMENT
INDEX(customer_id, ...) -- created_at, and possibly others
</code></pre>

do this to "cluster" the rows together:

<pre><code>PRIMARY KEY(customer_id, order_id)
INDEX (order_id) -- to keep AUTO_INCREMENT happy
</code></pre>

Then you can optionally have more indexes starting with <code>customer_id</code> as needed. Or not.

Another issue -- What will you do with 20K rows? That is a lot to feed to a client, especially of the human type. If you then munch on it, can't you make a more complex query that does more work, and returns fewer rows? That will probably be faster.

I need to fetch last 24 hrs data frequently and this query runs frequently.
Since this scans many rows, using it frequently, affects the database performance.

MySql execution strategy picks index on created_at and that returns 1,00,000 rows approx. and these rows are scanned one by one to filter customer_id = 10 and my final result has 20000 rows.

How can I optimize this query?

<pre><code>explain SELECT *
FROM `order`
WHERE customer_id = 10
and `created_at` &gt;= NOW() - INTERVAL 1 DAY;

id : 1
select_type : SIMPLE
table : order
partitions : NULL
type : range
possible_keys : idx_customer_id, idx_order_created_at
key : idx_order_created_at
key_len : 5
ref : NULL
rows : 103357
filtered : 1.22
Extra : Using index condition; Using where
</code></pre>

Optimize SQL to fetch 1 day data

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我需要频繁地获取最近24小时的数据，并且这个查询经常运行。由于这会扫描许多行，因此频繁使用它会影响数据库性能。MySql执行策略在created_at上选择索引，返回大约1,000,000行。这些行被逐个扫描以过滤customer_id = 10，我的最终结果是20000行。如何优化此查询？explain SELECT...

问优化SQL以获取1天的数据
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问优化SQL以获取1天的数据EN