The previous article looked at database optimization from an example point of view by configuring some parameters to optimize database performance. But some “bad” SQL can also slow down database queries and affect business processes. In this paper, database optimization is carried out from the perspective of SQL to improve the efficiency of SQL operation.

Judgment problem SQL

There are two representations to determine if there is a problem with SQL:

  • System level representation
    • Severe CPU consumption
    • I/O wait critical
    • The page response time is too long
    • Application logs have timed out. Procedure

You can usesarCommand,topCommand to view the current system status.

Also throughPrometheus, GrafanaAnd other monitoring tools to observe the system status. (Check out my previous post if you’re interested.)

  • SQL statement representation
    • long
    • The execution time is too long. Procedure
    • Get data from a full table scan
    • The rows and costs in the execution plan are large

Long SQL is easy to understand, but long SQL is bound to be poorly read, and problems are bound to occur more often. To further determine the SQL problem, start with the execution plan, as follows:

Type=ALL; rows = 9950400;

Get problem SQL

Different databases have different acquisition methods. The following is the slow query SQL acquisition tool of the current mainstream database

  • MySQL
    • Slow Query logs
    • Test tool loadRunner
    • Tools such as PtQuery from Percona
  • Oracle
    • Who runs AWR report
    • Test tools loadRunner, etc
    • Related internal views such as V $SQL, V $session_wait, etc
    • GRID CONTROL monitoring tool
  • Damont database
    • Who runs AWR report
    • Test tools loadRunner, etc
    • Dameng Performance Monitoring Tool (DEM)
    • Related internal views such as V $SQL, V $session_wait, etc

SQL Writing Skills

There are several general techniques for SQL writing:

• Use indexes wisely

Index less query slow; A large number of indexes occupies large space. Therefore, indexes need to be dynamically maintained when adding, deleting, or modifying statements, which affects performance

High selection rate (few duplicate values) and frequently referenced by WHERE need to establish B-tree index; Generally, join columns need to be indexed. Full-text indexing is more efficient for complex document type query. Indexes are built to strike a balance between query and DML performance; When creating a composite index, be aware of queries based on non-leading columns

• Replace UNION with UNION ALL

The execution efficiency of a UNION ALL is higher than that of a UNION. The UNION needs to sort the data

• Avoid the select * notation

When executing SQL, the optimizer needs to convert * into a specific column; Each query returns to the table and cannot overwrite the index.

• Index creation is recommended for the JOIN field

JOIN fields are usually indexed in advance

• Avoid complex SQL statements

Improve readability; Probability of avoiding slow queries; It can be converted into multiple short queries and processed by the business side

• Avoid where 1=1

• Avoid writing order by rand()

RAND() causes columns to be scanned multiple times

SQL optimization

The execution plan

To complete SQL optimization, always read the execution plan first. The execution plan will tell you where the inefficiencies are and where you need to optimize. Let’s take MYSQL as an example to see what the execution plan is. (The execution plan for each database is different and needs to be understood by yourself)

explain sql

field explain
id Each operation that is executed independently identifies the sequence in which the operation is performed. If the operation id is larger, the operation is performed first. If the operation id is the same, the operation is performed from top to bottom
select_type The type of each select clause in the query
table The name of the object being operated on, usually a table name, but in another format
partitions Matched partition information (NULL for non-partitioned tables)
type

 Type of connection operation
possible_keys Possible indexes
key The index actually used by the optimizer (The most important column) The connection type from best to worst isconst,eq_reg,ref,range,indexandALL. When there is aALL“, the current SQL has a “bad taste”
key_len

 The length of the index key selected by the optimizer, in bytes
ref

 Represents the reference object of the object being operated on in the row. NULL is the value of no reference object
rows

 Number of tuples scanned by query execution (for InnoDB, this value is an estimate)
filtered The percentage of tuples on the condition table that data is filtered
extra Important supplementary information for the execution plan when this column appearsUsing filesort , Using temporaryBe careful when typing, it is likely that SQL statements need to be optimized

Next, we use a practical optimization case to illustrate the process of SQL optimization and optimization skills.

