MySQL數(shù)據(jù)庫怎么加快查詢速度，優(yōu)化查詢效率，主要原則就是應盡量避免全表掃描，應該考慮在where及order by 涉及的列上建立索引。

建立索引不是建的越多越好，原則是：

第一：一個表的索引不是越多越好，也沒有一個具體的數(shù)字，根據(jù)以往的經(jīng)驗，一個表的索引最多不能超過6個，因為索引越多，對update和insert操作也會有性能的影響，涉及到索引的新建和重建操作。

第二：建立索引的方法論為：

• 多數(shù)查詢經(jīng)常使用的列；
• 很少進行修改操作的列；
• 索引需要建立在數(shù)據(jù)差異化大的列上
• 利用以上的基礎我們討論一下如何優(yōu)化sql.

1、MySQY語句模型結構優(yōu)化指導

a. ORDER BY + LIMIT組合的索引優(yōu)化

如果一個SQL語句形如：

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

這個SQL語句優(yōu)化比較簡單，在[sort]這個欄位上建立索引即可。

b. WHERE + ORDER BY + LIMIT組合的索引優(yōu)化

如果一個SQL語句形如：

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

這個語句，如果你仍然采用第一個例子中建立索引的方法，雖然可以用到索引，但是效率不高。更高效的方法是建立一個聯(lián)合索引(columnX,sort)

c. WHERE+ORDER BY多個欄位+LIMIT

如果一個SQL語句形如：

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

對于這個語句，大家可能是加一個這樣的索引:(x,y,uid)。但實際上更好的效果是(uid,x,y)。這是由MySQL處理排序的機制造成的。

2、復合索引(形如(x,y,uid)索引的索引)

先看這樣一條語句這樣的：

select* from users where area ='beijing' and age=22;

如果我們是在area和age上分別創(chuàng)建索引的話，由于mysql查詢每次只能使用一個索引，所以雖然這樣已經(jīng)相對不做索引時全表掃描提高了很多效率，但是如果area，age兩列上創(chuàng)建復合索引的話將帶來更高的效率。

在使用索引字段作為條件時，如果該索引是復合索引，那么必須使用到該索引中的第一個字段作為條件時才能保證系統(tǒng)使用該索引，否則該索引將不會被使用，并且應盡可能的讓字段順序與索引順序相一致。

例如我們建立了一個這樣的索引（area,age,salary），那么其實相當于創(chuàng)建了（area,age,salary）,(area,age),(area)三個索引，這樣稱為最佳左前綴特性。

3、like語句優(yōu)化

SELECT id FROM A WHERE name like ‘%abc%'

由于abc前面用了“%”，因此該查詢必然走全表查詢，除非必要，否則不要在關鍵詞前加%，優(yōu)化成如下

SELECT id FROM A WHERE name like ‘a(chǎn)bc%'

4、where子句使用 ！= 或 <> 操作符優(yōu)化

在where子句中使用 ！= 或 <>操作符，索引將被放棄使用，會進行全表查詢。

如SQL：

SELECT id FROM A WHERE ID != 5 

優(yōu)化成：

SELECT id FROM A WHERE ID>5 OR ID<5

5、where子句中使用 IS NULL 或 IS NOT NULL 的優(yōu)化

在where子句中使用 IS NULL 或 IS NOT NULL 判斷，索引將被放棄使用，會進行全表查詢。

如SQL:SELECT id FROM A WHERE num IS NULL 優(yōu)化成num上設置默認值0，確保表中num沒有null值，然后SQL為：

SELECT id FROM A WHERE num=0

6、where子句使用or的優(yōu)化

很多時候使用union all 或 nuin(必要的時候)的方式替換“or”會得到更好的效果。where子句中使用了or,索引將被放棄使用。

如SQL：

SELECT id FROM A WHERE num =10 or num = 20 

優(yōu)化成：

SELECT id FROM A WHERE num = 10 union all SELECT id FROM A WHERE num=20

