mysql性能优化之SQL优化

sql优化:

原因

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

SQL :

编写过程:
select dinstinct…from…join…on…where…groupby…having…ord
解析过程:(先解析from)
from … on… join . . where . . group by . … having … select dinstinct . . or

sql优化 主要就是 在优化索引

索引:相当于书的目录;index;是帮助MYSQL高效获取数据的数据结构。
索引是数据结构(树: B树(mysql默认)、 Hash树… )

索引的缺点

索引的弊端:
1.索引本身很大，可以存放在内存/硬盘 (通常为硬盘)
2.索引不适用的情况: a.少量数据 b.频繁更新的字段 c.很少使用的字段
3.索引会降低增删改的效率(增删改 查)

索引的优点

优势:
1提高查询效率( 降低I0使用率)
2.降低CPU使用率(… order by age desc,因为B树索引本身就是一个好排序的结构，因此在排序时 可以直接使用,利用先序遍历或后序遍历等遍历出来就可以)

索引

分类:

单值索引:单列， age ;一个表可以多个单值索引，name。
主键索引:字段的值不能重复和不能为null。id
唯一索引:字段的值不能重复。id
复合索引:多个列构成的索引 (相当于二级目录:z: zhao)(name, age),当查询的name有多个值就可以在通过age筛选,若只有一个值就不会使用age;

创建索引:

方式一:
create 索引类型 索引名 on表(字段)
单值索引:
create index dept_ index on tb(dept) ;
唯一索引:
create unique index name_ index on tb (name) ;
复合索引
create index dept_ name_ index on tb (dept, name) ;

方式二:
alter table 表名 add 索引类型 索引名 (字段)
单值索引:
alter table tb add index dept_ index (dept) ;
唯一索引:
alter table tb add unique index name_ index (name) ;
复合索引
alter table tb add index dept_ name_ index (dept, name ;
);
注意:如果一个字段是primary key，则改字段默认就是主键索引

删除索引:

drop index 索引名 on 表名
drop index name_ index on tb
查询索引:
show index from 表名
show index from 表名\G

sql性能问题

分析SQL的执行计划( explain): 可以模拟SQL优化器执行SQL语句

查询执行计划:explain +SQL语句
explain select * from tb ;

id:编号
select_ type :查询类型.
table :表
type : 类型
possible_ keys :预测用到的索引
key:实际使用的索引
key_ len :实际使用索引的长度 作用:用于判断复合索引是否被完全使用
ref:表之间的引用
rows:通过索引查询到的数据量|
Extra :额外说明.

当t表有3个数据,tc表有3个数据,c表有4个数据时,执行联合查询三表时为什么先执行顺序是t tc c?
1.id值相同,从上往下顺序执行
t3:表示t表有三条记录

t3-tc3-c4
tc3–c4-t6
表的执行顺序 因数量的个数改变而改变的原因: 笛卡儿积
t tc c 笛卡尔积
3 3 4 3x3=9x4=36
c tc t 笛卡尔积
4 3 3 3x3=12x3=36
相同的数据因顺序不一样而中间结果不一样,中间结果越小性能越好

2.id值不同: id值越大越优先查询(本质:在嵌套子查询时，先查内层再查外层)
查询教授SQL课程的老师的描述( desc)
explain select tc. tcdesc from teacherCard tc, course C, teacher t where c. tid = t. tid and t. tcid = tc. tcid and C. cname = sql’ ;
将以上多表查询转为子查询形式:
explain select tc. tcdesc from teacherCard tc where tc. tcid =
(select t. tcid from teacher t where t. tid =
(select c. tid from course C where c. cname =’sq1’ )
);
执行顺序就变成了c t tc,这样也就导致查询中间过程时效率更低,也就是子查询比联合查询效率更低的原因

3.select_ type:查询类型
PRIMARY(主查询):包含子查询SQL中的主查询(最外层)
SUBQUERY(子查询): 包含子查询SQL中的子查询 (非最外层)
simple:简单查询(不包含子查询、union连接查询)
union(连接查询):可以将两个字段连接起来形成一张临时表
derived:衍生查询(使用到了临时表)
a.在from子查诲中只有一- 张表
explain select cr. cname from ( select * from course where tid in (1,2) ) cr;

b.在from子查询中，如果有table1 union table2 ，则table1 就是derived, table2 就是union
explain select cr. cname from ( select * from course where tid =1 union select * from course where tid =2) cr;4.type:索引类型、类型
system>const>eq_ ref>ref>range> index>all， 要对type进 行优化的前提:有索引
其中: system, const只是理想情况;实际能达到ref>range

• system (忽略) :只有一条数据的系统表;或衍生表只有一条数据的主查询
• const:仅仅能查到一 条数据的SQL ,用于Primary key或unique索引 (类型与索引类型有关)
• eq_ ref:唯一 性索引:对于每个索引键的查询，返回匹配唯一 行数据(有且只有1个，不能多、不能0)
  select … from … where id = …常见于唯一索引和主键索引。
