索引及执行计划管理

索引及执行计划管理

1 什么是索引?

相当于书中的目录,起到优化查询的功能.
条件类查询: where group by order by join on distinct

2 索引的类型(数据结构,查找算法)

Bree :B树索引(Balance Tree). InnoDB,MyISAM
Hash :哈希索引, memory相关引擎.innodb中也会维护自己的AHI(自适应)的hash索引。
FullTEXT :全文索引.一般是在大字段使用,一般用ES数据库存储大字段.
GIS :地理位置索引,一般MongoDB可以替代
Rtree :

3. MySQL为什么要使用Btree查找算法？了解。

二叉树
平衡树
可以快速锁定，需要查找数据的范围。

4. MySQL 索引怎么应用的BTREE结构

4.1 聚簇索引

4.1.0 前导内容

段 : 一个表就是一个段,会由多个区构成
区 : 又称为簇,由连续的64个页构成,默认是1M
页 : 一个数据页是16KB,MySQL IO的最小单元

4.1.1 生成条件

(1) 创建InnoDB表时,如果设置了主键ID列PK.自动把ID列作为聚簇索引列.
(2) 没有主键,会选择第一个唯一键(UK) 作为聚簇索引列.
(3) 如果都没有,生成隐藏列(ROWID),作为聚簇索引列.

4.1.2 作用

(1) 在存储数据时,InnoDB申请连续的磁盘空间,进行存储.
(2) 数据在存储时，按照聚簇索引值的顺序在磁盘有序存储。
以上的作用,又被称之为聚簇索引组织存储数据表。简称：索引组织表。
尽可能保证 从逻辑层 —》 物理层的数据有序性

4.1.3 聚簇索引的B树构建

(1) 数据行在存储时,按照聚簇索引顺序逻辑顺序和屋里顺序有序存储
(2) 叶子节点构建,就是数据行 所在的数据页,数据即索引
(3) 非叶子节点,存储了叶子节点ID范围+指针
(4) 根节点,存储了非叶子节点的ID范围+指针

4.1.4 B-tree,B+tree,B*tree

B+tree : 在叶子节点加入双向指针
B*tree : 在非叶子节点加入双向指针
目的 : 1 减少IO次数
2 减少IO量级
3 减少随机IO

4.2 辅助索引

普通索引,主要的作用 : 优化非聚簇索引列的条件查询.

4.2.1 辅助索引构建过程原理

(1) “提取” 辅助索引列+ID列值
(2) 按照辅助索引列值进行从小到大排序
(3) 将有序的值均匀存储到数据页中，生成叶子节点
(4) 将叶子节点中的辅助索引列值的范围+指针，生成非叶子节点
(5) 提取下层非叶子节点的范围+指针，生成根节点

4.2.2 辅助如何加速查询

   按照辅助索引列作为查询条件时。

（1）通过扫描需要的索引页，获取到查询条件对应的ID值。
（2）拿着ID值，再回到聚簇索引进行扫描，最终获取数据行。又被称之为：回表查询。

4.2.3 回表查询

回表来的问题：
从辅助索引扫描得出ID值之后，回到聚簇索引扫描的过程。
IO次数、IO量会增多、随机IO会增多。

**怎么解决回表的问题： **

1. 不产生回表
   覆盖索引： 理论上是把所有查询的条件数据都包含在辅助索引中。
   这是比较理想的想法，现实情况不太好保证。
2. 减少回表
   将可能让辅助索引得出的ID值是唯一或者较少个的。
   （1）查询条件尽量精准。
   （2）MRR技术，将辅助索引扫描到的ID,先排序再一次性回表。

5. 影响索引树的高度因素

聚簇索引: 一般建议2-3层为佳。三层聚簇索引可以存储2000w+的数据行。
辅助索引：3-4 层为佳

5.1 数据行越多

分区表（逻辑）、历史数据归档（pt-archive）、分库分表（物理层次）

5.2 数据类型影响

varchar char
enum

5.3 索引列值长度

前缀索引。

6. 索引的管理操作

6.1 查看表的索引

desc city;
key 
-----
 PRI 
 UNI    
 MUL 

mysql> show index from city\G

6.2 创建索引

原则： 经常作为查询条件的列作为索引列。
创建单列索引：
mysql> alter table city add index idx_name(name); 
创建联合索引：
mysql> alter table city add index i_n_d_p(name,district,population);
创建前缀索引（字符串列）：
mysql> alter table city add index i_nn(name(5));

6.3 删除索引

