If we understand the principles of indexing in Mysql, we can explore how to use indexes and optimize some slow SQL, so we can explore the principles of indexing and slow optimization knowledge.

1 the slow query

MySQL > slow query log

MySQL is a type of logging provided by MySQL to record statements that exceed a threshold.

By default, the MySQL database does not start slow queries. You need to set this parameter manually.

If this parameter is not required for tuning, it is not recommended to enable this parameter. Slow query logs may affect performance.

Slow query logs can be used to find queries that take a long time to execute and are therefore candidates for optimization.

  • To view the configuration information of Slow Query:
SHOW VARIABLES LIKE "%slow%";
Copy the code

​

  • View the time definition of Slow Query
SHOW VARIABLES LIKE "long_query_time";
Copy the code

​

  • Set the time definition for slow Query
SET long_query_time = 2;
Copy the code

​

  • Enabling Slow Logs
SET GLOBAL slow_query_log = "ON";
Copy the code

2 Mysql statement optimization

2.1 Data Preparation

To do the experiment, we need to insert many, many rows of data into the current table to see the performance difference in the query. Here we insert 10 million rows of data.

  • Create a table:
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL.`email` varchar(30) DEFAULT NULL.`phone` char(11) DEFAULT NULL.`age` int(11) DEFAULT NULL.`sex` char(1) DEFAULT NULL,
  PRIMARY KEY (`id`))ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;
Copy the code

  • Create a stored procedure that inserts 10 million pieces of data:
\d //
create procedure p1()
begin
set @i=1;
while @i<=10000000 do
insert into users values(
    null.concat('user:',@i),
    concat('user:',@i,'@qq.com'),
    concat('13701'.FLOOR(RAND(*)500000 + 500000)),
    floor(rand(*)100),
    if(floor(rand(*)2) = 1 , 'male' , 'woman'));set @i=@i+1;
end while;
end;
//
\d ;
Copy the code

  • Call the stored procedure to complete the data insert
call p1();
Copy the code

The running time will be long, 168 minutes or so on my computer, please wait patiently

​

Let’s insert a special piece of data:

insert into users values(null."zhangsan"."[email protected]".13701383017.25.'woman');
Copy the code

  • Query the data just inserted
select * from users where name = "zhangsan";
Copy the code

​

It can be seen that it takes a long time to query this data, and the corresponding log is also stored in the slow query log. The corresponding log content is as follows:

# Time: 200916 15:19:54
# User@Host: root[root] @localhost [127.0.0.1]
# Query_time: 6.708004 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 10000001
SET timestamp=1600240794;
select * from users where name = "zhangsan";
Copy the code

2.2 EXPLAIN statement

A query statement generates a so-called execution plan after various cost – and rule-based optimizations through the MySQL query optimizer

The execution plan shows how the query will be executed next, such as the order in which multiple tables will be joined, what access method will be used for each table to execute the query, and so on.

MySQL provides EXPLAIN statements to help us see the specific execution plan for a statement.

  • Analyze SQL statements using EXPLAIN

​

The parameters of the output result are explained as follows, among which the important ones are indicated in the figure above:

  • Id Each SELECT keyword in a large query has a unique ID
  • Select Type Indicates the query type corresponding to the select keyword
  • The table name of the table
  • Matching partition information for PARTITIONS
  • Type Access method for a single table
  • Possible_keys Possible index
  • The index that key actually uses
  • Key_len Specifies the actual length of the index
  • Ref Indicates the information about the object matching the index column when the index column equivalence query is used
  • The estimated number of rows to read
  • Filtered Percentage of the remaining records of a table filtered by search criteria
  • Extra information

When we find this data in another way, such as using ID, because id default primary key index, so the query is faster:

​

In just 0.02 seconds, explain the following results, which also verify the theory:

​

2.3 Adding An Index

  • Try adding a normal index to the name field
 alter table users add index index_name(name);
Copy the code

Then we use the name field to query, and we find that the speed is much faster, because we set the name field as the index:

​

Use Explain to check:

​

As you can see, indexes can significantly improve the efficiency of data retrieval

So does that mean we should just index as many as possible?

Each index creates a B+ tree and requires maintenance, which is costly in performance and storage.

2.4 Establish appropriate indexes

1. Create a primary key index using an incremented number

2. Indexes are often used as fields for WHERE conditions

3. Keep the fields to be indexed as unique as possible

4. Consider using federated indexes and index overwrites

2.5 Rational use of indexes

  1. MySQL indexes are usually used to speed up searches for rows matching WHERE conditions,
  2. In the process of using index, there are some details and matters needing attention.
  3. Because improper use may lead to the creation of an index, not necessarily the use of the index

2.5.1 Do not use functions and operations on columns

  • Do not use functions on columns, which will invalidate the index
select * from news where year(publish_time) = 2017;
Copy the code

  • Adapt to use indexes
select * from news where publish_time = '2017-01-01';
Copy the code

  • Do not perform operations on columns, which will also invalidate the index
select * from news where id / 100 = 1;
Copy the code

  • Adapt to use indexes
select * from news where id = 100;
Copy the code

​

​

2.5.2 The types must match

When the types on the left and right sides of the query condition do not match, implicit conversion will occur. The influence of implicit conversion is that it may cause index failure and carry out full table scan.

  • Modify the data in the table
 update users set name = '123456' where id = 10086;
Copy the code

  • Normal queries versus those with implicit conversions

​

It is found that the query time is very long when implicit casts occur.

2.5.3 No wildcard character appears in the header

  • Indexes can be used when wildcards are used at the end

​

  • When wildcards are used in the header, the index is invalidated

​

2.5.4 Multiple single-column indexes are not the best choice

Mysql can only use one index and selects one (most restrictive) index from multiple indexes. Therefore, creating a single column index for multiple columns does not improve Mysql query performance.

Use of multiple single-column indexes:

alter table users add index index_name(name);
Copy the code

​

It looks nice, but the file can be very large.

​

In fact, MySQL can only use a single column index. This wastes space and does not improve performance (because rows need to be returned). To improve performance, you can use composite indexes to ensure that columns are covered by indexes.

Composite index:

alter table users add index in_x(email,phone,name);
Copy the code

​

​

2.5.5 Beware of the leftmost principle of composite indexes

The index is used only when the first field of the composite index is used in the query condition. Therefore, the order of index columns in a composite index is critical. An index cannot be used unless the search starts in the leftmost column of the index.

​

It is found that the index is invalid if you only query by email.

2.5.6 Achieve index coverage as far as possible

If an index contains the values of all required query fields, it can greatly improve performance by directly returning data based on the query results of the index without reading the table. Therefore, you can define an additional column that the index contains, even if the column is useless to the index.