关系型数据库MySQL（二）：高级特性-洪萨配资

1. 索引 (Indexes)

理论说明：索引是数据库中用于加速数据检索的一种数据结构。它类似于书籍的目录，允许数据库系统快速定位到数据行，而无需扫描整个表。MySQL 支持多种索引类型：

主键索引 (PRIMARY KEY)：唯一标识表中每一行的索引，不允许 NULL 值。每个表只能有一个主键。
唯一索引 (UNIQUE)：确保索引列中的值是唯一的，允许 NULL 值（但最多只能有一个 NULL）。
普通索引 (INDEX 或 KEY)：最基本的索引类型，仅用于加速查询。
全文索引 (FULLTEXT)：用于对文本内容 (CHAR,VARCHAR,TEXT) 进行全文搜索。
组合索引 (Composite Index)：基于多个列的索引。

代码示例 (创建索引)：

-- 创建表时定义主键索引 (隐式创建) CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, -- 主键索引 username VARCHAR(50) NOT NULL UNIQUE, -- 唯一索引 email VARCHAR(100), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- 创建表后添加普通索引 CREATE INDEX idx_users_email ON users (email); -- 创建组合索引 (常用于 WHERE 条件涉及多个列或覆盖索引) CREATE INDEX idx_users_name_email ON users (username, email); -- 创建全文索引 (表引擎需为 MyISAM 或 InnoDB >= 5.6) ALTER TABLE articles ADD FULLTEXT INDEX idx_articles_content (content);

查询使用索引 (EXPLAIN)：

-- 查看查询是否使用了索引及其类型 EXPLAIN SELECT * FROM users WHERE username = 'john_doe';

关键点：

索引能极大提高SELECT、WHERE、ORDER BY、GROUP BY、JOIN的速度。
索引会占用磁盘空间，并降低INSERT、UPDATE、DELETE的速度（因为索引也需要维护）。
选择合适的列创建索引（高选择性列、常用于查询条件的列）。
避免在频繁更新的列上创建过多索引。
组合索引遵循“最左前缀原则”。
使用EXPLAIN分析查询执行计划，确认索引使用情况。

2. CHECK 约束 (MySQL 8.0+)

理论说明：CHECK约束用于限制列中可接受的值范围。它确保列中的数据满足指定的条件（布尔表达式）。在 MySQL 8.0.16 之前，CHECK约束会被解析但会被忽略（语法兼容）。从 8.0.16 开始，MySQL 开始支持强制执行的CHECK约束。

代码示例：

-- 创建表时定义 CHECK 约束 CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(100), age INT, salary DECIMAL(10, 2), -- 确保年龄在 18 到 65 之间 CONSTRAINT chk_age CHECK (age >= 18 AND age <= 65), -- 确保薪水非负 CONSTRAINT chk_salary CHECK (salary >= 0) ); -- 向现有表添加 CHECK 约束 ALTER TABLE employees ADD CONSTRAINT chk_salary_nonnegative CHECK (salary >= 0); -- 违反 CHECK 约束的插入会被拒绝 INSERT INTO employees (id, name, age, salary) VALUES (1, 'Alice', 17, 5000); -- 失败，年龄不符合 INSERT INTO employees (id, name, age, salary) VALUES (2, 'Bob', 30, -1000); -- 失败，薪水为负

关键点：

用于保证域完整性（Domain Integrity）。
MySQL 8.0.16+ 版本才真正强制执行。
约束名 (CONSTRAINT constraint_name) 是可选的，但最好指定以方便管理。
可以定义在列级别或表级别。

3. 存储过程 (Stored Procedures)

理论说明：存储过程是存储在数据库中的一组预编译的 SQL 语句。它像一个函数，可以接受参数、执行逻辑操作（如条件判断、循环）并返回结果。优点包括：

减少网络流量：客户端只需调用存储过程名和参数，而不是发送多条 SQL 语句。
提高性能：预编译，执行效率通常更高。
代码复用和封装：业务逻辑封装在数据库层，便于维护和共享。
增强安全性：可控制对底层表的访问权限。

代码示例 (创建和调用)：

