数据库

宋正兵 on 2021-06-22

杂项

关系型数据库

关系型数据库就是一种建立在关系模型的基础上的数据库。关系模型表明了数据库中所存储的数据之间的联系(一对一、一对多和多对多)。

大部分关系型数据库都使用 SQL 来操作数据库中的数据,并且大部分关系型数据库都支持事务的四大特性(ACID)。

常见的关系型数据库

MySQL、PostgreSQL、Oracle、SQL Server、SQLite

MySQL 是一种关系型数据库,主要用于持久化存储系统中的一些数据,比如用户信息。

默认端口号是 3306。

范式

范式是“符合某一种级别的关系模式的集合,表示一个关系内部各属性之间的联系的合理化程度”。说人话就是“创建一个数据库的过程中,必须依照的准则”。

遵循范式,可以减少数据库中的数据冗余,增加数据的一致性。

第一范式 1NF :保证每列的原子性

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值,就说明满足了第一范式。

比如有一列 user_info:四川省成都市 123456789,那么遵循第一范式它应该被分解为 user_address:四川省成都市user_phone:123456789

第二范式 2NF :满足 1NF 后,要求表中的所有列,都必须依赖于主键,而不能有任何一列与主键没有关系。

通俗的讲法就是保证一张表只描述一件事情。

比如订单表只描述订单相关的信息,所有字段都必须与主键订单 id 相关;产品表只描述产品相关的信息,所有字段都必须与主键产品 id 相关。意味着不能够在一张表中同时出现订单信息与产品信息。

第三范式 3NF :保证每列都和主键直接相关

概念-第一范式第二范式第三范式 - 简书 (jianshu.com)

存储引擎

相关命令

查看 MySQL 提供的所有存储引擎

1
show engines;

MySQL 目前最新版的默认存储引擎为 InnoDB,并且只有 InnoDB 支持事务。

查看 MySQL 当前默认的存储引擎

1
show variables like '%storage_engine';

查看表的存储引擎

1
show table status like "table_name" ;

MyISAM 和 InnoDB 的区别

MySQL 5.5 之前,MyISAM 引擎是 MySQL 的默认存储引擎。之后的版本,默认的存储引擎改为了 InnoDB。

  • 是否支持行级锁

    MyISAM 只有表级锁;而 InnoDB 支持行级锁和表级锁,默认为行级锁。也就是 MyISAM 一锁就锁住了整张表,这也是为什么 InnoDB 在并发写的时候性能更高。

  • 是否支持事务

    MyISAM 不提供事务支持;InnoDB 提供事务支持,具有提交和回滚事务的能力。

  • 是否支持外键

    MyISAM 不支持;InnoDB 支持。

  • 是否支持数据库异常崩溃后的安全恢复

    MyISAM 不支持;InnoDB 支持。InnoDB 数据库在崩溃异常后,数据库重新启动的时候会利用 redo log 来保证数据库恢复到崩溃前的状态。

  • 是否支持 MVCC

    MyISAM 不支持;InnoDB 支持。

回滚日志undo log 和重做日志 redo log

回滚日志 undo log 主要用于记录数据被修改之前的日志,在表信息修改之前先会把数据拷贝到 undo log 里,当事务进行回滚时可以通过 undo log 里的日志进行数据还原。

重做日志 redo log 主要用于在进行日志回放的时候把已经 COMMIT 的事务重做一遍,对于没有 commit 交由 undo log 进行数据回滚操作。为了保证数据能正确的持久化,在系统出现异常的时候通常会对 redo log 进行回放,把已经 commit 的事务进行数据重做。

如何实现回滚?

在 MySQL 中,恢复机制是通过**回滚日志(undo log)**来实现的,所有事务进行的修改都会先记录到这个回滚日志中,然后再执行相关的操作。

如果执行过程中遇到异常,直接利用回滚日志中的信息将数据回滚到修改之前的样子即可。并且由于回滚日志会先于数据持久化到磁盘上,这就保证了即使遇到数据库突然宕机,当用户再次启动数据库时,数据库还能够通过查询回滚日志来完成之前没有完成的事务。

MySQL中的日志

回滚日志 undo log, 主要用于记录数据被修改之前的日志,在表信息修改之前先会把数据拷贝到 undo log 里,当事务进行回滚时可以通过 undo log 里的日志进行数据还原。【用于数据修改的回滚,记录的是逻辑日志,delete 一条记录时,undo log 中会记录一条对那个的 insert 记录】

重做日志 redo log, 主要用于在进行日志回放的时候把已经 COMMIT 的事务重做一遍,对于没有 commit 交由 undo log 进行数据回滚操作。为了保证数据能正确的持久化,在系统出现异常的时候通常会对 redo log 进行回放,把已经 commit 的事务进行数据重做。【用于数据的灾后重新提交,记录的是物理页的修改】

错误日志 error log,主要用于记录 mysqld 启动和停止,以及服务器在运行过程中发生的错误信息及警告相关信息。当数据库意外宕机或发生其他错误时,我们应该去排查错误日志。

慢查询日志 show query log,主要用来记录执行时间超过 long_query_time 这个变量所定义时长的查询语句。通过慢查询日志可以查出哪些语句的执行效率低,以便进行优化。

一般查询日志、通用查询日志 general log,主要用于记录 mysqld 中所有相关操作,是 MySQL 中记录最详细的日志。

二进制日志 bin log,主要用于记录数据库所有执行的 DDL 和 DML 语句(除了数据查询语句 select、show 等),以事件形式记录并保存在二进制文件中。

Innodb中的事务隔离级别和锁的关系

Innodb中的事务隔离级别和锁的关系

两段锁

在有大量的并发访问时,为了预防死锁,一般应用中推荐使用一次封锁法,就是在方法的开始阶段,已经预先知道会用到哪些数据,然后全部锁住,在方法运行之后,再全部解锁。这种方式可以有效的避免循环死锁,但在数据库中却不使用,因为在事务开始阶段,数据库并不知道会用到哪些数据。

数据库遵循的是两段锁协议,将事务分成两个阶段,加锁阶段和解锁阶段。(所以叫两段锁)

  • 加锁阶段:在对任何数据进行读操作之前要申请并获得 S 锁(共享锁,其他事务可以继续加共享锁,但不能加排它锁),在进行写操作之前需要申请并获得 X 锁(排它锁,其他事务不能再获得任何锁)。加锁不成功,则事务进入等待状态,直到加锁成功才继续执行。
  • 解锁阶段:当事务释放了一个锁后,事务进入解锁阶段,在该阶段只能进行解锁操作不能再进行加锁操作。
事务 加锁/解锁处理
begin;
insert into test …… 加insert对应的锁
update test set… 加update对应的锁
delete from test …. 加delete对应的锁
commit; 事务提交时,同时释放insert、update、delete对应的锁

事务的四种隔离级别

在数据库操作中,为了有效保证并发读取数据的正确性,提出的事务隔离级别。数据库锁,也是为了构建这些隔离级别存在的。

