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

This article mainly introduces "how to tune SQL". In daily operation, I believe many people have doubts about how to tune SQL. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "how to tune SQL"! Next, please follow the editor to study!

SQL normative check

Each company has its own MySQL development specification, basically the same, here is a list of some more important, I often come into contact with you during my work.

Select check

UDF user-defined function

The select of the SQL statement uses the custom function UDF,SQL to return how many rows, then the UDF function will be called many times, which has a great impact on performance.

# getOrderNo is a function defined by the user to obtain the order number select id, payment_id, order_sn, getOrderNo (order_sn) from payment_transaction where status = 1 and create_time between '2020-10-01 10 select id 00' and '2020-10-02 10 select id 00' according to order_sn.

Text type check

If a field of type text appears in select, it will consume a lot of network and IO bandwidth. Because the returned content is too large to exceed the max_allowed_packet setting, it will cause the program to report an error, so it needs to be used with caution.

# content in table request_log is of type text. Select user_id, content, status, url, type from request_log where user_id = 32121

Group_concat is used with caution

Gorup_concat is a string aggregation function that affects the response time of SQL. If the returned value exceeds the max_allowed_packet setting, it will cause the program to report an error.

Select batch_id, group_concat (name) from buffer_batch where status = 0 and create_time between '2020-10-01 10 name 0000' and '2020-10-02 10 name 00'

Inline subquery

The case where there is a subquery after select is called inline subquery. The number of rows returned by SQL is the number of times the subquery needs to be executed, which seriously affects the performance of SQL.

Select id, (select rule_name from member_rule limit 1) as rule_name, member_id, member_type, member_name, status from member_info m where status = 1 and create_time between '2020-09-02 10 as rule_name 00' and' 2020-10-01 10 purl 0000'

From check

How to link the table

Left Join is not recommended in MySQL. Even if ON filters conditional column indexes, some cases will not leave the index, resulting in a large number of data rows being scanned, SQL performance becoming very poor, and be aware of the difference between ON and Where.

SELECT a.membermemberidrecovera. Createkeeper timeline b.activetraintime FROM operation_log a LEFT JOIN member_info b ON a.member_id = b.member_id where b.`status` = 1 and a.create_time between '2020-10-01 00Vera 0000and' 2020-10-30 00VRV 000000100,0

Subquery

Because CBO, MySQL's cost-based optimizer, is weak in handling subqueries, it is not recommended to use subqueries, but can be rewritten as Inner Join.

