网络知识 娱乐 MySQL高级学习笔记

MySQL高级学习笔记

注:毕笔记来源于 黑马程序员 MySQL数据库入门到精通。

文章目录

  • 第1章 MySQL的架构介绍
    • 1.1 MySQL简介
    • 1.2 在Linux上安装MySQL
    • 1.3 MySQL配置文件
    • 1.3 MySQL逻辑架构介绍
    • 1.4 MySQL存储引擎
      • 1.4.0 存储引擎概述
      • 1.4.1 查看命令
      • 1.4.2 存储引擎特点
        • 1.4.2.1 InnoDB
        • 1.4.2.2 MyISAM
        • 1.4.2.3 Memory
      • 1.4.2 MyISAM和InnoDB
      • 1.4.3 存储引擎选择
  • 第2章 索引优化分析
    • 2.1 概述
      • 2.1.1 性能下降原因
      • 2.1.2 常用的join查询
        • 2.1.2.1 SQL执行顺序
        • 2.1.2.2 join图
    • 2.2 索引简介
      • 2.2.1 索引是什么
      • 2.2.2 索引的优缺点
      • 2.2.3 索引结构
        • 2.2.3.1 概述
        • 2.2.3.2 二叉树
        • 2.2.3.3 B-Tree
        • 2.2.3.4 B+Tree
        • 2.2.3.5 Hash
      • 2.2.4 索引的分类
        • 2.2.4.0 聚集索引与二级索引
        • 2.2.4.1 单值索引
        • 2.2.4.2 唯一索引
        • 2.2.4.3 主键索引
        • 2.2.4.4 复合索引
      • 2.2.5 基本语法
      • 2.2.6 索引的创建时机
        • 2.2.6.1 适合创建索引的情况
        • 2.2.6.2 不适合创建索引的情况
    • 2.3 性能分析
      • 2.3.0 SQL 执行频率
      • 2.3.1 MySQL Query Optimizer
      • 2.3.2 MySQL常见瓶颈
      • 2.3.3 Explain
        • 2.3.3.1 是什么
        • 2.3.3.2 能干啥
        • 2.3.3.3 怎么玩
        • 2.3.3.4 字段解释
          • 2.3.3.4.1 id
          • 2.3.3.4.2 select_type
          • 2.3.3.4.3 table
          • 2.3.3.4.4 type
          • 2.3.3.4.5 possible_keys
          • 2.3.3.4.6 key
          • 2.3.3.4.7 key_len
          • 2.3.3.4.8 ref
          • 2.3.3.4.9 rows
          • 2.3.3.4.10 extra
    • 2.4 索引优化
      • 2.4.1 索引分析
        • 2.4.1.1 单表
        • 2.4.1.2 双表
        • 2.4.1.3 三表
        • 2.4.1.4 总结
      • 2.4.2 索引失效
        • 2.4.2.1 创建测试表
        • 2.4.2.2 索引失效的各种情况
      • 2.4.3 小总结
      • 2.4.4 优化口诀
      • 2.4.5 面试题讲解
      • 2.4.6 一般性建议
    • 2.5 其他索引情况
      • 2.5.1 SQL 提示
      • 2.5.2 覆盖索引
  • 第3章 查询截取分析
    • 3.1 查询优化
      • 3.1.1 永远小表驱动大表
      • 3.1.2 order by关键字优化
        • 3.1.2.1 order by优化
        • 3.1.2.2 MySQL的排序算法
          • 3.1.2.2.1 双路排序
          • 3.1.2.2.2 单路排序
          • 3.1.2.2.3 存在的问题
        • 3.1.2.3 优化策略
        • 3.1.2.4 总结
      • 3.1.3 group by关键字优化
    • 3.2 慢查询日志
      • 3.2.1 是什么
      • 3.2.2 使用
      • 3.2.3 日志分析工具mysqldumpslow
    • 3.3 批处理数据脚本
    • 3.4 Show Profile
      • 3.4.1 是什么
      • 3.4.2 使用
    • 3.5 全局查询日志
      • 3.5.1 配置文件来启用
      • 3.5.2 编码来启用
  • 第4章 MySQL的锁机制
    • 4.1 概述
    • 4.2 三锁
      • 4.2.1 表锁(偏读)
        • 4.2.1.1 特点
        • 4.2.1.2 案例分析
        • 4.2.1.3 案例结论
        • 4.2.1.4 表锁分析
      • 4.2.2 行锁(偏写)
        • 4.2.2.1 特点
        • 4.2.2.2 行锁支持事务
        • 4.2.2.3 案例分析
        • 4.2.2.4 案例结论
        • 4.2.2.5 行锁分析
        • 4.2.2.6 优化建议
      • 4.2.3 页锁
  • 第5章 主从复制
    • 5.1 复制的基本原理
    • 5.2 复制的基本原则
    • 5.3 复制的最大问题
    • 5.4 一主一从配置
    • 5.5 主从复制的优势

