Performance optimization ideas

• First, you need to use the Slow Query Log function to obtain all SQL statements that take a long time to query

• View the execution plan to view the execution plan of the SQL in question

• Optimize for SQL statements with slow queries

• Use [show profile[s]] to view the performance usage of the SQL in question

• Adjust OS parameters for optimization

• Upgrading Server Hardware

Slow Query logs

Slow query log description

Database query speed is a major factor affecting project performance, for the database, we in addition to optimize SQL, more important is to find the NEED to optimize SQL. MySQL database has a “slow query log” function, which is used to record SQL sentences whose query time exceeds a certain set point. This greatly helps me to locate the crux of the problem quickly so that I can solve the problem. As for how slow the query time is, every project and business has different requirements

The slow query log function of MySQL is disabled by default. You need to manually enable it

Enable the slow query function

Check whether the slow query function is enabled

show variables like '%slow_query%';

show variables like 'long_query_time%';

• Parameters that

  • [slow_query_log] : indicates whether slow query logs are enabled. 1 indicates that slow query logs are enabled, and 0 indicates that slow query logs are disabled.

  • Log-slow-queries: indicates the log storage path of the MySQL database slow query earlier versions (version 5.6 and later). If you do not need to set this parameter, the system uses the default host_name-slow.log file by default

  • Slow-query-log-file: new in version 5.6 or later, the slow query log path is stored in the MySQL database. If you do not need to set this parameter, the system uses the default host_name-slow.log file by default

  • [long_query_time] : indicates the slow query threshold. When the query time exceeds the specified threshold, a log is generated, in seconds.

The slow query function is enabled temporarily

SQL statement Settings are executed in MySQL, but will fail if MySQL is restarted

set global slow_query_log = ON; 

set global long_query_time = 1;
Copy the code

The slow query function is permanently enabled

Change the /etc/my.cnf configuration file and restart MySQL. This will take effect permanently

[mysqld] 
slow_query_log=ON 
long_query_time=1
Copy the code

Slow query log format

Format description:

• The first line is the time when the SQL query is executed

• The second line, execute SQL query for connection information, user and connection IP

• The third line records some useful information for us

  • Query_time, the execution time of this SQL, the longer it is, the slower it is

  • Lock_time, wait for table lock time in MySQL server phase (not storage engine phase)

  • Rows_sent, query the number of rows returned

  • Rows_examined, the longer the number of lines examined, the more time it takes

• The fourth line sets the timestamp, which has no practical meaning, but corresponds to the execution time of the first line

