MYSQL 高級

2021 年 6 月 2 日
筆記

SQL查詢流程:

1. 通過客戶端/服務器通信協議與 MySQL 建立連接
2. 查詢緩存，這是 MySQL 的一個可優化查詢的地方，如果開啟了 Query Cache 且在查詢緩存過程中查詢到完全相同的 SQL 語句，則將查詢結果直接返回給客戶端；如果沒有開啟Query Cache 或者沒有查詢到完全相同的 SQL 語句則會由解析器進行語法語義解析，並生成解析樹。
3. 預處理器生成新的解析樹。
4. 查詢優化器生成執行計劃。
5. 查詢執行引擎執行 SQL 語句，此時查詢執行引擎會根據 SQL 語句中表的存儲引擎類型，以及對應的 API 接口與底層存儲引擎緩存或者物理文件的交互情況，得到查詢結果，由MySQL Server 過濾後將查詢結果緩存並返回給客戶端。若開啟了 Query Cache，這時也會將SQL 語句和結果完整地保存到 Query Cache 中，以後若有相同的 SQL 語句執行則直接返回結果。

MySQL物理文件:

日誌文件:

error log 錯誤日誌排錯 /var/log/mysqld.log【默認開啟】
bin log 二進制日誌備份增量備份 DDL DML DCL
Relay log 中繼日誌複製接收 replication master
slow log 慢查詢日誌調優查詢時間超過指定值

-- 查看錯誤日誌文件路徑
show variables like 'log_error';
+---------------+---------------------+
| Variable_name | Value |
+---------------+---------------------+
| log_error | /var/log/mysqld.log |
+---------------+---------------------+

-- 慢查詢日誌文件路徑
show variables like 'slow_query_log_file';
+---------------------+-----------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------+
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
-- bin log 日誌文件 需要在 my.cnf 中配置
log-bin=/var/log/mysql-bin/bin.log
server-id=2
-- 查看 relay log 相關參數
show variables like '%relay%'

配置文件&數據文件:

配置文件 my.cnf:

　　在 my.cnf 文件中可以進行一些參數設置, 對數據庫進行調優。

[client] #客戶端設置，即客戶端默認的連接參數
port = 3307 #默認連接端口
socket = /data/mysqldata/3307/mysql.sock #用於本地連接的socket套接字
default-character-set = utf8mb4 #編碼
[mysqld] #服務端基本設置
port = 3307 MySQL監聽端口
socket = /data/mysqldata/3307/mysql.sock #為MySQL客戶端程序和服務器之間的本地通訊指定一
個套接字文件
pid-file = /data/mysqldata/3307/mysql.pid #pid文件所在目錄
basedir = /usr/local/mysql-5.7.11 #使用該目錄作為根目錄（安裝目錄）
datadir = /data/mysqldata/3307/data #數據文件存放的目錄
tmpdir = /data/mysqldata/3307/tmp #MySQL存放臨時文件的目錄
character_set_server = utf8mb4 #服務端默認編碼（數據庫級別）

-- 查看數據文件的位置
show variables like '%dir%';
+-----------------------------------------+----------------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------------+
| datadir | /var/lib/mysql/ |
+-----------------------------------------+----------------------------

1、.frm文件
不論是什麼存儲引擎，每一個表都會有一個以表名命名的.frm文件，與表相關的元數據（meta)信息都存放在
此文件中，包括表結構的定義信息等。
2、.MYD文件
myisam存儲引擎專用，存放myisam表的數據（data)。每一個myisam表都會有一個.MYD文件與之呼應，同
樣存放在所屬數據庫的目錄下
3、.MYI文件
也是myisam存儲引擎專用，存放myisam表的索引相關信息。每一個myisam表對應一個.MYI文件，其存放的
位置和.frm及.MYD一樣
4、.ibd文件
存放innoDB的數據文件（包括索引）。
5. db.opt文件 此文件在每一個自建的庫里都會有，記錄這個庫的默認使用的字符集和校驗規。

