MYSQL 复苏2

最新推荐文章于 2024-08-09 21:27:22 发布

String vs Array

最新推荐文章于 2024-08-09 21:27:22 发布

阅读量1.4k

点赞数 2

文章标签： mysql

本文链接：https://blog.csdn.net/SStringss/article/details/123925478

版权

本文深入探讨了MySQL中的B树和B+树索引，包括它们对重复键值的处理，以及聚集索引和辅助索引的运作机制。详细介绍了InnoDB存储引擎的索引类型，如自适应哈希索引、全文检索和倒排索引。此外，还讨论了事务的原子性、持久性和MVCC实现，以及不同隔离级别的工作原理。同时，涵盖了慢查询日志、索引优化策略和锁机制，如全局锁、表级锁和行级锁。

摘要由CSDN通过智能技术生成

关于B树B+树问题纠正：

B树和B+树是允许键值重复的。虽然大部分的教程基于不支持重复，有的甚至直接说不支持重复真的请不要误导。 B+树中遇到重复键值的方法主要是采用溢出页的方法。即分配一个溢出页来存放所有重复键值以及其记录的偏移量：这也能很好的解决下面的辅助索引关于索引值重复的问题

在这里插入图片描述

InnoDB中的索引：

聚集索引/ 聚簇索引：只建立一颗B+树（索引树为主键）

将表的主键用来构造一棵B+树，并将整张表的行记录放在B+树的叶子节点中。对于聚集索引来说，叶子节点会放置这个所有的数据，而且根据主键式排好序的。同时叶子节点间本身就是双向链表的数据结构，我们通过主键去索引相似值的时候是很快的

即使一张表没有创建主键，Mysql也会隐式地创建一个rootid (隐含主键）。
辅助索引/ 二级索引：建立两颗B+树（查询字段 + 聚簇索引）

在聚集索引的基础上，如果我们筛选的对象字段并不是主键，难道我们就要遍历整个表了么？InnoDB也支持为这些非主键字段建立索引，我们称之为辅助索引或者说二级索引。

辅助索引的叶子节点不会放整行数据，只会放置当前字段的信息与主键信息方便提取数据 。在查找数据是，先提取主键，再通过主键到聚簇索引查询到这个行数据。这种查找方法叫做回表
联合索引/ 复合索引：建立两颗颗B+树（一颗B+树索引值按优先级进行排序），索引值的结果还是id + 聚簇索引。将表上多个列组起来进行索引，也可以用覆盖索引不进行回表

自适应Hash索引： innoDB存储引擎内部监控索引热数据然乎内部船舰hash索引。称之为自适应Hash索引（Adaptivr Hash Index, AHI), 控制参数innode_adaptive_hash_index 与innode_adaptive_hash_indexs_parts (调整我们Hash表的个数，默认8)

show engine innodb status;
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s

Tips: 热数据。指经常使用的IO频繁的数据。这类数据会被存储在缓存中方便快速调用。

全文检索之倒排索引：事先将相关内容关键字并保存索引值。用于数据库中类似正则的字段搜索（这玩应就是耍流氓，咋优化也是白搭）。倒排提取有一定的规则，通过Terms 词向搞出来的

重点：索引在查询中的使用

一个索引就是一个B+树，索引让我们的查询可以快速定位和扫描到我们需要的数据记录上，加快查询速度
一个 select 查询语句在执行过程中一般最多能使用一个二级索引，即使在where语句中使用了多个二级索引

扫描区间：由于叶子节点本身就是一个双向链表，我们B+ 树的作用其实就是减少我们扫描的区间，让我们不是全表扫描，性能答复上升。通过B+树找到的位置就是扫描区间。扫描区间包括单点扫描区间（a = * ）和符合扫描区间（a > * and a < *). 所以上面查询的第二点就相当于每个select只能给出一个二级索引的扫描区间。另外的条件会在确定扫描空间后遍历查看是否符合

关于运算符Like, 只有匹配完整的字符串或完整字符串前缀才产生合适的扫描区间。eg. b% 可以缩小扫描区间， %b必须经过全表

复杂搜索条件下范围匹配的区间, （mysql 如何优化）：

mysql在多个扫描字段的时候回去判断哪个字段的扫描范围最小。对最小的扫描区间构建B+树或者说辅助索引。
Mysql计算索引的代价方法：https://blog.csdn.net/why444216978/article/details/104986241 Cost = CPU Cost + IO Cost. 页是磁盘和内存之间交互等基本单位，读取一个页花费的成本默认是1.0，读取及检测一条记录是否符合搜索条件的成本默认是0.2。

