当前位置： 首页 > article >正文

Java学习笔记（十）

article 2024/10/30 8:02:12

MySQL 自适应哈希索引

MySQL 自适应哈希索引（Adaptive Hash Index）是 InnoDB 存储引擎提供的一种优化机制，用于提高查询性能。它基于数据页中的记录自动生成哈希索引，从而加速某些特定类型的查询操作，尤其是那些涉及主键或唯一键的精确查找操作。

工作原理

监控和统计：InnoDB 引擎会监控数据页上的访问模式，并统计哪些数据页上的记录被频繁访问。
哈希索引生成：当某个数据页上的记录访问频率达到某个阈值时，InnoDB 会为该数据页生成一个哈希索引。这个哈希索引将记录的主键或唯一键映射到数据页的地址。
查询优化：当执行查询时，如果查询条件匹配到某个已经生成哈希索引的数据页，InnoDB 可以直接使用哈希索引来快速定位数据页，从而加速查询过程。

配置和使用

自适应哈希索引是 InnoDB 的默认功能，通常不需要手动开启。但是，你可以通过以下系统变量来配置和监控自适应哈希索引的行为：

innodb_adaptive_hash_index：控制是否启用自适应哈希索引。默认值为 ON。

SET GLOBAL innodb_adaptive_hash_index = ON;

innodb_adaptive_hash_index_parts：控制哈希索引的分区数量。默认值为 8。这个值越大，哈希索引的冲突越少，但内存消耗也会增加。

SET GLOBAL innodb_adaptive_hash_index_parts = 16;
监控和调试

你可以通过以下方式监控自适应哈希索引的使用情况：

性能模式（Performance Schema）：MySQL 的性能模式提供了关于自适应哈希索引的详细统计信息。你可以查询 performance_schema 数据库中的相关表来获取这些信息。

SELECT * FROM performance_schema.data_locks;  
SELECT * FROM performance_schema.data_hash_index_usage;

SHOW ENGINE INNODB STATUS：通过执行 SHOW ENGINE INNODB STATUS 命令，你可以查看 InnoDB 引擎的当前状态，包括自适应哈希索引的一些统计信息。

SHOW ENGINE INNODB STATUS\G
注意事项
  1. 内存消耗：自适应哈希索引会消耗一定的内存资源。如果系统内存有限，或者数据表非常大且访问模式复杂，可能会导致内存消耗过多。
  2. 适用场景：自适应哈希索引最适合那些频繁进行精确查找操作的场景，特别是主键或唯一键查找。对于范围查询或复杂查询，自适应哈希索引的效果可能不明显。
  3. 禁用：在某些情况下，你可能需要禁用自适应哈希索引，例如在进行大量数据导入或批量更新操作时，以避免哈希索引的频繁重建导致的性能开销。
SET GLOBAL innodb_adaptive_hash_index = OFF;

通过合理配置和使用自适应哈希索引，你可以显著提高 MySQL 数据库的查询性能，特别是在处理高频访问的数据表时。

MyISAM 和 Innodb 的索引

MyISAM和InnoDB是MySQL数据库管理系统中的两种常用存储引擎，它们在索引结构上有显著的区别，这些区别影响了它们的性能、事务支持、数据一致性等多个方面。以下是对MyISAM和InnoDB索引的详细介绍：

一、MyISAM的索引

  1. 索引结构：

    • MyISAM使用B+树作为其索引结构。
    • MyISAM的索引与数据是分开存储的，这种索引被称为非聚集索引（Non-Clustered Index）。

  2. 索引类型：

    • 主键索引：MyISAM表可以有一个主键索引，其结构与普通索引类似，但主键索引的索引值是唯一的。
    • 普通索引：除主键索引外的其他索引都是普通索引，它们可以包含重复的值。

  3. 索引特点：

    • 无论是主键索引还是普通索引，其叶子节点都包含索引值和一个指向数据行记录的指针（或地址）。
    • 在MyISAM表中，即使查询使用了索引，也需要通过索引指针回表查询完整的数据行。
    • MyISAM的主键索引和普通索引是两颗独立的B+树，没有本质差异。

  4. 性能与限制：

    • MyISAM在只读或读多写少的场景下表现良好。
    • 由于索引和数据是分开存储的，插入和删除操作对索引结构影响较小，性能相对较好。
    • 但MyISAM不支持事务、外键以及行级锁定，使用表级锁定机制，在高并发场景下可能会导致性能瓶颈。