To optimize the case

  • Table structure
    CREATE TABLE `a`
    (
        `id`          int(11) NOT NULLAUTO_INCREMENT,
        `seller_id`   bigint(20)                                       DEFAULT NULL,
        `seller_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
        `gmt_create`  varchar(30)                                      DEFAULT NULL,
        PRIMARY KEY (`id`)
    );
    CREATE TABLE `b`
    (
        `id`          int(11) NOT NULLAUTO_INCREMENT,
        `seller_name` varchar(100) DEFAULT NULL,
        `user_id`     varchar(50)  DEFAULT NULL,
        `user_name`   varchar(100) DEFAULT NULL,
        `sales`       bigint(20)   DEFAULT NULL,
        `gmt_create`  varchar(30)  DEFAULT NULL,
        PRIMARY KEY (`id`)
    );
    CREATE TABLE `c`
    (
        `id`         int(11) NOT NULLAUTO_INCREMENT,
        `user_id`    varchar(50)  DEFAULT NULL,
        `order_id`   varchar(100) DEFAULT NULL,
        `state`      bigint(20)   DEFAULT NULL,
        `gmt_create` varchar(30)  DEFAULT NULL,
        PRIMARY KEY (`id`)
    );Copy the code

  • Associate three tables to query orders placed by the current user within 10 hours before and after the current time and arrange the orders in ascending order based on the creation time. The SQL is as follows
select a.seller_id, a.seller_name, b.user_name, c.state from a, b, c where a.seller_name = b.seller_name and b.user_id = c.user_id and c.user_id = 17 and a.gmt_create BETWEEN DATE_ADD(NOW(), INTERVAL -- 600 MINUTE) AND DATE_ADD(NOW(), INTERVAL 600 MINUTE) order by a.gmt_create;Copy the code

  • Viewing Data Volume
! [image](https://p1-jj.byteimg.com/tos-cn-i-t2oaga2asx/gold-user-assets/2019/11/2/16e2a10eeb735db2~tplv-t2oaga2asx-image. image)Copy the code
  • Original execution time
! [image](https://p1-jj.byteimg.com/tos-cn-i-t2oaga2asx/gold-user-assets/2019/11/2/16e2a10f86740a83~tplv-t2oaga2asx-image. image)Copy the code
  • Original execution plan
! [image](https://p1-jj.byteimg.com/tos-cn-i-t2oaga2asx/gold-user-assets/2019/11/2/16e2a10fa66c37e7~tplv-t2oaga2asx-image. image)Copy the code
  • Preliminary optimization idea
    1. SQL SQL where condition field type must be consistent with the table structure, tableuser_idType vARCHar (50), int used in SQL, implicit conversion, no index added. Table B and Cuser_idChange the field type to int.
    2. Table B and C are associated with each otheruser_idCreate indexes
    3. Table A and table B are associatedseller_nameField create index
    4. Use compound indexes to eliminate temporary tables and sorts
  • Preliminary optimization of SQL
    alter table b modify `user_id` int(10) DEFAULT NULL;
    alter table c modify `user_id` int(10) DEFAULT NULL;
    alter table c add index `idx_user_id`(`user_id`);
    alter table b add index `idx_user_id_sell_name`(`user_id`,`seller_name`);
    alter table a add index `idx_sellname_gmt_sellid`(`gmt_create`,`seller_name`,`seller_id`);Copy the code

  • View the optimized execution time
! [image](https://p1-jj.byteimg.com/tos-cn-i-t2oaga2asx/gold-user-assets/2019/11/2/16e2a10fc97978e7~tplv-t2oaga2asx-image. image)Copy the code
  • View the optimized execution plan

  • Viewing Warnings Information

  • Continue to optimize

    alter table a modify "gmt_create" datetime DEFAULT NULL;
  • Viewing execution Time
! [image](https://p1-jj.byteimg.com/tos-cn-i-t2oaga2asx/gold-user-assets/2019/11/2/16e2a110294bd394~tplv-t2oaga2asx-image. image)Copy the code
  • Viewing the Execution Plan
! [image](https://p1-jj.byteimg.com/tos-cn-i-t2oaga2asx/gold-user-assets/2019/11/2/16e2a1104a9fe5b8~tplv-t2oaga2asx-image. image)Copy the code
  • Optimize the summary
    1. View execution plan Explain
    2. If an alarm is generated, run the show warnings command.
    3. View table structure and index information involved in SQL
    4. Consider possible optimization points based on the execution plan
    5. Perform table structure changes, index additions, SQL overwrites, and other operations based on possible optimization points
    6. View the optimized execution time and plan
    7. If the optimization effect is not obvious, repeat step 4

      For more content, please pay attention to the public number: JAVA Daily Records