Mysql Technology Insider-notes-Chapter 3 query and processing _ MySQL

Mysql Technology Insider-notes-Chapter 3 query and processing

Chapter 3 query processing

Logical query processing: (8) SELECT (9) DISTINCT

(1) FROM

(3) JOIN

(2) ON

(4) WHERE

(5) GROUP

(6) WITH {CUBE | ROLLUP}

(7) HAVING

(10) ORDER

(11) LIMIT

SELECT has three filtering processes: WHERE, ON, HAVING, and ON are the first filtering processes to be executed.

WHERE filter:

1. because the data has not been grouped, you cannot filter attacks using where_condition = MIN (col) in the WHERE filter.

2. the column selection operation has not been performed, so the column alias used in the SELECT statement is not allowed, for example, SELECT city AS c FROM t WHERE c = 'Shanghai' is not allowed.

The filtering in the WHERE filter is different from that in the ON filter. For filtering in OUTERJOIN, after the ON filter is completed, records in the retained table filtered by the ON condition will be added, while the records filtered in the WHERE condition will be permanently filtered. In inner join, there is no difference between the two because no external rows are added.

3. HAVING is a filter used to filter grouping conditions. subqueries cannot be used as grouping aggregate functions, such as having count (SELECT...) <2 is invalid.

4. SELECT: The column alias cannot be used in other alias expressions in SELECT, for example:

Mysql> SELECT order_id AS o, o + 1 AS n FROM orders;

　　

5. DISTINCT: If the DISTINCT clause is specified in the query, a temporary memory table will be created (if the memory cannot be stored, it will be placed on the disk ). The table structure of this memory temporary table is the same as that of the virtual table generated in the previous step. The difference is that a unique index is added to the column for the DISTINCT operation to remove duplicate data. In addition, for queries using group by, the use of DISTINCT is redundant, because it has been grouped and no rows will be removed.

6.Most DBAs and Developers mistakenly think that when selecting data in the table, the record will be retrieved according to the size ORDER of the primary key in the table.Same.The main cause of this classic error is that you do not understand what a real relational database is. Common query operations in databases correspond to some operations of the set: Selection, projection, join, and union, intersection, difference, and division. Although the final result is presented to the user in the form of a two-dimensional table, it is a series of set operations in the database. Therefore,You need to understand the records in a table in the form of a set. NoORDERClause queries only query data from the set, but the set does not have the concept of order. Therefore, you do not need to assume any specific sequence for the rows in the table.

7. LIMIT: Select the specified row data starting from the specified position from the virtual table in the previous step. The result of the LIMIT statement that does not apply order by may also be unordered. Therefore, the LIMIT clause is usually used together with order.

LIMIT n, m

Indicates selecting m records from the Nth record. Most developers prefer to use such statements to solve the typical paging problem on the web. This is not a big problem for small-scale data. HoweverFor large-scale data,LIMIT n, mEfficiency is very low.Because data needs to be selected each time.