This article has participated in the “Digitalstar Project” and won a creative gift package to challenge the creative incentive money.

Develop one-liners

Let me 996 is not what, I am afraid the test also 996 bug to me!

I looking forward

Hello, no matter whether we meet each other in real life, we will meet each other happily in the world of InfoQ. I wish you a happy National Day in advance, and I will not work overtime on our “1024” day, go home early to accompany my wife and children.

Technology pyramid

This article will “tour” the MySQL technology pyramid in two directions, top-down and bottom-up, respectively from the cost (subtext: cheaper is better, take care of the cost of the company!). , the content of this chapter, may be a bit much, I hope you slowly digest, it is not good to a piece of “it”, ha ha, joking!

Technical summary

Major technologies are distributed in 6 parts, as shown in the following figure:

R&d cost perspective

From the Angle of software research and development costs 】 【 : along with the direction of the optimization, the direction from the top pyramid like the bottom of the pyramid to excessive, with highly more and more low, the cost will be more and more low, the direction is very test of the ability of technical personnel and project managers, but it does, the boss see objects, ha ha.

Technical feasibility and effect Angle

From the perspective of [technical feasibility and effect] of software: Along with the direction of the optimization, lower part from the pyramid like excessive direction on top of the pyramid, with the height is higher and higher, the cost will be more and more high, the cost of financial and human resources will be relatively lower, but if you spend more money, the boss sure don’t want to, for example, some industry Daniel or some cows to break off the server, please, etc., can see is just the reverse of the direction of the above.

To sum up, the above two points of policy, follow the research and development cost is lower and lower + the effect plan is higher and higher, so we divide a formula, as a system service tuning methodology, we will according to the pyramid level, from bottom to bottom for tuning! Let’s go in that direction.

Tuning white paper

Business requirements and business architecture

Single products to support, demand to be comprehensive. — King of Glory products

Adjusting the right demand, in fact, is a very good plan, so if you can start from the root, adjust the demand is a very effective plan oh! And say no to unreasonable demands! When designing the architecture, we should fully consider the actual business scenarios. It is very important to consider the database options and introduce some other solutions, such as NoSQL or NewSQL. Therefore, it is a very good plan to adjust a system architecture.

  • Try to intercept the request in the upstream of the system. The reason why the traditional business system hangs is that the requests overwhelm the back-end data layer, the data read and write lock conflicts are serious, the concurrency is high and the response is slow, almost all the requests timeout, although the flow is large, the effective flow of the successful order is very small.
  • Read too much and write too little Use cache Too much This is a typical application scenario where read too much and write too little, which is very suitable for caching.

SQL technology tuning

According to service requirements, SQL statements should not only be written, but also be tuned to optimize their performance.

Tuning ideas

Tuning consists of three main components: problem discovery, problem analysis, and problem resolution.

Problem detection (optimization and analysis of slow SQL)

Discovering slow SQL and querying logs

Querying the log of slow SQL is a built-in MySQL feature that logs SQL statements that take longer to execute than our configured threshold.

Parameters and Default values:

Usage: Modify the MySQL service configuration

Generally we set up “old three” can!

  1. Modify our installed configuration file my.cnf, add the above parameters in the [mysqld] paragraph:
[mysqld] log_output='FILE,TABLE'; Long_query_time =0.001 // Query time (s)Copy the code
  1. Then restart the service
service mysqld restart
Copy the code
Usage – Modify global service configuration
set global log_output='TABLE,FILE'; set global slow_query_log = 'NO'; The set long_query_time = 0.001;Copy the code

This mode takes effect without restarting the server, but the configuration will be lost again when the server restarts.

The slow_log table in the mysql database and the corresponding slow_SQL file can be logged by the above configuration.

Analyze the query logs of slow SQ

Slow_log select * from mysql.slow_log select * from mysql.slow_log select * from mysql.slow_log You can analyze and count SQL execution performance based on this aspect.

Analyze slow SQL log files

When log_output is set to FILE, the FILE is too large to view, so you can use a special tool for analysis. Here we mainly introduce the native mysqlDumpslow tool for analysis, as shown in the following figure:

Mysqldumpslow — help:

Use cases:
  1. SQL > select * from ‘SQL’;
mysqldumpslow -s  r -t 20 /path/show.log
Copy the code
  1. SQL > select * from left JOIN;
mysqldumpslow -s  t  -t  -g "left join"  /path/show.log
Copy the code

