Mysql查詢語句優化技能

Mysql查詢語句優化技能。本站提示廣大學習愛好者：（Mysql查詢語句優化技能）文章只能為提供參考，不一定能成為您想要的結果。以下是Mysql查詢語句優化技能正文

索引優化，查詢優化，查詢緩存，辦事器設置優化，操作體系和硬件優化，運用層面優化（web辦事器，緩存）等等。這裡的記載的優化技能更實用於開辟人員，都是從收集上搜集和本身整頓的，重要是查詢語句下面的優化，其它層面的優化技能在此不做記載。

查詢的開支目標：

履行時光

檢討的行數

前往的行數

樹立索引的幾個原則：

(1)、公道的樹立索引可以或許加快數據讀取效力，不公道的樹立索引反而會拖慢數據庫的呼應速度。

(2)、索引越多，更新數據的速度越慢。

(3)、盡可能在采取MyIsam作為引擎的時刻應用索引（由於MySQL以BTree存儲索引），而不是InnoDB。但MyISAM不支撐

Transcation。

(4)、當你的法式和數據庫構造/SQL語句曾經優化到沒法優化的水平，而法式瓶頸其實不能順遂處理，那就是應當斟酌應用諸如memcached如許的散布式緩存體系的時刻了。

(5)、習氣和強制本身用EXPLAIN來剖析你SQL語句的機能。

1、count的優化

好比：盤算id年夜於5的城市

select count(*) from world.city where id > ;
select (select count() from world.city) – count() from world.city where id <= ;

a語句當行數跨越11行的時刻須要掃描的行數比b語句要多， b語句掃描了6行，此種情形下，b語句比a語句更有用率。當沒有where語句的時刻直接select count(*) from world.city如許會更快，由於mysql老是曉得表的行數。

2、防止應用不兼容的數據類型

例如float和int、char和varchar、binary和varbinary是不兼容的。數據類型的不兼容能夠使優化器沒法履行一些原來可以停止的優化操作。

在法式中，包管在完成功效的基本上，盡可能削減對數據庫的拜訪次數；經由過程搜刮參數，盡可能削減對表的拜訪行數,最小化成果集，從而加重收集累贅；可以或許離開的操作盡可能離開處置，進步每次的呼應速度；在數據窗口應用SQL時，盡可能把應用的索引放在選擇的首列；算法的構造盡可能簡略；在查詢時，不要過量地應用通配符如 SELECT * FROM T1語句，要用到幾列就選擇幾列如：SELECT COL1,COL2 FROM T1；在能夠的情形下盡可能限制盡可能成果集行數如：SELECT TOP 300 COL1,COL2,COL3 FROM T1,由於某些情形下用戶是不須要那末多的數據的。不要在運用中應用數據庫游標，游標長短常有效的對象，但比應用慣例的、面向集的SQL語句須要更年夜的開支；依照特定次序提取數據的查找。

3、索引字段長進交運算會使索引掉效

盡可能防止在WHERE子句中對字段停止函數或表達式操作，這將招致引擎廢棄應用索引而停止全表掃描。如：

SELECT * FROM T1 WHERE F1/2=100 應改成: SELECT * FROM T1 WHERE F1=100*2

4、防止應用!=或＜＞、IS NULL或IS NOT NULL、IN ，NOT IN等如許的操作符

由於這會使體系沒法應用索引,而只能直接搜刮表中的數據。例如: SELECT id FROM employee WHERE id != “B%” 優化器將沒法經由過程索引來肯定將要射中的行數,是以須要搜刮該表的一切行。在in語句中能用exists語句取代的就用exists.

5、盡可能應用數字型字段

一部門開辟人員和數據庫治理人員愛好把包括數值信息的字段

設計為字符型，這會下降查詢和銜接的機能，並會增長存儲開支。這是由於引擎在處置查詢和銜接回逐一比擬字符串中每個字符，而關於數字型而言只須要比擬一次就夠了。

