一、存儲引擎

存儲引擎就是存儲數(shù)據(jù)、建立索引、更新/查詢數(shù)據(jù)等技術(shù)的實(shí)現(xiàn)方式 。存儲引擎是基于表的，而不是
基于庫的，所以存儲引擎也可被稱為表類型。我們可以在創(chuàng)建表的時(shí)候，來指定選擇的存儲引擎，如果
沒有指定將自動選擇默認(rèn)的存儲引擎

1-1.MYSQL體系結(jié)構(gòu)

image.png

1.連接層

最上層是一些客戶端和鏈接服務(wù)，包含本地sock 通信和大多數(shù)基于客戶端/服務(wù)端工具實(shí)現(xiàn)的類似于
TCP/IP的通信。主要完成一些類似于連接處理、授權(quán)認(rèn)證、及相關(guān)的安全方案。在該層上引入了線程
池的概念，為通過認(rèn)證安全接入的客戶端提供線程。同樣在該層上可以實(shí)現(xiàn)基于SSL的安全鏈接。服務(wù)
器也會為安全接入的每個(gè)客戶端驗(yàn)證它所具有的操作權(quán)限。

2.服務(wù)層

第二層架構(gòu)主要完成大多數(shù)的核心服務(wù)功能，如SQL接口，并完成緩存的查詢，SQL的分析和優(yōu)化，部
分內(nèi)置函數(shù)的執(zhí)行。所有跨存儲引擎的功能也在這一層實(shí)現(xiàn)，如 過程、函數(shù)等。在該層，服務(wù)器會解
析查詢并創(chuàng)建相應(yīng)的內(nèi)部解析樹，并對其完成相應(yīng)的優(yōu)化如確定表的查詢的順序，是否利用索引等，
最后生成相應(yīng)的執(zhí)行操作。如果是select語句，服務(wù)器還會查詢內(nèi)部的緩存，如果緩存空間足夠大，
這樣在解決大量讀操作的環(huán)境中能夠很好的提升系統(tǒng)的性能。

3.引擎層

存儲引擎層， 存儲引擎真正的負(fù)責(zé)了MySQL中數(shù)據(jù)的存儲和提取，服務(wù)器通過API和存儲引擎進(jìn)行通
信。不同的存儲引擎具有不同的功能，這樣我們可以根據(jù)自己的需要，來選取合適的存儲引擎。數(shù)據(jù)庫
中的索引是在存儲引擎層實(shí)現(xiàn)的。

4.存儲層

數(shù)據(jù)存儲層， 主要是將數(shù)據(jù)(如: redolog、undolog、數(shù)據(jù)、索引、二進(jìn)制日志、錯(cuò)誤日志、查詢
日志、慢查詢?nèi)罩镜?存儲在文件系統(tǒng)之上，并完成與存儲引擎的交互。
和其他數(shù)據(jù)庫相比，MySQL有點(diǎn)與眾不同，它的架構(gòu)可以在多種不同場景中應(yīng)用并發(fā)揮良好作用。主要
體現(xiàn)在存儲引擎上，插件式的存儲引擎架構(gòu)，將查詢處理和其他的系統(tǒng)任務(wù)以及數(shù)據(jù)的存儲提取分離。
這種架構(gòu)可以根據(jù)業(yè)務(wù)的需求和實(shí)際需要選擇合適的存儲引擎。

建表時(shí)指定存儲引擎

CREATE TABLE 表名（字段1 字段類型 【COMMIT 字段1注釋】）ENGINE = INNODB【COMMENT 表注釋】

查詢當(dāng)前數(shù)據(jù)庫支持的存儲引擎

show engines
例：CREATE TABLE my_myisam（id int，name varchar（10））ENGINE = MyISAM創(chuàng)建my_myisam，并指定MyISAM存儲引擎

1-2.存儲引擎特點(diǎn)

1-2-1.InnoDB

1.介紹：InnoDB是一種兼顧高可靠性和高性能的通用存儲引擎，在MYSQL5.5之后，InnoDB是默認(rèn)的MYSQL存儲引擎

