美文网首页
MySQL基础优化-索引及执行计划

MySQL基础优化-索引及执行计划

作者: 肥四_F4 | 来源:发表于2020-07-11 16:02 被阅读0次

    1.什么是索引?
    相当于一本书的目录。优化查询
    2.MySQL 支持的索引类型(算法)

    Btree : 平衡多叉树
    Rtree : 空间树索引
    Hash  : HASH索引
    fulltext: 全文索引
    

    3.数据查找算法介绍

    二叉树
    红黑树
    Btree: Blance Tree
    
    1. BTree 的查找算法(见图)
    B-Tree:每次查询都从根节点开始,不能从枝节点或者叶子节点之间直接转换
    B+Tree:实际上是在枝节点上添加了双向指针信息,从而减少对根节点的IO消耗
    B*tree  :实际上是在非根节点上添加了双向指针信息,从而减少对根节点和枝节点的IO消耗
    
    B6E99DF40DF7A76F6F1DA9947B62E5AF.jpg
    1. MySQL Btree 索引的应用
      5.1聚簇索引(聚集索引、主键索引)
      前提:
      0>InnoDB存储引擎的表才会有聚簇索引
      1>有主键,主键就是聚簇索引
      2>没有主键,选择唯一键作为聚簇索引
      3>生成一个隐藏列(DB_ROW_ID,6字节),作为聚簇索引

    作用:
    1. 聚簇(区)索引,组织表(IOT): 所有数据在插入时,都按照ID(主键)属性,在相邻数据页上有序存储数据。
    2. 加快存储数据,加快通过索引作为查找条件的查询。

    参考:

        https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html
    

    聚簇索引构建过程(见图)


    1.png
    1. 叶子节点 :
      由于存储数据时,已经按照ID顺序在各个数据页中有序存储了,所以《原表数据》所在数据页被作为叶子节点。
    2. 内部节点(非叶子节点--->枝节点):
      获取叶子节点ID范围+指针。
    3. 根节点:
      获取非叶子节点 ID范围+指针

    5.2辅助索引
    构建过程:
    1. 叶子节点构建:
    提取索引列值+ID ,进行从小到大排序(辅助索引列值),存储到各个数据页,作为叶子节点。
    2. 非叶子节点(internel node )
    提取下层的辅助索引列值范围+指针。
    3. 根节点:
    提取下层节点的范围+指针。
    对于查询的优化:

        1. 通过辅助索引列,进行条件查询,根据辅助索引BTREE快速锁定条件值对应的ID。
        2. 通过得出的ID值,回到聚簇索引继续查询到具体的数据行(回表)。
    
    2.png
    1. 辅助索引分类
      6.1. 普通单列
      6.2. 联合索引
      idx(a,b)提取出来再排序,先按a列排,如果a列用相同的多个值的话,就按照b列的值进行排列(先拿最左列排)
      叶子节点:
      id+a+b ,按照a和b进行排序,生成叶子节点
      枝节点和根节点:
      只会包含最左列(a列)的范围+指针(最左原则)
      注意: 最左原则
      1. 建索引,最左列重复值少的。
      2. 查询条件中,必须包含最左列。
      6.3. 唯一索引
      unique key

    6.4. 前缀索引
    idex(test(10))

    1. 索引树高度影响因素
      7.1 列值长度
      前缀索引。
      7.2 数据量
      分区表 。
      定期归档表。
      分布式架构:分库、分表。
      7.3 数据类型
      定长:char(20)
      变长:varchar(20)
    1. 回表问题的探讨?
      什么是回表?
      辅助索引查找完成----> 聚簇索引查询过程。

    回表会带来的问题?
    IO增多: 量、次数

    如何减少回表 ?
    使用唯一值索引查询
    联合索引
    覆盖索引:辅助索引完全覆盖到查询结果

    1. 索引应用
      9.1 压测
    mysql> source /root/t100w.sql    #上传数据
    mysql> grant all on *.* to root@'10.0.0.%' identified by '123';    #创建一个远程登陆用户
    

    进行压测

    shell> 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 -h10.0.0.51  -verbose
    
    --concurrency=100  :  模拟同时100会话连接
    --create-schema='test' : 操作的库是谁
    --query="select * from test.t100w where k2='780P'"  :做了什么操作
    --number-of-queries=2000 : 一共做了多少次查询
    
    Average number of seconds to run all queries: 719.431 seconds
    Minimum number of seconds to run all queries: 719.431 seconds
    Maximum number of seconds to run all queries: 719.431 seconds
    

    9.2 查询表的索引

    #查看表索引的三种方法
    desc t100w;  
    -----
     Key 
    -----
    PK     --> 主键(聚簇索引)     
    MUL    --> 辅助索引   
    UK     --> 唯一索引  
         
    mysql> show index from t100w;
    mysql> show creat table city;
    

    9.3 创建索引
    9.3.1 单列辅助索引

    select * from test.t100w where k2='780P'
    优化方式: 
    alter table 表名 add index 索引名(列名);   
    alter table t100w add index idx_k2(k2);
    

    9.3.2 联合索引创建

    mysql> alter table t100w add index idx_k1_num(k1,num);
    

    创建联合索引的时候要根据最左原则进行创建
    对比k2 和 k1 的重复值,少的放在最所测,优化效果更佳

    select count(distinct k1) from t100w;
    select count(distinct k2) from t100w;
    

    9.3.3 前缀索引创建

    #判断前缀长度多少合适:
    select count(distinct(left(name,5)))  from city ;
    select count(distinct name)  from city ;
    #创建前缀索引
    mysql> alter table city add index idx_n(name(5));
    

    9.4 删除索引

    #语法:alter tabel 表名  drop index 索引名;
    alter table city drop index idx_n;
    
    1. 执行计划查看和分析
      10.1 什么是执行计划?
      优化器优化后的“执行方案”。

    10.2 作用 ?
    a. 语句执行之前,通过执行计划,防患于未然。
    b. 对于有性能问题的语句,进行分析。得出优化方案。

    10.3 获取SQL的执行计划 。
    Select 、 update 、 delete

    #查看执行计划的两种方法
    mysql> explain select * from test.t100w where num=279106  and k2='VWtu';
    mysql> desc select * from test.t100w where num=279106  and k2='VWtu';
    +----+-------------+-------+------------+------+----------------+------+---------+-------------+------+----------+-------+
    | id | select_type | table | partitions | type | possible_keys  | key  | key_len | ref         | rows | filtered | Extra |
    +----+-------------+-------+------------+------+----------------+------+---------+-------------+------+----------+-------+
    |  1 | SIMPLE      | t100w | NULL       | ref  | ix_k2,idx,idx1 | idx1 | 22      | const,const |    1 |   100.00 | NULL  |
    +----+-------------+-------+------------+------+----------------+------+---------+-------------+------+----------+-------+
    

    10.4 执行计划介绍

    table          : 操作的表
    type           : 查询索引的类型(ALL、index、range、ref 、eq_ref、const(system))
    possible_keys  :  可能会走的。
    key            : 最终选择的索引。
    key_len        : 联合索引覆盖长度。
    rows           : 此次查询需要扫描的行数(预估值)。
    Extra          : 额外信息。
    

    10.5 type 详解
    10.5.1 ALL 全表扫描
    a. 查询条件没有建索引

    mysql> desc t100w;
    +-------+-----------+------+-----+-------------------+-----------------------------+
    | Field | Type      | Null | Key | Default           | Extra                       |
    +-------+-----------+------+-----+-------------------+-----------------------------+
    | id    | int(11)   | YES  |     | NULL              |                             |
    | num   | int(11)   | YES  |     | NULL              |                             |
    | k1    | char(2)   | YES  |     | NULL              |                             |
    | k2    | char(4)   | YES  |     | NULL              |                             |
    | dt    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
    +-------+-----------+------+-----+-------------------+-----------------------------+
    
    5 rows in set (0.00 sec)
    
    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       | ALL  | NULL          | NULL | NULL    | NULL | 986679 |    10.00 | Using where |
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)
    

    b. 有索引,但查询语句不能走的情况。

    mysql> alter table t100w add index idx(k2);
    mysql> desc select * from t100w where k2 like '%80P';
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    |  1 | SIMPLE      | t100w | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 986679 |    11.11 | Using where |
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    
    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       | ALL  | idx           | NULL | NULL    | NULL | 986679 |    73.37 | Using where |
    
    
    mysql> desc select * from t100w where k2 not in ('780P');
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    |  1 | SIMPLE      | t100w | NULL       | ALL  | idx           | NULL | NULL    | NULL | 986679 |    73.37 | Using where |
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    
    

    10.5.2 index 全索引扫描

    mysql> desc select k2 from t100w;
    +----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-------------+
    | id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
    +----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-------------+
    |  1 | SIMPLE      | t100w | NULL       | index | NULL          | idx  | 17      | NULL | 986679 |   100.00 | Using index |
    +----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-------------+
    

    10.5.3 range 索引范围扫描

    mysql> desc select * from city where id<100;
    mysql> desc select  * from city where countrycode like 'CH%';
    mysql> desc select  * from city where countrycode='CHN' or countrycode='USA';
    mysql> desc select  * from world.city where countrycode in ('CHN','USA');
    

    union all 改写:

    desc 
    select  * from city where countrycode='CHN' union all  select  * from city where countrycode='USA';
    
    注意: 
        如果重复值过多的话,可能改写的效果不佳。
    
    通过压测: 判断改写效果。
    
     mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query="select  * from world.city where countrycode in ('CHN','USA'); " engine=innodb --number-of-queries=2000  -uroot -p123 -h10.0.0.51  -verbose
    
     mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query="select  * from city where countrycode='CHN' union all  select  * from city where countrycode='USA'; " engine=innodb --number-of-queries=2000  -uroot -p123 -h10.0.0.51  -verbose
    

    10.5.4 ref : 辅助索引等值查询

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

    10.5.5 eq_ref: 多表连接
    非驱动表的连接条件是主键或唯一键。是多表连接中性能最好的查询方法。
    拿结果集小的作为i驱动表

    mysql> desc select a.name,b.name from city as a  join  country as b on a.countrycode=b.code where a.population<100;
    +----+-------------+-------+------------+--------+-----------------+---------+---------+---------------------+------+----------+-----------------------+
    | id | select_type | table | partitions | type   | possible_keys   | key     | key_len | ref                 | rows | filtered | Extra                 |
    +----+-------------+-------+------------+--------+-----------------+---------+---------+---------------------+------+----------+-----------------------+
    |  1 | SIMPLE      | a     | NULL       | range  | CountryCode,idx | idx     | 4       | NULL                |    1 |   100.00 | Using index condition |
    |  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY         | PRIMARY | 3       | world.a.CountryCode |    1 |   100.00 | NULL                  |
    +----+-------------+--
    
    #left join 强制左表为驱动表
    mysql> desc select a.name,b.name from city as a  left join  country as b on a.countrycode=b.code;
    +----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------+
    | id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                 | rows | filtered | Extra |
    +----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------+
    |  1 | SIMPLE      | a     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                | 4188 |   100.00 | NULL  |
    |  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 3       | world.a.CountryCode |    1 |   100.00 | NULL  |
    +----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------+
    2 rows in set, 1 warning (0.00 sec)
    

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

    mysql> desc select * from city where id=10;
    +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
    | 1 | SIMPLE | city | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
    +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

    10.5.7 NULL
    mysql> desc select * from city where id=1000000;
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
    | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+

    10.6 key_len 说明
    10.6.0 计算方式
    a. 介绍:
    索引的应用长度
    b. 作用:
    判断联合索引的覆盖长度。
    idx(a,b,c)

    c. 如何计算key_len
    总长度的计算:
    a+b+c

    d. 每个索引列占用多长?
    每个列key_len,是这个列的《最大》预留长度 。
    影响因素:
    1. 数据类型
    2. not null
    3. 字符集(字符串类型)

    3.png

    计算

    create table test (
    id int  not null primary key auto_increment,
    a int not null ,
    b char(10) not null ,
    c char(5) ,
    d varchar(20) not null ,
    e varchar(10)
    )engine=innodb charset=utf8mb4;
    
    alter table test add index idx(a,b,c,d,e);
    
    
    4+40+21+82+43=190
    

    计算方法

    mysql> desc select * from test where a=1 ;
    +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
    +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
    |  1 | SIMPLE      | test  | NULL       | ref  | idx           | idx  | 4       | const |    1 |   100.00 | Using index |
    +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql> desc select * from test where a=1 and b='aa';
    +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref         | rows | filtered | Extra       |
    +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+
    |  1 | SIMPLE      | test  | NULL       | ref  | idx           | idx  | 44      | const,const |    1 |   100.00 | Using index |
    +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql> desc select * from test where a=1 and b='aa' and c='aa' ;
    +----+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref               | rows | filtered | Extra       |
    +----+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------------+
    |  1 | SIMPLE      | test  | NULL       | ref  | idx           | idx  | 65      | const,const,const |    1 |   100.00 | Using index |
    +----+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql> desc select * from test where a=1 and b='aa' and c='aa' and d='aa' ;
    +----+-------------+-------+------------+------+---------------+------+---------+-------------------------+------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref                     | rows | filtered | Extra       |
    +----+-------------+-------+------------+------+---------------+------+---------+-------------------------+------+----------+-------------+
    |  1 | SIMPLE      | test  | NULL       | ref  | idx           | idx  | 147     | const,const,const,const |    1 |   100.00 | Using index |
    +----+-------------+-------+------------+------+---------------+------+---------+-------------------------+------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql> desc select * from test where a=1 and b='aa' and c='aa' and d='aa' and  e='aa';
    +----+-------------+-------+------------+------+---------------+------+---------+-------------------------------+------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref                           | rows | filtered | Extra       |
    +----+-------------+-------+------------+------+---------------+------+---------+-------------------------------+------+----------+-------------+
    |  1 | SIMPLE      | test  | NULL       | ref  | idx           | idx  | 190     | const,const,const,const,const |    1 |   100.00 | Using index |
    +----+-------------+-------+------------+------+---------------+------+---------+-------------------------------+------+----------+-------------+
    

    10.6.1 联合索引应用细节

    a. 联合索引全覆盖
    idx(num,k1,k2) 索引 相当于index(num,k1)、index(num)

    mysql> desc select * from t100w  where num=641631  and k1='At'  and k2='rsEF';
    mysql> desc select * from t100w  where k1='At'  and   num=641631  and  k2='rsEF';
    mysql> desc select * from t100w  where k1='At'  and   num=641631  and  k2 like 'rsE%';
    

    b. 部分覆盖

    mysql> desc select * from t100w  where num=641631  and k1='At' ;
    mysql> desc select * from t100w  where   k1='At'  and num=641631 ;
    #中间缺了k1所以只走num,后面就不能走索引了k2列走不到索引
    mysql> desc select * from t100w  where  num=641631  and  k2 like 'rsE%';
    mysql> desc select * from t100w  where num=641631  and k1 > 'AZ'  and k2='rsEF';
    mysql> desc select * from t100w  where num=641631  and k1 != 'AZ'  and k2='rsEF';
    

    c. 完全不覆盖

    #索引遵循最左原则的,这里没有num列,所以不会走索引的
    mysql> desc select * from t100w  where k1='At'  and     k2 like 'rsE%';
    

    d. 在多子句 必须得使用联合索引

    where a   order by  b 
    where a   group by  b   order by   xxx
    

    10.7 Extra
    using where : 此次查询中有部分条件是没有走索引的。
    如果出现以上信息,说明where 条件,索引设计问题或者语句有问题。
    using filesort : 出现文件排序,order by 、 group by 、 distinct ...

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

    11.2 不走索引的情况(开发规范)

    11.2.1 没有查询条件,或者查询条件没有建立索引
    select * from t1 ;
    select * from t1 where id=1001 or 1=1;

    11.2.2 查询结果集是原表中的大部分数据,应该是15-25%以上。
    查询的结果集,超过了总数行数25%,优化器觉得就没有必要走索引了。
    MySQL的预读功能有关。

    可以通过精确查找范围,达到优化的效果。
    1000000

    大于 > 500000 and

    11.2.3 索引本身失效,统计信息不真实(过旧)
    索引有自我维护的能力。
    对于表内容变化比较频繁的情况下,有可能会出现索引失效。
    一般是删除重建

    现象:

    有一条select语句平常查询时很快,突然有一天很慢,会是什么原因
    select?  --->索引失效,统计数据不真实
    
    innodb_index_stats  
    innodb_table_stats  
    结局方法
    #1.立即更新统计信息为最新的
    # mysql> ANALYZE TABLE  表名
    mysql> ANALYZE TABLE world.city;  
    #2.重建索引
    

    11.2.4 查询条件使用函数在索引列上,或者对索引列进行运算,运算包括(+,-,*,/,! 等)
    例子:
    错误的例子:select * from test where id-1=9;
    正确的例子:select * from test where id=10;

    算术运算  函数运算  子查询 都会导致索引失效或者不走索引
    

    11.2.5 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.
    11.2.6 <> ,not in 不走索引(辅助索引)
    11.2.7 like "%_" 百分号在最前面不走

    12. 彩蛋(扩展):优化器针对索引的算法

    12.1 自优化能力:
    12.1.1 MySQL索引的自优化-AHI(自适应HASH索引)
    a. 限制
    MySQL的InnoDB引擎,能够手工创建只有Btree。
    AHI 只有InnoDB表会有,MySQL自动维护的。

    AHI作用: 
    自动评估"热"的内存索引page,生成HASH索引表。
    帮助InnoDB快速读取索引页。加快索引读取的效果。
    相当与索引的索引。
    
    4.png

    参考文章:

    https://dev.mysql.com/doc/refman/5.7/en/innodb-adaptive-hash.html
    https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html

    12.1.2 MySQL索引的自优化-Change buffer
    限制:
    比如insert,update,delete 操作时会使用change buffer。
    对于聚簇索引会直接更新叶子节点。
    对于辅助索引,不是实时更新的。
    insert into t1 (id,name,age) values(33,'d',18)

    在InnoDB 内存结构中,加入了insert buffer(会话),现在版本叫change buffer。
    Change buffer 功能是临时缓冲辅助索引需要的数据更新。
    当我们需要查询新insert 的数据,会在内存中进行merge(合并)操作,此时辅助索引就是最新的。

    参考:
    https://dev.mysql.com/doc/refman/5.7/en/innodb-change-buffer.html

    12.1.3 8.0 版本索引的新特性

    a. 不可见索引。invisable/visable index
    针对优化器不可见。但是索引还在磁盘存在,还会自动维护。
    对于索引维护时,不确定索引是否还有用。这时可以临时设定为invisable。

    b. 倒序索引。
    select * from t1 where c = order by a ASC , b desc
    idx(c,a, b desc)

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

    12.2.1 优化器算法查询

    select @@optimizer_switch;        #查询默认优化器算法=show variables like '%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
    

    12.2.2 如何修改?

    1. my.cnf
    #修改配置文件
    optimizer_switch='batched_key_access=on'
    
    #在线修改
     set global optimizer_switch='batched_key_access=on';
    
    1. 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

    12.2.3 index_condition_pushdown (ICP)
    介绍: 索引下推 ,5.6+ 加入的特性
    idx(a,b,c)
    where a = and b 不等值 and c =

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

    测试1: ICP开启时
    idx(k1,k2)

    #开启ICP参数
    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 like '%sE%'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
    
    Benchmark
        Running for engine rbose
        Average number of seconds to run all queries: 1.114 seconds
        Minimum number of seconds to run all queries: 1.114 seconds
        Maximum number of seconds to run all queries: 1.114 seconds
        Number of clients running queries: 100
        Average number of queries per client: 20
    
    
    Benchmark
        Running for engine rbose
        Average number of seconds to run all queries: 6.945 seconds
        Minimum number of seconds to run all queries: 6.945 seconds
        Maximum number of seconds to run all queries: 6.945 seconds
        Number of clients running queries: 100
        Average number of queries per client: 200
    

    测试2:ICP关闭时:
    idx(k1,k2)

    #开启ICP参数
    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 like '%sE%'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
    
    Benchmark
        Running for engine rbose
        Average number of seconds to run all queries: 3.125 seconds
        Minimum number of seconds to run all queries: 3.125 seconds
        Maximum number of seconds to run all queries: 3.125 seconds
        Number of clients running queries: 100
        Average number of queries per client: 20
    
    
    Benchmark
        Running for engine rbose
        Average number of seconds to run all queries: 31.102 seconds
        Minimum number of seconds to run all queries: 31.102 seconds
        Maximum number of seconds to run all queries: 31.102 seconds
        Number of clients running queries: 100
        Average number of queries per client: 200
    

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

    12.4 MRR : Multi Range Read

    12.4.1 作用: 理论上减少回表。
    辅助索引扫描后,得到聚簇索引值,统一缓存到read_rnd_buffer,进行排序,再次回表。
    

    12.4.2 开关方法:

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

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

    压力测试: 
    alter table world.city add index idx_n(name);
    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
    
    no-mrr: 
    Benchmark
        Running for engine rbose
        Average number of seconds to run all queries: 2.909 seconds
        Minimum number of seconds to run all queries: 2.909 seconds
        Maximum number of seconds to run all queries: 2.909 seconds
        Number of clients running queries: 100
        Average number of queries per client: 200
    
    
    mrr: 
    Benchmark
        Running for engine rbose
        Average number of seconds to run all queries: 3.384 seconds
        Minimum number of seconds to run all queries: 3.384 seconds
        Maximum number of seconds to run all queries: 3.384 seconds
        Number of clients running queries: 100
        Average number of queries per client: 200
    
    

    优化没有最佳实践。

    12.5 SNLJ 普通嵌套循环连接
    例子:
    A join B
    on A.xx = B.yy
    where

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

    }

    例子:
    mysql> desc select * from teacher join course on teacher.tno=course.tno;

    优化器默认优化规则:

    1. 选择驱动表
    默认选择方式(非驱动表): 
        0. 结果集小的表作为驱动表
        按照on的条件列,是否有索引,索引的类型选择。
        1. 在on条件中,优化器优先选择有索引的列为非驱动表。
        2. 如果两个列都有索引,优化器会按照执行的代价去选择驱动表和非驱动表。
    

    for each row in course matching range {
    block
    for each row in teacher {
    course.tno = tracher.tno ,send to client
    }

    }

    关于驱动表选择的优化思路:
    理论支撑:
    mysql> desc select * from city join country on city.countrycode=country.code ;
    mysql> desc select * from city left join country on city.countrycode=country.code ;

    查询语句执行代价:
    mysql> desc format=json select * from city join country on city.countrycode=country.code ;
    mysql> desc format=json select * from city left join country on city.countrycode=country.code ;

    实践检验:
    [root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query="select * from city left join country on city.countrycode=country.code ;" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose

    [root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query="select * from city join country on city.countrycode=country.code ;" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose

    注: 可以通过 left join 强制驱动表。

    12.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)

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

    相关文章

      网友评论

          本文标题:MySQL基础优化-索引及执行计划

          本文链接:https://www.haomeiwen.com/subject/fzpjqktx.html