1.? 存儲引擎

? 1.1? InnoDB/MyIsam

? ? ? ? ?InnoDB(默認)-->支持外鍵，支持事務，提供表級鎖和行級鎖(默認)

? ? ? ? ?MyIsam-->只有表級鎖

2.? 事務的概念和 ACID 特性

? 2.1? 事務(Start Transaction/rollback/commit)

? 2.2? ACID

? ? ? ? C(一致性)是目的，A(原子性)、I(隔離性)、D(持久性)是手段

3.? 事務隔離級別詳解

? 3.1? 隔離級別(讀未提交/讀已提交/可重復讀/串行化)

? 3.2? 一致性問題(更新丟失/臟讀/不可重復讀/幻讀)

? 3.3??InnoDB 存儲引擎的默認支持的隔離級別是可重讀，達到了可串行化隔離級別

4.? 分布式事務

? 4.1? 開啟分布式事務(@EnableDistributedTransaction)

? 4.2? 使用分布式事務(@LcnTransation/@TCCTransation)

5.? 數據庫鎖和并發策略

? 5.1? 鎖(表鎖/頁級鎖/行鎖/讀鎖/寫鎖/共享鎖/排它鎖)

? 5.2? 并發策略(樂觀鎖/悲觀鎖)

6.? 存儲過程和觸發器

7.? 數據庫結構優化

? 7.1? 三大范式

? 7.2? 數據庫命名規范

? 7.3? 建表時優化

? ? ? ? 【列選擇原則】【主鍵的選擇】【反范式設計表】【將字段很多的表分解成多個表】

? ? ? ? 【增加中間表】【增加冗余字段】

8.? 查詢語句優化

? 8.1? 讀寫分離，分庫分表

? ? ? ? ?垂直拆分[分庫]? 水平拆分[分表]

? 8.2? explain

? ? ? ?【id】【select_type】【table】【type】【possible_keys】【key】

? 8.3? 常見的查詢優化

? ? ? ?【避免直接使用 select *】【避免造成索引失效】【IN 包含的值不應過多】【?limit 1】

? ? ? ?【能用 inner join 就不用 left join right join，如必須使用 一定要已小表為驅動】

? 8.4??子查詢優化

? ? ? ?【子查詢靈活，但執行效率并不高，執行子查詢時，MYSQL 需要創建臨時表】

? ? ? ?【使用連接查詢 (JOIN)? 代替子查詢】

9.? 索引及優化

? 9.1??索引的分類

? ? ? ?【普通索引】【主鍵索引】【唯一索引】【聯合索引】【全文索引】

? 9.2? 索引的優缺點

? ? ? ? 優點：【提高檢索的速度】【建立唯一索引或者主鍵索引，數據的唯一性】

? ? ? ? ? ? ? ? ? ?【表連接的連接條件】【分組和排序字句進行數據檢索】

? ? ? ? 缺點：【創建索引和維護索引會耗費時間】【索引文件會占用物理空間】

? ? ? ? ? ? ? ? ? ?【增刪改索引也要動態的維護】

9.3? 創建索引

? ? ? ?【經常需要搜索的列】【主鍵列上可以確保列的唯一性】【表與表的連接條件上】【排序，分組】

9.4? 索引失效

? ? ? ? 【最左前綴原則】【只有or前后的字段都帶索引才生效】【like 查詢以%開頭會失效】

? ? ? ? 【在索引的列上使用表達式或者函數會使索引失效】

? ? ? ? 【如果列類型是字符串，那一定要在條件中將數據使用單引號引用起來，否則不使用索引】

9.5? 索引優化

? ? ? ? ?【最左前綴原則】【注意避免冗余索引】【聚簇索引與非聚簇索引】

? ? ? ? ? 【既存儲了主鍵值，又存儲了行數據，這種結構稱為 "聚簇索引"、innodb為聚簇索引】

10.? 插入數據的優化

? 10.1? 影響插入速度的原因

? ? ? ? ? 【索引】【唯一性校驗】【一次插入的數據條數】

? 10.2??MyISAM

? ? ? ? ?【禁用索引】【禁用唯一性檢查】【批量插入數據】

? 10.3??InnoDB

? ? ? ? ? 【禁用唯一性檢查】【禁用外鍵檢查】【禁止自動提交】

數據庫存儲引擎是數據庫底層軟件組織，數據庫管理系統（DBMS）使用數據引擎進行創建、查詢、更新和刪除數據

不同的存儲引擎提供不同的存儲機制、索引技巧、鎖定水平等功能，使用不同的存儲引擎，還可以獲得特定的功能

現在許多不同的數據庫管理系統都支持多種不同的數據引擎

存儲引擎主要有： 1. MyIsam，?2. InnoDB， 3. Memory，4. Archive，5. Federated

Mysql 在 5.5.5 之前默認存儲引擎是 MyISAM；在此之后默認存儲引擎是 InnoDB

Server 層：主要包括連接器、查詢緩存、分析器、優化器、執行器等，所有跨存儲引擎的功能都在這一層實現，比如存儲過程、觸發器、視圖，函數等，還有一個通用的日志模塊 binglog 日志模塊

存儲引擎：主要負責數據的存儲和讀取，采用可以替換的插件式架構，支持 InnoDB、MyISAM、Memory 等多個存儲引擎，其中 InnoDB 引擎有自有的日志模塊 redolog 模塊

現在最常用的存儲引擎是 InnoDB，它從MySQL 5.5.5 版本開始就被當做默認存儲引擎了

當創建表時，應根據表的應用場景選擇適合的存儲引擎

MyISAM 表最適合于大量的數據讀而少量數據更新的混合操作

MyISAM 表的另一種適用情形是使用壓縮的只讀表

如果查詢中包含較多的數據更新操作，應使用 InnoDB

其行級鎖機制和多版本的支持為數據讀取和更新的混合操作提供了良好的并發機制

可使用 MEMORY 存儲引擎來存儲非永久需要的數據，或者是能夠從基于磁盤的表中重新生成的數據

MyISAM

InnoDB

count 運算上的區別

因為 MyISAM 緩存有表 meta-data(行數等)，因此在做 COUNT(*) 時對于一個結構很好的查詢是不需要消耗多少資源的

對于 InnoDB 來說，則沒有這種緩存

是否支持事務和崩潰后的安全恢復

MyISAM 強調的是性能，每次查詢具有原子性，其執行數度比 InnoDB 類型更快，但是不提供事務支持

InnoDB 提供事務支持事務，外部鍵等高級數據庫功能

具有事務(commit)、回滾(rollback)和崩潰修復能力(crash recovery capabilities)的事務安全(transaction-safe (ACIDcompliant))型表

是否支持外鍵

MyISAM 不支持

InnoDB 支持

是否支持行級鎖

MyISAM 只有表級鎖(table-level locking)

InnoDB 支持行級鎖(row-level locking)和表級鎖，默認為行級鎖

MyISAM 更適合讀密集的表，而 InnoDB 更適合寫密集的的表

在數據庫做主從分離的情況下，經常選擇 MyISAM 作為主庫的存儲引擎

一般來說，如果需要事務支持，并且有較高的并發讀取頻率（MyISAM 的表鎖的粒度太大，所以當該表寫并發量較高時，要等待的查詢就會很多了），InnoDB 是不錯的選擇

如果你的數據量很大（MyISAM 支持壓縮特性可以減少磁盤的空間占用），而且不需要支持事務時，MyISAM 是最好的選擇

如果表的類型是?MyISAM，那么是?18

因為 MyISAM 表會把自增主鍵的最大 ID 記錄到數據文件里，重啟 MySQL 自增主鍵的最大 ID 也不會丟失

如果表的類型是?InnoDB，那么是 15

InnoDB 表只是把自增主鍵的最大 ID 記錄到內存中，所以重啟數據庫或者是對表進行 OPTIMIZE 操作，都會導致最大 ID 丟失

存儲引擎不同，索引生成的文件也不同

? Myisam 生成 3 個文件，非聚簇索引

? ? frm 為表結構文件

? ? MYD 是數據文件

? ? MYI? 是索引文件

? InnoDB 生成 2 個文件，聚簇索引

? ? frm 為表結構文件

? ? ibd 為索引+數據

Mysql 服務器的默認端口是 3306

％ 對應于?0 個或更多字符

_? 只是 LIKE 語句中的一個字符

事務（TRANSACTION）表示一個完整的不可分割的業務，批量的 DML 語句同時成功或者同時失敗；原子性不可再分最基本單元，可以看做一個完整的事件，通常一個事務可以對應一個完整的業務流程

可以保證多個操作原子性，要么全成功，要么全失敗

對于數據庫來說事務保證批量的 DML 要么全成功，要么全失敗

事務中存在一些概念：

a) 事務（Transaction）：保證批量的 DML 同時成功或同時失敗；一批操作（一組 DML）