索引的代价：空间代价（B+树的空间代价）与时间代价（B+树本身的时间复杂度是 log n, 最坏情况是 logn **2 一直分裂到顶端）

高性能索引创建策略：

索引列的类型尽量越小越好，在查询时进行的比较操作越快
数据类型越小，索引占用的存储空间就越少，就会花掉更少的数据页加快读写效率
索引的离散型尽量高一些，这样扫描区间会减少很多

前缀索引：索引字符串时，之索引前半部分的字符串做索引，即前缀索引。后缀索引就是反过来从后面找啦。

三星索引：

索引将相关记录放到一起则一星，索引中数据顺序核查找中的排列顺序一致则而行，索引列中包含索引查询需要的全部列获得三星。

慢查询：慢查询日志，就是查询慢的日志。指mysql 记录所有执行超过long_query_time参数设定的时间阈值的SQL语句的日志。该日志能为SQL语句的优化带来很好的帮助。默认情况下慢查询日志是关闭的。要使用慢查询日志功能就要开启慢查询日志功能。

什么时候用到慢查询优化：

是否存在扫描额外的记录：1.查询了不需要的记录， 2. 总是去除重复列 3. 重复查询相同的数据
是否扫描额外的记录： 1. 响应时间 2. 扫描的行数与返回的行数 3.扫描的行数与访问类型

怎么优化:

一个复杂查询还是多个简单查询
切分查询
分解关联查询：
- 让缓存效率更高
- 减少锁的竞争
- 更容易做到高性能与可扩展
- 较少冗余记录的查询
- 相当于Hash关联

开启慢查询日志，可以让MySQL记录下查询超过指定时间的语句，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。

# 慢查询日志开启方法  /etc/my.cnf
# 查询状态: show variables like 'slow_query_log'
slow_query_log=1
# 将查询时间两秒以上的sql语句记录下来
long_query_time=2

关于索引的语法：

create [UNIQUE|FULLTEXT] INDEX index_name on (filed1,field2...)
SHOW INDEX FROM table_name;
Drop INDEX index_name ON table_name

最左前缀法则：查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃索引列，索引将会部分失效(跳跃后面的部分)失效。而且使用> <范围查询，范围查询后面的列索引会是失效.解决办法：加上 =

索引失效原因：

对索引字段进行运算
字符类型不加引号
模糊查询尾匹配索引失效（可以倒转过来头匹配，极端条件下可以用全文索引）
用or 分开的条件，or的两个条件必须都是用索引否则索引失效
Mysql自行计算判断，见上面计算代价方法

SQL提示，人为的告诉Mysql应该用哪个索引

explain select * from table use index(索引名称) ....
。。。。。。。。。。。。。。。 ignore index() .....
。。。。。。。。。。。。。。。 force inex() ......

视图：简单来说就是Mysql的函数，可重用，数据段结构自由，还能在一定程度上限定权限，进行迁移等等，同时不改变表原来的结构，也不会重新存储数据。属于是比较舒服的重用工具。

视图的存储过程：

CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
		--SQL语句
END;

# 调用
CALL 名称 ([参数]);
# 查看
SHOW CREATE PROCEDURE 存储过程名称
# 删除
DROP PROCEDURE [IF EXISTS] 存储过程名称

触发器：表有关的数据库对象，指在insert/ update/ delete之前或之后除法触发器定义的SQL语句合计。可用于数据完整性，日志记录，数据校验等操作。（有点像信号或者说钩子）

MYSQL 锁，事务：

Mysql中的锁：

全局锁：锁住整个数据库实例，数据库只读，主要用于备份4

flush tables with read lock
unlock tables

表级锁：锁住整张表。并发度较低。分为三类：表锁，元数据所，意向锁

表锁：读锁与写锁（读锁都共享，写锁读写都不共享—）
元数据锁: 无需显示使用，访问一张表会自动加上。主要是维护表元数据的数据一致性，在表上进行DDL的时候不可以DML
意向锁：防止行锁与表锁的冲突，加表锁的时候不用对行检测是否加行级锁。意向锁又分意向共享锁：与表读锁兼容；一项排他锁：与表锁完全排斥。

行级锁：锁住索引或行数据。锁定粒度最小。在InnoDB中行锁即为索引锁。

行锁：锁住相应的索引值。包括共享锁（兼容共享锁），排他锁（都不兼容）。如果不通过索引就会升级成表锁
间隙锁：锁住索引的间隙
临建锁：行锁 + 间隙锁

InnoDB引擎架构，事务原理， MVCC:

先来看Innodb的结构：