隔离级别 脏读(Dirty Read) 不可重复读(NonRepeatable Read) 幻读(Phantom Read)
未提交读(Read uncommitted) 可能 可能 可能
已提交读(Read committed) 不可能 可能 可能
可重复读(Repeatable read) 不可能 不可能 可能
可串行化(Serializable ) 不可能 不可能 不可能
  • 未提交读(Read uncommitted):允许脏读,也就是可能读取到其他会话中未提交事务修改的数据。
  • 已提交读(Read committed):只能读取到已经提交的数据。Oracle 等多数数据库默认都是该级别。
  • 可重复读(Repeatable read):在同一个事务内的查询都和事务开始时刻一致的。InnoDB 默认级别。
  • 可串行化(Serializable ):完全串行化的读,每次读都需要获得表级共享锁,读写都会相互阻塞。

MySQL中锁的种类

表级锁,是锁定粒度最大的一种锁,锁冲突概率高、并发度低。好处是不会出现死锁,开销小,获取和释放锁的速度快。适用以查询为主,少量更新的应用。

  • 表读锁(Table Read Lock)
  • 表写锁(Table Write Lock)

在表读锁和表写锁的环境下,表读锁不会阻塞其他用户对同一表的读操作,但是会阻塞对同一表的写操作;表写锁会阻塞其他用户对同一表的读和写操作,即只有持有表写锁的用户才可以对表进行更新操作。

**表读锁和表写锁是互斥的,读写操作是串行的。**如果某个进程想要获取表读锁,同时另外一个进程想要获取表写锁,在 MySQL 中,表写锁优先于表读锁。也可以通过调节参数 low-priority-updates 给予读请求优先的权力。

行级锁,是锁定粒度最小的一种锁,锁冲突概率低、并发度高。缺点是容易发生死锁,开销大,加锁慢。InnoDB 行级锁类型:

  • 共享锁(S),又称读锁,多个事务对同一数据共享一把锁,都能访问到数据,但是只能读不能修改。
  • 排他锁(X),又称写锁,不能与其他锁并存。如果一个事务获取了一个数据行的排他锁,其他事务就不可以对数据进行读取和修改。
  • 意向共享锁(IS),事务给一个数据行加共享锁前必须先取得该表的 IS 锁。
  • 意向排他锁(IX),事务给一个数据行加排他锁前必须先取得该表的 IX 锁。

MySQL 一般都是用行锁来处理并发事务的,这里也主要讨论的就是行锁。

Read Committed(读取已提交内容)

在 RC 级别中,数据的读取都是不加锁的,但是数据的写入、修改和删除是需要加锁的。

假设有一张表,有索引 id,事务 A 和事务 B 开启事务后同时对 id=1 的数据行进行更新操作。为了防止并发过程中的修改冲突,事务 A 会对 id=1 的数据行加锁,如果一直没有 commit(释放锁),那么事务 B 也就一直拿不到该行锁,wait 直到超时。

image0163ec691bfa07f8.png

如果 id 没有索引的话,更新操作是对整张表加锁。因为在 sql 运行的过程中,MySQL 并不知道哪些数据行是 id=1 的(没有索引),如果一个条件无法通过索引快速过滤,存储引擎层面就会将所有记录加锁后返回,再由 MySQL Server 层进行过滤。

Repeatable Read(可重复读)

这是 MySQL 中 InnoDB 引擎默认的隔离级别。分作为“读”和“写”两个模块来讨论。

读就是可重读,即一个事务的多个实例在并发读取数据时,会看到同样的数据行。通过 RC 和 RR 模式的对比来看一下:

RC(不可重读) 模式下的展现

image.png

事务 B 修改 id=1 的数据提交之后,事务 A 同样的查询,后一次和前一次的结果不一样,这就是不可重读(重新读取产生的结果不一样)。这就很可能带来一些问题,那么我们来看看在 RR 级别中 MySQL 的表现:

image28cab39274c96923.png

我们注意到,当 teacher_id=1 时,事务 A 先做了一次读取,事务 B 中间修改了 id=1 的数据,并 commit 之后,事务 A 第二次读到的数据和第一次完全相同。所以说它是可重读的。

不可重复读和幻读的区别

不可重复读重点在于 update 和 delete,而幻读的重点在于 insert。

如果使用锁机制来实现这两种隔离级别,在可重复读中,该 sql 第一次读取到数据后,就将这些数据加锁,其它事务无法修改这些数据,就可以实现可重复读了。但这种方法却无法锁住 insert 的数据,所以当事务 A 先前读取了数据,或者修改了全部数据,事务 B 还是可以 insert 数据提交,这时事务 A 就会发现莫名其妙多了一条之前没有的数据,这就是幻读,不能通过行锁来避免。需要 Serializable 隔离级别 ,读用读锁,写用写锁,读锁和写锁互斥,这么做可以有效的避免幻读、不可重复读、脏读等问题,但会极大的降低数据库的并发能力。

所以说不可重复读和幻读最大的区别,就在于如何通过锁机制来解决他们产生的问题。

上文说的,是使用悲观锁机制来处理这两种问题,但是MySQL、ORACLE、PostgreSQL等成熟的数据库,出于性能考虑,都是使用了以乐观锁为理论基础的 MVCC(多版本并发控制)来避免这两种问题。

悲观锁和乐观锁

悲观锁,对数据被外界(除了本事务之外)的修改持保守态度,因此在整个数据处理的过程中,将数据处于锁定状态。悲观锁的实现往往依靠数据库提供的锁机制。

读取数据时给加锁,其它事务无法修改这些数据。修改删除数据时也要加锁,其它事务无法读取这些数据。

乐观锁,解决悲观锁会消耗数据库大量性能开销的问题。大多基于数据版本 Version 记录机制实现。在基于数据库表的版本解决方案中,一般是通过数据库表增加一个 “version” 字段来实现。

读取出数据时,将此版本号一同读出,之后更新时,对此版本号加一。此时,将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对,如果提交的数据版本号大于数据库表当前版本号,则予以更新,否则认为是过期数据。

MVCC在MySQL的InnoDB中的实现

在 InnoDB 中,会在每行数据后添加三个额外的隐藏的值来实现 MVCC

  • DB_TRX_ID – 记录插入或更新该行的最后一个事务的事务 ID【这行数据何时被创建】
  • DB_ROLL_PTR – 指向该行对应的 undo log 的指针
  • DB_ROW_ID – 单调递增的行 ID,他就是 AUTO_INCREMENT 的主键 ID

InnoDB 拥有一个自增的全局事务 ID,每开启一个新事务,事务的版本号就会递增。 在可重读 Repeatable reads 事务隔离级别下:

  • SELECT时,读取创建版本号<=当前事务版本号,删除版本号为空或>当前事务版本号。
  • INSERT时,保存当前事务版本号为行的创建版本号
  • DELETE时,保存当前事务版本号为行的删除版本号
  • UPDATE时,插入一条新纪录,保存当前事务版本号为行创建版本号,同时保存当前事务版本号到原来删除的行

通过MVCC,虽然每行记录都需要额外的存储空间,更多的行检查工作以及一些额外的维护工作,但可以减少锁的使用,大多数读操作都不用加锁,读数据操作很简单,性能很好,并且也能保证只会读取到符合标准的行,也只锁住必要行。

快照读和当前读