b) 開啟事務（Start Transaction）

c) 回滾事務（rollback）

d) 提交事務（commit）

e) SET AUTOCOMMIT：禁用或啟用事務的自動提交模式

當執行 DML 語句時其實就是開啟一個事務

關于事務的回滾需要注意：只能回滾 insert、delete 和 update 語句，不能回滾 select（回滾 select 沒有任何意義），對于 create、drop、alter 這些無法回滾

事務只對 DML 有效果

注意：rollback，或者 commit 后事務就結束了

整個事務中的所有操作，必須作為一個單元全部完成（或全部取消）

undo log 名為回滾日志，是實現原子性的關鍵，當事務回滾時能夠撤銷所有已經成功執行的 sql 語句，他需要記錄你要回滾的相應日志信息

在事務開始之前與結束之后，數據庫都保持一致狀態?

從數據庫層面，數據庫通過原子性、隔離性、持久性來保證一致性

也就是說 ACID 四大特性之中，C（一致性）是目的，A（原子性）、I（隔離性）、D（持久性）是手段，是為了保證一致性，數據庫提供的手段

數據庫必須要實現 AID 三大特性，才有可能實現一致性；例如，原子性無法保證，顯然一致性也無法保證

但是，如果你在事務里故意寫出違反約束的代碼，一致性還是無法保證的

例如，你在轉賬的例子中，你的代碼里故意不給 B 賬戶加錢，那一致性還是無法保證；因此，還必須從應用層角度考慮

從應用層面，通過代碼判斷數據庫數據是否有效，然后決定回滾還是提交數據

一個事務不會影響其他事務的運行

事務的隔離性是利用的是鎖和 MVCC（Multi Version Concurrency Control?多版本并發控制）機制

在事務完成以后，該事務對數據庫所作的更改將持久地保存在數據庫之中，并不會被回滾

事務的持久性是利用 Innodb 的 redo log

InnoDB 實現了四個隔離級別，用以控制事務所做的修改，并將修改通告至其它并發的事務：

讀未提交（READ UMCOMMITTED）

允許一個事務可以看到其他事務未提交的修改

讀已提交（READ COMMITTED）

允許一個事務只能看到其他事務已經提交的修改，未提交的修改是不可見的

可重復讀（REPEATABLE READ）

確保如果在一個事務中執行兩次相同的 SELECT 語句，都能得到相同的結果，不管其他事務是否提交這些修改（銀行總賬）

串行化（SERIALIZABLE） 【序列化】

該隔離級別為 InnoDB 的缺省設置

將一個事務與其他事務完全地隔離

臟讀（Dirty Read）：一個事務在提交之前，在事務過程中修改的數據，被其他事務讀取到了

某個事務已更新一份數據，另一個事務在此時讀取了同一份數據，由于某些原因，前一個RollBack了操作，則后一個事務所讀取的數據就會是不正確的

不可重復讀（Non-repeatable Read）：一個事務在提交之前，在事務過程中讀取以前的數據卻發現數據發生了改變

在一個事務的兩次查詢之中數據不一致，這可能是兩次查詢過程中間插入了一個事務更新的原有的數據

幻讀（Phantom Read）：一個事務按照相同的條件重新讀取以前檢索過的數據時，卻發現了其他事務插入的新數據

在一個事務的兩次查詢中數據筆數不一致，例如有一個事務查詢了幾列（Row）數據，而另一個事務卻在此時插入了新的幾列數據，先前的事務在接下來的查詢中，就會發現有幾列數據是它先前所沒有的

通用的解決思路是更新丟失通過應用程序完全避免

而其他的問題點則通過調整數據庫事務隔離級別來解決

隔離級別

臟讀

不可重復讀

幻影讀

√

√

√

×

√

√

×

×

√

×

×

×

與 SQL 標準不同的地方在于 InnoDB 存儲引擎在 REPEATABLE-READ（可重讀）事務隔離級別下使用的是Next-Key Lock 鎖算法，因此可以避免幻讀的產生，這與其他數據庫系統(如 SQL Server)是不同的

所以說 InnoDB 存儲引擎的默認支持的隔離級別是 REPEATABLE-READ（可重讀） 已經可以完全保證事務的隔離性要求，即達到了 SQL 標準的 SERIALIZABLE（可串行化）隔離級別

因為隔離級別越低，事務請求的鎖越少，所以大部分數據庫系統的隔離級別都是 READ-COMMITTED（讀取提交內容），但是你要知道的是 InnoDB 存儲引擎默認使用 REPEATABLE-READ（可重讀）并不會有任何性能損失

InnoDB 存儲引擎在分布式事務的情況下一般會用到 SERIALIZABLE（可串行化）隔離級別

傳統項目中用一個 mysql 數據庫，mysql 數據庫當中 InnoDB 引擎是支持 ACID 事務特性的，能夠滿足我們的事務要求，但現在分布式項目中，我們會有多個微服務處理不同業務，這些微服務都是獨立的進程，數據庫也都是相互獨立，但是事務可能要橫跨這些微服務， 比如訂單功能， 如果我支付訂單成功在訂單微服務中我需要改變訂單狀態， 在庫存微服務中需要減對應的庫存， 在積分微服務中得加對應積分， 這是一個整體的事務，這種事務就是分布式事務

CAP 定理是由加州大學伯克利分校 Eric Brewer 教授提出來的，他指出 WEB 服務無法同時滿足以下三個屬性

一致性(Consistency)： 客戶端知道一系列的操作都會同時發生(生效)

可用性(Availability)： 每個操作都必須以可預期的響應結束

分區容錯性(Partition tolerance)： 即使出現單個組件無法可用，操作依然可以完成

在分布式系統中，我們往往追求的是可用性，它的重要程序比一致性要高，那么如何實現高可用性呢？那就是另外一個理論 BASE 理論，它是用來對 CAP 定理進行進一步擴充的

BASE 理論指的是

Basically Available（基本可用）：分布式系統在出現故障時，允許損失部分可用功能，保證核心功能可用

Soft state（軟狀態）：允許系統中存在中間狀態，這個狀態不影響系統可用性，這里指的是 CAP 中的不一致

Eventually consistent（最終一致性）：指經過一段時間后，所有節點數據都將會達到一致

BASE 理論是對 CAP 中的一致性和可用性進行一個權衡的結果，理論的核心思想就是：我們無法做到強一致，但每個應用都可以根據自身的業務特點，采用適當的方式來使系統達到最終一致性（Eventual consistency）

其核心思想是將分布式事務拆分成本地事務進行處理

白話

各個服務在處理數據過程中，會進行預備提交操作，預備操作可以反映出各個節點是否都能夠提交，預備操作

如果成功的話，那么事務協調器會執行每個數據庫的 commit 操作

如果在預提交過程當中有一個節點不能夠提交，事務協調器會執行每個數據庫的 roll back 操作

官腔

事務管理器要求每個涉及到事務的數據庫預提交（precommit）此操作，并反映是否可以提交

事務協調器要求每個數據庫提交數據，或者回滾數據

實現的思路

消息生產方，需要額外建一個消息表，并記錄消息發送狀態

消息表和業務數據要在一個事務里提交，也就是說他們要在一個數據庫里面

然后消息會經過 MQ 發送到消息的消費方

如果消息發送失敗，會進行重試發送消息消費方，需要處理這個消息，并完成自己的業務邏輯

此時如果本地事務處理成功，表明已經處理成功了，如果處理失敗，那么就會重試執行

如果是業務上面的失敗，可以給生產方發送一個業務補償消息，通知生產方進行回滾等操作

生產方和消費方定時掃描本地消息表，把還沒處理完成的消息或者失敗的消息再發送一遍

如果有靠譜的自動對賬補賬邏輯，這種方案還是非常實用的

這種方案遵循 BASE 理論，采用的是最終一致性

Try 階段：嘗試執行，完成所有業務檢查（一致性），預留必需業務資源（準隔離性）

Confirm 階段：確認真正執行業務，不作任何業務檢查，只使用 try 階段預留的業務資源，Confirm 操作滿足冪等性；要求具備冪等設計，Confirm 失敗后需要進行重試；默認只要 Try 成功，Confirm 一定成功

Cancel 階段：取消執行，釋放 Try 階段預留的業務資源，Cancel 操作滿足冪等性；Cancel 階段的異常和Confirm 階段異常處理方案基本上一致

舉個簡單的例子：如果你用 100 元買了一瓶水， Try 階段：你需要向你的錢包檢查是否夠 100 元并鎖住這 100 元，水也是一樣的。如果有一個失敗，則進行 Cancel（釋放這 100 元和這一瓶水），如果 Cancel 失敗不論什么失敗都進行重試 Cancel，所以需要保持冪等。如果都成功，則進行 Confirm，確認這 100 元被扣，和這一瓶水被賣，如果 Confirm 失敗無論什么失敗則重試（會依靠活動日志進行重試）

