MySQL 的索引、执行计划、优化器算法

SQL处理流程

INDEX 索引

索引介绍

索引：是排序的快速查找的特殊数据结构，定义作为查找条件的字段上，又称为键key，索引通过存储引擎实现；

索引相当于一本书的目录，可以优化查询。

优点：

• 索引可以降低服务需要扫描的数据量，减少了IO次数
• 索引可以帮助服务器避免排序和使用临时表
• 索引可以帮助将随机I/O转为顺序I/O

缺点：

• 占用额外空间，影响插入速度

索引类型：

• B+ TREE、HASH、R TREE、FULL TEXT
• 聚簇（集）索引、非聚簇索引：数据和索引是否存储在一起
• 主键索引、二级（辅助）索引
• 稠密索引、稀疏索引：是否索引了每一个数据项
• 简单索引、组合索引: 是否是多个字段的索引
• 左前缀索引：取前面的字符做索引
• 覆盖索引：从索引中即可取出要查询的数据，性能高

聚簇（区）索引（集群索引，聚集索引）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ViSwm6DD-1647356470190)(MySQL 的索引、执行计划、优化器算法.assets/image-20220315205535486.png)]

B+Tree索引：按顺序存储，每一个叶子节点到根结点的距离是相同的；左前缀索引，适合查询范围类的数据

区（extent）是聚簇索引分配叶子节点空间的最小单元；默认是连续的64个page页，大小默认1M。

extent 区： 默认是连续的64个page页（默认一个page16K），大小默认1M。区是聚簇索引分配叶子节点空间的最小 单元。表的数据行都是（逻辑）有序的存储到聚簇索引中的。按照聚簇索引组织存储（叶子节点）。

设置聚簇索引的前提

1. 如果表中设置了主键（例如ID列），自动根据ID列生成聚簇索引
2. 如果没有设置主键，自动选择第一个not null的唯一键的列作为聚簇索引
3. 自动生成隐藏（6字节row_id）的聚簇索引。

建议： 在建表时，创建的主键，最好是无关紧要的数字自增列。

btree构建过程:

• 叶子节点： 存储数据行时就是有序的，直接将数据行的page作为叶子节点（相邻的叶子结点，有双向指针）
• 枝节点 ： 提取叶子节点ID的范围+指针，构建枝节点（相邻枝节点，有双向指针）
• 根节点 ： 提取枝节点的ID的范围+指针，构建根节点

可以使用B+Tree索引的查询类型：(假设前提: 姓,名,年龄三个字段建立了一个复合索引)

• 全值匹配：精确所有索引列，如：姓wang，名xiaochun，年龄30

• 匹配最左前缀：即只使用索引的第一列，如：姓wang

• 匹配列前缀：只匹配一列值开头部分，如：姓以w开头的记录

• 匹配范围值：如：姓ma和姓wang之间

• 精确匹配某一列并范围匹配另一列：如：姓wang,名以x开头的记录

• 只访问索引的查询

B+Tree索引的限制：

• 如不从最左列开始，则无法使用索引，如：查找名为xiaochun，或姓为g结尾
• 不能跳过索引中的列：如：查找姓wang，年龄30的，只能使用索引第一列

注意：

索引列的顺序和查询语句的写法应相匹配，才能更好的利用索引

为优化性能，可能需要针对相同的列但顺序不同创建不同的索引来满足不同类型的查询需求

辅助索引

辅助索引，也叫非聚集索引。和聚集索引相比，叶子节点中并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点的索引行还包含了一个书签（bookmark），该书签用来告诉InnoDB哪里可以找到与索引相对应的行数据。

辅助索引存的是：辅助索引列值+ID主键值+指针

需要人为创建辅助索引，将经常作为查询条件的列创建辅助索引，起到加速查询 的效果。

btree 构建辅助索引过程

1. 叶子节点：提取主键（ID）+辅助索引列，按照辅助索引列进行从小到大排序后，生成叶子节点。（相邻的叶子结点，有双向指针）
2. 枝节点 ：提取叶子节点辅助索引列的范围+指针，构建枝节点（相邻枝节点，有双向指针）
3. 根节点 ：提取枝节点的辅助索引列的范围+指针，构建根节点

辅助索引的功能：按照辅助索引列，作为查询条件时。

1. 查找辅助索引树，得到ID值
2. 拿着ID值回表（聚簇索引）查询

回表

回表：就是Innodb引擎按照辅助索引列，作为查询条件时，先查找辅助索引树得到ID，再到聚簇索 引树查找数据行的过程。

