目录 start

索引
索引的类型
需要使用索引的场景
1. 索引优化场景
  1. 统计报表
SQL查询时不使用索引的场景

目录 end|2022-01-25 01:55|

索引

Official Doc

索引是采用特定的数据结构设计(B+Tree 或者 Hash), 为了对若干列进行快速访问

优点

加快查询速度
如果使用唯一索引，保证数据库表中每条数据的唯一性；
加快表与表之间的连接操作
使用排序和分组检索数据时，可以显著的加快排序和分组的时间

缺点

需要额外占用存储空间，如果索引建立太多可能会导致索引空间大于数据空间反而降低性能
当对被索引的数据进行DML(增删改), 需要重建索引, 有一定性能影响

注意: Avoiding Full Table Scans

业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引
对长度大于50的 varchar 字段建立索引时，按需求恰当的使用前缀索引，或使用其他方法(例如增加int类型列col_crc32，然后对col_crc32建立索引)
合理创建联合索引(避免冗余)，区分度最高的在最左边，单个索引的字段数不超过5个
单张表的索引数量控制在5个以内，若单张表多个字段在查询需求上都要单独用到索引，需要经过DBA评估。查询性能问题无法解决的，应从产品设计上进行重构

Explain

使用 explain 判断SQL语句是否合理使用索引

id：数字越大越先执行，一样大则从上往下执行，如果为NULL则表示是结果集，不需要用来查询。
select_type：


simple	不需要union的操作或者是不包含子查询的简单select语句。
primary	需要union操作或者含有子查询的select语句。
union	连接两个select查询，第一个查询是dervied派生表，第二个及后面的表select_type都是union。
dependent union	与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响。
union result	包含union的结果集。
subquery	除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery。
dependent subquery	与dependent union类似，表示这个subquery的查询要受到外部表查询的影响。
derived	from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select。

table: 表名，如果是用了别名，则显示别名
type 从上至下，好到差：除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引。



system	表中只有一行数据或者是空表。
const	使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。
eq_ref	出现在要连接过个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq_ref。
ref	不像eq_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找。
fulltext	全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引。
ref_or_null	与ref方法类似，只是增加了null值的比较。实际用的不多。
unique_subquery	用于where中的in形式子查询，子查询返回不重复值唯一值。
index_subquery	用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。
range	索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。
index_merge	表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引。
index	索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。
all	这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。

possible_keys：查询可能使用到的索引。
key：查询真正使用到的索引。
key_len：用于处理查询的索引长度。
ref：常数等值查询显示const，连接查询则显示表的关联字段。
rows：执行计划中估算的扫描行数，不是精确值。
filtered：表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例。
extra：该字段信息较多，这里就不一一叙述了。

在实际的使用过程中, 需要重点去关注type、key、key_len、rows、extra这几个参数， type要努力优化到range级别，all要尽量少的出现，在查询的过程中要尽量使用索引
在extra里面出现 Using filesort, Using temporary 是不太好的，要去优化提高性能。
通常情况下一个SQL语句只能在表上命中一个索引，但还有索引合并的情况参考: MySQL索引合并的使用与原理
- intersect， union， sort-union

为何选择 B+ 树结构

参考:【原创】为什么Mongodb索引用B树，而Mysql用B+树? MongoDB PostgreSQL 都是使用B-Tree 从 MongoDB 及 Mysql 谈B/B+树
 分布式数据库千亿级超大表性能优化实践

MySQL Innodb 设计索引块是固定大小，中间节点上存放数据就会导致能用来存放指针数据的空间变小，也就意味着引用的子节点更少，树的高度更高（增加了IO次数），极端情况下退化为线性表。
叶子节点以链表形式连接，方便范围查询，索引排序
联合索引原理：依据字段顺序例如 (a,b,c)，会先依据a有序，然后b有序，最后c有序的方式来组织B+树，据此才有最左原则限制，如果中间某列没有值或使用like会导致后面的列不走索引
叶子节点上存放的数据：
- 如果是聚簇索引，则保存的是实际数据
- 如果是非聚簇索引，则依然是和中间节点一样的索引节点，但是会有一个指针指向对应的数据块

