第七章：高级特性

1 索引（Indexes）

索引是一种数据结构，用于加速数据库查询。类似于书的目录——不需要翻遍每一页就能定位到内容。但索引也有代价：占用存储空间，且在 INSERT/UPDATE/DELETE 时需要额外维护。

创建索引

-- 单列索引
CREATE INDEX idx_customer_email ON customers(email);

-- 复合索引（多列）
CREATE INDEX idx_order_date_customer ON orders(order_date, customer_id);

-- 唯一索引（值不允许重复）
CREATE UNIQUE INDEX idx_user_username ON users(username);

-- 创建表时直接定义索引
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(200) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(10,2),
    INDEX idx_category (category),
    INDEX idx_price (price),
    UNIQUE INDEX idx_name (product_name)
) ENGINE=InnoDB;

索引类型

复合索引与最左前缀原则

-- 复合索引 (a, b, c) 可以加速以下查询：
-- WHERE a = ?                    ✅ 使用索引
-- WHERE a = ? AND b = ?          ✅ 使用索引
-- WHERE a = ? AND b = ? AND c = ? ✅ 使用索引
-- WHERE b = ?                    ❌ 无法使用索引（缺少最左列 a）
-- WHERE a = ? AND c = ?          ⚠️ 只用到 a 列的索引

CREATE INDEX idx_abc ON orders(customer_id, order_date, status);

-- 这个索引可以优化：
SELECT * FROM orders WHERE customer_id = 100;
SELECT * FROM orders WHERE customer_id = 100 AND order_date >= '2024-01-01';
SELECT * FROM orders WHERE customer_id = 100 AND order_date >= '2024-01-01' AND status = 'paid';

EXPLAIN — 分析查询计划

-- 查看查询是否使用了索引
EXPLAIN SELECT * FROM orders WHERE customer_id = 100;

-- 关键字段：
-- type:     ALL(全表扫描), index, range, ref, eq_ref, const
-- key:      实际使用的索引名
-- rows:     预估扫描行数
-- Extra:    Using index(覆盖索引), Using where, Using filesort

-- EXPLAIN 示例输出：
-- +----+------+------+------+---------------------+------+---------+-------+------+-------+
-- | id | type | table| key  | possible_keys       | rows | filtered| Extra               |
-- +----+------+------+------+---------------------+------+---------+-------+------+-------+
-- |  1 | ref  |orders| idx_customer |idx_customer|   25 |  100.00 | NULL                |
-- +----+------+------+------+---------------------+------+---------+-------+------+-------+

EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE customer_id = 100 AND order_date > '2024-01-01';

索引管理

-- 查看表的所有索引
SHOW INDEX FROM orders;

-- 删除索引
DROP INDEX idx_customer_email ON customers;

-- ALTER TABLE 方式
ALTER TABLE customers ADD INDEX idx_phone (phone);
ALTER TABLE customers DROP INDEX idx_phone;

2 视图（Views）

视图是一张虚拟表，本质上是保存的 SELECT 查询。它不存储数据，每次查询视图时实际执行的是底层 SQL。

创建与使用视图

-- 创建视图：简化复杂查询
CREATE VIEW v_order_summary AS
SELECT
    o.id AS order_id,
    c.name AS customer_name,
    c.email,
    o.order_date,
    SUM(oi.quantity * oi.unit_price) AS total_amount,
    COUNT(oi.id) AS item_count
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY o.id, c.name, c.email, o.order_date;

-- 像普通表一样查询视图
SELECT * FROM v_order_summary
WHERE order_date >= '2024-01-01'
ORDER BY total_amount DESC
LIMIT 10;

-- 视图上也可以做聚合
SELECT
    customer_name,
    COUNT(*) AS order_count,
    SUM(total_amount) AS lifetime_value
FROM v_order_summary
GROUP BY customer_name
ORDER BY lifetime_value DESC;

视图的用途

可更新视图与只读视图

