SQL优化

最新推荐文章于 2023-10-13 15:15:29 发布

G_farmer

最新推荐文章于 2023-10-13 15:15:29 发布

阅读量274

点赞数

分类专栏：日常学习总结文章标签： mysql

本文链接：https://blog.csdn.net/G_farmer/article/details/108766369

版权

日常学习总结专栏收录该内容

14 篇文章 0 订阅

订阅专栏

SQL优化:

主要就是优化索引;

索引:就相当于书的目录;index 是帮助Mysql高效获取数据的数据结构.索引是数据结构.

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wQPzAPSk-1600909249612)(D:\自我总结\SQL优化\image-20200917152249838.png)]

通过B树加快搜索查询的效率;

索引的弊端:

1.索引本身很大,可以存放在内存/硬盘中(通常为硬盘)

2.索引不是所有情况都适用:a.少量数据 b.频繁更新的字段 c.很少适用的字段

3.索引会降低增删改的效率(提升查询的效率)

索引的优势:

1.提高查询效率(降低I/O使用率)

2.降低CPU使用率(因为B树索引本身就是排好序的,因此在排序时就可以直接使用)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SIBuIJjV-1600909249616)(D:\自我总结\SQL优化\image-20200917152336028.png)]

索引的分类:

单值索引: 单列字段作为索引;一个表可以有多个单值索引.

主键索引:不能重复一般都是id 不可以为null

唯一索引: 不能重复一般都是id 可以为null

复合索引: 多个列构成的索引 (name,age) 遵循最左原则,也就是如果第一个字段匹配已经找到,就可以不用其他的字段

注意:如果一个字段是primary key,则该字段默认就是主键索引

创建索引:

方式一:

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

单值:

create index name_index on person(name);

唯一:

create unique index id_index on person(id);

复合:

create index name_age_index on person(name,age);

方式二:

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

单值:

alter table person add index name_index(name);

唯一:

alter table person add unique index id_index(id);

复合:

alter table person add index name_age_index(name,age);

删除索引:

drop index 索引名 on 表名;

drop index name_index on person;

查询索引:

show index from 表名;

SQL性能问题

a.分析SQL的执行计划: explain,可以模拟SQL优化器执行SQL语句

b.MySQL查询优化器会干扰我们的优化

查询执行计划: explain + SQL语句

explain select * from person;

id:编号

a.id值相同,由上往下顺序执行;表的执行顺序因数量的个数改变而改变的原因–>笛卡尔积;数据量少的表优先执行

b.id值不同,id值越大越优先执行(本质:在嵌套子查询时,先查内层,再查外层)

c.id值有相同,又有不同;id值越大越优先执行,id值相同,由上往下顺序执行

select_type:查询类型

table:表

type:类型 system>const>eq_ref>ref>range>index>all (其中system,const只是理想情况;实际只能达到ref,range)

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

const:仅仅能查到一条数据的SQL,用于Primary Key 或unique索引(类型与索引类型有关)

eq_ref:唯一性索引:对于每个索引键的查询,返回匹配唯一行数据(有且只有1个,不能多/不能为0),常见于唯一索引和主键索引

ref:非唯一性索引,对于每个索引键的查询,返回匹配的所有行(0,多)

range:检索指定范围的行,where后面是一个范围查询(between,in,>,<,>= 当in的数据大于表的一半时会失效)

index:查询全部索引中数据

all:查询全部表中的数据

总结:

system/const:结果只有一条数据

eq_ref:结果多条,但是每条数据是唯一的

ref:结果是多条,但是每条数据是0或多条

possible_keys:预测用到的索引

key:实际使用的索引

key_len:实际使用索引的长度,用于判断复合索引是否被完全使用

ref:表之间的引用,指明当前表所参照的字段 const–常量

rows:通过索引查询到的数据量

Extra:额外的信息

a.using filesort:性能消耗大;需要"额外"的一次排序;

对于单索引,如果排序和查找是同一个字段,则不会出现using filesort;如果不是同一个字段则会出现using filesort;避免:where那些字段,就order by哪些字段.

对于复合索引:不能跨列(最佳左前缀);避免:where和order by按照复合索引的顺序使用,不要跨列或无序使用.

b.using temporary:性能损耗大,用到了临时表.一般出现在group by语句中;避免:查询哪些列,就用哪些列group by

c.using index:性能提升;索引覆盖.原因:不读取源文件,只从索引文件中获取数据(不需要回原表查询),只要使用到的列全部都在索引中,就是索引覆盖.

如果用到了索引覆盖(using index时),会对possible_keys和key造成影响:

1.如果没有where,则索引只出现在key中;

2.如果有where,则索引出现在key和possible_keys中.

d.using where(需要回原表查询,不光是在索引表中查询还需要回到原表中查询数据)