Of course, other related MySQL slow query analysis log tools, such as MySQL Profiles or PT-Query-Digest, can also be used for analysis. Interested partners can search to see.

Execute plan analysis slow SQL

Explain keywords to perform slow SQL statements and index analysis:

Case analysis

The simplest example is this:

The explain SQL statements

Id field

It represents the unit of identity that represents the atomic query (maintenance) operation for each part of the statement. If there are multiple data items in Explain, remember to execute them in reverse order.

  • The higher the number, the more analysis is performed first
  • Numbers numbered identically, analyzed from top to bottom

Select_type field

Query type, with the following sets of values:

The table field

It indicates which table is being accessed by the current row and, if SQL defines an alias, shows the table alias

Partitions field

The partition that matches the current query record. For unpartitioned tables, null is returned.

The type field

The connection types are as follows:

The system:

This table has only one row (equivalent to a system table), and system is a special case of const type.

const:

An equivalent query scan against a primary key or unique index returns at most one row. A const query is very fast because it reads only once.

Eq_ref:

This type is used only when all components of an index are used and the index is PRIMARY KEY or UNIQUE NOT NULL. This type is second only to System and const.

Multiple table associated query, single row matching
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
Copy the code
Multi-table associated query, joint index, multi-row matching
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;
Copy the code

ref

This occurs when the left-most prefix rule for an index is met, or when the index is not a primary key or unique index. If you use an index that matches only a small number of rows, performance is good.

Matches multiple rows based on index (not primary key, not unique index)
SELECT * FROM ref_table WHERE key_column=expr;
Copy the code
Multi-table associated query, single index, multi-row matching
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
Copy the code
Multi-table associated query, joint index, multi-row matching
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;
Copy the code
TIPS

The left-most prefix principle states that an index matches an index in left-first fashion. Select column1, column2, column3 from column1; select column1 from column2;

  • WHERE column1= 1, column1= 1 AND column2 = 2, column1= 1 AND column2 = 2 AND column2 = 2, column1= 1 AND column2 = 2 AND column2 = 3
  • Column2 = 2, column2 = 1 AND column3 = 3 cannot match the index.

Fulltext: indicates the full-text index

ref_or_null

This type is similar to ref, but MySQL will additionally search for rows that contain NULL. This type is common in parsing subqueries.

SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;
Copy the code

index_merge

This type indicates that index merge optimization is used, indicating that multiple indexes are used in a query.

unique_subquery

This type is similar to eq_ref, but uses an IN query and the subquery is a primary key or unique index. Such as:

 value in ( select primary_key from single_table where some_condition)
Copy the code

index_subquery

 value in ( select key_column  from single_table where some_condition)
Copy the code

Similar to unique_subquery, except that the subquery uses a non-unique index

range

Range scan, which retrieves rows of a specified range. Used mainly for restricted index scans. More common range scans are those with BETWEEN clauses or WHERE clauses with operators such as >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, IN(), etc.

SELECT * FROM tbl_name
WHERE key_column BETWEEN 10 and 20;
SELECT * FROM tbl_name
WHERE key_column IN (10.20.30);
Copy the code

index

A full index scan is similar to ALL, except that index scans ALL the data in the index. This type is used when the query uses only a portion of the columns in the index. There are two scenarios that trigger:

  • Only the index tree is scanned if the index is the overwriting index of the query and the data in the index query is sufficient for all the data required in the query. In this case, the result of the Extra column in Explain is Using index. Index is usually faster than ALL because the index size is usually smaller than the table data.

  • A full table scan is performed to find rows of data in index order. At this point, the results of the Extra column in Explain do not appear Uses Index.

ALL

Full table scan has the worst performance.

possible_keys

Shows which indexes are available for the current query. The data in this column was created early in the optimization process, so some indexes may not be useful for subsequent optimization.

key

Represents the actual index selected by MySQL

key_len

The number of bytes used by the index. Because of the storage format, key_len is 1 byte larger when NULL is allowed than when NULL is not allowed.

Key_len evaluates the formula

Can refer to the blog: www.cnblogs.com/gomysql/p/4…

ref

Indicates which field or constant is compared to the field used by the key column. If ref is a function, the value used is the result of the function. To see which function it is, follow the EXPLAIN statement with a SHOW WARNING statement.

rows

MySQL estimates the number of rows scanned after SQL execution. The smaller the number, the better.

filtered

Percentage of data that matches the query conditions. The maximum value is 100. Use rows × Filtered to get the number of rows connected to the next table. For example, rows = 1000, filtered = 50%, then the number of rows connected to the next table is 500.

