详解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索引的性能，减少全表扫描和哈希表未命中的情况，是一个有效的索引优化方式。