2.特點(diǎn)：
DML操作遵循ACID模型，支持事務(wù)；
行級鎖，提高并發(fā)訪問性能；
支持外鍵FOREIGN KEY約束，保證數(shù)據(jù)的完整性和正確性；

3.文件：
XXX.ibd XXX代表的是表名，innoDB引擎的每張表都會對應(yīng)這樣的一個(gè)表空間文件，存儲該表的表結(jié)構(gòu)（frm-早期的，sdi新版的）、數(shù)據(jù)和索引
參數(shù)：innodb_file_per_table

如果該參數(shù)開啟，代表對于InnoDB引擎的表，每一張表都對應(yīng)一個(gè)ibd文件。 我們直接打開MySQL的
數(shù)據(jù)存放目錄： C:\ProgramData\MySQL\MySQL Server 8.0\Data ， 這個(gè)目錄下有很多文件
夾，不同的文件夾代表不同的數(shù)據(jù)庫，我們直接打開itcast文件夾。
可以看到里面有很多的ibd文件，每一個(gè)ibd文件就對應(yīng)一張表，比如：我們有一張表 account，就
有這樣的一個(gè)account.ibd文件，而在這個(gè)ibd文件中不僅存放表結(jié)構(gòu)、數(shù)據(jù)，還會存放該表對應(yīng)的
索引信息。 而該文件是基于二進(jìn)制存儲的，不能直接基于記事本打開，我們可以使用mysql提供的一
個(gè)指令 ibd2sdi ，通過該指令就可以從ibd文件中提取sdi信息，而sdi數(shù)據(jù)字典信息中就包含該表
show variables like 'innodb_file_per_table'; 1

4.邏輯存儲結(jié)構(gòu)

4-1.表空間:InnoDB存儲引擎邏輯的最高層，ibd文件其實(shí)就是表空間文件，在表空間中可以包含Segment段
4-2.段：表空間都是由各個(gè)段組成的，常見的段有數(shù)據(jù)段、索引段、回滾段等，InnoDB中對段的管理，都是引擎自身完成，不需要人為對其控制，一個(gè)段中包含多個(gè)區(qū)
4-3.區(qū)：區(qū)是表空間的單元結(jié)構(gòu)，每個(gè)區(qū)的大小為1M，默認(rèn)情況下，InnoDB存儲引擎頁大小為16k，即一個(gè)區(qū)中一共有64個(gè)連續(xù)的頁
4-4.頁：頁是組成區(qū)的最小單位，頁也是InnDB存儲引擎磁盤管理的最小單元，每個(gè)頁的大小默認(rèn)為16KB，InnoDB存儲引擎每次從磁盤申請4-5個(gè)區(qū)
4-5.行：InnDB存儲引擎是面向行的，也就是說數(shù)據(jù)是按行進(jìn)行存放的，在每一行中除了定義表時(shí)所指定的字段以外，還包含兩個(gè)隱藏字段

1-2-2.MyISAM

介紹：MyISAM是MySQL早期默認(rèn)存儲引擎
特點(diǎn)：不支持事務(wù)，不支持外鍵；支持表鎖，不支持行鎖；訪問速度快
文件：XXX.sdi存儲表結(jié)構(gòu)信息；XXX.MYD存儲數(shù)據(jù)；XXX.MYI存儲索引

1-2-3.Memory

介紹：Memory引擎的表數(shù)據(jù)是存儲在內(nèi)存中的，由于受到硬件問題、或斷電問題的影響，只能將這些表作為臨時(shí)表或緩存使用
特點(diǎn)：內(nèi)存存放 hash索引（默認(rèn)）
文件：XXX.sdi存儲表結(jié)構(gòu)信息；

1-2-4.區(qū)別及特點(diǎn)

存儲引擎選擇

