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.emailexample.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; -- 对 id1 的用户记录加排他锁 (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和MyISAMInnoDB (默认引擎)事务支持完全支持 ACID 事务。锁机制行级锁支持高并发。外键支持外键约束。崩溃恢复支持崩溃后的自动恢复。聚簇索引数据文件本身就是按主键索引组织的 B树。主键查询非常快。适用场景需要事务、高并发、数据完整性要求的应用OLTP。MyISAM事务支持不支持。锁机制表级锁并发写性能差。外键不支持。崩溃恢复较差可能损坏。非聚簇索引索引和数据是分开存储的。索引保存的是数据行的指针。特点支持全文索引InnoDB 后来也支持了压缩表。适用场景读密集型、不需要事务、对并发写要求低的场景如数据仓库读取、日志表。在现代应用中已较少使用。其他引擎MEMORY内存表、ARCHIVE归档压缩等。代码示例 (指定引擎)-- 创建表时指定引擎 CREATE TABLE my_innodb_table ( id INT PRIMARY KEY, data VARCHAR(255) ) ENGINEInnoDB; CREATE TABLE my_myisam_table ( id INT PRIMARY KEY, data VARCHAR(255) ) ENGINEMyISAM; -- 查看表的引擎 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分析慢查询日志慢查询日志是文本文件可以使用文本编辑器查看但更推荐使用专门的分析工具mysqldumpslowMySQL 自带的命令行工具用于汇总慢查询日志中的信息。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) -- 外键约束 ) ENGINEInnoDB; 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) ) ENGINEInnoDB; CREATE TABLE inventory ( product_id INT PRIMARY KEY, stock INT NOT NULL CHECK (stock 0), ... ) ENGINEInnoDB; -- 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 构建出高性能、高可用、数据一致且易于维护的数据库应用。务必在实践中不断尝试和优化。