TIPS

Prior to MySQL 5.7, to display this field used the Explain extended command; For MySQL.5.7 and later, explain shows Filtered by default

Extra(Key analysis)

Displays additional information about this query. The values are as follows:

  • Child of ‘table’ pushed join@1

This value will only occur under NDB Cluster.

  • const row not found

Query statement SELECT… FROM tbl_name, and the table is empty

  • Deleting all rows

For DELETE statements, some engines (such as MyISAM) support a simple and quick way to DELETE all data and display the value if this optimization is used.

  • Distinct

Looks for distinct values, and when the first matching row is found, stops searching for more rows for the current combination of rows

FirstMatch(tbl_name) Currently uses the half-join FirstMatch policy.

  • Full scan on NULL key

An optimization in subqueries that is used when null values cannot be accessed through indexes

  • Impossible HAVING

The HAVING clause is always false and does not hit any rows

  • Impossible WHERE

The WHERE clause is always false and does not hit any rows

  • Impossible WHERE noticed after reading const tables

MySQL has read all const(or system) tables and found that the WHERE clause is always false

LooseScan(m.. N) The semi-connected LooseScan policy is currently used,

  • No matching min/max row

There is no such thing as SELECT MIN(…) FROM … The condition row in WHERE condition

160

  • no matching row in const table

For an associated query, there is an empty table or no row that meets the unique index condition

  • No matching rows after partition pruning

For DELETE or UPDATE statements, the optimizer cannot find the content to DELETE or UPDATE after partition pruning

  • No tables used

Occurs when this query has no FROM clause or has a FROM DUAL clause. For example, explain select 1

  • Not exists

MySQL is optimized for LEFT JOIN and does not check more rows in the table for the previous row combination after finding a row that matches the LEFT JOIN. Such as:

 SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
Copy the code

Previous versions of MySQL8 did this:

For example, if t2.id is defined as NOT NULL, MySQL scans T1 and uses the value of t1.id to look for rows in T2. If MySQL finds a matching row in T2, it will know that T2.id is never NULL and will not scan for other rows in T2 with the same ID value. That is, for each row in T1, MySQL only needs to perform one lookup in T2, regardless of the number of rows actually matched in T2.

MySQL 8.0.17 and later:

  • If this prompt occurs, it can also indicate that a WHERE condition of the form NOT IN (subQuery) or NOT EXISTS (subQuery) has been internally converted to an anti-join.

  • This improves the overhead of the query by removing the subquery and placing its tables in the top-level query plan. By merging semi-joins and anti-joins, the optimizer has more freedom to reorder tables in the execution plan and, in some cases, speed up queries.

You can see when a reverse join transformation was performed on the query by following the EXPLAIN statement with a SHOW WARNING statement and analyzing the Message column in the result.

Reverse join: a join that returns only the data from the primary table and only the data from the primary table that is not associated with the child tables.

  • Plan isn’t ready yet

With EXPLAIN FOR CONNECTION, this value appears when the optimizer has not finished creating an execution plan FOR the statement to execute in the specified CONNECTION.

  • Range checked for each record (index map: N)

    • This prompt occurs when MySQL does not find a suitable index to use, but checks to see if it can use range or index_merge to retrieve rows.

    • Index map N Index numbers start from 1 and are in the same order as SHOW index of the table. The index mapping value N is a bitmask value indicating which indexes are selected. A value such as 0x19(binary 11001) implies that indexes 1, 4, and 5 will be considered.

Okay, are you dazzled by this? Now start the main play, the above can be used as knowledge expansion and understanding, but the following content suggests you must understand oh, will have a great help for performance optimization oh!

  • unique row not found

For forms such as SELECT… Query FROM tbl_name, but no rows meet the criteria for a unique index or primary key query.

  • Using filesort

    • The MySQL Query Optimizer has to select a sorting algorithm for SQL queries that contain an ORDER BY clause and cannot be sorted using an index.
    • Sort from memory when data is low and from disk when Memory_Sort thresholds are exceeded.
    • Also, the Explain command does not explicitly tell the MySQL database client which sort to use.

Official explanation: MySQL needs an extra pass to figure out how to retrieve rows in sort order.

  1. Sorting is done by browsing all rows based on the join type and saving the sort key and row pointer for all rows that match the WHERE clause.
  2. The keywords are then sorted and rows are retrieved in sorted order.

  • Using index

    • (commonly known as: single coverage index oh!) To retrieve column information from the table using only the information in the index tree without additional lookups to read the actual rows.
    • This policy can be used when queries use only columns belonging to a single index. Such as:
     select id from table
