MySQL邏輯分層，存儲引擎，sql優(yōu)化，索引優(yōu)化以及底層實現(xiàn)方法

本篇內容介紹了“MySQL邏輯分層，存儲引擎，sql優(yōu)化，索引優(yōu)化以及底層實現(xiàn)方法”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

一 ， 邏輯分層　　

連接層：連接與線程處理，這一層并不是MySQL獨有，一般的基于C/S架構的都有類似組件，比如連接處理、授權認證、安全等。

服務層：包括緩存查詢、解析器、優(yōu)化器，這一部分是MySQL核心功能，包括解析、優(yōu)化SQL語句，查詢緩存目錄，內置函數(shù)（日期、時間、加密等函數(shù)）的實現(xiàn)。

引擎層：負責數(shù)據(jù)存儲，存儲引擎的不同，存儲方式、數(shù)據(jù)格式、提取方式等都不相同，這一部分也是很大影響數(shù)據(jù)存儲與提取的性能的；對存儲層的抽象。

存儲層：存儲數(shù)據(jù)，文件系統(tǒng)。

二 ， 存儲引擎

查看數(shù)據(jù)庫支持的存儲引擎：show engines;

如果要想查看數(shù)據(jù)庫默認使用哪個引擎，可以通過使用命令： show variables like '%storage_engine%';

指定數(shù)據(jù)庫對象的引擎：

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 里的存儲引擎不只是 MyISAM 與 InnoDB 這兩個，但常用的就是它倆了?？赡苡姓鹃L并未注意過 MySQL 的存儲引擎，其實存儲引擎也是數(shù)據(jù)庫設計里的一大重要點，那么博客系統(tǒng)應該使用哪種存儲引擎呢？

下面我們分別來看兩種存儲引擎的區(qū)別。

• 一、InnoDB支持事務，MyISAM不支持，這一點是非常之重要。事務是一種高級的處理方式，如在一些列增刪改中只要哪個出錯還可以回滾還原，而MyISAM就不可以了。

• 二、MyISAM適合查詢以及插入為主的應用，InnoDB適合頻繁修改以及涉及到安全性較高的應用

• 三、InnoDB支持外鍵，MyISAM不支持

• 四、MyISAM是默認引擎，InnoDB需要指定

• 五、InnoDB不支持FULLTEXT類型的索引

• 六、InnoDB中不保存表的行數(shù)，如select count(*) from table時，InnoDB需要掃描一遍整個表來計算有多少行，但是MyISAM只要簡單的讀出保存好的行數(shù)即可。注意的是，當count(*)語句包含where條件時MyISAM也需要掃描整個表

• 七、對于自增長的字段，InnoDB中必須包含只有該字段的索引，但是在MyISAM表中可以和其他字段一起建立聯(lián)合索引

• 八、清空整個表時，InnoDB是一行一行的刪除，效率非常慢。MyISAM則會重建表

• 九、InnoDB支持行鎖（某些情況下還是鎖整表，如 update table set a=1 where user like '%lee%'

通過以上九點區(qū)別，結合個人博客的特點，推薦個人博客系統(tǒng)使用MyISAM，因為在博客里主要操作是讀取和寫入，很少有鏈式操作。所以選擇MyISAM引擎使你博客打開也頁面的效率要高于InnoDB引擎的博客，當然只是個人的建議，大多數(shù)博客還是根據(jù)實際情況下謹慎選擇。

三， sql優(yōu)化

3.1.1  mysql 內部實現(xiàn)索引原理（B+Tree）

3.1.1.1 ，  二叉樹

3.1.1.2 ，  B-Tree　　　　　　　　

　B-Tree中的每個節(jié)點根據(jù)實際情況可以包含大量的關鍵字信息和分支，如下圖所示為一個3階的B-Tree： 
　　　　　　　　