-- 简单视图（单表、无聚合）通常可更新
CREATE VIEW v_active_customers AS
SELECT id, name, email, phone
FROM customers
WHERE status = 'active';

-- 通过视图插入/更新数据
UPDATE v_active_customers SET phone = '13800138000' WHERE id = 1;

-- 包含以下特性的视图不可更新：
-- 聚合函数 (SUM, COUNT, AVG...)
-- DISTINCT / GROUP BY / HAVING
-- UNION / 子查询
-- JOIN（某些情况下可更新）

-- 替换已有视图
CREATE OR REPLACE VIEW v_active_customers AS
SELECT id, name, email, phone, created_at
FROM customers
WHERE status = 'active';

-- 删除视图
DROP VIEW IF EXISTS v_order_summary;

安全视图示例

-- 为客服团队创建脱敏视图
CREATE VIEW v_customer_support AS
SELECT
    id,
    name,
    CONCAT(LEFT(email, 3), '***@', SUBSTRING_INDEX(email, '@', -1)) AS masked_email,
    CONCAT(LEFT(phone, 3), '****', RIGHT(phone, 4)) AS masked_phone,
    created_at
FROM customers;

-- 客服只能看到脱敏后的数据
-- GRANT SELECT ON mydb.v_customer_support TO 'support_user'@'%';

3 事务（Transactions）

事务将多个 SQL 操作组合成一个不可分割的工作单元：要么全部成功，要么全部回滚。

ACID 特性

基本用法

-- 开启事务
START TRANSACTION;

-- 执行多条 SQL
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

-- 提交：所有操作永久生效
COMMIT;

-- 或者回滚：撤销所有操作
-- ROLLBACK;

银行转账示例（完整版）

-- 场景：用户 A 向用户 B 转账 500 元
START TRANSACTION;

-- 检查余额是否充足
SELECT balance INTO @balance FROM accounts WHERE id = 1 FOR UPDATE;

-- 余额不足时回滚
-- 在存储过程中可用 IF 判断，这里用注释说明逻辑

-- 扣款
UPDATE accounts SET balance = balance - 500 WHERE id = 1;

-- 入账
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

-- 记录转账流水
INSERT INTO transactions (from_account, to_account, amount, created_at)
VALUES (1, 2, 500.00, NOW());

-- 确认没有问题，提交
COMMIT;

SAVEPOINT — 部分回滚

START TRANSACTION;

INSERT INTO orders (customer_id, amount) VALUES (1, 100);
SAVEPOINT sp_order;

INSERT INTO order_items (order_id, product_id, quantity) VALUES (LAST_INSERT_ID(), 10, 2);
-- 假设这条插入出了问题
ROLLBACK TO sp_order;
-- 回滚到 SAVEPOINT，orders 的 INSERT 仍然有效

-- 修正后重新插入
INSERT INTO order_items (order_id, product_id, quantity) VALUES (LAST_INSERT_ID(), 10, 1);

COMMIT;

隔离级别

-- 查看当前隔离级别
SELECT @@transaction_isolation;

-- 设置会话级别的隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- MySQL 默认：REPEATABLE READ
-- 通过 MVCC + gap lock 防止幻读
SELECT @@transaction_isolation;
-- 'REPEATABLE-READ'

-- PostgreSQL 默认：READ COMMITTED
-- 每条语句看到的是该语句开始时刻的快照
-- SHOW default_transaction_isolation;
-- 'read committed'

-- 实际项目中，很多团队在 MySQL 上也改用 READ COMMITTED：
-- SET GLOBAL transaction_isolation = 'READ-COMMITTED';
-- 减少 gap lock 带来的锁冲突，在高并发场景下性能更好

4 存储过程与函数

存储过程是预编译并存储在数据库中的一组 SQL 语句，可以包含逻辑控制（IF、LOOP 等）。函数与存储过程类似，但必须返回一个值。

创建存储过程

