Iii. Index Optimization Analysis (II)

4. Performance analysis

4.1 Common performance Bottlenecks of MySQL

(1) CPU: THE CPU runs at full load when data is loaded into memory or read from disk.

(2) IO: The DISK IO bottleneck occurs when the load data is much larger than the memory capacity.

③ Server hardware bottleneck: Run the top, free, iostat, and vmstat commands to check the system performance and status.

(4) Database server configuration problems;

4.2 MySQL Performance Analysis

2 the EXPLAIN abstract

Use the EXPLAIN keyword to simulate the MySQL optimizer’s execution of SQL queries to see how MySQL understands your SQL statements to analyze performance bottlenecks in SQL queries or table structures.

A laboratory report similar to a hospital examination

Function:

① Obtain the reading order of the table;

② Get the operation type of data read operation;

③ Check which indexes are available;

(4) Check which indexes are actually used;

⑤ Check the reference relationship between tables;

⑥ See how many rows of each table are queried by the optimizer;

4.2.2 Usage:

EXPLAIN + SQL statements

Example:

mysql> explain select * from tbl_emp where id = 2;+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | tbl_emp  | const | PRIMARY | PRIMARY | 4 | const | 1 | | + - + -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00) sec)Copy the code

EXPLAINColumn of information returned after execution:

id: Serial number of the query, containing a set of numbers, indicating that the query is being executedSELECTThe order of clauses or tables of operations;
select_type: indicates the query type.
table: indicates the table where the data resides.
type: Access type of the query;
possible_keys: Displays one or more indexes that may be in this table;
key: Actual used index;
key_len: indicates the number of bytes used in the index. This column can be used to calculate the length of the index used in the query
ref: the column that shows the index is used;
rows: displays the number of rows that MySQL considers necessary to check when executing a query.
Extra: Additional important information;

4.2.2 Explanation of each analysis field

Use the following SQL statement to create the data table

