首先了解关于数据库一个误区,我们用的 MySQL、Oracle 都是 DBMS(数据库管理系统),用来操作数据的,而数据库是用来存数据的,而 sql 是结构化查询语言。程序员来写 sql 语句,DBMS 来执行 sql 语句。再一个,数据库是存储数据,数据库实例才是操作数据。

三大范式

数据库表设计的三大范式:
避免数据冗余、空间浪费。但有时候需要空间换时间的反范式优化,具体情况根据业务具体分析。比如父表冗余一些数据到子表中是为了排序。

第一范式:要求任何一张表必须有主键,每一个字段具有原子性(不可再分)。


第二范式:建立在第一范式之上,要求所有的非主键字段完全依赖主键,不要部分依赖主键。


第三范式:建立在第二范式之上,要求所有的非主键字段直接依赖主键,不要传递依赖主键。


理解1NF:考虑这样一个表:【联系人】(姓名,性别,电话) 如果在实际场景中,一个联系人的电话有家庭电话和公司电话。
那么这种表结构设计就没有达到1NF。 要符合 1NF 我们只需把列(电话)拆分。 即:【联系人】(姓名,性别,家庭电话,公司电话)。 1NF 很好辨别,但是 2NF 和3NF 就容易搞混淆。
理解2NF:当主键为联合主键时,其它字段只依赖于联合字段的一部分就是部分依赖。
理解3NF:也就是非主键字段不能产生依赖。

事务

事务是一个完整业务逻辑的最小工作单元,只有 DML (增删改)语句和事务有关,操作涉及数据的增删改就要考虑安全。 本质是多条 DML 语句同时成功 or 同时失败。 事务执行过程中,每一条 DML 的操作都会记录到“事务性活动的日志文件”中。 事务的执行过程中我们可以提交事务 or 回滚事务。默认是每执行一条 sql 提交一次,如果要关闭就要使用开启事务命令,这个命令可以理解为关闭每条都自动提交,也可理解为开启事务。

开启事务的方式有两种:

  1. begin/start transaction 命令:只有在执行这个命令后,执行了增删查改操作的 SQL 语句,才是事务真正启动的时机;
  2. start transaction with consistent snapshot 命令:执行了该命令,就会马上启动事务。

    提交事务:清空事务性活动日志,将数据全部彻底持久化到数据库表中。
    回滚事务:将之前所有的DML操作全部撤销,并且清空事务性活动的日志文件,回滚事务标志着事务的结束,并且是 失败的结束。 a向b转 1w。 将a的-1w。 将b的+1w。 这就是一个完整的业务逻辑。
    事务四个特性:
  • A :原子性:说明事务是最小的工作单元,不可再分。
  • C :一致性:所有事务要求,在同一个事务当中,所有的操作必须同时从成功或者同时失败。
  • I :隔离性:A 事务和 B 事务必须具有一定的隔离性。
  • D :持久性:事务最终结束的一个保障,事务一旦提交就是永久性的。

事务的隔离级别

  • 读未提交( Read Uncommitted ):
    A 可以读取到 B 未提交的数据,存在脏读、不可重复读、幻读问题!

  • 读已提交( Read Committed ):
    事务 A 只能读到事务 B 已提交的数据,解决了脏读!但是存在不可重复读、幻读问题!事务 A 执行到一半,突然事务 B 从头到尾执行完了,在事务 B 执行完到事务 A 结束完这段时间查询数据可能发生了变化。 Orcle 默认是这个级别。 每次读取都是新的快照

  • 可重复读(Repeatable Read):
    事务A开始之后,不管多久,同一事务内读取到的数据都是一致的,相当于读取的是事务开始执行时拍下的快照。即使A事务执行过程中事务B已insert并提交了,事务A读取到的数据条数仍然没变。解决了不可重复读问题,但是存在幻读问题! 可能A事务执行到一半,B事务执行完了,在事务中查询到的数据是读到了假象,其实数据条数已经变了,只是在事务中是没有改变,事务后再读取就改变了,改变的如果是事务B插入的就是幻读,如果是事务A插入的就不是幻读。可重复读不允许其他事务 进行update操作,但允许其他事务进行insert操作。这是MySQL默认隔离级别。每次读取都是第一次的快照

  • 序列化读(serializable):
    表示事务排队,不能并发!

    如何解决幻读

首先我们知道快照读是普通的 select。
快照读不会加锁,当前读会加锁。
除了普通 select 操作,其余操作都是当前读。也正因为普通的查询是都快照读,所以当前事务是不会看到别的事务插入的数据的。
因此,幻读问题在 “当前读”下才会出现。当前读就是读取最新版本数据。
MySQL 通过加 Next-Lock Key(行锁+间隙锁) 的方式来解决幻读,Next-Lock Key 锁的规则比较复杂,在某些情况会退化为行锁、间隙锁来提高效率。
通过锁住对应的范围来防止其他事务对该范围内数据进行增删进而防止改变数据条数。但是注意 Next-Lock Key 锁住的是索引,不是数据本身,如果 where 后无索引列,那么相当于整个表都被锁死,直到事务结束。
对应的 RC–>RR 当前读不可重复读是靠行锁解决。

加锁语法
select … lock in share mode:加共享(S)锁
select … for update:加排他(X)锁
insert / update / delete:加排他(X)锁