在这里插入图片描述

表空间：以ibd文件存在于磁盘当中

段：分为数据段，索引段，回滚段。 InnpDB是索引组织表，数据段就是B+树的叶子节点，索引段是B+树的非叶子节点。段用来管理多个Extent（区）

区是存放页的抽象结构。一个区默认为1M，存放64个数据页

页是Innodb存储引擎的最小管理单元。默认为16K

行： Trx_id 每次对记录进行改动时，都会把事务的id赋值给trx_id和隐藏列。四种Mysql的分级就是基于Trx_id实现的。可以看上面偏开头的部分。 Roll_pointer保存旧版本指针。旧版本会被写道undo.log中。配合undo.log完成row_back.

InnoDB在磁盘中的结构图如下

在这里插入图片描述

除了上述图中的结构, InnoDB还包含一个重要部分: 内存与磁盘进行数据交互的线程：

Master Thread: 核心后台线程，负责调度其他线程，负责将缓冲池中的数据异步刷新到磁盘中，保持数据的一一致性。包括脏页的刷新，很冰插入缓存，UNDO 页回收
IO Thread: 在Innodb引擎中大量使用了AIO (异步非阻塞IO) 极大提高了数据库性能
Purge Thread 主要用于回收事务已经提交了的undo_log. 在事务提奥之后前面的undo_log肯定要被更新掉/删除掉的。
Page Thread 协助master thread 进行脏页处理的

InnoDB 事务原理：

事务是一组操作的集合，是一个不可分割的工作单位。事务会把所有的操作作为一整个整体一起向系统系统提交操作请求。

事务的底层原理：

原子性：Undo_log, 一有问题立刻回滚肯定保证了原子性啦。Undo_log主要提供作用是回滚于MVCC. undo_log是逻辑日志（redo_log是存在缓冲区和磁盘上的物理日志）意思就是记录当前操作相反的内容并记录好指针。出错就按照Undo_log上相反的命令运行达到回滚的效果。undo_log并不会在事务提交时销毁。因为这些日志文件虽然不能继续作用于原子性，但可以用于MVCC. undolog采用段的方式管理。

持久性： Redo_log 记录数据页的物理修改。每次修改会自动刷新。由于状态在RedO_log中保存所以完成了持久化，即即使宕机也能至少返回上一个状态。

MVCC: ( 基本概念 )：

当前读：读取的记录是最新版本，保证其他并发事务不能修改当前记录，会对读取的记录进行枷锁
快照读：不加锁，读取到的有可能是历史数据，不加锁，是非阻塞读：
- Read Commited: 每次select 都生成一个快照
- Repeatable Read: 开启事务的第一个select 语句才是快照读的地放
- serializable 快照读退化为当前读（比read Commited 多一个阻塞于加锁)

MVCC: 多版本并发控制。维护一个数据的多个版本，使读写操作没有冲突。快照读为Mysql实现MVCC提供了一个非阻塞读的过程。MVC具体的实现，依赖于数据库记录中的三个隐式字段， undo log, readView.

MVCC的实现原理：

记录中的隐藏字段：再我们创建记录的时候，innoDB会为我们隐式的额外的创建三个字段：DB_TRX_ID , DB_ROLL_PTR, DB_ROE_ID.

隐藏字段	内容
DB_TRX_ID	最近修改事务ID。记录插入事务后最后yIC修改该记录的事务ID
DB_ROLL_PTR	回滚指针，指向这条记录的上一个版本，用于配合undo log回滚到上一个版本
DB_ROW_ID	隐藏主键，如果表结构没有指定主键，将会生成隐藏字段。

Undo_log日志。主要是便于数据的回滚，上文说到，undo_log不能在事务提交的时候立即删除，这里具象化删除时间：当insert的时候，产生的undo_log旨在回滚的时候需要。事务提交之后可以被立刻删除。但是当uupdate, delete的时候，产生的undo_log日志不仅回滚的时候需要，快照读的时候也需要，不能立刻删除。所以为什么不能立即删除，因为要兼顾到快照读。换句话说生成下一张快照快照的时候这些数据就可以被淘汰了。

Undo_log版本连：在并发回滚的时候我们需要用一条链式结构保证我们回滚的准确性。
readview 是快照读SQL执行MVCC提取数据的依据。记录并维护系统当前活跃的事务（未提交的) id。简单来说就是你去快照读的时候快照读本身不可能是表中的数据，那他肯定是一张视图喽。告诉你读那张快照并负责快照的删除

所以说实现隔离级别的这篇https://blog.csdn.net/baihualindesu/article/details/100375244简直超人