Mysql面试题

• 如何设计一个关系型数据库

1. 设计一个数据库,首先数据库是一个存储数据的软件,所以首先要有一个存储模块,负责存储我们的数据,存储在机械硬盘或者我们的固态硬盘里面
2. 还要有一个组织和管理数据的实例,就是存储管理
3. 还要考虑存储的效率,要把数据提取到内存里面,减少IO,所以就需要缓存机制,当你访问一个数据,也就意味着你相邻的数据也有可能会被访问到
4. 还需要一个SQL解析器,通过指令来读取数据
5. 数据库也有可能出错,那么我们就需要查错恢复,就需要一个日志管理,异常机制
6. 数据库也需要一些权限,不能人人都能读取数据,需要权限管理
7. 为了进一步提升查询数据的速率,让数据库支持并发,就需要索引和锁结构.

• 为什么要使用索引

1. 如果我们使用最简单的方式查询数据,全盘扫描,即将全表的数据分批次,加载到内存里面,但是实际的开发过程中,用户量非常大,表数据特别多,极少数会用到查询所有的数据,都是有一些限制条件,你这样无疑查询起来会非常慢,所以我们要避免全表扫描,就引出了索引的结构.通过索引可以快速查询数据.
2. 什么是索引呢?索引就是能把数据限制在一定查找范围的一种结构,一般用主键作为索引

• Mysql的基本架构?

1. Mysql架构可以分为三层
2. 第一层为客户端,向数据库发送请求,jdbc
3. 第二层为服务器,分析器,连接器,缓存,优化器等等
4. 第三层为存储引擎,InnoDB,MyISAM,

• Mysql索引的底层是什么?为什么不采用二叉树和红黑树和B树?

1. Mysql索引的底层是B+树
2. 如果采用二叉树,在一些极端的情况下会变成一个链表,他会按照你的插入的顺序,比前一个大的放在右边,小的放在左边,如果你插入的顺序是依次递增的,那么就会变成一个链表.
3. 红黑树是一个平衡二叉树,虽然每次查找的时间复杂度都是O(logn),但是随着你数据越多,红黑树的高度就会越高.IO次数就越多,也会影响查询效率.
4. B树相对于红黑树来说,就是每层的节点树不再是1,而是会有很多,且每层都有一个data域和指向下一层的指针,这样它每次查找的效率都是O(logn),当有新的节点插入,他会分裂或者上下节点移动,保证是一个平衡的数结构
5. 其实B+树就是B树的一种变形,对B树进行了以下改进,B树的所有节点都要存储一个data,而B+树只有每个叶子节点才会存放data,其余节点用来索引,磁盘读写代价更低,这就决定了B+树更适合用来存储外部数据,即磁盘数据.
6. B+树叶子节点用指针连接,能够跨区域范围统计,提高了区间访问的性能.
7. B+树的查询效率稳定.查找每个数据的时间是相同的O(logn)

• InnoDB和MySIAM引擎区别?

1. Innodb存储引擎使用的是聚集索引,即B+树叶子节点包含了完整的数据记录;MySIAM存储引擎使用的是非聚集索引,B+树的叶子节点只有一个数据的地址,然后通过地址去寻找数据
2. Innodb支持行级锁表级锁;MyISAM只有表级锁,当你进行一个查询语句的时候,同时你也要执行一条增删改语句,那么增删改语句一定会等到你查询结果出来之后才会进行,因为查询语句会为数据库加一个读锁.当读锁没有释放,不能加写锁.当表数据量特别大的时候,就会出现问题,
3. Innodb支持事务,MyISAM不支持事务.
4. InnoDB支持外键,MyISAM不支持外键
5. InnoDB支持MVCC,应对高并发事务,MVCC比单纯的加锁更高效,MVCC只在提交读和重复读两个隔离级别下才工作;MVCC可以使用乐观锁和悲观锁实现

• 稠密索引和稀疏索引的区别?

1. 稠密索引是每个索引项都对应一条数据记录
2. 稀疏索引是每个索引项对应着多条数据

