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

This article introduces the relevant knowledge of "how to implement MySql explain". Many people will encounter such a dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Through the execution plan, you can know the query method, index usage, the amount of data that needs to be scanned, and whether temporary tables or sort operations are required.

Query execution plan: explain + SQL statement

Id: number

Select_type: query type

Table: tabl

Type: type

Possible_keys: index used for forecasting

Key: the index actually used

Key_len: the length of the index actually used

Ref: references between tables

Rows: the amount of data queried by the index

Extra: additional information

Create the following table:

CREATE TABLE course (cid INT (3), cname VARCHAR (20), tid INT (3))

CREATE TABLE teacher (tid INT (3), tname VARCHAR (20), tcid INT (3))

CREATE TABLE teacherCard (tcid INT 3, tcdesc VARCHAR 200)

INSERT INTO course VALUES (1); INSERT INTO course VALUES (2); INSERT INTO course VALUES (3); INSERT INTO course VALUES (4)

INSERT INTO teacher VALUES (1); INSERT INTO teacher VALUES (2); INSERT INTO teacher VALUES (3)

INSERT INTO teacherCard VALUES (1); INSERT INTO teacherCard VALUES (2); INSERT INTO teacherCard VALUES (3)

The above data can be queried in many ways:

The first method:

SELECT t. * FROM teacher course cjournal teachercard tc WHERE t.tid = c.tid AND t.tcid = tc.tcid AND (c.cid = 2 OR tc.tcid = 3)

View the execution plan:

1. Id number

Id: same id value, from top to bottom, sequential execution, t3-tc3-c4

Let's insert a little more data into the teacher table:

INSERT INTO teacher VALUES (4); INSERT INTO teacher VALUES (5); INSERT INTO teacher VALUES (6)

Let's take a look at the implementation plan:

We found that the execution plan had changed and became tc3-c4-t6.

The reason why the execution order of the table changes due to the change in the number of numbers: Cartesian product

Priority query for tables with small amount of data

The id value is different: the higher the id value, the more priority the query. (essence: when nesting subqueries, check the inner layer first, then the outer layer)

Query the teacher description of the SQL course (desc)

EXPLAIN SELECT tc.tcdesc FROM teacherCard tc, course c, teacher t WHERE t.tid = c.tid AND t.tcid = tc.tcid AND c.cname = 'sql'

Change the above multi-table query into a subquery

EXPLAIN SELECT tc.tcdesc FROM teacherCard tc WHERE tc.tcid = (SELECT t.tcid FROM teacher t WHERE t.tid = (SELECT c.cid FROM course c WHERE c.cname = 'sql'))

Change it to subquery + multiple tables:

EXPLAIN SELECT t.tname, tc.tcdesc FROM teacher t, teacherCard tc WHERE t.tcid = tc.tcid AND t.tid = (SELECT c.tid FROM course c WHERE c.cname = 'sql')

Id values are the same and different: the larger the id value, the higher the priority; the same id value is executed sequentially from top to bottom.

2. Select_type:

PRIMARY: contains the main query in the subquery SQL (outermost)

SUBQUERY: contains subqueries in the subquery SQL (not the outermost layer)

SIMPLE: simple query (excluding subqueries, union)

DERIVED: derived query (using temporary tables)

A: there is only one table in the from subquery

EXPLAIN SELECT cr.cname FROM (SELECT * FROM course WHERE tid IN (1m 2)) cr

B: in the from subquery, if there is a table1 union table2, then table1 is derived

Table2 is union.

EXPLAIN SELECT cr.cname FROM (SELECT * FROM course WHERE tid = 1 UNION SELECT * FROM course WHERE tid = 2) cr

UNION: example above

UNION RESULT: tell developers which tables have union queries between them

3. Type: index type, type

The common ones are as follows: the more to the left, the higher the performance, sorted as follows

System > const > eq_ref > ref > range > index > all

Among them, system and const are only ideal situations, and ref and range can be achieved in practice.

System (ignore): a main query with only one data in a system table or derived table with only one data.

