MySQL数据库（四）

MySQL数据库高级特性一、主键Primary Key1.1、特性唯一表示一条记录不能有重复值一个表只能有一个主键可以是单列或多列的组合自动定义为NOT NULL1.2、作用数据的唯一性标识数据的完整性维护提高查询性能建立表间关系的基础1.3、创建CREATE TABLE users (id INT AUTO_INCREMENT,username VARCHAR(50) NOT NULL,password VARCHER(50) NOT NULL,PRIMARY KEY (id));1.4、修改不支持修改现有的主键约束可以删除并重新创建ALTER TABLE users DROP PRIMARY KEY;ALTER TABLE users ADD PRIMARY KEY (id);1.5、删除ALTER TABLE users DROP PRIMARY KEY;二、外键Foreign Key2.1、特性确保子表中的数据在父表中有对应值可以实现级联更新和删除外键一定是某一张表的主键2.2、作用维护数据一致性实现多表关联操作简化数据模型设计2.3、创建CREATE TABLE orders (order_id INT AUTO_INCREMENT,user_id INT,owner varchar(50) NOT NULL,level int NOT NULL,age int,PRIMARY KEY (order_id),FOREIGN KEY (user_id) REFERENCES users(id));2.4、修改不支持直接修改需要先删除再重新创建ALTER TABLE orders DROP FOREIGN KEY orders_ibfk_1;ALTER TABLE orders ADD CONSTRAINT orders_ibfk_1 FOREIGN KEY (user_id) REFERENCES users(id);2.5、删除ALTER TABLE orders DROP FOREIGN KEY fk_user_id;三、索引Index3.1、特性通过快速定位数据来提高查询速度可以是唯一的或非唯一的3.2、类型按数据结构分类Btree索引、Hash索引、Fulltext索引。按物理存储分类聚簇索引、二级索引。按字段特性分类主键索引、普通索引、前缀索引。按字段个数分类单列索引、联合索引复合索引、组合索引。3.3、创建普通索引CREATE INDEX idx_username ON usersusername);联合索引CREATE INDEX FROM users3.4、查看show index from users;3.5、修改索引不能直接修改但可以重建DROP INDEX idx_username ON usersCREATE INDEX idx_username ON users (username(10));3.6、删除DROP INDEX idx_username ON users四、Check约束Check Constraint4.1、特性保证列数据满足特定条件在MySQL8.0.16及以后的版本中可用4.2、作用数据验证维护数据完整性简化应用程序逻辑4.3、创建ALTER TABLE users ADD CONSTRAINT chk_username_format CHECK (username REGEXP ^[a-zA-Z][a-zA-Z0-9_]{2,}$);4.4、查看select * from information_schema.TABLE_CONSTRAINTS where TABLE_NAMEusers\G;4.5、修改创建后无法直接修改需要删除并重新定义ALTER TABLE users DROP CONSTRAINT chk_username_nonempty;ALTER TABLE users ADD CONSTRAINT chk_username_nonempty CHECK (username AND username IS NOT NULL);4.6、删除ALTER TABLE users DROP CONSTRAINT chk_username_nonempty;4.7、应用场景限制数值范围限制字符串格式基于多列的条件限制注意事项性能影响虽然 CHECK 约束有助于保证数据的准确性但过多复杂的 CHECK 约束可能会对数据库的性能产生一定影响特别是在进行大量数据插入和更新操作时。因为每次操作都需要对约束条件进行检查和计算增加了数据库的处理负担。因此在使用时应避免设置过于复杂或不必要的约束条件。约束条件的合理性设置的约束条件应基于合理的业务规则和数据逻辑既要确保能够有效地验证数据的有效性又不能过于严格或不合理以免影响正常的数据操作和业务流程。同时要考虑到数据的变化和扩展性避免因业务需求的变化而导致约束条件频繁修改。数据库兼容性不同的数据库管理系统对 CHECK 约束的支持和实现方式可能略有不同。例如某些数据库可能对 CHECK 约束中的函数和表达式的使用有限制或者在处理复杂约束条件时的行为有所差异。因此在开发跨数据库的应用程序时需要注意兼容性问题并进行充分的测试。五、存储过程Stored Procedure5.1、特性封装一段SQL语句便于复用和维护可以包含业务逻辑5.2、优点提高性能增强安全性简化复杂操作减少网络流量5.3、查看查看所有SHOW PROCEDURE status;查看指定数据库的存储过程SHOW PROCEDURE status where Dbjx\G;查看指定存储过程show create procedure GetAllUsers\G;5.4、删除DROP PROCEDURE IF EXISTS GetAllUsers;5.5、应用场景数据处理和转换业务逻辑封装系统维护和管理六、触发器Trigger6..1、特性在 INSERT、UPDATE 或 DELETE 操作之前或之后自动执行用于自动化维护数据的完整性或实施业务规则6.2、触发器类型按触发事件分类INSERT 触发器 、UPDATE 触发器、DELETE 触发器按触发时间分类BEFORE 触发器、AFTER 触发器6.3、删除DROP TRIGGER before_user_insert;七、事务Transaction7.1、特性一组 SQL操作要么全部成功要么全部失败用于保证数据的一致性和完整性支持提交COMMIT和回滚ROLLBACK7.2、ACID属性原子性Atomicity: 事务被视为最小的不可分割的工作单位只有事务中所有操作都完成事务才算成功。如果事务中的任一操作失败那么整个事务也会失败并且系统会自动撤销或回滚事务的所有操作保持数据的一致性。一致性Consistency: 事务必须保证它在不破坏数据库的完整性和约束的前提下执行。在事务开始之前和结束之后数据库必须保持一致状态。隔离性Isolation: 事务在提交之前对其它事务是不可见的。这是为了防止多个并发事务相互干扰。依赖于隔离级别事务可能会看到其他并非提交的事务所做的改动反之亦然。MySQL提供不同的隔离级别如READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ (默认的隔离级别), 和SERIALIZABLE。持久性Durability: 一旦事务提交其所做的改动就会被永久保存在数据库中即使数据库系统发生故障也不会丢失这些改动。7.3、开始事务START TRANSACTION;7.4、提交事务COMMIT;7.5、回滚事务ROLLBACK;7.6、调用事务call TransferFunds();7.7、事务的隔离级别读未提交READ UNCOMMITTED读已提交READ COMMITTED可重复读REPEATABLE READ串行化SERIALIZABLE7.8、事务的应用场景金融交易、订单处理、数据更新与一致性维护八、数据库引擎8.1、类型InnoDB支持事务处理和行级锁定适用于高并发和多表关联查询的应用。MyISAM不支持事务处理适用于读密集型应用。Memory将数据存储在内存中适用于需要快速读写的临时数据和缓存。NDB (Cluster)支持分布式存储和高可用性适用于大规模的分布式系统。Archive只支持插入和压缩适用于存储大量历史数据。CSV将数据存储为CSV文件适用于导入导出数据。Blackhole从写入的数据中丢弃数据适用于日志传输。8.2、InnoDB引擎存储结构InnoDB 采用表空间的形式来存储数据它将数据存储在一个或多个表空间文件中。事务支持InnoDB 是 MySQL 8 中默认的事务型数据库引擎它提供了完整的 ACID 事务支持。并发控制支持行级锁通过对每行数据加锁允许多个并发事务同时访问不同行的数据提高了数据库的并发性能。索引类型支持多种索引类型包括 B 树索引、全文索引等。B 树索引是最常用的索引类型它具有高效的查找性能适合用于各种查询场景。8.3、查看查看支持的引擎SHOW ENGINES;查看某个具体表所使用的数据库引擎 SHOW CREATE TABLE mytable;8.4、使用场景网站博客、电子商务平台、数据分析平台、大规模分布式系统九、慢日志慢日志是用于记录执行时间超过指定阈值的 SQL 语句的日志文件 它对于数据库性能优化非常有帮助。9.1、作用性能分析与优化资源监控与调整问题排查与追踪9.2、查看SHOW VARIABLES LIKE %slow_query%;