02-mysql-索引执行计划

1.执行计划分析

1.1什么是执行计划

select * from t1 where name='zs';
分析的是优化器按照内置的cost计算算法，最终选择后的执行计划。
cost？ 
代价，成本。
对于计算机来讲,代价是什么？
IO ，CPU，MEM

1.2查看执行计划

explain select * from world.city ;
mysql> desc  select * from world.city ;

 1.3执行计划的认识 

table         ： 此次查询涉及到的表
type          ： 查询类型： 全表扫，索引扫
possible_keys ： 可能用到的索引
key           ： 最后选择的索引
key_len       ： 索引覆盖长度
rows          ： 此次查询需要扫，扫描的行数
Extra         ： 额外的信息

1.4 table

此次查询涉及到的表，真对一个查询中多个表时，精确到问题表。
desc select country.name ,city.name 
from city join country 
on city.countrycode=country.code  
where city.population='CHN';

1.5 type 查询类型

全盘扫面：不适用任何索引 。ALL

例如：

mysql> desc select * from city;
mysql> desc select * from city where 1=1 ;
mysql> desc select * from city where countrycode like '%ch%';
mysql> desc select * from city where countrycode not in ('CHN','USA');
mysql> desc select * from city where countrycode != 'CHN';

索引扫描：index < range < ref < eq_ref < const(system)

index：全索引扫描

mysql> desc select countrycode from world.city ;

range：索引范围查询： > < >= <= like in or between and 

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');

特殊情况：查询条件为主键时   (ref)
mysql> desc select * from city where id != 10;
mysql> desc select * from city where id not in (10,20);

ref： 辅助索引等值查询

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

eq_ref : 多表链接中，非驱动表链接条件是主键或唯一键。

desc select country.name ,city.name 
from city join country 
on city.countrycode=country.code  
where city.population='CHN';

const(system) : 聚簇索引等值查询

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

1.6 possible_keys , key

possible_keys  : 可能会走的索引，所有和此次查询有关的索引。
key            : 此次查询选择的索引。

1.7 key_len  联合索引覆盖长度

（1）介绍： 
对于联合索引index(a,b,c) , 我们希望将来的查询语句，对于联合索引应用越充分越好。
key_len ,可以帮助我们判断，此次查询，走了联合索引的几部分。
全部覆盖：
select * from t1  where a=  and b=  and  c= 
select * from t1  where a in   and b in  and  c in 
select * from t1  where  b=  and  c=  and a=  
select * from t1 where a and b  order by c 
部分覆盖： 
select * from t1  where a=  and b= 
select * from t1  where a=  
select * from t1  where a= and  c=  
select * from t1  where a=  and b > < >= <= like   and  c=  
select xxx  from t1 where  a    order by b
不覆盖： 
bc 
b 

1.7.1 key_len的计算： idx(a,b,c)

假设，某条查询可以完全覆盖三列联合索引。例如：
select * from t1  where a=  and b=  and  c=  
key_len= a长度？ + b长度？  + c长度？

长度指的是什么？
长度受到： 数据类型 ， 字符集 影响
长度指的是，列的最大储值字节长度

数字： 
                 not null        没有not null 
    tinyint      1                  1+1
    int          4                  4+1
    bigint       8                  8+1
字符： utf8   ----->  一个字符最大占3个字节
                    not null        没有not null
    char(10)        3*10             3*10+1
    
    varchar(10)     3*10+2          3*10+2+1

1.8 extra

using filesort: 表示此次查询使用到了 文件排序，说明在查询中的排序操作： order by  group by   distinct ..
mysql> desc  select * from city where countrycode='CHN' order by population;
mysql> desc  select * from city where countrycode='CHN' order by population;

2.索引应用规范

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
建表时一定要有主键,一般是个无关列
优化方案:
(1)如果非得使用重复值较多的列作为查询条件(例如:男女),可以将表逻辑拆分
(2)可以将此列和其他的查询类,做联和索引
(3)为经常需要where 、ORDER BY、GROUP BY,join on等操作的字段
(4)尽量使用前缀来索引
(5)限制索引的数目
(6)删除不再使用或者很少使用的索引(percona toolkit)
(7)大表加索引,要在业务不繁忙期间操作
(8)尽量少在经常更新值的列上建索引

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

2.1建立索引原则

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

2.2不走索引的情况

没有查询条件，或者查询条件没有建立索引
查询结果集是原表中的大部分数据，应该是15-30%

索引本身失效，统计数据不真实

索引和表有自我维护的能力。
对于表内容变化比较频繁的情况下，统计信息不准确，过旧。有可能会出现索引失效。