mysql> alter table city drop index idx_name;
mysql> alter table city drop index i_n_d_p;
mysql> alter table city drop index i_n;
mysql> alter table city drop index i_nn;

7. 压力测试

7.1 导入 t100w.sql

7.2 建立索引之前的压测

mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema=‘test’ --query=“select * from test.t100w where k2=‘780P’” engine=innodb --number-of-queries=2000 -uroot -p123 -verbose

7.3 建立索引

alter table test.t100w add index idx_k2(k2);

7.4 再次对比

mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema=‘test’ --query=“select * from test.t100w where k2=‘780P’” engine=innodb --number-of-queries=2000 -uroot -p123 -verbose

8. 执行计划分析-explain(desc)

8.1 分析什么执行计划

优化器最终选择的执行计划。

8.2 如何获取？

mysql> desc select * from t100w where k2=‘780P’;
±—±------------±------±-----------±-----±--------------±-------±--------±------±-----±---------±------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±-----±--------------±-------±--------±------±-----±---------±------+
| 1 | SIMPLE | t100w | NULL | ref | idx_k2 | idx_k2 | 17 | const | 1 | 100.00 | NULL |
±—±------------±------±-----------±-----±--------------±-------±--------±------±-----±---------±------+

8.3 如何分析？

table         ：针对的表
type          ：查询类型，反馈：是否走索引，索引级别？
possible_keys ：可能会用到的索引
key           ：真正走的索引
key_len       ：索引的覆盖长度，判断联合索引使用情况。
rows          ：需要扫描的数据行数（预估）
Extra         ：额外信息

8.4 type输出详解

8.4.1 ALL 不走任何索引，全表扫描

例子： 
查询条件没有索引：
mysql> desc select * from t100w where k1='ab';
不等值的条件查询
mysql> desc select * from t100w where k2 !='abcc';
mysql> desc select * from t100w where k2 not in ('abcc','abdd');
like '%aa%'前面带%的模糊查询
mysql> desc select * from t100w where k2 like '%aa%';

8.4.2 index 全索引扫描

mysql> desc select k2 from t100w;

8.4.3 range 索引范围扫描 （> < >= <= ,like , in ,or）

mysql> desc select * from city where id<10;
mysql> desc select * from city where countrycode like 'Ch%';
mysql> desc select * from city where countrycode in ('CHN','USA');
如果查询条件重复值少的话，可以考虑将其改写为以下语句，
mysql> desc select * from city where countrycode='CHN' union all select * from city where countrycode='USA';

8.4.4 ref 辅助索引等值查询

mysql> desc select * from city where countrycode='CHN';

8.4.5 eq_ref 多表连接中非驱动的关联条件是主键或唯一键

mysql> desc select * from city left join country on city.countrycode=country.code ;

8.4.6 const(system) 主键或唯一键等值查询

mysql> desc select * from city where id=10;

8.4.7 NULL 查不到数据

mysql> desc select * from city where id=1000000000;

1. key_len 索引覆盖长度

1.1 作用

索引应用的长度。
如果单列索引比较长，需要评估是否需要前缀索引。
如果是联合索引，帮助判断应用了几部分。
idx(a,b,c)
select where a b c

1.2 key_len的计算

key_len是索引列的定义的最大预留长度。
key_len和哪些因素有关？

字符集  
  utf8      : 最多3个字节字符
  utf8mb4   ：最多4个字节字符
  
数字类型： int tinyint  
数据类型             是否非空           key_len
int                  not null            4
tinyint              not null            1
int                  		             4+1
tinyint                                  1+1

字符串类型： char varchar  （ut8mb4）
数据类型                  是否非空           key_len
char(10)                  not null           10*4
varchar(10)               not null           10*4+2 
char(10)                             		 10*4+1
varchar(10)                                  10*4+2+1 

create table t1 (
n1 int not null ,
n2 int ,
c1 char(10) not null,
c2 varchar(24) 
)charset=utf8mb4;

alter table t1 add index idx(n1,n2,c1,c2);

问： 
select  索引的四个列都被应用到了，key_len
4+5+4*10+24*4+2+1 =148

1.3 联合索引的应用细节

idx(a,b,c,d)
联合索引是遵循最左原则。
(1) 在查询中，应用到索引，至少要有最左列
(2) 最左列是第一过滤条件，一般选择重复值少的列作为最左列。

1.3.1 全部覆盖

select *   from  t100w where a=？ and  b=? and c=? and d=?
select *   from  t100w where  b=? and c=? and d=?  and   a=？  
select *  from t1 where  n2=20 and c1='a' and c2='b' and a in (1,2);

1.3.2 部分覆盖