在選擇存儲引擎時(shí)，應(yīng)該根據(jù)應(yīng)用系統(tǒng)的特點(diǎn)選擇合適的存儲引擎，對于復(fù)雜的應(yīng)用系統(tǒng)，還可以根據(jù)實(shí)際情況選擇多種存儲引擎進(jìn)行組合
InnoDB：是Mysql的默認(rèn)存儲引擎，支持事務(wù)、外鍵。如果應(yīng)用對于事務(wù)的完整性有比較高的要求，在并發(fā)條件下要求數(shù)據(jù)的一致性，數(shù)據(jù)操作除了插入和查詢之外，還包含很多的更新、刪除操作，那么InnoDB是比較合適的選擇
MyISAM：如果應(yīng)用是以讀操作和插入操作為主，只有很少的更新和刪除操作，并且對事務(wù)的完整性、并發(fā)性要求不是很高，可以選擇MyISAM
MEMORY：將所有數(shù)據(jù)保存在內(nèi)存中，訪問速度快，通常用于臨時(shí)表及緩存，MEMORY的缺陷就是對表的大小有限制，太大的表無法緩存在內(nèi)存中，而且無法保障數(shù)據(jù)的安全性

索引

索引（index）是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)（有序），在數(shù)據(jù)之外，數(shù)據(jù)庫系統(tǒng)還維護(hù)著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式引用（指向）數(shù)據(jù)，這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實(shí)現(xiàn)高級查找算法，這種數(shù)據(jù)結(jié)構(gòu)就是索引
例：假如我們要執(zhí)行的SQL語句為 ： select * from user where age = 45;
無索引情況下，需要從第一行開始掃描，一直掃描到最后一行，我們稱之為全表掃描，性能很低
有索引情況下，假設(shè)索引結(jié)構(gòu)是二叉樹，那么就意味著，會對age這個(gè)字段建立一個(gè)二叉樹的索引結(jié)構(gòu)，此時(shí)我們在進(jìn)行查詢時(shí)，只需要掃描三次就可以找到數(shù)據(jù)了，極大的提高的查詢的效率。

索引的特點(diǎn)
優(yōu)勢：提高數(shù)據(jù)檢索的效率，降低數(shù)據(jù)庫的IO成本，通過索引列付數(shù)據(jù)進(jìn)行排序，降低數(shù)據(jù)排序的成本，降低CPU的消耗
劣勢：索引也會占用空間，索引大大提高了查詢效率，同時(shí)也降低了更新表的速度，如對表進(jìn)行INSERT/UPDATE/DELETE時(shí)，效率降低

索引結(jié)構(gòu)

上述是MySQL中所支持的所有的索引結(jié)構(gòu)，接下來，我們再來看看不同的存儲引擎對于索引結(jié)構(gòu)的支持
情況

注意： 我們平常所說的索引，如果沒有特別指明，都是指B+樹結(jié)構(gòu)組織的索引。

二叉樹

選擇二叉樹作為索引結(jié)構(gòu)，會存在以下缺點(diǎn)：
順序插入時(shí)，會形成一個(gè)鏈表，查詢性能大大降低。
大數(shù)據(jù)量情況下，層級較深，檢索速度慢。
此時(shí)大家可能會想到，我們可以選擇紅黑樹，紅黑樹是一顆自平衡二叉樹，那這樣即使是順序插入數(shù)
據(jù)，最終形成的數(shù)據(jù)結(jié)構(gòu)也是一顆平衡的二叉樹,
但是，即使如此，由于紅黑樹也是一顆二叉樹，所以也會存在一個(gè)缺點(diǎn)：
大數(shù)據(jù)量情況下，層級較深，檢索速度慢。
所以，在MySQL的索引結(jié)構(gòu)中，并沒有選擇二叉樹或者紅黑樹，而選擇的是B+Tree，那么什么是
B+Tree呢？在詳解B+Tree之前，先來介紹一個(gè)B-Tree。

B-Tree

B-tree，B樹是一種多叉路衡查找樹，相對于二叉樹，B樹每個(gè)節(jié)點(diǎn)可以有多個(gè)分支，即多叉。
以一顆最大度數(shù)（max-degree）為5（5階）的b-tree為例，那么這個(gè)b樹最多存儲4個(gè)key，5個(gè)指針

