mysql 浅析

索引的结构 B+树

二叉查找树、平衡二叉树 、B树、 B+树

B树：

image.png

B+树：

image.png

B+树中各个页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。

B+树和B树有什么不同:

1. B+树非叶子节点上是不存储数据的，仅存储键值，而B树节点中不仅存储键值，也会存储数据。之所以这么做是因为在数据库中页的大小是固定的，innodb中页的默认大小是16KB。如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数有会再次减少，数据查询的效率也会更快。

另外，B+树的阶数是等于键值的数量的，如果我们的B+树一个节点可以存储1000个键值，那么3层B+树可以存储1000×1000×1000=10亿个数据。一般根节点是常驻内存的，所以一般我们查找10亿数据，只需要2次磁盘IO。 

2. 因为B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。而B树因为数据分散在各个节点，要实现这一点是很不容易的。  

聚簇索引和非聚簇索引

聚集索引与非聚集索引的区别是：叶节点是否存放一整行记录。
https://juejin.cn/post/6844903845554814983

Innodb和My ISAM的区别
MyISAM：
非聚簇索引
支持全文索引，
不支持事务。
存储结构：三个文件，一个数据文件，一个索引文件，一个表文件。
表级别的锁。
Innodb：
聚簇索引
不支持全文索引，
支持事务。
存储结构：所有表同一个文件。
行级别的锁。

image.png 4B8A110A-ED6C-410F-825D-CAEF82C280C9.png

隔离级别

事务的ACID表示原子性（atomicity）、一致性（consistency）、隔离性（isolation）和持久性（durability）。

原子性（atomicity）
　　一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性
一致性（consistency）
     数据库总是从一个一致性的状态转换到另一个一致性的状态。（在前面的例子中，一致性确保了，即使在执行第三、四条语句之间时系统崩溃，支票账户中也不会损失200美元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。）
隔离性（isolation）
     通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。（在前面的例子中，当执行完第三条语句、第四条语句还未开始时，此时有另外的一个账户汇总程序开始运行，则其看到支票帐户的余额并没有被减去200美元。）
持久性（durability）
　　一旦事务提交，则其所做的修改不会永久保存到数据库。（此时即使系统崩溃，修改的数据也不会丢失。持久性是个有占模糊的概念，因为实际上持久性也分很多不同的级别。有些持久性策略能够提供非常强的安全保障，而有些则未必，而且不可能有能做到100%的持久性保证的策略。）

概念：

脏读(Drity Read)：  
      某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。
不可重复读(Non-repeatableread)：  
      在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新了原有的数据。
幻读(Phantom Read):  
      当对某行执行插入或删除操作，而该行属于某个事务正在读取的行的范围时，会发生幻像读问题。事务第一次读的行范围显示出其中一行已不复存在于第二次读或后续读中，因为该行已被其它事务删除。同样，由于其它事务的插入操作，事务的第二次或后续读显示有一行已不存在于原始读中。幻读其实也应该算是一种不可重复读现象，只是它只是相对于insert和delete操作，而上面的不可重复读现象但注重的是update操作。这里这样称呼的原因是insert的新的row是没有版本信息的，它要通过一个范围来确定

"幻读"现象是不能通过行锁来避免的，必须通过Serializable隔离级别来避免

隔离级别：

READ UNCOMMITTED（未提交读）
　　在READ UNCOMMITTED级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称为脏读（Dirty Read）。
READ COMMITTED（提交读）
　　大多数数据库系统的默认隔离级别都是READ COMMTTED（但MySQL不是)一个事务开始时，只能"看见"已经提交的事务所做的修改
REPEATABLE READ(可重复读)
　　REPEATABLE READ解决了脏读的问题。该隔离级别保证了在同一个事务中多次读取同样记录结果是一致的. 无法解决另外一个幻读（Phantom Read）的问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时，另一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行（Phantom Row）。InnoDB和XtraDB存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）解决了幻读的问题。
SERIALIZABLE（可串行化）
    SERIALIZABLE是最高的隔离级别。它通过强制事务串行执行，避免了前面说的幻读的问题。简单来说，SERIALIZABLE会在读取每一行数据都加锁，所以可能导致大量的超时和锁争用问题。

打钩说明该隔离级别还存在这种情况，打X代表该隔离级别已经解决了这种情况：

image.png

查看mysql隔离级别
SELECT @@tx_isolation
设置mysql隔离级别
set session transaction isolation level 设置事务隔离级别
实例：http://blog.csdn.net/wudongxu/article/details/8623610

锁

• 乐观锁（version）
• 悲观锁[共享锁+排他锁、行锁+表锁]

排他锁和共享锁

排他锁： 指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁。

mysql InnoDB 引擎默认的语句：update,delete,insert都会自动给涉及到的数据加上排他锁。select语句默认不会加任何锁类型。
如果加排他锁可以使用select ...for update语句。加共享锁可以使用select ... lock in share mode语句。

所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select ...from...查询数据，因为普通查询没有任何锁机制。

