MySQL存储引擎与索引优化

最新推荐文章于 2024-09-20 21:26:51 发布

Xixw

最新推荐文章于 2024-09-20 21:26:51 发布

阅读量810

点赞数

分类专栏： Mysql数据库相关文章标签：数据库架构 mysql

本文链接：https://blog.csdn.net/weixin_43156699/article/details/119342992

版权

Mysql数据库相关专栏收录该内容

3 篇文章 1 订阅

订阅专栏

MySQL存储引擎

1 MySQL体系结构概述
2 MySQL主要文件
3 MySQL 存储引擎
4 MySQL索引
5 问题

1 MySQL体系结构概述

MySQL 从概念上分为四层，这四层自顶向下分别是网络连接层，服务层（核心层），存储引擎层，系统文件层。
在这里插入图片描述

1.1 网络连接层（Client Connectors）

主要负责连接管理、授权认证、安全等等。
每个客户端连接都对应着服务器上的一个线程。服务器上维护了一个线程池，避免为每个连接都创建销毁一个线程。当客户端连接到 MySQL 服务器时，服务器对其进行认证。可以通过用户名与密码认证，也可以通过 SSL 证书进行认证。登录认证后，服务器还会验证客户端是否有执行某个查询的操作权限。这一层并不是 MySQL 所特有的技术。

1.2 服务层（MySQL Server）

MySQL 的核心服务都在这一层：查询解析，SQL 执行计划分析，SQL 执行计划优化，查询缓存。以及跨存储引擎的功能都在这一层实现：存储过程，触发器，视图等。

连接池（Connection Pool）：管理、缓冲用户的连接，线程处理等需要缓存的需求。
管理服务和工具组件（Services & utilities）：系统管理和控制工具，例如备份恢复、MySQL 复制、集群等。
SQL 接口（SQL Interface）：接受用户的 SQL 命令，并且返回用户需要查询的结果。
查询解析器（Parser）：SQL 命令传递到解析器的时候会被解析器验证和解析（权限、语法结构）。
查询优化器（Optimizer）：SQL 语句在查询之前会使用查询优化器对查询进行优化。
缓存（Caches）：如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。

1.3 存储引擎层（Pluggable Storage Engines）

负责 MySQL 中数据的存储与提取。
服务器中的查询执行引擎通过 API 与存储引擎进行通信，通过接口屏蔽了不同存储引擎之间的差异。MySQL 采用插件式的存储引擎。MySQL 为我们提供了许多存储引擎，每种存储引擎有不同的特点。我们可以根据不同的业务特点，选择最适合的存储引擎。如果对于存储引擎的性能不满意，可以通过修改源码来得到自己想要达到的性能。

MySQL 存储引擎的特点
（1）MySQL 采用插件式的存储引擎。
（2）存储引擎是针对于表的而不是针对库的（一个库中不同表可以使用不同的存储引擎），服务器通过 API 与存储引擎进行通信，用来屏蔽不同存储引擎之间的差异。
（3）不管表采用什么样的存储引擎，都会在数据区，产生对应的一个 frm 文件（表结构定义描述文件）。

1.4 系统文件层（File System NTFS ext4 SAN NAS）

系统文件层包含数据、索引、redo 日志、undo 日志、错误日志、查询记录、慢查询等，该层的主要作用是将数据库的数据存储在文件系统之上，并完成与存储引擎的交互。

2 MySQL主要文件

2.1 配置文件

Linux 系统中，MySQL 默认的配置文件名是 my.cnf，一般存放在 /etc/my.cnf，/etc/mysql/my.cnf 目录中，MySQL 的常用的配置信息都存放在该文件中。

查看 MySQL 默认加载配置文件的顺序：mysql --help | grep my.cnf

在不修改配置文件的情况下，MySQL 还可以通过命令来设置变量，比如可以使用 GLOBAL 关键字设置全局变量，可以使用 SESSION 关键字设置会话变量，设置好的变量，我们可以通过 SHOW 命令查看。

SET GLOBAL slow_query_log = 1;，设置慢查询开启
SET SESSION slow_query_log = 1;，设置当前会话慢查询开启
SHOW VARIABLES LIKE '%slow%';，查看

2.2 日志文件

（1）错误日志（Error log）

