【MySQL】資料庫優化

来源:https://www.cnblogs.com/changwan/p/18224629
-Advertisement-
Play Games

如何對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行。主要用於增加資料庫的存儲容量。例如，根據一定的規則將數據表中的一部分數據存儲到一張數據表中，另一部分存儲到其他數據表中。


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 六、FreeRTOS學習筆記-任務掛起和恢復
    任務的掛起與恢復的API函數介紹 API函數 描述 vTaskSuspend() 掛起任務 vTaskResume() 恢復被掛起的任務 xTaskResumeFromISR() 在中斷中恢復被掛起的任務 1、掛起任務類似暫停，可恢復； 刪除任務，無法恢復 2、恢復是恢復被掛起任務 3、帶FromI ...
  • Linux VXLAN小實驗
    VXLAN在雲網路中應用十分廣泛。本文介紹一種方法在兩台Linux主機之間建立簡單的VXLAN隧道，以供學習、研究之用。 ...
  • golang開發 gorilla websocket的使用
    很多APP都需要主動向用戶推送消息，這就需要用到長連接的服務，即我們通常提到的websocket，同樣也是使用socket服務，通信協議是基本類似的，在go中用的最多的、也是最簡單的socket服務就是gorilla/websocket，它有21.1K的star，足以說明它的受歡迎程度， 它的git ...
  • 記一次基於倒序索引的SQL優化
    本文測試環境為SQLserver2019 背景 某業務流水錶，會基於固定範圍內的業務編號做寫入以及查詢操作，熱數據的量級在億級別，一個典型的查詢是基於業務編碼查詢最新（時間戳）某種狀態的前N條數據 簡化後的表結構如下 create table TestTable01 ( id bigint iden ...
  • LLVM技術在GaussDB等資料庫中的應用
    資料庫引入LLVM之後，可以為具體的查詢生成定製化的機器碼，並儘可能地將數據存儲在CPU的寄存器中進一步加快計算的速度。 ...
  • 深入理解Redis事務、事務異常、樂觀鎖、管道
    Redis事務與MySQL事務 不一樣。 原子性：MySQL有Undo Log機制，支持強原子性，和回滾。Redis只能保證事務內指令可以不被干擾的在同一批次執行，且沒有機制保證全部成功則提交，部分失敗則回滾。 隔離性：MySQL的隔離性指多個事務可以併發執行，MySQL有MVCC機制。而Redis ...
  • 面試必問：MySQL死鎖 是什麼，如何解決？（史上最全）
    MySQL死鎖接觸少，但面試又經常被問到怎麼辦？ 最近有小伙伴在面試的時候，被問了MySQL死鎖，如何解決？ 雖然也回答出來了，但是不夠全面體系化， 所以，小北給大家做一下系統化、體系化的梳理，幫助大家在面試過程中能夠脫穎而出，拿到自己心儀的Offer 插播一條：如果你近期準備面試跳槽，建議在htt ...
  • MySQL 8.0 Reference Manual（讀書筆記61節--Examining Server Thread (Process) Information(2)）
    隨著大數據技術和人工智慧的發展，企業逐漸意識到構建一個集中化的指標管理平臺的必要性。這樣的平臺旨在解決幾個核心問題：首先，確保所有部門都能通過統一的入口提交指標需求，實現需求的透明化管理；其次，建立完善的指標管理體系，涵蓋從需求定義、模型設計、數據集成、開發實施到應用監控的全鏈條；第三，通過自動化和 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類