MySQL查詢和慢查詢日誌分析:

　　等待時間長:

1.鎖表導致查詢一直處於等待狀態，後續我們從MySQL鎖的機制去分析SQL執行的原理

　　執行時間長:

1.查詢語句寫的爛
2.索引失效
3.關聯查詢太多join
4.服務器調優及各個參數的設置

需要遵守的優化原則:

第一條: 只返回需要的結果

　　　　一定要為查詢語句指定 WHERE 條件，過濾掉不需要的數據行

　　　　避免使用 select * from , 因為它表示查詢表中的所有字段

第二條: 確保查詢使用了正確的索引

經常出現在 WHERE 條件中的字段建立索引,可以避免全表掃描；

將 ORDER BY 排序的字段加入到索引中，可以避免額外的排序操作；

多表連接查詢的關聯字段建立索引，可以提高連接查詢的性能；

將 GROUP BY 分組操作字段加入到索引中，可以利用索引完成分組。

第三條: 避免讓索引失效

在 WHERE 子句中對索引字段進行表達式運算或者使用函數都會導致索引失效

使用 LIKE 匹配時，如果通配符出現在左側無法使用索引

如果 WHERE 條件中的字段上創建了索引，盡量設置為 NOT NULL

SQL的執行順序:

　　我們寫的sql:

sql的執行順序:

MYSQL的7種join:

慢查詢日誌分析:

　 MySQL的慢查詢，全名是慢查詢日誌，是MySQL提供的一種日誌記錄，用來記錄在MySQL中響應時間超過閾值的語句。

　默認情況下，MySQL數據庫並不啟動慢查詢日誌，需要手動來設置這個參數。

如果不是調優需要的話，一般不建議啟動該參數，因為開啟慢查詢日誌會或多或少帶來一定的性能影響。

慢查詢日誌支持將日誌記錄寫入文件和數據庫表。

SHOW VARIABLES LIKE "%query%" ;

slow_query_log：是否開啟慢查詢日誌， 1 表示開啟， 0 表示關閉。
slow-query-log-file：新版（5.6及以上版本）MySQL數據庫慢查詢日誌存儲路徑。
long_query_time： 慢查詢閾值，當查詢時間多於設定的閾值時，記錄日誌。

默認情況下slow_query_log的值為OFF，表示慢查詢日誌是禁用的

mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-----------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+

可以通過設置slow_query_log的值來開啟

mysql> set global slow_query_log=1;
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-----------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+

　　使用 set global slow_query_log=1 開啟了慢查詢日誌只對當前數據庫生效，MySQL重啟後則會失效。

　　如果要永久生效，就必須修改配置文件my.cnf（其它系統變量也是如此）

-- 編輯配置
vim /etc/my.cnf
-- 添加如下內容
slow_query_log =1
slow_query_log_file=/var/lib/mysql/lagou-slow.log
-- 重啟MySQL
service mysqld restart
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/lagou-slow.log |
+---------------------+-------------------------------+

那麼開啟了慢查詢日誌後，什麼樣的SQL才會記錄到慢查詢日誌裏面呢？

這個是由參數 long_query_time 控制，默認情況下long_query_time的值為10秒

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

注意：使用命令 set global long_query_time=1 修改後，需要重新連接或新開一個會話才能看到修改值。

mysql> set global long_query_time=1;

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+

log_output 參數是指定日誌的存儲方式。 log_output=’FILE’ 表示將日誌存入文件，默認值是’FILE’。

log_output=’TABLE’ 表示將日誌存入數據庫，這樣日誌信息就會被寫入到 mysql.slow_log 表中。

mysql> SHOW VARIABLES LIKE '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+

系統變量 log-queries-not-using-indexes ：未使用索引的查詢也被記錄到慢查詢日誌中（可選項）。如果調優的話，建議開啟這個選項。

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF |


mysql> set global log_queries_not_using_indexes=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'log_queries_not_using_indexes';

+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON |
+-------------------------------+-------+
1 row in set (0.00 sec)