记录 MySQL 运行过程中较为严重的警告和错误信息，以及 MySQL 每次启动和关闭的详细信息。默认开启。在 my.cnf 配置文件中的参数是 log_error，例如mysql.err。

show variables like '%log_err%'; 查看错误日志配置信息

（2）慢查询日志（Slow query log）

将运行超过某个时间阈值的 SQL 语句记录到文件，在 my.cnf 配置文件中的参数是slow.log。默认情况下，MySQL 是不开启慢查询日志的。

MySQL 5.1 开始可以以毫秒为单位记录运行的 SQL 语句。
MySQL 5.5 开始可以将慢查询保存到表。
MySQL 5.6 开始可以更细粒度的记录慢查询。
MySQL 5.7 将时区信息写入到慢查询日志。

（3）通用查询日志（General query log）

可以记录数据库所有相关操作，比如客户端连接信息，执行语句的信息等，默认文件名是：机器名.log，因为通用查询日志功能是同步记录的，开启后性能明显下降。通用查询日志一般用来做审计用的，用于记录 MySQL 的所有操作，可以追踪用户的登录信息、操作信息等。

（4）二进制日志（Binary log）

二进制日志（binary log）是一个二进制文件，记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。binlog是mysql的逻辑日志，并且由Server层进行记录，使用任何存储引擎的mysql数据库都会记录binlog日志。在实际应用中，binlog的主要使用场景有两个，分别是主从复制和数据恢复。

主从复制：在Master端开启binlog，然后将binlog发送到各个Slave端，Slave端重放binlog从而达到主从数据一致。
数据恢复：通过使用mysqlbinlog工具来恢复数据。

show binary logs; 查看当前服务器所有的二进制日志文件
show master logs;

（5）redolog

redolog的作用？
redolog是Innodb存储引擎产生的日志文件，我们都知道InnoDB是事务型存储引擎，事务的四大特性里面有一个是持久性，具体来说就是只要事务提交成功，那么对数据库做的修改就被永久保存下来了，不可能因为任何原因再回到原来的状态。那么mysql是如何保证一致性的呢？最简单的做法是在每次事务提交的时候，将该事务涉及修改的数据页全部刷新到磁盘中。但是这么做会有严重的性能问题，主要体现在两个方面：一是因为Innodb是以页为单位进行磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷到磁盘的话，太浪费资源了！二是因为一个事务可能涉及修改多个数据页，并且这些数据页在物理上并不连续，使用随机IO写入性能太差！因此mysql设计了redo log，具体来说就是只记录事务对数据页做了哪些修改，相对而言文件更小并且是顺序IO。
redolog的结构？
redo log包括两部分：一个是内存中的日志缓冲(redo log buffer)，另一个是磁盘上的日志文件(redo log file)。mysql每执行一条DML语句，先将记录写入日志缓冲，后续某个时间点再一次性将多个操作记录写到日志文件。这种先写日志，再写磁盘的技术就是MySQL里经常说到的WAL(Write-Ahead Logging) 技术。因为在计算机操作系统中，用户空间(user space)下的缓冲区数据一般情况下是无法直接写入磁盘的，中间必须经过操作系统内核空间(kernel space)缓冲区(OS Buffer)。因此，日志缓冲写入日志文件实际上是先写入OS Buffer，然后再通过系统调用fsync()将其刷到日志文件中。
redo log与binlog区别？
binlog日志只用于归档，只依靠binlog是没有crash-safe能力的。但只有redo log也不行，因为redo log是InnoDB特有的，且日志上的记录落盘后会被覆盖掉。因此需要binlog和redo log二者同时记录，才能保证当数据库发生宕机重启时，数据不会丢失。

（6）undolog

数据库事务四大特性中有一个是原子性，具体来说就是原子性是指对数据库的一系列操作，要么全部成功，要么全部失败，不可能出现部分成功的情况。实际上，原子性底层就是通过undo log实现的。undo log主要记录了数据的逻辑变化，比如一条INSERT语句，对应一条DELETE的undo log，对于每个UPDATE语句，对应一条相反的UPDATE的undo log，这样在发生错误时，就能回滚到事务之前的数据状态。
同时，undo log也是MVCC(多版本并发控制)实现的关键。

2.3 表结构定义文件&数据文件

