mysql 学习笔记

Posted on | In
Words count in article: 5.7k | Reading time ≈ 21

最近在面试时遇到了一些数据库相关的问题，补下一些数据库相关的一些基本知识

索引的本质

索引的疑问

  • 为什么要给表加上主键？
  • 为什么加索引后会使查询变快？
  • 为什么加索引后会使写入、修改、删除变慢？
  • 什么情况下要同时在两个字段上建索引？

索引的本质

索引本质上是一棵平衡树（b tree或者b+ tree）。当然， 有的数据库也使用哈希桶作用索引的数据结构 ， 然而， 主流的RDBMS都是把平衡树当做数据表默认的索引数据结构的。

我们平时建表时都会为表添加主键。一个拥有主键的表的实质结构如下:
clustered-index-b-tree-img

B+树的特点

  • InnoDB使用的是B+Tree
  • B+Tree：每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。
  • B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同，很适合查找范围数据。
  • B-Tree可以对<，<=，=，>，>=，BETWEEN，IN，以及不以通配符开始的LIKE使用索引

MySQL–SQL语句执行流程

mysql-sql-exec-flow

Oracle–SQL语句执行流程

  • 语法检查，例如SQL的拼写

  • 语义检查，例如sql中的对象是否存在及相关的权限

  • sql解析，对sql进行语法解析，生成解析树及执行计划

    • 硬解析 包括语法和语义分析，查看共享池是否有解析好的结果，然后选择执行计划和生成执行计划，执行sql
    • 软解析，如果在共享池中有解析好的结果，直接执行sql

说明：创建解析树、生成执行计划对于sql的执行来说是开销昂贵的动作，所以，应当极力避免硬解析，尽量使用软解析。这就是在很多项目中，倡导开发设计人员对功能相同的代码要努力保持代码的一致性，以及要在程序中多使用绑定变量的原因。

  • 执行sql

硬解析

  • 对SQL语句进行语法检查，看是否有语法错误。比如select from where 等的拼写错误，如果存在语法错误，则退出解析过程
  • 通过数据字典(row cache)，检查SQL语句中涉及的对象和列是否存在。如果不存在，则退出解析过程
  • 检查SQL语句的用户是否对涉及到的对象是否有权限。如果没有则退出解析
  • 通过优化器创建一个最优的执行计划。这个过程会根据数据字典中的对象的统计信息，来计算多个执行计划的cost，从而得到一个最优的执行计划。这一步涉及到大量的数据运算，从而会消耗大量的CPU资源；(library cache最主要的目的就是通过软解析来减少这个步骤)
  • 将该游标所产生的执行计划，SQL文本等装载进library cache中的heap中

软解析

  • 所谓软解析，就是因为相同文本的SQL语句存在于library cache中，所以本次SQL语句的解析就可以去掉硬解析中的一个或多个步骤。从而节省大量的资源的耗费

MySQL expalin