知識小貼士: 樹的度數(shù)指的是一個(gè)節(jié)點(diǎn)的子節(jié)點(diǎn)個(gè)數(shù)。
我們可以通過一個(gè)數(shù)據(jù)結(jié)構(gòu)可視化的網(wǎng)站來簡單演示一下。 https://www.cs.usfca.edu/~galles/visualization/BTree.html
插入一組數(shù)據(jù)： 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88
120 268 250 。然后觀察一些數(shù)據(jù)插入過程中，節(jié)點(diǎn)的變化情況。

1659063693250.png

特點(diǎn)：
5階的B樹，每一個(gè)節(jié)點(diǎn)最多存儲4個(gè)key，對應(yīng)5個(gè)指針。
一旦節(jié)點(diǎn)存儲的key數(shù)量到達(dá)5，就會裂變，中間元素向上分裂。
在B樹中，非葉子節(jié)點(diǎn)和葉子節(jié)點(diǎn)都會存放數(shù)據(jù)

B+Tree

B+tree是B-tree的變種，我們以一顆最大度數(shù)（max-degree）為4（4階）的B+tree為例，來看一下其結(jié)構(gòu)示意圖：

1659065349374.png

1659066333987.png

上述我們所看到的結(jié)構(gòu)是標(biāo)準(zhǔn)的B+Tree的數(shù)據(jù)結(jié)構(gòu)，接下來，我們再來看看MySQL中優(yōu)化之后的
B+Tree。
MySQL索引數(shù)據(jù)結(jié)構(gòu)對經(jīng)典的B+Tree進(jìn)行了優(yōu)化。在原B+Tree的基礎(chǔ)上，增加一個(gè)指向相鄰葉子節(jié)點(diǎn)
的鏈表指針，就形成了帶有順序指針的B+Tree，提高區(qū)間訪問的性能，利于排序。

1659067186577.png

Hash

MySQL中除了支持B+tree索引，還支持Hash索引
結(jié)構(gòu)：哈希索引就是采用一定的hash算法，將鍵值換算成新的hash值，映射到對應(yīng)的槽位上，然后存儲在hash表中

1659067602842.png

如果兩個(gè)(或多個(gè))鍵值，映射到一個(gè)相同的槽位上，他們就產(chǎn)生了hash沖突（也稱為hash碰撞），可
以通過鏈表來解決

1659067649925.png

特點(diǎn)：
1.hash所有只能用于對等比較（=，in），不支持范圍查詢（between，<,...）
2.無法利用索引完成排序操作
3.查詢效率高，通常（不存在hash沖突的情況）只需要一次檢索就可以了，效率通常要高于B+tree索引

存儲引擎支持：
在MySQL中，支持hash索引的是Memory存儲引擎。 而InnoDB中具有自適應(yīng)hash功能，hash索引是
InnoDB存儲引擎根據(jù)B+Tree索引在指定條件下自動構(gòu)建的

為什么InnoDB存儲引擎選擇使用B+tree所有結(jié)構(gòu)？
1.相較于二叉樹，層級更少，搜索效率高
2.對于b-tree，無論是葉子節(jié)點(diǎn)還是非葉子幾點(diǎn)，都會保存數(shù)據(jù)，這樣導(dǎo)致一頁中存儲的鍵值減少，指針跟著減少，要同樣保存大量數(shù)據(jù)，只能增加樹的高度，導(dǎo)致性能降低
3.相對于hash索引，b+tree支持范圍匹配及排序操作

索引分類

在MySQL數(shù)據(jù)庫，將索引的具體類型主要分為以下幾類：主鍵索引，唯一索引，常規(guī)索引，全文索引

而在InnoDB存儲引擎中，根據(jù)索引的存儲形式，又可以分為以下兩種：

聚集索引選取規(guī)則：
1.如果存在主鍵，主鍵索引就是聚集索引
2.如果不存在主鍵，將使用第一個(gè)唯一（UNIQUE）作為聚集索引
3.如果表沒有主鍵，或沒有合適的唯一索引，則InnoDB會自動生成一個(gè)rowId作為隱藏的聚集索引

聚集索引和二級索引的具體結(jié)構(gòu)如下：

1659080280514.png

聚集索引的葉子節(jié)點(diǎn)下掛的是這一行的數(shù)據(jù) 。
二級索引的葉子節(jié)點(diǎn)下掛的是該字段值對應(yīng)的主鍵值

