Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what is the most basic operation of MySQL optimization". In the daily operation, I believe that many people have doubts about what is the most basic operation of MySQL optimization. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "what is the most basic operation of MySQL optimization"! Next, please follow the editor to study!

Optimization idea

The detailed MySQL optimization steps are as follows:

Check the data table structure to improve the imperfect design

Go through the main business and collect the commonly used database query SQL

Analyze query SQL, split properly, add index, etc. Optimize query

Optimize the code logic while optimizing the SQL

Add local cache and redis cache

Do not use null values as much as possible

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

NULL makes index maintenance more complex. It is strongly recommended to set NOT NULL on index columns.

NOT IN,! = and other negative conditional queries return always empty results with null values, and the query is prone to errors.

The NULL column requires an extra byte as the flag bit to determine whether it is NULL or not

When using NULL, other values in this column may not be of the same type, causing problems. (express differently in different languages)

It is difficult for MySQL to optimize queries for columns that can be NULL

So for fields that used to be lazy, manually set a default value, an empty string, and add 0.

Although this approach does not improve the performance of MySQL much, it is a good habit, and see the whole through a small part, do not ignore these details.

Add Index

For fields that are queried frequently, please add an index, and the query speed with and without an index is ten times or more.

Generally speaking, each table needs to have a primary key id field

Fields commonly used for queries should be indexed

For fields of type varchar, it is best to specify the length when indexing

When a query has more than one condition, the condition with index is preferred.

Fuzzy search such as LIKE condition is not valid for field index, and another keyword index is needed to solve the problem.

Please try not to constrain the relationship between tables and tables at the database level. The dependencies between these tables should be resolved at the code level.

When there are constraints between the table and the table, although the addition and deletion of SQL statements become simple, but the negative effect is that operations such as insert will check the constraints (although you can manually set to ignore constraints), which is equivalent to writing some business logic to the database layer, which is not easy to maintain.

Optimize table field structure

For those data in the database that can be represented by shaping, do not use the string type, whether to use varchar or char depends on the possible value of the field.

This optimization is often not feasible after there is a large amount of data in the database, and it is best to design it before the database is designed.

For columns with limited possible values, use tinyint instead of VARCHAR

For example, to record the mobile device platform, there are only two values: android,ios, then you can use 0 for android,1 to represent ios. This column must be annotated.

Why not use ENUM? It is difficult to expand ENUM, for example, later an ipad was added to the mobile platform, isn't it silly, and tinyint plus 2 is fine, and ENUM is particularly strange to deal with in the code, whether it is treated as a shape or a string, which varies from language to language.

In this way, be sure to write the meaning of each value in the database comments or code.

For fixed-length strings, you can use char, such as the zip code, which is always 5 digits

For strings of unknown length, use varchar

Do not abuse bigint, such as the table id field that records 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 table joins when querying

When querying, be sure that the int type is faster than varchar, because the integer comparison can be directly called the underlying operator, while the string comparison should be compared character by character.

Fixed-length data is faster than variable-length data query, because the offset between fixed-length data and data is fixed, so it is easy to calculate the offset of the next data. On the other hand, the variable length data needs one more step to query the offset of the next data. But. Fixed-length data may waste more storage space.

Large table split

For those tables whose amount of data may exceed 500W or grow rapidly in the near future, be sure to make vertical or horizontal tables in advance. When the amount of data exceeds one million, the query speed will decrease significantly.

Try to finalize the scheme in the early stage of database design, otherwise it will greatly increase the code complexity and not easy to change in the later stage.

The vertical sub-table is divided into tables according to external variables such as dates, while the horizontal sub-table is divided by using hash mapping according to some field relations in the table.

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

Optimize query statement

This is the cause of many system database bottlenecks.

Please try to use simple queries and avoid using table links

Try to avoid full table scans. Statements that can cause full table scans include, but are not limited to:

Where clause condition is always true or empty

Use LIKE

Use the unequal operator (,! =)

Query columns with is null

Using or on non-indexed columns

When querying with multiple conditions, please put simple query conditions or index column queries in front.

Please try to specify the columns that need to be queried, do not be lazy to use select *

If not specified, on the one hand, excess data will be returned, bandwidth will be occupied, etc.

On the other hand, when MySQL executes a query, when there are no fields, it will first query which fields are in the table structure.

Uppercase query keywords are a little faster than lowercase.

Using subqueries creates temporary tables, which are slightly slower than JOIN and UNION

Try not to use database functions when querying on index fields, which is not convenient to cache query results.

When only one row of data is needed, please use LIMIT 1. If there is too much data, please set LIMIT appropriately and query by page.

Do not ORDER BY RAND (), the performance is extremely low

Add cach

Using caches such as redis, as well as local file caching, can greatly reduce the number of database queries. Cache this thing, must analyze the data characteristics of their own system, appropriate choice.

For some commonly used data, such as configuration information, you can put it in the cache

The table structure of the database can be cached locally

The cached data must be updated in time, and the validity period must be set.

Increasing the cache must increase the complexity of the system, and you must pay attention to the tradeoff.

At this point, the study on "what is the most basic operation of MySQL optimization" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.