• MySQL的索引及执行计划


    MySQL的索引及执行计划

    类似于一本书中的目录,起到优化查询作用

    索引的分类

    B树    默认使用的索引类型
    R树
    Hash
    FullText 
    GIS 索引
    

    Btree索引功能分类

    聚集索引

    如果有PK,MySQL默认使用PK作为聚簇索引
    将来存储数据时,能够保证所有数据行按照主键列的顺序存储到磁盘页中.
    自动生成聚集索引树
    如果没有PK,会自动选择唯一键(UK)
    什么都没有,会自动按照时间戳的hash值作为聚簇索引.
    一张表中只能有一个聚簇索引.
    建议是数字的自增列最佳.
    
    构建过程: 
    (1) 在建表时,设定了主键.MySQL InnoDB 自动将主键作为聚簇索引列
    (2) 在存储数据时,会按照聚簇索引组织存储数据
    (3) InnoDB引擎会将数据行所在的数据页作为叶子节点
    (5) 提取每个叶子节点的最小值,生成枝节点,保留叶子节点指针
    (6) 提取枝节点的最小值和指针,最终生成根节点
    

    辅助索引

    按照业务的查询特点,一般经常需要where  group  order by 条件列
    创建辅助索引.
    构建过程:
    (1) 将索引键值进行自动排序(默认从小到大排序)
    (2) 将排好序的键值+PK,生成叶子节点(16KB)
    (3) 提取每个叶子节点的最小值,生成枝节点,保留叶子节点指针
    (4) 提取枝节点的最小值和指针,最终生成根节点
    
    查询过程:
    一旦我们拿索引键值作为条件发起查询
    (1) 按照Btree查找算法,找到辅助索引叶子节点,获取到PK值
    (2) 按照PK的值回表,通过聚簇索引Btree进一步找到具体数据行
    

    聚集索引和辅助索引的区别

    表中任何一个列都可以创建辅助索引,在你有需要的时候,只要名字不同即可
    在一张表中,聚集索引只能有一个,一般是主键.
    辅助索引,叶子节点只存储索引列的有序值+聚集索引列值.
    聚集索引,叶子节点存储的时有序的整行数据.
    MySQL的表数据存储是聚集索引组织表
    

    辅助索引细分

    #普通的单列辅助索引
    #联合索引
    多个列作为索引条件,生成索引树,理论上设计的好的,可以减少大量的回表查询
    #唯一索引
    索引列的值都是唯一的.
    

    索引数高度

    索引树高度应当越低越好,一般维持在3-4最佳
    #数据行数较多
    分表 : parttion  用的比较少了.
    分片,分布式架构.
    #字段长度
    业务允许,尽量选择字符长度短的列作为索引列
    业务不允许,采用前缀索引.
    #数据类型
    char 和 varchar 
    enum 
    

    索引的命令操作

    #查询索引
    mysql> desc city;
    mysql> show index from cityG
    
    PRI   ==> 主键索引 
    MUL   ==> 辅助索引
    UNI   ==> 唯一索引 
    
    #创建索引
    #单列的辅助索引
    mysql> alter table city add index idx_name(name);
    
    #多列的联合索引
    mysql> alter table city add index idx_c_p(countrycode,population);
    
    #唯一索引
    mysql> alter table city add unique index uidex_dir(district);
    
    #前缀索引
    mysql> alter table city add index idx_dir(district(5));
    
    #删除索引
    mysql> alter table city drop index idx_name;
    

    压力测试

    mysql> create database test;
    mysql> use test
    mysql> source /root/t100w.sql
    
    #未做优化之前测试
    [root@mysql ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='MN89'" engine=innodb --number-of-queries=2000 -uroot -p123456 -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: 1323.035 seconds
    	Minimum number of seconds to run all queries: 1323.035 seconds
    	Maximum number of seconds to run all queries: 1323.035 seconds
    	Number of clients running queries: 100
    	Average number of queries per client: 20
    
    [root@mysql ~]# 
    
    #做优化之后测试
    mysql> alter table t100w add index idx_k2(k2);
    
    [root@mysql ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='MN89'" engine=innodb --number-of-queries=2000 -uroot -p123456 -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.794 seconds
    	Minimum number of seconds to run all queries: 2.794 seconds
    	Maximum number of seconds to run all queries: 2.794 seconds
    	Number of clients running queries: 100
    	Average number of queries per client: 20
    
    [root@mysql ~]# 
    
    

    执行计划分析

    #作用
    将优化器 选择后的执行计划 截取出来.便于管理管判断语句得执行效率.
    #获取执行
    desc   SQL语句
    explain SQL 语句
    
    mysql> desc select * from test.t100w where k2='MN89';
    mysql> explain select * from test.t100w where k2='MN89';
    
    #分析执行计划
    #table        	表名
    #type				
    查询的类型:
    全表扫描	 	: ALL 
    索引扫描    	: index,range,ref,eq_ref,const(system),NULL
    
    index:全索引扫描
    mysql> desc select id from city;
    
    range:索引范围扫描(> < >= <= , between and ,or,in,like )
    mysql> desc select * from city where id>2000;
    
    对于辅助索引来讲,!= 和not in等语句是不走索引的
    对于主键索引列来讲,!= 和not in等语句是走range
    
    ref: 辅助索引等值查询
    mysql> desc  select  * from city where countrycode='CHN'  union all  select  * from city where countrycode='USA';
    
    eq_ref : 多表连接时,子表使用主键列或唯一列作为连接条件
    A join B on a.x = B.y 
    mysql> desc select b.name,a.name,a.population from city as a join country as b on a.countrycode=b.code where a.population<100;
    
    const(system) : 主键或者唯一键的等值查询
    mysql> desc select * from city where id=100;
    
    #possible_key:可能会用到的索引
    
    #key: 真正选择了哪个索引
    
    #key_len: 索引覆盖长度
    varchar(20)  utf8mb4
    1. 能存20个任意字符
    2. 不管存储的时字符,数字,中文,都1个字符最大预留长度是4个字节
    3. 对于中文,1个占4个字节 
    4. 对于数字和字母,1个实际占用大小是1个字节
    select length() from test;
    
    #Extra
    出现Using filesort,说明在查询中有关排序的条件列没有合理的应用索引
    order by
    group by
    distinct 
    union 
    关注key_len应用的长度
    

    联合索引

    #联合索引应用细节
    只要我们将来的查询,所有索引列都是<等值>查询条件下,无关排列顺序 
    唯一值多的列放在最左侧
    abcd 
    acbd
    adbc
    acbd
    等等
    mysql> alter table test add index idx(k1,k2,k3,k4);
    mysql> desc  select * from test where k1='aa' and k2='中国' and k3='aaaa' and k4='中国你好';
    mysql> desc  select * from test where  k2='中国' and k3='aaaa' and k4='中国你好' and k1='aa';
    
    原因: 优化器,自动做查询条件的排列
    
    #不连续部分条件 
    cda   ----> acd   ---> a  -----> idx(c,d,a)
    dba   ----> abd   ---> ab ---->  idx(d,b,a)
    
    #在where查询中如果出现> < >= <= like 
    (1)
    mysql> alter table test add index idx1(k1,k3,k4,k2);
    (2) 
    mysql> desc  select * from test where k1='aa'  and k3='aaaa' and k4='中国你好' and  k2>'中国';
    
    #多子句 	查询,应用联合索引
    mysql> alter table test add index idx3(k1,k2);
    mysql> desc select * from test where k1='aa' order by k2;
    

    不走索引的情况

    (1) 语句本身就是全表扫描
    	select *  from city;
    	select * from city where 1=1;
    (2) 查询条件没建索引
    mysql> desc select * from city where name='dalian';
    (3) 查询条件不满足索引应用逻辑
    mysql> desc select * from city where countrycode like '%CH%';
    如果业务中有大量的需求,可以使用ES
    <>,not in()
    (5) 查询条件中出现计算
    mysql> desc select * from city where id-1=9;
    
    (6) 出现隐式转换
    mysql> desc select * from t1 where telnum=110;
    mysql> desc select * from t1 where telnum='110';
    

    索引应用规范

    1.建索引原则
    (1) 必须要有主键,如果没有可以做为主键条件的列,创建无关列
    (2) 经常做为where条件列  order by  group by  join on, distinct 的条件(业务:产品功能+用户行为)
    (3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
    (4) 列值长度较长的索引列,我们建议使用前缀索引.
    (5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
    (6) 列值经常变化,没必要建索引,小表可以不用建索引.
    (7) 索引维护要避开业务繁忙期(pt-toolkit)
    
  • 相关阅读:
    进程间通讯,线程间通讯
    进程与线程
    学习自测6.0
    学习自测5.0
    学习自测4.0
    学习自测3.0
    学习自测2.0
    学习自测1.0
    PS中怎么复制某个图层的效果?
    初学前端犯下的错误(用于反省)
  • 原文地址:https://www.cnblogs.com/opesn/p/12994013.html
Copyright © 2020-2023  润新知