Next-Key Lock 的算法

假设索引 a 有1,3,5,8,11,其记录的GAP的区间如下:是一个左开右闭的区间(原因是默认主键的有序自增的特性) (-∞,1],(1,3],(3,5],(5,8],(8,11],(11,+∞) select * from t where a = 3 for update; 那么锁住的范围有 (1,3)3,(3,5] 即 当前行+两边的间隙 (1,3],(3,5], 这些范围的行数据和索引都被锁住,所以可以防止 insert 或者 delete 带来的幻读
但是注意,对于RR默认使用的就是 next key lock,但是对于“唯一索引” ,比如主键的索引,next key lock 会降级成行锁 Record Lock ,即仅锁住索引本身,而不会锁住一个区间。唯一索引的等值条件就是从 next lock key降级成行锁。
而范围条件如
select * from t where a > 3 for update;
就降级到间隙锁锁住(3,5]。如果条件是>=3那就是行锁+间隙锁都用上了。


锁机制

锁分了乐观、悲观两大类别
悲观锁和乐观锁区别如下:
悲观锁是在是否能操作数据上就做了处理。假设会发生冲突。

悲观锁的实现

悲观锁是数据库已实现的,如共享锁、排它锁。

共享锁

又称为读锁,如果事务T1对数据对象O1加上了共享锁,那么当前事务只能对O1进行读取操作,其他事务也只能对这个数据对象加共享锁读取数据——直到该数据对象上的所有共享锁都被释放。修改是唯一的,必须等待前一个事务commit, 才可进行操作,因此,如果同时并发太大可能很容易造成死锁。

排它锁

又称为写锁或独占锁,如果事务 T1 对数据对象 O1 加上了排他锁,那么在整个加锁期间,只允许事务 T1 对 O1 进行读取和更新操作,其他任何事务都不能再对这个数据对象进行任何类型的操作(读、写)——直到 T1 释放了排他锁, 结果是超时或者等待第一个事务提交后再执行,相当于行锁。

排它锁和共享锁区别
加上排他锁后,数据对象只对一个事务可见,而加上共享锁后,数据对所有事务都可见。
误区:对于共享锁大家可能很好理解,就是多个事务只能读数据不能改数据,对于排他锁大家的理解可能就有些差别,我当初就犯了一个认知错误,以为排他锁锁住一行数据后,其他事务就不能读取和修改该行数据,其实不是这样的。 排他锁指的是一个事务在一行数据加上排他锁后,其他事务不能再在其上加其他的锁。MySQL 的 InnoDB 引擎自动的给修改数据语句(update,delete,insert)涉及到的数据加上排他锁,select 语句默认不会加任何锁类型。因此可以说明数据只要不加锁, 普通的 select…form… 是没有问题的,而加锁的 select 就读不到,值得一提的是加共享锁、排它锁都是显式的加锁,但是隐式的加锁(insert、delete、update)也读不到数据,原因就是不能加锁。
手动给查询语句加排它锁select … for update
手动给查询语句加共享锁select … lock in share mode
所以加过排他锁的数据行在其他事务中是不能修改数据的,也不能通过加排他锁或共享锁的方式查询数据,但可以直接通过select … from … 查询数据,因为普通查询没有任何锁机制。
乐观锁下大家都可以同时写数据只是在提交的时候做了处理。乐观的假设不会发生冲突。
乐观锁可以避免数据库幻读、业务处理时间过长等问题,适合读多写少,可以提高程序的吞吐量解决了写-写冲突。
而乐观锁要自己实现,如何实现呢?

乐观锁的实现

CAS

Java 中java.util.concurrent.atomic包下面的原子变量使用了乐观锁的一种 CAS 实现方式。

版本号控制

一般是在数据表中加上一个数据版本号 version 字段,表示数据被修改的次数。当数据被修改时,version 值会+1。当线程 A 要更新数据时,在读取数据的同时也会读取 version 值,在提交更新时, 若刚才读取到的 version 值与当前数据库中的 version 值相等时才更新,否则重试更新操作,直到更新成功。

MVCC

MVCC其实就是乐观锁的一种实现方式,MVCC只能在读已提交(RC)和可重复读(RR)两种隔离级别下工作,读未提交总是会读到最新的数据行,而不是符合当前事务版本的事务行的快照读,串行化是通过加锁互斥数据,因此不存在隔离问题。
不好理解?
那换句话说:
对于使用Read Uncommitted隔离级别的事务来说,只需要读取版本链上最新版本的记录即可;
对于使用Serializable隔离级别的事务来说,InnoDB使用加锁的方式来访问记录。
MVCC如何工作
MVCC是一种基于版本链的无锁并发控制,即使有读写冲突时,也能做到不加锁,非阻塞并发读。
MVCC仅在RC、RR下运作,原理就是 Read View(快照读) 四个参数、数据库表的两个隐藏列。 Read View 四个参数:活跃事务列表(m_ids)、最小活跃事务id(min_trx_id)、最大活跃事务id+1(max_trx_id)、创建当前 Read view 的事务 id(**creator_trx_id **)。

数据库表两个隐藏列
trx_id:当一个事务对某条聚簇索引记录进行改动的事务 id。
roll_pointer:指向上一版本的 undo 日志。

