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

This article is about the development of those commonly used MySQL optimization, the editor thinks it is very practical, so share it with you to learn, I hope you can get something after reading this article, say no more, follow the editor to have a look.

1. Optimization of mass insertion data

(1) for MyISAM storage engine tables, you can use: DISABLE KEYS and ENABLE KEYS to turn on or off updates to non-unique indexes of the MyISAM table.

ALTER TABLE tbl_name DISABLE KEYS; loading the data ALTER TABLE tbl_name ENABLE KEYS

(2) for InnoDB engine, there are several optimization measures:

The data imported by ① is saved in primary key order: this is because the InnoDB engine says that it is saved in primary key order, so it will save a lot of time if you can sort the inserted data in advance.

For example, the bulk_insert.txt file is stored in the order of the table user primary key, and the import time is 15.23 seconds.

Mysql > load data infile 'mysql/bulk_insert.txt' into table user; Query OK, 126732 rows affected (15.23 sec) Records: 126732 Deleted: 0 Skipped: 0 Warnings: 0

If it is not sorted by primary key, the time is 26.54 seconds.

Mysql > load data infile 'mysql/bulk_insert.txt' into table user; Query OK, 126732 rows affected (26.54 sec) Records: 126732 Deleted: 0 Skipped: 0 Warnings: 0

② executes SET UNIQUE_CHECKS=0 before importing data, turns off uniqueness check, and then opens it to 1: verification takes time and needs to be considered in the case of a large amount of data.

Set SET AUTOCOMMIT=0 before ③ import, disable autocommit, and then set it to 1 after import: this is because autocommit consumes part of the time and resources, which is not very expensive, but it still has to be considered in the case of a large amount of data.

2. Optimization of INSERT

(1) use INSERT statements of multiple value tables as much as possible, which will greatly reduce the consumption of connection and shutdown between the client and the database. (in the case of the same customer), that is:

INSERT INTO tablename values (1d2), (1d3), (1d4)

Lab: insert 8 pieces of data into a user table (using the navicat client tool)

Insert into user values (uuid (),'-','); insert into user values (2); insert into user values (3) (uuid (),'-,')); insert into user values (4) Insert into user values (uuid (),'-','); insert into user values (6); insert into user values (uuid (),'-',''); insert into user values (8)

Get feedback:

