本文是面試基礎(chǔ)的第二篇。本篇偏理論，包括三節(jié)：

• 事務(wù)和并發(fā)

• 數(shù)據(jù)庫設(shè)計

• 索引

所選的三個內(nèi)容均是面試的高頻考察點，需要細(xì)致地理解

No.1?????事務(wù)和并發(fā)

---

事務(wù)：數(shù)據(jù)庫操作的基本單元。對于數(shù)據(jù)庫的一系列操作，要么全部成功，要么全部失敗

1. 事務(wù)的ACID屬性

原子性(Atomic)

一個事務(wù)包含多個操作，這些操作要么全部執(zhí)行，要么全都不執(zhí)行。實現(xiàn)事務(wù)的原子性，要支持回滾操作，在某個操作失敗后，回滾到事務(wù)執(zhí)行之前的狀態(tài)。

回滾實際上是一個比較高層抽象的概念。大多數(shù)DB在實現(xiàn)事務(wù)時，是在事務(wù)操作的數(shù)據(jù)快照上進(jìn)行的，執(zhí)行事務(wù)時并不修改實際的數(shù)據(jù)，只有事務(wù)完成后才會提交到數(shù)據(jù)庫，完成對數(shù)據(jù)庫的修改。如有錯，則不會提交，所以很自然的支持回滾。

而在其他支持簡單事務(wù)的系統(tǒng)中，不會在快照上更新，而直接操作實際數(shù)據(jù)。可以先預(yù)演一邊所有要執(zhí)行的操作，如果失敗則這些操作不會被執(zhí)行，通過這種方式很簡單的實現(xiàn)了原子性。

一致性(Consistency)

一致性是指事務(wù)使得系統(tǒng)從一個一致的狀態(tài)轉(zhuǎn)換到另一個一致狀態(tài)。事務(wù)的一致性決定了一個系統(tǒng)設(shè)計和實現(xiàn)的復(fù)雜度。事務(wù)可以不同程度的一致性：強一致性：讀操作可以立即讀到提交的更新操作弱一致性：提交的更新操作，不一定立即會被讀操作讀到，此種情況會存在一個不一致窗口，指的是讀操作可以讀到最新值的一段時間最終一致性：是弱一致性的特例。事務(wù)更新一份數(shù)據(jù)，最終一致性保證在沒有其他事務(wù)更新同樣的值的話，最終所有的事務(wù)都會讀到之前事務(wù)更新的最新值。如果沒有錯誤發(fā)生，不一致窗口的大小依賴于：通信延遲，系統(tǒng)負(fù)載等。其他一致性變體還有：單調(diào)一致性：如果一個進(jìn)程已經(jīng)讀到一個值，那么后續(xù)不會讀到更早的值。會話一致性：保證客戶端和服務(wù)器交互的會話過程中，讀操作可以讀到更新操作后的最新值。

隔離性(Isolation)

