XMySQL(事物隔离级别、索引底层原理以及优化等)
参考书籍📚:《MySQL技术内幕》
一.事物的隔离级别
1.总述
读未提交：什么都防止不了 读已提交：防止脏读 ==可重复读==：防止脏读和不可重复读==(默认隔离级别)== 串行化： 防止脏读、不可重复读、幻读
脏读：一个事物读取了另一个事物未提交的操作 不可重复读：一个事物读取了另一个事物的修改操作 幻读：一个事物读取了另一个事物的增加和删除操作——依靠行锁解决:间隙锁、邻键锁
事物：用户对数据库的操作序列集合 事物的特性： A-原子性：事物要么全做要么都不做 依靠undoLog📝实现 undolog:逻辑日志 在存储引擎层 记录的是 之前事物提交的所有数据 用于实现事物的原子性
C-一致性：事物执行前后状态保持一致 数据总量不会变多也不会变少 -能量的守恒 由 AID共同完成
I -隔离性：事物在提交之前 事物之间是隔离的 不会互相打扰 互相不可见 依靠MVCC实现：ReadView+UndoLog📝+隐藏的列 MVCC：多版本并发控制：一致性非锁定读 实现的无锁情况下保证对资源的原子性访问 基于cas算法实现 重复读和读已提交的具体实现 读视图的生成：创建该视图的事物id、事物最大id号、事物最小id号、活跃事物列表 1.当前事物id是否和创建事物id一致？一致的话直接读取 2.不一致的话 如果大于最大事物id+1 则不能读取 如果小于最小id则直接读取 如果两者之间 的话 去查看活跃的事物id列表 3.在活跃列表的话则不能读 4.不在的话 直接读取
D-持久性：数据库中的数据一旦保存了 就永久存在物理磁盘上 依靠redoLog📝实现：redoLog物理日志 在存储引擎层 记录的是 物理数据页的改动 然后持久化到物理磁盘 用于实现 事物的持久性
二.索引
1.底层原理
索引底层原理采用B+树这样一种数据结构 B+树:是一种多叉树 是一种矮胖树 它的非叶子结点存放 向下的指针 key值 不存放data 叶子结点存放 向下的指针 索引key值 并且叶子结点是双向的有序链表 叶子结点存放全量的数据 非叶子结点和叶子结点数据有重复
==索引的加载== 索引存放在磁盘中 需要用到它时 要去磁盘获取 然后 加载到内存中 才能使用 一般呢索引文件都很大 不能一次性加载完 那么就只能分批分块加载到内存 一般我们选择以块为单位加载到内存 每一块称之为 磁盘页 每一页mysql规定时16kb大小 每一个磁盘页对应了B+树的一个结点 就是说 根据B+树向下到下一个结点查找索引 要先去磁盘加载到内存 这就一次磁盘IO 磁盘IO开销很大因为 涉及到用户态到内核态到转换 因为转换需要占用cpu处理时间 需要保存用户栈 切换 内核栈 开销很大 为了减少IO次数 我们要么就在固定大小的磁盘页中加载更多的索引区间 要么就减少树的高度
🌟B树和B+树的区别： 1.叶子结点和非叶子结点的存储数据不同 B树非叶子结点存data数据 而 B+树不存 B+树有重复的结点 B+树叶子结点上一个双向排序的链表 添加和插入元素快 B+树的查询路径稳定 B+树的索引范围存储的比B树多得多 约1000倍 所以B+树高度小 IO次数少 为什么不用哈希表 1.哈希表虽然等值查询快 但是范围查询相当的慢 因为哈希表上无序存储的 所以每个结点都要从头开始遍历 为什么不用红黑树？ 1.红黑树是二叉树 每次向下读取索引的时候 都要去磁盘中读取数据到内存才能使用 二叉树太深了 导致需要进行多次的磁盘IO 消耗资源
2.作用
优点： 海量数据下 提升数据的查询速度和效率 索引的查询 默认使用了隐藏的优化器 提升性能 缺点： 索引的创建需要开销 索引的维护需要开销 索引占空间
3.分类
主键索引 普通索引 唯一索引 组合索引 全文索引
4.合适/不合适的使用场景
合适： 对一些数据进行频繁的查找 where条件中的列 用于连接的列 ——userid = id ==位于主键的列== ==需要排序的列== ==需要范围查找的列== 🌟失效:-“走全表” ·like字段进行全字符匹配：like %xxx%——-落地了 使用es去优化 ·select*语句—-平时不写select * 没有遵循最左匹配原则 ·列上做了运算—-平时不做列运算 ·where 条件中 用了!=/is not null/ >和< —平时注意
5.技术名词
回表：通过辅助索引拿不到整行数据 只能通过辅助索引的value 再通过书签 回到 主键的聚簇key 再查找整行数据 回表过程会去磁盘中加载索引到内存有一次磁盘IO 所以尽量减少回表次数
覆盖索引：尽量通过辅助索引 能查找到想要的数据 不需要走回表 又去聚簇索引找一次 减少回表次数 索引下推：在组合索引中 通过最左匹配原则查找数据时候 如果对查找的数据找不到 那么需要回表 再回表之前如果有条件的判断 那么就必须先判断 这个操作是在存储引擎层去做的 最左匹配原则： 对于组合索引 索引的匹配从左到右依次开始
6.聚簇索引非聚簇索引
1.聚簇索引：主键作为索引建立起来的B+树 通过key就可以得到整行数据data 2.非聚簇索引：不是用主键建立起来的B+树 data是对应主键B+树的key 还需要通过书签 访问一次主键索引 多一次磁盘IO 所以尽量减少磁盘非聚簇索引 InnoDB 支持1个聚簇索引和多个非聚簇索引 MyISAM 仅仅支持非聚簇索引
三.MySQL调优
1.索引优化
通过索引去加快数据的查询
索引的创建
1.创建表的时候 指定字段为索引 2.输入 creat index on 索引名 on 表名 字段名
一条sql的执行时间？
·使用show profiles;
如何用索引进行优化？
1.使用explain执行计划配合对应 select查询语句 和设置的索引字段 查看是否走了索引 ·type：是否走了索引 ·key：理论上走的索引 ·possible：实际上走的索引 ·extra：是否使用了覆盖索引
2.索引类型的选取： 索引字段的类型要选择在满足条件下的最小容量 避免大材小用 因为在b+树中存储索引的多少取决于key的值 key占空间越小 存的索引范围越大
3.在充当where的条件的列设置索引
4.在表连接处设置索引
5.主键建立索引
6.减少回表、尽量索引覆盖
2.SQL语句
4.SQL语句避免select* ——>不走索引走全表
5.like字段 避免 %xxx% ——>不走索引走全表
6.不在列上做运算
7.where条件判断时候 不取反 如!=/ is not null操作
3.存储引擎
搜索引擎采用InnoDB
InnoDB和MyISAM区别 1.InnoDB 支持聚簇索引 MyISAM不支持 2.InnoDB 支持行锁 MyISAM不支持 3.InnoDB 支持事物 MyISAM不支持 4.InnoDB 支持外键 MyISAM不支持 5.InnoDB 相对复杂 MyISAM相对容易 6.InnoDB 跨平台性弱 MyISAM跨平台性强
#####
⭐MVCC 采用多版本号的方式去实现 多版本并发控制 一致性非锁定读 针对读取数据 读已提交和重复读取的一种具体的实现 作用 实现了读读不冲突 写回数据库的时候 再判断 实现原理 (每一条记录后面默认都有)隐藏字段 DB—TRX—ID 最近一次被哪个事务修改 记录其ID DB-Row-ID 隐藏的主键 回滚指针 指向上一个历史版本记录 历史版本存在undolog中 ReadView 每次查询时候 生成一个readview 提供一个快照给当前读的事务 ReadView组成 creator_trx_id 创建这个readview的事务id trx_ids 活跃的事务id列表 low_limit 最大事务id+1 up_limit 最小事务id 怎么提供一个快照？ 提供哪一个快照？ 算法！ 判断 最新的事务id(当前进行操作的事物id) 和 creator_trx_id 是否相同 相同 直接读 不相同 事务id 大于 low_limit (大于最大) 不能读 事务id 小于 up_limit (小于最小) 能读 up_limit < 事务id < low_limit (在中间) 事务id 是否在 活跃的列表(trx_ids)中？ 在 不能读 不在 读取 MVCC只在 读已提交 和 可重复的隔离级别下生效 读未提交 已经读取到最新数据了 已经读到当前读了 串行化 innodb下规定使用锁 事务在 读已提交 和 可重复的隔离级别 产生的read view 不同 读已提交 每次读取都产生一个 readview 可重复读 只在第一次读取的时候产生 readview 后续读取只 延用上面的readview UnDoLog 历史版本内容包括 最近一次修改的事务id号 隐藏主键：row-id 上一次操作的具体 代码 上一次 的操作 是添加还是删除 指向上一个历史版本的指针