使用開源框架 TX-LCN 分布式事務框架 5.0 版本對代碼的侵入比較少，而且提供了完整的 SpringCloud 和dubbo 注解解決方案，項目組引入該框架的成本非常低，并且有專門的公司對這個開源項目進行維護

該框架主要有兩大核心部分 TM 事務的協調管理器需要獨立運行（在源碼中有該工程，并且整合了 docker 只需要改下配置文件中 mysql、redis 的地址即可運行）

所有參與分布式事務的微服務項目需要引入客戶端依賴

com.codingapi.txlcn

txlcn-txmsg-netty

5.0.2.RELEASE

在啟動類當中，使用 @EnableDistributedTransaction 注解開啟分布式事務

在配置文件中配置好 Tx-manager 的連接地址

在需要進行事務管理的 service 方法中

如果要使用分布式事務加上 @LcnTransation；如果要使用 TCC 的模式需要加上@TCCTransation 注解并且在該注解指定 confirm 對應的方法以及 cancel 對應的方法

事務的隔離機制的實現手段之一就是利用鎖

MyIsam 實現了表鎖

表鎖可以針對數據庫表加鎖，在鎖的靈活性上不如行鎖

表鎖分為兩種鎖：讀鎖與寫鎖

InnoDB 存儲引擎實現了行鎖與表鎖（意向鎖）

行鎖可以以行為單位對數據集進行鎖定

行鎖也分為兩種鎖：共享鎖與排他鎖

InnoDB 對于 Update、Delete、Insert 語句會自動給涉及的數據集隱式的加上排他鎖

對于 select 語句 InnoDB 不會加任何鎖

共享鎖：允許一個事務讀取一行，阻止其他事務獲得相同數據集的排他鎖

但允許其他事務獲取共享鎖

顯示加鎖 select * from table where ... lock in share mode

共享鎖，事務加多少，都能讀

修改是唯一的，必須等待前一個事務 commit，才可以下個修改

排他鎖：允許獲得排他鎖的事務更新數據，阻止其他事務取得相同數據集的共享與排他鎖

但是可以對獲取了排他鎖的數據集進行單純的查詢訪問

顯示加鎖 sql select * from table where ... for update

排他鎖，不允許其它事務增加共享或排他鎖讀取

修改是唯一的，必須等待前一個事務 commit，才可以下一個修改

InnoDB 的行鎖的實現方式是基于索引項的

這意味著即使你嘗試獲取不同行的排他鎖，若使用了相同的索引鍵，也可能會造成鎖沖突

行級鎖是一種排他鎖，防止其他事務修改此行

行鎖的特點是開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的概率最低，并發度也最高

在使用以下語句時，Oracle 會自動應用行級鎖：

1. INSERT、UPDATE、DELETE、SELECT ? FOR UPDATE [OF columns] [WAIT n | NOWAIT];

2. SELECT ? FOR UPDATE 語句允許用戶一次鎖定多條記錄進行更新

3. 使用 COMMIT 或 ROLLBACK 語句釋放鎖

表鎖的特點是開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖沖突的概率最高，并發度最低

表示對當前操作的整張表加鎖，它實現簡單，資源消耗較少，被大部分 MySQL 引擎支持

最常使用的 MYISAM與 INNODB 都支持表級鎖定

頁級鎖是 MySQL 中鎖定粒度介于行級鎖和表級鎖中間的一種鎖

表級鎖速度快，但沖突多，行級沖突少，但速度慢

所以取了折衷的頁級，一次鎖定相鄰的一組記錄

BDB 支持頁級鎖

樂觀鎖認為一個用戶讀數據的時候，別人不會去寫自己所讀的數據；悲觀鎖就剛好相反，覺得自己讀數據庫的時候，別人可能剛好在寫自己剛讀的數據，其實就是持一種比較保守的態度；時間戳就是不加鎖，通過時間戳來控制并發出現的問題

解決方案為：時間戳就是在數據庫表中單獨加一列時間戳，比如 "TimeStamp"，每次讀出來的時候，把該字段也讀出來，當寫回去的時候，把該字段加 1，提交之前 ，跟數據庫的該字段比較一次，如果比數據庫的值大的話，就允許保存，否則不允許保存，這種處理方法雖然不使用數據庫系統提供的鎖機制，但是這種方法可以大大提高數據庫處理的并發量

Select max(nub) ,version from biao

Update biao set nub=nub+1,version=vsersion+1 where id=id and version =version

悲觀鎖就是在讀取數據的時候，為了不讓別人修改自己讀取的數據，就會先對自己讀取的數據加鎖，只有自己把數據讀完了，才允許別人修改那部分數據，或者反過來說，就是自己修改某條數據的時候，不允許別人讀取該數據，只有等自己的整個事務提交了，才釋放自己加上的鎖，才允許其他用戶訪問那部分數據

悲觀鎖所說的加 "鎖"，其實分為幾種鎖，分別是：排它鎖（寫鎖）和共享鎖（讀鎖）

解決方案為 sql 語句后邊加上 for update 例子：select id,nam from biao for update

鎖表發生在 insert、update 、delete 中

鎖表的原理是數據庫使用獨占式鎖機制，當執行 insert、update 和 delete 的語句時，對表進行鎖住，直到發生commite 或者回滾或者退出數據庫用戶

鎖表的原因 : 當多個連接（數據庫連接）同時對一個表的數據進行更新操作，那么速度將會越來越慢，持續一段時間后將出現數據表被鎖的現象，從而影響到其它的查詢及更新

A 程序執行了對 tableA 的 insert ，并還未 commite 時，B 程序也對 tableA 進行 insert 則此時會發生資源正忙的異常就是鎖表

鎖表常發生于并發而不是并行（并行時，一個線程操作數據庫時，另一個線程是不能操作數據庫的，cpu和 i/o 分配原則）

減少鎖表的概率

減少 insert 、update 、delete 語句執行到 commite 之間的時間；具體點批量執行改為單個執行、優化 sql 自身的非執行速度

如果異常對事物進行回滾

一組為了完成特定功能的 SQL 語句集，存儲在數據庫中，經過第一次編譯后再次調用不需要再次編譯，用戶通過指定存儲過程的名字并給出參數（如果該存儲過程帶有參數）來執行它；存儲過程是數據庫中的一個重要對象

存儲過程優化思路：

1. 盡量利用一些 sql 語句來替代一些小循環，例如聚合函數，求平均函數等

2. 中間結果存放于臨時表，加索引

3. 少使用游標

sql 是個集合語言，對于集合運算具有較高性能；而 cursors 是過程運算；比如對一個 100 萬行的數據進行查詢；游標需要讀表 100 萬次，而不使用游標則只需要少量幾次讀取

4. 事務越短越好

sqlserver 支持并發操作；如果事務過多過長，或者隔離級別過高，都會造成并發操作的阻塞，死鎖；導致查詢極慢，cpu 占用率極地

5. 使用 try-catch 處理錯誤異常

6. 查找語句盡量不要放在循環內

觸發器是一段能自動執行的程序，是一種特殊的存儲過程，觸發器和普通的存儲過程的區別是： 觸發器是當對某一個表進行操作時觸發

諸如：update、insert、delete 這些操作的時候，系統會自動調用執行該表上對應的觸發器

SQL Server 2005 中觸發器可以分為兩類：DML 觸發器和 DDL 觸發器，其中 DDL 觸發器它們會影響多種數據定義語言語句而激發，這些語句有 create、 alter、drop 語句

一個好的數據庫設計方案對于數據庫的性能往往會起到事半功倍的效果

需要考慮數據冗余、查詢和更新的速度、字段的數據類型是否合理等多方面的內容

第一范式：有主鍵，具有原子性，字段不可分割

第二范式：完全依賴，表中非主鍵列不存在對主鍵的部分依賴

要求每個表只描述一 件事情

第三范式：沒有傳遞依賴，表中的列不存在對非主鍵列的傳遞依賴

數據庫設計盡量遵循三范式，但是還是根據實際情況進行取舍，有時可能會拿冗余換速度，最終用目的要滿足客戶需求

所有數據庫對象名稱必須使用小寫字母并用下劃線分割

所有數據庫對象名稱禁止使用 MySQL 保留關鍵字（如果表名中包含關鍵字查詢時，需要將其用單引號括起來）

數據庫對象的命名要能做到見名識意，并且最后不要超過 32 個字符

臨時庫表必須以 tmp 為前綴并以日期為后綴，備份表必須以 bak 為前綴并以日期 (時間戳) 為后綴

所有存儲相同數據的列名和列類型必須一致（一般作為關聯列，如果查詢時關聯列類型不一致會自動進行數據類型隱式轉換，會造成列上的索引失效，導致查詢效率降低）

1: 字段類型優先級 整型 > date,time > char,varchar > blob

