一、為什么用自增列作為主鍵

1、如果我們定義了主鍵(PRIMARY KEY)，那么InnoDB會(huì)選擇主鍵作為聚集索引。

     如果沒(méi)有顯式定義主鍵，則InnoDB會(huì)選擇第一個(gè)不包含有NULL值的唯一索引作為主鍵索引。

如果也沒(méi)有這樣的唯一索引，則InnoDB會(huì)選擇內(nèi)置6字節(jié)長(zhǎng)的ROWID作為隱含的聚集索引(ROWID隨著行記錄的寫(xiě)入而主鍵遞增，這個(gè)ROWID不像ORACLE的ROWID那樣可引用，是隱含的)。

2、數(shù)據(jù)記錄本身被存于主索引（一顆B+Tree）的葉子節(jié)點(diǎn)上，這就要求同一個(gè)葉子節(jié)點(diǎn)內(nèi)（大小為一個(gè)內(nèi)存頁(yè)或磁盤(pán)頁(yè)）的各條數(shù)據(jù)記錄按主鍵順序存放

       因此每當(dāng)有一條新的記錄插入時(shí)，MySQL會(huì)根據(jù)其主鍵將其插入適當(dāng)?shù)墓?jié)點(diǎn)和位置，如果頁(yè)面達(dá)到裝載因子（InnoDB默認(rèn)為15/16），則開(kāi)辟一個(gè)新的頁(yè)（節(jié)點(diǎn)）

3、如果表使用自增主鍵，那么每次插入新的記錄，記錄就會(huì)順序添加到當(dāng)前索引節(jié)點(diǎn)的后續(xù)位置，當(dāng)一頁(yè)寫(xiě)滿(mǎn)，就會(huì)自動(dòng)開(kāi)辟一個(gè)新的頁(yè)

4、如果使用非自增主鍵（如果身份證號(hào)或?qū)W號(hào)等），由于每次插入主鍵的值近似于隨機(jī)，因此每次新紀(jì)錄都要被插到現(xiàn)有索引頁(yè)得中間某個(gè)位置

      此時(shí)MySQL不得不為了將新記錄插到合適位置而移動(dòng)數(shù)據(jù)，甚至目標(biāo)頁(yè)面可能已經(jīng)被回寫(xiě)到磁盤(pán)上而從緩存中清掉，此時(shí)又要從磁盤(pán)上讀回來(lái)，這增加了很多開(kāi)銷(xiāo)

同時(shí)頻繁的移動(dòng)、分頁(yè)操作造成了大量的碎片，得到了不夠緊湊的索引結(jié)構(gòu)，后續(xù)不得不通過(guò)OPTIMIZE TABLE來(lái)重建表并優(yōu)化填充頁(yè)面。

二、為什么使用數(shù)據(jù)索引能提高效率

數(shù)據(jù)索引的存儲(chǔ)是有序的

在有序的情況下，通過(guò)索引查詢(xún)一個(gè)數(shù)據(jù)是無(wú)需遍歷索引記錄的

極端情況下，數(shù)據(jù)索引的查詢(xún)效率為二分法查詢(xún)效率，趨近于 log2(N)

三、B+樹(shù)索引和哈希索引的區(qū)別

B+樹(shù)是一個(gè)平衡的多叉樹(shù)，從根節(jié)點(diǎn)到每個(gè)葉子節(jié)點(diǎn)的高度差值不超過(guò)1，而且同層級(jí)的節(jié)點(diǎn)間有指針相互鏈接，是有序的，如下圖：

哈希索引就是采用一定的哈希算法，把鍵值換算成新的哈希值，檢索時(shí)不需要類(lèi)似B+樹(shù)那樣從根節(jié)點(diǎn)到葉子節(jié)點(diǎn)逐級(jí)查找，只需一次哈希算法即可,是無(wú)序的，如下圖所示：

四、哈希索引的優(yōu)勢(shì)：

         等值查詢(xún)，哈希索引具有絕對(duì)優(yōu)勢(shì)（前提是：沒(méi)有大量重復(fù)鍵值，如果大量重復(fù)鍵值時(shí)，哈希索引的效率很低，因?yàn)榇嬖谒^的哈希碰撞問(wèn)題。）

