Shulou(Shulou.com)06/01 Report--

Optimize data access

1. Whether unwanted data is requested from the database

Solution:

a. Add limit after query

B. write the required columns after Select instead of *

two。 Whether additional data was scanned

Database access speed from slow to fast: full table scan, index scan, range scan, unique index query, constant reference

Type of the MYSQL Explain command (how the database engine accesses the table): Const > ref > range > index > all

1. Const constant reference

If the query is based on the primary key, the query will be converted to a constant, fetching only a certain row of data. It's the fastest one.

2. Ref

The lookup condition column uses an index and is not a primary key and unique (duplicate values are allowed). Only the data that determines the value is fetched, possibly multiple rows.

3. Ref_eq unique index query

Compared with ref, ref_eq has only one result set for this type of search.

4. Range range scan

Index or primary key, when in a certain range

4. Index index scan

Only the index is scanned

5. All full table scan

The general way that mysql applies where conditions is from good to bad:

1. Use where conditional filtering in the index, which is done at the storage engine layer

two。 Use index override scanning to filter unwanted data directly from the index and return results, which is done at the mysql server layer without going back to the table query (using index appears in extra)

3. Return data from the data table, and then filter the data that does not meet the criteria. In the server layer, mysql needs to read the record from the data table and then filter it (using where appears in extra)

A good index allows the query to use the appropriate access type and reduces the number of rows scanned.

The basis for executing the query:

1. The client sends a query to the server

two。 The server first checks the cache and returns the result immediately if it hits the cache.

3. The server performs sql parsing, preprocessing, and then the optimizer generates the corresponding execution plan

4. According to the execution plan generated by the optimizer, Mysql calls the storage engine API to execute the query plan

5. Return the result to the client

Step 1 (the client sends a query to the server):

The communication between Mysql client and server is half-duplex. Either the server sends data to the client, or the client sends data to the server, which cannot be carried out at the same time.

Therefore, in order to control the flow, the server will throw a corresponding error when the query statement sent by the client is too long and exceeds the max_allowed_packet parameter.

When the client gets data from the server, most of the library functions connected to mysql can get all the result sets and cache them in memory. Mysql needs to wait for all the data to be sent to the client to release the resources occupied by this query.

Step 3 (server performs sql parsing, preprocessing, query optimization):

First of all, parse the sql statement with keywords to generate a "parsing tree"

The parser validates syntax rules

The preprocessor checks whether the parsing tree is legal and verifies permissions

The query optimizer uses an optimization strategy to generate an optimal execution plan:

1. Redefine the order of associated tables

two。 Convert an external connection to an inner connection

3. Optimize count (), min (), max () (read only the first or last piece of data according to b-tree)

4. Estimate and convert to a constant expression

5. Terminate the query in advance

6. Comparison of the list in () (sort the data of the in list first and determine whether the value meets the condition by binary lookup)

Generate an execution plan-instruction tree: because mysql associations are nested from a table, the execution plan is a depth-first tree on the left.

Step 4 (call the storage engine API to execute the query plan)

The query optimizer is at the server level, while statistics (how many pages are there for each table or index, what is the cardinality of each index for each table, the length of data rows and indexes, the distribution of indexes, etc.) are at the storage engine layer.

How MYSQL executes the associated query:

Mysql believes that any query is an association, not just when a query relates to two tables.

In MySQL, there is only one Join algorithm, which is Nested Loop Join nested iteration.

Simple Nested-Loop Join simple nesting loop: take R1 from the driver table to match all the columns of the S table, and then R2MagneR3, until all the data in the R table are matched, and then merge the data, you can see that this algorithm has to RN the S table for many times, although it is simple, but it is still relatively expensive.

Index Nested-Loop Join index nesting loop: because there are indexes on undriven tables, records are no longer needed for comparison, but indexes can be used to reduce the comparison, thus speeding up the query.

Optimization:

Select a small number of records as the driver table

Give priority to optimizing the inner loop of NestedLoop

Ensure that the Join conditional field on the driven table has been indexed

Limitations of Mysql query optimizer

1. Associated subquery

Using in plus subquery, the performance is very poor

/ / not finished

two。 Maximum and minimum

For max () and min () queries, mysql is not optimized well, such as:

Select min (actor_id) from sakila.actor where first_name = "pene"

Because there is no index on the first_name field, mysql performs a full table scan

One optimization method is: (make mysql scan the primary key)

Select actor_id from sakila.actor use index (primary) where first_name = "pene" limit 1

Query with the primary index, because b-tree is sorted according to the primary key order, so limit 1 = min (actor_id), looking for the index until the first piece of data of the compound where condition

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.