MySQL sql 语句优化资料和学习笔记

MySql 里的IFNULL、NULLIF和ISNULL用法 - 海 华 - 博客园
http://www.cnblogs.com/JuneZhang/archive/2010/08/26/1809306.html

MySQL性能优化的最佳经验，随时补充 - 简书
http://www.jianshu.com/p/5dd73a35d70f

MySQL 性能优化的最佳20多条经验分享Mysql脚本之家
http://www.jb51.net/article/24392.htm

MySQL SQL优化 - ggjucheng - 博客园
http://www.cnblogs.com/ggjucheng/archive/2012/11/11/2765465.html

explain的使用 - you_yang - 博客园
http://www.cnblogs.com/you-yang/archive/2012/01/18/2325841.html

MySql的优化步骤 - 曾是土木人 - 博客园
http://www.cnblogs.com/hongfei/archive/2012/10/20/2732516.html

Mysql下优化SQL的一般步骤_MySQL中文网
http://c.biancheng.net/cpp/html/1472.html

阅读同学动态 SQL 优化学习

参考资料：
http://www.educity.cn/wenda/389787.html
http://blog.csdn.net/muxiaoshan/article/details/7617533
http://blog.sina.com.cn/s/blog_634c33eb010188ai.html

第 1 句：原来的语句（张卫写的，用 where 逗号，相当于 inner join，全部匹配）

SELECT sud.id,sud.user_id,TYPE,user_real_name,uui.`level`,sud.content,pic_img,
DATE_FORMAT(sud.create_time,'%Y-%m-%d %H:%i:%s') AS create_time 
FROM s_user_dynamic sud,langying.u_user uu,u_user_info uui 
WHERE sud.user_id = uu.user_id AND uu.user_id = uui.user_id 
  AND (LOWER(user_real_name) LIKE '%%' OR LOWER(sud.content) LIKE '%%') 
  AND sud.user_id IN (SELECT user_id FROM langying.u_classes_user ucu WHERE ucu.classes_id = 559) 
ORDER BY sud.create_time DESC 

第 2 句：我写，用左表连接，过滤条件写在 on 里面，得到条数多）

SELECT sud.id,sud.user_id,TYPE,user_real_name,uui.`level`,sud.content,pic_img,
DATE_FORMAT(sud.create_time,'%Y-%m-%d %H:%i:%s') AS create_time 
FROM s_user_dynamic sud LEFT JOIN langying.u_user uu ON sud.user_id = uu.user_id 
  LEFT JOIN u_user_info uui ON uu.user_id = uui.user_id AND sud.user_id IN 
  (SELECT user_id FROM langying.u_classes_user ucu WHERE ucu.classes_id = 559)
WHERE LOWER(user_real_name) LIKE '%%' OR LOWER(sud.content) LIKE '%%'
ORDER BY sud.create_time DESC  

第 3 句：（我写，用内连接，过滤条件写在 on 里面，得到条数多）

SELECT sud.id,sud.user_id,TYPE,user_real_name,uui.`level`,sud.content,pic_img,
DATE_FORMAT(sud.create_time,'%Y-%m-%d %H:%i:%s') AS create_time 
FROM s_user_dynamic sud INNER JOIN langying.u_user uu ON sud.user_id = uu.user_id 
  INNER JOIN u_user_info uui ON uu.user_id = uui.user_id AND sud.user_id IN 
  (SELECT user_id FROM langying.u_classes_user ucu WHERE ucu.classes_id = 559)
WHERE LOWER(user_real_name) LIKE '%%' OR LOWER(sud.content) LIKE '%%'
ORDER BY sud.create_time DESC  

第 4 句：（我写，用左表连接，过滤条件写在 where 后面，得到条数和第 1 条一样）

SELECT sud.id,sud.user_id,TYPE,user_real_name,uui.`level`,sud.content,pic_img,
  DATE_FORMAT(sud.create_time,'%Y-%m-%d %H:%i:%s') AS create_time 
FROM s_user_dynamic sud LEFT JOIN langying.u_user uu ON sud.user_id = uu.user_id 
  LEFT JOIN u_user_info uui ON uu.user_id = uui.user_id
  WHERE  sud.user_id IN (SELECT user_id FROM langying.u_classes_user ucu WHERE ucu.classes_id = 559)
  ORDER BY sud.create_time DESC 

2016 年 6 月 26 日
从查询结果中，体会这两句的不同之处。

语句1：

SELECT * FROM u_classes_user ucu
LEFT JOIN `u_user_role` uur ON ucu.`user_id` = uur.`user_id` AND uur.`role_id` = '42'

结果：

语句 2：

SELECT * FROM u_classes_user ucu
LEFT JOIN `u_user_role` uur ON ucu.`user_id` = uur.`user_id` 
WHERE uur.`role_id` = '42'

结果：