The optimization points of several common MySQL are summarized

Indexes related to

1. The query (or update, delete, can be converted to a query) does not use the index
This is the most basic step. You need to perform explain against sql to see if indexes are used in the execution plan. You need to focus on the fields of type=ALL and key=NULL.

2. Impose a function on an index field

to_char(gmt_created,  ' mmdd') = '0101 ' 

The correct way to write it

gmt_created between to_date( " 20090101 " ,  " yyyymmdd " ) and to_date( " 20090102 " ,  " yyyymmdd " )

3. Use full blur on index fields

member_id like  ' %alibab%'

The B tree does not solve such problems, so consider search engines.
But member_id like 'alibab%' can use the index.
In fact, using like '%xxxx%' for any 1 field is a non-standard practice that needs to be checked for errors.

4. Index of multi-column fields without the use of leading indexes
Index :(memeber_id, group_id)
where group_id=9234, actually, there is no way to use the above index for this condition. This is a very common misuse. To understand why you can't use this index, you need to understand how mysql constructs multi-column indexes.
The index is an B tree. The problem is that for multi-column indexes, mysql assemfies the index fields in the order in which the index was created to form a new string that is used as the key to build the B tree. Therefore, in the query condition, there is no way to access the B tree of multi-column indexes without using the leading column.
Should be indexed :(group_id, member_id)

5. Fields outside the index were accessed
Index (member_id subject)

select subject from offer where member_id=234

It is better than member_id=234 with a large number of records

select subject, gmt_created from offer where member_id=234

The reason is that clause 2 sql accesses the records in the table based on the rowid found by the index. Article 1 sql USES index range scan to obtain results.
If an sql executes many times, but the fields it reads are not covered by the index, then a coverage index may need to be established.

6. Counting count(id) is sometimes slower than count(*)

count(id) === count(1) where id is not null

If there is no (id) index, the full table scan is used, while count(*) USES the optimal index for a quick full scan with the index
Counting system 1 using count(*)

7. Use the stop mechanism correctly
Determine whether member_id has a record in offer table:

select count(*) from offer where member_id=234 limit 1

Better than

select count(*) from offer where member_id=234

The reason is that sql 1 will stop after obtaining records that meet the criteria in article 1.

Efficient paging
1. Efficient paging
Using join technology, the qualified id was found by using the index, and the temporary table was constructed. The small temporary table was used to make join with the original table

select *
from
(
select t.*, rownum AS rn
from
(select * from blog.blog_article
where domain_id=1
and draft=0
order by domain_id, draft, gmt_created desc) t
where rownum >= 2
) a
where a.rn <= 3

Should be rewritten as

select blog_article.*
from
(
select rid, rownum as rn
from
(
select rowid as id from blog.blog_article
where domain_id=1
and draft=0
order by domain_id, draft, gmt_created desc
) t
where rownum >= 2
) a, blog_article
where a.rn >= 3
and a.rid = blog_article.rowid

order by USES no indexes
Index (a, b,c)
Mixed collation

gmt_created between to_date( " 20090101 " ,  " yyyymmdd " ) and to_date( " 20090102 " ,  " yyyymmdd " )

0

The leading column is missing

gmt_created between to_date( " 20090101 " ,  " yyyymmdd " ) and to_date( " 20090102 " ,  " yyyymmdd " )

1

The middle column is missing

gmt_created between to_date( " 20090101 " ,  " yyyymmdd " ) and to_date( " 20090102 " ,  " yyyymmdd " )

2

Columns that are not in the index are used for sorting

WHERE a = const ORDER BY a, d /* d is not part of index */

Use primary key efficiently
Random query
1 Wrong Thing to do:

gmt_created between to_date( " 20090101 " ,  " yyyymmdd " ) and to_date( " 20090102 " ,  " yyyymmdd " )

4

This sql execution requires a full table scan and saves the data to a temporary table, which is a very time-consuming operation.
Improved practice using offsets.

gmt_created between to_date( " 20090101 " ,  " yyyymmdd " ) and to_date( " 20090102 " ,  " yyyymmdd " )

5

This approach makes use of the indexes on kind_id, reducing the number of data blocks to be scanned compared to the above approach. However, if offset is very large, then the data blocks that need to be scanned are also very large, and in the extreme case all the data blocks of the index k are scanned.
The best approach is to use the primary key for scope lookup

gmt_created between to_date( " 20090101 " ,  " yyyymmdd " ) and to_date( " 20090102 " ,  " yyyymmdd " )

6

This sql USES primary key to do range query, complete index, and only read one record, very fast. However, the limitation of this usage is that primary key must be of type int and be continuously self-growing.

Efficient join
1. Small table drives large table join
2. Avoid subqueries

Subqueries are a potential performance hazard. sql should be rewritten using join.

The data type
1. Avoid implicit conversion

gmt_created between to_date( " 20090101 " ,  " yyyymmdd " ) and to_date( " 20090102 " ,  " yyyymmdd " )

7

As you can see, the condition account=123 does not use the only index 'username'. The server of mysql reads all the records from storage engine and USES the function to_number() to convert the account in the record to a number, which is then compared with the parameters. There are two records in our test table, and the value of rows in the execution plan is also 2, and the value of type is ALL, which also indicates that the index 'username' is not used.

mysql> explain select * from user where account='123 '  \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: user

type: const

possible_keys: username

key: username

key_len: 33

ref: const

rows: 1

Extra:

1 row in set (0.00 sec)

The argument is of string type, and we can see that the index 'username' is used.
This is a practice that is often misused.

2. Primary keys are not self-augmenting columns
A self-augmenting primary key has several benefits:

High insertion performance. Reduce the fragmentation of page. Provides the performance of the level 2 index, reducing the space of the level 2 index, because the level 2 index stores the value of the primary key, not the row in page.