每個節(jié)點占用一個盤塊的磁盤空間，一個節(jié)點上有兩個升序排序的關鍵字和三個指向子樹根節(jié)點的指針，指針存儲的是子節(jié)點所在磁盤塊的地址。兩個關鍵詞劃分成的三個范圍域對應三個指針指向的子樹的數(shù)據(jù)的范圍域。以根節(jié)點為例，關鍵字為17和35，P1指針指向的子樹的數(shù)據(jù)范圍為小于17，P2指針指向的子樹的數(shù)據(jù)范圍為17~35，P3指針指向的子樹的數(shù)據(jù)范圍為大于35。

模擬查找關鍵字29的過程：

1. 根據(jù)根節(jié)點找到磁盤塊1，讀入內存?！敬疟PI/O操作第1次】　

2. 比較關鍵字29在區(qū)間（17,35），找到磁盤塊1的指針P2。

3. 根據(jù)P2指針找到磁盤塊3，讀入內存?！敬疟PI/O操作第2次】

4. 比較關鍵字29在區(qū)間（26,30），找到磁盤塊3的指針P2。

5. 根據(jù)P2指針找到磁盤塊8，讀入內存?！敬疟PI/O操作第3次】

6. 在磁盤塊8中的關鍵字列表中找到關鍵字29。

　分析上面過程，發(fā)現(xiàn)需要3次磁盤I/O操作，和3次內存查找操作。由于內存中的關鍵字是一個有序表結構，可以利用二分法查找提高效率。而3次磁盤I/O操作是影響整個B-Tree查找效率的決定因素。

　B-Tree相對于AVLTree縮減了節(jié)點個數(shù)，使每次磁盤I/O取到內存的數(shù)據(jù)都發(fā)揮了作用，從而提高了查詢效率。

3.1.1.3 ，  B+Tree(查詢任意數(shù)據(jù)的次數(shù)是 n)

　　B+Tree是在B-Tree基礎上的一種優(yōu)化，使其更適合實現(xiàn)外存儲索引結構，InnoDB存儲引擎就是用B+Tree實現(xiàn)其索引結構。

從上一節(jié)中的B-Tree結構圖中可以看到每個節(jié)點中不僅包含數(shù)據(jù)的key值，還有data值。而每一個頁的存儲空間是有限的，如果data數(shù)據(jù)較大時將會導致每個節(jié)點（即一個頁）能存儲的key的數(shù)量很小，當存儲的數(shù)據(jù)量很大時同樣會導致B-Tree的深度較大，增大查詢時的磁盤I/O次數(shù)，進而影響查詢效率。在B+Tree中，所有數(shù)據(jù)記錄節(jié)點都是按照鍵值大小順序存放在同一層的葉子節(jié)點上，而非葉子節(jié)點上只存儲key值信息，這樣可以大大加大每個節(jié)點存儲的key值數(shù)量，降低B+Tree的高度。

B+Tree相對于B-Tree有幾點不同：

1. 非葉子節(jié)點只存儲鍵值信息。

2. 所有葉子節(jié)點之間都有一個鏈指針。

3. 數(shù)據(jù)記錄都存放在葉子節(jié)點中。

將上一節(jié)中的B-Tree優(yōu)化，由于B+Tree的非葉子節(jié)點只存儲鍵值信息，假設每個磁盤塊能存儲4個鍵值及指針信息，則變成B+Tree后其結構如下圖所示： 

通常在B+Tree上有兩個頭指針，一個指向根節(jié)點，另一個指向關鍵字最小的葉子節(jié)點，而且所有葉子節(jié)點（即數(shù)據(jù)節(jié)點）之間是一種鏈式環(huán)結構。因此可以對B+Tree進行兩種查找運算：一種是對于主鍵的范圍查找和分頁查找，另一種是從根節(jié)點開始，進行隨機查找。

上面都應該知道B+Tree 了吧，所以我們在建立索引時，會生成一個B+Tree  如果我們在只查詢索引字段時，sql 語句就直接去B+Tree 查，不會再去數(shù)據(jù)表中查了，這樣提升性能是很重要的。 還有就是對于總是修改的字段不要對他建立索引，因為字段修改了，B+Tree 結構就要重構，這要是會降低性能的。

