sql编写与优化的基础原理。

数据库优化几个方面：库表设计，表的物理设计（索引，数据类型，分区表），sql语句优化

经常看到有人会写出一些很复杂的sql。我很好奇他们是怎样想到这样写的。毕竟我写的都是很简单的sql。

来看一个很常见的场景：

有一个用户发帖表有如下三个字段，user_id,bolg_id,post_date。一个用户可以发n个帖子。

现在要你给出一个报表：显示每个用户最后发帖的时间，以及最后发的帖子的id。

其sql如下，：

select u.user_id,u.bolg_id,u.post_date from user_bolg u
where not exists(
   select 1 from user_blog u2 using(user_id) where u.post_date < u2.post_date
)

另外一种写法，更符合直觉，建立一个按用户分组最新的日期的集合，和user_blog关联，找到blog_id,

select u.user_id,u.post_date,max(bolg_id) FROM user_blog u join
(select u2.user_id,max(u2.post_date) from user_blog u2 GROUP BY u2.user_id,post_date) temp
on (u.user_id =temp.user_id and u.post_date =temp.post_date)
GROUP BY u.user_id,u.post_date

最后还有一个利用外连接的方案，如下：

select u.user_id,u.post_date,u.blog_id FROM user_blog u LEFT OUTER JOIN
(user_blog u2 )
on (u.user_id =u2.user_id and u.post_date<u2.post_date)
where u2.bolg_id is null

这个利用了关联不到数据会置null的特点。是一个巧妙的实现。不好理解，但是通过我随后的对等算法分析后，就可以很容易理解该语句的含义。

现在问题是，这样写是怎么想到的呢。

首先sql总是要被服务器反应为算法去执行的，而sql主要操作的是数据结构就是集合，主要包括对集合进行遍历，筛选，排序，分组，交并补这些操作。

另外一方面理清了sql和算法的对应，对优化sql就能做到心中有数，毕竟大多程序员优化算法都很有一套。

如果清楚了sql怎么被翻译成算法，应该能反向思考出对应的算法用sql如何写。

首先看一个最基本的sql

select a.* from a where a.id >1;

其翻译后的算法应该是:

List<row> lstResult;

foreach(row in a.rows){

　　if(row.id > 1){

　　　　lstResult.add(row);
　　}
}
return lstResult;

关联查询，对应算法为嵌套循环遍历：

select a.id,b.name from a join b using(id) where a.id >1;

List<row> lstResult;
foreach(rowA in a.rows){
　　if(rowA.id > 1){
　　foreach(rowA in a.rows){
　　　　foreach(rowB in b.rows)
　　　　{
　　　　　　if(rowA.id == rowB.id){
　　　　　　　　lstResult.add(rowA.id,rowB.name);　
　　　　　　}
　　　　}　　
　　} 
}
return lstResult;

 

 

回到开头的例子，一般常规都会想到按user_id分组，但是如下是写法无法通过的。

select user_id,max(post_date),bolg_id
from user_blog
group by user_id

但是如果你的bolg_id是按时间单调递增的，可以有如下写法

select u.user_id,max(u.post_date),max(u.blog_id) FROM user_blog u 
GROUP BY u.user_id

 

换个思路，如果该表是个list集合，如果让你写算法实现该需求，怎么写

如下，嵌套循环查找每个帖子，在子循环中查找集合中是否有更新的时间。如果没找到，则该帖子最新

List lstResult<row>;
foreach(row in u.rows){
  bool isNew = true;
  foreach(row2 in u2.rows)       {
    //如果不是同一个用户，检索下一个帖子
    if(row.user_id <> row2.user_id) continue;
   //如果发现该帖子的时间更早，肯定不是不是最新的，退出循环
    if(row.post_date < row2.post_date)
    { 
       isNew = false; 
       continue;          
    }
  //如果遍历完u2也没有找到更新的，则符合条件
  if (isNew) lstResult.add(row);
}
return lstResult;

 

看到sql应该立刻脑补到对应的代码算法，可以较好的找到优化路径。