回表过多会导致 IO量多、IO次数多、随机IO会增多、SQL层和engine交互多次。IO偏高 ，CPU 偏高。

减少回表建议：

1. 辅助索引能够完全覆盖查询结果，可以使用联合索引。
2. 尽量让查询条件精细化，尽量使用唯一值多的列作为查询条件
3. 优化器：MRR（Multi-Range-Read），ICP（index condition pushdown），锦上添花的功能。
   • mysql> select @@optimizer_switch;
   • mysql> set global optimizer_switch='mrr=on’
     • 功能：辅助索引查找后得到ID值，进行自动排序；一次性回表，很有可能受到B+TREE中的双向指针的优化查 找。

索引树高度

索引树的高度越低越好，一般是2层，索引树从0层开始，一般到2层（实际3层）

如何减少索引树高度

• 分区表。一个实例里管理。
• 按照数据特点，进行归档表。
• 分布式架构。针对海量数据、高并发业务主流方案。
• 在设计方面，满足三大范式。
• 索引数据量大的话使用前缀索引。（100字符 只取前10个字符，构建索引树。）
• 选择合适的数据类型
  • 存储人的年龄 ，使用 tinyint 个好一些
  • 存储人名建议使用varchar（20）类型存储变长列值。

计算一个索引的高度

管理索引

创建索引：

CREATE [UNIQUE] INDEX index_name ON tbl_name (index_col_name[(length)],...);

#建议使用这种方式
ALTER TABLE tbl_name ADD INDEX index_name(index_col_name[(length)]); 

#创建联合索引
ALTER TABLE tbl_name ADD INDEX index_col_name(索引列名,索引列名);

#前缀索引创建：先要判断前缀长度多少合适：
ALTER TABLE tbl_name ADD INDEX index_name(索引列名（NUM));

help CREATE INDEX;

删除索引：

DROP INDEX index_name ON tbl_name;

#建议使用
ALTER TABLE tbl_name DROP INDEX index_name(index_col_name);

查看索引：

SHOW INDEX FROM [db_name.]tbl_name;

优化表空间：

OPTIMIZE TABLE tb_name;

查看索引的使用

explain select
desc select [format=json|tree]

压测

mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k1='aa'" engine=innodb --number-of-queries=20000 -uroot -p123 -verbose

--concurrency=100  		#模拟同时100会话连接
--create-schema='test'  #操作的库是谁
--query="select * from test.t100w where k2='780P'" #做了什么操作
--number-of-queries=2000 #一共做了多少次查询

执行计划获取和分析

执行计划信息介绍

mysql> desc select * from course;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | course | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

table	#此次查询访问的表
type 	#索引查询的类型（ALL、index、range、ref、eq_ref、const(system)、NULL）
possible_keys	#可能会应用的索引
key		#最终选择的索引
key_len #索引覆盖长度，主要是用来判断联合索引应用长度。
rows	#需要扫描的行数
Extra 	#额外信息

列名	说明
id	执行编号，标识select所属的行。如果在语句中没子查询或关联查询，只有唯一 的select，每行都将显示1。否则，内层的select语句一般会顺序编号，对应于其在原始语句中的位置；我的理解是SQL执行的顺利的标识,SQL从大到小的执行.
select_type	简单查询：SIMPLE 复杂查询：PRIMARY（最外面的SELECT）、DERIVED（用于FROM中的子查 询）、UNION（UNION语句的第一个之后的SELECT语句）、UNION RESUlT（匿名临时表）、SUBQUERY（简单子查询）
table	访问引用哪个表（引用某个查询，如“derived3”）
type	关联类型或访问类型，即MySQL决定的如何去查询表中的行的方式
possible_keys	查询可能会用到的索引
key	显示mysql决定采用哪个索引来优化查询
key_len	显示mysql在索引里使用的字节数
ref	当使用索引列等值查询时,与索引列进行等值匹配的对象信息
rows	为了找到所需的行而需要读取的行数，估算值，不精确。通过把所有rows列值 相乘，可粗略估算整个查询会检查的行数
Extra	额外信息 Using index：MySQL将会使用覆盖索引，以避免访问表 Using where：MySQL服务器将在存储引擎检索后，再进行一次过滤 Using temporary：MySQL对结果排序时会使用临时表 Using filesort：对结果使用一个外部索引排序

说明： type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：NULL> system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ，一般来说，得保证查询至少达到range级别，最好能达到ref

