Mysql部分整理
一.基础架构-一条SQL查询语句是怎么执行的？
1.Mysql的逻辑架构图
大体分为Server层和存储引擎层
1）Server层
包括连接器，查询缓存，分析器，优化器，执行器等，涵盖Mysql的大多数核心服务功能，所有跨存储引擎的功能都在这一层实现，比如存储过程，触发器，视图等
2）存储引擎层
负责数据的存储和提取。架构模式是插件式的，支持InnoDB,MyISAM,Memory等多个存储引擎(执行create table建表的时候，默认使用的就是InnoDB)
- 不同的存储引擎共用一个Server层
有时候MYSQL的内存涨的特别快，解决方法有：
- 定期断开长连接。使用一段时间之后断开连接，之后要查询的时候重连
- 如果是MySQL 5.7以上的版本，可以在每次执行一个比较大的操作的时候通过执行mysql_reset_connection来重新初始化连接资源
2.查询缓存
查询缓存每次只要是有一个表要更新，这个表上所有的查询缓存都会被清空。除非是一张静态表
3.分析器--》优化器--》执行器
4.MySQL是如何执行一条SQL的
二.日志系统-一条SQL更新语句是如何执行的？
1.更新
InnoDB引擎先把记录写到redo log里面，并更新内存。
2.redo log和binlog都可以用于表示事务的提交状态，而两阶段提交就是让两个状态保持逻辑上的一致
3.redo log用于保证crash-safe的能力
4.redolog可以去掉吗？
- redolog只有InnoDB有，别的引擎没有
- redolog是循环写的，不持久保存，binlog的归档的功能，redolog是没有的
5.redo log与binlog的区别
- redo log是在InnoDB存储索引层产生，而binlog是mysql数据库的上层产生，并且二进制日志不仅仅针对InnoDB存储索引，MySQL数据库中的任何存储索引对于数据库的更改都会产生二进制日志
- 两种日志记录的内容形式不同，MySQL的binlog是逻辑日志，对应的为SQL语句。InnoDB存储引擎层面的重做日志是物理日志
- 两种日志与记录写入磁盘的时间点不同，二进制日志只是在事务提交完成之后进行一次写入。而innoDB的重做日志不断被写入
- redo log是循环使用的。binlog在写满或者重启之后，会生成新的binlog
- binlog可以作为恢复数据使用，主从复制搭建
三.事务隔离
1.隔离性与隔离级别
- ACID特性(原子性，一致性，隔离性，持久性)
- 同一条记录在系统中可能存在多个版本，就是数据库的多版本并发控制
2.事务隔离级别
读未提交：别人改数据的事务尚未提交，我在我的事务中也能读到
读已提交：别人改数据的事务已经提交，我在我的事务中才能读到
可重复读：别人改数据的事务已经提交，我在我的事务中也不去读(同一行在同一个事务下无论怎么读取都是同一个结果[除非自己把他改了])
串行：我的事务尚未提交，别人就别想改数据
这4种隔离级别，并行性能依次降低，安全性依次提高。
3.幻读&&可重复读
四.索引
1.索引的出现时为了提高数据查询的效率，就像书的目录一样
存储引擎 描述
ARCHIVE -----用于数据存档（行被插入后不能再修改）
BLACKHOLE-----丢弃写操作，读操作会返回空内容
CSV-----在存储数据时，以逗号分隔各个数据项
FEDERATED-----用来访问远程表
InnoDB-----具备外键支持功能的事务存储引擎
MEMORY-----置于内存的表
MERGE-----用来管理多个MyISAM表构成的表集合
MyISAM-----主要的非事务处理存储引擎
NDB-----MySQL集群专用存储引擎
2.三种常见的索引模型
- 哈希表：键值对存储数据的结构，适用于只有等值查询的场景(链表法处理冲突)
- 有序数组：在等值查询和范围查询场景中的性能非常优秀，但是只是适合于静态存储引擎
- 搜索树：读写性能高，适配磁盘的访问模式
3.InnoDB的索引模型
每一个索引在InnoDB中对应一颗B+树
主键索引与非主键索引
- 主键索引的叶子节点的内容是整行数据。在InnoDB中，主键索引也被称为聚簇索引
- 非主键索引的叶子节点内容是主键的值。在InnoDB中，非主键索引也被称为二级索引
基于主键索引和普通索引的查询有什么区别？
- 主键查询方式，只需要搜索主键这一个B+树
- 普通索引查询方式，需要回表[回到主键索引树搜索的过程，叫做回表]
基于非主键索引的查询需要多扫描一颗索引树
主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小
适合用业务字段直接做主键的场景：
- 只有一个索引
- 该索引必须是唯一索引
N叉树的N值在5.6之后是可以通过page大小来间接控制的(默认情况下，表空间中的页大小都为 16KB，当然也可以通过改变 innodb_page_size 选项对默认大小进行修改，需要注意的是不同的页大小最终也会导致区大小的不同)
B+树的叶子节点是page （页），一个页里面可以存多个行
drop主键索引会导致其他索引失效，但drop普通索引不会
B+树这种索引结构，可以利用索引的“最左前缀”
MySQL 5.6引入的索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数
重建索引的过程会创建一个新的索引，把数据按照顺序插入，这样页面的利用率最高，也就是索引最紧凑
4.in和between
k in (1,2,3,4,5)相当于从根遍历索引树5次，而between 1 and 5 是范围查询，每一个叶子节点都有一个额外指针，指向下一个叶子节点，而且它们的值都是有序的，直接遍历叶子节点的链表就可以
5.ACID里面，原子性和持久性做不到；隔离性只能实现基本用不到的串行化；一致性在正常运行的时候依赖于串行化，在异常崩溃的时候也不能保证
6.联合索引
联合索引是依次按照联合字段的先后顺序，依次进行排序。如a,b,c三个字段是联合索引，则叶子节点存储的是三个字段的数据，且按照先后顺序进行排序；而非叶子节点存储的是第一个关键字的索引。故当执行查询的时候，因为联合索引中是先根据a进行排序的，如果a没有确定，直接对b或c进行查询的话，就相当于是乱序查询，因此联合索引无法生效，此时就相当于是全表查询
7.索引优化为全覆盖索引
- 增加索引中的字段，让索引字段覆盖SQL语句中使用的所有字段
- 减少SQL语句中使用的字段，使SQL需要的字段都包含在现有索引中
8.节点的读取成本[为什么数据库索引使用B+树而不是二叉树呢？]
现代的操作系统中，把数据从外存读到内存使用的单位一般来说被称为"页"，每次读取数据都需要读入整数个的“页”。数据库索引使用节点大小恰好等于操作系统一页大小的B+树是实现效率最高的选择
9.面试相关的问题
聚集索引：
- 如果我们定义了主键，那么会选择主键作为聚集索引
- 如果没有显式定义主键，则会选择第一个不包含NULL值得唯一索引作为主键索引
- 如果也没有这样的唯一索引，则会选择内置6字节长的ROWID作为隐含的聚集索引
数据记录本身存于主索引的叶子节点上，这就要求同一个叶子节点内各条数据记录按照主键顺序存放，每次有一个新的记录插入的时候，会根据主键将其插入适当的结点和位置，如果页面达到装载因子，则开辟一个新的页
- 如果使用自增主键，则每次插入新的记录，记录就会顺序添加到当前索引结点的后序位置，当一页写满的时候，就会自动开辟新的页
- 如果使用非自增主键，由于每次插入的主键类似于随机，因此每次新的记录都要被插入到索引页的中间某个位置
为什么使用数据索引能够提高效率？
- 数据索引的存储是有序的，在有序的情况下，通过索引查询一个数据是无需遍历索引记录的，极端情况下，查询效率近似于二分查找
B+树中所有的叶节点中包含了全部的关键字的信息，以及指向含有这些关键字记录的指针，。所有的非终端节点可以看成是索引部分，结点中仅仅含有其子树根节点中最大或最小的关键字
为什么B+树比B树更适合用在数据库索引中？
- 磁盘读写代价更低
- 查询效率稳定，任何一个都是从根节点到叶子节点
联合索引:
- 索引是key index (a,b,c). 可以支持a 、 a,b 、 a,b,c 3种组合进行查找（类似于电话簿，知道姓比名字更重要，确切来说只知道名毫无用处）
什么时候应该少建索引？
- 表记录太少
- 经常插入，删除，修改的表
- 数据重复且分布平均的表字段
- 经常和主字段一块查询但是主字段索引值较多的表字段
表分区：从逻辑上看只是一张表，但是从底层却是多个物理分区组成
分区和分表：分区从逻辑上只有一张表，而分表则是将一张表分解为多张表
表分区的好处:
- 存储更多数据：分区表的数据可以分布在不同的物理设备上
- 优化查询：可以在多个分区上并行处理，最后汇总结果
- 分区表更容易维护
- 避免某些特殊的瓶颈
key和index的区别：key是数据库的物理结构，有约束与索引；index是数据库的物理结构，只是辅助查询的
MySQL中的MyISAM和InnoDB的区别有哪些？
- InnoDB支持事务，而MyISAM不支持
- InnoDB支持外键，而MyISAM不支持。对于一个包含外键的InnoDB转化为MyISAM会失败
- InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高，但是会回表；而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针
- InnoDB不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高
- 如果表中绝大多数都是读查询，可以考虑MyISAM，如果既有读写也挺频繁，则使用InnoDB
- InnoDB支持行锁，而MyISAM支持表锁
五.锁相关
1.锁的分类
全局锁
- 全局锁为对整个数据库实例加锁，使用的场景为做全库逻辑备份，也就是把整库每个表都select出来存成文本[备份过程中整个库完全只是处于只读状态]
如果整个库都是只读状态，则：
- 如果只是在主库上做备份，那么在备份期间都不能执行更新，业务基本就得停摆
- 如果在从库上做备份，那么备份期间从库不能执行从库同步过来的binlog，会导致主从延迟
表级锁
MySQL中表级别的锁有两种：
表锁[读锁会阻塞写，不会阻塞读。而写锁则会把读写都阻塞]
元数据锁
表锁的使用场景：
- 全表更新
- 多表查询
行锁:针对数据表中行记录的锁
在InnoDB事务中，行锁是在需要的时候才加上的，但是并不是不需要了就立即释放，而是等到事务结束之后才释放。这就是两阶段锁协议
最可能影响并发度的锁尽可能的往后放
多个事务操作同一行数据的时候，后来的事务处于阻塞等待状态。这样可以避免脏读等数据一致性的问题。后来的事务可以操作其他行的数据，解决了表锁的高并发性能低的问题
行锁的优化：
- 尽可能让所有的数据检索都通过索引来完成
- 尽可能避免间隙锁带来的性能下降
- 尽可能减少事务的粒度
- 尽可能低级别事务隔离，隔离级别越高，并发的处理能力越低
死锁：当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态
2.事务的隔离级别
InnoDB里面每一个事务有一个唯一的事务ID，叫做transaction id。它是在事务开始的时候向InnoDB的事务系统申请的，是按照申请顺序严格递增的
InnoDB利用了“所有的数据都有多个版本”的特性，实现了“秒级创建快照”的能力
更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”
InnoDB的行数据有多个版本，每一个数据版本都有自己的row trx_id，每一个事务或者语句都有自己的一致性视图。普通查询语句是一致性读，一致性读会根据row trx_id和一致性视图确定数据版本的可见性
六.实战部分
1.普通索引和唯一索引
InnoDB的数据是按照数据页为单位进行读写的。也就是说，当需要读一条记录的时候，并不是将这个记录从磁盘读出来，而是以页为单位，将其整体读入内存
使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本
身份证的存储方式：
- 倒序建立索引存储：无法使用索引方式
- 使用hash字段：只是支持等值查找
count(*)的实现方式
- MyISAM引擎把一个表的总行数存放在了磁盘上，执行count(*)的时候直接返回这个数
- InnoDB引擎在执行的时候需要把数据一行一行的从引擎里面读出来，然后累加计数
在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一
解决方法：
- 用缓存系统保存计数：缓存系统可能会丢失，而且更新会有时延
- 用数据库保存计数--》单独设置一张计数表：InnoDB支持崩溃恢复不丢数据的
2.幻读
- InnoDB的默认事务隔离级别是可重复读
3.
- 只要是redo log和binlog保证持久化到磁盘，就能确保MySQL异常重启之后，数据可以恢复
- InnoDB表的数据总是有序存放的，而内存表的数据是按照写入顺序存放的
投递腾讯等公司8个岗位 >