追梦的小猪超人
发布于

史上最全的技术岗面试笔记—数据库篇(下)

**一、聚集函数? **

SELECT city FROM weather WHERE temp_lo = max(temp_lo); WRONG不过这个方法不能运转,因为聚集 max 不能用于 WHERE 子句中。(存在这个限制是因为WHERE 子句决定哪些行可以进入聚集阶段;因此它必需在聚集函数之前计算。) 不 过,我们通常都可以用其它方法实现我们的目的;这里我们就可以使用子查询:
SELECT city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROMweather);

理解聚集和SQL的 WHERE 以及 HAVING 子句之间的关系对我们非常重要。

WHERE 和 HAVING 的基本区别如下: WHERE 在分组和聚集计算之前选取输入行(因此,它控制哪些行进入聚集计算), 而 HAVING 在分组和聚集之后选取分组的行。因此,WHERE 子句不能包含聚集函数;因为试图用聚集函数判断那些行输入给聚集运算是没有意义的。相反,HAVING 子句总是包含聚集函数。(严格说来,你可以写不使用聚集的 HAVING 子句, 但这样做只是白费劲。同样的条件可以更有效地用于 WHERE 阶段。)

当应用于空集时,SUM、AVG、MAX 和 MIN 函数可以返回 null 值。当 COUNT函数应用于空集时,它返回零(0)。如果返回值可能是 NULL,那么使用包装器类型;否则,容器会显示 ObjectNotFound 异常。

drop、truncate、 delete区别
最基本:
● drop直接删掉表。
● truncate删除表中数据,再插入时自增长id又从1开始。
● delete删除表中数据,可以加where字句。

二、非关系型数据库和关系型数据库区别,优势比较?

非关系型数据库的优势:
● 性能:NOSQL是基于键值对的,可以想象成表中的主键和值的对应关系,而且不需要经过SQL层的解析,所以性能非常高。
● 可扩展性:同样也是因为基于键值对,数据之间没有耦合性,所以非常容易水平扩展。

关系型数据库的优势:
● 复杂查询:可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。
● 事务支持:使得对于安全性能很高的数据访问要求得以实现。

like %和-的区别通配符的分类:
%百分号通配符:表示任何字符出现任意次数(可以是0次).
**_下划线通配符:**表示只能匹配单个字符,不能多也不能少,就是一个字符.

三、最左前缀原则?

多列索引:ALTER TABLE people ADD INDEX lname_fname_age (lame,fname,age);

为了提高搜索效率,我们需要考虑运用多列索引,由于索引文件以B-Tree格式保存,所以我们不用扫描任何记录,即可得到最终结果。

注:在mysql中执行查询时,只能使用一个索引,如果我们在lname,fname,age上分别建索引,执行查询时,只能使用一个索引,mysql会选择一个最严格(获得结果集记录数最少)的索引。

最左前缀原则:顾名思义,就是最左优先,上例中我们创建了lname_fname_age多列索引,相当于创建了(lname)单列索引,(lname,fname)组合索引以及(lname,fname,age)组合索引。

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')https://blog.csdn.net/qq_19557947/article/details/76951912

四、MySQL B+Tree索引和Hash索引的区别?

Hash索引和B+树索引的特点:
● Hash索引结构的特殊性,其检索效率非常高,索引的检索可以一次定位;
● B+树索引需要从根节点到枝节点,最后才能访问到页节点这样多次的IO访 问;

五、为什么不都用Hash索引而使用B+树索引?

Hash索引仅仅能满足"=","IN"和""查询,不能使用范围查询

Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B+树索引高

为什么说B+比B树更适合实际应用中操作系统的文件索引和数据库索引?

(1) B+的磁盘读写代价更低

B+的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B树更小。如果把所有同一内部结点的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

(2) B+tree的查询效率更加稳定由于非终结点并不是最终指向文件内容的结点,而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同,导致每一个数据的查询效率相当。

六、聚集索引和非聚集索引区别?

聚集索引表记录的排列顺序和索引的排列顺序一致,所以查询效率快。

聚集索引指定了表中记录的逻辑顺序,但是记录的物理和索引不一定一致。

聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致。

七、锁的基本概念?

当并发事务同时访问一个资源时,有可能导致数据不一致,因此需要一种机制来将数据访问顺序化,以保证数据库数据的一致性。多个事务同时读取一个对象的时候,是不会有冲突的。同时读和写,或者同时写才会产生冲突。

共享锁(Shared Lock,也叫S锁)

共享锁(S)表示对数据进行读操作。因此多个事务可以同时为一个对象加共享锁。排他锁(Exclusive Lock,也叫X锁)

排他锁表示对数据进行写操作。如果一个事务对对象加了排他锁,其他事务就不能再给它加任何锁了。

锁的粒度

就是通常我们所说的锁级别。MySQL有三种锁的级别:页级、表级、行级。

表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。

行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。

页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。

行锁(Row Lock)
对一行记录加锁,只影响一条记录。
通常用在DML语句中,如INSERT, UPDATE, DELETE等。
表锁(Table Lock)

对整个表加锁,影响标准的所有记录。通常用在DDL语句中,如DELETETABLE,ALTER TABLE等。

很明显,表锁影响整个表的数据,因此并发性不如行锁好。https://www.cnblogs.com/wenxiaofei/p/9853682.html

乐观锁

总是假设最好的情况,每次去拿数据的时候都认为别人不会修改,所以不会上锁,但是在更新的时候会判断一下在此期间别人有没有去更新这个数据,可以使用版本号机制和CAS算法实现。乐观锁适用于多读的应用类型,这样可以提高吞吐量。

悲观锁

