This is the fourth day of my participation in the November Gwen Challenge. See details: The last Gwen Challenge 2021.

Slow Query logs

  1. Modify the configuration file (usually /etc/my.cnf) to enable slow log query
[mysqld] slow_query_log = 1 slow_query_log_file = /var/lib/mysql/slow-query.log Log long_QUERy_time = 1 log_QUERies_NOT_USING_INDEXES = 1Copy the code
  1. Use with mysqlDumpslow

How to enable MySQL slow query log

explain

The principle of

MySQL sets a tag on the query. When a query is executed, this tag causes it to return information about each step in the execution plan, rather than executing it. It returns one or more lines of information, showing each part of the execution plan and the order in which it was executed.

Will EXPLAIN be executed

If the query includes a subquery in the FROM clause, MySQL actually executes the subquery, puts its results in a temporary table, and then completes the outer query optimization. It must process all similar subqueries before the outer query optimization can be completed, which is a must for Explain (this restriction will be removed in MySQL5.6).

limit

  • Are triggers, stored procedures, and UDFs not told how they affect queries
  • Specific optimizations made during query execution are not told

See Appendix D of High-performance MySQL

Improvements in 5.6

Being able to interpret queries like UPDATE, INSERT, and so on allows anonymous tables to materialize as late as possible, instead of always creating and populating them while optimizing and executing partial queries that use this temporary table. This will allow MySQL to interpret the string query directly without actually executing the subquery first.

profile

The specific use

MySQL query optimization (4) – show profile – very six articles – zhihu zhuanlan.zhihu.com/p/58387072

Optimizer tracking

  1. View optimizer status

    • show variables like ‘optimizer_trace’;

  2. The session level is enabled temporarily

    • set session optimizer_trace=”enabled=on”,end_markers_in_json=on;

  3. Sets the size of memory tracked by the optimizer

    • set OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;

  4. Execute your own SQL

    • select host,user,plugin from user;

  5. Information_schema optimizer_trace table

    • SELECT trace FROM information_schema.OPTIMIZER_TRACE;

  6. Import a file named xx.trace, and use JSON reader to view it (if you don’t have console permission, or simply hand it over to o&E, ask him to output the trace file to you). .

    • SELECT TRACE INTO DUMPFILE "E:\\test.trace" FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

Note: Not setting the maximum optimizer capacity may result in incomplete results returned by the optimizer.