2: 夠用就行，不要慷慨 (如 smallint，varchar(N))

原因：列的字段越大，建立索引時所需要的空間也就越大，這樣一頁中所能存儲的索引節點的數量也就越少也越少，在遍歷時所需要的 IO 次數也就越多，索引的性能也就越差

3: 盡量避免用 NULL()

原因：索引 NULL 列需要額外的空間來保存，所以要占用更多的空間進行比較和計算時要對 NULL 值做特別的處理

4：對于非負型的數據 (如自增 ID、整型 IP) 來說，要優先使用無符號整型來存儲

原因：無符號相對于有符號可以多出一倍的存儲空間

SIGNED INT -2147483648~2147483647 UNSIGNED INT 0~4294967295

VARCHAR(N) 中的 N 代表的是字符數，而不是字節數，使用 UTF8 存儲 255 個漢字 Varchar(255)=765 個字節

過大的長度會消耗更多的內存

5：避免使用 TEXT、BLOB 數據類型，最常見的 TEXT 類型可以存儲 64k 的數據

6：同財務相關的金額類數據必須使用 decimal 類型

非精準浮點：float，double

精準浮點：decimal

Decimal 類型為精準浮點數，在計算時不會丟失精度

占用空間由定義的寬度決定，每 4 個字節可以存儲 9 位數字，并且小數點要占用一個字節可用于存儲比 bigint 更大的整型數據

主鍵用來區分，查找，和關聯數據，非常重要

1.在 myisam 中，字符串索引會被壓縮，用字符串做主鍵性能不如整型

2. 用遞增的值，不要用離散的值，離散值會導致文件在磁盤的位置有間隔，浪費空間且不易連續讀取

3. UUID，也是逐步增長的，可以去掉 "-"，轉換為整數

反范式的目的--減少表的關聯查詢

常用辦法：

冗余字段和冗余表

冗余字段：表中某字段存儲另一表的統計信息

冗余表：表中統計或匯總其他表的信息，又稱匯總表

本質

1.? 空間換時間

2.? 大任務分成小任務，分散執行

對于字段較多的表，如果有些字段的使用頻率很低，可以將這些字段分離出來形成新表

因為當一個表的數據量很大時，會由于使用頻率低的字段的存在而變慢

舉例：商品表和商品描述表就是把描述字段拆分成一個單獨的表

Note；不是多對多的中間表

對于需要經常聯合查詢的表，可以建立中間表以提高查詢效率

通過建立中間表，將需要通過聯合查詢的數據插入到中間表中，然后將原來的聯合查詢改為對中間表的查詢

設計數據表時應盡量遵循范式理論的規約，盡可能的減少冗余字段，讓數據庫設計看起來精致、優雅

但是，合理的加入冗余字段可以提高查詢速度

表的規范化程度越高，表和表之間的關系越多，需要連接查詢的情況也就越多，性能也就越差

注意：

冗余字段的值在一個表中修改了，就要想辦法在其他表中更新，否則就會導致數據不一致的問題

舉例：商品表里增加類目名稱

1: sql 語句的時間花在哪兒?

答: 等待時間，執行時間

這兩個時間并非孤立的，如果單條語句執行的快了，對其他語句的鎖定的也就少了

所以，我們來分析如何降低執行時間

2: sql 語句的執行時間，又花在哪兒了?

答：

a: 查 ----> 沿著索引查，甚至全表掃描

b: 取 ----> 查到行后，把數據取出來(sending data)

3: sql 語句的優化思路？

答: 不查，通過業務邏輯來計算，比如論壇的注冊會員數，我們可以根據前 3 個月統計的每天注冊數，用程序來估算

少查，盡量精準數據，少取行；我們觀察新聞網站，評論內容等，一般一次性取列表 10-30 條左右

必須要查，盡量走在索引上查詢行

取時，取盡量少的列

比如 select * from tableA，就取出所有列，不建議

比如 select * from tableA,tableB，取出 A，B 表的所有列

4: 如果定量分析查的多少行，和是否沿著索引查?

答: 用 explain 來分析

合理安排資源、調整系統參數使 MySQL 運行更快、更節省資源

優化是多方面的，包括查詢、更新、服務器等

原則：減少系統瓶頸，減少資源占用，增加系統的反應速度

使用 SHOW STATUS 語句查看 MySQL 數據庫的性能參數

SHOW STATUS LIKE 'value'

常用的參數：

Slow_queries 慢查詢次數

Com_(CRUD) 操作的次數

Uptime 上線時間

SELECT 語句務必指明字段名稱（避免直接使用 select * ）

SQL 語句要避免造成索引失效的寫法

SQL 語句中 IN 包含的值不應過多

當只需要一條數據的時候，使用 limit 1

? ?第一個參數指定第一個返回記錄行的偏移量，第二個參數指定返回記錄行的最大數目

? ? ? SELECT * FROM table LIMIT 5,10; // 檢索記錄行 6-15

? ?為了檢索從某一個偏移量到記錄集的結束所有的記錄行，可以指定第二個參數為 -1

? ? ?SELECT * FROM table LIMIT 95,-1; // 檢索記錄行 96-last

? 如果只給定一個參數，它表示返回最大的記錄行數目

? ? ?SELECT * FROM table LIMIT 5; //檢索前 5 個記錄行

? ? ?換句話說，LIIT n 等價于 LIMIT 0,n

如果排序字段沒有用到索引，就盡量少排序

如果限制條件中其他字段沒有索引，盡量少用 or

盡量用 union all 代替 union

避免在 where 子句中對字段進行 null 值判斷

不建議使用 % 前綴模糊查詢

避免在 where 子句中對字段進行表達式操作

Join 優化 能用 inner join 就不用 left join right join，如必須使用 一定要已小表為驅動

為了提高查詢速度，我們可以通過不同的方式去緩存我們的結果從而提高響應效率

當我們的數據庫打開了 Query Cache（簡稱 QC）功能后，數據庫在執行 SELECT 語句時，會將其結果放到 QC 中，當下一次處理同樣的 SELECT請求時，數據庫就會從 QC 取得結果，而不需要去數據表中查詢

如果緩存命中率非常高的話，有測試表明在極端情況下可以提高效率 238%

如果數據庫的使用場景讀的操作比較的時候，為了避免寫的操作所造成的性能影響可以采用讀寫分離的架構，讀寫分離，解決的是，數據庫的寫入，影響了查詢的效率

讀寫分離的基本原理是讓主數據庫處理事務性增、改、刪操作（INSERT、UPDATE、DELETE），而從數據庫處理 SELECT 查詢操作

數據庫復制被用來把事務性操作導致的變更同步到集群中的從數據庫

數據量越來越大時，單體數據庫無法滿足要求，可以考慮分庫分表

兩種拆分方案：

垂直拆分：（分庫）業務表太多？ 將業務細化不同的小業務專門用一個庫來維護

水平拆分：（分表）單個表存的數據太多，裝不下了？ 將該表查分成多個

分庫分表常用工具：MyCat、Sharding-JDBC

在 MySQL 中可以使用 EXPLAIN 查看 SQL 執行計劃，用法：EXPLAIN SELECT * FROM tb_item

SELECT 識別符

這是 SELECT 查詢序列號

這個不重要

代表 select 語句的編號，如果是連接查詢，表之間是平等關系，select 編號都是 1，從 1 開始

如果某 select 中有子查詢，則編號遞增

表示 SELECT 語句的類型

有以下幾種值：

?

1. SIMPLE

表示簡單查詢，其中不包含連接查詢和子查詢

2. PRIMARY

表示主查詢，或者是最外面的查詢語句

?

3. UNION

表示連接查詢的第 2 個或后面的查詢語句

?

4. DEPENDENT UNION

UNION 中的第二個或后面的 SELECT 語句，取決于外面的查詢

5. UNION RESULT

連接查詢的結果

6. SUBQUERY

子查詢中的第 1 個 SELECT 語句

?

7. DEPENDENT SUBQUERY

子查詢中的第 1 個 SELECT 語句，取決于外面的查詢

8. DERIVED

SELECT(FROM 子句的子查詢)

表示查詢的表

有可能是

實際的表名 如 select * from t1;

表的別名 如 select * from t2 as tmp;

derived 如 from 型子查詢時

null 直接計算得結果，不用走表

表示表的連接類型

以下的連接類型的順序是從最佳類型到最差類型：

1. system

表僅有一行，這是 const 類型的特列，平時不會出現，這個也可以忽略不計

2. const

數據表最多只有一個匹配行，因為只匹配一行數據，所以很快，常用于 PRIMARY KEY 或者 UNIQUE 索引的查詢，可理解為 const 是最優化的

?

3. eq_ref

mysql 手冊是這樣說的："對于每個來自于前面的表的行組合，從該表中讀取一行

這可能是最好的聯接類型，除了 const 類型