e.impossible where:where 子句永远为false

explain select a1,a2,a3,a4 from test where a1=1and a2=2 and a3=3 and a4=4;推荐写法

explain select a1,a2,a3,a4 from test where a4=4 and a3=3 and a2=2 and a1=1;虽然顺序不同但是sql优化器会优化sql等同于上面的推荐写法

explain select a1,a2,a3,a4 from test where a1=1and a2=2 and a4=4 order by a3;用到了a1,a2两个索引,该两个字段不需要会标查询using index;而a4因为跨列使用,造成了该索引失效,需要回表查询,因此是using where;可以通过key_len校验

explain select a1,a2,a3,a4 from test where a1=1 and a4=4 order by a3;出现了using filesort(文件内排序,“多了一次额外的查找/排序”):不要跨列使用(where和order by 拼接起来看,不存在跨列使用)

explain select a1,a2,a3,a4 from test where a1=1 and a4=4 order by a2,a3;a4失效,where与order by拼接是a1,a2,a3满足不存在using filesort

总结:

i.如果(a,b,c,d) 复合索引和使用的顺序全部一致(且不跨列使用),则复合索引全部使用;如果部分一致(且不跨列使用),则使用部分索引

ii.where和order by 使用不要跨列

优化:

单表优化,两表优化,三表优化

a.最佳左前缀,保持索引的定义和使用的顺序一致性

b.索引需要逐步优化

c.将含in的范围查询放置最后,可能会失效

两表优化:

a.小表驱动大表

b.索引的添加优先使用驱动表的字段

避免索引失效的原则

a.复合索引,不要跨列或无序使用(最佳左前缀)

b.复合索引,尽量使用全索引匹配

c.不要在索引上进行任何操作(计算,函数,类型转换),否则索引失效

d.复合索引不能使用不等于(!= <>)或is null(is not null),否则自身以及右侧所有全部失效.

e.尽量使用索引覆盖(using index)

f.like尽量以"常量"开头,不要以’%'开头,否则索引失效.

g.尽量不要使用类型转换(显示,隐式),否则索引失效

h.尽量不要使用or,否则索引失效

SQL优化,是一种概率层面的优化,至于是否实际使用了我们的优化,需要通过explain来推测.

一些其他的优化方法:

1.exist和in:如果主查询的数据集大,则使用in;如果子查询的数据集大,则使用exist

2.order by 优化

using filesort 有两种算法: 双路排序,单路排序(根据I/O的次数–消耗很大性能)

提高order by查询的策略:

a.选择使用单路/双路;调整buffer的容量大小

b.避免使用select *…

c.复合索引不要跨列使用,避免using filesort

d.保证全部的排序字段,排序的一致性(都是升序或降序)

MySQL4.1之前默认使用双路排序;扫描2次磁盘(第一次:从磁盘中读取排序字段,对排序字段进行排序(在buffer缓冲区进行排序) 第二次:扫描其他字段)

MySQL4.1之后默认使用单路排序;一次读取全部字段,在buffer中进行排序,但此种单路排序会有一定隐患(不一定是一次I/O 有可能是多次的),因为如果数据量比较大,不能一次性读取完毕,需要"分片读取,多次读取"

SQL排序 - 慢查询日志:MySQL提供的一种日志记录,用于记录MySQL中响应时间超过阈值的sql语句 (long_query_time,默认10秒)

慢查询日志默认是关闭的;

检查是否开启慢查询日志: show variables like ‘%slow_query_log%’;

临时开启:set global slow_query_log = 1;0关闭在内存中开启

永久开启:/etc/my.cnf中追加配置:

[mysqld]

slow_query_log = 1

slow_query_log_file = /var/lib/mysql/localhost-slow.log

慢查询阈值:show variables like ‘%long_query_time%’;

临时设置阈值:set global long_query_time = 5;需要重新登录生效

永久设置阈值:/etc/my.cnf中追加配置:

[mysqld]

long_query_time = 3

--查询超过阈值的SQL: show global status like ‘%slow_queries%’;

(1)慢查询的SQL被记录在了日志中,因此可以通过日志来查看具体的慢SQL

cat /var/lib/mysql/localhost-slow.log

(2)通过mysqldumpslow工具查看慢sql,可以通过一些过滤条件快速查找出需要定位的慢sql

mysqldumpslow --help

s:排序方式

r:逆序

l:锁定时间

g:正则匹配模式

…

–获取返回记录最多的3个sql

mysqldumpslow -s r -t 3 /var/lib/mysql/localhost-slow.log

–获取访问次数最多的3个sql

mysqldumpslow -s c -t 3 /var/lib/mysql/localhost-slow.log

–按照时间顺序,前10条包含left join 查询语句的sql

查看慢sql,可以通过一些过滤条件快速查找出需要定位的慢sql