MVCC 在 RC、RR 下的工作
一个事务访问记录时除了自己的更新记录可见(trx_id = creator_trx_id),就是隔离级别分辨哪些版本的记录对当前事务可见。

分辨 已提交的事务、活跃事务、未开启的事务
通过 trx_id 比较是否 < min_trx_id 或是 >= max_trx_id,是的话该事务就不在活跃事务列表也就不是活跃事务,小于是已提交事务,大于等于是未开启的事务版本记录。
又或是 min_trx_id <= trx_id < max_trx_id,那么当前记录可能在活跃事务列表,需要判断 trx_id 是否在 m_ids 列表中:存在就是活跃事务版本,否则就是已提交事务

RC
因为每次查询数据都会生成一个 read view 所以其中的四个参数一直在变,因此每一次都可以拿要查询的记录的 trx_id 去查询该事务是否已提交,只要是已提交的事务版本就可见,如果未提交就沿着 undo log 链条往下找旧版本的记录 ,直到找到已提交。

RR
因为每次查询都是该事务开启时生成的 read view ,所以每次拿到要找的记录的 trx_id 去和 对应 read view 对比找到已提交的最终版本(和RC一样步骤)。

trx_id 如何联系 read view
trx_id 隐藏列 与 creator_trx_id 参数。

事务版本号如何计算
事务版本号随着事务的开启顺序依次递增。

存储引擎

MySQL 存储引擎采用的是插件式架构,支持多种存储引擎,我们甚至可以为不同的数据库表设置不同的存储引擎以适应不同场景的需要。存储引擎是基于表的,而不是数据库。 MySQL默认的存储引擎是InnoDB,默认字符集是UTF-8,建表时指定存储引擎和字符编码方式。 MySQL支持九大引擎,版本不同支持情况不同。 存储引擎表示不同的数据在磁盘中的不同组织形式。

  1. MyISAM存储引擎? 它管理的表具有以下特征:只支持表锁、不支持外键、不支持回滚。 格式文件 - 存储表结构的定义(mytable.frm) 数据文件 - 存储表行的内容(mytable.MYD) 索引文件 - 存储表上的索引(mytable.MYI) MyISAM优势:可被转化为压缩、只读表来节省时间。
  2. InnoDB存储引擎 这是MySQL默认的存储引擎,是个重量级的存储引擎。 InnoDB支持事务,支持数据库崩溃后自动恢复机制。支持表锁行锁。 InnoDB存储的特点:非常安全。
  3. MEMORY存储引擎 数据存储在内存中,且表的长度固定,这两个特点使得MEMORY存储引擎非常快。不安全,关机数据消失。

InnoDB中存储数据

InnoDB中存储数据是按行存储的,但数据库的读取是以页为单位读取,也就是说当需要读取一条数据时,并不是将这个记录本身从磁盘中读取出来,而是以页为单位,将其整体读入内存。也因此数据库的 I/O 操作的最小单位也是页, InnoDB的数据页默认大小为16KB。也就是说一次至少把 16KB 的内容读取到内存。

数据页由7个部位

主从复制

主从复制的作用

  1. 读写分离,降低服务器压力实现了负载均衡。
  2. 主服务器出现故障时可切换到从服务器,提高性能。
  3. 从服务器备份避免备份过程影响主服务器服务,确保数据安全。

主从复制的原理

数据库下bin-log二进制文件,记录了所有的sql语句,把主数据库下的bin-log文件的sql语句拷贝过来让其在从数据库的redo-log(重做日志文件)再执行一遍这些sql语句。需要三个线程操作。
1. binlog线程:每当有从库连接到主库的时候,主库都会创建一个线程然后发送binlog内容到从库。在从库里,当复制开始的时候,从库就会创建下列的两个线程进行处理;
2. dump线程:主节点为每一个从节点创建一个 dump 线程,当 binlog 有变化,dump 线程就会通知从节点,并将 binlog 传递给从节点的 IO 线程。
3. Io线程:当START SLAVE语句在从库开始执行之后,从库创建一个I/O线程,接收到了内容后写入到 relay log(中继日志),再返回给主库“复制成功”的响应。 4. Sql线程:从库创建一个SQL线程,这个线程读取从库I/O线程写到relay log的更新事件并执行。

主从复制和主主复制区别

最大区别是: 主从复制是对主数据库操作数据,从数据库会实时同步数据。对从数据库操作,主数据库不会同步数据,还有可能造成数据紊乱,导致主从失效。 主主复制则是无论对哪一台操作,另一个都会同步数据。一般用作高容灾方案 。

limit使用

  1. 当limit后面跟两个参数的时候,第一个数表示要跳过的数量,后一位表示要取的数量,例如 select * from user limit 1,3; 取到 2,3,4
  2. 当 limit和offset组合使用的时候,limit后面只能有一个参数,表示要取的的数量,offset表示要跳过的数量 。例如select * from user limit 3 offset 1; 取到 2,3,4

索引

索引是在存储引擎用于提高数据库表的访问速度的数据结构,如果不添加索引查询会加载所有的数据进内存依次检索。
索引在数据库表的字段上添加,为了提高查询效率。一张表的一个字段可以添加一个索引,也可以多个字段联合。

