一、存储引擎

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

1-1.MYSQL体系结构

image.png

1.连接层

最上层是一些客户端和链接服务，包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于
TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程
池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务
器也会为安全接入的每个客户端验证它所具有的操作权限。

2.服务层

第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部
分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如 过程、函数等。在该层，服务器会解
析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等，
最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，
这样在解决大量读操作的环境中能够很好的提升系统的性能。

3.引擎层

存储引擎层， 存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通
信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。数据库
中的索引是在存储引擎层实现的。

4.存储层

数据存储层， 主要是将数据(如: redolog、undolog、数据、索引、二进制日志、错误日志、查询
日志、慢查询日志等)存储在文件系统之上，并完成与存储引擎的交互。
和其他数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要
体现在存储引擎上，插件式的存储引擎架构，将查询处理和其他的系统任务以及数据的存储提取分离。
这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

建表时指定存储引擎

CREATE TABLE 表名（字段1 字段类型 【COMMIT 字段1注释】）ENGINE = INNODB【COMMENT 表注释】

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

show engines
例：CREATE TABLE my_myisam（id int，name varchar（10））ENGINE = MyISAM创建my_myisam，并指定MyISAM存储引擎

1-2.存储引擎特点

1-2-1.InnoDB

1.介绍：InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MYSQL5.5之后，InnoDB是默认的MYSQL存储引擎

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

3.文件：
XXX.ibd XXX代表的是表名，innoDB引擎的每张表都会对应这样的一个表空间文件，存储该表的表结构（frm-早期的，sdi新版的）、数据和索引
参数：innodb_file_per_table

如果该参数开启，代表对于InnoDB引擎的表，每一张表都对应一个ibd文件。 我们直接打开MySQL的
数据存放目录： C:\ProgramData\MySQL\MySQL Server 8.0\Data ， 这个目录下有很多文件
夹，不同的文件夹代表不同的数据库，我们直接打开itcast文件夹。
可以看到里面有很多的ibd文件，每一个ibd文件就对应一张表，比如：我们有一张表 account，就
有这样的一个account.ibd文件，而在这个ibd文件中不仅存放表结构、数据，还会存放该表对应的
索引信息。 而该文件是基于二进制存储的，不能直接基于记事本打开，我们可以使用mysql提供的一
个指令 ibd2sdi ，通过该指令就可以从ibd文件中提取sdi信息，而sdi数据字典信息中就包含该表
show variables like 'innodb_file_per_table'; 1

4.逻辑存储结构

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

1-2-2.MyISAM

介绍：MyISAM是MySQL早期默认存储引擎
特点：不支持事务，不支持外键；支持表锁，不支持行锁；访问速度快
文件：XXX.sdi存储表结构信息；XXX.MYD存储数据；XXX.MYI存储索引

1-2-3.Memory

介绍：Memory引擎的表数据是存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用
特点：内存存放 hash索引（默认）
文件：XXX.sdi存储表结构信息；

1-2-4.区别及特点

存储引擎选择

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

索引

索引（index）是帮助MySQL高效获取数据的数据结构（有序），在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引
例：假如我们要执行的SQL语句为 ： select * from user where age = 45;
无索引情况下，需要从第一行开始扫描，一直扫描到最后一行，我们称之为全表扫描，性能很低
有索引情况下，假设索引结构是二叉树，那么就意味着，会对age这个字段建立一个二叉树的索引结构，此时我们在进行查询时，只需要扫描三次就可以找到数据了，极大的提高的查询的效率。

索引的特点
优势：提高数据检索的效率，降低数据库的IO成本，通过索引列付数据进行排序，降低数据排序的成本，降低CPU的消耗
劣势：索引也会占用空间，索引大大提高了查询效率，同时也降低了更新表的速度，如对表进行INSERT/UPDATE/DELETE时，效率降低

索引结构

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

注意： 我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引。

二叉树

