基础知识

数据库范式

  1. 第一范式(1NF):原子性,不可再分解
  2. 第二范式(2NF):唯一性,在第一范式基础上,非主键列完全依赖于主键
  3. 第三范式(3NF):独立性,在第二范式的基础上,非主键列依赖于主键,不依赖于其他非主键,消除传递依赖
  4. 其他:巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)

语句类别

  • DDL(Database Defination Language):create、alter等
  • DML(Database Manipulation Language):insert、update、delete等
  • DCL(Database Control Language):grant、revoke等
  • DQL(Database Query Language):select

事务

事务相关命令

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

建议总是使用set autocommit=1, 通过显式语句的方式来启动事务。

ACID

  • A (Atomicity) 原子性:整个事务中的所有操作,要么全部完成,要么全部不完成,不可能停滞在中间某个环节。事务在执行过程中发生错误,会被回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样
  • C (Consistency) 一致性:在事务开始之前和事务结束以后,数据库的完整性约束没有被破坏
  • I (Isolation) 隔离性:一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的,并发执行的各个事务之间不能互相干扰
  • D (Durability) 持久性:在事务完成以后,该事务所对数据库所作的更改便持久的保存在数据库之中,并不会被回滚

隔离级别

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

数据库隔离级别不够时容易出现3种情况:

  • 脏读:即事务A读取到了事务B未提交的数据。该数据可能不是最终数据而是中间变量,因此会导致事务A读取到错误的数据。
  • 不可重复读:即事务A多次访问同一个数据,获得结果不相同。在访问间隔时,事务A访问数据被事务B修改,导致事务A多次读取到不一样的数据值。
  • 幻读:即事务A在读取某些数据时,事务B通过插入或删除等方式对数据集进行了修改,导致事务A读取到了事务B执行后的结果。

下面是个级别可能发生的情况:

隔离级别和可能出现的情况

MySQL 的执行流程

大体来说,MySQL可以分为Server层和存储引擎层两部分。Server层包括连接器、查询缓存、分析器、优化器、执行器等,涵盖MySQL的大多数核心服务功能,以及所有的内置函数(如日期、时间、数学和加密函数等),所有跨存储引擎的功能都在这一层实现,比如存储过程、触发器、视图等。

查询执行流程如下:

  1. 连接到数据库,这时候接待你的就是连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接。

  2. 查询缓存。MySQL拿到一个查询请求后,会先到查询缓存看看,如果命中,那么这个value就会被直接返回给客户端,这里也会做一个权限验证。

  3. 如果没有命中查询缓存,就要开始真正执行语句了。分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条SQL语句,MySQL需要识别出里面的字符串分别是什么,代表什么。

  4. 根据词法分析的结果,语法分析器会根据语法规则,判断你输入的这个SQL语句是否满足MySQL语法。

    如果你的语句不对,就会收到“You have an error in your SQL syntax”的错误提醒。

  5. 经过了分析器,MySQL就知道你要做什么了。在开始执行之前,还要先经过优化器的处理。

    优化器是在表里面有多个索引的时候,决定使用哪个索引;或者在一个语句有多表关联(join)的时候,决定各个表的连接顺序。

  6. MySQL通过分析器知道了你要做什么,通过优化器知道了该怎么做,于是就进入了执行器阶段,开始执行语句返回结果。

    开始执行的时候,要先判断一下你对这个表有没有执行查询的权限,如果没有,就会返回没有权限的错误。如果有权限,就打开表继续执行。打开表的时候,执行器就会根据表的引擎定义,去使用这个引擎提供的接口。

需要注意:

存储引擎对比

常见:InnoDB、MyISAM;其他:Archive、CSV、Memory等

对比项 MyISAM InnoDB
外键 不支持 支持
事务 不支持 支持
表锁 行锁
缓存 只缓存索引,不缓存真实数据 不仅缓存索引还要缓存真实数据,对内存要求较高,而且内存大小对性能有决定性的影响
关注点 节省资源,消耗少,简单业务,性能 并发写,事务,更大资源
默认安装
默认使用
自动系统表使用

应用场景:

  • MyISAM管理非事务表,提供高速存储和检索以及全文搜索能力,如果再应用中执行大量select操作,应该选择MyISAM

  • InnoDB用于事务处理,具有ACID事务支持等特性,如果在应用中执行大量insert和update操作,应该选择InnoDB

索引

从底层实现来说,索引类型包括 B-Tree(InnoDB引擎)、哈希索引(Memory引擎)、空间数据索引(R-Tree, MyISAM引擎支持)、全文索引等。

