Summarize the most basic operations of MySQL optimization

                                                                                                               

Optimization ideas

The detailed MySQL optimization steps are as follows:

• Check the data table structure and improve the imperfect design
• Run the main business and collect commonly used database query SQL
• Analyze the query SQL, split it appropriately, add indexes, etc. to optimize the query
• While optimizing SQL, optimize the code logic
• Add local cache and redis cache

Try not to use NULL values

Because when creating a table, if you do not set a default value for the created value, MySQL Will be set to NULL by default. So why is it not good to use NULL?

• NULL makes index maintenance more complicated. It is strongly recommended to set NOT NULL
• NOT IN on the index column. , != and other negative conditional queries will always return empty results when there is a NULL value, and the query is prone to errors in the
• NULL column An extra byte is needed as a flag to determine whether it is NULL
• When using NULL, it may not be of the same type as other values ​​in the column, causing problems. (It behaves differently in different languages)
• MySQL has difficulty optimizing queries for columns that can be NULL

So for those fields that were lazy before, Manually set a default value, empty string, and add 0.

Although this method does not improve the performance of MySQL much, it is a good habit, and seeing the big from the small, don't ignore these details.

Add index

For frequently queried fields, please add indexes. The query speed with and without indexes differs by ten times or more.

• Generally speaking, each table needs to have a primary key idField
• Fields commonly used for queries should be indexed
• # For fields of type ##varchar, when creating an index, it is best to specify the length
When the query has multiple conditions, the conditions with indexes are preferred
• Like
• LIKE Conditional fuzzy search is invalid for field indexes, and additional keyword indexes need to be established to solve the problem
• Please try not to constrain the relationship between tables at the database level, The dependencies between these tables should be resolved at the code level

When there are constraints between tables, although the SQL statements for additions, deletions and queries become simpler, the negative effects are insertions, etc. Constraints will be checked when operating the database (although constraints can be ignored manually), which is equivalent to writing some business logic to the database layer, which is inconvenient to maintain.

Optimize the table field structure

Do not use the string type for data that can be represented by integers in the database. Should you use

varchar or charDepends on the possible values ​​of the field.

This kind of optimization is often not feasible when there is a large amount of data in the database. It is best to design it before the database design.

For those columns with very limited possible values, use
• tinyint instead of VARCHAR,
  For example, to record the mobile device platform, there are only two Value: android, ios, then you can use 0 to represent android and 1 to represent ios. This column must be commented
  • Why not use
  • ENUM? ENUM Difficulty in expansion. For example, the mobile platform later added an ipad, which would be confusing, but tinyint just adds a 2, and ENUM is very strange to handle in the code. Whether it is treated as an integer or a string varies from language to language.
  In this method, the meaning of each value must be stated in the database comment or code
For those fixed-length strings, you can use
• char, such as zip code, is always 5 digits
For those strings with unknown length, use
• varchar
and do not abuse
• bigint, such as records For the id field of the table with the number of articles, just use int. The upper limit of 2.1 billion articles is enough.
Properly break the database paradigm and add redundant fields to avoid queries. When querying table connection

, the

int type must be faster than varchar, because the comparison of integers can be achieved by directly calling the underlying operator, while the string type The comparison is done character by character.

Fixed-length data query is faster than variable-length data query, because the offset between fixed-length data and data is fixed, and it is easy to calculate the offset of the next data. For variable-length data, one more step is needed to query the offset of the next data. but. Fixed-length data may waste more storage space.

Large table splitting

For those tables whose data volume may exceed 5 million in the near future or grow rapidly, be sure to split tables vertically or horizontally in advance. When the data After the volume exceeds one million, the query speed will drop significantly.

Try to finalize the plan for sub-database and sub-table in the early stage of database design, otherwise it will greatly increase the code complexity and be difficult to change later.

Vertical table partitioning is based on external variables such as dates, and horizontal table partitioning is based on certain field relationships in the table, using hash mapping to divide the table into equal parts.

The prerequisite for sub-database and table sub-database is that before executing the query statement, you already know which sub-database and which sub-table the data to be queried may fall into.

Optimizing query statements

This is the initiator of many system database bottlenecks.

• Please try to use simple queries and avoid using table links
• Please try to avoid full table scans. Statements that will cause full table scans include but are not limited to:
  • where clause condition is always true or empty
  • Use LIKE
  • Use inequality operator (<>, !=)
  • Query the column containing is null
  • When using or
• on non-index columns to query with multiple conditions, please put Put simple query conditions or index column queries in front
• Please try to specify the columns you need to query, don't be lazy to use select *
  • If you don't specify, on the one hand, redundant data will be returned, occupying bandwidth, etc.
  • On the other hand, when MySQL executes a query, if there are no fields, it will first query the fields in the table structure.
• Uppercase query keywords are a little faster than lowercase ones
• Using subqueries will create temporary tables, which will be slightly slower than links (JOIN) and unions (UNION)
• Try not to use database functions when querying on index fields, which is not convenient for caching query results
• When there is only one row of data, please use LIMIT 1. If there is too much data, please set LIMIT appropriately and perform paging query
• Never use ORDER BY RAND(), the performance is extremely low

Add cache

Using caches such as redis and local file cache can greatly reduce the number of database queries. When it comes to caching, you must analyze the data characteristics of your own system and make appropriate choices.

• For some commonly used data, such as configuration information, etc., it can be placed in the cache.
• The table structure of the database can be cached locally.
• The cached data must be Pay attention to timely updates, and set the validity period
• Increasing cache will definitely increase system complexity, so be sure to pay attention to the trade-off

Check the data table structure

Recommended learning: "mysql video tutorial"

The above is the detailed content of Summarize the most basic operations of MySQL optimization. For more information, please follow other related articles on the PHP Chinese website!