【MySQL】MySQL語句最佳化

changwan發表於2024-05-28
MySql

一、巢狀查詢最佳化

當SLQ語句存在巢狀查詢時，MySLQ會生成臨時表來儲存子查詢的結果資料，外層查詢會從臨時表中讀取資料，待整個查詢完畢後，會刪除臨時表，在這個過程中是非常耗時的。

方案：使用JOIN語句進行聯表查詢來代替，提升查詢效能。

例如這裡查詢t_goods資料表中t_category欄位不在t_goods_category資料表中的資料。

使用巢狀查詢

mysql> explain select * from t_goods where t_category not in (select t_category from t_goods_category)\G;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t_goods
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 35
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: SUBQUERY
        table: t_goods_category
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

使用join聯表查詢

mysql> explain select * from t_goods as goods left join t_goods_category as category on goods.t_category_id = category.id where category.t_category is null \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: goods
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 35
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: category
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
     filtered: 10.00
        Extra: Using where; Using join buffer (hash join)
2 rows in set, 1 warning (0.00 sec)

這裡由於資料量的原因，效果可能不是這麼明顯，但是當查詢量非常大時，這種查詢效率的效果會更加明顯。

二、OR條件語句最佳化

在多個條件使用OR關鍵字進行連線時，只要OR連線的條件中有一個查詢條件沒有使用索引，那麼MySQL就不會使用索引，而是對資料表進行全表掃描。也就是說，在使用OR連線多個查詢條件時，必須每個查詢條件都是索引欄位，MySQL才會使用索引查詢資料。

mysql> show create table t_goods \G;
*************************** 1. row ***************************
       Table: t_goods
Create Table: CREATE TABLE `t_goods` (
  `id` int NOT NULL AUTO_INCREMENT,
  `t_category_id` int DEFAULT NULL,
  `t_category` varchar(30) DEFAULT NULL,
  `t_name` varchar(50) DEFAULT NULL,
  `t_price` decimal(10,2) DEFAULT NULL,
  `t_stock` int DEFAULT NULL,
  `t_upper_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_category_name` (`t_category_id`,`t_name`),
  KEY `category_part` (`t_category`(10)),
  CONSTRAINT `foreign_category` FOREIGN KEY (`t_category_id`) REFERENCES `t_goods_category` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

例如我有一個由t_category_id欄位和t_name構成聯合索引index_category_name

如果這裡我使用And語句條件來進行查詢，那麼這裡會使用聯合索引index_category_name。

但是我如果使用OR語句，那麼將不會使用索引查詢，因為這兩個欄位無法單獨構成索引。

mysql> explain select * from t_goods where t_name = '耳機' or t_category_id = 1 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ALL
possible_keys: index_category_name
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 35
     filtered: 12.57
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

mysql> explain select * from t_goods where t_name = '耳機' and t_category_id = 1 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ref
possible_keys: index_category_name
          key: index_category_name
      key_len: 208
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

三、ORDER BY語句最佳化

在查詢語句中使用ORDER BY排序時，如果排序的欄位不是索引欄位，那麼MySQL會對結果進行filesort檔案排序，filesort是一種在磁碟上執行的排序演算法。如果排序的欄位是索引欄位，那麼會採用索引排序Index，會避免額外的操作，提高查詢的效能。

mysql> explain select id,t_stock from t_goods order by t_stock \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: index
possible_keys: NULL
          key: stock_index
      key_len: 5
          ref: NULL
         rows: 35
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)

這裡將商品以價格進行排序，可以看到這裡的排序演算法為檔案排序Using filesort

現在我們在這個欄位上新增索引

mysql> alter table t_goods add index stock_index(t_stock);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

現在新增索引後，再次執行查詢語句，將商品名以價格進行排序，也就是以欄位t_stock進行排序

mysql> explain select id,t_stock from t_goods order by t_stock \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: index
possible_keys: NULL
          key: stock_index
      key_len: 5
          ref: NULL
         rows: 35
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

可以看到這裡使用了索引排序。如果查詢的欄位不在索引欄位之內，那麼在排序之後依然要進行篩選，所以依然會使用檔案排序。（除主鍵外，因為本身就會使用索引排序）

四、GROUP BY語句最佳化

GROUP BY分組，用於資料查詢時，對子段進行分組。在MySQL8.0之前的版本中，使用GROUP BY進行分組查詢時，預設會根據分組的欄位進行排序，如果該欄位上不存在索引，會進行檔案排序，比較耗費效能。解決方案：可以指定查詢語句透過ORDER BY NULL語句來禁止檔案排序，減少ORDER BY子句帶來的效能消耗。

但是在8.0之後的版本中，沒有這個預設條件。這裡用的是MySQL8.0

mysql> explain select t_category,count(*) from t_goods group by t_category \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 35
     filtered: 100.00
        Extra: Using temporary
1 row in set, 1 warning (0.00 sec)

可以看到這裡沒有使用檔案排序，這樣避免了filesort排序，會顯著提升效能。

五、分頁查詢的最佳化

LIMIT分頁查詢，指定查詢範圍。在使用分頁查詢時，軟如果單純的使用LIMIT m,n語句，MySQL預設需要排序出資料表中的前m+n條資料，然後將前m條資料捨棄，返回m+1到m+n條資料記錄，這樣會非常消耗效能。

