MySQL

数据库的三大范式？

第一范式:列不可再分割

第二范式:满足第一范式的基础上，非主键完全依赖于主键

第三范式:满足第二范式的基础上，非主键直接依赖于主键

Drop,Delete和truncate的区别？

用法:

drop table 表名

truncate table 表名

delete from 表名 where .....

不同:

1. drop删除表数据和结构，truncate清空表，delete删除表中数据
2. delete是DML(数据库操作语言)，drop和truncate是DDL(数据库定义语言)。所以delete事务提交后才生效，如果有触发器，也触发器也会执行。drop和truncae立刻生效，不会触发触发器。
3. delete是一行一行删除，操作会记录在日志中，可以回滚。
4. 效率方面drop>truncate>delete

事务的四大特性？

ACID

1. 原子性(事务的一系列操作要么全部成功，要么全部失败)(undo log来实现)
2. 一致性(事务执行完的前后，数据保持一致)(首先保证AID，然后在应用层面通过代码判断数据库数据是否有效，然后决定回滚还是提交数据)
3. 隔离性(事务不会相互影响)(锁+mvcc保证)
4. 持久性(事务提交后，对数据库的操作是永久性的)(redo log来实现，提交事务前把更新的数据写入redo log中)

数据库的隔离级别，默认那个，可以解决什么问题？

并发事务会出现的三大问题:

脏读(事务未提交):一个事务修改数据后，另一个事务读取到了修改后的数据，然后前一个事务回滚，那么后一个事务读取的数据就有问题了。

不可重复读(事务已提交，内容变化):一个事务读取数据，另一个事务修改该数据并提交，然后前一个事务再次读取该数据，发现两次读取的数据内容不一致。

幻读(事务已提交，条数变化):一个事务读取数据，另一个事务增加或者删除该数据并提交，然后前一个事务再次读取该数据，发现两次读取的内容条数发生变化。

1. 读未提交(脏读，不可重复读，幻读)
2. 读已提交(不可重复读和幻读)
3. 重复读(mysql默认)(幻读)
4. 序列化(不会出现问题)

怎么实现可重复读和读已提交？

https://www.cnblogs.com/jmliao/p/13204946.html

利用MVCC(多版本并发控制来实现的)MVCC是一种基于乐观锁的思想来实现的，主要用来解决读写不阻塞问题

与MVCC相关的有三个重要的东西，隐藏列，undo log和readview.

隐藏列:在innodb引擎中，每个数据表都会有两个隐藏列(创建的版本号(事务的id)和回归指针)，如果数据表没有指定索引，则会还有一个row_id隐藏列(innodb默认主键，自增)。

undo log(回滚日志):当事务对数据进行更新后，旧数据会被记录到undo log中。旧数据的创建版本号就是事务的更改该数据的事务的id，新数据的回滚指针指向旧数据。数据多次更新后，就会有一个数据的版本链，可以用来回滚数据。

readview(未提交事务的快照):当进行查询操作的时候，事务会生成一个readview，readview就是当前未提交的事务快照。

readview中有:

MVCC过程:

查询一条数据的时候，可以根据undo log中数据的版本链和当前事务产生的readview来判断哪个版本的数据对当前事务可见，然后返回结果。

1.当前版本的数据的创建版本号=创建readview事务的id,则说明该数据是当前事务更新的，所以是可见的。

2.当前版本的数据的创建版本号<readview中未提交事务最小的事务id，则说明生成readview时，更新该版本数据的事务已经提交了，所以是可见的。

3.当前版本的数据的创建版本号>=readview中系统应该分配给下一个事务的事务id，则说明生成readview之后的事务更新了该数据，所以是不可见的。

4.当前版本的数据的创建版本号介于readview中min_trx_id和max_trx_id之间，则会查找事务列表，如果创建版本号跟列表中某个事务id相同，则不可见，否则可见。

然后根据版本链向下循环执行以后操作。

读已提交:每次读操作都生成一个新的readview。

重复读:事务的多次读操作共用第一次读操作生成的readview

怎么解决幻读？

1.数据库的隔离级别设置为序列化。

2.MVCC + next-key lock(gap lock(间隙锁) 和 record(行锁)的结合版)

MVCC解决快照读 + next-key lock解决当前读。

在默认隔离级别下 select *** from ** where *** for update(排他锁)(推荐使用)

select *** from ** where *** in share mode(共享锁)(当前时候需要更新数据的时候可能发生死锁)

mysql中的锁？

表锁:锁定的是整张表

行锁(record lock):利用索引来实现，锁的是数据行

共享锁:S(一个事务加了S锁，其他事务可以加S锁，但是不能加X锁)

排他锁:X(一个事务加了X锁，其他事务不能加S锁和X锁)

gap锁:间隙锁，锁住的是索引之间进行加锁，主要作用于范围查询。

next-key锁:是行锁和gap锁的结合。

InnoDB和MyISAM的区别？

Myisam存储文件有.frm表结构，MYD数据，MYI索引，而Innodb的存储文件.frm，ibd或者ibdata1即数据和索引存储在一起。

Innodb支持事务，myisam不支持

Innodb支持外键，myisam不支持

innodb支持行锁和表锁，myisam支持表锁

Innodb表空间较大，myisam表空间较小

索引用来干嘛，索引的数据结构？

