数据库基础知识 🗄️

Li JiaXin大约 10 分钟

数据库基础知识 🗄️

嘿！欢迎来到"数据的豪华公寓"！🏢

数据库就像一个超级豪华的公寓楼，里面住着各种各样的数据"居民"。作为测试开发工程师，我们就像这个公寓的"物业管理员"，要确保每个居民都住得舒服，邻里关系和谐，不会出现"数据打架"的情况。

如果你曾经被SQL语句搞得头晕眼花，如果你曾经因为数据库死锁而抓狂，那么这篇文章就是你的"救命稻草"！我们用最接地气的方式，带你探索数据库的奥秘。

关系型数据库基础 - "数据公寓的建筑规范" 🏗️

关系型数据库就像一个按照严格规范建造的公寓楼，每个房间都有明确的用途和规则。

关系模型核心概念 - "公寓楼的基本构造"

基本术语 - "公寓楼的专业名词"

关系（Relation）：就是表（Table），相当于公寓楼的一层楼
元组（Tuple）：就是行（Row），相当于楼层里的一个房间
属性（Attribute）：就是列（Column），相当于房间里的家具类型
域（Domain）：属性的取值范围，相当于家具的规格限制
主键（Primary Key）：房间的唯一门牌号，绝对不能重复
外键（Foreign Key）：指向其他楼层房间的"地址簿"

关系完整性约束 - "公寓楼的管理规定"

实体完整性：每个房间必须有门牌号（主键不能为空）
参照完整性：地址簿里的地址必须真实存在（外键必须有效）
用户定义完整性：公寓楼的特殊规定（业务规则约束）

测试要点 - "检查公寓楼的管理是否到位"

-- 测试主键约束 - "能不能有两个相同门牌号的房间？"
INSERT INTO users (id, name) VALUES (1, 'Alice');
INSERT INTO users (id, name) VALUES (1, 'Bob'); -- 应该失败，门牌号重复了！

-- 测试外键约束 - "能不能给不存在的用户下订单？"
INSERT INTO orders (user_id, product) VALUES (999, 'Book'); -- 应该失败，999号用户不存在！

-- 测试非空约束 - "能不能有没有名字的用户？"
INSERT INTO users (id, name) VALUES (2, NULL); -- 应该失败，总得有个名字吧！

形象比喻：

数据库像公寓楼
表像楼层
行像房间
列像房间里的家具
主键像门牌号
外键像地址簿

数据库设计范式

第一范式（1NF）

要求：属性不可再分
测试场景：验证数据的原子性

第二范式（2NF）

要求：满足1NF，且非主属性完全依赖于主键
测试场景：检查部分依赖问题

第三范式（3NF）

要求：满足2NF，且非主属性不传递依赖于主键
测试场景：验证数据冗余控制

反范式化考虑

性能优化：适当冗余提高查询效率
测试要点：数据一致性维护

SQL语言详解

数据定义语言（DDL）

表结构操作

-- 创建表
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) CHECK (price > 0),
    category_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- 修改表结构
ALTER TABLE products ADD COLUMN description TEXT;
ALTER TABLE products MODIFY COLUMN name VARCHAR(200);
ALTER TABLE products DROP COLUMN description;

-- 删除表
DROP TABLE products;

索引管理

-- 创建索引
CREATE INDEX idx_product_name ON products(name);
CREATE UNIQUE INDEX idx_product_code ON products(code);
CREATE INDEX idx_product_category_price ON products(category_id, price);

-- 删除索引
DROP INDEX idx_product_name ON products;

数据操作语言（DML）

查询操作（SELECT）

-- 基本查询
SELECT id, name, price FROM products WHERE price > 100;

-- 连接查询
SELECT p.name, c.name as category_name
FROM products p
INNER JOIN categories c ON p.category_id = c.id;

-- 聚合查询
SELECT category_id, COUNT(*), AVG(price), MAX(price), MIN(price)
FROM products
GROUP BY category_id
HAVING COUNT(*) > 5;

-- 子查询
SELECT name FROM products
WHERE price > (SELECT AVG(price) FROM products);

数据修改操作

-- 插入数据
INSERT INTO products (name, price, category_id) VALUES ('Laptop', 999.99, 1);

-- 批量插入
INSERT INTO products (name, price, category_id) VALUES
('Mouse', 29.99, 2),
('Keyboard', 79.99, 2),
('Monitor', 299.99, 3);

-- 更新数据
UPDATE products SET price = price * 0.9 WHERE category_id = 1;

-- 删除数据
DELETE FROM products WHERE price < 10;

数据控制语言（DCL）

权限管理

-- 创建用户
CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'password';

-- 授权
GRANT SELECT, INSERT, UPDATE ON test_db.* TO 'test_user'@'localhost';

-- 撤销权限
REVOKE INSERT ON test_db.* FROM 'test_user'@'localhost';

-- 删除用户
DROP USER 'test_user'@'localhost';

事务处理机制

ACID特性详解

原子性（Atomicity）测试

-- 测试事务回滚
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 模拟错误
INSERT INTO invalid_table VALUES (1); -- 这会导致整个事务回滚
COMMIT;

一致性（Consistency）测试

-- 测试约束检查
START TRANSACTION;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
-- 如果余额变为负数，应该违反约束
COMMIT;

隔离性（Isolation）测试

-- 会话1
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1; -- 读取初始值
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 此时不提交，让会话2读取

-- 会话2（同时执行）
SELECT balance FROM accounts WHERE id = 1; -- 根据隔离级别，可能看到不同结果

持久性（Durability）测试

测试系统崩溃后数据恢复
验证已提交事务的持久化
检查日志文件的完整性

