preface

Let’s start with a picture

Directory figure

First, SQL statement optimization

  • Enable the slow query function

    Vim /etc/my.cnf [mysqld] slow-query-log = on slow_query_log_file = /data/slow-query.log Long_query_time = 5 log-queries-not-using-indexes = on # Lists the queries that do not use indexes

1) Show variables like ‘%quer%’;

2) Show variables like ‘show%’

  • Analyzing SQL statements

The MySql internal function explain (to query the execution plan of the SQL) returns the meaning of the columns

Type: This is the important column that shows what type the connection is using. The best to worst join types are const, eq_reg, ref, range, index, and ALL possible_keys: shows possible indexes that can be applied to the table. If empty, there is no possible index. Key: indicates the actual index. If NULL, no index is used. Keyjen: The length of the index used. Ref: Shows which column of the index is being used and, if possible, a constant rows: The number of rows MYSQL deems necessary to check to return the request dataCopy the code
  • Subquery optimization

Subquery should not be used or changed to JOIN

  • Group by optimization

Group by uses index fields whenever possible

  • Limit optimization

Add limit to query statement

Second, index optimization

  • Repeat index
  • Redundant indexes
  • A tool for checking duplicate and redundant indexes
  • Delete unnecessary indexes

Third, database structure optimization

  • Choose the appropriate data type

  • Formalization of tables

  • The anticanonical use of tables

  • Vertical split of tables

  • Table water row split

4. Configuration optimization

  • Operating System Configuration

1) Cache pool size

2) Open file limits

  • MySQL configuration

1) InnoDB buffer pool memory footprint

2) Innodb_buffer_POOL_instances Number of buffer pools

3) Innodb_log_buffer_size Buffer size

4) InnoDB IO configuration

5. Server hardware optimization

  • Multi-core CPU
  • Raid0 RAID1 RAID5 Increases the I/O speed of a disk

Refer to a list of articles:

MySql database optimization can be carried out from which aspects

www.cnblogs.com

] (link.zhihu.com/?target=htt…).