7、where子句使用IN 或 NOT IN的優(yōu)化

in和not in 也要慎用，否則也會導致全表掃描。

方案一：between替換in

如SQL：

SELECT id FROM A WHERE num in(1,2,3) 

優(yōu)化成：

SELECT id FROM A WHERE num between 1 and 3

方案二：exist替換in

如SQL：

SELECT id FROM A WHERE num in(select num from b ) 

優(yōu)化成：

SELECT num FROM A WHERE num exists(select 1 from B where B.num = A.num)

方案三：left join替換in

如SQL：

SELECT id FROM A WHERE num in(select num from B) 

優(yōu)化成：

SELECT id FROM A LEFT JOIN B ON A.num = B.num

8、where子句中對字段進行表達式操作的優(yōu)化

不要在where子句中的“=”左邊進行函數(shù)、算數(shù)運算或其他表達式運算，否則系統(tǒng)將可能無法正確使用索引。

• 如SQL:SELECT id FROM A WHERE num/2 = 100 優(yōu)化成：SELECT id FROM A WHERE num = 100*2
• 如SQL:SELECT id FROM A WHERE datediff(day,createdate,‘2016-11-30’)=0 優(yōu)化成：SELECT id FROM A WHERE createdate>=‘2016-11-30’ and createdate<‘2016-12-1’
• 如SQL:SELECT id FROM A WHERE year(addate) <2016 優(yōu)化成：SELECT id FROM A where addate<‘2016-01-01’

9、任何地方都不要用 select * from table 

用具體的字段列表替換"*"，不要返回用不到的字段

10、使用“臨時表”暫存中間結果

采用臨時表暫存中間結果好處：

（1）避免程序中多次掃描主表，減少程序執(zhí)行“共享鎖”阻塞“更新鎖”，減少了阻塞，提高了并發(fā)性能。

（2）盡量使用表變量來代替臨時表。如果表變量包含大量數(shù)據(jù)，請注意索引非常有限（只有主鍵索引）。

（3）避免頻繁創(chuàng)建和刪除臨時表，以減少系統(tǒng)資源的浪費。

（4）盡量避免向客戶端返回大數(shù)據(jù)量，若數(shù)據(jù)量過大，應考慮相應需求是否合理。

11、limit分頁優(yōu)化

總數(shù)據(jù)有500萬左右，以下例子

select * from wl_tagindex where byname=‘f’ order by id limit 300000,10 執(zhí)行時間是 3.21s

優(yōu)化后：

select * from (
select id from wl_tagindex
where byname=‘f' order by id limit 300000,10
) a
left join wl_tagindex b on a.id=b.id

執(zhí)行時間為 0.11s 速度明顯提升

這里需要說明的是 我這里用到的字段是 byname ,id 需要把這兩個字段做復合索引，否則的話效果提升不明顯

12、批量插入優(yōu)化

