目錄
第一章、mysql架構
一、mysql配置檔案
二、mysql的邏輯架構介紹
1、總體概述
2、詳情說明
3、存儲引擎
第二章：索引優化分析
一、性能下降SQL慢，執行時間長，等待時間長原因
二、SQL執行加載順序
1、手寫的SQL
2、機讀
3、總結
第三章、七種join理論
第四章、索引簡介
一、索引是什麼
二、索引優勢和劣勢
1、優勢
2、劣勢
三、索引分類和建立索引指令語句
1、索引分類
2、基本文法
四、索引結構與檢索原理
（1）結構
（2）檢索原理
五、那些情況适合建索引，那些情況不适合建索引
（1）需要
（2）不需要
第五章、性能分析前提知識
1、MySQL Query Optimizer
2、mysql常見瓶頸
第六章、explain
（一）簡介
（二）能幹嘛
（三）怎麼玩
（四）explain之id介紹
（五）explain之select_type介紹
（六）explain之table介紹
（七）explain之type介紹
（八）explain值possible_keys和key介紹
（九）explain值key_len介紹
（十）explain之ref介紹
（十一）explain之rows介紹
（十二）explain值extra介紹
1、Using filesort 久死一生
2、Using temorary 十死無生
3.Using index 不錯
4、Using where
5、using join buffer
6、impossible where
7、select tables optimized away
8、distinct
第一章、mysql架構
一、mysql配置檔案
1、二進制日志log-bin
主從複制用
2、錯誤日志log-error
預設是關閉的，記錄嚴重的警告和錯誤資訊，每次啟動和關閉的詳細資訊等。
3、查詢日志log
預設關閉，記錄查詢sql語句，如果開啟回降低mysql的整體性能，因為記錄日志也是需要消耗系統資源的
4、資料檔案
（1）window mysql\data 目錄下可以挑選很多庫
linux 預設路徑： /var/lib/mysql
（2）frm檔案：存放表結構
（3）myd檔案：存放表資料
（4）myi檔案：存放表索引
（5）如何配置
- windows 的my.ini檔案
- linux 的 /etc/my.cnf檔案
二、mysql的邏輯架構介紹
1、總體概述
和其他資料庫相比，MySQL有點與衆不同，他的架構可以在多種不同場景中應用并發揮良好作用，主要展現在存儲引擎的架構上，插件式的存儲引擎架構将查詢處理和其他系統任務一級資料的存儲提取相分離。這種架構可以根據業務的需求和實際需求選擇合适的存儲引擎
2、詳情說明
1)、連接配接層
最上層是一些用戶端和連接配接服務，包含本地sock通信和大多數基于用戶端/服務端工具實作的類似tcp/ip的通信，主要是完成一些類似于連接配接處理、授權認證、及相關的安全方案、在該層上引入了線程池的概念，為通過認證安全接入的用戶端提供線程、同樣在該層上可以實作基于SSL的安全連結，伺服器也會為安全接入的每個用戶端驗證它所具有的操作權限
2)、服務層
第二層架構主要是完成大多數的核心服務功能，如SQL接口，并完成緩存的查詢，SQL的分析和優化及部分内置函數的執行，所有跨存儲引擎的功能也在這一層實作，如過程，函數等。在該層，伺服器會解析查詢并建立相應的内部解析樹，并對其完成相應的優化，如确定查詢标的順序，是否利用索引等，最後生成相應的執行操作。如果是select語句，伺服器還會查詢内部的緩存。如果緩存空間足夠大，這樣在解決大量讀操作的環境中，能夠很好地提升系統的性能
3)、引擎層
存儲引擎層，存儲引擎真正的負責了MySQL中資料的存儲和提取，伺服器通過API與存儲引擎進行通信，不同的存儲引擎具有的功能不同，這樣我們可以根據自己的實際需求進行選取。主要有MyISAM 和 InnoDB
4)、存儲層
資料存儲層，主要是将資料存儲在運作于裸裝置的檔案系統上，并完成與存儲引擎的互動。
3、存儲引擎
（1）指令
a、show engines
b、show variables like '%storage_engine%';
(2)倆引擎對比
第二章：索引優化分析
一、性能下降SQL慢，執行時間長，等待時間長原因
- 查詢語句寫的爛
- 索引失效
- 關聯太多的join（設計缺陷或不得已的需求）
- 伺服器調優及各個參數的設定（緩存、線程數等）
二、SQL執行加載順序
1、手寫的SQL
2、機讀
3、總結
第三章、七種join理論
注意：FULL OUTER JOIN mysql不支援這種文法
第六種：
select * from tbl_empl a left join tbl_dept b on a.deptId=b.id union select * from tbl_emp a right join tbl_dept b on a.deptId=b.id;
第七種：
select * from tbl_emp a left join tbl_dept b on a.deptId = b.Id where b.id is null union select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptId is null;
第四章、索引簡介
一、索引是什麼
MYSQL官方對索引的定義為：索引是幫助MySQL高效擷取資料的資料結構————可以簡單了解為“排好序的快速查找資料結構”
可以得到索引的本質：索引是資料結構
在資料之外，資料庫系統還維護着特定查找算法的資料結構，這些資料結構以某種方式引用（指定）資料，這樣就可以在這些資料結構上實作進階查找算法。這種資料結構，就是索引。下圖就是一種可能的索引方式執行個體：
為了加快Col2的查找，可以維護一個右邊所示的二叉查找樹，每個節點分别包含索引鍵值和一個指向對應資料記錄實體位址的指針，這樣就可以運用二叉查找在一定的複雜度内擷取到相應資料，進而快速的檢索出符合條件的記錄。
表删資料 索引要重建
一般來說索引本身也很大，不可能全部存儲在記憶體中，是以索引往往以索引檔案的形式存儲在磁盤上
我們平常說的索引，如果沒有特别指明，都是指B樹（多路搜尋樹，并不一定是二叉的）結構組織的索引。其中聚集索引，次要索引，符合索引，字首索引，唯一索引預設都是使用B+樹索引，統稱索引。當然，除了B+樹這種類型的索引之外，還有哈希索引等
二、索引優勢和劣勢
1、優勢
建立索引，提高資料索引的效率，降低資料庫的IO成本
通過索引對資料進行排序，降低資料排序的成本，降低了CPU的消耗
2、劣勢
實際上索引也是一張表，該表儲存了主鍵與索引字段，并指向實體表的記錄，是以索引列也是要占空間的
雖然索引打打提高了查詢速度，同僚卻會降低更新表的速度，入隊表進行INSERT UPDATE 和DELETE，因為更新表示，MySQL不僅要儲存資料，還要儲存一下索引檔案每次更新添加了索引列的字段，都會調整因為更新所帶來的鍵值變化後的索引資訊。
索引知識提高效率的一個因素，如果你的MySQL有大資料的表，就需要花時間研究建立最優秀的索引或優化查詢語句
三、索引分類和建立索引指令語句
1、索引分類
- 單值索引：即一個索引隻包含單個列，一個表可以有多個單列索引
- 唯一索引：索引列的值必須唯一，但允許有空值
- 複合索引：即一個索引包含多個列
2、基本文法
（1）建立
CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));
ALERT mytable ADD [UNIQUE] INDEX [indexName] ON (columname(length))
（2）删除
DROP INDEX [indexName] ON mytable;
SHOW INDEX FROM table_name\G
（3）使用ALTER指令
有四種方式來添加資料庫表的索引
- ALTER TABLE tbl_name ADD PRIMARY KEY (column_list);該語句添加一個主鍵，這意味着索引值必須是唯一的，且不能為null
- ALTER TABLE tbl_name ADD UNIQUE index_name (column_list);這條語句建立索引的值必須是惟一的（除了NULL外，NULL可能會出現多次）
- ALTER TABLE tbl_name ADD INDEX index_name (column_list);添加普通索引，索引值可出現多次
- ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);改語句指定了索引為FULLTEXT，用于全文索引
四、索引結構與檢索原理
（1）結構
BTree索引、Hash索引、full-text索引、R-Tree索引
（2）檢索原理
【初始化介紹】
一顆B+樹，淺藍色的塊我們稱之為一個磁盤塊，可以看出每個磁盤塊包含幾個資料項（深藍色所示）和指針（黃色所示），如磁盤塊1包含資料項17和35，包含指針P1.P2.P3
P1表示小于17的磁盤塊，P2表示在17和35之間的磁盤塊，P3表示大于35的磁盤塊。
真實的資料存在于葉子節點即：3、5、9、10、13、15、28、29、36、60、75、79、90、99.
非葉子節點隻不存儲真實的資料，隻存儲指引索引方向的資料項，如17、35并不真實存在于資料庫表中。
【查找過程】
如果要查找資料項29，那麼首先會把磁盤塊1由磁盤加載到記憶體，此時發生一次IO，在記憶體中用二分查找确定29在17和35之間，鎖定磁盤塊1的P2指針，記憶體時間因為非常短（相比磁盤的IO）可以忽略不計，通過磁盤1的P2指針的磁盤位址把磁盤塊3由磁盤加載到記憶體，發生第二次IO，29在26和30之間，鎖定磁盤塊3的P2指針，通過指針加載磁盤塊8到記憶體，發生第三次IO，同僚記憶體中做二分查找找到29，結束查詢，總計三次IO。
真實的情況是，3層的b+樹可以表示上百萬的資料，如果上百萬的資料查找隻需要三次IO，性能提高将是巨大的，如果沒有索引，每個資料項都要發生一次IO，那麼總共需要百萬次的IO，顯然成本非常非常高。
五、那些情況适合建索引，那些情況不适合建索引
（1）需要
- 主鍵自動建立唯一索引
- 頻繁作為查詢條件的字段應該建立索引
- 查詢中與其他表關聯的字段，外鍵關系建立索引
- 頻繁更新的字段不适合建立索引，每次更新都要更新索引
- where條件裡用不到的字段不建立索引
- 單鍵/組合索引的選擇問題，在高并發下傾向建立組合索引
- 查詢中排序的字段，排序字段若通過索引去通路将大大提高排序速度
- 查詢中統計或者分組字段
（2）不需要
- 表記錄太少
- 經常增删改表的資料，提高了查詢速度，同時卻降低更新表的速度
- 資料重複且分布平均的表字段，是以應該隻為最經常查詢和最經常排序的資料列履歷索引，注意如果摸個資料包含許多重複内容，為他建立索引就沒有太大的實際效果
第五章、性能分析前提知識
1、MySQL Query Optimizer
（1）Mysql中有負責優化SELECT語句的優化器子產品，主要功能：通過計算分析系統中收集到的統計資訊，為用戶端請求的Query提供他認為最優的執行計劃（他認為最優的資料索引方式，但不見得是DBA認為是最優的，這部分最消耗時間）
（2）當用戶端向MySQL請求一條Query，指令解析器子產品完成請求分類，差別出示SELECT并轉發給MySQL Query Optimizer時，MySQL Query Optimizer首先會對整條query進行優化，處理掉一些常量表達式的預算，直接轉換算成常量值，并對Query中的查詢條件進行簡化和轉換，如去掉一些無用或顯而易見的條件，結構調整等，然後分析Query中的Hint資訊（如果有），看現實Hint資訊是否可以萬卻确定failQuery的執行計劃。如果沒有Hint或Hint資訊還不足以完全确定執行計劃，則會讀取所涉及對象的統計資訊，根據Query進行寫相應的計算分析，然後在得出最後的執行計劃
2、mysql常見瓶頸
- CPU:CPU在飽和的時候一般發生在資料裝入記憶體或從磁盤上讀取資料時候
- IO:磁盤I/O瓶頸發生在裝入資料遠大于記憶體容量的時候
- 伺服器硬體的性能瓶頸：top，free，iostat和vmstat來檢視系統的性能狀态
第六章、explain
（一）簡介
使用explain關鍵字可以模拟執行sql查詢語句，進而知道mysql是如何處理你的sql語句的，分析你的查詢語句或者是表結構的性能瓶頸
（二）能幹嘛
- 表的讀取順序
- 資料讀取操作的操作類型
- 那些索引可以使用
- 那些索引被實際使用
- 表之間的引用
- 每張表有多少行被優化器查詢
（三）怎麼玩
1、explain + SQL語句
2、執行計劃包換的資訊
（四）explain之id介紹
1、select 查詢的序列号，包含一組數字，表示查詢中執行select子句或操作表的順序
2、三種情況
（1）id相同，執行順序由上至下
先加載t1，再加載t3，最後加載t2
（2）id不同，如果是子查詢，id的序号回遞增，id值越大優先級越高，越先被執行
（3）id相同不同
（五）explain之select_type介紹
- SIMPLE:簡單的select查詢，查詢中不包含子查詢或者UNION
- PRIMARY：查詢中若包含任何複雜的字部分，最外層查詢則被标記
- SUBQUERY：在select或where清單中包含了子查詢
- DERIVED：在from清單中包含的子查詢被表姐為DERIVED(衍生)，mysql回遞歸執行這些子查詢，把結果放在臨時表中
- UNION：若第二個SELECT出現在UNION之後，則被标記為UNION;若UNION包含在FROM子句的子查詢中，外層SELECT将被表姐為DERIVED
- UNION RESULT
（六）explain之table介紹
顯示這一行的資料是關于哪張表的
（七）explain之type介紹
1、type代表查詢用了何種類型
2、類型排序（從最好到最壞依次是）
system>const>eq_ref>ref>range>index>ALL
一般來說，得保證至少達到range級别，最好能達到ref
3、type的詳解
①、system
表中有一行資料（等于系統表），這是const類型的特例，平時不會出現，這個可以忽略不計
②、const
表示通過索引一次就能找到了，const用于比較primary key或者unique索引。因為隻比對一行資料，是以很快，如将主鍵置于where清單中，mysql就能将該查詢轉換成一個常量
③、eq_ref
唯一性索引掃描，對于每個索引建，表中隻有一條記錄與之比對，常見于主鍵或者唯一索引掃描
④、ref
非唯一性索引掃描，傳回比對摸個單獨值得所有行
本質上也是一種索引通路，他傳回所有比對摸個單獨值的行，然而，他可能會找到多個符合條件的行，多以該屬于查找和掃描的混合體
（上面截圖，為t1表的兩個字段col1,col2建立了一個索引，然後查詢t1表col1字段的count最後進行一個查詢）
⑤、range
隻索引給定範圍的行，使用一個索引來選擇行，key列顯示使用了哪個索引
一般就是在你的where語句中出現了between、<、>、in等的查詢
這種範圍掃描索引比全表掃描要好，因為他隻需要開始于索引的某一點，而結束于另一點，不用去掃描全部索引
⑥、index
Full Index Scan，index和All差別為indx類型隻周遊索引樹。這通常比All快，因為索引檔案通常比資料檔案快
(也就是說雖然all和index都是讀全表，但index是從索引中讀取，而all是從硬碟中讀取)
⑦、all
full table scan，将周遊全表以找到比對的行
注釋：一般來說保證查詢至少達到range基本，最好能達到ref級别
（八）explain值possible_keys和key介紹
possible_keys理論上可能用到的索引
key實際用到的索引（查詢中若使用覆寫索引，則該索引僅出現在可以清單中）
覆寫索引：select後面接的字段（下面為col1，col2）和建立的符合索引的個數和順序一模一樣
理論上，應該用不到索引，實際上，select的列剛好和符合索引一樣，就掃描了（可以為ind_col1_col2）
（九）explain值key_len介紹
表示索引中使用的位元組數，可通過該列計算查詢中使用的索引的長度，在不損失精确性的情況下，長度越短越好
key_len顯示的值為索引字段的最大可能長度，并非實際使用長度，即key_len是根據表定義計算而得，不是通過表内檢索出來的
（十）explain之ref介紹
ref：顯示索引的哪一類被使用了，如果可能的話，是一個常數。哪些類或者常量用于查詢索引列上的值。
（十一）explain之rows介紹
根據表統計資訊及索引選用情況，大緻估算出所需的記錄所需要讀取的行數
（十二）explain值extra介紹
包含不适合在其他列中顯示但十分重要的額外資訊
1、Using filesort 久死一生
說明mysql會對資料使用一個外部的索引排序，而不是按照表内的索引書序進行排序
MySQL中無法利用索引完成的排序操作成為“檔案排序”
eg：下圖索引建立的規則是col1，col2，col3是以查詢用到了索引，但是排序的時候用不到索引了
查詢中排序的字段，排序字段若通過索引去通路将大大提高排序速度
2、Using temorary 十死無生
用不上索引，還産生了臨時表，常見于排序order by 和分組查詢group by
3.Using index 不錯
表示相應的select操作中使用了覆寫索引（Covering Index），避免通路了表的資料行，效率不錯
如果同時出現using where ，表明索引被用來執行索引鍵值的查找
如果沒有同時出現using where，表明索引用來讀取資料而非執行查找動作
覆寫索引
了解一：就是select的資料列隻用從索引中就能夠取得，不必讀取資料行，MySQL可以利用索引傳回select清單中的字段，而不必根據索引再次讀取資料檔案，換句話說查詢列要被所建的索引覆寫
了解二：索引是高效找到行的一個方法，但是一般資料庫也能使用索引找到一個列的資料，是以他不必讀取整個行，畢竟索引葉子節點存儲了它們索引的資料；當能通過讀取索引就可以得到想要的資料，那就不需要讀取行了，一個索引包含了（或覆寫了）滿足查詢結果的資料就叫做覆寫索引
注意：如果要使用覆寫索引，一定要注意select清單中隻取出需要的列，不能使select *，因為如果将所有的字段一起做索引會導緻索引檔案過大，查詢性能下降。
4、Using where
表明使用了where過濾
5、using join buffer
是引用了連接配接緩存
6、impossible where
你輸入的内容讓mysql錯亂了，where子句的值總是false，不能用來回去任何元組
7、select tables optimized away
在沒有group by子句的情況下，基于索引有啊MIN/MAX操作或者對于myisam存儲索引優化count（*）操作，不必等到執行階段在進行計算，查詢執行計劃生成的階段即完成優化
8、distinct
優化distinct操作，在找到第一比對的元組後即停止找同樣的動作