This is the fourth day of my participation in the August More text Challenge. For details, see:August is more challenging

Index failure problem

Case 1: Best left prefix

SQL: create a table and add a composite index with columns name, age, pos

CREATE TABLE staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24)NOT NULL DEFAULT' ' COMMENT'name',
`age` INT NOT NULL DEFAULT 0 COMMENT'age',
`pos` VARCHAR(20) NOT NULL DEFAULT' ' COMMENT'job',
`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'Entry Time'
)CHARSET utf8 COMMENT'Staff Records';
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3'.22.'manager',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July'.23.'dev',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000'.23.'dev',NOW());

ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`)
Copy the code

The query uses all the fields in the index

EXPLAIN SELECT
	*
FROM
	staffs
WHERE
	`name` = "July"
AND age = 23
AND pos = "dev"
Copy the code

Results:

In this case, everything seems to be working and the indexes are being used.

But if the query conditions are changed

EXPLAIN SELECT
	*
FROM
	staffs
WHERE
	age = 23
AND pos = "dev"
Copy the code

The result is:

It is found that the actual index is not used, which violates the best left prefix rule, that is, indexes must be created in order from left to right to take effect.

In the case of using the first and third fields of the index

EXPLAIN SELECT
	*
FROM
	staffs
WHERE
	name = "July"
AND pos = "dev"
Copy the code

Results:

Although the index is used, only the first index is used, and the third index is not used even though it uses a fixed value, because it violates the principle of best left prefix.

Case 2: The index column uses method functions and other operations

Use EXPLAIN SELECT * FROM staffs WHERE name = “July “; The query result is

Use EXPLAIN SELECT * FROM staffs WHERE left(name,4) = “July “; The query result is

Using a function on an index column invalidates the index.

Case 3: The storage engine cannot use the rightmost column of the index range condition

useEXPLAIN SELECT * FROM staffs WHERE name = "july" AND age > 20 AND pos = "manager";The query result is:

The query is not used from age to the following index. The index to the right of the range condition is invalidated.

Case 4: Try to overwrite the index

EXPLAIN SELECT name,age,pos FROM staffs WHERE name = “July” AND age > 20 AND pos = “manager”; The query result is

Case 5: Indexes that start with a like wildcard are invalid

EXPLAIN SELECT * FROM staffs WHERE name LIKE “% July %”

EXPLAIN SELECT * FROM staffs WHERE name LIKE “% July “if “%” is displayed on the left

EXPLAIN SELECT * FROM staffs WHERE name LIKE “July %” only if” %” is displayed on the right

Only by adding ‘%’ to the index on the right is the index not invalid, but some scenarios must use ‘%’.

Solution:

Create tables and indexes

CREATE TABLE `tbl_user`(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age`INT(11) DEFAULT NULL,
`email` VARCHAR(20) DEFAULT NULL.PRIMARY KEY(`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('1aa1'.21.'[email protected]');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('2bb2'.23.'[email protected]');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('3cc3'.24.'[email protected]');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('4dd4'.26.'[email protected]');

CREATE INDEX idx_user_nameAge ON tbl_user(`name`,age);
Copy the code

EXPLAIN SELECT name,age FROM tBL_user WHERE name LIKE “% AA %

conclusion
  • Full value matches are best
  • Best left prefix
  • Do not do anything on the index column, otherwise the index will become invalid and a full table scan will result
  • The storage engine cannot use the rightmost column of an index range condition
  • Use overwrite indexes as much as possible and reduce select *
  • MySQL in use is not equal to (! = or <>), which results in a full table scan
  • Is, NULL, and IS NOT NULL cannot use indexes
  • Indexes that start with a wildcard using like are also invalidated
  • The index of a string without single quotes is invalid
  • Use or as little as possible. Or joins can also invalidate indexes