使用WHERE语句对SQL进行基础性的优化

使用WHERE语句对SQL进行基础性的优化

从 JarrodGoddard.com 作者： Jarrod Goddard

【我首先要抱怨一下这个该死的CSDN博客，我辛辛苦苦翻译完成的东西，提交上去居然丢了，想杀人了】

【还有这该死的博客升级，升的是一塌糊涂，考虑换地方ING.....................】

我最近工作于一个拍卖网站的项目，由于在后期发现站点的数据库出现了过载现象，于是我想到了对数据库查询进行优化

使用WHERE语句对SQL进行优化

经过我的研究，发现主页上的一条语句执行居然要花近5s不可忍受，于是我就查看这条SQL语句了

SELECT I.itemId IitemId, max(B.amount) bidAmount, I.name, max(B.bidDate) lastBid, I.value, Ca.name CaName, Ca.categoryId, I.pictureThumbnail, Co.logoThumbnail, Co.name CoName, Co.companyId CoCompanyId, B.username
FROM item I
LEFT JOIN category Ca ON Ca.categoryId=I.categoryId
LEFT JOIN company Co ON Co.companyId=I.companyId
LEFT JOIN (SELECT itemId, amount, bidDate, username FROM bid B LEFT JOIN account A ON A.accountId=B.accountId ORDER BY amount DESC) B ON B.itemId=I.itemId
GROUP BY I.itemId
ORDER BY B.bidDate DESC
LIMIT 20

其实在刚开始的时候由于数据量比较少用户使用的时候也没有问题，但是到最后，成交表里有接近20000条数据，这个时候查询的速度就非常 慢了

我做了什么呢，不过是加了条WHERE 语句，筛选最近一小时内的记录

SELECT I.itemId IitemId, max(B.amount) bidAmount, I.name, max(B.bidDate) lastBid, I.value, Ca.name CaName, Ca.categoryId, I.pictureThumbnail, Co.logoThumbnail, Co.name CoName, Co.companyId CoCompanyId, B.username
FROM item I
LEFT JOIN category Ca ON Ca.categoryId=I.categoryId
LEFT JOIN company Co ON Co.companyId=I.companyId
LEFT JOIN (SELECT itemId, amount, bidDate, username FROM bid B LEFT JOIN account A ON A.accountId=B.accountId ORDER BY amount DESC) B ON B.itemId=I.itemId
WHERE B.bidDate>’2007-11-20 12:00:20′
GROUP BY I.itemId
ORDER BY B.bidDate DESC
LIMIT 20

为什么可以这么做呢？因为流量非常大，我可以有充分的自信1小时内绝对有20比单子

当然你也可以做索引优化查询的时间。
【补充：我原来的翻译比这要翔实多了，怪只能怪万恶的CSDN 博客系统】

---

 

Basic SQL Query Optimization with WHERE Clauses

从 JarrodGoddard.com 作者： Jarrod Goddard

I recently worked on an auction site that was very database-heavy. Because of the amount of traffic and number of connections needed per page, the database began to get overwhelmed. One of the first steps to improve performance of your site if it is getting bogged down by traffic is to take a look at your database connections and query optimization – you may also want to upgrade server hardware, which I would always recommend in complement to optimizing code.

Optimize SQL Queries With Where Clauses

The first query I noticed was a query on the home page of the auction that listed the most recent bids in the auction. The query grabbed the item details, and highest bidding information from the database for the 20 most recent bids in the site. I grabbed the twenty most recent records from the bid table and then join a bunch of other tables to get the item, company, and user details. Here it is:

SELECT I.itemId IitemId, max(B.amount) bidAmount, I.name, max(B.bidDate) lastBid, I.value, Ca.name CaName, Ca.categoryId, I.pictureThumbnail, Co.logoThumbnail, Co.name CoName, Co.companyId CoCompanyId, B.username
FROM item I
LEFT JOIN category Ca ON Ca.categoryId=I.categoryId
LEFT JOIN company Co ON Co.companyId=I.companyId
LEFT JOIN (SELECT itemId, amount, bidDate, username FROM bid B LEFT JOIN account A ON A.accountId=B.accountId ORDER BY amount DESC) B ON B.itemId=I.itemId
GROUP BY I.itemId
ORDER BY B.bidDate DESC
LIMIT 20

When I grabbed the database and code from the server and set it up on my laptop for testing, I found that running this query through mysql took 4.78 seconds! That meant every time the page would load the page would take 4.78 seconds to grab the data it needed from the database, among other database queries included on the page. Keep in mind, that MySQL will cache query results based on the settings in your my.cnf file, so if the results from this query didn’t change, it would be lightning quick for the next person to load the page, however, any time a new bid was placed, these results would change and the cached query would not be used. During peak levels of the auction we had close to a bid every 15-30 seconds so this was definitely slowing down the page and being the home page, a lot of users noticed. There was no problem at all when the auction started, but because the bid table had close to 20000 bids by the end of the auction it really slowed down.

What did I do to speed it up? All I added was a simple WHERE clause – WHERE B.bidDate>’2007-11-20 12:00:20′ – dynamically generating the date stamp to 1 hour before the current time.

SELECT I.itemId IitemId, max(B.amount) bidAmount, I.name, max(B.bidDate) lastBid, I.value, Ca.name CaName, Ca.categoryId, I.pictureThumbnail, Co.logoThumbnail, Co.name CoName, Co.companyId CoCompanyId, B.username
FROM item I
LEFT JOIN category Ca ON Ca.categoryId=I.categoryId
LEFT JOIN company Co ON Co.companyId=I.companyId
LEFT JOIN (SELECT itemId, amount, bidDate, username FROM bid B LEFT JOIN account A ON A.accountId=B.accountId ORDER BY amount DESC) B ON B.itemId=I.itemId
WHERE B.bidDate>’2007-11-20 12:00:20′
GROUP BY I.itemId
ORDER BY B.bidDate DESC
LIMIT 20

Because I knew there would be bids every few seconds, I could rely on their being plenty of bids each hour to fill up the 20 most recently bid on items. The difference in query time? Adding the WHERE clause reduced the query speed to 0.45 seconds. That’s a huge increase in performance – 4.78 seconds to 0.45 seconds.

I wanted to show the increase in performance by optimizing the SQL query here, but you could also take a look at what indexes you have setup on your database tables to help further improve performance of your database.