• On line 5 and all subsequent lines (the second # Time: before), the SQL statement executed records the information, because the SQL can be very long

Tool for analyzing slow query logs

Use the mysqlDumpslow tool, which is MySQL’s own slow query logging tool. You can use the mysqlDumpslow tool to search for SQL statements in slow query logs

Get the top 10 queries in chronological order that contain the left join

mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow.log

Common Parameters

• -s: indicates the sorting mode

Al Avg. Lock time AR Avg. Returned record time AT Avg. Query time (default) C Count L Lock time r Returned record t Query timeCopy the code

• -t: indicates top N, that is, the number of the previous columns of data to be returned

• -g: a regular matching pattern can be written after it, which is case-insensitive

Viewing the Execution Plan

introduce

MySQL provides an EXPLAIN command that analyzes the execution plan of SELECT statements and outputs details of SELECT execution for developers to optimize

Use the Explain command to view an execution plan for one of these SQL statements, to see if the SQL statement has used an index, and to see if a full table scan has been done, which can be viewed using the Explain command

You can use the Explain command to gain insight into MySQL’s overhead based optimizer, as well as to get a lot of detail about the access policies that the optimizer might consider, and which policies are expected to be adopted by the optimizer when running SQL statements

The EXPLAIN command is very simple to use. It simply precedes the SELECT statement with EXPLAIN

Build table statements

create table tuser( id int primary key auto_increment, name varchar(100), age int, sex char(1), address varchar(100) ); alter table tuser add index idx_name_age(name(100),age); alter table tuser add index idx_sex(sex(1)); Insert into Tuser (id,name,age,sex,address) values (1,'zhangsan',20,'1',' Beijing '); Insert into Tuser (id,name,age,sex,address) values (2,'lisi',16,'1',' Shanghai '); Insert into Tuser (id,name,age,sex,address) values (3,'wangwu',34,'1',' 2 '); Insert into Tuser (id,name,age,sex,address) values (4,'wangxin',26,'2',' guangzhou '); Insert into Tuser (id,name,age,sex,address) values (5,'wudi',18,'2',' Shanghai ');Copy the code

Parameters that

explain select * from tuser where id = 2 \G

id: 1

select_type: SIMPLE

table: tuser

partitions: NULL

type: const

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: const

rows: 1

filtered: 100.00

Extra: NULL

1 row in set, 1 warning (0.01 sec)
Copy the code

The meaning of each column is as follows:

Id: indicates the id of the SELECT query. Each SELECT is automatically assigned a unique identifier.

Select_type: SELECT query type.

Table: Indicates the table to be queried

Partitions: matches partitions

Type: the join type

Possible_keys: possible index in this query

Key: indicates the exact index used in the query.

Ref: Which field or constant is used with the key

Rows: Shows how many rows were scanned for this query. This is an estimate.

Filtered: indicates the percentage of data filtered by this query condition

-Blair: Extra information

id

The SELECT statement of each unit query automatically assigns a unique identifier that indicates the order in which tables are operated on in the query. There are four cases

• Same ID: Execution sequence from top to bottom

• Different ids: For sub-query, the ID is automatically increased. A larger ID indicates a higher priority

• A different simultaneous existence with the same ID

Select_type (Critical)

The query type of unit query, such as common query, union query (union, union all), subquery, and other complex queries

• simple

  Represents a simple SELECT query that does not require a union operation or contains no subqueries. When there is a connection query, the outer query is simple

• primary

  The select_type of the outermost unit query is primary for a select that requires a union operation or that contains subqueries

• union

  Select from dervied (select_type); select from dervied (select_type)

  • DERIVED The subqueries contained in the FROM list are labeled DERIVED, and MySQL executes these subqueries recursively, putting the results in temporary tables

  • UNION If the second SELECT appears after UNION, it is marked as UNION: If UNION is included in the subquery of the FROM clause, the outer SELECT is marked as: DERIVED

  • UNION RESULT SELECT the RESULT from the UNION table

• dependent union

  As with union, it appears in a union or union all statement, but this query is affected by an external query

• union result

  The result set containing the union, in the union and Union all statements, because it does not participate in the query, the ID field is null

• subquery

  Except for the subquery contained in the FROM clause, any subquery that appears elsewhere may be a subquery

• dependent subquery

  Similar to the dependent Union, this subQuery query is affected by external table queries

• derived

  Subqueries that appear in the FROM clause are also called derived tables, which in other databases may be called inline views or nested select

table

The unit query table name is displayed in the following situations

• If the query uses an alias, the alias is shown here

• This displays as NULL if no operation on the data table is involved

• If the table is enclosed in Angle brackets, it is a temporary table, and the following N is the ID of the execution plan, indicating that the result was generated from the query

• <union M,N>, enclosed in Angle brackets, is similarly a temporary table, indicating that the result is from the result set M,N of the union query

partitions

Which partitions are used (null for non-partitioned tables)

Versions after 5.7 have default partitions and filtered columns, but version 5.6 does not

Use Explain partitions select… To display columns with partitions,

Use Explain Extended Select… To show the columns that are filtered

What is a partitioned table?

Mysql internally implements horizontal splitting of tables, where all data is still in one table, but physical storage is placed in different files according to certain rules. This is a function supported by mysql, the business code does not need to change.

Technical Status:

Some technical exchanges in the industry are also more of their own sub-database sub-table, rather than the use of partition table.

1) Partition table, partition key design is not flexible, if you do not go to the partition key, it is easy to appear full table lock