select *   from  t100w where a=？ and  b=? and c=?   ====> abc
select *   from  t100w where a=？ and  b=? and d=?   ====> ab
select *   from  t100w where a=？ and  b>? and c=?   ====> ab

1.3.3 不走索引

查询中，缺失了最左列
bc
b
c
d
bcd
bd

多子句 ： 联合索引设计，需要按照子句顺序建立联合索引。

1.3.4 通过压测工具 测试联合索引全部覆盖，部分覆盖。

mysql> alter table t100w add index idx(num,k2,k1);
压测2000次：
mysql> desc select * from t100w where num=641631 and k1 > 'aa' and k2='rsEF';

mysql> alter table t100w add index idx(num,k1,k2);
压测2000次：
mysql> desc select * from t100w where num=641631 and k1 > 'aa' and k2='rsEF';

1.4 extra 说明

Using filesort 此次查询中发生了额外的排序操作

mysql> desc select * from world.city where countrycode='CHN' order by population limit 5;
mysql> alter table world.city add index idxx(countrycode,population);

2. 索引应用规范

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

2.1.0 说明

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。那么索引设计原则又是怎样的?

2.1.1 (必须的) 建表时一定要有主键,一般是个无关自增列数字列。

降低索引树高度。

2.1.2 选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。
如果使用姓名的话，可能存在同名现象，从而降低查询速度。

优化方案:
(1) 如果非得使用重复值较多的列作为查询条件(例如:男女),可以将表逻辑拆分
(2) 可以将此列和其他的查询类,做联和索引

select count(*) from world.city;
select count(distinct countrycode) from world.city;
select count(distinct countrycode,population ) from world.city;

2.1.3(必须的) 为经常需要where 、ORDER BY、GROUP BY,join on等操作的字段，

排序操作会浪费很多时间。
where A B C ----》 A B C
in
where A group by B order by C
A,B，C

如果为其建立索引，优化查询
注：如果经常作为条件的列，重复值特别多，可以建立联合索引。

2.1.4 尽量使用前缀来索引

如果索引字段的值很长，最好使用值的前缀来索引。

2.1.5 限制索引的数目

索引的数目不是越多越好。
可能会产生的问题:
(1) 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
(2) 修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
(3) 优化器的负担会很重,有可能会影响到优化器的选择.
percona-toolkit中有个工具,专门分析索引是否有用

2.1.6 删除不再使用或者很少使用的索引(percona toolkit)

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

2.1.7 大表加索引,要在业务不繁忙期间操作

2.1.8 尽量少在经常更新值的列上建索引

2.1.9 建索引原则

