MySQL Performance analysis and optimization

The limit of the hash index

Hash index must be searched two times

Hash index cannot be used for sorting

A hash index does not support partial index lookups, nor does IT support range lookups

Hash code calculation in hash index may have a hashing conflict

Why to use an index

Indexes significantly reduce the amount of data that the storage engine needs to scan

Indexes can help us sort to avoid using temporary tables

Indexes can turn random I/O into sequential I/O

Index optimization Strategy

An expression or function cannot be used on an indexed column

Selectivity of prefix index and index column, index selectivity is the ratio of non-repeating index values to the number of records in the table

Federated Index

How to select the Order of indexed columns

Columns that are often used are preferred

Selective high column Precedence

Column with small width takes precedence

Overwrite Index

Advantages:

Cache can be optimized to reduce disk IO operations

can reduce random io, change random io operation into sequential IO operation

Two queries against InnoDB primary key index can be avoided

MyISAM tables can be avoided for system calls

You cannot use the Overwrite index condition:

The storage engine does not support overwriting indexes

Too many columns were used in the query

A like query that uses a double% number

Using indexes to refine queries

Using index scans to optimize sorting

by sort operation

Scan data in index order

The column order of the index and ORDER BY clause are exactly the same

The direction of all columns in the index (ascending, descending) and the ORDER BY clause are exactly the same

The fields in Order by are all in the first table in the associated table

Simulation Hash Index optimization Query

All-value matching lookups can only handle key values

The hash function used determines the size of the index key

To remove duplicate and redundant indexes

Find indexes that have not been used

Update index statistics and reduce index fragmentation

Analyze Table table_name

Improper use of Optimize table table_name will result in a lock table

How to obtain SQL for performance issues

Get SQL that has performance issues with user feedback

Get SQL with performance issues by slow-track logs

Real-time access to SQL that has performance issues

Use slow query logs to get SQL for performance issues

Slow_query_log start stop logging slow scan log set global

Slow_query_log_file Specify the storage path and file for the slow query log (log store and datastore are stored separately)

LONG_QUERY_TIME Specifies the logging of slow log SQL execution time (records all eligible SQL)

Common Slow query Log analysis tool (Mysqldumpslow)

Summarize the exact same SQL except for the query criteria and output the results in the order specified in the parameters

Mysqldumpslow-s r-t Slow-mysql.log

-S Order (C,t,l,t,at,al,ar) specifies which sort of output results

C: Total number of times

T: Total time

L: Time of Lock

R: Total Data rows

At,al,ar:t,l,r Average

　　

-T top specifies to take the first few as the end output

　　

Frequently used slow-scan log analysis tool (Pt-query-digest)

Pt-query-digest \

--explain H=127.0.0.1,u=root,[email protected] \

Slow-mysql.log

MySQL Performance analysis and optimization