“This is the first day of my participation in the Gwen Challenge in November. Check out the details: The last Gwen Challenge in 2021”

Use the table of

CREATE TABLE employees (
	id int(11) NOT NULL AUTO_INCREMENT,
	name varchar(24) NOT NULL DEFAULT ' ' COMMENT 'name',
	age int(11) NOT NULL DEFAULT '0' COMMENT 'age',
	position varchar(20) NOT NULL DEFAULT ' ' COMMENT 'job',
	hire_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Entry Time'.PRIMARY KEY (id),
	KEY idx_name_age_position USING BTREE (name, age, position)
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARSET = utf8 COMMENT 'Staff Record';

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

Best practices

1. Full value matching

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

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

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

2. The best left prefix rule

If you index multiple columns, follow the leftmost prefix rule. Indicates that the query starts at the left-most front of the index and does not skip columns in the index.

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

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

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

3. Do not perform any operation (calculation, function, (automatic or manual) type conversion) on the index column, which will cause the index to fail and turn to full table scan

​

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

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

4. The storage engine cannot use the column to the right of the range condition in the index

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

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

5. Use overwrite indexes (index-only queries (index columns contain query columns) as much as possible, and reduce SELECT * statements

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

6. Mysql does not equal (! = or <>) will cause a full table scan

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

7. Is null,is not null

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

8. Like (‘$ABC… Mysql index failure is a full table scan operation

EXPLAIN SELECT * FROM employees WHERE name like '%Lei';
Copy the code

EXPLAIN SELECT * FROM employees WHERE name like 'Lei%';
Copy the code

Like ‘% string %’ index not used? A) To use an overwrite index, the query field must be an established overwrite index field

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

​

B) Overwrite index will be invalid if the overwrite index points to a field vARCHAR (380) or above.

9. The index of a string without single quotation marks fails

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

EXPLAIN SELECT * FROM employees WHERE name = 1000;
Copy the code

10. Use or sparingly, as it can cause index failure in many cases

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

A small summary

Like KK% equals = constant, %KK and %KK% equals range

Conclusion 1

Conclusion 2

Reference documentation

  • Blog.csdn.net/qq_38138069…