0.数据库的优化
1.选取最适用的字段属性;
2.尽量使用join来代替子查询;
3.尽量适用union来代替手动创建临时表;
4.事务;
5.尽量适用索引;
6.锁定表;
7.使用外键:锁定表的方法可以维护数据的完整性,但是它却不能保证数据的关联性。这个时候我们就可以使用外键。
8.优化查询语句;

1.请简洁描述Mysql中InnoDB支持的四种事务隔离级别名称,以及逐级之间的区别?
图片说明
Read Uncommitted(读取未提交内容) >> 在该隔离级别,所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用,因为它的性能也不比其他级别好多少。读取未提交的数据,也被称之为脏读(Dirty Read)。
Read Committed(读取提交内容) >> 这是大多数数据库系统的默认隔离级别(但不是MySQL默认的)。它满足了隔离的简单定义:一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读(Nonrepeatable Read),因为同一事务的其他实例在该实例处理其间可能会有新的commit,所以同一select可能返回不同结果。
Repeatable Read(可重读) >> 这是MySQL的默认事务隔离级别,它确保同一事务的多个实例在并发读取数据时,会看到同样的数据行。不过理论上,这会导致另一个棘手的问题:幻读(Phantom Read)。简单的说,幻读指当用户读取某一范围的数据行时,另一个事务又在该范围内插入了新行,当用户再读取该范围的数据行时,会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制(MVCC,Multiversion Concurrency Control 间隙锁)机制解决了该问题。注:其实多版本只是解决不可重复读问题,而加上间隙锁(也就是它这里所谓的并发控制)才解决了幻读问题。
Serializable(可串行化) >> 这是最高的隔离级别,它通过强制事务排序,使之不可能相互冲突,从而解决幻读问题。简言之,它是在每个读的数据行上加上共享锁。在这个级别,可能导致大量的超时现象和锁竞争。

//
数据隔离的四种级别:
1.未提交读:即事物可以再没有提交时被读取;会导致的问题是脏读,不可重复读,幻读
2.提交读:即事物必须在提交过后才可以读取;会导致的问题是不可重复读和幻读;
3.可重复读:是mysql的默认隔离级别,他能保证事务在并发时读到一样的数据,但是会导致幻读。innodb通过多版本并发控制协议解决了不可重复读,使用间隙锁解决了幻读;
3.可串行化:这是事务隔离的最高级别,是强制事务排序,不能相互冲突,从而解决了幻读。

2.在Mysql中ENUM的用法是什么?
ENUM 类型:enum 在数据库底层以整型方式储存,从 1 开始,0 用于指代空或错误的字符串。
ENUM 是一个字符串对象,其值通常选自一个允许值列表中,该列表在表创建时的列规格说明中被明确地列举。

在下列某些情况下,值也可以是空串("") 或 NULL:

如果将一个无效值插入一个 ENUM (即,一个不在允许值列表中的字符串),空字符串将作为一个特殊的错误值被插入。事实上,这个字符串有别于一个"普通的"空字符串,因为这个字符串有个数字索引值为 0。稍后有更详细描述。
如果一个 ENUM 被声明为 NULL,NULL 也是该列的一个合法值,并且该列的缺省值也将为NULL 。如果一个 ENUM 被声明为 NOT NULL,该列的缺省值将是该列表所允许值的第一个成员。
每个枚举值均有一个索引值:

在列说明中列表值所允许的成员值被从 1 开始编号。
空字符串错误值的索引值为 0。这就意味着,你可以使用下面所示的 SELECT 语句找出被赋于无效 ENUM 值的记录行。
mysql> SELECT * FROM tbl_name WHERE enum_col=0;
NULL 值的索引值为 NULL。

3.CHAR和VARCHAR的区别?
char:定长,即可容纳字符数,char的字符如果跟给定的长度不符合,用空格补齐,取出的时候再把空格去掉,所以如果内容有空格,会被自动消除掉。速度上,定长快一点,效率高一点
varchar:变长,如果经常修改,就比较适合。

4.事务是如何通过日志来实现的,说得越深入越好
Undo日志记录某数据被修改前的值,可以用来在事务失败时进行rollback;Redo日志记录某数据块被修改后的值,可以用来恢复未写入data file的已成功事务更新的数据。
例如某一事务的事务序号为T1,其对数据X进行修改,设X的原值是5,修改后的值为15,那么Undo日志为<T1, X, 5>,Redo日志为<T1, X, 15>。
也有把undo和redo结合起来的做法,叫做Undo/Redo日志,在这个例子中Undo/Redo日志为<T1, X, 5, 15>。
当用户生成一个数据库事务时,undo log buffer会记录被修改的数据的原始值,redo会记录被修改的数据的更新后的值。

