mysql面试复习

MySQL中 in 和 exists 区别::

Msyql中得in语句是把外表和内表做hash链接起来了,而exists语句是对外表做的loop循环,每次loop循环在对内表进行查询.主要是区分使用环境:

1,如果查询两个表的大小相当,那么in和exists的差别是不大的,

如果两个表中的一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in,not in 和not exists; 若是查询语句not in 那么内外表都进行全表的扫描,

没有用到索引,而not exists的的子查询是依然能够用到表上的索引,

所以无论那个表大,not exists都要比notin快 .

Mysql语句执行查询的过程:

1,客户端通过tcp链接发送请求到mysql链接服务器,连接器会对该请求进行权限的验证和链接资源分配.

2,查缓存,(当判断是否缓存是否命中的时候,mysql

而是直接使用sql语句和客户端发送过来的其他原始信息,所以任何字符上的不同都会导致缓存无法命中.)

3,语法分析,分析sql语句是否正确,如果把语句给到预处理器,检查数据表和数据列是否存在,

预处理器主要是对sql的正确和合法性进行检查

4,优化,是否使用了索引

5,交给执行器,去执行该sql语句,将数据保存在结果集中,同步到缓存中返回给客户端..’/’

为什么索引结构默认使用B+Tree

1,因为b+树的磁盘读写代价更低了,内部节点并没有指向关键字具体的信息的指针,因此其内部节点相对更小,读写的io次数就会降低

2,由于b+的数据都是存在叶子节点中,分支节点均为索引,方便扫描库,只需要扫描一遍叶子节点即可,b+树更适合在区间查询的情况下,所以通过使用b+数用于数据库的索引.

Hash: 虽然可以快速定位,但是没有顺序,io的复杂度高,基于hash表实现,只有memor存储引擎显示支持hash索引.

•      因为Hash索引始终索引的所有列的全部内容，所以不支持部分索引列的匹配查找 ；

•      如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题 。

二叉树： 树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。

红黑树： 树的高度随着数据量增加而增加，IO代价高。

聚簇索引与非聚簇索引？

在innoDB里,索引B+树的叶子结点上存储是整行数据的是主键索引,也被称为聚簇索引,即是将数据存储与索引放到了一块,找到了索引就是找到数据,.

而索引B+树的叶子结点存储了主键的值是非主键索引,也被称为非聚簇索引,二级索引.

两者的区别:

主要区别在于非聚簇索引的叶子结点不存储表中的数据,而是存储该对应的主键,

对于innodb来说,想要查找数据我们还是根据主键再去聚集索引中进行查找,然后根据聚集索引查找数据的这个过程,我们称为回表,第一次索引一般是顺序io,回表的操作是属于随机io,需要回表的次数越多,即是随机io的次数越多,我们也就越倾向于全表的扫描/

通常情况下,主键索引查询只会查询一次,但非主键索引需要回表查询多次,当然,如果是覆盖索引的话,查询一次即可,

•      注意：MyISAM无论主键索引还是二级索引都是非聚簇索引，而InnoDB的主键索引是聚簇索引，二级索引是非聚簇索引。我们自己建的索引基本都是非聚簇索引。

非聚簇索引一定会回表查询吗?

不一定,这涉及到查询语句所要求的字段是否全部命中了索引,如果全部命中了索引,那么就不同进行回表查询了,一个索引包含了所有需要查询的字段,称为覆盖索引.

联合索引是什么？为什么需要注意联合索引中的顺序？

Mysql可以使用多个字段同时建立一个索引,叫作联合索引,在联合索引中,如果想要命中索引,按需要建立索引时的字段顺序挨个使用,否则无法命中索引,因为是mysql在使用索引时候需要索引是有序的,当进行查询的时候,索引就会按照索引的匹配到的列然后进行向下的匹配.

Mysql的最左前缀原则:

最左前缀原则就是最左优先,在创建多列索引的时候,要根据业务需求,where字句中使用最频繁的一列放在最左边.

为什么索引结构默认使用B+Tree，而不是B-Tree，Hash，二叉树，红黑树？

B+tree: 磁盘IO占用低但效率高.为此读取的关键字多,;数据存储结构类型,将数据存储到了叶子节点,只需要一个扫描叶子节点即可,所以更加适合在区间查询情况,

Hash:

虽然可以快速定位,但是没有顺序,io的复杂度高.

•      基于Hash表实现，只有Memory存储引擎显式支持哈希索引 ；

二叉树:树的高度不均匀,不能自平衡,查找速率和高度有关,    io代价高

红黑树: 树的高度随着数据量的增加而增加,io代价高.

讲一讲前缀索引？

