1. 請詳細列舉 30 條常用 SQL 優化方法

• 定義

SQL 優化是指通過對 SQL 語句、數據庫表結構、索引等進行調整和改進，以提高 SQL 查詢的執行效率，減少系統資源消耗，提升數據庫整體性能的一系列操作。

• 要點

從索引運用、查詢語句結構優化、數據庫配置調整等多方面著手，避免全表掃描，降低磁盤 I/O 和 CPU 計算量，減少不必要的數據傳輸。

• 應用

在實際的數據庫開發和維護中，針對不同的業務場景和數據庫性能瓶頸，靈活運用這些優化方法，提升系統的響應速度和穩定性。

• SQL 代碼舉例及說明

1. 合理使用索引

sql

-- 為 users 表的 username 列創建索引
CREATE INDEX idx_username ON users (username);

說明：當經常根據?username?列進行查詢時，該索引可加快查詢速度。

1. 避免在索引列上使用函數

sql

-- 不推薦
SELECT * FROM orders WHERE YEAR(order_date) = 2024;
-- 推薦
SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

說明：在索引列?order_date?上使用?YEAR?函數會使索引失效，而使用范圍查詢可利用索引。

1. 使用覆蓋索引

sql

-- 創建覆蓋索引
CREATE INDEX idx_user_info ON users (user_id, username);
-- 查詢僅使用索引中的信息
SELECT user_id, username FROM users WHERE user_id < 100;

說明：查詢的列都包含在索引中，無需回表查詢數據行，提高查詢效率。

1. 優化?LIKE?查詢

sql

-- 不推薦
SELECT * FROM products WHERE product_name LIKE '%keyword';
-- 推薦
SELECT * FROM products WHERE product_name LIKE 'keyword%';

說明：以通配符開頭的?LIKE?查詢會導致全表掃描，而以固定字符開頭可利用索引。

1. 使用?EXISTS?代替?IN

sql

-- 不推薦
SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE country = 'China');
-- 推薦
SELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id AND c.country = 'China');

說明：EXISTS?效率更高，特別是子查詢結果集較大時。

1. 使用?UNION ALL?代替?UNION

sql

-- 不推薦
SELECT column1 FROM table1 UNION SELECT column1 FROM table2;
-- 推薦
SELECT column1 FROM table1 UNION ALL SELECT column1 FROM table2;

說明：UNION?會對結果去重，UNION ALL?直接合并結果，節省去重的開銷。

1. 避免?SELECT *

sql

-- 不推薦
SELECT * FROM employees;
-- 推薦
SELECT employee_id, employee_name FROM employees;

說明：只查詢需要的列，減少數據傳輸量。

1. 合理使用?JOIN

sql

-- 創建表
CREATE TABLE employees (employee_id INT PRIMARY KEY,department_id INT
);
CREATE TABLE departments (department_id INT PRIMARY KEY,department_name VARCHAR(50)
);
-- 為連接列創建索引
CREATE INDEX idx_department_id ON employees (department_id);
-- 進行連接查詢
SELECT e.employee_id, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

說明：確保?JOIN?條件上有索引，可提高連接查詢的效率。

1. 優化子查詢

sql

-- 不推薦
SELECT * FROM orders WHERE order_amount > (SELECT AVG(order_amount) FROM orders);
-- 推薦
SELECT o.*
FROM orders o
JOIN (SELECT AVG(order_amount) AS avg_amount FROM orders) sub ON o.order_amount > sub.avg_amount;

說明：將子查詢轉換為?JOIN?查詢，可避免子查詢的重復計算。

1. 使用?LIMIT?限制結果集

sql

SELECT * FROM products LIMIT 10;

說明：減少不必要的數據傳輸，適用于只需要部分結果的場景。

1. 對大表進行分區

sql

-- 創建按范圍分區的表
CREATE TABLE sales (sale_id INT,sale_date DATE
)
PARTITION BY RANGE (YEAR(sale_date)) (PARTITION p2023 VALUES LESS THAN (2024),PARTITION p2024 VALUES LESS THAN (2025)
);

說明：按時間范圍分區，可提高查詢效率，例如查詢 2024 年的銷售數據只需在?p2024?分區中查找。

1. 定期清理無用數據

sql

DELETE FROM logs WHERE log_date < '2024-01-01';

說明：減少表的數據量，降低查詢時的掃描行數。

1. 優化?GROUP BY?和?ORDER BY

sql