-- 修改分隔符（避免 ; 与过程体内的 ; 冲突）
DELIMITER //

CREATE PROCEDURE sp_get_customer_orders(
    IN p_customer_id INT,
    OUT p_order_count INT,
    OUT p_total_amount DECIMAL(12,2)
)
BEGIN
    SELECT COUNT(*), COALESCE(SUM(amount), 0)
    INTO p_order_count, p_total_amount
    FROM orders
    WHERE customer_id = p_customer_id;
END //

DELIMITER ;

-- 调用存储过程
CALL sp_get_customer_orders(1, @cnt, @total);
SELECT @cnt AS order_count, @total AS total_amount;

变量与流程控制

DELIMITER //

CREATE PROCEDURE sp_apply_discount(
    IN p_category VARCHAR(50),
    IN p_discount_pct DECIMAL(5,2)
)
BEGIN
    DECLARE v_count INT DEFAULT 0;
    DECLARE v_affected INT;

    SELECT COUNT(*) INTO v_count
    FROM products
    WHERE category = p_category;

    IF v_count = 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '指定类别不存在任何商品';
    ELSEIF p_discount_pct < 0 OR p_discount_pct > 100 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '折扣比例必须在 0-100 之间';
    ELSE
        UPDATE products
        SET price = price * (1 - p_discount_pct / 100)
        WHERE category = p_category;

        SET v_affected = ROW_COUNT();
        SELECT CONCAT('已更新 ', v_affected, ' 件商品的价格') AS result;
    END IF;
END //

DELIMITER ;

循环与游标

DELIMITER //

CREATE PROCEDURE sp_recalc_order_totals()
BEGIN
    DECLARE v_done INT DEFAULT FALSE;
    DECLARE v_order_id INT;
    DECLARE v_total DECIMAL(12,2);

    DECLARE cur CURSOR FOR
        SELECT id FROM orders WHERE status = 'pending';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO v_order_id;
        IF v_done THEN
            LEAVE read_loop;
        END IF;

        SELECT SUM(quantity * unit_price) INTO v_total
        FROM order_items
        WHERE order_id = v_order_id;

        UPDATE orders
        SET amount = COALESCE(v_total, 0)
        WHERE id = v_order_id;
    END LOOP;

    CLOSE cur;

    SELECT '订单金额重新计算完成' AS result;
END //

DELIMITER ;

自定义函数

DELIMITER //

CREATE FUNCTION fn_format_price(p_price DECIMAL(10,2))
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    IF p_price >= 10000 THEN
        RETURN CONCAT('¥', FORMAT(p_price / 10000, 2), '万');
    ELSE
        RETURN CONCAT('¥', FORMAT(p_price, 2));
    END IF;
END //

DELIMITER ;

-- 在 SQL 中直接使用函数
SELECT product_name, fn_format_price(price) AS display_price
FROM products;

管理存储过程

-- 查看所有存储过程
SHOW PROCEDURE STATUS WHERE Db = 'mydb';

-- 查看过程定义
SHOW CREATE PROCEDURE sp_get_customer_orders;

-- 删除
DROP PROCEDURE IF EXISTS sp_get_customer_orders;
DROP FUNCTION IF EXISTS fn_format_price;

5 触发器（Triggers）

触发器是绑定在表上的特殊存储过程，当 INSERT、UPDATE 或 DELETE 事件发生时自动执行。可以设置在操作之前（BEFORE）或之后（AFTER）触发。

自动记录变更日志

-- 创建审计日志表
CREATE TABLE audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(50),
    action VARCHAR(10),
    record_id INT,
    old_values JSON,
    new_values JSON,
    changed_by VARCHAR(100),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 产品价格变更触发器
DELIMITER //