CREATE TABLE t1(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
CREATE TABLE t2(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
CREATE TABLE t3(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
CREATE TABLE t4(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
INSERT INTO t1(content) VALUES(CONCAT('t1_'.FLOOR(1+RAND()*1000)));
INSERT INTO t2(content) VALUES(CONCAT('t2_'.FLOOR(1+RAND()*1000)));
INSERT INTO t3(content) VALUES(CONCAT('t3_'.FLOOR(1+RAND()*1000)));
INSERT INTO t4(content) VALUES(CONCAT('t4_'.FLOOR(1+RAND()*1000)));
Copy the code

4.2.2.1 id

The sequence number of the query, which contains a set of numbers indicating the order in which the SELECT clause or operation table is executed in the query;

There are three main cases:

(1) If the IDS are the same, the execution sequence is from top to bottom.

mysql> explain select * from t1,t2,t3 where t1.id = t2.id and t2.id = t3.id;+----+-------------+-------+--------+---------------+---------+---------+-----------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-----------------+------+-------+ | 1 | SIMPLE | t1 | ALL | PRIMARY | NULL | NULL | NULL | 1 | | | 1 | SIMPLE | t2 | eq_ref | PRIMARY | PRIMARY | 4 | base_crud.t1.id |  1 | | | 1 | SIMPLE | t3 | eq_ref | PRIMARY | PRIMARY | 4 | base_crud.t1.id | 1 | | +----+-------------+-------+--------+---------------+---------+---------+-----------------+------+-------+ 3 rows in set (0.00 SEC)Copy the code

(2) The id is different. If it is a sub-query, the id sequence number will increase. The larger the ID value is, the higher the priority will be, and the higher the execution will be.

mysql> explain select t1.id from t1 where t1.id in (select t2.id from t2 where t2.id in (select t3.id from t3 where t3.content = ' '));+----+--------------------+-------+-----------------+---------------+---------+---------+------+------+----------------- ---------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+-----------------+---------------+---------+---------+------+------+----------------- ---------+ | 1 | PRIMARY | t1 | index | NULL | PRIMARY | 4 | NULL | 1 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | t2 | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using index; Using where | | 3 | DEPENDENT SUBQUERY | t3 | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using where | +----+--------------------+-------+-----------------+---------------+---------+---------+------+------+----------------- ---------+ 3 rows in set (0.00 SEC)Copy the code

③ If the ids are the same, they can be considered as a group and executed from top to bottom. The larger the GROUP ID is, the higher the priority is

mysql> explain select t2.* from t2, (select t3.* from t3) s3 where s3.id = t2.id;+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+ | id | select_type  | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+ | 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | | | 1 | PRIMARY | t2 | const | PRIMARY | PRIMARY | 4 | const | 1 |  | | 2 | DERIVED | t3 | ALL | NULL | NULL | NULL | NULL | 1 | | +----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+ 3 rows in set (0.00 SEC)Copy the code

Summary: Each ID number. Represents a single query, the SQL query as few rows as possible

4.2.2.2 select_type

Indicates the query type, which is used to distinguish common query, combined query, and sub-query.

Select_type properties	meaning
`SIMPLE`	simple`SELECT`Query, which does not contain subqueries or`UNION`
`PRIMARY`	If the query contains subparts of a complex query, the outermost query is marked as`PAIMARY`
`DERIVED`	in`FROM`The subqueries contained in the list are marked as`DERIVED`MySQL recursively executes these subqueries, putting the results into temporary tables.
`SUBQUERY`	in`SELECT` 或 `WHERE`The list contains subqueries.
`DEPEDENT SUBQUERY`	in`SELECT` 或 `WHERE`The list contains subqueries based on the outer layer.
`UNCACHEABLE SUBQUERY`	A cached subquery cannot be used.
`UNION`	If the second`SELECT`Appear in the`UNION`After that, it is marked as`UNION`; if`UNION`Included in the`FROM`Clause in the subquery, the outer layer`SELECT`Will be marked as`DERIVED`
`UNION RESULT`	from`UNION`Table to obtain the result`SELECT`

(1) SIMPLE: indicates a single table query

mysql> explain select * from t1;+----+-------------+-------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1 | | + - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

② PRIMARY: If the query contains any complex subparts, the outermost query is marked as PRIMARY

mysql> explain select * from (select * from t2) a;+----+-------------+------------+--------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+------+---------+------+------+-------+ | 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | | | 2 | DERIVED | t2 | ALL | NULL | NULL | NULL | NULL | 1 | | + - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- + 2 rows in the set (0.00) sec)Copy the code

(3) DERIVED: The subqueries contained in the FROM list are labeled as DERIVED. MySQL executes these subqueries recursively, zooms the results into temporary tables.


mysql> explain select * from (select * from t2) a;+----+-------------+------------+--------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+------+---------+------+------+-------+ | 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | | | 2 | DERIVED | t2 | ALL | NULL | NULL | NULL | NULL | 1 | | + - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- + 2 rows in the set (0.00) sec)Copy the code

④ SUBQUERY: The SELECT or WHERE list contains subqueries

mysql> EXPLAIN SELECT t2.id FROM t2 WHERE t2.id = (SELECT t3.id FROM t3 WHERE t3.id = 1);+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ | id | select_type  | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ | 1 | PRIMARY | t2  | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index | | 2 | SUBQUERY | t3 | const | PRIMARY | PRIMARY | 4 | | 1 |  Using index | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ 2 Rows in set (0.00 SEC)Copy the code

⑤ DEPEDENT SUBQUERY: include subqueries in a SELECT or WHERE list based on the outer layer.

mysql> explain select t2.id from t2 where t2.id in (select t3.id from t3 where t3.content = 't3_993');+----+--------------------+-------+-----------------+---------------+---------+---------+------+------+----------------- ---------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+-----------------+---------------+---------+---------+------+------+----------------- ---------+ | 1 | PRIMARY | t2 | index | NULL | PRIMARY | 4 | NULL | 1 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | t3 | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using where | +----+--------------------+-------+-----------------+---------------+---------+---------+------+------+----------------- ---------+ 2 rows in set (0.00 SEC)Copy the code

SUPERQUERY is a single value, and DEPENDENT SUBQUERY is a set of values.

⑥ UNCACHEABLE SUBQUERY: indicates that the cache SUBQUERY cannot be used.

mysql> explain select * from t3 where id = (select id from t2 where t2.id = @@sort_buffer_size);+----+----------------------+-------+------+---------------+------+---------+------+------+----------------------------- ------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+----------------------+-------+------+---------------+------+---------+------+------+----------------------------- ------------------------+ | 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | | 2 | UNCACHEABLE SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table | +----+----------------------+-------+------+---------------+------+---------+------+------+----------------------------- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set (0.01 SEC)Copy the code

Caching is not used when @@ is used to reference system variables.

⑦ UNION: if the second SELECT occurs after the UNION, it is marked as UNION; If UNION is included in the subquery of the FROM clause, the outer SELECT is marked as DERIVED

mysql> explain select * from t1 union all select * from t2;+----+--------------+------------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+------------+------+---------------+------+---------+------+------+-------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 1 | | | 2 | UNION | t2 | ALL | NULL | NULL | NULL | NULL | 1 | | | NULL | UNION RESULT | < 2 > union1, | | NULL ALL | NULL | NULL | NULL | NULL | | + - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- + 3 rows in the set (0.00) sec)Copy the code

SELECT UNION RESULT from UNION table

4.2.2.3 table

Represents the table in which the data resides

4.2.2.4 type

The access type of the query;

attribute	meaning
system	The table has only one row of records (equal to the system table) is`const`The exception of type does not normally occur.
const	I found it by indexing it once,`const`Used to compare`primary key`or`unique`The index.
eq_ref	Unique index scan. For each index key, only one record in the table matches it. This is common for primary key or unique index scans.
ref	A non-unique index scan that returns all rows matching a single value. It is also essentially an index access that returns all rows that match a single value. However, it is possible to find multiple rows that match the criteria, so it is a mixture of lookup and scan.
range	Retrieves only rows in a specified range, using an index to select rows.`key`Column displays using which index are generally located in`WHERE`Statement`Between, <, >, in`Such a range scan is better than a full table scan because it only needs to start at one point in the index and end at another, rather than scanning the entire index.
index	appear`index`It is the SQL statement that uses the index butNo indexes are used for filtering, usuallyOverwrite indexes are used or sort groups are made by indexes.
ALL	Full table scan to find matching rows
index_merge	A combination of multiple indexes is required in a query, usually in a`OR`Keyword in SQL.
ref_or_null	A field requires both an associated condition and a condition`null`Value, the query optimizer will choose to use`ref_or_null`Join query.
index_subquery	Use index associated subqueries instead of full table scans.
unique_subquery	Similar to the`index_subquery`A unique index in a subquery.

The type of access to the query is an important metric, and the result values are from best to worst:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merege > unique_subquery > index_subquery > range > index > ALL

In general, make sure the query is at least range and preferably ref

index 和 ALLThe difference between:

Although both index and ALL read the entire table, index is read from the index and only traverses the index tree, while ALL is read from the hard disk. Index files are usually smaller than data files, so index is faster than ALL.

4.2.2.5 possible_keys

Displays one or more indexes that may be applied to this table. If an index exists on a field involved in a query, the index is listed but not necessarily used by the query.

4.2.2.6 key

If the value is NULL, no index is used.

If an overwrite index is used in the query, the index appears only in the key list.

4.2.2.7 key_len

Represents the number of bytes used in the index, which can be used to calculate the length of the index used in the query. The key_len field can help check if the index is being utilized.

The longer key_len is, the more indexes are used. That is, the more indexes are used, the more accurate the matching is.

In the case of no loss of accuracy, the shorter the better;

Count key_len as follows:

Int = 4; int = 4; varchar(20) = 20; Char (20) = 20;

Char (utF8, GBK, GBK, utF8, GBK, GBK);

③ Dynamic characters like vARCHar add 2 bytes;

(4) Add 1 byte to fields that are allowed to be empty;

The column type	`key_len`	note
`int`	4 + 1	allow`NUll`, plus 1 byte
`int not null`	4	Do not allow for`NULL`
`char(30) utf8`	30 times 3 plus 1	To allow for`NULL`
`varchar(30) not null utf8`	30 times 3 plus 2	Dynamic column type, plus 2 bytes
`varchar(30) utf8`	30 times 3 plus 2 plus 1	Dynamic column type, plus two bytes; To allow for`NULL`Plus one more byte
`text(10) utf8`	30 times 3 plus 3 plus 1	`text`The intercept part is treated as a dynamic column type and is allowed to be`NULL`

4.2.2.8 ref

The column that shows the index is used and is const if possible. Those columns or constants are used to find values on index columns.

4.2.2.9 rows

Based on table statistics and index selection, approximate the number of rows to read to find the desired record, with as few rows as possible.

4.2.2.10 Extra

Contains additional information that is not suitable for display in other columns, but is important.

(1) Using filesort

MySQL uses an external index sort for data, rather than reading the data in the order of the indexes in the table. The sort operation that MySQL cannot do with indexes is called ** “file sort” **

A sorted field in a query that can be accessed through an index to make sorting faster.

(2) Using temporary

Temporary tables are used to hold intermediate results. MySQL uses temporary tables when sorting query results. Common in sort ORDER BY and GROUP BY queries

Sorting ORDER BY and grouping query GROUP BY are the main culprits that slow down SQL execution, and temporary tables add to the SQL load.

(3) Using the index

The corresponding SELECT operation uses a Covering Index to avoid accessing the row of the table, which is efficient. Using WHERE indicates that the index is used to perform a key lookup. If Using WHERE is not present at the same time, it indicates that the index is used to read data rather than perform a lookup Using the index. Sort or group by means of an index.

Covering Index:

SELECTInstead of reading rows, MySQL can use indexes to return columnsSELECTFields in the list without having to read the data file again based on the index, that isThe query column is overwritten by the index created.

An index is an efficient way to find rows, but a typical database can also use an index to find data for a column, so it doesn’t have to read the entire row. After all, index leaf nodes store the data they index; There is no need to read rows when the desired data can be retrieved through the index. An index that contains (or overwrites) data that meets the query result is called an overwritten index.

An overwrite index is simplySELECTThe order and the order of the query and the compound index is exactly the same;

Note:

If you want to use an overwrite index, you must be careful to extract the required columns from the SELECT list. Do not use SELECT *, because if all the fields are indexed together, the index file will be too large and the query performance will deteriorate.

Using filesort: Using file sorting, affecting the efficiency of SQL execution;

Using temporary: A temporary table is used, which seriously affects the efficiency of SQL execution.

Using index: An overwrite index is used.

The above three parameters are basic methods to determine the efficiency of SQL execution.

(4) Using the where

Indicates WHERE filtering is used.

(5) Using the join buffer

Indicates that connection caching is used.

6. Impossible where

The value of the WHERE clause is always false and cannot be used to get any tuples.

⑦ select tables optimized away

In the absence of the GROUP BY clause, optimization of MIN/MAX operations based on indexes or COUNT(*) operations for MyISAM storage engines does not have to wait until the execution phase to perform the calculation. Optimization is completed during the query execution plan generation phase.

Today the distinct

Optimize distinct to stop looking for the same value once the first matching tuple is found.

5. Index optimization

5.1 Index Analysis

5.1.1 Single table optimization analysis

① Create a table

CREATE TABLE IF NOT EXISTS `article`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT (10) UNSIGNED NOT NULL,
`category_id` INT(10) UNSIGNED NOT NULL , 
`views` INT(10) UNSIGNED NOT NULL , 
`comments` INT(10) UNSIGNED NOT NULL,
`title` VARBINARY(255) NOT NULL,
`content` TEXT NOT NULL
);
INSERT INTO `article`(`author_id`,`category_id` ,`views` ,`comments` ,`title` ,`content` )VALUES
(1.1.1.1.'1'.'1'),
(2.2.2.2.'2'.'2'),
(3.3.3.3.'3'.'3');
Copy the code

