安徽工程建设信息网站,网站建设软件 优帮云,网络营销比较常用的营销模式有哪些,深圳建网站的文章目录union 和 unionAll 的区别#xff1f;drop、delete与truncate的区别 #xff1f;sql 语句如何优化 #xff1f;什么是事务 #xff1f;事务的四个特性(ACID) #xff1f;事务的隔离级别#xff1f;索引主要有哪几种分类 #xff1f;什么时候适合添加索引#x…
文章目录union 和 unionAll 的区别drop、delete与truncate的区别 sql 语句如何优化 什么是事务 事务的四个特性(ACID) 事务的隔离级别索引主要有哪几种分类 什么时候适合添加索引哪些列适合添加索引 索引的设计原则 索引的数据结构有哪些 为什么B树比B树更适合实现数据库索引 B 树和 B树的区别为什么 Mysql 使⽤B树 什么情况会使索引失效 MyISAM 和 InnoDB 的区别 MyISAM和InnoDB使用的锁从锁的分类来说MySQL都有哪些锁行级锁和表级锁对比 什么是死锁如何解决数据库的乐观锁和悲观锁是什么怎么实现的乐观锁和悲观锁的使用场景 索引的三种常见底层数据结构以及优缺点 什么是redo log日志 什么是binlog日志 什么是undo log日志 什么是 MVCC 以及实现 什么是主从复制 主从复制的作用 union 和 unionAll 的区别
union对两个结果集进行并集操作不包括重复行同时进行默认规则的排序 unionAll: 对两个结果集进行并集操作包括重复行不进行排序
drop、delete与truncate的区别
相同点
truncate和不带where子句的delete,以及drop都会删除表内的数据
不同点 truncate会清除表数据并重置id从1开始,delete就只删除记录,drop可以用来删除表或数据库并且将表所占用的空间全部释放 truncate和delete只删除数据不删除表的结构。drop语句将删除表的结构被依赖的约(constrain),触发器(trigger),依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。 速度上一般来说: drop truncate delete 使用上,想删除部分数据行用 delete想删除表用 drop,想保留表而将所有数据删除如果和事务无关用truncate即可。如果和事务有关,或者想触发trigger,还是用delete。 delete是DML语句,不会自动提交。drop/truncate都是DDL语句,执行后会自动提交。
sql 语句如何优化
1.建议少用*代替所有列名 2.用 exists 代替 in 3.多表连接时尽量减少表的查询次数 4.删除全表数据的时候用 truncate 代替 delete 5.sql 语句尽量大写Oracle 会默认把小写转换成大写在执行 6.优化 group by,将不需要的数据尽量在分组之前过滤掉 7.连表查询的时候尽量使用表的别名减少解析时间 8.表连接在 where 之前where 条件过滤顺序能够更多的过滤数据的放在前面 9.合理使用索引
什么是事务
事务是由一条或多条操作数据库的SQL组成的一个不可分割的工作单元这些操作要么全部执行成功要么全部失败。
事务的四个特性(ACID) 原子性 要么全部执行成功要么全部执行失败 一致性 在事务开始之前和事务结束以后数据库的完整性约束没有被破坏。 隔离性 多个并发事务之间要相互隔离不能被其他事务的操作所干扰 持久性 当事务正确完成后对于数据的改变是永久性的。
事务的隔离级别 索引主要有哪几种分类
普通索引: 是最基本的索引它没有任何限制 唯一索引: 索引列的值必须唯一但允许有空值。如果是组合索引则列值的组合必须唯一 主键索引: 是一种特殊的唯一索引一个表只能有一个主键不允许有空值。 组合索引: 一个索引包含多个列实际开发中推荐使用组合索引。 全文索引: 全文搜索的索引。FULLTEXT 用于搜索很长一篇文章的时候效果最好。只能用于InnoDB或MyISAM表只能为CHAR、VARCHAR、TEXT列创建。 主键索引和唯一索引的区别
主键必唯一但是唯一索引不一定是主键
一张表上只能有一个主键但是可以有一个或多个唯一索引。
什么时候适合添加索引哪些列适合添加索引
数据量大的时候 1.经常用作查询的列 2.多表关联时作为关联条件的列 3.在经常需要根据范围查询的列 4.经常需要排序的列 5.主键默认添加唯一索引
索引的设计原则
索引列的区分度越高索引的效果越好。比如使用性别这种区分度很低的列作为索引效果就会很差。 尽量使用短索引对于较长的字符串进行索引时应该指定一个较短的前缀长度因为较小的索引涉及到的磁盘I/O较少查询速度更快。 索引不是越多越好每个索引都需要额外的物理空间维护也需要花费时间。 利用最左前缀原则。
索引的数据结构有哪些
索引的数据结构主要有B树和哈希表对应的索引分别为B树索引和Hash索引。InnoDB引擎的索引类型有B树索引和Hash索引默认的索引类型为B树索引。
Hash索引
哈希索引是基于哈希表实现的当我们要给某张表某列增加索引时存储引擎会对这列进行哈希计算得到哈希码将哈希码的值作为哈希表的key值将指向数据行的指针作为哈希表的value值。这样查找一个数据的时间复杂度就是O(1)一般多用于精确查找。所以在 in (安全等于的时候塔的效率是非常但我们开发一般会选择Btree因为Hash会存在如下一些缺点。
Hash索引仅仅能满足,“IN和”查询不能使用范围查询。 Hash 索引无法被用来避免数据的排序操作。 Hash索引不能利用部分索引键查询。 Hash索引在任何时候都不能避免表扫描。 Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。 B树索引
相对于cpu和内存操作磁盘IO开销很大非常容易成为系统的性能瓶颈。为什么索引能提升数据库查询效率呢根本原因就在于索引减少了查询过程中的IO次数。那么它是如何做到的呢使用B树。下面先简单了解一下B树和B树。
B树
B树,这里的 B 表示 balance( 平衡的意思),B-树是一种多路自平衡的查找树它类似普通的平衡二叉树不同的一点是B树允许每个节点有更多的子节点。下图是 B树的简化图. 观察上图可见B树的两个特点
树内的每个节点都存储数据
叶子节点之间无指针连接
B树
BTree是在B-Tree基础上的一种优化使其更适合实现外存储索引结构InnoDB存储引擎就是用BTree实现其索引结构。 BTree相对于B-Tree有几点不同
非叶子节点只存储键值信息。所有叶子节点之间都有一个链指针。数据记录都存放在叶子节点中。
为什么B树比B树更适合实现数据库索引
B 树叶子结点之间用链表有序连接所以扫描全部数据只需扫描一遍叶子结点利于扫库和范围查询B 树由于非叶子结点也存数据所以只能通过中序遍历按序来扫。也就是说对于范围查询和有序遍历而言B 树的效率更高。 B 树更相比 B 树减少了 I/O 读写的次数。由于索引文件很大因此索引文件存储在磁盘上B 树的非叶子结点只存关键字不存数据因而单个页可以存储更多的关键字即一次性读入内存的需要查找的关键字也就越多磁盘的随机 I/O 读取次数相对就减少了。 B树的查询效率更加稳定任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同导致每一个数据的查询效率相当。
B 树和 B树的区别为什么 Mysql 使⽤B树
B 树的特点
节点排序⼀个节点了可以存多个元素多个元素也排序了 B树的特点拥有 B 树的特点叶⼦节点之间有指针⾮叶⼦节点上的元素在叶⼦节点上都冗余了也就是叶⼦节点中存储了所有的元素并 且排好顺序 Mysql 索引使⽤的是 B树因为索引是⽤来加快查询的⽽B树通过对数据进⾏排序所以 是可以提⾼查 询速度的然后通过⼀个节点中可以存储多个元素从⽽可以使得 B树的⾼度不会太⾼ 在 Mysql 中⼀ 个 Innodb⻚就是⼀个 B树节点⼀个 Innodb⻚默认 16kb所以⼀般情况下⼀颗两层的 B 树可以存 2000 万⾏左右的数据然后通过利⽤B树叶⼦节点存储了所有数据并且进⾏了排序并且叶⼦ 节点之间有指 针可以很好的⽀持全表扫描范围查找等 SQL 语句。
什么情况会使索引失效
1.where 条件中有 or 2.like 查询时以%开头 3.列的类型为 varchar where 条件没有使用’’ 4.not in, not exists, 5.左边为表达式或者函数
MyISAM 和 InnoDB 的区别
1InnoDB 支持事务而 MyISAM 不支持。
2InnoDB 支持外键而 MyISAM 不支持。因此将一个含有外键的 InnoDB 表 转为 MyISAM 表会失败。
3InnoDB 和 MyISAM 均支持 B Tree 数据结构的索引。但 InnoDB 是聚集索引而 MyISAM 是非聚集索引。
4InnoDB 不保存表中数据行数执行 select count(*) from table 时需要全表扫描。而 MyISAM 用一个变量记录了整个表的行数速度相当快注意不能有 WHERE 子句。
那为什么 InnoDB 没有使用这样的变量呢因为InnoDB的事务特性在同一时刻表中的行数对于不同的事务而言是不一样的。
5InnoDB 支持表、行默认级锁而 MyISAM 支持表级锁。
InnoDB 的行锁是基于索引实现的而不是物理行记录上。即访问如果没有命中索引则也无法使用行锁将要退化为表锁。
6InnoDB 必须有唯一索引如主键如果没有指定就会自动寻找或生产一个隐藏列 Row_id 来充当默认主键而 Myisam 可以没有主键。
MyISAM和InnoDB使用的锁
MyISAM使用表级锁。
InnoDB支持行级锁和表级锁默认为行级锁。
从锁的分类来说MySQL都有哪些锁
从锁的类别来讲有共享锁和排他锁。
共享锁
又叫读锁。当用户要进行数据读取时对数据加上共享锁。共享锁可以同时加上多个。 排他锁
又叫写锁。当用户要进行数据的写入时对数据加上排他锁。排他锁只可以加一个它和其它排他锁、共享锁都互斥。
行级锁和表级锁对比
行级锁 行级锁是MySQL中锁定粒度最细的一种锁表示只针对当前操作的行Row进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小但加锁的开销也最大。行级锁分为共享锁和排他锁。
特点开销大加锁慢会出现死锁锁定粒度最小发生锁冲突概率最低并发度也最高。
表级锁 表级锁是MySQL中锁定粒度最大的一种锁表示对当前操作的整张表加锁它实现简单资源消耗较少被大部分MySQL引擎支持。最常用的MyISAM与InnoDB都支持表级锁。表级锁分为表共享读锁共享锁与表独占写锁排他锁。
特点开销小加锁快不会出现死锁锁定粒度大发生锁冲突的概率最高并发度最低。
什么是死锁如何解决
死锁是指两个或多个事务在同一资源上向胡战勇并请求锁定对方的资源从而导致恶性循环。
解决方案
1、如果不同程序会并发存取多个表尽量约定以相同的顺序访问表可以大大降低发生死锁的风险。
2、同一个事务中尽可能做到一次锁定所需要的所有资源减少死锁产生概率。
3、对于非常容易产生死锁的业务部分可以尝试升级锁定粒度通过表级锁定来减少死锁产生的概率。
数据库的乐观锁和悲观锁是什么怎么实现的
数据库管理系统中的并发控制的任务是确保在多个事务同时存取数据库中同一份数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制乐观锁和悲观并发控制悲观锁是并发控制主要采用的技术手段。
悲观锁 假定会发生并发冲突屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来直到提交事务。
实现方式使用数据库中的锁机制。
乐观锁 假设不会发生并发冲突只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来通过版本控制的方式来进行锁定。
实现方式一般会使用版本号机制或CAS算法实现。
乐观锁和悲观锁的使用场景
从上面对两种锁的介绍我们知道两种锁各有优缺点不可认为一种好于另一种像乐观锁适用于写操作比较少的情况下多读场景也就是并发写操作较低的场景即冲突真的很少发生的时候这样可以省去加锁的开销加大了整个系统的吞吐量。
但如果是并发写操作较多的情况下一般会经常产生冲突这就会导致上层应用会不断的进行retry这样的话使用乐观锁反而是降低了性能所以一般并发写操作较多的场景下使用悲观锁比较合适
索引的三种常见底层数据结构以及优缺点
三种常见的索引底层数据结构分别是哈希表、有序数组和搜索树。
哈希表这种适用于等值查询的场景比如 memcached 以及其它一些 NoSQL 引擎不适合范围查询。 有序数组索引只适用于静态存储引擎等值和范围查询性能好但更新数据成本高。 N 叉树由于读写上的性能优点以及适配磁盘访问模式以及广泛应用在数据库引擎中。 扩展以 InnoDB 的一个整数字段索引为例这个 N 差不多是 1200。棵树高是 4 的时候就可以存 1200 的 3 次方个值这已经 17 亿了。考虑到树根的数据块总是在内存中的一个 10 亿行的表上一个整数字段的索引查找一个值最多只需要访问 3 次磁盘。其实树的第二层也有很大概率在内存中那么访问磁盘的平均次数就更少了。
什么是redo log日志
redo log重做日志是InnoDB存储引擎独有的它让MySQL拥有了崩溃恢复能力。比如 MySQL 实例挂了或宕机了重启时InnoDB存储引擎会使用redo log恢复数据保证数据的持久性与完整性。
什么是binlog日志
binlog是记录所以数据表结构变更以及表数据修改的二进制日志不会记录select和show这类操作。binlog是以事件形式记录还包括语句所执行的消耗时间。Binlog是MySql Server自己的日志但是Redo Log是基于InnoDB引擎所特有的日志。 MySQL数据库的 数据备份、主备、主主、主从都离不开binlog需要依靠binlog来同步数据保证数据一致性。
什么是undo log日志
数据库事务四大特性中有一个是 原子性 具体来说就是 原子性是指对数据库的一系列操作要么全部成功要么全部失败不可能出现部分成功的情况。 我们知道如果想要保证事务的原子性就需要在异常发生时对已经执行的操作进行回滚在 MySQL 中恢复机制是通过 回滚日志undo log 实现的所有事务进行的修改都会先先记录到这个回滚日志中然后再执行相关的操作。如果执行过程中遇到异常的话我们直接利用 回滚日志 中的信息将数据回滚到修改之前的样子即可并且回滚日志会先于数据持久化到磁盘上。这样就保证了即使遇到数据库突然宕机等情况当用户再次启动数据库的时候数据库还能够通过查询回滚日志来回滚将之前未完成的事务。
什么是 MVCC 以及实现
MVCC 的英文全称是 Multiversion Concurrency Control中文意思是多版本并发控制可以做到读写互相不阻塞主要用于解决不可重复读和幻读问题时提高并发效率。 其原理是通过数据行的多个版本管理来实现数据库的并发控制简单来说就是保存数据的历史版本。可以通过比较版本号决定数据是否显示出来。读取数据的时候不需要加锁可以保证事务的隔离效果。
什么是主从复制
主从复制是用来建立一个与主数据库完全一样的数据库环境即从数据库。主数据库一般是准实时的业务数据库。
主从复制的作用
读写分离使数据库能支撑更大的并发。 高可用做数据的热备作为后备数据库主数据库服务器故障后可切换到从数据库继续工作避免数据丢失。