因为可能索引的字段非常长,又占内存空间,也不利于维护,所以我们就想,如果只是把很长的字段的前面的公共部分作为一个索引,就会产生超级加倍的效果,但是我们需要注意的是,order by 不支持前缀索引.

流程是:

先计算完整列的选择性:; 再计算不同前缀长度的选择性,找到最优长度之后,创建前缀索引. 

怎么查看mysql语句有没有用到索引:

1,通过explain 附加sql语句:

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title='Senior Engineer' AND from_date='1986-06-26';

为什么官方建议使用自增长主键作为索引？

结合b+树的特点,自增主键是连续的,在插入过程中尽量减少页分分裂,即使要进行页分裂,也只会分裂很少的一部分,并且能减少数据的移动,每次插入都是插入到最后,总之就是减少分裂和移动的频率.减少io.

创建索引的方式:

1,再执行create table 时创建索引

2,alter table 命令去增加索引,

3,使用create index 命令创建,

创建索引需要注意什么:

1,非空字段:应该指定列非非空,除非你想存储null,在mysql中含有控制的列是很难进行查询优化的.所以最好不要有控制,而是用0或者其他值代替,

2,取值离散大的字段: 变量各个取值之间的差异程度,, 列放到联合索引的前面,可以通过count()函数查看字段的差异值,返回值越大说明字段唯一值越多,字段的离散程度高.

3,索引字段越小越好,数据库的数据存储以页为单位进行存储的数据,越多一次io操作获取的数据越大,效率越高

建立索引的原则有哪些?

1. 最左前缀匹配原则,重要因为mysql就是会一直向右匹配直到遇见范围查询(<,>,between,like)等就会停止匹配,
2. =和in可以乱序,他们建立索引可以是任意顺序
3. 尽量选择区分度高的列作为索引.
4. 索引列不能参与计算,要保持列干净.
5. 尽量扩展索引而非添加新的索引

使用索引查询一定能提高查询的性能吗？

不一定,因为索引本身也是需要定期的维护和扫描的,每当记录在表中发生修改时,索引的本身也会被修改,因为索引也是需要一定存储空间和处理的,那些不必要的索引反而会使得查询速度变慢,.

什么情况下不走索引（索引失效）？

查看索引失效使用explain关键字

1,使用!=或者<>导致索引失效

2,类型不一致导致索引失效

3,函数导致的索引失效

4,运算符导致的索引失效,

5,or引起的索引失效

6,模糊搜索导致索引失效

7,not in ,not exists 失效

8,where条件中查询中使用了or关键字

9,条件查询中是用了like关键字,并且不符合最左前缀原则

10,联合查询不符合最左前缀原则

11,where条件查询后对字段进行null值判断

什么是数据库的事务?

事务是一个不可分割的数据库的操作序列,也是数据库并发控制的基本单元,其执行的结果必须使得数据库从一种一致性的状态变换到一致性的状态,事务是逻辑上的一组操作,要么都执行,要么都不执行.

事务的四个特性:

原子性: 事务是不可分割的,事务中包含的所有操作要么都要做,要么都不做,

一致性: 事务执行的结果必须是使得数据库从一个一致性变成另外一个一致性.

隔离性: 一个事物的执行不能干扰其他事务,也不能被干扰,即是所有的并发都是进行隔离的.

持久性: 一个事物一旦提交,它对数据库的改变就是永久性的,

四种隔离级别: 读未提交;读已提交.可重复读,可串行化(最高隔离)

什么是脏读?幻读?不可重复读?

事物实现的原理:

1,事物是基于重做日志文件和回滚日志实现,,

每提交一个事务必须将该事务的所有的日志写入到重做日志文件进行持久化,数据库就可以通过重做日志来保证事物的原子性和持久性. 每当有修改事务发生,还会产生undo log 如果需要回滚,则根据undo log的反向语句进行逻辑操作,undo log主要是用来实现数据库的一致性.

Msyql事务日志介绍一下?

Innodb中事务日志主要包括: redo log和undo log

Undo log指的是事务开始之前,在操作任何数据之前,首先将需要操作的数据备份到一个地方,(操作之前备份)

redo log指的是事务中操作的任何数据,将最新的数据备份到一个地方,事务日志的目的:实例或者介质的失败,事务日志文件就能派上用场了. (操作之后立马备份)

redo log不是随着事务的提交才写入的,而是在事务执行的过程中,便开始了写入redo中,具体的存储策略可以进行配置,防止在发生故障的时间点,还有脏页未写入磁盘. 在重启 MySQL 服务的时候，根据 redo log 进行重做，从而达到事务的未入磁盘数据进行持久化这一特性。RedoLog 是为了实现事务的持久性而出现的产物。

