1、存储引擎
1.1、MySQL体系结构
- 连接层：最上层是一些客户端和链接服务，主要完成一些类似于连接处理、授权认证以及相关的安全方案。吴福气也会为安全接入的每个客户端验证它所具有的操作权限。
- 服务层：第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。
- 引擎层：存储引擎真正地负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。
- 存储层：主要是将数据文件存储在文件系统之上，并完成与存储引擎的交互。
1.2、存储引擎简介
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。
-- 在创建表时，指定存储引擎
create table 表名(
字段1 字段1类型 [comment '字段1注释'],
字段2 字段2类型 [comment '字段2注释'],
...
字段n 字段n类型 [comment '字段n注释']
) engine=INNODB [comment '表注释'];
-- 查询建表语句 -- MySQL默认存储引擎为InnoDB
show create table account;
-- 查询当前数据库支持的存储引擎
show engines;1.3、存储引擎类型
1.3.1、InnoDB
InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB是默认的 MySQL 存储引擎。
InnoDB的特点：
- DML操作遵循ACID模型，支持事务；
- 行级锁，提高并发访问性能；
- 支持外键foreign key 约束，保证数据的完整性和正确性。
文件：xxx.ibd：如 sccount.ibd，account是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。参数：innodb_file_per_table。
1.3.2、MyISAM
MyISAM是MySQL早期的默认存储引擎。
MyISAM的特点：
- 不支持事务；
- 支持表锁，不支持行锁；
- 访问速度快。
1.3.3、Memory
Memory引擎的表数据是存放在内存中的，由于受到硬件问题，或断电问题的影响，只能将这些表作为临时表或缓存使用。
特点：
- 内存存放；
- hash索引（默认）。
文件：xxx.sdi：存储表结构信息。
1.4、存储引擎的比较
| 特点 | InnoDB | MyISAM | Memory |
| 存储限制 | 64TB | 有 | 有 |
| 事务安全 | 支持 | - | - |
| 锁机制 | 行锁 | 表锁 | 表锁 |
| B+tree索引 | 支持 | 支持 | 支持 |
| Hash索引 | - | - | 支持 |
| 全文索引 | 支持（5.6版本之后） | 支持 | - |
| 空间使用 | 高 | 低 | N/A |
| 内存使用 | 高 | 低 | 中等 |
| 批量插入速度 | 低 | 高 | 高 |
| 支持外键 | 支持 | - | - |
1.5、存储引擎的选择
在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。
InnoDB：是MySQL的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含更多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。InnoDB可用于存储业务系统中对于事务、数据完整性要求较高的核心数据。
MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。MyISAM可用于存储业务系统的非核心事务。
MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。
2、MySQL8.0.26-Linux版安装
2.1、准备一台Linux服务器
云服务器或者虚拟机都可以。Linux的版本为 CentOS 7.
2.2、下载Linux版MySQL安装包
https://downloads.mysql.com/archives/community/
2.3、上传MySQL安装包至Linux系统
2.4、创建目录并解压
mkdir mysql
tar -xvf mysql-8.0.26-1.e17.x86_64.rpm-bundle.tar -C mysql2.5、安装mysql的安装包
cd mysql
rpm -ivh mysql-community-common-8.0.26-1.e17.x86_64.rpm
rpm -ivh mysql-community-client-plugin-8.0.26-1.e17.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.26-1.e17.x86_64.rpm
rpm -ivh mysql-community-libs-compat-8.0.26-1.e17.x86_64.rpm
rpm -ivh mysql-community-devel-8.0.26-1.e17.x86_64.rpm
rpm -ivh mysql-community-client-8.0.26-1.e17.x86_64.rpm
rpm -ivh mysql-community-server-8.0.26-1.e17.x86_64.rpm2.6、启动MySQL服务
systemctl start mysqld
systemctl restart mysqld
systemctl stop mysqld2.7、查询自动生成的root用户密码
grep 'temporary password' /var/log/mysqld.log命令行执行指令：mysql -u root -p，然后输入上述查询到的自动生成的密码，完成登录。
2.8、修改root用户密码
登录到MySQL之后，需要将自动生成的不便记忆的密码修改了，修改成自己熟悉的便于记忆的密码：alter user 'root'@'localhost' identified by '1234';，执行上述的SQL会报错，原因是因为设置的密码太简单，密码复杂度不够。我们可以设置密码的复杂度为简单类型，密码长度为4：
set global validate_password.policy = 0;
set global validate_password.length = 4;降低密码的校验规则之后，再次执行上述修改密码的指令。
2.9、创建用户并给root用户分配权限
默认的root用户只能当前节点localhost访问，是无法远程访问的，我们还需要创建一个root账户，用户远程访问：create user 'root'@'%' identified with mysql_native_password by '1234';，并给root用户分配权限：grant all on *.* to 'root'@'%';
3、索引
3.1、索引概述
索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护这满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
| 优势 | 劣势 |
| 提高数据检索的效率，降低数据库的IO成本 | 索引列也是要占用空间的 |
| 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗 | 索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行insert、update、delete时，效率降低 |
3.2、索引结构
MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种：
| 索引结构 | 描述 |
| B+Tree索引 | 最常见的索引类型，大部分引擎都支持B+树索引 |
| Hash索引 | 底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询 |
| R-tree（空间索引） | 空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少 |
| Full-text（全文索引） | 是一种通过建立倒排索引，快速匹配文档的方式。类似于Lucene，Solr，ES |
| 索引 | InnoDB | MyISAM | Memory |
| B+tree索引 | 支持 | 支持 | 支持 |
| Hash索引 | 不支持 | 不支持 | 支持 |
| R-tree索引 | 不支持 | 支持 | 不支持 |
| Full-text | 5.6版本之后支持 | 支持 | 不支持 |
我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引。
3.2.1、B+Tree
二叉树结构缺点：顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢。
红黑树：大数据量情况下，层级较深，检索速度慢。
B-Tree（多路平衡查找树）：以一颗最大度数（max-degree）为5（5阶）的b-tree为例（每个节点最多存储4个key，5个指针）。B-Tree的可视化网站：https://www.cs.usfca.edu/~galles/visualization/BTree.html 。
B+Tree：以一颗最大度数（max-degree）为4（4阶）的B+Tree为例。
MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。
思考：为什么InnoDB存储引擎选择使用B+Tree索引结构？
- 相对于二叉树，层级更少，搜索效率高；
- 对于B-Tree，无论是叶子结点还是非叶子结点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低。
- 相对Hash索引，B+Tree支持范围匹配及排序操作。
3.2.2、Hash
哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。
Hash索引的特点：
- Hash索引只能用于对等比较（=，in），不支持范围查询（between、>、<、...）；
- 无法利用索引完成排序操作；
- 查询效率高，通常只需要一次检索就可以了，效率通常要高于B+Tree索引。
在MySQL中，支持hash索引的是Memory引擎，而InnoDB中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。
3.3、索引分类
| 分类 | 含义 | 特点 | 关键字 |
| 主键索引 | 针对于表中主键创建的索引 | 默认自动创建，只能有一个 | primary |
| 唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | unique |
| 常规索引 | 快速定位特定数据 | 可以有多个 | |
| 全文索引 | 全文索引查找的是文本中的关键词，而不是比较索引中的值 | 可以有多个 | fulltext |
在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：
| 分类 | 含义 | 特点 |
| 聚集索引（Clustered Index） | 将数据存储与索引放到了一块，索引结构的叶子结点保存了行数据 | 必须有，而且只有一个 |
| 二级索引（Secondary Index） | 将数据与索引分开存储，索引结构的叶子结点关联的是对应的主键 | 可以存在多个 |
聚集索引的选取规则：
- 如果表存在主键，主键索引就是聚集索引；
- 如果不存在主键，将使用第一个唯一（unique）索引作为聚集索引；
- 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
思考：
-- 以下SQL语句，哪个执行效率高？为什么？
# 备注：id为主键，name字段创建的有索引
select * from user where id = 10; # 效率更高
select * from user where name = 'Arm'; # 需要回表查询，因此效率更低
-- InnoDB主键索引的B+Tree高度为多高呢？
假设：一行数据大小为1k，一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空间，主键即使为bigint，占用字节数为8。
高度为2的B+Tree，设一页可以存储n个主键，则有：n*8 + (n+1)*6 = 16 * 1024，算出n约为1170。故可以存储的行数 = (1170+1) * 16 = 18736 。
高度为3的B+Tree，同理可得存储的行数 = (1170+1)^2 * 16 = 21939856 。3.4、索引的语法
-- 创建索引
create [unique | fulltext] index index_name on table_name (index_col_name,...);
-- 查看索引
show index from table_name;
-- 删除索引
drop index index_name on table_name;案例：
-- 按照下面的需求，完成索引的创建：
-- name字段为姓名字段，该字段的值可能会重复，为该字段创建索引。
create index idx_user_name on tb_user(name);
-- phone手机号字段的值，是非空、且唯一的，为该字段创建唯一索引。
create unique index idx_user_phone on tb_user(phone);
-- 为profession、age、status创建联合索引。
create index idx_user_pro_age_sta on tb_user(profession, age, status);
-- 为email建立合适的索引来提升查询效率。
create index idx_user_email tb_user on tb_user(email);3.5、SQL性能分析
3.5.1、SQL执行频率
MySQL客户端连接成功后，通过show [session | global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的insert、update、delete、select的访问频次：show global status like 'Com_______';。
3.5.2、慢查询日志
p76
标签：存储,进阶,Tree,索引,引擎,InnoDB,MySQL From： https://www.cnblogs.com/aoe1231/p/16792412.html