索引的使用场景

  1. 数据量庞大。
  2. 该字段常出现在where后面,以条件形式存在,也就是说这个字段总是被扫描。
  3. 经常用于 GROUP BY 和 ORDER BY 的字段。
  4. 该字段很少的DML(增删改)操作。因为DML之后,索引需要重新排序。

索引的设计原则

  1. 区分度越高越好。
  2. 尽量使用短索引,较长字符串进行索引的时候应该指定个较短前缀,因为较小索引 I/O量小查询速度快。
  3. 索引不是越多越好,每个索引要花额外的物理空间,维护要需要时间。
  4. 利用最左前缀原则。

根据索引查询的原理

例如 select * from t_user where id =101;发现id字段上有索引先通过索引对象idIndex进行树的查找,通过 id=101 得出物理编号0x666 ,此时会转换SQL语句为: select * from t_user where 物理编号 = 0x666;

索引分类

单一索引、联合索引、主键索引、唯一性索引。(唯一性弱的字段添加索引用处不大)。 MySQL 中如果有 unique 约束也会自动创建索引对象,其实殊途同归,任何数据库的主键都会自动添加索引对象,其实都是为唯一键建立的索引,而主键是唯一且非空。
在MySQL 中,索引是一个单独的对象,索引在不同的存储引擎以不同形式存在。
MyISAM 存储引擎中,索引存储在.MYI 文件中。
InnoDB 存储引擎中索引存储在一个逻辑名 tablespace 的空间当中。
MEMORY 存储引擎中,索引存储在内存当中。
不管索引存储在哪里,索引在 MySQL 当中始终都是树的形式存在。
索引要排序,排序了才有区间查找,并且这个索引的排序和 TreeSet 数据结构相同,一个自平衡的二叉树。
MySQL的数据存储在磁盘中,查询慢一般卡都是在 I/O 过程,尽可能减少 I/O 的次数和 I/O 的量,可以有效优化查询速度,而这二者之间更重要的又是 I/O 次数。因此“矮胖”型的树结构是性能最高的。

索引的存储

索引存储的时候需存文件地址、偏移量offset、key,可以用 哈希表,树(二叉树、红黑树、AVL树、B树、B+树)为什么最后用B+树存。

为什么不用哈希表来存(k-v)
首先哈希表来存确实有个优点就是:等值查询时,速度很快!但同样它有以下缺点如:

  1. 哈希冲突导致数据散列不均匀,会产生大量的线性查询,比较浪费时间。
  2. 必须等值判断来查询,不支持范围查询,当进行范围查询时必须挨个遍历。
  3. 对于内存空间的要求比较高,要把所有数据加载到内存才能操作找到对应的数据。

MySQL 中常见的存储引擎有没有hash索引

  1. memory 存储引擎使用的是hash索引。
  2. Innodb 支持自适应 hash 索引,key 是索引列的 hashcode,指向行记录的指针是 value 。查找一条数据的时间复杂度O(1),多用于精确查找。

为什么不用普通树的结构
如果用的是普通二叉树,那数据插入是递增的时候就会从二叉树 O(logn)退回成链表 O(n)。但是可以通过左旋或者右旋让树平衡起来弥补这一缺陷,此时形成的是 AVL (平衡二叉树): 最短子树跟最长子树高度只差1,为了保证平衡,在插入数据的时候必须要旋转,通过插入、删除性能的损失来弥补查询性能(插入、删除会调整节点)。
但是此时又出现了新的问题:如何 读 » 写,AVL 是划算的,但是读写差不多甚至 写 » 读 呢?
所以就要左旋右旋的次数减少来提高增删的效率。怎样使得它更少的旋转呢?


为什么不用红黑树
通过变色减少旋转的次数,最长子树只要不超过最短子树的两倍即可,既有旋转又有变色,使得插入和查询性能近似取得平衡。但是随着数据的插入,发现树的深度会变深,树的深度越深,意味着 IO 次数越多, 影响数据的读取效率,而且由于局部性原理(经常被查询的数据有聚集成群的倾向,同时刚被查询的数据有可能很快被再次查询)、磁盘预读,使得它不得不做出改变,做出了在树的横向做文章 的改变,一个头节点对应多个子节点就解决了这个问题,这是采用的数据结构就是 B树 了,树的深度会小很多,一般<=3,深度是算出来的,不是指定的。这种情况下实际存储的数据为:key,完整的数据行。但 是它叶子节点和非叶子节点都存有数据行使得树在每一深度的索引都存的十分有限,不理解?
举个例子:假设一个磁盘块的大小为 16k ,假设一条行数据 1k ,树深度为三层。
第一层 16k 最多存 16 个行记录。
第二层能存 16×16 个行记录。
第三层 161616条记录 = 4096条。
这效率太低了吧?
只让叶子节点存数据,非叶子节点只存 索引 和 key 值,一二层就能存更多的磁盘块索引,这就是 B+ 树。我们来算算,如果没有创建主键约束、唯一键约束、系统会自生成 6字节的 rowId 给它作为聚簇索引 的 key 是 6+6(指针大小在 InnoDB 源码中设置为6字节),这能存多少?大概两千多万。而且 B+树 的叶子节点用指针连接,提高区间访问性能也方便扫库。那索引是用 int 还是 varchar 呢?Varchar超过四 个字节用 int ,小于四个字节用 varchar,因为空间固定是16kb,每个索引*索引个数<= 16kb,占的单个字节越少,存储的字节就可以越多。索引的创建和存储引擎是挂钩的。是否是聚簇索引取决于数据是否和 索引放在一起。Innodb 只能有一个聚簇索引(为了防止数据冗余,如果有多个聚簇索引,就会导致一份数据存多份,多个索引,只能有一个作为 key,如果多个作为 key 就冗余了),但是可以有很多非聚簇索引 :向 innodb 插入数据时必须要包含一个索引的 key 值,这个索引的 key 值可以是主键,如果没有主键就是唯一值,如果没有唯一值那就是自生成的6字节的 rowId 当聚簇索引 key 。如果一个表中的普通列创 建了索引,那么叶子结点存放的值是聚簇索引的 key 值。
Myisam 全是非聚簇索引,但是与 innodb 中不同的是,innodb 直接放数据行,myisam 放的是数据行地址,根据地址再去找数据。聚簇索引是和数据文件和索引文件放在一起的索引。

