Reasons for slow query: (1) Long execution time; ② Long waiting time.

  1. The query statement is not written properly.
  2. Index invalid.
  3. Join queries are too complex.
  4. The server parameter Settings are incorrect.

SQL machine read order

FROM <left table>
ON <join condition>
<join type> JOIN <right table>
WHERE <where condition>
GROUP BY <group by list>
HAVING <having condition>
SELECT
DISTINCT <select list>
ORDER BY <order by condition>
LIMIT <limit number>
Copy the code

The index

Ordered data structures that satisfy a particular algorithm and reference (point to) the data in a certain way help improve the efficiency of the WHERE clause (IO) and order by clause (CPU).

  1. Disadvantages of indexes
  • The indexes themselves are also large and cannot be stored in memory entirely, but are stored on disk as index files.
  • Data update operations are slow due to the need to maintain indexes.
  • If there is a large amount of data, it can take a lot of time to build indexes and optimize query statements.
  1. Index structure
  • BTree index
  • A Hash index
  • FullText FullText index
  • RTree index
  1. BTree search principle

The multi-path search tree reduces the height of the tree and the number of DISK I/OS. The data of the corresponding disk block is read into the memory at each layer of the tree and the binary search path is determined in the memory.

  1. Principles for index creation
  • Indexes are automatically created for primary, unique, and foreign keys.
  • Create indexes for frequently queried fields.
  • High concurrency tends to build composite indexes rather than single-value indexes.
  • Statistics or grouping or sorting fields are indexed.
  • A table should have no more than five indexes.
  • Table data is too small to create an index.
  • Tables that are frequently added or deleted do not need to be indexed.
  • Repeated and average fields, such as gender, do not need to be indexed.

Performance analysis

  1. MySQL optimizer takes time.
  2. Performance bottlenecks in CPU or IO or server hardware.

EXPLAIN can be used to analyze the MySQL query optimizer’s understanding of SQL statements. The following is an explanation of the EXPLAIN fields.

  1. Id: The ids with large ids have higher priorities. The ids with the same id are executed in top-down order.
  2. Select_type: specifies the query type.
    • SIMPLE: A SIMPLE query that does not contain subqueries or unions.
    • PRIMARY: indicates the outermost query.
    • SUBQUERY: SUBQUERY in SELECT or WHERE.
    • DERIVED: Subquery FROM
    • UNION: Query after UNION.
    • UNION RESULT: Query to get results from UNION.
  3. Table: Which table this row is about.
  4. Type: indicates the query type. The following performance ranges from good to poor.
    • System: A table with only one row of data. This is common in system tables.
    • Const: Occurs on a primary key or unique index where the field matches a constant.select * from t1 where id = 1;
    • Eq_ref: occurs on a primary key or unique index,select * from t1,t2 where t1.id = t2.id;, for each row in T1, t2 will only have 1 row match, so if it is found, break will not look back.
    • Ref: occurs on a non-unique index,select * from t1,t2 where t1.name = t2.name;For each row in T1, T2 has multiple rows matching, so the end of the table must be found.
    • The range:select * from t1 where id between 1 and 100;, range scan on index, comparison operator, IN is also range scan.
    • Index:select id from t1;Traversing the index tree, the data is retrieved from the index because the index is in memory and the index file is smaller than the data file.
    • ALL:select * from t1;Full table scan on data files.
  5. Possible_keys: Possible index
  6. Key: the actual index used.
  7. Ken_len: The maximum total length in bytes of the index field type used.
  8. Ref: Which columns or constants are used to match indexed columns.
  9. Rows: Find the approximate number of rows to read for the desired content.
  10. -sheldon: Extra information.
    1. Using filesort: Cannot use index sort.
    2. Using temporary: A temporary table is used to hold intermediate results when sorting query results. This is common for sorting and grouping.
    3. Using index: SELECT fields are available on the index.
CREATE INDEX idx_c1_c2 ONt1(c1,c2); Select * from c1 where c1 = c1;Using filesort;
SELECT c1 FROM t1 WHERE c1 = 1 ORDER BY c2; # order byC1 [,c2] resolve # to use c1 lookup on composite index, but grouping does not use index,Usingtemporary; Create temporary table for sorting after grouping;SELECT c1 FROM t1 WHERE c1 = 1 GROUP BY c2; # group byC1 [, c2] is solvedCopy the code

