Here is an introduction to the optimization method for the Insert, Group by, and other SQL statements:
1, large-scale data insertion
When the load command imports data, you can make the appropriate settings to increase the import speed.
1.1 For MyISAM tables, you can first disable non-unique index updates and then import the data to quickly import large amounts of data.
ALTER TABLE table_name disable keys;
Load date infile ' xxx.txt ' into table_name;
ALTER TABLE table_name enable keys;
1.2 For InnoDB tables:
1) because the InnoDB table must have a primary key, the table is saved in the order of the primary key, so the data to be imported is sorted by the primary key,
Can effectively improve the efficiency of importing data.
2) If there is a uniqueness constraint in the table, performing a close uniqueness check before importing the data can improve the efficiency of the import.
Set unique_checks=0;
Loading the data;
Set Unique_checks=1;
3) If your app uses autocommit, it is recommended that you execute set autocommit=0 before importing to turn off autocommit.
This saves I/O without having to write logs every time the insert goes to disk.
Set auto_commit=0;
Loading the data;
Set auto_commit=1;
2. Optimize INSERT statement
1) If you are inserting many rows from the same customer, you should try to use the INSERT statement for multiple value tables. (Save connection, close etc consumption)
INSERT into test values (1,3), (1,4);
2) You can use the Insert delayed statement to get a higher speed if you access multiple rows from a non-client. Delayed meaning is to let
The INSERT statement executes immediately, not until the other user reads and writes the table. Low_priority just the opposite.
3) The Data files and index files exist on separate disks, speeding up the access speed (can be specified when the table is built, or MY.CNF).
4) If the MyISAM table is bulk inserted, you can increase the value of the bulk_insert_buffer_size variable to speed up the method.
5) When loading a table from a text file, use load data infile, which is 20 times times faster than a large number of INSERT statements.
3. Optimize the ORDER BY statement
Two sorts of 3.1 MySQL
The first is an ordered index that returns ordered data directly by sequential scanning, which is displayed when the query is analyzed using explain
Using Index does not require additional sorting and is highly efficient, for example:
The second is by sorting the returned data, which is usually the filesort sort, and all sorts that do not return the sorted result directly by index
are called Filesort sort. Filesort does not represent a sort by disk file, but simply a sort operation, and the sort operation is
No The use of disk files or temporary tables, etc., depends on the MySQL server's setting of the sort parameter and the size of the data that needs to be sorted. For example:
As an example, the composite index is accessed and an action sort occurs on the index idx_storeid_email, so there is still a using filesort in the execution plan.
Filesort is to sort the obtained data in the memory sort area set by the Sort_buffer_size system variable by the corresponding sorting algorithm, if the memory
Loading , he will block the data on the disk, sort the individual blocks, and then merge the individual pieces into an ordered result set. Sort_buffer_size
The sort area that is set is exclusive for each thread, so there can be more than one sort buffer present.
MySQL Optimization goal: Minimize additional sorting and return ordered data directly through the index.
The Where condition and order by use the same index, and the order by is the same as the order of the indexes, and the order by field is ascending or
are descending. If not, you will definitely need an extra sort operation, so that Filesort appears.
In summary, the following SQL can use the index (the leftmost principle that conforms to the combined index):
However, you cannot use an index in the following situations:
Optimization of 3.2 filesort
By creating an appropriate index, you can reduce the appearance of filesort, but in some cases, you can't avoid it, you need to speed up filesort operations.
In MySQL, Filesort has two sorting algorithms:
- Two-scan algorithm: Sort fields and row pointer information are first removed according to criteria, and then sorted in the sort area. And then, after sorting through the
The row pointer information reads the data back into the table.
- One-time Scan algorithm: Once all the fields of the row that satisfy the condition are fetched, and then output the result set directly after sorting in sort buffer in the sorting area.
The memory overhead is larger when sorting, but the sorting efficiency is higher than the two scan algorithm.
4. Optimizing GROUP BY statements
By default, MySQL is for all group by Col1,col2, ... To sort the fields. With ORDER by col1,col2 ... Similarly, if
The inclusion of an ORDER BY clause with the same column after group by shows no effect on the actual execution performance.
If the query contains group by but the user wants to avoid the consumption of sort results, you can specify order by NULL to prohibit sorting, as in the following example:
5. Optimizing Nested Queries
It is easy to write a lot of SQL operations that logically require multiple steps to complete using subqueries. However, in some cases it is necessary to
Replace it with a more efficient connection (join).
Use connection (join) to replace subquery (in)
6. mysql optimization or condition
For query clauses with OR, the index must be used for each condition column between or if the index is to be used;
If there is no index, consider increasing the index.
For example: There are three indexes, Id,year, (Company_id,moneys); show index from table_name; View index
From the above, the index is correctly used and obtained from the execution plan, when MySQL processes queries that contain or words, it is actually the fields of the OR
The result of the query is the union operation.
However, indexes cannot be used when the or operation is done on the columns company_id and moneys of the combined index.
7. Optimize Paging Query
When you do a general paging query, you can better improve performance by creating an overlay index. A common and very annoying paging scene is "Limit 1000,20",
At this point MySQL sorted out the first 1020 records only need to return the 1001th to 1020th record, the first 1000 records are discarded, query and sorting is very expensive.
1) First optimization idea: (using sub-query to isolate index ID limit m,n after table query)
in the The index completes the sort paging operation, and finally retrieves the other column content needed for the original table query based on the primary Key association.
For example: processing inefficient statements:
After you overwrite SQL by index paging back to table mode:
2) Second optimization idea (change limit m,n to limit N, filter before M line to start query)
Explain select film_id, description from film where film_id >50 order by film_id ASC limit 5\g
Note that the query that converts the limit m,n to limit n is only suitable for specific environments where the sort field does not have duplicate values (unique keys or
Primary key) to reduce the stress of paging. This optimization is not appropriate if there are a large number of duplicate values in the Sort field.
[MySQL] optimized--18.4 for common SQL