1. MySQL的索引

1.1 概述

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

1.2 分類

索引是存儲(chǔ)引擎用來(lái)快速查找記錄的一種數(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ù)查詢時(shí)，可以顯著減少查詢時(shí)分組和排序的時(shí)間
• 創(chuàng)建唯一索引，能夠保證數(shù)據(jù)庫(kù)表中每一行數(shù)據(jù)的唯一性
• 在實(shí)現(xiàn)數(shù)據(jù)的參考完整性方面，可以加速表和表之間的連接。

缺點(diǎn)

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

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

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

2. 存儲(chǔ)引擎——MySQL的核心

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

2.1 分類

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

3. MyISAM和InnoDB

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

MyISAM

特點(diǎn)

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

使用場(chǎng)景

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

InnoDB

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

特點(diǎn)

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

使用場(chǎng)景

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

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

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

4. 事務(wù)

4.1 特性(ACID)

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

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

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

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

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

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

redo log：

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

undo log：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

4.7 MVCC

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

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

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

5. 鎖機(jī)制

5.1 定義

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

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

5.2 分類

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

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

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

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

6. 日志

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

6.1 錯(cuò)誤日志

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

SHOW variables LIKE 'log_error%'

6.2 二進(jìn)制日志

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

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

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

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

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

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

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

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

7. 索引

7.1 定義

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

B樹(shù)與B+樹(shù)對(duì)比

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

7.2 聚簇索引和非聚簇索引

7.2.1 聚簇索引

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

7.2.2 二級(jí)索引（非聚簇索引）

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

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

• 如果存在主鍵，主鍵索引就是聚簇索引
• 如果不存在主鍵，將使用第一個(gè)唯一（UNIQUE）索引作為聚簇索引。
• 如果表沒(méi)有主鍵，或沒(méi)有適合的唯一索引，則InnoDB會(huì)自動(dòng)生成一個(gè)rowid作為隱藏的聚簇索引。

7.2.4 回表查詢

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

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

7.3 覆蓋索引

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

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

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

7.4 MySQL超大分頁(yè)處理

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

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

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

7.6 什么情況下索引會(huì)失效？

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

8. 優(yōu)化

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

8.1 如何定位慢查詢？

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

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

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

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

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

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

• possible_key：當(dāng)前sql可能會(huì)使用到的索引
• 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：索引樹(shù)掃描
• all：全盤掃描

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

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

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

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

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

SQL語(yǔ)句優(yōu)化

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

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

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

分庫(kù)分表

8.4 主從同步原理

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

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

8.5 分庫(kù)分表

時(shí)機(jī)

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

拆分策略

垂直拆分

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

特點(diǎn)

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

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

特點(diǎn)

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

水平拆分

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

特點(diǎn)

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

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

特點(diǎn)

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

總結(jié)

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

最新評(píng)論