MySQL underlying index optimization

MySQL index tuning principles

It is forbidden to join more than three tables
Left prefix rule of joint index
Prohibit back to table query

MySQL query explain type level

All full table scanning, the most native sql state, has a lot of optimization space
Index is another form of full table scanning. The scanning order is the order of the index. The scanning is based on the index, and then the data is queried back to the table
Range refers to a full table scan with a range, which is better than index with a range
The ref query condition uses the index instead of the primary key and unique. Although the index is used badly, the values of the index columns are not unique and duplicate
ref_eq is better than ref in that it knows that there is only one search result set of this type, either a primary key index or a unique index
const constant, when the index is almost optimal, and the remaining optimization depends on the optimizer.

MySQL-EXPLAIN-Extra

using index : Appears when the index is overwritten with

using where: When using an index, you need to query the required data back to the table

using index condition: The index is used, but the data needs to be queried back to the table

using index & using where: The index is used, but the required data can be found in the index column, so there is no need to go back to the table

//Ordinary index, xxx identifies the field name
alter table table_name add index idx_xxx (xxx) ;
//Unique index, xxx identifies the field name
alter table table_name add unique (xxx) ;
//Primary key index, xxx identifies the field name
alter table table_name add primary key (xxx) ;
//Delete index
drop index index_name on talbe_name

Create two tables, user table and user class table, one to many association relationship, and associate through user's id

Table structure DDL is as follows

User table

CREATE TABLE `test_user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) DEFAULT NULL,
  `addr` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

Class table

CREATE TABLE `user_class` (
  `class_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `class_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`class_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

Perform an association query after adding test data

EXPLAIN SELECT a.user_id,a.user_name,a.addr from test_user a left join user_class b on a.user_id = b.user_id;

Query results

It looks ok, but it's very inefficient

View with EXPLAIN tool

It is found that the sql initiated two queries this time, both of which were full table scans, indicating that there is still a lot of room for optimization.

First give the associated table and the associated user_ Add a common index to the ID primary key

alter table user_class add index idx_user_id (user_id);

Use explain again

The second query has changed from All to ref in the whole table, indicating that the index has been taken

test_ The user table structure is modified to

CREATE TABLE `test_user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) DEFAULT NULL,
  `addr` varchar(255) DEFAULT NULL,
  `age` int(255) DEFAULT NULL,
  `sex` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  KEY `idx_add_age_sex` (`addr`,`age`,`sex`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

For single table query

EXPLAIN select a.addr,a.age,a.sex from test_user a

You can see that these three conditions are returned. The full table scan is followed by optimization

Index invalidation of Federated index

The joint index of a single table is optimized

Add a federated index to the user table

alter table test_user add index `idx_add_age_sex` (`addr`,`age`,`sex`)

Query again after adding

It is found that the index is reached and the full table scan of the index is used

If you bring the where condition

EXPLAIN select a.addr,a.age,a.sex from test_user a where a.addr = 'Hangzhou, Zhejiang' and a.age=22 and a.sex=0

It was found that there was no problem. We did go to our joint index for query

But if you change to

EXPLAIN select a.addr,a.age,a.sex from test_user a where a.age=22 and a.sex=0

At this time, the index is used to scan the whole table, and the efficiency is significantly reduced

What if you change the order

EXPLAIN select a.addr,a.age,a.sex from test_user a where a.age=22 and a.addr = 'Hangzhou, Zhejiang' and a.sex=0

You can see it's gone again

What if you remove the middle of the union index?

EXPLAIN select a.addr,a.age,a.sex from test_user a where a.addr = 'Hangzhou, Zhejiang' and a.sex=0

You can see that the union index is still gone

It can be concluded that when querying a joint index, there must be the first index as the condition in where. The order of the left prefix principle will be optimized and the order will be changed at the bottom of mysql.

Avoid back to table queries

Compare the following two paragraphs

EXPLAIN select a.addr,a.age,a.sex,a.user_name from test_user a where a.addr = 'Hangzhou, Zhejiang' and a.age=22 and a.sex=0

EXPLAIN select a.addr,a.age,a.sex from test_user a where a.addr = 'Hangzhou, Zhejiang' and a.age=22 and a.sex=0

The Extra in the first paragraph of SQL indicates that the underlying layer performs a back table query, that is, the data to be returned is not within the data range that the index can reach, so it returns the table query again to get all the required data

Added by argoSquirrel on Mon, 03 Jan 2022 07:09:55 +0200

Programming VIP