【MySQL】資料庫最佳化

changwan發表於2024-05-31
MySql資料庫

一、最佳化資料型別

在MySQL中不同的資料型別長度不同，在磁碟上所需要的儲存空間也不同，如果資料庫中使用不合理的資料型別，會造成很大的空間浪費，並且在資料插入與讀取時，也會造成MySQL的效能低下。

  • 更小的資料型別更好

如果沒有特殊情況，儘量使用可以正確儲存資料的最小資料型別，因為更小的資料型別在插入和讀取資料時更快，佔用的記憶體更小，CPU處理的週期也會更短。

  • 使用簡單的資料型別

在設計資料表時，儘量為欄位設計簡單的資料型別。例如能使用整型就不要使用字串型別，因為字串型別的比較規則更復雜，需要將字串轉化為ANSI碼後再進行比較。

  • 避免使用NULL

在沒有特殊情況下，儘量將欄位的型別限制為NOT NULL。軟功欄位允許為NULL，會使得索引、插入與更新資料變得複雜。因為在可以為NULL的列建立索引時，在使用索引時，每個索引記錄都會使用一個額外的空間來記錄索引列是否為NULL，並且在InnoDB儲存引擎中，需要單獨使用一個位元組的儲存空間來儲存NULL值。在實際情況中可以設定預設值，例如為“”、0等。

二、刪除重複索引和冗餘索引

重複索引：索引名稱不同，索引欄位相同

冗餘索引：索引最左邊的部分列是重複的

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)),
  KEY `stock_index` (`t_stock`),
  KEY `t_upper_time_index` (`t_upper_time`),
  KEY `name_index` (`t_name`),
  KEY `category_name_index` (`t_category`,`t_name`),
  KEY `category_name_index2` (`t_category`,`t_name`),
  KEY `name_stock_index` (`t_name`,`t_stock`),
  KEY `category_name_index3` (`t_category` DESC,`t_name`),
  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.01 sec)

例如在這張資料表中，category_name_index和category_name_index2索引是重複索引。這兩個索引的欄位完全相同。name_index和name_stock_index索引是冗餘索引，因為name_stock_index索引中包含了name_index索引的欄位。為什麼category_name_index3不是重複索引呢，因為category_name_index3索引的t_category欄位的順序不同。

三、反正規化設計

資料庫設計中三大正規化要求儘可能減少冗餘欄位，使資料庫設計看起來更簡單、優雅。

但是完全的遵循資料庫的三大正規化來設計資料庫，會導致很多表之間產生很多的依賴關係，規範越高，表之間的依賴關係越多這樣會導致在查詢資料時，資料表之間的頻繁連線，造成資料查詢的效能低下。

在實際情況下，對於查詢較多的夏天來說，應根據實際業務對資料庫進行反正規化化設計，適當的增加冗餘欄位，提高資料的查詢效率。

需要注意的是，在增加冗餘欄位時，需要考慮資料的一致性問題，也就是說，當資料表A中的某個欄位發生變化時，對應資料表B中也應該將相應的資料修改。

四、增加中間表

如果資料庫中存在經常需要關聯查詢的資料表，則可以為關聯查詢的資料表建立一箇中間表，中間表中儲存多個資料表關聯查詢的結果資料，將對多個資料表的關聯查詢轉化為對中間表的查詢，提高查詢效率。

例如建立部門表和員工表

create table t_department(
id int not null primary key auto_increment,
name varchar(30) not null default ""
);

create table t_employee(
id int not null primary key auto_increment,
name varchar(30) not null default "",
join_data DATE,
bobby varchar(100),
department int not null
)；

t_employee資料表透過department欄位與t_department資料表之間進行關聯。

使用聯表查詢

mysql> explain select e.name as employee_name,d.name as department_name from t_employee e left join t_department d on e.department=d.id \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: e
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: d
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: goods.e.department
         rows: 1
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.01 sec)

建立中間表，儲存連線查詢的資訊

create table t_employee_tmp(
employee_id int not null,
employee_name varchar(30),
department_name varchar(30)
);

將聯表查詢資訊匯入中間表