表结构如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE `user_info` (
  `id`   BIGINT(20)  NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL DEFAULT '',
  `age`  INT(11)              DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_index` (`name`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

CREATE TABLE `order_info` (
  `id`           BIGINT(20)  NOT NULL AUTO_INCREMENT,
  `user_id`      BIGINT(20)           DEFAULT NULL,
  `product_name` VARCHAR(50) NOT NULL DEFAULT '',
  `productor`    VARCHAR(30)          DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

explain命令使用

  • 使用样例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> explain select * from user_info where id = 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)
  • 各个字段的含义
1
2
3
4
5
6
7
8
9
10
11
id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
select_type: SELECT 查询的类型.
table: 查询的是哪个表
partitions: 匹配的分区
type: join 类型
possible_keys: 此次查询中可能选用的索引
key: 此次查询中确切使用到的索引.
ref: 哪个字段或常数与 key 一起被使用
rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
filtered: 表示此查询条件所过滤的数据的百分比
extra: 额外的信息

select_type的取值

1
2
3
4
5
6
7
SIMPLE: 表示此查询不包含 UNION 查询或子查询
PRIMARY: 表示此查询是最外层的查询
UNION: 表示此查询是 UNION 的第二或随后的查询
DEPENDENT UNION: UNION 中的第二个或后面的查询语句, 取决于外面的查询
UNION RESULT: UNION 的结果
SUBQUERY: 子查询中的第一个 SELECT
DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果

type 常用类型

  • system
    表中只有一条数据. 这个类型是特殊的 const 类型.

  • const
    针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可

  • eq_ref
    此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =, 查询效率较高. 例如:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    mysql> EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id\G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: order_info
       partitions: NULL
             type: index
    possible_keys: user_product_detail_index
              key: user_product_detail_index
          key_len: 254
              ref: NULL
             rows: 9
         filtered: 100.00
            Extra: Using where; Using index
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: user_info
       partitions: NULL
             type: eq_ref
    possible_keys: PRIMARY
              key: PRIMARY
          key_len: 8
              ref: db_test.order_info.user_id
             rows: 1
         filtered: 100.00
            Extra: NULL
    2 rows in set, 1 warning (0.00 sec)

  • ref
    此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了 最左前缀 规则索引的查询.
    例如下面这个例子中, 就使用到了 ref 类型的查询:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    mysql> EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id AND order_info.user_id = 5\G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: user_info
       partitions: NULL
             type: const
    possible_keys: PRIMARY
              key: PRIMARY
          key_len: 8
              ref: const
             rows: 1
         filtered: 100.00
            Extra: NULL
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: order_info
       partitions: NULL
             type: ref
    possible_keys: user_product_detail_index
              key: user_product_detail_index
          key_len: 9
              ref: const
             rows: 1
         filtered: 100.00
            Extra: Using index
    2 rows in set, 1 warning (0.00 sec)

  • range
    表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中.
    当 type 是 range 时, 那么 EXPLAIN 输出的 ref 字段为 NULL, 并且 key_len 字段是此次查询中使用到的索引的最长的那个.

    1
    2
    3
    4
    5
    6
    7
    mysql> EXPLAIN SELECT * FROM user_info WHERE id BETWEEN 2 AND 8;
    +----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
    | id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | user_info | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL |    7 |   100.00 | Using where |
    +----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)

  • index
    表示全索引扫描(full index scan), 和 ALL 类型类似, 只不过 ALL 类型是全表扫描, 而 index 类型则仅仅扫描所有的索引, 而不扫描数据.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    mysql> EXPLAIN SELECT name FROM  user_info \G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: user_info
       partitions: NULL
             type: index
    possible_keys: NULL
              key: name_index
          key_len: 152
              ref: NULL
             rows: 10
         filtered: 100.00
            Extra: Using index
    1 row in set, 1 warning (0.00 sec)

  • ALL
    表示全表扫描, 这个类型的查询是性能最差的查询之一. 通常来说, 我们的查询不应该出现 ALL 类型的查询, 因为这样的查询在数据量大的情况下, 对数据库的性能是巨大的灾难. 如一个查询是 ALL 类型查询, 那么一般来说可以对相应的字段添加索引来避免.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    mysql> EXPLAIN SELECT age FROM  user_info WHERE age = 20 \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: user_info
       partitions: NULL
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 10
         filtered: 10.00
            Extra: Using where
    1 row in set, 1 warning (0.00 sec)

联合索引与单列索引的使用

  • 多个单列索引在多条件查询时优化器会选择最优索引策略，可能只用一个索引，也可能将多个索引全用上！ 但多个单列索引底层会建立多个B+索引树，比较占用空间，也会浪费一定搜索效率，故如果只有多条件联合查询时最好建联合索引
  • 最左优先，以最左边的为起点任何连续的索引都能匹配上
    注：如果第一个字段是范围查询需要单独建一个索引
    注：在创建联合索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。这样的话扩展性较好，比如 userid 经常需要作为查询条件，而 mobile 不常常用，则需要把 userid 放在联合索引的第一位置，即最左边
  • 同时存在联合索引和单列索引（字段有重复的），这个时候查询mysql会怎么用索引呢？
    这个涉及到mysql本身的查询优化器策略了，当一个表有多条索引可走时, Mysql 根据查询语句的成本来选择走哪条索引；
  • 联合索引本质
    当创建(a,b,c)联合索引时,相当于创建了(a)单列索引(a,b)联合索引以及(a,b,c)联合索引.想要索引生效的话,只能使用 a和a,b和a,b,c三种组合
  • 需要加索引的字段，要在where条件中
  • 数据量少的字段不需要加索引；因为建索引有一定开销，如果数据量小则没必要建索引（速度反而慢）
  • 避免在where子句中使用or来连接条件,因为如果俩个字段中有一个没有索引的话,引擎会放弃索引而产生全表扫描
  • 联合索引比对每个列分别建索引更有优势，因为索引建立得越多就越占磁盘空间，在更新数据的时候速度会更慢。另外建立多列索引时，顺序也是需要注意的，应该将严格的索引放在前面，这样筛选的力度会更大，效率更高

慢日志日志记录

查看当前是否已经开启慢查询

1
2
3
4
5
6
7
8
9
mysql> show variables like 'slow_query%';

+---------------------+--------------------------+
| Variable_name       | Value                    |
+---------------------+--------------------------+
| slow_query_log      | ON                       |
| slow_query_log_file | STUPIDWOLF-CHEN-slow.log |
+---------------------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

开启慢查询日志记录

  • 方式1(永久方式)：修改配置文件mysql.conf
1
2
3
4
5
6
# 是否开启慢查询日志服务. 开启:ON 关闭:OFF
slow_query_log=ON
# 慢查询日志文件的保存路径
slow_query_log_file=/var/lib/mysql/instance-1-slow.log
# 查询符合慢查询的条件，单位为秒，表示当一个查询消耗的时间超过2秒时，即视该查询为慢查询
long_query_time=2
  • 方式2(临时方式)
1
2
3
set global slow_query_log='ON';
set global slow_query_log_file='/var/logs/mysql/instance-1-slow.log';
set global long_query_time=2;

测试慢查询开启之后的效果

模拟慢查询,运行下面的sql

1
select sleep(11);

查看相应的日志文件,可看到如下信息,在日志上可以看到慢查询对应的sql语句等些信息

1
2
3
4
5
6
# Time: 2019-09-13T08:03:10.144425Z
# User@Host: root[root] @ localhost [::1]  Id:    18
# Query_time: 10.999789  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use db_test;
SET timestamp=1568361790;
select sleep(11);

SQL注入

引发SQL注入的根本原因是恶意用户将sql指令伪装成参数传递到后台数据库执行。

  • 使用预编译语句，预编译语句使用参数占位符来替代需要动态传入的参数，使得攻击者无法改变SQL语句的结构，SQL语义不会发生改变
  • 使用ORM框架，常见的ORM框架一般都有对一些关键字进行转义处理
  • 避免密码铭文存放
  • 处理好异常系统异常，在后台系统发生异常时，防止将异常信息执行直接在页面上输出

Mybatis$与#变量的区别

  • #{ } 解析为一个 JDBC 预编译语句（prepared statement）的参数标记符；一个 #{ } 被解析为一个参数占位符 ?
  • ${ } 仅仅为一个纯碎的 string 替换，在动态 SQL 解析阶段将会进行变量替换。${ } 的变量的替换阶段是在动态 SQL 解析阶段，而 #{ }的变量的替换是在 DBMS 中。
  • #方式能够很大程度防止sql注入。$方式无法防止Sql注入。$方式一般用于传入数据库对象，例如传入表名.以及MyBatis排序时使用order by 动态参数时，用$而不是#

Spring 事务

事务

事务指的是满足 ACID 特性的一组操作，可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚

  • 原子性（Atomicity）

事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。回滚可以用回滚日志来实现，回滚日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可

  • 一致性（Consistency）

数据库在事务执行前后都保持一致性状态。在一致性状态下，所有事务对一个数据的读取结果都是相同的

  • 隔离性（Isolation）

一个事务所做的修改在最终提交以前，对其它事务是不可见的

  • 持久性（Durability）

一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。使用重做日志来保证持久性

事务ACID特性的理解：

  • 只有满足一致性，事务的执行结果才是正确的
  • 在无并发的情况下，事务串行执行，隔离性一定能够满足。此时只要能满足原子性，就一定能满足一致性
  • 在并发的情况下，多个事务并行执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性
  • 事务满足持久化是为了能应对数据库崩溃的情况

MySQL 默认采用自动提交模式。也就是说，如果不显式使用START TRANSACTION语句来开始一个事务，那么每个查询都会被当做一个事务自动提交

事务隔离级别

  • 未提交读（READ UNCOMMITTED）

事务中的修改，即使没有提交，对其它事务也是可见的

  • 提交读（READ COMMITTED）

一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的

  • 可重复读（REPEATABLE READ）

保证在同一个事务中多次读取同样数据的结果是一样的

  • 可串行化（SERIALIZABLE）
    强制事务串行执行。需要加锁实现，而其它隔离级别通常不需要

事务隔离级别的控制

多版本并发控制（Multi-Version Concurrency Control, MVCC）是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现

  • 版本号

    • 系统版本号：是一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。
    • 事务版本号：事务开始时的系统版本号

  • 隐藏的列

    • 创建版本号：指示创建一个数据行的快照时的系统版本号
    • 删除版本号：如果该快照的删除版本号大于当前事务版本号表示该快照有效，否则表示该快照已经被删除了

  • Undo 日志
    MVCC 使用到的快照存储在 Undo 日志中，该日志通过回滚指针把一个数据行（Record）的所有快照连接起来
    undo-log-img

  • 可重复读隔离级别

    当开始一个事务时，该事务的版本号肯定大于当前所有数据行快照的创建版本号，理解这一点很关键。数据行快照的创建版本号是创建数据行快照时的系统版本号，系统版本号随着创建事务而递增，因此新建一个事务时，这个事务的系统版本号比之前的系统版本号都大，也就是比所有数据行快照的创建版本号都大

    • SELECT

      多个事务必须读取到同一个数据行的快照，并且这个快照是距离现在最近的一个有效快照。但是也有例外，如果有一个事务正在修改该数据行，那么它可以读取事务本身所做的修改，而不用和其它事务的读取结果一致。
      把没有对一个数据行做修改的事务称为 T，T 所要读取的数据行快照的创建版本号必须小于等于 T 的版本号，因为如果大于 T 的版本号，那么表示该数据行快照是其它事务的最新修改，因此不能去读取它。除此之外，T 所要读取的数据行快照的删除版本号必须是未定义或者大于 T 的版本号，因为如果小于等于 T 的版本号，那么表示该数据行快照是已经被删除的，不应该去读取它

    • INSERT

      将当前系统版本号作为数据行快照的创建版本号

    • DELETE

      将当前系统版本号作为数据行快照的删除版本号

    • UPDATE

      将当前系统版本号作为更新前的数据行快照的删除版本号，并将当前系统版本号作为更新后的数据行快照的创建版本号。可以理解为先执行 DELETE 后执行 INSERT

  • 幻影读 – Next-Key Locks

Next-Key Locks 是 MySQL 的 InnoDB 存储引擎的一种锁实现。
MVCC 不能解决幻影读问题，Next-Key Locks 就是为了解决这个问题而存在的。在可重复读（REPEATABLE READ）隔离级别下，使用 MVCC + Next-Key Locks 可以解决幻读问题

  • Record Locks

锁定一个记录上的索引，而不是记录本身

  • Gap Locks

锁定索引之间的间隙，但是不包含索引本身。例如当一个事务执行以下语句，其它事务就不能在 t.c 中插入 15

  • Next-Key Locks

它是 Record Locks 和 Gap Locks 的结合，不仅锁定一个记录上的索引，也锁定索引之间的间隙

Spring 事务隔离级别

隔离级别是指若干个并发的事务之间的隔离程度。TransactionDefinition 接口中定义了五个表示隔离级别的常量：

  • TransactionDefinition.ISOLATION_DEFAULT：这是默认值，表示使用底层数据库的默认隔离级别。对大部分数据库而言，通常这值就是TransactionDefinition.ISOLATION_READ_COMMITTED

  • TransactionDefinition.ISOLATION_READ_UNCOMMITTED：该隔离级别表示一个事务可以读取另一个事务修改但还没有提交的数据。该级别不能防止脏读和不可重复读，因此很少使用该隔离级别

  • TransactionDefinition.ISOLATION_READ_COMMITTED：该隔离级别表示一个事务只能读取另一个事务已经提交的数据。该级别可以防止脏读，这也是大多数情况下的推荐值

  • TransactionDefinition.ISOLATION_REPEATABLE_READ：该隔离级别表示一个事务在整个过程中可以多次重复执行某个查询，并且每次返回的记录都相同。即使在多次查询之间有新增的数据满足该查询，这些新增的记录也会被忽略。该级别可以防止脏读和不可重复读

  • TransactionDefinition.ISOLATION_SERIALIZABLE：所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。但是这将严重影响程序的性能。通常情况下也不会用到该级别

事务传播行为

所谓事务的传播行为是指，如果在开始当前事务之前，一个事务上下文已经存在，此时有若干选项可以指定一个事务性方法的执行行为。在TransactionDefinition定义中包括了如下几个表示传播行为的常量：

  • TransactionDefinition.PROPAGATION_REQUIRED：如果当前存在事务，则加入该事务；如果当前没有事务，则创建一个新的事务

  • TransactionDefinition.PROPAGATION_REQUIRES_NEW：创建一个新的事务，如果当前存在事务，则把当前事务挂起

  • TransactionDefinition.PROPAGATION_SUPPORTS：如果当前存在事务，则加入该事务；如果当前没有事务，则以非事务的方式继续运行

  • TransactionDefinition.PROPAGATION_NOT_SUPPORTED：以非事务方式运行，如果当前存在事务，则把当前事务挂起

  • TransactionDefinition.PROPAGATION_NEVER：以非事务方式运行，如果当前存在事务，则抛出异常

  • TransactionDefinition.PROPAGATION_MANDATORY：如果当前存在事务，则加入该事务；如果当前没有事务，则抛出异常

  • TransactionDefinition.PROPAGATION_NESTED：如果当前存在事务，则创建一个事务作为当前事务的嵌套事务来运行；如果当前没有事务，则该取值等价于TransactionDefinition.PROPAGATION_REQUIRED

事务传播行为总结

  • NESTED和REQUIRED修饰的内部方法都属于外围方法事务，如果外围方法抛出异常，这两种方法的事务都会被回滚。但是REQUIRED是加入外围方法事务，所以和外围事务同属于一个事务，一旦REQUIRED事务抛出异常被回滚，外围方法事务也将被回滚。而NESTED是外围方法的子事务，有单独的保存点，所以NESTED方法抛出异常被回滚，不会影响到外围方法的事务

  • NESTED和REQUIRES_NEW都可以做到内部方法事务回滚而不影响外围方法事务。但是因为NESTED是嵌套事务，所以外围方法回滚之后，作为外围方法事务的子事务也会被回滚。而REQUIRES_NEW是通过开启新的事务实现的，内部事务和外围事务是两个事务，外围事务回滚不会影响内部事务

参考