NIAO Flash3D V1.0 Stable Edition Release

2023-01-12   ES  

Explain syntax

 The execution plan "anti -compilation" into a Select statement, running show warnings can get the query statement that is optimized by the MySQL optimizer, but it is not guaranteed to be optimal, and many places are not optimized.
From the output of Explain Extend, we canSeeing the execution of SQL, it is still very helpful to analyze SQL.
 Explain used for partition table

Through Explain, you can know how MySQL processes statements to analyze the performance bottleneck of query or table structure. It can be obtained through Expalin:
1. Table read order
2. The type of operation of the reading operation of the table
3. Which indexes can be used
4. Which indexes are actually used
5. Reference between tables
6. How many rows of each table are queried by optimizers
Explain display field
1. ID: The execution order logo of the statement
ID, if the same, can be considered a group, executed from top to bottom; in all groups, the larger the ID value, the higher the priority, the more
2. SELECT_TYPE: The type of query used, mainly in the following types of query:
1). Simple Simple Type
statement no sub -query or union
2). primary
If there is any complex sub -part in the query, the outermost query is marked as: Primary,not the main key.

The second select after

3).  union 
Union is marked as Union, the first select is primary
such as: query statement
EXPLAIN  select * from t where id = 3 union  select * from t where id = 2;
4).dependent subquery
Sub -query the first select statement in the inner layer in the middle
5).  dependent  union
SELECT after Union in the query depends on the external results set.
query statement
8). uncacheable subquery 
result set that cannot be caught in query

The results of merging in

9). union result 
3. table 
Show this step to be intervieweddatabase

The name of the table in

4. type 
This column is very important, showing which category is used and whether there are indexes. Type represents the connection method of the table specified in the inquiry execution plan (QEP). The worst connection type from best to the worst connection is 1.System, 2.Const, 3. EQ_reg, 4. Ref, 5. Range, 6.INDEX, 7. ALL
1). system
System is a special case, that is, there is only one record in the table.
2). const
CONST is a query condition in the where conditions, and there is a record match in the table. Because it is constant, you only need to read it once.
3). eq_ref
will only have one matching result at most, generally access by the main key or unique index. Generally appears in the sentences of connection query.
4). ref
Non -unique sexual index scan, return to all the lines that match a single value. Common search for non -unique prefixes with non -unique indexes and unique indexes
Index scanning, common in query of Between, <,>, in, etc.
6). index
Full index tree scannedFull Index Scan
7). all
full table scan, the effect is the least ideal.
5. possible_keys
Query can be used to use the index. If there is no index to use it, it will be displayed as NULL. The adjustment of the index when optimizing the content is very important.
The index selected from Possible_keys
7. key_len
Key_len column displaymysqlThe key length of the use, if the key is null, the length is null. The length of the index used, the shorter the better.
8. ref 
represents the connection matching conditions of the above table, that is, which columns or constants are used to find the values on the index column
In this example, the IDX_COL1_COL2 of the T1 table can be fully used by the key_len. Col1 matches the color of the T2 table, and color2 matches a constant, that is, ‘AC’
systemThe statistical information collected, the number of recorded records of the result set
10. extra
EXTRA: The additional detail information implemented in the query may be the following content:
1). DISTINCT: Find the distinct value, so when mysql finds the result of the first match, the query of the value is turned into the query of other values;

The optimization method in the

Inquiry is mainly to use the use of the NULL values that cannot be accessed by indexing;
2). ImpossibleWhereneoticeDaFTERREADINGCONSTTABLEs: MySQLQLQURYOMIZER determines the impossible results through the statistical information collected;
3). Notables: Use fromdual in the query statement or does not include any from classes;
4). Notexist: Optimized method used by MySQLQLQLQLQURYOPTIMIZER in the left connection, which can partially reduce the number of data access by changing the composition of the original Query;
5). RangecheckedForeachrecord (IndexMap: N): Until the description of the official HSQL manual, when mysqlqueryoptimizer not found a good index that can be used, if
It is found that if the column value of the previous table is known, some indexes may be used. For each line combination of the previous table, mysql checks whether the access method can be used to use the Range or index_merge access method
6). SelecttableSoptimized Away: When we use some polymer functions to access a certain field of indexing, mysqlqueryoptimizer will directly position it at one time at one time indexing.
The data line required to complete the entire query. Of course, the premise is that GroupBY operations cannot be operated in Query. Such as using min () or max ();
7). USINGFILESORT: When our Query contains Orderby operation and cannot use indexes to complete the sorting operation, mysqlqueryOptimizer has to choose the corresponding sorting algorithm
to implement.
8). USingIndex: The required data only needs to be obtained in the index without having to get the data in the table;
9). USingIndexForgroup-By: The data access is the same as usingIndex. The required data only needs to read the index, and when the groupby or distinct sentence is used in Query,
If the group field is also in the index, the information in EXTRA will be USINGINDEXFORGROUP-BY;
10). Usingtemporary: When mysql must use a temporary table in certain operations, USINGTEMPORARY will appear in Extra information. Mainly common in Groupby and Orderby
11). Useingwhere: If we are not read all the data of the table, or you can obtain all the data you need only through the index, the USINGWhere information will appear;
12). USingwhereWithPushedCondition: This is a message that only appears in the NDBCluster storage engine, and it also needs to open the ConnectionPushdown optimization function
can be used. The control parameter is Engine_Condition_Pushdown.

  `i_id` int(11) NOT NULL,
  `i_im_id` int(11) DEFAULT NULL,
  `i_name` varchar(24) DEFAULT NULL,
  `i_price` decimal(5,2) DEFAULT NULL,
  `i_data` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`i_id`)
CREATE TABLE `orders` (
  `o_id` int(11) NOT NULL,
  `o_d_id` tinyint(4) NOT NULL,
  `o_w_id` smallint(6) NOT NULL,
  `o_c_id` int(11) DEFAULT NULL,
  `o_entry_d` datetime DEFAULT NULL,
  `o_carrier_id` tinyint(4) DEFAULT NULL,
  `o_ol_cnt` tinyint(4) DEFAULT NULL,
  `o_all_local` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`o_w_id`,`o_d_id`,`o_id`),
  KEY `idx_orders` (`o_w_id`,`o_d_id`,`o_c_id`,`o_id`),
  CONSTRAINT `fkey_orders_1` FOREIGN KEY (`o_w_id`, `o_d_id`, `o_c_id`) REFERENCES `customer` (`c_w_id`, `c_d_id`, `c_id`)
-This article is transferred from:


Related Posts

appwidget display the current position

Step by step (12) expansion (increase the attributes of user management)

Two -way queue sdut1446

Flutter bottom navigation bar botttomnavigationbar

NIAO Flash3D V1.0 Stable Edition Release

Random Posts

MATLABKINECT obtain images and physical data osmanthus

How to use the EasyRecovery Tool for data recovery

[12.19 Simulation Tournament] Rectangle | Scanning Line+SET

Android Development Note (145) Improved the garbage cleansing animation of the application of Bao

jquery chosen drop -down box to beautify the plug -in