田敏
返回博客列表
技术

Mysql优化

田敏
2024-01-172 分钟阅读
CodingTipsC#

Mysql优化

说到Mysql的优化，我们肯定第一时间想到的就是利用索引的方式来进行一个优化；

1、索引

1、索引是什么？

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

2、Mysql当中索引采用什么样的数据结构来进行维护？

在Mysql当中，选取的是采用B树来作为索引的结构；那为啥是采用B+树，而非二叉树以及红黑树又或者其他的数据结构呢？

3、二叉树的优缺点：

假设出现一种业务场景，数值都是按顺序所排列的；

image-20240108221013503

其数据结构就会退化成链表，假设需要查询5，同样要进行五次io操作，也就是二叉树在这种业务场景非常不合适；

4、红黑树的优缺点：
5、采用Hash作为索引的优缺点：
6、b树

img

B树简单地说就是多叉树，每个叶子会存储数据，和指向下一个节点的指针。

例如要查找9，步骤如下

  1. 我们与根节点的关键字 (17，35）进行比较，9 小于 17 那么得到指针 P1；
  2. 按照指针 P1 找到磁盘块 2，关键字为（8，12），因为 9 在 8 和 12 之间，所以我们得到指针 P2；
  3. 按照指针 P2 找到磁盘块 6，关键字为（9，10），然后我们找到了关键字 9。
为什么采用B+树作为索引而非b树？
  1. B+树非叶子节点不存在数据只存索引，B树非叶子节点存储数据
  2. B+树查询效率更高。B+树使用双向链表串连所有叶子节点，区间查询效率更高（因为所有数据都在B+树的叶子节点，扫描数据库 只需扫一遍叶子结点就行了），但是B树则需要通过中序遍历才能完成查询范围的查找。
  3. B+树查询效率更稳定。B+树每次都必须查询到叶子节点才能找到数据，而B树查询的数据可能不在叶子节点，也可能在，这样就会造成查询的效率的不稳定
  4. B+树的磁盘读写代价更小。B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，通常B+树矮更胖，高度小查询产生的I/O更少。
6、B+树作为索引：

B+树是B树的改进，简单地说是：只有叶子节点才存数据，非叶子节点是存储的指针；所有叶子节点构成一个有序链表

img

数据库当中默认使用16KB的块来存储节点；假设索引类型BigInt，其所占空间为8B，其中6B存放下一个快的地址；也就是16KB/(8+6)=1170个节点；

image-20240108223822798

2、MyISAM存储引擎

MyISAM索引文件和数据文件是分离的；MyISAM 是 MySQL 早期的默认存储引擎。

特点：

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

文件：

  • xxx.sdi: 存储表结构信息
  • xxx.MYD: 存储数据
  • xxx.MYI: 存储索引

img

1、MyISAM 和 InnoDB 两种引擎所使用的索引的数据结构是什么？

都是 B+ 树，不过区别在于：

  • MyISAM 中 B+ 树的数据结构存储的内容是实际数据的地址值，它的索引和实际数据是分开的，只不过使用索引指向了实际数据。这种索引的模式被称为非聚集索引。
  • InnoDB 中 B+ 树的数据结构中存储的都是实际的数据，这种索引有被称为聚集索引。

3、联合索引

在学习的过程中，突然涉及到了一个我没听过的名词“联合索引”；好好的了解一下这个东西；

1、联合索引是什么？

根据为自己的理解就是假设根据（a,b,c）这个进行联合索引，会先根据阿来进行排序，再根据b，在根据c；

官方一点就是：联合索引（Composite Index）又称复合索引，它包括两个或更多列。与单列索引不同，联合索引可以覆盖多个列，这有助于加速复杂查询和过滤条件的检索。联合索引的列顺序非常重要，因为查询优化器会按照索引列的顺序执行搜索。

联合索引会引入一个最新的概念：最左匹配原则

2、最左匹配原则

使用 命令查询当前表的索引

SHOW INDEX FROM your_table_name;

查找的得到索引，依次解释以下字段的含义：

image-20240110215852762

| Table | 表示索引所属的表的名称 | | ------------ | ------------------------------------------------------------ | | Non_unique | 表示索引是否可以包含重复的值。1 表示可以包含重复值（非唯一索引），0 表示不能包含重复值（唯一索引）。 | | Key_name | 索引的名称 | | Seq_in_index | 索引中的列的序列号。表示在复合索引中的列的位置 | | Column_name | 索引中的列的名称 | | Collation | 列的排序规则 | | Cardinality | 表示索引的基数，即不同值的估计数量 | | Sub_part | 在索引的一部分上存储的字符数 | | Packed | 如果索引是压缩的，表示压缩方式 |

可以看到在当前的表中，其联合索引是（merchant_id,order_index)的方式；