接下來，我們來分析一下，當(dāng)我們執(zhí)行如下的SQL語句時(shí)，具體的查找過程是什么樣子的。

1659080382270.png

具體過程如下：
①.由于是根據(jù)name字段進(jìn)行查詢，所以先根據(jù)name=‘Arm’到name字段的二級索引中進(jìn)行匹配查找。但是在二級索引中只能查到Arm對應(yīng)的主鍵值10
②. 由于查詢返回的數(shù)據(jù)是*，所以此時(shí)，還需要根據(jù)主鍵值10，到聚集索引中查找10對應(yīng)的記錄，最
終找到10對應(yīng)的行row。
③. 最終拿到這一行的數(shù)據(jù)，直接返回即可

回表查詢：這種先到二級索引中查找數(shù)據(jù)，找到主鍵值，然后再到聚集索引中根據(jù)主鍵值，獲取數(shù)據(jù)的方式，就稱之為回表查詢

以下兩條SQL語句，那個(gè)執(zhí)行效率高? 為什么?
A. select * from user where id = 10 ;
B. select * from user where name = 'Arm' ;
備注: id為主鍵，name字段創(chuàng)建的有索引；
解答：
A 語句的執(zhí)行性能要高于B 語句。
因?yàn)锳語句直接走聚集索引，直接返回?cái)?shù)據(jù)。 而B語句需要先查詢name字段的二級索引，然
后再查詢聚集索引，也就是需要進(jìn)行回表查詢。

InnoDB主鍵索引的B+tree高度為多高呢?

1659080839765.png

索引語法

創(chuàng)建索引：CREATE 【UNIQUE|FULLTEXT】INDEX index_name ON table_name（index_col_name，...）
查看索引： SHOW INDEX FROM table_name
刪除索引：DROP INDEX index_name   ON table_name

SQL性能分析

SQL執(zhí)行頻率

MySQL客戶端連接成功后，通過show【session|global】status命令可以提供服務(wù)器狀態(tài)信息，通過如下指令，可以查看當(dāng)前數(shù)據(jù)庫的INSERT/UPDATE.SELECT的訪問頻次

-- session 是查看當(dāng)前會話 ; 
-- global 是查詢?nèi)謹(jǐn)?shù)據(jù) ; 
SHOW GLOBAL STATUS LIKE 'Com_______';
Com_delete: 刪除次數(shù)
Com_insert: 插入次數(shù)
Com_select: 查詢次數(shù)
Com_update: 更新次數(shù)

慢查詢?nèi)罩?/h4>

慢查詢?nèi)罩居涗浟怂袌?zhí)行時(shí)間超過指定參數(shù)（long_query_time，單位：秒，默認(rèn)10秒）的所有
SQL語句的日志。
MySQL的慢查詢?nèi)罩灸J(rèn)沒有開啟，我們可以查看一下系統(tǒng)變量 slow_query_log。

如果要開啟慢查詢?nèi)罩?，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

# 開啟MySQL慢日志查詢開關(guān) 
slow_query_log=1
# 設(shè)置慢日志的時(shí)間為2秒，SQL語句執(zhí)行時(shí)間超過2秒，就會視為慢查詢，記錄慢查詢?nèi)罩?   
long_query_time=2
#配置完畢之后，通過以下指令重新啟動MySQL服務(wù)器進(jìn)行測試，查看慢日志文件中記錄的信息/var/lib/mysql/localhost-slow.log。
systemctl restart mysqld

在慢查詢?nèi)罩局?，只會記錄?zhí)行時(shí)間超多我們預(yù)設(shè)時(shí)間（2s）的SQL，執(zhí)行比較快的是不會記錄的

profile詳情

show profiles能夠在做SQL優(yōu)化時(shí)幫助我們了解時(shí)間都耗費(fèi)到哪去了，通過have_profiling參數(shù)，能夠看到當(dāng)前MySQL是否支持profile操作
 show profiles