type信息详解

类型	说明
All	最坏的情况,全表扫描
ndex	和全表扫描一样。只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了 排序, 但是开销仍然非常大。如在Extra列看到Using index，说明正在使用覆盖索引， 只扫描索引的数据，它比按索引次序全表扫描的开销要小很多
range	范围扫描，一个有限制的索引扫描。key 列显示使用了哪个索引。当使用=、 <>、>、 >=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可以使 用 range
ref	一种索引访问，它返回所有匹配某个单个值的行。此类索引访问只有当使用非唯一性索 引或唯一性索引非唯一性前缀时才会发生。这个类型跟eq_ref不同的是，它用在关联操 作只使用了索引的最左前缀，或者索引不是UNIQUE和PRIMARY KEY。ref可以用于使 用=或<=>操作符的带索引的列。
eq_ref	最多只返回一条符合条件的记录。使用唯一性索引或主键查找时会发生 （高效）
const	当确定最多只会有一行匹配的时候，MySQL优化器会在查询前读取它而且只读取一 次，因此非常快。当主键放入where子句时，mysql把这个查询转为一个常量（高效）
system	这是const连接类型的一种特例，表仅有一行满足条件。
NUll	意味着mysql能在优化阶段分解查询语句，在执行阶段甚至用不到访问表或索引（高 效）

• ALL 没有使用到索引
  • 查询条件没建立索引
  • 有索引不走
• index 全索引扫描，注意： 建议不要出现。特别是聚簇索引INDEX，相当于全表扫描。
• range 索引范围扫描
  • 范围查找最好缩小查询范围。比如> < 要有上限和下限，或者可以使 用limit进行限制。
  • 特别是在做delete 和 update。在索引设计不合理时，使用limit有可能会出现主从数据不一致。
  • 如果查询列重复值少的话，我们建议改写为 union all
• ref 辅助索引等值查询
• eq_ref : 多表连接查询中，非驱动表的连接条件是主键或唯一键时。
• const(system)： 主键或唯一键等值查询

key_len介绍

key_len的作用是用来判断联合索引应用的部分。

例如： idx(a,b,c) 我们希望应用联合索引的部分越多越好

计算key_len的长度能知道SQL语句使用了几个索引

#8.0版本的新办法
desc format=json select * from t100w where
num=771751 and k1='47' and k2='ghLM';
"key": "idx_n_k1_k2",
"used_key_parts": [
"num",
"k1",
"k2"

联合索引应用细节

联合索引应用要满足最左原则

• 建立联合索引时，选择重复值较少的列作为最左列。
• 使用联合索引时，查询条件中，必须包含最左列，才有可能应用到联合 索引。

使用场景

• 应急性的慢

show full processlist; ----> 慢语句 ----> explain SQL ---> 优化索引、改写语句

• 间歇性慢。

slowlog ----> 慢语句 ---> explain SQL ---> 优化索引、改写语句

索引应用规范

建立索引的原则（DBA运维规范）

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和 创建什么类型的索引。

• 必须要有主键,最好数字自增列。

• 经常做为where条件列 order by group by join on, distinct 的条件(业务:产品功能+用户行为)

• 联合索引最左原则

• 列值长度较长的索引列，我们建议使用前缀索引。

• 降低索引条目，一方面不要创建没用索引，不常使用的索引清理

  #收集状态信息
  sys.schema_unused_indexes
  sys.schema_redundant_indexes
  

• 索引维护要避开业务繁忙期，建议用gh-ost

• 大量数据导入的时候，先把索引禁用。

不走索引的情况（开发规范）

• 没有查询条件，或者查询条件没有建立索引

• 查询的结果集，超过了总数行数25%，优化器觉得就没有必要走索引了。

• 索引本身失效，统计信息不真实（过旧） analyze table t1;

• 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-， *，/，! 等)

• 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.

  telnum char(11)
  select * from t1 where telnum = '110'; √
  select * from t1 where telnum = 110; X
  

• <> ，not in 不走索引（辅助索引）

• 单独的>,<,in 有可能走，也有可能不走，和结果集大小有关，尽量结合业 务添加limit

• like “%_” 百分号在最前面不走索引

索引优化

参考资料: 阿里的《Java开发手册》