第1章 MySQL的架构介绍

1.1 MySQL简介

官网:https://www.mysql.com/

MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司

MySQL 是一种关联数据库管理系统(RDBMS), 将数据保存在不同的表中,而不是将所有数据放在一个大的仓库内,这样就增加了速度并提高了灵活性。

MySQL 特征

  • MySQL 是开源的,所以你不需要支付额外的费用。
  • MySQL 支持大型的数据库。可以处理拥有处理上千万条记录的大型数据库。
  • MySQL 可以允许于多个系统上,并且支持多种语言,这些编程语言包含 C、C++、Java、Perl、PHP 、 Eiffel 、 Ruby 和 Tcl 等。
  • MySQL对 PHP 有很好的支持, PHP 是暮年最流行的 WEB 开发语言之一。
  • MySQL 支持大型数据库, 支持 5000W 条数据记录的数据仓库, 32 为操作系统最大可支持 4GB , 64位操作系统最大的表文件为 8TB。
  • MySQL 是可以支持定制的,采用了 GPL协议, 你可以修改源码来开发自己的MySQL 系统。

1.2 在Linux上安装MySQL

MySQL高级的应用都是在Linux系统上进行,是对数据库的优化。

请参考博客:在Linux中安装MySQL

1.3 MySQL配置文件

Windows系统下mysql的配置文件为my.ini文件,Linux系统下mysql的配置文件为/etc/my.cnf文件。

MySQL主要配置文件如下:

  • 二进制日志log-bin:用于主从复制
  • 错误日志log-error:默认是关闭的,记录严重的警告和错误信息,每次启动和关闭的详细信息等
  • 查询日志log:默认关闭,记录查询的sql语句,如果开启会减低mysql的整体性能,因为记录日志也是需要消耗系统资源的
  • 数据文件
    • 两系统
      • mysql安装目录下的data目录下可以挑选很多库
      • 默认路径:/var/lib/mysql
    • frm文件:存放表结构
    • myd文件:存放表数据
    • myi文件:存放表索引

1.3 MySQL逻辑架构介绍

总体架构:

在这里插入图片描述
总体架构说明:

在这里插入图片描述
1、连接层:提供客户端和连接服务,包含本地Sock通信和大多数基于客户端/服务端工具实现的类似于TCP/IP的通信,主要完成一些类似于连接处理、授权认证、及相关的安全方案,在该层上引入了线程池的概念,为通过认证安全接入的客户端提供线程,同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。注:分配数据库连接线程池,控制数据库的连接和关闭等资源。

2、业务逻辑处理层:主要完成大多数的核心服务功能,如SQL接口,并完成缓存的查询,SQL的分析和优化及部分内置函数的操作。所有跨操作引擎的功能也在这一层实现,如过程,函数等。在该层服务器会解析查询并创建相应的内部解析树,并对其完成相应的优A化如确定查询表的顺序,是否利用索引等,最后生成相应的执行操作。如果是SELECT语句,服务器还会查询内部的缓存,如果缓存空间足够大,这样在解决大量读操作的环境中能够很好的提升系统的性能。注:提供SQL操作的接口,对SQL脚本按一定规则进行解析,并通过SQL优化器优化执行顺序,对于查询的语句还会进入缓存区,提升系统的性能。