2) Once the volume of concurrent data, if the implementation of association in partitioned tables, it is a disaster

3) Divide the database and table by oneself, and control the business scene and access mode by oneself, controllable. Partition table, r & D write a SQL, are not sure how to play mysql, not too controllable

4) Pit of operation and maintenance

Type (important)

The connection type of the unit query, or access type, is displayed, with access performance descending from best to worst

system

const

eq_ref

ref

fulltext

ref_or_null

unique_subquery

index_subquery

range

index_merge

index

ALL
Copy the code

Matters needing attention

• All types except all can use indexes

• With the exception of index_Merge, only one index can be used for the other types

• Use rang at least

system

The table has only one row of data or is empty. Is equal to the system table, which is a const column. It doesn’t normally appear, so it can be ignored

Const (important)

When using a unique index or primary key and returning the equivalent WHERE condition that the record must be 1 row, type is usually const. Other databases are also called unique index scans

Eq_ref (Important)

Unique index scan. For each index key, only one record in the table matches it. Common for primary key or unique index scans

Ref (important)

A non-unique index scan, which returns all rows that match a single value, is essentially an index access, which returns all rows that match a single value. However, it may find multiple rows that match the criteria, so it should be a mixture of lookup and scan

fulltext

Note that full text index is a high priority. If both full text index and normal index exist at the same time, mysql preferentially uses full text index regardless of the cost

ref_or_null

Similar to the REF method, but with the addition of null comparisons. Not much actually

unique_subquery

Used for in-form subqueries in WHERE that return unique values that do not duplicate values

index_subquery

For in subqueries that use a secondary index or a list of IN constants, subqueries that may return duplicate values can be de-duplicated using the index

Range (important)

Index range scanning, common in queries using the >,<,is NULL,between,in,like operators, etc

index_merge

Select * from ref_or_NULL; select * from ref_or_NULL; select * from ref_or_NULL

Index (important)

If the index is used in the select result column, type is displayed as index

Full index scan, which sweeps the index from top to bottom, is common for queries that do not need to read data files using index columns, or queries that can be sorted or grouped using indexes

All (important)

This is the full table scan data file, and then filtered at the Server layer to return the records that meet the requirements

possible_keys

One or more indexes that may be selected in this query

key

If select_Type is index_merge, more than two indexes may appear. If other select_type is select_type, only one index may appear

key_len

Varchar (10) variable length field with NULL = 10 * (character set: Utf8 =3, GBK =2,latin1=1)+1(NULL)+2(variable length field) VARCHar (10) variable length field and NULL = 10 *(character set: Utf8 =3, GBK =2,latin1=1)+2(variable length field) char(10) fixed field allow NULL = 10 * (character set: Utf8 =3, GBK =2,latin1=1)+1(NULL) char(10) Utf8 =3, GBK =2,latin1=1) The length of bigINT is 8. The length of key_len is 4, the length of tinyINT is 1. The length of smallINT is 2Copy the code

The length of the index used to process the query is calculated. If it is a single-column index, the entire index length is counted. If it is a multi-column index, not all columns are counted

Take a look at the value of this column and calculate the total length of your multi-column index to see if you are using all columns

In addition, key_len only calculates the length of the index used by the WHERE condition, and sorting and grouping do not count to key_len if indexes are used

ref

• If the constant equivalent query is used, const is displayed here

• If it is a join query, the execution plan of the driven table shows the associated fields of the driven table

• If the condition uses an expression or a function, or if the condition column is implicitly converted, it might be shown as func

rows

This is the estimated number of rows scanned in the execution plan, not the exact value (InnoDB is not the exact value, MyISAM is the exact value, mainly because InnoDB uses MVCC concurrency).