EXPLAIN SELECT * FROM test_table_union_index WHERE merchant_id=2 AND order_id=565

得到结果，可以发现其是有使用到索引的；

image-20240110222014036

现在，我们将查询次序倒置过来

EXPLAIN SELECT * FROM test_table_union_index WHERE order_id=565 AND merchant_id=2

image-20240110222128644

欸，发现查询结果并没有改变；不是有最左匹配原则吗，那为什么查询得到的结果都是一样的，都使用到了索引；其实这其中涉及到了一个知识：**在执行sql的时候，优化器会 帮我们调整where后a,b,c的顺序，让我们用上索引。**至于EXPLAIN的各个字段的解释，我就放到后面第五点进行一个补充；

image-20240109222630106

4、Navicat生成MySQL测试数据

偶然间在网上发现可以使用Navicat来生成Mysql表的数据，特此记录一下；

image-20240109222009835

image-20240109222046842

image-20240109222123518

5、Explain 得到的各个字段的解释

含义表：

| id | 查询的标识符，表示查询中执行顺序的标识，有时会以子查询的形式存在。 | | ----------------- | ------------------------------------------------------------ | | select_type | 查询的类型，可能是SIMPLE（简单查询）、PRIMARY（最外层查询）、SUBQUERY（子查询）等。 | | table | 正在访问的表的名称 | | partitions | 表的分区信息 | | type | 表示访问表的方式，常见的类型有： ALL: 全表扫描。 index: 索引全扫描。 range: 利用索引的范围查找。 ref: 使用非唯一索引来查找单行记录。 eq_ref: 使用唯一索引查找单行记录。 | | possible_keys | 显示可能应用在这张表中的索引，表示查询中可以利用的索引。 | | key | 实际使用的索引。 | | key_len | 表示索引中使用的字节数，值越小越好。 | | ref | 显示索引的哪一列被使用了，如果是常数，则表示是常数条件 | | rows | 估计需要读取的行数 | | filtered | 表示按表条件过滤的行的百分比 | | Extra | 包含MySQL解决查询的详细信息，常见的有Using index、Using where、Using temporary、Using filesort等 |

6、BufferPool

1、首先抛出两个个问题：为什么有BufferPool？什么是BufferPool？

首先我们知道Mysql的数据是存储在硬盘当中，当我们去执行一条sql语句，就去查询一次硬盘，那这样的话性能是非常不好的。所以，想要提高性能，加个缓存就行了嘛。所以，当数据从磁盘中取出后，缓存内存中，下次查询同样的数据的时候，直接从内存中读取。

为此，Innodb 存储引擎设计了一个缓冲池（Buffer Pool），来提高数据库的读写性能。

img

所以说，BufferPool就是类似于缓冲池的结构；

2、通过Free链表对空白页进行管理；

Buffer Pool 是一片连续的内存空间，当 MySQL 运行一段时间后，这片连续的内存空间中的缓存页既有空闲的，也有被使用的。