相较于平衡二叉树，由于单节点具有多叉，数据节点发生变化时，调整成本低

基本SQL用法

创建
- ALTER 方式
  - 普通索引 ALTER table ADD INDEX index_name(column1, column2);
  - 唯一索引 ADD UNIQUE
  - 主键索引 ADD PRIMARY KEY
- CREATE 方式
  - 普通方式 CREATE INDEX index_name ON table_name (column_list)
  - 唯一索引 CREATE UNIQUE INDEX index_name ON table_name(column_list)
删除
- DROP INDEX index_name ON talbe_name
- ALTER TABLE table_name DROP INDEX index_name
- ALTER TABLE table_name DROP PRIMARY KEY
查看
- show index from tableName
- Official Doc详解命令的输出内容
强制使用索引
- select * from test force index(id) where id = 1;

索引的类型

索引分类的概念，非MySQL真实索引类型

覆盖索引

InnoDB存储引擎支持覆盖索引（covering index，或称索引覆盖），即仅从辅助索引中就可以得到查询的信息，而不需要查询聚集索引中的记录。
使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。

联合索引

两个或更多个列上的索引被称作联合索引，联合索引又叫复合索引

稠密索引

每个索引键值都对应有一个索引项

稀疏索引

相对于稠密索引，稀疏索引只为某些搜索码值建立索引记录；在搜索时，找到其最大的搜索码值小于或等于所查找记录的搜索码值的索引项，然后从该记录开始向后顺序查询直到找到为止。

普通索引

是最基本的索引，只在单列上建立索引，无特殊限制

唯一索引

与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一

主键索引

是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。

当创建表时没有显示定义主键时：
1. 首先判断表中是否有非空的整形唯一索引,如果有,则该列为主键(这时候可以使用 select _rowid from table 查询到主键列).
2. 如果没有符合条件的则会自动创建一个6字节的主键(该主键是查不到的).

聚集索引

聚集索引(Clustered Index)

聚集索引：指索引项的排序方式和表中数据记录存储排序方式一致的索引

而聚集索引（clustered index）就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。
聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。

辅助索引/非聚集索引

辅助索引（Secondary Index，也称非聚集索引）

叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签（bookmark）。
该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。辅助索引的书签就是相应行数据的聚集索引键。
辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。

当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶节点级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录，而 MyISAM 没有这个特性。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


-- MySQL 5.7 
create table report_user_date(id bigint primary key auto_increment, user_id bigint,
 name varchar(32), avatar varchar(32), phone varchar(16), a_c int , b_c int, d_c int , e_c int, crea datetime);

alter table report_user_date add index idx_user(user_id,crea);

-- 如果只查询索引字段（user_id 或 crea），还能利用上联合索引，即使查询条件没有最左列
-- innodb 引擎时，由上述特性 查id 也能使用上联合索引，myisam 就不能了
explain select id  from report_user_date where crea < '2021-12-26';

-- 如果查询非索引字段，就用不上联合索引了; 字段越多，查询越慢
select name  from report_user_date where crea < '2021-12-26';

非聚集索引的使用场合为查询所获数据量较少时或者某字段中的数据的唯一性比较高时，非聚集索引必须是稠密索引

Hash 索引

适用场景

等值查询，索引列数据分散

限制和缺点

哈希索引只保存哈希码和指针，而不存储字段值，所以不能使用索引中的值来避免回表
哈希索引数据并不是按照索引值顺序存储的，所以无法用于排序和范围查询
哈希索引不支持部分索引值查找，因为哈希索引始终是使用索引列的全部内容来计算哈希码。
HASH冲突（链地址法）会对整体维护加大负担（查询，新增，删除）。

优化

假如查询字段A较长，可用新列B存储字段A的hash值，再基于B列建立Hash索引，优化索引存储大小