从索引的实现上,我们可以将其分为聚集索引与非聚集索引,或称辅助索引或二级索引。

从索引的实际应用中,又可以细分为普通索引、唯一索引、主键索引、联合索引、外键索引、全文索引这几种。

建议看以下文章了解索引使用和底层实现:

10分钟让你明白MySQL是如何利用索引的

详解Mysql索引原理及其优化

MySQL索引背后的数据结构及算法原理

MySQL索引底层实现原理

B树的特点

1个 m 阶的B树满足以下条件:

  1. 每个结点至多拥有m棵子树;
  2. 根结点至少拥有两颗子树(存在子树的情况下),根结点至少有一个关键字;
  3. 除了根结点以外,其余每个分支结点至少拥有 m/2 棵子树;
  4. 所有的叶结点都在同一层上,B树的叶子结点可以看成是一种外部节点,不包含任何信息;
  5. 有 k 棵子树的分支结点则存在 k-1 个关键码,关键码按照递增次序进行排列;
  6. 关键字数量需要满足ceil(m/2)-1 <= n <= m-1;

更多请看这篇: B树和B+树的总结

B树和B+树的区别

这都是由于B+树和B树具有不同的存储结构所造成的区别,以一个m阶树为例。

  1. 关键字的数量不同;B+树中分支结点有m个关键字,其叶子结点也有m个,其关键字只是起到了一个索引的作用,虽然B树也有m个子结点,但是其只拥有m-1个关键字。
  2. 存储的位置不同;B+树中的数据都存储在叶子结点上,也就是其所有叶子结点的数据组合起来就是完整的数据,但是B树的数据存储在每一个结点中,并不仅仅存储在叶子结点上。
  3. 分支结点的构造不同;B+树的分支结点存储着关键字信息和儿子的指针(这里的指针指的是磁盘块的偏移量),也就是说内部结点仅仅包含着索引信息。
  4. 查询不同;B树在找到具体的数值以后就结束,而B+树则需要通过索引找到叶子结点中的数据才结束,也就是说B+树的搜索过程中走了一条从根结点到叶子结点的路径,其高度是相同的,相对来说更加的稳定;
  5. 区间访问:B+树的叶子结点会按照顺序建立起链状指针,可以进行区间访问;

索引类型

  • 前缀索引: 在对一个比较长的字符串进行索引时,可以仅索引开始的一部分字符,这样可以大大的节约索引空间,从而提高索引效率。但是这样也会降低索引的选择性。
  • 索引的选择性: 不重复的值/所有的值(区分度)
  • 联合索引
  • 聚簇索引:不是一种索引类型,而是一种存储数据的方式。InnoDB的聚簇索引是在同一个数据结构中保存了索引和数据。默认是主键,如果表中没有定义主键,InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引,InnoDB会隐式定义一个主键作为聚簇索引
  • 覆盖索引:当一个索引包含(或者说是覆盖)需要查询的所有字段的值时,我们称之为覆盖索引

索引优点

  • 减少查询需要扫描的数据量,加快了查询速度
  • 减少服务器的排序操作和创建临时表的操作(加快了groupby和orderby等操作)
  • 将服务器的随机IO变为顺序IO(加快查询速度).

索引缺点

  • 索引占用磁盘或者内存空间
  • 减慢了插入更新操作的速度

实际上,在一定数据范围内(索引没有超级多的情况下),建立索引带来的开销是远远小于它带来的好处的,但是我们仍然要防止索引的滥用。

explain出来的各种item的意义

  • id:每个被独立执行的操作的标志,表示对象被操作的顺序。一般来说, id 值大,先被执行;如果 id 值相同,则顺序从上到下。
  • select_type:查询中每个 select 子句的类型。
  • table:名字,被操作的对象名称,通常的表名(或者别名),但是也有其他格式。
  • partitions:匹配的分区信息。
  • type:join 类型。
  • possible_keys:列出可能会用到的索引。
  • key:实际用到的索引。
  • key_len:用到的索引键的平均长度,单位为字节。
  • ref:表示本行被操作的对象的参照对象,可能是一个常量用 const 表示,也可能是其他表的key 指向的对象,比如说驱动表的连接列。
  • rows:估计每次需要扫描的行数。
  • filtered:rows*filtered/100 表示该步骤最后得到的行数(估计值)。
  • extra:重要的补充信息。

根据加锁的范围,MySQL里面的锁大致可以分成全局锁、表锁和行锁三类。

全局锁

顾名思义,全局锁就是对整个数据库实例加锁。

