精通MySQL 8（视频教学版）
上QQ阅读APP看书，第一时间看更新
上一章目录下一章

6.3　MySQL 8中索引新特性

MySQL 8开始支持隐藏索引和降序索引。隐藏索引提供了更人性化的数据库操作，降序索引则提高了数据库的性能。

6.3.1　隐藏索引

隐藏索引，顾名思义，不可见索引，不会被优化器使用。默认情况下索引是可见的。隐藏索引可以用来测试索引的性能。验证索引的必要性时不需要删除索引，可以先将索引隐藏，如果优化器性能无影响就可以真正地删除索引。

1. 创建表时直接创建

在MySQL中创建隐藏索引通过SQL语句INVISIBLE来实现，其语法形式如下：

    CREATE TABLE tablename(
    propname1 type1[CONSTRAINT1],
    propname2 type2[CONSTRAINT2],
    ……
    propnamen typen,
    INDEX [indexname](propname1 [(length)]) INVISIBLE
    );

上述语句比普通索引多了一个关键字INVISIBLE，用来标记索引为不可见索引。

【示例6-5】在数据库school中，在表t_class的cname字段创建隐藏索引，具体步骤如下：

（1）在创建班级表t_class时，在字段cname上创建隐藏索引，具体SQL语句如下，执行结果如图6-12所示。

    CREATE TABLE t_class(
    classno INT(4),
    cname VARCHAR(20),
    loc VARCHAR(40),
    INDEX index_cname(cname) INVISIBLE);

（2）为了检验班级表t_class中多列索引是否创建成功，执行SQL语句SHOW CREATE TABLE，具体SQL语句如下，执行结果如图6-13所示。

    SHOW CREATE TABLE t_class \G

图6-12　创建表t_class

图6-13　查看班级表信息

2. 在已经存在的表上创建

在MySQL创建隐藏索引，除了可以在创建表时实现外，还可以为已经存在的表设置隐藏索引，其语法形式如下：

    CREATE INDEX indexname
    ON tablename(propname[(length)]) INVISIBLE;

在示例6-5中，如果表已存在，可通过如下语句创建多列索引：

    CREATE INDEX index_cname ON t_class(cname) INVISIBLE;
3. 通过ALTER TABLE语句创建

在MySQL创建隐藏索引，除了可以在创建表时实现外，还可以为已经存在的表设置隐藏索引，其语法形式如下：

    ALTER TABLE tablename
    ADD INDEX indexname (propname [(length)]) INVISIBLE;

在示例6-5中，如果表已存在，可通过如下语句创建多列索引：

    ALTER TABLE t_class ADD INDEX index_cname(cname) INVISIBLE;
4. 切换索引可见状态

已存在的索引可通过如下语句切换可见状态：

    ALTER TABLE tablename ALTER INDEX  index_name INVISIBLE;
    ALTER TABLE tablename ALTER INDEX  index_name VISIBLE;

6.3.2　降序索引

降序索引以降序存储键值。虽然在语法上，从MySQL 4起就支持DESC，但实际上该DESC定义是被忽略的，MySQL在此之前创建的仍然是升序索引，使用时进行反向扫描，这大大降低了数据库的效率。在某些场景下，降序索引意义重大。例如，如果一个查询，需要对多个列进行排序，且顺序要求不一致，那么使用降序索引将会避免数据库使用额外的文件排序操作，从而提高性能。

在MySQL中创建降序索引的SQL语句与创建多列索引的语法相同。

【示例6-6】在t_class表中创建降序索引，实现classno升序排列，cname降序排列。

（1）创建降序索引的SQL语句如下所示，执行结果如图6-14所示。

图6-14　创建表t_class

（2）使用如下语句检查SELECT语句发现没有使用filesort文件排序，而是使用预先创建的索引，如图6-15所示。

图6-15　查看查询语句

上一章目录下一章