Slow query optimization for SQL databases

  • Database optimization
    • The target
    • indicators
    • process
    • Database parameter optimization
      • Oracle
      • MySQL
  • SQL optimization
    • Slow location Query SQL
    • SQL statement optimization
  • conclusion

Database optimization

  • Database optimization should ensure the following three points:

    • Log cannot be small
    • Cache large enough
    • Connect enough

  • After a database transaction is committed, the changes made by the transaction need to be flushed to disk to ensure the persistence of the data. The refresh process is a random write process with low performance. If the disk needs to be refreshed every time a transaction commits, the performance of the database can be significantly affected

  • This can be optimized in the database architecture design using the following methods:

    • The transactions are first written to the log file RedoLog, optimizing random writes to sequential writes
    • Add a layer of Buffer structure to optimize each write into a batch write

The target

  • Depending on the role, database tuning includes the following goals:

    • Business perspective: Reduce client page response time

    • Database perspective: Reduce database SQL response time

    • Database server perspective:

      • Make full use of database server physical resources
      • Reduce database server CPU usage
      • Reduces the DATABASE server I/O usage
      • Reduces database server memory usage

indicators

  • Reduce the average response time of SQL
  • Reduce database server CPU usage
  • Reduce the I/O usage of the database server

process

  • Understand the optimization problem: Start by understanding as much as you can about the optimization problem
  • Collect system information: Collect system information during problems and archive it
  • Set optimization goals: set optimization goals based on current system performance and communicate and agree with customers
  • Analyze performance problems: Analyze system problems through a series of tools
  • Develop optimization plan
  • Implement optimization plan

Database parameter optimization

  • A database architecture consists of the following aspects, which can be adjusted to maximize the performance of the database:

    • Data cache
    • Parse SQL area
    • Sort memory
    • REDO and NUDO
    • Locks, LATCH, MUTEX
    • Monitoring and connection
    • File read and write performance

Oracle

Parameters of the category Parameter names The parameter value instructions
Data cache SGA_TAGET MEMORY_TARGET DB_CACHE_SIZE 70-80% of physical memory The bigger the better
SQL parsing SHARED_POOL_SIZE 4GB – 16GB Do not set it to 6GB
Listening to the connection PROCESS SESSIONS OPEN_CURSORS Set this parameter based on service requirements Generally, it is 120% of the estimated number of connections
The rest of the parameters SESSION_CACHED_CURSORS More than 200 parsing

MySQL

  • MySQL InnoDB engine has the following parameters:
Parameters of the category Parameter names The parameter value instructions
Data cache INNODB_BUFFER_POOL_SIZE 50-80% of physical memory The bigger the better
logging Innodb_log_buffer_size sync_binlog 16MB – 32MB 1, 100, 0 Security is best when adjusting 1 to health
Listening to the connection max_connections Set this parameter based on service requirements You can reserve some values
File read and write performance innodb_flush_log_at_trx_commit 2 Compromise on safety and performance
The rest of the parameters wait_timeout interactive_timeout 28800 Avoid application disconnection

SQL optimization

Slow location Query SQL

  • Slow-locating query SQL can be judged by two representations

    • System-level representation:

      • Severe CPU consumption

      • I/O wait critical

      • The page response time is too long

      • Errors such as timeout occur in project logs

        • Run the SAR command and the top command to check the current system status
        • You can also view the current system status using the Prometheus and Grafana monitoring tools

    • SQL statement representation:

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

  • Get the problem SQL in different ways depending on the database

    • MySQL:

      • Slow Query logs
      • Test tool loadRunner
      • Ptquery tools

    • Oracle:

      • Who runs AWR report
      • Test tool loadRunner
      • Related internal views v$, $session_wait
      • GRID CONTROL monitoring tool

SQL statement optimization

  • Fair use index:

    • Index less query slow. Multiple indexes occupy large space. Dynamic index maintenance is required when adding, deleting, or modifying statements, affecting performance
    • B tree indexes are required for fields with high selection rates, few duplicate values, and frequently referenced in WHERE

  • Use UNION ALL instead of UNION

  • Do not use the SELECT * notation

  • Index the join field

  • Avoid complex SQL statements and can be converted into multiple sub-queries to be processed in the business layer

  • Use Explain SQL to view SQL execution plans:

field instructions
id Each operation to be performed is identified by the order in which the object is to be operated. If they are the same, they are executed from top to bottom
select_type The type of each SELECT statement in the query
table The name of the object being operated on, usually the table name. Other formats will also be included
partitions Matched partition information, NULL for non-partitioned tables
type Type of connection operation
possible_keys Possible indexes
key The best to worst indexes used by the optimizer are const, eq_reg, ref, range, index, and ALL
key_len The length of the index key selected by the optimizer, in bytes
ref Represents the reference object of the line object being operated on. NULL if there is no reference object
rows Number of tuples scanned by the query
filtered The percentage of tuples on the condition table that data is filtered
extra When Using filesort, Using TEMPORARY the SQL statement is optimized

conclusion

  • Use Explain to view execution plans for SQL statements
  • If an alarm is generated, view the show Warnings command
  • View the table structure and index information involved in the SQL statement
  • Optimize where the SQL statement needs to be optimized according to the execution plan
  • Perform table structure modification, index addition,SQL statement rewriting and other operations according to the need for optimization
  • Use Explain again to see the optimized execution time and execution plan
  • Continue optimization according to the optimization effect, or optimization success