-- 為分組和排序的列創建索引
CREATE INDEX idx_group_order ON orders (customer_id, order_date);
-- 查詢并分組排序
SELECT customer_id, SUM(order_amount)
FROM orders
GROUP BY customer_id
ORDER BY order_date;

說明：確保分組和排序的列上有索引，可提高分組和排序的效率。

1. 使用索引提示

sql

SELECT * FROM products USE INDEX (idx_product_name) WHERE product_name = 'iPhone';

說明：在某些情況下可以指定使用的索引，讓查詢優化器按照指定的索引進行查詢。

1. 優化?OR?條件

sql

-- 不推薦
SELECT * FROM users WHERE user_id = 1 OR user_id = 2;
-- 推薦
SELECT * FROM users WHERE user_id IN (1, 2);

說明：OR?條件可能導致索引失效，使用?IN?可提高查詢效率。

1. 使用臨時表

sql

-- 創建臨時表存儲中間結果
CREATE TEMPORARY TABLE temp_orders AS
SELECT customer_id, SUM(order_amount) AS total_amount
FROM orders
GROUP BY customer_id;
-- 查詢臨時表
SELECT * FROM temp_orders WHERE total_amount > 1000;

說明：對于復雜查詢，先將中間結果存儲在臨時表中，可簡化后續查詢。

1. 優化?UPDATE?和?DELETE?語句

sql

-- 為更新條件列創建索引
CREATE INDEX idx_user_status ON users (status);
-- 更新數據
UPDATE users SET status = 'inactive' WHERE status = 'active';

說明：使用索引來定位要更新或刪除的記錄，減少掃描行數。

1. 調整數據庫參數

sql

-- 修改 innodb_buffer_pool_size 參數
SET GLOBAL innodb_buffer_pool_size = 2147483648; -- 2GB

說明：根據服務器內存大小調整數據庫參數，如?innodb_buffer_pool_size?可提高數據庫的緩存能力。

1. 避免使用?HAVING?子句過濾數據

sql

-- 不推薦
SELECT customer_id, SUM(order_amount)
FROM orders
GROUP BY customer_id
HAVING SUM(order_amount) > 1000;
-- 推薦
SELECT customer_id, SUM(order_amount)
FROM orders
WHERE order_amount > 0
GROUP BY customer_id
HAVING SUM(order_amount) > 1000;

說明：盡量在?WHERE?子句中過濾數據，減少分組和聚合的計算量。

1. 對頻繁查詢的結果進行緩存
   此條主要是代碼層面結合緩存工具實現，如 Java 中使用 Redis 緩存，SQL 層面不直接體現，以下為簡單示意。

sql

-- 假設緩存鍵為 "user_list"
-- 先從緩存中獲取數據
-- 如果緩存中沒有，則執行查詢
SELECT * FROM users;
-- 將查詢結果存入緩存

說明：使用 Redis 等緩存工具，減少對數據庫的頻繁查詢。

1. 優化字符串比較

sql

-- 不推薦
SELECT * FROM products WHERE UPPER(product_name) = 'IPHONE';
-- 推薦
SELECT * FROM products WHERE product_name = 'iPhone';

說明：避免在字符串比較中使用函數，可利用索引。

1. 合理設計表結構

sql

-- 避免數據冗余，將經常一起查詢的列放在一個表中
CREATE TABLE users (user_id INT PRIMARY KEY,username VARCHAR(50),email VARCHAR(100)
);

說明：合理設計表的字段，使用合適的數據類型，避免數據冗余。

1. 使用索引前綴

sql

-- 為較長的字符串列使用索引前綴
CREATE INDEX idx_product_name_prefix ON products (product_name(10));

說明：對于較長的字符串列，使用索引前綴可減少索引的存儲空間和查詢開銷。

1. 優化?CASE?語句

sql

-- 不推薦復雜嵌套的 CASE 語句
SELECT CASE WHEN condition1 THEN CASE WHEN sub_condition1 THEN result1ELSE result2ENDELSE result3END
FROM table;
-- 推薦簡化的 CASE 語句
SELECT CASE WHEN condition1 AND sub_condition1 THEN result1WHEN condition1 THEN result2ELSE result3END
FROM table;

說明：避免復雜的嵌套?CASE?語句，提高查詢性能。

1. 定期重建索引

sql

-- 重建 users 表的所有索引
ALTER TABLE users ENGINE = InnoDB;

說明：防止索引碎片化，提高索引的查詢效率。

1. 使用批量操作

sql

-- 批量插入數據
INSERT INTO users (user_id, username) VALUES (1, 'user1'), (2, 'user2'), (3, 'user3');