The created database

mysql> select * from article;+----+-----------+-------------+-------+----------+-------+---------+ | id | author_id | category_id | views | comments | title | content | +----+-----------+-------------+-------+----------+-------+---------+ | 1 | 1 | 1 | 1 | 1 | 1 | 1 | | 2 | 2 | 2 | 2 | 2 | 2 | | 2 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | + - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + 3 rows in the set (0.00 SEC)Copy the code

② Query requirements

Select ‘category_id’ from category_id and ‘comments’ from’ category_id ‘and’ comments’ from ‘1’

mysql> select id,author_id from article where category_id = 1 AND comments >= 1 order by views desc limit 1;+ - + -- -- -- -- -- -- -- -- -- -- -- + | | id author_id | + - + -- -- -- -- -- -- -- -- -- -- -- + | | | 1 + 1 - + -- -- -- -- -- -- -- -- -- -- - + 1 row in the set (0.00 SEC)Copy the code

③ Analyze and optimize SQL

SQL query problems:

SQL full table scan;
Indexes are not and are not used;
Using file sorting is inefficient;

④ Solutions

Create indexes

create index idx_article_ccv on article(category_id, comments, views);
alter table `article` add index idx_article_ccv(`category_id`,`comments`,`views`)
Copy the code

View index

mysql> show index from article;+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+--- ---+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+--- ---+------------+---------+---------------+ | article | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | | | article | 1 | idx_article_ccv | 1 | category_id | A | 3 | NULL | NULL | | BTREE | | | | article | 1 | idx_article_ccv | 2 | comments | A | 3 | NULL | NULL | | BTREE | | | | article | 1 | idx_article_ccv | 3 | views | A | 3 | NULL | NULL | |  BTREE | | | +---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+--- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 4 rows in the set (0.00 SEC)Copy the code