在 RR 级别中,通过 MVCC 机制,虽然让数据变得可重复读,但我们读到的数据可能是历史数据,不是数据库当前的数据。这在一些数据的时效特别敏感的业务中就很可能出问题。

读取历史数据的方式,我们叫它为快照读(snapshot read),而读取数据库当前版本的释放,叫当前读(current read)。在 MVCC 中:

  • 快照读:就是 select
    • select * from table ….;
  • 当前读:特殊的读操作,插入/更新/删除操作,属于当前读,处理的都是当前的数据,需要加锁。
    • select * from table where ? lock in share mode;
    • select * from table where ? for update;
    • insert;
    • update ;
    • delete;

事务的隔离级别实际上都是定义了当前读的级别,MySQL 为了减少锁处理(包括等待其它锁)的时间,提升并发能力,引入了快照读的概念,使得 select 不用加锁。而 update、insert 这些“当前读”,就需要另外的模块来解决了。

在串行化中 select 还是要加锁哈。

前面讲了在 RR 级别中,虽然能够通过加锁来实现可重复读,但是没办法防止其他事务 insert,可能会产生幻读。为了解决当前读中的幻读问题,MySQL 事务使用了 Next-Key 锁。

Next-Key锁

InnoDB 存储引擎的锁的算法有三种:

  • Record 锁:记录锁,单个行记录上的锁
  • Gap 锁:间隙锁,锁定一个范围,不包括记录本身
  • Next-key 锁:record+gap锁,锁定一个范围,包含记录本身

Next-Key 锁是行锁和 GAP(间隙锁)的合并。行锁在前边已经介绍过了,那么 Gap 锁呢?

MySQL 是这么实现的,假设在一张 person 表中,person_id 是个索引,那么它就会维护一套 B+ 树的数据关系,为了简化,我们就看它的叶子节点。(B+ 树的叶子节点是有序的)

InnoDB 将这段数据分成几个区间:(negative infinity, 5](5, 30](30, positive infinity)

sql 语句 update person set name='小红' where person_id=30; 不仅用行锁锁住了相应的数据行,同时也在两边的区间 (5, 30](30, positive infinity) 都加入了 Gap 锁。这样其他就无法在这两个区间 insert 进新数据,避免了幻读问题。

行锁防止别的事务修改或删除,GAP 锁防止别的事务新增,行锁和 GAP 锁结合形成的的 Next-Key 锁共同解决了 RR 级别在写数据时的幻读问题。

数据库中的锁

MySQL中锁详解(行锁、表锁、页锁、悲观锁、乐观锁等) - 星朝 - 博客园 (cnblogs.com)

在数据库中,使用锁来管理对共享资源的并发访问,维护数据的一致性。在数据库中有两类锁,分别是 latch 和 lock。

latch 一般称为闩锁,只作用于内存中,锁定的时间非常短。在 InnoDB 存储引擎中,latch 可以分为 mutex(互斥锁)和 rwlock(读写锁),其目的是用来保证并发线程操作临界资源的正确性,不存在死锁检测和处理机制。

lock 的对象是事务,用来锁定数据库中的 UI 项,如表、页和行。并且一般 lock 对象仅在事务提交或者回滚后进行释放,有死锁机制。

为了保证数据的一致性,MySQL 数据库的各存储引擎使用了三种级别的锁定机制:表级锁定、行级锁定和页级锁定。

表锁、行锁和页锁

表级锁,是锁定粒度最大的一种锁,锁冲突概率高、并发度低。好处是不会出现死锁,开销小,获取和释放锁的速度快。适用以查询为主,少量更新的应用。

  • 表读锁(Table Read Lock)
  • 表写锁(Table Write Lock)

在表读锁和表写锁的环境下,表读锁不会阻塞其他用户对同一表的读操作,但是会阻塞对同一表的写操作;表写锁会阻塞其他用户对同一表的读和写操作,即只有持有表写锁的用户才可以对表进行更新操作。

**表读锁和表写锁是互斥的,读写操作是串行的。**如果某个进程想要获取表读锁,同时另外一个进程想要获取表写锁,在 MySQL 中,表写锁优先于表读锁。也可以通过调节参数 low-priority-updates 给予读请求优先的权力。

行级锁,是锁定粒度最小的一种锁,锁冲突概率低、并发度高。缺点是容易发生死锁,开销大,加锁慢。InnoDB 行级锁类型:

  • 共享锁(S),又称读锁,多个事务对同一数据共享一把锁,都能访问到数据,但是只能读不能修改。
  • 排他锁(X),又称写锁,不能与其他锁并存。如果一个事务获取了一个数据行的排他锁,其他事务就不可以对数据进行读取和修改。
  • 意向共享锁(IS),事务给一个数据行加共享锁前必须先取得该表的 IS 锁。
  • 意向排他锁(IX),事务给一个数据行加排他锁前必须先取得该表的 IX 锁。

意向锁,用于解决事务 A 锁住了表中的一行,让这一行只能读不能写,之后事务 B 申请整个表的写锁,此时会与事务 A 持有的行锁冲突,数据库需要避免这种冲突,也就是说让事务 B 的申请被阻塞,直到事务 A 释放了行锁。

如何判断这个冲突呢?

  1. 判断表是否已被其他事务用表锁锁表
  2. 发现表上有意向共享锁,说明表中有些行被共享行锁锁住了,因此事务 B 申请表的写锁会被阻塞。

申请意向锁的动作是数据库完成的,即事务 A 申请一行的行锁时,数据库自动开始申请表的意向锁,不需要程序员手动申请。

InnoDB 的锁定模式分为四种:共享锁(S)、排他锁(X)、意向共享锁(IS)和意向排他锁(IX),这四种锁的共存逻辑关系:

如果一个事务请求的锁模式与当前的锁兼容,InnoDB 就将请求的锁授予该事务;反之,如果两者不兼容,该事务就要等待锁释放。意向锁是 InnoDB 自动加的,不需用户干预。

如果 where 条件没有索引,无法通过索引快速过滤,那么就会对整张表加锁。

页锁,介于行级锁和表级锁之间,会发生死锁,并发度一般。

InnoDB 行锁和表锁都支持,MyISAM 只支持表锁。

  • InnoDB 行锁是通过给索引上的索引项加锁来实现的,意味着只有通过索引条件检索数据,InnoDB 才使用行级锁,否则将使用表锁。
  • 由于 MySQL 的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行的记录,但是如果是使用相同的索引键,是会出现锁冲突的。

死锁及解决方案

事务 A 与事务 B 由于某种调度顺序,可能会互相等待对方释放资源的锁,进而造成死锁。

在数据库中,解决死锁采用两种方式,预防死锁和解决死锁。

我们知道发生死锁的 4 个充要条件:

  • 互斥
  • 请求与保持
  • 不剥夺
  • 循环等待

预防死锁

  • 一次封锁法:事务必须一次性申请所有加锁请求,如果不能同时加锁成功,则全部释放掉已经持有的锁并处于等待状态;如果全部加锁成功,则继续执行【不适用,因为在事务开启阶段,数据库并不知道会用到哪些数据】。
  • 顺序封锁法:预先对所有数据对象规定一个顺序,事务需要按照规定的顺序加锁。