INSERT into person(name,age) values(‘A',14)
　　　　　　INSERT into person(name,age) values(‘B',14)
　　　　　　INSERT into person(name,age) values(‘C',14)

可優(yōu)化為：

INSERT into person(name,age) values(‘A',14),(‘B',14),(‘C',14),

13、利用limit 1 、top 1 取得一行

但是需要注意的是，Top關鍵字只能在SQL Server數(shù)據(jù)庫中可以使用，而在MySQL數(shù)據(jù)庫中就要使用具有同樣功能的LIMIT函數(shù)

有時要查詢一張表時，你知道只需要看一條記錄，你可能去查詢一條特殊的記錄?？梢允褂胠imit 1 或者 top 1 來終止數(shù)據(jù)庫索引繼續(xù)掃描整個表或索引。

如SQL：

SELECT id FROM A LIKE ‘a(chǎn)bc%' 

優(yōu)化為：

SELECT id FROM A LIKE ‘a(chǎn)bc%' limit 1

14、盡量不要使用 BY RAND()命令

BY RAND()是隨機顯示結果，這個函數(shù)可能會為表中每一個獨立的行執(zhí)行BY RAND()命令，這個會消耗處理器的處理能力。

如SQL：

SELECT * FROM A order by rand() limit 10 

優(yōu)化為：

SELECT * FROM A WHERE id >= ((SELECT MAX(id) FROM A)-(SELECT MIN(id) FROM A)) * RAND() + (SELECT MIN(id) FROM A) LIMIT 10

15、排序的索引問題

Mysql查詢只是用一個索引，因此如果where子句中已經(jīng)使用了索引的話，那么order by中的列是不會使用索引的。因此數(shù)據(jù)庫默認排序可以符合要求情況下不要使用排序操作；

盡量不要包含多個列的排序，如果需要最好給這些列創(chuàng)建復合索引。

16、盡量用 union all 替換 union

union和union all的差異主要是前者需要將兩個（或者多個）結果集合并后再進行唯一性過濾操作，這就會涉及到排序，增加大量的cpu運算，加大資源消耗及延遲。

所以當我們可以確認不可能出現(xiàn)重復結果集或者不在乎重復結果集的時候，盡量使用union all而不是union

17、避免類型轉(zhuǎn)換

這里所說的“類型轉(zhuǎn)換”是指where子句中出現(xiàn)column字段的類型和傳入的參數(shù)類型不一致的時候發(fā)生的類型轉(zhuǎn)換。

人為的上通過轉(zhuǎn)換函數(shù)進行轉(zhuǎn)換，直接導致mysql無法使用索引。如果非要轉(zhuǎn)型，應該在傳入?yún)?shù)上進行轉(zhuǎn)換。

例如utime 是datetime類型，傳入的參數(shù)是“2016-07-23”，在比較大小時通常是 date（utime）>"2016-07-23",可以優(yōu)化為utime>"2016-07-23 00：00：00"

18、盡可能使用更小的字段

MySQL從磁盤讀取數(shù)據(jù)后是存儲到內(nèi)存中的，然后使用cpu周期和磁盤I/O讀取它，這意味著越小的數(shù)據(jù)類型占用的空間越小，從磁盤讀或打包到內(nèi)存的效率都更好，但也不要太過執(zhí)著減小數(shù)據(jù)類型，要是以后應用程序發(fā)生什么變化就沒有空間了。

修改表將需要重構，間接地可能引起代碼的改變，這是很頭疼的問題，因此需要找到一個平衡點。

19、Inner join 和 left join、right join、子查詢

第一：inner join內(nèi)連接也叫等值連接是，left/rightjoin是外連接。

SELECT A.id,A.name,B.id,B.name FROM A LEFT JOIN B ON A.id =B.id;
SELECT A.id,A.name,B.id,B.name FROM A RIGHT JOIN ON B A.id= B.id;
SELECT A.id,A.name,B.id,B.name FROM A INNER JOIN ON A.id =B.id;

經(jīng)過來之多方面的證實inner join性能比較快，因為inner join是等值連接，或許返回的行數(shù)比較少。但是我們要記得有些語句隱形的用到了等值連接，如：

SELECT A.id,A.name,B.id,B.name FROM A,B WHERE A.id = B.id;

推薦：能用inner join連接盡量使用inner join連接

第二：子查詢的性能又比外連接性能慢，盡量用外連接來替換子查詢。

Select* from A where exists (select * from B where id>=3000 and A.uuid=B.uuid);

A表的數(shù)據(jù)為十萬級表，B表為百萬級表，在本機執(zhí)行差不多用2秒左右，我們可以通過explain可以查看到子查詢是一個相關子查詢(DEPENDENCE SUBQUERY);Mysql是先對外表A執(zhí)行全表查詢，然后根據(jù)uuid逐次執(zhí)行子查詢，如果外層表是一個很大的表，我們可以想象查詢性能會表現(xiàn)比這個更加糟糕。

一種簡單的優(yōu)化就是用innerjoin的方法來代替子查詢，查詢語句改為：

Select* from A inner join B ON A.uuid=B.uuid using(uuid) where b.uuid>=3000; 

這個語句執(zhí)行測試不到一秒；

第三：使用JOIN時候，應該用小的結果驅(qū)動打的結果（left join 左邊表結果盡量小，如果有條件應該放到左邊先處理，right join同理反向），同時盡量把牽涉到多表聯(lián)合的查詢拆分多個query (多個表查詢效率低，容易鎖表和阻塞)。如：

Select * from A left join B A.id=B.ref_id where A.id>10;

可以優(yōu)化為：

select * from (select * from A wehre id >10) T1 left join B on T1.id=B.ref_id;

20、exist 代替 in

SELECT * from A WHERE idin (SELECT id from B)
SELECT * from A WHERE id EXISTS(SELECT 1 from A.id= B.id)

• in 是在內(nèi)存中遍歷比較
• exist 需要查詢數(shù)據(jù)庫，所以當B的數(shù)據(jù)量比較大時，exists效率優(yōu)于in.
• in()只執(zhí)行一次，把B表中的所有id字段緩存起來，之后檢查A表的id是否與B表中的id相等，如果id相等則將A表的記錄加入到結果集中，直到遍歷完A表的所有記錄。
• In 操作的流程原理如同一下代碼

List resultSet={};
　　　　Array A=(select * from A);
　　　　Array B=(select id from B);
　　　　for(int i=0;i<A.length;i++) {
　　　　　　for(int j=0;j<B.length;j++) {
　　　　　　　　if(A[i].id==B[j].id) {
　　　　　　　　　　resultSet.add(A[i]);
　　　　　　　　　　break;
　　　　　　　　}
　　　　　　}
　　　　}
　　　return resultSet;

可以看出，當B表數(shù)據(jù)較大時不適合使用in()，因為它會B表數(shù)據(jù)全部遍歷一次

• 如：A表有10000條記錄，B表有1000000條記錄，那么最多有可能遍歷10000*1000000次，效率很差。
• 再如：A表有10000條記錄，B表有100條記錄，那么最多有可能遍歷10000*100次，遍歷次數(shù)大大減少，效率大大提升。
• 結論：in()適合B表比A表數(shù)據(jù)小的情況

exist()會執(zhí)行A.length()次，執(zhí)行過程代碼如下

List resultSet={};
　　　　Array A=(select * from A);
　　　　for(int i=0;i<A.length;i++) {
　　　　　　if(exists(A[i].id) { //執(zhí)行select 1 from B where B.id=A.id是否有記錄返回
　　　　　　　　resultSet.add(A[i]);
　　　　　　}
　　　　}
　　　　return resultSet;

當B表比A表數(shù)據(jù)大時適合使用exists()，因為它沒有那么多遍歷操作，只需要再執(zhí)行一次查詢就行。

• 如：A表有10000條記錄，B表有1000000條記錄，那么exists()會執(zhí)行10000次去判斷A表中的id是否與B表中的id相等。
• 如：A表有10000條記錄，B表有100000000條記錄，那么exists()還是執(zhí)行10000次，因為它只執(zhí)行A.length次，可見B表數(shù)據(jù)越多，越適合exists()發(fā)揮效果。
• 再如：A表有10000條記錄，B表有100條記錄，那么exists()還是執(zhí)行10000次，還不如使用in()遍歷10000*100次，因為in()是在內(nèi)存里遍歷比較，而exists()需要查詢數(shù)據(jù)庫，

我們都知道查詢數(shù)據(jù)庫所消耗的性能更高，而內(nèi)存比較很快。

結論：exists()適合B表比A表數(shù)據(jù)大的情況

當A表數(shù)據(jù)與B表數(shù)據(jù)一樣大時，in與exists效率差不多，可任選一個使用。

總結

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論