Check the performance

After optimization, you can see that the query already uses indexes, but there are still file sorting problems.

Reasons why an index was created but not fully used:

According to the working principle of the B-tree index

(1) First sort;

(2) if you meet the same category_id then sort ‘Comments’;

③ If you encounter the same comments, sort the views again;

Comments >= 1 if comments >= 1 if comments >= 1 if comments >= 1

⑤ MySQL cannot use the views part behind the index for retrieval;

⑥ The index type used by MySQL is range.

Optimize the index

#Delete inappropriate indexes
mysql> drop index idx_article_ccv on article;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
#Create a new index
mysql> create index idx_article_cv on article(category_id, views);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
#View the new index
mysql> show index from article;+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+---- --+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+---- --+------------+---------+---------------+ | article | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | | | article | 1 | idx_article_cv | 1 | category_id | A | 3 | NULL | NULL | | BTREE | | | | article | 1 | idx_article_cv | 2 | views | A | 3 | NULL | NULL | | BTREE | | | +---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+---- - + -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 3 rows in the set (0.00 SEC)Copy the code

Check the performance

With the new index, the type is changed to ref, the index idx_article_cv is used and both columns are used, and there is no file sorting problem. The SQL performance is very good.

5.1.2 Optimization analysis of the two tables

Where does the associative index add to two tables?

