（1）建立物化視圖或盡可能減少多表查詢(xún)。

（2）以不相干子查詢(xún)替代相干子查詢(xún)。

（3）只檢索需要的列。

（4）用帶in的條件子句等價(jià)替換or子句。

（5）經(jīng)常提交commit，以盡早釋放鎖。

（6）避免嵌套的游標（Cursor）和多重循環(huán)等。

（7）在經(jīng)常查詢(xún)的列上創(chuàng )建索引，提高查詢(xún)效率。

（8）避免使用模糊查詢(xún)進(jìn)行匹配，如果一定要使用，建議使用最左模糊匹配原則。

（9）慢的查詢(xún)的sql，根據性能和存儲容量大小進(jìn)行評估，適當的可以考慮水平分表和垂直分表，以提高sql的查詢(xún)性能。

（10）查詢(xún)數據是否存在，適當的可以使用exists替代in。

建表

1.盡量使用數字型字段，若只含數值信息的字段盡量不要設計為字符型，這會(huì )降低查詢(xún)和連接的性能，并會(huì )增加存儲開(kāi)銷(xiāo)。這是因為引擎在處理查詢(xún)和連 接時(shí)會(huì )逐個(gè)比較字符串中每一個(gè)字符，而對于數字型而言只需要比較一次就夠了。

2.盡可能的使用 varchar 代替 char ，因為首先變長(cháng)字段存儲空間小，可以節省存儲空間， 其次對于查詢(xún)來(lái)說(shuō)，在一個(gè)相對較小的字段內搜索效率顯然要高些。

臨時(shí)表

避免頻繁創(chuàng )建和刪除臨時(shí)表，以減少系統表資源的消耗

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

臨時(shí)表并不是不可使用，適當地使用它們可以使某些例程更有效，例如，當需要重復引用大型表或常用表中的某個(gè)數據集時(shí)。但是，對于一次性事件，最好使用導出表。

在新建臨時(shí)表時(shí)，如果一次性插入數據量很大，那么可以使用 select into 代替 create table，避免造成大量 log，以提高速度；

如果數據量不大，為了緩和系統表的資源，應先create table，然后insert。

如果使用到了臨時(shí)表，在存儲過(guò)程的最后務(wù)必將所有的臨時(shí)表顯式刪除，先 truncate table，然后 drop table，這樣可以避免系統表的較長(cháng)時(shí)間鎖定。

游標的問(wèn)題

盡量避免使用游標，因為游標的效率較差，如果游標操作的數據超過(guò)1萬(wàn)行，那么就應該考慮改寫(xiě)。游標的一個(gè)常見(jiàn)用途就是保存查詢(xún)結果，以便以后使用。游標的結果集是由SELECT語(yǔ)句產(chǎn)生，如果處理過(guò)程需要重復使用一個(gè)記錄集，那么創(chuàng )建一次游標而重復使用若干次，比重復查詢(xún)數據庫要快的多。

使用基于游標的方法或臨時(shí)表方法之前，應先尋找基于集的解決方案來(lái)解決問(wèn)題，基于集的方法通常更有效。

與臨時(shí)表一樣，游標并不是不可使用。對小型數據集使用 FAST_FORWARD 游標通常要優(yōu)于其他逐行處理方法，尤其是在必須引用幾個(gè)表才能獲得所需的數據時(shí)。在結果集中包括“合計”的例程通常要比使用游標執行的速度快。如果開(kāi)發(fā)時(shí)間允許，基于游標的方法和基于集的方法都可以嘗試一下，看哪一種方法的效果更好。

事務(wù)

盡量避免大事務(wù)操作，提高系統并發(fā)能力。

數據量問(wèn)題

13.盡量避免向客戶(hù)端返回大數據量，若數據量過(guò)大，應該考慮相應需求是否合理。

具體SQL優(yōu)化 

1.避免使用select *

