Mysql進階篇1_存儲引擎、索引、SQL性能分析指令

文章目錄

1.存儲引擎
- InnoDB
- MyISAM
- Memory
- 存儲引擎選擇和對比
2.索引
- 索引結構
- 索引分類
- 索引語法
- 索引使用（建議看完第3節后觀看）！！！
- - mysql如何使用索引查詢數據（個人理解）
3.SQL性能分析
- SQL執行頻率
- 慢查詢日志
- profile
- explain

Mysql結構圖-mysql執行sql的過程
在這里插入圖片描述

1.存儲引擎

存儲引擎就是存儲數據、建立索引、更新/查詢數據等技術的實現方式。

存儲引擎是基于表的，而不是基于庫的，所以存儲引擎也可被稱為表類型。

我們可以在創建表的時候，來指定選擇的存儲引擎，如果沒有指定將自動選擇默認的存儲引擎，默認為InnoDB。

我們常用的存儲引擎有兩種1.MyISAM 2.InnoDB

InnoDB

在 MySQL 5.5 之后，InnoDB是默認的 MySQL 存儲引擎。
特點

事務支持：支持 ACID 事務，適合需要高并發和數據一致性的應用。
行級鎖：支持行級鎖，減少鎖沖突，提高并發性能。
外鍵約束：支持外鍵FOREIGN KEY約束，保證數據的完整性。（一般不用這個約束，都是邏輯約束）
崩潰恢復：具有崩潰恢復能力，數據安全性高。

文件
xxx.ibd：xxx是表名，innoDB引擎的每張表都會對應這樣一個表空間文件，存儲該表的表結構、數據和索引。
參數innodb_file_per_table：決定是多張表對應一個.ibd文件，還是一張表對應一個.idb文件

 show variables  like 'innodb_file_per_table';

在這里插入圖片描述
on時，一張表對應一個.ibd文件

表空間 : InnoDB存儲引擎邏輯結構的最高層，ibd文件其實就是表空間文件，在表空間中可以包含多個Segment段。
段 : 常見的段有數據段、索引段、回滾段等。InnoDB中對于段的管理，都是引擎自身完成，不需要人為對其控制，一個段中包含多個區。
區 : 區是表空間的單元結構，每個區的大小為1M。默認情況下， InnoDB存儲引擎頁大小為16K，即一個區中一共有64個連續的頁。
頁 : 頁是組成區的最小單元，頁也是InnoDB 存儲引擎磁盤管理的最小單元，即每次讀取到內存的時候是讀取一頁的數據，每個頁的大小默認為 16KB。為了保證頁的連續性，InnoDB 存儲引擎每次從磁盤申請 4-5 個區。
行 : InnoDB 存儲引擎是面向行的，也就是說數據是按行進行存放的，在每一行中除了定義表時所指定的字段以外，還包含兩個隱藏字段（MVCC會講解）。

MyISAM

MyISAM是MySQL早期的默認存儲引擎
特點

表級鎖：只支持表級鎖，適合讀多寫少的場景。
全文索引：支持全文索引，適合文本搜索。
不支持事務：不適合需要事務支持的應用。

文件
xxx.sdi：存儲表結構信息
xxx.MYD: 存儲數據
xxx.MYI: 存儲索引

Memory

Memory：數據存儲在內存中，速度快，但重啟后數據丟失，只能將這些表作為臨時表或緩存使用。
默認使用Hash索引

存儲引擎選擇和對比

InnoDB: 是Mysql的默認存儲引擎，支持事務、外鍵。如果應用對事務的完整性有比較高的要求，在并發條件下要求數據的一致性，數據操作除了插入和查詢之外，還包含很多的更新、刪除操作，那么InnoDB存儲引擎是比較合適的選擇。
MyISAM ：如果應用是以讀操作和插入操作為主，只有很少的更新和刪除操作，并且對事務的完整性、并發性要求不是很高，那么選擇這個存儲引擎是非常合適的。

2.索引

索引（index）是幫助 MySQL 高效獲取數據的數據結構。
數據庫系統除了維護數據之外，還維護著索引。索引指向著數據。

索引結構