*B+树三层怎么算的(n叉查找树) 三层可以存储大约2千万行数据

  1. InnoDB存储引擎默认最小存储单元是页,默认一个页的存储大小是16K(可以修改,通过设置参数innodb_page_size)
  2. InnoDB的所有数据文件(后缀为ibd的文件)它的大小始终是16K的整数倍
  3. 页可以存储数据也可以存储键值+指针,在B+树叶子节点存放数据,非叶子节点存放键值+指针
  4. 如果这一页存储的是非叶子节点,假设主键id为bigint类型,长度为8个字节,页号在InnoDB中是4个字节。这样一共12个字节,则一页能能存储多少个这样的单元,就代表一页能存储多少指针,即大约16384/12=1280 个指针
  5. 如果这一页存储的是叶子节点,假设一行记录大小为1K(实际上很多互联网业务数据记录大小通常就是1K左右),则一页可以存储16条记录。
  6. 若一颗高度为2的B+树,则能存放大约1280 X 16 = ?条记录 ,为什么X16:1层的16k数据页可以放1280 个(索引+指针),分布到第二层是1280 个数据页,每个数据页不仅仅包含这一条1k的行记录,而是还有其他15k行记录。
  7. 同样,若一棵高度为3的B+树,能存放1280 X 1280 X 16 = 2.45k万数据。
  8. 所以InnoDB中B+树的高度一般为1~3层,就能满足千万级的数据存储。
  9. 在查询数据时一次页的查找代表一次IO,所以通过主键索引查询通常只需要1~3次 I/O 操作即可查找到数据。

索引的失效

  1. 对一个索引字段模糊查询时%写在索引左边失效(特殊情况:表里 只有该字段+聚簇索引)优化器判定可以索引覆盖也就无需再全表扫描,除非有其它非聚簇索引字段,那么需要走两张表而且左模糊查询并不能体现b+树的有序性查找性能,还不如只走聚簇索引树全表扫描一张表。
  2. 使用or的时候可能失效,要求or两端的字段都有索引才会走索引,union不会让索引失效。
  3. (最左原则)使用复合索引的时候没有使用索引左侧的列查找,索引失效。
  4. where当中索引列参与了运算,索引失效。
  5. 在where中索引列使用了聚合函数。Explain select * from zs_case where lower(case_name)=’rxy’;
  6. 、<、!=、not in、in、IS NULL或者IS NOT NULL都会导致。>=、<= 不会。

  7. 对全表扫描速度比索引速度快的时候不会使用索引。
  8. 对索引隐式类型转换。

回表索引覆盖

回表:以非聚簇索引查找包含非聚簇索引的内容
索引覆盖:以非聚簇索引查找聚簇索引
一张表四个字段 (id,sex,name,age,name)是索引列,主键是id,select * from table where name=‘rxy’ 该语句先会根据非聚簇索引 name 字段查询到 id,再根据聚簇索引 id 字段查询整行记录,走了两棵 b+树,此时这种现象叫做回表

b+树分为非聚簇索引树和聚簇索引树,聚簇索引树叶子节点存放行记录,非聚簇索引树叶子节点保存了2列(聚簇索引,该非聚簇索引)数据。

就是根据普通索引查询到聚簇索引的 key 值后,再根据 key 值在聚簇索引中获取行记录。如果是 select id,name from table where name=‘rxy’; 该语句根据 name 可以直接查询到 id ,name 两个字段,直接返回即可,不需要从聚簇索引查询任何数据,此时就不需要回表,叫做索引覆盖,走一棵 B+ 树

回表会增加IO量,会影响效率。

索引下推

索引下推是 MySQL 5.6 版本中提供的一项索引优化功能,可以在非聚簇索引遍历过程中,对索引中包含的字段先做判断,过滤掉不符合条件的记录,减少回表次数。

eg:联合索引(A,B,C) 如果 where A=? and C=?
无索引下推会走索引 A 从引擎层返回数据到 server 层再在 server 层过滤 C。 而有索引下推直接在引擎层走了 A、C ,因此减少了回表数据。

设计数据库时id要不要自增

尽量自增,自增不会影响前面的磁盘块,如果不自增,可能插入一个数据在一个容量不够的磁盘块中,则会磁盘块分裂(页分裂),类似的操作删除会导致页合并。
分裂调整效率比较低,而自增直接append效率比较高。或者说,id由UUID随机生成,比较分裂,随机插入,会造成频繁页分裂,内存碎片化,大量随机IO。

