MySQL进阶笔记

最新推荐文章于 2024-07-08 08:09:11 发布

多喝氧烷_

最新推荐文章于 2024-07-08 08:09:11 发布

阅读量220

点赞数 1

分类专栏： MySQL 文章标签： mysql 数据库服务器后端

本文链接：https://blog.csdn.net/m0_61472414/article/details/127522671

版权

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

壹、存储引擎

一、MySQL体系结构

1.连接层

连接池（线程池）在该层实现。为使用MySQL的用户提供连接。

在该层可以实现基于SSL的安全连接。

2.服务层

主要完成大部分的SQL核心服务功能。SQL的分析优化，内置函数执行，缓存（select语句等），等服务均在此完成。

3.引擎层

数据库中的索引即在该层实现

4.存储层

是将数据(如: redolog、undolog、数据、索引、二进制日志、错误日志、查询日志、慢查询日志等)存储在文件系统之上，并完成与存储引擎的交互。

二、存储引擎介绍

在一个数据库中的不同表可以使用不同的存储引擎，因此存储引擎也可被称为表类型。

1.建表时指定存储引擎

create table tablename(
	字段 字段类型
)ENGINE = Innodb;

2.查询当前数据库支持的存储引擎

show engines;

三、存储引擎特点

1.InnoDB

MySQL5.5后的默认存储引擎。

特点

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

文件

tablename.ibd这是每张表所对应的表空间文件，存储该表的表结构（frm-旧版、sdi-8.0版）、数据和索引

show variables like 'innodb_file_per_table';

上述参数决定是否每个表都是单独的ibd文件

可以使用如下命令查看sdi表结构信息，以JSON格式给出。

ibd2sdi tablename.ibd

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PfrqXBVa-1666705802723)(C:\Users\X\AppData\Roaming\Typora\typora-user-images\image-20221022102028348.png)]$

注意！红字所标明的存储空间大小是默认的
常见的段有数据段、索引段、回滚段等，该存储引擎对于段的管理都是引擎自身完成，不需要人为对其控制
页也是InnoDB存储引擎磁盘管理的最小单元
数据是按行存放的，且每一行除了定义表所指定的字段以外，还包含两个隐藏字段（事务、指针相关）

2.MyISAM

MySQL早期的默认存储引擎

特点

不支持事务，不支持外键
不支持行锁，支持表锁
访问速度快

文件

tablename.sdi：存储表结构信息

tablename.MYD: 存储数据

tablename.MYI: 存储索引

3.Memory

该表的数据存储在内存中

特点

内存存放
hash索引（默认）

文件

tablename.sdi：存储表结构信息

4.区别及特点

特点	InnoDB	MyISAM	Memory
存储限制	64TB	有	有
事务安全	支持	-	-
锁机制	行级锁	表锁	表锁
B+Tree索引	支持	支持	支持
Hash索引	-	-	支持
全文索引	支持	支持	-
空间使用	高	低	不使用
内存使用	高	低	中等
批量插入速度	低	高	高
支持外键	支持	-	-

面试题:

InnoDB引擎与MyISAM引擎的区别 ?

①. InnoDB引擎, 支持事务, 而MyISAM不支持。

②. InnoDB引擎, 支持行锁和表锁, 而MyISAM仅支持表锁, 不支持行锁。

③. InnoDB引擎, 支持外键, 而MyISAM是不支持的。

主要是上述三点区别，当然也可以从索引结构、存储限制等方面，更加深入的回答，具体参考如下官方文档：

https://dev.mysql.com/doc/refman/8.0/en/innodb-introduction.html

https://dev.mysql.com/doc/refman/8.0/en/myisam-storage-engine.html

四、存储引擎选择

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

InnoDB: 是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。
MyISAM ：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。

可用于存储日志、用户评论等，（丢一两条关系不大

被noSQL数据库MongoDB代替

MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。

被noSQL数据库Redis代替

贰、索引

一、索引概述

索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

优势	劣势
提高数据检索的效率，降低数据库的IO成本	索引列会占用空间
通过索引列对数据进行排序，降低CPU的消耗	降低更新表的速度，对表进行数据增改时效率降低

二、索引结构

1.概述

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构。

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