MYSQL 高級-有解無憂

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);

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/286057.html

標籤：MySQL

上一篇：MySQL中InnoDB存盤引擎的實作和運行原理

下一篇：MySQL資料庫基礎回顧【詳細】