那当我们从磁盘读取数据的时候，总不能通过遍历这一片连续的内存空间来找到空闲的缓存页吧，这样效率太低了。

所以，为了能够快速找到空闲的缓存页，可以使用链表结构，将空闲缓存页的「控制块」作为链表的节点，这个链表称为 Free 链表（空闲链表）。

img

每次将页从硬盘加载到bufferpool当中，直接在Free链表当中去查询到空闲页进行分配；每次当一个页被释放，又将其对应的控制块添加到free链表当中；

3、如何管理脏页？
4、脏页什么时候会被刷入磁盘？

引入了 Buffer Pool 后，当修改数据时，首先是修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页，但是磁盘中还是原数据。

因此，脏页需要被刷入磁盘，保证缓存和磁盘数据一致，但是若每次修改数据都刷入磁盘，则性能会很差，因此一般都会在一定时机进行批量刷盘。

可能大家担心，如果在脏页还没有来得及刷入到磁盘时，MySQL 宕机了，不就丢失数据了吗？

这个不用担心，InnoDB 的更新操作采用的是 Write Ahead Log 策略，即先写日志，再写入磁盘，通过 redo log 日志让 MySQL 拥有了崩溃恢复能力。

下面几种情况会触发脏页的刷新：

  • 当 redo log 日志满了的情况下，会主动触发脏页刷新到磁盘；
  • Buffer Pool 空间不足时，需要将一部分数据页淘汰掉，如果淘汰的是脏页，需要先将脏页同步到磁盘；
  • MySQL 认为空闲时，后台线程回定期将适量的脏页刷入到磁盘；
  • MySQL 正常关闭之前，会把所有的脏页刷入到磁盘；

在我们开启了慢 SQL 监控后，如果你发现**「偶尔」会出现一些用时稍长的 SQL**，这可能是因为脏页在刷新到磁盘时可能会给数据库带来性能开销，导致数据库操作抖动。

如果间断出现这种现象，就需要调大 Buffer Pool 空间或 redo log 日志的大小。

我们可以通过命令查询redolog文件所在的位置

SHOW VARIABLES LIKE 'datadir';//查询redolog所在的路径
innodb_log_file_size：指定每个redo日志大小，默认值48MB
innodb_log_files_in_group：指定日志文件组中redo日志文件数量，默认为2
innodb_log_group_home_dir：指定日志文件组所在路劲，默认值./，指mysql的数据目录datadir

那我们什么时候会将更改操作放入redolog里面，首先可能以为的是每次更新操作不应该都会将这次操作放入redolog里面吗？其实这样是有问题的，可以想当执行一个事务的时候，你一次update就放到redolog里面吗？显然是不行的，假如事务进行回滚的话，肯定是不能放到redolog里面的，所以我们应该在commit的时候进行添加；

image-20240117211931603

参考文章：

面试官:谈谈你对mysql联合索引的认识? - 知乎 (zhihu.com)

[MySQL为什么不用二叉树与B树索引，而是用B+树？ - 知乎 (zhihu.com)](https://zhuanlan.zhihu.com/p/345414925#:~:text=B%2B树查询效率更高。 B%2B树使用双向链表串连所有叶子节点，区间查询效率更高（因为所有数据都在B%2B树的叶子节点，扫描数据库 只需扫一遍叶子结点就行了），但是B树则需要通过中序遍历才能完成查询范围的查找。 B%2B树查询效率更稳定。,B%2B树每次都必须查询到叶子节点才能找到数据，而B树查询的数据可能不在叶子节点，也可能在，这样就会造成查询的效率的不稳定 B%2B树的磁盘读写代价更小。 B%2B树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，通常B%2B树矮更胖，高度小查询产生的I%2FO更少。 这就是MySQL使用B%2B树的原因，就是这么简单！)

一文了解MySQL的Buffer Pool - 知乎 (zhihu.com)

版权协议：MIT返回列表