filtered

Filtered columns indicate the estimated percentage of records returned by the storage engine layer that will be filtered by the mysql Server layer, that is, the percentage of results returned by the storage engine layer that contain valid records. The maximum value is 100, which means that no rows are filtered. A decrease in the value from 100 indicates an increase in the amount of filtering. Rows shows the estimated number of rows to be checked, and rows× Filtered shows the number of rows to be joined with the table below. For example, if rows is 1000 and filtered is 50.00 (50%), the number of rows to join with the table below is 1000×50%=500

Be extra

This column contains additional information that is important not to display in other columns, and there are dozens of different kinds of information that can be displayed in this column

Using filesort

Note mysql uses an external index sort for data, rather than reading data in the order of the indexes in the table. A sort operation in MySQL that cannot be done with an index is called “file sort”. SQL needs to be optimized

Using temporary

Instead of using temporary tables to hold intermediate results, MySQL uses temporary tables when sorting query results. Common in sort order by and group by queries. SQL needs to be optimized

Using index

The query results can be obtained directly through the index without going back to the table

• SELECT Index (Covering Index), avoid access to the table row, efficiency is good!

• If Using Where is also present, the index is used to perform the lookup index key value

• If Using Where is not present at the same time, the index is used to read data rather than perform lookup actions

Using where (important)

Indicates that Mysql filters the results extracted by the Storage Engine. The filtering condition field has no index.

Using join buffer

The join buffer in the configuration file is larger than the join buffer in the configuration file

impossible where

The value of the WHERE clause is always false and cannot be used to get any tuples

SELECT * FROM t_user WHERE id = '1' and id = '2'

SQL statement optimization (developer)

The index optimization

• Create appropriate indexes for search fields (conditions in WHERE), sort fields, and SELECT query columns, but consider the business scenario of the data: more queries or more additions and deletes?

• Try to establish composite indexes and pay attention to the creation order of composite indexes, organize query conditions in order, try to put the query conditions with large screening granularity on the left

• Use overridden indexes and avoid * in SELECT statements

• Order by and group BY statements should use indexes as much as possible

• The index length should be as short as possible. Short indexes can save index space, improve search speed, and load more index keys in memory. If the column is too long, you can choose to create a prefix index

• Index updates cannot be frequent. Data that is updated very frequently is not suitable for indexing because of the cost of index maintenance

• The order by index should follow the best left prefix query. If multiple index columns are sorted, the order by index must be the same (ascending or descending), otherwise the index will be invalid

LIMIT optimization

If the query result of the SELECT statement is expected to be one, it is better to use LIMIT 1 to stop full table scanning

LIMIT is used for pagination, and it can be very inefficient when the offset is very large when the page is turned too far back. LIMIT OFFSET, SIZE;

The LIMIT optimization problem is OFFSET, which causes MySql to scan a large number of rows that are not needed and then discard them

Select * from ‘where id > offset’, ‘limit’, ‘rows’,’ select * from ‘where id > offset

select * from (select * from tuser2 where id > 1000000 and id < 1000500 ORDER BY id) t limit 0, 20

Other query optimizations

• Small tables drive large tables. It is recommended to associate small tables with large tables when using Left JOIN, because the first table must be fully scanned, and less association can reduce the number of scans

• Avoid full table scan, mysql does not equal (! The index cannot be used when = or <>), causing a full table scan. During the query, if the index is not equal to the operation, the index will be invalid, and the whole table scan will be performed

• Avoid mysql dropping index queries. If mysql estimates that a full table scan is faster than an index, do not use the index. (The most typical scenario is when the amount of data is low)

• JOIN The associated fields of two tables should be indexed and of the same type

• Do not use not in statements in WHERE conditions (not exists is recommended)

• Use slow query logs, explain execution plan queries, and show profile to view resource usage during SQL execution

Profile analysis statement