說明：批量插入、更新等操作可減少與數據庫的交互次數，提高效率。

1. 優化?JOIN?順序

sql

-- 小表驅動大表
SELECT *
FROM small_table s
JOIN large_table l ON s.id = l.small_table_id;

說明：讓小表驅動大表，可減少中間結果集的大小，提高連接效率。

1. 避免在?WHERE?子句中進行類型轉換

sql

-- 不推薦
SELECT * FROM users WHERE CAST(user_id AS CHAR) = '1';
-- 推薦
SELECT * FROM users WHERE user_id = 1;

說明：在?WHERE?子句中進行類型轉換會導致索引失效。

1. 對表進行統計信息更新

sql

-- 更新 users 表的統計信息
ANALYZE TABLE users;

說明：讓查詢優化器有更準確的判斷，生成更優的執行計劃。

1. 使用存儲過程

sql

-- 創建存儲過程
DELIMITER //
CREATE PROCEDURE get_user_orders(IN user_id INT)
BEGINSELECT * FROM orders WHERE customer_id = user_id;
END //
DELIMITER ;
-- 調用存儲過程
CALL get_user_orders(1);

說明：將復雜的業務邏輯封裝在存儲過程中，減少客戶端與數據庫的交互次數。

?

2. 請詳細說明實踐中如何優化 MySQL 數據庫

?

• 定義

MySQL 數據庫優化是指通過對 MySQL 數據庫的硬件資源、配置參數、表結構設計、查詢語句等方面進行調整和改進，以提高數據庫的性能、穩定性和可擴展性的一系列操作。

• 要點

綜合考慮數據庫的各個方面，根據實際業務需求和數據庫的運行情況，從硬件、配置、表結構、查詢語句等多個維度進行優化。

• 應用

在實際的數據庫開發和運維中，針對不同規模和業務特點的 MySQL 數據庫，靈活運用各種優化方法，提升數據庫的整體性能。

• 硬件優化

硬件優化主要是在服務器層面進行，SQL 層面不直接體現，以下為簡單說明。

• 增加內存，提高?innodb_buffer_pool_size?等參數，減少磁盤 I/O。

sql

-- 修改 innodb_buffer_pool_size 參數
SET GLOBAL innodb_buffer_pool_size = 4294967296; -- 4GB

說明：增大?innodb_buffer_pool_size?可將更多的數據和索引緩存到內存中，減少磁盤讀取。

• 使用高速磁盤，如 SSD，可提高數據讀寫速度。
• 增加 CPU 核心數，提高并發處理能力。

配置參數優化

sql

-- 根據服務器內存大小調整參數
SET GLOBAL key_buffer_size = 134217728; -- 128MB
SET GLOBAL max_connections = 500;
SET GLOBAL query_cache_size = 67108864; -- 64MB

說明：key_buffer_size?用于 MyISAM 表的索引緩存，max_connections?控制最大連接數，query_cache_size?開啟查詢緩存（但要注意緩存失效問題）。

表結構優化

sql

-- 合理設計表的字段，使用合適的數據類型
CREATE TABLE products (product_id INT PRIMARY KEY,product_name VARCHAR(100),price DECIMAL(10, 2),stock INT
);
-- 對大表進行分區
CREATE TABLE sales (sale_id INT,sale_date DATE
)
PARTITION BY RANGE (YEAR(sale_date)) (PARTITION p2023 VALUES LESS THAN (2024),PARTITION p2024 VALUES LESS THAN (2025)
);
-- 為經常用于查詢、排序和連接的列創建索引
CREATE INDEX idx_product_name ON products (product_name);

說明：合理設計表結構可減少數據冗余，提高查詢效率；分區可提高大表的查詢性能；索引可加快查詢速度。

查詢語句優化

sql

-- 避免 SELECT *
SELECT product_id, product_name FROM products;
-- 優化 JOIN 語句
SELECT p.product_name, c.category_name
FROM products p
JOIN categories c ON p.category_id = c.category_id;
-- 避免在索引列上使用函數
SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

說明：避免?SELECT *?可減少數據傳輸量；優化?JOIN?語句可提高連接效率；避免在索引列上使用函數可利用索引。

數據庫維護

sql

-- 定期清理無用數據
DELETE FROM logs WHERE log_date < '2024-01-01';
-- 定期重建索引
ALTER TABLE products ENGINE = InnoDB;
-- 更新表的統計信息
ANALYZE TABLE products;

說明：清理無用數據可減少表的數據量；重建索引可防止索引碎片化；更新統計信息可讓查詢優化器生成更優的執行計劃。