• 独立地使用列：尽量避免其参与运算，独立的列指索引列不能是表达式的一部分，也不能是函数的参数，在where条件中，始终将索引列单独放在比较符号的一侧，尽量不要在列上进行运算（函数操作和表达式操作）
• 左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择性（不重复的索引值和数据表的记录总数的比值）来评估，尽量使用短索引，如果可以，应该制定一个前缀长度
• 多列索引：AND操作时更适合使用多列索引，而非为每个列创建单独的索引
• 选择合适的索引列顺序：无排序和分组时，将选择性最高放左侧
• 只要列中含有NULL值，就最好不要在此列设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
• 对于经常在where子句使用的列，最好设置索引
• 对于有多个列where或者order by子句，应该建立复合索引
• 对于like语句，以 % 或者 _ 开头的不会使用索引，以 % 结尾会使用索引
• 尽量不要使用not in和<>操作,虽然可能使用索引,但性能不高
• 不要使用RLIKE正则表达式会导致索引失效
• 查询时，能不要就不用，尽量写全字段名，比如:select id,name,age from students;
• 大部分情况连接效率远大于子查询
• 在有大量记录的表分页时使用limit
• 对于经常使用的查询，可以开启查询缓存
• 多使用explain和profile分析查询语句
• 查看慢查询日志，找出执行时间长的sql语句优化

优化器针对索引的算法

MySQL索引的自优化-AHI（自适应HASH索 引）

MySQL的InnoDB引擎，能够创建只有Btree。

AHI作用

• 自动评估"热"的内存索引page，生成HASH索引表。
• 帮助InnoDB快速读取索引页。加快索引读取的效果
• 相当与索引的索引。

MySQL-Change buffer

Change buffer只对辅助索引有效

比如insert，update，delete 数据。 对于聚簇索引会立即更新。 对于辅助索引，不是实时更新的。

在InnoDB 内存结构中，加入了insert buffer（会话），现在版本叫 change buffer。

Change buffer 功能是临时缓冲辅助索引需要的数据更新。

当我们需要查询新insert 的数据，会在内存中进行merge(合并)操作，此时辅 助索引就是最新的。

analyze table tab_name;	#触发统计信息更新

index_condition_pushdown （ICP）

ICP：索引下推 ，5.6+ 加入的特性

作用： SQL层做完过滤后，只能用a,b的部分辅助索引，将c列条件的过滤下推 到engine层，进行再次过滤。排除无用的数据页。 最终去磁盘上拿数据页。

大大减少无用IO的访问。

MRR : Multi Range Read

作用： 减少回表。

开关方法：

set global optimizer_switch='mrr=on,mrr_cost_based=on|off';

BKA

主要作用，使用来优化非驱动表的关联列有辅助索引。

只有开启MRR后才能使用BKA

开启方式

mysql> set global optimizer_switch='mrr=on,mrr_cost_based=off';
mysql> set global optimizer_switch='batched_key_access=on';

SNLJ 普通嵌套循环连接

BNLJ

BKA

优化器算法查询

select @@optimizer_switch;
index_merge=on,
index_merge_union=on,
index_merge_sort_union=on,
index_merge_intersection=on,
engine_condition_pushdown=on,
index_condition_pushdown=on,
mrr=on,mrr_cost_based=on,
block_nested_loop=on,
batched_key_access=off,
materialization=on,
semijoin=on,
loosescan=on,
firstmatch=on,
duplicateweedout=on,
subquery_materialization_cost_based=on,
use_index_extensions=on,
condition_fanout_filter=on,
derived_merge=on

修改算法

1. 修改my.cnf文件

vim ~/my.cnf 
optimizer_switch='batched_key_access=on'

2. 命令行

set global optimizer_switch='batched_key_access=on';

3. hints 了解一下

8.0 版本索引的新特性

• 不可见索引。invisable/visable inde

针对优化器不可见。但是索引还在磁盘存在，还会自动维护。

对于索引维护时，不确定索引是否还有用。这时可以临时设定为invisable。

ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE;
ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;

• 倒序索引。
  ondition_fanout_filter=on,
  derived_merge=on

### 修改算法

1.   修改my.cnf文件

~~~mysql
vim ~/my.cnf 
optimizer_switch='batched_key_access=on'

2. 命令行

set global optimizer_switch='batched_key_access=on';

3. hints 了解一下

8.0 版本索引的新特性

• 不可见索引。invisable/visable inde

针对优化器不可见。但是索引还在磁盘存在，还会自动维护。

对于索引维护时，不确定索引是否还有用。这时可以临时设定为invisable。

ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE;
ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;

• 倒序索引。
• hash join：join 操作用不上索引优化的情况下。