二、InnoDB的索引

  1. 索引结构：

    • InnoDB也使用B+树作为其索引结构。
    • 但InnoDB的索引结构是聚集索引（Clustered Index），即主键索引与行记录是存储在一起的。

  2. 索引类型：

    • 主键索引：InnoDB表的主键索引是聚集索引，叶子节点直接对应记录。
    • 二级索引（非主键索引）：二级索引的叶子节点存储的是对应行的主键值，而不是直接存储行数据。

  3. 索引特点：

    • InnoDB的主键索引包含了实际的数据记录，索引结构与数据存储物理结构紧密关联。
    • 每张InnoDB表都会按照主键创建一个聚集索引，因此主键索引不仅保存索引键，还保存了实际的行数据。
    • 如果表中没有定义主键，InnoDB会自动选择一个唯一的非空列作为主键。如果没有这样的列，InnoDB会隐式创建一个自增主键列。

  4. 性能与优势：

    • 由于聚集索引的设计，主键查询性能非常高，可以减少I/O操作。
    • InnoDB支持事务（ACID），能够确保数据的一致性，适合高并发写入和复杂的事务操作。
    • InnoDB还支持行级锁定和外键约束，提供了更高的数据完整性和并发性能。

  5. 限制：

    • 插入和删除操作会导致B+树的重平衡，影响性能。
    • 二级索引查询效率略低于主键查询，因为需要先通过二级索引找到主键，再通过主键查找数据。
三、总结

MyISAM和InnoDB在索引结构上的主要区别在于是否使用聚集索引。MyISAM使用非聚集索引，索引与数据分开存储；而InnoDB使用聚集索引，主键索引与行记录存储在一起。这种差异导致了它们在性能、事务支持、数据一致性等方面的不同表现。在选择存储引擎时，需要根据具体的应用场景和需求进行权衡。

回表

回表是MySQL数据库查询中的一个重要概念，特别是在使用InnoDB存储引擎时。以下是对回表的详细介绍：

一、回表的定义

回表是指在执行查询操作时，MySQL首先通过索引（通常是二级索引或非聚集索引）找到满足条件的记录的主键值，然后再通过主键值回到主键索引（聚集索引）中去查找完整的记录信息的过程。简单来说，就是先通过索引找到主键，再通过主键找到完整的数据行。

二、回表的原理

在InnoDB存储引擎中，数据是按照主键索引（聚集索引）的顺序存储的，而二级索引（非聚集索引）的叶子节点存储的是对应行的主键值。因此，当通过二级索引查询数据时，MySQL需要先找到对应的主键值，然后再通过主键值去主键索引中查找完整的数据行。这个过程就称为回表。

三、回表的影响

回表操作会增加数据库的IO开销和CPU消耗，因为需要进行两次索引查找（一次是二级索引查找，一次是主键索引查找）。在高并发、大数据量的场景下，回表可能成为性能瓶颈，影响查询的响应时间。

四、如何避免回表

为了避免回表操作，可以采取以下几种策略：

  1. 使用覆盖索引：尽量设计能够覆盖查询结果的索引，即索引中包含查询语句中需要的所有字段。这样，查询时就可以直接从索引中获取所需数据，避免回表。
  2. 优化索引设计：合理设计索引，包括选择合适的索引列、创建联合索引等，以减少回表次数。
  3. 避免查询非索引列：在WHERE子句中尽量避免使用非索引列进行条件过滤，以减少回表的可能性。
  4. 利用索引的聚簇特性：InnoDB的主键索引是聚簇索引，它包含了完整的数据行。如果查询条件能够使用到主键或聚簇索引的列，就可以直接从聚簇索引中获取数据，避免回表。
五、回表的示例

假设有一张用户表user，包含id（主键）、username（二级索引）、age等字段。现在执行以下查询：

SELECT * FROM user WHERE username = 'bobo';

这个查询会首先通过username这个二级索引找到bobo记录和对应的主键id，然后再通过主键id去主键索引中找到完整的数据行。这个过程就是回表。

