MySQL performance optimization is the most common topic in the forum. Why should it be optimized?
Because:
• A database bottleneck occurs and the throughput of the system is slow
• As the application runs, more data is stored in the database, which takes longer to process
• Data read and write speeds are slow
This is what we call the “performance problem”, and programmers always have trouble with it!
Today I summed up some insights on MySQL optimization, hope you can have all the help in your future work!
Optimization of the like leading character
Like fuzzy queries such as ‘%AAA%’ and ‘%AAA’ will not use indexes, but business will inevitably need to use this form again.
There are two common methods:
Scheme 1: use overwrite index, that is, the queried column can be obtained only with the index, without the need to query the table record, so also go index;
Scheme 2: Use the locate function or position function instead of the like query, If table.field like ‘%AAA%’, you can change to Locate (‘AAA’, table.field)>0 or POSITION(‘AAA’ IN table.field)>0
And exist in
There is little difference between in and EXISTS if the two tables queried are of the same size. If one of two tables is small and the other is large, the subtable exists and the subtable in are small. For example, table A (small table) and table B (large table) are used in each case.
Example 1:
Example 2:
Not in and not exist
Select * from ‘not in’; select * from ‘not in’; The subqueries of Not Exist can still be used for indexes on the table. So no matter which table is large, not exists is faster than not in!
Subquery optimization
MySQL > select * from outer; select * from outer; select * from outer;
MySQL 5.6 select * from subquery; Caching the result set of a subquery into a temporary table. The temporary table index is used to remove duplicate entries and may later be used to join a query. This technique is called materialized subquery (select_type subquery). In 5.5, the DEPENDENT SUBQUERY is used.
3) Sub-queries can generally be changed into associated queries of tables, and there will be creation and destruction of temporary tables in sub-queries, which is inefficient.
straight_join
Mysql hint: the mysql optimizer may select the wrong driver for the association of multiple tables, resulting in an increase in association times and slow execution of SQL statements.
This was when an experienced DBA needed to make a judgment call and choose the right driver table, and that’s when StraightJoin came into play. Let’s take a look at straightjoin optimization using Straight_JOIN.
By straight_JOIN Try using user as the driver table and using straight_JOIN to enforce the join order:
Efficient paging
1) Traditional paging
Select * fromtablelimit10000, 10
2) Limit principle
• Limit 10000, 10
• The larger the offset, the slower it is
3) Paging is recommended
Optimization of complex relational SQL
1, first of all, the result set returned by the query, usually the result set returned by the query is very small, there is room for optimization.
2. By viewing the execution plan, see the driver table selected by the optimizer. The rows of the execution plan can roughly reflect the problem.
3. Make clear the association of each table and check whether the associated fields have appropriate indexes.
4. Test the optimization idea by using straight_join keyword to force adjustment of the driver table selection.
5. If possible, split complex SQL. Keep it as simple as possible.
force index
Sometimes the optimizer does not select the optimal execution plan due to inaccurate statistics and other reasons. You can manually change the execution plan of mysql, for example:
The optimization of the count
In order of efficiency, count(field)
conclusion
MySQL performance optimization is mainly to understand innoDB index principle and structure and SQL execution plan, on the basis of accumulated experience, practice makes perfect.