-- 示例：创建一个简单的存储过程，根据部门 ID 获取员工数量 DELIMITER $$ -- 更改分隔符，以便在过程中使用分号 CREATE PROCEDURE GetEmployeeCountByDept(IN dept_id INT, OUT emp_count INT) BEGIN SELECT COUNT(*) INTO emp_count FROM employees WHERE department_id = dept_id; END $$ DELIMITER ; -- 恢复分隔符 -- 调用存储过程 CALL GetEmployeeCountByDept(10, @count); SELECT @count; -- 查看输出参数的值 -- 示例：带 INOUT 参数和逻辑的存储过程（更新薪水并记录日志） DELIMITER $$ CREATE PROCEDURE AdjustSalary(INOUT employee_id INT, IN raise_amount DECIMAL(10,2)) BEGIN DECLARE old_salary DECIMAL(10,2); START TRANSACTION; -- 开始事务（后面会讲） SELECT salary INTO old_salary FROM employees WHERE id = employee_id FOR UPDATE; UPDATE employees SET salary = salary + raise_amount WHERE id = employee_id; INSERT INTO salary_log (emp_id, old_salary, new_salary, change_date) VALUES (employee_id, old_salary, old_salary + raise_amount, NOW()); COMMIT; -- 提交事务 SET employee_id = employee_id; -- INOUT 参数通常需要被设置 END $$ DELIMITER ; -- 调用 SET @emp_id = 123; SET @raise = 500; CALL AdjustSalary(@emp_id, @raise); SELECT @emp_id; -- 查看 INOUT 参数

关键点：

使用DELIMITER更改语句分隔符以定义过程体。
参数类型：IN(输入),OUT(输出),INOUT(输入输出)。
过程体中可以包含变量声明 (DECLARE)、条件语句 (IF...THEN...ELSE)、循环 (WHILE,LOOP,REPEAT)、游标 (CURSOR) 等。
调用使用CALL procedure_name([parameters])。
使用SHOW CREATE PROCEDURE procedure_name查看定义。

4. 触发器 (Triggers)

理论说明：触发器是与表相关联的命名数据库对象。它在表上发生特定事件（INSERT,UPDATE,DELETE）之前或之后自动执行。常用于：

审计（记录数据变更）
数据验证（比CHECK更复杂的约束）
维护衍生数据（如自动更新汇总表）
实施业务规则

代码示例：

-- 创建审计日志表 CREATE TABLE user_audit_log ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT, action VARCHAR(10), -- 'INSERT', 'UPDATE', 'DELETE' old_data JSON, -- 存储更改前的数据 (可选) new_data JSON, -- 存储更改后的数据 (可选) changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, changed_by VARCHAR(50) -- 记录操作者 (可通过应用或 USER() 函数设置) ); -- 创建 AFTER UPDATE 触发器：记录 users 表的更新 DELIMITER $$ CREATE TRIGGER trg_users_after_update AFTER UPDATE ON users FOR EACH ROW -- 行级触发器 BEGIN INSERT INTO user_audit_log (user_id, action, old_data, new_data, changed_by) VALUES ( OLD.id, 'UPDATE', JSON_OBJECT('username', OLD.username, 'email', OLD.email), JSON_OBJECT('username', NEW.username, 'email', NEW.email), USER() -- 当前 MySQL 用户 ); END $$ DELIMITER ; -- 创建 BEFORE INSERT 触发器：确保新用户的 email 不为空且格式大致正确 DELIMITER $$ CREATE TRIGGER trg_users_before_insert BEFORE INSERT ON users FOR EACH ROW BEGIN IF NEW.email IS NULL OR NEW.email = '' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Email cannot be empty'; END IF; -- 更复杂的验证可以用正则表达式，但 MySQL 原生支持有限，通常需在应用层或存储过程做 IF NEW.email NOT LIKE '%_@__%.__%' THEN -- 非常简单的格式检查 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Email format appears invalid'; END IF; END $$ DELIMITER ; -- 测试触发器 UPDATE users SET email = 'new.email@example.com' WHERE id = 1; -- 会在 audit_log 插入记录 INSERT INTO users (username, email) VALUES ('test', ''); -- 会被 BEFORE INSERT 触发器阻止

关键点：

触发时机：BEFORE(操作前),AFTER(操作后)。
触发事件：INSERT,UPDATE,DELETE。
行级触发 (FOR EACH ROW)：针对受影响的每一行数据执行一次。
在触发器内部：
- 使用OLD访问更新或删除前的行值 (UPDATE,DELETE)。
- 使用NEW访问要插入或更新后的行值 (INSERT,UPDATE)。
- 使用SIGNAL可以主动引发错误，阻止操作。
谨慎使用触发器，因为它们会隐式执行，可能影响性能且调试困难。逻辑尽量放在应用层或存储过程。

5. 事务 (Transactions)

理论说明：事务是数据库操作的逻辑单元，它包含一个或多个 SQL 语句。事务具有 ACID 属性：