SELECT @@have_profiling
通過set語句在session/global級別開啟profiling
SET profiling=1
-- 查看每一條SQL的耗時(shí)基本情況
show profiles;
-- 查看指定query_id的SQL語句各個(gè)階段的耗時(shí)情況 
show profile for query query_id;
-- 查看指定query_id的SQL語句CPU的使用情況
show profile cpu for query query_id;

explain

EXPLAIN或者DESC命令獲取MySQL如何執(zhí)行SELECT語句的信息，包括在SELECT語句執(zhí)行的過程中表如連接和連接的順序

-- 直接在select語句之前加上關(guān)鍵字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 條件

EXPLAIN執(zhí)行計(jì)劃中各個(gè)字段的含義：

最左前綴法則

如果索引了多列（聯(lián)合索引），要遵守最左前綴法則。最左前綴法則指的是查詢從索引的最左列開始，
并且不跳過索引中的列。如果跳躍某一列，索引將會部分失效(后面的字段索引失效)
注意 ： 最左前綴法則中指的最左邊的列，是指在查詢時(shí)，聯(lián)合索引的最左邊的字段(即是
第一個(gè)字段)必須存在，與我們編寫SQL時(shí)，條件編寫的先后順序無關(guān)。

范圍查詢

聯(lián)合索引中，出現(xiàn)范圍查詢(>,<)，范圍查詢右側(cè)的列索引失效。
當(dāng)范圍查詢使用>= 或 <= 時(shí)，走聯(lián)合索引了，但是索引的長度為54，就說明所有的字段都是走索引
的。
所以，在業(yè)務(wù)允許的情況下，盡可能的使用類似于 >= 或 <= 這類的范圍查詢，而避免使用 > 或 <

索引失效情況

1.索引列運(yùn)算：不要在索引列上進(jìn)行運(yùn)算操作，索引將失效
2.字符串不加引號：字符串類型字段使用時(shí)，不加引號，索引將失效。
3.模糊查詢：如果僅僅是尾部模糊匹配，索引不會失效。如果是頭部模糊匹配，索引失效。
4.or連接條件：用or分割開的條件， 如果or前的條件中的列有索引，而后面的列中沒有索引，那么涉及的索引都不會
被用到
5.數(shù)據(jù)分布影響：如果MySQL評估使用索引比全表更慢，則不使用索引。

SQL提示

SQL提示是優(yōu)化數(shù)據(jù)庫的一個(gè)重要手段，簡單來說，就是在SQL語句中加入一些人為的提示來達(dá)到優(yōu)化操作的目的

#user index 建議MySQL是用哪一個(gè)索引完成此次查詢（僅僅是建議，mysql內(nèi)部還會再次進(jìn)行評估）
explain SELECT * FROM 表名 use index （字段名）WHERE 條件 
#ignore index 忽略指定的索引
explain SELECT * FROM 表名 ignore index （字段名）WHERE 條件 
#force index 強(qiáng)制使用索引
explain SELECT * FROM 表名 force index （字段名）WHERE 條件 

覆蓋索引

盡量使用覆蓋索引，減少SELECT * 那么什么是覆蓋索引，覆蓋索引是指查詢時(shí)使用了索引，并且需要返回的列，在該索引中已經(jīng)全部能夠找到

一張表, 有四個(gè)字段(id, username, password, status), 由于數(shù)據(jù)量大, 需要對
以下SQL語句進(jìn)行優(yōu)化, 該如何進(jìn)行才是最優(yōu)方案:
select id,username,password from tb_user where username =
'itcast';
答案: 針對于 username, password建立聯(lián)合索引, sql為: create index
idx_user_name_pass on tb_user(username,password);
這樣可以避免上述的SQL語句，在查詢的過程中，出現(xiàn)回表查詢

前綴索引

當(dāng)字段類型為字符串（varchar，text，longtext等）時(shí)，有時(shí)候需要索引很長的字符串，這會讓索引變的很大，查詢時(shí)，浪費(fèi)大量的磁盤IO，影響查詢效率，此時(shí)可以只將字符串的一部分前綴，建立索引，這樣可以大大節(jié)約索引空間，從而提高索引效率

CREATE index idx_xxx on table_name（column（n））

例：create index idx_email_5 on tb_user（email（5））為tb_user的表email字段建立長度為5的前綴索引