解决死锁

  • 设置超时等待时间
  • 银行家算法
  • 【死锁检测】事务等待图:事务等待图是一个有向图,每个节点表示正在运行的事务,每条边表示事务等待的情况。事务等待图动态地反应了所有事务地等待情况,通过检测事务等待图,如果发现图中存在回路,则表示出现了死锁。

关于锁的常见问题

1.InnoDB存储引擎什么时候会锁住整张表(什么时候使用行级锁),什么时候或只锁住一行呢(使用行锁)?

只有通过索引条件查询数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁! 记住:一定要记住为匹配条件字段加索引。

2.什么时候使用行级锁?什么时候使用表级锁?

(1)在增删改查时匹配的条件字段不带有索引时,innodb使用的是表级锁,

3.行级锁锁的是什么?行级锁怎么实现加锁?

(1)行级锁是针对索引加的锁;

(2) InnoDB行锁是通过索引上的索引项来实现的,这一点MySQL与Oracle不同,后者是通过在数据中对相应数据行加锁来实现的。

4.mysql读锁和写锁?

(1)因为只有触发了读写锁,我们才会谈是进行行级锁定还是进行表级锁定;

(2)用 select 命令时触发读锁,当使用 update,delete,insert 时触发写锁,并且使用 rollback或commit后解除本次锁定。

5.常见的锁算法:

Next-key lock,record+gap 临键锁,锁定一个范围,包含记录本身

Gap lock,间隙锁,锁定一个范围,不包括记录本身

Record lock,记录锁,单个行记录上的锁

6.什么时候会释放锁?

提交事务或回滚事务就会释放锁。

ACID

原子性 (atomicity),一个事务要么全部提交成功,要么全部失败回滚,不能只执行其中的一部分操作,这就是事务的原子性。

一致性 (consistency),事务的执行不能破坏数据库数据的完整性和一致性,一个事务在执行之前和执行之后,数据库都必须处于一致性状态。举个例子说 A 向 B 转了 100 块钱,B 的账户里应该多 100,如果 A 的账户没有减 100,那么就是不一致的。

隔离性 (isolation),事务的隔离性是指在并发环境中,并发的事务是相互隔离的,一个事务的执行不应该被其他事务干扰。四个隔离级别:

  • 读未提交(Read Uncommited):写数据的时候添加一个 X 锁(排他锁),也就是在写数据的时候不允许其他事务进行写操作,但是读不受限制,读不加锁,会产生脏读。

    脏读:比如事务 A 和事务 B 同时进行,事务 A 在整个执行阶段,会将某数据的值从 1 开始一直加到 10,然后进行事务提交,此时事务 B 能够看到这个数据项在事务 A 操作过程中的所有中间值(1 变 2,2 变 3 等),而对这一系列的中间值的读取就是未授权读取。

  • 读已提交(Read Commited),写数据的时候加上 X 锁(排他锁),读数据的时候添加 S 锁(共享锁)而且有约定:如果一个数据加了 X 锁就没法加 S 锁;同理如果加了 S 锁就没法加 X 锁,但是一个数据可以同时存在多个 S 锁(因为只是读数据),并且规定 S 锁读取数据,一旦读取完成就立刻释放 S 锁(不管后续是否还有很多其他的操作,只要是读取了 S 锁的数据后,就立刻释放 S 锁)。这样就解决了脏读的问题,但是又有新的问题出现——不可重复读。

    不可重复读:同一个事务对数据的多次读取的结果不一致。比如事务 A 和事务 B 同时进行,事务 A 进行 +1 操作,此时事务 B 无法看到这个数据项在事务 A 操作过程中的所有中间值,只能看到最终的 10。此外,如果说有另一个事务 C,和事务 A 进行非常类似的操作,只是事务 C 是将数据项从 10 加到 20,此时事务 B 也同样可以读取到 20,即读已提交允许不可重复读取。

  • 可重复读 (Repeatable Read):在读取数据的时候加上 S 锁,但是要直到事务准备提交了才释放该 S 锁,X 锁还是一致。保证在事务处理过程中,多次读取同一个数据时,其值都和事务开始时刻是一致的。因此该事务级别禁止不可重复读取和脏读取,但是有可能出现——幻读。

    幻读:同样的事务操作,在前后两个时间段内执行对同一个数据项的读取,可能出现不一致的结果。比如事务 B 在第一次事务操作过程中,始终对数据项读取到 1,但是在下一次事务操作中,即使事务 B(注意,事务名字虽然相同,但是指的是另一个事务操作)采用同样的查询方式,就可能读取到 10 或 20。关于幻读,可重复读的真实用例是什么? - 知乎用户的回答 - 知乎

  • 串行化 (durability):事务只能一件一件的进行,不能并发进行。

参考:数据库的四大特性以及四个隔离级别和引发的问题 - Shan-KyLin - 博客园 (cnblogs.com) xdm,它讲得好,看它!

持久性 (durability),一旦事务提交,那么它对数据库中的对应数据的状态的变更就会被永久保存到数据库中。即使发生系统崩溃或机器宕机等故障,只要数据库能够重新启动,那么一定能够将其恢复到事务成功结束的状态。

事务隔离级别

事务是逻辑上的一组操作,要么都执行,要么都不执行。

最经典的例子是转账,小明要给小红转账 100 元,涉及两个操作:将小明的余额减少 100 元,将小红的余额增加 100 元。如果因为系统原因导致小明的余额减少了,而小红的余额每增加,这样的结果是不正确的。事务就是保证这两个操作要么都成功,要么都失败。

并发事务带来的问题

  • 脏读(Direty read),事务 A 对某个数据进行了修改,但还没有提交到数据库中,此时事务 B 读取了该数据。因为这个数据是还没有提交的,事务 B 根据这个数据所作的操作可能是不正确的,事务 B 发生了脏读,这个数据被称为“脏数据”。
  • 不可重复读(Unrepeatable read),在一个事务内多次读同一数据,结果不一样。在事务 A 中的两次读数据之间,由于事务 B 的修改导致事务 A 两次读取的数据不一样。
  • 幻读(Phantom read),事务 A 读取了几行数据,接着事务 B 插入了一些数据,在之后的查询中,事务 A 发现多了一些原本不存在的记录(注意,事务名字虽然相同,但是指的是另一个事务操作),就好像发生了幻觉一样,所以称为幻读。

幻读和不可重复读有些相似之处 ,但是不可重复读重点在于 update 和 delete,而幻读的重点在于 insert。

事务的隔离级别

  • 读未提交(READ-UNCOMMITTED),最低的隔离级别,允许读取尚未提交的数据变更,可能会导致脏读、不可重复读和幻读
  • 读已提交(READ-COMMITTED),允许读取并发事务已经提交的数据,可以防止脏读,但仍有可能发生不可重复读和幻读
  • 可重复读(REPEATABLE-READ),对同一字段的多次读取结果都是一致的,除非数据是被本身事务自己所修改,可以防止脏读和不可重复读,但仍有可能发生幻读
  • 串行化(SERIALIZABLE),最高的隔离级别,完全服从 ACID 的隔离级别。所有的事务依次逐个执行,这样事务之间就完全不可能产生干扰,可以防止脏读、不可重复读和幻读