并發(fā)事務(wù)之間互相影響的程度，比如一個事務(wù)會不會讀取到另一個未提交的事務(wù)修改的數(shù)據(jù)。在事務(wù)并發(fā)操作時，可能出現(xiàn)的問題有：臟讀：事務(wù)A修改了一個數(shù)據(jù)，但未提交，事務(wù)B讀到了事務(wù)A未提交的更新結(jié)果，如果事務(wù)A提交失敗，事務(wù)B讀到的就是臟數(shù)據(jù)。不可重復(fù)讀：在同一個事務(wù)中，對于同一份數(shù)據(jù)讀取到的結(jié)果不一致。比如，事務(wù)B在事務(wù)A提交前讀到的結(jié)果，和提交后讀到的結(jié)果可能不同。不可重復(fù)讀出現(xiàn)的原因就是事務(wù)并發(fā)修改記錄，要避免這種情況，最簡單的方法就是對要修改的記錄加鎖，這會導(dǎo)致鎖競爭加劇，影響性能。另一種方法是通過MVCC可以在無鎖的情況下，避免不可重復(fù)讀。幻讀：在同一個事務(wù)中，同一個查詢多次返回的結(jié)果不一致。事務(wù)A新增了一條記錄，事務(wù)B在事務(wù)A提交前后各執(zhí)行了一次查詢操作，發(fā)現(xiàn)后一次比前一次多了一條記錄。幻讀是由于并發(fā)事務(wù)增加記錄導(dǎo)致的，這個不能像不可重復(fù)讀通過記錄加鎖解決，因為對于新增的記錄根本無法加鎖。需要將事務(wù)串行化，才能避免幻讀。為解決上述問題，需要在并發(fā)和隔離進(jìn)行平衡。事務(wù)的隔離級別從低到高有：Read Uncommitted：未提交讀，最低的隔離級別，一個事務(wù)可以讀到另一個事務(wù)未提交的結(jié)果。所有的并發(fā)事務(wù)問題都會發(fā)生。Read Committed：提交讀。只有在事務(wù)提交后，其更新結(jié)果才會被其他事務(wù)看見。可以解決臟讀問題。Repeated Read：可重復(fù)讀。在一個事務(wù)中，對于同一份數(shù)據(jù)的讀取結(jié)果總是相同的，無論是否有其他事務(wù)對這份數(shù)據(jù)進(jìn)行操作，以及這個事務(wù)是否提交。可以解決臟讀、不可重復(fù)讀。Serialization：串行化。事務(wù)串行化執(zhí)行，隔離級別最高，犧牲了系統(tǒng)的并發(fā)性。可以解決并發(fā)事務(wù)的所有問題。通常，在工程實踐中，為了性能的考慮會對隔離性進(jìn)行折中。

持久性(Durability)

事務(wù)提交后，對數(shù)據(jù)庫的影響是永久的。

2. 四種隔離級別

在MySQL中設(shè)置隔離級別：

查看當(dāng)前隔離級別：

show variables like 'transaction_isolation'

結(jié)果為可重復(fù)讀

設(shè)置下次事務(wù)的隔離級別：

set transaction isolation level serializable

設(shè)置將來所有事務(wù)的隔離級別：

set?session?transaction?isolation?level?serializable

設(shè)置全局事務(wù)的隔離級別：

set global transaction isolation level serializable

下面利用具體例子來解釋四種隔離級別的含義，以及可能會出現(xiàn)的問題。假設(shè)有兩個事務(wù)A和B

1. 未提交讀：read uncommitted

原本A事務(wù)想讀50的余額，在讀的過程中因為B的修改，造成讀的結(jié)果為100，但由于B事務(wù)最后回滾了，數(shù)據(jù)庫中最終張三的余額還是50，即查詢結(jié)果并不為數(shù)據(jù)庫的最終真實結(jié)果。該現(xiàn)象稱為臟讀(dirty read)

2. 提交讀：commit read

為避免臟讀，最直接的做法是屏蔽掉沒有提交的修改。下圖中事務(wù)B真正將修改持久到數(shù)據(jù)庫中后，才會對事務(wù)A產(chǎn)生影響。

但這又回造成新的問題。假設(shè)A事務(wù)中有兩次查詢，第一次查詢得到結(jié)果50，第二次查詢之前，B事務(wù)修改余額為100并提交，那么A事務(wù)中的第二次查詢得到的結(jié)果為100。這樣在同一個事務(wù)中，兩次查詢結(jié)果不同，該現(xiàn)象稱為不可重復(fù)讀。

3. 可重復(fù)讀：repeatable read

為解決上述問題，可以給數(shù)據(jù)加上時間戳，規(guī)定每次讀的都是開啟事務(wù)時刻的數(shù)據(jù)庫數(shù)據(jù)。這樣即使在事務(wù)處理期間，數(shù)據(jù)庫發(fā)生了變化，也不會對本事務(wù)產(chǎn)生影響。該處理方式稱為快照，相當(dāng)于開始事務(wù)時，對數(shù)據(jù)庫進(jìn)行一個記錄，之后事務(wù)的進(jìn)行都以之為準(zhǔn)。

但是這又帶來新的問題。可重復(fù)讀意味著，A事務(wù)處理期間，數(shù)據(jù)庫的任何改變都不會被感知，這種做法雖然保證了可重復(fù)讀的一致性，但是也丟失了B事務(wù)的有用修改，會引起幻讀現(xiàn)象。

4. 串行化：serialization

