Mysql optimization Summary (insert and query)

Speaking of mysql, we immediately think of its small size, fast speed, and open source features, so it is widely used. Today, we will summarize the two most frequent mysql operations: insert and query, and the optimization methods.

Insert:

I. Text Import

Using load data infile to download DATA from text is 20 times faster than using the insert statement.

Example:

Load data local infile 'C:/Users/DELL/Desktop/Description & Data/news1.txt 'into table news (file location)
Fields terminated by ',' (each field is separated)
Lines terminated by '\ r \ n' (each group of data is separated by linefeeds)
(Content, date, ip, author, subject); (each line in txt is composed of these fields)

2. Insert multiple entries at a time

Using an INSERT statement with multiple VALUES lists inserts several rows at a time several times faster than using a single row INSERT statement.

Example:

1. Insert into food VALUES
2. (NULL, 'ee jelly ', 'ee jelly factory', 1.5, '000000', 2, 'beijing '),
3. (NULL, 'ff coffee ', 'ff coffee factory', 20, '000000', 5, 'tianjin '),
4. (NULL, 'gg toffee ', 'gg toffee', 14, '000000', 3, 'guangdong ');

Instead:

1. Insert into food VALUES (NULL, 'ee jelly ', 'ee
   Jelly factory ', 1.5, '000000', 2, 'beijing ');
2. Insert into food VALUES (NULL, 'ff coffee ', 'ff
   Coffee factory ', 20, '000000', 5, 'tianjin ');
3. Insert into food VALUES (NULL, 'gg toffee ', 'gg
   Toffee ', 14, '000000', 3, 'guangdong ');
4. 
   

The first method reduces the connection to the database and other operations, so the speed is faster than the second method.

Iii. Use varchar

Varchar is used instead of char because varchar is stored based on the actual data length. char automatically completes the blank space after storing the actual data. Obviously, char insertion is slower than varchar. The experiment shows that the efficiency of char is significantly lower than that of varchar regardless of whether the columns involved in data insertion are indexed.

4. Control the Field Length

Obviously, the smaller the data type, the smaller the space occupied, the better the efficiency from disk reading or packaging to memory, but do not persistently reduce the data type, if the application changes in the future, there will be no space, so we need to consider the length of the field.

5. Non-empty + Default Value

NULL requires special processing for most databases, and MySQL is no exception. It requires more code, more checks, and special index logic, therefore, we 'd better set the attribute to a non-null value and assign it a default value, such as 0.

6. Disable transactions

There are two types of MySQL database tables: transaction processing and transaction processing. MySQL uses different types of database engines to process these two types of tables. Therefore, the database engine has different insert validity rates. Theoretically, it is slower to enable the transaction function.

Example:

Create Table (

..... /* Field Description */

) ENGINE = InnoDB/MyISAM (with and without transactions)

It turns out that disabling transactions has a significant impact on the speed of data insertion.

But let's say that the transaction will not only slow down our insertion. When a transaction is used, it is inevitable to sacrifice a part of the speed, but data correctness can be ensured to a great extent.

7. Disable Indexes

When a record is inserted, MySQL sorts the inserted records based on the table index. If a large amount of data is inserted, the sorting will reduce the insert record speed. To solve this problem, disable indexes before inserting records. Enable the index after all records are inserted. (Although it may be a bit controversial whether to insert data first or create an index first)

Example:

1. Alter table name disable keys; (DISABLE indexes)
2. 1. Alter table name enable keys; (ENABLE index)
   
   
   1. 1. 
         

8. Disable uniqueness check

We know that MySQL will verify the uniqueness of the inserted records when inserting data. This validation also reduces the insert record speed. You can disable the uniqueness check before inserting a record. Enable this function after the record is inserted. The statement for disabling the uniqueness check is as follows:

1. SET UNIQUE_CHECKS = 0;
2. The statement to re-enable the uniqueness check is as follows:

1. SET UNIQUE_CHECKS = 1;

9. Lock the table before inserting

This will improve the database performance, because the index buffer only refresh the disk after all the insert statements are completed. Under normal circumstances, the index buffer refresh will occur as many insert statements as possible. If you can use an insert statement to insert all rows, you do not need to use an explicit lock statement.

Example:

Lock tables; (LOCK table)

10. Enable parallel insertion

You can insert the Concurrent_insert system variable to the myisam table in parallel. It can be set to modify the concurrent-insert Process. This variable is set to 1 by default. If concurrent_insert is set to 0, parallel insert is disabled. If this variable is set to 2, it can be inserted at the end of the table in parallel, even if some rows of the table have been deleted.

11. Delayed insertion

This is useful if your customers cannot or do not have to wait until the insertion is complete. When you use MySQL to store and regularly run SELECT and UPDATE statements that take a long time to complete, you will find this situation very common. When the customer uses the insert delay, the server immediately returns the result. If the table is not called by other threads, the row will wait for the table to be inserted. Another advantage of using insert latency is that insertion from multiple customers is bound and recorded in the same block. This is much faster than processing multiple independent inserts.

Query:

I. Optimize Data Types

In terms of query optimization, data types are the foundation of query, so we must first optimize our data types. In fact, in terms of data types, the optimization and insertion required for queries are similar, mainly to avoid null and try to use small fields.

2. Use connection Query

Connection query is generally more efficient than subquery. In the case of subqueries, the MySQL Query Optimization engine is not always the most effective, which is why subqueries are frequently converted to connection queries, the optimizer can correctly process connection queries. Of course, you must note that the connection columns of the connected table (the second table) are indexed, in the first table, MySQL usually performs a full table scan relative to the query subset of the second table, which is part of the nested loop algorithm.

Iii. Index

An index is a structure that sorts the values of one or more columns in a database table. You can use an index to quickly access specific information in a database table.

The index can accelerate the link between the table and the table, and greatly accelerate the data retrieval speed.

However, indexing brings additional overhead. Therefore, we usually create an index for columns that are frequently searched and columns that frequently need to be connected.

4. Set the id attribute for the table

We should set an ID for each table in the database as its primary key, and the best is an INT type (UNSIGNED is recommended), and set the AUTO_INCREMENT flag automatically added.

Because mysql's Data Engine requires a primary key for many operations, the performance and settings of the dead primary key become very important, such as clusters, partitions ......

V. Explain

The EXPLAIN keyword helps you know how MySQL processes your SQL statements. This helps you analyze the performance bottleneck of your query statement or table structure. The EXPLAIN query results also show you how your index primary key is used and how your data tables are searched and sorted ...... And so on.

6. Avoid select *

The more data you read from the database, the slower the query. In addition, if your database server and WEB server are two independent servers, this will increase the network transmission load.
Therefore, you should develop a good habit of taking what you need.

7. Adjust internal variables

MySQL is so open that you can easily adjust its default settings to achieve better performance and stability. Key variables to be optimized are as follows:

Change the index buffer length (key_buffer ):

Generally, this variable controls the buffer length to be used when processing index tables (read/write operations. MySQL user manual points out that this variable can be continuously increased to ensure the optimal performance of the index table, and it is recommended to use 25% of the size of the system memory as the value of this variable. This is one of the most important configuration variables of MySQL. If you are interested in Optimizing and improving the system, you can change the value of the key_buffer_size variable.

Change the table length (read_buffer_size ):

When a query continuously scans a table, MySQL allocates a memory buffer for it. The read_buffer_size variable controls the size of the buffer. If you think continuous scanning is too slow, you can increase the performance by increasing the variable value and memory buffer size.

Set the maximum number of opened tables (table_cache ):

This variable controls the maximum number of tables opened by MySQL at any time, thereby controlling the server's ability to respond to input requests. It is closely related to the max_connections variable. Increasing the table_cache value enables MySQL to open more tables, just like increasing the max_connections value to increase the number of connections. When receiving a large number of requests from different databases and tables, consider changing the size of this value.

Set a time limit for slow query (long_query_time ):

MySQL has a "Slow query log", which automatically records all queries that have not been completed within a specific time range. This log is useful for tracking inefficient or misperforming queries and searching for optimization objects. The long_query_time variable controls the maximum time limit, in seconds.

8. Use Limit 1

When we need a row of data, we should use LIMIT 1. In this way, the MySQL database engine will stop searching after finding a row of data, instead of continuing to query less data that matches the record.

This article is over, but the author's understanding of mysql optimization is just a glimpse of it. More optimization strategies need to be explored for practice.