redo日志应首先持久化在磁盘上,然后事务的操作结果才写入db buffer,(此时,内存中的数据和data file对应的数据不同,我们认为内存中的数据是脏数据),db buffer再选择合适的时机将数据持久化到data file中。这种顺序可以保证在需要故障恢复时恢复最后的修改操作。先持久化日志的策略叫做Write Ahead Log,即预写日志。
基本流程如下:
因为事务在修改页时,要先记 undo,在记 undo 之前要记 undo 的 redo, 然后修改数据页,再记数据页修改的 redo。 Redo(里面包括 undo 的修改) 一定要比数据页先持久化到磁盘。 当事务需要回滚时,因为有 undo,可以把数据页回滚到前镜像的状态,崩溃恢复时,如果 redo log 中事务没有对应的 commit 记录,那么需要用 undo把该事务的修改回滚到事务开始之前。 如果有 commit 记录,就用 redo 前滚到该事务完成时并提交掉。

5.drop,delete与truncate的区别
drop:删除内容和定义,释放空间。(表结构和数据一同删除)
drop table user;
truncate:删除内容,释放空间,但不删除定义。(表结构还在,数据删除)
truncate table user;
delete:删除内容,不删除定义,也不释放空间。
delete from user;
三者的执行速度,一般来说:drop > truncate > delete

释放空间可以体现在:
通过delete删除的行数据是不释放空间的,如果表id是递增式的话,那么表数据的id就可能不是连续的;而通过truncate删除数据是释放空间的,如果表id是递增式的话,新增数据的id又是从头开始,而不是在已删数据的最大id值上递增。

delete from user 与 truncate table user ,虽然同样是删除user表的数据,但却有很大的区别:
1、delete from user 在删除数据时,是一行数据一行数据地删除,每删除一行数据,就在事务日志中为删除的那行数据做一项记录,因此可对delete操作进行回滚(roll back);
truncate table user 在删除数据时,是通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放;因此,truncate 的执行速度比 delete 快,且使用的系统和事务日志资源少(在表数据越多的情况下对比更明显); 不过,在执行回滚命令时,delete 将被撤销,而 truncate 则不会别撤销。
2、delete 可以删除部分行数据, truncate 只能删除表中所有数据。
3、delete语句是数据库操作语言(dml),这个操作会放到 rollback segement 中,事务提交之后才生效;如果有相应的触发器(trigger),执行的时候将被激活。
truncate、drop 是数据库定义语言(ddl),操作立即生效,原数据不放到 rollback segment 中,不能回滚,操作不激活 触发器(trigger)。
4、truncate 在删除表中的所有行后,表的结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值,请改用delete。
5、truncate 将重新设置高水平线和所有的索引。在对整个表和索引进行完全浏览时,经过 truncate 操作后的表比Delete操作后的表要快得多。
6、当表被清空后表和表的索引讲重新设置成初始大小,而delete则不能。
7、truncate 不能清空父表。
8、truncate 不能用于参与了索引视图的表。
注:在什么情况下,使用 delete 而不使用 truncate ?
1、当只要删除表中部分数据时,使用 delete ,因为 truncate 只能清空表中所有数据;
2、当要删除数据的表中存在外键(foreign key)约束时,应使用 delete,不能使用 truncate,因为 truncate 操作不能激活 触发器(trigger)。

6.局部性原理与磁盘预读
CPU访问存储器时,无论是存取指令还是存取数据,所访问的存储单元都趋于聚集在一个较小的连续区域中。
https://blog.csdn.net/xd_1437/article/details/103253632
时间局部性:如果一个信息项正在被访问,那么在近期它很可能还会被再次访问。
空间局部性:在最近的将来将用到的信息很可能与现在正在使用的信息在空间地址上是临近的
顺序局部性:在典型程序中,除转移类指令外,大部分指令是顺序进行的。顺序执行和非顺序执行的比例大致是5:1。此外,对大型数组访问也是顺序的。
计算机各模块处理速度:CPU >> Cache >>> 主内存 >>>> 磁盘
磁盘存取,磁盘I/O涉及机械操作。磁盘是由大小相同且同轴的圆形盘片组成,磁盘可以转动(各个磁盘须同时转动)。磁盘的一侧有磁头支架,磁头支架固定了一组磁头,每个磁头负责存取一个磁盘的内容。磁头不动,磁盘转动,但磁臂可以前后动,用于读取不同磁道上的数据。磁道就是以盘片为中心划分出来的一系列同心环。磁道又划分为一个个小段,叫扇区,是磁盘的最小存储单元。

