MySQL 數據操縱與數據庫優化

MySQL數據庫的DML

一、創建（Create）

1. 基本語法

INSERT INTO 表名 [(列名1, 列名2, ...)] 
VALUES (值1, 值2, ...);

省略列名條件：當值的順序與表結構完全一致時，可省略列名（需包含所有字段值）

批量插入：單條語句插入多行數據提升效率

INSERT INTO student (id, name, score) VALUES
(1, '張三', 99), (2, '李四', 88), (3, '王五', 77);

2. 高級技巧

自增主鍵處理：使用AUTO_INCREMENT時，無需顯式插入主鍵值
大數據量優化：調整max_allowed_packet參數避免數據包過大錯誤
```
SET GLOBAL max_allowed_packet = 1024*1024*100; -- 擴容至100MB[1][8]
```

核心功能：max_allowed_packet?定義了 MySQL 服務器和客戶端之間傳輸的數據包最大允許大小。當執行大容量插入、更新或查詢時，若數據包超過該限制，會觸發?PacketTooBigException?錯誤。
適用場景：處理大型 BLOB 字段、批量導入數據、數據遷移等需要傳輸大容量數據的操作。
臨時生效：通過?SET GLOBAL?修改的參數僅在當前 MySQL 服務運行期間生效，重啟服務后會恢復為配置文件中的默認值。若需永久生效，需修改配置文件（如?my.cnf?或?my.ini）并重啟服務。
單位限制：
- 在命令行中設置時，只能使用字節數（如?1024*1024*100），不可直接使用?M?或?G?單位。
- 在配置文件中則支持?M/G?單位（如?max_allowed_packet=100M）。
取值范圍：最小值為?1KB，最大值為?1GB（超過會自動調整為?1GB）。

二、讀取（Retrieve）

1. 基礎查詢

全列查詢：SELECT * FROM 表名（需警惕性能問題，建議指定必要字段）

別名設置：增強結果可讀性

SELECT name AS 學生姓名, age+5 AS 修正年齡 FROM student;

2. 聚合函數與分組

核心聚合函數：

SELECT COUNT(*) AS 總人數, AVG(score) AS 平均分,MAX(score) AS 最高分 
FROM exam_result;

COUNT統計行數時推薦使用COUNT(*)，避免NULL值干擾

分組查詢：

SELECT course_id, AVG(grade) 
FROM study 
GROUP BY course_id 
HAVING AVG(grade) > 80; -- HAVING對分組后數據篩選[4][5]

與WHERE區別：WHERE在分組前過濾，HAVING在分組后過濾

3. 子查詢

WHERE子句嵌套：

SELECT name 
FROM student 
WHERE id IN (SELECT student_id FROM study WHERE course_id = 'CS101'
);

FROM子句派生表：

SELECT t.dept_name, avg_salary 
FROM (SELECT dept_id, AVG(salary) AS avg_salary FROM emp GROUP BY dept_id
) t;

4. 多表連接

內連接：僅返回匹配記錄

SELECT s.name, sc.score 
FROM student s 
INNER JOIN study sc ON s.id = sc.student_id;

左外連接：保留左表所有記錄

SELECT s.name, sc.score 
FROM student s 
LEFT JOIN study sc ON s.id = sc.student_id;

三、更新（Update）

1. 基礎語法

UPDATE 表名 
SET 列名1=值1, 列名2=值2 
WHERE 條件; -- 必須指定條件避免全表更新[8][9]

示例：UPDATE emp SET salary=salary*1.1 WHERE dept='研發部';

2. 級聯更新

UPDATE study 
SET grade=grade+5 
WHERE course_id IN (SELECT id FROM course WHERE teacher='張教授'
);

四、刪除（Delete）

1. 條件刪除

DELETE FROM 表名 WHERE 條件; -- 未加條件將清空全表[8]

示例：DELETE FROM log WHERE create_time < '2023-01-01';

2. 高效清空（巧用DDL）

TRUNCATE TABLE student; -- 重置自增主鍵，性能優于DELETE[1][8]

限制：外鍵約束存在時不可用，需先解除約束

五、約束與完整性

1. 主鍵與外鍵

CREATE TABLE student_course (student_id INT REFERENCES student(id) ON DELETE CASCADE,course_id INT REFERENCES course(id),PRIMARY KEY(student_id, course_id) -- 復合主鍵[1][5]
);

級聯操作：ON DELETE CASCADE實現主表刪除時自動清理關聯數據

2. 唯一性與默認值

CREATE TABLE user (id INT AUTO_INCREMENT PRIMARY KEY,email VARCHAR(255) UNIQUE, -- 唯一約束status TINYINT DEFAULT 1 -- 默認值約束[8][9]
);

小結

MySQL CRUD操作是數據庫開發的基礎，掌握其正確使用規則可以提高開發效率和數據安全性。在實際應用中，需要注意以下幾點：

避免頻繁使用SELECT *，盡量指定需要的列。
更新和刪除操作時，務必添加WHERE條件，防止誤操作。
使用TRUNCATE時，確保表中沒有外鍵約束。
合理設計表結構和約束，提高數據的完整性和一致性。

MySQL數據庫優化

一、查詢優化：從 SQL 到索引的全面調優

EXPLAIN 分析查詢（查執行計劃）
```
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';
```
- 關鍵字段解讀：
  - type：ALL（全表掃描，需優化）→ 目標優化到?ref?或?range
  - key：顯示實際使用的索引，若為?NULL?表示未用索引
  - rows：預估掃描行數，數值越大性能越差
  - Extra：出現?Using filesort（額外排序）或?Using temporary（臨時表）需警惕
