Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.
Slow query logs to find the SQL to be optimized
Enable and store slow query logs
-
Show variables like ‘slow_query_log’
-
Set global show_query_log_file=’/home/mysql/sql_log/mysql-slow. Log ‘
-
Set global long_QUERies_NOT_USING_INDEXES = ON
-
Set global long_query_time=1
Slowly querying log content
SQL optimization through execution plan analysis
SQL optimization
Max ()
SQL select Max (money) from pay SQL select Max (money) from pay SQL select Max (money) from pay
count()
Count (*) will count columns that are empty, and count(release_year) will not count columns that are empty if you specify a field directly
Subquery optimization
Subqueries are usually converted to join queries, but be aware of data duplication
Limit query optimization
Limit is usually accompanied by an Order by sort, so it is best to use indexed fields or primary keys for order by sort fields
The index optimization
How do I select the right columns to create an index
Discreteness refers to the number of non-repeating values of the column, and the more discreteness, the more discreteness it should be placed first in the federated index
Delete unused and duplicate indexes
Database table structure optimization
Choose the appropriate data type
Formal and anti-formal optimization
Table vertical split
Table horizontal split
Horizontal split is to solve the problem of large amount of data in a single table. It can be split by modular operation. The table structure of each table is the same, which brings about the problem of query and background analysis statistics
