1. MySQL的索引

1.1 概述

索引是通過某種算法，構(gòu)建出一個數(shù)據(jù)模型，用于快速找出在某個列中有以特定值的行，不使用索引，MySQL必須從一條記錄開始讀完整個表，直到找出相關(guān)的行，表越大查詢數(shù)據(jù)所花的時間越多，如果表中查詢的列有一個索引，MySQL能夠快速達(dá)到一個位置去搜索數(shù)據(jù)文件，而不必查看所有數(shù)據(jù)，那么將會節(jié)省很大一部分時間。

1.2 分類

索引是存儲引擎用來快速查找記錄的一種數(shù)據(jù)結(jié)構(gòu)：

• 按照實(shí)現(xiàn)的方式類分，主要有Hash索引和B+Tree索引。
• 按照功能劃分，單列索引（普通索引、唯一索引、主鍵索引），組合索引，全文索引，空間索引

1.3 特點(diǎn)

優(yōu)點(diǎn)

• 大大加快數(shù)據(jù)查詢的速度；
• 使用分組和排序進(jìn)行數(shù)據(jù)查詢時，可以顯著減少查詢時分組和排序的時間
• 創(chuàng)建唯一索引，能夠保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性
• 在實(shí)現(xiàn)數(shù)據(jù)的參考完整性方面，可以加速表和表之間的連接。

缺點(diǎn)

• 創(chuàng)建索引和維護(hù)索引需要消耗時間，并且隨著數(shù)據(jù)量的增加，時間也會增加
• 索引需要占據(jù)磁盤空間
• 對數(shù)據(jù)表中的數(shù)據(jù)進(jìn)行增加、修改、刪除時，索引也要動態(tài)的維護(hù)，降低了維護(hù)的速度

創(chuàng)建索引的原則

• 更新頻繁的列不應(yīng)該設(shè)置為索引
• 數(shù)據(jù)量小的表不要使用索引
• 重復(fù)數(shù)據(jù)多的字段不應(yīng)設(shè)置為索引（一般來說，重復(fù)的數(shù)量超過15%就不該建索引）
• 首先應(yīng)考慮對WHERE和ORDER BY涉及的列上建立索引

2. 存儲引擎——MySQL的核心

存儲引擎就是存儲數(shù)據(jù)、建立索引、更新、查詢數(shù)據(jù)等技術(shù)的實(shí)現(xiàn)方式。存儲引擎是基于表的，而不是基于庫的，所以存儲引擎也可以被稱為表類型。

2.1 分類

• MyISAM：MySQL5.5之前的默認(rèn)數(shù)據(jù)庫引擎，最為常用。擁有較高的插入，查詢速度，但不支持事務(wù)。
• InnoDB：事務(wù)型速記的首選引擎，支持ACID事務(wù)，支持行級鎖定，MySQL5.5成為默認(rèn)數(shù)據(jù)庫引擎。
• Memory：所有數(shù)據(jù)置于內(nèi)存的存儲引擎，擁有極高的插入，更新和查詢效率。但是會占用和數(shù)據(jù)量成正比的內(nèi)存空間，并且其內(nèi)容會在MySQL重新啟動會丟失。
• Archive：非常適合存儲大量的獨(dú)立的、作為歷史紀(jì)錄的數(shù)據(jù)。因?yàn)樗鼈儾唤?jīng)常被讀取。Archive擁有高效的插入速度，但其對查詢的支持相對較差。
• Federated：將不同的MySQL服務(wù)器聯(lián)合起來，邏輯上組成一個完整的數(shù)據(jù)庫。非常適合分布式應(yīng)用。
• CSV：邏輯上由都好分割數(shù)據(jù)的存儲引擎。他會在數(shù)據(jù)庫子目錄里為每個數(shù)據(jù)表創(chuàng)建一個.csv文件。這是一種普通文本文件，每個數(shù)據(jù)行占用一個文本行。CSV存儲引擎不支持索引。
• BlockHole：黑洞引擎，寫入的任何數(shù)據(jù)都會消失，一般用于記錄binlog做賦值的中繼。
• ERFORMANCE_SCHEMA：該存儲引擎主要用于收集數(shù)據(jù)庫服務(wù)器性能參數(shù)。

