一、開啟慢查詢日志，捕獲慢SQL
1、查看慢查詢日志是否開啟
SHOWVARIABLESLIKE'%slow_query_log%';
2、開啟慢查詢日志
SETGLOBALslow_query_log=1;
3、查看慢查詢日志闕值
SHOWGLOBALVARIABLESLIKE'%long_query_time%';
這個值表示超過多長時間的SQL語句會被記錄到慢查詢日志中
4、設置慢查詢日志闕值
[sql]view plaincopy
1.SETGLOBALlong_query_time=3;
5、查看多少SQL語句超過瞭闕值
[sql]view plaincopy
1.SHOWGLOBALSTATUSLIKE'%Slow_queries%';
6、MySQL提供的日志分析工具mysqldumpslow
進入MySQL的安裝目錄中的bin目錄下
執行 ./mysqldumpslow –help 查看幫助命令
常用參考：
得到返回記錄集最多的10個SQL
mysqldumpslow -s r -t 10 slow.log
得到訪問次數最多的10個SQL
mysqldumpslow -s c -t 10 slow.log
得到按照時間排序的前10條裡面含有左連接的查詢語句
mysqldumpslow -s t -t 10 -g"left join" slow.log
使用這些語句時結合|more使用
二、explain+慢SQL分析
使用EXPLAIN關鍵字可以模擬優化器執行SQL查詢語句，從而知道MySQL是 如何處理你的SQL語句的。分析你的查詢語句或是表結構的性能瓶頸。
使用方式：Explain+SQL語句
執行計劃包含的信息：
+—-+————-+——-+——+—————+——+———+——+——+——-+
| id | select_type | table | type |possible_keys | key | key_len | ref |rows | Extra |
+—-+————-+——-+——+—————+——+———+——+——+——-+
1、id
SELECT查詢的序列號，包含一組數字，表示查詢中執行SELECT語句或操作表的順序
包含三種情況：
1.id相同，執行順序由上至下
2.id不同，如果是子查詢，id序號會遞增，id值越大優先級越高，越先被執行
3.id既有相同的，又有不同的。id如果相同認為是一組，執行順序由上至下;在所有組中，id值越大優先級越高，越先執 行。
2、select_type
SIMPLE:簡單SELECT查詢，查詢中不包含子查詢或者UNION
PRIMARY:查詢中包含任何復雜的子部分，最外層的查詢
SUBQUERY：SELECT或WHERE中包含的子查詢部分
DERIVED：在FROM中包含的子查詢被標記為DERIVER(衍生)， MySQL會遞歸執行這些子查詢，把結果放到臨時表中
UNION：若第二個SELECT出現UNION，則被標記為UNION, 若UNION包含在FROM子句的子查詢中，外層子查詢將被標記為DERIVED
UNION RESULT：從UNION表獲取結果的SELECT
3、table
顯示這一行數據是關於哪張表的
4、type
type顯示的是訪問類型，是較為重要的一個指標，結果值從最好到最壞依次是：
system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL
一般來說，得保證查詢至少達到range級別，最好能達到ref。
system：表隻有一行記錄(等於系統表)，這是const類型的特例，平時不會出現
const：如果通過索引依次就找到瞭，const用於比較主鍵索引或者unique索引。 因為隻能匹配一行數據，所以很快。如果將主鍵置於where列表中，MySQL就能將該查詢轉換為一個常量
eq_ref：唯一性索引掃描，對於每個索引鍵，表中隻有一條記錄與之匹配。常見於主鍵或唯一索引掃描
ref：非唯一性索引掃描，返回匹配某個單獨值的所有行。本質上也是一種索引訪問，它返回所有匹配 某個單獨值的行，然而它可能會找到多個符合條件的行，所以它應該屬於查找和掃描的混合體
range：隻檢索給定范圍的行，使用一個索引來選擇行。key列顯示使用瞭哪個索引，一般就是在你的where語句中出現between、<、>、in等的查詢，這種范圍掃描索引比全表掃描要好，因為隻需要開始於縮印的某一點，而結束於另一點，不用掃描全部索引
index：Full Index Scan ，index與ALL的區別為index類型隻遍歷索引樹，這通常比ALL快，因為索引文件通常比數據文件小。 (也就是說雖然ALL和index都是讀全表， 但index是從索引中讀取的，而ALL是從硬盤讀取的)
all：Full Table Scan，遍歷全表獲得匹配的行
5、possible_keys
顯示可能應用在這張表中的索引，一個或多個。 查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢實際使用
6、key
實際使用的索引。如果為NULL，則沒有使用索引。
查詢中若出現瞭覆蓋索引，則該索引僅出現在key列表中。
7、key_len
表示索引中使用的字節數，可通過該列計算查詢中使用的索引的長度。在不損失精度的情況下，長度越短越好。
key_len顯示的值為索引字段的最大可能長度，並非實際使用長度，即key_len是根據表設定計算而得，不是通過表內檢索出的。
8、ref
顯示索引的哪一列被使用瞭，哪些列或常量被用於查找索引列上的值。
9、rows
根據表統計信息及索引選用情況，大致估算出找到所需記錄多需要讀取的行數。
10、Extra
包含不適合在其他列中顯示但十分重要的額外信息：
1、Using filesort：說明MySQL會對數據使用一個外部的索引排序，而不是按照表內的索引順序進行讀取。MySQL中無法利用索引完成的排序操作稱為“文件排序”
2、Using temporary： 使用瞭臨時表保存中間結果，MySQL在對查詢結果排序時使用臨時表。常見於排序order by和分組查詢group by
3、Using index： 表示相應的SELECT操作中使用瞭覆蓋索引(Covering Index)，避免訪問瞭表的數據行，效率不錯。 如果同時出現usingwhere，表明索引被用來執行索引鍵值的查找; 如果沒有同時出現using where，表明索引用來讀取數據而非執行查找動作覆蓋索引(Covering Index)： 理解方式1：SELECT的數據列隻需要從索引中就能讀取到，不需要讀取數據行，MySQL可以利用索引返回SELECT列表中 的字段，而不必根據索引再次讀取數據文件，換句話說查詢列要被所建的索引覆蓋 理解方式2：索引是高效找到行的一個方法，但是一般資料庫也能使用索引找到一個列的數據，因此他不必讀取整個行。 畢竟索引葉子節點存儲瞭他們索引的數據;當能通過讀取索引就可以得到想要的數據，那就不需要讀取行瞭，一個索引包含瞭(覆蓋)滿足查詢結果的數據就叫做覆蓋索引 註意： 如果要使用覆蓋索引，一定要註意SELECT列表中隻取出需要的列，不可SELECT *, 因為如果所有字段一起做索引會導致索引文件過大查詢性能下降
6、impossible where： WHERE子句的值總是false，不能用來獲取任何元組
7、select tables optimized away： 在沒有GROUP BY子句的情況下基於索引優化MIN/MAX操作或者對於MyISAM存儲引擎優化COUNT(*)操作， 不必等到執行階段再進行計算，查詢執行計劃生成的階段即完成優化
8、distinct： 優化distinct操作，在找到第一匹配的元祖後即停止找同樣值的操作
三、showprofile查詢SQL語句在伺服器中的執行細節和生命周期
Show Profile是MySQL提供可以用來分析當前會話中語句執行的資源消耗情況，可以用於SQL的調優測量
默認關閉，並保存最近15次的運行結果
分析步驟
1、查看狀態：SHOW VARIABLES LIKE 'profiling';
2、開啟：set profiling=on;
3、查看結果：show profiles;
4、診斷SQL：show profilecpu,block io for query 上一步SQL數字號碼;
ALL：顯示所有開銷信息
BLOCK IO：顯示IO相關開銷
CONTEXT SWITCHES：顯示上下文切換相關開銷
CPU：顯示CPU相關開銷
IPC：顯示發送接收相關開銷
MEMORY：顯示內存相關開銷
PAGE FAULTS：顯示頁面錯誤相關開銷
SOURCE：顯示和Source_function，Source_file，Source_line相關開銷
SWAPS：顯示交換次數相關開銷
註意(遇到這幾種情況要優化)
converting HEAP to MyISAM： 查詢結果太大，內存不夠用往磁盤上搬
Creating tmp table：創建臨時表
Copying to tmp table on disk：把內存中的臨時表復制到磁盤
locked
四、SQL資料庫伺服器參數調優
當order by 和 group by無法使用索引時，增大max_length_for_sort_data參數設置和增大sort_buffer_size參數的設置
五、常用的sql優化案例
1不使用子查詢
例：SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHEREname=’hechunyang’);
子查詢在MySQL5.5版本裡，內部執行計劃器是這樣執行的：先查外表再匹配內表，而不是先查內表t2，當外表的數據很大時，查詢速度會非常慢。
在MariaDB10/MySQL5.6版本裡，采用join關聯方式對其進行瞭優化，這條SQL會自動轉換為
SELECT t1.* FROM t1 JOIN t2 ON t1.id = t2.id;
但請註意的是：優化隻針對SELECT有效，對UPDATE/DELETE子查詢無效，固生產環境應避免使用子查詢
2避免函數索引
例：SELECT * FROM t WHERE YEAR(d)>= 2016;
由於MySQL不像Oracle那樣支持函數索引，即使d字段有索引，也會直接全表掃描。
應改為—–>
SELECT * FROM t WHERE d >= ‘2016-01-01’;
3用IN來替換OR
低效查詢
SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;
—–>
高效查詢
SELECT * FROM t WHERE LOC_IN IN (10,20,30);
4LIKE雙百分號無法使用到索引
SELECT * FROM t WHERE name LIKE ‘%de%’;
—–>
SELECT * FROM t WHERE name LIKE ‘de%’;
目前隻有MySQL5.7支持全文索引(支持中文)
5讀取適當的記錄LIMIT M,N
SELECT * FROM t WHERE 1;
—–>
SELECT * FROM t WHERE 1 LIMIT 10;
6避免數據類型不一致
SELECT * FROM t WHERE id = ’19’;
—–>
SELECT * FROM t WHERE id = 19;
7分組統計可以禁止排序
SELECT goods_id,count(*) FROM t GROUP BY goods_id;
默認情況下，MySQL對所有GROUP BY col1，col2…的字段進行排序。如果查詢包括GROUP BY，想要避免排序結果的消耗，則可以指定ORDERBY NULL禁止排序。
—–>
SELECT goods_id,count(*) FROM t GROUP BY goods_id ORDER BY NULL;
8避免隨機取記錄
SELECT * FROM t1 WHERE 1=1 ORDER BY RAND() LIMIT 4;
MySQL不支持函數索引，會導致全表掃描
—–>
SELECT * FROM t1 WHERE id >= CEIL(RAND()*1000) LIMIT 4;
9禁止不必要的ORDER BY排序
SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id =i.user_id WHERE 1 = 1 ORDER BY u.create_time DESC;
—–>
SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id;
10批量INSERT插入
INSERT INTO t (id, name) VALUES(1,’Bea’);
INSERT INTO t (id, name) VALUES(2,’Belle’);
INSERT INTO t (id, name) VALUES(3,’Bernice’);
—–>
INSERT INTO t (id, name) VALUES(1,’Bea’), (2,’Belle’),(3,’Bernice’);
11使用連接(JOIN)來代替子查詢(Sub-Queries)
MySQL從4.1開始支持SQL的子查詢。這個技術可以使用SELECT語句來創建一個單列的查詢結果，然後把這個結果作為過濾條件用在另一個查詢中。例如，我們要將客戶基本信息表中沒有任何訂單的客戶刪除掉，就可以利用子查詢先從銷售信息表中將所有發出訂單的客戶ID取出來，然後將結果傳遞給主查詢，如下所示：
DELETE FROM customerinfo
WHERE CustomerID NOT IN (SELECT CustomerID FROMsalesinfo)
使用子查詢可以一次性的完成很多邏輯上需要多個步驟才能完成的SQL操作，同時也可以避免事務或者表鎖死，並且寫起來也很容易。但是，有些情況下，子查詢可以被更有效率的連接(JOIN)..替代。例如，假設我們要將所有沒有訂單記錄的用戶取出來，可以用下面這個查詢完成：
SELECT * FROM customerinfo
WHERE CustomerID NOT IN (SELECTC ustomerID FROMsalesinfo)
如果使用連接(JOIN)..來完成這個查詢工作，速度將會快很多。尤其是當salesinfo表中對CustomerID建有索引的話，性能將會更好，查詢如下：
SELECT * FROM customerinfo
LEFT JOIN salesinfo ONcustomerinfo.CustomerID=salesinfo.CustomerID
WHERE salesinfo.CustomerID ISNULL
連接(JOIN)..之所以更有效率一些，是因為MySQL不需要在內存中創建臨時表來完成這個邏輯上的需要兩個步驟的查詢工作。
12使用聯合(UNION)來代替手動創建的臨時表
MySQL從4.0的版本開始支持union查詢，它可以把需要使用臨時表的兩條或更多的select查詢合並的一個查詢中。在客戶端的查詢會話結束的時候，臨時表會被自動刪除，從而保證資料庫整齊、高效。使用union來創建查詢的時候，我們隻需要用UNION作為關鍵字把多個select語句連接起來就可以瞭，要註意的是所有select語句中的字段數目要想同。下面的例子就演示瞭一個使用UNION的查詢。
SELECT Name,Phone FROM client UNION
SELECT Name,BirthDate FROM author UNION
SELECT Name,Supplier FROM product
六mysql索引總結—-mysql 索引類型以及創建
文章歸屬：https://feiyan.info/16.html，我想自己去寫瞭，但是發現此君總結的非常詳細。直接搬過來瞭
關於MySQL索引的好處，如果正確合理設計並且使用索引的MySQL是一輛蘭博基尼的話，那麼沒有設計和使用索引的MySQL就是一個人力三輪車。對於沒有索引的表，單表查詢可能幾十萬數據就是瓶頸，而通常大型網站單日就可能會產生幾十萬甚至幾百萬的數據，沒有索引查詢會變的非常緩慢。還是以WordPress來說，其多個數據表都會對經常被查詢的字段添加索引，比如wp_comments表中針對5個字段設計瞭BTREE索引。
一個簡單的對比測試
MySQL索引的概念
索引是一種特殊的文件(InnoDB數據表上的索引是表空間的一個組成部分)，它們包含著對數據表裡所有記錄的引用指針。更通俗的說，資料庫索引好比是一本書前面的目錄，能加快資料庫的查詢速度。上述SQL語句，在沒有索引的情況下，資料庫會遍歷全部200條數據後選擇符合條件的;而有瞭相應的索引之後，資料庫會直接在索引中查找符合條件的選項。如果我們把SQL語句換成“SELECT * FROM article WHERE id=2000000”，那麼你是希望資料庫按照順序讀取完200萬行數據以後給你結果還是直接在索引中定位呢?上面的兩個圖片鮮明的用時對比已經給出瞭答案(註：一般資料庫默認都會為主鍵生成索引)。
索引分為聚簇索引和非聚簇索引兩種，聚簇索引是按照數據存放的物理位置為順序的，而非聚簇索引就不一樣瞭;聚簇索引能提高多行檢索的速度，而非聚簇索引對於單行的檢索很快。
MySQL索引的類型
1. 普通索引
這是最基本的索引，它沒有任何限制，比如上文中為title字段創建的索引就是一個普通索引，MyIASM中默認的BTREE類型的索引，也是我們大多數情況下用到的索引。
01–直接創建索引
02CREATEINDEXindex_nameONtable(column(length))
03–修改表結構的方式添加索引
04ALTERTABLEtable_nameADDINDEXindex_nameON(column(length))
05–創建表的時候同時創建索引
06CREATETABLE`table` (
07`id`int(11)NOTNULLAUTO_INCREMENT ,
08`title`char(255)CHARACTERSETutf8COLLATEutf8_general_ciNOTNULL,
09`content` textCHARACTERSETutf8COLLATEutf8_general_ciNULL,
10`time`int(10)NULLDEFAULTNULL,
11PRIMARYKEY(`id`),
12INDEXindex_name (title(length))
13)
14–刪除索引
15DROPINDEXindex_nameONtable
2. 唯一索引
與普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值(註意和主鍵不同)。如果是組合索引，則列值的組合必須唯一，創建方法和普通索引類似。
01–創建唯一索引
02CREATEUNIQUEINDEXindexNameONtable(column(length))
03–修改表結構
04ALTERTABLEtable_nameADDUNIQUEindexNameON(column(length))
05–創建表的時候直接指定
06CREATETABLE`table` (
07`id`int(11)NOTNULLAUTO_INCREMENT ,
08`title`char(255)CHARACTERSETutf8COLLATEutf8_general_ciNOTNULL,
09`content` textCHARACTERSETutf8COLLATEutf8_general_ciNULL,
10`time`int(10)NULLDEFAULTNULL,
11PRIMARYKEY(`id`),
12UNIQUEindexName (title(length))
13);
3. 全文索引(FULLTEXT)
MySQL從3.23.23版開始支持全文索引和全文檢索，FULLTEXT索引僅可用於 MyISAM 表;他們可以從CHAR、VARCHAR或TEXT列中作為CREATE TABLE語句的一部分被創建，或是隨後使用ALTER TABLE 或CREATE INDEX被添加。////對於較大的數據集，將你的資料輸入一個沒有FULLTEXT索引的表中，然後創建索引，其速度比把資料輸入現有FULLTEXT索引的速度更為快。不過切記對於大容量的數據表，生成全文索引是一個非常消耗時間非常消耗硬盤空間的做法。
01–創建表的適合添加全文索引
02CREATETABLE`table` (
03`id`int(11)NOTNULLAUTO_INCREMENT ,
04`title`char(255)CHARACTERSETutf8COLLATEutf8_general_ciNOTNULL,
05`content` textCHARACTERSETutf8COLLATEutf8_general_ciNULL,
06`time`int(10)NULLDEFAULTNULL,
07PRIMARYKEY(`id`),
08FULLTEXT (content)
09);
10–修改表結構添加全文索引
11ALTERTABLEarticleADDFULLTEXT index_content(content)
12–直接創建索引
13CREATEFULLTEXTINDEXindex_contentONarticle(content)
4. 單列索引、多列索引
多個單列索引與單個多列索引的查詢效果不同，因為執行查詢時，MySQL隻能使用一個索引，會從多個索引中選擇一個限制最為嚴格的索引。
5. 組合索引(最左前綴)
平時用的SQL查詢語句一般都有比較多的限制條件，所以為瞭進一步榨取MySQL的效率，就要考慮建立組合索引。例如上表中針對title和time建立一個組合索引：ALTER TABLE article ADD INDEXindex_titme_time (title(50),time(10))。建立這樣的組合索引，其實是相當於分別建立瞭下面兩組組合索引：
–title,time
–title
為什麼沒有time這樣的組合索引呢?這是因為MySQL組合索引“最左前綴”的結果。簡單的理解就是隻從最左面的開始組合。並不是隻要包含這兩列的查詢都會用到該組合索引，如下面的幾個SQL所示：
1–使用到上面的索引
2SELECT*FROMarticle WHREE title='測試'ANDtime=1234567890;
3SELECT*FROMarticle WHREE utitle='測試';
4–不使用上面的索引
5SELECT*FROMarticle WHREEtime=1234567890;
MySQL索引的優化
上面都在說使用索引的好處，但過多的使用索引將會造成濫用。因此索引也會有它的缺點：雖然索引大大提高瞭查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數據，還要保存一下索引文件。建立索引會占用磁盤空間的索引文件。一般情況這個問題不太嚴重，但如果你在一個大表上創建瞭多種組合索引，索引文件的會膨脹很快。索引隻是提高效率的一個因素，如果你的MySQL有大數據量的表，就需要花時間研究建立最優秀的索引，或優化查詢語句。下面是一些總結以及收藏的MySQL索引的註意事項和優化方法。
1. 何時使用聚集索引或非聚集索引?
動作描述使用聚集索引使用非聚集索引
列經常被分組排序使用使用
返回某范圍內的數據使用不使用
一個或極少不同值不使用不使用
小數目的不同值使用不使用
大數目的不同值不使用使用
頻繁更新的列不使用使用
外鍵列使用使用
主鍵列使用使用
頻繁修改索引列不使用使用
事實上，我們可以通過前面聚集索引和非聚集索引的設定的例子來理解上表。如：返回某范圍內的數據一項。比如您的某個表有一個時間列，恰好您把聚合索引建立在瞭該列，這時您查詢2004年1月1日至2004年10月1日之間的全部數據時，這個速度就將是很快的，因為您的這本字典正文是按日期進行排序的，聚類索引隻需要找到要檢索的所有數據中的開頭和結尾數據即可;而不像非聚集索引，必須先查到目錄中查到每一項數據對應的頁碼，然後再根據頁碼查到具體內容。其實這個具體用法我還不是很理解，隻能等待後期的項目開發中慢慢學學瞭。
2. 索引不會包含有NULL值的列
隻要列中包含有NULL值都將不會被包含在索引中，復合索引中隻要有一列含有NULL值，那麼這一列對於此復合索引就是無效的。所以我們在資料庫設計時不要讓字段的默認值為NULL。
3. 使用短索引
對串列進行索引，如果可能應該指定一個前綴長度。例如，如果有一個CHAR(255)的列，如果在前10個或20個字符內，多數值是惟一的，那麼就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節省磁盤空間和I/O操作。
4. 索引列排序
MySQL查詢隻使用一個索引，因此如果where子句中已經使用瞭索引的話，那麼order by中的列是不會使用索引的。因此資料庫默認排序可以符合要求的情況下不要使用排序操作;盡量不要包含多個列的排序，如果需要最好給這些列創建復合索引。
5. like語句操作
一般情況下不鼓勵使用like操作，如果非使用不可，如何使用也是一個問題。like “%aaa%” 不會使用索引而like “aaa%”可以使用索引。
6. 不要在列上進行運算
例如：select * from users where YEAR(adddate)<2007，將在每個行上進行運算，這將導致索引失效而進行全表掃描，因此我們可以改成：select * fromusers where adddate<’2007-01-01′。關於這一點可以圍觀：一個單引號引發的MYSQL性能損失。
最後總結一下，MySQL隻對一下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些時候的like(不以通配符%或_開頭的情形)。而理論上每張表裡面最多可創建16個索引，不過除非是數據量真的很多，否則過多的使用索引也不是那麼好玩的，比如我剛才針對text類型的字段創建索引的時候，系統差點就卡死瞭。
七、mysql字符串處理
left(), right(), substring(), substring_index(),mid(), substr()
字符串截取：left(str, length) 從左開始截取length位
right(str, length) 從右開始截取length位
substring(str,pos); 截取從第pos位到末尾不包含pos
substring(str,pos, len) 截取從pos開始到pos+len之間的部分，不包含pos，包含pos+len;
substring('example.com',-pos); 從字符串的倒數pos 個字符位置開始取，直到結束。
substring('example.com',-pos,len); 從字符串的倒數第 pos個字符位置開始取，隻取 len 個字符。
substring_index('www.example.com', '.', 2); 截取第二個 '.' 之前的所有字符
substring_index('www.example.com','.', -2);截取倒數第二個 '.'之後的所有字符。
substring_index('www.example.com','.coc', 1); 如果在字符串中找不到待匹配的字符串，就返回整個字符串;