preface

Today we will talk about how to optimize MySQL performance, mainly from the index aspect. MySQL > slow query log (); slow query log ()

Build table

/ / 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'job'.

    add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'Entry Event'

) CHARSET utf8 COMMENT'Staff Record';



// Insert data

INSERT INTO `test`.`staffs` (`name`.`age`.`pos`.`add_time`) VALUES ('z3'.22.'manager'.now());

INSERT INTO `test`.`staffs` (`name`.`age`.`pos`.`add_time`) VALUES ('July'.23.'dev'.now());

INSERT INTO `test`.`staffs` (`name`.`age`.`pos`.`add_time`) VALUES ('2000'.23.'dev'.now());



// Create a composite index (that is, an index containing multiple fields)

ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos);

Copy the code

Optimization 1: Use all indexes

introduce

The compound index contains several fields, and it is best to use all of them when querying, and strictly follow the index order, so that the query efficiency is the highest. (Best case, case by case)

SQL case


Optimization 2: leftmost prefix rule

introduce

If a compound index is created, the index should be in the same order as it was created, i.e. from left to right, for example, A -> B -> C.

Invalid index example

  • A -> C: A is valid, c is invalid
  • B ->c: both b and C are invalid
  • C: c is invalid

SQL case


Optimization 3: Do not do the following to indexes

The following uses will invalidate the index

  • Computations such as +, -, *, /,! =, <>, is null, is not null, or
  • Functions such as sum(), round(), and so on
  • Manual/automatic type conversion, such as id = “1”, which is a number, is written as a string

SQL case


Optimization 4: Do not place indexes to the right of range queries

For example,

A ->b->c where a=”” and b>10 and 3=””

SQL case


Optimization 5: Reduce the use of SELECT *

Using overwrite indexes

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

SQL case


Optimization 6: Like fuzzy search

Failure case

  • Like “% 3 %”
  • Like “% zhang SAN”

The solution

  • Select name from table where name = “% zhang3%”
  • Use like “zhang SAN %”

SQL case


Order by optimization

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

The situation with Filesort is an example

  • The ORDER BY field is not an index field
  • The order by field is an index field, but there is no overwriting index used in select.select * from staffs order by age asc;
  • Order by DESC descending order by ASC descending order by DESC descending order by ASC descending order by DESC descending orderselect a, b from staffs order by a desc, b asc;
  • Select * from order by; select * from order by;select a, b from staffs order by b asc, a asc;

Index level solution

  • Sort using primary key indexes
  • Follow the leftmost prefix rule, and use overwrite index sort, when multiple fields are sorted, keep the same sort direction
  • To force an index to be used in a SQL statement, force index
  • Sort not in the database, sort at the code level

Order by sorting algorithm

  • Dual sorting

    Mysql4.1 used double-path sorting, which literally means scanning the disk twice to get the data, reading the row pointer and ORDER BY column, sorting them, then scanning the sorted list and re-reading the data output from the list according to the values in the list. That is, sort fields are read from disk, sort in buffer, and read other fields from disk.

Disk IO of files is very time consuming, so after Mysql4.1, there is a second algorithm, which is single-way sort.

  • Single way sorting

    It reads all the columns needed for the query from disk, sorts them in buffer by order Derby columns, and then scans the sorted list for output. It is faster, avoids a second read, and turns random I/O into sequential I/O, but uses more space. Because it keeps every line in memory.

What if we can’t optimize at the index level when sorting is unavoidable? If possible, let MySQL choose to use the second single-path algorithm for sorting. This can reduce a large number of random I/O operations and greatly improve the efficiency of sorting. Let’s take a look at the points you need to pay attention to in single-way sorting optimization

Single way sorting optimization point

  • Increase max_length_for_sort_data

    In MySQL, the decision to use the “double sort” algorithm or the “single sort” algorithm is determined by the parameter maxlength_for sort_data. When the maximum length of all returned fields is less than this value,MySQL selects the “single-way sort” algorithm, otherwise, selects the “multi-way sort” algorithm. So, if there is enough memory for MySQL to store the unsorted fields that need to be returned, you can increase the value of this parameter to give MySQL the option to use the “single-way sort” algorithm.

  • Remove unnecessary return fields and avoid select *

    When memory is low, you cannot simply force MySQL to use “single-way sort” algorithm by forcing the above parameter. Otherwise, MySQL may have to split the data into many segments and then sort the data, which may not be worth the loss. In this case, you need to remove unnecessary return fields and make the return length fit the max_LENGTH_FOR_sorT_DATA parameter.

  • Increases the sort_buffer_size parameter setting

    If this value is too small, and if you return too many columns at once, you may end up sorting multiple times and then concatenating the results of each sequence, which will be slower. Increasing sort_buffer_size is not an attempt to get MySQL to select a “single-way sort” algorithm, but rather an attempt to get MySQL to select a “single-way sort” algorithm Try not to segment the data that needs to be sorted during the sort process, because fragmentation causes MySQL to use temporary tables for swap sorts.

But sort_buffer_size is not always better:

  • Sort_Buffer_Size is a connection-level parameter that allocates the set memory once each connection needs to use the buffer for the first time.
  • Sort_Buffer_Size is not always better. Because it is a connection level parameter, too large a setting and high concurrency may exhaust system memory resources.
  • It is said that when Sort_Buffer_Size exceeds 2M, mmap() is used instead of malloc() for memory allocation, resulting in reduced efficiency.

Optimization 8: Group by

Its principle is also sorting first and then grouping, and its optimization method can be referred to order by. Where having is higher than where having can be qualified.

IT brother

A big factory to do advanced Java development program ape

Follow wechat public account: IT elder brother

Java foundation, Java Web, JavaEE all tutorials, including Spring Boot, etc

Reply: Resume template, you can get 100 beautiful resumes

Reply: Java learning route, you can get the latest and most complete a learning roadmap

Re: Java ebooks, get 13 must-read books for top programmers