选择二叉树作为索引结构，会存在以下缺点：
顺序插入时，会形成一个链表，查询性能大大降低。
大数据量情况下，层级较深，检索速度慢。
此时大家可能会想到，我们可以选择红黑树，红黑树是一颗自平衡二叉树，那这样即使是顺序插入数
据，最终形成的数据结构也是一颗平衡的二叉树,
但是，即使如此，由于红黑树也是一颗二叉树，所以也会存在一个缺点：
大数据量情况下，层级较深，检索速度慢。
所以，在MySQL的索引结构中，并没有选择二叉树或者红黑树，而选择的是B+Tree，那么什么是
B+Tree呢？在详解B+Tree之前，先来介绍一个B-Tree。

B-Tree

B-tree，B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。
以一颗最大度数（max-degree）为5（5阶）的b-tree为例，那么这个b树最多存储4个key，5个指针

知识小贴士: 树的度数指的是一个节点的子节点个数。
我们可以通过一个数据结构可视化的网站来简单演示一下。 https://www.cs.usfca.edu/~galles/visualization/BTree.html
插入一组数据： 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88
120 268 250 。然后观察一些数据插入过程中，节点的变化情况。

1659063693250.png

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

B+Tree

B+tree是B-tree的变种，我们以一颗最大度数（max-degree）为4（4阶）的B+tree为例，来看一下其结构示意图：

1659065349374.png

1659066333987.png

上述我们所看到的结构是标准的B+Tree的数据结构，接下来，我们再来看看MySQL中优化之后的
B+Tree。
MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点
的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序。

1659067186577.png

Hash

MySQL中除了支持B+tree索引，还支持Hash索引
结构：哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中

1659067602842.png

如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可
以通过链表来解决

1659067649925.png

特点：
1.hash所有只能用于对等比较（=，in），不支持范围查询（between，<,...）
2.无法利用索引完成排序操作
3.查询效率高，通常（不存在hash冲突的情况）只需要一次检索就可以了，效率通常要高于B+tree索引

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

为什么InnoDB存储引擎选择使用B+tree所有结构？
1.相较于二叉树，层级更少，搜索效率高
2.对于b-tree，无论是叶子节点还是非叶子几点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低
3.相对于hash索引，b+tree支持范围匹配及排序操作

索引分类

在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引，唯一索引，常规索引，全文索引

而在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

聚集索引选取规则：
1.如果存在主键，主键索引就是聚集索引
2.如果不存在主键，将使用第一个唯一（UNIQUE）作为聚集索引
3.如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowId作为隐藏的聚集索引

聚集索引和二级索引的具体结构如下：

1659080280514.png

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

接下来，我们来分析一下，当我们执行如下的SQL语句时，具体的查找过程是什么样子的。

1659080382270.png

具体过程如下：
①.由于是根据name字段进行查询，所以先根据name=‘Arm’到name字段的二级索引中进行匹配查找。但是在二级索引中只能查到Arm对应的主键值10
②. 由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最
终找到10对应的行row。
③. 最终拿到这一行的数据，直接返回即可

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

以下两条SQL语句，那个执行效率高? 为什么?
A. select * from user where id = 10 ;
B. select * from user where name = 'Arm' ;
备注: id为主键，name字段创建的有索引；
解答：
A 语句的执行性能要高于B 语句。
因为A语句直接走聚集索引，直接返回数据。 而B语句需要先查询name字段的二级索引，然
后再查询聚集索引，也就是需要进行回表查询。

InnoDB主键索引的B+tree高度为多高呢?

1659080839765.png

索引语法

创建索引：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性能分析

SQL执行频率

MySQL客户端连接成功后，通过show【session|global】status命令可以提供服务器状态信息，通过如下指令，可以查看当前数据库的INSERT/UPDATE.SELECT的访问频次