MySQL提供了一个加全局读锁的方法,命令是

Flush tables with read lock (FTWRL)。

当你需要让整个库处于只读状态的时候,可以使用这个命令,之后其他线程的以下语句会被阻塞:数据更新语句(数据的增删改)、数据定义语句(包括建表、修改表结构等)和更新类事务的提交语句。

典型使用场景是,做全库逻辑备份。

官方自带的逻辑备份工具是mysqldump。当mysqldump使用参数–single-transaction的时候,导数据之前就会启动一个事务,来确保拿到一致性视图。而由于MVCC的支持,这个过程中数据是可以正常更新的。

表锁

MySQL里面表级别的锁有两种:一种是表锁,一种是元数据锁(meta data lock,MDL)。

表锁的语法是

lock tables … read/write

与FTWRL类似,可以用unlock tables主动释放锁,也可以在客户端断开的时候自动释放。需要注意,lock tables语法除了会限制别的线程的读写外,也限定了本线程接下来的操作对象。

举个例子, 如果在某个线程A中执行

lock tables t1 read, t2 write

这个语句,则其他线程写t1、读写t2的语句都会被阻塞。同时,线程A在执行unlock tables之前,也只能执行读t1、读写t2的操作。连写t1都不允许,自然也不能访问其他表。

在还没有出现更细粒度的锁的时候,表锁是最常用的处理并发的方式。而对于InnoDB这种支持行锁的引擎,一般不使用lock tables命令来控制并发,毕竟锁住整个表的影响面还是太大。

另一类表级的锁是MDL(metadata lock)。

MDL不需要显式使用,在访问一个表的时候会被自动加上。

MDL的作用是,保证读写的正确性。你可以想象一下,如果一个查询正在遍历一个表中的数据,而执行期间另一个线程对这个表结构做变更,删了一列,那么查询线程拿到的结果跟表结构对不上,肯定是不行的。

因此,在MySQL 5.5版本中引入了MDL,当对一个表做增删改查操作的时候,加MDL读锁;当要对表做结构变更操作的时候,加MDL写锁。读锁之间不互斥,因此你可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的,用来保证变更表结构操作的安全性。因此,如果有两个线程要同时给一个表加字段,其中一个要等另一个执行完才能开始执行。

虽然MDL锁是系统默认会加的,但却是你不能忽略的一个机制。比如有人掉到这个坑里:给一个小表加个字段,导致整个库挂了。

行锁

MySQL的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁,如MyISAM引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁,对于这种引擎的表,同一张表上任何时刻只能有一个更新在执行,这就会影响到业务并发度。InnoDB是支持行锁的,这也是MyISAM被InnoDB替代的重要原因之一。

在InnoDB事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束时才释放。这个就是两阶段锁协议

间隙锁

gap lock,也就是间隙锁,是innodb行级锁的一种,其他的还有record lock, Next-KeyLocks。

  • 行锁(Record Lock):锁直接加在索引记录上面。
  • 间隙锁(Gap Lock):锁加在不存在的空闲空间,可以是两个索引记录之间,也可能是第一个索引记录之前或最后一个索引之后的空间。
  • Next-Key Lock:行锁与间隙锁组合起来用就叫做Next-Key Lock。
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tb2 values(9,4);
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb2 values(10,4);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into tb2 values(19,4);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into tb2 values(20,4);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into tb2 values(21,4);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into tb2 values(29,4);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into tb2 values(30,4);
Query OK, 1 row affected (0.01 sec)

什么时候会取得gap lock

这和隔离级别有关,只在REPEATABLE READ或以上的隔离级别下的特定操作才会取得gap lock或next-key lock。 locking reads,UPDATE和DELETE时,除了对唯一索引的唯一搜索外都会获取gap锁或next-key锁。即锁住其扫描的范围。

如何防止间隙锁?

  • 尽量采用乐观锁,乐观锁是在php等代码层面的锁,就不会锁住数据库资源

  • 事务中update,where后面的字段尽量带上索引,不然间隙锁的范围很大

  • 尽量不要出现长事务,否则事务中更新订单时间隙锁会被锁很久,另一事务插入订单就会执行很久

  • update表,begin和commit之间的时间不要太长,之间不要写一些慢代码,比如请求第三方接口 5 分表能防止不分表情况下整张表被锁住。分表后是锁住众多表中的其中一张

死锁

所谓死锁: 是指两个或两个以上的进程在执行过程中, 因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等待的进程称为死锁进程。表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB。