insert into t_employee_tmp
(employee_id,employee_name,department_name)
select e.id as employee_id,e.name as employee_name,d.name as department_name
from t_employee as e left join t_department as d
on e.department =d.id;

查詢中間表中的資料集

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

這時候只需要查詢中間表的資料就可以了，不需要再進行聯表查詢，並且如果在中間表的查詢中，適當新增索引，會更明顯的提升效率。

五、分析資料表

當使用ANALYZE TAVLE來分析資料表時，MYSQL會自動為資料表新增一個只讀的鎖，此時，只能對資料表中的資料進行讀取操作而不能進行寫入和更新操作。

mysql> analyze table  t_goods;
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| goods.t_goods | analyze | status   | OK       |
+---------------+---------+----------+----------+
1 row in set (0.05 sec)

mysql> analyze table  t_goods \G;
*************************** 1. row ***************************
   Table: goods.t_goods
      Op: analyze
Msg_type: status
Msg_text: OK
1 row in set (0.01 sec)
Table 當前分析的資料表的名稱
Op 當前執行的操作
Msg_type 輸出結果資訊的型別，包括status(狀態)、info(資訊)、note(注意)、warning(警告)、erroe(錯誤)
Msg_test 結果資訊

六、檢查資料表

當使用CHECK TABLE語句檢查資料表時，MySQL會自動為資料表新增讀鎖。

 check table t_goods\G；
+-------------+-------+----------+-----------------------------------+
| Table       | Op    | Msg_type | Msg_text                          |
+-------------+-------+----------+-----------------------------------+
| goods.goods | check | Error    | Table 'goods.goods' doesn't exist |
| goods.goods | check | status   | Operation failed                  |
+-------------+-------+----------+-----------------------------------+
2 rows in set (0.02 sec)

mysql> check table t_goods\G
*************************** 1. row ***************************
   Table: goods.t_goods
      Op: check
Msg_type: status
Msg_text: OK
1 row in set (0.01 sec)

七、最佳化資料表

OPTIMIZE TABLE語句主要用來最佳化刪除和更新資料造成的檔案碎片。使用時，會自動新增讀鎖。

mysql> optimize table t_goods \G;
*************************** 1. row ***************************
   Table: goods.t_goods
      Op: optimize
Msg_type: note
Msg_text: Table does not support optimize, doing recreate + analyze instead
*************************** 2. row ***************************
   Table: goods.t_goods
      Op: optimize
Msg_type: status
Msg_text: OK
2 rows in set (0.13 sec)

注意，只能最佳化資料表中的Varchar、Blob或Text型別欄位。

八、拆分資料表

如果一個表的欄位數量比較多，某些欄位的查詢效率非常低。這樣的欄位在資料量非常大時，會嚴重影響資料表的效能，可以將這些欄位分離出來形成新的表。

1、垂直拆分

mysql> show create table t_user \G;
*************************** 1. row ***************************
       Table: t_user
Create Table: CREATE TABLE `t_user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(30) DEFAULT NULL,
  `password` varchar(64) DEFAULT NULL,
  `phone` varchar(14) DEFAULT NULL,
  `address` varchar(200) DEFAULT NULL,
  `hobby` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

從分析可以看到，其中最常使用的是username和pasword，其他欄位資料查詢的頻率非常低，此時可以將表拆分為兩個表t_user、t_user_detail。

mysql> show create table t_user_puls \G;
*************************** 1. row ***************************
       Table: t_user_puls
Create Table: CREATE TABLE `t_user_puls` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(30) DEFAULT NULL,
  `password` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)


mysql> show create table t_user_detail \G;
*************************** 1. row ***************************
       Table: t_user_detail
Create Table: CREATE TABLE `t_user_detail` (
  `user_id` int NOT NULL,
  `phone` varchar(14) DEFAULT NULL,
  `address` varchar(200) DEFAULT NULL,
  `hobby` varchar(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

這裡使用索引欄位將兩個表進行關聯，如果只需要查詢使用者名稱和密碼，就可以大大提高效率。

2、水平拆分

主要拆分的資料。例如將10行資料拆分為5行5行。主要用於增加資料庫的儲存容量。例如，根據一定的規則將資料表中的一部分資料儲存到一張資料表中，另一部分儲存到其他資料表中。

相關文章