MySQL database optimization (5)—MySQL query optimization

1. Mysql query type (all data columns are queried by default)
1. Inner join
Default multiple tables Relevant query mode, query all fields in two tables; you can omit the inner join keyword
2. Outer join query all data in a certain table
(1) Left Connection
Query out all the fields of the first table
(2) Right join
Query out all the fields of the second table, the matching data in table one is empty Return null

3, sub-connection

##

--内连接  查询出bookID=book类型ID的记录  
SELECT tb.bookName,tby.bookTypeName FROM t_book tb,t_bookType tby WHERE tb.bookTypeId=tby.id;

--外连接（左连接）
SELECT  tb.bookName,tby.bookTypeName FROM t_book LEFT JOIN t_bookType ON t_book.bookTypeId=t_bookType.id where ......;
--外连接（右连接）
SELECT  tb.bookName,tby.bookTypeName FROM t_book RIGHT JOIN t_bookType ON t_book.bookTypeId=t_bookType.id where ......;

--子查询
SELECT * FROM t_book WHERE booktypeId IN (SELECT id FROM t_booktype);
SELECT * FROM t_book WHERE booktypeId NOT IN (SELECT id FROM t_booktype);
SELECT * FROM t_book WHERE price>=(SELECT price FROM t_pricelevel WHERE priceLevel=1);
SELECT * FROM t_book WHERE EXISTS (SELECT * FROM t_booktype);
SELECT * FROM t_book WHERE NOT EXISTS (SELECT * FROM t_booktype);
SELECT * FROM t_book WHERE price>= ANY (SELECT price FROM t_pricelevel);
SELECT * FROM t_book WHERE price>= ALL (SELECT price FROM t_pricelevel);

two , Query optimization ideas
#1. Why is it slow? Before trying to optimize the query, you must understand what makes the query slow? If the query is regarded as a task composed of n subtasks, as the subtasks increase, the SQL related queries also increase; optimizing the query actually means optimizing these n subtasks, either eliminating some subtasks or reducing the number of subtask executions. .

2. What are the subtasks?
Mysql generally has several processes for executing a query: the client sends the query statement to the server——>The server parses the query statement——>Generates an execution plan——> Execute the query; execution is the most important stage of the entire life cycle, including the data processing process of calling the database engine, sorting, and grouping.
3. Optimization direction
1. Optimize query data
Basic reason for inefficient query: access There is too much requested data, which inevitably requires a lot of screening work; All data columns in a table
Correct way:

select * from t_user t inner join t_role r inner join t_permission p where .....

2. Split query
Divide and conquer to separate complex The query is divided into small queries, and each query only returns a small part of the results

Error attempt:

select t.name,r.rolename,p.pname from t_user t inner join t_role r inner join t_permission p where

Error reason: When the amount of data in the user table When the table is huge, one-time query or deletion of a large amount of data in the table will cause waiting pauses; The previous 1000 pieces of data;
3. Decomposition related query

Error attempt:

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)

Cause of error: when user When the amount of data in the table is huge, one-time query or deletion of a large amount of data in the table will cause waiting pauses;

Correct way:

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就是应多高并发的优化基础，不容小觑。  

 以上就是MySQL数据库优化（五）—MySQL查询优化的内容，更多相关内容请关注PHP中文网（www.php.cn）！