3. MyISAM和InnoDB

MyISAM和InnoDB都是MySQL數(shù)據(jù)庫中常見的存儲引擎，各自特點(diǎn)和使用場景如下：

MyISAM

特點(diǎn)

• 基于表格的存儲引擎，對每個表對應(yīng)三個文件，.frm文件存儲表結(jié)構(gòu)定義，.MYD文件存儲數(shù)據(jù)，MYI文件存儲索引。
• 不支持事務(wù)，也不支持外鍵。
• 支持全文索引（Full-Text-Indexing），適合于搜索場景
• 讀操作比寫操作效率高，適用于讀多寫少的場景。

使用場景

• 非事務(wù)性的應(yīng)用，例如博客、新聞網(wǎng)站等；
• 對數(shù)據(jù)的讀操作頻繁，寫操作相對較少的場景；
• 對全文搜索需求較多的場景；

InnoDB

MySQL默認(rèn)的事務(wù)型引擎。

特點(diǎn)

• 提供了對事務(wù)的支持（ACID兼容），具有提交、回滾和崩潰恢復(fù)的能力；
• 支持行級鎖定，提高了并發(fā)性能；
• 支持外鍵約束。
• 采用聚合索引（Clustered Indexing），數(shù)據(jù)按照主鍵的順序存儲，可以提高查詢性能。

使用場景

• 需要事務(wù)支持的應(yīng)用，例如電子商務(wù)網(wǎng)站、金融系統(tǒng)等。
• 需要較高并發(fā)性能的應(yīng)用，例如論壇、社交網(wǎng)絡(luò)等。
• 需要支持外鍵約束的應(yīng)用。

在存儲引擎時，需要根據(jù)應(yīng)用的具體需求來決定使用哪種引擎。如果是簡單的應(yīng)用，只需要基本的增刪改查功能，可以選擇MyISAM。

如果需要支持事務(wù)、并發(fā)性能較高或者有復(fù)雜的數(shù)據(jù)關(guān)系，建議選擇InnoDB。

4. 事務(wù)

4.1 特性(ACID)

• 原子性(Atomicity)：事務(wù)開始后所有操作，要么全部完成，要么全部不做，不可能停止在中間環(huán)節(jié)。事務(wù)執(zhí)行過程中出錯，會回滾到事務(wù)開始前的狀態(tài)。即事務(wù)是一個整體。
• 一致性(Consistecy)：事務(wù)開始前和結(jié)束后，數(shù)據(jù)庫的完整性約束沒有被破壞。
• 隔離性(Isolation)：同一時間，只允許一個事務(wù)請求同一數(shù)據(jù)，不同的事務(wù)之間彼此沒有任何干擾。
• 持久性(Durability)：事務(wù)完成后，事務(wù)對數(shù)據(jù)的所有更新將被保存到數(shù)據(jù)庫，不能回滾。

4.2 事務(wù)靠什么保證

• 原子性：由undo log日志保證，它記錄了需要回滾的日志信息，回滾時撤銷一致性的SQL；
• 一致性：由其他三大特性共同保證，是事務(wù)的目的；
• 隔離性：由MVCC保證。
• 持久性：由redo log日志和內(nèi)存保證，MYSQL修改數(shù)據(jù)時內(nèi)存和redo log會記錄操作，宕機(jī)時可恢復(fù)。

4.3 undo log和redo log的區(qū)別

緩沖池（buffer pool）：主內(nèi)存中的一個區(qū)域，里面可以緩存磁盤上經(jīng)常操作的真實(shí)數(shù)據(jù)，在執(zhí)行增刪改操作時，先操作緩沖池中的數(shù)據(jù)（若緩沖池沒有數(shù)據(jù)，則從主磁盤加載并緩存），以一定頻率刷新到磁盤，從而減少磁盤IO，加快處理速度。

