mysql總結
索引概述:
-
索引是高效獲取資料的資料結構
索引結構:
-
B+Tree()
-
Hash(不支援範圍查詢,精準匹配效率極高)
儲存引擎:
常見儲存引擎:
-
Myisam:5.5之前預設引擎,支援表鎖,不支援外來鍵和事務,查詢插入效能很高
-
InnoDB:支援事務,外來鍵,支援行級鎖,5.5之後預設儲存引擎,5.6之後支援全文索引
-
Memory:所有資料置於記憶體中,擁有極高的效率,但是重啟資料會丟失
-
Archive:擁有很快的插入速度,但是查詢相對差勁
-
Federated:將不同的mysql伺服器聯合,邏輯形成一個完整的資料庫,適合分散式場景
樹的區別:
-
二叉樹:可能產生不平衡,順序資料可能會出現連結串列結構
-
平衡二叉樹:插入需要自旋,效能根據層級而定,效能不穩定
-
b+tree:
-
主鍵聚簇葉子節點存放資料,非葉子節點存放索引,
-
二級索引非葉子節點存放索引,葉子節點存放主鍵
-
索引優缺點:
優點:
-
大大加快查詢速度
-
使用分組和排序時候可以顯著減少分組和排序時間
-
唯一索引可以保證欄位唯一
-
可以加速表與表之間的連線
缺點
-
建立和維護索引需要消耗時間,隨著資料量增加時間也會增加
-
佔用磁碟空間
-
對錶進行urd操作時候也要動態維護,urd效能會下降
建立索引原則(我們對哪種資料建立索引):
-
更新頻繁資料不易建立索引
-
資料量少的沒必要建立,全表和用索引可能差不多
-
首先考慮在where和orderby欄位建立索引
索引分類:
單列索引(只包含單個列):
-
主鍵索引:唯一且不為null,一個表只能有一個,(聚集索引:葉子節點下儲存資料)
-
唯一索引:唯一且只能有一個Null值(二級索引,葉子節點儲存主鍵)
-
普通索引:沒有限制(二級索引,葉子節點儲存主鍵)
組合索引/複合索引(包含多列):
-
為了避免回表,進行更高效的查詢
全文索引:
-
like+%(InnoDB(5.6之後支援)預設3個字元,最大84,MyISam預設4最小1個字元)
空間索引(使用較少)
Sql效能分析
-
資料庫的執行頻次
- show session status like 'Com_____'; --查詢當前會話統計結果
- show global status like 'Com_____'; --查詢字資料庫上次啟動至今的結果
- show status like 'Innodb_rows_%';
-
慢查詢日誌
-- 檢視慢日誌配置資訊
show variables like '%slow_query_log%’;
-- 開啟慢日誌查詢
set global slow_query_log=1;
-- 檢視慢日誌記錄SQL的最低閾值時間
show variables like 'long_query_time%’;
-- 修改慢日誌記錄SQL的最低閾值時間
set global long_query_time=4;
-
profile Sql執行查詢
-
explain/desc執行計劃查詢
-
id 相同表示載入表的順序是從上到下。
-
id 不同id值越大，優先順序越高，越先被執行。
-
id 有相同，也有不同，同時存在。id相同的可以認為是一組，從上往下順序執行；在所有的組中，id的值越大，優先順序越高，越先執行。
-
type含義
-
extra含義
-
索引使用
聯合索引:
-
遵循最左原則,如果最左使用中間跳過了某個欄位,會造成後面索引失效,範圍查詢右側的列會失效,儘量是<= ,>=
索引失效:
-
索引列進行了函式運算
-
沒有遵循最有匹配原則
-
字串型別索引沒有加'',造成隱士轉換,導致索引失效
-
左模糊查詢
-
如果最左使用中間跳過了某個欄位,會造成後面索引失效,範圍查詢右側的列會失效,儘量是<= ,>=
-
mysql優化器判定全表比用索引塊
-
or連結索引失效
sql提示:
-
多個索引下,可以提醒執行器是由哪個索引,建議使用,忽略使用,強制使用
覆蓋索引:
-
查詢返回欄位都在聯合索引中會直接拿到資料,避免回表即聯合索引
字首索引:
-
針對欄位資料庫較大的建立索引,縮小索引長度
單列/聯合索引:
-
避免單列索引在and情況下第二索引不生效,使用聯合索引,使用恰當可避免回表
索引設計原則
-
表層面:資料量大,且查詢頻繁
-
欄位層面:經常在where groupby orderby後的欄位
-
索引層: 唯一的建立唯一索引,儘量聯合索引,大文字儘量字首索引
-
附加原則:
-
區分度較高
-
索引不易過多
-
索引不為null加上非空約束
-
所長度儘量短
-
鎖機制
鎖的分類:
按粒度分:
-
全域性鎖:鎖定全域性,用於資料備份保證資料庫完整性
-
表鎖(加鎖快,併發低,不會死鎖):
-
表鎖:鎖定整張表
-
後設資料鎖:保證資料完整執行,修改的鎖會和所有鎖衝突
-
意向鎖:為了避免加表鎖時候,全域性掃描行鎖
-
-
行鎖(加鎖慢,鎖衝突低,併發高,會死鎖)
-
行鎖:鎖定單行資料
-
間隙鎖:鎖定間隙,不包含當前資料
-
臨鍵鎖:鎖定當前資料和間隙(行鎖+間隙鎖)
-
按型別分:
-
讀鎖(共享):阻塞寫,可讀
-
寫鎖(排他):阻塞讀寫
事務
事務隔離級別:
-
讀未提交:一個事務可以讀取另一個事務未提交的資料(髒讀,不可重複讀,幻讀)
-
讀已提交:可讀取另一個事務已經提交的事務(不可重複讀,幻讀)
-
不可重複讀(預設):事務開啟時不在允許修改操作,可避免髒讀,不可重複讀但是會造成(幻讀)
-
序列化:最高事務隔離級別,效率低下
大批量資料插入優化
-
主鍵順序插入
-
批量插入減少IO,批量最好500左右
-
load載入資料至資料結構
-- 1、首先，檢查一個全域性系統變數 'local_infile' 的狀態， 如果得到如下顯示 Value=OFF，則說明這是不可用的
show global variables like 'local_infile';
-- 2、修改local_infile值為on，開啟local_infile
set global local_infile=1;
-- 3、載入資料
/*
指令碼檔案介紹 :
sql1.log ----> 主鍵有序
sql2.log ----> 主鍵無序
*/
load data local infile 'D:\\sql_data\\sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n';
-
-- 關閉唯一性校驗
SET UNIQUE_CHECKS=0;
truncate table tb_user;
load data local infile 'D:\\sql_data\\sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n';
SET UNIQUE_CHECKS=1;
-
減少事務,批量執行資料