详解MySQL InnoDB的索引扩展

MySQL InnoDB索引扩展详解

InnoDB是MySQL中,一个常用的事务性存储引擎,也是MySQL5.5版本以后默认的存储引擎。InnoDB对于数据的存储以及索引都有自己的特定优化策略,本文主要探讨InnoDB在索引扩展上的优化方式。

InnoDB的索引类型

InnoDB支持B-Tree索引(默认)、全文索引,以及R-Tree索引和哈希索引。

B-Tree(默认)

B-Tree是InnoDB默认的索引类型。B-Tree索引又分为聚集索引和辅助索引。聚集索引的叶子节点存储的是整个表中的行数据,而辅助索引的叶子节点仅仅存储索引值和对应的主键值。

B-Tree索引适用于离散数据类型,比如数字和字符串,但是不适用于范围查询和全文搜索。

R-Tree

R-Tree索引适用于GIS和空间数据类型,通过R-Tree,将空间数据转化为平面上的多维点来处理。

哈希索引

哈希索引主要用于等值查询,一旦查询条件不为等值查询,哈希索引就无法发挥作用。

全文索引

全文索引适用于对文本类型的字段进行搜索,InnoDB默认沿用了MySQL的MyISAM存储引擎的全文索引。

InnoDB索引扩展

InnoDB在B-Tree索引的基础之上,对于索引的扩展,引入了自适应哈希索引(ADI)以及预计数器(PSA)

自适应哈希索引

自适应哈希索引是InnoDB在运行期间动态创建的哈希索引,用于加速等值查询。当某个表上同一个等值查询的查询次数超过了一个阈值,InnoDB会自动创建基于该列的哈希索引。

例如,下面的users表格,可以使用ALTER TABLE命令,在age列上创建哈希索引:

ALTER TABLE users ADD INDEX HASH(age);

为了查看哈希索引是否生效,执行以下查询语句:

EXPLAIN SELECT * FROM users WHERE age = 31;

如果返回结果中有Using index或者Using index condition,则说明已经使用了哈希索引。

预计数器

InnoDB引入了预计数器,用于优化查找、扫描以及缓存的阈值计算。预计数器以3个值为基础:

  • count:上一次模糊计数结果
  • expected:上一次扫描的期望值
  • variation:上一次计数与期望值相差的值

基于上面的值,InnoDB对于下一次查询会调整计算方式的阈值,从而优化性能。

示例

使用employees数据库中的salaries表作为示例,其中包含员工工资的数据,并且在salary列上创建索引。

自适应哈希索引示例

假设我们需要查询薪水为10001的员工信息,代码如下:

EXPLAIN SELECT * FROM salaries WHERE salary = 10001;

可以发现,该查询语句使用的是B-Tree索引。

接下来,我们删除索引并且开启ADI:

ALTER TABLE salaries DROP INDEX salary_idx;
SET GLOBAL innodb_adaptive_hash_index = ON;
ALTER TABLE salaries ADD INDEX salary_idx (salary);

再次执行查询语句:

EXPLAIN SELECT * FROM salaries WHERE salary = 10001;

如果返回结果中有Using index或者Using index condition,则说明已经使用了哈希索引。

注意:当哈希索引的查询次数过少或者哈希索引的大小超过了一定的阈值,则哈希索引会自动禁用或者降低优先级。

预计数器示例

假设我们需要查询工资高于50000美元的员工的薪水:

EXPLAIN SELECT * FROM salaries WHERE salary > 50000;

可以发现,该查询语句使用的是全表扫描,其性能较差。

通过设置预计数器的方式,可以提高性能:

SET GLOBAL innodb_stats_sample_pages = 100;

修改之后再次执行查询语句:

EXPLAIN SELECT * FROM salaries WHERE salary > 50000;

在返回结果中,我们可以发现,其查询类型为范围查找(range),基于索引的扫描(Using index condition)。

总结

通过使用自适应哈希索引和预计数器,可以提高InnoDB索引的性能,减少全表扫描和哈希表未命中的情况,是一个有效的索引优化方式。

本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:详解MySQL InnoDB的索引扩展 - Python技术站

(0)
上一篇 2023年5月19日
下一篇 2023年5月19日