磁盘读取时,系统将数据逻辑地址传给磁盘,磁盘的控制电路会解析出物理地址(哪个磁道,哪个扇区),于是磁头需要前后移动到相应的磁道——寻道,消耗的时间叫——寻道时间,磁盘旋转将对应的扇区转到磁头下(磁头找到对应磁道的对应扇区),消耗的时间叫——旋转时间,这一系列操作是非常耗时。

为了尽量减少I/O操作,计算机系统一般采取预读的方式,预读的长度一般为页(page)的整倍数。页是计算机管理存储器的逻辑块,硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块,每个存储块称为一页(在许多操作系统中,页得大小通常为4k),主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时,会触发一个缺页异常,此时系统会向磁盘发出读盘信号,磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中,然后异常返回,程序继续运行。

计算机系统是分页读取和存储的,一般一页为4KB(8个扇区,每个扇区125B,8*125B=4KB),每次读取和存取的最小单元为一页,而磁盘预读时通常会读取页的整倍数。根据文章上述的【局部性原理】①当一个数据被用到时,其附近的数据也通常会马上被使用。②程序运行期间所需要的数据通常比较集中。由于磁盘顺序读取的效率很高(不需要寻道时间,只需很少的旋转时间),所以即使只需要读取一个字节,磁盘也会读取一页的数据。

至于磁盘分页,参考计算机操作系统的分页,分段存储管理——逻辑地址和物理地址被分为大小相同的页面,逻辑地址中叫页,物理地址中叫块。

7.数据库范式
第一范式:属性的原子性
当关系模式R的所有属性都不能在分解为更基本的数据单位时,称R是满足第一范式的,简记为1NF。满足第一范式是关系模式规范化的最低要求,否则,将有很多基本操作在这样的关系模式中实现不了。
(1、每一列属性都是不可再分的属性值,确保每一列的原子性
2、两列的属性相近或相似或一样,尽量合并属性一样的***保不产生冗余数据。)

第二范式:属性完全依赖于主键
如果关系模式R满足第一范式,并且R得所有非主属性都完全依赖于R的每一个候选关键属性,称R满足第二范式,简记为2NF。
(每一行的数据只能与其中一列相关,即一行数据只做一件事。只要数据列中出现数据重复,就要把表拆分开来。)

第三范式:设R是一个满足第一范式条件的关系模式,X是R的任意属性集,如果X非传递依赖于R的任意一个候选关键字,称R满足第三范式,简记为3NF.
(数据不能存在传递关系,即每个属性都跟主键有直接关系而不是间接关系。像:a-->b-->c 属性之间含有这样的关系,是不符合第三范式的。)

注:关系实质上是一张二维表,其中每一行是一个元组,每一列是一个属性

8.存储过程与触发器的区别
触发器原理:
触发器与存储过程非常相似,触发器也是SQL语句集,两者唯一的区别是触发器不能用EXECUTE语句调用,而是在用户执行Transact-SQL语句时自动触发(激活)执行。触发器是在一个修改了指定表中的数据时执行的存储过程。通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。由于用户不能绕过触发器,所以可以用它来强制实施复杂的业务规则,以确保数据的完整性。触发器不同于存储过程,触发器主要是通过事件执行触发而被执行的,而存储过程可以通过存储过程名称名字而直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时,SQLSERVER就会自动执行触发器所定义的SQL语句,从而确保对数据的处理必须符合这些SQL语句所定义的规则。
触发器的作用:
触发器的主要作用是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。它能够对数据库中的相关表进行级联修改,强制比CHECK约束更复杂的数据完整性,并自定义操作消息,维护非规范化数据以及比较数据修改前后的状态。与CHECK约束不同,触发器可以引用其它表中的列。在下列情况下使用触发器实现复杂的引用完整性;强制数据间的完整性。创建多行触发器,当插入,更新、删除多行数据时,必须编写一个处理多行数据的触发器。执行级联更新或级联删除这样的动作。级联修改数据库中所有相关表。撤销或者回滚违反引用完整性的操作,防止非法修改数据。
触发器与存储过程的区别:
触发器与存储过程的主要区别在于触发器的运行方式。存储过程必须有用户、应用程序或者触发器来显示的调用并执行,而触发器是当特定时间出现的时候,自动执行或者激活的,与连接用数据库中的用户、或者应用程序无关。当一行被插入、更新或者删除时触发器才执行,同时还取决于触发器是怎样创建的,当UPDATE发生时使用一个更新触发器,当INSERT发生时使用一个插入触发器,当DELETE发生时使用一个删除触发器。