數(shù)據(jù)頁（page）：是InnoDB存儲引擎磁盤管理的最小單元，每個頁的大小默認(rèn)為16KB，液中存儲的是行數(shù)據(jù)。

redo log：

• 重做日志，記錄的是事務(wù)提交時數(shù)據(jù)頁的物理修改，是用來實(shí)現(xiàn)事務(wù)的持久性。
• 該日志文件由兩部分組成：重做日志緩沖（redo log buffer）以及重做日志文件（redo log file），前者是在內(nèi)存中，后者在磁盤中。當(dāng)事務(wù)提交之后會把所有修改信息都存到該日志文件中，用于在刷新臟頁到磁盤，發(fā)生錯誤時，進(jìn)行數(shù)據(jù)恢復(fù)使用。

undo log：

• 回滾日志，用于記錄數(shù)據(jù)被修改前的信息，作用包含兩個：提供回滾和MVCC（多版本并發(fā)控制）。undo log和redo log記錄物理日志不一樣，它是邏輯日志。
• 可以認(rèn)為當(dāng)delete一條記錄時，undo log中會記錄一條對應(yīng)的Insert記錄，反之亦然
• 當(dāng)update一條記錄時，他記錄一條對應(yīng)相反的update的記錄。當(dāng)執(zhí)行rollback時，就可以從undo log中的邏輯記錄讀取到相應(yīng)的內(nèi)容并進(jìn)行回滾。
• undo log可以實(shí)現(xiàn)事物的一致性和原子性
  事物的隔離性是如何保證的

鎖：排他鎖（如一個事務(wù)獲取了一個數(shù)據(jù)行的排他鎖，其他事務(wù)就不能再獲取該行的其他鎖）

MVCC：多版本并發(fā)控制

4.4 事務(wù)的隔離級別

高并發(fā)情況下，并發(fā)事務(wù)會產(chǎn)生臟讀、不可讀重復(fù)、幻讀等問題，這時需要用隔離級別來控制。

• 讀未提交：允許一個事務(wù)讀取另一個事務(wù)沒有提交的數(shù)據(jù)，而未提交的事務(wù)可能發(fā)生回滾，出現(xiàn)臟讀，不可重復(fù)度、幻讀。
• 讀提交：只允許事務(wù)讀取另一個事務(wù)已提交的數(shù)據(jù)可能會出現(xiàn)不可重復(fù)度，幻讀。
• 可重復(fù)讀：確保同一字段多次讀取結(jié)果一致，可能出現(xiàn)幻讀。
• 可串行化：所有事務(wù)逐次執(zhí)行，沒有并發(fā)問題。

InnoDB默認(rèn)隔離級別為可重復(fù)讀級別，分為快照讀和當(dāng)前讀，并且通過間隙鎖解決了幻讀問題。

4.5 事務(wù)的并發(fā)問題

臟讀：事務(wù)A讀取了事務(wù)B更新的數(shù)據(jù)，然后事務(wù)B回滾操作，那么事務(wù)A讀取到的數(shù)據(jù)是臟數(shù)據(jù)。

不可重復(fù)讀：事務(wù)A多次讀取同一數(shù)據(jù)，事務(wù)B在事務(wù)A多次讀取的過程中，對數(shù)據(jù)做了更新并提交，導(dǎo)致事務(wù)A多次讀取同一數(shù)據(jù)時，結(jié)果不一致。

幻讀：系統(tǒng)管理員A將數(shù)據(jù)庫中所有學(xué)生的成績從具體分?jǐn)?shù)改為ABCDE等級，但是系統(tǒng)管理員B就在這時插入了一條具體分?jǐn)?shù)的記錄，當(dāng)系統(tǒng)管理員A改結(jié)束后發(fā)現(xiàn)還有一條記錄沒有改過來，就好像發(fā)生了幻覺一樣，就成為幻讀。

如何解決臟讀、幻讀、不可重復(fù)讀