如果为了避免回表，可以创建一个覆盖索引，即包含查询所需所有列的索引：

CREATE INDEX idx_username_age ON user(username, age);

然后执行以下查询：

SELECT username, age FROM user WHERE username = 'bobo';

这个查询就可以直接从idx_username_age这个覆盖索引中获取所需数据，避免回表。

综上所述，回表是MySQL查询中的一个重要概念，了解回表的原理和影响，以及如何避免回表，对于优化数据库查询性能具有重要意义。

InnoDB 的二级索引是不是相当于 MyISAM 的主键索引

InnoDB的二级索引与MyISAM的主键索引在结构和功能上并不等同，它们之间存在显著的差异。

首先，InnoDB的二级索引（也称为辅助索引）的叶子节点存储的是对应行的主键值，而不是直接存储行数据。这意味着，当通过二级索引查找数据时，需要先找到对应的主键值，然后再通过主键值去聚集索引中查找实际的数据行。这种设计使得二级索引的查询效率略低于主键索引，因为需要额外的回表操作。

而MyISAM的主键索引则是一种非聚集索引，其叶子节点包含索引值和一个指向数据行记录的指针（或地址）。在MyISAM表中，即使查询使用了主键索引，也需要通过索引指针回表查询完整的数据行。然而，MyISAM的主键索引与普通索引在结构上并没有本质的差异，它们都是非聚集索引，只是主键索引的索引值是唯一的。

因此，InnoDB的二级索引与MyISAM的主键索引在以下方面存在显著差异：

  1. 存储结构：InnoDB的二级索引存储的是主键值，而MyISAM的主键索引存储的是指向数据行的指针。
  2. 查询效率：InnoDB的二级索引需要额外的回表操作来查找数据行，而MyISAM的主键索引则直接通过指针访问数据行（尽管也需要回表，但这里的回表是指通过指针找到数据行的过程，与InnoDB的回表操作在本质上不同）。
  3. 索引类型：InnoDB的二级索引是非聚集索引，而MyISAM的主键索引虽然也是非聚集索引，但它在MyISAM表中具有特殊的地位，因为MyISAM表只能有一个主键索引。

综上所述，InnoDB的二级索引并不相当于MyISAM的主键索引。它们在存储结构、查询效率和索引类型等方面都存在显著的差异。

show profile cpu,block io for query 2 命令分析

SHOW PROFILE CPU, BLOCK IO FOR QUERY <query_id> 是 MySQL 中用于分析查询性能的一个命令。它提供了关于指定查询的 CPU 和块 I/O（输入/输出）使用的详细信息。这个命令特别有用，因为它可以帮助你识别性能瓶颈，比如是否是由于 CPU 密集型操作还是由于磁盘 I/O 等待造成的。

命令结构
  • SHOW PROFILE: 这是用于显示 MySQL 查询性能分析结果的命令。
  • CPU, BLOCK IO: 这些是过滤条件，指定要显示的性能指标。你可以根据需要选择其他指标，比如 CONTEXT SWITCHES, PAGE FAULTS, SWAPS, SOURCE, EVENTS 等。
  • FOR QUERY <query_id>: 这指定了要分析的查询的 ID。每个查询在执行时，MySQL 都会为其分配一个唯一的 ID。
如何使用

  1. 执行查询并记录查询 ID：
    首先，你需要执行你想要分析的查询。在 MySQL 命令行客户端或任何 MySQL 客户端工具中，你可以使用 SHOW VARIABLES LIKE 'profiling'; 来检查是否启用了 profiling（应该设置为 ON）。如果未启用，你可以使用 SET profiling = 1; 来启用它。

    执行查询后，使用 SHOW PROFILES; 来查看最近执行的查询及其 ID。

  2. 分析查询性能：
    使用查询 ID，执行 SHOW PROFILE CPU, BLOCK IO FOR QUERY <query_id>;。这将显示该查询的 CPU 和块 I/O 使用情况。

输出解读

输出结果将包含以下几个部分：

  • Status: 描述了 MySQL 执行过程中的各个阶段。
  • Duration: 每个阶段消耗的时间（以秒为单位）。
  • CPU_user: 用户态 CPU 时间。
  • CPU_system: 内核态 CPU 时间。
  • Block_ops_in: 从磁盘读取的块数。
  • Block_ops_out: 写入磁盘的块数。
