mysql引擎、索引

最新推荐文章于 2023-12-09 20:31:04 发布

工大枸杞微铺

最新推荐文章于 2023-12-09 20:31:04 发布

阅读量128

点赞数

分类专栏： Mysql MySQL高级文章标签： mysql 数据库 sqlserver

本文链接：https://blog.csdn.net/weixin_45466462/article/details/119575906

版权

Mysql 同时被 2 个专栏收录

11 篇文章 0 订阅

订阅专栏

MySQL高级

3 篇文章 0 订阅

订阅专栏

本文中所有命令操作皆可以通过图形化工具navicat实现。设计表。

Mysql体系结构

在这里插入图片描述

体系结构详解
- 客户端连接
  - 支持接口：支持的客户端连接，例如C、Java、PHP等语言来连接MySQL数据库
- 第一层：网络连接层
  - 连接池：管理、缓冲用户的连接，线程处理等需要缓存的需求。
  - 例如：当客户端发送一个请求连接，会从连接池中获取一个连接进行使用。
- 第二层：核心服务层
  - 管理服务和工具：系统的管理和控制工具，例如备份恢复、复制、集群等。
  - SQL接口：接受SQL命令，并且返回查询结果。
  - 查询解析器：验证和解析SQL命令，例如过滤条件、语法结构等。
  - 查询优化器：在执行查询之前，使用默认的一套优化机制进行优化sql语句
  - 缓存：如果缓存当中有想查询的数据，则直接将缓存中的数据返回。没有的话再重新查询！
- 第三层：存储引擎层
  - 插件式存储引擎：管理和操作数据的一种机制，包括(存储数据、如何更新、查询数据等)
- 第四层：系统文件层
  - 文件系统：配置文件、数据文件、日志文件、错误文件、二进制文件等等的保存

存储引擎

1.引擎的概念

生活中，引擎就是整个机器运行的核心，不同的引擎具备不同的功能。
MySQL存储引擎的概念
- MySQL数据库使用不同的机制存取表文件 , 机制的差别在于不同的存储方式、索引技巧、锁定水平以及广泛的不同的功能和能力，在MySQL中 , 将这些不同的技术及配套的功能称为存储引擎
- 在关系型数据库中数据的存储是以表的形式存进行储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）。
- Oracle , SqlServer等数据库只有一种存储引擎 , 而MySQL针对不同的需求, 配置MySQL的不同的存储引擎 , 就会让数据库采取了不同的处理数据的方式和扩展功能。
MySQL支持的存储引擎
- MySQL支持的引擎包括：InnoDB、MyISAM、MEMORY、Archive、Federate、CSV、BLACKHOLE等
- 其中较为常用的有三种：InnoDB、MyISAM、MEMORY

2.存储引擎的操作语法：

查看数据库支持的操作引擎

show engines;
-- 表含义:
  - support : 指服务器是否支持该存储引擎
  - transactions : 指存储引擎是否支持事务
  - XA : 指存储引擎是否支持分布式事务处理
  - Savepoints : 指存储引擎是否支持保存点

在这里插入图片描述

查询某个数据库中所有数据表的引擎

show table status from 数据库名称;
show table status from db1

在这里插入图片描述

查询某个数据库中某个数据表的引擎

show table status from 数据库名 where name='表名';

在这里插入图片描述

创建数据表，指定存储引擎

create table 表名(
	列名,数据类型,
    ...
)engine = 引擎名称;

在这里插入图片描述

修改表的存储引擎

alter table 表名 engine = 引擎名称;

在这里插入图片描述

3.引擎的选择

MyISAM ：MyISAM不支持事务、不支持外键、支持全文检索和表级锁定，读写相互阻塞，读取速度快，节约资源，所以如果应用是以查询操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
InnoDB : 是MySQL的默认存储引擎，由于InnoDB支持事务、支持外键、行级锁定，支持所有辅助索引(5.5.5后不支持全文检索)，高缓存，所以用于对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，读写频繁的操作，那么InnoDB存储引擎是比较合适的选择，比如BBS、计费系统、充值转账等
MEMORY：将所有数据保存在RAM中，在需要快速定位记录和其他类似数据环境下，可以提供更快的访问。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表，用以快速得到访问结果。
总结：针对不同的需求场景，来选择最适合的存储引擎即可！如果不确定、则使用数据库默认的存储引擎！

索引

是数据结构，用于帮助mysql高效获取数据。
简单理解：排好序的快速查找数据结构
用于提升查询效率。两方面，排序和查找。
属于sql优化。
索引也是文件，需要进行维护和存储。
在这里插入图片描述

在这里插入图片描述

关于索引。只有主键索引的叶子节点存储的是全量表的行数据。对于其他索引，如唯一索引来讲其btree中存储的除了唯一索引列外，其叶子节点存储的是主键的值，通过主键的值便可以定位到全量数据

1. 索引解释

帮助MySQL高效获取数据的一种数据结构B+Tree。查询快
在表数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

一张数据表，用于保存数据。一个索引配置文件，用于保存索引，每个索引都去指向了某一个数据。

2.索引分类