概述
MySQL的索引是在存儲引擎層實現的，不同的存儲引擎有不同的索引結構，主要包含以下幾種
在這里插入圖片描述
不同的存儲引擎對于索引結構的支持情況。

為什么要使用B+樹而不是B樹或者二叉樹

二叉樹
如果選擇二叉樹作為索引結構，會存在以下缺點：

順序插入時，會形成一個鏈表，查詢性能大大降低。
大數據量情況下，層級較深，檢索速度慢。
如果選擇紅黑樹作為索引結構，但在大數據量情況下，層級較深，檢索速度慢。

B-Tree

B樹：是一個多叉的平衡搜索樹。
度數（節點的子節點個數）為5的B樹，B樹的每個節點最多存儲4個Key，5個指針。

特點：

例如：5階的B樹，每一個節點最多存儲4個key，對應5個指針
在B樹中，非葉子節點和葉子節點都會存放數據。

B+Tree
B+Tree是B-Tree的變種，我們以一顆最大度數（max-degree）為4（4階）的b+tree為例，來看一下其結構示意圖：

我們可以看到，兩部分：
藍色框起來的部分，是索引部分，僅僅起到索引數據的作用，不存儲數據。
綠色框起來的部分，是數據存儲部分，在其葉子節點中要存儲具體的數據。

B+Tree 與 B-Tree相比，主要有以下三點區別：

所有的數據都會出現在葉子節點。
葉子節點形成一個單向鏈表。
非葉子節點僅僅起到索引數據作用，具體的數據都是在葉子節點存放的
MySQL索引數據結構對經典的B+Tree進行了優化。在原B+Tree的基礎上，增加一個指向相鄰葉子節點的鏈表指針，就形成了帶有順序指針的B+Tree，提高區間訪問的性能，利于排序。
單向鏈表變雙向鏈表

Hash

MySQL中除了支持B+Tree索引，還支持一種索引類型—Hash索引。

哈希索引就是采用一定的hash算法，將鍵值換算成新的hash值，映射到對應的槽位上，然后存儲在hash表中

如果兩個(或多個)鍵值，映射到一個相同的槽位上，他們就產生了hash沖突（也稱為hash碰撞），可以通過鏈表來解決
在這里插入圖片描述
特點：

Hash索引只能用于對等比較(=，in)，不支持范圍查詢（between，>，< ，…）
無法利用索引完成排序操作
查詢效率高，通常(不存在hash沖突的情況)只需要一次檢索就可以了，效率通常要高于B+tree索引
在MySQL中，支持hash索引的是Memory存儲引擎。而InnoDB中具有自適應hash功能，hash索引是InnoDB存儲引擎根據B+Tree索引在指定條件下自動構建的。

面試題：
為什么InnoDB存儲引擎選擇使用B+tree索引結構
1.相對于二叉樹，層級更少，搜索效率高
2.對于B-tree，無論是葉子節點還是非葉子節點，都會保存數據，這樣導致一頁中存儲的鍵值減少，指針跟著減少，要同樣保存大量數據，只能增加樹的高度，導致性能降低。(且難以進行范圍查詢，查找數據的時間穩定性不足)
3.相對Hash索引，B+tree支持范圍匹配及排序操作

索引分類

索引分類
在MySQL數據庫，將索引的具體類型主要分為以下幾類：主鍵索引、唯一索引、常規索引、全文索引。
在這里插入圖片描述
聚集索引&二級索引
注：聚簇索引只能存在一個，且必須要有

聚集索引選取規則:

如果存在主鍵，主鍵索引就是聚集索引。
如果不存在主鍵，將使用第一個唯一（UNIQUE）索引作為聚集索引。
如果表沒有主鍵，或沒有合適的唯一索引，則InnoDB會自動生成一個rowid作為隱藏的聚集索引。
聚集索引和二級索引的具體結構如下：
聚集索引的葉子節點下掛的是這一行的數據。
二級索引的葉子節點下掛的是該字段值對應的主鍵值。

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

所以直接查聚集索引必用二級索引要快，少了回表查詢這個步驟

索引語法

1.創建索引

 CREATE  [ UNIQUE | FULLTEXT ]  INDEX  index_name  ON  table_name  ( index_col_name,... );

