Shulou(Shulou.com)05/31 Report--

This article introduces how to solve the slow query speed in MySQL. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

1. What affects the speed of database query

1.1 four factors that affect the speed of database query

1.2 risk analysis

QPS: QueriesPerSecond means "query rate per second". It is the number of queries per second that a server can make. It is a measure of how much traffic a particular query server handles within a specified period of time.

TPS: is an acronym for TransactionsPerSecond, that is, transactions per second. It is the unit of measurement of software test results. The client starts the timing when sending the request and ends the timing after receiving the response from the server to calculate the time used and the number of transactions completed.

Tips: it is best not to back up the database on the main library and cancel such a plan before a large event.

Inefficient sql: ultra-high QPS and TPS.

A lot of concurrency: the number of data connections is full (max_connection defaults to 100, which is generally set to a larger number). Concurrency: the number of requests processed by the database server at the same time

Ultra-high CPU utilization: downtime occurs when CPU resources are exhausted.

Disk IO: a scheduled task that suddenly degrades disk IO performance and consumes a lot of disk performance. Solution: faster disk devices, adjust scheduled tasks, and do a good job of disk maintenance.

1.3 Network card traffic: how to avoid being unable to connect to the database

Reduce the number of slave servers (slave servers copy logs from the master server)

Perform hierarchical caching (avoid a large number of cache invalidation at the front end)

Avoid using select* for queries

Separate business network from server network

1.4 problems caused by large tables (important)

1.4.1 Features of large tables

The number of rows of records is huge, and the number of single tables is over 10 million.

Table data files are huge, more than 10 gigabytes

1.4.2 hazards of large tables

1. Slow query: it is difficult to filter out the required data in a short period of time. Low differentiation of query words-> filtering out part of the data in a table with a large amount of data will result in a large amount of disk io-> reduce disk efficiency.

two。 Impact on DDL:

Indexing takes a long time:

MySQL-v=5.5 indexing will cause master-slave delay (mysql indexing, first executed on the group, then executed on the library)

Modifying the table structure requires a long time to lock the table: it will cause a long master-slave delay ('480 second delay')

1.4.3 how to deal with large tables on the database

To divide a large table into smaller tables.

Difficulties:

Selection of primary key of sub-table

Query and statistics of cross-partition data after sub-table

1.5 problems caused by major transactions (important *) *

1.5.1 what is a transaction

1.5.2 ACID attribute of the transaction

1. Atomicity: all succeeded and all rollback failed. Deposit and withdraw money from the bank.

2. Consistent: the total amount transferred by the bank remains unchanged.

3. Isolation (isolation):

Isolation level:

Uncommitted read (READ UNCOMMITED) dirty read, two transactions visible to each other

Committed read (READ COMMITED) conforms to the basic concept of isolation. When a transaction is in progress, other committed things are visible to the transaction, that is, the data committed by other transactions can be obtained.

The default isolation level of the REPEATABLE READ InnoDB. When a transaction is in progress, all other transactions are not visible to it, that is, multiple reads are performed, and the result is the same!

Serializable (SERIALIZABLE) locks on every row of data read, resulting in a large number of lock timeouts and lock requisitions. strict data consistency and no concurrency are available.

View the transaction isolation level of the system: show variables like'%iso%'

Start a new transaction: begin

Commit a transaction: commit

Modify the isolation level of things: setsession tx_isolation='read-committed'

4. DURABILITY: from the point of view of database persistence, disk corruption is not good.

Redolog mechanism ensures the consistency and persistence of transaction updates.

1.5.3 Major transactions

Transactions that take a long time to run and operate more data

Risks: locking too much data, long rollback time, and long execution time.

Locking too much data, causing a lot of blocking and lock timeouts

Rollback takes a long time, and the data is still locked

If the execution time is long, it will cause a master-slave delay, because only when the master server has finished writing to the log will the slave server start synchronization, causing a delay.

The solution is as follows:

Avoid processing too much data at one time, you can process it in batches

Remove unnecessary SELECT operations to ensure that there are only necessary write operations in the transaction.

What affects the performance of MySQL (very important)

2.1 several aspects that affect performance

Server hardware.

Server system (system parameter optimization).

Storage engine. MyISAM: transactions and table-level locks are not supported. InnoDB: support transactions, support row-level locks, transaction ACID.

Database parameter configuration.

Database structure design and SQL statement. (focus on optimization)

2.2 MySQL architecture

It is divided into three layers: client-> Service layer-> Storage engine

MySQL is a plug-in storage engine, in which there are many kinds of storage engines. As long as you implement the interface that meets the mysql storage engine, you can develop your own storage engine!

All cross-storage engine functions are implemented at the service layer.

MySQL's storage engine is for tables, not libraries. This means that different storage engines can be used in a database. But this is not recommended.

2.3 InnoDB Storage engine

The default storage engine for MySQL5.5 and later versions is InnoDB.

2.3.1 InnoDB uses tablespaces for data storage.

Show variables like'innodb_file_per_table

If innodbfileper_table is ON, a separate tablespace will be established with the file tablename.ibd

If innodbfileper_table is OFF to store data to the system's shared tablespace, the file is ibdataX (X is an integer starting at 1)

.frm: a file generated at the server level, similar to the data dictionary of the server layer, recording the table structure.