3.1.1 索引分類：

mysql索引的四種類型：主鍵索引、唯一索引、普通索引和全文索引。通過給字段添加索引可以提高數(shù)據(jù)的讀取速度，提高項目的并發(fā)能力和抗壓能力。索引優(yōu)化時mysql中的一種優(yōu)化方式。索引的作用相當于圖書的目錄，可以根據(jù)目錄中的頁碼快速找到所需的內容。

主鍵索引:  主鍵是一種唯一性索引，但它必須指定為PRIMARY KEY，每個表只能有一個主鍵。

alert table tablename add primary key (`字段名`)

  唯一索引:    索引列的所有值都只能出現(xiàn)一次，即必須唯一，值可以為空。

alter table table_name add primary key (`字段名`);

   普通索引 :   基本的索引類型，值可以為空，沒有唯一性的限制。

alter table table_name add index (`字段名`);

   全文索引:
        全文索引的索引類型為FULLTEXT。全文索引可以在varchar、char、text類型的列上創(chuàng)建?？梢酝ㄟ^ALTER TABLE或CREATE INDEX命令創(chuàng)建。對于大規(guī)模的數(shù)據(jù)集，通過ALTER TABLE（或者CREATE INDEX）命令創(chuàng)建全文索引要比把記錄插入帶有全文索引的空表更快。MyISAM支持全文索引，InnoDB在mysql5.6之后支持了全文索引。        全文索引不支持中文需要借sphinx(coreseek)或迅搜<、code>技術處理中文。

3.2.2 索引的機制

1.為什么我們添加完索引后查詢速度為變快？

    傳統(tǒng)的查詢方法，是按照表的順序遍歷的，不論查詢幾條數(shù)據(jù)，mysql需要將表的數(shù)據(jù)從頭到尾遍歷一遍

    在我們添加完索引之后，mysql一般通過BTREE算法生成一個索引文件，在查詢數(shù)據(jù)庫時，找到索引文件進行遍歷(折半查找大幅查詢效率)，找到相應的鍵從而獲取數(shù)據(jù)

2.索引的代價
    2.1創(chuàng)建索引是為產(chǎn)生索引文件的，占用磁盤空間
    2.2索引文件是一個二叉樹類型的文件，可想而知我們的dml操作同樣也會對索引文件進行修改，所以性能會下降

3.在哪些column上使用索引？
    3.1較頻繁的作為查詢條件字段應該創(chuàng)建索引
    3.2唯一性太差的字段不適合創(chuàng)建索引，盡管頻繁作為查詢條件，例如gender性別字段
    3.3更新非常頻繁的字段不適合作為索引
    3.4不會出現(xiàn)在where子句中的字段不該創(chuàng)建索引

總結: 滿足以下條件的字段，才應該創(chuàng)建索引.
a: 肯定在where條經(jīng)常使用 b: 該字段的內容不是唯一的幾個值 c: 字段內容不是頻繁變化。

3.2.2、SQL解析順序

接下來再走一步，讓我們看看一條SQL語句的前世今生。

首先看一下示例語句

SELECT DISTINCT  .....   FROM .....  JOIN  .....  ON   .....  WHERE.....   GROUP BY    .....   HAVING  .....   ORDER BY   .....   LIMIT .....

　 然而它的執(zhí)行順序是這樣的

FROM   .....  ON  .....    JOIN  .....   WHERE     ..... GROUP BY     .....  HAVING  .....    SELECT    DISTINCT   .....  ORDER BY   .....  LIMIT   .....

   3.2.3 如何建立索引　　　　　　　　　　

　一般說來，索引應建立在那些將用于JOIN,WHERE判斷和ORDERBY排序的字段上。盡量不要對數(shù)據(jù)庫中某個含有大量重復的值的字段建立索引。對于一個ENUM類型的字段來說，出現(xiàn)大量重復值是很有可能的情況.

