一奏甫、存儲(chǔ)引擎

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

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ù)
器也會(huì)為安全接入的每個(gè)客戶端驗(yàn)證它所具有的操作權(quán)限悍汛。

2.服務(wù)層

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

3.引擎層

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

4.存儲(chǔ)層

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

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

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

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

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

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

1-2-1.InnoDB

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

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

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

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

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

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

1-2-2.MyISAM

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

1-2-3.Memory

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

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

存儲(chǔ)引擎選擇

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

索引

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

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

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

上述是MySQL中所支持的所有的索引結(jié)構(gòu)晋修，接下來卦绣，我們?cè)賮砜纯床煌拇鎯?chǔ)引擎對(duì)于索引結(jié)構(gòu)的支持
情況

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

二叉樹

選擇二叉樹作為索引結(jié)構(gòu)廊蜒，會(huì)存在以下缺點(diǎn)：
順序插入時(shí)趴拧，會(huì)形成一個(gè)鏈表，查詢性能大大降低山叮。
大數(shù)據(jù)量情況下著榴，層級(jí)較深，檢索速度慢屁倔。
此時(shí)大家可能會(huì)想到脑又，我們可以選擇紅黑樹，紅黑樹是一顆自平衡二叉樹，那這樣即使是順序插入數(shù)
據(jù)问麸，最終形成的數(shù)據(jù)結(jié)構(gòu)也是一顆平衡的二叉樹,
但是往衷，即使如此，由于紅黑樹也是一顆二叉樹严卖，所以也會(huì)存在一個(gè)缺點(diǎn)：
大數(shù)據(jù)量情況下席舍，層級(jí)較深，檢索速度慢哮笆。
所以来颤，在MySQL的索引結(jié)構(gòu)中，并沒有選擇二叉樹或者紅黑樹稠肘，而選擇的是B+Tree福铅，那么什么是
B+Tree呢？在詳解B+Tree之前项阴，先來介紹一個(gè)B-Tree滑黔。

B-Tree

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

知識(shí)小貼士: 樹的度數(shù)指的是一個(gè)節(jié)點(diǎn)的子節(jié)點(diǎn)個(gè)數(shù)跨扮。
我們可以通過一個(gè)數(shù)據(jù)結(jié)構(gòu)可視化的網(wǎng)站來簡(jiǎn)單演示一下序无。 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)最多存儲(chǔ)4個(gè)key璃氢，對(duì)應(yīng)5個(gè)指針哟玷。
一旦節(jié)點(diǎn)存儲(chǔ)的key數(shù)量到達(dá)5，就會(huì)裂變一也，中間元素向上分裂巢寡。
在B樹中，非葉子節(jié)點(diǎn)和葉子節(jié)點(diǎn)都會(huì)存放數(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)奶甘，接下來篷店，我們?cè)賮砜纯碝ySQL中優(yōu)化之后的
B+Tree。
MySQL索引數(shù)據(jù)結(jié)構(gòu)對(duì)經(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值，映射到對(duì)應(yīng)的槽位上带膜，然后存儲(chǔ)在hash表中

1659067602842.png

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

1659067649925.png

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

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

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

索引分類

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

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

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

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

1659080280514.png

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

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

1659080382270.png

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

回表查詢：這種先到二級(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í)索引咱枉，然
后再查詢聚集索引卑硫，也就是需要進(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ù)庫(kù)的INSERT/UPDATE.SELECT的訪問頻次

-- session 是查看當(dāng)前會(huì)話 ; 
-- 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)罩荆枰贛ySQL的配置文件（/etc/my.cnf）中配置如下信息：

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

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

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級(jí)別開啟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)合索引），要遵守最左前綴法則沦寂。最左前綴法則指的是查詢從索引的最左列開始学密，
并且不跳過索引中的列。如果跳躍某一列凑队，索引將會(huì)部分失效(后面的字段索引失效)
注意 ： 最左前綴法則中指的最左邊的列则果，是指在查詢時(shí)，聯(lián)合索引的最左邊的字段(即是
第一個(gè)字段)必須存在漩氨，與我們編寫SQL時(shí)西壮，條件編寫的先后順序無關(guān)。

范圍查詢

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

索引失效情況

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

SQL提示

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

