MySQL Optimization summary

1. Speaking of MySQL, we immediately think of its small size, speed, but also open source features, so it is widely used. Today we summarize the most frequent two operations in MySQL: Inserts and queries, and the optimization method.

2. Insert:

3. One, Text import

4. Downloading data from text using the load infile will be 20 times times faster than using the INSERT statement.

5. Example:

6.load data local infile ' c:/users/dell/desktop/description&data/news1.txt ' into table News (file location)

7.fields terminated by ', ' (each field is separated by ', ')

8.lines terminated by ' RN ' (each group of data is separated by line feed)

9. (content,date,ip,author,subject); (explains that each row in TXT is made up of these fields)

10. 二、一次 insert more than one

11. Inserting a few rows at a time using an INSERT statement with multiple values lists is several times faster than using a single line INSERT statement.

12. Example:

13.INSERT into food VALUES

(NULL, ' ee jelly ', ' ee Jelly Factory ', 1.5, ' 2007 ', 2, ' Beijing '),

(NULL, ' FF coffee ', ' ff Coffee Factory ', 20, ' 2002 ', 5, ' Tianjin '),

(NULL, ' GG Candy ', ' GG Candy ', 14, ' 2003 ', 3, ' Guangdong '); Instead of:

1.INSERT into food VALUES (NULL, ' ee jelly ', ' ee

Jelly Factory ', 1.5, ' 2007 ', 2, ' Beijing ');

2.INSERT into food VALUES (NULL, ' FF coffee ', ' ff

Coffee Factory ', 20, ' 2002 ', 5, ' Tianjin ');

3.INSERT into food VALUES (NULL, ' GG Toffee ', ' GG

Toffee ', 14, ' 2003 ', 3, ' Guangdong ');

4. The first approach reduces the connection to the database and so on, so it is faster than the second way.

Iii. Use of varchar

VARCHAR is used instead of char because the varchar is stored at the length of the actual data, and char automatically complements the blank space after the actual data has been stored. So obviously char inserts are slower than varchar. Experiments show that the efficiency of char is significantly lower than that of varchar, regardless of whether the columns involved in the insertion data are indexed.

Four, control the length of the field

This is obvious, the smaller data types occupy less space, from disk read or packaged to memory efficiency is better, but do not too persistent to reduce data types, if the future application of what changes will have no space, so to consider the decision field length.

Five, Non-null + default value

Null for most databases require special processing, MySQL is no exception, it requires more code, more checks and special indexing logic, so we'd better set the attribute as Non-empty, and give it a default value, such as 0.

Vi. Disabling transactions

There are two types of MySQL database tables, one of which is to support transactions, and one that does not support transaction processing. MySQL uses different types of database engines when working on both tables, so the database engine is more efficient at insertion, which is theoretically slower when transactional functionality is enabled.

Example:

Create Table (

..... /* Field Description * *

) Engine=innodb/myisam (with transaction, without transaction)

It turns out that disabling transactions has a significant effect on the speed at which data is inserted.

But let's say it's not just that our inserts are slowing down. With a transaction, it is inevitable to sacrifice part of the speed, but to a large extent to ensure the correctness of the data.

Seven, disable indexing

When you insert a record, MySQL sorts the inserted records according to the table's index. If you insert large amounts of data, these sorts reduce the speed at which records are inserted. To resolve this situation, disable the index before inserting the record. Wait until the record is inserted and then open the index. (although it may be a bit controversial to insert data first or index first)

Example:

1.ALTER table name DISABLE KEYS; (Disable index)

2.

1.ALTER table name ENABLE KEYS; (Indexing enabled)

1.

1.

Viii. Disabling uniqueness Checking

We know that MySQL makes the unique checksum of inserted records when inserting data. This checksum also lowers the speed at which records are inserted. You can disable the uniqueness check before inserting a record. Wait until the record is inserted and then open. The statement that disables the uniqueness check is as follows:

1.SET unique_checks=0;

2. The statement to reopen the uniqueness check is as follows:

1.SET Unique_checks=1;

First, lock the table and insert

This improves database performance because the index buffer only refreshes the disk once after all the INSERT statements have completed. In general, how many insert statements will have a number of secondary index buffer flushes. If you can insert all rows with an INSERT statement, you do not need to use an explicit locking statement.

Example:

LOCK TABLES; (lock table)

Ten, enable parallel insertion

The MyISAM table can be inserted in parallel Concurrent_insert system variables can be set to modify Concurrent-insert processing. The default setting for this variable is 1. If the Concurrent_insert is set to 0, parallel inserts are disabled. If the variable is set to 2, it can be inserted in parallel at the end of the table, even if some of the rows of the table have been deleted.

Xi. Delayed insertion

This is useful if your client cannot or does not have to wait for the insertion to complete. This is common when you use MySQL to store and run regular select and UPDATE statements that take a long time to complete. When the customer uses the Insert delay, the server returns immediately and the row queue waits to be inserted if the table is not called by another thread. Another benefit of using insert latency is that situations that are inserted from multiple customers are bound and recorded in the same block. This will be much faster than handling multiple standalone inserts.

Inquire:

I. Optimizing Data types

In query optimization, the data type is the basis of the query, so we first have to optimize our data type. In fact, the data type aspect, the query needs the optimization and the insertion is similar, mainly is avoids the null and uses the small field as far as possible.

Second, use the connection query

Using connection queries is generally more efficient than subqueries. When a subquery is encountered, the MySQL query optimization engine is not always the most effective, which is why the subquery is often converted to a connection query, and the optimizer has been able to handle the connection query correctly, and of course it is important to ensure that the connection column (second table) of the connection table is indexed, On the first table MySQL usually scans the entire table relative to the query subset of the second table, which is part of the nested loop algorithm.

Third, index

An index is a structure that sorts the values of one or more columns in a database table, using indexes to quickly access specific information in a database table.

Indexes speed up the linking of tables to tables, which can greatly speed up the retrieval of data.

But indexes can incur additional overhead, so we typically index on the columns that are often searched for columns that often need to be connected.

Set the ID property 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 (recommended to use unsigned) and set up an automatically added Auto_increment flag.

Because in the MySQL data engine, many operations require a primary key, so the performance and settings of the dead primary key become very important, such as cluster, partition ...

Five, Explain

Use the EXPLAIN keyword to let you know how MySQL handles your SQL statements. This can help you analyze your query statements or the performance bottlenecks of the table structure. EXPLAIN's query results will also tell you how your index keys are being used, how your data tables are searched and sorted ... Wait, wait.

Vi. Avoiding SELECT *

The more data you read from the database, the slower the query will become. And, if your database server and Web server are two separate servers, this will also increase the load on the network transport.

So, you should develop a good habit of taking whatever you need.

Vii. Adjustment of internal variables

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

Change index buffer Length (key_buffer):

In general, the variable controls the length of the buffer used when processing an index table (read/write operation). The MySQL manual indicates that the variable can be increased to ensure optimal performance of the index table, and it is recommended to use the size of system memory 25% as the value of the variable. This is one of the most important configuration variables for MySQL, and if you are interested in optimizing and improving system performance, you can start by changing the value of the key_buffer_size variable.

Change table Length (read_buffer_size):

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

Set the maximum number of open tables (Table_cache):

This variable controls the maximum number of tables that MySQL opens at any time, thereby controlling the server's ability to respond to input requests. It is closely related to the max_connections variable, and increasing the Table_cache value allows MySQL to open more tables, such as increasing the max_connections value to increase the number of connections. When you receive a large number of requests for different databases and tables, consider changing the size of this value.

Set a time limit (long_query_time) for slow-length queries:

MySQL has a "slow query log" that automatically records all of the queries that have not yet ended within a specific time span. This log is useful for tracking inefficient or misbehaving queries and looking for optimized objects. Long_query_time variable controls This maximum time limit, in seconds.

Viii. Use of Limit 1

We should use LIMIT 1 when we want a row of data, so that the MySQL database engine stops searching after finding a piece of data, rather than continuing to look back at the next record-keeping data.

This is the end of the article, but the author's understanding of MySQL optimization is only glimpse, and more optimization strategies need everyone to explore to practice OH.