相关文章

  • MySQL细数发生索引失效的情况

    MySQL细数发生索引失效的情况 前言 在MySQL中,为了加速查询操作,我们通常会通过创建索引来提高查询效率。但是,如果我们不小心创建索引或者索引过期、被删除等情况时,会导致索引失效,查询效率降低,甚至直接影响业务运行。如何防止索引失效?需要从什么方面入手呢?本文将详细讲解MySQL中的索引失效原因和解决方案。 为什么会发生索引失效? 1. 不到万不得已就…

    MySQL 2023年5月19日
    00
  • mysql利用覆盖索引避免回表优化查询

    MySQL中的覆盖索引是指当我们查询的结果可以直接从索引中获取,而不需要再去查询数据表中其他的列时,就可以利用覆盖索引来避免回表操作,从而优化查询操作的效率,提升整个系统的性能。 以下是基本的步骤: 创建合适的索引:通过EXPLAIN命令分析查询语句,检查是否使用了索引,如果没有,则需要创建合适的索引。 包含所有必需列的索引:确保创建的索引包含所有SELEC…

    MySQL 2023年5月19日
    00
  • Mysql中key 、primary key 、unique key 与index区别

    key 是数据库的物理结构,它包含两层意义和作用, 一是约束(偏重于约束和规范数据库的结构完整性), 二是索引(辅助查询用的)。   https://www.cnblogs.com/zjfjava/p/6922494.html   CREATE TABLE `act_ru_execution` ( `ID_` varchar(64) COLLATE utf8…

    MySQL 2023年4月16日
    00
  • MySQL5.7主从复制教程

    ​ 简述:主从复制,是用来建立一个和主数据库完全一样的数据库环境,称为从数据库,主数据库一般是准实时的 业务数据库、事务处理库,从库做查询库。 ​ 复制过程简单的说就是 master 将数据库的改变写入二进制日志,slave同步这些二进制日志,并根据这些二进制日志行数据操作 1、什么是主从复制 ​ 主从复制,是用来建立一个和主数据库完全一样的数据库环境,称为…

    MySQL 2023年4月11日
    00
  • mysql 8.0.17 winx64(附加navicat)手动配置版安装教程图解

    MySQL 8.0.17 winx64(附加Navicat)手动配置版安装教程图解 下载MySQL和Navicat 首先,我们需要从官网下载MySQL 8.0.17的安装包。在下载时,需要注意选择对应的操作系统版本,即Windows 64位。 与此同时,我们还需要下载Navicat软件,这是一款GUI数据库管理工具,可以帮助我们更方便地管理MySQL数据库。…

    MySQL 2023年5月18日
    00
  • MySQL密码正确却无法本地登录的解决方法

    请看下面的完整攻略。 问题描述 在使用MySQL数据库时,我们有时会遇到一个问题:输入正确的密码后,无法在本地登录。这种情况可能出现在新安装MySQL时,或者更新系统后,等等。那么,应该如何解决这个问题呢? 解决方法 1. 检查MySQL是否启动 首先,我们需要检查MySQL是否已经启动。要查看系统上是否正在运行MySQL,请使用以下命令: sudo sys…

    MySQL 2023年5月18日
    00
  • mysql服务启动不了解决方案

    这里提供一份基于Ubuntu 18.04操作系统的MySQL服务无法启动的解决方案攻略,可以尝试按照以下步骤解决问题。 1. 检查MySQL服务是否正在运行 首先,我们需要检查MySQL服务是否正在运行。可以通过运行以下命令来检查它: sudo systemctl status mysql 如果看到以下输出,说明MySQL正在运行: ● mysql.serv…

    MySQL 2023年5月18日
    00
  • Java 程序员容易犯的10个SQL错误

    Java 程序员容易犯的10个SQL错误攻略 1. 没有使用参数化查询 使用参数化查询可以防止 SQL 注入攻击。SQL 注入攻击是指攻击者在数据库查询中注入恶意的 SQL 语句,从而获取非法的数据库权限或数据。因此,在编写 SQL 查询时应该使用参数化查询来避免此类攻击。 示例: String name = "John"; String…

    MySQL 2023年5月18日
    00
合作推广
合作推广
分享本页
返回顶部