它用在一個索引的所有部分被聯接使用并且索引是 UNIQUE 或 PRIMARY KEY"

eq_ref 可以用于使用=比較帶索引的列

?

4. ref

查詢條件索引既不是 UNIQUE 也不是 PRIMARY KEY 的情況

ref 可用于=或操作符的帶索引的列

?

5. ref_or_null

該聯接類型如同 ref，但是添加了 MySQL 可以專門搜索包含 NULL 值的行

在解決子查詢中經常使用該聯接類型的優化

上面這五種情況都是很理想的索引使用情況

6. index_merge

該聯接類型表示使用了索引合并優化方法

在這種情況下，key 列包含了使用的索引的清單，key_len 包含了使用的索引的最長的關鍵元素

7. unique_subquery

該類型替換了下面形式的 IN 子查詢的 ref：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique_subquery 是一個索引查找函數，可以完全替換子查詢，效率更高

8. index_subquery

該聯接類型類似于 unique_subquery

可以替換 IN 子查詢，但只適合下列形式的子查詢中的非唯一索引：

value IN (SELECT key_column FROM single_table WHERE some_expr)

9. range

只檢索給定范圍的行，使用一個索引來選擇行

?

10. index

該聯接類型與 ALL 相同，除了只有索引樹被掃描

這通常比 ALL 快，因為索引文件通常比數據文件小

11. ALL

對于每個來自于先前的表的行組合，進行完整的表掃描（性能最差）

可能用到的索引

注意: 系統估計可能用的幾個索引，但最終只能用 1 個

指出 MySQL 能使用哪個索引在該表中找到行

如果該列為 NULL，說明沒有使用索引，可以對該列創建索引來提供性能

最終用的索引

顯示 MySQL 實際決定使用的鍵(索引)

如果沒有選擇索引，鍵是 NULL

可以強制使用索引或者忽略索引：

?

顯示 MySQL 決定使用的鍵長度

如果鍵是 NULL，則長度為 NULL

注意：key_len 是確定了 MySQL 將實際使用的索引長度

使用的索引的最大長度

type 列：是指查詢的方式，非常重要，是分析 ”查數據過程” 的重要依據可能的值

all：意味著從表的第 1 行，往后，逐行做全表掃描，運氣不好掃描到最后一行

index：比 all 性能稍好一點

通俗的說：all 掃描所有的數據行,相當于 data_all index 掃描所有的索引節點,相當于 index_all

顯示使用哪個列或常數與 key 一起從表中選擇行

顯示 MySQL 認為它執行查詢時必須檢查的行數

該列包含 MySQL 解決查詢的詳細信息

1.? Distinct：MySQL 發現第 1 個匹配行后，停止為當前的行組合搜索更多的行

2.? Not exists：MySQL 能夠對查詢進行 LEFT JOIN 優化，發現 1 個匹配 LEFT JOIN 標準的行后，不再為前面的的行組合在該表內檢查更多的行

