【数据库SQL系列】sql优化,你学废了吗

2022-10-13 10:18:16

热门系列:


目录

1.序言

2.SQL优化

2.1 为什么要做sql优化

2.2 sql优化的那些方式

2.2.1 慢sql发现

2.2.2 explain sql分析

2.2.3 sql优化的那些操作

3.总结


1.序言

对于一个系统而言,要想达到高可用,需要从架构,程序设计,网络层面以及数据库层面都保证一个较优的设计与使用。而数据库层面,sql优化则是很重要的一个环节。今天,就和大家一起捋一捋sql优化的那些个方式。


2.SQL优化

2.1 为什么要做sql优化

其实这个问题,有些此地无银三百两。但还是有必要提一提。

sql优化,能在有限的物理资源条件下。依据数据库系统自身的特性和设计,从逻辑处理层面,尽量达到数据库CRUD操作的最优状态,从而提升数据库的性能,间接从数据库层面提升系统的性能。

但当数据库达到瓶颈时,比如mysql一个表有了上千万或是上亿的数据时。这可谓是已经达到了数据的物理极限了,所以已经不是sql优化所能改善的。此时就需要对数据库或表做横向或是纵向的拓展了。

2.2 sql优化的那些方式

2.2.1 慢sql发现

1、开启慢查询日志,设置超过几秒为慢SQL,抓取慢SQL(具体操作参考:https://www.cnblogs.com/Yang-Sen/p/11384440.html

2、通过explain对慢SQL分析(重点)

mysql直接通过explain查看;oracle需要通过以下方式查看:

explain plan FOR SELECT * FROM table_name WHERE field = 1;
SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));

3、show profile查询SQL在Mysql服务器里的执行细节和生命周期情况(重点)

4、其他各类数据库监控工具。例如阿里数据库系统就自带sql监控。还有mysqldumpslow,pt-query-digest等。

2.2.2 explain sql分析

这是mysql中通过explain关键字解析sql语句后的执行结果。下面对每个字段分别做下说明:

①id:反映的是表的读取的顺序,或查询中执行select子句的顺序。

小表永远驱动大表,三种情况:

  • id相同,执行顺序是由上至下的
  • id不同,如果是子查询,id序号会递增,id值越大优先级越高,越先被执行
  • id存在相同的,也存在不同的,所有组中,id越大越先执行,如果id相同的,从上往下顺序执行

select_type  反映的是Mysql理解的查询类型,一般有六种类别

  • simple:简单的select查询,查询中不包含子查询或union。
  • primary:查询中若包含任何复杂的字部分,最外层查询标记为primary。
  • subquery:select或where列表中的子查询。
  • derived(衍生):在from列表中包含的子查询,Mysql会递归执行这些子查询,把结果放在临时表里。
  • union:若第二个select出现在union后,则被标记为union,若union包含在from字句的子查询中,外层select将被标记为derived
  • union result:union后的结果集

table:反映这一行数据是关于哪张表的

type:访问类型排序

  • system:从单表只查出一行记录(等于系统表),这是const类型的特例,一般不会出现
  • const:查询条件用到了常量,通过索引一次就找到,常在使用primary key或unique索引中出现。
  • eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯一索引扫描。
  • ref:非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它可能会找到多个符合条件的行,与eq_ref的差别是eq_ref只匹配了一条记录。
  • range:只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引,一般是在where语句中出现了between、<、>、in等的查询。这种范围扫描索引扫描比全表扫描要好,因为它只需要开始于索引的某一点,而结束于另一点,不用扫描全部索引。与eq_ref和ref的区别在于筛选条件不是固定值,是范围。
  • index:full Index scan,index和all的区别为index类型只遍历索引树。这通常比all快,因为索引文件通常比数据文件小。
  • all:全表扫描,如果查询数据量很大时,全表扫描效率是很低的。

重点:

all和index都是读全表,但index是从索引中读取的,而all是从硬盘中读的

完整的排序:system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index >all

possible_keys、key、key_len:反映实际用到了哪个索引,索引是否失效

ref:反映哪些列或常量被用于查找索引列上的值

rows:根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数

Extra

  • using filesort:mysql中无法利用索引完成的排序,这时会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。创建索引时就会对数据先进行排序,出现using filesort一般是因为order by后的条件导致索引失效,最好进行优化。
  • using temporary:使用了临时表保存中间结果,mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。影响更大,所以要么不建索引,要么group by的顺序要和索引一致
  • using index:表示相应的select操作中使用了覆盖索引,避免访问了表的数据行,效率好。覆盖索引:select后的数据列只从索引就能取得,不必读取数据行,且与所建索引的个数(查询列小于等于索引个数)、顺序一致。所以如果要用覆盖索引,就要注意select的列只取需要用到的列,不用select *,同时如果将所有字段一起做索引会导致索引文件过大,性能会下降。出现using where,表明索引被用来执行索引键值的查找。如果没有同时出现using where,表明索引用来读取数据而非执行查找动作。
  • using where:表明使用了where过滤
  • using join buffer:使用了连接缓存
  • impossible where:where子句的值是false
  • select tables optimized away
  • distinct:优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作