CREATE TRIGGER trg_products_after_update
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    IF OLD.price != NEW.price THEN
        INSERT INTO audit_log (table_name, action, record_id, old_values, new_values, changed_by)
        VALUES (
            'products',
            'UPDATE',
            NEW.id,
            JSON_OBJECT('price', OLD.price, 'product_name', OLD.product_name),
            JSON_OBJECT('price', NEW.price, 'product_name', NEW.product_name),
            CURRENT_USER()
        );
    END IF;
END //

DELIMITER ;

BEFORE 触发器 — 数据校验

DELIMITER //

CREATE TRIGGER trg_orders_before_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    IF NEW.amount < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '订单金额不能为负数';
    END IF;

    IF NEW.order_date IS NULL THEN
        SET NEW.order_date = CURDATE();
    END IF;
END //

DELIMITER ;

自动更新库存

DELIMITER //

CREATE TRIGGER trg_order_items_after_insert
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    UPDATE products
    SET stock = stock - NEW.quantity
    WHERE id = NEW.product_id;
END //

CREATE TRIGGER trg_order_items_after_delete
AFTER DELETE ON order_items
FOR EACH ROW
BEGIN
    UPDATE products
    SET stock = stock + OLD.quantity
    WHERE id = OLD.product_id;
END //

DELIMITER ;

管理触发器

-- 查看触发器
SHOW TRIGGERS FROM mydb;

-- 删除触发器
DROP TRIGGER IF EXISTS trg_products_after_update;

6 用户与权限

MySQL 的权限系统精细到表和列级别，遵循最小权限原则是保障数据库安全的基础。

创建用户

-- 创建用户（仅允许从本机连接）
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'StrongP@ss2024!';

-- 创建用户（允许从任意主机连接）
CREATE USER 'app_user'@'%' IDENTIFIED BY 'StrongP@ss2024!';

-- 创建用户（限制特定 IP 段）
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'StrongP@ss2024!';

-- 修改密码
ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'NewP@ss2024!';

-- 删除用户
DROP USER IF EXISTS 'temp_user'@'%';

授权（GRANT）

-- 授予特定数据库的查询权限
GRANT SELECT ON mydb.* TO 'readonly_user'@'%';

-- 授予特定表的 CRUD 权限
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.orders TO 'app_user'@'localhost';

-- 授予所有权限
GRANT ALL PRIVILEGES ON mydb.* TO 'admin_user'@'localhost';

-- 授予特定列的权限
GRANT SELECT (id, name, email), UPDATE (email) ON mydb.customers TO 'support_user'@'%';

-- 授予创建视图的权限
GRANT CREATE VIEW ON mydb.* TO 'analyst_user'@'%';

-- 使授权立即生效
FLUSH PRIVILEGES;

撤销权限（REVOKE）

-- 撤销特定权限
REVOKE DELETE ON mydb.orders FROM 'app_user'@'localhost';

-- 撤销所有权限
REVOKE ALL PRIVILEGES ON mydb.* FROM 'admin_user'@'localhost';

-- 查看用户权限
SHOW GRANTS FOR 'app_user'@'localhost';

-- 查看当前用户权限
SHOW GRANTS FOR CURRENT_USER();

常用权限速查

生产环境最佳实践

-- 为应用创建专用账号（仅必要权限）
CREATE USER 'myapp'@'192.168.%.%' IDENTIFIED BY 'AppSecurePass!';
GRANT SELECT, INSERT, UPDATE, DELETE ON production_db.* TO 'myapp'@'192.168.%.%';

-- 为数据分析师创建只读账号
CREATE USER 'analyst'@'%' IDENTIFIED BY 'AnalystPass!';
GRANT SELECT ON production_db.* TO 'analyst'@'%';

-- 为 DBA 创建管理账号
CREATE USER 'dba_admin'@'10.0.0.%' IDENTIFIED BY 'DbaStr0ngP@ss!';
GRANT ALL PRIVILEGES ON *.* TO 'dba_admin'@'10.0.0.%' WITH GRANT OPTION;

FLUSH PRIVILEGES;

7 本章要点