Shulou(Shulou.com)06/01 Report--

Common strategies for query optimization

1. Optimize data access: the application should reduce data access to the database, and the database should reduce the number of records actually scanned

For example, Redis cache, avoid "select * from table"

2. Rewrite SQL

For operations that require a large amount of data, they can be performed in batches to reduce the impact on the production system and thus alleviate replication timeouts.

MySQL join seriously reduces concurrency, so you should join too many tables as much as possible. It is recommended to implement some connection functions in the application layer.

3. Redesign the database table

In the absence of other optimization methods, you can consider changing the table structure design, adding cached tables, temporarily storing statistics, or adding redundant columns to reduce connections.

4. Index

The index can solve 80% of the problems.

Introduction of optimizer

The deficiency of optimizer

1. The statistics of the data may be wrong.

2. CPU, memory and whether the data is being cached will affect the optimizer.

3. The optimizer will not consider concurrency, and Zheng Yong of resources may lead to performance problems.

Tip:

1. Use the index

Select * from table1 use index (col1_index,col2_index) where col1=1 and col2=2 and col3=3

2. Do not use index

Select * from table1 ignore index (col3_index) where col1=1 and col2=2 and col3=3

3. Force the use of indexes

Select * from table1 force index (col3_index) where col1=1 and col2=2 and col3=3

Note: use index,ignore index,force index only affects the index to be used to retrieve records and joins in the MySQL table, not order by or group by or group by

4. Do not use query cache

SQL_NO_CACHE

5. Use query to cache explicit_mode,query_cache_type=2, indicating that SQL needs caching before caching

SQL_CACHE

6 、 Straight_join

Join in the order of the tables described in the FROM sentence

Connection Mechanism of MySQL

Nested Loop join

The MySQL optimizer generally chooses a small table to drive the table (external table)

Optimization of various sentences

Optimization of connection

1. No more than 4 tables should be connected

2. The column of the ON,using clause should have an index.

3. The cost of converting to inner join,left join is much higher than inner join.

4. Explain checks the connection. If the output rows column is too high, consider whether the index or join table is in the wrong order.

5. Anti-paradigm design

Optimization of group by, distinct and order by statements

1. Sort fewer rows as much as possible

2. Multiple tables are joined, and the column of order by should belong to the first table in the join order.

3. Sort by index

4. The columns of group by,order by should be the columns of the first table as far as possible. If not, consider redundant columns.

5. Make sure that the index column is the same as the order by column and is sorted in the same direction

6. Add sort_rnd_buffer_size

7. Change the tempdir variable to point to a memory-based file system or other faster disk

8. Specify Order by null

By default, MySQL will sort all Group by queries. If you want to avoid sorting results, you can specify Order by null

9. Optimize Group by with rollup

Consider implementing in the application layer

10. Replace group by columns with non-group by columns

For example, if the group by z can achieve the same result, then there will be as few group by as possible, for example, group by XBI y.

11. Consider replacing group by statements with Sphinx

Optimized subquery

In most cases, the join is faster than the subquery, and the temporary table generated by the subquery has no index

Select distinct col1 from T1 where col1 in (select col1 from T2)

Rewrite as follows:

Select distinct t1.col1 from t1,t2 where t1.col1=t2.col1

Select * from T1 where id not in (select id from T2)

Rewrite:

Select * from T1 where not exists (select id from T2 where t1.id=t2.id)

It can also be rewritten as:

Select table1.* from table1 left join table2 on table1.id=table2.id where table2.id is null

Transfer the clause from the outside of the subquery to the inside

Select * from T1 where S1 in (select S1 from T1) or S1 in (select S1 from T2)

Rewrite

Select * from T1 where S1 in (select S1 from T1 union all select S1 from S2)

Select (select column1 from T1) + 5 from T2

Rewrite

Select (select column1+5 from T1) from T2

Optimize limit sentences

Optimize IN

Optimize Union

Optimize queries with fields of type BLOB and Text

Optimization of filesort

Optimize SQL_CALC_FOUND_ROWS

Optimize temporary table

OLAP service optimization

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.