關于資料庫sql優化，簡略版筆記
核心要關注的東西
通過explain觀察低效率的sql陳述句執行情況，可以通過添加索引減小掃描行數，提高資料庫訪問速度，
例：explain select count（orderid） from orders a,user b where a.userid=b.userid and a.goodid=1 and b.userid=1;
可通過Create index ind_goodid on orders(goodid);
添加索引，再執行例句，便可發現type（表連接的型別）得到優化
索引的使用
不使用索引的情況下，查詢的行數會很多，使用索引會減少查詢行數，提高資料庫訪問速度
在使用like時，%放在第一位，將不使用索引，%不放在第一位的時候使用索引
如果對大的文本進行搜索，將使用全文索引而不用like ‘%…%’
當索引搜索比全表搜索慢，系統會不使用索引
當使用or連接條件時，第一個條件有索引，但第二個條件沒索引的時候，將不使用索引
當使用復合索引時，欄位作為條件，但該欄位并不是復合索引的第一列，索引也將不被使用
通過show status like ‘Handler_read%’;查看索引使用情況，關鍵點為handler_read_key，他代表一個行被索引讀的次數，他的值越高，代表著索引使用的理想，資料庫訪問速度提高
handler_read_rnd_next代表下一條資料查詢情況，當這個值很高的時候，代表你的表索引不正確或寫入的查詢沒有利用索引
簡單實用的優化方法
定期分析表和檢查表
分析表可以使系統得到準確的訊息 analyze table [tablename];
檢查表可以檢查一個或多個表是否有錯誤 check table [tablename];
定期優化表
如果表中含有可變長度行例如varchar，blob，text型別的列，則應該使用optimize table [tablename]對表進行優化，
他可以使表中的空間碎片進行合并，消除由于洗掉或者更新造成的空間浪費，
注意的是，optimize 的時候，表會被鎖定，因此需要在資料庫不繁忙時進行碎片整理，并且只對特定表，次數也不需要很多，一周一次或者一個月一次就行
常用的sql優化
對于MyISAM表可以使用DISABLE KEYS和ENABLE KEYS來提高在使用load向一張有資料的表中插入大量資料使用時運行速度，例如
load data infile ‘/home/msql/test1.txt’into table test1; 運行時間為115秒
而使用 alert table test1 disable keys; 運行時間為0秒
再使用load data infile ‘/home/msql/test1.txt’into table test1; 運行時間為6秒
再使用 alert table test1 enable keys；運行時間為12秒 整體運行時間為18秒，大大縮減了運行時間，
而上述方法在innodb中是不能提高匯入效率的，在innodb中可以使用以下方法：
檔案按照表主鍵順序存盤，提高匯入速率
在匯入資料前，關閉唯一性校驗，結束后打開，可提高匯入速率
執行set unique_checks=0
如果應用使用自動提交的方式，在匯入之前關閉自動提交，結束后再打開，可提高匯入速率，
執行 set autocommit =0
優化insert
使用insert時，若一個客戶需要插入多條資料，推薦采用
insert into test values（1，2），（3，4）…的方式
可以減小客戶端與資料庫之間的連接、關閉的消耗
如過不同客戶插入多行資料，可以使用insert delayed提高速度，
將索引檔案和資料檔案放在不同的磁盤上存放（建表中的選項）
對于myisam表，進行批量插入的時候，可以增加bulk_insert_buffer_size變數值的方法來提高速度
當從一個文本檔案裝載一個表時，使用load data infile 通常比使用insert快20倍
優化group by
當使用group by時，explain后發現extra中有using filesort，可以在陳述句最后面加上order by null禁止排序，因為using filesort非常耗費時間
優化order by
可以使用一個索引來滿足order by，但是當order by欄位混合desc，asc使用，用于查詢的關鍵字和order by中所使用的不同，對不同的關鍵字使用order by就不使用索引
優化嵌套查詢
對于子查詢，當使用連接查詢（join）代替嵌套查詢時，速度會快得多，原因是使用連接查詢不需要創建臨時表來完成這個邏輯上需要兩個步驟的查詢作業
優化or
在使用or時，考慮前后條件是否都具有索引，可以通過show index from [tablename]查看索引情況，如果有一個條件沒有獨立索引或者都沒有索引，則考慮增加索引
使用sql提示
在查詢陳述句中表名字的后面加上use index（indexname）,以此來提供參考的索引串列，可以讓mysql不再考慮其他索引
如果不想使用某個索引可以在表名字后面加上ignore index（indexname）
如果想要強制使用某個索引可在表名字后面加上force index（indexname）
重點是經常使用explain 查看sql的狀態，有助于sql的優化
本篇筆記來源于在社區的老前輩處學習得來，來前輩住在
https://blog.csdn.net/wildpen/article/details/81335777
想學習的小伙伴可以去看看
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/243645.html
標籤：其他
上一篇：Linux下MySql安裝