SQL优化

为什么要SQL优化?

性能低、执行时间太长、等待时间太长、SQL语句欠佳（连接查询）、索引失效、服务器参数设置不合理（缓冲、线程数）

SQL的编写过程

　　select dinstinct ..from ..join ..on ..where ..group by ...having ..order by ..limit ..

SQL的解析过程

　　from .. on.. join ..where ..group by ....having ...select dinstinct ..order by limit ...

SQL优化主要就是优化索引

　　Mysql的索引分类

　　主键索引不能是重复的id 不能是null

唯一索引不能是重复的id 可以是null　　

单值索引一个表可以有多个单值索引

　　复合索引多个列构成的索引

　　先创建一个表

create table user(
	id  int(10) auto_increment,
	name varchar(55),
　　　　 sex varchar(55),
	primary key(id)
);

//语法 

create 索引类型 索引名 on 表(字段)  

// 创建单值索引

create index index_name on user(name);

// 创建唯一索引

create unique index on user(sex);

// 创建复合索引

create index index_name_sex on user(name,sex);

创建索引的第二种方式

// 语法

alter table 表名 add 索引类型 索引名(索引字段);

// 创建单值索引
alter table user add index index_name(name);

// 创建唯一索引

alter table user add unique index_sex(sex);

// 创建复合索引

alter table user add index index_name_sex(name,sex);

删除索引

// 语法

drop 索引类型 索引名 on 表名

drop index index_name on user;

// 删除整个表

drop table user;

查询执行计划

explain +SQL语句

id:编号

select_type 查询类型

table 查询的表

type 类型

possible_keys 预测用到的索引

key 实际用到的索引

key_len 实际使用到的索引长度

ref 表之间的引用

row 通过索引查询到的数据量

Extra 额外的信息

id 编号

create table user(
uid int(3), 
uname varchar(20),
oid int(3),
primary key(uid)
);

create table orders
(
oid int(3),
oname varchar(20),
rid int(3),
primary key(oid)

); 

create table repertory (

rid int(3),
name varchar(200),
primary key(rid)
)

insert into user values(1,'小紫',1);
insert into user values(2,'小白',2);
insert into user values(3,'小黑',3);insert into orders values(1,'订单1',1);
insert into orders values(2,'订单2',2);
insert into orders values(3,'订单3',3);

insert into repertory values(1,'湖北仓库') ;
insert into repertory values(2,'上海仓库') ;
insert into repertory values(3,'北京仓库') ;

查询用户id为3 或订单编号为2 的库存信息

explain select * from repertory r,user u,orders o where u.uid = 3 or o.oid = 2;

id相同按顺序往下执行先执行r表在执行u表最后o表

表的执行顺序是根据数量的个数而改变数据越小的表优先查询

从用户表里删除2条数据

delete from user where uid > 2;

再次查询可以看到u表在最上面执行因为user表里的数据是最少的所以最优先执行

查询小王订单所在的仓库

EXPLAIN SELECT r.`name` FROM repertory r WHERE r.`rid` = (SELECT o.`rid` FROM orders o WHERE o.`rid` = (SELECT u.`oid` FROM USER u WHERE u.`uname` = '小王' LIMIT 1))

id越大的越先执行子查询是嵌套执行的先查里面再查外面 id为3的是user表因此先查询user表再查询orders表

如果id值有相同的又有不同的越大的越先执行相同的按照顺序执行

select_type 查询的类型

PRIMARY 主查询 (在最外层)

SUBQUERY 子查询 (在最里层)

SIMPLE 简单查询 (不包含子查询,union)

DERIVED 衍生查询 (使用到了临时表)

UNION 联合查询(合并两个结果集)

SIMPLE 简单查询（只查询了一张表）

EXPLAIN SELECT * FROM USER;

PRIMARY 主查询(SELECT r.name FROM)

UNION UNION 查询(SELECT * FROM repertory WHERE rid = 1 UNION SELECT * FROM repertory WHERE rid = 2)

DERIVED 衍生查询(使用了一张临时表)

EXPLAIN SELECT r.name FROM(SELECT * FROM repertory WHERE rid = 1 UNION SELECT * FROM repertory WHERE rid = 2) r;

执行顺序为:

　　先UNION 查询联合查询的结果集为一张临时表所以会有DERIVED 最后执行主查询

type类型

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

实际能达到的

system>const>eq_ref>ref>range>index>all

其中system,const只是理想情况

system 只有一条数据的系统表或衍生表只有一条数据的主查询

EXPLAIN SELECT * FROM  (SELECT tid FROM test01 WHERE tid = 1 ) t WHERE tid = 1;

const 仅仅能查到一条数据的SQL ,用于Primary key 或unique索引

// 查询

explain select tid from test01 where tid =1 ;

eq_ref 唯一性索对于每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能多、不能0）

explain select u.oid from user u,orders o where o.oid = u.oid;

ref 非唯一性索引，对于每个索引键的查询，返回匹配的所有行（0，多）

// 创建索引

alter table user add index index_name(uname);

// 查询

explain select * from  user where uname = '小白';

range 检索指定范围的行 where后面是一个范围查询(between ,> < >=, 特殊:in有时候会失效，从而转为无索引all)

