永川区做网站,有那个网站做外贸,孝义做网站,小程序制作开发一、delete与trancate的区别
相同点
1.两者都是删除表中的数据#xff0c;不删除表结构
不同点
1.delete支持按条件删除#xff0c;TRUNCATE不支持。 2.delete 删除后自增列不会重置#xff0c;而TRUNCATE会被重置。 3.delete是逐条删除#xff08;速度较慢#xff09…一、delete与trancate的区别
相同点
1.两者都是删除表中的数据不删除表结构
不同点
1.delete支持按条件删除TRUNCATE不支持。 2.delete 删除后自增列不会重置而TRUNCATE会被重置。 3.delete是逐条删除速度较慢truncate是整体删除速度较快。 4.delete删除是一条一条删除并不会改变表结构属于DML而truncate删除全部数据属于DDL。 5.delete不会释放空间而TRUNCATE会释放空间。 6.delete 支持回滚TRUNCATE不支持。
7.对于由 FOREIGN KEY 约束引用的表不能使用 TRUNCATETABLE而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中所以它不能激活触发器
8.delete语句为DMLdata maintain Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。
本文结合了以下内容与
https://blog.csdn.net/weixin_44224262/article/details/125538188
二、事务
特性
1. 原子性Atomicity
指事务是一个不可分割的单位要么全部执行要么完全不执行。如果事务中的任何一部分失败整个事务将被回滚到初始状态不会留下部分执行的结果。
2. 一致性Consistency
指事务执行前后数据库的状态必须是一致的。这意味着事务执行后数据库中的数据必须满足所有的约束和规则如唯一性约束、外键约束等。
3. 隔离性Isolation
指多个事务并发执行时每个事务的执行都应该与其他事务隔离开来互不干扰。这可以防止并发执行时产生的一些问题如脏读、不可重复读和幻读。
4. 持久性Durability
指一旦事务提交其结果应该是永久性的即使系统发生故障也不应该丢失提交的数据。
隔离级别
1. 读未提交
在这个级别一个事务可以读取另一个未提交事务的数据。这可能导致脏读问题即事务A读取了事务B未提交的数据如果事务B在提交前回滚那么事务A读取到的数据就是“脏”数据。这是就会发生脏读。
2. 读已提交Read Committed
这个级别要求事务只能读取已经提交的数据。这解决了脏读问题但在高并发场景下可能出现不可重复读和幻读问题。不可重复读是指事务A在读取同一数据时事务B修改了该数据并提交导致事务A多次读取的结果不一致。这是不可重复读。不可重复读主要关注的是数据的修改。
3.可重复读Repeatable Read
可重复读的实现方式并不是在事务开始时创建一个快照然后事务内的所有查询都基于这个快照。实际上这个“快照”的概念更接近于一种逻辑上的快照它保证了在事务进行期间看到的数据是一致的但并非通过一个物理的快照实现。还有更离谱的说法是在当前事务没结束时不允许其他事务进行修改操作这明显不太合理。
MySQL通过多版本并发控制MVCC机制来确保事务的可重复读。每个数据行都会关联一个版本号或称为事务ID。当一个事务开始时它不会立即创建整个数据库的快照而是会根据需要读取的数据行和这些行的版本号来确定可见的数据版本。事务只能看到在事务开始时已经提交的数据版本而对于未提交或在事务开始后提交的数据版本该事务是不可见的。这解决了不可重复读问题但仍然存在幻读问题如果一个事务读取了几行数据然后另一个并发事务插入新行并提交那么原事务再次读取同样的范围时就会看到这些新的“幻影”行这就是幻读现象。幻读则主要关注新增或删除操作。
4.串行化Serializable
这是最高的隔离级别。一个事务执行期间对数据加锁其他数据不能访问该数据可以避免所有并发问题但效率最低它要求事务串行化顺序执行即事务只能一个接一个地执行而不能并发执行。这样可以避免脏读、不可重复读和幻读问题但会大大降低并发性能。
Mysql默认级别是可重复读查询默认隔离级别select tx isolation;修改默认隔离级别select global transactionisolation level 级别字符串
三、MySQL中有哪几种锁
MySQL中有三种锁表级锁、行级锁和页面锁表级锁是对整张表加锁不会出现死锁但并发度低行级锁只对涉及的行加锁支持高并发但是开销大可能出现死锁。页面锁是介于两者之间的一种锁是对整个数据页加锁可以减少锁冲突但也可能出现死锁。死锁是指两个或多个事务在执行过程中因争夺资源造成的一种互相等待现象。避免或解决死锁的方法有以下几种设计合理的索引和锁策略尽量缩短锁的持有时间和范围。减少每个事务锁定的资源数量和和时间;为表添加合理的主键或唯一索引避免表锁升级为行锁设置合理的超时时间和重试机制遇到死锁时主动回滚并重新执行事务
四、MySQL中有哪些不同的表类型存储引擎 InnoDB InnoDB是MySQL的默认存储引擎Mysql5.5版本之后它提供了事务安全ACID兼容的表。支持外键约束支持自动增长列。提供行级锁定从而提高了并发性能。支持崩溃恢复支持热备。提供了MVCC多版本并发控制功能。读写分离可重复读在MVCC中每个事务启动时系统会为其分配一个唯一的事务id。当一个事务要访问某个数据时系统会检查数据的版本号和启动时间如果数据的版本号早于事务的启动时间该事务可以访问数据否则该事务需要等待其他事务完成对该数据的访问。MVCC通过使用版本号或时间戳来标记每个事务的读写版本将读操作与写操作进行隔离避免了读写冲突。因此当一个事务正在读取某个数据时其他事务可以继续并发地进行读操作提高了数据库的并发性能。这种机制在长时间运行的事务中特别有用可以避免锁资源长时间占用影响其他事务的执行效率。此外MVCC还提供了非阻塞的快照读取功能适用于需要读取历史数据或快照数据的场景数据和索引紧密捆绑使用B树结构所有的表按照主键来组织。 MyISAM MyISAM是早期MySQL版本中常用的存储引擎。它提供了全文索引功能。通常用于只读或大量插入的表。不支持事务和行级锁定还有外键只支持表级锁定。MyISAM的查询操作本身是原子的即每次查询都是一个不可分割的操作但MyISAM本身并不支持事务的原子性即在多个查询组成的事务中无法保证原子性有较高的查询和插入速度select count很快因为把表的总行数储存在磁盘上执行select count操作后直接返回索引和数据是分开的并且索引是有压缩内存使用率对应提高了不少 MEMORY (或 HEAP) 数据存储在内存中速度非常快但数据在MySQL服务器重启后会丢失。通常用于临时表或需要快速访问的表。不支持事务。支持数据类型有限不支持text和blob类型对于字符串类型的数据只支持固定长度的varchar会被自动存储为char类型支持表级锁使用hash索引 CSV 数据以逗号分隔值CSV格式存储。可以使用文本编辑器查看和编辑。不支持索引。 ARCHIVE 用于存储和检索大量归档数据。只支持INSERT和SELECT操作。使用zlib压缩来减小存储空间。 BLACKHOLE 一个“黑洞”存储引擎它会接受数据但不存储读取操作会返回空内容。主要用于复制架构中的特殊目的。 FEDERATED FEDERATED存储引擎允许你访问远程MySQL服务器上的表。在本地服务器上创建一个FEDERATED表实际上是一个指向远程服务器上真实表的链接。 MERGE MERGE存储引擎允许你将多个MyISAM表当作一个表来查询。这对于分区表特别有用。 NDBCLUSTER (或 NDB) NDBCLUSTER是MySQL Cluster的一部分提供了分布式数据库解决方案。支持高可用性和数据冗余。
五、索引
索引是一种数据结构可以帮助数据库快速查找和访问数据。索引的目的和作用有以下几下几点
提高查询效率通过索引可以减少扫描的数据量避免全表扫描但是也会增加插入更新和啊删除开销以及占用磁盘空间等。因此在创建索引时要选择恰当的列通常选择参与频繁的查询条件或排序操作的列选择不同值多的列较小的数据类型或长度的列避免使用过多或过少的索引一个表最多不超过五个遵循最左前缀原则如果创建了组合索引那么查询条件必须包含组合索引最左边的列可以使用explain命令查询语句索引的使用情况例EXPLAIN SELECT * FROM...
索引失效的情况查询条件使用函数或表达式查询条件中使用了隐式类型转换查询条件使用了OR连接符或查询条件使用、、等非等值符号
索引类型
普通索引最基本的索引类型没有任何限制可以创建多个普通索引。 唯一索引唯一索引要求索引列的值不能重复除了NULL值可以创建多个唯一索引。 主键索引主键索引是一种特殊的唯一索引要求索引列的值不能重复且不能为NULL一个表只能有一个主键索引。 组合索引组合索引是由多个列组成的索引可以加快多个列的查询条件的效率但要注意遵循最左前缀原则。 全文索引全文索引是一种针对文本内容的索引可以支持模糊匹配和相关度排序只适用于MyISAM和InnoDB表的CHAR、VARCHAR和TEXT类型的列。
创建和删除方式
创建索引CREATE [UNIQUE] INDEX ON ( [ASC|DESC] [, …]); 删除索引DROP INDEX ON ; 也可以在创建表或修改表时指定索引 创建表时指定索引CREATE TABLE (, …, [INDEX|UNIQUE|PRIMARY KEY] ( [ASC|DESC] [, …])); 修改表时指定索引ALTER TABLE ADD [INDEX|UNIQUE|PRIMARY KEY] ( [ASC|DESC] [, …]);
六、备份恢复
1.使用cp或tar进行物理备份
这种方法是直接复制或打包数据库的物理文件数据文件日志文件配置文件等。优点:备份速度快恢复速度快不需要停止数据库服务。缺点是占用空间大不能进行增量备份不能跨平台恢复需要注意文件权限和所有者。
2.使用mysqldump:
从数据库中导出sql语句或者数据可以备份整个数据库也可以指定备份哪个数据库哪个表。
优点可以进行增量备份占用空间小可跨平台恢复
缺点:备份速度慢恢复速度慢可能会影响数据库服务
3.使用xtrbackup
这种方法是使用xtra backup工具对InnoDB存储引擎的数据文件进行物理备份可以实现热备份。
优点备份速度快恢复速度快可以进行增量备份和差异备份不会锁表影响数据库服务。
缺点但是缺点是占用空间大只支持InnoDB存储引擎。如果要实现增量备份和定点恢复还需要结合二进制日志。开启二进制服务在配置文件中添加log-bin选项并重启mysql服务。使用mysqldump或xtrabackup进行完全备份时需要添加--master-data2选项这样会在备份文件中记录二进制日志的文件名和位置。定期清理二进制日志要在配置文件中添加expire_logs_days选项并重启mysql服务
七、Mysql性能优化
可以从以下几个方面
1. 选择最合适的字段属性选择合适的数据类型和长度。避免使用过大或过小的字段尽量把字段设置为not null 这样可以减少数据库比较null值开销
2.使用恰当的索引
3.使用连接代替子查询
4.使用union代替临时表
5.使用事务多表操作放在一个事务可以减少开销并且避免数据不一致的风险
6.使用外键
7.查看和优化慢查询MySQL有一个内置的慢查询日志可以用来查看和优化慢查询。要启用慢查询日志你需要在MySQL配置文件中设置以下参数
slow_query_log1 表示开启慢查询日志功能。 slow_query_log_file/var/log/mysql/mysql-slow.log 表示指定慢查询日志的文件路径和名称。 long_query_time1 表示设置慢查询的阈值单位是秒。如果一个查询的执行时间超过这个值就会被记录到慢查询日
八、主从复制
MySQL中可以使用以下步骤来实现主从复制
在主服务器上修改配置文件开启二进制日志功能设置唯一的服务器ID指定需要复制的数据库或表重启MySQL服务。 在从服务器上修改配置文件设置唯一的服务器ID开启中继日志功能重启MySQL服务。 在主服务器上创建一个专用的复制用户并授予相应的权限。 在从服务器上使用CHANGE MASTER TO命令指定主服务器的IP地址、端口号、复制用户、密码、二进制日志文件名和位置等信息。 在从服务器上使用START SLAVE命令启动复制进程。 在主服务器或从服务器上使用SHOW MASTER STATUS或SHOW SLAVE STATUS命令查看复制状态和信息。
九、MySQL中如何监控和诊断性能问题
1.使用SHOW PROCESSLIST命令:
查看当前正在执行的连接和语句分析是否有慢查询、锁等待、死锁等情况。 2.使用SHOW STATUS命令:
查看MySQL服务器的运行状态和统计信息分析是否有资源瓶颈、错误、警告等情况。 3.使用SHOW VARIABLES命令:
查看MySQL服务器的配置参数分析是否有不合理或不适合的设置影响性能或稳定性。 4.使用SHOW ENGINE命令:
查看存储引擎的状态和信息分析是否有异常或错误发生影响数据的完整性或可用性。 使用第三方工具或框架如MySQL Workbench、Percona Toolkit、Performance Schema等提供更多的监控和诊断功能和界面方便分析和优化性能问题。 十、范式
在了解范式前先学习几个概念
1.码相当于键。设 K 为某表中的一个属性或属性组若除 K 之外的所有属性都完全函数依赖于 K这个“完全”不要漏了那么我们称 K 为候选码简称为码。、候选键候选码能够唯一标识一条记录的最小属性集任一候选键的任何真子集都不能唯一标识一个记录比如在成绩表中学号,课程号是一个候选键单独的学号课程号都不能决定一条记录 包含在任何一个码中的属性成为主属性。
第一范式1NF的定义为符合1NF的关系中的每个属性都不可再分
第二范式表中不存在非主属性对码存在部分函数依赖。例有一个学生表,学号课名作为码存在以下关系学号课名 → 姓名有 学号 → 姓名存在非主属性 姓名 对码学号课名的部分函数依赖。
第三范式表中不存在非主属性对码存在传递函数依赖。比如A-BB-C
BC范式表不存在着主属性对于码的部分函数依赖与传递函数依赖。
