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
- This parameter should be set large enough to fit the entire Innodb buffer pool in one shared memory segment.
- 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:
-
Reduces the performance of the operating system
-
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.