知识点汇总

一条语句的执行

img

(以上图片来自极客时间 欢迎购买对应专栏)

索引

目的

• 减少扫描的数据量
• 把随机IO变成顺序IO
• 避免分组、排序等操作时，生成临时表

B+树

• 多路绝对平衡查找树，深度低
• 磁盘读写能力更强(相邻读取 IO次数减少)
• 遍历更加方便

表现形式

MyISAM: 叶子节点上放的是数据地址

InnoDB: 聚簇索引，放的是全部数据，聚簇索引就是表 -> 普通索引需要回查 -> 覆盖索引

索引原则

• 离散性：
• 最左匹配原则
• 联合索引
  • 经常使用的列优先(最左匹配)
  • 选择性高的列优先(离散性)
  • 宽度小的列优先(最少空间原则)

索引失效的常见误区

• 匹配列前缀可用到索引 like 9999%， like %9999%、 like %9999用不到索引；
• Where 条件中 not in 和 <>操作无法使用索引
• 匹配范围值， order by 也可用到索引
• 联合索引中如果不是按照索引最左列开始查找， 无法使用索引
• 联合索引中如果查询中有某个列的范围查询， 则其右边的所有列都无法使用索引

外键

为了保证数据完整性而设计的

• 两个表都必须是InnoDb表

• 外键列必须建立了索引

• 外键关系的列，必须数据类型相似或一致，比如 tinyint 和 int可以，但是 char 和int不行

• 使用外键以后，如果子表试图创建一个在父表中不存在的外键值，InnoDB会拒绝任何INSERT或UPDATE操作。如果父表试图UPDATE或者DELETE任何子表中存在或匹配的外键值，最终动作取决于外键约束定义中的ON UPDATE和ON DELETE选项；

• 外键约束使用最多的两种情况：

  1）父表更新时子表也更新，父表删除时如果子表有匹配的项，删除失败；

  2）父表更新时子表也更新，父表删除时子表匹配的项也删除。

存储引擎

Mysql 采用的是插拔式的插件方式，是指定在表之上的，即一个库中的每一个表都可以指定专用的存储引擎。

不管表采用什么样的存储引擎，都会生成一个frm(表结构定义描述文件)

InnoDB

InnoDB采用MVCC来支持高并发，并且实现了四个标准的隔离级别，其默认级别是REPEATBALE READ(可重复读), 并通过间隙锁(next-key locking)策略防止幻读的出现。

间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引的间隙进行锁定，防止幻影行的插入。

InnoDB标的基于聚簇索引建立的，其索引结构和Mysql的其他存储引擎有很大的不同，聚簇索引对主键查询有很高的性能，不过它的二级索引必须包含主键列，因此主键应该尽可能的小。

InnoDB内部做了许多优化，包括从磁盘读取数据时采用的可预测性预读，能够自动在内存中创建hash索引以加速读操作的自适应哈希索引以及能够加速插入操作的插入缓冲区

InnoDB的数据存储在表空间中，表空间是由InnoDB管理的一个黑盒子，由一系列的数据文件组成。

优势：

• 支持事务安装：InnoDB 最重要的一点就是支持事务，可以说这是 InnoDB 成为 MySQL 中最流行的存储引擎的一个非常重要的原因。InnoDB 还实现了 SQL92 标准所定义的 4 个隔离级别（READ UNCOMMITTED，READ COMMITTED，REPEATABLE READ 和 SERIALIZABLE）。
• 灾难恢复性好：InnoDB 通过 commit、rollback、crash-recovery 来保障数据的安全。具体来说，crash-recovery 就是指如果服务器因为硬件或软件的问题而崩溃，不管当时数据是怎样的状态，在重启 MySQL 后，InnoDB 都会自动恢复到发生崩溃之前的状态，并回到用户离开的地方。使用行级锁：
  • InnoDB 改变了 MyISAM 的锁机制，实现了行锁。虽然 InnoDB 的行锁机制是通过索引来完成的，但毕竟在数据库中 99%的 SQL 语句都要使用索引来检索数据。行锁定机制也为 InnoDB 在承受高并发压力的环境下增强了不小的竞争力。
  • 在 SQL 查询中可以自由地将 InnoDB 类型的表与其他类型的表混合起来，甚至在同一个查询中也可以混合。
