MySQL调优学习笔记(三)：组合索引和函数索引

目录

什么是组合索引

组合索引的优势

什么是函数索引

函数索引的优势

总结

参考资料：姜承尧的MySQL实战宝典

什么是组合索引

组合索引是指由多个列所组合而成的B+树索引，组合索引既可以是主键索引，也可以是二级索引。
组合索引(a,b)和组合索引(b,a)排序结果是完全不一样的。对组合索引(a,b)来说，它可以对以下几个查询进行优化。

SELECT * FROM table_name WHERE a = ?

SELECT * FROM table_name WHERE a = ? AND b = ?

SELECT * FROM table_name WHERE b = ? AND a = ?

SELECT * FROM table_name WHERE a = ? ORDER BY b DESC

然而组合索引(a,b)无法对以下几个查询进行优化，因为(a,b)排序并不能推出(b,a)排序。

SELECT * FROM table_name WHERE b = ?

SELECT * FROM table_name WHERE b = ？ ORDER BY a DESC

组合索引的优势

1. 覆盖多个条件
组合索引是针对多个列的索引，可以提升多列查询的性能。

2. 避免额外排序
设计组合索引(a,b)可以避免额外排序，提升WHERE a = ? ORDER BY b 的查询性能。

3. 避免回表
由于二级索引的叶子节点，包含索引键值和主键值，若查询的字段在二级索引的叶子节点中，则可直接返回结果，无需回表，提升性能。这种通过组合索引避免回表的优化技术也称为索引覆盖。

什么是函数索引

从 MySQL 5.7 版本开始，就开始支持创建函数索引，即索引键是一个函数表达式。

函数索引的优势

1. 优化SQL查询性能
索引 idx_register_date 只对 register_date排序，没有对DATE_FORMAT(register_date,'%Y-%m')排序，因此代码处第2条SQL无法使用二级索引idx_register_date。

索引idx_func_register_date对DATE_FORMAT(register_date,'%Y-%m')排序，因此代码处第4条SQL可以使用二级索引idx_func_register_date。

数据库规范要求查询条件中函数写在等式右边，而不能写在左边，也是这个原因。

#对register_date列创建索引idx_register_date
ALTER TABLE user 
ADD INDEX idx_register_date(register_date);

#查询时无法使用索引idx_register_date
SELECT * FROM user
WHERE DATE_FORMAT(register_date,'%Y-%m')='2020-01';

#对register_date列创建函数索引idx_func_register_date
ALTER TABLE user 
ADD INDEX idx_func_register_date((DATE_FORMAT(register_date,'%Y-%m')));

#查询时使用索引idx_func_register_date
SELECT * FROM user
WHERE DATE_FORMAT(register_date,'%Y-%m')='2020-01';

2. 配合虚拟列使用

列 cellphone 是一个虚拟列，它是由后面的函数表达式计算而成，不占用任何的存储空间，而索引 idx_cellphone 对虚拟列排序，实质上是一个函数索引。这样做的好处是在写 SQL 时都可以直接使用这个虚拟列，而不用写冗长的函数。

CREATE TABLE userLogin (
    userId BIGINT,
    loginInfo JSON,
    cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone"),
    PRIMARY KEY(userId),
    UNIQUE KEY idx_cellphone(cellphone)
);

#不使用虚拟列进行查询
SELECT * FROM userLogin
WHERE loginInfo->>"$.cellphone" = '11111111111'

#使用虚拟列进行查询
SELECT * FROM userLogin
WHERE cellphone = '11111111111'

总结

• 组合索引也是一颗B+树，只是索引由多个列组成，组合索引既可以是主键索引，也可以是二级索引；
• 组合索引的优势：覆盖多个查询条件；避免额外排序；可以实现索引覆盖技术，避免回表；
• 函数索引，即索引键是一个函数表达式；
• 函数索引的优势：有时可通过函数索引快速解决SQL的性能问题；在虚拟列上创建索引本质就是函数索引。