Copy the code

  • Using index condition

    • (Commonly known as: cover down push oh!) After the index is filtered, all rows that meet the index criteria are found. Then, the rows are filtered using other criteria in the WHERE clause. In this way, index information can delay “push-down” reading of the entire row unless necessary. Such as:
    SELECT * FROM people where id > 10 and age = 10 or address > 100000 ;
Copy the code

Can be adjusted by switching on or off index condition push

 SET optimizer_switch = 'index_condition_pushdown=off';
 SET optimizer_switch = 'index_condition_pushdown=on';
Copy the code

By the way, MySQL is divided into Server layer and Engine layer. Push-down refers to passing requests to the Engine layer, but the performance difference is determined by different engines.

  • Using index for group-by

Using index is the same as Using index. When a GROUP BY or DISTINCT clause is used in Query, the information in Extra will be Using index for group-by if the GROUP BY or DISTINCT clause is in the index, for example:

explain SELECT name FROM t1 group by name
Copy the code
  • Using index for skip scan

Indicates that Skip Scan is used. The bottom layer adopts the “Skip Scan Range Access Method algorithm mechanism”, which means that Index query positioning is made in advance and a lot of scanning is reduced. In fact, it is not very different from using Index!

  • Using join buffer (Block Nested Loop)

The performance of join can be improved by Using Batched Key Access (batch keyword retrieval) algorithm. In addition, their memory lines are similar to adjacent positions. Therefore, the buffer cache mechanism is used to cache the data retrieved in this batch comparison, which greatly improves efficiency!

Specific interested partners can recommend reference:www.cnblogs.com/chenpingzha…

  • Using MRR

The multi-range Read optimization strategy is used. See “Multi-range Read Optimization”

Using sort_union(…) , Using union(…) , Using intersect(…) , which indicates how index scans are merged into the Index_MERGE join type. See the official Index Merge Optimization for more details.

  • Using temporary

What to do when sorting or grouping occurs and the data needs to be further computed, which cannot be solved by the natural data model of the index? To resolve this query, MySQL needs to create a temporary table to hold the results. This usually happens if you query for GROUP BY and ORDER BY clauses that contain different columns.

  • The name field has indexes

    • explain SELECT name FROM t1 group by name

  • The name has no index

    • Explain SELECT name FROM T1 group by name.

  • Using where

The using WHERE information appears if we do not read all of the table’s data, or if we do not get all of the data we need through the index alone. The threshold corresponds to “overwrite index”!

SELECT * FROM t1 where id = 1
Copy the code
  • Zero limit

This query has a limit 0 clause and cannot select any rows.

explain SELECT name FROM resource_template limit 0
Copy the code

Explain command explanation

EXPLAIN can generate additional extended information, which can be viewed by a SHOW WARNING statement immediately following the EXPLAIN statement.

  • In MySQL 8.0.12 and later, extended information can be used in SELECT, DELETE, INSERT, REPLACE, UPDATE statements;
  • Prior to MySQL 8.0.12, extended information was only available for SELECT statements;
  • In MySQL 5.6 and later, use the EXPLAIN EXTENDED XXX statement. Starting with MySQL 5.7, you don’t need to add EXTENDED keywords.

The result of SHOW WARNING is not necessarily a valid SQL or executable (because it contains many special tags)

Description of special values

  • <auto_key> : indicates the temporary table key that is automatically generated.

  • (EXPR) : An expression (such as a scalar query) is executed once and the value is stored in memory for later use. For results that include multiple values, temporary tables may be created, and you will see the words.

  • Query Fragment: Subqueries are converted to exist and perform better.

  • <in_optimizer>(Query fragment) : This is an internal optimizer object that has no meaning to the user

  • < index_Lookup >(Query Fragment) : Use index lookup to process query fragments to find qualified rows

  • (condition, expr1, expr2) : Expr1 if the condition is true, expr2 otherwise

  • < is_NOT_NULl_test >(expR) : tests that verify expressions are not NULL

  • Query Fragment: Implemented using subqueries

  • Materialized-subquery.col_name, which internally materializes a reference to col_name in a temporary table to hold the results of a subquery

  • < primary_index_Lookup >(Query Fragment) : Use primary keys to process query fragments to find qualified rows

  • < ref_NULl_helper >(expR) : This is an internal optimizer object and has no meaning to the user

  • /* SELECT #N */ select_STmt: SELECT associate with the row id=N in the non-extended EXPLAIN output

  • Outer_tables semi JOIN (inner_tables) : a semi-join operation.

  • : indicates that an internal temporary table is created and intermediate results are cached

