Previous articles introduced mysql's underlying data structure and MySQL optimized artifact explain. Some backstage friends said that Xiaoqiang only introduced the concept, usually use or face confused, strong demand Xiaoqiang to a real-world sql optimization, after two days of weekend collation and summary, sql optimization is fresh out, everybody in peacetime learning and work, 90% of the sql optimization will be introduced, mind the length is too long, divided into three articles. Article Kazakhstan.

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT 'Full name',
  `age` int(20) NOT NULL DEFAULT '0' COMMENT 'Age',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT 'position',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Initiation time',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Statement of Employees';

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())

Full Value Matching

The indexed field type is varchar(n): 2 bytes storage string length, and if utf-8, length is 3n+2

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 age = 22 AND position = 'manager';

Leftmost prefix rule

If the index is multi-column, the most left-most prefix rule is prefix rule. Refers to a query that starts at the top left column of the index and does not skip columns in the index. The following three SQLs do not index according to the leftmost prefix rule.

EXPLAIN select * from employees where age = 22 AND position='manager';
EXPLAIN select * from employees where position ='manager';
EXPLAIN select * from employees where age=17;

Index failure

Do not do anything on the index column (calculation, function, type conversion), which will lead to index failure and turn to full table scanning.

EXPLAIN select * from employees where name='LiLei';

EXPLAIN select * from employees where left(name, 3)='LiLei';

Add a common index to hire_time:

alter table `employees` ADD INDEX `idx_hire_time`(`hire_time`) USING BTREE;
EXPLAIN select * from employees where date(hire_time) = '2019-08-25';

Restore the original index state

ALTER TABLE `employees` DROP INDEX `idx_hire_time`;

The storage engine cannot use columns on the right of range conditions in the index

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

You can see that the index length of key_len is 78, that is, only the first two fields name and age are used, and postition does not use the index.

Coverage Index

Try to use overlay indexes (only queries accessing indexes (index columns contain query columns) to reduce selelct * statements.

EXPLAIN SELECT name,age,position FROM employees WHERE name ='LiLei' AND age=22 AND position ='manager';

Conditional Judgment

Failure to use indexes when using mysql is not equal to (!= or <>) results in full table scanning

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

Null Value Judgment

is null, is not null can not use index

EXPLAIN SELECT * FROM employees WHERE name is null;

like

like starts with wildcards ('$abc') mysql index failure becomes a full table scan operation

EXPLAIN SELECT * FROM employees WHERE name LIKE '%Lei';

String index invalidation without single quotation mark

EXPLAIN SELECT * FROM employees WHERE name ='1000';
EXPLAIN SELECT * FROM employees WHERE name =1000;

Without a single quoted string, the underlying mysql uses the cust function to convert it to a string, which invalidates the index.

Or&in Less Used

When querying with or or or in, mysql does not necessarily use index. The internal optimizer of mysql will evaluate whether to use index according to index proportion, table size and other factors.

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

Range query optimization

Adding a single-valued index to age

ALTER TABLE `employees`ADD INDEX `idx_age`(`age`) USING BTREE;
EXPLAIN select * from employees where age > 1 and age <= 2000;

There is no reason to go indexing: mysql internal optimizer will evaluate whether to use indexing according to multiple factors such as retrieval ratio, table size and so on.
This example does not walk indexing may be due to too many single data queries leading to the optimizer's final choice not to walk indexing.
Optimizing method: It can divide a large area into several small areas.

Not paying attention to my public number yet?

• At the end of the scan, the two-dimensional code pays attention to the public number [Xiaoqiang's way to advance], which can be obtained as follows:
• Learning materials: 1T video tutorial: covering Java web front-end and back-end teaching videos, machine learning/artificial intelligence teaching videos, Linux system tutorial videos, IELTS video tutorials;
• More than 100 books: including C/C++, Java, Python three programming languages must see the classic books, LeetCode problem-solving complete;
• Software tools: Almost all of the software you might use on your programming path;
• Project source code: 20 Java Web project source code.