Getting started with MySQL is simple-learning notes-Chapter 18th Performance Optimization

ArticleDirectory

• 18.2.1. Analysis query statement
• 18.2.2. Index
• 18.3.1. Split tables with many fields into multiple tables.
• 18.3.2. Add an intermediate table.
• 18.3.3 add redundant Fields
• 18.3.4. optimized the insert record speed.
• 18.3.5, analysis, check and optimization table
• 18.4.1. Optimize server hardware
• 18.4.2. Optimize MySQL Parameters

18.1 optimization Overview

Show status like 'value ';

 

Connections connections

Uptime Start Time

Slow_queries slow Query Count

Com_select query operation count

Com_insert insert operation count

Com_update update operation count

Com_delete delete operation count

18.2 Optimize Query 18.2.1 and analyze query statements

Explain/DESC select;

 

Type: Connection Type

There is only one record in the system table.

The const table has multiple records, but only one record is queried from the table.

All scans the table completely.

Eq_ref indicates that the unique or primarykey is used for the subsequent tables when multiple tables are connected.

Ref indicates that when multiple tables are queried, the following tables use normal indexes.

Unique_subquery indicates that the subquery cooperates with unique or primary key.

Index_subquery indicates that the common index is used in the subquery.

Range indicates the query range.

Index indicates that the index in the table is completely scanned.

 

Possible_key indicates the indexes that may be used in the query.

Key indicates the index used for query.

 

18.2.2. Index

1. Take a single column Index

2. Multi-column Index

3. query without Indexing

Like starting with % does not go

Or one of the columns on both sides does not create an index and does not leave the index.

The first field of the Multi-column index is not used.

 

18.3 optimize the database structure 18.3.1, split tables with many fields into multiple tables 18.3.2, add Intermediate tables 18.3.3, and add redundant Fields

Anti-paradigm

Space Change Time

18.3.4. optimized the insert record speed.

1. Disable Indexing

Altertable table disable/enable keys;

2. Disable unique Indexes

Stunique_check = 0/1

3. Optimize insert statements

USE insert into table (F1, F2 .... FN) values (V1, V2 .... VN ),

(F1, F2 .... FN) values (V1, V2 .... VN ),

(F1, F2 .... FN) values (V1, V2 .... VN ),

...

Replace multiple insert into statements

 

18.3.5, analysis, check and optimization table

Analyzetable Table1 [, Table2…]

Checktable Table1 [, Table2…]

Optimize table Table1 [, Table2…]

Optimize text fields to eliminate the fragmentation caused by update operations and reduce space waste.

 

18.4 optimize MySQL Server 18.4.1 and server hardware

CPU

Disk, array

Memory

Configuration (dedicated server, large memory configuration)

18.4.2. Optimize MySQL Parameters

My. ini