本文最后更新于：2022年7月14日 下午
概览：MySQL索引
索引
索引是一种帮助存储引擎快速搜索数据的数据结构
类似于新华字段的拼音和部首索引。
索引的作用
索引的分类
索引失效
对索引使用了左模糊匹配，就是like '%xx'
对索引使用了函数或者进行了表达式计算，mysql 8.0之后有了函数索引。
对索引使用了隐式转换类型，例如字段是字符型但是输入了整型
联合索引不是最左匹配的时候
where语句中的or前面是索引但是后面不是的时候，也不走索引
where子句使用了<> 或者 != 一般不走索引
not in或者not exist一般不走索引
部分范围查询不走索引
eg: select * from detail where expire_time < ‘2022-07-10’ && expire_time > ‘2022-05-10’
原因：这里要取的是整行信息，expire_time 索引无法取到对应的信息。
虽然expire_time在索引中顺序存放，但是对应的数据不一定按照顺序存放，回表查询的时候数据是无序的 – 变成了磁盘上的离散读操作。
如果当访问的数据占整个表的数据超过一定比例的时候，优化器会尝试通过聚集索引来查找数据， —— 顺序读远远快于离散读。
数据量太大导致的统计数据不真实或者表没分析 —— 不走索引
说明：数据库会定时对表进行分析，如果表过大导致分析计划没有及时跑完，或者由于其他原因导致统计数据不真实，这样会导致CBO计算走索引花费不准确的情况，可能会导致不走索引而使用全表扫描。这也是为什么当表的数据量达到一定级别的时候，我们建议进行分表分库，因为表数据量过大，可能导致表分析过程没有执行完成。
这个是真的吗？？？？？？
[索引失效的几种情况 - 彼岸-花已开 - 博客园 (cnblogs.com)](https://www.cnblogs.com/szw906689771/p/14719186.html#:~:text=说明：单次查询如果查出表的大部分数据，这会导致编译器认为全表扫描性能比走索引更好，从而导致索引失效。,一般单次查询数量大概占大表的30%以上索引会失效。 2.索引本身失效)
explain详解
1
2
3
4
5
6
| mysql> explain select * from actor;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | actor | ALL | NULL | NULL | NULL | NULL | 2 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|
- id：有几个select就有几个id，并且id顺序和查询顺序相同
- select_type：表示是简单查询还是复杂查询，简单是SIMPLE
- table：查询表名
- type：表示关联类型或者访问类型，这就是MySQL决定如何查找表中的行。
- **
NULL**：mysql在优化阶段分解查询语句，在执行阶段用不着访问表或者索引，eg：求索引列中的最小值。
- **
const, system**：mysql能对查询的某部分进行优化并将其转化成一个常量，用于 primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。
- eq_ref：primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录
- **
ref**：相比eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。
ref_or_null：类似ref，但是可以搜索值为NULL的行。index_merge：表示使用了索引合并的优化方法。 例如下表：id是主键，tenant_id是普通索引。or 的时候没有用 primary key，而是使用了 primary key(id) 和 tenant_id 索引- **
range**：范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。
- **
index**：和ALL一样，不同就是mysql只需扫描索引树，这通常比ALL快一些。
- ALL：即全表扫描，意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了
- possible_keys：可能使用哪些索引
- key：实际使用的索引
- key_len：mysql在索引里使用的字节数
- 字符串
- char(n)：n字节长度
- varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n + 2
- 数值类型
- tinyint：1字节
- smallint：2字节
- int：4字节
- bigint：8字节
- 时间类型
- date：3字节
- timestamp：4字节
- datetime：8字节
- 如果字段允许为 NULL，需要1字节记录是否为 NULL
- 索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引
- ref：这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），func，NULL，字段名（例：film.id）。
- rows：这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。
- Extra：展示额外信息
distinct: 一旦mysql找到了与行相联合匹配的行，就不再搜索了- **
Using index**：这发生在对表的请求列都是同一索引的部分的时候，返回的列数据只使用了索引中的信息，而没有再去访问表中的行记录。是性能高的表现。
- **
Using where**：mysql服务器将在存储引擎检索行后再进行过滤。就是先读取整行数据，再按 where 条件进行检查，符合就留下，不符合就丢弃。
- **
Using temporary**：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。
- **
Using filesort**：mysql 会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。这种情况下一般也是要考虑使用索引来优化的。
参考连接：mysql explain详解 - 腾讯云开发者社区-腾讯云 (tencent.com)
探究联合索引
一个案例：
1
2
3
4
5
6
7
8
9
| CREATE TABLE `test_abc` (
`id` int NOT NULL AUTO_INCREMENT,
`a` varchar(255) NOT NULL,
`b` varchar(255) NOT NULL,
`c` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_a_b_c` (`a`,`b`,`c`),
KEY `idx_a_c_b` (`a`,`c`,`b`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
表中有1k条数据，使用随机数插入得到的。
语句一：
1
2
3
| select *
from test_abc
where a= '1' and b = '2' and c='3'
|
毫无疑问使用了索引idx_a_b_c，key_len = 3066,ref中里三个const
1
2
3
4
5
| +----+-------------+----------+------------+------+---------------------+-----------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------------+-----------+---------+-------------------+------+----------+-------------+
| 1 | SIMPLE | test_abc | NULL | ref | idx_a_b_c,idx_a_c_b | idx_a_b_c | 3066 | const,const,const | 1 | 100.00 | Using index |
+----+-------------+----------+------------+------+---------------------+-----------+---------+-------------------+------+----------+-------------+
|
语句二：
1
2
3
| select *
from test_abc
where a= '1' and c='3' and b = '2';
|
还是使用了三个索引，并且key_len=3066，这里不知道为什么没有使用idx_a_c_b这个索引。
1
2
3
4
5
| +----+-------------+----------+------------+------+---------------------+-----------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------------+-----------+---------+-------------------+------+----------+-------------+
| 1 | SIMPLE | test_abc | NULL | ref | idx_a_b_c,idx_a_c_b | idx_a_b_c | 3066 | const,const,const | 1 | 100.00 | Using index |
+----+-------------+----------+------------+------+---------------------+-----------+---------+-------------------+------+----------+-------------+
|
语句三
1
2
3
| select *
from test_abc
where a= '1' and b = '2';
|
使用了索引的一部分，key_len = 2044，Using index不需要回表查询。
1
2
3
4
5
| +----+-------------+----------+------------+------+---------------------+-----------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------------+-----------+---------+-------------+------+----------+-------------+
| 1 | SIMPLE | test_abc | NULL | ref | idx_a_b_c,idx_a_c_b | idx_a_b_c | 2044 | const,const | 1 | 100.00 | Using index |
+----+-------------+----------+------------+------+---------------------+-----------+---------+-------------+------+----------+-------------+
|
语句四
1
2
3
| select *
from test_abc
where a= '1';
|
仍然可以使用索引
1
2
3
4
5
| +----+-------------+----------+------------+------+---------------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------------+-----------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | test_abc | NULL | ref | idx_a_b_c,idx_a_c_b | idx_a_b_c | 1022 | const | 5 | 100.00 | Using index |
+----+-------------+----------+------------+------+---------------------+-----------+---------+-------+------+----------+-------------+
|
语句五：
1
2
3
| select *
from test_abc
where b = '223' and c= '55';
|
按理来说应该不会使用索引的
1
2
3
4
5
| +----+-------------+----------+------------+-------+---------------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------------+-----------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | test_abc | NULL | index | idx_a_b_c,idx_a_c_b | idx_a_b_c | 3066 | NULL | 1010 | 1.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------------+-----------+---------+------+------+----------+--------------------------+
|
语句六
1
2
3
| select *
from test_abc
where a= '1' and b > '2' and c = '3'
|
按照最左匹配原则使用了索引idx_a_b_c，从索引长度来看b也使用了索引
1
2
3
4
5
| +----+-------------+----------+------------+-------+---------------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------------+-----------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | test_abc | NULL | range | idx_a_b_c,idx_a_c_b | idx_a_b_c | 2044 | NULL | 4 | 10.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------------+-----------+---------+------+------+----------+--------------------------+
|
语句七
1
2
3
4
| select *
from test_abc
FORCE INDEX (idx_a_c_b)
where a= '1' and c = '3' and b > '2';
|
按照最左匹配原则使用了索引idx_a_c_b
1
2
3
4
5
| +----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | test_abc | NULL | range | idx_a_c_b | idx_a_c_b | 3066 | NULL | 3 | 100.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|
语句八
1
2
3
| select *
from test_abc
where a>'500' and b = '2' and c = '3'
|
使用索引：
1
2
3
4
5
| +----+-------------+----------+------------+-------+---------------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------------+-----------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | test_abc | NULL | range | idx_a_b_c,idx_a_c_b | idx_a_b_c | 1022 | NULL | 559 | 1.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------------+-----------+---------+------+------+----------+--------------------------+
|
核心：对比语句6、7、8，最左匹配原则，对于范围查询的那一项也是有效的。
全文索引 - Full-Text Search
全文索引是将存储于数据库的整本书或整篇文章中的任意内容信息查找出来的技术，它可以根据需要获得全文中有关章、节、段、词等信息，也可以进行各种统计和分析。
仅作了解