SQL查询优化实践[通俗易懂]

SQL查询优化实践[通俗易懂]为什么要优化 系统的吞吐量瓶颈往往出现在数据库的访问速度上,即随着应用程序的运行,数据库的中的数据会越来越多,处理时间会相应变慢,且数据是存放在磁盘上的,读写速度无法和内存相比 如何优化 设计数据库时

为什么要优化

  • 系统的吞吐量瓶颈往往出现在数据库的访问速度上,即随着应用程序的运行,数据库的中的数据会越来越多,处理时间会相应变慢,且数据是存放在磁盘上的,读写速度无法和内存相比

如何优化

  1. 设计数据库时:数据库表、字段的设计,存储引擎
  2. 利用好MySQL自身提供的功能,如索引,语句写法的调优
  3. MySQL集群、分库分表、读写分离

关于SQL语句的优化的方法方式,网络有很多经验,所以本文抛开这些,设法在DAO层的优化和数据库设计优化上建树,并列举两个简单实例

  例子1:ERP查询优化

现状分析:

1 缺少关联索引
2 Mysql本身的性能所限,对多个表的关联支持不好,目前的性能主要集中在列表查询上面,列表查询关联了很多表

应对方法:
1 增加必要的索引:通过explain查看执行记录,根据执行计划添加索引;
2 先统计业务数据主表主键,获取较小结果集,然后再利用结果集关联查询;
1) 先根据主表和条件查询显示业务数据的主键
2) 根据主键作为查询条件,再关联其他关联表,查询需要的业务字段
3) 在主表查询时,针对需要关联其他表的查询条件,需要做只有设置这个条件,才会做表关联的设置

例如 有如下表 TT_A   TT_B    TT_C  TT_D

假设未优化前的SQL是这样的

SELECT
    A.ID,
    ....
    B.NAME,
    .....
    C.AGE,
    ....
    D.SEX
    .....

FROM  TT_A A
LEFT JOIN TT_B B ON A.ID  = B.ITEM_ID
LEFT JOIN TT_C C ON B.ID = C.ITEM_ID
LEFT JOIN TT_D D ON C.ID = D.ITEM_ID
WHERE 1=1
AND A.XX = ?
AND A.VV = ?
.....

那么优化后的SQL是

第一步

SELECT
    A.ID

FROM  TT_A A
WHERE 1=1
AND A.XX = ?
AND A.VV = ?

第二步

SELECT
    A.ID,
    ....
    B.NAME,
    .....
    C.AGE,
    ....
    D.SEX
    .....
FROM  ( SELECT A.ID,..... FROM  TT_A  WHERE ID IN (1,2,3..)  ) A
LEFT JOIN TT_B B ON A.ID  = B.ITEM_ID
LEFT JOIN TT_C C ON B.ID = C.ITEM_ID
LEFT JOIN TT_D D ON C.ID = D.ITEM_ID
WHERE 1=1
AND A.XX = ?
AND A.VV = ?

代码100分

 小结:

这种优化适用于,列表查询,因为一个列表查询的条件一般都是和主表挂钩的,所以利用这一点,建立关键字段索引,同时通过查询条件的限制大大的缩小主表的数据量。这样关联其他表的时候就会快的多

 

   例子2:文章搜索优化

  假设你要做个贴吧的文章搜索功能,最简单直接的存储结构,就是利用关系数据库,创建这样一个存储文章的关系数据库表 TT_ARTICLES:

 SQL查询优化实践[通俗易懂]

  那么,假如现在的搜索关键字是“目标”,我们就可以利用字符串匹配的方式来对 CONTENT 列进行匹配查询:

代码100分select * from ARTICLES where CONTENT like "% 目标 %";

  这很容易就实现了搜索功能。但是,这样的方式有着明显的问题,即使用 % 来进行字符串匹配是非常低效的,因此这样的查询需要遍历整个表(全表扫描)。几篇、几十篇文章的时 候,还不是什么问题,但是如果有几十万、几百万的文章,这种方式是完全不可行的。且不说单独的关系数据库表就不能容纳那么大的数据了,就是能够容纳,要扫描一遍,这里的时间代价是难以想象的

  于是,我们就要引入“倒排索引”的技术了。在前面所述的场景下, 我们可以把这个概念拆分为两个部分来解释: 好,那上面的 ARTICLES 表依然存在,但现在需要添加一个关键字表 KEYWORDS,并且,KEYWORD 列需要添加索引,因此这条关键字的记录可以被迅速找到:

SQL查询优化实践[通俗易懂]

 

 

 当然,我们还需要一个关联关系表把 KEYWORDS 表和 ARTICLES 表结合起来, KEYWORD_ID 和 ARTICLE_ID 作为联合主键 

SQL查询优化实践[通俗易懂]

 

 

你看,这其实是一个多对多的关系,即同一个关键字可以出现在多篇文章中,而一篇文章可 以包含多个不同的关键字。这样,我们可以先根据被索引了的关键字,从 KEYWARDS 表 中找到相应的 KEYWORD_ID,进而根据它在上面的关联关系表找到 ARTICLE_ID,再根据 它去 ARTICLES 表中找到对应的文章。

 

小结:

  这看起来是三次查找,但是因为每次都走索引,就免去了全表扫描,在数据量较小的时候速 度并不慢,并且,在使用 SQL 实现的时候,这个过程完全可以放到一个 SQL 语句中。在数 据量较小的时候,上面的方法已经足够好用了。 这样解决了全表扫描和字符串 % 匹配查询造成的性能问题。

 

总结:

  在技术面试的时候,如果你能举出实际的例子,或者是直接说自己开发过程的问题和收获会让面试分会加很多,回答逻辑性也要强一点,不要东一点西一点,容易把自己都绕晕的。例如,问为怎么优化SQL你不要一上来就直接回答加索引,你可以这样回答:

  面试官您好,首先我们的项目DB数据量遇到了瓶颈,导致列表查询非常缓慢,给用户的体验不好,为了解决这个问题,有很多种方法,例如最基本的数据库表设计,基本的SQL优化,MYSQL的集群,读写分离,分库分表,架构上增加缓存层等,他们的优缺点……,综合这些然后再结合我们项目特点,最后我们在技术选型的时候选了谁。

  如果你这样有条不紊,有理有据的回答了问题而且还说出这么多问题外的知识点,面试官会觉得你不只是一个会写代码的人,而是你逻辑清晰,你对技术选型,有自己的理解和思考

 

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
转载请注明出处: https://daima100.com/10679.html

(0)
上一篇 2022-12-23
下一篇 2022-12-23

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注