SQL查询优化实践
为什么要优化
- 系统的吞吐量瓶颈往往出现在数据库的访问速度上，即随着应用程序的运行，数据库的中的数据会越来越多，处理时间会相应变慢，且数据是存放在磁盘上的，读写速度无法和内存相比
如何优化
- 设计数据库时：数据库表、字段的设计，存储引擎
- 利用好mysql自身提供的功能，如索引，语句写法的调优
- 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的集群，读写分离，分库分表，架构上增加缓存层等，他们的优缺点……，综合这些然后再结合我们项目特点，最后我们在技术选型的时候选了谁。
如果你这样有条不紊，有理有据的回答了问题而且还说出这么多问题外的知识点，面试官会觉得你不只是一个会写代码的人，而是你逻辑清晰，你对技术选型，有自己的理解和思考