-- session 是查看当前会话 ; 
-- global 是查询全局数据 ; 
SHOW GLOBAL STATUS LIKE 'Com_______';
Com_delete: 删除次数
Com_insert: 插入次数
Com_select: 查询次数
Com_update: 更新次数

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有
SQL语句的日志。
MySQL的慢查询日志默认没有开启，我们可以查看一下系统变量 slow_query_log。

如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

# 开启MySQL慢日志查询开关 
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志    
long_query_time=2
#配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log。
systemctl restart mysqld

在慢查询日志中，只会记录执行时间超多我们预设时间（2s）的SQL，执行比较快的是不会记录的

profile详情

show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪去了，通过have_profiling参数，能够看到当前MySQL是否支持profile操作
 show profiles
SELECT @@have_profiling
通过set语句在session/global级别开启profiling
SET profiling=1
-- 查看每一条SQL的耗时基本情况
show profiles;
-- 查看指定query_id的SQL语句各个阶段的耗时情况 
show profile for query query_id;
-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

explain

EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行的过程中表如连接和连接的顺序

-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件

EXPLAIN执行计划中各个字段的含义：

最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，
并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)
注意 ： 最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段(即是
第一个字段)必须存在，与我们编写SQL时，条件编写的先后顺序无关。

范围查询

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。
当范围查询使用>= 或 <= 时，走联合索引了，但是索引的长度为54，就说明所有的字段都是走索引
的。
所以，在业务允许的情况下，尽可能的使用类似于 >= 或 <= 这类的范围查询，而避免使用 > 或 <

索引失效情况

1.索引列运算：不要在索引列上进行运算操作，索引将失效
2.字符串不加引号：字符串类型字段使用时，不加引号，索引将失效。
3.模糊查询：如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
4.or连接条件：用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会
被用到
5.数据分布影响：如果MySQL评估使用索引比全表更慢，则不使用索引。

SQL提示

SQL提示是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的

#user index 建议MySQL是用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）
explain SELECT * FROM 表名 use index （字段名）WHERE 条件 
#ignore index 忽略指定的索引
explain SELECT * FROM 表名 ignore index （字段名）WHERE 条件 
#force index 强制使用索引
explain SELECT * FROM 表名 force index （字段名）WHERE 条件 

覆盖索引

尽量使用覆盖索引，减少SELECT * 那么什么是覆盖索引，覆盖索引是指查询时使用了索引，并且需要返回的列，在该索引中已经全部能够找到

一张表, 有四个字段(id, username, password, status), 由于数据量大, 需要对
以下SQL语句进行优化, 该如何进行才是最优方案:
select id,username,password from tb_user where username =
'itcast';
答案: 针对于 username, password建立联合索引, sql为: create index
idx_user_name_pass on tb_user(username,password);
这样可以避免上述的SQL语句，在查询的过程中，出现回表查询

前缀索引

当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让索引变的很大，查询时，浪费大量的磁盘IO，影响查询效率，此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率

CREATE index idx_xxx on table_name（column（n））

例：create index idx_email_5 on tb_user（email（5））为tb_user的表email字段建立长度为5的前缀索引

前缀长度
可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的
SELECT COUNT（DISTINCT email）/ COUNT（）FROM tb_user
SELECT COUNT（DISTINCT substring（email，1,5））/ COUNT（）FROM tb_user

1659143802121.png

单列索引与联合索引

单列索引：即一个索引只包含单个列
联合索引：即一个索引包含了多个列
在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，
而非单列索引
如果查询使用的是联合索引，具体的结构示意图如下：

1659143974133.png

索引设计原则

1.针对于数据量较大，且查询比较频繁的表建立索引
2.针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引
3.尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率就越高
4.如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引
5.尽量使用联合索引，减少单列索引，查询时，；联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
6.要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率
7.如果索引不能存储NULL值，请在创建表时使用NOT NULL 约束它，当优化器知道每列是否包含NULL值时，它可以更好的确定哪个索引最有效的用于查询

SQL优化

插入数据

insert