① Create a table

CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
 
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
Copy the code

The created data table

mysql> select * from book;+--------+------+ | bookid | card | +--------+------+ | 1 | 1 | | 2 | 16 | | 3 | 18 | | 4 | 2 | | 5 | 13 | | 6 | 19 | | 10 8 July 18 | | | | | | | | 17 9 10 12 | | | | | | 10 11 12 13 | | | | | 13 15 | | 14 14 | | | | | 6 15 16 6 | | | | | 11 | | | | 17 18 19 10 | | | | | | 20 + 20 + -- -- -- -- -- -- -- -- -- -- -- -- -- - + 20 rows in the set (0.00 SEC)
mysql> select * from class;+----+------+ | id | card | +----+------+ | 1 | 10 | | 2 | 3 | | 3 | 4 | | 4 | 9 | | 5 | 14 | | 6 | 4 | | 7 | 16 | | 8 | 10 | | | | 9 19 10 6 | | | | | | 11 11 12 17 | | | | | | 12 of 13 14 6 | | | | | sixteen | | | | 1 of 16 17 15 | | | | | 10 18 19 8 | | | | | | | 9 + 20 - + -- -- -- -- -- - + 20 rows in the set (0.00 SEC)Copy the code

② Practical problems

Create index position when two tables are associated:

Will the left or right table be indexed when left joins?
Do you index the left or right table when joining right?

② Analysis and optimization

Left join class as the left table and book as the right table

Analysis shows that the query performs full table scan and does not use indexes.

③ Solutions

Create index for class table

#Create an index in the class table
mysql> create index idx_class_card on class(card);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
#Index of the query
mysql> show index from class;+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------ +------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------ +------------+---------+---------------+ | class | 0 | PRIMARY | 1 | id | A | 20 | NULL | NULL | | BTREE | | | | class |  1 | idx_class_card | 1 | card | A | 20 | NULL | NULL | | BTREE | | | +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------ + -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set (0.00 SEC)Copy the code

After the left join table is indexed, type reaches the index level, but the number of scanned rows is not reduced.

Index the book table


mysql> drop index idx_class_card on class;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index idx_book_card on book(card);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from book;+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+ ------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+ ------------+---------+---------------+ | book | 0 | PRIMARY | 1 | bookid | A | 20 | NULL | NULL | | BTREE | | | | book | 1 | idx_book_card | 1 | card | A | 20 | NULL | NULL | | BTREE | | | +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+ -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set (0.00 SEC)Copy the code

Add index to left join right table, type reaches ref level and index is used, right table scan number is reduced.

This is due to the nature of the left join:

LEFT JOINConditions are used to determine how to make the right table search row, the left table must be all contained.

Therefore, the right table is our search key table, which needs to be indexed.

In the same way, it can be inferred that the same problem exists in the right join, so it is concluded that the left join establishes the index in the right table, and the right join establishes the index in the left table, that is, ** “join index establishes the opposite” **

5.1.3 Optimization analysis of three tables

① Create a table