Undo log : 用来回滚行记录到某个版本,事务未提交之前,undo保存了未提交的之前的版本数据,undo中的数据可作为数据旧版本快照共其他并发事务的进行快照读. 是为了实现事务的原子性而出现的产物,在innodb中存储引擎中用来实现多版本的并发控制.

Mysql中binllong:

1,binlog是用来记录所有数据库表结构变更的操作,不会记录查询的操作,主要是以事件的形式记录,还包含了所执行的消耗的时间,二进制日志是事务安全型的,binlog的主要目的是复制和恢复.

主要有三种格式:

Statement: 基于sql语句的模式

Row:基于行的模式,记录都是行的变化

Mixed:混合模式,根据语句来选是上面两种的其中一种.

什么是mvcc?实现的原理是什么?

Mvcc,即是通过保存数据在某个时间点的快照来实现,根据事务的开始时间不同,每个事务对同一张表,同一时刻看到的数据可能是不一样的,,因为事务开始的时间不同,所以到了同一时刻看到的数据可能不一样.

对于innodb,聚簇索引记录中包含3个隐藏的列:

RowID: 隐藏的自增id,如果表中没有主键,innodb会自动的按row id产生的一个聚集索引树.

事务id:记录最后一次修改该记录的事务id,

回滚指针: 指向这条记录的上一个版本

Mvcc的最大好处是读不加锁,读写不冲突,极大的增强的mysql的并发性,通过了mvcc就可以保证了事务的acid中的隔离性

MyISAM和InnoDB存储引擎使用的锁：

Myisam采用的是表级锁,innodb支持行级锁和表级锁,默认为行级锁.

从锁的类别上分MySQL都有哪些锁呢？

从所的类别上有共享锁和排它锁:

共享锁:又叫读锁,同时可以有多个访问

排它锁:又叫写锁,当用户要进行数据写入时,则只能一个人写操作..

数据库中乐观锁两者都是和悲观锁是什么?怎么实现的?

两者都是用来控制数据库的并发操作的.

悲观锁: 假定会发生冲突的操作,将可能发生的冲突的情况和事务锁起来,直到提交事务,实现方式: 使用数据库中的锁机制.

乐观锁: 假定不会发生冲突情况,只在提交操作的时候检查是否违反了数据的完整性即可.通过version的方式来进行锁定,实现方式是通过版本号机制或CAS算法实现.

针对不同的优缺点: 乐观锁更适合写比较少的情况下,即是冲突了真的很少发生的时候,省去大量的锁的开销.针对写多的场景则是使用于悲观锁.

Innodb的引擎的行锁是怎么实现的?

是基于索引你来完整的行锁,例如: select * from tab_with_index where id=1 for update;

For updata 可以根据条件完整行锁的锁定,并且id是由索引键的列,如果id不是索引建那么innodb将完成表锁.就不会并发的操作.

什么是mysql中的死锁?怎么解决?

死锁时指两个或多个事务在同一个资源上相互占用,并请求锁定对方的资源,从而导致恶性循环的现象.,注意区分与进程死锁的区别:

常见的死锁的解决方法:

1,如果不同程序会并发的存取在多个表中,尽量约定以相同的顺序访问表,可以大大降低死锁的机会.

2,在同一个事务中,,尽可能的做到一次锁定的所需要的全部所有资源,减少死锁的产生概率

3,对于容易产生死锁的业务部分,可以采取提升锁的颗粒度来提升死锁产生的概率.

针对锁优化方面的意见?

使用较低的隔离级别,

设计索引,尽量使用索引去访问数据,加锁加精确减少锁的冲突,

选择合理的事务大小,不要让事务过大,申请加锁的资源时候,最好一次申请完成,避免死锁.

不要申请超过实际需要的锁级别,这样导致资源的浪费和占用

用过哪些分库分表中间件？不同的分库分表中间件都有什么优点和缺点？

这里就说自己接触过的mycat,是基于之前cobar进行改造的,属于proxy层的方案,支持的功能非常完善,,之前还用来做好读写分离的中间件.
如何对数据库如何进行垂直拆分或水平拆分的？

水平拆分的意思:就是把一个表的数据给弄到多个库的多个表里去,但是每个表的结构是相同的,只不过每个库的数据是不同的,所有的库表的数据加起来就是全部的数据,意义就是用来对抗高并发,就是用多个库存储容量进行扩容.

垂直拆分的意思:就是把一个有很多字段的表给拆分成多个表,或者是多个库上去,但是每个表的结构都是不一样的,每个库可能只包含了部分的字段,会将访问频率很高的字段放到一个表里面,然后将访问低的放到另一个表中.