Estimate query performance

In most cases, you can estimate query performance by counting the number of disk searches. For smaller tables, rows can usually be found in a single disk search (because the index may already be cached), while for larger tables, you can use the B-tree index to estimate how many lookups you need to do to find the row: Log (row_count)/log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1 Index_block_length is typically 1024 bytes, and data Pointers are typically 4 bytes. For example, if you have a table with 500,000 and key is 3 bytes, log(500,000)/log(1024/3*2/(3+4)) + 1 = 4 searches. This index will require 500,000 7 3/2 = 5.2MB of storage (assuming a typical index cache fill rate of 2/3), so you can store more indexes in memory and possibly find the desired row with just one or two calls. However, for write operations, you need four search requests to find where to put the new index value, and then usually two searches to update the index and write the row. The previous discussion does not mean that your application performance will slow down with log N. As long as the content is cached by the OS or MySQL server, it will only slow down slightly as the table grows larger. After the amount of data becomes too large to cache, it will slow down a lot until your application is constrained by disk search (log N increments). To avoid this, increase key values as the data grows. For MyISAM tables, the cache size of keys is controlled by a system variable called KEY_buffer_size, see Section 5.1.1, “Configuring the Server”.

SQL Performance Analysis

SQL performance analysis means we mainly introduce three:

  • SHOW PROFILE
  • INFORMATION_SCHEMA.PROFILING
  • PERFORMANCE_SCHEMA

SHOW PROFILE (old MySQL service available, new version deprecated)

SHOW PROFILE is a MySQL performance analysis command that tracks resource consumption in SQL. The format is as follows:

SHOW PROFILE [type [, type] ... ]  [FOR QUERY n] [LIMIT row_count [OFFSET offset]]Copy the code
  • The value range for type:
    • ALL: Displays ALL information
    • BLOCK IO: displays The Times of blocking input and output
    • CONTEXT SWITCHES: Displays the number of voluntary and involuntary CONTEXT SWITCHES
    • CPU: displays the CPU usage between the user and the system
    • IPC: displays The Times of sending and receiving messages
    • MEMORY: Displays memory-related costs. This feature is not currently implemented
    • PAGE FAULTS: Displays overhead information related to PAGE errors
    • SOURCE: Lists the corresponding function name and its position in the SOURCE (line).
    • SWAPS: Indicates the number of swap SWAPS

By default, SHOW PROFILE displays only the Status and Duration columns. If you want to display more information, you can specify type as follows:

  • Check whether SHOW PROFILE is supported. Yes indicates that the SHOW PROFILE function is supported. As of MySQL 5.0.37, MySQL supports SHOW PROFILE.
select @@have_profiling;
Copy the code
  • Check whether the SHOW PROFILE function is enabled. 0 indicates that the SHOW PROFILE function is disabled, and 1 indicates that the SHOW PROFILE function is enabled
select @@profiling;
Copy the code
  • Enable or disable performance analysis for the current session. 1 indicates that performance analysis is enabled, and 0 indicates that performance analysis is disabled
set profiling=1
Copy the code

  • Do a summary performance analysis of the most recently sent SQL statements. The number of items displayed is controlled by the profiling_HISTORy_size session variable, which defaults to 15. The maximum value is 100. Setting the value to 0 has the practical effect of disabling analysis.

  • The Show profiles command

- 15 entries are displayed by default
show profiles
Use profiling_history_size to adjust the number of items displayed
set profiling_history_size = 100;
Copy the code

First specify queries using show Profiles analysis:

Use show Profile for analysis. By default, only the Status and Duration columns are displayed, and you can specify type if you want to display more information.

Run SHOW PROFILE FOR QUERY 1; , 1 represents query_id(show profiles)

Show CPU-related overhead

When the analysis is complete, remember to turn off the SHOW PROFILE function:

set profiling = 1
Copy the code

NFORMATION_SCHEMA.PROFILING

Information_schema. PROFILING is used for performance PROFILING and corresponds to information generated by the SHOW PROFILE and SHOW PROFILES statements. Unless set profiling = 1; Otherwise, the table will not have any data.