一張表一次只能被一個事務(wù)訪問，完全沒有并發(fā)。每個事務(wù)占用一張表時，會將該表上鎖。表處于鎖定期間，其他事務(wù)只能等待。只有當(dāng)占有該表的事務(wù)結(jié)束，該表才會被釋放。

因此，當(dāng)兩個事務(wù)互相等待時，便會造成死鎖現(xiàn)象。

一般發(fā)生死鎖的四個必要條件為：

• 互斥條件：一個資源每次只能被一個進(jìn)程使用

• 占有且等待：一個進(jìn)程因請求資源而阻塞時，對已獲得的資源保持不放

• 不可剝奪：進(jìn)程已獲得的資源，在末使用完之前，不能強行剝奪

• 循環(huán)等待條件：若干進(jìn)程之間形成一種頭尾相接的循環(huán)等待資源關(guān)系

解決死鎖的途徑：

打破上述四個條件中的一個或多個。例如設(shè)置事務(wù)執(zhí)行時間，一旦timeout，則該事務(wù)自動回滾并釋放資源。再比如，在寫并發(fā)執(zhí)行的SQL語句時，盡量按照相同的處理次序，這樣至多只會出現(xiàn)爭搶，避免形成循環(huán)等待。

No.2????設(shè)計數(shù)據(jù)庫

---

1.?基本過程

一個設(shè)計合理的數(shù)據(jù)庫，將大大提高讀寫效率和擴(kuò)展性。設(shè)計數(shù)據(jù)庫，由表及里，包含四個步驟：

1. 理解需求：對象是什么，滿足什么功能2. 構(gòu)建概念模型：確定實體有哪些，實體之間什么關(guān)系3. 構(gòu)建邏輯模型：對概念模型進(jìn)行具體化，確定有哪幾張表，每個表之間有哪些字段，每個表之間是什么關(guān)系4. 構(gòu)建物理模型：具體的數(shù)據(jù)庫表格，如果每個字段的類型，哪些是主鍵，外鍵，唯一鍵等等

舉例：設(shè)計一個數(shù)據(jù)庫，記錄學(xué)生選課的信息

概念模型

數(shù)據(jù)庫模型本質(zhì)就是具有不同屬性實體及其相互關(guān)系。對于上述問題，可以分析出：

學(xué)生實體：姓名，郵箱，注冊日

期課程實體：標(biāo)題，價格，教師，標(biāo)簽

學(xué)生實體和課程實體之間關(guān)系：多對多。因為一個學(xué)生可以選多門課，一門課會包含多個學(xué)生。

圖中二者之間的連接線端點形狀代表不同的關(guān)系類型。實體之間的關(guān)系類型：三種

邏輯模型

假設(shè)現(xiàn)在要記錄某個學(xué)生參加某門課程的日期。這個時間如果放在student表，由于每個學(xué)生可能選擇多門課，即有多個時間，僅根據(jù)學(xué)生信息無法確定具體對應(yīng)哪個時間(student表中不記錄課程信息)。若將該時間字段放在course表，也會面臨同樣的問題。問題的本質(zhì)是，某個學(xué)生選某門課的時間，既不是student實體的屬性，也不是course表的屬性，而是二者關(guān)系(即enroll)的屬性。解決方案是，利用第三張表，將多對多的關(guān)系，轉(zhuǎn)換為兩個一對多的關(guān)系。不妨建一個enrollment表，記錄各個學(xué)生，各門課的選擇信息。同時，每張表中補充了具體字段的類型和名稱細(xì)分。有了邏輯模型，便可以明確總共有三張表，每張表有哪些字段，以及各個字段的類型。同時各個表的關(guān)系也得到體現(xiàn)。

物理模型：

即邏輯模型在MySQL的具體實現(xiàn)。對于不同的SQL軟件，其邏輯模型相同，物理模型有差異。

在創(chuàng)建一張表時，我們依次指定各個字段名，字段類型(varchar，datetime等)，能否為空，默認(rèn)值等。在完成各個表的構(gòu)建后，我們接著實現(xiàn)對各個表之間關(guān)系的指定。

原則：一張表只描述一個實體，該表中的所有字段，都只描述該實體。當(dāng)涉及其他實體時，需要用外鍵進(jìn)行連接。這樣做使得各個表的邊界清晰，當(dāng)某個屬性發(fā)生修改時，不用修改多個表。本質(zhì)是盡量降低數(shù)據(jù)的重復(fù)率