MySQL存儲引擎:

InnoDB(推薦):

　　優點:

Innodb引擎提供了對數據庫ACID事務的支持，並且實現了SQL標準的四種隔離級別
支持多版本並發控制的行級鎖，由於鎖粒度小，寫操作和更新操作並發高、速度快。
支持自增長列。
支持外鍵。
適合於大容量數據庫系統，支持自動災難恢復。

缺點:

它沒有保存表的行數，當SELECT COUNT(*) FROM TABLE時需要掃描全表

應用場景 :

當需要使用數據庫事務時，該引擎當然是首選。由於鎖的粒度更小，寫操作不會鎖定全表，所以在並發較高時，使用Innodb引擎會提升效率
更新密集的表, InnoDB存儲引擎特別適合處理多重並發的更新請求

MyISAM:

優點:

MyISAM存儲引擎在查詢大量數據時非常迅速，這是它最突出的優點
另外進行大批量插入操作時執行速度也比較快。

缺點:

MyISAM表沒有提供對數據庫事務的支持。
不支持行級鎖和外鍵。
不適合用於經常UPDATE（更新）的表，效率低。

應用場景:

以讀為主的業務，例如：圖片信息數據庫，博客數據庫，商品庫等業務。
對數據一致性要求不是非常高的業務（不支持事務）
硬件資源比較差的機器可以用 MyiSAM （佔用資源少）

MySQL索引優化:

普通索引:

CREATE INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名);
CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名) );

唯一索引:

CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);
CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (字段名) ;

主鍵索引:

CREATE TABLE tablename ( [...], PRIMARY KEY (字段名) );
ALTER TABLE tablename ADD PRIMARY KEY (字段名);

複合索引:

　　用戶可以在多個列上建立索引，這種索引叫做組複合索引（組合索引）。複合索引可以代替多個單一索引，相比多個單一索引複合索引所需的開銷更小。

CREATE INDEX <索引的名字> ON tablename (字段名1，字段名2...);
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1，字段名2...);
CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名1，字段名2...) );

複合索引注意事項:

1. 何時使用複合索引，要根據where條件建索引，注意不要過多使用索引，過多使用會對更新操作效率有很大影響。
2. 如果表已經建立了(col1，col2)，就沒有必要再單獨建立（col1）；如果現在有(col1)索引，如果查詢需要col1和col2條件，可以建立(col1,col2)複合索引，對於查詢有一定提高。

全文索引:

　　查詢操作在數據量比較少時，可以使用like模糊查詢，但是對於大量的文本數據檢索，效率很低。如果使用全文索引，查詢速度會比like快很多倍。

CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);
CREATE TABLE tablename ( [...], FULLTEXT KEY [索引的名字] (字段名) ;

和常用的like模糊查詢不同，全文索引有自己的語法格式，使用 match 和 against 關鍵字，比如:

SELECT * FROM users3 WHERE MATCH(NAME) AGAINST('aabb');
-- * 表示通配符,只能在詞的後面
SELECT * FROM users3 WHERE MATCH(NAME) AGAINST('aa*' IN BOOLEAN MODE);

全文索引使用注意事項：

全文索引必須在字符串、文本字段上建立。
全文索引字段值必須在最小字符和最大字符之間的才會有效。（innodb：3-84； myisam：4-84）

創建索引的原則:

在經常需要搜索的列上創建索引，可以加快搜索的速度；
在作為主鍵的列上創建索引，強制該列的唯一性和組織表中數據的排列結構；
在經常用在連接的列上，這些列主要是一些外鍵，可以加快連接的速度；
在經常需要根據範圍進行搜索的列上創建索引，因為索引已經排序，其指定的範圍是連續的；
在經常需要排序的列上創建索引，因為索引已經排序，這樣查詢可以利用索引的排序，加快排序查詢時間；
在經常使用在WHERE子句中的列上面創建索引，加快條件的判斷速度。
group by字段

索引原理:

　　hash 結構:

　　　　Hash底層實現是由Hash表來實現的，是根據鍵值 <key,value> 存儲數據的結構。非常適合根據 key查找value值，也就是單個key查詢，或者說等值查詢。

　　B+Tree結構

　　　　非葉子節點不存儲data數據，只存儲索引值，這樣便於存儲更多的索引值葉子節點包含了所有的索引值和data數據葉子節點用指針連接，提高區間的訪問性能

EXPLAIN性能分析:

id:

　　id相同，執行順序由上至下

　　id不同，如果是子查詢，id的序號會遞增，id值越大優先級越高，越先被執行

select_type:

　　simple : 簡單的select查詢，查詢中不包含子查詢或者UNION

　　primary : 查詢中若包含任何複雜的子部分，最外層查詢被標記

　　subquery : 在select或where列表中包含了子查詢

union : 如果第二個select出現在UNION之後，則被標記為UNION，如果union包含在from子句的子查詢中，外層select被標記為derived

type介紹:

　　type顯示的是連接類型，是較為重要的一個指標。

　　下面給出各種連接類型,按照從最佳類型到最壞類型進行排序:

　　　　system > const > eq_ref > ref > range > index > ALL

system : 表僅有一行 (等於系統表)。這是const連接類型的一個特例,很少出現。

const : 表示通過索引一次就找到了, const用於比較 primary key 或者 unique 索引. 因為只匹配一行數據,所以如果將主鍵放在 where條件中, MySQL就能將該查詢轉換為一個常量

eq_ref : 唯一性索引掃描,對於每個索引鍵,表中只有一條記錄與之匹配. 常見與主鍵或唯一索引掃描

ref : 非唯一性索引掃描, 返回匹配某個單獨值的所有行, 本質上也是一種索引訪問, 它返回所有匹配某個單獨值的行, 這是比較常見連接類型.

range : 只檢索給定範圍的行,使用一個索引來選擇行。

index : 出現index 是 SQL 使用了索引, 但是沒有通過索引進行過濾,一般是使用了索引進行排序分組

ALL : 對於每個來自於先前的表的行組合,進行完整的表掃描。

possible_keys:顯示可能應用到這張表上的索引, 一個或者多個. 查詢涉及到的字段上若存在索引, 則該索引將被列出, 但不一定被查詢實際使用

key :實際使用的索引

key_len介紹:

表示索引中使用的位元組數, 可以通過該列計算查詢中使用索引的長度.

CREATE TABLE T1(
a INT PRIMARY KEY,
b INT NOT NULL,
c INT DEFAULT NULL,
d CHAR(10) NOT NULL
);

EXPLAIN SELECT * FROM T1 WHERE a > 1 AND b = 1;

ALTER TABLE T1 ADD INDEX idx_b(b);

ALTER TABLE T1 ADD INDEX idx_d(d);

MYSQL 高級

SQL查詢流程:

MySQL物理文件:

配置文件&數據文件:

MySQL查詢和慢查詢日誌分析:

需要遵守的優化原則:

SQL的執行順序:

MYSQL的7種join:

慢查詢日誌分析:

MySQL存儲引擎:

MyISAM:

MySQL索引優化:

索引原理:

EXPLAIN性能分析:

id:

select_type:

type介紹:

VirMach 便宜 VPS

QNews

MYSQL 高級

SQL查詢流程:

MySQL物理文件:

配置文件&數據文件:

MySQL查詢和慢查詢日誌分析:

需要遵守的優化原則:

SQL的執行順序:

MYSQL的7種join:

慢查詢日誌分析:

MySQL存儲引擎:

MyISAM:

MySQL索引優化:

索引原理:

EXPLAIN性能分析:

id:

select_type:

type介紹:

分享此文：

Related Posts

Sentinel上下文創建及執行

分佈式數據庫排序及優化

相鄰兩個生產計劃之間的銜接問題

《孤島危機》重製版三部曲公布：畫質驚艷

VirMach 便宜 VPS

QNews

熱門文章

熱門搜尋