introduce

Query Profiler is a diagnostic tool for MySQL. It can analyze the hardware performance bottlenecks of a SQL statement

Normally, explain and slow Query logs cannot be used for precise analysis, but Query Profiler can locate the resource consumption of an SQL statement, such as CPU, IO, and time spent executing the SQL statement. However, this tool is only available in MySQL 5.0.37 and above

This function is disabled by default. You need to manually enable it

Enabling the Profile function

• The Profile function is controlled by the MySQL session variable: profiling, which is OFF by default

• Check whether the Profile function is enabled

  select @@profiling; -- show variables like '%profil%';Copy the code

• Enabling the Profile function

  set profiling=1; --1 indicates on, 0 indicates off

Statements use

• The show Profile and Show Profiles statements show resource usage for executing statements in the current session (profiling is reset to 0 after exiting the session)

• Show Profiles: Shows resource usage in a list of statements recently sent to the server for execution. The number of records displayed is controlled by the profiling_history_size variable. The default number is 15

• Show profile: displays detailed resource usage information of the latest statement execution. The default columns are Status and Duration

• The show profile can also display performance analysis information about a record based on the Query_ID in the Show Profiles list

  

  Type is optional. The value can be:

  • ALL Displays ALL performance information

  • BLOCK IO Displays the number of BLOCK I/O operations

  • CONTEXT SWITCHES show the number of CONTEXT SWITCHES, whether active or passive

  • CPU Displays the user CPU time and system CPU time

  • IPC displays the number of messages sent and received

  • MEMORY [not yet implemented]

  • PAGE FAULTS displays the number of PAGE errors

  • SOURCE displays the function name and location in the SOURCE code

  • SWAPS shows the number of SWAPS

Server level optimization

Buffer optimization

Data is stored in memory to ensure that data is read from memory

• Set innodb_buffer_POOL_size large enough to read data into memory

  It is recommended that innodb_buffer_POOL_size be set to 3/4 or 4/5 of the total memory size

Reduces the number of disk writes

• In the production environment, you do not need to enable many logs, such as general query logs, slow query logs, and error logs

• Use a write cache large enough innodb_log_file_size

  The recommended value for innodb_log_file_size is 0.25 * Innodb_buffer_pool_size

• Set the appropriate innodb_flush_log_at_trx_COMMIT, which is related to log shedding

MySQL database configuration is optimized

• Represents buffer pool size in bytes

  The recommended value is 50% to 80% of the physical memory

  innodb_buffer_pool_size

• The policy used to control redo log flushing to disk

  innodb_flush_log_at_trx_commit=1

• The value can be set to n for each transaction synchronization committed to disk

  sync_binlog=1

• The ratio of dirty pages to innodb_buffer_POOL_size triggers a dirty page brush to disk. The recommended value ranges from 25% to 50%

  innodb_max_dirty_pages_pct=30

• Maximum I/O performance indicator of background processes

  The default value is 200. If SSD is used, the value ranges from 5000 to 20000

  innodb_io_capacity=200

• Specifies the size of innoDB shared tablespace files

  innodb_data_file_path

• Set the threshold for slow query logs, in seconds

  Long_qurey_time = 0.3

• Row is the default form for MySQL8.0

  binlog_format=row

• If you increase this parameter, lower the values of interactive_TIMEOUT and wait_timeout

  max_connections=200

• Too large, the instance recovery time is long; If the number is too small, log switching is frequent

  innodb_log_file_size

• You are advised to disable full logs

  Off by default

general_log=0

Operating system Optimization

Kernel parameter optimization

The CentOS system is optimized for mysql parameters

Kernel parameters (/etc/sysctl.conf)

The following parameters can be placed directly at the end of the sysctl.conf file.

1. Increase the upper limit of listening queues:

net.core.somaxconn = 65535

net.core.netdev_max_backlog = 65535

net.ipv4.tcp_max_syn_backlog = 65535