主鍵：每行記錄在表中的唯一標(biāo)識

• 一個或多個屬性的組合，一張表只有一個主鍵，其值不可為空

外鍵：建立表的父子關(guān)系

如students表、courses表可認(rèn)為由enrollment表派生而來。前者為子表，后者為其父表

• 外鍵約束：當(dāng)外鍵所依賴的父表主鍵對應(yīng)的值發(fā)生變化時，子表是否自動同步變化。例如student表中新加入一個學(xué)生，若設(shè)置外鍵為casecade，則erollment表會同步加入該學(xué)生信息。同理，當(dāng)刪除某個學(xué)生時，erollment表也會同步刪除相應(yīng)學(xué)生信息

唯一鍵：該屬性的值不重復(fù)

有時候，我們希望某個屬性的值各不相同，如訂單號，可以設(shè)置為唯一鍵

• 一個或多個屬性的組合，一張表中可以有多個唯一鍵，其值允許為空

• 主鍵一定是唯一鍵，唯一鍵并不一定是主鍵

2. 范式

第一范式：屬性的原子性

屬性具有原子性，不可再分解。如：

表：字段1、 字段2(字段2.1、字段2.2)、字段3 ......

如學(xué)生(學(xué)號，姓名，性別，出生年月日)，如果認(rèn)為最后一列還可以再分成(出生年，出生月，出生日)，它就不滿足第一范式

例如：原本course表中的標(biāo)簽(tags)字段，每門課可能有多個tag(tag1，tag2，...)，因此在該表中，tags 不能只有一列(否則可能出現(xiàn)A課程在tags字段下，同時有多個值)。因此，就有tag1，tag2等一系列的tags值的列，這顯然不合理，因為我們可能不知道總共有幾個tag，同時tags的新增和刪除也會涉及全表掃描。

稍微思考可以發(fā)現(xiàn)，course與tag是多對多的關(guān)系，每門課有多個tags，每個tags對應(yīng)多門課。因此，可以將tags從courses表中分離出來，作為一個單獨的只與tag有關(guān)的表。而courses表與tags表則通過鏈接表course_tags表連接。如下圖所示：

第二范式：記錄的唯一性

表中的每行由主鍵唯一區(qū)分，非主屬性完全依賴主鍵，不存在部分依賴

表：學(xué)號、課程號、姓名、學(xué)分

這個表明顯說明了兩個事務(wù):學(xué)生信息, 課程信息。由于非主鍵字段必須依賴主鍵，這里學(xué)分依賴課程號，姓名依賴與學(xué)號，所以不符合二范式

可能會存在問題：

• 數(shù)據(jù)冗余: ? 每條記錄都含有相同信息；

• 刪除異常：刪除所有學(xué)生成績，就把課程信息全刪除了；

• 插入異常：學(xué)生未選課，無法記錄進(jìn)數(shù)據(jù)庫；

• 更新異常：調(diào)整課程學(xué)分，所有行都調(diào)整。

正確做法:

學(xué)生表：學(xué)號, 姓名課程表：課程號, 學(xué)分選課關(guān)系表：學(xué)號, 課程號, 成績

第三范式：字段的冗余性

任何字段不能由其他字段派生出來，它要求字段沒有冗余，即不存在傳遞依賴

表: 學(xué)號, 姓名, 年齡, 學(xué)院名稱, 學(xué)院電話

上表存在依賴傳遞: (學(xué)號) → (學(xué)生)→(所在學(xué)院) → (學(xué)院電話)?

可能會存在問題：

• 數(shù)據(jù)冗余: 有重復(fù)值，如某學(xué)院電話有多個，則多行記錄前三個屬性值都相同，只有學(xué)院電話不同；

• 更新異常: 有重復(fù)的冗余信息，修改時需要同時修改多條記錄，否則會出現(xiàn)數(shù)據(jù)不一致的情況 。

正確做法：

學(xué)生表：學(xué)號, 姓名, 年齡, 所在學(xué)院

學(xué)院表：學(xué)院, 電話

反范式化

