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执行计划的认识
1 table : 此次查询涉及到的表
2 type : 查询类型: 全表扫,索引扫
3 possible_keys : 可能用到的索引
4 key : 最后选择的索引
5 key_len : 索引覆盖长度
6 rows : 此次查询需要扫,扫描的行数
7 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%
索引本身失效,统计数据不真实
索引和表有自我维护的能力。
对于表内容变化比较频繁的情况下,统计信息不准确,过旧。有可能会出现索引失效。