3.? range checked for each record (index map: #)：MySQL 沒有發現好的可以使用的索引，但發現如果來自前面的表的列值已知，可能部分索引可以使用

4.? Using filesort：MySQL 需要額外的一次傳遞，以找出如何按排序順序檢索行

5.? Using index：從只使用索引樹中的信息而不需要進一步搜索讀取實際的行來檢索表中的列信息

6.? Using temporary：為了解決查詢，MySQL 需要創建一個臨時表來容納結果

7.? Using where：WHERE 子句用于限制哪一個行匹配下一個表或發送到客戶

8.? Using sort_union(...)，Using union(...)，Using intersect(...)：這些函數說明如何為 index_merge 聯接類型合并索引掃描

9.? Using index for group-by：類似于訪問表的Using index 方式，Using index for group-by 表示MySQL發現了一個索引，可以用來查 詢 GROUP BY 或 DISTINCT 查詢的所有列，而不要額外搜索硬盤訪問實際的表

改進：用連接查詢來代替子查詢

exists 子查詢

優化 1: 在 group 時，用帶有索引的列來 group，速度會稍快一些，另外，用 int 型 比 char 型分組，也要快一些

優化 2：在 group 時，我們假設只取了 A 表的內容，group by 的列，盡量用 A 表的列，會比 B 表的列要快

優化 3：從語義上去優化

注意：內層 from 語句查到的臨時表，是沒有索引的

所以 from 的返回內容要盡量少

min/max 優化

在表中，一般都是經過優化的? 如下地區表

?

我們查 min(id)，id 是主鍵，查 Min(id)非常快

但是，pid 上沒有索引，現在要求查詢 3113 地區的 min(id);

select min(id) from it_area where pid=69;

試想 id 是有順序的，(默認索引是升續排列)，因此，如果我們沿著 id 的索引方向走，那么 第 1 個 pid=69 的索引結點，他的 id 就正好是最小的 id

select id from it_area use index(primary) where pid=69 limit 1;

| 12 | 0.00128100 | select min(id) from it_area where pid=69 |

| 13 | 0.00017000 | select id from it_area use index(primary) where pid=69 limit 1 |

改進后的速度雖然快，但語義已經非常不清晰，不建議這么做，僅僅是實驗目的

誤區：

1.? myisam 的 count() 非?？?/p>

答: 是比較快，但僅限于查詢表的 "所有行" 比較快，因為 Myisam 對行數進行了存儲

一旦有條件的查詢，速度就不再快了，尤其是 where 條件的列上沒有索引

2: 假如，id

select count(*) from lx_com where id>=100; (1000 多萬行用了 6.X 秒)

小技巧：

select count(*) from lx_com; 快

select count(*) from lx_com where id<100; 快

select count(*) frol lx_com -? select count(*) from lx_com where id<100; 快

select (select count(*) from lx_com) - (select count(*) from lx_com where id<100)

注意：

1.? 分組用于統計，而不用于篩選數據

比如：統計平均分，最高分，適合；但用于篩選重復數據，則不適合

以及用索引來避免臨時表和文件排序

2.? 以 A，B 表連接為例，主要查詢 A 表的列

那么 group by，order by 的列盡量相同，而且列應該顯示聲明為 A 的列

注意：union all 不過濾效率提高，如非必須，請用 union all

因為 union 去重的代價非常高，放在程序里去重

limit 及翻頁優化

limit offset，N，當 offset 非常大時，效率極低

原因是 mysql 并不是跳過 offset 行，然后單取 N 行，

而是取 offset+N 行，返回放棄前 offset 行，返回 N 行

效率較低，當 offset 越大時，效率越低

優化辦法：

1.? 從業務上去解決

辦法：不允許翻過 100 頁

以百度為例，一般翻頁到 70 頁左右

2.? 不用 offset，用條件查詢

3.? 非要物理刪除，還要用 offset 精確查詢，還不限制用戶分頁，怎么辦?

分析：優化思路是? 不查，少查，查索引，少取

我們現在必須要查，則只查索引，不查數據，得到 id

再用 id 去查具體條目? ?這種技巧就是延遲索引

1.? 用變量排名

例: 以 ecshop 中的商品表為例，計算每個欄目下的商品數，并按商品數排名

select cat_id,count(*) as cnt from goods group by cat_id order by cnt desc;

并按商品數計算這些欄目的名次

set @curr_cnt := 0,@prev_cnt := 0, @rank := 0;

select cat_id, (@curr_cnt := cnt) as cnt,

(@rank := if(@curr_cnt <> @prev_cnt,@rank+1,@rank)) as rank,

@prev_cnt := @curr_cnt

from ( select cat_id,count(*) as cnt from shop. goods group by shop. goods.cat_id order by cnt desc) as tmp;

2.? 用變量計算真正影響的行數

當插入多條，當主鍵重復時，則自動更新，這種效果，可以用 insert on duplication for update

要統計真正 ”新增” 的條目，如下圖，我們想得到的值是 ”1”，即被更新的行數

?

insert into user (uid,uname) values (4,?ds?),(5,'wanu'),(6,?safdsaf?)

on duplicate key update uid=values(uid)+(0*(@x:=@x+1)) , uname=values(uname);

mysql> set @x:=0;

Query OK, 0 rows affected (0.00 sec)

?

總影響行數-2*實際 update 數，即新增的行數

3.? 簡化 union

比如有新聞表，news，news_hot

new_hot 是一張內存表，非?？?#xff0c;用來今天的熱門新聞

首頁取新聞時，邏輯是這樣的：先取 hot，沒有 再取 news，為了省事，用一個 union 來完成

select nid,title from news_hot where nid=xxx

union

select nid,title from news where nid=xxx;

?

如何利用變量讓后半句 select 不執行

select id,content,(@find := 1) from news where id=1

union

select id,content,(@find :=1) from news2 where id=1 and (@find

union 1,1,1 where (@find :=null) is not null;

4.? 小心變量的順序

如下圖：變量先在 where 中發揮作用,然后再是 select 操作

如果 where 不成立，select 操作再不發生

?

Eg；

?

在這個例子中，1、2 兩行，先排好序，在內存中，就是這樣的順序 [2] [1]

再逐行 where 條件判斷，取值

?

?

?

對比這 2 張圖，分析：

1.? where 先發揮作用，把需要的行都給找出

2.? 然后再逐行 select

因此，前者，最終 select 時，select@num 變量，都是一個值

后者，不斷 select，不斷修改@num 的值，值不斷變化

同時：使用變量，將會使 sql 語句的結果不緩存

MySQL 從 4.1 版本開始支持子查詢，使用子查詢進行 SELECT 語句嵌套查詢，可以一次完成很多邏輯上需要多個步驟才能完成的 SQL 操作

子查詢雖然很靈活，但是執行效率并不高

執行子查詢時，MYSQL 需要創建臨時表，查詢完畢后再刪除這些臨時表，所以，子查詢的速度會受到一定的影響

優化：

可以使用連接查詢（JOIN）代替子查詢，連接查詢時不需要建立臨時表，其速度比子查詢快

Rbac-----兩種 SQL 語句

數據庫索引的本質是數據結構，這種數據結構能夠幫助我們快速的獲取數據庫中的數據

當表中的數據量越來越大時，索引對于性能的影響愈發重要

索引優化應該是對查詢性能優化最有效的手段

索引能夠輕易將查詢性能提高好幾個數量級

有了索引相當于我們給數據庫的數據加了目錄一樣，可以快速的找到數據，如果不適用索引則需要一點一點去查找數據

簡單來說提高數據查詢的效率

普通索引：index：加速查找

主鍵索引：primary key ：加速查找+約束（不為空且唯一）

唯一索引：unique：加速查找+約束 （唯一）

聯合索引（組合索引）

全文索引

-primary key(id,name)：聯合主鍵索引

-unique(id,name)：聯合唯一索引

-index(id,name)：聯合普通索引

全文索引 fulltext：用于搜索很長一篇文章的時候，效果最好

空間索引 spatial：了解就好，幾乎不用

1.? 可以通過建立唯一索引或者主鍵索引，保證數據庫表中每一行數據的唯一性

2.? 建立索引可以大大提高檢索的速度，以及減少表的檢索行數

3.? 在表連接的連接條件可以加速表與表直接的相連

4.? 在分組和排序字句進行數據檢索，可以減少查詢時間中分組和排序時所消耗的時間（數據庫的記錄會重新排序）

5.? 建立索引，在查詢中使用索引可以提高性能

1.? 在創建索引和維護索引會耗費時間，隨著數據量的增加而增加

2.? 索引文件會占用物理空間，除了數據表需要占用物理空間之外，每一個索引還會占用一定的物理空間

3.? 當對表的數據進行 INSERT，UPDATE，DELETE 的時候，索引也要動態的維護，這樣就會降低數據的維護速度（建立索引會占用磁盤空間的索引文件；一般情況這個問題不太嚴重，但如果你在一個大表上創建了多種組合索引，索引文件的會膨脹很快）

1.? 在經常需要搜索的列上，可以加快索引的速度

2.? 主鍵列上可以確保列的唯一性

3.? 在表與表的而連接條件上加上索引，可以加快連接查詢的速度

4.? 在經常需要排序（order by），分組（group by）和的 distinct 列上加索引可以加快排序查詢的時間

1.? 查詢中很少使用到的列不應該創建索引，如果建立了索引然而還會降低 mysql 的性能和增大了空間需求

2.? 很少數據的列也不應該建立索引，比如 一個性別字段 0 或者 1，在查詢中，結果集的數據占了表中數據行的比例比較大，mysql 需要掃描的行數很多，增加索引，并不能提高效率

3.? 定義為 text 和 image 和 bit 數據類型的列不應該增加索引

4.? 當表的修改（UPDATE，INSERT，DELETE）操作遠遠大于檢索（SELECT）操作時不應該創建索引，這兩個操作是互斥的關系

1.? 如果條件中有 or，即使其中有條件帶索引也不會使用（這也是為什么盡量少用 or 的原因）

2.? 索引字段的值不能有 null 值，有 null 值會使該列索引失效

3.? 對于多列索引，不是使用的第一部分，則不會使用索引（最左原則）

4.? like 查詢以 % 開頭

5.? 如果列類型是字符串，那一定要在條件中將數據使用單引號引用起來，否則不使用索引

6.? 在索引的列上使用表達式或者函數會使索引失效

例如：select * from users where YEAR(adddate) < 2007，將在每個行上進行運算，這將導致索引失效而進行全表掃描，因此我們可以改成：select * from users where adddate < '2007-01-01′

mysql>ALTER TABLE 表名 ADD INDEX 索引名 列名;

mysql>ALTER TABLE 表名 ADD UNIQUE 索引名 列名;

mysql>ALTER TABLE 表名 ADD PRIMARY KEY 索引名 列名;

mysql>CREATE INDEX 索引名 ON 表名 列名;

mysql>CREATE UNIQUE INDEX 索引名 ON 表名 列名;

DORP INDEX IndexName ON TableName

show index from tableName;

MySQL 的基本存儲結構是頁（記錄都存在頁里邊）：

各個數據頁可以組成一個雙向鏈表

每個數據頁中的記錄又可以組成一個單向鏈表

每個數據頁都會為存儲在它里邊兒的記錄生成一個頁目錄，在通過主鍵查找某條記錄的時候可以在頁目錄中使用二分法快速定位到對應的槽，然后再遍歷該槽對應分組中的記錄即可快速找到指定的記錄

以其他列（非主鍵）作為搜索條件：只能從最小記錄開始依次遍歷單鏈表中的每條記錄

所以說，如果我們寫 select * from user where indexname = 'xxx' 這樣沒有進行任何優化的 sql 語句，默認會這樣做：

1. 定位到記錄所在的頁：需要遍歷雙向鏈表，找到所在的頁

2. 從所在的頁內中查找相應的記錄：由于不是根據主鍵查詢，只能遍歷所在頁的單鏈表了

很明顯，在數據量很大的情況下這樣查找會很慢！這樣的時間復雜度為 O(n)

使用索引之后

索引做了些什么可以讓我們查詢加快速度呢？其實就是將無序的數據變成有序（相對）：

很明顯的是：沒有用索引我們是需要遍歷雙向鏈表來定位對應的頁，現在通過 "目錄"?就可以很快地定位到對應的頁上了！（二分查找，時間復雜度近似為 O(logn)）

其實底層結構就是 B+ 樹，B+ 樹作為樹的一種實現，能夠讓我們很快地查找出對應的記錄

索引被用來快速找出在一個列上用一特定值的行。沒有索引，MySQL 不得不首先以第一條記錄開始，然后讀完整個表直到它找出相關的行；表越大，花費時間越多；對于一個有序字段，可以運用二分查找（Binary Search），這就是為什么性能能得到本質上的提高

MYISAM 和 INNODB 都是用 B+Tree 作為索引結構

（主鍵，unique 都會默認的添加索引）

索引的實現本質上是為了讓數據庫能夠快速查找數據，而單獨維護的數據結構；mysql 實現索引主要使用的兩種數據結構：hash 和 B+ 樹： 我們比較常用的 MyIsam 和 innorDB 引擎都是基于 B+ 樹的

hash：（hash 索引在 mysql 比較少用）他以把數據的索引以 hash 形式組織起來，因此當查找某一條記錄的時候，速度非常快；當時因為是 hash 結構，每個鍵只對應一個值，而且是散列的方式分布；所以他并不支持范圍查找和排序等功能

B+樹：b+tree 是（mysql 使用最頻繁的一個索引數據結構）數據結構以平衡樹的形式來組織，因為是樹型結構，所以更適合用來處理排序，范圍查找等功能；相對 hash 索引，B+ 樹在查找單條記錄的速度雖然比不上 hash 索引，但是因為更適合排序等操作，所以他更受用戶的歡迎；畢竟不可能只對數據庫進行單條記錄的操作

大的方面看，都用的平衡樹，但具體的實現上，各引擎稍有不同

比如，嚴格的說，NDB 引擎使用的是 T-tree

Myisam，innodb 中，默認用 B-tree 索引

但抽象一下---B-tree 系統，可理解為 "排好序的快速查找結構"

BTree 是平衡搜索多叉樹，設樹的度為 2d（d>1），高度為 h，那么 BTree 要滿足以一下條件：

每個葉子結點的高度一樣，等于 h；

每個非葉子結點由 n-1 個 key 和 n 個指針 point 組成，其中 d

葉子結點指針都為 null；

非葉子結點的 key 都是 [key,data] 二元組，其中 key 表示作為索引的鍵，data 為鍵值所在行的數據

B+Tree 是 BTree 的一個變種，設 d 為樹的度數，h 為樹的高度，B+Tree 和 BTree 的不同主要在于：

B+Tree 中的非葉子結點不存儲數據，只存儲鍵值；

B+Tree 的葉子結點沒有指針，所有鍵值都會出現在葉子結點上，且 key 存儲的鍵值對應 data 數據的物理地址；

B+Tree 的每個非葉子節點由 n 個鍵值 key 和 n 個指針 point 組成；

在 memory 表里，默認是 hash 索引，hash 的理論查詢時間復雜度為 O(1) 疑問

既然 hash 的查找如此高效，為什么不都用 hash 索引?

答:

1.? ?hash 函數計算后的結果，是隨機的，如果是在磁盤上放置數據，比主鍵為 id 為例，那么隨著 id 的增長， id 對應的行，在磁盤上隨機放置

2.??無法對范圍查詢進行優化

3: 無法利用前綴索引；比如 在 btree 中，field 列的值 "hellopworld"，并加索引

查詢 xx=helloword，自然可以利用索引， xx=hello，也可以利用索引(左前綴索引)

因為 hash("helloword")，和 hash("hello")，兩者的關系仍為隨機

4.? 排序也無法優化

5.? 必須回行；就是說通過索引拿到數據位置，必須回到表中取數據

例：where cat_id=3 and price>100;? //查詢第 3 個欄目，100 元以上的商品

誤：cat_id 上和 price 上都加上索引

錯：只能用上 cat_id 或 Price 索引，因為是獨立的索引，同時只能用上 1 個

誤：多列索引上，索引發揮作用，需要滿足左前綴要求；以 index(a,b,c) 為例

?

多列索引經典題目：

有商品表，有主鍵，goods_id，欄目列 cat_id，價格 price

說：在價格列上已經加了索引，但按價格查詢還是很慢，問可能是什么原因，怎么解決?

答：在實際場景中，一個電商網站的商品分類很多，直接在所有商品中，按價格查商品，是極少的，一般客戶都來到分類下，然后再查

改正：去掉單獨的 Price 列的索引，加 (cat_id,price) 復合索引再查詢

1.? 選擇唯一性索引

2.? 唯一性索引的值是唯一的，可以更快速的通過該索引來確定某條記錄

3.? 為經常需要排序、分組和聯合操作的字段建立索引

4.? 為常作為查詢條件的字段建立索引

5.? 限制索引的數目： 越多的索引，會使更新表變得很浪費時間

6.? 盡量使用數據量少的索引

7.? 如果索引的值很長，那么查詢的速度會受到影響

8.? 盡量使用前綴來索引

9.? 如果索引字段的值很長，最好使用值的前綴來索引

10.? 刪除不再使用或者很少使用的索引

11.? 最左前綴匹配原則，非常重要的原則

12.? 盡量選擇區分度高的列作為索引

13.? 區分度的公式是表示字段不重復的比例

14.? 索引列不能參與計算，保持列 "干凈"：帶函數的查詢不參與索引

15.? 盡量的擴展索引，不要新建索引

MySQL 中的索引可以以一定順序引用多列，這種索引叫作聯合索引；如 User 表的 name 和 city 加聯合索引就是(name,city)，而最左前綴原則指的是，如果查詢的時候查詢條件精確匹配索引的左邊連續一列或幾列，則此列就可以被用到；如下：

這里需要注意的是，查詢的時候如果兩個條件都用上了，但是順序不同，如 city= xx and name ＝xx，那么現在的查詢引擎會自動優化為匹配聯合索引的順序，這樣是能夠命中索引的

由于最左前綴原則，在創建聯合索引時，索引字段的順序需要考慮字段值去重之后的個數，較多的放前面ORDER BY 子句也遵循此規則

冗余索引指的是索引的功能相同，能夠命中就肯定能命中 ，那么就是冗余索引如（name,city ）和（name ）這兩個索引就是冗余索引，能夠命中后者的查詢肯定是能夠命中前者的，在大多數情況下，都應該盡量擴展已有的索引而不是創建新索引

MySQLS.7 版本后，可以通過查詢 sys 庫的 schema_redundant_indexes 表來查看冗余索引

索引可以提供查詢的速度，但并不是使用了帶有索引的字段查詢都會生效，有些情況下是不生效的，需要注意

在使用 LIKE 關鍵字進行查詢的查詢語句中，如果匹配字符串的第一個字符為 "%"，索引不起作用

只有 "%" 不在第一個位置，索引才會生效

?

?

MySQL 可以為多個字段創建索引，一個索引可以包括 16 個字段

對于聯合索引，只有查詢條件中使用了這些字段中第一個字段時，索引才會生效

?

?

?

查詢語句的查詢條件中只有 OR 關鍵字，且 OR 前后的兩個條件中的列都是索引時，索引才會生效，否則，索引不生效

?

?

?

Myisam 與 innodb 引擎，索引文件的異同

innodb 的主索引文件上，直接存放該行數據，稱為聚簇索引，次索引指向對主鍵的引用

myisam 中，主索引和次索引，都指向物理行(磁盤位置)

注意：innodb 來說

1.? 主鍵索引既存儲索引值，又在葉子中存儲行的數據

2.? 如果沒有主鍵，則會 Unique key 做主鍵

3.? 如果沒有 unique，則系統生成一個內部的 rowid 做主鍵

4.? 像 innodb 中，主鍵的索引結構中，既存儲了主鍵值，又存儲了行數據，這種結構稱為 "聚簇索引"

聚簇索引

優勢：根據主鍵查詢條目比較少時，不用回行（數據就在主鍵節點下）

劣勢：如果碰到不規則數據插入時，造成頻繁的頁分裂

對于 innodb 而言，因為節點下有數據文件，因此節點的分裂將會比較慢

對于 innodb 的主鍵，盡量用整型，而且是遞增的整型

如果是無規律的數據，將會產生的頁的分裂，影響速度

索引覆蓋是指：如果查詢的列恰好是索引的一部分，那么查詢只需要在索引文件上進行，不需要回行到磁盤再找數據

這種查詢速度非常快，稱為 "索引覆蓋"

1.? 查詢頻繁

2.? 區分度高

3.? 長度小

4.? 盡量能覆蓋常用查詢字段

1.? 索引長度直接影響索引文件的大小，影響增刪改的速度，并間接影響查詢速度（占用內存多）

針對列中的值，從左往右截取部分，來建索引

1: 截的越短，重復度越高，區分度越小，索引效果越不好

2: 截的越長，重復度越低，區分度越高，索引效果越好，但帶來的影響也越大--增刪改變慢，并間影響查詢速度

所以，我們要在 區分度 + 長度 兩者上，取得一個平衡

對于一般的系統應用：區別度能達到 0.1，索引的性能就可以接受

如 url 列

列的前 11 個字符都是一樣的，不易區分，可以用如下 2 個辦法來解決

1.? 把列內容倒過來存儲，并建立索引

這樣左前綴區分度大

2.? 偽 hash 索引效果

同時存 url_hash 列

多列索引的考慮因素 --- 列的查詢頻率，列的區分度

排序可能發生 2 種情況：

1.? 對于覆蓋索引，直接在索引上查詢時，就是有順序的，using index

2.? 先取出數據，形成臨時表做 filesort（文件排序，但文件可能在磁盤上，也可能在內存中）

我們的爭取目標-----取出來的數據本身就是有序的；利用索引來排序

重復索引：

是指在同 1 個列（如 age），或者順序相同的幾個列（age,school），建立了多個索引，稱為重復索引，重復索引沒有任何幫助，只會增大索引文件，拖慢更新速度，需要去掉

冗余索引：

冗余索引是指 2 個索引所覆蓋的列有重疊，稱為冗余索引

比如 x，m列，加索引 index x(x)，index xm(x,m)

x，xm 索引，兩者的 x 列重疊了，這種情況，稱為冗余索引

甚至可以把 index mx(m,x) 索引也建立，mx，xm 也不是重復的，因為列的順序不一樣

在長期的數據更改過程中，索引文件和數據文件，都將產生空洞，形成碎片

我們可以通過一個 nop 操作(不產生對數據實質影響的操作)，來修改表

比如：表的引擎為 innodb，可以 alter table xxx engine innodb

optimize table 表名，也可以修復

注意：修復表的數據及索引碎片，就會把所有的數據文件重新整理一遍，使之對齊

這個過程，如果表的行數比較大，也是非常耗費資源的操作

所以，不能頻繁的修復

如果表的 Update 操作很頻率，可以按周/月，來修復

如果不頻繁，可以更長的周期來做修復

建議單張表索引不超過 5 個

索引并不是越多越好

索引可以提高效率同樣可以降低效率

索引可以增加查詢效率，但同樣也會降低插入和更新的效率，甚至有些情況下會降低查詢效率

因為 MySQL 優化器在選擇如何優化查詢時，會根據統一信息，對每一個可以用到的索引來進行評估，以生成出一個最好的執行計劃，如果同時有很多個索引都可以用于查詢，就會增加 MySQL 優化器生成執行計劃的時間，同樣會降低查詢性能

5.6 版本之前，一個 sql 只能使用到一個表中的一個索引，5.6 以后，雖然有了合并索引的優化方式，但是還是遠遠沒有使用一個聯合索引的查詢方式好

Innodb 是一種索引組織表：數據的存儲的邏輯順序和索引的順序是相同的

每個表都可以有多個索引，但是表的存儲順序只能有一種

Innodb 是按照主鍵索引的順序來組織表的

1.? 不要使用更新頻繁的列作為主鍵，不適用多列主鍵（相當于聯合索引）

2.? ? 不要使用 UUID，MD5，HASH，字符串列作為主鍵（無法保證數據的順序增長）

3.? ?主鍵建議使用自增 ID 值

1. 出現在 SELECT、UPDATE、DELETE 語句的 WHERE 從句中的列

2. 包含在 ORDER BY、GROUP BY、DISTINCT 中的字段

3. 并不要將符合 1 和 2 中的字段的列都建立一個索引， 通常將 1、2 中的字段建立聯合索引效果更好

4. 多表 join 的關聯列

建立索引的目的是：希望通過索引進行數據查找，減少隨機 IO，增加查詢性能 ，索引能過濾出越少的數據，則從磁盤中讀入的數據也就越少

區分度最高的放在聯合索引的最左側（區分度=列中不同值的數量/列的總行數）

盡量把字段長度小的列放在聯合索引的最左側（因為字段長度越小，一頁能存儲的數據量越大，IO 性能也就越好）

使用最頻繁的列放到聯合索引的左側（這樣可以比較少的建立一些索引）

重復索引示例：primary key(id)、index(id)、unique index(id)

冗余索引示例：index(a,b,c)、index(a,b)、index(a)

覆蓋索引：就是包含了所有查詢字段 (where,select,ordery by,group by 包含的字段) 的索引覆蓋索引的好處：

避免 Innodb 表進行索引的二次查詢: Innodb 是以聚集索引的順序來存儲的，對于 Innodb 來說，二級索引在葉子節點中所保存的是行的主鍵信息，如果是用二級索引查詢數據的話，在查找到相應的鍵值后，還要通過主鍵進行二次查詢才能獲取我們真實所需要的數據。而在覆蓋索引中，二級索引的鍵值中可以獲取所有的數據，避免了對主鍵的二次查詢 ，減少了 IO 操作，提升了查詢效率

可以把隨機 IO 變成順序 IO 加快查詢效率: 由于覆蓋索引是按鍵值的順序存儲的，對于 IO 密集型的范圍查找來說，對比隨機從磁盤讀取每一行的數據 IO 要少的多，因此利用覆蓋索引在訪問時也可以把磁盤的隨機讀取的 IO 轉變成索引查找的順序 IO

盡量避免使用外鍵約束

不建議使用外鍵約束（foreign key），但一定要在表與表之間的關聯鍵上建立索引

外鍵可用于保證數據的參照完整性，但建議在業務端實現

外鍵會影響父表和子表的寫操作從而降低性能

主鍵

外鍵

索引

定義

唯一標識一條記錄，不能有重復的，不允許為空

表的外鍵是另一表的主鍵，外鍵可以有重復的，可以是空值

該字段沒有重復值，但可以有一個空值

作用

用來保證數據完整性

用來和其他表建立聯系用的

是提高查詢排序的速度

個數

主鍵只能有一個

一個表可以有多個外鍵

一個表可以有多個唯一索引

程度從強到弱

1. drop table tb

drop將表格直接刪除，沒有辦法找回

2. truncate (table) tb

刪除表中的所有數據，不能與where一起使用

3. delete from tb (where)

刪除表中的數據(可制定某一行)

區別：truncate 和 delete 的區別

1. 事務：truncate 是不可以 rollback 的，但是 delete 是可以 rollback 的

原因：truncate 刪除整表數據（ddl語句,隱式提交），delete 是一行一行的刪除，可以 rollback

2. 效果：truncate 刪除后將重新水平線和索引（id從零開始），delete 不會刪除索引

3. truncate 不能觸發任何? Delete 觸發器

4. delete 刪除可以返回行數

插入數據時，影響插入速度的主要是索引、唯一性校驗、一次插入的數據條數等

插入數據的優化，不同的存儲引擎優化手段不一樣，在 MySQL 中常用的存儲引擎有：MyISAM 和 InnoDB，兩者的區別：

?

對于非空表，插入記錄時，MySQL 會根據表的索引對插入的記錄建立索引

如果插入大量數據，建立索引會降低插入數據速度

為了解決這個問題，可以在批量插入數據之前禁用索引，數據插入完成后再開啟索引

禁用索引的語句：

ALTER TABLE table_name DISABLE KEYS

開啟索引語句：

ALTER TABLE table_name ENABLE KEYS

對于空表批量插入數據，則不需要進行操作，因為 MyISAM 引擎的表是在導入數據后才建立索引

唯一性校驗會降低插入記錄的速度，可以在插入記錄之前禁用唯一性檢查，插入數據完成后再開啟

禁用唯一性檢查的語句：SET UNIQUE_CHECKS = 0;

開啟唯一性檢查的語句：SET UNIQUE_CHECKS = 1;

插入數據時，可以使用一條 INSERT 語句插入一條數據，也可以插入多條數據

?

?

第二種方式的插入速度比第一種方式快

當需要批量導入數據時，使用 LOAD DATA INFILE 語句比 INSERT 語句插入速度快很多

用法和 MyISAM 一樣

插入數據之前執行禁止對外鍵的檢查，數據插入完成后再恢復，可以提供插入速度

禁用：SET foreign_key_checks = 0;

開啟：SET foreign_key_checks = 1;

插入數據之前執行禁止事務的自動提交，數據插入完成后再恢復，可以提高插入速度

禁用：SET autocommit = 0;

開啟：SET autocommit = 1;

服務器的硬件性能直接決定著 MySQL 數據庫的性能，硬件的性能瓶頸，直接決定 MySQL 數據庫的運行速度和效率

需要從以下幾個方面考慮：

1. 配置較大的內存

足夠大的內存，是提高 MySQL 數據庫性能的方法之一；內存的 IO 比硬盤快的多，可以增加系統的緩沖區容量，使數據在內存停留的時間更長，以減少磁盤的 IO

2. 配置高速磁盤

比如 SSD

3. 合理分配磁盤 IO

把磁盤 IO 分散到多個設備上，以減少資源的競爭，提高并行操作能力

4. 配置多核處理器

MySQL 是多線程的數據庫，多處理器可以提高同時執行多個線程的能力

通過優化 MySQL 的參數可以提高資源利用率，從而達到提高 MySQL 服務器性能的目的

MySQL 的配置參數都在 my.conf 或者 my.ini 文件的[mysqld]組中，常用的參數如下：

?

?

?

要求：必須記憶至少 3 個

高性能不是指 "絕對性能" 強悍，而是指業務能發揮出硬件的最大水平。性能強的服務器并非 "設計" 而來，而是不斷改進，提升短板，測試，就是量化找出短板的過程

只有會測試，能把數據量化，才能進一步改進優化

1：吞吐量：單位時間內的事務處理數，單位 tps(每秒事務數)

2：響應時間：語句平均響應時間，一般截取某段時間內，95% 范圍內的平均時間

3：并發性：線程同時執行

4：可擴展性：資源增加，性能也能正比增加

1.開啟 mysql 慢日志查詢 定位查詢較慢的 sql 語句 （200ms 500ms ）

2.使用 EXPLAIN 關鍵字可以讓你知道 MySQL 是如何處理你的 SQL 語句的。這可以幫你分析你的查詢語句或是表結構的性能瓶頸。EXPLAIN 的查詢結果還會告訴你你的索引主鍵被如何利用的，你的數據表是如何被搜索和排序的……等等，等等

3.代碼中 可以使用 AOP 的操作 對每個持久層的 service 方法打印執行時間，將所有執行時間較長的 sql 語句進行預警

?

測試 CPU 性能

測試 IO 性能

測試事務性能

mysql -h 192.168.177.128 -u root -e 'show processlist\G'|grep State:|sort|uniq -c|sort -rn

5 State: Sending data

2 State: statistics

2 State: NULL

1 State: Updating

1 State: update

以下幾種狀態要注意:

converting HEAP to MyISAM 查詢結果太大時，把結果放在磁盤

create tmp table 創建臨時表(如 group 時儲存中間結果)

Copying to tmp table on disk 把內存臨時表復制到磁盤

locked 被其他查詢鎖住

logging slow query 記錄慢查詢