沒有冗余的數(shù)據(jù)庫設(shè)計可以做到。但是，沒有冗余的數(shù)據(jù)庫未必是最好的數(shù)據(jù)庫，有時為了提高運行效率，就必須降低范式標(biāo)準(zhǔn)，適當(dāng)保留冗余數(shù)據(jù)。具體做法是：在概念數(shù)據(jù)模型設(shè)計時遵守第三范式，降低范式標(biāo)準(zhǔn)的工作放到物理數(shù)據(jù)模型設(shè)計時考慮。降低范式就是增加字段，允許冗余，達(dá)到以空間換時間的目的。

例如：有一張存放商品的基本表，“金額”這個字段的存在，表明該表的設(shè)計不滿足第三范式，因為“金額”可以由“單價”乘以“數(shù)量”得到，說明“金額”是冗余字段。但是，增加“金額”這個冗余字段，可以提高查詢統(tǒng)計的速度，這就是以空間換時間的作法。

范式與反范式對比

范式化：適合寫

優(yōu)點：降低數(shù)據(jù)冗余，數(shù)據(jù)表體積小，更新快

缺點：查詢需要將多個表進(jìn)行連接，更難進(jìn)行索引優(yōu)化

反范式化：適合讀

優(yōu)點：減少表關(guān)聯(lián)，更易索引優(yōu)化

缺點：存在冗余數(shù)據(jù)和數(shù)據(jù)維護(hù)異常(數(shù)據(jù)不一致)，修改成本高

3. 基本操作

1. 數(shù)據(jù)庫的創(chuàng)建與刪除

create?database?if?not?exists?sql_store?--創(chuàng)建一個名為sql_store的數(shù)據(jù)庫drop database if exists sql_store --刪除數(shù)據(jù)庫sql_store

2. 表的創(chuàng)建刪除與修改

聲明方式與數(shù)據(jù)庫的創(chuàng)建和刪除類似，但表需要指定其中的字段。指定方式為：名稱+類型+屬性：

類型：如int，varchar等，varchar可在括號中分配指定容量屬性：包括主鍵、外鍵、唯一鍵、非空、自增、默認(rèn)值等等

use sql_store2; -- 建表之前一般都會檢查刪除原來的表drop table if exists customers; create table if not exists customers( customer_id int primary key auto_increment, first_name varchar(50) not null, points int not null default(0), email varchar(255) not null unique);

表的修改：

alter table custormers -- 在first_name后增加一個last_name字段 add last_name varchar(50) not null after firat_name, -- 修改first_name字段的類型和默認(rèn)值 modify first_name varchar(55) default(''), -- 刪除points字段 drop points;

3. 關(guān)系的創(chuàng)建與修改

下面創(chuàng)建一個訂單表：orders。orders表通過外鍵custormer_id與之前創(chuàng)建的custormers表相關(guān)聯(lián)。該關(guān)聯(lián)又稱為外鍵約束。

drop table if exists orders;create table if not exists orders( order_id int primary key, custormer_id int not null, foreign key fk_orders_custormers (custormer_id) references customers (custormer_id) on update cascade on delete no action);

(1)外鍵的命名中，首先是子表名，然后是父表名。orders表通過custormer_id依賴父表custormers，因此命名為fk_orders_custormers。

(2)括號中聲明表中哪個字段為外鍵

(3)references聲明依賴的哪張表的哪個字段(主鍵)

(4)on update和on delete用來聲明，父表的對應(yīng)字段變化時，子表是否要同步變化

update和delete分別指代父表的更新和刪除的變化。分析可知，顧客id變化時，訂單表中的id也應(yīng)該同步，從而維持一致性，因此采用cascase模型；而當(dāng)顧客id意外刪除時，訂單表應(yīng)保持原來的訂單信息，不能同步刪除，因此采用no action模式，即什么也不做

(5)刪除表時需要按照先刪除子表，再刪除父表的順序。因此需要將刪除orders表的語句，放在刪除custormers表之前

修改主鍵或外鍵約束，同樣使用alter關(guān)鍵字：

alter table orders add primary key (order_id), drop primary key, add foreign key fk_orders_custormers (customer_id) references customers (customer_id) on update cascade on delete no action, drop foreign key fk_orders_custorms, ;

No.3? ? 索引

---

在查詢數(shù)據(jù)時，如果時逐行掃描，無疑查詢速度會非常慢。索引的出現(xiàn)，便是為了加速查詢。