自适应哈希索引

自适应哈希索引默认开启 show variables like "innodb_adaptive_hash_index";
查看引擎状况，能看到hash索引使用情况 show engine innodb status;

存储引擎会自动对个索引页上的查询进行监控，如果能够通过使用自适应哈希索引来提高查询效率，其便会自动创建自适应哈希索引，不需要开发人员或运维人员进行任何设置操作。自适应哈希索引是对innodb的缓冲池的B+树页进行创建，不是对整张表创建，因此速度很快。

倒排索引

需要使用索引的场景

经常出现在 where 条件中的字段需添加索引。
join 关联，被驱动表需要对关联字段添加索引。
order by ，group by ，distinct的字段需要添加在索引的后面。

创建索引时避免有如下极端误解：

宁滥勿缺。认为一个查询就需要建一个索引。
宁缺勿滥。认为索引会消耗空间、严重拖慢更新和新增速度。
抵制唯一索引。认为业务的唯一性一律需要在应用层通过“先查后插”方式解决。

索引优化场景

统计报表

如果使用MySQL存储一定数据量的统计报表数据，使用 MyISAM 有更多优势, 没有事务，行锁等开销

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


    CREATE TABLE `report_user_date` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `user_id` bigint(20) DEFAULT NULL,
    `name` varchar(32) DEFAULT NULL,
    `avatar` varchar(32) DEFAULT NULL,
    `phone` varchar(16) DEFAULT NULL,
    `a_c` int(11) DEFAULT NULL,
    `b_c` int(11) DEFAULT NULL,
    `d_c` int(11) DEFAULT NULL,
    `e_c` int(11) DEFAULT NULL,
    `crea` datetime DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_user` (`user_id`,`crea`)
    )

构造测试数据

1
2
3
4
5
6
7
8
9


    // 生成随机数据
    insertOne := "insert into report_user_date( user_id, name, avatar, phone, a_c, b_c, d_c, e_c,crea) value (?,?,?,?,?,?,?,?,?);"
	stmt, _ := db.Prepare(insertOne)
	now := time.Now()
	for i := 0; i < 100000; i++ {
		stmt.Exec(i%2000, fmt.Sprint(i), fmt.Sprint(i), fmt.Sprint(i), i, i+1, i+2, i+3, now.Add(time.Second*time.Duration(i)))
	}
    // 多次复制自身数据 扩大数据量 
    // insert into report_user_date( user_id, name, avatar, phone, a_c, b_c, d_c, e_c,crea) select user_id, name, avatar, phone, a_c, b_c, d_c, e_c,crea from report_user_date;

对比查询效率

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


-- MySQL 5.7 340w数据

select user_id, count(a_c )  from report_user_date where user_id between 100 and 700  group by user_id ;
-- innodb 2700ms
-- myisam 900ms

select user_id, count(distinct a_c ),count(distinct b_c ) from report_user_date where user_id between 100 and 700 and crea < '2021-12-26 18:00:00' group by user_id ;
-- innodb 2700ms
-- myisam 1100ms

select user_id from report_user_date where crea < '2021-12-26';
-- 如果只查询索引字段，还能利用上联合索引，即使查询条件没有最左列字段， 如果查询非索引字段，就用不上联合索引了，字段越多，查询越慢
-- 如果查询的是id 主键字段：innodb 会使用上索引，myisam 就不能了

SQL查询时不使用索引的场景

全局

如果MySQL分析器估计使用全表扫描要比使用索引快,则不使用索引(例如全部记录数量少于10 等等)
通过索引扫描的记录数超过20%-30%，可能会变成全表扫描。和查询条件无关
- 例如使用 <> 或！= , is null 或 is not null 不影响使用索引，通常是因为扫描行数过多
注意: 索引列上存在 null 值, 索引仍能使用, 包括 is null 和 is not null, 但是 Null值可能会相比于默认值占用更多存储空间