1. Introduction

1.1 Why optimize SQL?

Low performance, too long execution time, too long wait time, poor SQL statement (connect query), index failure, improper server parameter Settings (buffering, number of threads)

1.2 SQL statements

  • Writing process
select dinstinct .. from .. join .. on .. where .. group by ... having .. order by .. limit ..Copy the code
  • The parsing process
from .. on.. join .. where .. group by .... having ... select dinstinct .. order by limit ...Copy the code

1.3 The nature of SQL optimization

SQL tuning is primarily about optimizing indexes

Indexes are ordered data structures that help MySQL efficiently retrieve data.

1.4 Advantages and Disadvantages of indexes

The downside of indexing

  1. The index itself is large and can be stored in memory/hard disk (usually hard disk)
  2. Indexes are not suitable in all cases: a. Small amounts of data b. Frequently updated fields C. Rarely used fields
  3. Indexes reduce the efficiency of adding, deleting, modifying, and querying.

Advantages of indexes

  1. Improve query efficiency (reduce I/O usage)
  2. Reduce CPU usage (… Order by age desc; order by age desc;

MySQL > query execution flow > statement execution sequence

2. Avoid index failure

Do not perform any more operations (calculations, functions, type conversions) on the index, otherwise the index will be invalidated.

  • Do not add, subtract, multiply, and divide indexes, e.g. where t.id * 3 = ‘ ‘; Multiplying by 3 is a calculation that invalidates the index.
  • Cannot be used that does not equal (! =, <, >, is null, is not null), fuzzy query like starts with a percent sign (‘ % parameter % ‘).

For composite indexes.

  • Do not use it across columns or out of order (satisfying the best left prefix).
  • Use full index matching as much as possible, and remove unused indexes from composite indexes.
  • Index failure on the left causes index failure on the right.

2.1 Index Failure Caused by Calculation

Explain select * from book where authorid * 2 = 1 and typeid = 2;Copy the code
  • The left authorID index is invalidated by multiplying by 2

If it is a compound index, the one on the left is invalidated and the one on the right is invalidated.

If it’s not a compound index, failing on the left doesn’t cause the right to fail.

Drop index index_atb on book; Alter table book add index index_A (authorID); alter table book add index index_t(typeid); Explain select * from book where authorid * 2 = 1 and typeid = 2;Copy the code

In the case of a single-value index, the index on the right is not invalidated.

2.2! = Indicates that the index is invalid

#! Explain select * from book where authorid! = 1 and typeid = 2;Copy the code

Authorid uses’! The =’ operator invalidates the index.

2.3 Comparison symbols (> <! =), causing index invalidation

The presence of SQL optimizers in the service layer may affect our optimization.

Drop index index_t on book; drop index index_a on book; Alter table book add index index_at(authorID, typeID); Explain select * from book where authorid = 1 and typeid = 2; Explain select * from book where authorid > 1 and typeid = 2;Copy the code
  • In database versions 5.6 and earlier, the: > ‘operator itself and the index on the right are invalidated.
  • In database versions 5.7 and earlier, the: ‘>’ operator itself will be range, and the right-hand one will be invalidated.

Explain select * from book where authorid < 1 and typeid = 2; Explain select * from book where authorid < 4 and typeid = 2;Copy the code

The possibility of index failure is a probabilistic event.

Conclusion:

  • Generic SQL optimizations work well in most cases, but are not 100% correct because of the SQL optimizer.
  • In general, range queries (>, <, in) have valid indexes on their own, and all on the right are invalidated.
  • Use a using index whenever possible, that is, the index will not be invalidated.

2.4 Index Failure (Fuzzy Query like) Causes index failure

Alter table book add index index_name(name); # explain select * from book where name like '%1%';Copy the code

Invalidates index by using a fuzzy query like starting with a percent sign:

# explain select * from book where name like '1%';Copy the code

Because a fuzzy query does not start with % backed up, the index is not invalidated:

2.5 Do not cast

Explain select * from book where name = 123;Copy the code

Because name is a string and 123 is an integer, 123 is converted to ‘123’ at the bottom level of SQL, so the index is invalid.

2.6 Do not use OR

# explain select * from book where name = "or typeid =";Copy the code

If the or join condition is used, both indexes will be invalidated:

3. Why does the index fail?

  • The field type is stored when the index is set. For example, the index is invalid in the strong conversion process. The type of the field does not match that of the index after the strong conversion, leading to a full table scan.
  • Why should an optimal left prefix be satisfied in an aggregated index? Because the aggregated index is stored in the original order in the stored procedure, if the order of the query conditions is inconsistent with the order of the index, the index will be invalid.