MySQL面经整理

MySQL专题

1.索引建立的原则？
(1)确定对表的操作是大量的查询操作还是大量的增删改操作？
(2)频繁出现在where子句中的字段建立索引。
(3)建立复合索引提高性能。
(4)小型表尽量不建立索引。
(5)避免较少值得字段建立索引。
(6)避免对较大数据类型得字段建立索引。
(7)对于经常存取得列尽量不要建立索引。
(8)避免回表（覆盖索引）。
2.ACID以及事务的隔离级别？
(1)原子性，一致性，隔离性，持久性。
(2)读未提交：脏读，不可重复读，虚读。
(3)读已提交：不可重复读（多次读取得到的结果不一样），虚读。
(4)可重复读：存在虚读。
(5)串行化：解决了所有问题。
3.三种问题是如何出现的？
(1)脏读：就是读到了其他事务没有提交的数据。
(2)不可重复读：主要针对update和delete，读取到了另一个事务修改后的数据，只需要将对应的数据锁起来就好了。
(3)幻读：主要是针对insert，读取到了另一个事务插入的数据，这个时候就需要锁住相近的数据，都不可以动了。
4.聚簇索引和非聚簇索引？
(1)聚簇索引：将数据存储和索引放在一起、并且是按照一定的顺序组织的，找到索引也就找到了数据，数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻的存放在磁盘上的。非聚簇索引：叶子节点不存储数据，存储的是主键值，也就是说根据索引查找到主键的位置再去磁盘查找数据，这就有点类似一本书的目录。
(2)聚簇索引的优点：相比非聚簇索引效率高，范围查询效率高，适合排序；
(3)聚簇索引的缺点：维护代价大，使用某些主键会导致效率不如全表扫描；主键过大，辅助索引也会更大。
5.MVVC？
(1)首先对于每一条聚簇索引中的数据的更新操作都会产生一个事务id（在这个数据里面），这个id是递增的，相当于按照时间顺序递增的，之后存放起来，其后面还有一个指针指向其上一个版本的数据在undo_log里面。
(2)在我们想要查询的时候，首先会获取一个readView就是当前系统中活动的事务，之后我们找到这些数据的事务id，看看他是在哪里，在我们当前查询的这些事务前面就是提交完成了，可以读取；要是再后面那就是未来的事务，不能动。
6.MySQL的三种日志记录方式？
(1)statement：记录的是修改SQL的语句（5.0之前只有这一种）。
(2)Row：记录的是每行数据的变更
(3)Mixed以上两种模式的混合。
7.什么是最左匹配？
其是针对联合索引说的，从左到右开始任何连续的索引都是匹配的上的，但是遇到<,>,between,like就会停止匹配。例如（a,b）建立索引，b=2就应用不到，但是a=1 and b=2就应用得到，b=2 and a=1也可以，编译器自动优化顺序。
(1)我们b+树的建立是根据一个索引建立的，其实就是根据a建立的，所以直接用b是无法查询的。
(2)但是当a相等的情况下，b是有序的，所以a有了，b也会可以用。
(3)当遇到范围查询的时候，后面就无法生效了，因为a发生了跳跃的时候，b又重新排序的，所以还是相当于乱序。
8.SQL的执行顺序？
(1)第一步：两表笛卡尔积。
(2)第二步：join确定表。
(3)第三步：on，由绑定条件生成中间表。
(4)第四步：where，筛选。
(5)第五步：group by分组。
(6)第六步：having，继续筛选。
(7)第七步：select，筛选出需要的属性。
(8)第八步：distinct，去重。
(9)第九步：order by排序。
(10)第十步：limit，分页。
9.数据库的水平分表和垂直分表？
一般数据库的查询优化基本都是基于索引的，也就是优化语句或者索引；但是当海量数据的时候，优化完了查询还是慢怎么办。
(1)水平分表，id自增，可以用这个idmod3之后得到的结果拼接到表名，这样就生成了三张表。Mod3的结果就是每一个数据分配到哪一个表。对于不存在id的也可以进行md5加密。
(2)垂直分表：将列进行分开，比如有一个字段存在大量的数据，但是不是每次都能用的上，用上的时候很少，这个时候就可以进行垂直分表。
10.数据库的垂直分表的要求？
(1)大型字段，blob，text等字段放在一个表中
(2)经常组合查询的列放在一个表中
(3)不经常查询用得到的列放在一个表中
但是要避免联查，否则得不偿失。
11.垂直分库：将多个不同的表按照业务耦合进行归类，之后分别放在不同的库，这些库可以放在不同的服务器，从而缓解负载。
12.跨库join的几种解决思路？
(1)字段冗余：共用字段在每一个表中都存上一个
(2)数据同步：定时进行一个数据层面的同步。
(3)全局表：所有系统都可能依赖的模块的表在每一个数据库中都存上一份。
(4)系统层组装。
13.获取全局唯一id的几种方式？
(1)自增
优点：简单，无需任何操作；保持定量增长；单表内能保持唯一性。
缺点：高并发下性能不佳，主键产生的性能上先就是数据库服务器的性能上限；
水平扩展困难，分布式数据库下无法保证唯一性。
(2)UUID
优点：本地生成ID，不需要远程调用；全局唯一不重复；水平扩展能力很好。
缺点：ID战用128bits，需要存成字符串类型，索引效率低（因为首先是太大了，存放效率以及io效率都差，其次就是由于uuid的无序性，插入的时候可能会使得页面分割，移动数据使得效率差，同样页面不饱和也会使得效率差）；生成ID没有时间戳，无法保证趋势递增。
(3)SnowFlake：雪花算法：产生一个long类型的ID，使用其中41bit作为毫秒数，10bit作为机器编号，12bit作为毫秒内的序列号。这个算法单机理论上每秒能够产生最多（1000212）个，完全能满足业务需求。
(4)对于不同的数据库表设计相同的步长，比如三个表，每一个表步长都设计成是3，就可以了。
优点：解决DB单点问题
缺点：不利于数据库的扩容，增加一个节点。
(5)基于数据库的号段模式：就是数据库每一行数据里面有一个type表示业务类型，max_id表示这个业务能够承载的最大的id数目，之后每次用的时候都需要看看version对不对的上，对的上才能更新。
14.InnerJoin和OuterJoin的区别？
(1)InnerJoin产生两个表的交集，如果两个表有重复，那么就产生笛卡尔积。
(2)OuterJoin包括leftJoin和rightJoin，前者生成表1的完全集，后面没有与之匹配的就为null；后者生成表2的完全集，前面没有与之匹配的就为null。
15.关系型数据库和非关系型数据库的区别？
(1)关系型数据库采用了关系模式来组织的数据库，即一个二维表模型，符合范式要求。非关系型数据库是采用简单的key-value形式进行数据组织。
(2)关系型数据库容易理解，使用方便，易于维护。但是网站的并发度要是高的话，硬盘的I/O是一个很大的平静；对于海量数据的表查询的话，效率很低。数据库比较难进行横向扩展。性能欠佳，由于需要按照ACID进行设计。
(3)非关系型数据库对于系统功能的增加，字段的变动都有着非常好的支持，但是只适合存储一些简单的数据，对于需要进行复杂查询的数据的时候，关系型数据库更为合适。
16.索引失效的场景？
(1)模：模糊查询，like为代表，索引失效。
(2)型：数据类型错误，比如，字段类型为varchar，但是我们where后面的用的int，就会失效。
(3)数：对索引字段使用函数，索引会失效。
(4)空：索引不存null值，如果不限制索引列为notnull，那么就不会按照索引查。
(5)运：对索引进行加减运算就会失效。
(6)最：最左原则。
(7)快：全表扫描，要是数据库预计使用全表扫描比使用索引还快，那么就不是用索引了。
17.为什么数据库采用B+树存储数据而不是B树或者是红黑树？
(1)使用B+树因为其只有叶子节点存放数据，不会再其他的层级上存放数据，这样保证了减少IO次数，不像B树一样，每一层都存放数据，会导致IO次数变多；同时B+树叶子节点上还有指针，这样方便区间查询，B树不行。
(2)红黑树一般应用于内存存储，深度太高的话IO操作浪费时间。
18.数据库索引类型？
(1)单列索引：主键索引，唯一索引，普通索引。
(2)组合索引（最左匹配原则）
(3)全文索引：只有在MyISAM中才能用，主要是检索char，varchar和text才能用。
19.聚簇索引？
(1)只要索引是相邻的，那么在物理层面也是相邻的。
(2)聚集索引是携带者数据的。
(3)大数据量排序和全表扫面不是很友好。
20.非聚簇索引？
(1)只存放目录，后面不是指向数据的而是指向聚簇索引里面的东西的。
(2)之后相当于两次查询。
21.慢查询如何排查？
(1)首先打开慢查询日志，之后定义一下哪些是慢查询。
(2)定位了之后，就看看这个sql的执行计划（explain plan for）。
(3)没有索引就添加索引。
(4)可能mysql使用特定的查询优化器导致没使用我们想要的索引，那么就优化sql就可以了。
(5)如果是数据库表太大了，那么就分表。
22.InnoDB和MyISAM的区别？
(1)InnoDB支持事务，MyISAM不支持事务，但是InnoDB会将每一条sql都当成是事务提交上去。
(2)InnoDB支持外键，但是MyISAM不支持外键。
(3)InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。
(4)nnoDB不保存表的具体行数，执行select count() from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快。
(5)InnoDB不支持全文索引，MyISAM支持，但是后期也支持了。
(6)InnoDB必须有唯一主键。
(7)InnoDB默认行级锁。
23.MySQL如何进行主从同步？
(1)首先主库会对自己的所有的更新（insert，update，delete）操作都放在binlog日志里面。
(2)之后主库创建一个binlog dump thread，将所有的binlog内容发送到从库里面。
(3)从库接收到之后发起连接，连接到主库。
(4)从库连接成功之后创建一个I/O线程，将收到的binlog存放到relaylog里面。
(5)之后创建一个SQL线程，将relaylog的东西存放到自己的库里面。
(6)使用binlog + position做增量同步。
-异步复制：就是发送了之后就干自己的事情了，不需要关心是否同步成功。
-半同步复制：发送了binlog之后，需要持续等待至少一个从节点返回同步成功即可，如果持续没有任何回复，就降级成异步。
-全同步：等待所有同步完成，性能较差。
24.MYSQL的ACID分别由什么保证？
(1)A原子性：由undolog日志，回滚，撤销执行的事务保证。
(2)D持久性：由redolog保证，宕机了就重复执行一次。
(3)I隔离性：由MVVC保证。
(4)C一致性：由其余三个保证。
25.MYSQL如何解决幻读？
(1)MVVC
(2)串行化
(3)Select for update
26.Where和ON的区别？
(1)left join 的话，不管on中的条件是不是真的，都会返回left的记录。
(2)Where是在临时表生成之后，在进行过滤，已经没有join的含义了，不符合要求的全部过滤掉。
27.next-key-lock？
(1)他是间隙锁+行锁，就是select * from t for update
(2)假如我们得数据字段a得值是1，3，5，7，9
(3)如果我们查询5，则会将(3,5],(5,7]都锁住，之后不允许我们向这两个区间里面进行插入数据。
(4)但是，如果我们的a是唯一属性，那么这个锁就会优化变成行级锁，只锁住索引本身，不锁柱区间。
28.关系数据库为什么要设定字段列字段的长度？
因为倾向于行级存储，那么即时不设置某些字段的数值，那么也会给予占位，方便于日后对该行的增删改
29.什么是倒排索引？
(1)就是首先将长文档进行分词，分词得到得结果再分别统计其出现在哪个文档以及出现了多少次。
(2)之后我们根据词去找对应得文档，这样倒映射。
30.undolog原理？
(1)首先，在每一次更新数据之前，都需要记录当前数据的状态。
(2)之后，将更新操作放到undolog里面。
(3)时候将undolog持久化到磁盘里面。
(4)将数据持久化到磁盘里面
(5)提交事务。
31.redolog原理？
(1)在每一次更新数据之后，都要记录更新后的数据到redolog
(2)将redolog写入磁盘
(3)事务提交。
32.为什么MYSQL中innodb没有把count(*)也放在磁盘里面呢？
(1)因为InnoDB是需要多版本并发控制的，就是他自己是不知道自己读出来的数据有多少，需要一条一条读出来累加。
(2)MyISAM不支持事务，所以直到总数是多少。
33.什么是crash-safe？
其实MySQL最大的两个优点，就是能够恢复到任何时间点的状态（由binlog保证）另一个优点就是即使数据库崩溃了，重启之前提交的数据都不会丢失（由undolog和redolog保证，这个就是crash-safe）。
(1)WAL（wirte ahead log）就是对数据修改之前现要修改日志，保证数据的一致性和持久性，并且提升性能（数据写入是随机写，日志写入时顺序写）
(2)数据在有更新的时候，做redolog分成两段提交，首先要记录redolog，之后将状态置为prepare；之后记录binlog；再修改redolog，状态置为commit。这样能够防止宕机之后数据有问题。
34.如何提高Mysql的读写效率？
(1)在读的时候：
-尽量要命中索引；
-数字类型不要使用字符串类型存储；
-使用varchar代替char，边长字段存储空间较小；
-索引的创建规则；
(2)在写的时候
-多条数据插入的时候，不要一条一条执行
-使用事务（因为其将多条语句揉成一起执行）
35.Innodb的四大特性？
(1)插入缓存：在插入数据的时候，如果非聚簇索引页存储在缓存池中， 那么就直接插入到索引页中，不存在就放到缓存也中，到时候按照一定的频率合并存入磁盘中（减少io次数），聚簇索引实际上是id自增的顺序的，所以插入效率很高，没有这种情况。
(2)二次写：就是存入数据库是先存入一个缓存（这个速度会很快）中，之后缓存放入数据库，当真正入库的时候，如果宕机就可以从这里恢复。
(3)自适应哈希索引：经常访问的二级索引数据会被放到里面。
(4)预读：innoDB将64个页认为是一个extent。
-顺序预读：如果已经连续读取了前50个数据，那么就认为下面的也要被读，就提前加载。
-随机预读：如果某个extent里面的很多个页都被读取了，那么认为其他的页也是要被读取的，就把其他的页也先加到缓存里面。
36.MyISAM和InnoDB的选取？
(1)MyIsam一般用于读多写少的情况，其原子性要求低，并且count(*)操作实际是经常有的；InnoDB一般用于读少写多的情况下，因为其对于并发的控制更为严谨一些。
(2)MySIAM对于数据的恢复很快。
37.使用间隙锁的目的？
(1)防止幻读，对范围进行读取的时候，InnoDB加上间隙锁，防止对间隙之间的数据进行修改，实际上插入也不行了。
(2)满足恢复的要求，对范围进行加锁，恢复的期间防止对这个范围修改数据。
(3)间隙锁也是有缺点的：对范围加锁，并发程度降低。
38.索引的缺点？
(1)查询没使用索引的时候，InnoDB会放弃行级锁，使用表级锁，开销增大。
(2)索引无法包含所有的条件的时候，也会触发间隙锁。
(3)创建和维护索引需要消耗大量的时间。
(4)创建索引也需要大量的物理空间。
39.对于大批量的插入，如何进行优化？
(1)多条插入合并成一条，减少交互。
(2)多个客户端插入，可以使用insert delayde让insert语句直接执行，否则会先写入内存中，再写入磁盘中。
(3)可以将数据写入到文件中，之后从文件中读取插入，速度很快。
40.SQL的其余优化？
(1)order by最好要结合索引使用。
(2)有or的时候，对于每个条件都加入索引，要不然就会全表扫描。
(3)嵌套语句使用join，不会产生临时表。
41.什么是存储过程？
存储过程就是一组经过预编译，执行特定功能的语句。
(1)优点
-执行效率高；
-服务端运行，减少客户端压力
-一个存储过程可以有多条语句，提高网络通信的效率
-保护数据安全
(2)缺点
-移植性差，存储过程依赖数据库
-不便于调试
-无法进行分库分表，因为其不知道数据在哪里了。
(3)优化手段
-用sql函数代替循环
-中间结果存放在临时表中
-事务尽量短
42.Drop，Delete，Truncate的区别？
(1)Drop删除表的一切，不记录日志，表也删除
(2)Delete可以加上条件，一条一条删，DML。
(3)Truncate删除表中所有数据，但是不删除表，也不记录日志。
43.varchar，char，char(50)，int(20)的区别？
(1)varchar表示变长，char为定长。
(2)50表示最大存储长度。
(3)20最大显示宽度。
44.加行级锁的问题？
or update 可以根据条件完成行锁，并且id是有索引的。如果id不是索引，for update会加表锁。
45.什么影响数据库性能？
(1)硬件
(2)系统
(3)数据库引擎
(4)参数配置
(5)数据结构设计和sql语句的执行
46.将一下Mysql的三层日志？
(1)binlog：属于mysql-server，会记录我们所有的操作。
(2)undolog：属于innodb引擎，里面存放了所有更新操作的一个相反操作，可以保证事务的原子性。
(3)redolog：属于innodb引擎，是一个预写日志，所有我们的操作都需要先写到这个日志里面之后在写到磁盘里面，防止没写完就断电（WAL write ahead log）。并且保证了数据的一致性。
47.写日志的具体流程？
(1)执行引擎先看看数据在不在里面，在的话就直接返回，不在的话就查询后返回。
(2)修改数据并且更新到内存中。
(3)将修改的数据写入redolog，并将这条数据的状态设置成prepare。
(4)将数据写入binlog。
(5)提交事务，并将数据状态改成commit。
48.如果不这么干会出现什么问题？
如果我们写完了redo，直接写binlog，这样要是写binlog的时候断电了，就会导致我们恢复数据的时候，binlog会丢失一行更新；如果我们先写binlog，后写redo，假设写redo的时候断电了，我们就多出了binbog里面的操作，使得数据不一致。所以用两重保障。