1、回表查詢最佳化

例如，按照t_upper_time排序分頁查詢t_goods資料表中的資料

mysql> explain select id,t_price from t_goods order by t_upper_time limit 1,10 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 35
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)

直接使用LIMIT語句進行分頁時，MySQL會進行全表掃描並對查詢的結果資料使用filesort方式進行排序。

現在使用索引分頁並回表查詢資料

mysql> explain select g1.id,g1.t_price from t_goods as g1 inner join (select id from t_goods order by t_upper_time limit 1,10) as g2 on g1.id=g2.id \G;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 11
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: g1
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: g2.id
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: t_goods
   partitions: NULL
         type: index
possible_keys: NULL
          key: t_upper_time_index
      key_len: 6
          ref: NULL
         rows: 11
     filtered: 100.00
        Extra: Using index
3 rows in set, 1 warning (0.00 sec)

這裡使用查詢巢狀，將子查詢的結果集（派生表）作為中間表與g1也就是goods表進行join連線，形成最終表。這裡的三條記錄分別代表步驟。

第一條記錄（最終的子查詢），展示了從執行子查詢到最終進行連線和返回結果的整個過程

第二條記錄（主查詢的一部分），也就是g1表與派生表之間的巢狀查詢，透過id進行條件篩選。

第三條記錄（子查詢），也就是g2派生表，在這個查詢中使用的索引排序並且使用逐漸篩選。

這樣會大大提高查詢的效率。不需要掃瞄全表。

2、記錄資料標識最佳化分頁

簡單來說就是透過id值來查詢資料的索引位置，當資料量特別大時，可以記錄分頁資料的最後一條資料的Iid值，當需要查詢下一頁時，只需要查詢id的值大於記錄的id值的n條資料即可。

例如，這裡的每頁查詢10條記錄，現在我需要查詢第二頁，那麼我只需要將條件設定為id>20即可，向後取10個資料，再次記錄id值，以此類推。

mysql> explain select id,t_price from t_goods force index (t_upper_time_index) where id>20 order by t_upper_time limit 10 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: index
possible_keys: NULL
          key: t_upper_time_index
      key_len: 6
          ref: NULL
         rows: 10
     filtered: 33.33
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

可以看到這樣頁可以大大提高查詢的效能，但是這種最佳化方式有限定條件，必須保證排序欄位的索引不會重複，如果出現了重複，則可能會丟失部分資料。

六、插入資料最佳化

向資料表中插入資料時，如果資料表中存在索引、唯一性校驗時，會影響插入資料的效率。

1、MyISAM資料表插入資料最佳化

1.1、禁用索引

插入大量資料時，資料表中存在索引會降低資料插入的效能，可以在插入資料前禁用索引，待資料插入完畢再開啟索引。

alter table t_table_name disable keys;    #禁用索引
alter table t_table_name enable keys;    #開啟索引

1.2、禁用唯一性檢查

向資料表中插入資料時，對資料進行唯一性檢查也會降低資料的插入效能，和索引一樣，可以在插入資料前禁用唯一性檢查，插入完畢後再開啟。

set unique_checks = 0 ；    #禁用唯一性檢查
set unique_checks = 1 ；    #開啟唯一性檢查

1.3、禁用外來鍵檢查

set foreign_key_checks = 0 ;     #禁用外來鍵檢查
set foreign_key_checks = 1 ;     #開啟外來鍵檢查

1.4、批次插入資料

向資料表中韓插入多條資料時，使用insert語句一次插入多條資料比每次插入一條資料效能要高很多。

INSERT INTO t_goods_category (id, t_category, t_remark) VALUES
(6, '食品', '各種食品和飲料'),
(7, '玩具', '兒童和成人的玩具'),
(8, '傢俱', '家庭和辦公室傢俱'),
(9, '辦公用品', '辦公裝置和文具'),
(10, '寵物用品', '寵物的食品和用品');

1.5、批次匯入資料

使用LOAD DATA INFILE 語句向資料表中匯入資料比使用INSERT語句的效能高，適用於需要批次匯入的資料量很大時。

LOAD DATA INFILE 'data_file_path' into table table_name;

2、InnoDB資料表插入資料最佳化

這裡，MyISAM中適用的最佳化，在這裡也適用。但是需要注意的是，在批次匯入資料時，被匯入的檔案中的資料記錄最好是按主鍵順序排列，這樣可以提高匯入資料的效率。

除此之外，InnoDB是支援事務的，可以在插入資料之前禁用MySQL自動提交事務，待插入資料完成後，在開啟事務的自動提交，這樣可以提高資料的插入效能。

set autocommit = 0 ;    #禁用事務自動提交
set autocommit = 1 ;    #開啟事務自動提交

七、刪除資料的最佳化

如果資料表是分割槽表，刪除資料表中的資料時，如果待刪除的資料正好是資料表中某個分割槽的所有資料，這樣可以直接刪除分割槽，比DELETE語句刪除資料效能要高很多。

alter table table_name drop partition partition_name;    #刪除分割槽

注意，透過刪除分割槽的方式刪除資料只適用於range、list、range columns、list columns分割槽。

相關文章