2.查看索引

SHOW  INDEX  FROM  table_name;

3.刪除索引

DROP  INDEX  index_name  ON  table_name;

索引使用（建議看完第3節后觀看）！！！

最左前綴法則
如果索引了多列（聯合索引），要遵守最左前綴法則。最左前綴法則指的是查詢從索引的最左列開始，并且不跳過索引中的列。如果跳躍某一列，索引將會部分失效(后面的字段索引失效)。

注意：
注意：最左前綴法則中指的最左邊的列，是指在查詢時，聯合索引的最左邊的字段 (即第一個字段) 必須存在，與我們編寫SQL時，條件編寫的先后順序無關。

舉例：比如這三個是聯合索引，profession是最左字段，age和status緊跟其后

如果select * from tb_user where profession= xxx and age = xx and status = xx（三個都用）
或者select * from tb_user where profession= xxx and age = xx（用profession和age）
都會使用到聯合索引
如果where中無profession這個字段，就不會走索引，不滿足最左前綴法則
如果where 只用了profession 和 status（少了中間的age），也會用索引，但是只會用profession的索引
那么索引不連續會造成什么后果呢？
查詢效率的降低，以下是查詢索引不連續的查詢流程,逐行過濾降低了查詢篩選效率
可以看下這篇文章，可以加深對于最左前綴法則的理解，簡單來說后一個字段依靠前一個字段的排序來進行排序的，沒有用前面的字段索引就沒法用有后面的字段索引
MySQL聯合索引底層數據結構是怎樣的

范圍查詢
聯合索引中，出現范圍查詢(>,<)，范圍查詢右側的列索引失效。

在業務允許的情況下，盡可能的使用類似于>=或 <= 這類的范圍查詢，而避免使用 > 或 <

索引失效情況

索引列計算
不要在索引列上進行運算操作，索引將失效。

 explain  select  *  from  tb_user  where  substring(phone,10,2) = '15'; # phone索引失效

字符串不加引號

字符串類型字段使用時，不加引號，索引將失效。（規范書寫）

select * from tb_user where phone = '123456';
和
select * from tb_user where phone = 123456;
都可以查到，但是下面那個不會用到索引，我猜測應該是底層用了某種計算轉換（相當于索引列計算唄）

模糊查詢
如果僅僅是尾部模糊匹配，索引不會失效。如果是頭部模糊匹配，索引失效。

即在like模糊查詢中，在關鍵字后面加%，索引可以生效。而如果在關鍵字前面加了%，
索引將會失效

select * from tb_user where name like '小%'; 索引正常起作用
select * from tb_user where name like '%小%'; 索引失效

or連接條件
用or分割開的條件，如果or前的條件中的列有索引，而后面的列中沒有索引，那么涉及的索引都不會被用到。

當or連接的條件，左右兩側字段都有索引時，索引才會生效。

數據分布影響
如果MySQL評估使用索引比全表更慢，則不使用索引。
下面的explain的type有具體說明

SQL提示
SQL提示，是優化數據庫的一個重要手段，簡單來說，就是在SQL語句中加入一些人為的提示來達到優化操作的目的。

use index：建議MySQL使用哪一個索引完成此次查詢（僅僅是建議，mysql內部還會再次進行評估）
比如現在我的profession有兩個索引，一個是聯合索引idx_user_sta，一個是單列索引idx_user_pro

 explain select * from tb_user use index(idx_user_pro) where profession = '軟件工程';

ignore index ：忽略指定的索引，不用哪個索引。

 explain select * from tb_user ignore index(idx_user_pro) where profession = '軟件工程';

force index ：強制使用索引，必須用哪個索引。

 explain select * from tb_user force index(idx_user_pro) where profession = '軟件工程';

覆蓋索引
盡量使用覆蓋索引，減少select *。

那么什么是覆蓋索引呢？覆蓋索引是指查詢條件使用了索引，并且需要返回的列，在該索引中已經全部能夠找到 。
注：這里的該索引是mysql優化器選擇的索引