前綴長度
可以根據(jù)索引的選擇性來決定，而選擇性是指不重復(fù)的索引值（基數(shù)）和數(shù)據(jù)表的記錄總數(shù)的比值索引選擇性越高則查詢效率越高，唯一索引的選擇性是1，這是最好的索引選擇性，性能也是最好的
SELECT COUNT（DISTINCT email）/ COUNT（）FROM tb_user
SELECT COUNT（DISTINCT substring（email，1,5））/ COUNT（）FROM tb_user

1659143802121.png

單列索引與聯(lián)合索引

單列索引：即一個(gè)索引只包含單個(gè)列
聯(lián)合索引：即一個(gè)索引包含了多個(gè)列
在業(yè)務(wù)場景中，如果存在多個(gè)查詢條件，考慮針對于查詢字段建立索引時(shí)，建議建立聯(lián)合索引，
而非單列索引
如果查詢使用的是聯(lián)合索引，具體的結(jié)構(gòu)示意圖如下：

1659143974133.png

索引設(shè)計(jì)原則

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

SQL優(yōu)化

插入數(shù)據(jù)

insert

如果我們需要一次性往數(shù)據(jù)表里插入多條記錄，可以從以下三個(gè)方面進(jìn)行優(yōu)化
1.批量插入數(shù)據(jù)
INSERT INTO tb_test values（1，‘tom’），（2，‘cat’）；
2.手動控制事務(wù)
start transaction;
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
commit;
3.主鍵順序插入，性能要高于亂序插入

大批量插入數(shù)據(jù)

如果一次性需要插入大批量數(shù)據(jù)（比如：幾百萬的記錄），使用insert語句插入性能較低，此時(shí)可以使用MySQL數(shù)據(jù)庫提供的load指令進(jìn)行插入
--客戶端連接服務(wù)端時(shí)，加上參數(shù) --local-infile
mysql --local-infile -u root -p
--設(shè)置全局參數(shù)local_infile為1，開啟從本地加載文件導(dǎo)入數(shù)據(jù)的開關(guān)
set global local_infile = 1
--執(zhí)行l(wèi)oad指令將準(zhǔn)備好的數(shù)據(jù)，加載到表結(jié)構(gòu)中
load data local infile ‘/root/sqll.log’ into table tb_user fields terminated by ‘，’ lines termainated by ‘\n’;

主鍵優(yōu)化

1).數(shù)據(jù)組織方式
在InnoDB引擎中，表數(shù)據(jù)都是根據(jù)主鍵順序組織存放的，這種存儲方式的表稱為索引組織表（index organized table IOT）

1659146279852.png

在InnoDB引擎中，數(shù)據(jù)行是記錄在邏輯結(jié)構(gòu)page頁中的，而每一個(gè)頁的大小是固定的，默認(rèn)16k，那也就意味著，一個(gè)頁中所存儲的行也是有限的，如果插入的數(shù)據(jù)行row在該頁存儲不小，將會存儲到下一頁中，頁與頁之間會通過指針連接
2).頁分裂
頁可以為空，也可以填充一半，也可以填充100%，每個(gè)頁包含了2-n行數(shù)據(jù)（如果一行數(shù)據(jù)過大，會行溢出），根據(jù)主鍵排列
a.主鍵順序插入效果
①. 從磁盤中申請頁， 主鍵順序插入

1659147281074.png

1659147306484.png

1659147330726.png

④. 當(dāng)?shù)诙搶憹M了，再往第三頁寫入

1659147439524.png

1659147505569.png

1659147548572.png

1659147581625.png

1659147606516.png

1659147671477.png

1659147695545.png

上述的這種現(xiàn)象，稱之為頁分裂，是比較耗費(fèi)性能的操作
3).頁合并
假設(shè)目前表中已有數(shù)據(jù)的索引結(jié)構(gòu)（葉子節(jié)點(diǎn)）如下：

1659147010681.png

1659147059900.png

1659147092757.png

