目录
什么是组合索引
组合索引的优势
什么是函数索引
函数索引的优势
总结
参考资料：姜承尧的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的性能问题；在虚拟列上创建索引本质就是函数索引。