begin;
select * from users where id=1 for update;
select * from users where id=1 lock in share mode;
update users set uname='aaa' where id =1;
select * from users where id=1;
commit;
rollback;

主从复制

https://blog.csdn.net/wolfGuiDao/article/details/108245146

image.png

• ①当Master节点进行insert、update、delete操作时，会按顺序写入到binlog中。
• ②salve从库连接master主库，Master有多少个slave就会创建多少个binlog dump线程。
• ③当Master节点的binlog发生变化时，binlog dump 线程会通知所有的salve节点，并将相应的binlog内容推送给slave节点。
• ④I/O线程接收到 binlog 内容后，将内容写入到本地的 relay-log。
• ⑤SQL线程读取I/O线程写入的relay-log，并且根据 relay-log 的内容对从数据库做对应的操作。

主从延迟原理

从库同步主库数据的过程是串行化的，也就是说主库上并行的操作，在从库上会串行执行。

解决方式：

• 一个是半同步复制，用来解决主库数据丢失问题；
  指的就是主库写入 binlog 日志之后，就会将强制此时立即将数据同步到从库，从库将日志写入自己本地的 relay log 之后，接着会返回一个 ack 给主库，主库接收到至少一个从库的 ack 之后才会认为写操作完成了。
• 一个是并行复制（手动打开），用来解决主从同步延时问题。
  指的是从库开启多个线程，并行读取 relay log 中不同库的日志，然后并行重放不同库的日志，这是库级别的并行。

mysql实现主从复制配置：
https://www.cnblogs.com/superfat/p/5267449.html

Redo 的组成

Redo log可以简单分为以下两个部分：

• 一是内存中重做日志缓冲 (redo log buffer),是易失的，在内存中
• 二是重做日志文件 (redo log file)，是持久的，保存在磁盘中

redo log 流转过程

image.png

• 第一步：先将原始数据从磁盘中读入内存中来，修改数据的内存拷贝
• 第二步：生成一条重做日志并写入redo log buffer，记录的是数据被修改后的值
• 第三步：当事务commit时，将redo log buffer中的内容刷新到 redo log file，对 redo log file采用追加写的方式
• 第四步：定期将内存中修改的数据刷新到磁盘中

undo log

主要记录的是数据的逻辑变化，为了在发生错误时回滚之前的操作，需要将之前的操作都记录下来，然后在发生错误时才可以回滚。

有两个作用：

• 用于事务的回滚
• MVCC

事物

[大厂面试必问的MySQL事务ACID原理 https://www.bilibili.com/video/BV1Zv411b7Ty?p=2&spm_id_from=pageDriver]
WAL 预写日志（顺序写，提高性能）
写事物 -> [innodb存储引擎]bufeerPool（DML：脏页数据）wal方式-> redo log buffer （择时落磁盘、commit 提交时）-> redo log file (循环写入)
redo log 落盘机制：【默认1，0，2】
undo log [事物ID：TRX_ID,ROW_ID + ROLL_PT 回滚指针] (rollback +mvcc)
mvcc 多版本并发控制 （一致性非锁定读：写不阻塞读）
怎么实现的：
快照读(普通的 select) -》readView 读视图 [RC / RR] ->可重复读
当前读 （undo log）
幻读：（insert） mysql 间隙锁
-------索引
[https://www.modb.pro/db/48094] 联合索引 -> “最左匹配”, (a,b)在 B+树上存在一个树上，所以遇到范围查询失效

explain优化

explain优化

A366301E-F6C0-4F1C-ABA0-E51D5B4D19DF.png

监控

show processlist;
show full processlist;
显示哪些线程正在运行：

FBBC9DDF-59BF-4645-A497-DEA54EB965DE.png

如果您有root权限，您可以看到所有线程。否则，您只能看到登录的用户自己的线程，通常只会显示100条。如果想看跟多的可以使用full修饰。

参数

* id       #ID标识，要kill一个语句的时候很有用
* use      #当前连接用户
* host     #显示这个连接从哪个ip的哪个端口上发出
* db       #数据库名
* command  #连接状态，一般是休眠（sleep），查询（query），连接（connect）
* time     #连接持续时间，单位是秒
* state    #显示当前sql语句的状态
* info     #线程执行的sql语句，如果没有语句执行则为null

总结了一些常见问题：

• CPU报警：很可能是 SQL 里面有较多的计算导致的
• 连接数超高：很可能是有慢查询，然后导致很多的查询在排队，排查问题的时候可以看到”事发现场“类似的 SQL 语句一大片，那么有可能是没有索引或者索引不好使，可以用：explain 分析一下 SQL 语句

其他：

mysql 连库语句
mysql -uroot -P3306 -h10.xx.xx.225 -ppwd db_name --default-character-set=utf8;

修改mysql的auto_increment
alter table xxx auto_increment = 100;

mysql 查看指定数据库中所有表的行数
SELECT table_name, table_rows FROMinformation_schema.tablesWHERE TABLE_SCHEMA = ’table_name_xxx' ORDER BY table_rows DESC;