How to understand MySQL database optimization

Partitioning allows multiple parts of a single table to be allocated across the file system according to specified rules. Different parts of the table are stored as separate tables in different locations.

SQL and Index Optimization

How to find problematic SQL?

##              

Use MySQL slow check log to monitor SQL with efficiency issues

show variables like 'slow_query_log'

set global slow_query_log_file = '/home/mysql/sql_log/mysql-show.log'

set global log_queries_not_using_indexs = on;

set global long_query_time = 1

Slow check log storage format

# Time: 140606 12:30:17

// 执行SQL的主机信息
# User@Host: root[root] @ localhost[]

// SQL的执行信息
# Query_time:0.000031 Lock_time:0.000000 Rows_sent: 0 Rows_examined:0

// SQL 执行时间
SET timestamp = 1402029017

// SQL的内容
select CONCAT('storage engine:',@@storage_engine) as INFO;

Slow query log analysis tool

- mysqldumpslow

- pt-query-digest

How to find problematic SQL through slow query logs?

1. SQL that has many queries and takes a long time for each query

is usually the first few queries analyzed by pt-query-digest

2.SQL with large IO

Pay attention to the Rows examine item in the pt-query-digest analysis

3.SQL that misses the index

Pay attention to the comparison between Rows examine and Rows Send in pt-query-digest analysis

Related recommendations:

mac uses the terminal to run mysql, mysql terminal, mysql mac, mysql directory, mysql path, macmysql

Mysql partition|mysql partition table|mysql partition|by range

The above is the detailed content of How to understand MySQL database optimization. For more information, please follow other related articles on the PHP Chinese website!