• 臟讀：隔離級別設(shè)置為讀提交、可重復(fù)讀、串行化可解決臟讀
• 不可重復(fù)讀：隔離級別設(shè)置為可重復(fù)讀、串行化可以解決不可重復(fù)讀
• 幻讀：隔離級別為串行化可以解決幻讀、通過MVCV + 區(qū)間鎖可以解決幻讀

4.6 快照讀和當(dāng)前讀

• 快照讀：讀取的是當(dāng)前數(shù)據(jù)的可見版本，可能是會過期數(shù)據(jù)，不加鎖的SELECT就是快照讀。
• 當(dāng)前讀：讀取的是數(shù)據(jù)的最新版本，并且當(dāng)前讀返回的記錄都會上鎖，保證其他事務(wù)不會并發(fā)修改這條記錄。如UPDATE，DELETE，INSERT，SELECT FOR UNDATE（排他鎖），SELECT LOCKIN SHARE MODE（共享鎖）都是當(dāng)前讀。

4.7 MVCC

MVCC是多版本并發(fā)控制，為每次事務(wù)生成一個新版本數(shù)據(jù)，每個事務(wù)都有自己的版本，從而不加鎖就解決讀寫沖突，這種讀為快照讀。只在讀已提交和可重復(fù)讀中生效。

實(shí)現(xiàn)的原理由以下四項(xiàng)保證：

• undo log日志：記錄數(shù)據(jù)歷史版本
• readView：事務(wù)進(jìn)行快照度時動態(tài)生成產(chǎn)生的視圖，記錄了當(dāng)前系統(tǒng)中活躍的事務(wù)id，控制哪個歷史版本對當(dāng)前事務(wù)可見。
• 隱藏字段DB_TRC_ID：最近修改記錄的事務(wù)ID
• 隱藏字段DB_Roll_PTR:回滾指針，配合undolog指向數(shù)據(jù)的上一個版本

5. 鎖機(jī)制

5.1 定義

鎖是計(jì)算機(jī)協(xié)調(diào)多個進(jìn)程或線程并發(fā)訪問某一資源的機(jī)制（避免競爭）。在數(shù)據(jù)庫中，除傳統(tǒng)的計(jì)算資源（如CPU，RAM，I/O等）的爭用外，數(shù)據(jù)也是一種供許多用戶共享的資源。

鎖機(jī)制能保證數(shù)據(jù)并發(fā)訪問的一致性、有效性，同時也影響數(shù)據(jù)庫并發(fā)訪問性能。

5.2 分類

根據(jù)對數(shù)據(jù)操作的粒度

• 表鎖：操作時，會鎖定整個表
• 行鎖：操作時，會鎖定當(dāng)前操作行
• 表級鎖更適合于以查詢?yōu)橹?，只有少量按索引條件更新數(shù)據(jù)的應(yīng)用，如Web應(yīng)用。
• 行級鎖更適合于有大量按索引條件并發(fā)更新少量不同數(shù)據(jù)，同時又有并發(fā)查詢的應(yīng)用，如一些在線事務(wù)處理（OLTP）系統(tǒng)。

根據(jù)對數(shù)據(jù)操作的類型

• 讀鎖（共享鎖）：針對同一份數(shù)據(jù)，多個讀操作可以同時進(jìn)行而不會互相影響
• 寫鎖（排它鎖）：當(dāng)前操作沒有完成之前前，會阻斷其他寫鎖和讀鎖。

6. 日志

分類：錯誤日志、二進(jìn)制日志、查詢?nèi)罩尽⒙樵內(nèi)罩?/p>

6.1 錯誤日志

• 錯誤日志是MySQl中最重要的日志之一，記錄了當(dāng)MySQL啟動個停止時，以及服務(wù)器在運(yùn)行過程中發(fā)生任何嚴(yán)重錯誤時的相關(guān)信息，當(dāng)數(shù)據(jù)庫出現(xiàn)任何故障導(dǎo)致無法正常使用時，可以首先查看錯誤日志。
• 該日志默認(rèn)是開啟的，默認(rèn)存放目錄是mysql的數(shù)據(jù)目錄，默認(rèn)的日志文件名為hosyname.err(hostname為主機(jī)名)。
• 查看日志位置指令

