In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
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.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.