五、哈希索引不適用的場(chǎng)景：

不支持范圍查詢(xún)

不支持索引完成排序

不支持聯(lián)合索引的最左前綴匹配規(guī)則

通常，B+樹(shù)索引結(jié)構(gòu)適用于絕大多數(shù)場(chǎng)景，像下面這種場(chǎng)景用哈希索引才更有優(yōu)勢(shì)：

在HEAP表中，如果存儲(chǔ)的數(shù)據(jù)重復(fù)度很低（也就是說(shuō)基數(shù)很大），對(duì)該列數(shù)據(jù)以等值查詢(xún)?yōu)橹?，沒(méi)有范圍查詢(xún)、沒(méi)有排序的時(shí)候，特別適合采用哈希索引，例如這種SQL：

# 僅等值查詢(xún)
 
select id, name from table where name='李明';

而常用的 InnoDB 引擎中默認(rèn)使用的是B+樹(shù)索引，它會(huì)實(shí)時(shí)監(jiān)控表上索引的使用情況。

如果認(rèn)為建立哈希索引可以提高查詢(xún)效率，則自動(dòng)在內(nèi)存中的“自適應(yīng)哈希索引緩沖區(qū)”建立哈希索引（在InnoDB中默認(rèn)開(kāi)啟自適應(yīng)哈希索引）。

通過(guò)觀察搜索模式，MySQL會(huì)利用index key的前綴建立哈希索引，如果一個(gè)表幾乎大部分都在緩沖池中，那么建立一個(gè)哈希索引能夠加快等值查詢(xún)。

注意：在某些工作負(fù)載下，通過(guò)哈希索引查找?guī)?lái)的性能提升遠(yuǎn)大于額外的監(jiān)控索引搜索情況和保持這個(gè)哈希表結(jié)構(gòu)所帶來(lái)的開(kāi)銷(xiāo)。

但某些時(shí)候，在負(fù)載高的情況下，自適應(yīng)哈希索引中添加的read/write鎖也會(huì)帶來(lái)競(jìng)爭(zhēng)，比如高并發(fā)的join操作。like操作和%的通配符操作也不適用于自適應(yīng)哈希索引，可能要關(guān)閉自適應(yīng)哈希索引。

六、B樹(shù)和B+樹(shù)的區(qū)別

1、B樹(shù)，每個(gè)節(jié)點(diǎn)都存儲(chǔ)key和data，所有節(jié)點(diǎn)組成這棵樹(shù)，并且葉子節(jié)點(diǎn)指針為nul，葉子結(jié)點(diǎn)不包含任何關(guān)鍵字信息。

2、B+樹(shù)，所有的葉子結(jié)點(diǎn)中包含了全部關(guān)鍵字的信息，及指向含有這些關(guān)鍵字記錄的指針，且葉子結(jié)點(diǎn)本身依關(guān)鍵字的大小自小而大的順序鏈接

所有的非終端結(jié)點(diǎn)可以看成是索引部分，結(jié)點(diǎn)中僅含有其子樹(shù)根結(jié)點(diǎn)中最大（或最?。╆P(guān)鍵字。(而B(niǎo) 樹(shù)的非終節(jié)點(diǎn)也包含需要查找的有效信息)

七、為什么說(shuō)B+比B樹(shù)更適合實(shí)際應(yīng)用中操作系統(tǒng)的文件索引和數(shù)據(jù)庫(kù)索引？

1、B+的磁盤(pán)讀寫(xiě)代價(jià)更低。

      B+的內(nèi)部結(jié)點(diǎn)并沒(méi)有指向關(guān)鍵字具體信息的指針，因此其內(nèi)部結(jié)點(diǎn)相對(duì)B樹(shù)更小。

如果把所有同一內(nèi)部結(jié)點(diǎn)的關(guān)鍵字存放在同一盤(pán)塊中，那么盤(pán)塊所能容納的關(guān)鍵字?jǐn)?shù)量也越多。一次性讀入內(nèi)存中的需要查找的關(guān)鍵字也就越多。相對(duì)來(lái)說(shuō)IO讀寫(xiě)次數(shù)也就降低了。