SHOW variables LIKE 'log_error%'

6.2 二進(jìn)制日志

二進(jìn)制日志（BINLOG）記錄了所有的DDL語句和DML語句，但不包括DQL語句。此日志對災(zāi)難時的數(shù)據(jù)恢復(fù)起著極其重要的作用，MySQL的主從復(fù)制就是通過改BINLOG實(shí)現(xiàn)的。

二進(jìn)制日志在MySQL8默認(rèn)開啟，低版本需要通過配置文件開啟，并配置MySQL日志的格式。

Windows系統(tǒng)：my.ini Linux系統(tǒng)： my.cnf

6.3 查詢?nèi)罩?/h3>

查詢?nèi)罩局杏涗浟丝蛻舳说乃胁僮髡Z句，而二進(jìn)制日志不包含查詢數(shù)據(jù)的SQL語句。

默認(rèn)情況下，查詢?nèi)罩臼俏撮_啟的。

6.4 慢查詢?nèi)罩?/h3>

慢查詢?nèi)罩居涗浟怂袌?zhí)行時間超過參數(shù)long_query_time設(shè)置值并且掃描記錄數(shù)不小于min_examined_row_limit的所有SQL語句的日志。long_query_time默認(rèn)為10秒，最小為0，精度可以到微秒。

7. 索引

7.1 定義

索引（index）是幫助MySQL高效的獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)（有序）。在數(shù)據(jù)之外，數(shù)據(jù)庫系統(tǒng)還維護(hù)著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)（B+樹），這些數(shù)據(jù)結(jié)構(gòu)以某種方式指向數(shù)據(jù)，這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實(shí)現(xiàn)高級查找算法，這種數(shù)據(jù)結(jié)構(gòu)就是索引。這樣可以提高數(shù)據(jù)檢索的效率，降低數(shù)據(jù)庫的IO成本（不需要全表掃描）。通過索引列對數(shù)據(jù)進(jìn)行排序，降低數(shù)據(jù)排序的成本，降低了CPU的消耗。

B樹與B+樹對比

• 階數(shù)更多，路徑更短
• 磁盤讀寫代價B+樹更低，非葉子節(jié)點(diǎn)只存儲指針，葉子階段存儲數(shù)據(jù)
• 查詢效率B+樹更加穩(wěn)定
• B+樹便于掃庫和區(qū)間查詢，非葉子節(jié)點(diǎn)是一個雙向鏈表

7.2 聚簇索引和非聚簇索引

7.2.1 聚簇索引

• 含義：將數(shù)據(jù)存儲與索引放到了一起，索引結(jié)構(gòu)的葉子節(jié)點(diǎn)保存了行數(shù)據(jù)
• 特點(diǎn)：必須有，而且只有一個

7.2.2 二級索引（非聚簇索引）

• 含義：將數(shù)據(jù)與索引分開存儲，索引結(jié)構(gòu)的葉子節(jié)點(diǎn)關(guān)聯(lián)的是對應(yīng)的主鍵
• 特點(diǎn)：可以存在多個

7.2.3 聚簇索引選取規(guī)則

• 如果存在主鍵，主鍵索引就是聚簇索引
• 如果不存在主鍵，將使用第一個唯一（UNIQUE）索引作為聚簇索引。
• 如果表沒有主鍵，或沒有適合的唯一索引，則InnoDB會自動生成一個rowid作為隱藏的聚簇索引。

7.2.4 回表查詢

（首先介紹聚簇索引和非聚簇索引，然后介紹回表查詢）

通過二級索引找到對應(yīng)的主鍵值，到聚簇索引中查找整行數(shù)據(jù)，這個過程就是回表。

7.3 覆蓋索引

覆蓋索引是指查詢使用了索引，并且需要返回的列在該索引中已經(jīng)全部能夠找到。