最左匹配原则

联合查询下:
MySQL内部有优化器: select * from table where 最左索引 = ‘ss’ and 其他索引 = ‘hh’;
把两个索引位置对换也会走索引。因为优化器会重新把最左索引放在左边。但是如果where中没有用到最左索引,此时其他索引是无序的,就不会走索引。
如果用范围查询,就会停止该索引字段之后的字段的匹配。
比如 a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d 是用不到索引的,因为 c 字段是一个范围查询,从 c 开始都是无序的,它之后的字段会停止匹配。
注:联合索引条件下,优化器调整的是写的sql的查询条件的顺序,调整成可以被走最多索引的顺序。

MySQL常见的日志:

redo log(重做日志):用于掉电等故障恢复。
undo log(回滚日志)
binlog(二进制日志):全量日志,用于备份恢复、主从复制;

redo logo redo log 是 InnoDB 引擎特有的,只记录该引擎中表的修改记录。
binlog 是 MySQL 的 Server 层实现的,会记录所有引擎对数据库的修改,所有引擎可见。
redo log 是物理日志,记录的是在具体某个数据页上做了什么修改;binlog是逻辑日志,记录的是这个语句的原始逻辑。
redo log 是基于两个文件环形文件组的循环写的,空间固定会用完,用完了就循环写;binlog 是可以追加写入的,binlog 文件写到一定大小后会切换到下一个,并不会覆盖以前的日志。
而 redo log 实际本不需要那么多,因为 redo log只是保证 buffer pool 里的脏数据落盘,脏数据会依次落盘,并不会堆积着。如果满了,那么MySQL更新操作会被阻塞,
redo log(重做日志)让 Innodb 有了崩溃恢复的能力,MySQL 实例挂了或宕机后重启时 Innodb 会使用 redo log 恢复数据,保证数据的完整和持久。
后续的查询直接在查询缓存中查询,没命中再去硬盘加载,减少了 IO 开销。更新表时也是这样,发现缓存里存在要更新的数据,就直接在缓存里更新,然后会记录“哪个数据页做了什么修改”记录在 redo log 的缓存,接着刷盘到 redo log
理想状态是事务一提交就刷盘,实际上刷盘时机是根据策略来进行,有0,1,2三个策略。
0:每次提交不刷。
1(默认值):每次提交都刷。
2:每次事务提交都只把 redo log缓存写入 page cache(系统缓存)。
另外Innodb存储引擎有一个后台线程,每隔一秒,就会把 redo log 缓存写到 page cache(系统缓存),然后刷盘。
也就是说一个没提交的 redo log 记录可能也会被刷盘,因为在事务执行过程 redo log 记录是会写入 redo log 缓存,这些 redo log 会被后台线程刷盘,除了后台每秒一次刷盘还有一种情况会导致刷盘, 当redo log 缓存占用的空间即将达到 innodb_log_buffer_size 的一半时,后台线程会主动刷盘。
所以,为0时,MySQL 挂了或宕机了可能有一秒的数据丢失。
为1时,redo log一定在硬盘里不会丢失,就算挂了,这部分日志也会丢失,但是事务并没提交,也不会损失。
为2时,只要事务提交成功了,redo log缓存内容只写入page cache,如果是MySQL挂了不会丢失数据,宕机可能会有1秒数据丢失。
binlog用于复制,从库利用主库上的bin log进行传播,实现主从同步。
事务提交的时候,一次性将事务中的 sql 语句(一个事物可能对应多个sql语句)按照一定的格式记录到 binlog 中。
binlog 与 redo log 很明显的差异就是 redo log 并不一定是在事务提交的时候刷新到磁盘,redo log 是在事务开始之后就开始逐步写入磁盘。
事务提交后断电,redo log 恢复数据,提交前 redo log 恢复数据之后 undo log 回滚事务。

binlog
MySQL 在完成一条更新操作后,Server 层还会生成一条 binlog,等之后事务提交的时候,会将该事物执行过程中产生的所有 binlog 统一写 入 binlog 文件。

binlog 日志有三种格式,可以通过binlog_format参数指定。

  • statement
  • row
  • mixed 指定 statement,记录的内容是SQL语句原文,比如执行一条 update T set update_time=now() where id=1
    同步数据时,会执行记录的 SQL 语句,update_time=now()这里会获取当前系统时间,直接执行会导致与原库的数据不一致。

为了解决这种问题,我们需要指定为 row,记录的内容不再是简单的SQL语句了,还包含操作的具体数据。
row格式记录的内容看不到详细信息,要通过mysqlbinlog工具解析出来。

update_time=now()变成了具体的时间update_time=1627112756247,条件后面的@1、@2、@3 都是该行数据第 1 个~3 个字段的原始值(假设这张表只有 3 个字段)。

这样就能保证同步数据的一致性,通常情况下都是指定为row,这样可以为数据库的恢复与同步带来更好的可靠性。

但是这种格式,需要更大的容量来记录,比较占用空间,恢复与同步时会更消耗IO资源,影响执行速度。

所以就有了一种折中的方案,指定为mixed,记录的内容是前两者的混合。

