赞
踩
面试题:MySQL 的查询流程具体是?or 一条SQL语句在MySQL中如何执行的?
客户端请求 —> 连接器(验证用户身份,给予权限) —> 查询缓存(存在缓存则直接返回,不存在则执行后续操作) —> 分析器(对SQL进行词法分析和语法分析操作) —> 优化器(主要对执行的sql优化选择最优的执行方案方法) —> 执行器(执行时会先看用户是否有执行权限,有才去使用这个引擎提供的接口) —> 去引擎层获取数据返回(如果开启查询缓存则会缓存查询结果)
使用哪一种引擎可以灵活选择,一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求,使用合适的存储引擎,将会提高整个数据库的性能 。
常见的存储引擎有 InnoDB、MyISAM、Memory、NDB。
InnoDB 现在是 MySQL 默认的存储引擎,支持事务、行级锁定和外键
面试题:说说MySQL有哪些存储引擎?都有哪些区别?
select count(*)
更快主要有五大类
面试题:CHAR 和 VARCHAR 的区别?
char是固定长度,varchar长度可变。存储时,前者不管实际存储数据的长度,直接按 char 规定的长度分配存储空间;而后者会根据实际存储的数据分配最终的存储空间。对于非常短的列,char比varchar在存储空间上也更有效率。
相同:
不同:
**面试题:**说说你对 MySQL 索引的理解?
索引(Index)是帮助MySQL高效获取数据的数据结构,可以简单理解为“排好序的快速查找数据结构”。这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。它的存在是用于提升查询效率。
索引本身很大,不可能全部存储在内存中,一般以索引文件的形式存储在磁盘上
优势
劣势
哪些情况需要创建索引
不应该创建索引的情况:
聚集索引(clustered index)
非聚集索引(non-clustered index),也叫辅助索引(secondary index) 聚集索引和非聚集索引都是B+树结构
两者区别:
首先要明白索引(index)是在存储引擎(storage engine)层面实现的,而不是server层面。而且不是所有引擎都支持所有类型的索引
MyISAM 和 InnoDB 存储引擎,都使用 B+Tree的数据结构,它相对与 B-Tree结构,所有的数据都存放在叶子节点上,且把叶子节点通过指针连接到一起,形成了一条数据链表,以加快相邻数据的检索效率。
首先要了解B树和B+树的差别
B-Tree是为磁盘等外存储设备设计的一种平衡查找树。
系统从磁盘读取数据到内存时是以磁盘块(block)为基本单位的,位于同一个磁盘块中的数据会被一次性读取出来,而不是需要什么取什么。InnoDB 存储引擎中有页(Page)的概念,页是其磁盘管理的最小单位。InnoDB 存储引擎中默认每个页的大小为16KB,
而系统一个磁盘块的存储空间往往没有这么大,因此 InnoDB 每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小 16KB。InnoDB 在把磁盘数据读入到磁盘时会以页为基本单位,在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置,这将会减少磁盘I/O次数,提高查询效率。
B-Tree 结构的数据可以让系统高效的找到数据所在的磁盘块。B-Tree 中的每个节点根据实际情况可以包含大量的关键字信息和分支
每个节点占用一个盘块的磁盘空间,一个节点上有两个升序排序的关键字和三个指向子树根节点的指针,指针存储的是子节点所在磁盘块的地址。
两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例,关键字为17和35,P1指针指向的子树的数据范围为小于17,P2指针指向的子树的数据范围为17~35,P3指针指向的子树的数据范围为大于35。
模拟查找关键字29的过程:
分析上面过程,发现需要3次磁盘I/O操作,和3次内存查找操作。由于内存中的关键字是一个有序表结构,可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。B-Tree相对于AVLTree缩减了节点个数,使每次磁盘I/O取到内存的数据都发挥了作用,从而提高了查询效率。
B+Tree 是在 B-Tree 基础上的一种优化,使其更适合实现外存储索引结构。
从上一张 B-Tree 图中,可以看到每个节点中不仅包含数据key值,还有data值。但是每一个page的存储空间都是有限的,当data数据较大时将会导致每个节点(即一个页)能存储的key的数量很小,当存储的数据量很大时同样会导致B-Tree的深度较大,增大查询时的磁盘I/O次数,进而影响查询效率。
在B+Tree中,所有数据节点都是按照键值大小存放在同一层的叶子结点上,而非叶子节点上只存储key值信息,这样可以大大加大每个节点存储的key值数量,降低B+Tree的高度
所以与B树的不同在于
通常在B+Tree上有两个头指针,一个指向根节点,另一个指向关键字最小的叶子节点,而且所有叶子节点(即数据节点)之间是一种链式环结构。因此可以对B+Tree进行两种查找运算:一种是对于主键的范围查找和分页查找,另一种是从根节点开始,进行随机查找。
B+Tree性质
InnoDB主键索引与辅助索引的结构
InnoDB引擎索引结构的叶子节点的数据域,存放的就是实际的数据记录。或者说,InnoDB的数据文件本身就是主键索引文件,也被称为"“聚簇索引”,一个表只能有一个聚簇索引。
而辅助索引则不一样,比如对name列进行条件搜索,需要两个步骤:
① 在辅助索引上检索name,到达其叶子节点获取对应的主键;② 使用主键在主索引上再进行对应的检索操作。 这也就是所谓的“回表查询”
MyISAM主键索引与辅助索引的结构
MyISAM引擎的索引文件和数据文件是分离的。MyISAM引擎索引结构的叶子节点的数据域,存放的并不是实际的数据记录,而是数据记录的地址。索引文件与数据文件分离,这样的索引称为"非聚簇索引"。MyISAM的主索引与辅助索引区别并不大,只是主键索引不能有重复的关键字
面试题:为什么Mysql索引要用B+树不是B树?
用B+树不用B树考虑的是IO对性能的影响,B树的每个节点都存储数据,而B+树只有叶子节点才存储数据,所以查找相同数据量的情况下,B树的高度更高,IO更频繁。数据库索引是存储在磁盘上的,当数据量大时,就不能把整个索引全部加载到内存了,只能逐一加载每一个磁盘页(对应索引树的节点)。其中在MySQL底层对B+树进行进一步优化:在叶子节点中是双向链表,且在链表的头结点和尾节点也是循环指向的。
面试题:为何不采用Hash方式?
因为Hash索引底层是哈希表,哈希表是一种以key-value存储数据的结构,所以多个数据在存储关系上是完全没有任何顺序关系的,所以,对于区间查询是无法直接通过索引查询的,就需要全表扫描。所以,哈希索引只适用于等值查询的场景。而B+ Tree是一种多路平衡查询树,所以他的节点是天然有序的(左子节点小于父节点、父节点小于右子节点),所以对于范围查询的时候不需要做全表扫描。而且,哈希索引不支持多列联合索引的最左匹配规则,如果有大量重复键值得情况下,哈希索引的效率会很低,因为存在哈希碰撞问题。
面试题:count(*) 和 count(1)和count(列名)区别
执行结果
执行效率
面试题:MySQL中 in和 exists 的区别?
参考:https://blog.csdn.net/jinjiniao1/article/details/92666614
SELECT * FROM A WHERE A.id IN (SELECT id FROM B);
//可转化为
SELECT * FROM A WHERE A.id = 1 OR A.id = 2 OR A.id = 3;//用到A的索引,与B关系不大
SELECT * FROM A WHERE EXISTS (SELECT * from B WHERE B.id = A.id);//用到B的索引,与A关系不大
exists:exists对外表用loop逐条查询,每次查询都会查看exists的条件语句,当exists里的条件语句能够返回记录行时, 条件就为真,返回当前loop到的这条记录;反之当前loop到的这条记录被丢弃,exists的条件就像一个bool条件,当能返回结果集则为true,不能返回结果集则为false
总结:如果A表有n条记录,那么exists查询就是将这n条记录逐条取出,然后判断n遍exists条件
in:in查询相当于多个or条件的叠加 总结:in查询就是先将子查询条件的记录全都查出来,假设结果集为B,共有m条记录,然后再将子查询条件的结果集分解成m个,再进行m次查询
所以,如果查询的两个表大小相当,那么用in和exists差别不大。
如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in:
面试题:UNION和UNION ALL的区别?
UNION和UNION ALL都是将两个结果集合并为一个,两个要联合的SQL语句 字段个数必须一样,而且字段类型要一致;
SELECT DISTINCT <select_list>
FROM <left_table> <join_type>
JOIN <right_table> ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>
FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>
事务是逻辑上的一组操作,要么都执行,要么都不执行。又分为分布式事务和数据库事务
事务日志则是InnoDB 使用日志来减少提交事务时的开销
atomicity–整个事务中的所有操作,要么全部完成,要么全部不完成。undo log 回滚日志
consistency --在事务开始之前和事务结束以后,数据库的完整性约束没有被破坏。保证其他三者,才能保证一致性
isolation --一个事务的执行不能其它事务干扰。通过锁,MVCC来保证
durability – 在事务完成以后,该事务所对数据库所作的更改便持久的保存在数据库之中,并不会被回滚。redo log 重做日志,保证事务持久性
不可重复读和幻读区别:
MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ(可重读)
需要强调的是,事务隔离级别和数据访问的并发性是对立的,事务隔离级别越高并发性就越差。所以要根据具体的应用来确定合适的事务隔离级别,这个地方没有万能的原则。
MySQL的大多数事务型存储引擎实现都不是简单的行级锁。基于提升并发性考虑,一般都同时实现了多版本并发控制(MVCC),可以认为 MVCC 是行级锁的一个变种,但它在很多情况下避免了加锁操作,因此开销更低。
典型的MVCC实现方式,分为乐观(optimistic)并发控制和悲观(pressimistic)并发控制
MVCC 只在 COMMITTED READ(读提交)和REPEATABLE READ(可重复读)两种隔离级别下工作。
MySQL 间隙锁有没有了解,死锁有没有了解,写一段会造成死锁的 sql 语句,死锁发生了如何解决,MySQL 有没有提供什么机制去解决死锁
MySQL中InnoDB引擎的行锁是怎么实现的?
InnoDB 实现了以下两种类型的行锁:
什么是锁粒度:为了尽可能提高数据库的并发度,每次锁定的数据范围越小越好。但是管理锁是很耗资源的事情,因此数据库系统需要在高并发响应和系统性能两方面进行平衡,这样就产生了锁粒度
行级锁:mysql中锁粒度最小。表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突,其加锁粒度最小,但加锁的开销也最大。行级锁分为共享锁和排他锁。
开销大,加锁慢,会出现死锁。发生锁冲突的概率最低,并发度也最高。
InnoDB行锁是通过给索引上的索引项加锁来实现的,InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁
表级锁:开销小,加锁快;不会出现死锁;锁粒度大,发生锁冲突的概率最高,并发度最低
开销小,加锁快,不会出现死锁。发生锁冲突的概率最高,并发度也最低
页级锁:页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。会出现死锁,并发度一般。
面试题:介绍一下数据库的乐观锁和悲观锁?
乐观锁与悲观锁是两种并发控制的思想,可用于解决丢失更新问题
乐观锁会“乐观地”假定大概率不会发生并发更新冲突,访问、处理数据过程中不加锁,只在更新数据时再根据版本号或时间戳判断是否有冲突,有则处理,无则提交事务。用数据版本(Version)记录机制实现,这是乐观锁最常用的一种实现方式
悲观锁会“悲观地”假定大概率会发生并发更新冲突,访问、处理数据前就加排他锁,在整个数据处理过程中锁定数据,事务提交或回滚后才释放锁。另外与乐观锁相对应的,悲观锁是由数据库自己实现了的,要用的时候,我们直接调用数据库的相关语句就可以了。
记录锁(Record Locks):单个行记录上的锁。对索引项加锁,锁定符合条件的行。其他事务不能修改和删除加锁项;
间隙锁(Gap Locks):当我们使用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁。对于键值在条件范围内但并不存在的记录,叫做“间隙”。InnoDB 也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁。锁定记录的范围,其他事务不能在锁范围内插入数据,这样就防止了幻读。
注意:使用间隙锁锁住的是一个区间,而不仅仅是这个区间中的每一条数据。
SELECT * FROM table WHERE id BETWEN 1 AND 10 FOR UPDATE;
即所有在(1,10)区间内的记录行都会被锁住,所有id 为 2、3、4、5、6、7、8、9 的数据行的插入会被阻塞,但是 1 和 10 两条记录行并不会被锁住。
Phantom Read
)。死锁产生:死锁是指两个或多个事务在同一资源上相互占用,并请求锁定对方占用的资源。(两个事务都持有对方需要的锁,并且在等待对方释放,并且双方都不会释放自己的锁)。
与事务执行顺序和存储引擎相关联
死锁的解决办法?
InnoDB避免死锁:
SELECT ... FOR UPDATE
语句来获取必要的锁MyISAM避免死锁:
在自动加锁的情况下,MyISAM 总是一次获得 SQL 语句所需要的全部锁,所以 MyISAM 表不会出现死锁。
日常工作中你是怎么优化SQL的?SQL优化的一般步骤是什么,如何写sql能够有效的使用到复合索引?一条sql执行过长的时间,你如何优化,从哪些方面入手?什么是最左前缀原则?什么是最左匹配原则?
常见的分析手段有慢查询日志,EXPLAIN 分析查询,profiling分析以及show命令查询系统状态及系统变量
面试题:什么是执行计划explain,介绍一下
使用 Explain 关键字可以模拟优化器执行SQL查询语句,从而知道 MySQL 是如何处理你的 SQL 语句的。它可以
对于单键索引,尽量选择针对当前query过滤性更好的索引
在选择组合索引的时候,当前Query中过滤性最好的字段在索引字段顺序中,位置越靠前越好。
在选择组合索引的时候,尽量选择可以能够包含当前query中的where字句中更多字段的索引
查询中哪些情况不会使用索引?
要让小表驱动大表
Order by子句,尽量使用 Index 方式排序。尽可能在索引列上完成排序操作,遵照索引建的最佳左前缀
group by实质是先排序后进行分组,遵照索引建的最佳左前缀
分库和分表(了解)
为什么要分库:数据库集群环境后都是多台 slave,基本满足了读取操作; 但是写入或者说大数据、频繁的写入操作对master性能影响就比较大,这个时候,单库并不能解决大规模并发写入的问题,所以就会考虑分库。
所以我们把原本存储于一个库的表拆分存储到多个库上, 通常是将表按照功能模块、关系密切程度划分出来,部署到不同库上。优点:
但是无法解决单表数据量太大的问题–>分表
分库分表带来的问题:
面试题:讲一讲百万级别或以上的数据如何删除
参考与感谢:
https://zhuanlan.zhihu.com/p/164519371
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。