• 为什么InnoDB表必须要有主键,并且推荐使用整型的自增主键?

1. 首先Innodb是根据主键进行索引,有了主键效率更加高,如果没有主键,Innodb会默认给你加一个主键.
2. B+树在查找的过程中每一层的索引要进行比较,整型的比较快.
3. 自增主键:B+树中叶子节点有一个指向相邻节点的一个指针,又因为叶子节点从左到右是依次递增的,这样在范围查找的时候更加方便,根据这个指针顺藤摸瓜~
4. 自增主键: 防止B+树分裂,然后树平衡,使用自增,永远是在叶子节点后面添加元素,而不会在某两个叶子节点中间,有效的防止树分裂,减少开销.

• 如果定位并优化慢查询SQL

1. 根据慢日志定位慢查询SQL;如果一个SQL语句超过了指定的时间限制,这条SQL语句会被自动放在一个慢日志里面，可以进入这个日志里面查询具体是那个SQL语句执行那么慢．
2. 使用explain等工具分析SQL，explain放在select语句前面,type字段表示的是mysql找到指定行的方式,如果是index或者all说明是全表扫描,证明语句就需要优化;extra字段表示的是
3. 修改SQL尽量让SQL走索引

• 联合索引最左匹配原则的成因?

1. 联合索引由多列组成的索引
2. 最左匹配原则:比如说两个索引是AB,我们在where语句中调用where A = ? and B = ?会使用索引,where A = ?也会走索引,但是当走where B = ?不会走索引,就会走全表扫描.
3. 最左前缀匹配原则,就是mysql会一直向右匹配直到遇到范围查找就会停止匹配.
4. 联合索引的B+树是根据第一个数字进行排序的,比如说B+树的结构,最上边是(2,4),那么左下边可能是(1,1)(1,2)(2,1),右下边可能是(3,1)(3,3)(4,1),可以发现第一个索引是有序的,但是第二个索引是无序的,

• 索引是越多越好吗?

1. 物极必反,数据量小的表不需要建立索引,建立会增加额外的索引开销.
2. 数据变更需要维护索引,增加维护成本,
3. 更多的索引也会更多的空间.

• Mysql的分库分表

1. 分库分表就是随着你业务的发展,用户增多,数据访问量大,表单数据就会太大,会极大影响你sql的执行性能,这时候一张表就不够用了,就可以分表.分库是将一个库的数据拆分到多个库中,访问的时候就访问一个就好了.
2. 分库分表分为水平拆分和垂直拆分
3. 水平拆分:将一个表的数据拆分到多个库的多个表,每个表的表结构都一样,将数据均匀的放在更多的库里面,然后用多个库杠更多的并发量.一般就是按时间范围来分,或者hash分表
4. 垂直拆分:把一个有很多字段的表拆分多个表,每个表的表结构不一样

• 为什么不使用Hash索引而使用b+树?

1. 不支持范围查询,只能使用"=","in"
2. 哈希索引数据并不是按照索引值顺序存储的,无法应用于排序
3. 会出现哈希冲突,必须遍历链表中所有的行指针,逐行进行比较.
4. 但是InnoDB有一个特殊的功能自适应哈希索引,当某一些索引值使用的非常频繁,他会在内存中基于B+树再创建一个哈希索引.
5. B+树索引大大减少了服务器需要扫描的数据量,索引帮助服务器避免排序和临时表,将随机IO转换为顺序IO.

• Mysql的其他索引方式?

1. 全文索引: MyISAM存储引擎支持全文索引,用于查找文本中的关键词,而不是直接比较是否相等,全文索引更类似于是搜索引擎做的事情.
2. 空间数据索引:MyISAM存储引擎支持空间数据索引,可以用于地理数据存储,空间数据索引会从所有维度来索引数据

• 什么是事务?

1. 事务就是保证一个业务的多个数据库操作要么一起完成,要么一起失败,典型的例子就是转账.

• 事务的特点?事务的实现原理