MySQL会判断这条SQL语句是否可能引起数据不一致,如果是,就用row格式,否则就用statement格式。

binlog 写入机制:
binlog的写入时机也非常简单,事务执行过程中,先把日志写到binlog cache,事务提交的时候,再把binlog cache写到binlog文件中。

因为一个事务的binlog不能被拆开,无论这个事务多大,也要确保一次性写入,所以系统会给每个线程分配一个块内存作为binlog cache。
在写到内存是很快的,但再进一步刷盘持久化到磁盘也有对应的刷盘策略。
0:表示每次提交事务都只write,由系统自行判断什么时候执行fsync。
1:表示每次提交事务都会执行fsync,就如同 redo log 日志刷盘流程 一样。
N:累积到了 N 个事务后才刷盘。

两阶段提交

redo log(重做日志)让InnoDB存储引擎拥有了崩溃恢复能力。保证主库数据。
binlog(归档日志)保证了MySQL集群架构的数据一致性。保证从库数据。

二者都属于持久化的保证,但侧重点不同,只要有一个落盘不成功,就会导致主从数据不一致。
在执行更新语句过程,会记录redo log与binlog两块日志,以基本的事务为单位,redo log在事务执行过程中可以不断写入,而binlog只有在提交事务时才写入,所以redo log与binlog的写入时机不一样。

话说回来,假设执行过程中写完 redo log 日志后,binlog 日志写期间发生了异常,会出现什么情况呢?
由于 binlog 没写完就异常,所以用 binlog 恢复数据时就会少了这次更新,而用 redo log恢复,则数据与用 binlog 恢复不一致。

为了解决两份日志之间的逻辑一致问题,InnoDB 引擎使用两阶段提交方案。
原理:将 redo log 的写入拆分为 prepare、commit 两个步骤。

使用两阶段提交后,写入 binlog 时发生异常也不会有影响,因为 MySQL 根据 redo log 日志恢复数据时,发现 redo log 还处于prepare阶段,并且没有对应 binlog 日志,就会回滚该事务。
但如果在 redo log “设置 commit 阶段”发生异常,是不会回滚事务的,因为虽然 redo log 是 prepare 阶段,但是能通过事务 id 找到对应的 binlog ,所以 MySQL 认为是完整的,就会提交事务恢复数据。

其它问题合集

数据库的读的延时问题

主库宕机后,数据可能丢失,从库只有一个 sql Thread,主库写压力大,复制很可能延时。
如何解决? 半同步复制解决数据丢失,并行复制解决从表复制延迟。

Exist和in的区别

exists是一个存在判断。
如果后面的查询中有结果,则exists为真,否则为假,检测行的存在。

delete、drop、truncate 的区别

delete、truncate都只是删除表数据,而drop连表结构一块删除了,打个比方,delete 是单杀,truncate 是团灭,drop 是把电脑摔了。
delete是DML语句,可以回滚,但truncate、drop是DLL语句,不支持回滚。 执行速度:drop>truncate>delete

视图

分为普通视图、物化视图。

首先明白基本概念:

  1. 普通视图
    普通视图是不存储任何数据的,它只有定义,在查询中是转换为对应的定义SQL去查询。
  2. 物化视图
    物化视图是将数据转换为一个表,实际存储着数据,这样查询数据,就不用关联一大堆表,否则如果表很大的话,会在临时表空间内做大量的操作。
    物化视图也分为两种。

两种物化视图的区别

on demand 物化视图和 on commit 视图两者刷新方式不同,二者的区别在于刷新方法的不同。
ON DEMAND顾名思义,仅在该物化视图“需要”被刷新了,才进行刷新(REFRESH),即更新物化视图,以保证和基表数据的一致性;
而 ON COMMIT 是说,一旦基表有了 COMMIT,即事务提交,则立刻刷新,立刻更新物化视图,使得数据和基表一致。

为什么不推荐用外键?

答:外键保障了数据质量,但是影响性能,每次 delete 或 update 都必须考虑外键约束,会导致开发痛苦,测试数据不方便。

数据类型的优化?

更小的通常更好:尽量使用不会超出范围的最小的占用空间的类型。
简单就好:什么类型就用什么类型去存,如果是 ip 就调用方法转换成 int 类型去存。因为 int 比 字符串 节省很多空间。
尽量避免使用 null:对于包含可为 null 列时很难优化。
实际类型细则
整数类型:可以使用的几种整数类型:tiny int、small int、medium int、int、big int 分别是8、16、24、32、64 位存储位置。尽量使用满足需求的最小的数据类型。
字符串类型:char、varchar、blob、text。(一个误区:文件大小和文件占用空间完全不同,因为 4kb 是一个读取数据的基本单元,那 4.01kb 的还是要占用两个 4kb 即两个基础单元(页)来存储,所以文件大小是 4.01kb, 占用空间是 8kb 。读一个数据会把该基础单元(页)里的数据都读进来这就是磁盘预读。)
varchar:使用最小符合需求的长度,varchar(n),当 n<=255 时会用额外一个字节保存长度, n> 255 时会使用额外两个字节来保存长度, varchar(5) 和 varchar(255) 保存同样内容硬盘存储空间相同,但内存占用空间不同。varchar 在 MySQL 5.6 前变更长度从 255 变更到 255 以上会导致锁表。Char 最大长度 255 ,会删除末尾空格。
时间戳类型:datetime 占 8 字节,与时区无关,数据库底层对时区对配置对其无效,不用要字符串类型来存,空间占用大。Timestamp 占 4 个字节,时间范围 1970-1-1 到 2038-1-19,精切到秒。Date类型占3个字节, 日期范围 1000-1-1 到 9999-12-31 。

