1 Common Problems

  1. blocking
  2. The slow query

2. We can start from the following aspects

  1. Server hardware optimization
  2. System Configuration Optimization
  3. 4) Database structure optimization
  4. SQL and indexes

Usually optimize 3 and 4

3 Slowly query logs

show variables like 'slow_query_log'; # Enable slow query logsset global slow_query_log=on; # query Slowly query the location of the log fileshow variables like 'slow_query_log_file'; Set the location of the slow query log fileset global slow_query_log_file= '/home/mysql/sql_log/mysql-slow.log'# set whether indexes will not be usedSQLThe logs are recorded in slow query logsset global log_queries_not_using_indexes=on; # set whether the query event exceeds0Of a secondSQLLog to slow query log, general production Settings0.01secondsset global long_query_time=0;
Copy the code

4 in field

4.1 Slow Log Query is disabled by default

4.2 Slowly Querying the Log Storage Location

Query log, here I am Docker container deployment MySQL, mapping on the server/data/mysqldirectory

4.3 Log Format

Line 1: Information about the host where the SQL is executed

Line 2: Execution information for SQL

Line 3: SQL execution events

Line 4: The contents of the SQL

5 Slowly query the log analysis tool

5.1 MysqlDumpslow (official tool)

5.1.1 Parameter Description

-s indicates the order. Al Average lock time AR Average return record time AT Average query time (default) C count L Lock time R Return record T Query time -t indicates the top N value, that is, the number of previous records to be returned. -g Indicates that a regular matching pattern can be written after it. Case insensitiveCopy the code

5.1.2 Basic Usage

# get the most recordsets returned10aSQL. mysqldumpslow-s r -t 10 /database/mysql/Mysql06_slow.log # gets the most accesses10aSQL
mysqldumpslow -s c -t 10 /database/mysql/Mysql06_slow.log # gets the first sorted by time10Bar contains the left join query statement. mysqldumpslow-s t -t 10 -G"left join"/database/mysql/Mysql06_slow.log # It is also recommended to combine these commands when using them|And more. Otherwise, the screen may be flooded. mysqldumpslow-s r -t 20 /mysqldata/mysql/mysql06-slow.log | more
Copy the code

5.2 pt – query – digest

5.2.1 Quick Installation

Wget https://www.percona.com/downloads/percona-toolkit/2.2.16/RPM/percona-toolkit-2.2.16-1.noarch.rpm && yum Localinstall -- y percona toolkit - 2.2.16-1. Noarch. RPMCopy the code

5.2.2 Parameter Description

Pt-query-digest [OPTIONS] [FILES] [DSN] --create-review-table When the --review parameter is used to output analysis results to a table, the table is automatically created if there is no table. --create-history-table When the --history parameter is used to output analysis results to a table, the table is automatically created if there is no table. The default value is 20. That is, the 20 slowest statements are output. If the total response time is 50%, the 20 slowest statements are output in ascending order by proportion of total response time. --host MYSQL server address --user mysql user name --password mysql user password --history Save the analysis result in the table, the analysis result is detailed, the next time use --history, if the same statement exists, If the query time range is different from that in the historical table, the data will be recorded in the data table. You can compare the historical changes of a certain type of query by querying the same CHECKSUM. Review saves the analysis results to the table. This analysis only parameterizes the query criteria. It is relatively simple to query one record per type. The next time you use --review, if the same statement analysis exists, it will not be recorded in the table. The value can be report(standard analysis report), slowlog(Mysql slowlog), JSON, or JSON-anon. Generally, report is used for easy reading. The value is a string of time in the format of YYYY-MM-DD [hh:mm:ss], s(seconds), h(hour), m(minute), or D (day). For example, 12h indicates that statistics were collected 12 hours ago. Since can analyze slow queries over a period of time.Copy the code

5.2.3 Basic Usage

# output to file pt-query-digest slow-log >Slow_log. report # outputs to the database-- create-reviewTable: outputs slow query logs to a table
pt-query-digest slow.log -review h=127.0. 01.,D=test,p=root,P=3306,u=root.t=query_review --create-reviewtable --review-history t= hostname_slow
Copy the code

6 SQL optimization

6.1 SQL Features to be Optimized

  1. SQL that has multiple queries and takes a long time for each query
  2. IO large SQL (the more rows scanned in SQL, the more I/O)
  3. SQL that did not match the index

6.2 the use ofexplainQuery the SQL execution plan

6.2.1 Explanation

tableThe type of the table in which this row of data is displayed this is the important column, showing what type the connection is using. The best to worst connection type is const,eq_reg,ref.range,index,ALL. Const is common in primary keys/Unique index lookups, eq_reg is common for primary key range lookups,refCommon in join queries,rangeThis is common for range lookup of indexes, index is common for scan of indexes,ALLCommon in table scanning possible_keys displays indexes that could be applied to the table. If empty, there is no possible index. Key Indicates the actual index used. If it isNULL, no index is used. Key_len Specifies the length of the index used. With no loss of accuracy, the shorter the length, the betterrefThe column showing the index is used, if applicable, as a constantrowsThe number of rows that MySQL considers necessary to check to return request dataCopy the code

6.3 SQL optimization

  1. Index frequently queried fields appropriately
  2. Avoid sub-query and optimize to join query. Pay attention to whether there is one-to-many relationship, which may lead to data duplication

6.4 How to Select a Proper Column to Build an Index

  1. The columns that appear in where, group by, order by, on clauses
  2. The smaller the index field, the better
  3. Columns with high dispersion precede the union index

Such as:

select * from payment where staff_id = 2 and customer_id = 584;
Copy the code

Since customer_id is more discrete, index(customer_id,staff_id) should be used

6.4.1 How to determine the dispersion of columns

select count(distinct customer_id),count(distinct staff_id) from payment
Copy the code

The higher the unique value of the column, the greater the dispersion and the higher the selectivity.

6.4.2 Index Maintenance and optimization

Duplicate and redundant indexes. Duplicate indexes refer to indexes of the same type created by the same columns in the same order. For example, duplicate indexes on parmary key and ID columns are duplicate indexes

create table test(id int not null primart key),name vachar(10) not null,title varchar(50) not null.unique(id) ) engine=innodb
Copy the code

Use the pt-duplicate-key-checker tool to check for duplicate and redundant indexes

pt-duplicate-key-checker -uroot -p' ' -h 127.0.0.1
Copy the code

7. Optimize database and table structure

7.1 Selecting an Appropriate Data type

  1. Use the smallest data type that can hold your data
  2. Using simple data types, int is easier to handle with MySQL than vARCHar
  3. Use not NULL to define fields whenever possible
  4. Use text as little as possible, and consider tables when you must

For example,

Bigint is used to store IP addresses, using INET_ATON() and INET__NTOA() functions for conversion

insert into sessions(ipaddress) values (INET_ATON('127.0.0.1'));
select INET__NTOA('127.0.0.1') from sessions;
Copy the code

7.2 Stereotype and antistereotype

7.2.1 Fan Shihua

  1. The first normal form emphasizes atomicity and requires attributes to be atomic and unresolvable

  2. The second normal form, which emphasizes primary keys, requires that records have a unique identity, that is, that the entity is unique and that there is no partial dependence on the level

  3. The third normal form, which emphasizes foreign keys, requires that no field can be derived from any other field, and requires that fields have no redundancy, that is, no dependency passing

7.2.2 Antistereotype

In order to consider the query efficiency, the tables that originally conform to the third normal form should be added with appropriate redundancy to achieve the purpose of optimizing the query efficiency.

7.3 Optimization of database structure

7.3.1 Vertical splitting of tables

Split a table with many columns into multiple tables. This solves the problem of table width. Usually, vertical splitting can be carried out according to the following principles:

  1. Store infrequently used fields in a separate table
  2. Keep large fields in a separate table
  3. Put together fields that are often used together

7.3.2 Horizontal splitting of tables

In order to solve the problem of large amount of data in a single table, the structure of each table in a horizontal split table is exactly the same

8 Optimize system configuration

The database is based on the operating system. At present, most MySQL is installed on Linux. Therefore, some parameters of the operating system may affect the performance of MySQL

8.1 Network configuration

Example Modify the /etc/sysctl.coonf file

#Example Add the number of queues supported by TCP
net.ip4.tcp_max_syn_backlog=65535
#Reduce disconnection when reclaiming resources
net.ipv4.tcp_max_tw_buckets=8000
net.ipv4.tcp_tw_reuse=1
net.ipv4.tcp_tw_recycle=1
net.ipv4.tcp_fin_timeout=10
Copy the code

The number of open file limit, you can use the ulimit – a directory of limitation, can modify the/etc/security/limits file, add the following content to modify opens the file number of restrictions

soft nofile 65335
hard nofile 65535
Copy the code

In addition, it is best to turn off iptables, Selinux and other firewall software on MySQL server

8.2 MySQL Configuration File

8.2.1 Configuration File Path

Linux: / etc/my CNF

8.2.2 parameters

Innodb_buffer_pool_size: a very important parameter for configuring innoDB buffer pools. If only InnoDB tables are in the database, it is recommended to configure 75% of the total memory

Innodb_buffer_pool_instances: a new parameter in MySQL5.5 that controls the number of cache pools. By default, there is only one cache pool

Innodb_log_buffer_size: The size of the innodb log cache, usually not too large because logs are refreshed at most every second

Innodb_flush_log_at_trx_commit: a key parameter, which has the greatest impact on INNODB I/O efficiency. The default value is 1. The value can be 0 or 1

Innodb_read_io_threads/innodb_write_IO_threads: Determines the number of INNODB PROCESSES that can read and write I/OS. The default value is 4

Innodb_file_per_table: a key parameter that controls innoDB not to use a single table space. The default value is OFF, meaning that all tables are created in a shared table space

Innodb_stats_on_metadata: Determine under what circumstances MySQL will refresh innoDB table statistics

8.2.3 Third-party Configuration Tools

Percon Configuration Wizard

9 Server hardware optimization

9.1 the CPU

Choose the right CPU, a single faster CPU

  1. MySQL can only use single core for some work
  2. MySQL does not support CPU cores as quickly as possible. MySQL5.5 should use no more than 32 cores

9.2 Disk I/O Optimization

This section describes common RAID levels

RAID0: Also known as stripe, which is used to connect multiple disks into one hard drive. This level of I/O is the best

“RAID1” : Also called mirroring, there must be at least two disks that store the same data in each group

“RAID5” : Combines multiple (at least three) hard disks into one logical disk. During data reads and writes, parity check information is created and stored on different hard disks. When data on a RAID5 disk is corrupted, the remaining data and corresponding parity information are used to recover the corrupted data.

RAID1+0: The combination of RAID1 and RAID0 has the advantages and disadvantages of both levels. It is generally recommended that databases use this level.

Check whether SNA and NAT are suitable for the database

  1. Often used in highly available solutions
  2. Sequential reads and writes are efficient, but random reads and writes are not
  3. Database random read and write efficiency is very high