2、B+-tree的查詢(xún)效率更加穩(wěn)定。

       由于非終結(jié)點(diǎn)并不是最終指向文件內(nèi)容的結(jié)點(diǎn)，而只是葉子結(jié)點(diǎn)中關(guān)鍵字的索引。所以任何關(guān)鍵字的查找必須走一條從根結(jié)點(diǎn)到葉子結(jié)點(diǎn)的路。所有關(guān)鍵字查詢(xún)的路徑長(zhǎng)度相同，導(dǎo)致每一個(gè)數(shù)據(jù)的查詢(xún)效率相當(dāng)。

八、MySQL聯(lián)合索引

1、聯(lián)合索引是兩個(gè)或更多個(gè)列上的索引。

      對(duì)于聯(lián)合索引:Mysql從左到右的使用索引中的字段，一個(gè)查詢(xún)可以只使用索引中的一部份，但只能是最左側(cè)部分。

例如索引是key index (a,b,c). 可以支持a 、 a,b 、 a,b,c 3種組合進(jìn)行查找，但不支持 b,c進(jìn)行查找 .當(dāng)最左側(cè)字段是常量引用時(shí)，索引就十分有效。

2、利用索引中的附加列，您可以縮小搜索的范圍，但使用一個(gè)具有兩列的索引不同于使用兩個(gè)單獨(dú)的索引。

    復(fù)合索引的結(jié)構(gòu)與電話簿類(lèi)似，人名由姓和名構(gòu)成，電話簿首先按姓氏對(duì)進(jìn)行排序，然后按名字對(duì)有相同姓氏的人進(jìn)行排序。

如果您知道姓，電話簿將非常有用；如果您知道姓和名，電話簿則更為有用，但如果您只知道名不知道姓，電話簿將沒(méi)有用處。

九、什么情況下應(yīng)不建或少建索引

1、表記錄太少

2、經(jīng)常插入、刪除、修改的表

3、數(shù)據(jù)重復(fù)且分布平均的表字段，假如一個(gè)表有10萬(wàn)行記錄，有一個(gè)字段A只有T和F兩種值，且每個(gè)值的分布概率大約為50%，那么對(duì)這種表A字段建索引一般不會(huì)提高數(shù)據(jù)庫(kù)的查詢(xún)速度。

4、經(jīng)常和主字段一塊查詢(xún)但主字段索引值比較多的表字段

十、什么是表分區(qū)？

      表分區(qū)，是指根據(jù)一定規(guī)則，將數(shù)據(jù)庫(kù)中的一張表分解成多個(gè)更小的，容易管理的部分。從邏輯上看，只有一張表，但是底層卻是由多個(gè)物理分區(qū)組成。

十一、表分區(qū)與分表的區(qū)別

分表：指的是通過(guò)一定規(guī)則，將一張表分解成多張不同的表。比如將用戶(hù)訂單記錄根據(jù)時(shí)間成多個(gè)表。

分表與分區(qū)的區(qū)別在于：分區(qū)從邏輯上來(lái)講只有一張表，而分表則是將一張表分解成多張表。

十二、表分區(qū)有什么好處？

1、存儲(chǔ)更多數(shù)據(jù)。分區(qū)表的數(shù)據(jù)可以分布在不同的物理設(shè)備上，從而高效地利用多個(gè)硬件設(shè)備。和單個(gè)磁盤(pán)或者文件系統(tǒng)相比，可以存儲(chǔ)更多數(shù)據(jù)

2、優(yōu)化查詢(xún)。在where語(yǔ)句中包含分區(qū)條件時(shí)，可以只掃描一個(gè)或多個(gè)分區(qū)表來(lái)提高查詢(xún)效率；涉及sum和count語(yǔ)句時(shí)，也可以在多個(gè)分區(qū)上并行處理，最后匯總結(jié)果。

3、分區(qū)表更容易維護(hù)。例如：想批量刪除大量數(shù)據(jù)可以清除整個(gè)分區(qū)。

4、避免某些特殊的瓶頸，例如InnoDB的單個(gè)索引的互斥訪問(wèn)，ext3問(wèn)價(jià)你系統(tǒng)的inode鎖競(jìng)爭(zhēng)等。

十三、分區(qū)表的限制因素

1、一個(gè)表最多只能有1024個(gè)分區(qū)

