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

This article introduces the relevant knowledge of "what are the optimizations of mysql". In the operation process of actual cases, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

1. Select the field attributes that best apply

In general, the smaller a database table, the faster queries execute on it. Therefore, we can design the table width as small as possible.

For fields, we try to sum values NOT NULL so that the database doesn't compare NULL values when querying.

For some text fields, you can use ENUM type to restrict the numeric value under the field.

2. Use JOIN instead of subquery

Anyone who knows mysql knows that subqueries can perform many SQL operations that logically require multiple complex steps at once, but such queries greatly slow down the query rate.

If you use JOIN to do this, it will be much faster.

JOIN is efficient. MYSQL does not need to create temporary tables in memory to complete this logical two-step query.

UNION replaces manually created temporary tables

mysql supports federated queries since version 4.0.

It can combine two or more select queries that require the use of temporary tables into a single query, concluding the query session on the client side to ensure that the database is clean and efficient.

4. Business

Although subqueries,JOIN and UNION are used to create various queries. But not all operations can be completed with one or more SQL statements

To prevent database accidents. To use transactions, we recommend using the InnoDB engine.

Its role is to maintain database data consistency and integrity.

Another function is to provide a secure way for cub to access the database when multiple users are using the same data source simultaneously

5. lock table

Transactions are good, but because of their exclusivity, they sometimes affect database performance. Assuming thousands of users access a database at the same time, there will be severe response delays

Locking tables, then, can play a role in maintaining data integrity for better performance.

6. Use foreign keys

Locking tables also has its drawbacks, that is, it does not guarantee the relevance of data. At this point, we can use foreign keys.

Note that the engine uses innodb instead of myisam type

7. use the index

Indexing is a common way to improve data performance. It allows the database server to retrieve specific rows much faster than without indexing, especially when the MAX(),MIN(), and ORDER BY commands are included in the query statement.

In general, indexes should be built on fields that are used for JOIN ,WHERE, and ORDER BY sorting, and as far as possible do not index a field in the database that contains a large number of duplicate values.

8. Optimized query statements

In most cases, indexes speed queries, but they don't work if SQL statements aren't used properly.

Then we should pay attention to the following aspects:

1. It is best to compare between fields of the same type

2. Try not to use functions on fields that are indexed.

3. When searching character fields, we sometimes use the LIKE keyword and wildcards, which is simple but at the expense of system performance.

Finally, care should be taken to avoid automatic type conversion of MYSQL in queries, as the conversion process will also render the index useless!

"What are the optimizations of mysql?" The content is introduced here. Thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

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.