美文网首页
MySQL-索引及执行计划管理

MySQL-索引及执行计划管理

作者: 文娟_狼剩 | 来源:发表于2019-08-13 00:55 被阅读0次

    1.1 索引的作用

    类似于一本书的目录,起到优化查询的功能

    1.2 索引的类型(笔试)

    BTREE索引 (最常用)
    RTREE索引
    HASH索引
    全文索引

    1.3 BTREE索引的细分类(算法)

    B-TREE
    B+TREE
    B*TREE(默认)

    1.4 BTREE索引的功能分类

    聚集索引(集群索引)
    辅助索引(二级索引) ※※※※

    1.5 Btree是如何构建的?

    1.5.1 辅助索引
    建立索引:
    alter table t1 add index idx_name(name);
    1>将name列的所有值取出来,进行自动排序
    2>将排完序的值均匀的落到16KB叶子节点数据页中,并将索引键值所对应的数据行的聚集索引列值
    3>向上生成枝节点和根节点
    
    1.5.2 聚集索引
    1>默认是按照主键生成聚集索引.没有主键,存储引擎会使用唯一键;如果都没有,会自动生成隐藏的聚集索引.
    2>数据在存储是,就会按照聚集索引的顺序存储到磁盘的数据页.
    3>由于本身数据就是有序的,所以聚集索引构建时,不需要进行排序.
    4>聚集索引直接将磁盘的数据页,作为叶子节点.
    5>枝节点和根节点只会调取下层节点主键的最小值
    
    1.5.3 辅助索引和聚集索引的区别?(重点)

    1.6 辅助索引细分

    单列辅助索引
    联合索引
    唯一索引

    1.7 索引树的高度(越低越好)

    1.7.1 表的数据量级大

    (1)分区表
    (2)分库分表(分布式架构)----目前最流行的

    1.7.2 索引键值的长度

    (1)尽可能的选择列值短的列创建索引
    (2)采用前缀索引

    1.7.3 数据类型的选择(选择合适)

    1.8 索引的管理

    1.8.1 准备压力测试的数据
    create database test charset utf8mb4;
    use test;
    create table t100w (id int,num int,k1 char(2),k2 char(4),dt timestamp);
    delimiter //
    create  procedure rand_data(in num int)
    begin
    declare str char(62) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    declare str2 char(2);
    declare str4 char(4);
    declare i int default 0;
    while i<num do
    set str2=concat(substring(str,1+floor(rand()*61),1),substring(str,1+floor(rand()*61),1));
    set str4=concat(substring(str,1+floor(rand()*61),2),substring(str,1+floor(rand()*61),2));
    set i=i+1;
    insert into t100w values (i,floor(rand()*num),str2,str4,now());
    end while;
    end;
    //
    delimiter ;
    
    插入100w条数据:
    
    call rand_data(1000000);
    
    commit;
    
    1.8.2 进行压力测试

    1>没有添加索引之前的测试

    [root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='LMhi'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
    mysqlslap: [Warning] Using a password on the command line interface can be insecure.
    Benchmark
        Running for engine rbose
        Average number of seconds to run all queries: 683.581 seconds
        Minimum number of seconds to run all queries: 683.581 seconds
        Maximum number of seconds to run all queries: 683.581 seconds
        Number of clients running queries: 100
        Average number of queries per client: 20
    
    [root@db01 ~]# 
    

    2>添加索引之后的测试

    alter table t100w add index idx_k2(k2);
    
    [root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='LMhi'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
    mysqlslap: [Warning] Using a password on the command line interface can be insecure.
    Benchmark
        Running for engine rbose
        Average number of seconds to run all queries: 4.542 seconds
        Minimum number of seconds to run all queries: 4.542 seconds
        Maximum number of seconds to run all queries: 4.542 seconds
        Number of clients running queries: 100
        Average number of queries per client: 20
    
    [root@db01 ~]#
    
    1.8.2 索引命令操作

    1>查询索引

    desc student;
    show index from student\G
    

    Key:
      PRI(主键)
      UNI(唯一索引)
      MUL(辅助索引)

    2>创建索引
    (1)创建单列索引:

    alter table student add index idx_name(sname);
    

    (2)创建联合索引:

    alter table student add index idx_sname_sage_ssex(sname,sage,ssex);
    

    (3)创建前缀索引:

    alter table student add index idx_sname(sname(5));
    

    (4)创建唯一索引:

    alter table student add unique index idx_tel(telnum);
    

    3>删除索引

    alter table student drop index idx_name;
    

    1.9 explain(desc)-----重点

    1.9.1 作用:

    抓取优化器优化过的执行计划

    1.9.2 执行计划的分析
    wenjuan[test]>wenjuan[test]>desc select * from t100w where k2 != 'asdf';
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    |  1 | SIMPLE      | t100w | NULL       | ALL  | idx_k2        | NULL | NULL    | NULL | 997381 |    86.34 | Using where |
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)
    
    wenjuan[test]>
    
    说明:
    table:以上SQL语句涉及到的表   ***
    type:查询的类型(全表扫描---ALL、索引扫描、查不到数据---NULL)  *****
    possible_keys:可能会用到的索引 ***
    key:使用到的索引 ****
    key_len:索引的覆盖长度  *****
    Extra:额外的信息 ****
    
    1.9.3 type详细说明(重点)

    1> ALL: 全表扫描 , 不会走任何索引
    (1)查询条件,没建索引

    oldguo[test]>explain  select * from test.t100w where k2='VWtu'
    oldguo[test]>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 |
    +-------+-----------+------+-----+-------------------+-----------------------------+
    

    (2)有索引不走
    以下这几种情况:

    desc select * from t100w where k2 != 'asdf';
    desc select * from t100w where k2 like '%aa%';
    desc select * from t100w where k2 not in ('asda','asas');
    desc select * from t100w;
    desc select * from t100w where 1=1;
    

    注意: !=和not in 如果是主键列,是走range.
    2> index 全索引扫描

    wenjuan[test]>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_k2 | 17      | NULL | 997381 |   100.00 | Using index |
    +----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)
    
    wenjuan[test]>
    

    ===========从range开始,索引才有价值的============
    3> range 索引范围查询
    (1)所有索引:> ,<, >=, <= ,like , between and

    wenjuan[world]>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       | range | PRIMARY       | PRIMARY | 4       | NULL |    9 |   100.00 | Using where |
    +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)
    
    wenjuan[world]>
    
    
    wenjuan[world]>desc select * from city where countrycode like 'CH%';
    +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
    | id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
    +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
    |  1 | SIMPLE      | city  | NULL       | range | CountryCode   | CountryCode | 3       | NULL |  397 |   100.00 | Using index condition |
    +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
    1 row in set, 1 warning (0.00 sec)
    
    wenjuan[world]>
    

    in , or

    wenjuan[world]>desc select * from city where countrycode in ('CHN','USA');
    +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
    | id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
    +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
    |  1 | SIMPLE      | city  | NULL       | range | CountryCode   | CountryCode | 3       | NULL |  637 |   100.00 | Using index condition |
    +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
    1 row in set, 1 warning (0.00 sec)
    
    wenjuan[world]>
    

    (2)聚集索引:!= 、not in

    wenjuan[test]>desc select * from world.city where id != 10;
    wenjuan[test]>desc select * from world.city where id not in (10,20);
    

    说明:

    • B+tree 索引能额外优化到:> ,<, >=, <= ,like , between and
    • in 和 or 享受不到b+tree额外的优化效果的,所以我一般情况会将in , or 进行改写:
      desc select * from city where countrycode='CHN' 
      union all 
      select * from city where countrycode='USA';
      
      wenjuan[world]>desc select * from city where countrycode='CHN' 
          -> union all 
          -> select * from city where countrycode='USA'; 
      +----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
      | id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
      +----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
      |  1 | PRIMARY     | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  363 |   100.00 | NULL  |
      |  2 | UNION       | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  274 |   100.00 | NULL  |
      +----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
      2 rows in set, 1 warning (0.00 sec)
      
      wenjuan[world]>
      

    4> ref(辅助索引等值查询):

    wenjuan[world]>desc select * from city where countrycode='CHN';
    +----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
    | id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
    |  1 | SIMPLE      | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  363 |   100.00 | NULL  |
    +----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)
    
    wenjuan[world]>
    

    5> eq_ref:多表连接查询中,非驱动表on的条件列是主键或者唯一键

    wenjuan[world]>desc select a.name,b.name from city as a join country as b on a.countrycode=b.code where a.population<100;
    

    6> const(system):主键或唯一键的等值

    wenjuan[world]>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  |
    +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)
    
    wenjuan[world]>
    

    7> NULL: 获取不到数据

    wenjuan[world]>desc select * from city where id=100000000000000;
    
    1.9.4 possible_keys(可能会用到的索引)
    NULL:没有和查询条件匹配的索引条目
    有值:有查询条件匹配的索引条目,但是没走,大部分原因是语句查询方式不符合索引应用条件
    
    1.9.5 key(使用到的索引)

    最终使用的索引,可以帮助我们判断是否走了合适的索引

    1.9.6 key_len(索引的覆盖长度):在联合索引应用的判断时,会经常看
    字符集 一个字符最大存储长度占几个字节
    utf8 3
    utfmb4 4
    不同类型索引 有not nulll时(非空) 没有指定not null时(为空)
    int 最大4个字节 最大4+1个字节
    tinyint 最大1个字节 最大1+1个字节
    char(10) 最大4*10个字节 最大4*10+1个字节
    varchar(10) 最大4*10+2个字节 最大4*10+2+1个字节

    说明:

    • 有非空约束时,key_length就是最大字节长度
    • 在没有非空约束时: 字符最大长度+1
    • varchar类型,需要额外在最大字符长度+2(存储字符长度的最长度占位)

    联合索引准备:

    -- 创建测试表
    create table t1(a int not null ,b char(10) not null ,c varchar(10) not null )charset utf8mb4;
    wenjuan[test]>desc t1;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | a     | int(11)     | NO   |     | NULL    |       |
    | b     | char(10)    | NO   |     | NULL    |       |
    | c     | varchar(10) | NO   |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    3 rows in set (0.00 sec)
    
    wenjuan[test]>
    
    -- 创建索引
    wenjuan[test]>alter table t1 add index idx(a,b,c);
    Query OK, 0 rows affected (0.25 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    wenjuan[test]>show index from t1;
    +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
    +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | t1    |          1 | idx      |            1 | a           | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
    | t1    |          1 | idx      |            2 | b           | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
    | t1    |          1 | idx      |            3 | c           | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
    +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    3 rows in set (0.00 sec)
    
    wenjuan[test]>
    

    (1)最完美的查询情况

    desc select * from t1 where a=1 and b='a' and c='a';
    desc select * from t1 where b='1' and a=1 and c='a';
    desc select * from t1 where c='1' and a=1 and b='a';
    desc select * from t1 where c='1' and b='a' and a=1;
    desc select * from t1 where a=1 and c='a' and b='a';
    desc select * from t1 where b='1' and c='a' and a=1;
    

    结论:
    当查询条件中,包含了索引列中所有的列条件,并且都是等值的查询,那么无关排列顺序,都可以走全联合索引优化;
    原因:优化器会自动调整顺序,达到最佳的优化效果。所以,我们重点需要关注的是联合索引建立的顺序,从左到右,唯一值越多的列放在最左边

    (2)部分索引(查询条件中,哪些因素会key_len长度)

    --------1>按照索引的建立顺序,在查询条件中,少了任意一个中间列,后续列都无法走索引
    wenjuan[test]>desc select * from t1 where a=1 and c='a';
    +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+--------------------------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra                    |
    +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+--------------------------+
    |  1 | SIMPLE      | t1    | NULL       | ref  | idx           | idx  | 4       | const |    1 |   100.00 | Using where; Using index |
    +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+--------------------------+
    1 row in set, 1 warning (0.00 sec)
    
    wenjuan[test]>
    
    wenjuan[test]>desc select * from t1 where a=1 and b like 'a%' and c='a';
    -------2>在条件查询中间,出现不等值查询时,从不等值列开始,所有列都无法使用联合索引 (暂存)
    优化方法:将不等值列放在最后.
    
    -------3>如果有多子句的条件查询(必须是联合索引),按照子句的执行顺序,建立联合索引.
    
    1.9.7 Extra:额外的信息

    using filesort ===> 排序不走索引,走的额外排序

    wenjuan[(none)]>use world;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    wenjuan[world]>desc select * from world.city where countrycode='CHN' order by population;
    +----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
    | id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra                                 |
    +----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
    |  1 | SIMPLE      | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  363 |   100.00 | Using index condition; Using filesort |
    +----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
    1 row in set, 1 warning (0.00 sec)
    
    wenjuan[world]>
    
    原因:在 group  by ,order by,distinct等.
    一般优化的方法是,和where条件的列建立联合索引
    

    扩展:

    以json的方式显示执行计划,可以通过used_key_parts查看使用的索引部分

    wenjuan[test]>desc format=json select * from t1 where b='1' and c='a' and a=1;
    +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | EXPLAIN                                                                                                                                                                                                                                        |
    +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | {
      "query_block": {
        "select_id": 1,
        "cost_info": {
          "query_cost": "1.20"
        },
        "table": {
          "table_name": "t1",
          "access_type": "ref",
          "possible_keys": [
            "idx"
          ],
          "key": "idx",
          "used_key_parts": [
            "a",
            "b",
            "c"
          ],
          "key_length": "86",
          "ref": [
            "const",
            "const",
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "0.20",
            "prefix_cost": "1.20",
            "data_read_per_join": "88"
          },
          "used_columns": [
            "a",
            "b",
            "c"
          ]
        }
      }
    } |
    +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set, 1 warning (0.00 sec)
    
    wenjuan[test]>
    

    未完……

    相关文章

      网友评论

          本文标题:MySQL-索引及执行计划管理

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