MVCC

史上最详尽,一文讲透 MVCC 实现原理_居士的CSDN-CSDN博客_mvcc实现原理

MVCC 多版本并发控制,是 InnoDB 实现事务并发与回滚的重要功能。

利用 MVCC 可以实现读已提交和可重复读两种隔离级别,即解决脏读和不可重复读问题。

MVCC 一般读写时不阻塞的(即很多情况下避免了加锁的操作),通过利用快照来提供一定级别的一致性读取。

具体实现是在数据库的每一行,添加额外的三个字段:

  • DB_TRX_ID – 记录插入或更新该行的最后一个事务的事务 ID
  • DB_ROLL_PTR – 指向该行对应的 undo log 的指针
  • DB_ROW_ID – 单调递增的行 ID,他就是 AUTO_INCREMENT 的主键 ID

InnoDB 拥有一个自增的全局事务 ID,每当一个事务开启,在事务中都会记录当前事务的唯一 ID,而全局事务 ID 会随着新事物的创建而增长。在新事物创建的同时,事务系统会将当前未提交的所有事务 ID 组成数组传递给这个新事务,我们把这个数组称为 TRX_ID 集合。

快照读

每当一个事务更新一条数据时,都会在写入对应 undo log 后将这行记录的隐藏字段 DB_TRX_ID 更新为当前事务的事务 ID,用来表明最新更新该数据的事务是谁。

当另一个事务去 select 数据时,读到该行数据的 DB_TRX_ID 不为空并且 DB_TRX_ID 与当前事务的事务 ID 是不同的,这就说明这一行数据是另一个事务修改并提交的。

那么,这行数据究竟是在当前事务开启前提交的还是在当前事务开启后提交的呢?

通过上文提到的 TRX_ID 集合,就很容易判断这个问题。如果这一行数据的 DB_TRX_ID 在 TRX_ID 集合中或大于当前事务的事务 ID,那么就说明这行数据是在当前事务开启后提交的,否则说明这行数据是在当前事务开启前提交的。

这里解释下上面一段话。

如果这一行数据的 DB_TRX_ID 在 TRX_ID 集合中或大于当前事务的事务 ID,那么就说明这行数据是在当前事务开启后提交的。
这个说法没问题的,事务10把某行数据的字段count修改为10,该行数据DB_TRX_ID会被修改为10。
假设此时有一个事务n来读取该行数据,如果n=11,没有任何疑问,这行数据是在当前事务11开启前提交的。
如果n=9,那么当前事务开启的时候事务10还没有开启,此时该行数据的DB_TRX_ID表示最后是由事务10修改的,说明这行数据是在当前事务9开启后提交的,这个也没问题。
如果当前事务的 TRX_ID集合中包含事务10,存在这样一个情况:事务n开启后,DB_TRX_ID中有事务10还未提交,等事务10提交后,事务n才执行到读取该行数据,发现事务10是最后修改该行数据的事务,此时该行数据当前事务开启后提交的。

对于当前事务开启后提交的数据,当前事务需要通过隐藏的 DB_ROLL_PTR 字段找到 undo log,然后进行逻辑上的回溯才能拿到事务开启时的原数据。

这个通过 undo log + 数据行获取到事务开启时的原始数据的过程就是“快照读”。

慢查询

MySQL索引原理及慢查询优化 - 美团技术团队 (meituan.com)

慢查询日志是用来记录 MySQL 中响应时间超过指定阈值的 SQL 语句,运行时间朝贡国 long_query_time 值的 SQL 会被记录到慢查询日志中。

一个慢查询引发的思考

有一个查询很慢,

1
2
3
4
5
6
7
8
9
10
select
count(*)
from
task
where
status=2
and operator_id=20839
and operate_time>1371169729
and operate_time<1371174603
and type=2;

如何去优化呢?这种情况可以建立一个联合索引,因为是最左前缀匹配,所以 operate_time 需要放到最后,还需要把其他相关的查询都找到,做一个综合评估。

根据最左匹配原则,最开始的 sql 语句的索引应该是 status、operator_id、type、operate_time 的联合索引,其中 status、operator_id 和 type 的顺序可以颠倒。把这个表的所有相关索引都找到,比如有如下查询:

1
2
select * from task where status = 0 and type = 12 limit 10;
select count(*) from task where status = 0 ;

综合分析后,可以得出结论:索引建立成 (status, type, operator_id, operator_time) 就是非常正确的,因为可以覆盖到所有情况。这个就是利用了索引的最左匹配原则。

查询优化神器-explain命令

MySQL Explain详解 - GoogSQL - 博客园 (cnblogs.com)

我们可以用 explain 这个命令来查看一个 SQL 语句的执行计划,查看该 SQL 语句有没有使用上了索引,有没有做全表扫描,这都可以通过 explain 命令来查看。

需要强调 rows 是核心指标,它表示 MySQL 根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数,绝大部分 rows 小的语句执行一定很快【有例外,下面会提到】。所以优化语句基本都是在优化 rows。

慢查询优化步骤

  1. 先运行看看是否真的很慢,注意设置 SQL_NO_CACHE。【为了测试sql语句的效率,有时候要不用缓存来查询】
  2. 用原语句查询一次,看最终返回的记录数,这个记录数用在后面 explain 分析执行计划时使用,看是否其他查询返回了过多的结果。
  3. explain 查看执行计划,是否与 1 预期一样(从锁定记录较少的表开始查询)。
  4. order by + limit 形式的 sql 语句,去掉 order by 和 limit,让排序的表查一次,看看用了多少记录来排序,是不是排序量太大造成的。
  5. 了解业务使用场景。
  6. 加索引时参照建索引的几大原则。
  7. 观察结果,不符合预期继续从 0 分析。

常见的优化方法

  • 解决索引失效的情况

    • 模糊查询时 like 匹配的字符串以 % 开头会导致索引失效,只要 % 不在第一个位置索引就能够起作用。
    • 在 where 中索引列参与了运算(或使用了函数)会导致索引失效,需要注意尽量不要在 where 中让索引列参与运算。
  • 优化数据库结构

    • 将字段很多的表分解成多个表。对于字段较多的表,如果有些字段的使用频率很低,可以将这些字段分离出来形成新表,因为当一个表的数据量很大时,会由于使用频率低的字段的存在而变慢。
    • 增加中间表,对于经常需要联合查询的表,可以建立中间表以提高查询效率。通过建立中间表,把需要经常联合查询的数据插入到中间表中,然后将原来的联合查询改为对中间表的查询,以此来提高查询效率。
  • 分解关联查询

    对关联查询进行分解,就是可以对每一个表进行一次单表查询,然后将查询结果在应用程序中进行关联,很多场景下这样会更高效。

  • 优化 LIMIT 分页

    MySQL limit 分页查询优化(百万级优化) - 王默默 - 博客园 (cnblogs.com)

    • 建立 id 索引,查询索引 id

      1
      select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;

      先查询出 90000 条数据对应的主键 id 的值,然后直接通过该 id 的值直接查询该 id 后面的数据。

    • 分表存储,把百万级的数量分成十万一张表。

    • 使用复合索引查询数据。

      1
      select * from acct_trans_log WHERE acct_id = 3095 order by create_time desc limit 0,10

      建立复合索引 (acct_id, create_time),注意 where 用到的放第一位。