3、数据存储引擎层:存储引擎真正的负责了MySQL中数据的存储和提取,服务器通过API与存储引[擎进行通信,不同的存储引|擎具有的功能不同,这样我们可以根据自己的实际需要进行选选取。注:存储引擎都是可插拔的,每个存储引l擎所提供的服务都有所差异,所以我们需要根据具体的业务需要,选择合适的存储引[擎,常用的只有两种MyISAM和lnnoDB。数据库中的索引是在存储引擎层实现的。

4、数据存储层:主要是将数据存储在运行于裸设备的文件系统上,并完成与存储引擎的交互。注:将数据存储到磁盘上,并协同存储引擎对数据进行读写操作。是将数据(如: redolog、undolog、数据、索引、二进制日志、错误日志、查询
日志、慢查询日志等)存储在文件系统之上,并完成与存储引擎的交互。

数据库逻辑结构共分为四层,分别是连接层(线程连接池)、业务逻辑处理层(SQL解析读取)、数据存储引擎层(存储擎)、数据存储层(数据存储)和其它的数据库相比,MySQL有点与众不同,它的架构可以在多种不同场景中应用并发挥良好作用,主要体现在存储引擎的架构上,插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离,这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

1.4 MySQL存储引擎

1.4.0 存储引擎概述

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。存储引擎是基于表的,而不是基于库的,所以存储引擎也可被称为表类型。我们可以在创建表的时候,来指定选择的存储引擎,如果没有指定将自动选择默认的存储引擎。

1.4.1 查看命令

  • 建表时指定存储引擎

语法如下:

CREATE TABLE 表名( 
	字段1 字段1数据类型 [ COMMENT 字段1注释 ],
	...
	字段n 字段n数据类型 [ COMMENT 字段n注释 ]
) ENGINE = 引擎名 [ COMMENT 表注释 ];

引擎名可以是 InnoDBMyISAMMemory。例如:

create table my_myisam(
	id int,
	name varchar(10)
) engine = MyISAM;
  • 查询当前数据库支持的存储引擎

可以通过如下命令查看支持的存储引擎:

show engines; #查看支持的存储引擎

在这里插入图片描述

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

通过如下命令查看当前默认的存储引擎:

show variables like '%storage_engines%'; #查看当前默认的存储引擎

在这里插入图片描述

  • 查看指定表使用的存储引擎

语法:

# 语法
show create table 表名;
# 示例,查看 account 表的建表语句
show create table account;

在这里插入图片描述

1.4.2 存储引擎特点

1.4.2.1 InnoDB

(1)概述

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎,在 MySQL 5.5 之后,InnoDB是默认的 MySQL 存储引擎。

(2)特点

  • DML 操作遵循 ACID 模型,支持事务。
  • 行级锁,提高并发访问性能。
  • 支持外键 FOREIGN KEY 约束,保证数据的完整性和正确性。

(3)文件

xxx.idb:其中 xxx 表示的是表名,如果使用了 InnoDB 存储引擎,那么数据库中的每张表都会对应这样一个表空间文件,存储该表的表结构、数据和索引。在早期版本中是以 .frm 后缀结尾的。

在这里插入图片描述

这些文件是二进制文件,是不能直接使用记事本程序打开的,但可以使用 MySQL 提供的一个命令 ibd2sdi,通过该命令就可以从 .ibd 文件中提取 sdi 信息,而 sdi 数据字典就包含该表的表结构。命令格式如下:

# 语法
ibd2sdi 表名.ibd
# 示例,查看 user.ibd 表空间文件的数据字典
ibd2sdi user.ibd

在这里插入图片描述

注:在 MySQL 中,变量 innodb_file_per_table 表示是否开启对于使用了 InnoDB 存储引擎的表,每一张表都对应一个 .ibd 文件。其中 ON 表示默认开启。

在这里插入图片描述

(4)逻辑存储结构

注:InnoDB 存储引擎的逻辑存储结构如下图(图来源于黑马视频):

在这里插入图片描述

  • 表空间 : InnoDB存储引擎逻辑结构的最高层,ibd文件其实就是表空间文件,在表空间中可以包含多个 Segment 段。
  • 段 : 表空间是由各个段组成的, 常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管理,都是引擎自身完成,不需要人为对其控制,一个段中包含多个区。
  • 区 : 区是表空间的单元结构,每个区的大小为1M。 默认情况下, InnoDB存储引擎页大小为 16K, 即一个区中一共有 64 个连续的页。
  • 页 : 页是组成区的最小单元,页也是InnoDB 存储引擎磁盘管理的最小单元,每个页的大小默认为 16KB。为了保证页的连续性,InnoDB 存储引擎每次从磁盘申请 4-5 个区。
  • 行 : InnoDB 存储引擎是面向行的,也就是说数据是按行进行存放的,在每一行中除了定义表时所指定的字段以外,还包含两个隐藏字段

1.4.2.2 MyISAM

(1)概述

MyISAM 是 MySQL 早起版本的默认存储引擎。

(2)特点

  • 不支持事务。
  • 不支持外键。
  • 支持表锁,不支持行锁。
  • 访问速度快。

(3)文件

MySQL 中使用了 MyISAM 存储引擎的表,会生成如下几个文件:

  • xxx.sdi:存储了表的结构信息。
  • xxx.MYD:存储了表数据信息。
  • xxx.MYI:存储了表索引信息。

在这里插入图片描述

1.4.2.3 Memory

(1)概述

Memory 引擎的表数据时存储在内存中的,由于受到硬件问题、或断电问题的影响,只能将这些表作为临时表或缓存使用。

(2)特点

  • 存储在内存中。
  • 默认使用 Hash 索引。

(3)文件

使用了这种存储引擎的表会生成如下文件:

  • xxx.sdi:该文件存储了表结构信息。

在这里插入图片描述

1.4.2 MyISAM和InnoDB

在这里插入图片描述

1.4.3 存储引擎选择

在选择存储引擎时,应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统,还可以根据实际情况选择多种存储引擎进行组合。

  • InnoDB: 是Mysql的默认存储引擎,支持事务、外键。如果应用对事务的完整性有比较高的要求,在并发条件下要求数据的一致性,数据操作除了插入和查询之外,还包含很多的更新、删除操
    作,那么InnoDB存储引擎是比较合适的选择。
  • MyISAM : 如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不是很高,那么选择这个存储引擎是非常合适的。
  • MEMORY:将所有数据保存在内存中,访问速度快,通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制,太大的表无法缓存在内存中,而且无法保障数据的安全性。


第2章 索引优化分析

2.1 概述

2.1.1 性能下降原因

性能下降SQL慢,执行时间长,等待时间长的原因:

  • 查询语句写的烂
  • 索引失效:单值和复合
  • 关联查询太多join(设计缺陷或不得已的需求)
  • 服务器调优及各个参数设置不合理(缓冲、线程数等)

2.1.2 常用的join查询

2.1.2.1 SQL执行顺序

①手写

通常我们按照需求写的SQL查询语句是这样的:

SELECT DISTINCT查询列表
FROM 左表 INNER|LEFT|RIGHT
JOIN 右表 ON 连接条件
WHERE 分组前的筛选条件
GROUP BY 分组列表
HAVING 分组后的筛选条件
ORDER BY 排序列表
LIMIT 分页参数

我们手写的SQL中SELECT在最前面的位置。

②机读

随着 Mysql 版本的更新换代,其优化器也在不断的升级,优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序。下面是经常出现的查询顺序:

FROM 左表 INNER|LEFT|RIGHT
JOIN 右表 ON 连接条件
WHERE 分组前的筛选条件
GROUP BY 分组列表
HAVING 分组后的筛选条件
SELECT DISTINCT查询列表
ORDER BY 排序列表
LIMIT 分页参数

而在机读中FROM是最先执行的。

③总结

在这里插入图片描述

2.1.2.2 join图

  • 左外连接

A表独有部分+AB两表的公有部分。

在这里插入图片描述

-- SQL语句如下:
SELECT 查询列表 FROM A LEFT JOIN B ON A.key=B.key# 其中A.key=B.key指的是连接条件
-- 注:
-- 左外连接中左边的是主表,右边的是从表
  • 右外连接

在这里插入图片描述

-- SQL语句如下:
SELECT 查询列表 FROM A RIGHT JOIN B ON A.key=B.key# 其中A.key=B.key指的是连接条件
-- 注:
-- 右外连接中右边的是主表,左边的是从表
  • 内连接

获取的是两张表的公有部分。

在这里插入图片描述

-- SQL语句如下:
SELECT 查询列表 FROM A INNER JOIN B ON A.key=B.key# 其中A.key=B.key指的是连接条件
-- 注:
-- 内连接求的是多张表的交集部分
  • 左外连接去除交集

在这里插入图片描述

SELECT 查询列表 FROM A LEFT JOIN B ON A.key=B.key WHERE B.key IS NULL;# 将从表B的连接条件作为NULL值判断
  • 右外连接去除交集

在这里插入图片描述

SELECT 查询列表 FROM A RIGHT JOIN B ON A.key=B.key WHERE A.key IS NULL;# 将从表A的连接条件作为NULL值判断
  • 全外连接

注意:MySQL不支持full join。

在这里插入图片描述

SELECT 查询列表 FROM A FULL JOIN B ON A.key=B.key;# 全外连接就是求并集

# 可以间接完成效果

SELECT 查询列表 FROM A LEFT JOIN B ON A.key=B.key
UNION
SELECT 查询列表 FROM A RIGHT JOIN B ON A.key=B.key
  • 全外连接去除交集

在这里插入图片描述

SELECT 查询列表 FROM A FULL JOIN B ON A.key=B.key WHERE A.key IS NULL OR B.key IS NULL;

实例如下:

-- 左外连接
select * from beauty left join boys on beauty.boyfriend_id=boys.id;
-- 右外连接
select * from beauty right join boys on beauty.boyfriend_id=boys.id;
-- 内连接
select * from beauty inner join boys on beauty.boyfriend_id=boys.id;
-- 左外连接去除交集
select * from beauty left join boys on beauty.boyfriend_id=boys.id where boys.id IS NULL;
-- 右外连接去除交集
select * from beauty right join boys on beauty.boyfriend_id=boys.id where beauty.boyfriend_id IS NULL;
-- 全外连接(MySQL不能运行)
select * from beauty full join boys on beauty.boyfriend_id=boys.id;
-- 全外连接去除交集(MySQL不能运行)
select * from beauty full join boys on beauty.boyfriend_id=boys.id where boys.id IS NULL OR beauty.boyfriend_id IS NULL;

2.2 索引简介

2.2.1 索引是什么

MySQL 官方对索引的定义为:索引(Index)是帮助 MySQL 高效获取数据的数据结构(有序)。可以得到索引的本质:索引是数据结构。可以简单理解为排好序的快速查找数据结构

在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。下图就是一种可能的索引方式示例:

在这里插入图片描述
左边是数据表,一共有两列七条记录,最左边的是数据记录的物理地址 。 为了加快 Col2 的查找,可以维护一个右边所示的二叉查找树,每个节点分别包含索引键值和一个指向对应数据记录物理地址的指 针,这样就可以运用二叉查找在一定的复杂度内获取到相应数据,从而快速的检索出符合条件的记录。

我们平常所说的索引,如果没有特别指明,都是指B树(多路搜索树,并不一定是二叉的)结构组织的索引。其中聚集索引,次要索引,覆盖索引,复合索引,前缀索引,唯一索引默认都是使用B+树索引,统称索引。当然,除了B+树这种类型的索引之外,还有哈希索引(hashindex)等。

一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上

如下演示使用索引与不使用索引的区别(假如表结构和数据如下):

在这里插入图片描述

假如我们要执行的 SQL 语句是:select * from user where age = 45;

  • 如果是无索引的情况,则会从第一行一直扫描到最后一行,即全表扫描,性能很低。

在这里插入图片描述

  • 如果是有索引的情况,假设索引结构是二叉树(并不是真实的索引结构),对该表建立索引,即对 age 字段建立一个二叉树的索引结构。在进行查询时,只需要扫描三次就可以查到,极大的提高了查询效率。

在这里插入图片描述

2.2.2 索引的优缺点

优势:

  • 类似大学图书馆建书目索引,提高数据检索的效率,降低数据库的IO成本
  • 通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗

劣势:

  • 实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用空间的
  • 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息。
  • 索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。

2.2.3 索引结构

2.2.3.1 概述

MySQL 的索引在存储引擎层实现的,不同的存储引擎有不同的索引结构,主要包含以下几种:

索引结构描述
B+Tree索引最常见的索引类型,大部分引擎都支持 B+ 树索引
Hash索引底层数据结构是用哈希表实现的, 只有精确匹配索引列的查询才有效, 不支持范围查询
R-tree(空间索引)空间索引是MyISAM引擎的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少
Full-text(全文索引)是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES

上述是MySQL中所支持的所有的索引结构,接下来,我们再来看看不同的存储引擎对于索引结构的支持情况:

索引InnoDBMyISAMMemory
B+tree索引支持支持支持
Hash 索引不支持不支持支持
R-tree 索引不支持支持不支持
Full-text5.6版本之后支持支持不支持

注:平常所说的索引,如果没有特别指明,都是指 B+ 树结构组织的索引。

2.2.3.2 二叉树

注:通过下面这几节可以了解为什么索引结构采用 B+ 树的数据结构。

假如 MySQL 的索引结构采用二叉树,比较理想的情况如图:

在这里插入图片描述

如果是按主键顺序插入,在比较极端的情况下,则会变成一个单链表,那么查找又会变成全表扫描。结构如下:

在这里插入图片描述

因此使用二叉树作为索引结构,缺点如下:

  • 顺序插入时,会退化成一个链表,查询性能大大降低。
  • 大数据量情况下,即使是比较理想的二叉树结构,也会有很多层,造成层次比较深,那么检索速度会变慢。

为了解决顺序插入而退化成链表的问题,可以考虑使用红黑树,红黑树是一颗自平衡二叉树,即使顺序插入数据,最终也会形成一颗平衡的二叉树,而不会退化成链表。

在这里插入图片描述

但即使如下,由于红黑树本身也是二叉树,所以大数据量层次比较深的问题仍然存在。使用红黑树的缺点如下:

  • 大数据量情况下,即使是理想的红黑树,也会有很多层,造成层次比较深,那么检索速度会变慢。

所以,在 MySQL 的索引结构中,没有选择二叉树或红黑树,而是选择 B+ 树来作为数据结构。

2.2.3.3 B-Tree

B-Tree,B 树是一种多叉路衡查找树,相对于二叉树,B 树每个节点可以有多个分支,即多叉。

而多叉就能解决深层次的问题,将原来的高度问题变成了宽度问题(高瘦——>矮胖)。

以一颗最大度数(max-degree,度数即指一个节点的子节点个数)为5(5阶)的b-tree为例,那这个B树每个节点最多存储4个key,5个指针:

在这里插入图片描述

注:可通过 B-Trees 网站来动态演示查看 B-Tree 的执行过程。

例如插入一组数据:[100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250]。然后观察一些数据插入过程中,节点的变化情况。如图所示:

在这里插入图片描述

B-Tree的特点:

  • 5阶的B树,每一个节点最多存储4个key,对应5个指针。
  • 一旦节点存储的key数量到达5,就会裂变,中间元素向上分裂。
  • 在B树中,非叶子节点和叶子节点都会存放数据。

为什么不使用 B-Tree 作为实现索引的数据结构呢?因为对于B-tree,无论是叶子节点还是非叶子节点,都会保存数据,这样导致一页中存储的键值减少,指针跟着减少,要同样保存大量数据,只能增加树的高度,导致性能降低。

2.2.3.4 B+Tree

B+Tree是B-Tree的变种,我们以一颗最大度数(max-degree)为4(4阶)的b+tree为例,来看一下其结构示意图:

在这里插入图片描述

如图,可以看到 B+Tree 只在叶子结点存储数据:

  • 绿色框框起来的部分,是索引部分,仅仅起到索引数据的作用,不存储数据。
  • 红色框框起来的部分,是数据存储部分,在其叶子节点中要存储具体的数据。

例如插入一组数据:[100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250]。然后观察一些数据插入过程中,节点的变化情况。如图所示:

在这里插入图片描述

B+Tree 与 B-Tree 相比,主要有以下三点区别:

  • 所有的数据都会出现在叶子节点。
  • 叶子节点形成一个单向链表。
  • 非叶子节点仅仅起到索引数据作用,具体的数据都是在叶子节点存放的。

上述我们所看到的结构是标准的B+Tree的数据结构,而 MySQL 索引数据结构对经典的 B+Tree 进行了优化。在原 B+Tree 的基础上,增加一个指向相邻叶子节点的链表指针,就形成了带有顺序指针的 B+Tree(实际上是循环双链表),提高区间访问的性能,利于排序。

在这里插入图片描述

2.2.3.5 Hash

MySQL 中除了支持 B+Tree 索引,还支持一种索引类型——Hash索引。

(1)结构

注:如果想要了解 Hash 索引更多的信息,建议先学习了解 Hash 数据结构。

哈希索引就是采用一定的 hash 算法,将键值换算成新的 hash 值(通常是一个字符串转换成一个数字,再将数字映射到对应表中位置),映射到对应的槽位上,然后存储在hash表中。

在这里插入图片描述

如果两个(或多个)键值,映射到一个相同的槽位上,他们就产生了 hash 冲突(也称为 hash 碰撞),可以通过链表(但如果链表中冲突的结点多了话也会变成线性遍历查找了)来解决,如在 Java 中的 HashMap 中后期版本中采用了红黑树来解决冲突。

在这里插入图片描述

(2)特点

Hash 索引的特点如下:

  • Hash索引只能用于对等比较(=in),不支持范围查询(between>< ,…)。
  • 无法利用索引完成排序操作,因为元素是乱序存放的。
  • 查询效率高,通常(不存在 hash 冲突的情况)只需要一次检索就可以了,效率通常要高于 B+Tree 索引。

(3)存储引擎支持

在 MySQL 中,支持 hash 索引的是 Memory 存储引擎。 而 InnoDB 中具有自适应 hash 功能,hash 索引是 InnoDB 存储引擎根据 B+Tree 索引在指定条件下自动构建的。

为什么 InnoDB 存储引擎选择使用 B+tree 索引结构?

  • 相对于二叉树,层级更少,搜索效率高。
  • 对于 B-Tree,无论是叶子节点还是非叶子节点,都会保存数据,这样导致一页中存储的键值减少,指针跟着减少,要同样保存大量数据,只能增加树的高度,导致性能降低。
  • 相对 Hash 索引,B+Tree支持范围匹配及排序操作。

2.2.4 索引的分类

索引可以分为单值索引、唯一索引、主键索引、复合索引和全文索引。

分类含义特点关键字
主键索引针对表中的主键创建的索引默认自动创建,只能有一个PRIMARY
唯一索引避免同一个表中某数据列的值重复可以有多个UNIQUE
单值索引只对某列建立索引可以有多个
复合索引对多个列一起建立索引可以有多个
全文索引全文索引查找的是文本中的关键词,而不是比较索引中的值可以有多个FULLTEXT

2.2.4.0 聚集索引与二级索引

而在在 InnoDB 存储引擎中,根据索引的存储形式(物理存储),又可以分为以下两种:

分类含义特点
聚集索引(Clustered Index)将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据必须有,而且只有一个
二级索引(Secondary Index)将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键可以存在多个

聚集索引选取规则:

  • 如果存在主键,主键索引就是聚集索引。
  • 如果不存在主键,将使用第一个唯一(UNIQUE)索引作为聚集索引。
  • 如果表没有主键,或没有合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

聚集索引(如以主键 id 建立聚集索引)和二级索引(如以 name 字段建立二级索引)的具体结构如下:

  • 聚集索引的叶子节点下挂的是这一行的数据。
  • 二级索引的叶子节点下挂的是该字段值对应的主键值。

在这里插入图片描述

当我们执行如下的SQL语句(select * from user where name='Arm';)时,具体的查找过程如下:

  • 1、由于是根据 name 字段进行查询,所以先根据 name='Arm'name 字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10
  • 2、由于查询返回的数据是 *,所以此时,还需要根据主键值 10,到聚集索引中查找 10 对应的记录,最终找到 10 对应的行 row
  • 3、最终拿到这一行的数据,直接返回即可。

在这里插入图片描述

回表查询: 这种先到二级索引中查找数据,找到主键值,然后再到聚集索引中根据主键值,获取数据的方式,就称之为回表查询。

思考题:以下两条SQL语句,那个执行效率高? 为什么?

  • A. select * from user where id = 10;
  • B. select * from user where name = 'Arm';
    备注: id 为主键,name 字段创建的有索引;
    解答:A 语句的执行性能要高于B 语句。因为A语句直接走聚集索引,直接返回数据。而B语句需要先查询name字段的二级索引,然后再查询聚集索引,也就是需要进行回表查询。

2.2.4.1 单值索引

即一个索引只包含单个列,一个表可以有多个单列索引。

创建单值索引的语法如下:

-- 创建情况一:在创建表时创建
CREATE TABLE 表名(
	字段名 字段类型 [约束],
	字段名 字段类型 [约束],
	...
	字段名 字段类型 [约束],
	KEY(字段名)# 这一句就是创建单列索引的语句,直接在KEY()中写字段名即可
);

-- 创建情况二:单独创建单值索引
CREATE INDEX 索引名 ON 表名(字段名);
# 注释:
# 	1.索引名通常是idx_表名_字段名这样的格式,比如idx_user_name
#   2.单独创建索引的示例:CREATE INDEX idx_user_name user(nmae);

2.2.4.2 唯一索引

即索引列的值必须唯一,但允许有空值。

创建唯一索引的语法如下:

-- 创建情况一:在创建表时创建
CREATE TABLE 表名(
	字段名 字段类型 [约束],
	字段名 字段类型 [约束],
	...
	字段名 字段类型 [约束],
	UNIQUE(字段名)# 这一句就是创建唯一索引的语句,直接在UNIQUE()中写字段名即可
);

-- 创建情况二:单独创建唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名(字段名);
# 注释:
# 	1.索引名通常是idx_表名_字段名这样的格式,比如idx_user_name
#   2.单独创建唯一索引的示例:CREATE UNIQUE INDEX idx_user_id user(id);

2.2.4.3 主键索引

设定某字段为主键后,数据库会自动建立索引,innodb存储引擎的主键为聚簇索引。

创建索引的基本语法如下:

-- 创建情况一:在创建表时创建
CREATE TABLE 表名(
	字段名 字段类型 [约束],
	字段名 字段类型 [约束],
	...
	字段名 字段类型 [约束],
	PRIMARY KEY(字段名)# 这一句就是创建主键索引的语句,直接在PRIMARY KEY()中写字段名即可
);

-- 创建情况二:单独创建主键索引
ALTER TABLE 表名 ADD PRIMARY KEY 表名(字段名);
# 注释:
#  1.创建示例:ALTER TABLE user ADD PRIMARY KEY user(id);

-- 删除主键索引
ALTER TABLE 表名 DROP PRIMARY KEY;
# 注释:
#  1.删除示例:ALTER TABLE user DROP PRIMARY KEY;
#  2.如果要修改主键索引,那么必须先删除掉原索引,再新建索引

2.2.4.4 复合索引

即一个索引包含多个列。

创建的基本语法如下:

-- 创建情况一:在创建表时创建
CREATE TABLE 表名(
	字段名 字段类型 [约束],
	字段名 字段类型 [约束],
	...
	字段名 字段类型 [约束],
	KEY(字段名,字段名,..)# 这一句就是创建复合索引的语句,直接在KEY()中写多个字段名即可
);

-- 创建情况二:单独创建复合索引
CREATE INDEX 索引名 ON 表名(字段名,字段名,...);

2.2.5 基本语法

①创建索引

-- 语法
CREATE [UNIQUE] INDEX 索引名 ON 表名(字段名[,字段名,字段名,..]);

②删除索引

-- 语法
DROP INDEX 索引名 ON 表名;

③查看索引

-- 语法
SHOW INDEX FROM 表名;

④修改索引

-- 语法
-- 通过修改语句添加主键索引
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);# 该语句添加一个主键,这意味着索引值必须是唯一的,且不能为 NULL。
-- 通过修改语句添加普通索引
ALTER