CREATE TABLE IF NOT EXISTS `phone`(
`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
)ENGINE = INNODB;

INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
Copy the code

mysql> select * from phone;+---------+------+ | phoneid | card | +---------+------+ | 1 | 18 | | 2 | 13 | | 3 | 10 | | 4 | 12 | | 5 | 8 | | 6 | 5 | 7 | 2 | | | | | 8 11 12 | | | 9 10 4 | | | | | 3 | | | | 5 12 13 13 | | | | | | 11 of 14 15 13 | | | | | | 15 of 16 17 | | 15 | | | | 7 18 19 10 | | | | | | 7 + 20 + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 20 rows in the set (0.00 SEC)Copy the code

② Query requirements

Where is the associated index created for the table?

③ Analysis and optimization

The three-table association does not use an index and performs a full table scan.

④ Solutions

Based on the association between the two tables, it can be seen that indexes in the opposite direction of the join should be set up. Therefore, indexes should be set up in the card field of the book table and phone table.

#Create an index in the book table
mysql> create index idx_book_card on book(card);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
#Create an index in the phone table
mysql> alter table phone add index idx_phone_card(card);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
#Index of the query
mysql> show index from book;+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+ ------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+ ------------+---------+---------------+ | book | 0 | PRIMARY | 1 | bookid | A | 20 | NULL | NULL | | BTREE | | | | book | 1 | idx_book_card | 1 | card | A | 20 | NULL | NULL | | BTREE | | | +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+ -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set (0.00 SEC)
mysql> show index from pnone;
ERROR 1146 (42S02): Table 'base_crud.pnone' doesn't exist
mysql> show index from phone;+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------ +------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------ +------------+---------+---------------+ | phone | 0 | PRIMARY | 1 | phoneid | A | 20 | NULL | NULL | | BTREE | | | | phone | 1 | idx_phone_card | 1 | card | A | 20 | NULL | NULL | | BTREE | | | +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------ + -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set (0.00 SEC)Copy the code

Query analysis

After the index is added, you can see that the query uses the index and the number of rows scanned by the table decreases.

5.1.4 Optimization summary of Associated Query

① Reduce the number of NestedLoop loops in Join statements as much as possible; “Driving large Result Sets with small result sets”

② Optimize the inner field of NestedLoop first;

(3) If you cannot ensure that the JOIN field of the driver table is indexed and the memory is sufficient, you can adjust the JoinBuffer Settings appropriately.

5.2 Index Failure

MySQL > insert into index ();

(1) Performing operations on an index (calculation, function, manual/automatic type conversion) will cause the index to fail and the full table scan will be performed.

② Use SQL (! = or <>) will cause index invalidation and full table scan;

Mysql > alter table scan SQL > alter table scan SQL > alter table scan SQL > alter table scan SQL > alter table scan

(4) In SQL, LIKE starts with a wildcard character LIKE(& string) causes index invalidation and full table scan;

⑤ If the string in SQL is not quoted, the index will be invalidated and full table scan will be performed.

⑥ SQL > select * from table where index (s) OR (s) is used;

5.2.1 Try to use full value matching

The fields in the SQL statement WHERE query can all be matched in the index in order.

The order in which SQL fields are queried does not depend on the order in which the fields in the index are used. The optimizer will automatically optimize without affecting the results of SQL execution.

5.2.2 Optimal left prefix rule

In SQL, the creation sequence of query fields and indexes is different. As a result, indexes cannot be used or even fail. Therefore, in SQL, a field must be matched in the order in which the index is created. All fields following an index cannot be used. If you use compound indexes, follow the leftmost prefix rule.

Left-most prefix rule: Queries start at the left-most column of the index and do not skip columns in the index.

(1) The query is performed in the order that the index is created. You can see that the index is used and the query is constant

(2) The index is not fully used. If the index is not fully used, the index is not fully used

(3) If the table does not use the first digit of the index column, you can see that the table does not use the index at all

5.2.3 No operation is performed on index columns

Calculations, functions, and (automatic/manual) type conversions cause index invalidation to shift to a full table scan.

In SQL WHERE query strings are not quoted, string conversion is performed in SQL, resulting in invalid indexes.

5.2.4 No range query is performed on index columns

The storage engine cannot use the column to the right of the index range condition, so the index order of the fields that can be used for the range query is placed last.

5.2.5 Use overridden indexes whenever possible

Index-only queries, where the index column is consistent with the query column, reduce SELECT * operations

5.2.6 No nulls on index columns

MySQL is using (! If = or <>), the index is invalid and a full table scan is performed

Although nulling will cause index invalidation, the specific case needs to be analyzed. SQL queries cannot only consider index invalidation.

5.2.7 No non-null queries on index columns

In SQL WHERE IS NULL does NOT invalidate an index, but IS NOT NULL does invalidate an index.

Do not judge a field to be null. It is better to set the default value for the field.

5.2.8 Correct use of fuzzy query

SQL LIKE begins with a wildcard (‘% ABC… ‘) MySQL will cause index invalidation and perform full table scan instead. That is, when using LIKE for fuzzy matching, left blur and full blur will cause index failure, and right blur can only be used index.

The LIKE ‘% ‘character %’ index is not used. Overwrite index is established to solve the index failure caused by total ambiguity.

As shown in the following figure, an overwrite index can be used when the name and age fields are indexed and then the full fuzzy query is performed.

5.2.9 Pay attention to the Use of connections

An OR join will cause index failure. Use UNION OR UNION ALL instead.

5.2.10 Summary of Index Optimization

Full value matching is my favorite, the leftmost rule to follow.

The first brother cannot die, the middle brother cannot be broken.

Less calculation on index column, all invalidated after range.

Write LIKE to the right, overwrite index does not write *.

Unequal null values and OR, index effects should be noted.

VAR quotes cannot be lost. There are tricks to SQL optimization.

5.2.11 Index Optimization interview Questions

① Creating a database

create table test(
id int primary key not null auto_increment,
c1 char(10),
c2 char(10),
c3 char(10),
c4 char(10),
c5 char(10));

insert into test(c1,c2,c3,c4,c5) values ('a1'.'a2'.'a3'.'a4'.'a5');
insert into test(c1,c2,c3,c4,c5) values ('b1'.'b2'.'b3'.'b4'.'b5');
insert into test(c1,c2,c3,c4,c5) values ('c1'.'c2'.'c3'.'c4'.'c5');
insert into test(c1,c2,c3,c4,c5) values ('d1'.'d2'.'d3'.'d4'.'d5');
insert into test(c1,c2,c3,c4,c5) values ('e1'.'e2'.'e3'.'e4'.'e5');

create index idx_test03_c1234 on test03(c1,c2,c3,c4);
Copy the code

mysql> select * from test;+----+------+------+------+------+------+ | id | c1 | c2 | c3 | c4 | c5 | +----+------+------+------+------+------+ | 1 | a1 | a2 | a3 | a4 | a5 | | 2 | b1 | b2 | b3 | b4 | b5 | | 3 | c1 | c2 | c3 | c4 | c5 | | 4 | d1 | d2 | d3 | d4 | d5 | | | | e1 | 5 e2 e3 | e4 | e5 | + + -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + 5 rows in the set (0.00 SEC)Copy the code

Create index

#Create indexes
mysql> create index idx_test_c1234 on test(c1,c2,c3,c4);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
#Index of the query
mysql> show index from test;+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------ +------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------ +------------+---------+---------------+ | test | 0 | PRIMARY | 1 | id | A | 5 | NULL | NULL | | BTREE | | | | test | 1 | idx_test_c1234 | 1 | c1 | A | 5 | NULL | NULL | YES | BTREE | | | | test | 1 | idx_test_c1234 | 2 | c2 | A | 5 | NULL | NULL | YES | BTREE | | | | test | 1 | idx_test_c1234 | 3 | c3 | A | 5 | NULL | NULL | YES | BTREE | | | | test | 1 | idx_test_c1234 | 4 | c4 | A | 5 | NULL | NULL | YES | BTREE | | | +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------ + -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 5 rows in the set (0.00 seCopy the code

③ Analyze the following SQL execution

1) Basic query

explain select * from test where c1 = 'a1';
explain select * from test where c1 = 'a1' and c2 = 'a2';
explain select * from test where c1 = 'a1' and c2 = 'a2' and c3 = 'a3';
explain select * from test where c1 = 'a1' and c2 = 'a2' and c3 = 'a3' and c4 = 'a4';
Copy the code

All of the above SQL can use indexes, and the number of index columns used is gradually increasing.

2) Basic query

explain select * from test where c1 = 'a1' and c2 = 'a2' and c3 = 'a3' and c4 = 'a4';
explain select * from test where c1 = 'a1' and c3 = 'a3' and c2 = 'a2' and c4 = 'a4';
explain select * from test where c4 = 'a4' and c3 = 'a3' and c2 = 'a2' and c1 = 'a1';
Copy the code

The above SQL queries all use four index columns. The reason is that the MySQL internal optimizer optimizes SQL, but it is recommended that the index order be the same as the query order.

3) Range query

explain select * from test where c1 = 'a1' and c2 = 'a2' and c3 > 'a3' and c4 = 'a4';
Copy the code

The above SQL can only apply index columns C1, C2, c3 because the index is invalidated by a range query at C3.

explain select * from test where c1 = 'a1' and c2 = 'a2' and c4 > 'a4' and c3 = 'a3';
Copy the code

Above SQL application index column c1, c2 and c3, c4 for c4 for range queries is the one inducing the inefficiency of the index, but c4 was the last index columns, although is range search, but still USES the index of all columns.

4) Single-value sort query

explain select * from test where c1 = 'a1' and c2 = 'a2' and c4 = 'a4' order by c3;
Copy the code

The SQL above actually uses three index columns C1, C2, and C3, but only C1 and C2 are used. In fact, C1 and C2 participate in the query operation, and A3 participate in the sorting operation. This leads to the two main functions of indexes, query and sort.

explain select * from test where c1 = 'a1' and c2 = 'a2' order by c3;
Copy the code

Same as the SQL execution above.

explain select * from test where c1 = 'a1' and c2 = 'a2' order by c4;
Copy the code

SQL filesort will appear above, the reason is that the composite index in the a3 fracture, led to the subsequent a4 only file sorting

5) Multi-value sort query

explain select * from test where c1 = 'a1' and c5 = 'a5' order by c2,c3;
Copy the code

The above SQL uses only the C1 index column, actually C2 and C3 are used for sorting.

explain select * from test where c1 = 'a1' and c5 = 'a5' order by c3,c2;
Copy the code

Mysql > alter table SQL > alter table SQL > alter table SQL > alter table SQL

explain select * from test where c1 = 'a1' and c2 = 'a2' order by c2,c3;
Copy the code

Two index columns C1 and C2 are used, and c2 and C3 are used for sorting

explain select * from test where c1 = 'a1' and c2 = 'a2' and c5 = 'a5' order by c2,c3;
Copy the code

Same as the previous SQL execution.

explain select * from test where c1 = 'a1' and c2 = 'a2' and c5 = 'a5' order by c3,c2;
Copy the code

In combination with the features of MySQL, it can be seen that c2 is not involved in sorting, only C3 is involved in sorting. In fact, index columns C1 and C2 are used for searching, while index columns C3 are not invalid for sorting.

Reason: In MySQL, when the value of a sort is a constant, the constant does not participate in the sort.

explain select * from test where c1 = 'a1' and c5 = 'a5' order by c3,c2;
Copy the code

Filesort will appear in the above SQL. Only index column C1 is used for query. When sorting using C3, the index is broken at C2, resulting in index failure and file sorting instead.

6) Group query

explain select * from test where c1 = 'a1' and c4 = 'a4' group by c2,c3;
Copy the code

Index column C1 is used in query. Index columns C2 and C3 are used in grouping. Indexes are not invalid.

explain select * from test where c1 = 'a1' and c4 = 'a4' group by c3,c2;
Copy the code

The above SQL will appear filesort and temporary, and only use index column C1 for query. When grouping, use c3 because the index is broken at c2, resulting in index failure and file sorting instead. In addition, MySQL will perform a temporary table sorting before grouping C3.

④ Summary of the interview questions

Fixed values and ranges are actually sorted, usuallyorder byGiven is a range;
group byIn general, sorting is required, resulting in temporary tables;

5.3 Optimization Suggestions

(1) For single-key indexes, try to select fields with better filtering for the current query.

② When selecting the combined index, the filtering field in the current query is higher in the index field order, the better;

(3) When selecting a composite index, try to select an index that contains as many columns as possible in the CURRENT QUERY WHERE clause.

(4) By analyzing statistical information and adjusting query writing method, we can select the appropriate index.

Iii. Index Optimization Analysis (II)

4. Performance analysis

4.1 Common performance Bottlenecks of MySQL

4.2 MySQL Performance Analysis

2 the EXPLAIN abstract

4.2.2 Usage:

4.2.2 Explanation of each analysis field

5. Index optimization

5.1 Index Analysis

5.1.1 Single table optimization analysis

5.1.2 Optimization analysis of the two tables

5.1.3 Optimization analysis of three tables

5.1.4 Optimization summary of Associated Query

5.2 Index Failure

5.2.1 Try to use full value matching

5.2.2 Optimal left prefix rule

5.2.3 No operation is performed on index columns

5.2.4 No range query is performed on index columns

5.2.5 Use overridden indexes whenever possible

5.2.6 No nulls on index columns

5.2.7 No non-null queries on index columns

5.2.8 Correct use of fuzzy query

5.2.9 Pay attention to the Use of connections

5.2.10 Summary of Index Optimization

5.2.11 Index Optimization interview Questions

5.3 Optimization Suggestions

Related Posts

There aren’t any books on the market that talk about Java class loaders

The failure to de-duplicate the List object made me think about distinct() in Java8

On ownership of Rust