What do you know about MySQL? _ Execution plan. mysql understands the execution plan.

What do you know about MySQL? _ Execution plan. mysql understands the execution plan.
I. database storage engine

View mysql version:

View the storage engines supported by the current mysql version:

This article only introduces two common storage engines:

1. MyISAM

Transaction processing not supported

Table lock: High deadlock probability and low concurrency

2. InnoDB (default storage engine after MySQL 5.5)

Support Transaction Processing

Row lock: Low deadlock probability and high concurrency

3. Definitions of row locks and table locks

SQL example: update stu set c1 =? Where c2 = ?;

Row lock: other threads cannot operate on the data currently retrieved, but other row data can be operated.

Table lock: other threads cannot operate the current table data.

4. view the data table storage engine and specify the data table storage engine 4.1 to create a data table

Create table stu (

Stuidint primary key auto_increment,

Stunamevarchar (255 ),

Stuageint,

Studescriptionvarchar (255)

);

4.2 view the storage engine used by the data table

Use a visualization tool to view the table information:

You can specify the storage engine when creating a data table:

View the DDL statements of the table. When the database executes the SQL table creation statement, it adds a part of content to us by default. The end part is the specified ENGINE = InnoDB, if you want to use another database storage engine, you can modify this value when creating a table, but we do not recommend you change it to another storage engine !!!

Ii. Database Indexes

• PRIMARY KEY
• UNIQUE
• INDEX
• FULLTEXT
  

Iii. Execution Plan

Where can I start with slow SQL optimization?

By viewing the execution plan, you can check whether the SQL statement uses indexes during execution.

Insert a part of the simulation data in the stu table:

Insertinto stu (stuname, stuage, studescription) values ('zhang san', 18, 'playing chess ');

Insertinto stu (stuname, stuage, studescription) values ('Li si', 22, 'Bad today ');

Insertinto stu (stuname, stuage, studescription) values ('wang wu', 16, 'health ');

Insertinto stu (stuname, stuage, studescription) values ('zhao liu', 21, 'like romance of Three Kingdoms ');

Insertinto stu (stuname, stuage, studescription) values ('tian 7', 17, 'recently studying machine learn ');

View the execution plans of the following SQL statements:

Select stuid, stuname, stuage, studescription from stu;

Select stuname, stuage, studescription from stu where stuid = 3;

Select stuname, stuage, studescription from stu where stuname = 'tag7 ';

Select stuname, stuage, studescription from stu where stuid> 2;

The id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra fields are described as follows:

1. id

• The larger the number, the more advanced the execution
• If the number is as big as the number, execute from top to bottom.
• If it is null, it indicates that this is a result set and does not need to be used for query.
  

2. select_type

• Type of each SELECT clause
• Common options

3. table

• Displays the name of the queried table. If an alias is used for the query, the alias is displayed here.
• If the operation on the data table is not involved, null is displayed.
• If <derived N> is displayed, it indicates that this is a temporary table, and N indicates the id in the execution plan, indicating that this table is from this query.
  
• If <union M, N> is displayed, it indicates that this is a temporary table, indicating that this table comes from the result set where the union query id is M and N.
  

4. partitions5. type

• Performance from good to bad: system, const, eq_ref, ref, fulltext, ref_or_null, unique_subquery, index_subquery, range, index_merge, index, ALL
• Except ALL, other types can use indexes.
• Apart from index_merge, other types can only use one index.
  
• Explanation of different connection types

6. possible_keys

• Query indexes that may be used
  

7. key

• Query the actually used index. If no index exists, it is NULL.
• If select_type is index_merge, more than two indexes may appear here.
  

8. key_len

• Only the index length used by the where condition is calculated, and the sorting and grouping are not calculated into key_len even if the index is used.

9. ref

• For constant equivalent query, const is displayed here.
• For connection query, the associated fields of the driver table are displayed in the execution plan of the driver table.
• If an expression or function is used in the query condition, or the condition column is implicitly converted, it may be displayed as func.
  

10. rows

• Number of scanned rows estimated by the execution plan
  

11. filtered

• Percentage of the instances that meet the query conditions. Unit: percentage.
  

12. Extra

This column contains detailed information about MySQL queries, in the following situations:

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.