MySql大厂面试题总结
前言
该文章整理了网络大厂的一些面试题,并对其做出解答
一、数据库概念
1. 讲一下ACID
事务管理 原子性(Atomicity) 一致性(Consistency) 隔离性(Isolation) 持续性(Durability)
原子性 : 针对一个事务中的操作,要么全部成功要么全部失败
一致性 : 在一个事务中的重复读取只要该事务没进行操作读取的状态应该是一致的,修改也是
隔离性 : 不同的事务之间,相互不影响数据应该是隔离的
持续性 : 事务结束后不会随着外界原因导致数据丢失
2. ACID在MySql中的实现
原子性 : undo log 记录着对数据的操作日志,事务回滚后会根据undo log回滚
一致性 : 其他三种特性保证了一致性(原子性，持久性，隔离性)
隔离性 : MVCC 多版本控制,同时要看隔离级别(四种),越高性能越低
持续性 : mysql是并不会第一时间进行刷库,而是进行日志记录(redo log),日志的记录是远比刷库快的,在mysql重启时会检查redo log,检查刷库失败的数据
3. 脏读、不可重复读、幻读
这三种状态,都是在数据库在执行事务中的情况。
脏读 : 事务之间可以读到其他事务没提交的数据
不可重复读 : 对相同的数据进行读取,可以读取到其他事务提交的数据
幻读 : 在一个范围的查询或修改时,其他事务进行删除或修改,但是发现不了数据已经被删除
4. 数据库隔离级别
隔离级别解决 脏读、不可重复读、幻读
级别越高性能越低
| 级别 | 缺点 | 简介 |
|---|---|---|
| 读未提交 | 脏读、不可重复读、幻读 | 会读取到其他事务未提交的数据 |
| 读已提交 | 不可重复读、幻读 | oracle默认,只能读取到别人提交的 |
| 可重复读 | 幻读 | mysql默认,在重复读取数据,结果集保持一致 |
| 串行化 | - | 不存在事务问题,都串行了 |
5. 三大范式
| 级别 | 关键词 | 简介 |
|---|---|---|
| 第一范式 | 字段原子性 | 比如手机字段,有些人有两个手机号,但不可将两个手机号放在一个字段里,比如，隔开 |
| 第二范式 | 依赖候选键 | 如OrderID, ProductID, ProductName, PersonName可以看到，OrderlD和ProductID是联合主键，但是ProductName是依赖于ProductID的,只依赖了部分主键，没有依赖全部主键。需要拆分成三个表: |
| 第三范式 | 依赖主键 | 如OrderID, ProductID, ProductName， OrderID是主键， 但是ProductlD依赖了OrderID,而ProductName依赖了 ProductID,等于说是间接依赖了OrderID,所以需要拆分为两个表: OrderID,ProductID和ProductID, Produ tName |
6. 范式的优缺点
范式的优点：
1）范式化的数据库更新起来更加快；
2）范式化之后，只有很少的重复数据，只需要修改更少的数据；
3）范式化的表更小，可以在内存中执行；
4）很少的冗余数据，在查询的时候需要更少的distinct或者group by语句。
范式的缺点：
5）范式化的表，在查询的时候经常需要很多的关联，因为单独一个表内不存在冗余和重复数据。这导致，稍微复杂一些的查询语句在查询范式的schema上都可能需要较多次的关联。这会增加让查询的代价，也可能使一些索引策略无效。因为范式化将列存放在不同的表中，而这些列在一个表中本可以属于同一个索引。
反范式的优点：
1）可以避免关联，因为所有的数据几乎都可以在一张表上显示；
2）可以设计有效的索引；
反范式的缺点：
3）表格内的冗余较多，删除数据时候会造成表有些有用的信息丢失。
7. 内连接,外连接
内连接 : 相连的两张表都有数据时才能查出结果
外连接 : 可分为左连接,右链接,相连的两张表,只需要指定的一边表有数据即可查出数据集
8. 索引类型
| 结构 | 优点 | 简介 |
|---|---|---|
| B- | 相比二叉树,树更低 | 一个节点可以保存两个值,但节点是保存数据的,orderby就慢 |
| B+ | 查询稳定,遍历效率高,io次数少 | 只在叶子节点存储数据,始终维持在三层,减少io次数,叶子之间维持链接加快全表扫描 |
| Hash | 定值查询快 | =,in(),<>效率非常快,但是在做范围,排序就远比b+慢,不支持匹配查找(模糊匹配),重复键查询慢(hash碰撞) |
| 全文 | 查询文本值数据快 | 在有使用like等语句作为条件时,使用全文索引会快很多 |
9. B+和Hash索引的区别
其实做到Hash特性的学者大概也能想出来区别了
B+ : 范围、排序、部分组合索引(只匹配最左索引)、定值(=,<>,in)查询没Hash快
Hash : 定值查询快、有大量hash碰撞后变慢,组合索引需要满足全部字段
10. 为什么使用B+树
说B+的优点,同时与Hash对比,结语B+在各场景都没有明显短板
11. 说一下EXPLAIN
查看Sql的执行计划的命令,通常用来检查sql性能,
具体可以看这篇文章 https://blog.csdn.net/tianya9704/article/details/80067860
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 查询的序号 | 查询类型 | 引用的表 | 匹配到的分区信息 | 联结类型 | 可以使用的索引 | 最终使用的索引 | 索引中使用的字节数 | 和索引进行比较的列 | 需要被检索的大致行数 | 按表条件过滤的行百分比 | 额外信息 |
12. 聚簇索引与非聚簇索引
- 聚簇索引一个表只能有一个,决定了表数据的存储顺序,通常是数据库主键,如果没设置数据库会生成一列rowid作为聚簇索引,key是主键,value是其他列数据
- 除了主键外 其他的字段创建出的索引都是非聚簇索引, 非聚簇索引的key是表的value值,而value存的是主键
- 非聚簇索引的value存的是主键
- 在innoDB中,我们命中了非聚簇索引,还需要通过主键去聚簇表查询具体数据,这个过程称作回表
- 因为非聚簇索引的key就是聚簇索引的value,所以如果查询的字段都在非聚簇的key中,那就没必要通过value中的主键到聚簇索引中查询数据,这个特性叫覆盖索引,对应的减少了一个io操作有利于提高性能,特别是在排序字段不被索引覆盖
- 注意: MyISAM无论主键索弓|还是二级索弓|都是非聚簇索引，而InnoDB的主键索弓 |是聚簇索引，二级索引是非聚簇索引。我们自己建
的索弓|基本都是非聚簇索引
13. 索引覆盖
因为非聚簇索引的key就保存有数据字段,如果查询的字段都在key中,就没必要再通过value值回到聚簇索引中查询数据。该特性有利于提高性能,被称为索引覆盖
14. 组合索引
多个字段联合设置为索引称为组合索引
当我们where是需要用到多列条件,那我们就可以考虑使用组合索引
该类型的索引只需要从左往右使用索引列即可,无需全部匹配全部组合索引列(Hash索引需要全匹配)
当匹配组合索引时,如果遇到<>,between,like会导致后续的匹配列失效
当匹配组合索引时,需要从左往右依次匹配,进行跳列匹配的条件不生效
15. 前缀索引
说白了就是只选择字段值的前几位值作为索引
因为可能我们索引的字段非常长,这既占内存空间,也不利于维护。所以我们就想，如果只把很长字段的前面的公共部分作为一个索引，
就会产生超级加倍的效果。但是，我们需要注意，order by不支持前缀索引
16. 索引下推
索引下推（index condition pushdown ）简称ICP
MySql 5.6后提供的一种优化查询
SELECT * from user where age=20 and name like ‘陈%’
age是索引 name不是
在5.6前 : 在索引中命中了age查询一批数据,存储引擎会将age=20的给mysql服务器,然后mysql再去筛选这批数据中name like '陈%'的数据
在5.6以后 : 它会直接将age和name的条件直接给存储引擎让它帮你直接筛选
优点 索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数
17. 索引失效
首先，我们可以说通过explain去排查一个慢查询，进而找到它的索引*(参看第五题)*，当创建索引却不走索引时，我们就需要考虑到优化器的问题。
在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案。这个成本最低的方案就是所谓的执行计划。 优化过程大致如下：
根据搜索条件，找出所有可能使用的索引
计算全表扫描的代价
计算使用不同索引执行查询的代价
对比各种执行方案的代价，找出成本最低的那一个
那么，有哪几种情况明明设了索引却不走索引呢？
假设索引为(a,b,c)
ASC和DESC索引混合使用的排序：select * from tab order by a, b desc limit 10;
违背最左前缀原则：select * from tab where b = ‘1’;
WHERE⼦句中出现非排序使⽤到的索引列：select * from tab d = ‘1’ order by a limit 10;
排序列包含非同⼀个索引的列：select * from tab order by a, d limit 10;
WHERE子句中出现计算：select * from tab where a * 4 = 2;
WHERE子句中出现null值：select * from tab where a = null;
WHERE子句中使用!=或<>操作符：select * from tab where a != 1;
18. 唯一索引和普通索引的区别
唯一索引和普通索引在读取的时候效率基本差不多，普通索引差了一点点。主要是判断和特殊情况下的一-次IO
写入的时候，普通索引可以利用change buffer, 适合写多读少，比唯一 索引要快
以业务为前提，如果要求唯- -,就要选择唯一索引。 如果已经保证列的唯一，我们尽量 选择普通索引，然后把change buffer 调大
二、MySql优化
1. 查询优化
超过500条创建索引,在索引字段上查询
使用连接代替子查询
尽用IN代替0R, OR的效率是n级别，IN 的效率是log(n)级别，IN 的个数建议控制在200以内
能用BETWEEN不用IN,B+树的特性这种取范围值是比较快的
尽用LIMIT,同时尽量不用count *,inndo是不维护数量的会扫描表数量
切分一个连接时间很长的查询，或返回数据量很大的查询
分解关联查询，在应用层做关联，可以更容易对数据库进行拆分,减少锁的竞争，减少冗余记录的查询
2. 大数据量优化
1.建表优化
此处考察如何建立索引
字段类型尽量精确，尽量小，能用int不要用bigint
尽量不要用null，声明not null，如果是null用0代替
尽量使用TIMESTAMP而非DATETIME
用整型来存ip
注意反范式和范式的优化
2.查询优化
索引命中问题和索引失效问题
3.加缓存
mysql一两千级并发
redis五六万级并发
4.横向纵向拆分
横向拆分 : 分表避免单表数据量过大,mysql千万级就明显性能降低了
纵向拆分 : 单行数据量过大会导致无用字段我网络开销,因为mysql是page为单位读取,如果单行数据量过大会导致page存储数量降低,导致需要读取的page变多缓存变大
5.分表分库
6.使用服务业务场景的数据库
ES或者MoungoDb右或者HBase
3. MySql存储引擎’
主要三个: Innodb(事务),MySIAM(查询),Memory(内存)
4. Mysql锁
乐观,悲观锁,行锁,间隙锁,表锁,读锁,写锁 笔者对锁有一定了解了
5. CAS和MVCC
6. redo log和binlog的区别
- redo log是InnoDB弓|擎特有的; binlog是MySQL 的Server层实现的，所有弓|擎都可以使用
- redo log是物理日志，记录的是‘在某个数据页上做了什么修改”; binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这
-行的c字段加1” - redo log是循环写的，空间固定会用完; binlog是可以追加写入的。“追加写’是指binlog文件写到-定大小后会切换到下一个，并不会覆盖以前的日志
- binlog可以作为恢复数据使用，主从复制搭建，redo log作为异常宕机或者介质故障后的数据恢复使用
1768
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
