目录
索引是一种数据结构，使用索引可以提高select查询的性能。索引也是存储在文件中的，对索引的使用也会涉及到磁盘IO的使用。
索引的优缺点
- 优点：提升SQL查询的效率
- 缺点：索引也是存储在文件中的，索引并不是越多越好，过多的索引会导致过多的磁盘IO，导致CPU使用率居高。
索引的分类
- 根据物理实现上划分为：聚集索引和非聚集索引
- 聚集索引：数据和索引存储在一颗索引树上，即叶子节点包含了完整的数据记录。例如对于使用InnoDB存储引擎创建的表，假设包含一个主键索引，则其索引底层使用的是B+树结构，B+树的叶子节点存放着索引和该索引所在的那条记录中除索引外的数据。因此这种索引就是聚集索引。对于InnoDB来说，主键索引是聚集索引，辅助索引或者说二级索引（非主键字段创建的索引）是非聚集索引，因为辅助索引中，其B+树的叶子节点存储着辅助索引信息以及主键索引的值。
- 非聚集索引：数据和索引不存储在一起，即叶子节点不包含完整的数据记录。例如对于使用MyISAM存储引擎创建的表，假设包含一个主键索引，则其索引底层使用的是B+树结构，B+树的叶子节点存放着索引和指向表中索引值所在记录的地址（在MyISAM中索引文件和数据文件是分离的，这个在MySQL之存储引擎有讲）。这种索引就是非聚集索引。对于MyISAM来说，主键索引和二级索引都是非聚集索引。
- 逻辑上划分：
- 普通索引：任何类型的字段都可以创建普通索引，但是一张表的一次SQL查询只能使用一个索引。
- 唯一性索引：使用unique约束的字段就会创建唯一性索引，索引名称默认为字段名
- 主键索引：使用primary key约束的字段就会创建主键索引，索引名称默认为primary。主键索引也是一种唯一性索引
- 全文索引：使用fulltext关键字可以设置全文索引，目前只有MyISAM存储引擎支持全文索引，并且只限于char、varchar、text类型的字段。
- 根据使用字段的个数：单列索引和多列索引
- 单列索引：在一个字段上创建索引
- 多列索引（联合索引或者叫做复合索引）：在表的多个字段上创建索引。对于多列索引，编写SQL时根据最左匹配原则，需要使用到第一个字段，查询才会使用到多列索引。
索引的操作
- 建表时创建索引：
- 创建主键索引或者唯一性索引
# 创建主键索引和唯一性索引 CREATE TABLE `t_user` ( `uid` int unsigned NOT NULL AUTO_INCREMENT, `nickname` varchar(32) DEFAULT NULL, `age` bigint DEFAULT NULL, `gender` enum('male','female') NOT NULL, PRIMARY KEY (`uid`), UNIQUE KEY `nickname` (`nickname`) ) ENGINE=InnoDB AUTO_INCREMENT=2000262 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci- MySQL中使用key或者index关键字定义普通索引，如下定义普通索引name
CREATE TABLE `t_user` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(32) NOT NULL, `sex` enum('male','female') DEFAULT NULL, PRIMARY KEY (`id`), KEY `name` (`name`) ) ENGINE=InnoDB - 为存在的表创建索引：
# 创建一个索引名称叫做index_age的普通索引
create index index_age on t_user(age);
# 创建一个索引名称叫做index_name的唯一性索引
create unique index index_name on t_user(nickname);
- 索引的删除：
drop index 索引名称 on 表名称 - 查看表中的索引：
show indexes from 表名
索引的底层实现原理
索引的实现有哈希表、B树、B+树。如下内容参考王道考研辅导书。
1.B树
- B树（B-树）是一种平衡的多路查找树。
- B树的阶：B树中所有结点的孩子个数的最大值。
- 一颗m阶B树或者为空树或者为满足如下特性的m叉树：
- 树中每个节点至多有m颗子树，即至多有m - 1个关键字。
- 如果根节点不是叶子结点，则至少有两颗子树
- 除了根节点外的所有非叶子结点至少有
$\lceil m/2 \rceil$颗子树，即至少有$\lceil m/2 \rceil$- 1个关键字 - 所有的叶子节点出现在同一层次上，因此B-树的所有结点的平衡因子为0。
- 结点的孩子个数等于该结点中关键字个数加1。
- 一颗5阶B-树如下所示：
- B树的高度：通常会对B树进行调整，使得B树每个结点的元素刚好与硬盘存储的页面大小或者说磁盘块相匹配。因此B树的高度即为磁盘IO的次数。这也是在内外存交互的应用中引入B树的原因，相比于AVL、红黑树，在数据量很大的情况下，B树可以大大减少磁盘IO的次数。
2.B+树
- B+树是B树的变形树
- 一颗m阶的B+树的特点如下：
- 每个分支结点最多有m颗子树
- 非叶根结点至少有两颗子树，其他每个分支节点至少有
$\lceil m/2 \rceil$颗子树 - 结点的子树个数与关键字个数相等
- 所有叶结点包含全部关键字及指向相应记录的指针，叶子结点按照关键字大小顺序排列，并且相邻叶子节点按照大小顺序相互链接起来
- 所有分支节点中仅仅包含它的各个子节点中关键字的最大值及指向子节点的指针。
- 通常在B+树中有两个头指针：一个指向根节点，方便进行多路查找；一个指向关键字最小的叶子节点，方便进行顺序查找。两者结合则方便进行范围查找。
- 一颗四阶B+树如下所示：
- B+树和B树在结构上的不同：
3.MySQL中的索引
- 使用InnoDB或者MyISAM存储引擎时，MySQL使用B+树来构建索引，每一个非叶子节点只存放键，不存放键所在的那一条除键以外的记录。这样的话每一个非叶子节点存放的key的个数更多。B+树在理论上层数比B树更低，磁盘IO的次数更低，搜索的效率更高。
- 叶子节点存放所有的索引值和索引值所在的记录，搜索每一个索引对应的记录，都会搜索到叶子节点上，这样每一条记录的查询时间是非常平均的。
- 所有叶子节点由链表组织，头指针指向第一个叶子节点。如果需要进行整表遍历，则直接遍历这个链表即可，时间复杂度为O(n)。
主键索引和二级索引（辅助索引）
1.简介
- 主键索引：使用主键字段创建的索引
- 二级索引：使用非主键字段创建的索引
2.InnoDB存储引擎
- InnoDB存储引擎中，主键索引树中的叶子节点中，索引关键字的值和数据存放在一起
- InnoDB存储引擎中，辅助索引树中的叶子节点中，存放着索引关键字和索引关键字所在记录对应的主键。
3.InnoDB中的自适应哈希索引
- 回表问题：在辅助索引的B+树中，会先根据辅助索引关键字查找对应的主键，再去主键索引树上查找对应的行记录。
- 回表示例：
# 表结构如下，其中uid为主键索引，nickname为辅助索引
CREATE TABLE `t_user` (
`uid` int unsigned NOT NULL AUTO_INCREMENT,
`nickname` varchar(32) DEFAULT NULL,
`age` bigint DEFAULT NULL,
`gender` enum('male','female') NOT NULL,
PRIMARY KEY (`uid`),
UNIQUE KEY `nickname` (`nickname`)
) ENGINE=InnoDB AUTO_INCREMENT=2000265 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
# 不会回表
mysql> explain select nickname, uid from t_user where nickname="张三";
+----+-------------+--------+------------+-------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+----------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | t_user | NULL | const | nickname | nickname | 131 | const | 1 | 100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+----------+---------+-------+------+----------+-------------+
# 回表查询
# 1.先搜索nickname的二级索引树，查询到张三对应的主键uid
# 2.再根据uid在主键索引树上搜索uid那一行的记录
mysql> explain select nickname, uid, age from t_user where nickname="张三";
+----+-------------+--------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t_user | NULL | const | nickname | nickname | 131 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
- 自适应哈希索引：用于缓解回表效率低的问题。InnoDB存储引擎检测到同样的二级索引不断被使用，那么会根据这个二级索引在内存上根据二级索引树上的二级索引值，在内存中构建一个哈希索引来加速搜索。
- 查看InnoDB存储引擎是否开启自适应哈希索引
mysql> show variables like 'innodb_adaptive_hash_index'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | innodb_adaptive_hash_index | ON | +----------------------------+-------+
4.MyISAM存储引擎
- 在MyISAM存储引擎中，主键索引树和二级索引树上的叶子节点存储着索引关键字和数据的地址，主键索引树和二级索引树的叶子节点存储的数据的地址指向同一个地方。在磁盘上就是索引存放在后缀名为MYI的文件中，数据存放在后缀名为MYD的文件中。因此不会涉及到回表的问题
哈希索引
- memory存储引擎内部使用的是哈希表来构建索引
SQL的执行过程
- 以下列t_user表为例：t_user表中包含2000000条记录。
# 包含主键索引，唯一键索引
CREATE TABLE `t_user` (
`uid` int unsigned NOT NULL AUTO_INCREMENT,
`nickname` varchar(32) DEFAULT NULL,
`age` bigint DEFAULT NULL,
`gender` enum('male','female') NOT NULL,
PRIMARY KEY (`uid`),
UNIQUE KEY `nickname` (`nickname`)
) ENGINE=InnoDB AUTO_INCREMENT=2000262 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
- 可以使用explain查看SQL的执行计划，分析索引的执行过程，语法为
explain SQL语句。示例：explain展示结果的各个字段如3所示
mysql> explain select uid, nickname from t_user where nickname = '张三';
+----+-------------+--------+------------+-------+---------------+------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | t_user | NULL | const | index_name | index_name | 131 | const | 1 | 100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
- explain执行后返回的信息包含id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra这些字段。这些字段的意义如下所示：
- id：select查询的序列号，表示查询中操作表的顺序
- select_type：
- table
- partitions
- type
- all：表示全表扫描
- index：索引全表扫描
- const：使用唯一索引或者主键索引
- possible_keys
- key
- ken_len
- ref
- rows
- filtered
- Extra：其他额外的重要的信息。可选值如下
- Using filesort：表示使用外部排序，未使用到索引
- using index：查询时通过索引就可以查询到需要的数据
问题
- 索引的分类有哪些？
- 按照逻辑上划分普通索引、唯一性索引、主键索引、全文索引等。
- 按照物理上划分有聚簇索引和非聚簇索引
- 按照字段的个数划分有单列索引和联合索引
- 索引失效的场景有哪些？列举一下
- 创建了索引的字段涉及到类型强转，则不能使用到索引
- 创建了索引的字段使用了MySQL中的函数，则不能使用索引
- 创建了索引的字段涉及到表达式计算，则不能使用索引
- 如果查询的记录数与整表搜索的记录数大差不差，则会进行整表搜索。
- 索引的存储结构为什么不使用AVL树或者红黑树，而是使用B+树？因为使用B+树可以大大减少磁盘IO的次数
- 索引的存储结构为什么不使用B树，而是使用B+树？
- 因为B+树的查询效率更加稳定。B树中索引和数据分散在不同的节点上，离根节点近，搜索就快；离根节点远，搜索就慢。
- B+树空间利用率更高，可以减少次磁盘IO次数。B树中的每一个非叶子节点不仅仅要存储索引值还要存储索引值所在的那一条记录，一个节点所能存放的索引值的个数比B+树中的节点要少。
- B+树同时支持随机检索和整表遍历，范围查询，而B树只适合随机检索，整表遍历以及范围查询不方便。
- B+树的阶多少合适？当一次磁盘IO读取的磁盘块的内容，刚好存储在B树的一个节点中。
- InnoDB存储引擎为什么不使用哈希这种数据结构构建索引？
- 使用哈希构建索引，只能进行等值查询，而对于范围查询以及通配符匹配查询、排序查询等需要整表遍历。
- 项目中使用了索引优化，怎么做的？
- 在开发环境下打开慢查询日志，设置合理的慢查询时间，这样的话耗时的SQL会记录到慢查询日志文件中。
- 压测执行各种接口。查看慢查询日志记录中那些超过预定义的SQL执行时间的SQL
- 使用explain查看这些SQL的执行计划，确定SQL是否如预期一样使用到了索引。
