This is the 16th day of my participation in the August Text Challenge.More challenges in August

preface

In the development of how do we locate which SQL needs to be optimized, how to let the database automatically help us find out the slow SQL, so that we are efficient, targeted to optimize?

Multi-table association optimization

When we carry out associative query between two tables, as shown in the figure above, TBL1 and TBL2 are associative query, and the associated field is COL3. The database will select a table as the condition according to its own judgment (TBL1 in the figure), and then screen out data with COL3 =1 and bring these data into TBL2 for cyclic comparison. Finally, all result sets are returned. The conditional table is called a driven table, and the database decides which one to select.

example

There are more than 4 million customer information and contact information. Perform associated query of the contact information of the customer.

SQL > select ‘ALL’ from ‘type’ and ‘rows’ from’ 46371 ‘. SQL > select ‘ALL’ from ‘type’ and ‘rows’ from’ 46371 ‘.

The default table appearing in the first row of the EXPLAIN execution plan is the driver table, which is automatically selected by the query optimizer

To optimize the

This type of full table scan SQL should be avoided at all costs. Next, optimize this query.

Key points of associated query optimization

  1. Add index to foreign key
  2. Add indexes to query conditions
CREATE INDEX idx_customer_id ON 72crm_crm_customer_contact(customer_id)
Copy the code

Take a look at the execution plan after the index is added

The “idx_custoemr_id” index was changed to “1” for the t2 table. The improvement is even more pronounced in high concurrency scenarios.

Slow SQL log

We can enable the slow SQL log function, so that the database can help us record the SLOW SQL statements in the use process, which is convenient for rapid positioning and targeted optimization.

-- Enable the slow SQL logging function
SET GLOBAL slow_query_log = ON;
-- Slow SQL execution time threshold, in seconds, 1 ms =0.001 seconds
SET GLOBAL long_query_time = 0.001;
-- Specify the slow SQL file name slow-sql, and slow SQL logs are stored in mysql/data
SET GLOBAL slow_query_log_file = "slow-sql.log";
Copy the code

Note: SET GLOBAL only applies to the current connection. If persistent configuration is required, add the configuration item in my.cnf

Use the following statement to check whether the Settings take effect
show variables like '%query%'
Copy the code

Let’s test this by executing a query statement

As you can see, the execution time is 3.120 seconds, much higher than the threshold we set. Let’s see if slow-sqL.log is generated

You can see that our SQL was recorded.

Parameter meaning

# Time: 2021- 08- 16T14:01:25.943484Z -- Execution time
# User@Host: root[root] @  [211.161247.64.]  Id:  3255 -- Environment Information
# Query_time: 0.242326  -- Query time
# Lock_time: 0.112309 -- Resource lock time
# Rows_sent: 98  -- Total number of rows in the query result
# Rows_examined: 98 -- Number of rows scanned
# SET timestamp=1629122485; - a timestamp
Copy the code

Records statements that are not indexed

At the beginning of the project, the amount of data is small, and some data will not be logged by slow SQL even if it is not indexed, so it does not mean that we can let go of these SQL statements, because as the amount of data increases, there will be a lag.

The following statements are used to record the SQL statements that are not indexed

SET GLOBAL log_queries_not_using_indexes = ON
Copy the code

prompt

In the above example, we set the threshold to a small value for effect. In normal environment, the threshold is set to 300ms-500ms. After this time, the query speed is slow, so you can optimize for these SQL

extension

Antiparadigm design

It is unrealistic to strictly refer to the three normal forms for the design of database table structure. According to the requirements of the three normal forms, the tables will be split too fragmented, resulting in complex database table association, which brings great pressure to the compilation of SQL statements. In the development manual of Ali, it is mandatory to forbid the use of join for the association of three tables.

Optimization scheme

Add redundant fields properly

Advantages and disadvantages of antiparadigm

advantages

  • Single-table queries are easy to optimize and manage
  • SQLSimple statement, easy to understand, conducive to program development, team collaboration

disadvantages

  • Redundant data fields exist, requiring additional update of slave table data during write operations
  • Improper anti-paradigm design can make a watch bloated