• 实现了缓冲处理：InnoDB 提供了专门的缓存池，实现了缓冲管理，不仅能缓冲索引也能缓冲数据，常用的数据可以直接从内存中处理，比从磁盘获取数据处理速度要快。相比之下，MyISAM 只是缓存了索引。
  • InnoDB 的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘分区）。这与 MyISAM 表不同，比如在 MyISAM 表中每个表被保存在分离的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制为 2GB 的操作系统上。
• 支持外键：InnoDB 支持外键约束，检查外键、插入、更新和删除，以确保数据的完整性。在存储表中数据时每张表的存储都按主键顺序存放，如果没有显式地在定义表时指定主键，InnoDB 会为每一行生成一个 6 字节的 ROWID ，并以此作为主键。
  • InnoDB 实现外键引用这一重要特性，使在数据库端控制部分数据的完整性成为可能。虽然很多数据库系统调优专家都建议不要这样做，但是对于不少用户来说，大部分情况下，在数据库端加外键控制仍然是成本最低的选择。

四大特性

• 插入缓冲(insert buffer)

• 二次写(double write)

• 自适应哈希索引(ahi)：Innodb存储引擎会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，二级索引成为热数据，建立哈希索引可以带来速度的提升

  经常访问的二级索引数据会自动被生成到hash索引里面去(最近连续被访问三次的数据)，自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快。
  哈希（hash）是一种非常快的等值查找方法，在一般情况下这种查找的时间复杂度为O(1),即一般仅需要一次查找就能定位数据。而B+树的查找次数，取决于B+树的高度，在生产环境中，B+树的高度一般3-4层，故需要3-4次的查询。

• 预读(read ahead):预读机制就是发起一个i/o请求，异步地在缓冲池中预先回迁若干页面，预计将会用到回迁的页面，这些请求在一个范围内引入所有页面。InnoDB以64个page为一个extent

MyISAM

MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务和行级锁，而且有一个毫无疑问的缺陷是崩溃后无法安全恢复。

对于只读的数据，或者表比较小，可以忍受修复操作，则依然可以继续使用MyISAM

MyISAM会将表存储在两个文件中：数据文件和索引文件，分别以.MYD和.MYI为扩展名。

特性：

• 加锁与并发
  • MyISAM对整张表加锁，而不是针对行。读取时会对所有用到的表加共享锁，写入则加排他锁。但是有读取的时候，可以往表中插入新的记录(并发插入)

区别

优化

EXPLAIN语句

select_type 查询类型

查询的类型， 主要是用于区分普通查询、 联合查询、 子查询等

• SIMPLE： 简单的select查询， 查询中不包含子查询或者union
• PRIMARY： 查询中包含子部分， 最外层查询则被标记为primary
• SUBQUERY/MATERIALIZED： SUBQUERY表示在select 或 where列表中包含了子查询
  MATERIALIZED表示where 后面in条件的子查询
• UNION： 若第二个select出现在union之后， 则被标记为union；
• UNION RESULT： 从union表获取结果的select

table

涉及到的表名

type 访问类型

结果值从好到坏依次是：
system > const > eq_ref > ref > range > index > ALL

system： 表只有一行记录（等于系统表） ， const类型的特例， 基本不会出现， 可以忽略不计
const： 表示通过索引一次就找到了， const用于比较primary key 或者 unique索引
eq_ref： 唯一索引扫描， 对于每个索引键， 表中只有一条记录与之匹配。 常见于主键 或 唯一索引扫描
ref： 非唯一性索引扫描， 返回匹配某个单独值的所有行， 本质是也是一种索引访问
range： 只检索给定范围的行， 使用一个索引来选择行
index： Full Index Scan， 索引全表扫描， 把索引从头到尾扫一遍
ALL： Full Table Scan， 遍历全表以找到匹配的行

possible_keys

可能用到的索引

key

实际用到的索引，为NULL未使用索引

rows

根据表统计信息或者索引选用情况， 大致估算出找到所需的记录所需要读取的行数

filtered

它指返回结果的行占需要读到的行(rows列的值)的百分比
表示返回结果的行数占需读取行数的百分比， filtered的值越大越好

Extra 额外信息

