MySQL SQL statement analysis and optimization

This is the 6th day of my participation in the August More Text Challenge

MySQL SQL statement analysis and optimization (below), this article mainly explains some tips about SQL optimization.

Lead to

CREATE TABLE `employees` ( `id` INT ( 11 ) NOT NULL AUTO_INCREMENT, 'name' VARCHAR (24) NOT NULL DEFAULT 'COMMENT ',' age 'INT (11) NOT NULL DEFAULT '0' COMMENT' age ', 'position' VARCHAR (20) NOT NULL DEFAULT '' COMMENT ' 'hire_time' TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'CURRENT_TIMESTAMP ', PRIMARY KEY (' id'), KEY `idx_name_age_position` ( `name`, `age`, 'position') USING BTREE) ENGINE = INNODB AUTO_INCREMENT = 4 DEFAULT CHARSET = utf8 COMMENT = '10 '; INSERT INTO employees (NAME, age, position, hire_time) VALUES('LiLei',22,'manager',NOW()); INSERT INTO employees ( NAME, age, position, hire_time ) VALUES ('HanMeimei',23,'dev',NOW()); INSERT INTO employees ( NAME, age, position, hire_time ) VALUES('Lucy',23,'dev',NOW());Copy the code

Select * from idx_name_AGe_position; select * from idx_name_age_position; select * from IDx_name_age_position

Left-most prefix rule

When we use the union index, we need to follow the left-most prefix rule, which means that the query starts from the left-most column of the index and does not skip the columns in the index:

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' and age = 22;
Copy the code

So if we skip name, what happens if we skip name

 EXPLAIN SELECT * FROM employees WHERE age = 22 AND position = 'manager';
Copy the code

We can see that there is no index in the table. The type of the table is ALL, which means that there is no index in the table. Because we skipped the name field, we cannot skip the index.

 EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' AND position = 'manager';
Copy the code

Did you go through the index, but not quite through the index? That’s right! We skipped age, but not name, so name can be indexed, but position can not be indexed because age is skipped, notice that if age is a range query, then position can not be indexed.

All values match

Full value matching is based on the left-most prefix rule, which refers to the order in which queries are created by index, as shown in the following SQL statements

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei';
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' and age = 22;
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' and and age = 22 position = manager;
Copy the code

The above will go index, specific results do not demonstrate!

Use overwrite indexes whenever possible

Using overridden indexes means that queries that access an index contain index columns and use select * statements as little as possible

EXPLAIN SELECT name,age FROM employees WHERE name= 'LiLei' AND age = 23 AND position='manager';
Copy the code

Try not to do anything on the index column

Calculations, functions, (automatic or manual) type conversions), can cause indexes to fail and move to full table scans

EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei';
Copy the code

Less use does not equal

Mysql not in, not exists most of the index cannot be used cause full table scan,

But in use! The mysql internal optimizer will evaluate whether to use an index or not based on the number of factors such as the size of the table and the proportion of data to be retrieved. It may be due to the high proportion of data that is not equal to the table.

EXPLAIN SELECT * FROM employees WHERE name ! = 'LiLei';Copy the code

The same SQL, just because the amount of data is different, resulting in a different situation, when the index, the table only a few data, and did not go to the index when the data in the table although only dozens of, but also relative to a few more than ten times, so some scope is related to the amount of data index

Is null,is not NULL Generally, indexes cannot be used

EXPLAIN SELECT * FROM employees WHERE name is null
Copy the code

Like starts with wildcard % mysql index failure will become a full table scan operation

EXPLAIN SELECT * FROM employees WHERE name like '%LiLei';
EXPLAIN SELECT * FROM employees WHERE name like '%LiLei%';

Copy the code

Neither of the above two SQL statements can go index, but we can use index overwrite method to make it go index

To use an overwrite index, the query field must be an overwrite index field

EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei%';
Copy the code

** Range query optimization

ALTER TABLE 'employees' ADD INDEX' idx_age '(' age') USING BTREE; # explain select * from employees where age >=1 and age <=2000;Copy the code

The mysql internal optimizer evaluates whether to use an index based on multiple factors such as the percentage of retrieves, table size, and so on. In this example, the optimizer may choose not to move the index because of a single large query

Optimization method: Large ranges can be divided into smaller ranges

explain select * from employees where age >=1 and age <=1000;
explain select * from employees where age >=1001 and age <=2000;
Copy the code

conclusion

There are a lot of SQL optimization summary, not a demonstration, and then summed up below:

• String indexing fails without single quotation marks: for example, name = ‘1000’; name = 1000; The former will go to the index, the latter will not
• The mysql internal optimizer will evaluate whether to use an index based on multiple factors, such as the index ratio, table size, etc. In should understand the principle that small tables drive large tables. If you do not understand the principle, you can search online

Optimization summary formula:

Full value match my favorite, most left prefix to obey;

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

Less calculation on index column, all invalid after range;

Like 100 write most right, cover index does not write star;

Unequal null values and OR, index failure should be used less;

VAR quotes are not lost, SQL advanced is not difficult!