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

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

作者: 学无止境_9b65 | 来源:发表于2019-07-04 19:57 被阅读0次

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

    一、上节回顾:

    1、作用

    优化查询,类似于书中的目录


    2、算法分类

    btree
    Rtree
    hash
    fulltext
    gis


    3、聚集索引和辅助索引构成逻辑


    4、辅助索引细分

    单列
    多列(联合索引)
    唯一


    5、索引树高度

    1、数据行 分表
    2、索引列值较长 前缀索引
    3、数据类型 char varchar
    4、enum (枚举)能用则用


    二、索引

    1、索引管理

    #添加索引
    mysql> alter table  t100w add index idx_k2(k2);
    
    #压力测试
    [root@mysql ~]# mysqlslap --defaults-file=/etc/my.cnf \
    --concurrency=100 --iterations=1 --create-schema='oldboy' \
    --query="select * from oldboy.t100w where k2='klVW '" engine=innodb \
    --number-of-queries=2000 -uroot -p**** -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: 2.886 seconds
        Minimum number of seconds to run all queries: 2.886 seconds
        Maximum number of seconds to run all queries: 2.886 seconds
        Number of clients running queries: 100
        Average number of queries per client: 20
    
    

    1.1、创建辅助索引

    mysql> alter table  t100w add index idx_k2(k2);
                         表名            索引名(列名)
    
    

    1.2、创建唯一索引

    mysql> alter table t100w add unique index idx_k1(k1);
                        表名       唯一        索引名(列名)
    
    

    1.3、查询索引

    mysql> desc t100w;
    mysql> show index from t100w\G;
    
    
    image

    2、前缀索引

    前缀索引只能使用在字符串上

    2.1 创建前缀索引

    mysql> alter table city add index idx_name(name(5));
    
    

    2.2 创建联合索引

    mysql> alter table city add index idx_co_po(countrycode,population);
    
    

    2.3 查看联合索引

    mysql> show index from city;
    
    

    2.4 删除索引

    mysql> alter table city drop index idx_co_po;
    
    

    三、执行计划

    1、作用

    上线新的查询语句之前,进行提前预估语句的性能
    再出现性能问题时,找到合理的解决思路

    2、执行计划获取
    select * from oldboy.t100w where k2='klVW';

    获取优化器选择后的执行计划

    方法1:

    mysql> desc select * from oldboy.t100w where k2='klVW';
    +----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
    | 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 |  278 |   100.00 | NULL  |
    +----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)
    
    

    方法2:

    mysql>  explain select * from oldboy.t100w where k2='klVW'\G;
    *************************** 1\. row ***************************
               id: 1
      select_type: SIMPLE
            table: t100w                        #表名
       partitions: NULL
             type: ref
    possible_keys: idx_k2                 #可能会使用到的索引 
              key: idx_k2                        #实际上使用的索引
          key_len: 17                            #联合索引的覆盖长度
              ref: const
             rows: 278                            #查询的行数(越小越好)
         filtered: 100.00
            Extra: NULL                         #额外的信息
    1 row in set, 1 warning (0.00 sec)
    
    

    3、执行计划的分析
    type 索引的应用级别

    3.1、ALL:全表扫描,不走索引

    可能情况:
    没建立索引!!
    建立索引不走的!!!

    不走索引演示

    image image image
    mysql> desc select * from t100w;
    
    mysql> desc select * from t100w where k1='aa';
    (辅助索引)
    mysql> desc select * from t100w where k2 != 'aaaa';
    mysql> desc select * from t100w where k2 like '%xt%';
    
    

    3.2、index:全索引扫描
    演示

    image
    mysql> desc select k2 from t100w;
    
    

    3.3、range:索引范围扫描

    辅助索引:>  <   >=   <=  like,in or
    # 以上都是范围扫描
    主键:!=
    
    

    演示

    image image
    mysql> desc select * from world.city where countrycode like 'C%'
    mysql> desc select * from world.city where id!=3000;
    mysql> desc select * from world.city where id>3000;
    
    mysql> desc select * from world.city where countrycode in ('CHN','USA');
    改写为:
    desc
    select * from world.city where countrycode='CHN'
    union all 
    select * from world.city where countrycode='USA';
    
    

    3.4、ref:辅助索引等值查询

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

    3.5、eq_ref:在多表连接查询是on的条件列是唯一索引或主键
    演示:

    image
    mysql> desc select a.name,b.name ,b.surfacearea 
    from city as a 
    join country as b 
    on a.countrycode=b.code 
    where a.population <100;
    
    

    3.6、const,system:主键或唯一键等值查询

    image
    mysql> DESC SELECT * from city where id=10;
    
    

    从下往上性能一个比一个好

    3.7、Extra:null 额外的信息
    using filesort
    演示

    image image

    3.8、explain(desc)使用场景(面试题)

    题目意思:  我们公司业务慢,请你从数据库的角度分析原因
    1.mysql出现性能问题,我总结有两种情况:
    (1)应急性的慢:突然夯住
    应急情况:数据库hang(卡了,资源耗尽)
    处理过程:
    1.show processlist;  获取到导致数据库hang的语句
    2\. explain 分析SQL的执行计划,有没有走索引,索引的类型情况
    3\. 建索引,改语句
    (2)一段时间慢(持续性的):
    (1)记录慢日志slowlog,分析slowlog
    (2)explain 分析SQL的执行计划,有没有走索引,索引的类型情况
    (3)建索引,改语句
    
    

    4. 索引应用规范

    业务
    1.产品的功能
    2.用户的行为
    "热"查询语句 --->较慢--->slowlog
    "热"数据


    4.1 建立索引的原则(DBA运维规范)

    4.1.0 说明

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


    4.1.1 (必须的) 建表时一定要有主键,一般是个无关列
    略.回顾一下,聚集索引结构.


    4.1.2 选择唯一性索引

    唯一性索引的值是唯一的,可以更快速的通过该索引来确定某条记录。
    例如,学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。
    如果使用姓名的话,可能存在同名现象,从而降低查询速度。
    
    优化方案:
    (1) 如果非得使用重复值较多的列作为查询条件(例如:男女),可以将表逻辑拆分
    (2) 可以将此列和其他的查询类,做联和索引
    select count(*) from world.city;
    select count(distinct countrycode) from world.city;
    select count(distinct countrycode,population ) from world.city;
    
    

    4.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
    1、联合索引
    2、(A、B、C)
    
    如果为其建立索引,优化查询
    注:如果经常作为条件的列,重复值特别多,可以建立联合索引。
    
    

    4.1.4 尽量使用前缀来索引

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


    4.1.5 限制索引的数目

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

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

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

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


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


    4.1.9 建索引原则

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

    **4.10 关于联合索引*******

    (1)where A、GROUP BY B、ORDER BY C ----> (A,B,C)
    (2)where   A B C
           (2.1)都是等值,在5.5 以后无关索引顺序,把控一个原则唯一值多的列放在联合索引的左侧
           (2.2)如果有不等值,例如如下情况
              select where A= and B> and C=
              索引顺序,ACB,语句改写为 ACB
    

    相关文章

      网友评论

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

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