几个慢查询案例

复杂语句写法

很多情况下,我们写 SQL 只是为了实现功能,这只是第一步,不同语句书写方式对于效率往往有本质的差别,这要求我们对 MySQL 的执行计划和索引原则有非常清楚的认识,请看下面的语句:

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
select
distinct cert.emp_id
from
cm_log cl
inner join
(
select
emp.id as emp_id,
emp_cert.id as cert_id
from
employee emp
left join
emp_certificate emp_cert
on emp.id = emp_cert.emp_id
where
emp.is_deleted = 0
) cert
on (
cl.ref_table ='Employee'
and cl.ref_oid = cert.emp_id
)
or (
cl.ref_table = 'EmpCertificate'
and cl.ref_oid = cert.cert_id
)
where
cl.last_upd_date >= '2013-11-07 15:03:00'
and cl.last_upd_date <= '2013-11-08 16:00:00';

0.先运行一下,53 条记录 1.87 秒,又没有用聚合语句,比较慢

1
53 rows in set (1.87 sec)

1.explain

image.png

简述一下执行计划,首先 mysql 根据 idx_last_upd_date 索引扫描 cm_log 表获得 379 条记录;然后查表扫描了 63727 条记录,分为两部分,derived 表示构造表,也就是不存在的表,可以简单理解成是一个语句形成的结果集,后面的数字表示语句的 ID。derived2 表示的是 ID=2 的查询构造了虚拟表,并且返回 63727 条记录。

我们再来看看 ID=2 的语句究竟做了些什么返回这么大量的数据,首先全表扫描 employee 表 13317 条记录,然后根据索引 emp_certificate_empid 关联 emp_certificate 表,rows=1 表示每个关联都只锁定了一条记录,效率比较高。获得后,再和 cm_log 的379 条记录根据规则关联。从执行过程上可以看出返回了太多的数据,返回的数据绝大部分 cm_log 都用不到,因为 cm_log 只锁定了 379 条记录。

如何优化呢?可以看到我们在运行完后还要和 cm_log 做 join,那么我们能不能之前和 cm_log 做 join 呢?仔细分析语句不难发现,其基本思想是如果 cm_log 的 ref_table 是 EmpCertificate 就关联 emp_certificate 表,如果 ref_table 是 Employee 就关联 employee 表,我们完全可以拆分成两部分,并用 union 连接起来(注意这里用 union ,不用 union all 是因为原语句有 distinct 来得到唯一的记录,而 union 恰好具备了这种功能)。如果原语句中没有 distinct 不需要去重,就可以直接使用 union all,因为使用 union 需要去重的动作,会影响 SQL 的性能。

优化过的语句如下:

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
select
emp.id
from
cm_log cl
inner join
employee emp
on cl.ref_table = 'Employee'
and cl.ref_oid = emp.id
where
cl.last_upd_date >='2013-11-07 15:03:00'
and cl.last_upd_date<='2013-11-08 16:00:00'
and emp.is_deleted = 0
union -- union 合并结果集,去除重复行
select
emp.id
from
cm_log cl
inner join
emp_certificate ec
on cl.ref_table = 'EmpCertificate'
and cl.ref_oid = ec.id
inner join
employee emp
on emp.id = ec.emp_id
where
cl.last_upd_date >='2013-11-07 15:03:00'
and cl.last_upd_date<='2013-11-08 16:00:00'
and emp.is_deleted = 0

4.不需要了解业务场景,只需要改造的语句和改造之前的语句保持结果一致

5.现有索引可以满足,不需要建索引

6.用改造后的语句实验下,只需要 10ms 降低了近 200 倍。

image60ca7494abbd0d71.png

明确应用场景

举这个例子的目的在于颠覆我们对列的区分度的认知,一般上我们认为区分度越高的列,越容易锁定更少的记录,但在一些特殊的情况下,这种理论是有局限性的。

1
2
3
4
5
6
7
8
9
10
11
select
*
from
stage_poi sp
where
sp.accurate_result=1
and (
sp.sync_status=0
or sp.sync_status=2
or sp.sync_status=4
);

0.先看看运行多长时间,951 条数据 6.22 秒,很慢。

1.先 explain,rows 达到了 361 万,type=ALL 表明是全表扫描

image0e5f4a33cb43568e.png

2.所有字段都应用查询得到返回的记录数,因为单表查询 0 已经做过了,返回 951 条。

3.让 explain 的 rows 尽量逼近 951。

看一下 accurate_result = 1 的记录数:

1
2
3
4
5
6
7
8
select count(*),accurate_result from stage_poi  group by accurate_result;
+----------+-----------------+
| count(*) | accurate_result |
+----------+-----------------+
| 1023 | -1 |
| 2114655 | 0 |
| 972815 | 1 |
+----------+-----------------+

我们看到 accurate_result 这个字段的区分度非常低,整个表只有 -1、0 和 1 三个值,加上索引也无法锁定特别少的数据。

再看一下 sync_status 字段的情况:

1
2
3
4
5
6
7
select count(*),sync_status from stage_poi  group by sync_status;
+----------+-------------+
| count(*) | sync_status |
+----------+-------------+
| 3080 | 0 |
| 3085413 | 3 |
+----------+-------------+

同样区分度也很低,也不适合建立索引。

问题分析到这,好像得出了这个表无法优化的结论,两个列的区分度都很低,即便加上索引也只能适应这种情况,很难做普遍性的优化,比如当 sync_status 0、3 分布的很平均,那么锁定记录也是百万级别的。

4.找业务方去沟通,看看使用场景。业务方是这么来使用这个 SQL 语句的,每隔五分钟会扫描符合条件的数据,处理完成后把 sync_status 这个字段变成 1,五分钟符合条件的记录数并不会太多,1000 个左右。了解了业务方的使用场景后,优化这个 SQL 就变得简单了,因为业务方保证了数据的不平衡,如果加上索引可以过滤掉绝大部分不需要的数据。

5.根据建立索引规则,使用如下语句建立索引

1
alter table stage_poi add index idx_acc_status(accurate_result,sync_status);

6.观察结果,发现只需要 200ms,快了 30 多倍。

1
952 rows in set (0.20 sec)

我们再来回顾一下分析问题的过程,单表查询相对来说比较好优化,大部分时候只需要把 where 条件里面的字段依照规则加上索引就好,如果只是这种“无脑”优化的话,显然一些区分度非常低的列,不应该加索引的列也会被加上索引,这样会对插入、更新性能造成严重的影响,同时也有可能影响其它的查询语句。所以我们第 4 步调查 SQL 的使用场景非常关键,我们只有知道这个业务场景,才能更好地辅助我们更好的分析和优化查询语句。

索引

索引是一种用于快速查询和检索数据的数据结构。常见的索引结构:B 树、B+ 树和 hash。

索引的作用就相当于目录的作用,比如查字典,有了目录后我们只需要先去目录中查找字的位置,直接翻到那一页就好了。

索引的优缺点