This table contains the following fields:

  • QUERY_ID: Unique identifier of a statement
  • SEQ: An ordinal that shows the order in which rows with the same QUERY_ID value are displayed
  • STATE: analysis status
  • DURATION: how long it lasts in this state (seconds)
  • CPU_USER, CPU_SYSTEM: User and system CPU usage in seconds
  • CONTEXT_VOLUNTARY, CONTEXT_INVOLUNTARY: How many voluntary and involuntary context shifts took place
  • BLOCK_OPS_IN, BLOCK_OPS_OUT: number of block input and output operations
  • MESSAGES_SENT, MESSAGES_RECEIVED: indicates the number of messages sent and received. PAGE_FAULTS_MAJOR, PAGE_FAULTS_MINOR: indicates the major and minor page errors
  • SWAPS: How many SWAPS SOURCE_FUNCTION, SOURCE_FILE, SOURCE_LINE occur: Where in the source code is the current state executed

The SHOW PROFILE essentially uses the Information_schema.profiling table as well;

The Information_schema. PROFILING table has been deprecated and may be deleted in the future. In the future, Performance Schema will be used instead,

Run the show profile command to query information
SHOW PROFILE FOR QUERY 2;
Copy the code
Information_schema. PROFILING can be queried
SELECT STATE, FORMAT(DURATION, 6) AS DURATION FROM INFORMATION_SCHEMA.PROFILING
WHERE QUERY_ID = 2 ORDER BY SEQ;
Copy the code

PERFORMANCE_SCHEMA(Future successor)

PERFORMANCE_SCHEMA is the performance analysis method recommended by MySQL. In the future, SHOW PROFILE/PROFILES and Information_schema. PROFILING will be deprecated.

PERFORMANCE_SCHEMA was introduced in MySQL 5.6 and is therefore only available in MySQL 5.6 and later. SHOW VARIABLES LIKE ‘performance_schema’;

 SHOW VARIABLES LIKE 'performance_schema';
Copy the code

PERFORMANCE_SCHEMA performs a similar effect to SHOW PROFILE: Check whether performance monitoring is enabled

MySQL 5.7 is enabled by default.

You can also execute SQL statements like the following to monitor only the SQL executed by the specified user:

Thus, only SQL sent by the test_user user on the localhost machine will be monitored. Do not monitor SQL messages sent by other hosts or users. Run the following SQL statement to enable monitoring items:

Use the user with monitoring enabled to execute SQL statements such as:

Execute the following SQL to obtain the EVENT_ID of the statement.

This step is similar to SHOW PROFILES. Execute the following SQL statement to perform performance analysis, so that you can know the information about the various stages of the statement.

MySQL official documentation states that SHOW PROFILE has been deprecated and suggests using Performance Schema as an alternative.

Three ways to compare and choose

  • SHOW PROFILE: Simple, convenient, obsolete
  • Information_schema.profiling, which is essentially the same as the SHOW PROFILE
  • PERFORMANCE SCHEMA: The light of the future, but not easy to use at present

So: Keep up with the PERFORMANCE_SCHEMA with SHOW PROFILE for now and get ready for the future

OPTIMIZER_TRACE Parameters

  • Optimizer_trace Specifies whether to enable optimizer_trace. Default value: enabled=off,one_line=off Enabled: Whether to enable optimizer_trace. On: enabled; OFF: disabled.
  • One_line: indicates whether to enable single-line storage. On: enable. Off means off, and it will be stored in the standard JSON format. Setting it to on will be well formatted, and setting it to off will save some space.
  • Optimizer_trace_features: Controls what optimizer_trace traces, default

Values: GREedy_search = ON,range_optimizer= ON,dynamic_range= ON, REPEATED_subSELECT = ON GREedY_search: whether to track greedy search.

  • Dynamic_range: indicates whether to track dynamic range optimization, indicating that all tracing items are enabled.
  • Repeated_subselect: Whether to track subqueries. If set to off, only the execution of the first Item_subselect is tracked.
  • Optimizer_trace_limit: Controls how many results optimizer_trace displays. The default value is 1
  • Optimizer_trace_max_mem_size: Maximum memory allowed by optimizer_trace stack information. The default is 1048576
  • Optimizer_trace_offset: The offset of the first Optimizer trace to show, default -1.
  • End_markers_in_json: If the JSON structure is large, it is difficult to pair the close bracket with the open bracket. To help the reader read, set it to on, which will be commented near the closing parenthesis and defaults to off.

Summary analysis

Detailed analysis of performance will follow in the “unique MySQL Tuning Pyramid” believe that if you have it, you probably have the world. In addition, it will be accompanied by an in-depth understanding and analysis of indexing principles.