SQL語句最佳化

hjq_hh發表於2024-07-06
SQL

SQL語句最佳化

  • 插入資料最佳化

    • 批次插入

      注：透過測試，若插入一千萬條資料，不使用批次插入，每條一個insert需要耗費時間10分鐘左右，但若是透過批次插入耗費時間為十幾秒左右 ，由此可見使用批次插入可以極大的提高效能，同時減少多次訪問資料庫對資料庫造成的壓力。

      注：若在springboot或springcloud中使用批次插入，需要在寫連線到資料庫的配置時將批次插入的開關開啟，否則即使你在使用 insert in() 時也不會使用批次插入。

      如果一次性需要插入大量資料，使用insert語句插入效能較低，此時可以使用MySQL資料庫提供的load命令進行插入。

      前提：本地需要插入資料的檔案中檔案的內容需要符合規範。每個欄位使用逗號分隔。（這裡的逗號可以是別的符號，但必須保持一致）

      在資料庫管理工具中使用批次插入的語句：

      #客戶端連線服務端時，加上引數 --local-infile
mysql --local-infile -u root -p
#設定全域性引數local——infile為1，開啟從本地載入檔案匯入資料的開關
set global local_infile = 1;
#執行load指令將準備好的資料，載入到表結構中
load data local infile '/rooot/sql1.log' （檔名） into table 'tb_user'（表名） fileds terminated by '，'(設定欄位間分割符) lines terminated by '\n'；（設定換行符號）

    • 手動提交事務

    若不手動控制事務，每條sql語句都會是一個單獨的事務，降低效能，而開啟手動後可以多條sql語句一起提交，最佳化效能。

    • 主鍵順序插入（與b+tree結構有關，涉及到頁分裂）

  • 主鍵最佳化

    • 資料組織方式

      在InnoDB引擎中，表資料都是根據主鍵順序組織存放的，這種儲存方式的表稱為索組織表。（想想B+Tree底層葉子結點結構，

      之前的文章裡有，也可去百度B+Tree結構模型）

      所以建議主鍵儲存時使用自增，在符合要求的情況下，儘量降低主鍵的長度，且使用自增，不要使用UUID隨機生成，儘量避免產生頁分裂。

  • order by 最佳化

    1. Using filesort：透過表的索引或全表掃描，讀取滿足條件的資料行，然後在排序緩衝區sort buffer中完成排序操作，所有不是透過索引直接返回排序結果的排序都叫做FileSort排序。該方法進行排序效能相對較低。

    2. Using index：透過有序索引順序掃描直接返回有序資料，這種情況即為using index，不需要額外排序，操作效率高。

      所以我們在進行最佳化時儘量使進行排序時使用Using index

      例：

      #沒有建立索引時
select id,age,phone from tb_user order by age,phone （Using filesort）
#建立索引
create index idx_user_age_phone_aa on tb_user（age,phone)
#建立索引後，根據age，phone進行升序排序
select id,age,phone from tb_user order by age,phone （Using index)
#建立索引後，根據age，phone進行降序排序
select id，age,phone from tb_user order by age desc,phone desc (Using index)
#根據age，phone進行排序，一個使用升序，一個使用降序
select id，age,phone from tb_user order by age asc,phone desc； （Using filesort)
#建立索引
create index idx_user_age_phone_ad on tb_user（age asc,phone desc); (建立索引時age根據升序建立，phone根據降序建立)
#根據age，phone進行排序，一個降序，一個升序
select id，age,phone from tb_user order by age asc,phone desc； (Using index)
      • 根據排序欄位建立合適的索引，多欄位排序時，也遵循最左字首法則。（前面的文章有介紹最左字首法則）
      • 儘量使用覆蓋索引。
      • 多欄位排序，一個升序一個降序，此時需要注意聯合索引在建立時的規則（ASC/DESC)
      • 如果不可避免的出現filesort，大量資料排序時，可以適當增加排序緩衝區大小sort_buffer_size(預設256k)。

  • group by 最佳化

    #執行分組 操作，根據profession欄位進行排序
select profession，count（*） from tb_user group by profession (Using temporary 使用臨時表，效能低)
#建立索引
create index idx_user_pro_age_sta on tb_user(profession,age,status);
#執行分組操作，根據profession欄位分組
select profession，count(*) from tb_user group by profession （Using index）
#執行分組操作，根據age欄位分組
select age，count(*) from tb_user group by age (Using temporary 不滿足最左字首法則)
#執行分組操作，根據profession進行篩選，age欄位排序
select age，count(*) from tb_user where profession = ‘軟體工程’ group by age；（Using index 滿足最左字首法則）
    • 在分組操作時，可以透過索引來提高效率。
    • 分組操作時，索引的使用也是滿足最左字首法則的。

  • limit 最佳化

    • 一個常見又非常頭疼的問題就是 limit 2000000,10，此時需要MySQL排序前2000010記錄，僅僅返回2000000-2000010的記錄，其他記錄丟棄，查詢排序的代價非常大。

    • 透過覆蓋索引加子查詢的方式最佳化：

      例：

      select * from tb_sku t,(select id from tb_sku order by id limit 2000000,10) a where t.id = a.id
#此條sql語句如果看不懂可以先了解下多表查詢中的子查詢和自連線查詢

  • count 最佳化

    • MyISAM引擎把一個表的總行數存在了磁碟上，因此執行count(*)的時候會直接返回這個數，效率很高；

    • InnoDB引擎就很麻煩了，他執行count(*)的時候，需需要把資料一行一行的從引擎裡面讀出來，然後累積計數。

    • 最佳化思路：自己計數

    • count的幾種用法：

      • count是一個聚合函式，對於返回的結果集，一行行地判斷，如果count函式的引數不是NULL，累計值就加一，否則不加，最後返回累計值

      • 用法：count（*）、count(主鍵)、count（欄位）、count(1)

      • count(主鍵)： InnoDB引擎會遍歷整張表，把每一行的主鍵id取出來，返回給服務層。服務層拿到主鍵後，直接按行進行累加（主鍵不可能為null）。

      • count（欄位）：

        • 沒有NOT null約束：InnoDB引擎會遍歷整張表把每一行的欄位都取出來，返回給服務層，服務層判斷是否為null，不為null，計數累加。
        • 有NOT null 約束：InnoDB引擎會遍歷整張表把每一行的欄位值都取出來，返回給服務層，直接進行累加。

      • count（1）：InnoDB引擎遍歷整張表，但不取值。服務層對於返回的每一行，放一個數字1進去，直接按行進行累加。

      • count(*)：InnoDB引擎並不會把全部欄位取出來，而是專門做了最佳化，不取值，服務層直接按行進行累加。

        所以效能：count(欄位)<count(主鍵)<count(1)≈count(*)

  • update 最佳化

    update student set no='20000100' where id = 1 #id為主鍵，必然有索引，所以加的是行鎖

    update student set no='199999987' where name = '韋一笑' #（若是name沒有設定索引，那麼在修改時會加表鎖，降低效能）

    InnoDB的行鎖是針對索引加的鎖，不是針對記錄加的鎖，並且該索引不能失效，否則會從行鎖升級為表鎖

相關文章