MYSQL查询执行性能问题 [英] MYSQL query execution performance issue

问题描述

HI，



我正在使用c＃，后端是Mysql db。



我有3个db中的表，第1表有1.5万条记录，第2表有1.75万条记录，第3表有21万条记录。我需要使用上面3个表的输出，所以我使用内连接加入了3个表，我得到了1.5万条记录的输出，但它需要 8小时的时间。



我在加入桌子时使用了主键，任何人都可以建议我提高性能。



谢谢！



我尝试过：



选择Col1，col2， ...来自table1的coln

在table1.Id = table2.Id上的JOIN table2

在table1.Id = table3.Id

I am using c#, backend is Mysql db.

I have 3 tables in db, 1st table has 1.5 lakh records, 2nd table has 1.75 lakh records and 3rd table has 21 lakh records. i need the output using above 3 tables, so i joined above 3 table using inner join and i got the output with 1.5 lakh records, but it takes 8 hours of time.

I used primary keys in joining tables, can anyone suggest me to increase the performance.

Thanks!

What I have tried:

select Col1, col2,...coln from table1
JOIN table2 on table1.Id = table2.Id
JOIN table3 on table1.Id = table3.Id

推荐答案

我同意理查德，你的第一个问题是你的设计。没有要求您可以一次性显示所有150,000条记录。



但是我们假设您确定它是150k记录或破产。



我的第一个评论是要考虑分页你的数据，我认为mysql有LIMIT的概念（我在我的mysql上生锈了）但是允许你做一些像 SELECT * FROM< Table> LIMIT 5,10 这将让你从第5条记录开始获得5条记录。我会告诉你，没有一个用户会欣赏必须查看150k记录才能找到他们想要的具体内容。你最好将这些数据过滤到相关的结果集而不是尝试并立即显示，如果他们必须自己手动过滤这些数据，你的用户会讨厌你。



然后你需要查看你的服务器硬件。 8小时内的150k记录是荒谬的。我可以在sql server中运行类似的查询（在不同的db平台中授权），并在几秒钟内获得带有三个表连接的150k记录。对于150k记录，8小时，你是否在一个500mb RAM的单核上运行你的数据库？所以看看你的硬件问题



看看你在查询什么。这么多记录需要8个小时，你必须拔出10,000列或者什么......你是否正在做一些疯狂的聚合，你可以通过以所需的聚合方式存储数据来卸载？例如，如果您总是添加4列来创建总计，为什么不只是添加第五列Total并将其存储在那里，这样您每次查询时都无需进行数学计算。



要继续上一条评论，您还需要查看您的查询，以查看您的查询中可以调整的内容。你在不必要地进行子查询吗？你可以使用临时表进行一些连接吗？如果要向模式中添加更多列，是否可能不需要进行某些查询？我认为mysql有一个名为 EXPLAIN 的关键字，您可以使用该关键字在运行时对您的查询进行分析，以便分析发生的情况。



我想最后，如果你需要帮助，你需要发布一个示例模式和你的查询，以便那些想要的人可以尝试复制你的问题。如果没有这一点，我们所能做的就是猜测并指出你可能会看到的方向，但没有那个......你所做的只是去看医生，告诉他们你的病了，并期望他们在没有诊断的情况下诊断你检查了你。

I agree with Richard, your first issue with this is your design. There is no requirement that you can have the you would absolutely need to show all 150,000 records all at once.

But lets assume you are determined that it is 150k records or bust.

My first comment would be to look into paginating your data, i think mysql has the concept of LIMIT (I am rusty on my mysql) but would allow you to do something like SELECT * FROM <Table> LIMIT 5,10 which would let you get 5 records starting with the 5th record. I will tell you, none of your users are going to appreciate having to look at 150k records to try and find specifics of what they want. You are better to filter this data to the relevant result set than try and show it all at once, your users will hate you if they have to manually filter that data on their own.

Then you need to look at your servers hardware. 150k records in 8 hours is ridiculous. I can run a similar query in sql server (granted in different db platform) and get 150k records with a three table join in a few seconds. 8 hours for 150k records, are you running your DB on a single core with 500mb of RAM? So look into your hardware problems

Look into what you are querying. 8 hours for that many records, you must be pulling out 10,000 columns or something...are you doing some crazy aggregation that you can maybe offload by storing the data in the required aggregated manner to begin with? For example, If you always add 4 columns to create a total, why not just add a fifth column called "Total" and store that total there so your not having to do the math every time you query it out.

To go along with the last comment, you need to look at your query as well to see what, within your query, can be tuned. Are you doing sub queries unnecessarily? Could you utilize temp tables for some of your joins? Could some of your query that you are doing be unnecessary if you were to add more columns to your schema? I think mysql has the keyword called EXPLAIN that you can use in order to have your query profiled as it runs so you can analyze what is going on.

And I suppose finally, if you want help you need to post a sample schema and your queries so those that want to, can try and replicate your issue your having. Without that all we can do is guess and point you in directions of what you could possibly look at but without that...all you've done is gone to a doctor, tell them your sick, and expect them to diagnose you without having examined you.

这篇关于MYSQL查询执行性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！