舉例：在customers表中查詢state為CA的記錄

可以預(yù)先將state的值建立成有序的索引，這樣先通過索引查到相應(yīng)行，再依次讀取customers表中的相應(yīng)行即可，從而避免了全表掃描

索引優(yōu)點：

• 避免全表掃描，提升查詢效率
• 相比放在磁盤的數(shù)據(jù)庫表，索引比較小，可以放在內(nèi)存中，提升查詢速度

索引缺點：

• 增加了額外的索引表，數(shù)據(jù)庫體積變大
• 每次更新數(shù)據(jù)，都要同步維護(hù)索引，降低寫效率

1. 索引的優(yōu)化原理

查詢問題，就是算法中的查找問題。查找問題最經(jīng)典的解決方案，是二分查找。索引是一種有序的數(shù)據(jù)結(jié)構(gòu)，其本質(zhì)是一棵搜索樹。為了提高平均查詢性能，基本都是平衡搜索樹(balance search tree)。MySQL中采用的數(shù)據(jù)結(jié)構(gòu)為B樹/B+樹。

舉例：8條數(shù)據(jù)，編號依次為1，2，...，8，如何組織索引？

數(shù)據(jù)庫中的記錄在磁盤中按照頁式存儲，由于頁的大小固定(一般為16kB)，因此數(shù)據(jù)分布在若干頁上，為簡化問題，假設(shè)每頁的容量為2。如下圖所示，8條數(shù)據(jù)兩兩組合構(gòu)成了4頁，每組之間通過前后指針連接。

下面建立索引。由于頁號有序，我們可以依次記錄每一頁的起始編號，這樣得到了4條索引，分別為1，3，5，7。代表所指向頁中的最小編號。同樣，這4條索引兩兩組合，共占用兩頁。依次類推，可以得到最上層的兩個索引1，6，共占用一頁。索引樹建立完成，可以看到是一棵二叉搜索樹，本質(zhì)就是一個分級目錄

此時，如查詢4，從樹根開始走過的路徑如下。對1-8中任何一條記錄的查詢，操作次數(shù)不會超過三次。由上到下，每下一層，數(shù)據(jù)范圍減半。這便是最典型的二分法

從上面的描述中，我們發(fā)現(xiàn)只有最底層的葉子節(jié)點存儲數(shù)據(jù)，建立在其上的索引并不存儲真實數(shù)據(jù)。

在實際中，每一頁存儲的數(shù)據(jù)不止兩個，因此索引的數(shù)量也遠(yuǎn)遠(yuǎn)小于數(shù)據(jù)的數(shù)量。這兩點使得索引需要的存儲體積很小，不用放在硬盤中，而可以直接放在內(nèi)存中。

上述為最簡情況，可以看到具備以下三個局限：

• 單次查詢的次數(shù)，取決于索引樹的樹高。對于二叉樹來說，樹高即為log2(n)，其中n為數(shù)據(jù)總量。因此，索引樹應(yīng)該越低越好。如何降低樹高？增加樹的出度即可，如3叉樹，4叉樹等等

• 以上分析是靜態(tài)的，實際中數(shù)據(jù)是動態(tài)地插入和刪除，因此搜索樹必須自動平衡，避免退化為鏈表。

• 范圍查詢難以實現(xiàn)，如查詢編號在4到6之間到數(shù)據(jù)，上述方法只能拆為4，5，6三次查詢

基于以上原因，索引一般采取B樹或B+樹。因為：

• B/B+樹出度大，因此樹高小，具有更少的比較次數(shù)

• B/B+樹的節(jié)點中可以存儲多個數(shù)據(jù)，可以充分利用磁盤的預(yù)讀特性減少磁盤I/O

• B/B+樹是平衡樹，在插入或刪除時會利用旋轉(zhuǎn)來使得失衡的樹恢復(fù)平衡

• B+樹只需遍歷葉子節(jié)點便可遍歷所有數(shù)據(jù)(如上圖葉子節(jié)點之間用指針互相連接)，有利于范圍查詢，這一點B樹則難以做到

例子中的編號有序，當(dāng)我們指定某個字段作為索引時，MySQL會先將該字段的所有當(dāng)前值構(gòu)建一個B+樹，后續(xù)數(shù)據(jù)的插入和刪除，會對索引樹進(jìn)行同步處理。

