溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇內容介紹了“MySQL邏輯分層，存儲引擎，sql優化，索引優化以及底層實現方法”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！
連接層：連接與線程處理，這一層并不是MySQL獨有，一般的基于C/S架構的都有類似組件，比如連接處理、授權認證、安全等。
服務層：包括緩存查詢、解析器、優化器，這一部分是MySQL核心功能，包括解析、優化SQL語句，查詢緩存目錄，內置函數（日期、時間、加密等函數）的實現。
引擎層：負責數據存儲，存儲引擎的不同，存儲方式、數據格式、提取方式等都不相同，這一部分也是很大影響數據存儲與提取的性能的；對存儲層的抽象。
存儲層：存儲數據，文件系統。
二 ， 存儲引擎
查看數據庫支持的存儲引擎：show engines;
如果要想查看數據庫默認使用哪個引擎，可以通過使用命令： show variables like '%storage_engine%';
指定數據庫對象的引擎：
create table tb( id int(4) auto_increment , name varchar(5), dept varchar(5) , primary key(id) )ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ;
查看建表語句:show create table default_table;
雖然 MySQL 里的存儲引擎不只是 MyISAM 與 InnoDB 這兩個，但常用的就是它倆了。可能有站長并未注意過 MySQL 的存儲引擎，其實存儲引擎也是數據庫設計里的一大重要點，那么博客系統應該使用哪種存儲引擎呢？
下面我們分別來看兩種存儲引擎的區別。
一、InnoDB支持事務，MyISAM不支持，這一點是非常之重要。事務是一種高級的處理方式，如在一些列增刪改中只要哪個出錯還可以回滾還原，而MyISAM就不可以了。
二、MyISAM適合查詢以及插入為主的應用，InnoDB適合頻繁修改以及涉及到安全性較高的應用
三、InnoDB支持外鍵，MyISAM不支持
四、MyISAM是默認引擎，InnoDB需要指定
五、InnoDB不支持FULLTEXT類型的索引
六、InnoDB中不保存表的行數，如select count(*) from table時，InnoDB需要掃描一遍整個表來計算有多少行，但是MyISAM只要簡單的讀出保存好的行數即可。注意的是，當count(*)語句包含where條件時MyISAM也需要掃描整個表
七、對于自增長的字段，InnoDB中必須包含只有該字段的索引，但是在MyISAM表中可以和其他字段一起建立聯合索引
八、清空整個表時，InnoDB是一行一行的刪除，效率非常慢。MyISAM則會重建表
九、InnoDB支持行鎖（某些情況下還是鎖整表，如 update table set a=1 where user like '%lee%'
通過以上九點區別，結合個人博客的特點，推薦個人博客系統使用MyISAM，因為在博客里主要操作是讀取和寫入，很少有鏈式操作。所以選擇MyISAM引擎使你博客打開也頁面的效率要高于InnoDB引擎的博客，當然只是個人的建議，大多數博客還是根據實際情況下謹慎選擇。
三， sql優化
3.1.1 mysql 內部實現索引原理（B+Tree）
3.1.1.1 ， 二叉樹
3.1.1.2 ， B-Tree
B-Tree中的每個節點根據實際情況可以包含大量的關鍵字信息和分支，如下圖所示為一個3階的B-Tree：
每個節點占用一個盤塊的磁盤空間，一個節點上有兩個升序排序的關鍵字和三個指向子樹根節點的指針，指針存儲的是子節點所在磁盤塊的地址。兩個關鍵詞劃分成的三個范圍域對應三個指針指向的子樹的數據的范圍域。以根節點為例，關鍵字為17和35，P1指針指向的子樹的數據范圍為小于17，P2指針指向的子樹的數據范圍為17~35，P3指針指向的子樹的數據范圍為大于35。
模擬查找關鍵字29的過程：
根據根節點找到磁盤塊1，讀入內存。【磁盤I/O操作第1次】
比較關鍵字29在區間（17,35），找到磁盤塊1的指針P2。
根據P2指針找到磁盤塊3，讀入內存。【磁盤I/O操作第2次】
比較關鍵字29在區間（26,30），找到磁盤塊3的指針P2。
根據P2指針找到磁盤塊8，讀入內存。【磁盤I/O操作第3次】
在磁盤塊8中的關鍵字列表中找到關鍵字29。
分析上面過程，發現需要3次磁盤I/O操作，和3次內存查找操作。由于內存中的關鍵字是一個有序表結構，可以利用二分法查找提高效率。而3次磁盤I/O操作是影響整個B-Tree查找效率的決定因素。
B-Tree相對于AVLTree縮減了節點個數，使每次磁盤I/O取到內存的數據都發揮了作用，從而提高了查詢效率。
3.1.1.3 ， B+Tree(查詢任意數據的次數是 n)
B+Tree是在B-Tree基礎上的一種優化，使其更適合實現外存儲索引結構，InnoDB存儲引擎就是用B+Tree實現其索引結構。
從上一節中的B-Tree結構圖中可以看到每個節點中不僅包含數據的key值，還有data值。而每一個頁的存儲空間是有限的，如果data數據較大時將會導致每個節點（即一個頁）能存儲的key的數量很小，當存儲的數據量很大時同樣會導致B-Tree的深度較大，增大查詢時的磁盤I/O次數，進而影響查詢效率。在B+Tree中，所有數據記錄節點都是按照鍵值大小順序存放在同一層的葉子節點上，而非葉子節點上只存儲key值信息，這樣可以大大加大每個節點存儲的key值數量，降低B+Tree的高度。
B+Tree相對于B-Tree有幾點不同：
非葉子節點只存儲鍵值信息。
所有葉子節點之間都有一個鏈指針。
數據記錄都存放在葉子節點中。
將上一節中的B-Tree優化，由于B+Tree的非葉子節點只存儲鍵值信息，假設每個磁盤塊能存儲4個鍵值及指針信息，則變成B+Tree后其結構如下圖所示：
通常在B+Tree上有兩個頭指針，一個指向根節點，另一個指向關鍵字最小的葉子節點，而且所有葉子節點（即數據節點）之間是一種鏈式環結構。因此可以對B+Tree進行兩種查找運算：一種是對于主鍵的范圍查找和分頁查找，另一種是從根節點開始，進行隨機查找。
上面都應該知道B+Tree 了吧，所以我們在建立索引時，會生成一個B+Tree 如果我們在只查詢索引字段時，sql 語句就直接去B+Tree 查，不會再去數據表中查了，這樣提升性能是很重要的。 還有就是對于總是修改的字段不要對他建立索引，因為字段修改了，B+Tree 結構就要重構，這要是會降低性能的。
3.1.1 索引分類：
mysql索引的四種類型：主鍵索引、唯一索引、普通索引和全文索引。通過給字段添加索引可以提高數據的讀取速度，提高項目的并發能力和抗壓能力。索引優化時mysql中的一種優化方式。索引的作用相當于圖書的目錄，可以根據目錄中的頁碼快速找到所需的內容。
主鍵索引: 主鍵是一種唯一性索引，但它必須指定為PRIMARY KEY，每個表只能有一個主鍵。
alert table tablename add primary key (`字段名`)
唯一索引: 索引列的所有值都只能出現一次，即必須唯一，值可以為空。
alter table table_name add primary key (`字段名`);
普通索引 : 基本的索引類型，值可以為空，沒有唯一性的限制。
alter table table_name add index (`字段名`);
全文索引:
全文索引的索引類型為FULLTEXT。全文索引可以在varchar、char、text類型的列上創建。可以通過ALTER TABLE或CREATE INDEX命令創建。對于大規模的數據集，通過ALTER TABLE（或者CREATE INDEX）命令創建全文索引要比把記錄插入帶有全文索引的空表更快。MyISAM支持全文索引，InnoDB在mysql5.6之后支持了全文索引。 全文索引不支持中文需要借sphinx(coreseek)或迅搜<、code>技術處理中文。
3.2.2 索引的機制
1.為什么我們添加完索引后查詢速度為變快？
傳統的查詢方法，是按照表的順序遍歷的，不論查詢幾條數據，mysql需要將表的數據從頭到尾遍歷一遍
在我們添加完索引之后，mysql一般通過BTREE算法生成一個索引文件，在查詢數據庫時，找到索引文件進行遍歷(折半查找大幅查詢效率)，找到相應的鍵從而獲取數據
2.索引的代價
2.1創建索引是為產生索引文件的，占用磁盤空間
2.2索引文件是一個二叉樹類型的文件，可想而知我們的dml操作同樣也會對索引文件進行修改，所以性能會下降
3.在哪些column上使用索引？
3.1較頻繁的作為查詢條件字段應該創建索引
3.2唯一性太差的字段不適合創建索引，盡管頻繁作為查詢條件，例如gender性別字段
3.3更新非常頻繁的字段不適合作為索引
3.4不會出現在where子句中的字段不該創建索引
總結: 滿足以下條件的字段，才應該創建索引.
a: 肯定在where條經常使用 b: 該字段的內容不是唯一的幾個值 c: 字段內容不是頻繁變化。
3.2.2、SQL解析順序
接下來再走一步，讓我們看看一條SQL語句的前世今生。
首先看一下示例語句
SELECT DISTINCT ..... FROM ..... JOIN ..... ON ..... WHERE..... GROUP BY ..... HAVING ..... ORDER BY ..... LIMIT .....
然而它的執行順序是這樣的
FROM ..... ON ..... JOIN ..... WHERE ..... GROUP BY ..... HAVING ..... SELECT DISTINCT ..... ORDER BY ..... LIMIT .....
3.2.3 如何建立索引
一般說來，索引應建立在那些將用于JOIN,WHERE判斷和ORDERBY排序的字段上。盡量不要對數據庫中某個含有大量重復的值的字段建立索引。對于一個ENUM類型的字段來說，出現大量重復值是很有可能的情況.
3.2.4 使用索引時，有一些技巧：
1.索引不會包含有NULL的列
只要列中包含有NULL值，都將不會被包含在索引中，復合索引中只要有一列含有NULL值，那么這一列對于此符合索引就是無效的。
2. 索引要建立在經常進行select操作的字段上。而經常修改的字段，沒沒必要建立索引了，因為，你建立了索引會生成一個B+樹，你修改了該索引的字段后，這個B+樹就需要修改，反而對性能不是很好。
3. 復合索引 : 復合索引，不要跨列或無序使用（最佳左前綴）
4.like語句操作: 一般情況下不鼓勵使用like操作，如果非使用不可，注意正確的使用方式。like ‘%aaa%’不會使用索引，而like ‘aaa%’可以使用索引。
5. 不要在索引上進行任何操作（計算、函數、類型轉換），否則索引失效
6.不使用NOT IN 、<>、！=操作，但<,<=，=，>,>=,BETWEEN,IN是可以用到索引的
7.索引要建立在經常進行select操作的字段上。
這是因為，如果這些列很少用到，那么有無索引并不能明顯改變查詢速度。相反，由于增加了索引，反而降低了系統的維護速度和增大了空間需求。
8.索引要建立在值比較唯一的字段上。
9.對于那些定義為text、image和bit數據類型的列不應該增加索引。因為這些列的數據量要么相當大，要么取值很少。
10.在join操作中(需要從多個數據表提取數據時)，mysql只有在主鍵和外鍵的數據類型相同時才能使用索引，否則及時建立了索引也不會使用。
三， sql性能問題
a.分析SQL的執行計劃 : explain ，可以模擬SQL優化器執行SQL語句，從而讓開發人員 知道自己編寫的SQL狀況
b.MySQL查詢優化其會干擾我們的優化（mysql服務層有一個sql優化器），可以對我們寫的sql進行優化，這是我們控制不了的。
查詢執行計劃： explain +SQL語句 explain SELECT * from book ;
id : 編號
select_type ：查詢類型
table ：表
type :索引類型 system>const>eq_ref>ref>range>index>all ，要對type進行優化的前提：有索引 一般能達到range 就行。
possible_keys ：預測用到的索引
key ：實際使用的索引
key_len ：實際使用索引的長度
ref :表之間的引用
rows ：通過索引查詢到的數據量
Extra :額外的信息 下面是他可能發出的情況
i). using filesort ： 性能消耗大；需要“額外”的一次排序（查詢） 。常見于 order by 語句中。 解決：where哪些字段，就order by那些字段2
ii). using temporary:性能損耗大 ，用到了臨時表。一般出現在group by 語句中。 解決： 避免：查詢那些列，就根據那些列 group by .
iii). using index :性能提升; 索引覆蓋（覆蓋索引）。原因：不讀取原文件，只從索引文件中獲取數據 （不需要回表查詢）
只要使用到的列 全部都在索引中，就是索引覆蓋using index
iii).using where （需要回表查詢）。
假設age是索引列
但查詢語句select age,name from ...where age =...,此語句中必須回原表查Name，因此會顯示using where. 解決 吧name 也添加到索引中去。
“MySQL邏輯分層，存儲引擎，sql優化，索引優化以及底層實現方法”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
