Thresh

EXPLAIN

MySQL provides an EXPLAIN command that analyzes SELECT statements and outputs details of SELECT execution for developers to optimize.

EXPLAIN SELECT * from USERS where id >1 ;
Copy the code

Select_type Specifies the query type. Common values are as follows:

SIMPLE: indicates that the query does not contain sub-queries or union PRIMARY: indicates that this query is the outermost query union: indicates that this query is the second or subsequent query of the union EXPLAIN SELECT * from user WHERE id < 3; DEPENDENT UNION: the second or subsequent query in the UNION that uses the RESULT of the external query UNION RESULT: SUBQUERY: SELECT the DEPENDENT SUBQUERY: The SELECT subquery depends on the results of the outer query.Copy the code

Type Indicates the mode used by the storage engine to query data. An important property to determine whether a query is a full table scan or a partial index-based scan. The common attribute values are as follows, and the efficiency increases from top to bottom.

ALL: indicates full table scan, with the lowest performance. Index: indicates a full table scan based on an index. The index is scanned first and then all table data. Range: indicates the range of indexes to be queried. Use >, >=, <, <=, in, and so on. Ref: indicates that a non-unique index is used for single-value query. Eq_ref: usually occurs in multi-table JOIN query, indicating that each record of the previous table matches only one row of the subsequent table. Const: use primary key or unique index for equivalent query, constant query. NULL: indicates that the table is not accessed and the fastestCopy the code

Possible_keys indicates the possible_keys index that can be used in query. Note that it is not always used; the index name is displayed.

Key indicates the actual index used in the query, and the index name is displayed.

The Rows MySQL query optimizer estimates, based on the statistics, how many rows the SQL needs to scan to get the results. In principle, the less rows, the higher the efficiency, you can intuitively understand the efficiency of SQL.

Key_len indicates the number of bytes of the index used by the query. You can determine whether all composite indexes are used. Key_len is computed as follows:

String Type The string length depends on the character set: latin1=1, GBK =2, UTF8 =3, utF8MB4 =4 CHAR (n) : N x character set length VARCHAR (n) : N x Character set length + 2 bytes Value type TINYINT: 1 byte SMALLINT: 2 bytes MEDIUMINT: 3 bytes INT, FLOAT: 4 bytes BIGINT, DOUBLE: 8 bytes Time type DATE: 3 bytes TIMESTAMP: 4 bytes DATETIME: The NULL attribute takes up 1 byte. If a field is NOT NULL, this item is NOT availableCopy the code

“Extra” means a lot of Extra information. Relevant information will be displayed in “Extra” for various operations. Common ones are as follows:

Using WHERE indicates that the query needs to query data through the index back to the table. Using index indicates that a query requires an index. The index can satisfy the required data. Using filesort indicates that the results of the query need to be sorted. The amount of data is small in memory, and large in disk. Therefore, Using filesort suggests optimization. A Using temprorary query uses temporary tables. This occurs when operations such as deduplication and grouping are performed.Copy the code

Back to the table query

InnoDB index has clustered index (primary key index) and secondary index. The leaf node of the clustered index stores row records. InnoDB must have only one. Leaf nodes of secondary indexes store primary key values and index field values. Row records cannot be directly located through secondary indexes. Generally, it is necessary to scan the index tree twice. Locating the primary key value through the secondary index and then the row record through the clustered index is called a table-back query, which has lower performance than scanning the index tree.

Summary: Query the primary key through the index, and then query the record information through the cluster index

Cover index

That is, index overwriting can be triggered when the Extra field of the output result of Explain is Using index.

EXPLAIN SELECT e.* from EMPLOYEE e INNER JOIN COMPANY c on e.companyId = c.id
Copy the code

Left-most prefix rule

When a composite index is used, the left-most prefix is the left-most prefix. That is, if the left-most column is used in the query, the index will be used. If the search starts from the second column of the index, the index will be invalid

CREATE TABLE `user2` (
  `userid` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL DEFAULT ' ',
  `password` varchar(20) NOT NULL DEFAULT ' ',
  `usertype` varchar(20) NOT NULL DEFAULT ' '.PRIMARY KEY (`userid`),
  KEY `a_b_c_index` (`username`,`password`,`usertype`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
Copy the code

Select * from index where username exists;

explain select * from user2 where username = '1' and password = '1';
Copy the code

If there is no username, no index will be used:

explain select * from user2 where password = '1';
Copy the code

LIKE the query

MySQL > select * from ‘like’;

The index can be used only if the % character is appended to it.

select * from user where name like '%o%'; Select * from user where name like 'o%'; Select * from user where name like '%o'; // It doesn't workCopy the code

NULL query

If a column of the MySQL table contains a NULL value, is the index containing the column valid?

For MySQL, NULL is a special value. Conceptually, NULL means “an unknown value” and is handled a little differently than other values. For example, the =, <, > operators cannot be used, the result of the arithmetic operation on NULL is NULL, the count does not include NULL lines, etc. NULL requires more storage space than an empty string, etc

The NULL column requires additional space to record whether its value is NULL. For MyISAM tables, each empty column takes an extra bit, rounded to the nearest byte.

MySQL can use indexes on columns that contain NULL, but NULL is different from other data, and it is not recommended to allow NULL on columns. It is best to set NOT NULL and give a default value, such as 0 and an empty string. If datetime is used, you can also set the current system time or a fixed special value, such as ‘1970-01-01 00:00:00’.

Indexing and sorting

MySQL supports sorting by filesort and index. Filesort searches the results first and sorts them in the cache or disk, which is inefficient. Using index means that the index can be automatically sorted without additional sorting operations.

Filesort has two sorting algorithms: double-path sort and single-path sort.

• Dual-path sort: Two disk scans are required to obtain user data. The first time the sort field is read out, and then sort; The second time to read the other field data.
• Single-way sort: Query all required column data from disk and sort the results in memory back. If the query data exceeds the cache sort_buffer, it will result in multiple disk reads and the creation of temporary tables, resulting in multiple I/OS, which will increase the burden.
• Solution: use select * sparingly; Increase sort_BUFFer_SIZE capacity max_LENGTH_FOR_sort_data capacity.

If we Explain SQL analysis, the Extra attribute shows Using filesort, indicating that filesort sorting method is used, which needs to be optimized. If Extra shows Using index, it means overwriting the index, and also means that all operations are completed on the index. Index sorting can also be used. It is recommended that you use overwriting the index as much as possible.

Sort by index

Explain select ID from user ORDER BY ID; Explain select ID from user WHERE age=18 ORDER BY name; explain select id from user WHERE age=18 ORDER BY name; // Corresponding to (age,name) indexCopy the code

Will use filesort – style sorting

Explain select ID from user order by age ASC,name DESC; // Index (age,name) WHERE clause and ORDER BY clause satisfy the leftmost prefix, Explain select ID from user where age>10 order by name; explain select id from user where age>10 order by name; Explain select ID from user ORDER BY name (age,name); Explain select ID from user ORDER BY name,age; // select * from user ORDER BY name,age; Explain select ID from user WHERE name=' Tom 'ORDER BY age; explain select id from user WHERE name=' Tom' ORDER BY age; Explain select ID from user ORDER BY ABS (age); // Corresponding (age) indexCopy the code