(1) 必须要有主键,如果没有可以做为主键条件的列,创建无关列
(2) 经常做为where条件列 order by group by join on, distinct 的条件(业务:产品功能+用户行为)
(3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 索引维护要避开业务繁忙期

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

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

2.2.2 查询结果集是原表中的大部分数据，应该是15-25%以上。

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

limit
精确范围
1000000
500000 and < 600000

2.2.3 索引本身失效，统计信息不真实

索引有自我维护的能力。
对于表内容变化比较频繁的情况下，有可能会出现索引失效。
一般是删除重建

现象:
有一条select语句平常查询时很快,突然有一天很慢,会是什么原因
select? —>索引失效,，统计数据不真实

innodb_index_stats  
innodb_table_stats  

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

例子：
错误的例子：select * from test where id-1=9;
正确的例子：select * from test where id=10;
算术运算
函数运算
子查询

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

这样会导致索引失效. 错误的例子：
mysql> alter table tab add index inx_tel(telnum);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql>
mysql> desc tab;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id    | int(11)    | YES  |    | NULL    |      |
| name  | varchar(20) | YES  |    | NULL    |      |
| telnum | varchar(20) | YES  | MUL | NULL    |      |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> select * from tab where telnum='1333333';
+------+------+---------+
| id  | name | telnum  |
+------+------+---------+
|    1 | a    | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> select * from tab where telnum=1333333;
+------+------+---------+
| id  | name | telnum  |
+------+------+---------+
|    1 | a    | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> explain  select * from tab where telnum='1333333';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref  | rows | Extra                |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+

|  1 | SIMPLE      | tab  | ref  | inx_tel      | inx_tel | 63      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
mysql> explain  select * from tab where telnum=1333333;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra      |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tab  | ALL  | inx_tel      | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain  select * from tab where telnum=1555555;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra      |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tab  | ALL  | inx_tel      | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain  select * from tab where telnum='1555555';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref  | rows | Extra                |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | tab  | ref  | inx_tel      | inx_tel | 63      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
mysql>

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

EXPLAIN  SELECT * FROM teltab WHERE telnum  <> '110';
EXPLAIN  SELECT * FROM teltab WHERE telnum  NOT IN ('110','119');


mysql> select * from tab where telnum <> '1555555';
+------+------+---------+
| id  | name | telnum  |
+------+------+---------+
|    1 | a    | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> explain select * from tab where telnum <> '1555555';

单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit
or或in  尽量改成union
EXPLAIN  SELECT * FROM teltab WHERE telnum  IN ('110','119');
改写成：
EXPLAIN SELECT * FROM teltab WHERE telnum='110'
UNION ALL
SELECT * FROM teltab WHERE telnum='119'

2.2.7 like “%_” 百分号在最前面不走

EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%'  走range索引扫描
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110'  不走索引


%linux%类的搜索需求，可以使用elasticsearch+mongodb 专门做搜索服务的数据库产品

3. 彩蛋：优化器针对索引的算法

3.1 自优化能力：

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

MySQL的InnoDB引擎，能够创建只有Btree。
AHI作用：
自动评估"热"的内存索引page,生成HASH索引表。
帮助InnoDB快速读取索引页。加快索引读取的效果。
相当与索引的索引。

3.1.2 MySQL索引的自优化-Change buffer

比如insert，update，delete 数据。
对于聚簇索引会立即更新。
对于辅助索引，不是实时更新的。
在InnoDB 内存结构中，加入了insert buffer（会话），现在版本叫change buffer。
Change buffer 功能是临时缓冲辅助索引需要的数据更新。
当我们需要查询新insert 的数据，会在内存中进行merge(合并)操作，此时辅助索引就是最新的。

id name age gender
10001 zs 18 m

3.1.3 8.0 版本 自优化能力

不可见索引。invisable index
倒叙索引。
(a,b desc )
select * from t1 where a order by b desc ;

3.2 可选的优化器算法-索引

3.2.1 优化器算法查询

mysql> 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

3.2.2 如何修改?

1. my.cnf 
2. set global optimizer_switch='batched_key_access=on';
3. hints 
SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1
FROM t3 WHERE f1 > 30 AND f1 < 33;
SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ NO_ICP(t1, t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) */ * FROM t1 ...;
EXPLAIN SELECT /*+ NO_ICP(t1) */ * FROM t1 WHERE ...;

https://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html

3.2.3 index_condition_pushdown

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

idx(a,b,c)
where a = and b > and c =

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

测试1: ICP开启时
idx(k1,k2)
mysql> SET global optimizer_switch='index_condition_pushdown=ON'
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where  k1 > 'Za' and k2='rsEF'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose

测试2:ICP关闭时：
idx(k1,k2)
mysql> SET global optimizer_switch='index_condition_pushdown=OFF'
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where  k1 > 'Za' and k2='rsEF'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose

测试3:优化索引 
idx(k2,k1)
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where  k1 > 'Za' and k2='rsEF'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose

具体参考 ： 
https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html
https://mariadb.com/kb/en/index-condition-pushdown/

3.4 MRR : Multi Range Read

3.4.1 作用： 减少回表。

3.4.2 开关方法：

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

3.4.3 区别

具体参考 ： 
https://dev.mysql.com/doc/refman/5.7/en/mrr-optimization.html
https://mariadb.com/kb/en/multi-range-read-optimization/


压力测试： 
mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query="select * from world.city where name in ('Aachen','Aalborg','Aba','Abadan','Abaetetuba')" engine=innodb --number-of-queries=20000 -uroot -p123 -verbose

3.5 SNLJ

例子： 
A  join  B 
on   A.xx = B.yy 

伪代码：
for each row in A matching range {
	block 
    for each row in B {
      A.xx = B.yy ，send to client
    }
 
}

以上例子，可以通过 left join 强制驱动表。

3.6 BNLJ

在 A和B关联条件匹配时，不再一次一次进行循环。
而是采用一次性将驱动表的关联值和非驱动表匹配.一次性返回结果
主要优化了， CPU消耗，减少了IO次数

In EXPLAIN output,
use of BNL for a table is signified
when the Extra value contains Using join buffer (Block Nested Loop)

3.7 BKA

主要作用，使用来优化非驱动表的关联列有辅助索引。
BNL+ MRR的功能。
开启方式： 
mysql> set global optimizer_switch='mrr=on,mrr_cost_based=off';
mysql> set global optimizer_switch='batched_key_access=on';
重新登陆生效。