001、数据库的三大范式?
- 原子不可分割
- 在第一范式的基础上,非主键完全依赖于主键
- 在第二范式的基础上,消除传递依赖
002、索引有哪些优缺点?
索引的优点:
- 提高数据检索的效率
索引的缺点:
- 时间方面:
a. 创建索引和维护索引需要耗费额外的时间
b. 当表中的数据经常发生修改时,需要额外的时间去维护索引树 - 空间方面:
a. 索引需要额外的物理空间
003、索引有哪几种类型?
- 主键索引:数据列不允许重复,不允许为null,一个表只有一个主键
- 唯一索引:数据列不允许重复,允许一个值为null,一个表可以有多个唯一索引
a. 可以通过alter table table_name add unique(column);
创建唯一索引
b. 可以通过alter table table_nameA_nameB add unique index index_name(column,column);
- 普通索引:基本的索引类型,没有唯一性约束,允许NULL值,一个表允许创建多个列普通索引
- 全文索引:是目前搜索引擎使用的一种关键技术,
MyISAM 存储引擎才有全文索引
-- 创建索引
create [unique] index index_name on tb_name(column);
alter table tb_name add [unique] / [fulltext] index index_name(column)
-- 查看索引
show index from tb_name;
-- 删除索引
drop index index_name on tb_name;
004、MySQL索引算法有哪些?
- BTree 算法
- hash 算法
Btree 可以用在 =,<=,>= 和between and 这些比较操作符上,还可以用于like操作。
hash 只能用于等值查询
005、索引的设计原则?
- 经常查询的列,作为where条件中的列
- 经常需要分组、排序的列
频繁更新的字段不适合建立索引
离散度底的字段不适合建立索引
对于text、image类型的字段不适合建立索引
- 最左前缀原则
- 非空字段,应该指定为not null
- 不要过度索引。索引的创建和维护需要额外的磁盘空间,同时降低写的性能。
006、什么是MVCC?
多版本并发控制
MVCC 是通过在每行记录后面保存两个隐藏的列来实现的。一个是保存了行的创建时间,一个是保存了行的过期时间(删除时间)。存储的并不是实际的时间值,而是系统版本号
。每开始一个新的事务,系统版本号都会递增。原理是:查找创建版本小于或等于当前事务版本号。删除版本为空或者大于当前事务版本号
007、锁的类型有哪些?
- 共享锁(读锁 S)
- 排他锁(写锁 X)
读锁是共享的,可以通过lock in share mode 实现,这时候只能读不写。
写锁是排他的,它会阻塞其它的写锁和读锁。从颗粒度来区分,可以分为表锁和行锁两种。
表锁会锁定整张表并且阻塞其它用户对该表的所有读写操作。比如alter 修改表结构的时候会锁表
行锁又可以分为乐观锁和悲观锁。
悲观锁可以通过for update 实现,乐观锁通过版本号实现。
008、MySQL 大数据量分页为什么慢?
案例:
select * from user where sex = 1 limit 1000000,10;
当前sex 列是索引列。MySQL会走 sex 索引树,命中sex=1的数据。
分析:
由于非聚簇索引中存储的是主键id 的值,且查询语句要求查询所有列
。所以这里会发生一个回表
的情况。在命中 sex 所以树种值为1的数据后,拿着它叶子结点上的(主键id)值去主键索引树上查询这一行其它列(name,sex) 的值,最后返回到结果集中。这样一行数据就查询成功了。
最后这句SQL 要求 limit 1000000,10。也就是查询1000001到1000010的数据。但是MySQL 会查询前1000010行,然后将前1000000 行抛弃。
最后结果集中就只剩下了第1000001到1000010行,执行结束。
小结:
在上述执行过程中,造成limit 大偏移量执行时间变久的原因有:
- limit a,b 会查询前 a+b条数据,然后丢弃前a条数据。
MySQL 数据库的查询优化器是才用了基于代价的方式,而查询代价的估算是基于CPU代价
和 IO代价
。如果MySQL在查询代价估算中,认为全表扫描方式比走索引扫描的方式效率更高的话,就会放弃索引,直接走全表扫描。
009、MySQL 大数据分页如何优化?
现有User表,主键为id,索引为sex
优化方案如下:
- 覆盖索引:
使用覆盖索引返回索引字段避免回表
,预计提升性能10倍+。
-- 示例:
select * from user where id >= (select id,sex from user where sex = 1 limit 1000000,10);
- 连续id优化:
id连续
的情况下通过记录上次分页的最大id。
-- 示例:
select * from user where sex = 1 and id >= #{上次分页id} order by id desc limit 10;
\-- 缺点:
需要id连续,需要按id排序、
010、说说一条查询SQL 语句的执行过程?
MySQL组件:客户端(client)、Server端(连接器、查询缓存、分析器、优化器、执行器)、存储引擎(InnoDB、MyISAM、Memory)等。
执行流程:
- 客户端: MySQL-client,连接服务端后传输各项命令
- 连接器: 与客户端建立连接
- 查询缓存: 连接建立后,以SQL语句为key 执行查询缓存,命中则返回
- 分析器: 没有命中查询缓存,进行SQL语法分析和解析
- 优化器: SQL语句分析无误后制定执行方案,比如
选择什么索引
等 - 执行器: 调用存储引擎读写接口执行语句和执行前的权限校验等
- 存储引擎: 存储引擎,提供读写接口
011、说说常见表设计方法?
- 将字段很多的表拆分成多个表。
原因:对于字段多的表,如果有些字段的使用频率很低,可以将这些字段分离出来形成新的表。当一个表的数据量很大时,会由于使用低的字段存在而变慢。 - 增加中间表
原因:建立中间表可以提高查询效率 - 增加冗余字段
原因:合理的冗余字段可以提高查询速度
012、说说MySQL 的WAL 是什么?
WAL: write ahead logging,预写式日志,先写日志再写磁盘
。innodb 引擎通过redo log实现了WAL 并具备crash-safe能力
- redo log:存储引擎层日志(innodb),redo log 是固定大小循环写。
- crash-safe:数据库即使发生了重启,提交的记录也不会丢失。
013、bin log 和 redo log 的区别?
- bin log:
1. Server 层,所有存储引擎都可用,用于归档
2. 逻辑日志,记录的原始逻辑,如给id = 1 的a字段 +1
3. 追加写,每个log 文件写完了一定大小切换到下一个,不会覆盖原有的log文件 - redo log:
1. 存储引擎层,Innodb 特有。用于实现WAL和Crash-safe能力
2. 物理日志,记录的是某个数据页做了什么修改
3. 循环写,指定log大小文件写完后会将老数据刷盘后重新写
014、update语句内部执行流程?
实例语句:update user set sex = 1 where id = 1
- **执行器:**通过调用存储引擎获取id = 1 的数据。
- 存储引擎: 判断id=1这一行数据是否在内存中?如果在则直接返回,如果不在从磁盘读取数据到内存中并返回。
- **执行器:**得到存储引擎返回的数据,修改sex = 1 得到新数据,调用存储引擎接口写入新数据。
- **存储引擎:**将新数据更新到内存中,同时更新到redo log且
状态为prepare
,表示随时可以提交任务。 - **执行器:**生成binlog 日志,并将binlog 写入磁盘后调用存储引擎提交事务接口。
- **存储引擎:**将redo log
状态改为commit
,事务完成
015、程序员删库跑路了如何恢复?
- 备份机制:
1. 保留最近半个月的增量binlog
2. 定期全量备份binlog(一天或者一周,频率大于最小增量保留时长) - 恢复步骤:
1. 找到最近一次全量binlog 备份恢复到临时库
2. 从增量binlog中找到全量备份binlog时间点开始误删操作前的那个时刻进行重做
3. 临时库替换为正式库
016、为什么redo log 需要2阶段提交?
示例语句:update user set sex = 1 where id = 1
假设没有2阶段提交,故障场景举例:
redo log 写入,bin log 未写入: 此时MySQL异常重启能根据redi log 恢复sex = 1的数据,但bin log 没有记录,后续使用binlog 恢复临时库会出现数据丢失,导致状态不一致。
**binlog 写入,redo log 未写入:**此时MySQL异常重启能根据binlog 恢复sex = 1 的数据,但是redo log中没有记录修改的数据,出现数据丢失,导致状态不一致。
017、说说redo log 日志的刷盘机制?
- 异步刷盘(异步写入OS Cache):提交事务不刷盘,log Thread 每隔1秒钟将log buffer 写入文件(os cache)并通知OS做Flush 操作写入磁盘,当MySQL 宕机 或者 OS 宕机时
最多丢失1秒数据
。 - 同步刷盘(默认):提交事务即触发log buffer写入文件,并通知OS做flush 操作写入磁盘,
不会丢失数据
。 - 异步刷盘(同步写入OS Cache):提交事务时将log buffer写入文件(OS Cache),不通知OS flush 操作,OS flush 操作由操作系统自行决定,
当MySQL Crash时不会丢失数据,当OS Crash 时可能导致部分数据丢失。
redo log 配置参数:innodb_flush_log_at_trx_commit(取0,1,2 对应上述三种场景)
018、可重复隔离级别实现原理?
- MVVC 多版本并发控制:
同一条记录在系统中存在多个版本
,多版本通过试图+回滚日志(undo log)实现
- 回滚日志(undo log):
每次更新数据的时候都会记录一条回滚操作
,并将最新数据指向上个版本,形成回滚版本链,通过回滚操作可以得到上个版本值。 - 视图(read-view):
在可重复读隔离级别下不同的事务查询数据均会得到一个read view,在同一个事务内查询多次都是使用同一个read view
。所以得到的数据是一致的,不同的事务read view得到的版本也有所不同,通过多版本+试图的方式保证修改/查询不冲突。
温馨提示:
因为回滚日志会占用大量存储空间,且MySQL会判断当前没有事务需要回滚日志才会删除回滚日志,所以我们应当避免使用长事务
(一个事务里面包含大量操作),让空间尽快释放,长事务除了会影响回滚日志以外,还会造成长期占用锁资源
,影响并发性能,拖垮数据库。
019、如何避免长事务对业务的影响?
- 应用层面:
1. 设置合理的autocommit 参数(autocommit= 1,避免所有语句都手动提交)
2. 设置合理的显示事务(begin 或 start transaction,避免只读事务)
3. 设置合理的事务执行时间 - 数据库层面:
1. 监控information_schema.Innodb_trx表,超过长事务阈值就kill 掉
2. 通过设置innodb_undo_tablespaces>=2,使undo log 存储与系统表空间分离,存储在独立表空间,利于清理。
020、为什么要使用自增主键?
**页分裂:**如果使用非自增主键,出现某个数据页存满后
,后续插入的数据
放入到已满的数据页时,可能导致大量的数据挪动
到下一页,产生较大的性能开销。
**页合并:**由于部分页删除了数据
,导致数据页的空间利用率降低
,就会做页合并操作,合并过程就是页分裂的逆过程。
总结:
自增主键保证了数据有序插入,避免了页分裂。同时由于数据页大小固定,字段大小越小数据页中存储的数据越多,性能越好。