6、公道應用EXISTS,NOT EXISTS子句

以下所示：

 SELECT SUM(T1.C1) FROM T1 WHERE (SELECT COUNT(*)FROM T2 WHERE T2.C2=T1.C2>0)
 SELECT SUM(T1.C1) FROM T1WHERE EXISTS(SELECT * FROM T2 WHERE T2.C2=T1.C2)

二者發生雷同的成果，然則後者的效力明顯要高於前者。由於後者不會發生年夜量鎖定的表掃描或是索引掃描。假如你想校驗內外能否存在某條記載，不要用count(*)那樣效力很低，並且糟蹋辦事器資本。可以用EXISTS取代。如：

IF (SELECT COUNT() FROM table_name WHERE column_name = ‘xxx')可以寫成：IF EXISTS (SELECT FROM table_name WHERE column_name = ‘xxx')

7、 可以或許用BETWEEN的就不要用IN

8、 可以或許用DISTINCT的就不消GROUP BY

9、盡可能不要用SELECT INTO語句。

SELECT INTO 語句會招致表鎖定，阻攔其他用戶拜訪該表

10、 需要時強迫查詢優化器應用某個索引

SELECT * FROM T1 WHERE nextprocess = 1 AND processid IN (8,32,45) 改成：
SELECT * FROM T1 (INDEX = IX_ProcessID) WHERE nextprocess = 1 AND processid IN (8,32,45)

則查詢優化器將會強行應用索引IX_ProcessID 履行查詢。

11、清除對年夜型表行數據的次序存取

雖然在一切的檢討列上都有索引，但某些情勢的WHERE子句強制優化器應用次序存取。如：

SELECT * FROM orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008

處理方法可使用並集來防止次序存取：

SELECT * FROM orders WHERE customer_num=104 AND order_num>1001 UNION SELECT * FROM orders WHERE order_num=1008

如許就可以應用索帶路徑處置查詢。【jacking 數據成果集許多，但查詢前提限制後成果集不年夜的情形下，前面的語句快】

12、盡可能防止在索引過的字符數據中，應用非打頭字母搜刮。

這也使得引擎沒法應用索引

見以下例子：

SELECT * FROM T1 WHERE NAME LIKE ‘%L%'
SELECT * FROM T1 WHERE SUBSTING(NAME,2,1)='L'
SELECT * FROM T1 WHERE NAME LIKE ‘L%'

即便NAME字段建有索引，前兩個查詢仍然沒法應用索引完成加速操作，引擎不能不對全表一切數據逐條操作來完成義務。而第三個查詢可以或許應用索引來加速操作，不要習氣性的應用 ‘%L%'這類方法(會招致全表掃描)，假如可使用`L%'絕對來講更好;

十3、固然UPDATE、DELETE語句的寫法根本固定，然則照樣對UPDATE語句給點建議

(1). 盡可能不要修正主鍵字段。

(2). 當修正VARCHAR型字段時，盡可能應用雷同長度內容的值取代。

(3). 盡可能最小化關於含有UPDATE觸發器的表的UPDATE操作。

(4). 防止UPDATE將要復制到其他數據庫的列。

(5). 防止UPDATE建有許多索引的列。

(6). 防止UPDATE在WHERE子句前提中的列。

十4、能用UNION ALL就不要用UNION

UNION ALL不履行SELECT DISTINCT函數，如許就會削減許多不用要的資本

在跨多個分歧的數據庫時應用UNION是一個風趣的優化辦法，UNION從兩個互不聯系關系的表中前往數據，這就意味著不會湧現反復的行，同時也必需對數據停止排序，我們曉得排序長短常消耗資本的，特殊是對年夜表的排序。

UNION ALL可以年夜年夜加速速度，假如你曾經曉得你的數據不會包含反復行，或許你不在意能否會湧現反復的行，在這兩種情形下應用UNION ALL更合適。另外，還可以在運用法式邏輯中采取某些辦法防止湧現反復的行，如許UNION ALL和UNION前往的成果都是一樣的，但UNION ALL不會停止排序。