2.3.2 (MySQL5.5 default) system tablespaces and (MySQL5.6 and later default) independent tablespaces

1.1 the system tablespace cannot simply shrink the file size, resulting in a waste of space and a large number of disk fragments.

1.2 stand-alone tablespaces can shrink system files through optimeze table without the need to restart the server and will not affect normal access to the table.

2.1 if multiple tables are refreshed, it is actually done sequentially, resulting in IO bottlenecks.

2.2 independent tablespaces can refresh data to multiple files at the same time.

Strongly build independent tablespaces for Innodb, optimize what's more convenient and controllable.

2.3.3 the method of transferring tables from system tablespaces to independent tablespaces

1. Using mysqldump to export all database data (stored procedures, triggers, and scheduled tasks are all exported together) can be operated on the slave server.

2. Stop the MYsql server, modify the parameters (my.cnf joins innodbfileper_table), and delete Inoodb related files (you can rebuild the Data directory).

3. Restart MYSQL and rebuild the Innodb system tablespace.

4. Re-import data.

Or Altertable can also transfer, but can not reclaim the space occupied in the system tablespace.

2.4 Features of the InnoDB storage engine

2.4.1 feature 1: transactional storage engine and two special log types: Redo Log and Undo Log

Innodb is a transactional storage engine.

The ACID feature of transactions is fully supported.

There are two special log types required to support the transaction: RedoLog and UndoLog

Redo Log: enables transaction persistence (committed transactions). Undo Log: uncommitted transactions, independent of tablespaces, require random access and can be stored on high-performance io devices.

The Undo log records the value before the data is modified, which can be used to record the modified value of a data block when the transaction fails, and can be used to recover the data that has been successfully updated by the transaction that has not been written to the data file.

2.4.2 feature 2: support for row-level locks

InnoDB supports row-level locks.

Row-level locks can support concurrency as much as possible.

Row-level locking is implemented by the storage engine layer.

2.5 what is a lock

2.5.1 Lock

2.5.2 Lock type

2.5.3 granularity of locks

MySQL's transaction support is not tied to the MySQL server itself, but is related to the storage engine

Add table_name to table-level lock command: locktable table_name write; write lock blocks other users' read and write operations on the table until the write lock is released: unlock tables

The greater the overhead of the lock, the smaller the granularity, and the higher the degree of concurrency.

Table-level locks are usually implemented at the server layer.

Row-level locks are implemented at the storage engine layer. The locking mechanism of innodb is unknown to the server layer.

2.5.4 blocking and deadlock

The main results are as follows: (1) blocking is a queuing phenomenon caused by insufficient resources. (2) deadlock is due to the fact that two objects apply for another resource when they have one resource, and the other resource happens to be held by these two objects, so that the two objects cannot complete the operation and the held resources cannot be released.

2.6 how to choose the right storage engine

Reference conditions:

Business

Backup (Innobd free online backup)

Crash recovery

Unique features of the storage engine

Conclusion: Innodb is a good method.

Note: try not to use a hybrid storage engine, for example, a rollback will cause problems with online hot backup.

2.7 configuration parameters

2.7.1 parameters related to memory configuration

Determine the maximum amount of memory you can use.

The upper limit of memory usage cannot exceed physical memory, otherwise it is easy to cause memory overflow; (for 32-bit operating systems, MySQL can only try memory below 3G.)

Determine the memory used separately for each connection to the MySQL.

Sort_buffer_size # defines the size of the sorting cache for each thread. MySQL allocates memory for each buffer only when there is a query and needs to be sorted (all memory for this parameter is allocated directly)

Join_buffer_size # defines the size of the connection buffer used by each thread. If a query is associated with multiple tables, MySQL allocates a join buffer for each table, resulting in multiple join buffers for a query.

Read_buffer_size # defines the size of the read buffer pool allocated when a full table scan of a MyISAM is performed. MySQL allocates memory to it when a query is needed, which must be a multiple of 4k

The size of the read_rnd_buffer_size # index buffer. MySQL allocates memory to it when it needs a query, and only allocates the required size.

Note: the above four parameters are assigned to a thread, and if there are 100 connections, then × 100 is required.

MySQL database example:

① MySQL is single-process and multithreaded (while oracle is multi-process), which means that MySQL instances behave as a service process on the system, that is, processes.

② MySQL instances are composed of threads and memory, and the instances are really used to manipulate database files.

In general, an instance operates on one or more databases; in a cluster case, multiple instances operate on one or more databases.

How to allocate memory for a cache pool:

Innodb_buffer_pool_size, which defines the size of the cache pool used by Innodb, is very important for its performance and must be large enough, but when it is too large, it takes more time to flush dirty pages from the buffer pool to disk when Innodb is shut down.

Total memory-(memory required per thread * connections)-memory reserved by the system

Key_buffer_size, which defines the size of the cache pool used by MyISAM. Because the data is dependent on the storage operating system cache, more memory space should be reserved for the operating system.

Select sum (index_length) from information_schema.talbes where engine='myisam'

Note: even if all the tables used by the development are Innodb tables, reserve memory for MyISAM because the tables used by the MySQL system are still MyISAM tables.

Max_connections controls the maximum number of connections allowed, usually 2000 or more.

Do not use foreign key constraints to ensure the integrity of the data.

How to solve the slow query speed in MySQL is shared here. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.