MYSQL
1. 事务的四大特性 ACID
-
原子性 Atomicity
我们对数据库的一系列操作要么都成功 要么都失败，如果转账场景，一个账户增加 另一个失败，要么都成功或者都失败
-
一致性 Consistent
一种是指数据库的完整性月数没有被破坏，比如主键唯一，事务的合法等，另一种是业务的一致性，比如转账场景，A账户余额减少1000，B账户余额增加500，这时候虽然两个操作都成功了，符合了原子性协议，但没有符合一致性
-
隔离性 Isolation
数据库中有很多事务同时去访问同一个表或者同一行数据，必然会产生一些并发的影响，这时候就要定义不同的事务让他们之间互不干扰，也是通过这种方式保证数据的一致性
-
持久性 Durable
对数据库的任意操作，只要成功了就应该是持久的
数据库的持久性是如何实现的？
通过redo log 和 double write双写缓冲来实现的，我们操作数据的时候，会先写道内存的buffer pool中，同时记录redo log，如果在刷盘之前出现异常，再重启后就可以读取redo log的内容，写入磁盘，保证数据的持久性，当然恢复成功的前提是数据页本身没有被破坏，是完整的 这个是通过双写缓冲来保证的
2. 性能优化
-
通过开启slow query log 可以把慢查询日志记录下来，并用mysqlDumpSlow分析并导出对应需要的格式
mysqlDumpSlow -s t -t 20 -g 'select' /usr/local/mysql/data/slow-query.log
-
通过开启select @@profile 开关 可以查看sql执行所用时间 cpu占用信息等情况，
select * from information_schema.profiling 表中存储了所有信息
-
通过show processlist 或者select * from information_schema.processlist 查看所有正在执行的sql线程，看看是否有死锁线程或者等待队列已满等情况
3. EXPLAIN
-
id 笛卡尔积，2 * 3 * 4 = 6 * 4 会优于 3 * 4 * 2 = 12 * 2 因为中间产生的笛卡尔积临时表空间更少
如果id大小不一样 查询顺序是从大到小进行，如果id大小是一样的，查询顺序是自上而下
-
select_type
- PRIMARY
- SUBQUERY
- DERIVED
- UNNION ALL
-
table
-
type
- System 系统只有一行数据
- const 只查询到一行数据
- eq_ref 唯一性索引 是对于多表join查询，并且对于前表的每一条结果刚好能匹配到后表的每一条结果
- ref 非唯一性索引，
- range 范围查找
- index full index scan 查询的字段是在索引上的字段
- all
- null
-
possible_keys 可能使用到的索引
-
key 实际使用到的索引，如果查询的字段存在于某个索引上，虽然这个索引不符合最左匹配原则，但实际还是会用到索引，此时 possible_keys为null key不为null，
-
rows 扫描到的大概行数
-
filtered 过滤的百分比 越接近100越好
-
extra 附加条件
-
using where 如果存储引擎层返回的数据不是我们最终需要的数据，需要经过server端过滤的时候
需要索引查询到的数据回表查询，
-
using index 只是用了索引数据，不需要回表去查询数据
-
using index condition 索引下推 查询的条件语句经过索引进一步过滤之后 再返回给server端
-
using filesort 没有使用到对应的索引排序， 用了额外的排序规则
-
using temporary 临时表
-
4. 一个SQL语句的执行过程
连接--》分析器（词法分析器（把一个sql语句拆分成单个的单词去校验）和语法分析器（按照语法树去分析））--》预处理器--》优化器（query optimizer，会生成所有可能的执行计划，然后基于开销时间获取一个最佳的执行计划） --》执行计划（可以通过explain查看）-》执行引擎
5. mysql 存储文件
- innodb：
- .frm文件 表结构文件
- ibd文件索引文件
- myisam
- frm文件 表结构文件
- myi 索引文件
- myd 数据文件
6. 存储引擎
-
MYISAM
-
支持表级别的锁，不支持事务 ，所以插入和更显会锁表
-
拥有较高的插入和查询速度，适合读多写少的情况
-
存储了表的count行数，所以count速度更快
怎么快速向数据库插入100万条数据了，可以先用MYISAM引擎插入数据，然后在修改为INNODB
-
-
INNODB
- 支持事务，支持外键，
- 支持航级别的锁和表级别的锁
- 支持读写并发，写不阻塞读（MVCC）
- 特殊的索引存放 可以减少IO
7. INNODB的内存结构和磁盘结构
1. 内存结构（Buffer Pool） 默认128M 可以缓存索引页和数据页
-
Buffer Pool 内存的缓冲池数据写满了怎么办？Innodb使用LRU算法，有一个Buffer Pool List分成Old （3/8）和young（5/8）代，如果查询的数据在缓冲池中已存在，则提升到连表头，如果不存在则放到中间，后面的数据进行末尾淘汰
-
Change Buffer 写缓冲，如果要写入的数据不是唯一索引，也就是说不存在数据重复的情况下，就不需要先从磁盘加载判断唯一性，直接先在缓冲池中更改，从而提升增删改的执行速度，5.5之前的版本叫Insert Buffer 现在可以支持更新和删除，所以更改为Change Buffer
然后把change Buffer的数据merge，在访问这个数据页的时候merge，通过后台线程、或者数据库发生 shutdown的时候，以及 redo log 写满时触发
如果数据库的大部分索引都是非唯一索引，并且业务是写多读少，不会在写后立刻读取数据，就可以调大change buffer的值，默认占用 Buffer Pool的25%空间
-
Adaptive Hash index
-
（redo）log buffer 为防止Change Buffer里的数据未同步到磁盘发生宕机，innodb 把所有的写操作都记录在 redo log 中，redo log在表空间中 默认有2个文件，每个文件默认48M，默认都是先写日志 在写磁盘，因为写入磁盘是随机IO，写入日志文件是顺序IO，所以速度更快。默认是每次事务提交的时候 都会把log buffer 写入到磁盘。
redo log的文件大小是固定的,默认16M，里边有两个指针，一个写指针，一个check指针，如果两个指针重叠，说明文件满了，这个时候需要同步数据到磁盘中
2. 磁盘结构
- 系统表空间 system tablespace
- 独占表空间
- segment 段 数据段 索引段
- extent 区（簇） 大小是1M 包括64个页
- page 页 每个页大小是16K
- 通用表空间
8. 日志文件
- redo log 记录修改后的数据，用来更新数据日志 是在存储引擎层完成的
- undo log 记录之前的数据，用来恢复数据
- bin log 记录逻辑日志，只记录操作，不记录数据，主要用来做主从复制和数据恢复 是在server层完成的
一个更新语句的执行流程是：先经过server层，然后交给存储引擎层执行更新，将更新结果写入到Buffer Pool，记录redo log的记录并且设置状态为prepare的，然后返回给server层，server层写入bin log日志，此时再通知存储引擎层更改redo log的状态为commit，保持数据一致性
Innodb 辅助索引只存储索引和主键值 ，主键索引即聚簇索引是和数据放在一起的
Myisam 索引和数据是分开存储的
mysql innodb如果没有主键：第一个不为空唯一索引 --》row_id
9. 事务的几种数据特性 / 事务并发带来的几个问题
-
脏读
A事务读到了B事务更改后但未提交的值，导致两次读取到的数据不一样
-
不可重复读
A事务读到了B事务更改（特指修改或者删除）后提交的值，导致两次读取的数据不一样，
-
幻读
A事务读到了B事务更改（特指新增）后提交的值，导致两次读取的数据不一样
10. 事务的4个隔离级别
-
RU （Read Uncommitted）未提交读
-
RC （Read Committed） 已提交读
通过MVCC实现读一致性 通过Record lock 实现写一致性，存在幻读问题
-
RR （Repeatable Read）可重复读 同一个事务里多次读取同样的数据结果是一样的，解决了不可重复读问题，并在INNODB中解决了幻读的问题，通过临键锁，
通过MVCC实现读一致性，通过Record Lock，Gap Lock， Next-Key Lock实现写一致性
-
Serializable 串行化 所有的事务都是串行执行的，解决了所有问题，但效率降低
11. Mysql默认添加的几个字段
- DB_TRX_ID 事务id
- DB_ROLL_PTR 回滚id 指向undo log
- DB_ROW_ID 行id
12. mysql锁
包括有排它锁 即Exclusive Lock 即X锁 写锁 和 共享锁 Share Mode Lock 即S锁 读锁
-
record lock 记录锁
如果查询的数据正好是唯一索引或者主键索引的数据，就是记录锁，只锁当前这一行数据
-
gap lock 间隙锁
间隙所是记录锁的n+1个，是指记录不错的区间锁，左开右开的，如果查询的记录不存在，则用间隙锁
-
Next-key Lock 临键锁 默认
临键锁是INNODB默认的行锁，并通过此解决了在RR事务级别下的数据幻读问题，如果查询的是区间数据并且命中了某一个索引的值，则用间隙锁，锁住当前记录的前一个间隙锁和后一个间隙锁。
13. mysql几种日志文件格式及作用
- redo log 重做日志 。是存储引擎层记录的，在数据更新到Buffer Pool之后记录在redo log中，用于记录数据的更新值
- undo log 回滚日志，也是存储引擎层记录的，用于记录数据更新前的值，
- bin log server层记录的，只记录增删改的这些sql日志，并不记录实际的值，用于主从同步和数据恢复的
14. MySQL多版本控制MVCC
Multi Version Concurrency， 基于快照实现， 通过数据库隐藏的字段 事务id和 回滚指针来完成，回滚指针指向undo log里的数据
15. 如何解决数据库读一致性的问题
-
LBCC （Lock Based Conconrrency Control）
在读取数据前，对其加锁，阻止其他事务对数据进行修改
-
MVCC（ Muti Version Concurrency Control ）
生成一个数据请求时间点的快照，并用这个快照来提供一定级别的一致性读取
16. 二叉搜索树 二叉平衡树（AVL）B树 B+树
-
二叉搜索树：左子树小于根节点 右子树大于根节点 按照中序排列的话是一个有序的队列，缺点是树的深度太大
-
二叉平衡树：基于二叉搜索树的一个平衡树 树的深度绝对值差不超过1
-
B树 ：多路平衡树，度是关键字n的n+1个，叶子节点都存储数据和索引，度数多
-
B+树：非叶子节点只存储关键字，不存储索引和值，所以可以存储更多关键字，叶子节点中有一个指向下一个节点的指针，排序和范围查询更快，而且树的深度是固定的，所以IO次数是固定的
17. 死锁的四大必要条件(举例说明，如何发生一个死锁)
- 互斥条件
- 持有且等待
- 不可剥夺
- 循环依赖
18. 哪些情况不走索引
1、查询条件中使用了计算 比如 age+=20
2、查询条件中使用了函数
3、第三是like %
4、or操作
扫一扫
628
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
