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 = ?

 小结：

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

　　 例子2：文章搜索优化

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

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

select * from ARTICLES where CONTENT like "% 目标 %";

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

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

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

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

小结：

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

总结：

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

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

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