2、MySQL5.1中，分區(qū)表達(dá)式必須是整數(shù)，或者返回整數(shù)的表達(dá)式。在MySQL5.5中提供了非整數(shù)表達(dá)式分區(qū)的支持。

3、如果分區(qū)字段中有主鍵或者唯一索引的列，那么多有主鍵列和唯一索引列都必須包含進(jìn)來(lái)。即：分區(qū)字段要么不包含主鍵或者索引列，要么包含全部主鍵和索引列。

4、分區(qū)表中無(wú)法使用外鍵約束

5、MySQL的分區(qū)適用于一個(gè)表的所有數(shù)據(jù)和索引，不能只對(duì)表數(shù)據(jù)分區(qū)而不對(duì)索引分區(qū)，也不能只對(duì)索引分區(qū)而不對(duì)表分區(qū)，也不能只對(duì)表的一部分?jǐn)?shù)據(jù)分區(qū)。

十四、如何判斷當(dāng)前MySQL是否支持分區(qū)？

命令：show variables like '%partition%' 運(yùn)行結(jié)果:

mysql> show variables like '%partition%';
+-------------------+-------+| Variable_name |
 Value |+-------------------+-------+|
 have_partitioning | YES |+-------------------+-------+1 row in set (0.00 sec)

have_partintioning 的值為YES，表示支持分區(qū)。

十五、MySQL支持的分區(qū)類(lèi)型有哪些？

RANGE分區(qū)：這種模式允許將數(shù)據(jù)劃分不同范圍。例如可以將一個(gè)表通過(guò)年份劃分成若干個(gè)分區(qū)

LIST分區(qū)：這種模式允許系統(tǒng)通過(guò)預(yù)定義的列表的值來(lái)對(duì)數(shù)據(jù)進(jìn)行分割。按照List中的值分區(qū)，與RANGE的區(qū)別是，range分區(qū)的區(qū)間范圍值是連續(xù)的。

HASH分區(qū) ：這中模式允許通過(guò)對(duì)表的一個(gè)或多個(gè)列的Hash Key進(jìn)行計(jì)算，最后通過(guò)這個(gè)Hash碼不同數(shù)值對(duì)應(yīng)的數(shù)據(jù)區(qū)域進(jìn)行分區(qū)。例如可以建立一個(gè)對(duì)表主鍵進(jìn)行分區(qū)的表。

KEY分區(qū) ：上面Hash模式的一種延伸，這里的Hash Key是MySQL系統(tǒng)產(chǎn)生的。

十六、四種隔離級(jí)別

Serializable (串行化)：可避免臟讀、不可重復(fù)讀、幻讀的發(fā)生。

Repeatable read (可重復(fù)讀)：可避免臟讀、不可重復(fù)讀的發(fā)生。

Read committed (讀已提交)：可避免臟讀的發(fā)生。

Read uncommitted (讀未提交)：最低級(jí)別，任何情況都無(wú)法保證。

十七、關(guān)于MVVC

MySQL InnoDB存儲(chǔ)引擎，實(shí)現(xiàn)的是基于多版本的并發(fā)控制協(xié)議——MVCC (Multi-Version Concurrency Control)

注：與MVCC相對(duì)的，是基于鎖的并發(fā)控制，Lock-Based Concurrency Control

MVCC最大的好處：讀不加鎖，讀寫(xiě)不沖突。在讀多寫(xiě)少的OLTP應(yīng)用中，讀寫(xiě)不沖突是非常重要的，極大的增加了系統(tǒng)的并發(fā)性能，現(xiàn)階段幾乎所有的RDBMS，都支持了MVCC。

LBCC：Lock-Based Concurrency Control，基于鎖的并發(fā)控制

MVCC：Multi-Version Concurrency Control

基于多版本的并發(fā)控制協(xié)議。純粹基于鎖的并發(fā)機(jī)制并發(fā)量低，MVCC是在基于鎖的并發(fā)控制上的改進(jìn)，主要是在讀操作上提高了并發(fā)量。

十八、在MVCC并發(fā)控制中，讀操作可以分成兩類(lèi)：