Index of the failure

  1. The filtering condition does not satisfy the left prefix rule.
  2. Index columns have calculations, functions, or implicit type conversions.
  3. Range lookups invalidate subsequent index columns.
  4. Using the! =,<>Comparison operator.
  5. Using theis null,is not null.
  6. Using the%like%,%like, butlike%Can be equivalent to range lookup.
  7. The string is unquoted, and an implicit type conversion has occurred.
  8. Using theorThe keyword.

Summary: Just understand that an index is an ordered structure. Compound index is multi-field sort, which can not use ordered data structure to improve efficiency, will lead to index failure.

Example: CREATE INDEX idx_T1_C1C2C3 ON T1 (C1, C2,c3); (1,3,7) (1,5,3) (1,5,4) (2,1,4) (3,4,3) (3,5,6) (3,7,4) (3,7,4) (3,7,4) But c2 or C3 or C2C3 or C1C3 doesn’t work. Where c1 = 1 and c2 > 1 and c3 = 1; The C1 and C2 indexes are valid, but the C3 index is invalid. Because C2 is a range lookup, the C3 field is unordered in this condition. This is equivalent to looking for the c3 fields of (1,3,7) (1,5,3) (1,5,4).

Small tables drive large tables

select * from A where id in (select id fromB); Is equivalent tofor select id from B
	for select * from A where A.id = B.id

select * from A where exists (select 1 from B where B.id =A.id); Is equivalent tofor select * from A
	for select * from B where A.id = B.id
Copy the code

When the data set of table B is smaller than the data set of table A, it is better to use IN and vice versa.

The sorting

SQL SQL has Using index and Using filesort, you should try to use index sort. Note that sorting on an index requires multiple columns to rise or fall together, and the rest is similar to index failure.

File sorting is classified into dual-path sorting and single-path sorting. Dual-path sorting before MySQL 4.1 scans disks twice and performs TWO I/O operations. The single-path sort is performed in the buffer, with only one IO operation.

However, if the amount of sorted data is larger than the buffer size, it will cause more I/O operations. So the sorting optimization can be divided into three points: ① try to avoid select * multiple fields will lead to cache overflow; ② Increase the size of server parameter sort_buffer_size; ③ Increase the size of the server parameter max_LENGTH_FOR_sort_data (if the amount of data exceeds this value, use dual-path sorting).

The same goes for GROUP BY, because groups need to be sorted before they can be grouped, and not in a HAVING clause if they can be written in a WHERE clause.

Slow Query logs

  1. Check whether the function is enabled.
SHOW VARIABLES LIKE '%slow_query_log%';
Copy the code
  1. Enable slow log query
SET GLOBAL slow_query_log = 1;
Copy the code
  1. Check the slow query threshold
SHOW VARIABLES LIKE '%long_query_time%';
Copy the code
  1. Example Change the slow query threshold
You can't see the change until you open a new sessionSET GLOBAL long_query_time = 3;
Copy the code
  1. View the current number of slow query records
SHOW GLOBAL STATUS LIKE '%Slow_queries%';
Copy the code
  1. The configuration file
[mysqld] slow_query_log is permanently modified only if it is written to the configuration file= 1;
slow_query_log_file = /var/lib/mysql/slow.log;
long_query_time = 3;
log_output = FILE;
Copy the code
  1. Log analysis tool mysqlDumpslow
# return the largest number of logs10asql
mysqldumpslow -s r -t 10 /var/lib/mysql/Slow.log # returns the most visited log10asql
mysqldumpslow -s c -t 10 /var/lib/mysql/Slow.log # returns the first in chronological order10The bar contains the left-join query statement mysqlDumpslow-s t -t 10 -g "left join" /var/lib/mysql/slow.log
Copy the code

show profile

MySQL provides an analysis of resource consumption for SQL execution in the current session.

  1. Check whether the function is enabled.
SHOW VARIABLES LIKE 'profiling';
Copy the code
  1. open
SET profiling = ON; Run after it is enabledsqlstatementsCopy the code
  1. View the results
# displays everything executed after it is enabledsqlstatementsSHOW PROFILES;
Copy the code
  1. Diagnosis of SQL
SHOW PROFILE cpu,block io FORquery Query_ID; # Query_ID isSHOW# other parameters obtained in PROFILESALL | BLOCK IO | CONTEXT SWITCHES | CPU | IPC | MEMORY | PAGE FAULTS | SOURCE |Optimization is required if the diagnosis contains the following items. converting HEAPtoMyISAM query result is too large, not enough memory data is put on disk Creating TMPtableCreated temporary tables (new copy then delete)to tmp table onThe temporary table in disk memory was copied to disk lockedCopy the code