Select b.memberaccounidredevicetrainmodel from member_operation_log an inner join (select member_id,member_type from member_base_info where `status` = 1 and create_time between '2020-10-01 00 and' 2020-10-30 00 and') as b on a.member_id = b.member_id

Where check

Index columns are computed

When a field is indexed and the where condition appears at the same time, no operation can be performed, resulting in the invalidation of the index.

# device_no column has an index. Due to the use of the ltrim function, the index fails select id, name, phone, address, device_no from users where ltrim (device_no) = 'Hfs1212121'; # balance column has an index, and the operation causes the index to fail select account_no, balance from accounts where balance + 100 = 10000 and status = 1

Type conversion

For fields of type Int, the value of type varchar can be indexed, and implicit type conversion is automatically done within MySQL; on the contrary, it is not possible for fields of type varchar to pass Int values, so the corresponding values passed by the corresponding field types should always be correct.

# user_id is a bigint type. An implicit type conversion occurs when passing a varchar value, so you can use the index. Select id, name, phone, address, device_no from users where user_id = '23126; # card_no is varchar (20). The int value passed in cannot be indexed select id, name, phone, address, device_no from users where card_no = 2312612121

Column character set

From MySQL 5.6it is recommended that all object character sets should use utf8mb4, including MySQL instance character set, database character set, table character set, column character set. Avoid index invalidation caused by mismatch of field character set when associating query Join, and currently only utf8mb4 supports emoji facial expression storage.

Character_set_server = utf8mb4 # database instance character set character_set_connection = utf8mb4 # connection character set character_set_database = utf8mb4 # database character set character_set_results = utf8mb4 # result set character set

Group by check

Prefix index

The column after group by has an index, which can eliminate the CPU overhead caused by sorting. If it is a prefix index, it cannot eliminate sorting.

# device_no field type varchar, which creates a prefix index. Mysql > alter table users add index idx_device_no (device_no (64)); mysql > select device_no, count (*) from users where create_time between '2020-10-01 00 and' 2020-10-30 00 group by device_no

Function operation

If you need to count the number of new users per day in a month, refer to the following SQL statement. Although you can follow the index of create_time, you can not eliminate sorting. You can consider redundancy of a field stats_date date type to solve this problem.

Select DATE_FORMAT (create_time,'% Ymuri% mmurf% d'), count (*) from users where create_time between '2020-09-01 0000count and' 2020-09-30 23mov 59moe 59' group by DATE_FORMAT (create_time,'% Ymuri% MMI% d')

Order by check

Prefix index

The column after order by has an index, which can eliminate the CPU overhead caused by sorting. If it is a prefix index, it cannot eliminate sorting.

Field order

Sort field order, asc/desc rise and fall should be consistent with the index, make full use of the order of the index to eliminate the CPU overhead caused by sorting.

Limit check

Limit mjinn should be careful.

For limit m, n paging query, the more the page is turned back, the longer the SQL will take. For this case, the primary key id should be taken out first, and then the Join association query with the original table should be done through the primary key id.

Table structure check

Table & column name keyword

In the database design modeling stage, the table name and field name should be set reasonably, and the keywords of MySQL, such as desc, order, status, group and so on, should not be used. It is also recommended to set lower_case_table_names = 1 table name to be case-insensitive.

Table storage engine

For OLTP business systems, it is recommended to use the InnoDB engine for better performance, which can be controlled by the parameter default_storage_engine.

AUTO_INCREMENT attribute

When building the table, the primary key id has an AUTO_INCREMENT attribute, and AUTO_INCREMENT=1, inside InnoDB, is counted by a system global variable dict_sys.row_id. Row_id is an 8-byte bigint unsigned,InnoDB that only reserves 6 bytes for row_id at design time, so the value of row_id ranges from 0 to 2 ^ 48-1. If the value of id reaches the maximum, the next value will continue to increase cyclically from 0. The insertion of the specified primary key id value is prohibited in the code.

# the newly inserted id value will start at 10001, which is wrong and should start at 1. Create table booking (`id` bigint (20) NOT NULL AUTO_INCREMENT COMMENT 'primary key id',.) Engine = InnoDB auto_increment = 10000; # specifies id value insertion, from which subsequent increments will start with + 1, and the index forbids specified id value insertion. Insert into booking (id, book_sn) values (1234551121, 'N12121')

NOT NULL attribute

According to the business meaning, try to add the NOT NULL DEFAULT VALUE attribute to all the fields. If the column value stores a large amount of NULL, it will affect the stability of the index.

DEFAULT attribute

When creating a table, it is recommended that each field have a default value as far as possible, disable DEFAULT NULL, and fill in the default value of the response for the field type.

COMMENT attribute

The remarks of the field should be able to clarify the role of the field, especially some fields that represent the status, and explicitly write out all the possible state values of the field and the meaning of the value.

TEXT Typ

It is not recommended to use Text data type, on the one hand, the transmission of a large number of data packets may exceed the max_allowed_packet setting, and on the other hand, the DML operation on the table will become very slow, so it is recommended to use es or object storage OSS to store and retrieve.

Index check

Index attribute

The index cardinality refers to the number of unique values of the indexed columns. The more unique values are close to the count (*) of the table, the higher the index selection rate, the fewer rows scanned by the index, and the higher the performance. For example, the selection rate of the primary key id is 100%. In MySQL, as far as possible, all update are updated with the primary key id, because the id is a clustered index that stores the whole row of data and does not need to return to the table, so the performance is the highest.

Mysql > select count (*) from member_info; +-+ | count (*) | +-+ | 148416 | +-+ 1 row in set (0.35sec) mysql > show index from member_base_info +- -- + | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index _ type | Comment | Index_comment | +-- -+ | member_info | 0 | PRIMARY | 1 | id | A | | 131088 | NULL | NULL | | BTREE | member_info | 0 | uk_member_id | 1 | member_id | A | 131824 | NULL | NULL | | BTREE | member_info | 1 | | Idx_create_time | 1 | create_time | A | 6770 | NULL | NULL | | BTREE | + -+- -+ # Table: table name # Non_unique: whether it is unique index 0-Yes, 1-No. # Key_name: index name # Seq_in_index: the sequence number in the index, single-column index-all 1; composite index-increments from 1 according to the order of index columns. # Column_name: column name of the index # Collation: sort order. If asc/desc is not specified, the default is ascending ASC. # Cardinality: index cardinality-the number of unique values in the index column. # sub_part: the length of the prefix index; for example, index (member_name (10), length is 10. # Packed: how the index is organized. The default is NULL. # Null:YES: the index column contains null values;'': the index does not contain null values. # Index_type: default is BTREE, and other values are FULLTEXT,HASH,RTREE. # Comment: there is no information described in the index column, such as the index is disabled. # Index_comment: comments when creating an index.

Prefix index

For the variable length string type varchar (m), to reduce key_len, you can consider creating a prefix index, but the prefix index does not eliminate the sorting overhead caused by group by and order by. If the actual maximum value of the field is much smaller than m, it is recommended to reduce the length of the field.

Alter table member_info add index idx_member_name_part (member_name (10))

Composite index order

There are many people like to create a composite index, always think that the leading column must be the only value of the column, such as the index index idx_create_time_status (create_time, status), this index is often unable to hit, because the scan of the IO number is too many, the overall cost is also larger than the full table scan, the final choice of CBO is to go full table scan.

MySQL follows the principle of leftmost index matching. For a composite index, scan the index column from left to right until the first range query (> =, >, 0) is encountered. The final SQL is:

Select id,team_id,reason,status,type,created_time,invite_id,falg_admin,file_id from t_user_msg where 1 and (team_id in (3212) and * * IFNULL (app_id, 0) > 0) * *) or (invite_id=12395 or app_id=12395) order by created_time desc limit 0L10

Rewrite Optimization 3

If you change the field app_id bigint (20) DEFAULT NULL to app_id bigint (20) NOT NULL DEFAULT 0, and update all the app_id is null to 0, you can convert the conditional app_id is not null to app_id > 0, and the final SQL is:

Select id,team_id,reason,status,type,created_at,invite_id,falg_admin,file_id from t_user_msg where 1 and (team_id in (3212) and * * app_id > 0) * *) or (invite_id=12395 or app_id=12395) order by created_time desc limit 0pr 10

From the point of view of the execution plan, the two rewriting optimization methods take three single-column indexes, and the execution time is reduced from 2s to 10ms, and optimization 1 is adopted online, which will avoid problems if they can follow the MySQL development specification at the beginning.

At this point, the study of "how to tune SQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.