SELECT * FROM tb_user WHERE id=1 AND name=‘Alice’;
比如該語句，若sql優化器語句選擇id索引就不會回表查
選擇name就需要回表查詢
但mysql一般會選覆蓋索引的索引來查詢
所以我們需要的是where后面有索引的字段中有一個索引結構中包含查詢字段的全部信息即可
一般創建聯合索引解決該問題

接下來，我們來看一組SQL的執行計劃，看看執行計劃的差別，然后再來具體做一個解析。

explain select id, profession from tb_user where profession = '軟件工程' and age = 31 and status = '0' ;
explain select id,profession,age, status from tb_user where profession = '軟件工程' and age = 31 and status = '0' ;
explain select id,profession,age, status, name from tb_user where profession = '軟件工程' and age = 31 and status = '0';
explain select * from tb_user where profession = '軟件工程' and age = 31 and status = '0';

上述這幾條SQL的執行結果為:
在這里插入圖片描述
從上述的執行計劃我們可以看到，這四條SQL語句的執行計劃前面所有的指標都是一樣的，看不出來差異。但是此時，我們主要關注的是后面的Extra，前面兩天SQL的結果為 Using where; Using Index ; 而后面兩條SQL的結果為:Using index condition。
在這里插入圖片描述
下面我們解釋一下回表查詢
像前兩條sql語句就不會涉及到回表查詢
因為索引結構已經包含了所有需要查詢的字段-覆蓋索引
select * from tb_user where id=2;使用聚簇索引查詢可以使用*不用回表（聚簇索引下就是所有數據）
select id,name from tb_user where name=‘Arm’;使用其他索引也可以帶上聚簇索引所在的列，也不會回表查詢，因為其他索引除了存儲本列的值還存儲了對應聚簇索引列的值

而第三條sql由于多引用了一個gender，只根據name的索引結構查不出所以要回表查詢
在這里插入圖片描述
所以為什么要減少使用select * 呢？
因為極易產生回表查詢

思考題：
一張表, 有四個字段(id, username, password, status), 由于數據量大, 需要對以下SQL語句進行優化, 該如何進行才是最優方案:
select id,username,password from tb_user where username = ‘itcast’;

答案: 針對于 username, password建立聯合索引, sql為: create index idx_user_name_pass on tb_user(username,password);
這樣username和password是索引結構既有他們自己的值，也有id的值
可以避免上述的SQL語句，在查詢的過程中，出現回表查詢。

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

create index  idx_xxxx on table_name(column(n));

n代表使用前n個字符創建前綴索引
關于n長度的選擇要平衡選擇性和索引體積來指定
選擇性就是當你的索引每個值但是唯一的，選擇性就是1（unique 索引就是1），如果有重復就是distinct/count，選擇性越高，查詢效率越高，而索引體積就是依靠于n取多少

select count(distinct substring(字符串字段名,1,n))/count(*) from table
來觀察，對應的值就是選擇性，通過改變n的值獲取最佳的n

通過前綴索引回表查詢到數據之后還要對比一下（因為索引匹配只是前綴匹配，不確定就整個字符串都匹配，你選擇性為1當我沒說）
在這里插入圖片描述
單列索引與聯合索引
單列索引：即一個索引只包含單個列。

聯合索引：即一個索引包含了多個列。

在業務場景中，如果存在多個查詢條件，考慮針對于查詢字段建立索引時，建議建立聯合索引，而非單列索引。聯合索引的數據結構，葉子結點會包含所有索引列的值（降低回表查詢概率），且也可以通過最左前綴原則使用聯合索引中的列進行查詢

可以看下這篇文章
MySQL聯合索引底層數據結構是怎樣的

索引使用原則

針對于數據量較大，且查詢比較頻繁的表建立索引。
針對于常作為查詢條件（where）、排序（order by）、分組（group by）操作的字段建立索引。
盡量選擇區分度高的列作為索引，盡量建立唯一索引，區分度越高，使用索引的效率越高。
如果是字符串類型的字段，字段的長度較長，可以針對于字段的特點，建立前綴索引。
盡量使用聯合索引，減少單列索引，查詢時，聯合索引很多時候可以覆蓋索引，節省存儲空間，避免回表，提高查詢效率。
要控制索引的數量，索引并不是多多益善，索引越多，維護索引結構的代價也就越大，會影響增刪改的效率。
如果索引列不能存儲NULL值，請在創建表時使用NOT NULL約束它。當優化器知道每列是否包含NULL值時，它可以更好地確定哪個索引最有效地用于查詢。