// 添加索引

alter table user add index  index_id(uid);

// 查询

explain select u.* from user u where u.uid in (1,2)；

index 查询全部索引中数据

// 查询

explain select uname from user ;

uname是索引查询的也是索引

all 查询表中的全部数据

// 查询

 explain select * from user;

possible_keys 可能用到的索引(预测)

EXPLAIN SELECT r.`name` FROM repertory r WHERE r.`rid` = (SELECT o.`rid` FROM orders o WHERE o.`rid` = (SELECT u.`oid` FROM USER u WHERE u.`uname` = '小王' LIMIT 1))

其它2个表没有添加索引所以显示为空 u表使用了index_name所以因为uname添加了索引并且在(SELECT u.`oid` FROM user u WHERE u.`uname` = '小王' LIMIT 1) 子查询种用到了uname索引

key 实际用到的索引

实际用到了index_name索引

key_len 索引长度(用于判断复合索引是否被使用)

// 创建表

create table test3(
id int(4)auto_increment ,
name char(4),
sex char(3),
 primary key(id)
)；

//创建复合索引

alter table test3 add index index_name_sex(name,sex);

// 查询

explain select * from test3 where name = '1' and sex = '1';

可以看到索引长度为23 因为数据库使用的是utf8的编码一个字符占用3个字节查询语句中使用到了2个索引 name 和 sex

name char(4) * 3 = 12

sex char(3) * 3 = 9

如果索引字段可以为Null,则会使用1个字节用于标识。

2个字段都可以为null + 1 = 2

12 + 9 + 2 = 23

通过结果我们知道使用了2个索引

再次实验

// 创建表

CREATE TABLE test5(
id INT AUTO_INCREMENT,
NAME VARCHAR(4) NOT NULL,
sex VARCHAR(5) NOT NULL,
PRIMARY KEY (id)
);

// 添加索引

ALTER TABLE test5 ADD INDEX index_name_sex(name,sex);

// 查询

EXPLAIN SELECT * FROM test5 WHERE NAME = '1';

4 * 3 = 12

varchar可变字符 + 2

字段不可以为空所以不用加

12 + 2 = 14

ref指明当前表所参照的字段

explain select * from  user u,orders o where o.oid = u.oid and u.uname = '小王';

const 为常量u.uname='小王'

test.u.oid 因为o.oid = u.oid o表引用了u表的u.oid

rows被索引优化查询的个数(通过索引查到的个数)

 explain select uname  from user;

uname是有索引的所以根据unam查询有3条数据

Extra

using filesort 性能消耗大；需要“额外”的一次排序（查询）常见于 order by 语句中

// 创建表

create table test02
(
    a1 char(3),
    a2 char(3),
    a3 char(3),
    index idx_a1(a1),
    index idx_a2(a2),
    index idx_a3(a3)
);

执行语句
explain select * from test02 where a1 ='' order by a1 ;

这样没任何问题

修改查询语句

using filesort

explain select * from test02 where a1 ='' order by a2 ;

因为查询al 而排序得不是同一个字段如果排序和查找是同一个字段，则不会出现using filesort 最好where哪个字段就order by 哪个字段

复合索引不能跨列（最佳左前缀）

// 删除单值索引

drop index idx_a1 on test02;
drop index idx_a2 on test02;
drop index idx_a3 on test02;

// 添加复合索引

alter table test02 add index index_a1_a2_a3(a1,a2,a3);

explain select * from test02 where a2 = '' order by a3;

查询得是a2 orderby a3 所以 Using filesort

 explain select * from test02 where a1 = '' order by a2;

这次查询没有 Using filesort 因为创建索引得时候(a1,a2,a3) 而 where a1 = '' order by a2 顺序是对的所以没问题

避免： where和order by 按照复合索引的顺序使用，不要跨列或无序使用。

using temporary

性能损耗大，用到了临时表。一般出现在group by 语句中

 explain select a1 from test02 where a1 in ('1','2','3') group by a1 ;

没任何问题因为where a1字段 group by 也是a1

 explain select a1 from test02 where a1 in ('1','2','3') group by a2 ;

此时出现Using temporary 因为where a1 group a2

如何避免? 查询那些列，就根据那些列 group by .

using index（性能提升; 索引覆盖)

只在索引文件中读取数据

explain select uname from user where uname = '';

如果用到了索引覆盖(using index时)，会对 possible_keys和key造成影响：
如果没有where，则索引只出现在key中；
如果有where，则索引出现在key和possible_keys中。

using where （需要回表查询）

explain select a1,a3 from test02 where a3 = '' ;

a3 需要回原表查询

posted @ 2020-03-08 15:35 辰梓悦阅读(240) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

辰梓悦

SQL优化

为什么要SQL优化?

id 编号

select_type 查询的类型

type类型

possible_keys 可能用到的索引(预测)

key 实际用到的索引

key_len 索引长度(用于判断复合索引是否被使用)

ref指明当前表所 参照的 字段

rows被索引优化查询的个数(通过索引查到的个数)

Extra

using filesort

using temporary

using index（性能提升; 索引覆盖)

using where （需要回表查询）

公告

ref指明当前表所参照的字段