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