索引是一种排好序的数据结构用来检索数据，提高查询效率的。

索引的数据结构-hash索引，b树和b+树

Hash索引类似hash表的结构-使用数组+链表的数据结构(拉链法)。

b树它是一个m阶平衡搜索树，具有以下性质:

1. 根节点有[2,m]子女
2. 非根节点的关键字个数为j ,ceil(m/2) - 1 <=j <= m - 1
3. 非根节点的度的个数为j,ceil(m/2) <= j <= m
4. 叶子结点位于同一深度。

(1)叶子节点位于同一深度

(2)关键字没有冗余

(3)叶子节点的关键字从左至右依次递增。

 

B+树它是对b树进行了改进，也是一颗m阶平衡搜索树，具有以下性质:

1. 根节点有[2,m]子女
2. 非根节点有j个子女ceil(m/2) <= j <= m
3. 有k个子女的节点必有k个关键字
4. 叶子结点位于同一深度

1. 叶子节点位于同一深度
2. 非根节点的关键字作为索引使用
3. 叶子节点拥有一份完整的关键字
4. 叶子节点使用指针相连，从左到右依次递增

B+树查询到关键字并没有结束，而是沿着关键字的左边向下查询，直到查询到叶子节点才结束。

B+树和B树的区别？为什么用B+树？

B+树相比b树的优势:

1. 单个节点存储更多的关键字，使得查询的io次数变少
2. 所有的查询都要查找到叶子节点，查询性能稳定。
3. 所有的叶子节点形成有序链表，便于范围查询。

数据库hash索引和B+树的区别？

1. Hash 索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询
2. 优化器不能使用hash索引来加速order by操作
3. Hash 索引不能利用部分索引键查询。
4. Hash 索引在任何时候都不能避免回表扫描。
5. Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。​​​​​​​

聚簇索引和非聚簇索引的区别？

聚簇索引:表数据的存储顺序是按照索引的顺序来存储的。

非聚簇索引:表数据的存储顺序和索引的顺序无关。

数据库有哪些索引(四种)？

1. 主键索引
2. 唯一索引
3. 普通索引
4. 全文索引​​​​​​​​​​​​​​

​​​​​​​有主键的组合索引怎么走？

复合索引遵循最左前缀匹配原则，”即带头大哥不能死，中间兄弟不能断”的原则.

如果使用到了全部的复合索引就按照全部索引来走，通过explain分析type = ref（匹配某个单独值的所有行，走索引）

或者type = range(检索给定的范围，走索引)。

如果使用到了部分索引看是否满足最左匹配原则，如果满足，则走索引 type=ref或者range，否则遍历索引type = index。

​​​​​​​Mysql联合索引？

两个或者两个以上的列组成的索引就算联合索引。联合索引需要遵循最左前缀匹配原则，即”带头大哥不能死，中间兄弟不能断”的原则。比如联合索引(a,b,c)，那么可以以三种方式来查询索引，a|ab|abc。

为什么主键索引快？

主键索引是聚合索引，表数据的存储顺序和索引的顺序一致，并且b+树的叶子节点存放的是行数据。而其他的辅助索引，b+树的叶子节点存放的是主键的值，那么需要再利用主键的值查询主键索引才能够得到具体的值。

Explain查看sql的执行计划重要字段？

type:访问类型(ALL,index,range,ref,const,system,null)(从左到右性能从差到好)

possible_keys(指出mysql能使用哪些索引在表中找到记录)

key(实际使用的索引)

key_len(索引的长度)

Sql慢查询优化？

开启mysql慢查询

在mysql的配置文件中配置慢查询的定义时间和慢查询日志记录

查看慢查询日志，通过explain 来对sql查询语句进行分析，看下是否用到了索引

创建索引来优化sql，尽量避免全表扫描。

将字段很多的表拆分成多个表(水平分表)，增加中间表(通过表连接来查询)，增加冗余字段，提高查询速度。

如何进行MySql优化？

1. 建立索引，尽量避免全表扫描。
2. 读写分离，master用来写数据，slave用来读数据
3. 使用redis缓存
4. 分库。根据业务将数据库拆分成多个。
5. 分表。水平分表(将多个字段拆分，主键作为冗余字段)和垂直分表

超大分页怎么处理？

利用延迟查询或者子查询进行优化。

select * from student limit 0, 100;

优化后:select a.* from student a, (select id from student limit 0, 100) b where a.id = b.id;

Mysql主从复制的过程？

MySql中的日志文件?

1.重做日志(redo log)(物理格式的日志，记录的是物理数据页面的信息，恢复速度快)

作用:确保事务的持久性。redo log中记录事务执行后的状态，mysql发生故障尚有脏页未写入磁盘，可以根据redo log进行重做，从而达到事务的持久性。

2.回滚日志(undo log)(逻辑格式的日志)

作用:保证数据的原子性，1.可以用于事务回滚2.同时提供多版本并发控制下的读

3.二进制日志(binary log)(逻辑格式的日志)

作用:提交事务前，记录该次事务的sql操作(主要是增删改)，用于主从数据复制和同步。

4.慢查询日志(slow query log）

作用:存放慢查询的sql语句

5.中继日志(relay log)(物理格式的日志)

作用:mysql的slave从master拷贝binary log数据到relay log中