MYSQL 复苏2

最新推荐文章于 2024-05-20 14:31:07 发布
最新推荐文章于 2024-05-20 14:31:07 发布
阅读量1.3k 收藏 2
点赞数 2
文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/SStringss/article/details/123925478
版权

关于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 词向搞出来的

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

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

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

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

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

  1. mysql在多个扫描字段的时候回去判断哪个字段的扫描范围最小。对最小的扫描区间构建B+树或者说辅助索引。
  2. 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.扫描的行数与访问类型

怎么优化:

  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

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

索引失效原因：

  1. 对索引字段进行运算
  2. 字符类型不加引号
  3. 模糊查询尾匹配索引失效（可以倒转过来头匹配，极端条件下可以用全文索引）
  4. 用or 分开的条件，or的两个条件必须都是用索引否则索引失效
  5. 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还包含一个重要部分: 内存与磁盘进行数据交互的线程：

  1. Master Thread: 核心后台线程， 负责调度其他线程， 负责将缓冲池中的数据异步刷新到磁盘中，保持数据的一一致性。包括脏页的刷新， 很冰插入缓存，UNDO 页回收
  2. IO Thread: 在Innodb引擎中大量使用了AIO (异步非阻塞IO) 极大提高了数据库性能
  3. Purge Thread 主要用于回收事务已经提交了的undo_log. 在事务提奥之后前面的undo_log肯定要被更新掉/删除掉的。
  4. 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的实现原理：

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

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

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

    Undo_log版本连：在并发回滚的时候我们需要用一条链式结构保证我们回滚的准确性。

  3. readview 是快照读SQL执行MVCC提取数据的依据。记录并维护系统当前活跃的事务（未提交的) id。简单来说就是你去快照读的时候快照读本身不可能是表中的数据，那他肯定是一张视图喽。告诉你读那张快照并负责快照的删除

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

所以说隔离性就是靠锁以及MVCC实现的。四种隔离级别在上面的BLOG

至于一致性， 一致性属于是原子性， 隔离性， 持久性在一起想要达到的目标。没有具体的实现手段，需要我们人为的使用令三种特性却解决和完成一致性问题。

