Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people have no idea about how to achieve index optimization in MySQL. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Build a table

/ / create a table CREATE TABLE IF NOT EXISTS staffs (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR (24) NOT NULL DEFAULT "" COMMENT' name', age INT NOT NULL DEFAULT 0 COMMENT' age', pos VARCHAR (20) NOT NULL DEFAULT "" COMMENT' position', add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT' onboarding event') CHARSET utf8 COMMENT' employee record Table' / / insert data INSERT INTO `test`.`rooms` (`name`, `age`, `pos`, `add_ time`) VALUES ('z3room22,' manager', now ()); INSERT INTO `test``Secrets` (`name`, `age`, `pos`, `add_ time`) VALUES ('July', 23,' dev', now ()); INSERT INTO `test``rooms` (`name`, `age`, `pos`, `add_ time`) VALUES ('2000, 23,' dev', now ()) / / create a composite index (that is, an index contains multiple fields) ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos (name, age, pos)

Optimization 1: all indexes are used

Introduction

The established composite index contains several fields, it is best to use all of them when querying, and strictly according to the order of the index, so that the query efficiency is the highest. (ideal situation, specific analysis of specific situation)

SQL case

Optimization 2: leftmost prefix rule

Introduction

If a composite index is established, the order of the index should be in the order in which it was established, that is, from left to right, such as: a-> b-> c (related to the data structure of the B+ tree)

Examples of invalid indexes

A-> c is valid, but c is invalid.

B-> c is invalid for both b and c

Invalid cRV c

SQL case

Optimization 3: do not do the following for the index

The following usage can cause the index to fail

Calculation, such as: +, -, *, /,! =, is null, is not null, or

Functions, such as: sum (), round (), etc.

Manual / automatic type conversion, such as: id = "1", which is originally a number and is written as a string

SQL case

Optimization 4: do not put the index to the right of the range query

Give an example

For example, composite index: a-> b-> c, when where a = "" and b > 10 and 3 = "", only an and b and c can be used, because the index fails after the range (related to the B+ tree structure).

SQL case

Optimization 5: reduce the use of select *

Use override index

That is, the select query field is the same as the index field used in where.

SQL case

Optimization 6: like fuzzy search

Failure situation

Like "% Zhang San%"

Like "% Zhang San"

Solution

Use a composite index, that is, the like field is a query field for select, such as: select name from table where name like "% Zhang San%"

Use like "Zhang San%"

SQL case

Optimization 7: order by optimization

When order by is used for sorting in the query statement, if the index is not used for sorting, there will be sorting within the filesort file. In this case, when the amount of data is large or the concurrency is high, there will be performance problems and need to be optimized.

An example of what happened to filesort

The order by field is not an index field

The order by field is an index field, but override indexes are not used in select, such as select * from staffs order by age asc

There are both ASC ascending sort and DESC descending sort in order by, such as select a, b from staffs order by a desc, b asc.

When sorting multiple fields in order by, order by is not performed according to the index order, that is, not according to the leftmost prefix rule, such as: select a, b from staffs order by b asc, an asc

Index level solution

Sort using primary key index

According to the leftmost prefix rule, and using the overlay index to sort, when sorting multiple fields, keep the sort direction consistent

Force the use of an index, force index (index name), in the SQL statement

Do not sort in the database, sort at the code level

Order by sorting algorithm

Two-way sorting

Mysql4.1 used to use two-way sorting, which literally means to scan the disk twice and finally get the data, read the row pointer and ORDER BY column, sort them, and then scan the sorted list and re-read the data output from the list according to the values in the list. That is, read the sort fields from disk, sort them in buffer, and then read other fields from disk.

The disk IO of files is very time-consuming, so after Mysql4.1, there is a second algorithm, which is one-way sorting.

One-way sorting

Read all the columns needed by the query from disk, sort them in buffer by the orderby column, and then scan the sorted list for output, which is faster, avoids a second read, and turns the random IO into a sequential IO, but it uses more space because it keeps each row in memory.

When we inevitably use sorting, what if the index level cannot be optimized? Whenever possible, let MySQL choose to use the second one-way algorithm for sorting. This can reduce a large number of random IO operations and greatly improve the efficiency of sorting work. Let's take a look at the points that need to be paid attention to in one-way sorting optimization.

One-way sorting optimization point

Increase max_length_for_sort_data

In MySQL, the decision to use the "two-way sorting" algorithm or the "single-path sorting" algorithm is determined by the parameter max_length_for_ sort_data. When the maximum length of all returned fields is less than this parameter value, MySQL chooses the "single sort" algorithm, and vice versa, select the "multiple sort" algorithm. So, if you have enough memory for MySQL to store the unsorted fields that need to be returned, you can increase the value of this parameter to let MySQL choose to use the "one-way sorting" algorithm.

Remove unnecessary return fields and avoid select *

When there is not enough memory, you can't simply force MySQL to use the "one-way sorting" algorithm by forcibly increasing the above parameters, otherwise MySQL may have to divide the data into many segments and then sort them, which may outweigh the gain. At this point, you need to remove the unnecessary return fields and make the length of the returned result adapt to the limitation of the max_length_for_sort_data parameter.

Increase the sort_buffer_size parameter setting

If this value is too small, and if you return too many entries at a time, you are likely to sort it many times, and then finally concatenate the sorting results of each time, which will be slower. Increasing sort_buffer_size is not to let MySQL choose the "one-way sorting" algorithm, but to let MySQL minimize the segmentation of the data to be sorted in the sorting process. Because of the segmentation, MySQL has to use temporary tables for exchange sorting.

But the bigger the sort_buffer_size, the better:

Sort_Buffer_Size is a connection-level parameter that allocates the set memory at once when each connection needs to use this buffer for the first time.

Sort_Buffer_Size is not the bigger the better, because it is a connection-level parameter, too large settings and high concurrency may deplete system memory resources.

It is said that when the Sort_Buffer_Size exceeds 2m, mmap () instead of malloc () is used for memory allocation, resulting in inefficiency.

Optimization 8: group by

Its principle is also sorting first and then grouping, and its optimization mode can be referred to order by. Where is higher than having, so don't go to having if you can write the conditions that are limited by where.

After reading the above, have you mastered how to optimize the index in MySQL? 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.