如果我们需要一次性往数据表里插入多条记录，可以从以下三个方面进行优化
1.批量插入数据
INSERT INTO tb_test values（1，‘tom’），（2，‘cat’）；
2.手动控制事务
start transaction;
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
commit;
3.主键顺序插入，性能要高于乱序插入

大批量插入数据

如果一次性需要插入大批量数据（比如：几百万的记录），使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入
--客户端连接服务端时，加上参数 --local-infile
mysql --local-infile -u root -p
--设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1
--执行load指令将准备好的数据，加载到表结构中
load data local infile ‘/root/sqll.log’ into table tb_user fields terminated by ‘，’ lines termainated by ‘\n’;

主键优化

1).数据组织方式
在InnoDB引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（index organized table IOT）

1659146279852.png

在InnoDB引擎中，数据行是记录在逻辑结构page页中的，而每一个页的大小是固定的，默认16k，那也就意味着，一个页中所存储的行也是有限的，如果插入的数据行row在该页存储不小，将会存储到下一页中，页与页之间会通过指针连接
2).页分裂
页可以为空，也可以填充一半，也可以填充100%，每个页包含了2-n行数据（如果一行数据过大，会行溢出），根据主键排列
a.主键顺序插入效果
①. 从磁盘中申请页， 主键顺序插入

1659147281074.png

1659147306484.png

1659147330726.png

④. 当第二页写满了，再往第三页写入

1659147439524.png

1659147505569.png

1659147548572.png

1659147581625.png

1659147606516.png

1659147671477.png

1659147695545.png

上述的这种现象，称之为页分裂，是比较耗费性能的操作
3).页合并
假设目前表中已有数据的索引结构（叶子节点）如下：

1659147010681.png

1659147059900.png

1659147092757.png

• 满足业务需求的情况下，尽量降低主键的长度
• 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键
• 尽量不要使用UUID做主键或者是其他自然主键，如身份证号
• 业务操作时，避免对主键的修改

order by优化

MySQL的排序，有两种方式：
Using filesort：通过表的索引或全局扫描，读取满足条件的数据行，然后在排序缓冲区sortbuffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫Filesort排序
Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高
对于以上两种排序方式，Using index性能高，而Using Filesort的性能低，我们在优化排序操作时，尽量要优化为Using index

order by优化法则：
a.根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
b.尽量使用覆盖索引
c.多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）
d.如果不可避免的出现filesort，大数据排序时，可以适当增大排序缓冲区大小sort_buffer_size（默认256k）

group by 优化

A. 在分组操作时，可以通过索引来提高效率。
B. 分组操作时，索引的使用也是满足最左前缀法则的

limit优化

优化思路: 一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查
询形式进行优化。

count优化

SELECT COUTN（*）FROM tb_user;
如果数据量很大，在执行count在、操作时，是非常耗时的

• MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count（*）的时候会直接返回这个数，效率很高，但是如果是带条件的count，MyISAM也慢
• InnDB引擎就麻烦了，它执行count（*）的时候，需要把数据一行一行的从引擎里面读出来，然后累积计数

如果说要大幅度提升InnoDB表的count效率，主要的优化思路：自己计数(可以借助于redis这样的数
据库进行,但是如果是带条件的count又比较麻烦了)。
count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是
NULL，累计值就加 1，否则不加，最后返回累计值。
用法：count（*）、count（主键）、count（字段）、count（数字）

按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count()，所以尽量使用 count()。

update优化

我们主要需要注意一下update语句执行时的注意事项
UPDATE coures SET name = ‘javaEE’ WHERE id = 1
当我们在执行删除的SQL语句时，会锁定id为1这一行的数据，然后事务提交之后，行锁释放。
但是当我们在执行如下SQL时。
update course set name = 'SpringBoot' where name = 'PHP' ;
当我们开启多个事务，在执行上述的SQL时，我们发现行锁升级为了表锁。 导致该update语句的性能
大大降低。
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁
升级为表锁 。