当出现死锁以后,有两种策略:

  • 策略1是,直接进入等待,直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置。
  • 策略2是,发起死锁检测,发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on,表示开启这个逻辑。

如何避免死锁?

  1. 操作完之后立即提交事务,特别是在交互式命令行中。比如中途不要去请求第三方接口或者执行一些慢逻辑, 能不放到事务里面的php代码, 就别放到事务里面
  2. 资源一次性分配

一次性锁协议,事务开始时,即一次性申请所有的锁,之后不会再申请任何锁,如果其中某个锁不可用,则整个申请就不成功,事务就不会执行,在事务尾端,一次性释放所有的锁。一次性锁协议不会产生死锁的问题,但事务的并发度不高。

  1. 尽量采用乐观锁,因为悲观锁都是要求mysql锁资源,而乐观锁不是
  2. 采用超时设置,时间越短,锁等待时间越短。到点就会自动超时,不会继续等待锁释放 锁超时设置:

SHOW VARIABLES LIKE ‘innodb_lock_wait_timeout’; SET innodb_lock_wait_timeout=10;

  1. 保证没有长事务,长事务尽量拆分成短事务,以为2个短事务很难重合到一起。就是说,如果一个事务瞬间执行 完毕了,就很好,如果一直没执行完毕, 就很可能另外一个事务冲进来二者重合。就加大死锁几率
  2. 修改多个表或者多个行的时候,将修改的顺序保持一致。死锁是因为锁定资源的顺序刚好相反。如果顺序是一 样的,就不会产生死锁。
  3. 创建索引,可以使创建的锁关联到的数据更少。如果where后面的字段没有索引,哪怕只操作一行数据, 也会 锁整张表, 因为锁是基于索引的。

SQL注入

原理

sql注入的原理是将sql代码伪装到输入参数中,传递到服务器解析并执行的一种攻击手法。也就是说,在一些对server端发起的请求参数中植入一些sql代码,server端在执行sql操作时,会拼接对应参数,同时也将一些sql注入攻击的“sql”拼接起来,导致会执行一些预期之外的操作。

如:

SELECT USERNAME,PASSWORD FROM USER WHERE USERNAME='123456' AND PASSWORD="aaa' or '1'='1";

预防方法

  • sql预编译
  • 严格校验
  • 权限管理

Gorm 的 SQL 预编译

在 Gorm 中,就为我们封装了 SQL 预编译技术,可以供我们使用。

db = db.Where("merchant_id = ?", merchantId)

在执行这样的语句的时候实际上我们就用到了 SQL 预编译技术,其中预编译的 SQL 语句merchant_id = ?和 SQL 查询的数据merchantId将被分开传输至 DB 后端进行处理。

db = db.Where(fmt.Sprintf("merchant_id = %s", merchantId))

而当你使用这种写法时,即表示 SQL 由用户来进行拼装,而不使用预编译技术,随之可能带来的,就是 SQL 注入的风险。

Mysql 端的SQL 预编译

在MySQL中是如何实现预编译的,MySQL在4.1后支持了预编译,其中涉及预编译的指令实例如下

可以通过PREPARE预编译指令,SET传入数据,通过EXECUTE执行命令

mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> SET @a = 3;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @b = 4;                                                   
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt1 USING @a, @b;
+------------+
| hypotenuse |
+------------+
|          5 |
+------------+
1 row in set (0.00 sec)

mysql> DEALLOCATE PREPARE stmt1;                                     
Query OK, 0 rows affected (0.00 sec)

首先我们先简单回顾下客户端使用 Prepare 请求过程:

  1. 客户端发起 Prepare 命令将带 “?” 参数占位符的 SQL 语句发送到数据库,成功后返回 stmtID。
  2. 具体执行 SQL 时,客户端使用之前返回的 stmtID,并带上请求参数发起 Execute 命令来执行 SQL。
  3. 不再需要 Prepare 的语句时,关闭 stmtID 对应的 Prepare 语句。

这里展示不使用 sql 预编译和使用 sql 预编译时的 Mysql 的日志。

2020-06-30T08:14:02.430089Z           10 Query        COMMIT
2020-06-30T08:14:02.432995Z           10 Query        select * from user where merchant_id='123456'

2020-06-30T08:15:10.581287Z           12 Query        COMMIT
2020-06-30T08:15:10.584109Z           12 Prepare        select * from user where merchant_id =?
2020-06-30T08:15:10.584725Z           12 Execute        select * from user where merchant_id ='123456'

参考

关注和赞赏都是对小欧莫大的支持! 🤝 🤝 🤝
公众号