1. 主鍵索引底層的實現原理
?
- 定義
主鍵索引是數據庫中用于唯一標識表中每一行記錄的索引,常見的底層實現是 B+ 樹結構。B+ 樹是一種平衡的多路搜索樹,由內部節點和葉子節點組成。內部節點只存儲索引鍵和指向下一層節點的指針,不存儲實際數據;葉子節點存儲索引鍵和對應的實際數據記錄,并且所有葉子節點通過指針連接成一個有序鏈表。查詢時從根節點開始,依據索引鍵的值在內部節點中比較,逐步向下查找直至找到對應的葉子節點獲取數據。
- 要點
- 平衡性:B+ 樹所有葉子節點在同一層,保證查詢時間復雜度穩定在 O (log n),n 為數據記錄數量。
- 多路搜索:每個節點可有多個子節點,降低樹的高度,減少磁盤 I/O 次數,提升查詢效率。
- 鏈表結構:葉子節點間通過指針連接成有序鏈表,便于進行范圍查詢。
- 應用
在電商系統的商品表中,以商品 ID 作為主鍵索引。當用戶根據商品 ID 查詢商品詳情時,數據庫能通過 B+ 樹快速定位到對應的商品記錄,提高查詢效率。
SQL 代碼示例
sql
-- 創建商品表并設置商品 ID 為主鍵索引
CREATE TABLE products (product_id INT PRIMARY KEY,product_name VARCHAR(100),price DECIMAL(10, 2)
);-- 插入數據
INSERT INTO products (product_id, product_name, price) VALUES (1, 'iPhone', 999.99);
INSERT INTO products (product_id, product_name, price) VALUES (2, 'iPad', 599.99);-- 根據主鍵索引查詢商品
SELECT * FROM products WHERE product_id = 1;
Java 代碼示例(模擬 B+ 樹查詢)
java
import java.util.ArrayList;
import java.util.List;// 簡單模擬 B+ 樹節點
class BPlusTreeNode {List<Integer> keys;List<BPlusTreeNode> children;boolean isLeaf;BPlusTreeNode next;public BPlusTreeNode(boolean isLeaf) {this.keys = new ArrayList<>();this.children = new ArrayList<>();this.isLeaf = isLeaf;this.next = null;}
}// 簡單模擬 B+ 樹查詢
class BPlusTree {BPlusTreeNode root;public BPlusTree() {this.root = new BPlusTreeNode(true);}public Integer search(int key) {BPlusTreeNode node = root;while (!node.isLeaf) {int i = 0;while (i < node.keys.size() && key > node.keys.get(i)) {i++;}node = node.children.get(i);}for (int i = 0; i < node.keys.size(); i++) {if (node.keys.get(i) == key) {return key;}}return null;}
}public class BPlusTreeExample {public static void main(String[] args) {BPlusTree bPlusTree = new BPlusTree();Integer result = bPlusTree.search(5);System.out.println(result);}
}
?
2. 01 索引
- 定義
“01 索引” 指的是位圖索引,它是一種特殊的索引結構,用位圖表示每個索引鍵的值。對于每個索引鍵,位圖中的每一位對應一個數據記錄,若該記錄的索引鍵值等于該索引鍵,對應位為 1,否則為 0。通過位圖的位運算可快速進行查詢和過濾。
- 要點
- 空間效率高:對于低基數(不同值數量較少)的列,能顯著減少存儲空間。
- 查詢效率高:利用位運算可快速查詢和過濾,尤其適用于多條件查詢。
- 更新成本高:數據記錄插入、更新或刪除時,需更新對應位圖,成本較高。
- 應用
在數據倉庫的用戶表中,對于性別(只有男、女兩種值)列可使用位圖索引。當需要統計男性用戶數量時,通過位圖的位運算能快速得出結果。
SQL 代碼示例(創建位圖索引)
sql
-- 假設表名為 users,列名為 gender
CREATE TABLE users (user_id INT,gender CHAR(1)
);-- 創建位圖索引
CREATE BITMAP INDEX idx_users_gender ON users (gender);-- 插入數據
INSERT INTO users (user_id, gender) VALUES (1, 'M');
INSERT INTO users (user_id, gender) VALUES (2, 'F');-- 查詢男性用戶
SELECT * FROM users WHERE gender = 'M';
?
3. 如何在長文本中快捷的篩選出你的名字
?
- 定義
在長文本中篩選指定名字可借助字符串匹配算法。常見算法有樸素匹配算法、KMP 算法、Boyer - Moore 算法等,這些算法通過不同策略在長文本中查找目標名字的位置。
- 要點
- 樸素匹配算法:簡單直觀,但時間復雜度高,為 O (m * n),m 是長文本長度,n 是要匹配名字的長度。
- KMP 算法:預處理要匹配的名字,構建部分匹配表,避免匹配過程中的不必要回溯,時間復雜度為 O (m + n)。
- Boyer - Moore 算法:從右向左匹配,利用壞字符規則和好后綴規則跳過盡可能多的字符,平均時間復雜度接近 O (m)。
- 應用
在新聞文章的搜索系統中,用戶輸入關鍵詞(如人名),系統可使用高效的字符串匹配算法在大量文章中快速篩選出包含該關鍵詞的文章。
Java 代碼示例(使用 KMP 算法)
java
public class KMP {public static int kmpSearch(String text, String pattern) {int[] lps = computeLPSArray(pattern);int i = 0; // 文本指針int j = 0; // 模式指針while (i < text.length()) {if (pattern.charAt(j) == text.charAt(i)) {j++;i++;}if (j == pattern.length()) {return i - j; // 找到匹配} else if (i < text.length() && pattern.charAt(j) != text.charAt(i)) {if (j != 0) {j = lps[j - 1];} else {i++;}}}return -1; // 未找到匹配}private static int[] computeLPSArray(String pattern) {int[] lps = new int[pattern.length()];int len = 0;int i = 1;lps[0] = 0;while (i < pattern.length()) {if (pattern.charAt(i) == pattern.charAt(len)) {len++;lps[i] = len;i++;} else {if (len != 0) {len = lps[len - 1];} else {lps[i] = 0;i++;}}}return lps;}public static void main(String[] args) {String text = "Hello, my name is John. John is a good person.";String pattern = "John";int index = kmpSearch(text, pattern);System.out.println("Index of pattern: " + index);}
}
?
4. 數據庫的完整性約束, 事務隔離級別
?
- 數據庫的完整性約束
- 定義
數據庫的完整性約束是為保證數據庫中數據的正確性、一致性和有效性而設置的規則。包括實體完整性、參照完整性和用戶定義的完整性。實體完整性確保表中每一行記錄唯一,通常通過主鍵約束實現;參照完整性保證表之間關聯關系正確,通常通過外鍵約束實現;用戶定義的完整性是根據業務需求定義的規則,如檢查約束、默認值約束等。
- 要點
- 主鍵約束:一個表只能有一個主鍵,主鍵值必須唯一且不為空。
- 外鍵約束:外鍵是表中的列,引用另一個表的主鍵,其值必須存在于被引用表的主鍵中或為空。
- 檢查約束:限制列的取值范圍,如年齡必須大于 0。
- 默認值約束:為列指定默認值,插入記錄時若未指定該列值,則使用默認值。
- 應用
在學生選課系統中,學生表的學生 ID 作為主鍵,課程表的課程 ID 作為主鍵。選課表中通過外鍵關聯學生 ID 和課程 ID,確保選課信息的正確性。同時,可設置成績列的檢查約束,保證成績在 0 - 100 分之間。
SQL 代碼示例(創建完整性約束)
sql
-- 創建學生表
CREATE TABLE students (student_id INT PRIMARY KEY,name VARCHAR(50) NOT NULL,age INT CHECK (age > 0)
);-- 創建課程表
CREATE TABLE courses (course_id INT PRIMARY KEY,course_name VARCHAR(100)
);-- 創建選課表
CREATE TABLE enrollments (enrollment_id INT PRIMARY KEY,student_id INT,course_id INT,grade DECIMAL(5, 2) CHECK (grade >= 0 AND grade <= 100),FOREIGN KEY (student_id) REFERENCES students(student_id),FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
事務隔離級別
- 定義
事務隔離級別用于控制多個事務之間的可見性和并發操作行為。不同隔離級別影響事務的并發性能和數據一致性。常見的事務隔離級別有讀未提交(Read Uncommitted)、讀已提交(Read Committed)、可重復讀(Repeatable Read)和串行化(Serializable)。
- 要點
- 讀未提交:一個事務可讀取另一個未提交事務的數據,可能出現臟讀、不可重復讀和幻讀問題。
- 讀已提交:一個事務只能讀取另一個已提交事務的數據,避免臟讀問題,但可能出現不可重復讀和幻讀問題。
- 可重復讀:在一個事務中,多次讀取同一數據結果相同,避免臟讀和不可重復讀問題,但可能出現幻讀問題。
- 串行化:所有事務依次執行,避免臟讀、不可重復讀和幻讀問題,但并發性能最低。
- 應用
在銀行轉賬系統中,為保證數據一致性,可將事務隔離級別設置為可重復讀。例如,在一個事務中進行轉賬操作時,多次讀取賬戶余額結果相同,避免出現數據不一致的情況。
SQL 代碼示例(設置事務隔離級別)
sql
-- 設置事務隔離級別為可重復讀
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;-- 開始事務
START TRANSACTION;-- 執行轉賬操作
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;-- 提交事務
COMMIT;
?
5. 數據庫悲觀鎖怎么實現的
?
- 定義
數據庫悲觀鎖是一種保守的并發控制策略,假設數據處理過程中會發生沖突,因此在訪問數據前先對數據加鎖,防止其他事務修改數據。常見實現方式有共享鎖(讀鎖)和排他鎖(寫鎖)。
- 要點
- 共享鎖:多個事務可同時對同一數據加共享鎖用于讀取數據。加共享鎖后,其他事務可繼續加共享鎖,但不能加排他鎖,直到所有共享鎖釋放。
- 排他鎖:一個事務對數據加排他鎖后,其他事務不能再對該數據加任何類型的鎖,直到排他鎖釋放。排他鎖用于修改數據,保證數據一致性。
- 應用
在電商系統的庫存管理中,當用戶下單時,對相應商品的庫存記錄加排他鎖,防止其他用戶同時購買導致庫存數據不一致。
SQL 代碼示例(使用排他鎖)
sql
-- 會話 1
START TRANSACTION;
SELECT * FROM products WHERE product_id = 1 FOR UPDATE;
-- 進行庫存更新操作
UPDATE products SET stock = stock - 1 WHERE product_id = 1;
COMMIT;-- 會話 2
START TRANSACTION;
SELECT * FROM products WHERE product_id = 1 FOR UPDATE;
-- 會被阻塞,直到會話 1 的排他鎖釋放
COMMIT;
?
6. 建表的原則
?
- 定義
建表原則是為保證數據庫設計合理、高效、可維護和可擴展而遵循的準則。設計表結構時需考慮數據的完整性、一致性、性能和可維護性等因素。
- 要點
- 遵循范式:盡量遵循數據庫的范式設計,如第一范式(1NF)、第二范式(2NF)和第三范式(3NF),減少數據冗余。
- 選擇合適的數據類型:根據數據特點和使用場景,選擇合適的數據類型,避免數據類型不匹配導致的性能問題。
- 合理設置主鍵和索引:為表設置合適的主鍵,確保數據唯一性。根據查詢需求,合理創建索引,提高查詢性能。
- 考慮數據的擴展性:設計表結構時要考慮未來數據增長和業務變化,預留一定擴展性。
- 應用
在企業的員工管理系統中,設計員工表、部門表和項目表時,遵循建表原則。員工表以員工 ID 為主鍵,部門表以部門 ID 為主鍵,項目表以項目 ID 為主鍵。通過外鍵關聯員工和部門、員工和項目,同時根據查詢需求在相關列上創建索引。
SQL 代碼示例(創建符合建表原則的表)
sql
-- 創建部門表
CREATE TABLE departments (department_id INT PRIMARY KEY,department_name VARCHAR(100)
);-- 創建員工表
CREATE TABLE employees (employee_id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL,department_id INT,salary DECIMAL(10, 2),hire_date DATE,FOREIGN KEY (department_id) REFERENCES departments(department_id),INDEX idx_department_id (department_id)
);-- 創建項目表
CREATE TABLE projects (project_id INT PRIMARY KEY,project_name VARCHAR(100),start_date DATE,end_date DATE
);-- 創建員工項目關聯表
CREATE TABLE employee_projects (employee_project_id INT PRIMARY KEY AUTO_INCREMENT,employee_id INT,project_id INT,FOREIGN KEY (employee_id) REFERENCES employees(employee_id),FOREIGN KEY (project_id) REFERENCES projects(project_id)
);
?
7. 索引的定義和用法
?
- 定義
索引是數據庫中一種特殊的數據結構,用于加快數據查詢速度。它對表中某些列的值進行排序和存儲,建立快速查找的映射關系,使數據庫查詢時可直接定位所需數據記錄,無需掃描整個表。
- 用法
- 創建索引:使用?
CREATE INDEX
?語句創建索引,如?CREATE INDEX idx_column_name ON table_name (column_name);
- 使用索引:在查詢語句中,數據庫會自動根據索引進行優化,提高查詢效率,如?
SELECT * FROM table_name WHERE column_name = 'value';
- 刪除索引:使用?
DROP INDEX
?語句刪除索引,如?DROP INDEX idx_column_name ON table_name;
- 要點
- 選擇合適的列創建索引:通常在經常用于查詢條件、排序和連接的列上創建索引。
- 避免創建過多的索引:過多索引會增加數據插入、更新和刪除的開銷,同時占用更多存儲空間。
- 定期維護索引:隨著數據變化,索引可能碎片化,影響查詢性能,需定期重建和優化索引。
- 應用
在電商系統的商品搜索功能中,對商品名稱、價格等列創建索引。當用戶根據商品名稱或價格范圍進行搜索時,數據庫可利用索引快速定位到相關商品記錄。
SQL 代碼示例(創建和使用索引)
sql
-- 創建商品表
CREATE TABLE products (product_id INT PRIMARY KEY,product_name VARCHAR(100),price DECIMAL(10, 2),category VARCHAR(50)
);-- 創建索引
CREATE INDEX idx_product_name ON products (product_name);
CREATE INDEX idx_price ON products (price);-- 使用索引進行查詢
SELECT * FROM products WHERE product_name = 'iPhone';
SELECT * FROM products WHERE price > 500;-- 刪除索引
DROP INDEX idx_product_name ON products;
?
8. 怎么創建表
?
- 定義
創建表是數據庫操作的基本操作,通過 SQL 語句在數據庫中創建新表。創建時需指定表名、列名、數據類型和約束條件等信息。
- 要點
- 表名:應具有描述性,清晰表達表的用途。
- 列名:也應具有描述性,清晰表達列的含義。
- 數據類型:根據數據特點和使用場景,選擇合適的數據類型,如整數類型、字符串類型、日期類型等。
- 約束條件:可為列添加約束條件,如主鍵約束、外鍵約束、唯一約束、檢查約束等,保證數據的完整性和一致性。
- 應用
在開發一個博客系統時,需要創建文章表、用戶表和評論表。通過合理創建表結構,保證系統數據的有效存儲和管理。
SQL 代碼示例(創建表)
sql
-- 創建用戶表
CREATE TABLE users (user_id INT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(50) UNIQUE NOT NULL,password VARCHAR(100) NOT NULL,email VARCHAR(100) UNIQUE,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);-- 創建文章表
CREATE TABLE articles (article_id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(200) NOT NULL,content TEXT,author_id INT,published_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY (author_id) REFERENCES users(user_id)
);-- 創建評論表
CREATE TABLE comments (comment_id INT PRIMARY KEY AUTO_INCREMENT,article_id INT,user_id INT,comment_text TEXT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY (article_id) REFERENCES articles(article_id),FOREIGN KEY (user_id) REFERENCES users(user_id)
);
?
9. 已知兩條 SQL 語句, 如何根據這兩條語句建索引
?
- 定義
根據 SQL 語句創建索引是為提高查詢性能。需要分析 SQL 語句中的查詢條件、排序條件和連接條件,在這些條件涉及的列上創建索引。
- 要點
- 查詢條件:在經常用于?
WHERE
?子句的列上創建索引,如?WHERE column_name = 'value'
。 - 排序條件:在經常用于?
ORDER BY
?子句的列上創建索引,如?ORDER BY column_name
。 - 連接條件:在經常用于?
JOIN
?子句的列上創建索引,如?JOIN table_name ON table1.column_name = table2.column_name
。
- 應用
在一個圖書管理系統中,有兩條 SQL 語句:一條用于查詢某一分類下價格大于 50 元的圖書,另一條用于按圖書出版日期排序查詢。可根據這兩條語句在分類列、價格列和出版日期列上創建索引。
SQL 代碼示例(根據 SQL 語句創建索引)
sql
-- 假設有兩條 SQL 語句
-- 語句 1: SELECT * FROM books WHERE category = 'fiction' AND price > 50;
-- 語句 2: SELECT * FROM books ORDER BY publish_date;-- 創建圖書表
CREATE TABLE books (book_id INT PRIMARY KEY,title VARCHAR(200),category VARCHAR(50),price DECIMAL(10, 2),publish_date DATE
);-- 根據語句 1 創建復合索引
CREATE INDEX idx_category_price ON books (category, price);-- 根據語句 2 創建索引
CREATE INDEX idx_publish_date ON books (publish_date);
?
10. select 語句實現順序
?
- 定義
SELECT
?語句的執行順序并非按照語句中各子句的書寫順序,而是遵循特定的邏輯順序。數據庫先確定查詢范圍和條件,再進行數據篩選、分組、排序等操作,最后返回結果。
- 要點
SELECT
?語句的執行順序如下:
- FROM:指定要查詢的表或視圖。
- JOIN:若有連接操作,進行表的連接。
- WHERE:根據條件篩選記錄。
- GROUP BY:對篩選后的記錄進行分組。
- HAVING:對分組后的結果進行篩選。
- SELECT:選擇要返回的列。
- DISTINCT:去除重復的記錄。
- ORDER BY:對結果進行排序。
- LIMIT:限制返回的記錄數量。
- 應用
在學生成績統計系統中,要統計每個班級的平均成績,并篩選出平均成績大于 80 分的班級,按平均成績降序排列,取前 5 個班級。通過了解?SELECT
?語句的執行順序,可正確編寫查詢語句。
SQL 代碼示例(演示?SELECT
?語句執行順序)
sql
-- 假設有兩個表:students 和 scores
-- students 表包含 student_id, class_id, name 列
-- scores 表包含 score_id, student_id, score 列SELECT s.class_id, AVG(sc.score) AS average_score
FROM students s
JOIN scores sc ON s.student_id = sc.student_id
WHERE sc.score > 60
GROUP BY s.class_id
HAVING AVG(sc.score) > 80
ORDER BY average_score DESC
LIMIT 5;
在這個示例中,數據庫先執行?FROM
?和?JOIN
?操作,連接?students
?表和?scores
?表;接著執行?WHERE
?子句,篩選出成績大于 60 分的記錄;然后執行?GROUP BY
?操作,按班級 ID 分組;再執行?HAVING
?子句,篩選出平均成績大于 80 分的班級;之后執行?SELECT
?操作,選擇班級 ID 和平均成績;最后執行?ORDER BY
?和?LIMIT
?操作,對結果排序并限制返回的記錄數量。
友情提示:本文已經整理成文檔,可以到如下鏈接免積分下載閱讀
https://download.csdn.net/download/ylfhpy/90567261