Const: SQL, user Primary Key or unique index of only one piece of data can be found

Eq_ref: unique index: for each index key query, returns matching unique row data (one and only, no more, no zero), which is common in unique indexes and primary key indexes.

Ref: non-unique index: for each index key query, returns all matching rows (0, multiple)

Range: retrieves a specified range of rows, followed by a range query (between)

Special: in sometimes fails, which translates to indexless all)

Index: query the data of all indexes, and the fields queried are indexes.

EXPLAIN SELECT tid FROM test01

All: query the data in all tables. If the query field is not an index, a full table scan is required.

EXPLAIN SELECT tname FROM test01

System and const: the result is only one piece of data

Eq_ref: multiple results, but each piece of data is unique

Ref: multiple results, but each piece of data is 0 or more

4. Possible_keys: the index that may be encountered, not accurate, is a kind of prediction

5. Key: the index actually used

6. Key_len: the length of the index, used to determine whether the composite index is fully used.

CREATE TABLE test_k1 (NAME CHAR (20) NOT NULL DEFAULT'); ALTER TABLE test_k1 ADD INDEX index_name (NAME)

EXPLAIN SELECT * FROM test_k1 WHERE NAME =''

In utf-8, a character occupies 3 bytes. Because name is 20 in length, key_len is 60. Because the name field cannot be null, it is 60 bytes. If the name field can be null, one byte is used for identification, and key_len is 61 bytes.

Utf8: 1 character occupies 3 bytes

Gbk:1 characters account for 2 bytes

Mysql uses 2 bytes to identify variable length

7. Ref: indicates the field referenced by the current table

Select... From... Where a.c = b.x (where b.x can be a constant, const)

8. Rows: the number of data queried by the index

9. Extra:

Using filesort: high performance consumption, which requires an "extra" sort (query), which is common in order by statements.

For a single index, using filesort does not appear if the sort and lookup are the same field, and using filesort appears if the sorting and lookup are not the same field.

Composite index: cannot span columns (best left prefix)

ALTER TABLE test_k1 ADD INDEX name_name1_name2_index (NAME,name1,name2); EXPLAIN SELECT * FROM test_k1 WHERE name1 =''ORDER BY name2;-- Using filesort EXPLAIN SELECT * FROM test_k1 WHERE NAME1 =' 'ORDER BY name2;-- Using filesort EXPLAIN SELECT * FROM test_k1 WHERE NAME =' 'ORDER BY name1

Where and order by are used in the order of composite indexes, not across columns or out of order.

Using temporary: high performance loss, using temporary watches. It generally appears in group by statements.

EXPLAIN SELECT NAME FROM test_k1 WHERE NAME IN ('1pm, 2m, 3') GROUP BY NAME;EXPLAIN SELECT NAME FROM test_k1 WHERE NAME IN, GROUP BY NAME1;-- Using TEMPORARY

Note: group columns with which columns are queried

Using index: performance improvement, index overwriting (overwriting index), because the original file is not read, only data is obtained from the index file (no query back to the table is required)

Index overrides: index overrides as long as all the columns used are in the index

ALTER TABLE test_k1 ADD INDEX name_name1_index (NAME,name1)

EXPLAIN SELECT NAME FROM test_k1 WHERE NAME ='';-- Using INDEX does not need to query back to the table EXPLAIN SELECT NAME1 FROM test_k1 WHERE NAME ='';-- Using INDEX does not need to query back to the table

EXPLAIN SELECT NAME2 FROM test_k1 WHERE NAME ='';-- query back to the table

If using index is used, it will affect possible_keys and key

a. If there is no where, the index appears only in the key

b. If there is a where, the index appears in key and possible_keys

Using where (need to query back to the table)

EXPLAIN SELECT name2 FROM test_k1 WHERE NAME2 ='';-- query back to the table

New features added to Using index condition after version 5.6

The query column is not completely covered by the index, and the query condition can be used with the index.

That's all for the content of "how to execute MySql explain". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.