mysql如何使用索引查詢數據（個人理解）

id為主鍵索引，name為普通索引
WHERE id=1 AND name=‘Alice’
比如說這條語句，篩選了兩個條件，優化器會選擇一個最優索引進行查詢，一般有主鍵值都先選主鍵(主鍵不會產生回表查詢)
在這里插入圖片描述
那么這樣只會篩選出來id符合條件的，如果篩選name也符合條件的呢？
先將第一次篩選出來的結果讀到內存，再在內存中全部掃描過濾（內存過濾）

3.SQL性能分析

SQL執行頻率

MySQL 客戶端連接成功后，通過 how [session|global] status 命令可以提供服務器狀態信息。通過如下指令，可以查看當前數據庫的INSERT、UPDATE、DELETE、SELECT的訪問頻次：

-- session 是查看當前會話 ;-- global 是查詢全局數據 ;SHOW  GLOBAL STATUS LIKE  'Com_______';

在這里插入圖片描述
那么通過查詢SQL的執行頻次，我們就能夠知道當前數據庫到底是增刪改為主，還是查詢為主。那假如說是以查詢為主，我們又該如何定位針對于那些查詢語句進行優化呢？次數我們可以借助于慢查詢日志。

慢查詢日志

慢查詢日志記錄了所有執行時間超過指定參數（long_query_time，單位：秒，默認10秒）的所有SQL語句的日志。

MySQL的慢查詢日志默認沒有開啟，我們可以查看一下系統變量 slow_query_log。
在這里插入圖片描述
如果要開啟慢查詢日志，需要在MySQL的配置文件（my.ini）中配置如下信息：

 # 開啟MySQL慢日志查詢開關
slow_query_log=1# 設置慢日志的時間為2秒，SQL語句執行時間超過2秒，就會視為慢查詢，記錄慢查詢日志
long_query_time=2

配置完畢之后，重新啟動MySQL服務器。
慢查詢日志文件生成，文件名為localhost-slow.log
在慢查詢日志中，只會記錄執行時間超多我們預設時間（2s）的SQL，執行較快的SQL是不會記錄的。

那這樣，通過慢查詢日志，就可以定位出執行效率比較低的SQL，從而有針對性的進行優化。
最下面的就是查詢較慢的于語句
在這里插入圖片描述

profile

profile詳情
show profiles 能夠在做SQL優化時幫助我們了解SQL執行時間具體都耗費到哪里去了。
點擊后會查詢到我們在該回話執行過的sql
然后show profile for query 16（想要查詢sql對應的Query_ID）去具體看各個階段用的時間

自我感覺用處不大，基本上但是exectuing耗時最長，大部分的優化還是直接針對于sql的索引做優化，即用explain查詢執行計劃

在這里插入圖片描述

explain

explain 或者 desc 命令獲取 MySQL 如何執行 select 語句的信息，包括在 select 語句執行過程中表如何連接和連接的順序。

-- 直接在select語句之前加上關鍵字 explain / descEXPLAIN   SELECT   字段列表   FROM   表名   WHERE  條件 ;

在這里插入圖片描述
explain 執行計劃中各個字段的含義

其中type是最重要的字段
解釋一下其中可以出現的類型

NULL 不訪問任何表的查詢

explain select 'A'

system 訪問一張系統表
const 使用主鍵/唯一索引訪問
eq_ref
ref 使用非唯一索引進行訪問時候
range
index 用了索引，但是也遍歷了整個索引樹
all 全表掃描

注：不一定一定按照上面走
如果表里全是create_user=1的數據
explain SELECT * from category where create_user=1;
其Type為ALL,盡管我再create_user創建了index（普通）

Extra是第二重要的字段，展示是否會回表查詢，所以我們查詢字段盡量覆蓋索引，防止回表查詢
rows 用來參考
possible_key、key、key_len也算比較重要的字段