[SQL] insert into user values (1 uuid (),'-',''); affected lines: 1 time: 0.033 s [SQL] insert into user values (2 uuid (),'-','')); affected lines: 1 time: 0.034 s [SQL] insert into user values (3 pas (uuid (),'-,'')) Affected lines: 1 time: 0.056s [SQL] insert into user values (4 uuid (),'-',''); affected lines: 1 time: 0.008s [SQL] insert into user values (5 uuid (),'-','') Affected lines: 1 time: 0.008s [SQL] insert into user values (6 uuid (),'-',''); affected lines: 1 time: 0.024s [SQL] insert into user values (7 uuid (),'-',') Affected lines: 1 time: 0.004s [SQL] insert into user values (8 uuid (),'-',''); affected lines: 1 time: 0.004s

The total time is 0.171 seconds, followed by a multi-valued table:

Insert into user values (uuid (),'-',')), (1414) Replace (uuid (),'-','')), (15) uuid (),'-','')), (16) uuid ()

Get feedback:

[SQL] insert into user values (uuid (),'-',')), (10) (uuid (),'-',')), (11) (uuid (),'-',')), (12) (uuid (),'-',')), (13) (uuid (),'-',')), (14) Replace (uuid (),'-','')), (15) uuid (),'-','')), (16) uuid () Affected lines: 8 time: 0.038s

The time you get is 0.038, which obviously saves time to optimize SQL.

(2) if you insert many rows in different clients, you can use the INSERT DELAYED statement to get a higher speed. DELLAYED means that the INSERT statement is executed immediately, but in fact, the data is placed in the queue in memory. It's not really written to disk. LOW_PRIORITY is just the opposite.

(3) separate index files and data files on different disks (InnoDB engine is in the same tablespace).

(4) if you insert in bulk, you can increase the speed of providing bluk_insert_buffer_size variable values (useful for MyISAM only)

(5) when loading a table from a text file, using LOAD DATA INFILE is usually 20 times faster than the INSERT statement.

3. Optimization of GROUP BY

By default, the GROUP BY statement in MySQL sorts subsequent fields by default (in the case of a non-primary key), just as we use ORDER BY col1,col2,col3. So we have the same column followed by the col1,col2,col3 that appears after the GROUP BY. The ORDER BY clause does not affect the actual execution performance of the SQL.

Then there will be a situation where we can use ORDER BY NULL to prohibit sorting for optimization purposes when we don't care whether the query results have been sorted or not. Let's use the EXPLAIN command to analyze SQL. The official account of Java bosom friend replied to "aggregation of interview questions" and sent you a treasure book of interview questions.

When executing select id and sum (money) form user_1 group by name in user_1, it will be sorted by default (note that the column after group by is non-index will reflect the sorting of group by. If it is primary key, then as mentioned earlier, InnoDB is sorted by default according to the primary key index)

Mysql > select*from user_1 +-+ | id | name | money | +-+ | 1 | Zhangsan | 32 | 2 | Lisi | 65 | 3 | Wangwu | 44 | | 4 | Lijian | 100 | +-+ 4 rows in set

Sorting is not prohibited, that is, when ORDER BY NULL is not used: there is an obvious Using filesort.

Using filesort does not exist when sorting is disabled using ORDER BY NULL

4. Optimization of ORDER BY.

MySQL can use an index to satisfy the sorting of ORDER BY clauses without the need for additional sorting, but the following conditions need to be met:

(1) WHERE condition and OREDR BY use the same index: that is, key_part1 and key_part2 are composite indexes, and key_part1 in composite index is used in where.

SELECT*FROM user WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC

(2) and the ORDER BY order is the same as the index order:

SELECT*FROM user ORDER BY key_part1, key_part2

(3) and all are either ascending or descending:

SELECT*FROM user ORDER BY key_part1 DESC, key_part2 DESC

However, indexes are not used in the following situations:

(1) mix ASC and DESC in ORDER BY:

SELECT*FROM user ORDER BY key_part1 DESC, key_part2 ASC

(2) the keywords of the query row are different from those used by ORDER BY, that is, the fields after WHERE are different from those after ORDER BY.

SELECT*FROM user WHERE key2 = 'xxx' ORDER BY key1

(3) ORDER BY uses different keywords, that is, the keywords after ORDER BY are different.

SELECT*FROM user ORDER BY key1, key2

5. Optimization of OR.

When MySQL uses OR queries, if indexes are to be used, each conditional column must have an independent index instead of a composite index (multi-column index) to ensure that the index is used when the query is used.

For example, let's create a new user information table user_info.

Mysql > select*from user_info +-+ | user_id | idcard | name | address | +-+ | 1 | 111111 | Zhangsan | Kunming | | 2 | 222222 | Lisi | Beijing | | 3 | 333333 | Wangwu | Shanghai | | 4 | 444444 | Lijian | Guangzhou | +-+ 4 rows in set

After that, three indexes are created: ind_name_id (user_id, name) composite index, id_index (id_index) independent index and idcard primary key index.

Mysql > show index from user_info + -- + | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +- -+- -+ | user_info | 0 | PRIMARY | 1 | idcard | A | 4 | NULL | NULL | BTREE | user_info | 1 | ind_name_id | 1 | user_id | A | 4 | NULL | | NULL | BTREE | user_info | 1 | ind_name_id | 2 | name | A | 4 | NULL | NULL | YES | BTREE | user_info | 1 | id_index | 1 | user_id | | | A | 4 | NULL | NULL | | BTREE | +-- | -- +-+ 4 rows in set

Test 1: OR joins two fields with separate indexes before the entire SQL query uses the index (index_merge), and we know that OR actually UNION each result at the end.

Mysql > explain select*from user_info where user_id=1 or idcard='222222' +-+- -- +-- + | id | select_type | table | partitions | type | possible_keys | key | Key_len | ref | rows | filtered | Extra | +- -+-+ | 1 | SIMPLE | | user_info | NULL | index_merge | PRIMARY | Ind_name_id,id_index | ind_name_id,PRIMARY | 4Jing 62 | NULL | 2 | 100 | Using sort_union (ind_name_id,PRIMARY) Using where | + -+-+ 1 row in set

Test 2: OR uses the field name of composite index, and address without index, the whole SQL is scanned by ALL.

Mysql > explain select*from user_info where name='Zhangsan' or address='Beijing' +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + -+ | 1 | SIMPLE | user_info | NULL | ALL | NULL | 4 | 43.75 | Using where | +- -+ 1 row in set

Columns that swap OR locations and use another composite index are also ALL full table scans:

Mysql > explain select*from user_info where address='Beijing' or user_id=1 + -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | SIMPLE | user_info | NULL | ALL | ind_name_id Id_index | NULL | 4 | 43.75 | Using where | + +-+ 1 row in set

6. Optimize nested queries

Using nested queries can sometimes be replaced by more efficient JOIN joins because MySQL does not need to create temporary tables in memory to complete both the select subquery and the main query. But it's not always true, and it's better to have an index in the column after the on keyword.

For example, in the table major, major_id has an index:

Select * from student u left join major m on u.major_id=m.major_id where m.major_id is null

When nesting a query, creating a temporary table in memory to complete the select subquery and the main query will consume to a certain extent.

Select * from student u where major_id not in (select major_id from major)

7. Use the SQL prompt

SQL hint (SQL HINT) is an important means to optimize the database, which is to add some artificial hints to the SQL statement to achieve the purpose of optimization. Here are some common SQL tips:

(1) USE INDEX: when you use USE INDEX, you want MySQL to refer to the index list, so that MySQL does not need to consider other available indexes, that is, the index values referenced under the possible_keys attribute.

Mysql > explain select* from user_info use index (id_index,ind_name_id) where user_id > 0 + -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | SIMPLE | user_info | NULL | ALL | ind_name_id Id_index | NULL | 4 | 100 | Using where | +-- +-- + -+ 1 row in set mysql > explain select* from user_info use index (id_index) where user_id > 0 +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + -+ | 1 | SIMPLE | user_info | NULL | ALL | id_index | NULL | 4 | 100 | Using where | + -+ 1 row in set

(2) IGNORE INDEX ignores index

We use user_id to determine that indexes can be ignored when no other indexes are needed. That is, in contrast to USE INDEX, unwanted indexes are subtracted from possible_keys, but are rarely used in the real world.

Mysql > explain select* from user_info ignore index (primary,ind_name_id,id_index) where user_id > 0 +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + -+ | 1 | SIMPLE | user_info | NULL | ALL | NULL | 4 | 33.33 | Using where | +- -+ 1 row in set

(3) FORCE INDEX mandatory indexing

For example, where user_id > 0, but user_id is greater than 0 in the table, ALL full table search will be carried out naturally, but although the efficiency of using FORCE INDEX is not the highest (determined by the where user_id > 0 condition), MySQL still uses the index.

Mysql > explain select* from user_info where user_id > 0 + -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | SIMPLE | user_info | NULL | ALL | ind_name_id Id_index | NULL | 4 | 100 | Using where | +-- +-- + -+ 1 row in set

Then force the use of the independent index id_index (user_id):

Mysql > explain select* from user_info force index (id_index) where user_id > 0 +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | + -+-+-- + | 1 | SIMPLE | user_info | NULL | range | id_index | id_index | 4 | NULL | 4 | 100 | Using index condition | + -+-+ 1 row in set

In many cases, the performance of the database is caused by inappropriate SQL statements (that is, inefficiency, which may lead to table locking, etc.). Some optimizations are not needed in real development, but they need to be analyzed one by one when there is a problem with performance degradation.

These are the commonly used MySQL optimizations in development, and the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.