MySQL的SQL语句编写

关联更新

UPDATE A,B SET A.c1 = B.c1,A.c2 = B.c2 Where A.c1 = B.c1 and B.age > 50;

UPDATE A INNER JOIN B ON A.id = B.id WHERE B.age > 50;

关联查询语句

六种关联查询

交叉连接(CROSS JOIN):没有任何关联条件，结果是笛卡尔积，结果集很大，没有意义

SELECT * FROM A,B(,C)

SELECT * FROM A CROSS JOIN B(CROSS JOIN C)

内连接(INNER JOIN)：多表中同时符合某种条件的数据记录的集合

SELECT * FROM A,B WHERE A.id = B.id 

SELECT * FROM A INNER JOIN B ON A.id = B.id 

# 等值连接 
SELECT * FROM A INNER JOIN B ON A.id = B.id 

# 不等值连接 
SELECT * FROM A INNER JOIN B ON A.id > B.id

# 自连接 
SELECT * FROM A T1 INNER JOIN A T2 ON T1.id = T2.pid

外连接(LEFT OUTER JOIN/RIGHT OUTER JOIN)：以左表为主，先查询出左表，按照ON后的关联条件匹配右表，没有匹配到的用NULL填充，可以简写成LEFT JOIN

联合查询(UNION与UNION ALL)：把多个结果集集中在一起，UNION之前的结果集为基准，需要注意的是联合查询的列数要相等，相同的记录行会合并。如果使用UNION ALL不会合并重复记录

SELECT * FROM A UNION SELECT * FROM B 

全连接(FULL JOIN)：MySQL不支持全连接

SELECT * FROM A LEFT FROM B ON A.id = B.id UNION 
SELECT * FROM A RIGHT FROM B ON A.id = B.id 

嵌套查询：用一条SQL语句的结果作为另外一条SQL语句的条件

SELECT * FROM A WHERE id IN (SELECT id FROM B)

MySQL的查询优化

查找分析查询速度慢的原因

• 记录慢查询日记, 分析查询日记，不要直接打开慢查询日志分析，这样比较浪费时间和精力，可以使用pt-query-digest工具进行分析
• 使用show_profile,使用set profiling = 1开启，服务器上执行的所有语句会检测消耗的时间，存到临时表中

# 开启show_profile
set profiling = 1;

• 查看SQL语句临时表

show profiles;

• 查看每条语句执行的历程

show profile for query 临时表ID;

• show status会返回一些计数器，show global status 查看服务器级别的所有计数，有时根据这些计数，可以猜测出哪些操作代价较高或者消耗时间多

show status;

• 观察是否有大量的线程处于不正常的状态

show processlist;

• 使用explain分析单条SQL使用情况

explain select * from a;
desc select * from a;

优化查询过程中的数据访问

• 访问数据太多导致查询性能下降
• 确认应用程序是否在检索大量超过需要的数据，可能是太多行或列
• 确认MySQL是否在分析不必要的数据行
• 避免使用如下SQL语句：
  • 查询不需要的记录，使用limit解决
  • 多表关联返回全部列,指定A.id,A.name
  • 避免使用SELECT *
  • 重复查询相当的数据，可以缓冲数据，下次直接读取缓冲
• 是否在扫描额外的记录，使用explain进行分析，如果发现查询需要扫描大量的数据，但只返回少数的行，可以通过如下技巧去优化。使用索引覆盖扫描，把所有用的列放到索引中，这样存储引擎不需要回表获取对应行就可以返回结果
• 改变数据库和表的结构，修改数据表范式
• 重写SQL语句，让优化器以更优的方式来执行

优化长难的查询语句

一个复杂查询还是多个简单查询?

MySQL内部每秒能扫描内存中上百万行数据，相比之下，响应数据给客户端就要慢的多。使用尽可能少的查询是好的，但是有的时候将一个大的查询分解为多个小的查询是很有必要的。

切分查询

将一个大的查询，切分为多个小的相同的查询，包含大数据拆分处理，一次处理一定量的数据。

分解关联查询

可以将一条关联语句分解成多条SQL来执行,这样做的好处在于：

• 让缓冲的效率更高
• 执行单个查询可以减少锁的竞争
• 在应用层做关联可以更容易对数据进行拆分

优化特定类型的查询语句

优化count()查询

count()中会忽略所有的列，直接统计所有的列数，因此不要使用count(列名)。MyISAM中，没有任何条件的WHERE条件的count()非常快，当有WHERE条件，MyISAM的count统计不一定比其他表引擎快

优化方案为：

• 可以使用explain查询近似值，用近似值替代count(*)
• 增加汇总表

优化关联查询

• 确定ON或者USING子句的列上有索引
• 确保GROUP BY和ORDER BY中只有一个表中的列，这样MySQL才有可能使用到索引

优化子查询

• 尽可能的使用关联查询进行替代

优化GROUP BY和DISTINCT

• 这两种查询均可使用索引来优化，是最有效的优化方法。关联查询中，使用标识列进行分组的效率会更高。如果不需要ORDER BY,进行GROUP BY时使用ORDER BY NULL,MySQL不会进行文件排序
• WITH ROLLUP超级聚合，可以挪到应用程序处理

优化LIMIT分页

• 可以记录上次查询的最大ID，下次查询时直接根据该ID查询

优化UNION查询

• UNION ALL的效率高于UNION