事务隔离级别

READ UNCOMMITTED

特点：可以读取未提交的数据
问题：脏读、不可重复读、幻读
测试场景：验证脏读现象

READ COMMITTED

特点：只能读取已提交的数据
问题：不可重复读、幻读
测试场景：验证不可重复读

REPEATABLE READ

特点：同一事务内重复读取结果一致
问题：幻读
测试场景：验证幻读现象

SERIALIZABLE

特点：最高隔离级别，串行执行
问题：性能较低
测试场景：并发性能测试

并发控制机制

锁机制

-- 共享锁（读锁）
SELECT * FROM products WHERE id = 1 LOCK IN SHARE MODE;

-- 排他锁（写锁）
SELECT * FROM products WHERE id = 1 FOR UPDATE;

-- 测试死锁
-- 会话1
START TRANSACTION;
UPDATE products SET price = 100 WHERE id = 1;
UPDATE products SET price = 200 WHERE id = 2;

-- 会话2（同时执行）
START TRANSACTION;
UPDATE products SET price = 300 WHERE id = 2;
UPDATE products SET price = 400 WHERE id = 1; -- 可能导致死锁

多版本并发控制（MVCC）

原理：为每个事务提供数据的快照
优势：读写不冲突，提高并发性能
测试要点：版本链的正确性、垃圾回收机制

索引优化

索引类型

B+树索引

特点：平衡多路搜索树，叶子节点存储数据
适用场景：范围查询、排序操作
测试要点：查询性能、索引维护开销

哈希索引

特点：基于哈希表，等值查询快
限制：不支持范围查询、排序
测试场景：等值查询性能测试

全文索引

用途：文本搜索优化
测试要点：搜索准确性、性能表现

索引设计原则

选择性原则

-- 计算列的选择性
SELECT COUNT(DISTINCT column_name) / COUNT(*) as selectivity
FROM table_name;

-- 选择性高的列适合建索引

复合索引设计

-- 最左前缀原则
CREATE INDEX idx_user_age_city ON users(age, city);

-- 可以使用的查询
SELECT * FROM users WHERE age = 25; -- 使用索引
SELECT * FROM users WHERE age = 25 AND city = 'Beijing'; -- 使用索引
SELECT * FROM users WHERE city = 'Beijing'; -- 不使用索引

查询优化

执行计划分析

-- MySQL
EXPLAIN SELECT * FROM products WHERE price > 100;

-- PostgreSQL
EXPLAIN ANALYZE SELECT * FROM products WHERE price > 100;

常见优化技巧

-- 避免SELECT *
SELECT id, name FROM products WHERE category_id = 1;

-- 使用LIMIT限制结果集
SELECT * FROM products ORDER BY created_at DESC LIMIT 10;

-- 避免在WHERE子句中使用函数
-- 不好的写法
SELECT * FROM orders WHERE YEAR(created_at) = 2023;
-- 好的写法
SELECT * FROM orders WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';

数据库测试策略

功能测试

CRUD操作测试

def test_database_crud():
    # 创建测试
    user_id = create_user("test_user", "test@example.com")
    assert user_id is not None
    
    # 读取测试
    user = get_user(user_id)
    assert user['name'] == "test_user"
    assert user['email'] == "test@example.com"
    
    # 更新测试
    update_user(user_id, name="updated_user")
    updated_user = get_user(user_id)
    assert updated_user['name'] == "updated_user"
    
    # 删除测试
    delete_user(user_id)
    deleted_user = get_user(user_id)
    assert deleted_user is None

约束测试

def test_database_constraints():
    # 主键约束测试
    with pytest.raises(IntegrityError):
        create_user_with_id(1, "user1")
        create_user_with_id(1, "user2")  # 应该失败
    
    # 外键约束测试
    with pytest.raises(IntegrityError):
        create_order(user_id=999, product="book")  # 不存在的用户
    
    # 非空约束测试
    with pytest.raises(IntegrityError):
        create_user(None, "test@example.com")  # 名字不能为空

性能测试

查询性能测试

def test_query_performance():
    # 准备测试数据
    create_test_data(10000)  # 创建1万条记录
    
    # 测试查询性能
    start_time = time.time()
    results = query_products_by_category(1)
    end_time = time.time()
    
    execution_time = end_time - start_time
    assert execution_time < 0.1  # 查询应该在100ms内完成
    assert len(results) > 0

并发测试

def test_concurrent_access():
    def update_balance(user_id, amount):
        with database.transaction():
            current_balance = get_balance(user_id)
            new_balance = current_balance + amount
            update_user_balance(user_id, new_balance)
    
    # 并发更新同一用户余额
    threads = []
    for i in range(10):
        thread = threading.Thread(target=update_balance, args=(1, 10))
        threads.append(thread)
        thread.start()
    
    for thread in threads:
        thread.join()
    
    # 验证最终余额正确性
    final_balance = get_balance(1)
    expected_balance = initial_balance + 10 * 10
    assert final_balance == expected_balance

数据一致性测试

事务一致性测试

def test_transaction_consistency():
    initial_total = get_total_balance()
    
    try:
        with database.transaction():
            transfer_money(user1_id, user2_id, 100)
            # 模拟异常
            raise Exception("Simulated error")
    except:
        pass
    
    # 验证总余额未变化（事务回滚）
    final_total = get_total_balance()
    assert final_total == initial_total

数据完整性测试

def test_data_integrity():
    # 测试级联删除
    category_id = create_category("Electronics")
    product_id = create_product("Laptop", category_id)
    
    delete_category(category_id)
    
    # 验证相关产品也被删除
    product = get_product(product_id)
    assert product is None