快照讀 (snapshot read)：讀取的是記錄的可見(jiàn)版本 (有可能是歷史版本)，不用加鎖（共享讀鎖s鎖也不加，所以不會(huì)阻塞其他事務(wù)的寫(xiě)）

當(dāng)前讀 (current read)：讀取的是記錄的最新版本，并且，當(dāng)前讀返回的記錄，都會(huì)加上鎖，保證其他事務(wù)不會(huì)再并發(fā)修改這條記錄

十九、行級(jí)鎖定的優(yōu)點(diǎn)：

1、當(dāng)在許多線程中訪問(wèn)不同的行時(shí)只存在少量鎖定沖突。

2、回滾時(shí)只有少量的更改

3、可以長(zhǎng)時(shí)間鎖定單一的行。

二十、行級(jí)鎖定的缺點(diǎn)：

比頁(yè)級(jí)或表級(jí)鎖定占用更多的內(nèi)存。

當(dāng)在表的大部分中使用時(shí)，比頁(yè)級(jí)或表級(jí)鎖定速度慢，因?yàn)槟惚仨毇@取更多的鎖。

如果你在大部分?jǐn)?shù)據(jù)上經(jīng)常進(jìn)行GROUP BY操作或者必須經(jīng)常掃描整個(gè)表，比其它鎖定明顯慢很多。

用高級(jí)別鎖定，通過(guò)支持不同的類(lèi)型鎖定，你也可以很容易地調(diào)節(jié)應(yīng)用程序，因?yàn)槠滏i成本小于行級(jí)鎖定。

二十一、MySQL優(yōu)化

開(kāi)啟查詢(xún)緩存，優(yōu)化查詢(xún)

explain你的select查詢(xún)，這可以幫你分析你的查詢(xún)語(yǔ)句或是表結(jié)構(gòu)的性能瓶頸。EXPLAIN 的查詢(xún)結(jié)果還會(huì)告訴你你的索引主鍵被如何利用的，你的數(shù)據(jù)表是如何被搜索和排序的

當(dāng)只要一行數(shù)據(jù)時(shí)使用limit 1，MySQL數(shù)據(jù)庫(kù)引擎會(huì)在找到一條數(shù)據(jù)后停止搜索，而不是繼續(xù)往后查少下一條符合記錄的數(shù)據(jù)

為搜索字段建索引

使用 ENUM 而不是 VARCHAR。如果你有一個(gè)字段，比如“性別”，“國(guó)家”，“民族”，“狀態(tài)”或“部門(mén)”，你知道這些字段的取值是有限而且固定的，那么，你應(yīng)該使用 ENUM 而不是VARCHAR

Prepared StatementsPrepared Statements很像存儲(chǔ)過(guò)程，是一種運(yùn)行在后臺(tái)的SQL語(yǔ)句集合，我們可以從使用 prepared statements 獲得很多好處，無(wú)論是性能問(wèn)題還是安全問(wèn)題。
Prepared Statements 可以檢查一些你綁定好的變量，這樣可以保護(hù)你的程序不會(huì)受到“SQL注入式”攻擊

垂直分表

選擇正確的存儲(chǔ)引擎

二十二、key和index的區(qū)別

key 是數(shù)據(jù)庫(kù)的物理結(jié)構(gòu)，它包含兩層意義和作用，一是約束（偏重于約束和規(guī)范數(shù)據(jù)庫(kù)的結(jié)構(gòu)完整性），二是索引（輔助查詢(xún)用的）。包括primary key, unique key, foreign key 等

index是數(shù)據(jù)庫(kù)的物理結(jié)構(gòu)，它只是輔助查詢(xún)的，它創(chuàng)建時(shí)會(huì)在另外的表空間（mysql中的innodb表空間）以一個(gè)類(lèi)似目錄的結(jié)構(gòu)存儲(chǔ)。索引要分類(lèi)的話，分為前綴索引、全文本索引等；

二十三、Mysql 中 MyISAM 和 InnoDB 的區(qū)別有哪些？

區(qū)別：

1、InnoDB支持事務(wù)，MyISAM不支持

對(duì)于InnoDB每一條SQL語(yǔ)言都默認(rèn)封裝成事務(wù)，自動(dòng)提交，這樣會(huì)影響速度，所以最好把多條SQL語(yǔ)言放在begin和commit之間，組成一個(gè)事務(wù)；