select *不走覆蓋索引，會(huì )有大量的回表操作，從而導致查詢(xún)SQL的性能很低。應該使用具體的字段代替*，只返回使用到的字段。     

2.用union all代替union

union可以獲取排除重復后的數據，union all可以獲取所有數據，包含重復的數據，排除重復的過(guò)程需要遍歷，排序和比較，他更耗時(shí)，更消耗CPU資源，所以如果能用union all，盡量不用union，除非是業(yè)務(wù)場(chǎng)景中不允許產(chǎn)生重復數據

3.小表驅動(dòng)大表

in 和 not in 也要慎用，否則會(huì )導致全表掃描。對于連續的數值，能用 between 就不要用 in，對于子查詢(xún)，可以用exists代替。

用小表的數據集驅動(dòng)大表的數據集

in關(guān)鍵字，他會(huì )優(yōu)先執行in里面的子查詢(xún)語(yǔ)句，然后在執行in外面的語(yǔ)句，in里面的數據量很少，作為條件查詢(xún)速度更快

exists關(guān)鍵字，他會(huì )優(yōu)先執行exists左邊的語(yǔ)句（即主查詢(xún)語(yǔ)句），然后把它作為條件，去跟右邊的語(yǔ)句匹配，如果匹配上，則可以查出數據，如果匹配不上，數據就被過(guò)濾掉了

in適用于左邊大表，右邊小表

exists適用于左邊小表，右邊大表

4.批量操作

每次遠程請求數據庫，是會(huì )消耗一定性能的

提供一個(gè)批量插入的方法，這樣只需要遠程請求一次數據庫，SQL性能會(huì )提升，數據量越大，提升越多，但是不建議一次批量操作太多數據，如果數據太多，數據庫響應也會(huì )很慢，批量操作需要把握一個(gè)度，建議每批數據盡量控制在500以?xún)?，多批如果數據多?00，則分多批處理。

5.多用limit

6.in中值太多

如果in數據太多，不做任何限制，可能會(huì )導致接口超時(shí)

可以在SQL語(yǔ)句中對數據用limit做限制，不過(guò)更多的是在業(yè)務(wù)代碼中加限制

如果超出500，可以分批用多線(xiàn)程去查詢(xún)數據，每批只查500條記錄，最后把查詢(xún)到的數據匯總到一起返回

7.增量查詢(xún)

有時(shí)候，我們需要通過(guò)遠程接口查詢(xún)數據，然后同步到另一個(gè)數據庫，如果直接獲取所有的數據，然后同步過(guò)去，這樣如果數據很多，查詢(xún)性能會(huì )非常差，可以按時(shí)間和id升序，每次只同步一批數據，這一批數據只有100條記錄，每次同步完后，保存這100條數據中最大的id和時(shí)間，給同步下一批數據的時(shí)候用，通過(guò)這種增量查詢(xún)的方式，能夠提升單次查詢(xún)的效率

select * from user where id > #{lastId} and create_time >= #{lastCreateTime} limit 100;

8.高效的分頁(yè)

列表頁(yè)在查詢(xún)數據的時(shí)候，為了避免一次性返回過(guò)多的數據影響接口性能，我們一般會(huì )對查詢(xún)接口做分頁(yè)處理，在數據庫中分頁(yè)一般用的limit關(guān)鍵字如圖

select id,name,age from user limit 10,20;

如果表中的數據量較少，用limit關(guān)鍵字做分頁(yè)，沒(méi)什么問(wèn)題，但如果表中數據量很多，用他就會(huì )出現性能問(wèn)題，

select id,name,age from user limit 1000000,20;

優(yōu)化

select id,name,age from user where id >1000000 limit 20;

利用id上的索引查詢(xún)，要求id是連續的，并且是有序的，還可以使用between優(yōu)化分頁(yè)

select id,name,age from user where id between 1000000 and  1000020;

between要在唯一索引上分頁(yè)，不然會(huì )出現每頁(yè)大小不一致的問(wèn)題

