Mysql笔记一

目录

• Mysql笔记一
  • SQL查询的执行
  • 数据库事务
    • 是什么：
      • 特性
      • 避免长事务
      • 事务的启动方式
      • 总结
  • 索引
    • 为什么
    • 是什么
      • 哈希表
      • 搜索树
      • 有序数组
    • EG：InnoDB 的索引模型
      • 建表使用索引：
      • 索引的维护
      • 覆盖索引
      • 最左前缀原则（前缀索引）
      • 索引下推
  • 锁
    • 全局锁
      • 怎么做
      • 用途:
    • 表级锁
      • • • 表锁
        • 元数据锁
        • 如何安全地给小表加字段？
  • 事务隔离
    • 视图
    • 原理

Mysql笔记一

SQL查询的执行

MySQL 可以分为 Server 层和存储引擎层两部分。

Server 层包括连接器、查询缓存、分析器、优化器、执行器

存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持
InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是
InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

Server 层包括连接器、查询缓存、分析器、优化器、执行器

连接器：mysql ‑h$ip ‑P$port ‑u$user ‑p

查询缓存：之前执行过的语句及其结果可能会以 key-value 对的形式，被直
接缓存在内存中，8.0彻底删除了；

分析器：

优化器：

执行器：

数据库事务

是什么：

事务就是要保证一组数据库操作，要么全部成功，要么全部失败， 在引擎层实现， MyISAM 引擎就不支持事务

特性

读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

避免长事务

长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。

事务的启动方式

1. 显式启动事务语句， begin 或 start transaction。配套的提交语句是
   commit，回滚语句是 rollback。

2. set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如
   果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提
   交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者
   断开连接。

   总结

   读未提交：别人改数据的事务尚未提交，我在我的事务中也能读到。
   读已提交：别人改数据的事务已经提交，我在我的事务中才能读到。
   可重复读：别人改数据的事务已经提交，我在我的事务中也不去读。
   串行：我的事务尚未提交，别人就别想改数据。
   这4种隔离级别，并行性能依次降低，安全性依次提高

   脏读：

   当数据库中一个事务A正在修改一个数据但是还未提交或者回滚，

   另一个事务B 来读取了修改后的内容并且使用了，
   之后事务A提交了，此时就引起了脏读

   不可重复读

   幻读

索引

为什么

为了提高数据查询的效率，就像书的目录一样

是什么

类型:哈希表、有序数组和搜索树。

哈希表

哈希表是一种以键 - 值（key-value）存储数据的结构，我们只要输入待查找的值即 key，就可以找到其对应的值即 Value。哈希的思路很简单，把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把value 放在数组的这个位置。

特点：区间查询的速度是很慢，插入，等值查询速度快；

搜索树

O(log(N))的插入和查找复杂度

优化减少磁盘的访问次数，这个 N 差不多是 1200。这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。

有序数组

有序数组索引只适用于静态存储引擎，不能再修改的数据； O(log(N))的二分查找效率

EG：InnoDB 的索引模型

建表使用索引：

create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

根据叶子节点的内容，索引类型分为主键索引（聚簇索引）和非主键索引（二级索引）。

索引查询方式的区别： 是否需要回表；非主键索引需要先查找到对应的主键，然后回表进行查找到对应的

索引的维护

插入数据： 自增主键避免出现页分裂（数据页已经满了，根据 B+ 树的算法，插入数据需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂）

空间：用整型做主键，则只要 4 个字节，如果是长整型（bigint）则是 8 个字节。主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

覆盖索引

高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间

最左前缀原则（前缀索引）

B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。

遵循：，第一原则是，如果通过调整顺序，可以少维护一个索引，那么
这个顺序往往就是需要优先考虑采用的。第二：考虑的原则就是空间

索引下推

可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

锁

全局锁、表级锁和行锁

全局锁

怎么做

全局锁就是对整个数据库实例加锁。命令是

Flush tables with read lock (FTWRL)

让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：

数据更新语句（数据的增删改）

数据定义语句（包括建表、修改表结构等）

更新类事务的提交语句。

用途:

不加锁的话，备份系统备份的得到的库不是一个逻辑时间点，这个视图是逻辑不一致的。

可重复读也可以实现，但前提是引擎要支持这个隔离级别。

表级锁

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（metadata lock，MDL)。

表锁

Lock tables with read lock (FTWRL)

元数据锁

当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执
行完才能开始执行。

如何安全地给小表加字段？

1. 解决长事务，如果事务不提交，就会一直占着 MDL 锁。
2. 如果事务很多，可以在 alter table 语句里面设定等待时间。超时间直接放弃锁；

事务隔离

视图

1. 一个是 view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是 create view ... ，而它的查询方法与表一样。
2. 另一个是 InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistentread view，用于支持 RC（Read Committed，读提交）和RR（Repeatable Read，可重复读）隔离级别的实现。

原理

InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。
数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务ID 的最大值加 1 记为高水位。
这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。
而数据版本的可见性规则，就是基于数据的 row trx_id 和这个一致性视图的对比结果得到的。