1. MySQL架構邏輯設計
1.1 MySQL的邏輯架構圖
Mysql的架構總體分為四層：
- 用戶端：各種語言都提供了連接配接mysql資料庫的方法，比如jdbc、php、go等，可根據選擇 的後端開發語言選擇相應的方法或架構連接配接mysql
- server層：包括連接配接器、查詢緩存、分析器、優化器、執行器等，涵蓋mysql的大多數核心服務功能，以及所有的内置函數（例如日期、世家、數 學和加密函數等），所有跨存儲引擎的功能都在這一層實作，比如存儲過程、觸發器、視圖等。
- 存儲引擎層：負責資料的存儲和提取，是真正與底層實體檔案打交道的元件。 資料本質是存儲在磁盤上的，通過特定的存儲引擎對資料進行有組織的存放并根據業務需要對資料進行提取。存儲引擎的架構模式是插件式的，支援Innodb，MyIASM、Memory等多個存儲引擎。現在最常用的存儲引擎是Innodb，它從mysql5.5.5版本開始成為了預設存儲引擎。
- 實體檔案層：存儲資料庫真正的表資料、日志等。實體檔案包括：redolog、undolog、binlog、errorlog、querylog、slowlog、data、index等。
- binlog由server層産生，無論什麼搜尋引擎都有binlog；redolog隻有innodb存儲引擎産生。
1.2 什麼是MySQL高性能?
什麼是高性能，每個人都有不同的回答，如：“每秒查詢次數”、“CPU使用率”、“可擴充性”。《高性能Mysql》中給出的定義是“響應時間”，它認為CPU使用率來作為高性能的名額是悖論，CPU使用率低恰恰說明性能差對cpu使用率不高，真正有效的是“查詢響應時間”。
mysql的高性能主要展現在寫和讀上，在寫的層面公司DBA已經進行了主從主備的高可用部署，同時對redolog、binlog等有很好的管理，基于我們目前對快速讀的需求比較多，本文主要探讨怎麼高性能讀？
1.3 基本概念
原理先行，實踐為後，我們需要先明白mysql優化中常見的基本概念。
(1) 資料頁
為了避免一條一條讀取磁盤資料，InnoDB采取頁的方式，作為磁盤和記憶體之間互動的基本機關。一個頁的大小一般是16KB。
頁存儲的過程：
（1）在一開始生成頁的時候，沒有UserRecords部分。
（2）當插入一條記錄時，就會從Free Space中申請一個記錄大小的空間，并将這個空間劃分到User Records部分。
（3）當Free Space部分的空間全部都被User Records部分替代掉後，則這個頁使用完了，如果再有新的記錄插入，則需要去申請新的頁了。
(2) 回表
衆所周知Mysql預設使用的InnoDB資料庫，引擎使用的資料結構是B+樹，索引分類如果按照實體存儲方式來看，分為聚簇索引和非聚簇索引。
聚簇索引：主鍵索引 ； 非聚簇索引：非主鍵索引
1.4 緩存命中及LRU算法
1.4.1 buffer pool緩沖池
上述我們講到Mysql再查詢時優先命中緩存，此處的緩存區叫做“buffer pool(緩存池) ”。
- buffer pool 是資料庫的一個記憶體元件，裡面緩存了磁盤上的真實資料，Java系統對資料庫的增删改操作，主要是這個記憶體資料結構中的緩存資料執行的。
- 緩存和磁盤是對應的，都是使用頁存儲，緩存區叫緩存頁，也是16KB
- 緩存區的資料是從磁盤中查詢，然後通過LRU算法存入的緩存區中的
- Buffer Pool 對查詢的加速效果，依賴于一個重要的名額，即：記憶體命中率
buffer pool内部存儲它是一個hash結構。優化器會通過這張表所對應的“表空間+頁号”計算為key，然後通過value對應的緩沖頁的控制塊
緩沖池（buffer pool）它是有固定大小的，雖然我們一頁是資料是16KB。但是資料頁多了，難免會把緩沖池（buffer pool）撐滿，此時通過lru算法淘汰資料頁。其實也可以了解為lru存儲的也是“表空間+頁号”資料。
1.4.2 LRU算法
政策：緩存區的資料更新是通過LRU算法更新，LRU算法的政策是：“最近最少使用”。
結構：在 InnoDB 實作上，按照 5:3 的比例把整個 LRU 連結清單分成了 young 區域和 old 區域。前5/8處是年輕代，後3/8是老年代。
算法：
- 新讀取的資料會被存入old區域，下一個新資料進來會在old區前插，原資料以此後移；
- 如果資料在old區域存在時間超過1s,就會前移到首位，進入young區域，其他資料依次超過1s前移；
- 對young區資料通路，被通路資料會前移到首位
根據上面的LRU算法，如果全表掃描，全表掃描的資料不會被寫入緩存young區被命中。
掃描過程中，需要新插入的資料頁，都被放到 old 區域 ; 一個資料頁裡面有多條記錄，這個資料頁會被多次通路到，但由于是順序掃描，這個資料頁第一次被通路和最後一次被通路的時間間隔不會超過 1 秒，是以還是會被保留在 old 區域； 再繼續掃描後續的資料，之前的這個資料頁之後也不會再被通路到，于是始終沒有機會移到連結清單頭部（也就是 young 區域），很快就會被淘汰出去。
2. 索引優化
2.1 索引結構
2.1.1 常見的索引結構模型
（1）哈希表，數組+連結清單的行式，這種結構适合用于等值查詢的場景 （2）有序數組，單從查詢效率上來說，查詢快更新慢，是以有序數組隻适合用于靜态存儲引擎 （3）搜尋樹，平衡二叉樹查詢和更新的時間複雜度都是O(log(n))，InnoDB引擎使用的是B+樹； （4）資料庫技術發展到今天，跳表、LSM 樹等資料結構也被用于引擎設計中
2.1.2 B+ tree
1）“B+樹”+“資料頁”的視角了解索引的查詢邏輯，才能更清晰了解查詢過程。
- 隻有葉子節點（最底層的節點）才存放了資料，非葉子節點（其他上層節）僅用來存放目錄頁作為索引。
- 非葉子節點分為不同層次，通過分層來降低每一層的搜尋量；
- 所有節點按照索引鍵大小排序，構成一個雙向連結清單，便于範圍查詢；
（2）B+樹的時間複雜度：O(logN) ,上述我們想查到6，隻需要查詢二次即可。
(3) 通過上述查找過程，我們看出，頁數越少，層級約淺，越好查找。是以索引建立規則中：“索引越小越好（即索引要有辨識度） ”
2.2 索引優化
2.2.1 索引分類
按照實體存儲方式，索引可以分為：聚簇索引(主鍵索引) 和非聚簇索引(二級索引) 。
- 聚簇索引的葉子節點存放的是實際資料，所有完整的使用者記錄都存放在聚簇索引的葉子節點；
- 二級索引的葉子節點存放的是主鍵值，而不是實際資料。
是以二級索引查詢非索引字段時，需要回表。
2.2.2 各索引優缺點
- 覆寫索引
覆寫索引是select的資料列隻用從索引中就能夠取得，不必讀取資料行，換句話說查詢列要被所建的索引覆寫。
優點：避免回表，覆寫索引特權很大，可以無視最左比對原則
缺點：需要建立大量複合索引，建議單表索引數量低于5個
- 最左字首原則
最左字首可以是聯合索引的最左N個字段也可以是字元串索引的最左N個字元。
此處需要注意的是，如"where name like ‘張 %’"，一般來講like是不走索引的，但是索引支援字元最左比對，是以，比對了字元走了索引
建立複合索引時，需要注意順序，存在(a,b)則不需要額外建立a索引，節約空間
- 索引下沉（MYSQL5.6）
(name, age)，select * from table where name like 'zhan%' and age = 10 and male = 1，這個用到了什麼索引？是否比單索引更快一點？
根據最左比對，雖然age 引失效了，但是會起到過濾作用，無需回表過濾查詢age=10的，直接從二級索引中取值了，這個過程叫做**“索引下沉”。
- 全文索引（MYSQL5.7.6）
你是否對多個字段想一起模糊查詢，隻要有一個字段比對，即展示結果？
這個可以使用全文索引實作，全文索引是FultLeXT類型的索引，隻能用于InnoDB或MyISAM表,隻能為CHAR、VARCHAR、TEXT列建立，MySQL5.7.6中支援了中文。
索引的最終選擇是優化器的職責，優化器選擇的依據是：掃描行數、臨時表、排序
3. 總結
我們從Mysql查詢鍊路的架構設計以及存儲查詢的實體邏輯分析，得到如上的邏輯圖，進而在日常mysql查詢中，我們應當考慮建立合适的索引，進而提升mysql查詢的性能。