基础知识
数据库范式
- 第一范式(1NF):原子性,不可再分解
- 第二范式(2NF):唯一性,在第一范式基础上,非主键列完全依赖于主键
- 第三范式(3NF):独立性,在第二范式的基础上,非主键列依赖于主键,不依赖于其他非主键,消除传递依赖
- 其他:巴斯-科德范式(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的大多数核心服务功能,以及所有的内置函数(如日期、时间、数学和加密函数等),所有跨存储引擎的功能都在这一层实现,比如存储过程、触发器、视图等。
查询执行流程如下:
-
连接到数据库,这时候接待你的就是连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接。
-
查询缓存。MySQL拿到一个查询请求后,会先到查询缓存看看,如果命中,那么这个value就会被直接返回给客户端,这里也会做一个权限验证。
-
如果没有命中查询缓存,就要开始真正执行语句了。分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条SQL语句,MySQL需要识别出里面的字符串分别是什么,代表什么。
-
根据词法分析的结果,语法分析器会根据语法规则,判断你输入的这个SQL语句是否满足MySQL语法。
如果你的语句不对,就会收到“You have an error in your SQL syntax”的错误提醒。
-
经过了分析器,MySQL就知道你要做什么了。在开始执行之前,还要先经过优化器的处理。
优化器是在表里面有多个索引的时候,决定使用哪个索引;或者在一个语句有多表关联(join)的时候,决定各个表的连接顺序。
-
MySQL通过分析器知道了你要做什么,通过优化器知道了该怎么做,于是就进入了执行器阶段,开始执行语句返回结果。
开始执行的时候,要先判断一下你对这个表有没有执行查询的权限,如果没有,就会返回没有权限的错误。如果有权限,就打开表继续执行。打开表的时候,执行器就会根据表的引擎定义,去使用这个引擎提供的接口。
需要注意:
- MySQL 8.0版本直接将查询缓存的整块功能删掉了MySQL 8.0:不再支持查询缓存
- 虽然优化器会自动使用索引,但有时候选取的索引未必正确
存储引擎对比
常见:InnoDB、MyISAM;其他:Archive、CSV、Memory等
对比项 | MyISAM | InnoDB |
---|---|---|
外键 | 不支持 | 支持 |
事务 | 不支持 | 支持 |
锁 | 表锁 | 行锁 |
缓存 | 只缓存索引,不缓存真实数据 | 不仅缓存索引还要缓存真实数据,对内存要求较高,而且内存大小对性能有决定性的影响 |
关注点 | 节省资源,消耗少,简单业务,性能 | 并发写,事务,更大资源 |
默认安装 | 是 | 是 |
默认使用 | 否 | 是 |
自动系统表使用 | 是 | 否 |
应用场景:
-
MyISAM管理非事务表,提供高速存储和检索以及全文搜索能力,如果再应用中执行大量select操作,应该选择MyISAM
-
InnoDB用于事务处理,具有ACID事务支持等特性,如果在应用中执行大量insert和update操作,应该选择InnoDB
索引
从底层实现来说,索引类型包括 B-Tree(InnoDB引擎)、哈希索引(Memory引擎)、空间数据索引(R-Tree, MyISAM引擎支持)、全文索引等。
从索引的实现上,我们可以将其分为聚集索引与非聚集索引,或称辅助索引或二级索引。
从索引的实际应用中,又可以细分为普通索引、唯一索引、主键索引、联合索引、外键索引、全文索引这几种。
建议看以下文章了解索引使用和底层实现:
B树的特点
1个 m 阶的B树满足以下条件:
- 每个结点至多拥有m棵子树;
- 根结点至少拥有两颗子树(存在子树的情况下),根结点至少有一个关键字;
- 除了根结点以外,其余每个分支结点至少拥有 m/2 棵子树;
- 所有的叶结点都在同一层上,B树的叶子结点可以看成是一种外部节点,不包含任何信息;
- 有 k 棵子树的分支结点则存在 k-1 个关键码,关键码按照递增次序进行排列;
- 关键字数量需要满足ceil(m/2)-1 <= n <= m-1;
更多请看这篇: B树和B+树的总结
B树和B+树的区别
这都是由于B+树和B树具有不同的存储结构所造成的区别,以一个m阶树为例。
- 关键字的数量不同;B+树中分支结点有m个关键字,其叶子结点也有m个,其关键字只是起到了一个索引的作用,虽然B树也有m个子结点,但是其只拥有m-1个关键字。
- 存储的位置不同;B+树中的数据都存储在叶子结点上,也就是其所有叶子结点的数据组合起来就是完整的数据,但是B树的数据存储在每一个结点中,并不仅仅存储在叶子结点上。
- 分支结点的构造不同;B+树的分支结点存储着关键字信息和儿子的指针(这里的指针指的是磁盘块的偏移量),也就是说内部结点仅仅包含着索引信息。
- 查询不同;B树在找到具体的数值以后就结束,而B+树则需要通过索引找到叶子结点中的数据才结束,也就是说B+树的搜索过程中走了一条从根结点到叶子结点的路径,其高度是相同的,相对来说更加的稳定;
- 区间访问: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,表示开启这个逻辑。
如何避免死锁?
- 操作完之后立即提交事务,特别是在交互式命令行中。比如中途不要去请求第三方接口或者执行一些慢逻辑, 能不放到事务里面的php代码, 就别放到事务里面
- 资源一次性分配
一次性锁协议,事务开始时,即一次性申请所有的锁,之后不会再申请任何锁,如果其中某个锁不可用,则整个申请就不成功,事务就不会执行,在事务尾端,一次性释放所有的锁。一次性锁协议不会产生死锁的问题,但事务的并发度不高。
- 尽量采用乐观锁,因为悲观锁都是要求mysql锁资源,而乐观锁不是
- 采用超时设置,时间越短,锁等待时间越短。到点就会自动超时,不会继续等待锁释放 锁超时设置:
SHOW VARIABLES LIKE ‘innodb_lock_wait_timeout’; SET innodb_lock_wait_timeout=10;
- 保证没有长事务,长事务尽量拆分成短事务,以为2个短事务很难重合到一起。就是说,如果一个事务瞬间执行 完毕了,就很好,如果一直没执行完毕, 就很可能另外一个事务冲进来二者重合。就加大死锁几率
- 修改多个表或者多个行的时候,将修改的顺序保持一致。死锁是因为锁定资源的顺序刚好相反。如果顺序是一 样的,就不会产生死锁。
- 创建索引,可以使创建的锁关联到的数据更少。如果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 请求过程:
- 客户端发起 Prepare 命令将带 “?” 参数占位符的 SQL 语句发送到数据库,成功后返回 stmtID。
- 具体执行 SQL 时,客户端使用之前返回的 stmtID,并带上请求参数发起 Execute 命令来执行 SQL。
- 不再需要 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'
参考
关注和赞赏都是对小欧莫大的支持! 🤝 🤝 🤝