• ref: 非唯一 性索引，对于每个索引键的查询，返回匹配的所有行(0， 多)
• range:检索指定范围的行,where后面是–个范围查询(between,> < >= 特殊:in 有时会失效成ALL)
• index: 查询全部索引中数据
  explain select tid from teacher ; --tid 是索引， 只需要扫描索引表，不需要扫描全部数据
• all: 查询全部表中的数据
  explain select cid from course ; --cid不是索引，需要全表扫描，即需要表中的所有数据

5. key_ len :索引的长度;
作用:用于判断复合索引是否被完全使用(a, b, c)。
create table test_ k(
name char (20) not null default ‘’);
alter table test_ k add index index_ name (name) ;
explain select * from test_ kl where name =’’; - - key_ len : 60
在utf8: 1个字符站3个字节
alter table test_ kl add column name1 char(20) ;
name1可以为null
alter table test_ k add index index_ name1 (name1) ;
explain select * from test_ kl where namel =’ ‘;- - key_ len : 61
如果索引字段可以为Null,则会使用1个字节用于标识。
增加一个复合索引
alter table test_ k add index name_ name1_ index (name, name1) ;
explain select * from test_ k where name1 =’’ ; — - key_ len : 121
explain select * from test_ kl where name =’’- - key_ len :60
查询name1需要先查询name
如果是可变字符串varchar (20)
alter table test_ kl add column name2 varchar(20) ; --可以为Null
alter table test kl add index name2 index (name2) ;
explain select * from test kl where name2 = ;-- key_ len :63
20*3=60 + 1(null) +2(用2个字节标识可变长度) =63

6. ref :指明当前表所 参照的字段。
注意与type中的ref值区分
作用:指明当前表所 参照的字段。
select … where a.c = b. x;(其中b. x可以是常量，const)

7.rows: 被索引优化查询的数据个数(实际通过索引而查询到的数据个数)

8.Extra:额外说明
①using filesort :性能消耗大; 需要“额外”的一次排序(查询)

先查询a1后按照a1排序:
explain select * from test02 where al =’’ order by a1 ;–using where
先查询a1后,再查洵a2按照a2排序:
explain select * from test02 where al =’’ order by a2 ; --using filesort
小结:对于单索引， 如果排序和查找是同一个字段，则不会出现using filesort;
避免: where哪些字段， 就order by那些字段

*复合索引:不能跨列(最佳左前缀)
alter table test02 add index idx_ al_ a2 a3 (al, a2, a3) ;
explain select from test02 where a1=’’ order by a3 ;-- using filesort
从a1到a3,垮了一个a2
小结:避免:where和order by按照复合索引的顺序使用，不要跨列或无序使用。

②using temporary:性能损耗大，用到了临时表。一般出现在group by语句中。
eg:explain select a1 from test02 where a1 in(‘1’‘2’,‘3’) group by a2 ;
避免方法:查询哪些列，就根据哪些列group by .

③ using index :性能提升;索引覆盖(覆盖索引)。只要使用到的列全部都在索引中，就是索引覆盖using index.
例如: test02表中有一个复合索引(al, a2, a3)
explain select a1 a2 from test02 where a1=’’ or a2=’’ ;
如果用到了索引覆盖(using index)，会对possible_ keys和key造成影响:
a.如果没有where，则索引只出现在key中;
b.如果有where，则索引出现在key 和possible_ keys中。

④using_ where(需要回表查询)
假设age是索引列 但查询语句select age, name from … where age =… 此语句中必须回原表查Name，因此会显示using_ where

⑤impossible where : where子句永远为false
explain select * from test02 where a1= ‘x’ and a1= ’ y’;

MySQL查询优化器可能会干扰我们的优化,MySQL查询优化器自动执行是概率事件,有时候不会帮你优化

sql优化实例

test03 表有复合索引(a1,a2,a3,a4)
1.
explain select a1, a2, a3, a4 from test03 where a1=1 and a2=2 and a3=3 and a4 =4 ; using index-----推荐写法，因为索引的使用顺序(where后面的顺序)和复合索引的顺序一致
explain select a1, a2, a3, a4 from test03 where a4=1 and a3=2 and a2=3 and al =4 ; using index-----虽然编写的顺序和索引顺序不一致，但是sql在真正执行前经过了SQL优化器的调整，结果与上条SQL是一致的.

2.
explain select a1,a2,a3,a4 from test03 where a1=1 and a2=2 and a4=3 order by a3;
SQL用到了a1 a2两个索引，该两个字段不需要回表查询所以是using index 但是产生了useing where说明需要回原表查询,这是因为where子句中跨列了,从a2跨到a4,导致a4索引失效需要回原表查询

3.
explain select a1, a2, a3, a4 from test03 where a1=1 and a4=4 order by a3;
-----以上SQL出现了using filesort(文件内排序， “多了一次额外的查找/排序”)不要跨列使用( where和order by拼起来，不要跨列使用,上一个例子没有出现using filesort的原因是where a1=1 and a2=2 后面a4索引失效不理会,order by a3,这样拼起来就是a1,a2,a3所以没有using filesort)