功能分类
1. 普通索引：最基本的索引，它没有任何限制。
2. 唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值组合必须唯一。
3. 主键索引：一种特殊的唯一索引，不允许有空值。一般在建表时同时创建主键索引。主键列自带主键索引
4. 组合索引：顾名思义，就是将单列索引进行组合。
  ====================
  注意：组合索引有最左前缀匹配原则，即索引的命中规则是从最左边的列开始的，且不跳过中间列。如果where条件中没有最左边列的条件则不会走索引。如果跳过某中间列，则组合索引后边得字段不会生效
  ====================
5. 外键索引：只有InnoDB引擎支持外键索引，用来保证数据的一致性、完整性和实现级联操作。外键列自带外键索引
6. 全文索引：快速匹配全部文档的方式。InnoDB引擎5.6版本后才支持全文索引。MEMORY引擎不支持。
结构分类
1. B+Tree索引：MySQL使用最频繁的一个索引数据结构，是InnoDB和MyISAM存储引擎默认的索引类型。聚簇索引：将索引与数据存储放在一块
2. Hash索引 : MySQL中Memory存储引擎默认支持的索引类型。非聚簇索引：将索引和数据存储分开通过地址进行关联。

3.索引的操作

索引的创建

-- 标准语法
CREATE [UNIQUE唯一索引|FULLTEXT全文索引 都不写为普通类型] INDEX 索引名称
[USING 索引类型 ]  -- 不指定默认是B+TREE
ON 表名(列名...);-- 多个列名为组合索引

eg：

-- 为student表中年龄列创建一个唯一索引
-- ids开头index简写_列名
CREATE UNIQUE INDEX idx_age ON student(age);

查看索引

-- 标准语法
SHOW INDEX FROM 表名;

-- 查看student表中的索引
SHOW INDEX FROM student;

添加索引：alter语句
修改表结构方式添加索引

-- 普通索引
ALTER TABLE 表名 ADD INDEX 索引名称(列名);

-- 组合索引
ALTER TABLE 表名 ADD INDEX 索引名称(列名1,列名2,...);

-- 主键索引
ALTER TABLE 表名 ADD PRIMARY KEY(主键列名); 

-- 外键索引(添加外键约束，就是外键索引)
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);

-- 唯一索引
ALTER TABLE 表名 ADD UNIQUE 索引名称(列名);

-- 全文索引(mysql只支持文本类型)
ALTER TABLE 表名 ADD FULLTEXT 索引名称(列名);


-- 为student表中name列添加全文索引
ALTER TABLE student ADD FULLTEXT idx_fulltext_name(name);

-- 查看student表中的索引
SHOW INDEX FROM student;

删除索引

-- 标准语法
DROP INDEX 索引名称 ON 表名;

-- 删除student表中的idx_score索引
DROP INDEX idx_score ON student;

-- 查看student表中的索引
SHOW INDEX FROM student;

4.索引实现的原理

索引是在MySQL的存储引擎中实现的，所以每种存储引擎的索引不一定完全相同，也不是所有的引擎支持所有的索引类型。主要使用的是InnoDB引擎的实现的B+Tree索引。
B+Tree是一种树型数据结构，是B-Tree的变种。通常使用在数据库和操作系统中的文件系统，特点是能够保持数据稳定有序。

4.1磁盘存储

系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的
位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。
InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB。一页有多个磁盘块。
InnoDB引擎将若干个地址连接磁盘块，以此来达到页的大小16KB，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

4.2BTree

黄色的数据存储再磁盘的磁盘块中
在这里插入图片描述

根据图中结构显示，每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

模拟一个数据的查找过程：
查找顺序：

模拟查找15的过程 : 

1.根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
	比较关键字15在区间（<17），找到磁盘块1的指针P1。
2.P1指针找到磁盘块2，读入内存。【磁盘I/O操作第2次】
	比较关键字15在区间（>12），找到磁盘块2的指针P3。
3.P3指针找到磁盘块7，读入内存。【磁盘I/O操作第3次】
	在磁盘块7中找到关键字15。
	
-- 分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。
-- 由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。
-- 而3次磁盘I/O操作是影响整个BTree查找效率的决定因素。BTree使用较少的节点个数，
-- 使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。

4.3B+Tree

B+Tree是在BTree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。
从上一节中的BTree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，
如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

将上一节中的BTree优化，由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：
在这里插入图片描述
通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。
因此可以对B+Tree进行两种查找运算：

【有范围】对于主键的范围查找和分页查找
【有顺序】从根节点开始，进行随机查找

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2~4层。
MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

b+Tree和bTree的区别

非叶子即分支节点只键的值，不保存数据。即不和磁盘交互。通过指针地址来到下一层。
叶子节点保存键的值、数据。叶子节点进行了连接，方便进行范围查询。

非叶子节点只存储键值信息。
所有叶子节点之间都有一个连接指针。
数据记录都存放在叶子节点中。

5.创建索引时的原则

对查询频次较高，且数据量比较大的表建立索引。
使用唯一索引，区分度越高，使用索引的效率越高。
索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。
使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。
索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。
联合索引的特点

在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，
对列name列、address和列phone列建一个联合索引
注意：
1.Mysql的优化器会帮助我们调整where条件中的顺序，以匹配我们建立的索引。
2.联合索引中最左边的列不包含在条件查询中，所以根据上面的原则，下面的SQL语句就不会命中索引。

在这里插入图片描述