String vs Array
关注
  • 2
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
oracle2mysql
08-17
oracle2mysql 将oracle数据库中数据导入到mysql中，超级方便。
键值重复率高的B树索引构建过程和查询操作分析
bestpaydata的专栏
01-19 1141
liuyu（2014-09-16）     创建测试表： SQL> create table t_liu(id number,namevarchar2(100)); Table created.   SQL> createindex i_liu on t_liu(id);     SQL> selectobject_id from dba_objects where objec
MySQL索引底层：B+树详解
weixin_44837153的博客
03-02 1805
如果插入关键字后，叶子节点当前含有的关键字数目等于阶数m，则插，该节点开始 「分裂」为两个新的节点，一个节点包含m/2 个关键字，另外一个关键字包含m/2个关键值。最后插入28，发现当前节点关键字也是不小于阶数4了，于是分裂，于是分裂， 第 4/2=2个，也就是36上移到父节点，因父子节点只有2个关键值，还是小于4的，所以不用继续分裂，插入完成。找到包含关键值的结点,如果关键字个数大于m/2-1，并且关键值是当前节点的最大（小）值，并且该关键值存在父子节点中，那么删除该关键字，同时需要相应调整父节点的值。
脚本一键编译安装MySQL
suwenyang_blogs
07-10 338
脚本代码如下： #!/bin/bash #解压mysql二进制安装包 tar xf mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz #移动mysql解压后的文件到/usr/local下改名叫mysql mv mysql-5.7.33-linux-glibc2.12-x86_64 /usr/local/mysql #新建组和用户 mysql groupadd mysql #mysql这个用户的shell 是/bin/false 属于mysql组 useradd
MySQL的my.ini文件
weixin_45682377的博客
03-22 246
MySQL的my.ini文件原文:http://blog.csdn.net/heirenheiren/article/details/7895139正文 原文:http://blog.csdn.net/heirenheiren/article/details/7895139 正文 第一步，我们找到mysql安装文件下面的my.ini文件，打开可以看到第一句： # MySQL Server Ins...
第30章 MySQL 序列使用教程
12-24 806
MySQL 序列是一组整数：1, 2, 3, …，由于一张数据表只能有一个字段自增主键， 如果仁兄想实现其他字段也实现自动增加，就可以使用MySQL序列来实现。本章咱们将介绍如何使用MySQL的序列。
Access2MySQL
09-16
Access2MySQL,将Access表中的数据导入到MySQL数据库中。完美解决Access数据库迁移升级问题。
mysql 5.7 中文文档
02-16
翻译的还行 资源挺难找的
win11安装mysql
04-28
win11安装mysqlwin11安装mysqlwin11安装mysqlwin11安装mysqlwin11安装mysqlwin11安装mysqlwin11安装mysqlwin11安装mysqlwin11安装mysqlwin11安装mysqlwin11安装mysqlwin11安装mysqlwin11安装mysqlwin11安装mysqlwin...
mysql 5.7 perl
08-08
离线安装mysql文件包，包含依赖，以及peal全套安装包
MYSQL 视图/存储过程/触发器详解
lIujunXHU的博客
05-17 1122
视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，视图的主要目的是简化复杂的SQL查询，提高数据访问的安全性，以及隐藏数据的复杂性。。通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以在创建视图的时候，主要的工作就在于创建这条SQL查询语句上。存储过程是事先经过编译并存储在数据库中。
【MySQL精通之路】管理和实用程序
Anakki的博客
05-19 175
8 mysql_migrate_keyring——keyring密钥迁移实用程序。2 innocchecksum——离线InnoDB文件校验和实用程序。7 mysql_config_editor——mysql配置工具。9 mysqlbinlog——用于处理二进制日志文件的实用程序。6 myisampack——生成压缩的只读MyISAM表。4 myisamchk——MyISAM表格维护实用程序。5 myisamlog——显示MyISAM日志文件内容。3 myisam_ftdump——显示全文索引信息。
js遇到需要正则匹配来修改img标签+清除行内样式
阿乐的博客
05-14 326
遇到特殊的情况就是自带样式，那么方法一就不能用了，因为只会算是从原有字符串上面多加字符串。这个正则表达式就是匹配所有的。//踩坑完毕，可以直接使用。第二行代码按自己需要改改。
一条sql语句在mysql中是怎么执行的
m0_59925573的博客
05-17 653
连接器主要和身份认证和权限相关的功能相关，就好比一个级别很高的门卫一样。主要负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作，如果用户账户密码已通过，连接器会到权限表中查询该用户的所有权限，之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据，也就是说，后续只要这个连接不断开，即时管理员修改了该用户的权限，该用户也是不受影响的。
【Golang】 MySQL 在 Go 语言驱动程序中常用参数
tr6666的博客
05-15 319
在 Go 语言中，我们使用数据库驱动程序来连接和操作数据库。对于 MySQL，我们通常使用 go-sql-driver/mysql 驱动程序。在使用这个驱动程序时，我们需要提供一个数据源名称（DSN），它是一个包含数据库连接信息的字符串。在这个字符串中，我们可以设置很多参数来控制驱动程序的行为。本文将介绍一些常用的参数。在 MySQL 的 Go 语言驱动程序中，我们可以通过设置 DSN 中的参数来控制驱动程序的行为。这些参数可以影响到连接的建立，数据的读写，以及数据的解析等各个方面。
Mysql利用AI生成问题完成目标
最新发布
kkaagf的博客
05-20 246
帮我创建2个表student与score表，要求student表有id,createDate,userName,phone,age,sex,introduce，帮我创建一个修改score表scoreName的触发器，当修改scoreName的时候触发，判断修改的scoreName是否是数学，如果是数学就改成(天书)。帮我根据两个DDL创建一个插入信息的存储过程，先插入10条学生表信息，再根据插入的学生表信息插入100条对应studentId的score成绩，所有信息随机生成，学生表的自增序号从6开始。
Mysql-事务
2302_77099705的博客
05-17 331
事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。例如银行转账。默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务。
数据库基础MySql
2301_78085386的博客
05-14 763
●数据(结构)定义语言DDL(Data Definition Language)，是用于创 建和修改数据库表结构的语言。●常用的语句：create ,alter,drop,rename。
mysql和mysql2
04-30
MySQL2是一个用于Ruby编程语言的MySQL数据库驱动程序。它是MySQL和Ruby之间的桥梁，允许Ruby程序与MySQL数据库进行交互。MySQL2提供了简单易用的API，可以执行SQL查询、插入、更新和删除操作，并且支持事务处理和...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值