MySQL数据库优化（五）——MySQL查询优化

一、mysql查询类型（默认查询出所有数据列）

1、内连接 
默认多表关联查询方式，查询出两个表中所有字段；可省略inner join 关键字

2、外连接 查询出某一张表中的所有数据

（1）左连接
查询出第一张表的所有字段

（2）右连接
查询出第二张表的所有字段，表一匹配数据为空的返回null

3、子连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
1--内连接  查询出bookID=book类型ID的记录  

2SELECT tb.bookName,tby.bookTypeName FROM t_book tb,t_bookType tby WHERE tb.bookTypeId=tby.id;

3

4--外连接（左连接）

5SELECT  tb.bookName,tby.bookTypeName FROM t_book LEFT JOIN t_bookType ON t_book.bookTypeId=t_bookType.id where ......;

6--外连接（右连接）

7SELECT  tb.bookName,tby.bookTypeName FROM t_book RIGHT JOIN t_bookType ON t_book.bookTypeId=t_bookType.id where ......;

8

9--子查询

10SELECT * FROM t_book WHERE booktypeId IN (SELECT id FROM t_booktype);

11SELECT * FROM t_book WHERE booktypeId NOT IN (SELECT id FROM t_booktype);

12SELECT * FROM t_book WHERE price>=(SELECT price FROM t_pricelevel WHERE priceLevel=1);

13SELECT * FROM t_book WHERE EXISTS (SELECT * FROM t_booktype);

14SELECT * FROM t_book WHERE NOT EXISTS (SELECT * FROM t_booktype);

15SELECT * FROM t_book WHERE price>= ANY (SELECT price FROM t_pricelevel);

16SELECT * FROM t_book WHERE price>= ALL (SELECT price FROM t_pricelevel);

17

18

二、查询优化思路

1、为什么会慢？
在尝试做查询优化之前，得明白是什么让查询变慢？如果把查询看作一个由n个子任务组成的任务，随着子任务增多，sql关联查询也增多；优化查询，实际上就是优化这n个子任务，要么消除一些子任务，要么减少子任务执行次数。

2、有哪些子任务？

     mysql执行一个查询一般有这么几个流程：客户端发送查询语句到服务端——>服务器解析查询语句——>生成执行计划——>执行查询；其中执行时整个生命周期最重要的阶段，其中包括对数据库引擎的调用、排序、分组的数据处理过程。
三、优化方向

1、优化查询数据

     低效的查询基本原因：访问请求的数据太多，不可避免的需要进行大量筛选工作；

     错误尝试：select * from t_user t inner join t_role r inner join t_permission p where …..

     错误原因：使用内连接，查询出三个表中的所有数据列

     正确方式：select t.name,r.rolename,p.pname from t_user t inner join t_role r inner join t_permission p where

2、切分查询
分而治之，将复杂的查询切分成小查询，每次查询只返回一小部分结果
错误尝试：select * from t_user t where createData>DATE_SUB(NOW(),INTERVAL 3 MONTH)
delete from t_user t where createData>DATE_SUB(NOW(),INTERVAL 3 MONTH)

     错误原因：当user表中数据量巨大时，一次性查询或删除表中大量数据均会导致等待停顿；

     正确方式：select * from t_user t  where createData>DATE_SUB(NOW(),INTERVAL 3 MONTH)  limit 0，1000；

                     先查询表中符合条件的前一1000条数据；

3、分解关联查询
错误尝试：select t.name,r.rolename,p.pname from t_user t inner join t_role r inner join t_permission p where
错误原因：当user表中数据量巨大时，一次性查询或删除表中大量数据均会导致等待停顿；

      正确方式：select * from t_user where  t.age=10;
select t. rolename from t_user where …
select t. pname from t_user where …
分解关联查询表面上好像原本1个sql就干完的事，现在非得由3个sql去完成，实际上它比关联查询更有优势：

（1）提高缓存效率:对于第一条查询中age=10这条记录的所有字段已经缓存在mysql中，供第二个查询语句使用
（2）减少单个查询间的锁竞争
（3）减少冗余字段的查询     
四、总结 在平时的应用中，尤其在在java开发提供了良好的数据持久化框架，对于mysql的查询优化并未过分关系，并且在使用sql执行查询时，可能也经常使用到多表关联查询，在使用这些sql拼接的过程中，对于sql优化不能不知晓，在应对高并发问题中，除了在web服务器前做负载、平行扩展等措施之外，数据库高并发的解决方案也与其并重，而这一个一个sql就是应多高并发的优化基础，不容小觑。