sql優化

一、SQL優化的手段：

1.查詢、定位慢查詢，並優化

在專案自驗或專案轉測試前進行定位，檢視慢查詢日誌，就可以看到所有的慢查詢。

具體操作：

(1)檢視慢sql是否啟動。

show variables like 'log_slow_queries';  

off代表禁用

(2)開啟慢查詢

set global log_slow_queries = on;   

(3)檢視慢查詢的預設時間

show [global] variables like 'long_query_time';

(4)修改慢查詢的預設時間

  set long_query_time=1; 
  set global long_query_time =1;
  --用命令設定的，會立即生效，不用重啟mysql服務。但重啟mysql服務後就會失效。

(5)檢視慢查詢日誌是否開啟

show variables like 'slow_query_log'

(6)檢視慢查詢存放日誌

 show variables like 'slow_query_log_file';

(7)檢視慢查詢sql的具體效能問題

使用explain查詢語句檢視sql的執行效果。

2.建立索引

建立合適的索引，我們就可以先載索引中查詢，查詢到以後直接找對應的記錄。

3.分表

當一張表的資料比較多或者一張表的某些欄位的值比較多並且很少使用時，採用水平分表（行數多時）和垂直分表（欄位內容較大時,如文章）來優化。

4.分庫（讀寫分離）

當一臺伺服器不能滿足需求時，把讀和寫進行分離。

5.快取

使用redis等記憶體資料庫快取，來減少直接運算元據庫。

因為快取速度快於io。

6.選擇合適的儲存引擎

MyISAM：對事務要求不高，以查詢和新增為主。如bbs中發帖表、回覆表。

INNODB：對事務要求高，儲存的資料都是重要資料。如訂單表，賬號表。

Memory：資料變化頻繁，不需要入庫，同時又頻繁的查詢和修改。速度快。

7.建立合適的索引

(1)分類：普通索引/唯一索引/主鍵索引/全文索引

普通索引：允許出現重複值。

唯一索引：除了不能又重複值，其他和普通索引一樣。

​ e.g. 使用者名稱，使用者身份證，email，tel等

主鍵索引：建立主鍵時，資料庫就會給該列建立主鍵索引，唯一且不能有null。

全文索引：用來對錶中的文字域（char,varchar,text）進行索引，全文索引針對MyIsam儲存引擎。

(2)索引使用場景

弊端：

• 佔用磁碟空間。
• 對插入、修改、刪除操作有影響，變慢。

使用場景：

• where條件經常使用。
• 欄位的內容不是唯一的幾個值（sex）。
• 欄位內容不是頻繁變化。

(3)索引使用技巧

• 對於建立複合索引（多列索引），不使用第一列時就不會使用該索引。

  alter table dept add index my_ind(dname,loc);--dname左邊的列，loc右邊的列
  explain select * from dept where dname='aaa'\G --會使用索引
  explain select * from dept where loc='aaa'\G --不會使用索引，因為沒使用dname
  

• 對於使用like的查詢，查詢如果是'%aaa'不會使用索引，而'aaa%'會使用索引。
  所以在like查詢時，關鍵字的最前面不能使用%和_這樣的字元。如果一定要前邊有變化的值，則考慮使用全文索引。

• 如果條件中有or，要求連線所有欄位都必須單獨可以使用索引，否則全部索引都不生效。

• 如果mysql估計使用全表掃描要比使用索引快，則不適用索引。
  e.g. 表中只有一條資料時，不會使用索引，而時全表掃描

8.分表

• 分類：水平分表（按行），垂直分表（按照列）

• 水平分表策略

  • 按時間分表：有一定侷限性，資料有較強的時效性，使用者很少查詢幾月前的資料，如聊天記錄等。

  • 按區間範圍分表：一般有嚴格的自增id需求上，如user_id水平分表：
    tt_user_1 user_id從1~100w
    tt_user_1 user_id從100~200w
    tt_user_1 user_id從200~300w

  • hash分表(最多)：具有普遍性，通過原始目標的ID或者名稱通過一定的hash演算法計算出資料儲存表的表名，然後訪問對應的表。

9.讀寫分離（分庫）

一臺資料庫支援的最大併發是有限的，如果使用者併發訪問太多，一臺伺服器滿足不了要求時，就可以做叢集處理。mysql的叢集處理技術最常用的就是讀寫分離。

(1)主從同步

​ 資料庫最終會把資料持久化到磁碟上，如果叢集必須確保每個資料庫伺服器的資料是一致的，改變資料庫資料的操作都往主資料庫寫，而其他資料庫從主資料庫上同步資料。

(2)讀寫分離

​ 使用負載均衡來實現寫的操作都往主資料庫寫，而讀的操作去從伺服器讀。

10.快取

在持久層（dao）和資料庫（db）之間新增一個快取層，如果使用者訪問的資料已經快取過，使用者直接從快取中獲取，不用訪問資料庫。而快取是記憶體級的，訪問速度快。

作用：減少資料庫伺服器壓力，減少訪問時間。

常用快取方案：

• hibernate的二級快取，該快取不能完成分散式快取。

• 快取資料庫(如redis)，作為中央快取，對快取的資料集中處理。

11.語句優化小技巧

(1)DDL優化（常用的有CREATE和DROP，用於在資料庫中建立新表或刪除表，以及為表加入索引等）

• 通過禁用索引來提工匯入資料效能。此操作主要針對已有的表批量追加資料。

--去除鍵
alter table test2 disable keys;
--批量插入資料
insert into test2 select * from test1;
--恢復鍵
alert table test2 enable keys;

• 關閉唯一校驗

--關閉
set unique_checks=0;
--開啟
set unique_checks=1;

• 修改事務提交方式（匯入）（變多次提交為一次）

  --關閉
  set autocommit=0;
  --開啟
  set aotucommit=1;
  

(2)DML優化（主要用來對資料庫的資料進行一些操作，常用的就是INSERT、UPDATE、DELETE）（變多次提交為一次）

insert into test values (1,2);
insert into test values (1,3);
insert into test values (1,4);
--上面語句合併為一條
insert into test values (1,2),(1,3),(1,4);

(3)DQL優化（資料檢索語句，用於從表中獲取資料。通常最常用的為保留字SELECT,並且常與FROM子句、WHERE子句組成查詢SQL查詢語句）

• order by 優化
  • 多用索引排序
• group by 優化
  • 使用order by null，取消預設排序
• 子查詢優化
  • 使用left join代替子查詢
• or 優化
  • or兩邊都是單獨索引欄位，才會使用索引，否則不會使用索引

二、例項

1.jdbc批量插入幾百萬條資料怎麼實現？*

思想：變多次提交為一次，使用批量操作

效果：省出的時間很可觀。

注：這樣的批量插入操作能不使用程式碼操作就不使用，可以使用儲存過程來實現。