以 frm 结尾的文件就是表结构定义文件，每张表对应一个表结构定义文件，表结构定义文件是二进制文件。
db.opt 文件记录这个库的默认使用的字符集和校验规，文件存放在所属数据库的目录下。MySQL 的配置文件 my.cnf 中有一个参数 datadir，配置了 MySQL 数据文件的存放位置。

MYD文件

MYD 文件是 MyISAM 存储引擎专用，MYD 文件存放 MyISAM 表的数据（data)。每一张 MyISAM 表都会有一个 .MYD 文件，文件存放在所属数据库的目录下。
MYI 文件也是 MyISAM 存储引擎专用，存放 MyISAM 表的索引相关信息。每一张 MyISAM 表对应一个 .MYI 文件，文件存放在所属数据库的目录下。

IBD文件

IBD 文件和 IBDATA 文件存放 InnoDB 的数据文件（包括索引）。InnoDB 存储引擎有两种表空间方式：独享表空间和共享表空间。

独享表空间：使用 .ibd 文件来存放数据，且每一张 InnoDB 表对应一个 .ibd 文件，文件存放在所属数据库的目录下。
共享表空间：使用 .ibdata 文件，所有表共同使用一个（或多个，自行配置）.ibdata 文件。

2.4 其他文件

除了上面的文件，MySQL 的物理文件还包括 pid 文件、socket 文件。

3 MySQL 存储引擎

存储引擎是用来把数据存储在文件或内存的技术。mysql常用的存储引擎有MyISAM、InnoDB、MEMORY和ARCHIVE。对于MySQL 5.5及更高版本，默认的存储引擎是InnoDB，而在5.5版本之前，MySQL的默认存储引擎是MyISAM。
查看mysql支持引擎命令：SHOW ENGINES；

3.1 MyISAM存储引擎

特点：

堆表数据结构。
表锁设计，不适合高并发操作。
支持数据静态压缩。
不支持事务、外键。
数据容易丢失。
只缓存索引，不缓存真实数据。
索引容易损坏，不保证数据文件和索引文件同步更新，所以数据库一些异常宕机、磁盘满了等情况都可能造成数据文件和索引文件数据不一致。
select count(*) from table 无需进行数据的扫描。
数据（MYD）和索引（MYI）分开存储。
唯一的优点是：数据文件可以直接拷贝到另一台服务器上使用。

应用场景：
报表，数据仓库。

3.2 InnoDB存储引擎

特点：

事务 ACID。
行级锁，适合高并发。
聚集索引（主键索引）方式进行数据存储。
支持外键关系保证数据完整性。
不仅要缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性影响。

3.2 二者区别

（1）是否⽀持⾏级锁 : MyISAM 只有表级锁(table-level locking)，⽽InnoDB ⽀持⾏级锁(rowlevel locking)和表级锁，默认为⾏级锁。
（2）是否⽀持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原⼦性，其执⾏速度⽐InnoDB类型更快，但是不提供事务⽀持。但是InnoDB 提供事务⽀持事务，外部键等⾼级数据库功能。具有事务(commit)、回滚(rollback)和崩溃修复能⼒(crash recoverycapabilities)的事务安全(transaction-safe (ACID compliant))型表。
（3）是否⽀持外键： MyISAM不⽀持，⽽InnoDB⽀持。
（4）是否⽀持MVCC：仅 InnoDB ⽀持。应对⾼并发事务, MVCC⽐单纯的加锁更⾼效；MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下⼯作；MVCC可以使⽤乐观(optimistic)锁和悲观(pessimistic)锁来实现，各数据库中MVCC实现并不是统⼀的。
总的来说，MyISAM适合查询以及插入为主的应用，InnoDB更适合频繁修改以及涉及到安全性较高的应用，但是当需要事务支持时，肯定优先选择InnoDB。

4 MySQL索引

MySQL 官方对索引的定义为：索引(Index)是帮助 MySQL 高效获取数据的数据结构。可以得到索引的本质就是数据结构。索引列在MySQL 中也叫做“键（ key ）”，索引是存储引擎用于快速找到记录的一种数据结构。一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。

4.1 索引的数据结构

4.1.1 B树

MyISAM普通索引就采用B树数据结构。
B树的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；
在B树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在。

4.1.2 B+树