• using filesort: 使用外部文件内容进行排序
• using temporary: 查询中使用了临时表，常见于order by 或 group by
• using index: 使用了覆盖索引
• using where: 使用了where过滤条件
• select tables optimized away: 基于索引优化MIN/MAX操作或者MyISAM存储引擎优化COUNT(*)操作， 不必等到执行阶段在进行计算， 查询执行计划生成的阶段即可完成优化

事务

事务是数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作；
事务是一组不可再分割的操作集合（工作逻辑单元）

ACID

• 原子性(Atomicity)：最小工作单元
• 一致性(Consistency)：事务的执行不能破坏数据库数据的完整性和一致性，一个事务在执行之前和执行之后，数据库都必须处于一致性状态
• 隔离性(Isolation): 并发的事务相互隔离
• 持久性(Durability): 事务提交后，数据的变更将永久的保存到数据库中

四种隔离级别

InnoDB的锁

• 共享锁（行锁）：Shared Locks
• 排它锁（行锁）：Exclusive Locks
• 意向锁共享锁（表锁）：Intention Shared Locks
• 意向锁排它锁（表锁）：Intention Exclusive Locks
• 自增锁：AUTO-INC Locks

行锁的算法

• 记录锁 Record Locks
• 间隙锁 Gap Locks
• 临键锁 Next-key Locks

共享锁(Shared Locks)和排他锁（Exclusive Locks)

共享锁：

• 共享锁又称为读锁，简称S锁， 顾名思义， 共享锁就是多个事务对于同一数据可以共享一把锁，
  都能访问到数据， 但是只能读不能修改;
• 加锁释锁方式：
  select * from users WHERE id=1 LOCK IN SHARE MODE;
  commit/rollback

排他锁：

• 又称为写锁， 简称X锁， 排他锁不能与其他锁并存， 如一个事务获取了一个数据行的排他锁， 其他事务就不能再获取该行的锁（共享锁、 排他锁） ， 只有该获取了排他锁的事务是可以对数据行进行读取和修改， （其他事务要读取数据可来自于快照）
• 加锁释锁方式：
  delete / update / insert 默认加上X锁
  SELECT * FROM table_name WHERE ... FOR UPDATE
  commit/rollback

数据库的增删改操作默认都会加排他锁，而查询不会加任何锁

InnoDB的行锁是通过给索引上的索引项加锁来实现的

即只有通过索引条件进行数据检索，InnoDB才使用行级锁，否则，InnoDB将使用表锁（锁住索引的所有记录）

意向锁（innodb特有）分意向共享锁和意向排他锁。

• 意向共享锁：表示事务获取行共享锁时，必须先得获取该表的意向共享锁；
• 意向排他锁：表示事务获取行排他锁时，必须先得获取该表的意向排他锁；

临键锁&间隙锁&记录锁

• 临键锁(Next-key locks)： 锁住记录+区间(左开右闭) ；

  • 当sql执行按照索引进行数据的检索时,查询条件为范围查找（ between and、 <、 >等） 并有数据命中则此时SQL语句加上的锁为Next-key locks， 锁住索引的记录+区间（ 左开右闭 )

  • 为InnoDB行锁的默认算法, 目的是防止幻读

    原理： 因为Innodb底层的B+Tree结构，底层是用了聚簇索引，底层叶子节点是有顺序的（根据主键的顺序）。临键锁键命中数据的区间+next区间锁住的目的就是为了不然其他事务在这连个区间插入数据，这样就解决了幻读的问题，这也就是为什么 Innodb的RR隔离级别能解决幻读问题。注意：Innodb的默认隔离级别是RR级别的（可以解决幻读问题）

• 间隙锁(Gap locks) :锁住数据不存在的区间（ 左开右开）

  • 当sql执行按照索引进行数据的检索时， 查询条件的数据不存在， 这时SQL语句加上的锁即为
    Gap locks， 锁住索引不存在的区间（ 左开右开）

• 记录锁(Record locks):锁住具体的索引项

  • 当sql执行按照唯一性（ Primary key、 Unique key） 索引进行数据的检索时， 查询条件等值匹配且查询的数据是存在， 这时SQL语句加上的锁即为记录锁Record locks， 锁住具体的索引项