Tips ： When the article is finished , Directories can be generated automatically , How to generate it, please refer to the help document on the right

Silicon Valley ：mysql Optimize the original video address
mysql The four floors of include
adjoining course
Service layer
Engine layer
Storage layer

mysql Comparison of two storage engines

SQL The main reason for performance degradation

• The query statement is complex
• Index failure （sql The main optimization points of development ）
• Too many associated queries join（ Design defects or unavoidable requirements )
• Server tuning and parameter settings （ buffer 、 Number of threads, etc )

SQL Execution order of keywords

JOIN Icon

left join

right join

inner join

is null

Full connection full out join

full out join Oracle Database support , however mysql I won't support it
mysql Changing grammar is not supported , Need to rewrite

sellect* from A left join B on A.key= B.key union sellect* from A
right join B on A.key= B.key

mysql Rewrite as

sellect* from A left join B on A.key= B.key where B.key is null union
sellect* from A right join B on A.key= B.key where A.key is null

Index optimization

The left and right connections are opposite, plus

left join To the right key Suo Yin

select * from a left join b on a.key=b.key

To be indexed as create index b-key on b(key)

The right connection is opposite

join Statement optimization

• Reduce... As much as possible Join Nesting in statements
• Always drive large result sets with small result sets
• Optimize inner query statements first
• Guarantee Join Statement on the driven table Join The condition field has been indexed
• When there is no guarantee that the driven meter Join If the condition field is indexed and the memory resource is sufficient , Don't be too stingy JoinBuffer Set up

Possible cases of index failure

There are the following table indexes

create index a_key1_key2_key3 on a (key1,key2,key3)

1. Full value matching my favorite
Use indexes as much as possible to find matches
2. The best left prefix rule
If you index multiple columns , Follow the leftmost prefix rule . It means that the query starts from the left front of the index and does not skip the columns in the index .

for example select * from key1=? and key2=? and key3=?

The query statement must contain at least the first column of the index

for example select * from key1=? and key3=?

3. Do nothing on the index column （ Calculation 、 function 、( Automatically or Manual ) Type conversion ）, It will lead to index failure and turn to full table lookup
4. The storage engine cannot use the column to the right of the range condition in the index

Scope conditions include like < >

EXPLAIN SELECT id FROM member WHERE id =? and
NAME = ? AND ORG_DEPARTMENT_ID=?

EXPLAIN SELECT id FROM member WHERE id =? and NAME like ‘%xx%’ AND ORG_DEPARTMENT_ID=?

5. Try to use overlay index ( Queries that only access the index ( The index column is consistent with the query column )), Reduce select*
6. mysql In use is not equal to (!= perhaps <>) Unable to use the index will result in a full table scan R
7. is null ,is not null You can't use indexes
8. like Start with a wildcard (‘%abc…’ )mysql Index invalidation will become a full table scan operation
9. String index is invalid without single quotation marks
The essence of a string without quotation marks is that the index fails due to type conversion
10. To use less or, Using it to connect will invalidate the index

SQL Optimize

Full value matching my favorite , The leftmost prefix should follow ;
Leading brother can't die , The middle brother can't break ;
Less computation on index columns , After the range, it all fails ;
Like 100% right , Overlay index does not write stars ;
I don't want empty values and or, Index failure should be used less ;
VAR Don't throw quotation marks ,SQL It's not hard to be advanced ！

in and exists The difference between

in Execute subquery first
exists First perform the main search , That is, outer query
for example

SELECT ID FROM A WHERE ID IN (SELECT id FROM B)

SELECT ID FROM A WHERE EXISTS (SELECT id FROM B)

When B The amount of data in the table is greater than A, Use in be better than B
Otherwise use EXISTS better