The previous articles introduced the underlying data structure of mysql and the artifact of mysql optimization explain. Background some friends said jack Bauer only introduced concept, use at ordinary times is a face of meng, urged cockroaches to an actual combat SQL optimization, after the weekend two days of sorting and summary, SQL optimization of actual combat fresh, everyone in the study and work at ordinary times, with 90% of SQL optimization, will be introduced to mind length is too long, divided into three articles.

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

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

All values match

The field type of the index is VARCHAR (n) : 2 bytes storage string length, or 3n+2 if utF-8

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

Leftmost prefix rule

If the index is multi-column, 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. The following two SQL statements do not follow the index according to the leftmost prefix rule.

EXPLAIN select * from employees where age = 22 AND position='manager';
EXPLAIN select * from employees where position ='manager';
Copy the code

Index of the failure

Do not do anything on the index column (calculation, function, cast) that will cause the index to fail and move to a 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

Add a normal 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';
Copy the code

Restores the original index state

ALTER TABLE `employees` DROP INDEX `idx_hire_time`;
Copy the code

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';
EXPLAIN SELECT * FROM employees WHERE name ='LiLei' AND age>22 AND position ='manager';
Copy the code

Cover index

Minimize selelct * statements by using overridden indexes (queries that access only indexes (index columns contain query columns).

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

conditional

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

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

A null value judgment

Is null, is not NULL also cannot use the index

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

like

Like starts with a wildcard character (‘ $ABC ‘) mysql index failure becomes a full table scan operation

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

The index of a string without single quotation marks is invalid

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

Or&in use less

The mysql internal optimizer evaluates whether or not to use an index based on the index ratio, table size, and other factors.

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

Range query optimization

Add a single value index to the age

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 or not to use an index based on multiple factors such as the percentage of retrieves, the size of the table, and so on. This example may not be indexed because the optimizer ultimately chose not to index because a single query was too large. Optimization method: Large ranges can be divided into smaller ranges.

Haven’t followed my official account yet?

  • Scan the qr code at the end of the public number [xiaoqiang advanced road] can be obtained as follows:
  • Learning materials: 1T video tutorial: covering Javaweb front and back end teaching video, machine learning/artificial intelligence teaching video, Linux system tutorial video, IELTS exam video tutorial;
  • More than 100 books: including C/C++, Java, Python three classic must-see books, LeetCode solution;
  • Software tools: Includes almost all the software you will need in your programming path;
  • Project source: source code for 20 JavaWeb projects.