Mysql three of relational database: start from the life cycle of a sql

The mysql tutorial column introduces the life cycle of SQL in relational databases.

Connector:

Establish a connection with MySQL for querying SQL statements and judging permissions.

Query cache:

• If the statement is not in the query cache, it will continue to the subsequent execution phase. After the execution is completed, the execution result will be stored in the query cache
• If the query hits the cache, MySQL can directly return the result without performing the following complex operations, improving efficiency

Analyzer:

Perform hard parsing of SQL statements, and the analyzer will first perform lexical analysis. Analyze the components of an SQL statement. Determine whether the input SQL statement satisfies the grammatical rules.

Optimizer:

The optimizer decides which index to use when there are multiple indexes in the table; or when there are multiple indexes in a statement When tables are joined (join), the connection order of each table is determined. The logical results of different execution methods are the same, but the execution efficiency will be different, and the role of the optimizer is to decide which solution to use.

Executor:

• With index: the first call is to fetch the first line that meets the conditions, and then loop This interface takes the next row that meets the conditions, and finally returns the query result to the client
• No index: call the InnoDB engine interface to fetch the first row of this table, judge the sql query condition, skip if not, if If yes, store this row in the result set; call the engine interface to fetch the next row, and repeat the same judgment logic until the last row of the table is fetched. The executor returns the record set composed of all rows satisfying the conditions in the above traversal process as a result set to the client

Understand the execution plan

The EXPLAIN command outputs how MySQL will execute your SQL statement, but will not return data

How to use

[root@localhost][(none)]> explain select * from table name where project_id = 36;
 +----+-------------+--------------------------+---  ---------+------+---------------+------------+----  -----+-------+--------+----------+-------+
 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 +----+-------------+--------------------------+---  ---------+------+---------------+------------+----  -----+-------+--------+----------+-------+
 | 1 | SIMPLE | table name | NULL | ref | project_id | project_id | 4 | const | 797964 | 100.00 | NULL |
 +----+-------------+--------------------------+---  ---------+------+---------------+------------+----  -----+-------+--------+----------+-------+ 

id

• Identical execution order from top to bottom
• Id is different, the bigger the id value, the higher the priority, the earlier it will be executed

select_type

• SIMPLE: Simple select query, the query does not contain subquery or union
• PRIMARY: The query contains subparts, and the outermost query is marked as primary
• DERIVED: It is part of the subquery from
• DEPENDENT SUBQUERY: The first in the subquery A SELECT, the subquery depends on the result of the outer query
• SUBQUERY indicates that the subquery is included in the select or where list,
• MATERIALIZED: indicates the subquery of the in condition after where
• UNION: indicates the second or subsequent select statement in union
• UNION RESULT: the result of union

table

• table object

type

system > const > eq_ref > ref > range > index > ALL (query efficiency)

• system: There is only one piece of data in the table, this type is a special const type
• const: For the equivalent query scan of the primary key or unique index, only one row of data is returned at most. It is very fast because it is only read once.
• eq_ref: This type usually appears in the join query of multiple tables, which means that for each result of the previous table, only one row of the result of the latter table can be matched, and the comparison operation of the query is usually =, the query More efficient
• ref: This type usually appears in multi-table join queries, for non-unique or non-primary key indexes, or queries that use the leftmost prefix rule index
• range: This type of range scan usually appears in , >, >=, <, <=, IS NULL, , BETWEEN, IN() operations
• index: index tree scan
• index: index tree scan

li>

• ALL: full table scan

possible_keys

• Possible indexes to use , Note that it may not be used
• If there is an index on the field involved in the query, the index will be listed
• When the column is NULL, it is necessary to consider whether the current SQL Need to optimize

key

• Display the index actually used by MySQL in the query, if no index is used, display NULL.
• If a covering index is used in the query (covering index: the indexed data covers all the data to be queried�, then call it a covering index

SELECT user_name, city, age FROM user WHERE user_name='Tony' AND age='28' AND city=’Shanghai’;

Because the fields to be queried (user_name, city, age) are included in the index columns of the composite index, coverage is used Index query, check whether the covering index is used. The value of the Extra in the execution plan is Using index, which proves that the covering index is used. The covering index can greatly improve the access performance.

Use index for sorting

If you can use index to sort in the sorting operation, it can greatly improve the sorting efficiency Speed, to use the index to sort needs to meet the following two points:

• The column order after the ORDER BY clause must be consistent with the column order of the composite index, and the sorting direction of all sorting columns ( Forward order/reverse order) need to be consistent
• The queried field value needs to be included in the index column and satisfy the covering index

Available demo for sorting:

• SELECT user_name, city, age FROM user_test ORDER BY user_name;
• SELECT user_name, city, age FROM user_test ORDER BY user_name,city;
• SELECT user_name, city, age FROM user_test ORDER BY user_name DESC,city DESC;
• SELECT user_name, city, age FROM user_test WHERE user_name='Tony' ORDER BY city;

Sorting is not available demo:

• SELECT user_name, city, age FROM user_test ORDER BY user_name gender;
• SELECT user_name, city, age , gender FROM user_test ORDER BY user_name;
• SELECT user_name, city, age FROM user_test ORDER BY user_name ASC,city DESC;
• SELECT user_name, city, age FROM user_test WHERE user_name LIKE 'W%' ORDER BY city;

Data acquisition suggestion

Do not return data that is not required by the user program to limit the number of returns

LIMIT: MySQL cannot return the amount of data as required, that is, MySQL will always query all the data. The use of the LIMIT clause is actually to reduce the pressure of network data transmission, and will not reduce the reading of data. Fetch the number of rows.

Remove unnecessary columns

• The SELECT * statement retrieves all fields in the table, regardless of whether the data in the field is relevant to the calling application Useful, this will cause waste of server resources, and even have a certain impact on server performance
• If the structure of the table changes later, the SELECT * statement may get incorrect data
• When executing the SELECT * statement, you must first find out which columns are in the table before you can start to execute the SELECT * statement, which will cause performance problems in some cases
• Use the SELECT * statement It will not make the covering index, which is not conducive to the performance optimization of the query.

Advantages of using the index correctly

• Avoid full table Scanning

1. When single table query, full table scan needs to query every row
2. When multi-table query, full table scan needs to retrieve at least every row in all tables

• Increase speed

1. You can quickly locate the first row of the result set
2. Exclude irrelevant The result
3. There is no need to check each row for MIN() or MAX() values

• Improve the efficiency of sorting and grouping
• When you can Avoid row loop-up when using a covering index

The cost of indexing

• If there are too many indexes, Data modification will be slow

1. Affected indexes need to be updated
2. High pressure for write-intensive environments

• Index consumes too much disk space

1. InnoDB storage engine stores index and data together
2. Need to monitor disk space

li>

Index best practices

Consider using indexes for the following columns

• In the WHERE clause Column
• Column in ORDER BY or GROUP BY clause
• Table connection condition column

Consider using prefix index for string column

• Compared with loop up more quickly
• Reduce disk I/O

SELECT Consider when the statement is inefficient

• Avoid full table scan
• Try to increase the index

1. WHERE statement
2. Table connection conditions

• Use ANALYZE TABLE to collect statistical information
• Consider the optimization of the storage engine layer

Tuning table join method

• Increase index on the column of ON or USING clause
• Use SELECT STRAIGHT_JOIN to enforce table join order
• Increase indexes on ORDER BY and GROUP BY columns
• Join connections are not necessarily more efficient than subqueries

More related free learning recommendations: mysql tutorial(video)

The above is the relational database mysql three: For details starting from the life cycle of a sql, please pay attention to other related articles on 1024programmer.com for more information!