两种分库分表的方式:

一种是按照range来分.就是每个库一段连续的数据,这个一般是按照比如时间范围来分隔.但很容易产生热点问题,大量的流量都访问到最新的数据上.

一种是按照某个字段的hash来均衡分散,较常用,但缺点是难以扩容和迁移,因为涉及到hash值的重新生成

Mysql主从复制的流程和原理:

基本的原理是3个线程以及之间的关联:

1,主:binlog线程—记录master所有改变了数据库数据的语句,写入到binlog中,

2,从机的io线程—在使用start slave之后,负责从master上拉取到binlog内容,放进自己的relay log中.,然后读取当前的自己的进度是否和master同步,如果同步了就睡眠等待新的事件发生后继续拉取内容,

3,从库的sql线程将relay log 中的语句进行读取,按照顺序执行该日志中的sql事件,从而让自己的数据库和master数据一致.

主从复制的复制方式有哪些?

1,基于sql语句的复制(SBR),:在服务器上执行sql语句,从服务器执行相同语句

2,基于行的复制,:把变化的内容直接复制过去,而不是执行命令

3,混合模式,先使用语句如果还不够精确,那么就复制数据

4,全局事务标识符(GTID)

基于sql语句复制的优点: 1,技术成熟,2,binlog文件较小,3,binlog中包含所有数据修改的信息,可以据此审核数据库安全情况,, 4binlog可以用于还原数据,作为一种备份

5,版本可以不一致.

缺点:  不是所有的update语句都可以被复制上,尤其是包含不确定操作的时候复制需要进行全表的扫描的update., 需要比 RBR 请求更多的行级锁对于一些复杂的语句，在从服务器上的耗资源情况会更严重，而 RBR 模式下，只会对那个发生变化的记录产生影响数据表必须几乎和主服务器保持一致才行，否则可能会导致复制出错执行复杂语句如果出错的话，会消耗更多资源。

主从同步时延怎么产生和解决的?

产生的原因:主从复制都是单线程的操作,主库对所有的ddl和dml产生的binlog,binlog是顺序写,所以效率很高,slave的slave)io_running线程到主库取日志,效率比较高,但如果dml和ddl对io的操作是随机的,不是顺序的,成本高了很多,还可能与slave上其他查询发生锁的争用,由于是单线程,所以一个ddl卡住了,后面的都会卡住.因为slave是可以并发的操作,而slavesql线程不可以.

MySQL数据库主从同步延迟是怎么产生的。

当主库的TPS并发较高时，产生的DDL数量超过slave一个sql线程所能承受的范围，那么延时就产生了，当然还有就是可能与slave的大型query语句产生了锁等待。

MySQL数据库主从同步延迟解决方案

最简单的减少slave同步延时的方案就是在架构上做优化，尽量让主库的DDL快速执行。还有就是主库是写，对数据安全性较高。另外就是使用比主库更好的硬件设备作为slave。

Sql的中的慢查询是怎么做的?怎么对慢查询进行优化,

1,首先对于慢查询需要分析产生的原因: 是因为sql语句太复杂还是因为查询条件没有命中

还是load了不需要的数据列,还是因为数据量太大了.

所以针对优化也是对着几个方面进行优化:

1,首先分析语句,对于语句进行优化,不去查询没有作用的数据

2,分析语句的执行计划,然后分析索引的使用情况,之后修改语句或者索引,尽可能让命中索引       

3,考虑是不是数据检索量太大,导致效率慢,必要时可以进行分表操作.

如何优化关联查询:

确定on后者using字句是否有索引,确保group by 和order by 只有一个表中的列,这样mysql才有可能使用索引列.

MySQL数据库cpu飙升到500%的话他怎么处理？

当 cpu 飙升到 500%时，先用操作系统命令 top 命令观察是不是 MySQLd 占用导致的，如果不是，找出占用高的进程，并进行相关处理。

如果是 MySQLd 造成的， show processlist，看看里面跑的 session 情况，是不是有消耗资源的 sql 在运行。找出消耗高的 sql，看看执行计划是否准确， index 是否缺失，或者实在是数据量太大造成。

一般来说，肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降)，等进行相应的调整(比如说加索引、改 sql、改内存参数)之后，再重新跑这些 SQL。

也有可能是每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等。

大表怎么优化？

1,限定数据查询范围,指令一个查询范围,一次性只能查询规定范围内的多条数据.

2,读写分离,将操作拆分开,多个从库提高读效率

3,使用缓存机制,将热点数据放到缓存中,

4,通过分库分表的方式进行优化,主要是对表进行水平和垂直拆分.