• 使用id查詢，直接走聚簇索引，一次索引掃描，直接返回數(shù)據(jù)，性能高；
• 如果返回的列中沒有創(chuàng)建索引，有可能會觸發(fā)回表查詢，盡量避免使用select *；

（可以使用索引覆蓋解決MySQL超大分頁）

7.4 MySQL超大分頁處理

• 在數(shù)據(jù)量比較大時，如果進(jìn)行l(wèi)imit分頁查詢，在查詢時，越往后分頁查詢的效率越低。
• 優(yōu)化思路：一般分頁查詢時，通過創(chuàng)建覆蓋索引能夠比較好的提高性能，可以通過覆蓋索引加子查詢形式進(jìn)行優(yōu)化。

7.5 創(chuàng)建索引的原則

• 針對數(shù)據(jù)量較大，且查詢比較頻繁的表建立索引（單表超過10萬數(shù)據(jù)，增加用戶體驗(yàn)）[重要]
• 針對于常作為查詢條件（where）、排序（order by）、分組（group by）操作的字段建立索引[重要]
• 盡量選擇區(qū)分度高的列作為索引，盡量建立唯一索引，區(qū)分度越高，使用索引的效率越高
• 如果是字符串類型的字段，字段的長度較長，可以針對于字段的特點(diǎn)，建立前綴索引
• 盡量使用聯(lián)合索引，減少單列索引，查詢時，聯(lián)合索引很多時候可以覆蓋索引，避免回表，提高查詢效率【重要】
• 要控制索引的數(shù)量，索引并不是多多益善，索引越多，維護(hù)索引結(jié)構(gòu)的代價也就越大，會影響增刪改的效率【重要】
• 如果索引列不能存儲NULL值，在創(chuàng)建表時使用NOT NULL約束。當(dāng)優(yōu)化器知道每列是否包含NULL值時，它可以更好地確定哪個索引最有效地用于查詢

7.6 什么情況下索引會失效？

• 違反最左前綴法則
• 范圍查詢右邊的列，不能使用索引
• 不要再索引列上進(jìn)行運(yùn)算操作，索引將會失效
• 隱式類型轉(zhuǎn)換：字符串不加單引號，造成索引失效（類型失效）
• 以%開頭的Like模糊查詢，索引失效
• 使用Select * ，索引會失效
• 使用OR操作：查詢條件使用or關(guān)鍵字，其中一個字段沒有創(chuàng)建索引，則會導(dǎo)致整個查詢語句索引失效； or兩邊為“>”和“<”范圍查詢時，索引失效。
• 兩列作比較：兩列數(shù)據(jù)做比較，即便兩列都創(chuàng)建了索引，索引失效
• 不等于比較：查詢條件使用不等進(jìn)行比較時，需要慎重，普通索引會查詢結(jié)果集占比較大時，索引失效
• is not null：查詢條件使用is null時正常走索引，使用is not null時，索引失效。

8. 優(yōu)化

SQL優(yōu)化一般可以從設(shè)計(jì)、查詢、索引和存儲四方面進(jìn)行。

8.1 如何定位慢查詢？

• 采用運(yùn)維工具（Skywalking），可以檢測出是哪個接口，最終找到SQL問題
• 在數(shù)據(jù)庫中開啟慢日志查詢，設(shè)置閾值（可以設(shè)置為2秒），一旦SQL執(zhí)行超過閾值就hi記錄到日志中。

8.2 一個SQL語句執(zhí)行很慢，如何分析

可以使用MySQL自帶的分析工具EXPLAIN或者DESC

通過key和key_len檢查是否命中了索引（索引本身存在是否失效的情況）

通過type字段查看sql是否有進(jìn)一步的優(yōu)化空間，是否存在全索引掃描或全盤掃描

通過extra建議判斷，是否出現(xiàn)了回表的情況，如果出現(xiàn)了，可以嘗試添加索引或修改返回字段來修復(fù)。