2. 索引的使用和分類

在MySQL中創(chuàng)建一個索引：

create index idx_points on customers(points); -- 基于points字段建立表customers表的索引，索引名稱為idx_points查看索引：show indexes in customers; 刪除索引：drop index idx_points on customers;

索引的建立本質(zhì)上就是一個對原數(shù)據(jù)進(jìn)行分段分層的目錄管理問題，根據(jù)某個字段建立索引，即將該列的值進(jìn)行了排序。

1. 聚簇索引：索引和數(shù)據(jù)存儲在一塊

也叫主鍵索引，根據(jù)主鍵創(chuàng)建的索引，自動創(chuàng)建，只有一個

按照主鍵構(gòu)造一棵B+樹，若無主鍵，采用非空唯一鍵。如果仍沒有，則會隱式采用某個字段。

葉子節(jié)點中存放的整張表的行記錄數(shù)據(jù)。葉子節(jié)點稱為數(shù)據(jù)頁，每個數(shù)據(jù)頁通過一個雙向鏈表來進(jìn)行鏈接，而且數(shù)據(jù)頁按照主鍵的順序進(jìn)行排列。每個數(shù)據(jù)頁上存放的是完整的行記錄，而在非數(shù)據(jù)頁的索引頁中，僅存放鍵值及指向數(shù)據(jù)頁的頁號。即上面圖例所示

2. 非聚簇索引：索引和數(shù)據(jù)分離

也叫輔助索引(二級索引)，用戶定義，可以有多個

非聚簇索引構(gòu)建的B+樹，只記錄主鍵值。在進(jìn)行查詢時，先利用輔助索引查找到主鍵值，再通過主索引查找到行數(shù)據(jù)。因此需要兩次索引查找

舉例：學(xué)生表：，，，，其中id為主鍵，會自動根據(jù)id建立主索引：idx_id。用戶也可以指定，根據(jù)city或者score建立輔助索引：idx_city或idx_score

非聚簇索引包含：普通索引，唯一索引，前綴索引等，這些因為只依據(jù)一個字段，因此又稱為單列索引。與之相對的，是由多個字段組合構(gòu)成的索引，稱為組合索引。下面分兩類分別介紹。

2.1 單列索引

普通索引：最基本的索引，沒有任何限制，是我們大多數(shù)情況下使用到的索引

唯一索引：與普通索引類似，不同的是唯一索引的列值必須唯一，但允許為空值

全文索引：僅可以適用于MyISAM引擎的數(shù)據(jù)表；作用于CHAR、VARCHAR、TEXT數(shù)據(jù)類型的列

前綴索引：對于string類的數(shù)據(jù)，可將值的前一部分作為索引，如前5個字符。這樣既可以節(jié)約空間，又可以提高查詢效率。所取長度需根據(jù)具體情況來確定最優(yōu)值。但無法使用前綴索引做 ORDER BY 和 GROUP BY，也無法使用前綴索引做覆蓋掃描。

舉例：學(xué)生表：，，，，其中name有：streadeerff，addedeasa，defwwdserfs三個記錄值。假設(shè)直接指定name字段建立輔助索引，那么B+樹中需要存儲streadeerff，addedeasa，stfwwdserfs這三個string。但其實我們根據(jù)前三個字符即可區(qū)分，指定前綴索引長度為3，則B+樹中記錄的為：str，add，stf

覆蓋索引：索引本身就包含所有需要查詢的字段的值，sql只需要通過索引就可以返回查詢所需要的數(shù)據(jù)，這樣避免了查到索引后再返回表拿取數(shù)據(jù)的操作，減少I/O提高效率。

具有以下優(yōu)點：

• 索引通常遠(yuǎn)小于數(shù)據(jù)行的大小，只讀取索引能大大減少數(shù)據(jù)訪問量。

• 一些存儲引擎(例如 MyISAM)在內(nèi)存中只緩存索引，而數(shù)據(jù)依賴于操作系統(tǒng)來緩存。因此，只訪問索引可以不使用系統(tǒng)調(diào)用(通常比較費時)。

• 對于 InnoDB 引擎，若輔助索引能夠覆蓋查詢，則無需訪問主索引。

