SQL:学习SQL优化

学习 

1.语句

2.原则（三条快速记忆）

3.常见查询类型

试验

本次试验采用SQL表中的world 数据库中city表来试验

1.查询方法

explain SELECT * FROM city where ID>500 limit 10;
#1.all查询，主要是因为查询的键不是District，而且key中无数据
explain select `Name`,CountryCode,District from city where District='Utrecht';
#2.ref查询，键为CountryCode，key主要依存索引名称
explain select `Name`,CountryCode,District from city where CountryCode='NLD';
#3.const查询，常量
explain select `Name`,CountryCode,District from city where ID=5;
#4.range查询，范围查询
explain select `Name`,CountryCode,District from city where ID<5;

2.SQL优化

1.模糊查询优化

#基础语言
select `Name`,CountryCode,District from city where CountryCode like '%LD';
#错误查询，不走索引，查询开头是%，_
explain select `Name`,CountryCode,District from city where CountryCode like '%LD';
explain select `Name`,CountryCode,District from city where CountryCode like '_LD';
#正确查询
explain select `Name`,CountryCode,District from city where CountryCode like 'NL%';
explain select `Name`,CountryCode,District from city where CountryCode like 'NL_';

2.in优化

#基础语言
select `Name`,CountryCode,District from city where CountryCode in('NLD','PHL');
#基础in查询，缺点：慢
explain select `Name`,CountryCode,District from city where CountryCode in('NLD','PHL');
#or查询，稍快
explain select `Name`,CountryCode,District from city where CountryCode='NLD' or CountryCode='PHL';
#union联合查询，性能最快的方法，输出三条数据
explain
select `Name`,CountryCode,District from city where CountryCode='NLD'
union
select `Name`,CountryCode,District from city where CountryCode='PHL';