未完待续,2020-1-13续上

咱们接着上面的继续,谈完慢sql的发现与分析方法之后,得来说一说具体应该做哪些操作来解决慢sql的出现或者sql优化了。

2.2.3 sql优化的那些操作

测试数据:本次针对sql优化性能对比,我在本地的mysql,对拥有209W+条数据的表test_data,进行优化对比测试。

                  mysql数据库使用的是8.0版本。

①尽量少用范围查询,如in,between等。如果必须用,则需要对各用法做一个优劣比较和性能对比

例如做顺序范围查询时,使用between替代in

select * from test_data where id in (1,2,3,4,5,6,7,8,9);

select * from test_data where id between 1 and 9;

可以看到,同样查询8条数据,between的查询速度是由于in的。(此查询有做多次查询,因为mysql有缓存,所以取了差别较大的截图)

②in与exist选择使用

注释:此处关联表test_reference表中有425条数据。其中有一半的重复数据,但不影响我们测试。

select * from A where A.id in (select id from B);

in 和 exists区别:in适合A表比B表数据大的情况,exists适合B表比A表数据大的情况

not in 和 not exists都是使用not exists效率更好。具体原因是网上资料说是not in会使索引失效。

此处我通过测试发现,其实并不然,并非所有情况下使用not in都会使索引失效。

通过时间对比,两个sql执行都是花了2.3秒左右。后来我通过explain查看执行计划发现:

原来两个sql语句都使用到了索引。通过搜索资料发现,其实自5.6之后,貌似mysql对于not in也可以使用索引了。不过因为是测试数据,可能是数据原因,导致mysql内部的索引控制机制使用到了索引。此处大家可在实践中,自行了解。

③SELECT语句务必指明字段名称

增加了使用覆盖索引的可能性;还能减少IO性能消耗。

全字段查询花了2.25秒,而指明查询字段,同样查询100W条数据,却只花了1.2秒左右。差异显著。

④当只需要一条数据的时候,使用limit 1

这是为了使EXPLAIN中type列达到const类型

使用了一个非索引字段name来查询(因为使用id有主键,速度差不多),执行效率就差异很明显啦。

⑤如果排序字段没有用到索引,就尽量少排序

sql查询时,都会依照执行顺序来操作。(可参照Sql语句执行顺序)而每一步操作,都会生成一个虚拟表。当执行到order by时,也会生成。等于降低了sql执行效率。

⑥如果限制条件中其他字段没有索引,尽量少用or

or两边的字段中,如果有一个不是索引字段,而其他条件也不是索引字段,会造成该查询不走索引的情况。不过字段都有索引(非组合索引)则会走索引查询。很多时候使用union all或者是union(必要的时候)的方式来代替“or”会得到更好的效果。

该测试数据中,id是主键索引,但是却进行了全表扫描,就是因为使用了or。

⑦如果不用去重或是多表之间数据没有重复,尽量用union all代替union

union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作,这就会涉及到排序,增加大量的CPU运算,加大资源消耗及延迟。当然,union all的前提条件是两个结果集没有重复数据。

⑧不使用ORDER BY RAND()

查看官方手册,解释rand()放在ORDER BY 子句中会被执行多次,自然效率及很低。

替代方式:通过程序实现随机,或是通过id*rand()获取随机id再匹配数据。

⑨使用like模糊查询时不要使用%前缀

当使用like查询时,如果带有“%”前缀,会使索引失效。所以不建议使用。

⑩避免在where子句中对字段使用is not null值判断

如我在测试表中name字段添加了索引

使用is null判断,发现是可以是用索引的

而使用is not null,却不行

⑪联合索引,需要遵循最左原则

联合索引需要使用最左的字段开头,否则索引不会生效。其次,联合索引需要避免与between、>、<等条件使用,会使联合索引的后面索引失效。

⑫必要时可以使用force index来强制查询走某个索引

有的时候MySQL优化器采取它认为合适的索引来检索SQL语句,但是可能它所采用的索引并不是我们想要的。这时就可以采用forceindex来强制优化器使用我们制定的索引。


3.总结

数据库相关的知识其实有很多,sql优化只是其中的一小部分。以上的一些操作主要是以Mysql为例来测试的。而有些情况,还需要各位自己针对现实业务,亲自校验。没有指定的操作方式,只有合适自己业务的优化。总结一句,先分析,再对具体分析情况做具体优化。

后续我会把平时工作中用到的一些优化技巧,陆续补充进来,与大家共同分享,共同进步。

本博客皆为学习、分享、探讨为本,欢迎各位朋友评论、点赞、收藏、关注,一起加油!


  • 作者:善良勤劳勇敢而又聪明的老杨
  • 原文链接:https://yangy.blog.csdn.net/article/details/103928252
    更新时间:2022-10-13 10:18:16