十5、字段數據類型優化

(1). 防止應用NULL類型：NULL關於年夜多半數據庫都須要特別處置，MySQL也不破例，它須要更多的代碼，更多的檢討和特別的索引邏輯，有些開辟人員完整沒無意識到，創立表時NULL是默許值，但年夜多半時刻應當應用NOT NULL，或許應用一個特別的值，如0，-1作為默許值。

(2). 盡量應用更小的字段，MySQL從磁盤讀取數據後是存儲到內存中的，然後應用cpu周期和磁盤I/O讀取它，這意味著越小的數據類型占用的空間越小，從磁盤讀或打包到內存的效力都更好，但也不要太甚執著減小數據類型，如果今後運用法式產生甚麼變更就沒有空間了。修正表將須要重構，直接地能夠惹起代碼的轉變，這是很頭疼的成績，是以須要找到一個均衡點。

(3). 優先應用定長型

十7、關於年夜數據量limit散布的優化（當偏移量特殊年夜時，limit效力會異常低）

附上一個進步limit效力的簡略技能，在籠罩索引(籠罩索援用淺顯的話講就是在select的時刻只用去讀取索引而獲得數據，無需停止二次select相干表)長進行偏移，而不是對全行數據停止偏移。可以將從籠罩索引上提掏出來的數據和全行數據停止聯接，然後獲得須要的列，會更有用率，看看上面的查詢：

mysql> select film_id, description from sakila.film order by title limit 50, 5;

假如表異常年夜，這個查詢最好寫成上面的模樣：

mysql> select film.film_id, film.description from sakila.film
inner join(select film_id from sakila.film order by title liimit 50,5) as film usinig(film_id);

十8、法式中假如一次性對統一個表拔出多條數據

好比以下語句：

insert into person(name,age) values(‘xboy', 14);
insert into person(name,age) values(‘xgirl', 15);
insert into person(name,age) values(‘nia', 19);

把它拼成一條語句履行效力會更高.

insert into person(name,age) values(‘xboy', 14), (‘xgirl', 15),(‘nia', 19);

十9、不要在選擇的欄位上放置索引，這是有意義的。

應當在前提選擇的語句上公道的放置索引，好比where，order by

SELECT id,title,content,cat_id FROM article WHERE cat_id = 1;

下面這個語句，你在id/title/content上放置索引是毫有意義的，對這個語句沒有任何優化感化。然則假如你在外鍵cat_id上放置一個索引，那感化就相當年夜了。

二10、ORDER BY語句的MySQL優化

(1). ORDER BY + LIMIT組合的索引優化。假如一個SQL語句形如：

SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort] LIMIT [offset],[LIMIT];

這個SQL語句優化比擬簡略，在[sort]這個欄位上樹立索引便可。

(2). WHERE + ORDER BY + LIMIT組合的索引優化，形如：

SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [VALUE] ORDER BY [sort] LIMIT [offset],[LIMIT];

這個語句，假如你依然采取第一個例子中樹立索引的辦法，固然可以用到索引，然則效力不高。更高效的辦法是樹立一個結合索引(columnX,sort)

(3). WHERE + IN + ORDER BY + LIMIT組合的索引優化，形如：

SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] IN ([value1],[value2],…) ORDER BY [sort] LIMIT [offset],[LIMIT];

這個語句假如你采取第二個例子中樹立索引的辦法，會得不到預期的後果（僅在[sort]上是using index，WHERE那邊是using where;using filesort），來由是這裡對應columnX的值對應多個。

今朝哥還木有找到比擬優良的方法，期待高手指教。

(4).WHERE+ORDER BY多個欄位+LIMIT，好比:

SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10;

關於這個語句，年夜家能夠是加一個如許的索引:(x,y,uid)。但現實上更好的後果是(uid,x,y)。這是由MySQL處置排序的機制作成的。