• 滿足業(yè)務(wù)需求的情況下，盡量降低主鍵的長度
• 插入數(shù)據(jù)時(shí)，盡量選擇順序插入，選擇使用AUTO_INCREMENT自增主鍵
• 盡量不要使用UUID做主鍵或者是其他自然主鍵，如身份證號
• 業(yè)務(wù)操作時(shí)，避免對主鍵的修改

order by優(yōu)化

MySQL的排序，有兩種方式：
Using filesort：通過表的索引或全局掃描，讀取滿足條件的數(shù)據(jù)行，然后在排序緩沖區(qū)sortbuffer中完成排序操作，所有不是通過索引直接返回排序結(jié)果的排序都叫Filesort排序
Using index：通過有序索引順序掃描直接返回有序數(shù)據(jù)，這種情況即為using index，不需要額外排序，操作效率高
對于以上兩種排序方式，Using index性能高，而Using Filesort的性能低，我們在優(yōu)化排序操作時(shí)，盡量要優(yōu)化為Using index

order by優(yōu)化法則：
a.根據(jù)排序字段建立合適的索引，多字段排序時(shí)，也遵循最左前綴法則
b.盡量使用覆蓋索引
c.多字段排序，一個(gè)升序一個(gè)降序，此時(shí)需要注意聯(lián)合索引在創(chuàng)建時(shí)的規(guī)則（ASC/DESC）
d.如果不可避免的出現(xiàn)filesort，大數(shù)據(jù)排序時(shí)，可以適當(dāng)增大排序緩沖區(qū)大小sort_buffer_size（默認(rèn)256k）

group by 優(yōu)化

A. 在分組操作時(shí)，可以通過索引來提高效率。
B. 分組操作時(shí)，索引的使用也是滿足最左前綴法則的

limit優(yōu)化

優(yōu)化思路: 一般分頁查詢時(shí)，通過創(chuàng)建 覆蓋索引 能夠比較好地提高性能，可以通過覆蓋索引加子查
詢形式進(jìn)行優(yōu)化。

count優(yōu)化

SELECT COUTN（*）FROM tb_user;
如果數(shù)據(jù)量很大，在執(zhí)行count在、操作時(shí)，是非常耗時(shí)的

• MyISAM引擎把一個(gè)表的總行數(shù)存在了磁盤上，因此執(zhí)行count（*）的時(shí)候會直接返回這個(gè)數(shù)，效率很高，但是如果是帶條件的count，MyISAM也慢
• InnDB引擎就麻煩了，它執(zhí)行count（*）的時(shí)候，需要把數(shù)據(jù)一行一行的從引擎里面讀出來，然后累積計(jì)數(shù)

如果說要大幅度提升InnoDB表的count效率，主要的優(yōu)化思路：自己計(jì)數(shù)(可以借助于redis這樣的數(shù)
據(jù)庫進(jìn)行,但是如果是帶條件的count又比較麻煩了)。
count() 是一個(gè)聚合函數(shù)，對于返回的結(jié)果集，一行行地判斷，如果 count 函數(shù)的參數(shù)不是
NULL，累計(jì)值就加 1，否則不加，最后返回累計(jì)值。
用法：count（*）、count（主鍵）、count（字段）、count（數(shù)字）

按照效率排序的話，count(字段) < count(主鍵 id) < count(1) ≈ count()，所以盡量使用 count()。

update優(yōu)化

我們主要需要注意一下update語句執(zhí)行時(shí)的注意事項(xiàng)
UPDATE coures SET name = ‘javaEE’ WHERE id = 1
當(dāng)我們在執(zhí)行刪除的SQL語句時(shí)，會鎖定id為1這一行的數(shù)據(jù)，然后事務(wù)提交之后，行鎖釋放。
但是當(dāng)我們在執(zhí)行如下SQL時(shí)。
update course set name = 'SpringBoot' where name = 'PHP' ;
當(dāng)我們開啟多個(gè)事務(wù)，在執(zhí)行上述的SQL時(shí)，我們發(fā)現(xiàn)行鎖升級為了表鎖。 導(dǎo)致該update語句的性能
大大降低。
InnoDB的行鎖是針對索引加的鎖，不是針對記錄加的鎖 ,并且該索引不能失效，否則會從行鎖
升級為表鎖 。