优点

  • 索引可以加快数据的检索速度(通过减少检索的数据量)【这也是创建索引的主要原因】
  • 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性

缺点

  • 创建索引和维护索引需要耗费许多时间。(对表中数据进行增删改的时候,如果数据有索引,那么索引也需要动态的修改,会降低 SQL 执行效率)
  • 索引需要使用物理文件存储,会耗费一定的空间

使用索引一定能提高查询性能吗?

大多数情况下,索引查询都是比全表扫描要快的。但是如果数据库的数据量不大,那么使用索引也不一定能够带来很大提升。

索引的底层数据结构

hash表

hash 表是禁止对的集合,通过 key 可以快速取出对应的 value,因此 hash 表可以快速检索数据,时间复杂度接近 $O(1)$。

哈希算法可以计算 key 所对应的哈希值,然后快速的找到 key 所对应 value 所在的位置。如果发生了哈希冲突,比较常用的解决办法是链地址法,将哈希冲突的数据存放在链表中。

但是它存在缺陷:

  1. 哈希冲突问题
  2. hash 索引不支持查询排序:hash 后的数据不会保持原有的数据顺序
  3. hash 索引不持支范围查询:hash 索引是根据哈希算法来定位的,比如 WHERE id < 500,总不能够把 1~499 的数据每个都进行一次 hash 计算定位吧

B树&B+树

B 树又称多路平衡查找树,B 是 balanced 的意思。

B 树B树详解 - 简书 (jianshu.com)

B 树的出现是因为磁盘 IO 操作的效率很低,在大量查询时不能一下子将所有数据加载到内存中,只能逐一加载磁盘页,每个磁盘页对应树的节点。于是查找的过程分为两步:

  1. 在 B 树中找节点:在磁盘上进行,找磁盘页。
  2. 在节点内找关键字:在内存中进行,找到目标节点后,先将节点中的信息读入内存,再采用顺序查找或者折半查找关键字。

B+ 树

叶节点包含信息,所有非叶节点仅起到索引的作用,非叶节点中的每个索引项只含有对应子树的最大关键字和指向该子树的指针。B+ 树有两个头指针:一个指向根节点,另一个指向关键字最小的叶节点。于是 B+ 树的查找方法有两种,从最小关键字开始的顺序查找和从根节点开始进行多路查找。

目前大部分数据库系统及文件系统都采用 B 树或 B+ 树作为索引结构。

使用 B+ 树这种数据结构,能够把每次查找数据时把磁盘 IO 次数控制在一个很小的数量级。

B树和B+树的区别

B+ 树是应数据库所需而出现的一种 B 树的变体。

  • B 树的节点存放关键字也存放数据;B+ 树只有叶子节点存放数据,其他节点存放索引(对应子树的最大关键字和指向该子树的指针,不包含该关键字对应记录的存储地址)
  • B 树的所有叶子节点都是独立的;B+ 树的相邻叶子节点按大小顺序相互链接
  • B 树的查找过程相当于多路查找,分为在 B 树中找节点和在节点内找关键字两步,可能还没到叶子节点,查找就结束了;B+ 树有两个头指针:一个指向根节点,另一个指向关键字最小的叶节点,于是 B+ 树的查找方法有两种,从最小关键字开始的顺序查找和从根节点开始进行多路查找,B+ 树每次查找都是一条从根节点到叶节点的路径,因为数据都存放在叶子节点上,所以在非叶子节点并不会停止。

在 MySQL 中,使用的是 B+ 树作为索引结构。

MyISAM 引擎中,B+ 树叶节点存放的是数据记录的地址。在索引时先按照 B+ 树索引算法进行索引,如果指定的索引存在,取出叶节点中的地址数据,根据该地址数据读取相应的数据记录。【非聚簇索引】

InnoDB 引擎中,其数据文件本身就是索引文件。树的叶节点保存了完整的数据记录,找到了对应叶节点就可以获得数据。【聚簇索引】

索引类型

主键索引(Primary Key)

数据表的主键列使用的就是主键索引。

一张数据表只有能一个主键,并且主键不能为 null,不能重复。

二级索引(辅助索引)

二级索引又称为辅助索引,二级索引的叶子节点存储的数据是主键。通过二级索引,可以定位主键的位置。

唯一索引、普通索引、前缀索引等索引属于二级索引。

  1. 唯一索引(Unique Key) :唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据,但是允许数据为 NULL,一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性,而不是为了查询效率。
  2. 普通索引(Index)普通索引的唯一作用就是为了快速查询数据,一张表允许创建多个普通索引,并允许数据重复和 NULL。
  3. 前缀索引(Prefix) :前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引,相比普通索引建立的数据更小, 因为只取前几个字符。
  4. 全文索引(Full Text) :全文索引主要是为了检索大文本数据中的关键字的信息,是目前搜索引擎数据库使用的一种技术。

为什么需要主键

数据表的主键是为了遵循三范式,然后可以根据主键建立索引,通过索引提高查询效率。

在 InnoDB 里,聚集索引是根据主键建立的,如果我们没有指定主键,那 InnoDB 自己会找个其它属性建立索引。由存储引擎自己建立的索引对于我们查询语句的编写没啥好处。

聚簇索引与非聚簇索引

非聚簇索引:将数据与索引分开,存放索引的叶子节点指向了数据的地址。MyISAM 通过 key_buffer 把索引先缓存到内存中,当需要访问数据时(通过索引访问数据),在内存中直接搜索索引,然后通过索引找到磁盘响应数据。

聚簇索引:将数据与索引放到一块,找到索引就找到了数据。

聚簇索引与非聚簇索引(也叫二级索引) - 简书 (jianshu.com)

聚簇索引

聚簇索引即索引结构和数据一起存放的索引。主键索引属于聚簇索引。

在 MySQL 中,InnoDB 引擎的表的 .ibd 文件就包含了该表的索引和数据,对于 InnoDB 引擎来说,该表的索引(B+ 树)的每个非叶子节点存储索引,叶子节点存储索引和索引对应的数据。

优点 :查询速度快,每次查询都是一条从根节点到叶子节点的路径,找到了索引就找到了数据。

缺点

  1. 依赖于有序的数据,如果索引的数据不是有序的,那么需要在插入时排序;
  2. 更新代价大,对索引列的数据进行修改,那么对应的索引也会被修改,聚簇索引的叶子节点还存放有数据,修改代价肯定大。所以对于主键索引来说,主键一般都是不可修改的。

非聚簇索引

非聚簇索引即索引结构和数据分开存放的索引。二级索引属于非聚簇索引。

MyISAM 引擎的表的 .myi 文件包含了表的索引,该表的索引(B+ 树)的非叶子节点存储索引,叶子节点存储索引和索引对应数据的指针,指向 .myd 文件的数据。

非聚簇索引的叶子节点并不一定存放数据的指针,因为二级索引的叶子节点就存放的是主键,根据主键再回表查询数据。

优点 :更新代价比聚簇索引小,因为非聚簇索引的叶子节点不是存放数据,而是数据的指针。

缺点

  1. 依赖于有序的数据,B+ 树的嘛
  2. 可能会二次查询(回表),当查到索引对应的指针或主键后,可能还需要根据指针或主键再到数据文件或表中查询。

