mysql總結:索引,儲存引擎,大批量資料插入,事務,鎖

趕星而至發表於2022-03-08
MySql索引儲存引擎

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

  • 減少事務,批量執行資料

相關文章