2、InnoDB支持外鍵，而MyISAM不支持。

對(duì)一個(gè)包含外鍵的InnoDB表轉(zhuǎn)為MYISAM會(huì)失??；

3、InnoDB是聚集索引，數(shù)據(jù)文件是和索引綁在一起的，必須要有主鍵，通過(guò)主鍵索引效率很高。

但是輔助索引需要兩次查詢(xún)，先查詢(xún)到主鍵，然后再通過(guò)主鍵查詢(xún)到數(shù)據(jù)。因此主鍵不應(yīng)該過(guò)大，因?yàn)橹麈I太大，其他索引也都會(huì)很大。

而MyISAM是非聚集索引，數(shù)據(jù)文件是分離的，索引保存的是數(shù)據(jù)文件的指針。主鍵索引和輔助索引是獨(dú)立的。

4、InnoDB不保存表的具體行數(shù)，執(zhí)行select count(*) from table時(shí)需要全表掃描。而MyISAM用一個(gè)變量保存了整個(gè)表的行數(shù)，執(zhí)行上述語(yǔ)句時(shí)只需要讀出該變量即可，速度很快；

5、Innodb不支持全文索引，而MyISAM支持全文索引，查詢(xún)效率上MyISAM要高；

如何選擇：

是否要支持事務(wù)，如果要請(qǐng)選擇innodb，如果不需要可以考慮MyISAM；

如果表中絕大多數(shù)都只是讀查詢(xún)，可以考慮MyISAM，如果既有讀寫(xiě)也挺頻繁，請(qǐng)使用InnoDB

系統(tǒng)奔潰后，MyISAM恢復(fù)起來(lái)更困難，能否接受；

MySQL5.5版本開(kāi)始Innodb已經(jīng)成為Mysql的默認(rèn)引擎(之前是MyISAM)，說(shuō)明其優(yōu)勢(shì)是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不會(huì)差。

二十四、數(shù)據(jù)庫(kù)表創(chuàng)建注意事項(xiàng)

1、字段名及字段配制合理性

剔除關(guān)系不密切的字段；

字段命名要有規(guī)則及相對(duì)應(yīng)的含義（不要一部分英文，一部分拼音，還有類(lèi)似a.b.c這樣不明含義的字段）；

字段命名盡量不要使用縮寫(xiě)（大多數(shù)縮寫(xiě)都不能明確字段含義）；

字段不要大小寫(xiě)混用（想要具有可讀性，多個(gè)英文單詞可使用下劃線形式連接）；

字段名不要使用保留字或者關(guān)鍵字；

保持字段名和類(lèi)型的一致性；

慎重選擇數(shù)字類(lèi)型；
-給文本字段留足余量；

2、系統(tǒng)特殊字段處理及建成后建議

添加刪除標(biāo)記（例如操作人、刪除時(shí)間）；

建立版本機(jī)制；

3、表結(jié)構(gòu)合理性配置

多型字段的處理，就是表中是否存在字段能夠分解成更小獨(dú)立的幾部分（例如：人可以分為男人和女人）；

多值字段的處理，可以將表分為三張表，這樣使得檢索和排序更加有調(diào)理，且保證數(shù)據(jù)的完整性！

4、其它建議

對(duì)于大數(shù)據(jù)字段，獨(dú)立表進(jìn)行存儲(chǔ)，以便影響性能（例如：簡(jiǎn)介字段）；

使用varchar類(lèi)型代替char，因?yàn)関archar會(huì)動(dòng)態(tài)分配長(zhǎng)度，char指定長(zhǎng)度是固定的；

給表創(chuàng)建主鍵，對(duì)于沒(méi)有主鍵的表，在查詢(xún)和索引定義上有一定的影響；

避免表字段運(yùn)行為null，建議設(shè)置默認(rèn)值（例如：int類(lèi)型設(shè)置默認(rèn)值為0）在索引查詢(xún)上，效率立顯；

建立索引，最好建立在唯一和非空的字段上，建立太多的索引對(duì)后期插入、更新都存在一定的影響（考慮實(shí)際情況來(lái)創(chuàng)建）；

以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。

最新評(píng)論