原子性 (Atomicity)：事务中的所有操作要么全部完成，要么全部不完成。如果中间出错，已执行的操作会回滚。
一致性 (Consistency)：事务必须使数据库从一个一致性状态转变到另一个一致性状态。它保证数据的完整性约束不被破坏。
隔离性 (Isolation)：并发执行的事务之间互不干扰。一个事务的操作对其他并发事务是隔离的（不同隔离级别效果不同）。
持久性 (Durability)：一旦事务提交，它对数据库的修改就是永久性的，即使系统崩溃也能恢复。

代码示例：

-- 开始一个事务 START TRANSACTION; -- 或 BEGIN -- 执行一系列操作 UPDATE accounts SET balance = balance - 100 WHERE user_id = 1; -- 用户1扣款100 UPDATE accounts SET balance = balance + 100 WHERE user_id = 2; -- 用户2收款100 -- 根据业务逻辑决定提交或回滚 IF (SELECT balance FROM accounts WHERE user_id = 1) >= 0 THEN -- 检查余额是否充足 COMMIT; -- 提交事务，使更改永久生效 ELSE ROLLBACK; -- 回滚事务，撤销所有更改 END IF; -- 设置事务隔离级别 (会话级别) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 设置自动提交模式 (默认通常是 ON) SET autocommit = 0; -- 关闭自动提交，需要显式 COMMIT 或 ROLLBACK SET autocommit = 1; -- 开启自动提交，每条语句都是一个事务

关键点：

使用START TRANSACTION或BEGIN显式开始事务。
使用COMMIT提交事务，使更改永久生效。
使用ROLLBACK回滚事务，撤销自事务开始以来的所有更改。
隔离级别：控制并发事务如何相互“看见”对方的数据。
- READ UNCOMMITTED：可能读到未提交的数据（脏读）。
- READ COMMITTED(常用)：只能读到已提交的数据，避免脏读。但同一事务内重复读同一行可能结果不同（不可重复读）。
- REPEATABLE READ(InnoDB 默认)：保证在同一事务中多次读取同一行结果一致。避免脏读和不可重复读。可能发生幻读（读取范围时出现新行）。
- SERIALIZABLE：最高隔离级别，强制事务串行执行。避免所有并发问题（脏读、不可重复读、幻读），但性能最低。
理解不同隔离级别的影响对并发编程至关重要。

6. 锁 (Locking)

理论说明：锁是数据库用于管理并发访问共享资源的机制，以确保事务的隔离性和数据的一致性。MySQL InnoDB 引擎主要使用行级锁和表级锁。

共享锁 (Shared Lock / S Lock / 读锁)：允许事务读取一行。多个事务可以同时持有同一行的共享锁。
排他锁 (Exclusive Lock / X Lock / 写锁)：允许事务更新或删除一行。一个事务持有某行的排他锁时，其他事务不能对该行加任何锁（共享或排他）。
表级锁：对整个表加锁（如LOCK TABLES ... READ/WRITE），粒度粗，影响并发性能。InnoDB 通常使用行锁，但在特定操作（如 DDL 语句ALTER TABLE）或显式请求时也会使用表锁。
意向锁 (Intention Lock)：InnoDB 的表级锁，表示事务稍后会对表中的某些行加共享锁 (IS) 或排他锁 (IX)。用于快速判断表级冲突。

代码示例 (显式加锁)：

-- 显式加行级锁 (在事务内) START TRANSACTION; -- 对 id=1 的用户记录加排他锁 (FOR UPDATE)，其他事务尝试更新或加锁会被阻塞 SELECT * FROM users WHERE id = 1 FOR UPDATE; -- ... 执行一些操作，比如更新该用户 ... UPDATE users SET last_login = NOW() WHERE id = 1; COMMIT; -- 显式加表级锁 (谨慎使用，会阻塞整个表的并发访问) LOCK TABLES users WRITE; -- 获取 users 表的写锁 -- ... 执行需要独占表的操作 ... UNLOCK TABLES; -- 释放所有锁

隐式锁 (InnoDB 自动管理)：大多数时候，InnoDB 会根据事务的隔离级别和操作自动获取和释放行锁。例如：

执行UPDATE ...,DELETE ...时，涉及的行会被自动加上排他锁 (X Lock)。
在REPEATABLE READ或READ COMMITTED下执行SELECT ...通常不加锁（快照读）。使用SELECT ... FOR UPDATE或SELECT ... LOCK IN SHARE MODE会显式请求锁。

关键点：