1. 原子性:通过Undo log实现,在操作任何的数据之前,首先会将数据备份到一个地方,这个地方称为Undo log,系统可以通过Undo log中的备份将数据恢复到事务开启之前的状态;Undo log是一个逻辑日志,当你delete一条记录,undo log记录一个insert记录;当insert一条记录,Undo log记录一个delete记录;当update一条记录,记录一条相反的update记录.
2. 持久性:通过Redo log保证,他和Undo log相反,在事务提交之前,只要将redo log持久化即可,不需要将数据持久化.
3. 隔离性
4. 一致性

• 事务的每个隔离级别会发生什么问题?Mysql的默认级别

1. 未提交读,他会产生脏读现象,当一个进程开启事务,修改表数据,但是还没有提交,另一个进程开启事务,可以查看前一个进程已经修改了的数据.这就是脏读.
2. 可提交读,他会产生不可重复度现象,当一个进程开启了事务,修改表结构,但是没有提交,但是另一个进程开启事务,此时看到的是未修改的数据,此时前一个事务提交,此时后一个事务查看数据,发现数据是修改了的.在一个事务中,没有进行任何的修改操作,两次查询得到的结果是不一样的.
3. 重复读:他会产生幻读现象,当一个进程开启事务,另一个进程也开启事务,前一个进程插入一个数据提交,后一个进程查询数据,还是原来的数据,但是当你插入同一个数据的时候,会报错.
4. 串行化

• Mysql中有那些锁?

1. 共享锁:是将对象数据变成只读形式,不能进行更新,所以也被称为只读锁
2. 排他锁,当执行insert,delete.update的时候,其他事务不能读取该数据.

• 数据库崩溃时事务的恢复机制?
• 什么是聚簇索引和非聚簇索引

1. 聚簇索引: 叶子节点data域记录着完整的数据记录,这种索引方式被称为聚簇索引,因为无法把数据行存放在两个不同的地方,所以一个表中只能有一个聚簇索引
2. 非聚簇索引,叶子节点的data域记录着主键的值,因此进行查找时,先查找到主键值,然后再到主索引中进行查找

• 查询优化?怎么优化

1. 需要查询优化其实最基本的原因就是访问的数据太多,我们可以通过减少访问的数据量的方式进行优化,Mysql查询的过程是,先返回全部结果集,再进行计算,只返回必要的行,使用limit来限制,尽量少使用select *;或者是将访问很多次的数据放进缓存里面
2. Mysql查询只返回需要的数据后,也有可能为了结果集而扫描了过多的数据,可以通过一些命令查看扫描的行数和返回的行数,判断性能
3. 重构查询方式,可以将一个复杂的查询分为索格简单查询,如果一个大查询一次性执行的话,可能依次所著很多数据,占满整个事务日志,高金系统资源,阻塞很多小的但是重要的数据
4. 重构查询方式,可以将一个大连接查询分解成对每一个表进行依次单表查询

• 一个查询语句的执行过程?

1. 客户端发送一个查询语句给服务器
2. 服务器先检查缓存,如果有直接返回否则下一步
3. 服务器解析SQL,预处理,由优化器生成对应的执行计划
4. 调用存储引擎API执行计划
5. 返回给客户端

• SQL的视图,游标,存储过程,触发器
• 视图是一个或几个表中导出的虚拟的表
• 游标是一种从包含多条数据记录的结果集中每次提取一条记录的机制
• 存储过程:对一系列SQL的预处理,预编译SQL语句
• 触发器:执行完SQL语句后有什么操作。

• 数据库的三范式

1. 第一范式,就是数据库表的每一列都是不可分割的基本数据线,同一个列不能有多个值,比如说数据库中有一项是用户信息,这只能作为一个列,不能在对他进行分成姓名和电话两个属性,满足第一范式才符合关系型数据库
2. 第二范式,满足1范式后,要求表中的所有列,都必须依赖主键,而不能部分依赖.
3. 第三范式,满足2范式后,表中的每一列只与主键直接相关,而不能间接相关.不能存在传递依赖,