Mysql 查询性能 -
我有以下 Mysql 查询
explain SELECT count(*) as assetCount
FROM MdhRawAsset m
where sysCreationDate > date_add(now(), INTERVAL -1 DAY)
AND sysCreationDate <= now()
AND itemType = 'TS';
结果：
| id | select_type | table | type |possible_keys
| 1 | SIMPLE | m | range | MdhRawAsset_on_sysCreationDate, MdhRawAsset_itemType
---------------------------------------------------------------------
|Key |Key_len | ref | rows | Extra |
MdhRawAsset_on_sysCreationDate| 8 | NULL | 53269 | Using where |
问题：
- 我如何知道该查询的执行时间？
- 表
MdhRawAsset包含3750万条数据，是否有更好的方法来编写这个查询？
I have the following Mysql query
explain SELECT count(*) as assetCount
FROM MdhRawAsset m
where sysCreationDate > date_add(now(), INTERVAL -1 DAY)
AND sysCreationDate <= now()
AND itemType = 'TS';
Results :
| id | select_type | table | type |possible_keys
| 1 | SIMPLE | m | range | MdhRawAsset_on_sysCreationDate, MdhRawAsset_itemType
---------------------------------------------------------------------
|Key |Key_len | ref | rows | Extra |
MdhRawAsset_on_sysCreationDate| 8 | NULL | 53269 | Using where |
Questions :
- How will I know the execution time of this query ?
- The table
MdhRawAssetcontains 37.5 million of data, is there a better way to write this query ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
查询的执行时间取决于太多问题，无法确定需要多长时间。服务器上的负载、行数等。您能做的最好的事情就是在典型负载下运行它，看看需要多长时间。
您似乎设置了正确的索引，所以我没有看到更好的优化方法，但其他人可能比我更了解。
The execution time of a query depends on too many issues to determine how long it will take. The load on the server, the number of rows, etc. The best you can do is run it in a typical load and see how long it takes.
You seem to have the correct indexes set up, so I do not see a better way to optimize, but someone else may know better than I do.
就像艾伦说的
尝试 count(id) 应该会更快。它取决于数据库引擎，但根据我的经验，不使用 * 总是更快。
Like Alan said
Try count(id) instead is should be faster. It depend on the database engine but in my experience not using * is always quicker.
首先，执行时间取决于表、服务器、负载以及不同级别的缓存被预热。
为了提高特定查询的性能，复合索引
......
KEY itemType_sysCreationDate (itemType, sysCreationDate)
...
表上将允许仅使用索引执行查询。没有比这更好的了。请注意，顺序很重要。
另外，用应用程序层中生成的显式日期字符串替换“now()”将允许 mysql 使用它的查询缓存（如果它之前见过该查询），并且该表自上次执行以来尚未更新，并且该查询仍然在它的缓存中。我并不是提倡MySQL 的查询缓存。 ;)
Firstly, the execution time depends on the table, the server, the load, and what caches at the different levels are warmed up.
w.r.t. improving the performance of that specific query, a compound index on
...
KEY itemType_sysCreationDate (itemType, sysCreationDate)
...
on the table will allow that query to be executed using only indexes. Can't get much better than that. note that the order is important.
Also, replacing the 'now()' with an explicit date string generated in the application layer will allow mysql to use it's query cache if it's seen the query before, AND the table hasn't been updated since the last execution, AND the query is still in it's cache. Not that I advocate for MySQL's query cache. ;)
目前执行此查询需要多长时间？你确实有很多数据。
如果你不能真正改进查询，也许你需要一个稍微不同的解决方案 -
在问题上投入更多硬件 - 查看瓶颈在哪里，并尝试升级硬件的该部分
使用汇总表 - 如果这是经常绘制的报告，那么使用数据仓库技术来维护汇总表可能会有所帮助。您可以动态更新汇总表（当事务正在进行时）或定期更新汇总表（如果不需要最新信息）。
请注意，当您将数据放入汇总表时，不必总是进行完整计数 - 例如。您在事务表中添加的每条新记录，只需执行existing_summary_count=existing_summary_count + 1，您就可以获得摘要的增量值，而无需真正执行昂贵的查询。
How long is it taking currently to execute this query? You do have a lot of data.
If you cannot really improve the query, maybe you need a slightly different solution -
Throw more hardware at the problem - see where's the bottleneck and try to upgrade that part of your hardware
Use summary tables - if this is a report that is drawn often, then it might help to use datawarehousing techniques to maintain summary tables. You can either update the summary tables on the fly (when the transaction is going on) or periodically (if up-to-date info is not needed).
Note that when you put data into summary tables, its not necessary to do a full count always - for eg. every new record you add in the transaction table, just do existing_summary_count = existing_summary_count + 1 and you get the incremented value for the summary without really performing an expensive query.
您一定正在考虑来自 MS-SQL 服务器的查询计划，如果没记错的话，它提到了一些关于时间的事情。事实是，无论如何，这都是一个废话。要确定查询是否会尽可能快地执行，您需要结合使用
show index from 和explain ...。使用
itemType和sysCreationDate的索引，您的查询速度将是最快的。这将允许对一系列连续的索引条目进行计数。You must be thinking of the query plan thing from MS-SQL server that, if memory serves, mentions something about time. Fact is, that's a crapshoot anyway. To figure out if a query will execute as fast as reasonably possibly, you need a combination of
show index from <table>andexplain ....Your query would be fastest with an index that of
itemTypeandsysCreationDate. That would allow counting a sequential series of index entries.