Hello，今天给各位童鞋们分享的是Mysql面试习题，赶紧拿出小本子记下来吧
数据库三大范式
第一范式（1NF）无重复的列
所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF）中表的每一行只包含一个实例的信息。简而言之，第一范式就是无重复的列。
第二范式:属性完全依赖于主键 [ 消除部分的函数依赖 ]
假定选课关系表为SelectCourse(学号, 姓名, 年龄, 课程名称, 成绩, 学分)，关键字为组合关键字(学号, 课程名称)，因为存在如下决定关系：
(学号, 课程名称) → (姓名, 年龄, 成绩, 学分)
这个数据库表不满足第二范式，因为存在如下决定关系：
(课程名称) → (学分)
(学号) → (姓名, 年龄)
第三范式:属性不依赖于其它非主属性 [ 消除传递依赖 ]
满足第三范式（3NF）必须先满足第二范式（2NF）。第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。
事务是什么?事务的特性?
事务是由一组SQL语句组成的逻辑单元,是满足ACID特性的一组操作,可以通过Commit提交一个事务,也可以使用Rollback进行回滚,事务具有以下四个属性
- A – 原子性(Atomicity) 事务是一个原子操作单元,其对数据的修改,要么全部执行,要么全部不执行.
- C – 一致性(Consistent) 事务开始和完成前,数据都必须保持一致状态(这里的一致性是指系统从一个正确的状态,迁移到另一个正确的状态)
- I – 隔离性(Isolation) 数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的"独立"环境执行,隔离性是当多个用户并发访问数据库时,比如操作同一张表时,数据库为每一个用户开启的事务,不能被其他事务的操作所打扰,多个并发事务之间要相互隔离
- D – 持久性(Durable) 事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持
补充
这几个特性并不是平级关系
只有满足一致性,事务的执行结果才是正确的
在无并发的情况下,事务串行执行,隔离性一定能够满足,此时只要满足原子性,就一定能满足一致性
在并发的情况下,多个事务并发执行,事务不仅要满足原子性,还需要满足隔离性,才能满足一致性
事务满足持久性是为了能应对数据库崩溃的情况(日志系统)
并发一致性问题有哪些?
更新丢失
T1和T2两个事务都对一个数据进行修改,T1先修改,T2随后修改,T2的修改覆盖了T1的修改
解决方案:悲观锁(并发量低) 乐观锁(并发量高)
脏读
事务B读到了事务A修改但尚未提交的数据,还在这个数据的基础上做了操作,此时,如果A事务回滚Rollback,B读取的数据失效,不符合一致性要求
不可重复读
在一个事务内,多次读同一个数据,在这个事务还没有结束时,另一个事务也访问该同一数据,那么,在第一个事务的两次读数据之间,由于第二个事务的修改,那么第一个事务读到的数据可能不一样,这样就发生了在一个事务内两次渡到的数据是不一样的
T2读一个数据,T1对该数据做了修改,如果T2再次读取这个数据,此时读取的结果和第一次读取的结果不同
解决方案 如果只有在修改事务完全提交之后才可以读取数据,则可以避免该问题,把数据库的隔离级别调整到REPEATABLE_READ
幻读
事务A读取到了事务B提交的新增数据,不符合隔离性
解决办法:如果在操作事务完成数据处理之前,任何其他事务都不可以添加新数据,则可避免该问题,把数据库的事务隔离级别调整到SERIALIZABLE_READ
事务的隔离级别?
- 读未提交 (Read Uncommitted)
- 读已提交 (Read Committed)
- 不可重复读(Repeatable Read)
- 串行化(Serializable)
事务的持久性是怎么做到的?
redolog binlog
redo log 是 InnoDB 引擎特有的， binlog 是 MySQL 的 Server 层实现的，所有的引擎都是可以的
redo log 是物理日志，记录的是"在 XXX 页上做了 XXX 修改"; binlog 是逻辑日志，比如" 给 id = 2 这一行的 c 字段加 1"
redo log 是有固定大小的，所以它的空间会用完，如果用完的话，一定要进行一些写入磁盘的操作才可以继续; binlog 是可以追加写入的，也就是 binlog 没有空间的概念，一直写就行了
你是不是 redo log 和 binlog 傻傻分不清楚？
Mysql锁有哪些?粒度?隔离级别与锁的关系?
锁
1.共享锁
S锁,读锁(lock in share mode)
锁住的数据其他事务可以读,但无法修改和删除
2.排他锁
X锁,写锁
锁住的数据其他事务不可以读写
如果锁的行没有索引,则为表锁
innodb行锁是给索引上的索引项上锁
3.意向共享锁
IS 当给某一行增加共享锁时,数据库会自动给该表添加意向共享锁
4.意向排他锁
IX 当给某一行增加排他锁时,数据库会自动给该表添加意向排他锁
意向锁是系统自动操作的,在其他事务试图进行全表操作(加锁)时,可以先询问是否有意向锁表明表中有某几行被行锁锁住,以避免全表扫描
5.自增锁
特殊表级锁 针对Insert操作
关于临键锁,间隙锁,记录锁
间隙锁和next-key锁都是RR隔离级别特有的
1）记录锁：mysql默认的行锁是next-key锁。当使用唯一性索引等值查询匹配到记录时，退化为记录锁。
2）间隙锁：mysql默认的行锁是next-key锁。当使用索引查询没有匹配到任何记录时，退化为间隙锁。eg：存在id=1和id=4记录，select * from t where id =3 for update；select * from tb_temp where id > 1 and id < 4 for update; 就会将(1,4)区间锁定
3）Next-Key锁：锁住记录本身，还要锁住记录之间的间隙。eg：select * from tb_temp where id > 2 and id <= 7 for update; 会锁住(2, 7]，(7, ~)
没有匹配到任何记录时，退化成间隙锁。
4）范围查询：命中了部分record记录，使用next-key锁。eg：select * from tb_temp where id > 2 and id <= 7 for update; 会锁住(2, 7]，(7, ~)【锁住的区间会包含最后一个record的右边的临键区间】
间隙锁示例
1）间隙锁(辅助索引时)：以(键列，辅助索引列)为间隙点，两个间隙点之间的数据区域加锁。
next-key锁：包含了记录锁和间隙锁，即锁定一个范围，并且锁定记录本身，InnoDB默认加锁方式是next-key 锁。
2）InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。
任何辅助索引上的锁，或者非索引列上的锁，最终都要回溯到主键上，在主键上也要加一把锁。
3）间隙锁的目的防止幻读、防止间隙内有新数据插入、防止已存在的数据更新为间隙内的数据。
6.临键锁
7.间隙锁
粒度
锁根据粒度可分为 行锁 表锁 记录锁,页锁,库锁(少见)
无索引读写锁------>表锁
自增锁,意向锁—>表锁
有索引读写锁---->行锁
记录锁:行锁的一种,记录锁的范围是表中的某一条记录,记录锁是说事务在加锁后锁住的只是表的某一条记录
精准条件命中,且命中的条件是唯一索引
页锁:页级锁是Mysql中锁定粒度介于行级锁和表级锁中间的一把锁,表级锁速度快,但冲突多,行级锁冲突少,但速度慢
特点:开锁和加锁时间介于表锁和行锁之间:会出现死锁,锁粒度介于表锁和行锁之间,并发度一般
间隙锁 : 范围锁
读未提交 (Read Uncommitted)
存在脏读,不可重复读,幻读
读已提交 (Read Committed)
通过写锁解决脏读
在写数据时其他事务要查询此记录需阻塞,在写完提交后,才可以查看
会导致重复读和幻读
不可重复读(Repeatable Read)
通过长期读锁和长期写锁解决重复读
读锁:我读到的数据,你不能改,这样就解决了重复读
通过临键锁解决幻读
临键锁主要针对insert插入操作
串行化(Serializable)
ACID靠什么保证地
A原子性由undo log日志保证,它记录了需要回滚的日志信息,事务回滚时撤销已经执行成功的sql
C一致性由其他三大特性保证,程序代码要保证业务上的一致性
I隔离性由MVCC保证
D持久性由内存+redo log来保证,mysql修改数据同时在内存和redo log记录着此操作,宕机的时候可以从redo log恢复
InnoDB redo log写盘,InnoDB事务进入prepare状态如果前面prepare成功,binlog写盘,再继续将事务日志持久化到binlog,如果持久化成功,那么InnoDB事务则进入commit状态(再redo log里面一个commit记录)
MVCC了解吗?
多版本并发控制:读取数据时同一种类似快照的方式将数据保存下来,这样读锁和写锁就不冲突了,不同的事务session会看到自己特定的版本,版本链
对数据库的任何修改的提交都不会直接覆盖之前的数据，而是产生一个新的版本与老版本共存，使得读取时可以完全不加锁,所以MVCC主要解决了并发读取的性能问题
MVCC只在READ COMMITTED和REPEATABLE READ两个隔离级别下工作,其他两个隔离级别和MVCC不兼容,因为READ UNCOMMITTED总是读取最新的数据行,而不是符合当前事务版本的数据行,而SERIALIZABLE则会对所有读取的行都加锁
聚簇索引记录中有两个必要的隐藏列
trx_id:用来存储每次对某条聚簇索引记录进行修改的时候的事务id
roll_pointer:每次对哪条聚簇索引记录有修改的时候,都会把老版本写入undo日志中,这个roll_pointer就是存了一个指针,它指向这条聚簇索引的上一个版本的位置,通过它来获得上一个版本的记录信息,(注意插入操作的undo日志没有这个属性,因为它没有老版本)
delete truncate,drop有什么区别?
drop 直接整个表的结构都删除,再想记录数据,要重新建表
truncate:清空表的数据,并且释放空间,表结构还在,清空索引,不可回滚
delete:删除表中的指定数据,不释放空间,不清除索引,可以回滚
如何定位低效的sql?
慢查询日志
在业务系统中,除了使用主键进行的查询,其他的都会在测试库上测试其耗时,慢查询的统计主要由运维做.
慢查询的优化首先要搞明白慢的原因是什么?是查询条件没有命中索引?是load了不需要的数据列?还是数据量太大?
首先分析语句,看看是否load了额外的数据,可能是查询了多余的行并且抛弃掉了,可能是加载了许多结果中并不需要的列,对语句进行分析以及重写
分析语句的执行计划,然后获得其使用索引的情况,之后修改语句或者修改索引,使得语句可以尽可能地命中索引
如果对语句地优化已经无法进行,可以考虑表中地数据量是否太大,如果是可以进行横向或者纵向地分表
横向分表和纵向分表
纵向分表
将本来可以在同一个表的内容，人为划分为多个表
对于一个博客系统，文章标题，作者，分类，创建时间等，是变化频率慢，查询次数多，而且最好有很好的实时性的数据，我们把它叫做冷数据。而博客的浏览量，回复数等，类似的统计信息，或者别的变化频率比较高的数据，我们把它叫做活跃数据。所以，在进行数据库结构设计的时候，就应该考虑分表，首先是纵向分表的处理。
横向分表
将用户表user分为user1,user2对id做特殊处理
InnoDB和MyISAB的区别
- InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
- InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
- InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，
- 因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
- Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；
索引的基本原理
索引是存储引擎用于快速找到记录的一种数据结构.
索引用来快速地寻找那些具有特定值地记录,如果没有索引,一般来说执行查询时遍历整张表
索引的原理:就是把无序的数据变成有序的查询
- 把创建了索引的列的内容进行排序
- 对排序结果生成倒排表
- 在倒排表内容上拼上数据地址链
- 在查询的时候,先拿到倒排表内容,再取出数据地址链,从而拿到具体数据
Mysql索引为什么不常用Hash?
从内存角度,据库中的索引一般是存储在磁盘上的，使用hash索引需要在内存中构建hash表，而表的数据量很大的时候可能无法把全量的索引列数据一次性装入内存；B+树每一个超级节点的大小可以设置成一个数据页的大小，每次查询只加载符合条件的少部分数据页，而不必把全量的索引数据都加载到内存。
从业务场景上，如果只需要根据特定条件查询一条数据的话确实hash更快，但是在实际业务中经常会查询多条、查询某个条件范围内的数据，这时候由于B+树索引有序，并且有链表相连，可以找第一个匹配上的，然后顺着链表把符合查询条件的数据一次取出来；而hash则无法做到这样的范围条件查询，因为是无序的，只能需要逐条遍历匹配。
Mysql索引为什么不用红黑树?
树的查询时间跟树的高度有关，B+树是一棵多路搜索树可以降低树的高度，提高查找效率。此外，操作系统针对硬盘读写的最小单元是块(block)，一个block大小一般是4KB, 也就是一次至少会读取4KB; 红黑树是二叉树，每层只有两个节点，加载一部分节点需要多次磁盘随机IO操作，效率非常低。
Mysql聚簇和非聚簇索引的区别
都是B+树的数据结构
聚簇索引:将数据存储与索引放到了一块,并且是按照一定的顺序组织的,找到索引也就找到了数据,数据的物理存放顺序与索引顺序是一致的,即:只要索引是相邻的,那么对应的数据一定也是相邻地存放在磁盘上的.
非聚簇索引:叶子节点不存储数据,存储的是数据行地址,也就是说根据索引查找到数据行的位置再去磁盘查找数据,这个就有点类似一本树的目录
优势
查询通过聚簇索引可以直接获取数据,相比非聚簇索引需要第二次查询(非覆盖索引的情况下)效率要高
聚簇索引对于范围查询的效率很高,因为其数据是按照大小排列的
聚簇索引适合用在排序的场合,非聚簇索引不适合
劣势
维护索引很昂贵,特别是插入新行或者主键被更新导致要分页的时候,建议在大量插入新行后,选择负载较低的时间段,通过optimize table优化表,因为必须被移动的行数据可能造成碎片,使用独享表可以弱化碎片
表如果使用UUID(随机ID)作为主键,数据存储会很稀疏,这就会出现聚簇索引有可能比全表扫描更慢,所以建议使用Int的auto_increment作为主键
如果主键比较大的话,那辅助索引将会变的更大,因为辅助索引的叶子存储的是主键值,过长的主键值,会导致非叶子节点占用更多的物理空间
InnoDB中一定有主键,主键一定是聚簇索引,不手动设置,则会使用unique索引,没有unique索引,则会使用数据库内部的一个行的隐藏id来当作主键索引.在聚簇索引之上创建的索引称之为辅助索引,辅助索引访问数据总是需要二次查找,非聚簇索引都是辅助索引,像复合索引,前缀索引,唯一索引,辅助索引叶子节点存储的不再是行的物理位置,而是主键值.
MyISM使用的是非聚簇索引,没有聚簇索引,非聚簇索引的两颗B+树看上去没什么不同,节点的结构完全一致,只是存储的内容不同而已,主键索引B+树的节点存储了主键,辅助索引B+树存储了辅助键,表数据存储在独立的地方,这两颗B+树的叶子节点都使用一个地址指向真正的表数据,对于表数据来说,这两个键没有任何差别,由于索引树是独立的,通过索引键检索无需访问主键的索引树
如果涉及到大数据量的排序,全表扫描,count之类的操作的话,还是MyISAM占优势,因为索引所占的空间小,这些操作是需要在内存中完成的
Mysql索引的数据结构,各自优劣
B+树-----平衡多叉树
索引设计的原则
查询更快,占用空间更小
mysql执行计划怎么看?(explain)
explain select * from A whrer X=? and Y=?
select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序
id相同，执行顺序由上至下
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id相同不同，同时存在
id号每个号码，表示一趟独立的查询。一个sql 的查询趟数越少越好。
selectType:查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询
SIMPLE:简单的 select 查询,查询中不包含子查询或者UNION
PRIMARY:查询中若包含任何复杂的子部分，最外层查询则被标记为Primary
DERIVED:在FROM列表中包含的子查询被标记为DERIVED(衍生)
MySQL会递归执行这些子查询, 把结果放在临时表里。
SUBQUERY:在SELECT或WHERE列表中包含了子查询
DEPENDENT SUBQUERY:在SELECT或WHERE列表中包含了子查询,子查询基于外层
UNCACHEABLE SUBQUREY
UNION:若第二个SELECT出现在UNION之后，则被标记为UNION；
若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED
UNION RESULT:从UNION表获取结果的SELECT
table:显示这一行的数据是关于哪张表的
partitions:代表分区表中的命中情况，非分区表，该项为null
type :优化sql的重要字段,也是我们判断sql性能和优化程度的重要指标
possible_keys:显示可能应用在这张表中的索引，一个或多个。
查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
key:实际使用的索引。如果为NULL，则没有使用索引
key_len:表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。 key_len字段能够帮你检查是否充分的利用上了索引
ref:显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
rows:rows列显示MySQL认为它执行查询时必须检查的行数。
filtered:这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数
Extra:包含不适合在其他列中显示但十分重要的额外信息
索引失效的场景
- where语句中包含or时，可能会导致索引失效(使用or并不是一定会使索引失效，你需要看or左右两边的查询列是否命中相同的索引)。
- where语句中索引列使用了负向查询，可能会导致索引失效,负向查询包括：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等。
- 索引字段可以为null，使用is null或is not null时，可能会导致索引失效
- 在索引列上使用内置函数，一定会导致索引失效
- 对索引列进行运算，一定会导致索引失效
- like通配符可能会导致索引失效
- 联合索引中，where中索引列违背最左匹配原则，一定会导致索引失效
- MySQL优化器的最终选择，不走索引
mysql主从同步原理
mysql主从同步的过程
Mysql的主从复制中主要有三个线程:master(binlog dump thread),slave(I/O thread,sql thread),Master一条线程和Slave中的两条线程.
主节点binlog,主从复制的基础是主库记录数据库的所有变更记录到binlog,binlog是数据库服务器启动的那一刻起,保存所有修改数据库结构或内容的一个文件
主节点log dump线程,当binlog有变动时,log dump线程读取其内容并发送给节点.
从节点I/O线程接受binlog内容,并将其写入到relay log中
从节点的SQL线程读取relay log文件内容对数据更新进行重放,最终保证主从数据库的一致性
注:主从节点使用binlog文件+position偏移量来定位主从同步的位置,从节点会保存其已接收到的偏移量,如果从节点发送宕机重启,则会自动从position的位置发起同步
由于mysql默认的复制方式是异步的,主库把日志发送给从库后不关心从库是否已经处理,这样会产生一个问题就是假设主库挂了,从库处理失败了,这时候从库升为主库后,日志就丢失了,由此产生两个概念
全同步复制
主库写入binlog后强制同步日志到从库,所有的从库都执行完成后才返回给客户端,但是很显然这个方式的话性能会收到严重影响
半同步复制
和全同步不同的是,半同步复制的逻辑是这样,从库写入日志成功后返回ACK确认为主库,主库收到至少一个从库的确认就认为写操作完成
简述mysql索引类型及对数据库的性能的影响
普通索引:允许被索引的数据列包含重复的值
唯一索引:可以保证数据记录的唯一性
主键:是一种特殊的索引,在一张表只能定义一个主键索引,主键用于唯一标识的一条记录,使用关键字PRIMARY KEY来创建
联合索引:索引可以覆盖多个数据列,例如Index(columnA,columnB)索引
全文索引:通过建立倒排索引,可以极大的提升检索效率,解决判断字段是否包含的问题,是目前搜索引擎的一种关键技术.
索引可以极大的提高数据的查询数据.
通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能
但是会降低插入,删除,更新表的速度,因为在执行这些写操作时,还要操作索引文件
索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大,如果非聚簇索引很多,一旦聚簇索引改变,那么所有非聚簇索引都会跟着变
好啦，今天的文章就到这里了，希望能够帮助到屏幕前迷茫的你们
