Simple optimization by index

  • The first field in a federated index does not walk the index using a range query
EXPLAIN SELECT * FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';
Copy the code

It can be seen that possible_key prompt may use joint index, but Type uses all full table scan. Mysql internally determines that the first field uses range query, and the result set is very large, so it may need to return to the table. Due to low efficiency, full table scan is used.

  • Use overwrite index optimization
  • In and OR select the index when the table data is large. In and OR select the full table scan when the table data is small
EXPLAIN SELECT * FROM employees WHERE name in ('LiLei','HanMeimei','Lucy') AND age = 22 AND position ='manager';
Copy the code

  • Like SS%

Index Condition Pushdown (ICP), like KK% For auxiliary syndication indexes (name,age,position), normally the left-most prefix rule is followed, SELECT * FROM employees WHERE name like ‘LiLei%’ AND age = 22 AND position =’manager’ SELECT * FROM employees WHERE name like ‘LiLei%’ AND age = 22 AND position =’manager’ Age and position in the resulting index row are unordered and cannot be used well. In previous versions of MySQL5.6, this query could only match entries in the federated index with names starting with ‘LiLei’, and then run the primary keys back to the table to find the corresponding entries in the primary key index. MySQL 5.6 introduces index push-down optimization, which can judge all fields contained in the index during index traversal and filter out the unqualified records before returning to the table, effectively reducing the number of return to the table. With index push-down optimization, the above query matches an index whose name starts with ‘LiLei’ in the federated index, and filters the age and position fields in the index. After filtering the primary key IDS of the remaining indexes, the query returns to the table to query the entire row.

Common SQL in-depth optimization

  • Order by and Group by optimizations
EXPLAIN select * from employees where name = 'lilei' and position = 'dev' order by age
Copy the code

Select * from key_len=74; select * from key_len=74; select * from age;

EXPLAIN select * from employees where name = 'lilei' order by position;
Copy the code

Key_len =74 uses the name index because position is used for sorting, skipping age and using filesort.

EXPLAIN select * from employees where name = 'lilei' order by age,position;
Copy the code

Search only uses index name,age, and position for sorting, without using filesort.

EXPLAIN select * from employees where name = 'lilei' order by position,age;
Copy the code

Because position and age are reversed.

EXPLAIN select * from employees where name = 'lilei' and age=18 order by position,age;
Copy the code

Using filesort is not used because age is constant and is optimized during sorting, so the index is not reversed.

EXPLAIN select * from employees where name = 'lilei' order by age desc,position asc;
Copy the code

Using Filesort is created, although the order of the sorted fields is the same as the index order, and the order by default is in ascending order.

EXPLAIN select * from employees where name in('lilei','hanmeimei') order by age desc,position asc;
Copy the code

For sorting, multiple equality conditions are also range queries.

  • Join Associated query optimization

For query optimization of multi-table join association, in fact, there is no optimized space. The association between tables should be associated between two tables as far as possible. If multi-table association is necessary, it should be handled in the code as far as possible. Nested-loop join (NLJ) algorithm: A row at a time from the first table (driven table) read row, in this row of data to fetch the associated field, according to the associated field in the other table (driven table) to fetch the conditions of the row, and then extract the result set of the two tables.

EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;
Copy the code

According to the execution plan:

The driving table is T2, and the driven table is under T1. The driver table is executed first; The optimizer generally takes precedenceThe small table is the driver table. So with inner join,The first list is not necessarily the driver list.

When left JOIN is used, the left table drives the table, the right table drives the table, and the left table drives the table. When right Join is used, the right table drives the table, and the left table drives the table.

NLJ algorithm is used. In general join statements, if using Join buffer does not appear in plan Extra, it indicates NLJ when the join algorithm is used.

If the associated field of the driven table has no index,The NLJ algorithm has low performance, myslq will select the Block nested-loop join algorithm.

2) Block based Nested Loop join (BNL) algorithm

把The driver tableThe data is read into join_buffer and scannedWas the driver tableCompare each row of the driven table to the join_buffer data.

EXPLAIN select * from t1 inner join t2 on t1.b= t2.b;
Copy the code

SQL process:

1. Load all data of T2 into join_buffer

2. Compare each row in T1 with the data in join_buffer

3. Return the data that meets the join condition

A full table scan is performed on both tables T1 and T2, so the number of rows scanned is 10000 (total data for T1) +100 (total data for T2) =10100. And the data in join_buffer is unordered, so every row in table T1 needs to be checked 100 times, so the number of checks in memory is 10010000 = 1 million times.

What if the t2 table is too large for join_buffer?

The size of join_buffer is set by the join_buffer_size parameter. The default value is 256K. If you can’t fit all the data, it will be usedload.

The associated field of the driven table has no index.

The reason is simple: if you use the NLJ algorithm, the scan function is 10010,000 is a million times. This isDisk scan. Disk scanning is inefficient.

Optimization for relational SQL

  • Mysql > select NLJ from NLJ; mysql > select NLJ from NLJ;
  • Small tables drive large tables. When the amount of data in a table is known, associate a table with a large amount of data as much as possible.

In and Exsits optimizations

Principle: Small tables drive large tables

Count (*) query optimization

Count (*) is efficient and does not require additional optimization. You don’t have to use count(1).

Optimize the summary

  • MySQL supports two kinds of sorting: index and filesort. Index refers to scanning the index itself for sorting. Index is efficient, filesort is inefficient.
  • Order by satisfies both cases using index

1) The order by statement satisfies the left-most index prefix rule. 2) Use the combination of where clause and order by clause to satisfy the left-most prefix principle

  • Try to sort the index columns as much as possible, following the left-most prefix rule for ** index creation (index creation order) **.
  • Using filesort is generated if the order by condition is not in the index column.
  • Use overwrite indexes whenever possible.
  • Order by group by group by group by group by group by Group by optimizations that do not require sorting can add order by NULL to disable sorting. Note: Try not to use having when using where.

Using filesort to sort files in detail

  • Single-way sort: Fetch all the fields that meet the condition at one time and sort them in sort Buffer.
  • Double-sort (back-table sort) : Retrieves the sorted fields and row ids that can directly locate the row data based on the condition, and sorts the rows in sort Buffer. After sorting, the other required fields need to be retrieved again.

Max_length_for_sort_data (default: 1024 bytes) and the total size of the field to be queried are used to determine which sort mode to use. If the total length of the field is less than max_LENGTH_FOR_sorT_data, single-way sort mode is used; If greater than, then use two-way sort mode.

Index design principles

  • Code first, index second
  • Union indexes try to cover conditions
  • Do not index small cardinality fields. For example, the gender field.
  • Long strings can be indexed with a prefix.
  • Where and order by conflict before WHERE