理解锁的兼容性矩阵（如 S 锁兼容 S 锁，但不兼容 X 锁）。
避免长时间持有锁，特别是排他锁。
注意锁的粒度：行锁并发度高，表锁并发度低。
死锁 (Deadlock)：两个或多个事务互相等待对方释放锁。InnoDB 能检测死锁并回滚其中一个事务（牺牲者）。应用程序应能处理死锁错误并重试。
使用SHOW ENGINE INNODB STATUS查看锁信息（需要权限）。

7. 数据库引擎 (Storage Engines)

理论说明：数据库引擎（存储引擎）是负责管理表的数据存储、索引、事务、锁等底层操作的组件。MySQL 支持多种引擎，最常用的是InnoDB和MyISAM：

InnoDB (默认引擎)：
- 事务支持：完全支持 ACID 事务。
- 锁机制：行级锁，支持高并发。
- 外键：支持外键约束。
- 崩溃恢复：支持崩溃后的自动恢复。
- 聚簇索引：数据文件本身就是按主键索引组织的 B+树。主键查询非常快。
- 适用场景：需要事务、高并发、数据完整性要求的应用（OLTP）。
MyISAM：
- 事务支持：不支持。
- 锁机制：表级锁，并发写性能差。
- 外键：不支持。
- 崩溃恢复：较差，可能损坏。
- 非聚簇索引：索引和数据是分开存储的。索引保存的是数据行的指针。
- 特点：支持全文索引（InnoDB 后来也支持了），压缩表。
- 适用场景：读密集型、不需要事务、对并发写要求低的场景（如数据仓库读取、日志表）。在现代应用中已较少使用。
其他引擎：MEMORY（内存表）、ARCHIVE（归档压缩）等。

代码示例 (指定引擎)：

-- 创建表时指定引擎 CREATE TABLE my_innodb_table ( id INT PRIMARY KEY, data VARCHAR(255) ) ENGINE=InnoDB; CREATE TABLE my_myisam_table ( id INT PRIMARY KEY, data VARCHAR(255) ) ENGINE=MyISAM; -- 查看表的引擎 SHOW TABLE STATUS LIKE 'my_innodb_table'; -- 修改现有表的引擎 (谨慎操作，可能耗时且需要表锁) ALTER TABLE my_myisam_table ENGINE = InnoDB;

关键点：

MySQL 5.5 以后，InnoDB成为默认引擎，强烈建议新表使用 InnoDB。
选择合适的引擎对性能和功能至关重要。
了解不同引擎的特性和限制。

8. 慢查询日志 (Slow Query Log)

理论说明：慢查询日志是 MySQL 记录执行时间超过指定阈值（long_query_time）或没有使用索引的查询的日志文件。它是识别和优化数据库性能瓶颈（特别是查询性能）的重要工具。

配置 (OpenEuler 下通常修改/etc/my.cnf或/etc/my.cnf.d/下的配置文件)：

[mysqld] # 启用慢查询日志 slow_query_log = 1 # 指定慢查询日志文件路径 slow_query_log_file = /var/log/mysql/mysql-slow.log # 定义慢查询阈值（单位：秒），执行时间超过此值的查询会被记录 long_query_time = 2 # 记录未使用索引的查询（即使执行时间很短） log_queries_not_using_indexes = 1 # 记录管理语句（如 ALTER TABLE, ANALYZE TABLE） log_slow_admin_statements = 1

重启 MySQL 服务使配置生效：

sudo systemctl restart mysqld

分析慢查询日志：慢查询日志是文本文件，可以使用文本编辑器查看，但更推荐使用专门的分析工具：

mysqldumpslow：MySQL 自带的命令行工具，用于汇总慢查询日志中的信息。

mysqldumpslow /var/log/mysql/mysql-slow.log # 查看总结 mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log # 按总时间排序，显示前10条 mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log # 按出现次数排序，显示前10条

pt-query-digest (Percona Toolkit)：更强大、更流行的第三方工具，提供更详细的分析报告。
```
pt-query-digest /var/log/mysql/mysql-slow.log > slow_report.txt
```
报告会显示：
- 最慢的查询
- 执行次数最多的查询
- 消耗总时间最多的查询
- 查询的样例和执行计划 (EXPLAIN)
- 锁定时间、发送行数、检查行数等统计信息

优化步骤：

识别慢查询。
使用EXPLAIN分析查询执行计划。
查看是否使用了合适的索引。
考虑重写查询（如避免SELECT *，优化JOIN顺序和条件，使用覆盖索引）。
检查表结构和索引设计。
考虑数据库参数调优（如缓冲区大小）。