?

3. 什么是 left join， right join， inner join， full join， cross join

?

• 定義

1. left join（左連接）：返回左表中的所有記錄，以及右表中匹配的記錄。如果右表中沒有匹配的記錄，則右表的列值為?NULL。
2. right join（右連接）：返回右表中的所有記錄，以及左表中匹配的記錄。如果左表中沒有匹配的記錄，則左表的列值為?NULL。
3. inner join（內連接）：只返回兩個表中匹配的記錄。
4. full join（全連接）：返回左表和右表中的所有記錄，如果某表中沒有匹配的記錄，則對應列值為?NULL。
5. cross join（交叉連接）：返回兩個表的笛卡爾積，即左表的每一行與右表的每一行都組合一次。

• 要點

不同的連接方式根據連接條件返回不同的結果集，需要根據業務需求選擇合適的連接方式，以獲取所需的數據。

• 應用

在實際的數據庫查詢中，根據不同的業務場景，使用不同的連接方式來關聯多個表的數據，如在訂單系統中關聯訂單表和用戶表。

• SQL 代碼舉例

sql

-- 創建表
CREATE TABLE employees (employee_id INT PRIMARY KEY,employee_name VARCHAR(50)
);CREATE TABLE departments (department_id INT PRIMARY KEY,department_name VARCHAR(50)
);CREATE TABLE employee_department (employee_id INT,department_id INT,FOREIGN KEY (employee_id) REFERENCES employees(employee_id),FOREIGN KEY (department_id) REFERENCES departments(department_id)
);-- 插入數據
INSERT INTO employees (employee_id, employee_name) VALUES (1, 'John'), (2, 'Jane'), (3, 'Bob');
INSERT INTO departments (department_id, department_name) VALUES (1, 'HR'), (2, 'IT');
INSERT INTO employee_department (employee_id, department_id) VALUES (1, 1), (2, 2);-- left join
SELECT e.employee_name, d.department_name
FROM employees e
LEFT JOIN employee_department ed ON e.employee_id = ed.employee_id
LEFT JOIN departments d ON ed.department_id = d.department_id;-- right join
SELECT e.employee_name, d.department_name
FROM employees e
RIGHT JOIN employee_department ed ON e.employee_id = ed.employee_id
RIGHT JOIN departments d ON ed.department_id = d.department_id;-- inner join
SELECT e.employee_name, d.department_name
FROM employees e
JOIN employee_department ed ON e.employee_id = ed.employee_id
JOIN departments d ON ed.department_id = d.department_id;-- full join（MySQL 不直接支持，可通過 UNION 模擬）
SELECT e.employee_name, d.department_name
FROM employees e
LEFT JOIN employee_department ed ON e.employee_id = ed.employee_id
LEFT JOIN departments d ON ed.department_id = d.department_id
UNION
SELECT e.employee_name, d.department_name
FROM employees e
RIGHT JOIN employee_department ed ON e.employee_id = ed.employee_id
RIGHT JOIN departments d ON ed.department_id = d.department_id;-- cross join
SELECT e.employee_name, d.department_name
FROM employees e
CROSS JOIN departments d;

?

4. 什么是數據庫范式

?

• 定義

數據庫范式是為了規范數據庫設計，減少數據冗余，提高數據的一致性和可維護性而提出的一系列規則和標準。通過將數據庫表按照一定的范式進行設計，可以使數據庫結構更加合理、高效。

• 要點

不同的范式有不同的要求，通常數據庫設計需要滿足一定的范式，但也不是越高的范式越好，需要根據實際情況進行權衡，在數據冗余和查詢性能之間找到平衡。

• 應用

在數據庫設計階段，根據業務需求和數據特點，遵循相應的范式進行表結構設計，提高數據庫的質量和可維護性。

• SQL 代碼舉例及說明

第一范式（1NF）

sql

-- 不滿足 1NF 的表
CREATE TABLE orders (order_id INT,product_names VARCHAR(255) -- 包含多個產品名稱，不滿足原子性
);
-- 滿足 1NF 的表
CREATE TABLE orders (order_id INT,product_name VARCHAR(50)
);

說明：第一范式要求每個列都具有原子性，即不可再分。

第二范式（2NF）

sql

-- 不滿足 2NF 的表
CREATE TABLE order_items (order_id INT,product_id INT,product_name VARCHAR(50),product_price DECIMAL(10, 2),PRIMARY KEY (order_id, product_id)
);
-- 滿足 2NF 的表
CREATE TABLE products (product_id INT PRIMARY KEY,product_name VARCHAR(50),product_price DECIMAL(10, 2)
);
CREATE TABLE order_items (order_id INT,product_id INT,quantity INT,PRIMARY KEY (order_id, product_id),FOREIGN KEY (product_id) REFERENCES products(product_id)
);

