MySQL数据库进阶级宝藏干货！！！（存储引擎&索引）

关于个人笔记梳理

目录

存储引擎

索引

存储引擎

索引

MySQL体系结构

连接层、服务层、引擎层、存储层

连接层：最上层是客户端和链接服务，主要是完成一些类似于连接处理、授权认证、及相关的安全方案

服务层：第二层架构主要完成大多数的核心服务功能，eg：SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，eng：过程、函数等

引擎层：存储引擎真正的负责MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能

存储层：主要是将数据存储在文件系统之上，并完成与存储引擎的交互

存储引擎简介：是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型

1、在创建表时，指定存储引擎

create table 表名（

字段一 字段一类型[comment字段1注释]，

......

字段n 字段n类型[comment 字段n注释]

）engine=innodb[comment表注释];

2、查看当前数据库支持的存储引擎

show engines;

InnoDB

是一种兼顾高可靠性和高性能的通用存储引擎

是MySQL的默认存储引擎，支持事物外键，可应用于对事务的完整性有较高的要求，在并发条件下要求数据的一致性

特点：DML操作遵循ACID模型，支持事务；

行级锁，提高并发访问性能；

支持外键foreign key约束，保证数据的完整性和正确性

文件：xxx.idb:xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。

参数：innodb_file_per_table

存储引擎特点：tablespece(表空间)-->segment(段)-->extent(区)-->page(页)-->row(行)逻辑逐级排列

MySAM

是MySQL早期的默认存储引擎

以读操作和插入操作为主

特点：不支持事务，不支持外键

支持表锁，不支持行锁

访问速度快

文件：xxx.sdi(存储表结构信息)

xxx.MYD(存储数据)

xxx.MYI(存储索引)

Memory

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

缺陷是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性

特点：内存存放

hash索引（默认）

文件：xxx.sdi（存储表结构信息）

MySQL安装（Linux版本）

下载FinalShell --连接远程 linux 服务器（官网地址：http://www.hostbuf.com/）

Windows版本下载地址FinalShell SSH工具,服务器管理,远程桌面加速软件,支持Windows,macOS,Linux,版本4.0,更新时间2023.6.9 - FinalShell官网

具体安装见其他博主博客

安装成功，配置Linux网卡ip成功连接，上传Linux版本的MySQL

解压安装包

tar -xvf  mysql-8.0.27-linux-glibc2.12-x86_64.tar.xz

修改解压后的安装包名 （安装包是免安装版链接：提取码：qdtu）

mv mysql-8.0.27-linux-glibc2.12-x86_64/ mysql/

此用例为8.0.26版本（在超级用户账号安装，普通用户账号权限不够）

 安装mysql的安装包（输入一个并运行）

cd mysql

rpm -ivh mysql-community-common-8.0.26-1.el7.x86_64.rpm （第一条安装命令）

rpm -ivh mysql-community-client-plugins-8.0.26-1.el7.x86_64.rpm （第二条）

rpm -ivh mysql-community-libs-8.0.26-1.el7.x86_64.rpm 

rpm -ivh mysql-community-libs-compat-8.0.26-1.el7.x86_64.rpm

yum install openssl-devel

rpm -ivh  mysql-community-devel-8.0.26-1.el7.x86_64.rpm

rpm -ivh mysql-community-client-8.0.26-1.el7.x86_64.rpm

rpm -ivh  mysql-community-server-8.0.26-1.el7.x86_64.rpm

如果第二条安装出现依赖检测失败，则从第二条安装命令开始后面加上 --nodeps --force（后面安装命令依次加上）

虚拟机中Linux系统配置YUM源

先查看虚拟机是否有yum源

[root@red ~]# ls /etc/yum.repos.d/ 

无yum源，新建一个扩展名为repo的文件

[root@red ~]# vim /etc/yum.repos.d/rhel.repo 

进入默认在命令模式，无法编辑文件，需要输入i键，进入编辑模式，手动输入如下内容:

[a]
name=a
baseurl=http://172.25.0.254/rhel8.2/AppStream
gpgcheck=0
[b]
name=b
baseurl=http://172.25.0.254/rhel8.2/BaseOS
gpgcheck=0

启动MySQL服务

systemctl start mysqld

systemctl restart mysqld

systemctl stop mysqld

Linux配置本地YUM源（RHEL8）

索引概况：索引（index）是帮助mysql高效获取数据的数据结构(有序)。

 

 

 二叉树缺点：顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢

红黑树：大数据情况下，层级较深，检索速度慢

B-Tree（多路平衡查找树）

以一颗最大度数为 的b-tree为例（每个节点最多存储4个key，五个指针）

B+Tree：mysql索引数据结构对经典的B+Tree进行了优化，在原B+Tree的基础上，增加一个指向相邻节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能

两者区别：1、所有的数据都会出现在叶子节点   2、叶子节点形成一个单向链表

Hash：哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。

特点：hash索引只能用于对等比较(=,in),不支持范围查询（between，>,)<,...）

无法利用索引完成排序操作；查询效率高，要通常只需要一次检索就可以了，效率通常高于B+Tree索引

在mysql中，支持hash索引的是memory引擎，而innoDB中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的

为什么innoDB存储引擎选择使用B+Tree索引结构？
相对于二叉树，层级更少，搜索效率高

对于B-Tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中的键值减少，指针跟着减少，要同样保存大量的数据，只能增加树的高度，导致性能降低

相对hash索引，B+Tree支持范围匹配及排序操作

 

 索引语法

创建索引

create[unique|fulltext]index index_name on table_name(index_col_name,...);

查看索引

show index from table_name;

删除索引

drop index index_name on table_name;

SQL 性能分析

mysql客户端连接成功后，通过show[session|global]status命令可以提供服务器状态信息，以下指令可以查看当前数据库的insert、update、delete、select的访问频次

show global status like 'Com____';