总是假设最坏的情况,每次去拿数据的时候都认为别人会修改,所以每次在拿数据的时候都会上锁,这样别人想拿这个数据就会阻塞直到它拿到锁(共享资源每次只给一 个线程使用,其它线程阻塞,用完后再把资源转让给其它线程)。传统的关系型数据库里边就用到了很多这种锁机制,比如行锁,表锁等,读锁,写锁等,都是在做操作之前先上锁。

脏读

所谓脏读是指一个事务中访问到了另外一个事务未提交的数据,获得更新前的值

幻读

一个事务读取2次,得到的记录条数不一致

不可重复读

一个事务读取同一条记录2次,得到的结果不一致

解决方法

八、数据库的主从复制?

主从复制的几种方式:
同步复制:
所谓的同步复制,意思是master的变化,必须等待slave-1,slave-2,...,slave-n完成后才能返回。这样,显然不可取,也不是MySQL复制的默认设置。比如,在WEB前端页面上,用户增加了条记录,需要等待很长时间。

异步复制:

如同AJAX请求一样。master只需要完成自己的数据库操作即可。至于slaves是否收到二进制日志,是否完成操作,不用关心,MySQL的默认设置。

半同步复制:

master只保证slaves中的一个操作成功,就返回,其他slave不管。这个功能,是由google为MySQL引入的。

master的写操作,slaves被动的进行一样的操作,保持数据一致性。如果slave可以主动的进行写操作,slave又无法通知master,这样就导致了master和slave数据不一致了。

主从复制中,可以有N个slave,实现数据备份,异地容灾。

插入数据 insert into t1 values(5,'xiaoming',null); insert into t1 (id,name) values(2,'aa'); insert into t1 values(5,'xiaoming',null),(5,'xiaoming',null),(5,'xiaoming',null);insert into t1 (id,name) values (2,'aa'),(2,'aa'),(2,'aa');

查询 select * from t1; select name from t1; select * from t1 where id=10;

修改 update t1 set age=100 where id=10;

删除 delete from t1 where id=10;

修改表名 rename table t1 to t2;

修改表属性 alter table t1 engine=myisam/innodb charset=utf8/gbk;

添加表字段 alter table t1 add age int first/after xxx;

删除表字段 alter table t1 drop age;

修改表字段名和类型 alter table t1 change age newAge int;

修改表的类型和位置 alter table t1 modify age int first/after xx;

删除表 drop table t1;

九、MySQL主从复制原理?

MySQL 主从复制概念MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式,这样从节点不用一直访问主服务器来更新自己的数据,数据的更新可以在远程连接上进行,从节点可以复制主数据库中的所有数据库或者特定的数据库,或者特定的表。

MySQL 主从复制主要用途
l 读写分离
在开发工作中,有时候会遇见某个sql 语句需要锁表,导致暂时不能使用读的服务,这 样就会影响现有业务,使用主从复制,让主库负责写,从库负责读,这样,即使主库 出现了锁表的情景,通过读从库也可以保证业务的正常运作。
l 数据实时备份,当系统中某个节点发生故障时,可以方便的故障切换

l 高可用HA

l 架构扩展

随着系统中业务访问量的增大,如果是单机部署数据库,就会导致I/O访问频率过高。有了主从复制,增加多个数据存储节点,将负载分布在多个从节点上,降低单机磁盘I/O访问的频率,提高单个机器的I/O性能。

MySQL 主从形式

一主一从,一主多从,提高系统的读性能。一主一从和一主多从是最常见的主从架构,实施起来简单并且有效,不仅可以实现HA,而且还能读写分离,进而提升集群的并发能力。

多主一从 (从5.7开始支持)多主一从可以将多个mysql数据库备份到一台存储性能比较好的服务器上。

双主复制,也就是互做主从复制,每个master既是master,又是另外一台服务器的slave。这样任何一方所做的变更,都会通过复制应用到另外一方的数据库中。

级联复制。级联复制模式下,部分slave的数据同步不连接主节点,而是连接从节点。因为如果主节点有太多的从节点,就会损耗一部分性能用复制,那么我们可以让3~5个从节点连接主节点,其它从节点作为二级或者三级与从节点连接,这样不仅可以缓解主节点的压力,并且对数据一致性没有负面影响。

MySQL 主从复制原理

MySQL主从复制涉及到三个线程,一个运行在主节点(log dump thread),其余两个(I/O thread, SQL thread)运行在从节点。

主节点 binary log dump 线程

当从节点连接主节点时,主节点会创建一个log dump 线程,用于发送bin-log的内容。在读取bin-log中的操作时,此线程会对主节点上的bin-log加锁,当读取完成,甚至在发动给从节点之前,锁会被释放。

从节点I/O线程

当从节点上执行start slave命令之后,从节点会创建一个I/O线程用来连接主节点,请求主库中更新的bin-log。I/O线程接收到主节点binlog dump 进程发来的更新之后,保存在本地relay-log中。

从节点SQL线程

SQL线程负责读取relay log中的内容,解析成具体的操作并执行,最终保证主从数据的一致性。

复制的基本过程如下:

从节点上的I/O 进程连接主节点,并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容;

主节点接收到来自从节点的I/O请求后,通过负责复制的I/O进程根据请求信息读取指定日志指定位置之后的日志信息,返回给从节点。返回信息中除了日志所包含的信息之外,还包括本次返回的信息的bin-log file 的以及bin-log position;从节点的I/O进程接收到内容后,将接收到的日志内容更新到本机的relay log中,并将读取到的binarylog文件名和位置保存到master-info 文件中,以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log 的哪个位置开始往后的日志内容,请发给我”;

Slave 的 SQL线程检测到relay-log 中新增加了内容后,会将relay-log的内容解析成在祝节点上实际执行过的操作,并在本数据库中执行。

点赞
收藏
评论