Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

What is the common optimization scheme of MySQL?

2025-01-22 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Development >

Share

Shulou(Shulou.com)06/02 Report--

What is the common optimization scheme of MySQL? I believe many inexperienced people don't know what to do about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Several places of sql Optimization

Select [Field Optimization 1]: mainly overrides indexes

From []

Where [condition Optimization 2]

Union [Joint query Optimization 3]

New form

CREATE TABLE `student` (`id` int (11) NOT NULL AUTO_INCREMENT COMMENT 'primary key', `name` varchar (50) DEFAULT NULL COMMENT 'name', `age`int (11) DEFAULT NULL COMMENT 'age', `phone`varchar (12) DEFAULT NULL, `create_ time`datetime DEFAULT NULL COMMENT 'creation time', PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8

Add an index, after adding an index

Key_len: based on this value, you can determine the index usage, especially when combining indexes, to determine whether all index fields are used by the query.

A brief introduction to key_len calculation method

Latin1 takes 1 byte, gbk 2 bytes, and utf8 3 bytes

Null is not allowed:

Varchar (10): 10: 3

Char (10): 10 "3" 2

Int:4

Allowed to be empty:

Varchar (10): 10 "3" 1

Char (10): 10'3'2'1

Int:4+1

Use the full index key_len=name (50 / 3 / 2 / 1 / 153) + age (4 / 1) + phone (12 / 3 / 2 / 1 / 39)

Alter table studen add index name_age_phone (name, age, phone)

Add data

Insert into student (name,age,phone,create_time) values (15717177664); insert into student (name,age,phone,create_time) values (15733337664); insert into student (name,age,phone,create_time) values (Tyro, 800) 15714447664); 1, optimization point 1: field optimization covers the index as much as possible

Simply explain, query which columns the index is: the reason for overwriting the index: the index is an efficient way to find rows, but the general database can also use the index to find the data of a column, so it does not have to read the entire row. After all, index leaf nodes store the data they index; when you can get the data you want by reading the index, you don't need to read rows. An index that contains (or overwrites) data that satisfies the query results is called an overlay index. Note: if there is an index, try not to use select *

# unoverwritten index EXPLAIN SELECT * FROM student WHERE NAME = 'Tyro' and age = 1000 and phone='15717177664';# overrides index EXPLAIN SELECT name,age,phone FROM student WHERE NAME = 'Tyro' and age = 1000 and phone='15717177664';# contains index EXPLAIN SELECT name FROM student WHERE NAME = 'Tyro' and age = 1000 and phone='15717177664';# plus primary key also overrides index EXPLAIN SELECT id, name,age,phone FROM student WHERE NAME = 'Tyro' and age = 1000 and phone='15717177664'

No override index is used

Use a full override index

Use the include override index

Add the primary key or overwrite the index

Second, optimization point 2:where optimization 1. Try to match EXPLAIN SELECT * FROM student WHERE NAME = 'Severn'; EXPLAIN SELECT * FROM student WHERE NAME = 'Leo' AND age = 1200 ex plain SELECT * FROM student WHERE NAME = 'Tyro' AND age = 800AND phone = '15714447664'

As a result, all three indexes are used, but key_len is different, key_len=197, which means that all indexes are used.

When an index column is created, you can use the index in the wherel condition as much as possible.

two。 Best left prefix rule

Leftmost prefix rule: the query starts at the leftmost front column of the index and does not skip the columns in the index. The index order we defined is name_age_phone, so the query should also start with name, then age, and then phone case 1: start with age, phone, tpye=All,key = null, no index is used

Case 2: query from phone, type=All,key=null, no index is used

Case 3: starting with name, type=ref, using an index

3. The range condition is last.

Did not use range query, key_len=197, used name+age+phone composite index

EXPLAIN SELECT * FROM student WHERE NAME = 'Tyro' AND age = 1000 AND phone = '15717177664'

Using a range query, key_len changed from 197 to 158, that is, except for name and age,phone indexes that are invalid

EXPLAIN SELECT * FROM student WHERE NAME = 'Tyro' AND age > 800 AND phone = '15717177664'

Key_len=name (153) + age (5)

4. Do nothing on the index column EXPLAIN SELECT * FROM student WHERE NAME = 'Tirol'; EXPLAIN SELECT * FROM student WHERE left (NAME,1) = 'Tyro'

Without calculation, key_len has a value, key_len=153, and some use name indexes.

Intercept settlement done, type=All,key_len=null, unused index

5. Doesn't mean you have to be very useful.

The inability of mysql to use an index when using (! = or) will result in a full table scan.

# Index EXPLAIN SELECT * FROM student WHERE NAME = 'tirol' is used; # is not equal to query, index EXPLAIN SELECT * FROM student WHERE NAME! = 'tirol' is not used; EXPLAIN SELECT * FROM student WHERE NAME 'tirol'; # if it is necessary to use an index that is not equal to, please use overlay index EXPLAIN SELECT name,age,phone FROM student WHERE NAME! = 'tirol'; EXPLAIN SELECT name,age,phone FROM student WHERE NAME 'tirol'

Use is not equal to query, skip index

Use is not equal to query, and override index is used at the same time, index can be used at this time

6.Null/Not null has influence

Modify to non-empty

Then it is not null, which causes the index to fail.

EXPLAIN select * from student where name is null;EXPLAIN select * from student where name is not null

Can be empty instead

The query is empty and the query has caused an effect.

Query non-empty index invalidation

Solution:

Use overlay index (override index to solve sorrow)

7. Like query should beware of like

Start with a wildcard ('% abc...') mysql index failure becomes a full table scan operation

# like begins with a wildcard ('% abc...') mysql index invalidation becomes a full table scan operation # Index valid EXPLAIN select * from student where name = 'Tirol'; # Index invalidation EXPLAIN select * from student where name like'% Tyro%'; # Index invalidation EXPLAIN select * from student where name like'% Tyro'; # Index valid EXPLAIN select * from student where name like 'Tyro%' Solution: overwrite index EXPLAIN select name,age,phone from student where name like'% Tyro%'

Using an overlay index can solve the problem.

8. Character types in quotation marks

The index of the string is invalid without single quotation marks (this looks a little chicken rib, usually the query string will be in quotation marks)

Use override index to solve

Third, optimize 31.OR to UNION with high efficiency without using index EXPLAINselect * from student where name=' Tyro'or name=' Leo'; use index EXPLAINselect * from student where name=' Tyro 'UNIONselect * from student where name=' Leo'; solution: overwrite index EXPLAINselect name,age from student where name=' Tyro'or name=' Leo'

Indexes are not used when using or

Using union, using index

Solution: overwrite the index

After reading the above, have you mastered what the common optimization solutions of MySQL are? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Development

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report