2. Reclaiming TCP connections faster:

net.ipv4.tcp_fin_timeout = 10

net.ipv4.tcp_tw_reuse = 1

net.ipv4.tcp_tw_recycle = 1

3. Default and maximum values for TCP connection receive and send buffer sizes:

net.core.wmem_default = 87380

net.core.wmem_max = 16777216

net.core.rmem_default = 87380

net.core.rmem_max = 16777216

4. Reduce the number of TCP resources occupied by failed connections and accelerate the efficiency of resource recovery:

net.ipv4.tcp_keepalive_time = 120

net.ipv4.tcp_keepalive_intvl = 30

net.ipv4.tcp_keepalive_probes = 3

5. Maximum value of a single shared memory segment

kernel.shmmax = 4294967295

1. This parameter should be set large enough to fit the entire Innodb buffer pool in one shared memory segment.
2. For 64-bit Linux systems, the maximum value is (physical memory value -1)byte. It is recommended that the value be greater than half of the physical memory. Generally, the value is greater than the size of the Innodb buffer pool

6. Control the relative weight of memory when switching out and out: vm.swappiness = 0

This parameter can have a significant impact on performance when memory is low. (If the value is set to 0, the Linux kernel virtual memory is fully occupied.)

Linux memory swap:

Every Linux installation has a special partition called the system swap partition.

Use the free -m command to see that swap is the memory swap area.

What it does: When the operating system does not have enough memory, it writes some of the virtual memory to the swap area of the disk

Risks associated with completely disabling swap partitions on a Linux system:

1. Reduces the performance of the operating system

2. Memory may overflow, crash, or be killed by the operating system

Increase resources

Limit on the number of open files The following parameters can be placed at the end of the (/etc/security/ limite.conf) file:

* soft nofile 65535

* hard nofile 65535

* : this parameter is valid for all users

Soft: indicates the Settings that take effect in the current system. (Soft cannot be greater than hard.)

Hard: indicates the maximum value that can be set in the system

Nofile: indicates that the limited resource is the maximum number of open files

65535: Limit number

The above two lines increase the number of open files to 65535 to ensure that enough file handles can be opened

Note: Changes to this file require a system reboot to take effect

Disk Scheduling Policy

1. CFQ (fully fair queue policy, system default policy of Linux2.6.18 and later)

In this mode, multiple queues are created for each process. The I/O requests sent by each process are processed by CFQ in a round-robin manner, which is fair to each I/O request. This policy is suitable for discrete read applications.

2. Deadline

Deadline, which contains both read and write queues, ensures that requests are served within a deadline (the deadline is adjustable), and the default read deadline is shorter than the write deadline. This prevents write operations from starving to death because they cannot be read. Deadline is the best choice for database applications.

3. Noop (Elevator Scheduling Strategy)

Noop implements only a simple FIFO queue and tends to starve read in favor of write, so noOP is the best choice for flash devices, RAM, and embedded systems.

16. Anticipatory I/O scheduling policy

Essentially the same as the Deadline policy, but after the last read operation, there is a wait of 6ms before scheduling of other I/O requests can proceed. It inserts new I/O operations at every 6ms, merges write flows, and trades write latency for maximum write throughput.

16 Anticipatory is suitable in write-heavy environments, such as file servers. This policy performs poorly in a database environment.

Methods of viewing scheduling policies:

cat /sys/block/devname/queue/scheduler

The method of modified scheduling policy: echo/sys/block/devname/queue/scheduler

Server hardware optimization

Improve hardware devices, for example, select the memory frequency as high as possible (not higher than that supported by the mainboard), increase network bandwidth, use SSD high-speed disks, and improve CPU performance

CPU selection:

For high database concurrency scenarios, the number of cpus is more important than the frequency.

For CPU-intensive scenarios and scenarios where complex SQL is frequently executed, the higher the CPU frequency, the better.