9.用連接查詢(xún)代替子查詢(xún)

數據庫中如果需要從兩張以上的表中查詢(xún)出數據的話(huà)，一般有兩種方式，子查詢(xún)和連接查詢(xún)

子查詢(xún)，可以通過(guò)in關(guān)鍵字實(shí)現，一個(gè)查詢(xún)語(yǔ)句的條件落在另一個(gè)select語(yǔ)句的查詢(xún)結果中，程序先運行嵌套在最內層的語(yǔ)句，在運行外層的語(yǔ)句，子查詢(xún)的優(yōu)點(diǎn)是簡(jiǎn)單，結構化，如果涉及的表數據不多的話(huà)，但缺點(diǎn)是數據庫執行子查詢(xún)時(shí)，需要創(chuàng )建臨時(shí)表，查詢(xún)完畢后，會(huì )刪除這些臨時(shí)表，有一些額外的性能消耗

select * from order where user_id in (select id from user where status=1);

連接查詢(xún)，性能會(huì )更高

select o.* from order o

inner join user u on o.user_id=u.id

where u.status=1;

10.join的表不宜過(guò)多

join表的數據不應超過(guò)3個(gè)，如果join太多，數據庫在選擇索引的時(shí)候會(huì )非常復雜，很容易選錯索引，并且如果每天命中，nested loop join就是分別從兩個(gè)表讀一行數據進(jìn)行兩兩對比

11.join時(shí)要注意

我們在使用多張表聯(lián)合查詢(xún)的時(shí)候，一般會(huì )使用join關(guān)鍵字，join使用最多的是是left join和inner join

left join求兩個(gè)表的交集外加左表剩下的數據

inner join求兩個(gè)表交集的數據

12.索引

        并不是所有索引對查詢(xún)都有效，SQL是根據表中數據來(lái)進(jìn)行查詢(xún)優(yōu)化的，當索引列有大量數據重復時(shí)，SQL查詢(xún)可能不會(huì )去利用索引，如一表中有字段sex，male、female幾乎各一半，那么即使在sex上建了索引也對查詢(xún)效率起不了作用。

        索引問(wèn)題 法則：不要在建立的索引的數據列上進(jìn)行下列操作:避免對索引字段進(jìn)行計算操作。避免在索引字段上使用not，<>，!=。避免在索引列上使用IS NULL和IS NOT NULL。避免在索引列上出現數據類(lèi)型轉換。避免在索引字段上使用函數。避免建立索引的列中使用空值。

        索引并不是越多越好，索引固然可以提高相應的 select 的效率，但同時(shí)也降低了 insert 及 update 的效率， 因為 insert 或 update 時(shí)有可能會(huì )重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個(gè)表的索引數最好不要超過(guò)6個(gè)，若太多則應考慮一些不常使用到的列上建的索引是否有必要。

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

控制索引的數量：索引可以顯著(zhù)提升 查詢(xún)的性能，但索引數量并非越多越好，因為表中新增數據時(shí)，需要同時(shí)為他創(chuàng )建索引，而索引時(shí)需要額外的存儲空間的，而且還會(huì )有一定的性能消耗，單表中的索引數量應該盡量控制在5個(gè)以?xún)?，并且單個(gè)索引中的字段不超過(guò)5個(gè)

13.合理的數據類(lèi)型

14.提升group by的效率

select user_id,user_name from order group by user_id having user_id <=200;

優(yōu)化

select user_id,user_name from order where user_id <=200 group by user_id;

15，索引優(yōu)化

檢查SQL語(yǔ)句有沒(méi)有走索引-explain查看數據庫的執行計劃

16.對查詢(xún)進(jìn)行優(yōu)化，應盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。

where 表之間的連接必須寫(xiě)在其他 Where 條件之前， 那些可以過(guò)濾掉最大數量記錄的條件必須寫(xiě)在 Where 子句的末尾，HAVING 最后。

