Database optimization & amp; lt; 2 & amp; gt; SELECT optimization for SQL optimization-_ MySQL

Database optimization-SELECT optimization for SQL optimization-mysql internal optimization policy bitsCN.com

Database Bottlenecks often occur in query statements. when such problems occur, the general step is to check whether the correct index is used,

You can use the explain SQL statement to check the corresponding fields. a reasonable index will increase your data access speed,

Everything has a degree. if there are too many indexes, more indexes will be maintained during insertion. This will also be a big overhead, but how can this happen?

It is suitable. You are welcome to discuss this. I will not do it any more. However, a table usually has a primary key with a unique key. it should not be a problem to get a few more.

1.1. how does mysql execute the where clause?

These statements are usually fast:

SELECT COUNT(*) FROM tbl_name;SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;SELECT MAX(key_part2) FROM tbl_name  WHERE key_part1=constant;SELECT ... FROM tbl_name  ORDER BY key_part1,key_part2,... LIMIT 10;SELECT ... FROM tbl_name  ORDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10;

However, the premise is that all the Accessed fields have indexes, because the statements such as min (), max (), and order by will use sorting, but if

After the index is created, the binary method is used to traverse the Btree during reading. this speed is very fast. if there is no index, this can only be used for full table scanning,

The speed will be slow. for the first statement, you can directly read this value in the tables of the MyISAM and MEMORY storage engines.

The two storage engines have corresponding counters to maintain data, but innodb must scan the entire table to obtain the results, as shown below:

mysql> explain select count(*) from b_order/G;*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: b_order         type: indexpossible_keys: NULL          key: status      key_len: 1          ref: NULL         rows: 274146        Extra: Using index1 row in set (0.01 sec)

When this statement is used, all rows are scanned, but we use the primary key index, which is faster.

1.2. mysql internal optimization policy

1, count (*).

2. both mysql and oracle use reading data while judging data. when the data is met, it is sent to the client, and the query ends when the SQL requirement is met.

Remember: if you do not require all rows for your data, for example, you 'd better add the limit statement as long as the data of the current SQL statement is satisfied.

In this example, the database will end the query in advance, occupying less memory, reading less data, and transmitting less network bandwidth.

3. if The HAVING statement does not use aggregate functions such as count () or min () and does not have a group by statement, it will be merged into the where statement.

4. all constant tables take precedence over other tables for reading. a constant table is defined:

1. an empty table or a table with only one row

2. if the where clause is used in the table, a non-empty primary key or unique index is used, and this search is a constant expression.

3. Another case is that a join table has only a few rows, and whether the table will be read first (you can try it)

The following statement:

SELECT * FROM t WHERE primary_key=1;SELECT * FROM t1,t2  WHERE t1.primary_key=1 AND t2.primary_key=t1.id;

Explanation: 1. the first one is a small table with only one row

2. check t1 first because only one row of data is returned.

5. let's talk about the join strategy: the database is not so intelligent, so we can find a good join sequence for execution. it will put all possible

Try to join the order, call the storage engine api to evaluate the cost, and then select the minimum cost for execution. Therefore, the table we write

The sequence is irrelevant to the optimizer.

6. when both group by and order by are used, if the fields are from the same table, join the table first.

7. for the data to be sorted, if you see that file sorting is used in the explain statement, but you are sure that this dataset is not very large,

You can specify SQL _SMALL_RESULT in SQL to use the memory temporary table sorting. this will be faster:

select sql_small_result * from table_name;

8. MySQL may obtain the query results from the index, but this is generally a numerical value.

9. The HAVING statement is the final filter result condition.

BitsCN.com