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、为什么要使用自增主键？
**页分裂：**如果使用非自增主键，出现某个数据页存满后，后续插入的数据放入到已满的数据页时，可能导致大量的数据挪动到下一页，产生较大的性能开销。
**页合并：**由于部分页删除了数据，导致数据页的空间利用率降低 ,就会做页合并操作，合并过程就是页分裂的逆过程。
总结：
自增主键保证了数据有序插入，避免了页分裂。同时由于数据页大小固定，字段大小越小数据页中存储的数据越多，性能越好。
388
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