• possible_key：當(dāng)前sql可能會使用到的索引
• key：當(dāng)前sql實(shí)際命中的索引
• key_len：索引占用的大小
• Extra：額外的優(yōu)化建議

type：這條SQL的連接類型，性能由好到差依次為：NULL，system，const，eq_ref，ref，range，index，all

• system：查詢系統(tǒng)中的表
• const：根據(jù)主鍵查詢
• eq_ref：主鍵索引查詢或唯一索引查詢
• ref：索引查詢
• range：范圍查詢
• index：索引樹掃描
• all：全盤掃描

8.3 SQL優(yōu)化的經(jīng)驗(yàn)

表的設(shè)計(jì)優(yōu)化

參考阿里開發(fā)手冊《嵩山版》

• 比如設(shè)置合適的數(shù)值（tinyint int bigint），要根據(jù)實(shí)際情況選擇
• 比如設(shè)置合適的字符串類型（char和varchar）char定長效率高，varchar可變長度，效率稍低

索引優(yōu)化[參考優(yōu)化創(chuàng)建原則和索引失效]

SQL語句優(yōu)化

• SELECT語句無比指定字段名稱（避免直接使用SELECT *）
• SQL語句要避免造成索引失效的寫法
• 盡量用UNION ALL代替UNION UNION會多一次過濾，效率低
• 避免在where子句中對字段進(jìn)行表達(dá)式操作
• Join優(yōu)化：能用INNERJOIN就不用LEFT JOIN RIGHT JOIN，如必須使用一定要以小表為驅(qū)動，內(nèi)連接會對兩個表進(jìn)行優(yōu)化，優(yōu)先把小表放到外面，把大表放到里面。LEFT JOIN或RIGHT JOIN，不會重新調(diào)整順序。

主從復(fù)制、讀寫分離

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

分庫分表

8.4 主從同步原理

MySQL主從復(fù)制的核心是二進(jìn)制bin log（記錄DDL和DML）

• 主從在事務(wù)提交時會把數(shù)據(jù)變更記錄在二進(jìn)制日志文件bin log中
• 從庫讀取主庫的二進(jìn)制日志文件bin log，寫入到從庫的中繼日志Relay log
• 從庫重做中繼日志中的事件，將改變反應(yīng)它自己的數(shù)據(jù)

8.5 分庫分表

時機(jī)

• 前提：項(xiàng)目業(yè)務(wù)數(shù)據(jù)逐漸增多，或業(yè)務(wù)發(fā)展比較迅速（單表的數(shù)據(jù)量達(dá)1000W或20G以后）
• 優(yōu)化已解決不了性能問題（主從讀寫分離，查詢索引…）
• IO瓶頸（磁盤IO，網(wǎng)絡(luò)IO）、CPU瓶頸（聚合查詢、連接數(shù)太多）

拆分策略

垂直拆分

垂直分庫：以表為依據(jù)，根據(jù)業(yè)務(wù)將不同表拆分到不同庫中

特點(diǎn)

• 將業(yè)務(wù)對數(shù)據(jù)分級管理、維護(hù)、監(jiān)控、擴(kuò)展
• 在高并發(fā)下，提高磁盤IO和數(shù)據(jù)量連接數(shù)

垂直分表：以字段為依據(jù)，根據(jù)字段屬性將不同字段拆分到不同表中

特點(diǎn)

• 冷數(shù)據(jù)分離
• 減少IO過度爭搶，兩表互不影響

水平拆分

水平分庫：將一個庫的數(shù)據(jù)拆分到多個庫中

特點(diǎn)

• 解決了單庫大數(shù)量，高并發(fā)的性能瓶頸問題
• 提高了系統(tǒng)的穩(wěn)定性和可用性

水平分表：將一個表的數(shù)據(jù)拆分到多個表中（可以在同一個庫內(nèi)），按照記錄分表

特點(diǎn)

• 優(yōu)化單一表數(shù)量過大而產(chǎn)生的性能問題
• 避免IO爭搶并減少鎖表的幾率

總結(jié)

以上為個人經(jīng)驗(yàn)，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論