InnoDB普通索引就采用B+树数据结构。
B+树非叶子节点中只有关键字和指向下一个节点的索引，叶子节点中值存放数据记录；
B+树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。
B+Tree是MySql使用最频繁的一个索引数据结构，数据结构以平衡树的形式来组织，因为是树型结构，所以更适合用来处理排序，范围查找等功能。相对Hash索引，B+树在查找单条记录的速度虽然比不上Hash索引，但是因为更适合排序等操作，所以他更受用户的欢迎。毕竟不可能只对数据库进行单条记录的操作。

有关B树和B+树的具体内容可以在之前博客中查找~
B树与B+树详解

4.1.3 R树

R树在mysql很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有myisam、bdb、innodb、ndb、archive几种。相对于B树，R树的优势在于范围查找。

4.1.4 Hash

Hsah索引在MySql比较少用，他以把数据的索引以Hash形式组织起来，因此当查找某一条记录的时候，速度非常快。因为Hash结构，每个键只对应一个值，而且是散列的方式分布。所以他并不支持范围查找和排序等功能。
Hash索引只有Memory, NDB两种引擎支持，Memory引擎默认支持Hash索引，如果多个hash值相同，出现哈希碰撞，那么索引以链表方式存储。

4.2 索引分类

从物理存储的角度来分的话，索引分为聚簇索引和非聚簇索引。区别主要看叶子节点存了什么数据。
（1） 聚簇索引
innodb使用聚簇索引。
聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的即为整张表的行记录数据。.frm存放的是表结构，.ibd存放数据文件和索引文件。
（2） 非聚簇索引
myisam使用非聚簇索引。
数据文件和索引文件分开存放的模式叫做非聚簇索引。.frm存放表结构，.MYI存放索引数据，.MYD存放实际数据。
（3）区别
聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。聚簇索引查询相对会更快一些，因为主键索引树的叶子节点直接就是我们要查询的整行数据了，而非主键索引的叶子节点是主键的值，查到主键的值以后，还需要再通过主键的值再进行一次查询（这个过程叫做回表，也就是查了2 个索引树）。

从约束功能来分的话，索引可以分为普通索引、唯一索引、联合索引和全文索引等。
（1）普通索引（index) ：加速查找
（2）唯一索引
主键索引（primary key ）：加速查找+约束（不为空且唯一）
唯一索引（unique）：加速查找+约束（唯一）
（3）联合索引
-primary key(id,name)：联合主键索引
-unique(id,name)：联合唯一索引
-index(id,name)：联合普通索引
（4）全文索引（fulltext ）：用于搜索很长一篇文章的时候，效果最好。

4.3 适合创建索引的情况

主键自动建立唯一索引;
频繁作为查询条件的字段应该创建索引
查询中与其它表关联的字段，外键关系建立索引
单键/组合索引的选择问题，组合索引性价比更高
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段

4.4 不适合创建索引的情况

表记录太少
经常增删改的表或者字段
Where条件里用不到的字段不创建索引
过滤性不好的不适合建索引

4.5 索引优化

（1）全值匹配
查询的字段按照顺序在索引中都可以匹配到。

（2） 最佳左前缀法则
查询字段与索引字段顺序的不同会导致，索引无法充分使用，甚至索引失效!
原因：使用复合索引，需要遵循最佳左前缀法则，即如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
结论：过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。

（3）不要在索引列上做任何计算
在索引列上做任何操作(计算、函数、(自动 or 手动)类型转换)，会导致索引失效而转向全表扫描。

（4）索引列上不能有范围查询
将可能做范围查询的字段的索引顺序放在最后。

（5）尽量使用覆盖索引
覆盖索引指的是查询的字段，都在索引上，即查询列要被所使用的索引覆盖。

（6）尽量不使用(!= 或者<>)的时候
mysql 在使用不等于或者<>时，有时会无法使用索引会导致全表扫描。

（7）不使用 is not null 和 is null
is not null导致索引失效，is null对索引的使用也会有影响。

（8） like 的前后模糊匹配

索引失效

like '%xxx'
like '%xxx%'

索引不失效

like 'xxx%'

（9）减少使用or
使用union all 或者union来代替。

（10）注意间隙锁的使用
注意：索引失效将导致行锁变表锁。
当我们用范围条件而不是相等条件检索数据，并请求共享或者排他锁时，innodb会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙”。innodb也会对这个间隙加锁，这种锁机制就是所谓的间隙锁(Next-Key锁)。因为Query执行过程中通过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。
间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。