2.2 組合索引

采用多個字段構(gòu)成索引

當(dāng)查詢條件涉及多個字段，而每個字段都有單列索引時，MySQL會根據(jù)書寫順序采取第一個字段對應(yīng)的索引來進(jìn)行查詢，然后對查詢結(jié)果依次掃描，效率較低

如下圖中，查詢涉及state字段和points字段，對應(yīng)索引為idx_state和idx_points，實際MySQL采用idx_state進(jìn)行查詢出所有位于CA的記錄，然后遍歷比對其points是否大于1000

創(chuàng)建組合索引：idx_state_points，查詢效率會大大提升

組合索引適用于多條件查詢的情況，同時能縮減輔助索引數(shù)量。如上例中，有了idx_state_points，便不再需要idx_state和idx_points，減少了索引開銷

組合索引順序

應(yīng)讓選擇性最強的索引列放在前面

如性別，只有男、女兩類，那么通過性別來篩選，只能縮減為原來的1/2。而如果根據(jù)城市，假設(shè)有48個城市，那么通過城市來篩選，便可將數(shù)據(jù)規(guī)模縮減為原來的1/48。顯然后者的篩選效率更高

舉例：查詢居住在CA，名字以A開頭的顧客

組合索引1: inx_lastName_state，先根據(jù)last_name排序，再根據(jù)state排序，查詢過程如左圖

組合索引1: inx_state_lastName，先根據(jù)state，再根據(jù)last_name，查詢過程如右圖

索引失效

1. 在進(jìn)行查詢時，索引列不能是表達(dá)式的一部分，也不能是函數(shù)的參數(shù)，否則無法使用索引。

例如下面的查詢不能使用 ?points 列的索引：

select customer_id FROM customers WHERE points + 1 = 5

2. 組合索引順序與where子句順序應(yīng)相統(tǒng)一

當(dāng)創(chuàng)建(a,b,c)組合索引時，相當(dāng)于創(chuàng)建了(a)單列索引，(a,b)組合索引以及(a,b,c)組合索引

想要索引生效的話,只能使用 a和a,b和a,b,c三種組合。當(dāng)然，a,c組合也可以，但實際上只用到了a的索引，c并沒有用到！

舉例：復(fù)合索引的結(jié)構(gòu)與電話簿類似，人名由姓和名構(gòu)成，電話簿首先按姓氏對進(jìn)行排序，然后按名字對有相同姓氏的人進(jìn)行排序。如果知道姓，電話簿將非常有用；如果知道姓和名，電話簿則更為有用，但如果只知道名不知道姓，電話簿將沒有用處。

所以說創(chuàng)建復(fù)合索引時，應(yīng)該仔細(xì)考慮列的順序。對索引中的所有列執(zhí)行搜索或僅對前幾列執(zhí)行搜索時，組合索引非常有用；僅對后面的任意列執(zhí)行搜索時，組合索引則沒有用處。

本質(zhì)原因：組合索引是按照字段依次排序生成的

3. 索引總結(jié)

索引的優(yōu)點

1. 大大減少了服務(wù)器需要掃描的數(shù)據(jù)行數(shù)。

2. 幫助服務(wù)器避免進(jìn)行排序和分組，以及避免創(chuàng)建臨時表(B+Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY 操作。臨時表主要是在排序和分組過程中創(chuàng)建，不需要排序和分組，也就不需要創(chuàng)建臨時表)

3. 將隨機 I/O 變?yōu)轫樞?I/O(B+Tree 索引是有序的，會將相鄰的數(shù)據(jù)都存儲在一起)

索引的使用條件

1. 對于非常小的表、大部分情況下簡單的全表掃描比建立索引更高效；

2. 對于中到大型的表，索引就非常有效；

3. 但是對于特大型的表，建立和維護(hù)索引的代價將會隨之增長。這種情況下，需要用到一種技術(shù)可以直接區(qū)分出需要查詢的一組數(shù)據(jù)，而不是一條記錄一條記錄地匹配，例如可以使用分區(qū)技術(shù)。

總結(jié)

以上是生活随笔為你收集整理的mysql 同一张表 某个字段更新到另一条数据上_面试基础：数据库MySQL基础入门(下)...的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。