Introduction to MySQL database performance optimization

Why optimize? ?

Because the amount of data is too large, from project deployment to user use, the data increases by hundreds of thousands every day, which brings a very heavy burden to the server. The Internet has always pursued high performance, but as the business scale grows and the number of users increases, the performance of the server becomes worse and worse, so we have to have higher requirements for the database.

Where to start? ?

First, it is the query speed. We expect that even when the data volume reaches the TB level, we can still achieve a query speed of millions.

Second, it is the concurrency. We require it to be able to handle thousands or even tens of thousands of concurrent accesses at the same time, and it must also cooperate with Redis, MQ, etc.

Third, high availability. As the business scale continues to grow, we must be ready to expand the server at any time, possibly from dozens of servers to hundreds or even thousands of servers, so we need to equip it with a MySQL cluster.

Fourth, transaction security: when the business encounters high concurrent access, how to ensure read-write consistency? ? ? Ensure the security of transactions? ? ? Refer to the idea of ​​multithreading. .

What is the solution? ? ?

The first thing to think about is what kind of storage engine should be used, because the storage engine determines its performance, just like whether you use a car, plane or tank, each engine has its special function. There are two commonly used ones in business, INNODB and MYISAM.

How to choose? ? ?

When we do not have too many read and write requirements for the business and mainly use queries, we use MyISAM. When we have high requirements for transaction integrity, high concurrency requirements, frequent additions and deletions, and frequent read and write operations, it is better to use INNODB.

Secondly, we need to speed up the query, so we need to add indexes to special fields of the table. The principle of indexing is to change the storage structure of data. There are two types here: the first is BTree, and the second is B+Tree. We generally use B+Tree in our business. One feature of BTree is that both the root node and the leaf node will store data. This will result in, for example, querying the bottom-level leaf node and reading the root node data layer by layer, which will increase the number of disk I/O times and invisibly increase the pressure on the database. So we need to use B+Tree

Third, we will implement a high availability solution. Here we will equip the database service with a master-slave structure cluster to relieve the pressure of reading and writing.

The fourth is the security issue. You can refer to the thread security issue here, such as how to solve high concurrent access? ? How can we ensure the integrity of transactions? ? Like RocketMQ, it also involves transactional messages. How to avoid such problems? We can lock it. Following are the classifications of locks.

SQL Optimization

1. Avoid full table scans when querying. First, consider adding indexes on the where and order by fields.

2. Avoid using NULL values ​​in the where field, so try to use NOT NULL constraints when designing tables. Some data will default to NULL, and you can set the default value to 0 or -1

3. Avoid using != or <> operators in the where clause. MySQL only uses indexes for <, <=, =, >, >=, BETWEEN, IN, and sometimes LIKE.

4. Avoid using OR to connect conditions in where, otherwise it may cause the engine to abandon the index to perform a full table scan. You can use UNION for combined queries

select id from t where num = 30 union select id from t where num = 40;

5. Try to avoid function or expression operations in the where clause

6. It is best not to use select * from t. Replace "*" with a specific field list and do not return any unused fields.

7. Use in and not in with caution, otherwise it will lead to a full table scan, such as

select id from t where num IN(1,2,3) If the values ​​are continuous, it is recommended to use between and, select id from t where between 1 and 3;

8. select id from t where col like %a%; % on the left side of the fuzzy query will result in a full table search. If a full-text search is required, you can use a full-text search engine such as es, slor

9. Limit offset rows For paging queries, try to avoid large offsets. For example, limit 10000,10 is equivalent to discarding the first 10,000 rows and then taking 10 rows. You can add some conditions to filter (complete the screening), but you should not use limit to skip the queried data. This is a problem of ==offset doing useless work==. In actual projects, we should avoid large page numbers and try to guide users to filter by conditions.

Summarize

This is the end of this article about MySQL database performance optimization. For more relevant MySQL performance optimization content, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope everyone will support 123WORDPRESS.COM in the future!