MySQL Troubleshooter Introduction

original link: http://mrchenatu.com/2017/03/24/mysql-tool/

This summary is shared within the U.S. group, shielding internal data and tools

Knowledge Preparation Index

• An index is a data structure that the storage engine uses to quickly find records
• B-tree for full-key, key-value range or key-leftmost prefix: (a,b,c): A, AB, ABC,B,C,BC
• Which columns are recommended for index creation: WHERE, JOIN, GROUP by, and the columns used by statements such as ORDER by
• How to select the Order of indexed columns:
  1. Columns that are often used are preferred
  2. Selective high column precedence: Selective =distinct (col)/count (COL)
  3. Column with small width first: width = data type of column

Slow query Reason

1. Index not used
2. Index is not optimal
3. Poor server Configuration
4. Dead lock
5. ...

Command View version

MYSQL-V Client version Select Version Server version

Explain execution plan, slow query analysis artifact

• type

  • const,system: Matches up to one row, index
  • eq_ref: Returns a row of data, usually appearing on joins, using a primary key or unique index (inner table index connection type)
  • ref: Use the leftmost prefix of key, and key is not a primary key or unique key
  • range: Index range Scan, the sweep face of the index starts at a point, and the matching line is returned
  • index: Full table Scan in the order of the index, the advantage is not to sort, the disadvantage is that the full table scan
  • all: Full table Scan No no no

• Extra

  • Using index: Index overwrite, use only index, can avoid accessing table
  • Using where: Filtering after the storage engine retrieves rows
  • Using temporary: Use temporary tables, usually appearing when using group By,order by (Forbidden)
  • Using Filesort: Extra sort to non-indexed order when order by Col is not made to index (forbidden)
• Possible_keys: Displays the index that the query might use
• Key: The optimizer determines which index to use to optimize access to the table
• Rows:mysql estimates the number of rows to be retrieved to find the desired row, and optimizes the reference for key selection (not the number of rows in the result set)
• Key_len: The length (in bytes) of the left prefix of the index used, or what fields in the index are used
  • Fixed-length field, int is 4 bytes, date is 3 bytes, timestamp is 4 bytes, char (n) occupies n bytes
  • Null field: Requires 1 bytes, so the recommended design is not NULL
  • Variable-Length field varchar (n), you need (n encoded characters to occupy bytes + 2,) bytes, such as UTF8 encoded, characters
    is 3 bytes, then the degree is N 3 + 2
• Force index: Use index (recommended) or force_index (mandatory)

SHOW command

• show status
  • View the execution number of the SELECT statement Show global status like ' Com_select ';
  • View the number of slow queries show global status like ' Slow_queries ';
  • table scan Status show global status like ' handler_read% '; Handler_read_rnd_next/com_select > 4000 need to consider optimizing indexes
• Show variables
  • View slow query-related configurations show variables like ' long_query_time ';
  • Set the slow query timeline to 2s set global long_query_time=2;
  • View InnoDB cache show variables like ' innodb_buffer_pool_size ';
  • View the usage status of the INNODB cache show status like ' Innodb_bufferpool% '; Cache Hit Ratio = (1-innodb_buffer_pool_reads/innodb_buffer_pool_read_requests) 100%; Cache rate = (innodb_buffer_pool_pages_data /innodb_buffer_pool_pages_total)100%
  • SHOW PROFILES; This command can trace the consumption of resources throughout the execution (session level)
  • SHOW processlist; See which threads are currently running and in what state
  • SHOW ENGINE INNODB STATUS; can be used to analyze deadlocks, but requires Super permissions

MySQL Troubleshooter Introduction