关键点：

定期开启并分析慢查询日志是 DBA 和开发人员的必备工作。
结合EXPLAIN命令深入理解查询为何慢。
long_query_time的设置需要权衡，设置过低会记录太多查询，设置过高可能遗漏问题查询。

综合应用示例：订单处理系统片段

-- 1. 表定义 (使用 InnoDB) CREATE TABLE orders ( order_id INT AUTO_INCREMENT PRIMARY KEY, user_id INT, total_amount DECIMAL(10,2) NOT NULL, status ENUM('pending', 'processing', 'shipped', 'completed', 'cancelled') DEFAULT 'pending', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_orders_user_id (user_id), -- 普通索引 INDEX idx_orders_status (status), -- 状态常用于查询和统计 CONSTRAINT fk_orders_user_id FOREIGN KEY (user_id) REFERENCES users(id) -- 外键约束 ) ENGINE=InnoDB; CREATE TABLE order_items ( item_id INT AUTO_INCREMENT PRIMARY KEY, order_id INT, product_id INT, quantity INT CHECK (quantity > 0), -- CHECK 约束 price DECIMAL(10,2), FOREIGN KEY (order_id) REFERENCES orders(order_id), FOREIGN KEY (product_id) REFERENCES products(id) ) ENGINE=InnoDB; CREATE TABLE inventory ( product_id INT PRIMARY KEY, stock INT NOT NULL CHECK (stock >= 0), ... ) ENGINE=InnoDB; -- 2. 存储过程：下单 (包含事务和锁) DELIMITER $$ CREATE PROCEDURE PlaceOrder(IN p_user_id INT, IN p_items JSON) -- p_items 格式如 [{"product_id":1, "quantity":2}, ...] BEGIN DECLARE v_order_id INT; DECLARE v_total DECIMAL(10,2) DEFAULT 0; DECLARE i INT DEFAULT 0; DECLARE item_count INT; DECLARE v_product_id, v_quantity INT; DECLARE v_price DECIMAL(10,2); -- 计算总金额并检查库存 (需要锁定库存行) SET item_count = JSON_LENGTH(p_items); START TRANSACTION; WHILE i < item_count DO SET v_product_id = JSON_EXTRACT(p_items, CONCAT('$[', i, '].product_id')); SET v_quantity = JSON_EXTRACT(p_items, CONCAT('$[', i, '].quantity')); -- 检查库存并锁定行 (FOR UPDATE) SELECT stock, price INTO @v_stock, v_price FROM inventory WHERE product_id = v_product_id FOR UPDATE; IF @v_stock < v_quantity THEN ROLLBACK; SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient stock for product'; END IF; -- 临时累加金额 SET v_total = v_total + (v_price * v_quantity); SET i = i + 1; END WHILE; -- 创建订单头 INSERT INTO orders (user_id, total_amount) VALUES (p_user_id, v_total); SET v_order_id = LAST_INSERT_ID(); -- 插入订单明细并扣减库存 SET i = 0; WHILE i < item_count DO SET v_product_id = JSON_EXTRACT(p_items, CONCAT('$[', i, '].product_id')); SET v_quantity = JSON_EXTRACT(p_items, CONCAT('$[', i, '].quantity')); SELECT price INTO v_price FROM inventory WHERE product_id = v_product_id; -- 已锁定，无需再锁 INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (v_order_id, v_product_id, v_quantity, v_price); UPDATE inventory SET stock = stock - v_quantity WHERE product_id = v_product_id; SET i = i + 1; END WHILE; COMMIT; END $$ DELIMITER ; -- 3. 触发器：记录订单状态变更 DELIMITER $$ CREATE TRIGGER trg_orders_after_update AFTER UPDATE ON orders FOR EACH ROW BEGIN IF OLD.status <> NEW.status THEN INSERT INTO order_status_log (order_id, old_status, new_status, changed_at) VALUES (OLD.order_id, OLD.status, NEW.status, NOW()); END IF; END $$ DELIMITER ;

总结：MySQL 的这些高级特性（索引、约束、存储过程、触发器、事务、锁、引擎选择、慢日志分析）共同构成了其强大功能和可靠性的基础。在 OpenEuler 系统上，它们的配置和使用方式与其他 Linux 发行版基本相同。深入理解并合理运用这些特性，能够帮助开发者和 DBA 构建出高性能、高可用、数据一致且易于维护的数据库应用。务必在实践中不断尝试和优化。