示例

假设你有一个查询 ID 为 10 的查询，你可以执行以下命令：

SHOW PROFILE CPU, BLOCK IO FOR QUERY 10;

输出可能类似于：

+--------------------+----------+------------+------------+--------------+---------------+
| Status             | Duration | CPU_user   | CPU_system | Block_ops_in | Block_ops_out |
+--------------------+----------+------------+------------+--------------+---------------+
| starting           | 0.000034 | 0.000010   | 0.000005   |            0 |             0 |
| checking permissions | 0.000005 | 0.000002   | 0.000001   |            0 |             0 |
| Opening tables     | 0.000020 | 0.000006   | 0.000004   |            0 |             0 |
| System lock        | 0.000005 | 0.000001   | 0.000001   |            0 |             0 |
| init                 | 0.000015 | 0.000005   | 0.000003   |            0 |             0 |
| optimizing           | 0.000005 | 0.000002   | 0.000001   |            0 |             0 |
| executing            | 0.000120 | 0.000050   | 0.000030   |            8 |             0 |
| sending data         | 0.000250 | 0.000100   | 0.000050   |            0 |             4 |
| end                  | 0.000005 | 0.000002   | 0.000001   |            0 |             0 |
| query end            | 0.000005 | 0.000001   | 0.000001   |            0 |             0 |
| freeing items        | 0.000010 | 0.000003   | 0.000002   |            0 |             0 |
| cleaning up          | 0.000005 | 0.000001   | 0.000001   |            0 |             0 |
+--------------------+----------+------------+------------+--------------+---------------+
分析建议
  • CPU 使用：查看 CPU_user 和 CPU_system 列，找出 CPU 使用较高的阶段。
  • 块 I/O：检查 Block_ops_in 和 Block_ops_out 列，识别磁盘 I/O 较高的阶段。
  • 优化建议：根据分析结果，考虑是否可以通过添加索引、优化查询逻辑、调整服务器配置等方式来减少 CPU 和磁盘 I/O 的使用。

通过 SHOW PROFILE 命令，你可以获得关于查询性能的深入见解，从而更有效地优化你的 MySQL 数据库。


http://www.kler.cn/a/371284.html

相关文章：

  • 18 Docker容器集群网络架构：一、etcd 概述
  • 群控系统服务端开发模式-应用开发-业务架构逻辑开发BaseAPI继续开发二
  • centos7 zabbix监控nginx的pv和uv和status_code
  • cesium 加载本地json、GeoJson数据
  • NSSCTF刷题篇web部分
  • apisix的原理及作用,跟spring cloud gateway有什么区别?
  • golang版本工具GVM 和包管理工具go mod原理讲解
  • 17 Docker容器存储架构：docker存储持久化-bind mount
  • 计算机视觉-Harris特征点检测实验报告
  • c++11新语法（上）
  • Python bs4 结合 Scrapy，进行数据爬取和处理
  • 利用游戏引擎的优势
  • windows 驱动实例分析系列: NDIS 6.0的Filter 驱动改造(四)
  • Educational Codeforces Round 171 (Rated for Div. 2)（A~D题题解）
  • ChatGPT、Python和OpenCV支持下的空天地遥感数据识别与计算——从0基础到15个案例实战
  • Ubuntu22.04环境搭建MQTT服务器
  • 【Spring框架】Spring框架的开发方式
  • 短视频矩阵系统源代码开发|技术源代码部署/OEM贴牌搭建
  • electron知识整理和问题汇总
  • Data+AI时代下，如何做数字化转型升级！
  • 【MySQL】 运维篇—备份与恢复：使用mysqldump进行数据库备份与恢复
  • 开源一款前后端分离的企业级网站内容管理系统，支持站群管理、多平台静态化，多语言、全文检索的源码
  • IDEA连接EXPRESS版本的SQL server数据库
  • QT交互界面：实现按钮运行脚本程序
  • conda、virtualenv, venv分别是什么？它们之间有什么区别？
  • （青牛科技）双通道H桥电机驱动芯片GC8548 12V双通道全桥驱动芯片GC8548兼容LV8548