9.锁的优化策略
1、读写分离:因为读的次数远远大于写的次数,所以读写锁分离
2、分段加锁:减小锁的粒度。但也不能将锁的粒度过于细化,不然可能会出现线程的加锁和释放次数过多,反而效率不如一次加一把大锁。
3、减少锁持有的时间:在需要锁竞争的之后在加锁,减少锁的占有时间,以减少线程间互斥的可能。
4.锁粗化:一个线程如果多次请求一个锁,则将多次锁操作整合成对锁的一次请求,从而减小对锁请求的同步次数。
JVM:锁偏向 ,轻量级锁,自旋锁,锁消除

10.什么情况下设置了索引但无法使用
1.全值匹配
2.最佳左前缀
3.在索引上加计算,函数等操作
4.存储引擎使用了索引中范围条件右边的列
5.使用一些查找范围关键字,例如!=<>;
6.like左边直接%的
9.字符串不加单引号
10.用or

11.什么情况下不宜建立索引?
1.数据频繁修改的字段
2.where语句查询用不到的
3.有重复且均分布均匀的字段不适合;
4.数据已经很少也不适合建索引;
5.参与列计算的列不适合建索引。

12.解释MySQL外连接、内连接与自连接的区别
1.外连接:left join/right join/join
根据左表,右表或两个表中的所有数据行进行匹配连接
2.内连接:inner join 则是只有条件的交叉连接,根据某个条件筛选出符合条件的记录,不符合条件的记录不会出现在结果集中,即内连接只连接匹配的行。
3.自连接:即笛卡尔连接,即不需要任何条件,直接将两个表中的数据进行匹配连接。

13.完整性约束包括哪些?
数据完整性(Data Integrity)是指数据的精确(Accuracy) 和 可靠性(Reliability)。
包括:
(1)实体完整性:规定表的每一行在表中是惟一的实体。
(2)域完整性:是指表中的列必须满足某种特定的数据类型约束,其中约束又包括取值范围、精度等规定。
(3)参照完整性:是指两个表的主关键字和外关键字的数据应一致,保证了表之间的数据的一致性,防止了数据丢失或无意义的数据在数据库中扩散。
(4) 用户定义的完整性:不同的关系数据库系统根据其应用环境的不同,往往还需要一些特殊的约束条件。用户定义的完整性即是针对某个特定关系数据库的约束条件,它反映某一具体应用必须满足的语义要求。

14.Mysql 的存储引擎,myisam和innodb的区别。
myisam:只有表锁
innodb:除了表锁之外还添加了行锁
图片说明

15.如何进行SQL优化
1.尽量具体化到要查询出来的字段,别用*;
2.对于经常查询的字段建立索引;
3.尽量少用%s%这样的索引,这样会放弃索引;
4.尽量少用or,!=null这样的范围匹配,会导致索引失效;
5.尽量避免在where查询中用null
6.尽可能使用varchar代替char。
7.优化group by;
8.如果能用数字来代替,尽量别用字符;
9.避免使用耗费资源的操作

16.乐观锁和悲观锁是什么,INNODB的标准行级锁有哪2种,解释其含义。
悲观锁与乐观锁
悲观锁,也叫悲观并发控制,当事务A对某行数据应用了锁,并且当这个事务把锁释放后,其他事务才能够执行与该锁冲突的操作,这里事务A所施加的锁就叫悲观锁。

看到这里,会发现,我们之前学习的共享锁和排他锁(行锁,间隙锁,next-key lock)都属于悲观锁。

乐观锁,也叫乐观并发控制,它假设多用户并发的事务在处理时不会彼此互相影响,各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前,每个事务会先检查在该事务读取数据后,有没有其他事务又修改了该数据。如果其他事务有更新的话,那么当前正在提交的事务会进行回滚。

悲观锁与乐观锁的实现方式
悲观锁的实现依靠的是数据库提供的锁机制来实现,例如select * from news where id=12 for update,而乐观锁依靠的是记录数据版本来实现,即通过在表中添加版本号字段来作为是否可以成功提交的关键因素。
标准行级锁
共享锁(S Lock):允许事务读取一行数据。
排他锁(X Lock):允许事务更新或删除一行数据。

17.MVCC的含义,如何实现的
MVCC是一种多版本并发控制机制。MVCC是通过保存数据在某个时间点的快照来实现的。
在每个表后添加创建版本号和删除版本号,版本号为修改该项的事务版本号。
读取只能读取创建版本号小于该事务版本号以及无删除版本号或删除版本号大于事务版本号的项。
update操作将旧数据标记为删除,插入相同的新数据就新创建版本号。

18.MYSQL的主从延迟怎么解决。
https://blog.csdn.net/hao_yunfeng/article/details/82392261

https://blog.csdn.net/u013575592/article/details/88110514

19.视图和表有什么区别?
1.视图是已经编译好的sql语言
2.视图没有物理记录,表有物理记录;
3.表是内容,而视图是窗口;