MySQL 8中新增的这三大索引 隐藏、降序、函数

MySQL 8中新增了许多索引的功能，其中比较常用且值得重点学习的有三个：隐藏索引、降序索引、函数索引。

隐藏索引

隐藏索引实际上指的是覆盖索引的一种，即只包含列的索引，不存储对应的行数据。这种索引可以减少不必要的I/O读写操作，从而提高查询效率。

以下是一个简单的示例，假设有一个表students，其中有sid、name、age、gender四个字段，我们需要查询age为18岁的所有学生姓名：

SELECT name FROM students WHERE age = 18;

如果在age列上创建索引，MySQL会使用B-Tree算法搜索索引，然后根据索引中的主键去回表查询，这时会把name列作为回表查询的列。由于name列并没有索引，因此需要进行全表扫描，效率较低。

此时，我们可以创建一个只包含age和name两个列的联合索引，避免直接扫描name列：

ALTER TABLE students ADD INDEX idx_age_name(age, name);

查询语句改成以下就可以避免全表扫描了：

SELECT name FROM students WHERE age = 18;

由于该索引只包含age和name两个字段，因此不需要回表查询，可以直接从索引中取出name的值，因此该查询操作速度会有所提升。

降序索引

MySQL中的B-Tree索引默认是升序排序的，因此在使用ORDER BY子句进行排序时，如果要使用索引，则需要在索引上进行一个反转操作，变成降序排列，以便更好地优化查询性能。

以下是一个简单示例，假设有一个表grades，其中有sid、cid、score三个字段，我们需要查询某门课程成绩最高的学生：

SELECT * FROM grades WHERE cid = 'math' ORDER BY score DESC LIMIT 1;

如果创建了一个包含(cid, score)的联合索引，MySQL会使用索引键顺序在索引树上搜索，得到符合条件的记录，并按照score进行排序，但排序时需要频繁地读取和反转score值，会导致额外的性能损耗。

因此，我们可以创建一个降序索引，避免排序操作时的性能问题：

ALTER TABLE grades ADD INDEX idx_cid_score(cid, score DESC);

这样，在执行SELECT语句时，MySQL会先搜索索引，找出符合cid条件的记录，并按照score的降序排列，然后直接读取对应的行数据，无需再执行排序操作，从而提高查询的性能。

函数索引

MySQL可以支持在索引中使用函数，这个功能可以帮助我们更好地优化查询。例如，可以使用字符串函数，对索引列的值进行相应的处理，然后再使用索引。

以下是一个简单的示例，假设有一个表items，其中有id、title、content三个字段，我们需要查询包含“mysql”关键词的记录：

SELECT * FROM items WHERE MATCH(title, content) AGAINST('mysql');

如果我们在title和content字段上分别创建索引，MySQL会使用内建的全文索引引擎InnoDB进行检索，但如果直接在函数中使用AGAINST函数，则无法使用这些索引，会导致全表扫描和更慢的查询速度。

因此，我们可以创建一个函数索引，让MySQL在检索时能够使用整个索引，提高查询效果：

CREATE FULLTEXT INDEX idx_items_ft ON items (CONCAT(title, ' ', content));

这样，在执行查询操作时，MySQL会自动使用该函数创建的索引，进行全文检索操作，避免了全表扫描和更慢的查询速度。

以上就是MySQL 8中新增的这三大索引的完整攻略，其中包含了隐藏索引、降序索引和函数索引等内容，同时也给出了两个操作示例，希望能够对大家的查询操作提供帮助。