sql語句優化原則與百萬數據優化方案

1、使用索引來更快地遍歷表。
缺省情況下建立的索引是非群集索引，但有時它並不是最佳的。在非群集索引
下，數據在物理上隨機存放在數據頁上。合理的索引設計要建立在
對各種查詢的分析和預測上。一般來說：
a.有大量重復值、且經常有范圍查詢（ > ,< ，> =,< =）和order by、group by發生的列，可考
慮建立群集索引；
b.經常同時存取多列，且每列都含有重復值可考慮建立組合索引；
c.組合索引要盡量使關鍵查詢形成索引覆蓋，其前導列一定是使用最頻繁的列。索引雖有助於提高性能但不是索引越多越好，恰好相反過多的索引會導致系統低效。用戶在表中每加進一個索引，維護索引集合就要做相應的更新工作。
2、在海量查詢時盡量少用格式轉換。
3、ORDER BY和GROPU BY使用ORDER BY和GROUP BY短語，任何一種索引都有助於SELECT的性能提高。
4、任何對列的操作都將導致表掃描，它包括數據庫教程函數、計算表達式等等，查詢時要盡可能將操作移至等號右邊。
5、IN、OR子句常會使用工作表，使索引失效。如果不產生大量重復值，可以考慮把子句拆開。拆開的子句中應該包含索引。

Mysql的優化原則2：
1、只要能滿足你的需求，應盡可能使用更小的數據類型：例如使用MEDIUMINT代替INT
2、盡量把所有的列設置為NOT NULL，如果你要保存NULL，手動去設置它，而不是把它設為默認值。
3、盡量少用VARCHAR、TEXT、BLOB類型
4、如果你的數據只有你所知的少量的幾個。最好使用ENUM類型
5、正如graymice所講的那樣，建立索引。

方法二

優化前：A表數據造成冗余
SELECT `T`.`img_id`, `T`.`thumb_path`
     FROM `gallery_photofiles` P
     LEFT JOIN `gallery_thumbs` T ON `T`.`img_id`=`P`.`img_id` and T.thumb_type='11'
     WHERE `P`.`owner_user_id` = '1'
     AND P.img_id in (select A.img_id from `gallery_album_img_link` A WHERE A.img_id)
優化後：count(*)大大提升速度
SELECT `T`.`img_id`, `T`.`thumb_path`
     FROM `gallery_photofiles` P
     LEFT JOIN `gallery_thumbs` T ON `T`.`img_id`=`P`.`img_id` and T.thumb_type='11'
     WHERE `P`.`owner_user_id` = '1'
     AND (select count(*) from `gallery_album_img_link` A WHERE A.img_id=P.img_id)<1

一直以為mysql教程隨機查詢幾條數據，就用

SELECT * FROM `table` ORDER BY RAND() LIMIT 5
就可以了。
但是真正測試一下才發現這樣效率非常低。一個15萬余條的庫，查詢5條數據，居然要8秒以上

查看官方手冊，也說rand()放在ORDER BY 子句中會被執行多次，自然效率及很低。

You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times.

搜索Google，網上基本上都是查詢max(id) * rand()來隨機獲取數據。

SELECT *
FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `table`)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id ASC LIMIT 5;
但是這樣會產生連續的5條記錄。解決辦法只能是每次查詢一條，查詢5次。即便如此也值得，因為15萬條的表，查詢只需要0.01秒不到。

上面的語句采用的是JOIN，mysql的論壇上有人使用

SELECT *
FROM `table`
WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` )
ORDER BY id LIMIT 1;
我測試了一下，需要0.5秒，速度也不錯，但是跟上面的語句還是有很大差距。總覺有什麼地方不正常。

於是我把語句改寫了一下。

SELECT * FROM `table`
WHERE id >= (SELECT floor(RAND() * (SELECT MAX(id) FROM `table`)))
ORDER BY id LIMIT 1;
這下，效率又提高了，查詢時間只有0.01秒

最後，再把語句完善一下，加上MIN(id)的判斷。我在最開始測試的時候，就是因為沒有加上MIN(id)的判斷，結果有一半的時間總是查詢到表中的前面幾行。
完整查詢語句是：

SELECT * FROM `table`
WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`)) + (SELECT MIN(id) FROM `table`)))
ORDER BY id LIMIT 1;SELECT *
FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`))+(SELECT MIN(id) FROM `table`)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id LIMIT 1;