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

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

讓客戶滿意是我們工作的目標(biāo)，不斷超越客戶的期望值來(lái)自于我們對(duì)這個(gè)行業(yè)的熱愛。我們立志把好的技術(shù)通過有效、簡(jiǎn)單的方式提供給客戶，將通過不懈努力成為客戶在信息化領(lǐng)域值得信任、有價(jià)值的長(zhǎng)期合作伙伴，公司提供的服務(wù)項(xiàng)目有：主機(jī)域名、虛擬主機(jī)、營(yíng)銷軟件、網(wǎng)站建設(shè)、北侖網(wǎng)站維護(hù)、網(wǎng)站推廣。

一 ， 邏輯分層　　

連接層：連接與線程處理，這一層并不是MySQL獨(dú)有，一般的基于C/S架構(gòu)的都有類似組件，比如連接處理、授權(quán)認(rèn)證、安全等。

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

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

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

二 ， 存儲(chǔ)引擎

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

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

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

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 ;

查看建表語(yǔ)句:show create table default_table;

MySQL 存儲(chǔ)引擎 MyISAM 與 InnoDB 如何選擇？

雖然 MySQL 里的存儲(chǔ)引擎不只是 MyISAM 與 InnoDB 這兩個(gè)，但常用的就是它倆了?？赡苡姓鹃L(zhǎng)并未注意過 MySQL 的存儲(chǔ)引擎，其實(shí)存儲(chǔ)引擎也是數(shù)據(jù)庫(kù)設(shè)計(jì)里的一大重要點(diǎn)，那么博客系統(tǒng)應(yīng)該使用哪種存儲(chǔ)引擎呢？

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

• 一、InnoDB支持事務(wù)，MyISAM不支持，這一點(diǎn)是非常之重要。事務(wù)是一種高級(jí)的處理方式，如在一些列增刪改中只要哪個(gè)出錯(cuò)還可以回滾還原，而MyISAM就不可以了。

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

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

• 四、MyISAM是默認(rèn)引擎，InnoDB需要指定

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

• 六、InnoDB中不保存表的行數(shù)，如select count(*) from table時(shí)，InnoDB需要掃描一遍整個(gè)表來(lái)計(jì)算有多少行，但是MyISAM只要簡(jiǎn)單的讀出保存好的行數(shù)即可。注意的是，當(dāng)count(*)語(yǔ)句包含where條件時(shí)MyISAM也需要掃描整個(gè)表

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

• 八、清空整個(gè)表時(shí)，InnoDB是一行一行的刪除，效率非常慢。MyISAM則會(huì)重建表

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

通過以上九點(diǎn)區(qū)別，結(jié)合個(gè)人博客的特點(diǎn)，推薦個(gè)人博客系統(tǒng)使用MyISAM，因?yàn)樵诓┛屠镏饕僮魇亲x取和寫入，很少有鏈?zhǔn)讲僮?。所以選擇MyISAM引擎使你博客打開也頁(yè)面的效率要高于InnoDB引擎的博客，當(dāng)然只是個(gè)人的建議，大多數(shù)博客還是根據(jù)實(shí)際情況下謹(jǐn)慎選擇。

三， sql優(yōu)化

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

3.1.1.1 ，  二叉樹

　　　　　　

3.1.1.2 ，  B-Tree　　　　　　　　

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

每個(gè)節(jié)點(diǎn)占用一個(gè)盤塊的磁盤空間，一個(gè)節(jié)點(diǎn)上有兩個(gè)升序排序的關(guān)鍵字和三個(gè)指向子樹根節(jié)點(diǎn)的指針，指針存儲(chǔ)的是子節(jié)點(diǎn)所在磁盤塊的地址。兩個(gè)關(guān)鍵詞劃分成的三個(gè)范圍域?qū)?yīng)三個(gè)指針指向的子樹的數(shù)據(jù)的范圍域。以根節(jié)點(diǎn)為例，關(guān)鍵字為17和35，P1指針指向的子樹的數(shù)據(jù)范圍為小于17，P2指針指向的子樹的數(shù)據(jù)范圍為17~35，P3指針指向的子樹的數(shù)據(jù)范圍為大于35。

模擬查找關(guān)鍵字29的過程：

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

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

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

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

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

6. 在磁盤塊8中的關(guān)鍵字列表中找到關(guān)鍵字29。

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

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

3.1.1.3 ，  B+Tree(查詢?nèi)我鈹?shù)據(jù)的次數(shù)是 n)

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

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

B+Tree相對(duì)于B-Tree有幾點(diǎn)不同：

1. 非葉子節(jié)點(diǎn)只存儲(chǔ)鍵值信息。

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

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

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

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

上面都應(yīng)該知道B+Tree 了吧，所以我們?cè)诮⑺饕龝r(shí)，會(huì)生成一個(gè)B+Tree  如果我們?cè)谥徊樵兯饕侄螘r(shí)，sql 語(yǔ)句就直接去B+Tree 查，不會(huì)再去數(shù)據(jù)表中查了，這樣提升性能是很重要的。 還有就是對(duì)于總是修改的字段不要對(duì)他建立索引，因?yàn)樽侄涡薷牧?，B+Tree 結(jié)構(gòu)就要重構(gòu)，這要是會(huì)降低性能的。

3.1.1 索引分類：

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

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

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)建。對(duì)于大規(guī)模的數(shù)據(jù)集，通過ALTER TABLE（或者CREATE INDEX）命令創(chuàng)建全文索引要比把記錄插入帶有全文索引的空表更快。MyISAM支持全文索引，InnoDB在mysql5.6之后支持了全文索引。        全文索引不支持中文需要借sphinx(coreseek)或迅搜<、code>技術(shù)處理中文。

3.2.2索引的機(jī)制

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

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

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

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

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

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

3.2.2、SQL解析順序

接下來(lái)再走一步，讓我們看看一條SQL語(yǔ)句的前世今生。

首先看一下示例語(yǔ)句

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 如何建立索引　　　　　　　　

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

3.2.4     使用索引時(shí)，有一些技巧：

1.索引不會(huì)包含有NULL的列

只要列中包含有NULL值，都將不會(huì)被包含在索引中，復(fù)合索引中只要有一列含有NULL值，那么這一列對(duì)于此符合索引就是無(wú)效的。

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

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

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

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

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

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

  這是因?yàn)椋绻@些列很少用到，那么有無(wú)索引并不能明顯改變查詢速度。相反，由于增加了索引，反而降低了系統(tǒng)的維護(hù)速度和增大了空間需求。

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

    9.對(duì)于那些定義為text、image和bit數(shù)據(jù)類型的列不應(yīng)該增加索引。因?yàn)檫@些列的數(shù)據(jù)量要么相當(dāng)大，要么取值很少。

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

 三， sql性能問題

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

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

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

　　　　　　

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

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

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

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

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

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

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

網(wǎng)頁(yè)標(biāo)題：MySQL邏輯分層，存儲(chǔ)引擎，sql優(yōu)化，索引優(yōu)化以及底層實(shí)現(xiàn)方法
標(biāo)題網(wǎng)址：http://bm7419.com/article22/pscpcc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供品牌網(wǎng)站設(shè)計(jì)、企業(yè)建站、微信小程序、網(wǎng)站收錄、響應(yīng)式網(wǎng)站、靜態(tài)網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)