About MySQL Optimization

The idea of ①mysql optimization
1,sql statement optimization and index optimization
2, Optimization of database table structure
3, System Configuration
4, hardware optimization

Ii
View slow query log status: Show variables like ' Slow_query_log '
If the result above is off, turn on the slow query log: Set global Slow_query_log=on

Log_queries_not_using_indexes indicates that a query that does not use a key is logged. Set to on to open.
Long_query_time=1 indicates that more than 1s of queries will be logged.

Install Pt-query-digest:
wget http://www.percona.com/get/pt-query-digest
chmod u+x Pt-query-digest
Move to a directory such as/usr/bin/.

Pt-query-digest this is better.

③ SQL statements to be aware of
1) SQL with many queries and a long time consuming each query
Usually the third part of the Pt_query_digest analysis, the first few
2) IO-Large SQL
Note the rows examine item in the Pt_query_digest analysis
Most bottlenecks in a database are IO bottlenecks
3) Missing SQL for index
Note the comparison of rows examine and rows send in the Pt_query_digest analysis

④
Mysql> Explain select Customer_id,first_name,last_name from customer;
Information for the table that returns the value
Table: List name;
Type: Types of connections, const, EQ_REG, ref, range, index, and All;const: Primary key, Index, Eq_reg: Primary key, index range lookup; Ref: Lookup for Join, Range: Scope lookup for Index ; index: scan of index;
Possible_keys: The index that may be used;
Key: the actual index used;
Key_len: The length of the index, the shorter the better;
Ref: Which column of the index is used and the constant is good;
Rows:mysql the number of rows that must be checked to return the requested data;
Extra:using Filesort, using temporary (often appearing when using order by) need to be optimized.
⑤ optimizations for Max () and Count ()
Max's query can be optimized by indexing: Create INDEX idx_paydate on payment (payment_date);
Overwrite Index
Use Max to query the entire table without creating an index
Use Max after the index is created, because the index is sorted by data size.

Optimizations for Max () and COUNT ()

1. For the max () query, you can create an index for the table, the CREATE INDEX index_name on table_name (COLUMN_NAME specifies the column to be indexed), and then the query

2.count () Queries for multiple keywords, such as detecting the number of movies in 2006 and 2007 simultaneously in a single SQL statement:

Select count (release_year= ' 2006 ' or null) as ' 2006 movie Number ',
Count (release_year= ' or null) as ' 2007 movie number '
from film;

The result of the 3.count (*) query contains the result that the column value is null

⑥ Sub-query optimization
Sub-query optimization into join
Subqueries do not have duplicate records.
Joiin If there is a one-to-many result there will be re-use distinct deduplication

Optimization of ⑦group by
The group by may have temporary tables (using temporary), file ordering (using Filesort), etc., affecting efficiency.
You can save IO by correlating subqueries to avoid temporary table and file sorting
Before rewriting

select actor.first_name,actor.last_name,count(*)from sakila.film_actorinner join sakila.actor using(actor_id)group by film_actor.actor_id;

After rewriting

select actor.first_name,actor.last_name,c.cntfrom sakila.actor inner join(select actor_id,count(*) as cnt from sakila.film_actor group byactor_id)as c using(actor_id);

Optimization method of ⑧limit
Limit is often used for paging, often accompanied by an ORDER BY clause, so most of the time using filesorts will cause a lot of IO problems
1. Using indexed columns or primary keys for order by operations, using primary key ordering will greatly improve the functionality
2. Record the last returned primary key and use primary key filtering at the next query
There is a restriction that the primary key must be sequential and sequential, and if the primary key appears to be empty, it may result in less than 5 listings on the final page, and the workaround is to append a column to ensure that the column is self-incremented and the index is increased.

⑨ Building an Index
Select the appropriate index column
1. Columns appearing in the Where,group by,order by,on clause
2. The smaller the index field, the better (because the storage unit of the database is the page, the more data you can save in a page, the better)
3. High dispersion is placed in front of the federated Index
Select COUNT (Distinct customer_id), COUNT (distinct staff_id) from payment;
View the degree of dispersion by counting the different column values to achieve a higher count of discretization

When an index contains all the columns in the query, it is called the Overwrite index

⑩ index-Optimized SQL method
Too many indexes affect not only writing but also query, the more indexes, the slower the analysis
How to find duplicate and redundant indexes, the primary key is already indexed, so Primay Key's primary key does not have to set a unique unique index anymore
Redundant index refers to the same prefix column for multiple indexes, and InnoDB automatically adds primary key information after each index

Redundant index query tool
Pt-duplicate-key-checker

Find Duplicate Index Selecta. Table_schema as ' data name ',a. table_name as ' table name,a. Index_name as ' index1', B.index_name as ' index2‘,a. column_name as ' repeating column name ' from STATISTICSaJOIN STATISTICS B ona. Table_schema = B.table_schema and a. table_name = B.table_name and a. Seq_in_index = B.seq_in_index and a. COLUMN_NAME = B.column_namewherea. Seq_in_index =1  and a. Index_name <> B.index_name

About table design features to be continued.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

About MySQL Optimization