(3)mysql優化之sql語句優化

(3)mysql優化之sql語句優化

概述

該篇主要介紹一些常用的sql優化技巧

sql優化

1.select * from table_name where;

建議將*改為需要的列。這對速度不會有明顯的影響，主要考慮節省內存。

2.like語句

一般情況下不鼓勵使用like操作，如果非使用不可，如何使用也是一個問題。like “%aaa%” 不會使用索引而like “aaa%”可以使用索引。

3.不要在列上進行運算，無法運用索引

select * from users where YEAR(adddate)<2007;

將在每個行上進行運算，這將導致索引失效而進行全表掃描，因此我們可以改成

select * from users where adddate<‘2007-01-01’;

4.不使用NOT IN和<>操作

NOT IN和<>操作都不會使用索引將進行全表掃描。NOT IN可以NOT EXISTS代替，id<>3則可使用id>3 or id<3來代替。

5.為查詢緩存優化你的查詢

當有很多相同的查詢被執行了多次的時候，這些查詢結果會被放到一個緩存中，這樣，後續的相同的查詢就不用操作表而直接訪問緩存結果了。
這裡最主要的問題是，對於程序員來說，這個事情是很容易被忽略的。因為，我們某些查詢語句會讓MySQL不使用緩存。請看下面的示例：


6.千萬不要 ORDER BY RAND()

這樣使用只讓你的數據庫的性能呈指數級的下降。這裡的問題是：MySQL會不得 不去執行RAND()函數(很耗CPU時間)，而且這是為了每一行記錄去記行，然後再對其排序。就算是你用了Limit 1也無濟於事(因為要排序)。

7.limit huge_num,offset

在分頁的時候，當huge_num比較大的時候，這樣分頁有一個很大的性能問題。我用‘limit 1000,20;’去1000-1020行的記錄，會遍歷1020行，然後前1000條記錄都會被拋棄。

SELECT * FROM payment ORDER BY rental_id LIMIT 100,10;

思路一：使用子查詢,按照索引分頁後回表方式改寫sql

SELECT * FROM payment a INNER JOIN (SELECT payment_id FROM payment ORDER BY rental_id LIMIT 100,10)b ON a.payment_id = b.payment_id;

思路二：使用between … and…

#不建議，用處不大
SELECT * FROM payment WHERE rental_id BETWEEN 100 AND 110 ORDER BY rental_id;

思路三：和開發人員協商，翻頁過程通過增加一個參數last_page_record，來記錄上一頁最後一行排序編號，然後通過該參數范圍查找下一頁的記錄。

8.使用count(*),而不是count(id);

9.批量插入

insert into t(id,name) values(1,'test1');
insert into t(id,name) values(2,'test2');
insert into t(id,name) values(3,'test3');

#改為

insert into t(id,name) values(1,'test1'),(2,'test2'),(3,'test3');

10.order by 減少filesort排序，通過索引直接返回有序數據