#user index 建議MySQL是用哪一個(gè)索引完成此次查詢（僅僅是建議辑鲤，mysql內(nèi)部還會(huì)再次進(jìn)行評(píng)估）
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ù)量大, 需要對(duì)
以下SQL語句進(jìn)行優(yōu)化, 該如何進(jìn)行才是最優(yōu)方案:
select id,username,password from tb_user where username =
'itcast';
答案: 針對(duì)于 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í)候需要索引很長(zhǎng)的字符串，這會(huì)讓索引變的很大继找，查詢時(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字段建立長(zhǎng)度為5的前綴索引

前綴長(zhǎng)度
可以根據(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ù)場(chǎng)景中，如果存在多個(gè)查詢條件亿傅，考慮針對(duì)于查詢字段建立索引時(shí)，建議建立聯(lián)合索引瘟栖，
而非單列索引
如果查詢使用的是聯(lián)合索引葵擎，具體的結(jié)構(gòu)示意圖如下：

1659143974133.png

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

1.針對(duì)于數(shù)據(jù)量較大，且查詢比較頻繁的表建立索引
2.針對(duì)于常作為查詢條件（where）半哟、排序（order by）酬滤、分組（group by）操作的字段建立索引
3.盡量選擇區(qū)分度高的列作為索引，盡量建立唯一索引寓涨，區(qū)分度越高盯串，使用索引的效率就越高
4.如果是字符串類型的字段，字段的長(zhǎng)度較長(zhǎng)戒良，可以針對(duì)于字段的特點(diǎn)体捏，建立前綴索引
5.盡量使用聯(lián)合索引，減少單列索引糯崎，查詢時(shí)几缭，；聯(lián)合索引很多時(shí)候可以覆蓋索引沃呢，節(jié)省存儲(chǔ)空間年栓，避免回表，提高查詢效率
6.要控制索引的數(shù)量薄霜，索引并不是多多益善某抓，索引越多，維護(hù)索引結(jié)構(gòu)的代價(jià)也就越大惰瓜，會(huì)影響增刪改的效率
7.如果索引不能存儲(chǔ)NULL值否副，請(qǐng)?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.手動(dòng)控制事務(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ù)庫(kù)提供的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ù)主鍵順序組織存放的敛熬，這種存儲(chǔ)方式的表稱為索引組織表（index organized table IOT）

1659146279852.png

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

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ù)需求的情況下坡脐，盡量降低主鍵的長(zhǎng)度
• 插入數(shù)據(jù)時(shí)，盡量選擇順序插入房揭，選擇使用AUTO_INCREMENT自增主鍵
• 盡量不要使用UUID做主鍵或者是其他自然主鍵备闲，如身份證號(hào)
• 業(yè)務(wù)操作時(shí)，避免對(duì)主鍵的修改

order by優(yōu)化

MySQL的排序捅暴，有兩種方式：
Using filesort：通過表的索引或全局掃描恬砂，讀取滿足條件的數(shù)據(jù)行，然后在排序緩沖區(qū)sortbuffer中完成排序操作蓬痒，所有不是通過索引直接返回排序結(jié)果的排序都叫Filesort排序
Using index：通過有序索引順序掃描直接返回有序數(shù)據(jù)泻骤，這種情況即為using index，不需要額外排序，操作效率高
對(duì)于以上兩種排序方式狱掂，Using index性能高演痒，而Using Filesort的性能低，我們?cè)趦?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í)候會(huì)直接返回這個(gè)數(shù)，效率很高属瓣，但是如果是帶條件的count载迄，MyISAM也慢
• InnDB引擎就麻煩了，它執(zhí)行count（*）的時(shí)候抡蛙，需要把數(shù)據(jù)一行一行的從引擎里面讀出來护昧，然后累積計(jì)數(shù)

如果說要大幅度提升InnoDB表的count效率，主要的優(yōu)化思路：自己計(jì)數(shù)(可以借助于redis這樣的數(shù)
據(jù)庫(kù)進(jìn)行,但是如果是帶條件的count又比較麻煩了)粗截。
count() 是一個(gè)聚合函數(shù)惋耙，對(duì)于返回的結(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)我們?cè)趫?zhí)行刪除的SQL語句時(shí)约炎，會(huì)鎖定id為1這一行的數(shù)據(jù)，然后事務(wù)提交之后锌仅，行鎖釋放。
但是當(dāng)我們?cè)趫?zhí)行如下SQL時(shí)墙贱。
update course set name = 'SpringBoot' where name = 'PHP' ;
當(dāng)我們開啟多個(gè)事務(wù)热芹，在執(zhí)行上述的SQL時(shí)，我們發(fā)現(xiàn)行鎖升級(jí)為了表鎖惨撇。 導(dǎo)致該update語句的性能
大大降低伊脓。
InnoDB的行鎖是針對(duì)索引加的鎖，不是針對(duì)記錄加的鎖 ,并且該索引不能失效魁衙，否則會(huì)從行鎖
升級(jí)為表鎖 报腔。