不要在where條件中使用左右兩邊都是%的like模糊查詢(xún)，這樣會(huì )導致數據庫引擎放棄索引進(jìn)行全表掃描。優(yōu)化：盡量在字段后面使用模糊查詢(xún)

應盡量避免在 where 子句中使用 or 來(lái)連接條件，否則將導致引擎放棄使用索引而進(jìn)行全表掃描，優(yōu)化：可以用in代替or。

盡量不要在 where 子句中對字段進(jìn)行表達式操作，這樣也會(huì )造成全表掃描。

where條件里盡量不要進(jìn)行null值的判斷，null的判斷也會(huì )造成全表掃描。給字段添加默認值，對默認值進(jìn)行判斷。

應盡量避免在 where 子句中使用!=或<>操作符，否則將引擎放棄使用索引而進(jìn)行全表掃描

應盡量避免在where子句中對字段進(jìn)行函數操作，這將導致引擎放棄使用索引而進(jìn)行全表掃描。將表達式.函數操作移動(dòng)到等號右側。

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

盡量不要使用where 1=1的條件,有時(shí)候，在開(kāi)發(fā)過(guò)程中，為了方便拼裝查詢(xún)條件，我們會(huì )加上該條件，這樣，會(huì )造成進(jìn)行全表掃描。優(yōu)化：如果用代碼拼裝sql，則由代碼進(jìn)行判斷，沒(méi)where加where，有where加and如果用mybatis，請用mybatis的where語(yǔ)法。

其他的優(yōu)化

20.Update 語(yǔ)句，如果只更改1、2個(gè)字段，不要Update全部字段，否則頻繁調用會(huì )引起明顯的性能消耗，同時(shí)帶來(lái)大量日志。

21.盡量使用Join 語(yǔ)句來(lái)替代子查詢(xún)，因為子查詢(xún)是嵌套查詢(xún)，而嵌套查詢(xún)會(huì )新創(chuàng )建一張臨時(shí)表，而臨時(shí)表的創(chuàng )建與銷(xiāo)毀會(huì )占用一定的系統資源以及花費一定的時(shí)間，同時(shí)對于返回結果集比較大的子查詢(xún)，其對查詢(xún)性能的影響更大。

22.對于多張大數據量（這里幾百條就算大了）的表JOIN，要先分頁(yè)再JOIN，否則邏輯讀會(huì )

很高，性能很差。

23.前提也是在sql基礎優(yōu)化完成后，有多表聯(lián)合查詢(xún)導致查詢(xún)數據很慢，可以在代碼上進(jìn)行分割，如一條語(yǔ)句查多個(gè)表，可以拆分成兩條sql語(yǔ)句或者多條sql語(yǔ)句，然后再代碼上進(jìn)行數據拼裝。

24.業(yè)務(wù)層面優(yōu)化是指在sql基礎優(yōu)化上沒(méi)有問(wèn)題之后，然后一次性查詢(xún)的數據量很大，達到上億的數據量，即使是分頁(yè)也會(huì )很慢，所以要在業(yè)務(wù)層面進(jìn)行優(yōu)化，固定條件，緩存count值，避免每次查詢(xún)全表掃描計算count值，每次更新都要對count值進(jìn)行同步修改

Count優(yōu)化

count(column) ：是表示結果集中有多少個(gè)column字段不為空的記錄。

count(*) ：是表示整個(gè)結果集有多少條記錄

count(1)：InnoDB 引擎遍歷整張表，但不取值。server 層對于返回的每一行，放一個(gè)數字“1”進(jìn)去，判斷是不可能為空的，按行累加。count(1) 執行得要比 count(主鍵 id) 快。因為從引擎返回 id 會(huì )涉及到解析數據行，以及拷貝字段值的操作。

原文鏈接：https://blog.csdn.net/m0_45312259/article/details/130841454