day08-存储引擎
一、回顾
1.1 索引
(1) 聚集索引构建B树的过程
(2) 辅助索引构建B树的过程
(3) 辅助索引细分
单列辅助索引
多列辅助索引*****
唯一索引
前缀索引
(4) 索引树高度问题
| 因素 | 解决方法 |
|---|---|
| 数据量级 | 分库、分表、分布式 |
| 数据类型 | 变长：char 定长：varchar |
| 索引列值长度 | 前缀索引 |
| enum | 优化索引高度，能用则用 |
(5) explain(desc)重点回顾
你在你们公司做过哪些优化（MySQL）?
- 我在公司，主要是配合开发和业务人员，进行SQL优化和索引优化这块的工作
- 我主要针对索引优化这块儿，做的工作比较多
- 我一般都是配合两个数据库工具进行配优化
- 第一个就是slowlog（自动收集慢语句），第二个工具是explain
- 我通过之前做过的小案例来简单说明下我的优化思路
- explain(desc)使用场景（面试题）
1.2 存储引擎
(1) InnoDB存储引擎核心特性
1. 事务（Transaction）
2. MVCC（Multi‐Version Concurrency Control）多版本并发控制
3. 行级锁（Row‐level Lock）
4. ACSR（Auto Crash Safey Recovery）自动故障安全恢复
5. 支持热备份（Hot Backup）
6. 复制 Replication：Group Commit，GTID（Global Transaction ID），多线程（Multi‐
Threads‐SQL）
(2) 表空间管理
alter table t1 discard tablespace;
alter table t1 import tablespace;
(3) 事务特性
ACID
A：原子性
C：一致性
I：隔离性
D：持久行
(4) Redo在ACID中的作用
重做日志，前滚日志。
主要是完成ACID中的D(持久行)的特性，对AC也有一定的作用
存什么？
内存数据页变化的过程
(5) Undo在ACID中的作用
回滚日志，撤销日志。
主要完成的ACID中的A，对CI也有一定的作用
(6) Redo-CSR前滚
(7) Undo-CSR回滚
二、锁介绍
2.1 介绍
锁定的意思，提供的是ACID中I的功能，需要配合Undo+隔离级别一起来实现
2.2 InnoDB锁级别
行级锁
工作中(优化章节)：需要排除锁的争用、锁等待、死锁
2.3 扩展内容
Next LOCK
GAP LOCK
悲观锁：
乐观锁：
三、事务的隔离级别
影响到数据的读取，默认的级别是RR级别
transaction_isolition隔离级别(参数)
负责的是，MVCC，读一致性问题
RU：读未提交，可脏读，一般部议述出现
RC：读已提交，可能出现幻读，可以防止脏读
RR：可重复度，功能是防止“幻读”现象，利用的是undo的快照技术+GAP（间隙锁）+Next LOCK（下键锁）
SR：可串行化，可以防止锁死，但是并发事务性能较差
RR级别：解决了不可重复读问题+幻读现象
不可重复读问题有undo的快照技术来解决
幻读现象是有：MVCC+GIS+Next-LOCK
补充：在RC级别下，可以减轻GAP+Next-LOCK锁的问题，但是会出现幻读现象，一般是为了读一致性会在正常select后添加for uqdate语句，但是,请记住执行完一定要commit 否则容易出现所等待比较严重。
四、InnoDB核心参数
4.1 双一标准之一：(*****)innodb_flush_log_at_trx_commit=1
1.作用：
控制了redo buffer 刷新策略，是一个安全参数，是在5.6版本以上默认的参数
2.参数功能：
1：每次事务提交，都会刷新redo到磁盘（redo buffer--每事务--->os buffer--每事务--磁盘）
0：表示当事务提交时，不立即做日志写入操作（redo buffer--每秒-->os buffer--每秒--磁盘）
2：每次事务提交引起写入文件系统缓存（redo buffer--每事务--->os buffer--每秒--磁盘）
4.2 Innodb_flush_method=(O_DIRECT,fdatasync)
1. 作用：
控制了redo buffer和data buffer刷写磁盘方式
2. 最大安全模式：
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
3. 最大性能模式：
innodb_flush_log_at_trx_commit=0
innodb_flush_method=fsync
4.3 关于redo设置
innodb_log_buffer_size=128M 业务系统CPU压力有关
innodb_log_file_size=256 一般是1~2倍
innodb_log_files_in_group=3 一般是3~4组
4.4 innodb_buffer_pool_size
生产一般调整为物理内存的50%~80%左右（你的系统中只有一个mysql实例）
面试题：
面试题简易回答：
请你简述InnoDB聚集索引和辅助索引的区别*****
1.一张表只能有一个聚集索引，最好是自增的数据列
2.聚集索引叶子节点是有序存储的整行数据
3.辅助索引一张表可以有多个
4.辅助索引叶子节点存储的是索引列的有序值+此列值的主键值
想要详细回答，整个两个索引构建的过程和配合使用买最好是画图说明
都是等值查询，建索引有什么好的建议？
1.考虑联合索引
2.排列问题？唯一值多放在
aler table t1 add idx(c,b,a)
select count(discard a) from t1;
为什么？
优化器会自动按照索引建立的顺序，自动排序 where条件见的顺序，前提是都是等值或者in的
如果出现了其他方式条件，比如：> 、<或者group by、order by
怎么判断联合索引将来的优化效果
1. 看执行计划
2. key_len
你在你们公司做过哪些优化（MySQL）?
1. 我在公司，主要是配合开发和业务人员，进行SQL优化和索引优化这块的工作
2. 我主要针对索引优化这块儿，做的工作比较多
3. 我一般都是配合两个数据库工具进行配优化
4. 第一个就是slowlog（自动收集慢语句），第二个工具是explain
5. 我通过之前做过的小案例来简单说明下我的优化思路
6. explain(desc)使用场景（面试题）
题目意思:我们公司业务慢,请你从数据库的角度分析原因
1.mysql出现性能问题,我总结有两种情况:
(1) 应急性的慢：突然夯住
应急情况:数据库hang(卡了,资源耗尽)
处理过程:
1.show processlist; 获取到导致数据库hang的语句
2. explain 分析SQL的执行计划,有没有走索引,索引的类型情况
3. 建索引,改语句
(2)一段时间慢(持续性的):
1.记录慢日志slowlog,分析slowlog
2.explain 分析SQL的执行计划,有没有走索引,索引的类型情况
3.建索引,改语句
7. 另外，我还做一部分存储引擎方面的优化。
我们有个业务是插入类的操作比较多，做过了一个存储引擎方面的优化。
将innoDB替换成了tokuDB
主要说说为什么会使用tokudb