查询语句的执行顺序

from子句 –> where子句 –> group by子句 –> 聚集函数–> having子句 –> select的字段 –> Distinct(去重) –> order by的字段 –> limit
表的关联项是从右到左解析,所以尽量把大的表项放在右边进行关联     group by(分组函数)不能直接在 where 子句中就是因为先执行 where 才执行分组 group by,where是无分组的过滤,having才是过滤分组。
select max (score) from zs_case
该语句可以执行就是因为 select 在聚集函数后执行。

查询如果分组 (group by) 了,那查询的字段只能和参与分组的字段相关,使用 having 可以对分完组后的数据继续过滤,但他必须和 group by 联合使用,而且 where 比 having 效率高。

如果数据不分组,那整张表就默认为一组,分组函数自动忽略null,判断 null 用 isnull 而不是 = 。

查询优化怎么做

  1. 减少请求的数据量:只返回有必要的列尽量避免 select * 、只返回有必要的行。
  2. 用 limit 限制、缓存重复查询的数据。
  3. 减少服务端扫描的行数:最有效就是避免回表(走两棵 b+ 树),而是尽量索引覆盖查询(走一棵 b+ 树)。
  4. 在表中建立索引,优先考虑 where、group by 使用到的字段。
  5. where 子句模糊查询用在在字段后面,不要放在前面,否则会索引失效导致全表扫描。
  6. 尽量不要使用in 和 not in 和 or,会造成全表扫描。对于连续的数值,用 between 代替 in,对于子查询,用 exists 代替 in。union 代替 or,或者用 or 时两个字段都要是索引字段。
  7. 尽量不要在 where 子句中对字段进行表达式操作,会导致索引失效扫描整张表。
  8. 给字段添加默认值,不要对 null 进行判断,null 的判断会扫描整张表。

执行计划怎么看

  • id:表项的查询顺序,注意是最大的先执行,也就是子查询先执行,查询的顺序如上。
  • table:查询涉及的表或衍生表。
  • select_type
    1. simple:不包含子查询或 union。
    2. primary:包含子查询的 SQL 的最外层 select。
    3. subquery:子查询中的第一个 select。
    4. derived:派生/衍生表的 select, from 子句的子查询。
    5. union:包含 union 的查询中的第二个或后面的 select 语句。
    6. union result: union 的结果。
  • type
    1. ALL:全表扫描。
    2. index:只扫描索引树,此时 select 的字段需要的字段不需要回表,则在 extra 会显示 using index。
    3. range:索引范围扫描。
    4. ref:非唯一性索引扫描,结果可能是多行,此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了最左前缀规则索引的查询。
    5. const、system:只有按照全部主键查询,执行计划type为const,也就是说此时查询出来的数据唯一,system就是其中特别的,表中只有一条数据,但在innodb中仍然显示为 const,而在 myisam 中显示 system。
    6. eq_ref: 除了system和const类型之外,效率最高的连接类型;唯一索引扫描,对于每个索引键,表中只有一条记录与之对应;常用于主键或唯一索引扫描。
  • possible_keys:where子句包含的字段上若存在的索引,但该索引不一定被查询所使用。
  • key:MySQL查询实际使用到的索引。
  • key_len:表示索引占用的字节数。
  • ref:显示该表的索引字段关联了哪张表的哪个字段。
  • filtered:返回结果的行数占读取行数的百分比,值越大越好。
  • rows:根据表统计信息及选用情况,大致估算出找到所需的记录或所需读取的行数,数值越小越好。
  • extra:包含不适合在其他列中显示但十分重要的额外信息。常见的值如下
    1. use filesort:MySQL会对数据使用非索引列进行排序,而不是按照索引顺序进行读取;若出现改值,应优化索引
    2. use temporary:使用临时表保存中间结果,比如,MySQL在对查询结果排序时使用临时表,常见于order by和group by;若出现改值,应优化索引
    3. use index:表示select操作使用了索引覆盖,避免回表访问数据行,效率不错。
    4. use where:包含 where 子句。

排查MySQL问题的手段

  1. show processlist命令查询当前所有连接信息。
  2. Explain命令查询sql语句执行计划。
  3. 开启慢查询日志,查看慢查询的日志。

update语句执行流程

  1. 应用程序把 sql 发送到 server 端执行。
  2. 连接器:与客户端进行 TCP 三次握手建立连接;校验客户端的用户名和密码;读取该用户的权限,然后后面的权限逻辑判断都基于此时读取到的权限;
  3. 查询缓存:查询并清空查询缓存(MySQL 8.0 版本后移除,因为这个功能不太实用)。
  4. 分析器 :词法语法分析,提取表名查询条件。检查语法是否错误。
  5. 预处理器:检查表名字段名是否存在分析器
  6. 优化器 :优化器根据自己的算法选择效率高的执行计划。
  7. 执行器 :校验权限,有权限就调用数据库引擎接口,返回引擎的执行结果。 eg:更新会写日志文件。