　3.2.4     使用索引時，有一些技巧：

　 1.索引不會包含有NULL的列

　 只要列中包含有NULL值，都將不會被包含在索引中，復合索引中只要有一列含有NULL值，那么這一列對于此符合索引就是無效的。

　2.　索引要建立在經(jīng)常進行select操作的字段上。而經(jīng)常修改的字段，沒沒必要建立索引了，因為，你建立了索引會生成一個B+樹，你修改了該索引的字段后，這個B+樹就需要修改，反而對性能不是很好。

　3. 復合索引 : 復合索引，不要跨列或無序使用（最佳左前綴）

　4.like語句操作: 一般情況下不鼓勵使用like操作，如果非使用不可，注意正確的使用方式。like ‘%aaa%’不會使用索引，而like ‘a(chǎn)aa%’可以使用索引。

    5. 不要在索引上進行任何操作（計算、函數(shù)、類型轉換），否則索引失效

   6.不使用NOT IN 、<>、！=操作，但<,<=，=，>,>=,BETWEEN,IN是可以用到索引的

   7.索引要建立在經(jīng)常進行select操作的字段上。

  這是因為，如果這些列很少用到，那么有無索引并不能明顯改變查詢速度。相反，由于增加了索引，反而降低了系統(tǒng)的維護速度和增大了空間需求。

   8.索引要建立在值比較唯一的字段上。

    9.對于那些定義為text、image和bit數(shù)據(jù)類型的列不應該增加索引。因為這些列的數(shù)據(jù)量要么相當大，要么取值很少。

   10.在join操作中(需要從多個數(shù)據(jù)表提取數(shù)據(jù)時)，mysql只有在主鍵和外鍵的數(shù)據(jù)類型相同時才能使用索引，否則及時建立了索引也不會使用。

 三， sql性能問題

　　a.分析SQL的執(zhí)行計劃  : explain   ，可以模擬SQL優(yōu)化器執(zhí)行SQL語句，從而讓開發(fā)人員 知道自己編寫的SQL狀況

　　b.MySQL查詢優(yōu)化其會干擾我們的優(yōu)化（mysql服務層有一個sql優(yōu)化器），可以對我們寫的sql進行優(yōu)化，這是我們控制不了的。

　　查詢執(zhí)行計劃：  explain +SQL語句     explain SELECT * from book ;

id : 編號
select_type ：查詢類型
table ：表
type :索引類型 system>const>eq_ref>ref>range>index>all ，要對type進行優(yōu)化的前提：有索引 一般能達到range 就行。
possible_keys ：預測用到的索引
key ：實際使用的索引
key_len ：實際使用索引的長度
ref :表之間的引用
rows ：通過索引查詢到的數(shù)據(jù)量
Extra :額外的信息 下面是他可能發(fā)出的情況

　　i). using filesort ： 性能消耗大；需要“額外”的一次排序（查詢）  。常見于 order by 語句中。 解決：where哪些字段，就order by那些字段2

　　ii). using temporary:性能損耗大 ，用到了臨時表。一般出現(xiàn)在group by 語句中。 解決： 避免：查詢那些列，就根據(jù)那些列 group by .

　　iii). using index :性能提升; 索引覆蓋（覆蓋索引）。原因：不讀取原文件，只從索引文件中獲取數(shù)據(jù) （不需要回表查詢）
只要使用到的列 全部都在索引中，就是索引覆蓋using index

　　iii).using where （需要回表查詢）。

　　　　假設age是索引列
　　　　但查詢語句select age,name from ...where age =...,此語句中必須回原表查Name，因此會顯示using where.  解決 吧name  也添加到索引中去。

“MySQL邏輯分層，存儲引擎，sql優(yōu)化，索引優(yōu)化以及底層實現(xiàn)方法”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關的知識可以關注億速云網(wǎng)站，小編將為大家輸出更多高質量的實用文章！