非聚簇索引一定回表查询吗?

非聚簇索引不一定回表查询。

比如用户准备使用 SQL 查询用户名,而用户名字段正好建立了索引。

1
SELECT name FROM table WHERE name='xiaoming';

那么这个索引的关键字本身就是用户名,查到对应的用户名直接返回就行了,无需回表查询。

再比如说覆盖索引的情况,也不需要回表查询。

覆盖索引

如果一个索引包含(或者说覆盖)所有需要查询的字段的值,就称之为覆盖索引。在 InnoDB 存储引擎中,如果不是主键索引,叶子节点存储的是主键 + 列值,最终还是要回表,通过主键再查找一次。覆盖索引就是要查询出的列和索引是对应的,不做回表操作。

覆盖索引即需要查询的字段正好是索引的字段,那么直接根据该索引就可以查到数据,不需要回表查询。

比如主键索引,如果一条 SQL 需要查询主键,那么正好根据主键索引就可以查到主键。

又或者说普通索引,如果一条 SQL 需要查询 name,name 字段正好有索引,可以直接根据这个索引就可以查到数据,不需要回表。

创建索引的几大原则

  1. 最左前缀匹配原则。MySQL 会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如 a = 1 and b = 2 and c > 3 and d = 4,如果建立 (a,b,c,d) 顺序的索引,d 是用不到索引的。如果建立 (a,b,d,c) 的索引则都可以用到,a、b、d 的顺序可以任意调整。
  2. = 和 in 可以乱序,比如 a = 1 and b = 2 and c = 3 建立 (a,b,c) 索引可以任意顺序,MySQL 的查询优化器会帮你优化成索引可以识别的形式。
  3. 尽量选择区分度高的列作为索引,区分度的公式是 count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是 1,而一些状态、性别字段可能在大数据面前区分度就是 0。【经验值:一般 join 字段我们都要求是 0.1 以上,即平均 1 条扫描 10 条记录】
  4. 索引列不能参加计算,保持列“干净”,在 where 语句中索引字段不要使用函数,进行检索的时候会把所有元素都应用到函数才能比较,成本太大。
  5. 尽量的扩展索引,不要新建索引。比如表中已经有 a 的索引,现在要加 b 的索引,那么可以扩展成 (a,b),因为索引对应着一颗 B+ 树,都需要占用存储空间。

MySQL如何为表字段添加索引?

1.添加 PRIMARY KEY(主键索引)

1
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2.添加 UNIQUE(唯一索引)

1
ALTER TABLE `table_name` ADD UNIQUE ( `column` )

3.添加 INDEX(普通索引)

1
ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4.添加 FULLTEXT(全文索引)

1
ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

5.添加多列索引

1
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

MySQL 常用数据类型

MySQL 支持多种数据类型,大致可以分为四类:数值型、浮点型、日期/时间和字符串类型。例如:

  • INT:4字节
  • TINTINT:1字节
  • BIGINT:8字节
  • FLOAT与DOUBLE
  • DATE:3字节
  • DATETIME:8字节,和时区无关,服务器更换地址或者更换客户端连接时区的设置,会导致读出的时间出错。
  • TIMESTAMP:4字节,和时区有关,字段的值会随着时区的变化而变化
  • CHAR:0-255字节,定长字符串
  • VARCHAR:0-65536字节,变长字符串
  • TEXT:0-65535字节,长文本数据

char和varchar

  • char(n) 若存入字符数小于 n,则以空格补于其后,查询之时再将空格去掉。所以 char 类型存储的字符串末尾不能有空格,varchar 不限于此。
  • char(n) 固定长度,char(4) 不管是存入几个字符,都将占用4个字节,varchar 是存入的实际字符数+1个字节(n<=255)或2个字节 (n>255),所以 varchar(4),存入3个字符将占用4个字节【这多出来的一个字节用于保存字符串的长度】。
  • char 类型的字符串检索速度要比 varchar 类型的快。

SQL语法

写法顺序和执行顺序

写法顺序:select–from–where–group by–having–order by

执行顺序:from–where–group by–having–select–order by

(1)from
(3) join
(2) on
(4) where
(5)group by(开始使用select中的别名,后面的语句中都可以使用)
(6) avg,sum…
(7)having
(8) select
(9) distinct
(10) order by

就是select要放后面,如果有order by,则order by放最后,因为order by 是对结果进行排序

SQL主要分成四部分

(1)数据定义。(SQL DDL)用于定义SQL模式、基本表、视图和索引的创建和撤消操作。
(2)数据操纵。(SQL DML)数据操纵分成数据查询和数据更新两类。数据更新又分成插入、删除、和修改三种操作。
(3)数据控制。(DCL)包括对基本表和视图的授权,完整性规则的描述,事务控制等内容。
(4)嵌入式SQL的使用规定。(TCL)涉及到SQL语句嵌入在宿主语言程序中使用的规则。

数据库中drop、truncate、delete三种删除的区别?谁最快?

drop :用于删除表,表的结构、属性、索引也会被删除。【不再需要该表时使用】

语法:DROP table 表名

truncate :用于删除表内数据,不删除表本身。【仍要保留该表,但是要删除所有记录】

语法:TRUNCATE TABLE 表名

delete :用于删除表中的行。【删除部分记录】

语法:DELETE FROM 表名 [WHERE 条件]

在速度上来讲,drop > truncate > delete。

count(*)和count(1)区别

它俩功能一样,都会数 null 的行。MySQL 官方推荐使用 count(*)

count(1) 的 1 表示 SELECT 子句的第一个字段。

若有主键,count(主键) 的运行效率最快;若无主键,count(1)count(*) 运行效率要快。

若整个表只有一个行,count(*) 的运行效率最快;若多于一个行时,count(1) 要比 count(*) 运行效率快。因为 count(*) 会扫描整个表。

数据库的分库分表

数据库的垂直切分与水平切分_慕课手记 (imooc.com)

数据库分库分表,何时分?怎样分?详细解读,一篇就够了 (toutiao.com)

一条SQL语句在MySQL中的执行过程

首先,一条语句在 MySQL 中执行时,涉及到诸多组件,分别如下:

  • 连接器: 身份认证和权限相关(登录 MySQL 的时候)
  • 查询缓存: 执行查询语句的时候,会先查询缓存(MySQL 8.0 版本后移除,因为这个功能不太实用)
  • 分析器: 没有命中缓存的话,SQL 语句就会经过分析器,分析器说白了就是要先看你的 SQL 语句要干嘛,再检查你的 SQL 语句语法是否正确
  • 优化器:按照 MySQL 认为最优的方案去执行
  • 执行器: 执行语句,然后从存储引擎返回数据

简单来说 MySQL 主要分为 Server 层和存储引擎层: Server 层主要包括连接器、查询缓存、分析器、优化器、执行器等,所有跨存储引擎的功能都在这一层实现,比如存储过程、触发器、视图,函数等,还有一个通用的日志模块 binglog 日志模块。存储引擎层主要负责数据的存储和读取,采用可以替换的插件式架构,支持 InnoDB、MyISAM、Memory 等多个存储引擎,其中 InnoDB 引擎有自有的日志模块 redolog 模块。