避免全表掃描的 3 大技巧
- 索引覆蓋：確保 WHERE、JOIN、ORDER BY 涉及的列都有索引
- 函數陷阱：禁止在索引列用函數（如?YEAR(created_at)），改用范圍查詢
```
-- 錯誤：索引失效
SELECT * FROM users WHERE YEAR(created_at) = 2023;
-- 正確：索引生效
SELECT * FROM users WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31';
```
- 模糊查詢優化：避免?LIKE '%abc%'，改用?LIKE 'abc%'（前綴匹配可用索引）
索引優化的黃金法則
- 覆蓋索引：查詢字段全在索引中，無需回表
```
CREATE INDEX idx_email_name ON users(email, name);  -- 聯合索引
SELECT email, name FROM users WHERE email = 'user@example.com';  -- 直接命中索引
```
- 前綴索引：對長文本（如地址）截取前 20 字符建索引，節省空間?
```
CREATE INDEX idx_title_prefix ON articles(title(20));
```
- 索引避坑：
  - 刪除未使用的索引（如單字段索引被聯合索引覆蓋）
  - 聯合索引順序遵循最左前綴原則（(a,b,c)?索引對?a、a,b?生效，對?b,c?無效）

JOIN 與分頁的高效寫法

JOIN 優化：
- 用小表驅動大表（如?FROM 小表 JOIN 大表）
- 確保關聯字段有索引，避免笛卡爾積

分頁優化（百萬級數據場景）：

-- 傳統分頁（慢）：需掃描前 100000 行
SELECT * FROM users LIMIT 100000, 10;
-- 優化方案：通過覆蓋索引跳過偏移量
SELECT * FROM users 
WHERE id >= (SELECT id FROM users ORDER BY id LIMIT 100000, 1)
ORDER BY id LIMIT 10;

二、表結構優化：從設計到存儲的進階

數據類型選擇
- 數值型優先：用?INT?存 IP（INET_ATON()?轉換），而非?VARCHAR
- 避免 NULL：用默認值（如空字符串）替代，減少索引復雜度
- ENUM 妙用：有限值字段（如性別）用 ENUM 比 VARCHAR 更省空間
范式與反范式的平衡
- 范式化（減少冗余）：適合寫多讀少場景（如日志表）
- 反范式化（適當冗余）：讀多寫少場景（如用戶表冗余常用字段）

分區與分庫分表

分區表：按時間切分歷史數據，加速查詢

CREATE TABLE logs (id INT, log_date DATE)
PARTITION BY RANGE (YEAR(log_date)) (PARTITION p2020 VALUES LESS THAN (2021),PARTITION p2023 VALUES LESS THAN (2024)
);

分庫分表：單表超千萬行時用 ShardingSphere 分片

三、服務器參數調優：關鍵配置詳解

InnoDB 核心參數

innodb_buffer_pool_size = 16G  # 物理內存的 70%-80%
innodb_file_per_table = ON  # 每個表獨立表空間

查詢緩存慎用
- 適用場景：讀多寫極少（如靜態配置表）
- 禁用場景：高并發寫入時，緩存頻繁失效反降低性能
```
query_cache_type = 0  # 高寫入場景關閉
```

連接與內存管理

max_connections = 500  # 根據業務負載調整
wait_timeout = 600  # 空閑連接 10 分鐘斷開
tmp_table_size = 64M  # 增大臨時表內存

四、架構優化：高可用與擴展方案

讀寫分離
- 主庫處理寫操作，從庫處理讀請求（用 ProxySQL 路由）
高可用方案
- MHA：自動故障轉移，主庫宕機 30 秒內切換
- Galera Cluster：多主同步，適合寫負載均衡場景
緩存與負載均衡
- Redis 緩存熱點數據：減少數據庫壓力
- HAProxy：均衡讀請求到多個從庫

五、鎖與事務優化：并發控制秘訣

隔離級別選擇
- 默認?REPEATABLE READ?適合多數場景
- 高并發讀寫可用?READ COMMITTED?減少鎖競爭
死鎖監控與處理
```
innodb_print_all_deadlocks = 1  # 記錄死鎖日志
```
- 重試機制：代碼層捕獲死鎖異常后自動重試

六、監控與工具：數據庫的“健康管家”

內置工具
- 慢查詢日志：定位耗時 SQL?
```
slow_query_log = 1
long_query_time = 2  # 記錄超過 2 秒的查詢
```
- SHOW PROCESSLIST：實時查看活躍連接
第三方利器
- Percona Toolkit：分析索引效率與表結構
- Prometheus + Grafana：可視化監控 QPS、連接數等

七、硬件與系統優化：底層性能基石

磁盤與文件系統
- SSD 替代 HDD：隨機讀寫性能提升 10 倍+
- XFS 文件系統：禁用?atime?減少磁盤寫入
```
mount -o noatime,nodiratime /dev/sdb1 /data
```
內核參數調優
- TCP 緩沖區：增大網絡吞吐量
- 文件句柄數：避免?Too many open files?錯誤

八、持續維護：數據庫的“養生之道”

定期維護
- 每月優化碎片化表：OPTIMIZE TABLE large_table;
- 清理歷史數據：分區表直接?DROP PARTITION
避免過度優化
- 二八原則：優先優化 20% 高頻查詢
- 業務優先：架構擴展前評估投資回報率（如分庫分表成本高）