說明：第二范式要求在滿足 1NF 的基礎上，非主鍵列完全依賴于主鍵，而不是部分依賴。

第三范式（3NF）

sql

-- 不滿足 3NF 的表
CREATE TABLE employees (employee_id INT PRIMARY KEY,department_id INT,department_name VARCHAR(50)
);
-- 滿足 3NF 的表
CREATE TABLE departments (department_id INT PRIMARY KEY,department_name VARCHAR(50)
);
CREATE TABLE employees (employee_id INT PRIMARY KEY,department_id INT,FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

說明：第三范式要求在滿足 2NF 的基礎上，非主鍵列之間不存在傳遞依賴。

巴斯 - 科德范式（BCNF）

sql

-- 不滿足 BCNF 的表
CREATE TABLE suppliers (supplier_id INT,product_id INT,supplier_name VARCHAR(50),PRIMARY KEY (supplier_id, product_id),UNIQUE (supplier_name, product_id)
);
-- 滿足 BCNF 的表
CREATE TABLE supplier_products (supplier_id INT,product_id INT,PRIMARY KEY (supplier_id, product_id)
);
CREATE TABLE suppliers (supplier_id INT PRIMARY KEY,supplier_name VARCHAR(50)
);

說明：巴斯 - 科德范式要求在滿足 3NF 的基礎上，每個非平凡的函數依賴的左邊必須包含候選鍵。

?

5. 什么是數據庫連接池

?

• 定義

數據庫連接池是一種管理數據庫連接的技術，它預先創建一定數量的數據庫連接并存儲在連接池中。當應用程序需要訪問數據庫時，從連接池中獲取一個可用的連接，使用完后將連接歸還到連接池中，而不是每次都創建和銷毀連接。

• 要點

減少了頻繁創建和銷毀數據庫連接的開銷，提高了數據庫的訪問效率和應用程序的性能，同時可以對連接進行統一管理和監控。

• 應用

在 Java 等編程語言開發的數據庫應用中廣泛使用，如 Web 應用、企業級應用等，以提高系統的響應速度和并發處理能力。

以下為結合 Java 使用 HikariCP 連接池的示例

java

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;public class DatabaseConnectionPoolExample {public static void main(String[] args) {HikariConfig config = new HikariConfig();config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");config.setUsername("root");config.setPassword("password");config.setMaximumPoolSize(10);HikariDataSource dataSource = new HikariDataSource(config);try (Connection connection = dataSource.getConnection();Statement statement = connection.createStatement();ResultSet resultSet = statement.executeQuery("SELECT * FROM users")) {while (resultSet.next()) {System.out.println(resultSet.getString("username"));}} catch (Exception e) {e.printStackTrace();}}
}

說明：通過 HikariCP 連接池獲取數據庫連接，執行查詢操作，使用完后連接會自動歸還到連接池中。

?

6. 什么是 DDL DML DCL

?

• 定義

1. DDL（數據定義語言）：用于定義數據庫、表、視圖、索引等數據庫對象的結構，主要負責數據庫對象的創建、修改和刪除操作。
2. DML（數據操作語言）：用于對數據庫中的數據進行插入、更新和刪除操作，實現對數據的增刪改功能。
3. DCL（數據控制語言）：用于控制用戶對數據庫對象的訪問權限，確保數據的安全性和完整性。

• 要點

不同的操作類型有不同的用途，DDL 主要關注數據庫對象的結構，DML 主要處理數據的內容，DCL 主要管理用戶的權限。

• 應用

在數據庫開發和管理中，根據不同的需求使用不同的操作類型，如創建表使用 DDL，插入數據使用 DML，授予用戶權限使用 DCL。

SQL 代碼舉例

DDL（數據定義語言）

sql

-- 創建數據庫
CREATE DATABASE mydb;
-- 使用數據庫
USE mydb;
-- 創建表
CREATE TABLE users (user_id INT PRIMARY KEY,username VARCHAR(50),email VARCHAR(100)
);
-- 修改表結構
ALTER TABLE users ADD COLUMN phone_number VARCHAR(20);
-- 刪除表
DROP TABLE users;

DML（數據操作語言）

sql

-- 插入數據
INSERT INTO users (user_id, username, email) VALUES (1, 'john', 'john@example.com');
-- 更新數據
UPDATE users SET email = 'new_john@example.com' WHERE user_id = 1;
-- 刪除數據
DELETE FROM users WHERE user_id = 1;

DCL（數據控制語言）

sql

-- 授予用戶查詢權限
GRANT SELECT ON users TO 'user1'@'localhost';
-- 授予用戶所有權限
GRANT ALL PRIVILEGES ON mydb.* TO 'user1'@'localhost';
-- 撤銷用戶查詢權限
REVOKE SELECT ON users FROM 'user1'@'localhost';

?

7. 什么是 explain，舉實例說明

• 定義

EXPLAIN?是 MySQL 提供的一個用于分析查詢語句執行計劃的工具，它可以顯示查詢語句如何執行，包括使用的索引、掃描的行數、連接的順序等信息，幫助開發人員了解查詢的性能瓶頸。

• 要點

通過分析?EXPLAIN?的結果，可以找出查詢語句的性能瓶頸，從而進行針對性的優化，如調整索引、優化查詢語句結構等。

• 應用

在數據庫開發和優化過程中，對復雜或性能較差的查詢語句使用?EXPLAIN?進行分析，以提高查詢效率。

SQL 代碼舉例

sql

-- 創建表
CREATE TABLE products (product_id INT PRIMARY KEY,product_name VARCHAR(100),price DECIMAL(10, 2),category VARCHAR(50)
);-- 創建索引
CREATE INDEX idx_category ON products (category);-- 查詢語句
EXPLAIN SELECT * FROM products WHERE category = 'electronics';

執行上述?EXPLAIN?語句后，會返回一個結果集，包含以下重要信息：

• id：查詢的標識符。
• select_type：查詢的類型，如?SIMPLE?表示簡單查詢。
• table：查詢涉及的表。
• type：連接類型，如?ref?表示使用索引查找。
• possible_keys：可能使用的索引。
• key：實際使用的索引。
• key_len：索引使用的字節數。
• ref：哪些列或常量被用于查找索引列上的值。
• rows：估計要掃描的行數。
• Extra：額外的信息，如?Using where?表示使用了?WHERE?子句。

?

8. 如何進行分庫，分表

?

• 定義

分庫分表是指當數據庫的數據量和訪問量達到一定程度時，將數據庫的數據分散存儲到多個數據庫（分庫）或多個表（分表）中，以提高數據庫的并發處理能力和可擴展性的技術手段。

• 要點

需要根據業務需求和數據特點選擇合適的分庫分表策略，同時要考慮數據的一致性、查詢的復雜性以及后續的數據遷移和維護問題。

• 應用

在大型互聯網應用、電商系統等數據量和訪問量較大的場景中廣泛應用，以應對高并發和大數據量的挑戰。

SQL 代碼舉例及說明

垂直分庫

sql

-- 原數據庫中有用戶表和訂單表
-- 用戶數據庫
CREATE DATABASE user_db;
USE user_db;
CREATE TABLE users (user_id INT PRIMARY KEY,username VARCHAR(50),email VARCHAR(100)
);
-- 訂單數據庫
CREATE DATABASE order_db;
USE order_db;
CREATE TABLE orders (order_id INT PRIMARY KEY,user_id INT,order_amount DECIMAL(10, 2)
);

說明：將一個數據庫按照業務功能拆分成多個數據庫，如將用戶信息和訂單信息分別存儲在不同的數據庫中。

水平分庫

sql

-- 假設按照用戶 ID 范圍進行水平分庫
-- 數據庫 1
CREATE DATABASE db1;
USE db1;
CREATE TABLE users (user_id INT PRIMARY KEY,username VARCHAR(50),email VARCHAR(100)
) PARTITION BY RANGE (user_id) (PARTITION p1 VALUES LESS THAN (1000),PARTITION p2 VALUES LESS THAN (2000)
);
-- 數據庫 2
CREATE DATABASE db2;
USE db2;
CREATE TABLE users (user_id INT PRIMARY KEY,username VARCHAR(50),email VARCHAR(100)
) PARTITION BY RANGE (user_id) (PARTITION p3 VALUES LESS THAN (3000),PARTITION p4 VALUES LESS THAN (4000)
);

說明：將一個數據庫中的數據按照一定的規則（如按用戶 ID 范圍）拆分到多個數據庫中。

垂直分表

sql

-- 原表
CREATE TABLE users (user_id INT PRIMARY KEY,username VARCHAR(50),email VARCHAR(100),address TEXT,description TEXT
);
-- 垂直分表
CREATE TABLE user_basic_info (user_id INT PRIMARY KEY,username VARCHAR(50),email VARCHAR(100)
);
CREATE TABLE user_extra_info (user_id INT PRIMARY KEY,address TEXT,description TEXT
);

說明：將一個表按照列進行拆分，將經常一起查詢的列放在一個表中，不經常一起查詢的列放在另一個表中。

水平分表

sql

-- 按照訂單日期進行水平分表
CREATE TABLE orders_202401 (order_id INT PRIMARY KEY,order_date DATE,order_amount DECIMAL(10, 2)
);
CREATE TABLE orders_202402 (order_id INT PRIMARY KEY,order_date DATE,order_amount DECIMAL(10, 2)
);

說明：將一個表中的數據按照一定的規則（如按時間）拆分到多個表中。

?

9. 如何解決分庫分表帶來的壞處

?

• 定義

分庫分表在提高數據庫性能和可擴展性的同時，會帶來一些問題，如分布式事務、跨庫查詢、數據遷移等，解決這些問題的一系列技術和策略即為解決分庫分表帶來壞處的方法。

• 要點

針對不同的問題，采用不同的解決方案，同時要考慮方案的可行性、性能開銷和對業務的影響。

• 應用

在實施分庫分表后，當遇到分布式事務、跨庫查詢等問題時，運用相應的解決方法來保證系統的正常運行和數據的一致性。

分布式事務問題

可使用分布式事務框架 Seata 來解決，以下為簡單的 Java 代碼示例結合 SQL 示意，SQL 層面主要是業務操作。

java

import io.seata.spring.annotation.GlobalTransactional;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;@Service
public class OrderService {@Autowiredprivate JdbcTemplate jdbcTemplate;@GlobalTransactionalpublic void createOrder() {// 插入訂單數據jdbcTemplate.update("INSERT INTO orders (order_id, user_id, order_amount) VALUES (1, 1, 100.0)");// 扣減庫存，可能涉及另一個數據庫jdbcTemplate.update("UPDATE products SET stock = stock - 1 WHERE product_id = 1");}
}

說明：Seata 通過全局事務注解?@GlobalTransactional?來管理分布式事務，確保多個數據庫操作的一致性。

?

跨庫查詢問題

可使用中間件 ShardingSphere 來解決，以下為簡單的配置和 SQL 示例。

yaml

# ShardingSphere 配置
spring:shardingsphere:datasource:names: ds0, ds1ds0:type: com.zaxxer.hikari.HikariDataSourcejdbc-url: jdbc:mysql://localhost:3306/db0username: rootpassword: passwordds1:type: com.zaxxer.hikari.HikariDataSourcejdbc-url: jdbc:mysql://localhost:3306/db1username: rootpassword: passwordrules:sharding:tables:users:actual-data-nodes: ds$->{0..1}.usersdatabase-strategy:standard:sharding-column: user_idsharding-algorithm-name: database-inlinetable-strategy:standard:sharding-column: user_idsharding-algorithm-name: table-inlinesharding-algorithms:database-inline:type: INLINEprops:algorithm-expression: ds$->{user_id % 2}table-inline:type: INLINEprops:algorithm-expression: users

sql

-- 跨庫查詢
SELECT * FROM users;

說明：ShardingSphere 會根據配置的規則對跨庫查詢進行處理，將查詢路由到相應的數據庫和表中。

數據遷移問題

采用雙寫遷移的方式，以下為簡單的 Java 代碼示例結合 SQL 示意。

java

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;@Service
public class DataMigrationService {@Autowiredprivate JdbcTemplate oldJdbcTemplate;@Autowiredprivate JdbcTemplate newJdbcTemplate;public void migrateData() {// 從舊數據庫查詢數據String sql = "SELECT * FROM users";oldJdbcTemplate.query(sql, (rs, rowNum) -> {int userId = rs.getInt("user_id");String username = rs.getString("username");String email = rs.getString("email");// 同時寫入新數據庫newJdbcTemplate.update("INSERT INTO users (user_id, username, email) VALUES (?,?,?)", userId, username, email);return null;});}
}

sql

-- 舊數據庫
CREATE DATABASE old_db;
USE old_db;
CREATE TABLE users (user_id INT PRIMARY KEY,username VARCHAR(50),email VARCHAR(100)
);
-- 新數據庫
CREATE DATABASE new_db;
USE new_db;
CREATE TABLE users (user_id INT PRIMARY KEY,username VARCHAR(50),email VARCHAR(100)
);

說明：雙寫遷移時，在業務代碼里，從舊數據庫查詢數據后，同時將數據寫入新數據庫。這樣能保證在遷移期間新舊數據庫的數據一致。等數據遷移完成，并且驗證無誤后，就可以把業務切換到新數據庫。

?

10. 什么是封鎖

?

• 定義

封鎖是數據庫管理系統中用于控制并發訪問的一種機制。它通過對數據對象（如表、行等）加鎖，來限制不同事務對這些數據對象的并發操作，以此保證數據的一致性和完整性，避免并發訪問可能引發的數據沖突問題。

• 要點

不同的鎖類型具有不同的特點和用途，需要依據實際情況來選擇合適的鎖類型。同時，要合理運用封鎖機制，避免出現死鎖等問題，因為死鎖會導致事務無法正常執行，降低數據庫的性能。

• 應用

在高并發的數據庫環境中，合理使用封鎖機制可以提高數據庫的并發處理能力。例如，在電商系統的訂單處理、銀行系統的轉賬操作等場景中，需要使用封鎖機制來保證數據的一致性和準確性。

SQL 代碼舉例

• 共享鎖（S 鎖）

sql

-- 開啟事務
START TRANSACTION;
-- 對 users 表的某一行加共享鎖
SELECT * FROM users WHERE user_id = 1 LOCK IN SHARE MODE;
-- 可以繼續執行其他查詢操作
SELECT * FROM orders WHERE user_id = 1;
-- 提交事務
COMMIT;

說明：共享鎖也叫讀鎖，多個事務可以同時對一個數據對象加共享鎖，用于并發讀取數據。在上述示例中，使用?LOCK IN SHARE MODE?對?user_id?為 1 的行加了共享鎖，在事務提交之前，其他事務也可以對該行加共享鎖進行讀取操作，但不能加排他鎖進行修改操作。

• 排他鎖（X 鎖）

sql

-- 開啟事務
START TRANSACTION;
-- 對 users 表的某一行加排他鎖
SELECT * FROM users WHERE user_id = 1 FOR UPDATE;
-- 修改數據
UPDATE users SET username = 'new_username' WHERE user_id = 1;
-- 提交事務
COMMIT;

說明：排他鎖也叫寫鎖，一個數據對象只能被一個事務加排他鎖，用于修改數據。在加排他鎖期間，其他事務不能對該數據對象加任何鎖，直到持有排他鎖的事務提交或回滾。

• 意向鎖

意向鎖用于表示事務對某個數據對象的子對象（如行）有某種類型的鎖。雖然在 SQL 中一般不需要顯式使用意向鎖，但在數據庫內部會自動處理。例如，當一個事務要對表中的某一行加排他鎖時，數據庫會先對表加意向排他鎖。以下是一個隱示意圖向鎖存在的示例：

sql

-- 事務 1：對表中的某一行加排他鎖
START TRANSACTION;
SELECT * FROM users WHERE user_id = 1 FOR UPDATE;
-- 此時數據庫會自動對 users 表加意向排他鎖
-- 事務 2：嘗試對表加共享鎖，會被阻塞
START TRANSACTION;
SELECT * FROM users LOCK IN SHARE MODE;
-- 直到事務 1 提交或回滾
COMMIT;

說明：事務 1 對?user_id?為 1 的行加排他鎖時，數據庫會自動對?users?表加意向排他鎖。此時事務 2 嘗試對表加共享鎖會被阻塞，因為意向排他鎖和共享鎖不兼容。

• 行鎖

sql

-- 開啟事務
START TRANSACTION;
-- 對 users 表的某一行加行鎖（使用 InnoDB 存儲引擎時，默認的行級鎖）
UPDATE users SET email = 'new_email@example.com' WHERE user_id = 1;
-- 提交事務
COMMIT;

說明：行鎖對表中的某一行數據加鎖，粒度較小，并發性能較高。在上述示例中，只對?user_id?為 1 的行加了鎖，其他行的數據可以被其他事務并發訪問。

• 表鎖

sql

-- 對 users 表加表鎖
LOCK TABLES users WRITE;
-- 執行數據操作
INSERT INTO users (user_id, username) VALUES (2, 'user2');
-- 釋放表鎖
UNLOCK TABLES;

說明：表鎖對整個表加鎖，粒度較大，并發性能較低。在加表鎖期間，其他事務不能對該表進行任何操作，直到表鎖被釋放。

?友情提示：本文已經整理成文檔，可以到如下鏈接免積分下載閱讀

https://download.csdn.net/download/ylfhpy/90560627