Shulou(Shulou.com)06/02 Report--

Xiaobian to share with you MySQL database performance optimization example analysis, I believe most people still do not know how, so share this article for everyone's reference, I hope you have a lot of harvest after reading this article, let's go to understand it together!

Why optimize?

Because the amount of data is too much, the project deployment is online and then used by users. The data grows by hundreds of thousands every day, which brings a very large burden to the server. The Internet has been pursuing high performance. However, as the business scale becomes larger and the number of users becomes larger, the performance of the server becomes worse and worse. Therefore, we have to have higher requirements for the database.

Where to start?

The first is the query speed. We expect the query speed to reach the terabyte level.

The second is concurrency, which we require to be able to handle thousands or even tens of thousands of concurrent accesses at the same time, and to cooperate with Redis, MQ, etc.

Third, high availability, as the business scale continues to grow, we have to be ready to expand the server, possibly from dozens of servers to hundreds or even thousands of servers, so we have to carry MySQL clusters.

Fourth, transaction security, when the business has high concurrent access, how to ensure read and write consistency? Secure 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 storage engine determines its performance, just like whether you use a car, an airplane or a tank, each engine has its own special role. There are two common types of business, INNODB and MYISAM.

How to choose??

When we don't have too many read and write requirements for business, we mainly use queries and use MyISAM. When we have high requirements for transaction integrity, high concurrency requirements, frequent additions and deletions, and frequent read and write operations, INNODB is better.

The second one we want to speed up the query, so we want to add indexes to special fields of the table. The principle of indexes is to change the storage structure of the data. There are two types here: the first is BTree, and the second is B+Tree. B+Tree is generally used in our business. BTree has a characteristic that both root nodes and leaf nodes store data. This will cause, for example, querying the lowest leaf nodes, reading the data of root nodes layer by layer, increasing the number of disk I/O, and increasing the pressure on the database. B+Tree

Third, implement a high availability scenario, where we cluster the database services with a master-slave structure to ease the pressure of reading and writing

The fourth is security issues, where you can refer to thread security issues, such as how to solve high concurrency access?? How can we guarantee the integrity of transactions? Like RocketMQ also involves transactional messages, how to avoid this kind of problem, we can lock. The following is the classification of locks.

SQL Tuning

1. Try to avoid full table scanning for queries. First consider adding indexes to 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 the default value can be set to 0 or-1.

Avoid using!= in the where clause Or operator, Mysql uses indexes only for =, BETWEEN, IN, and sometimes LIKE

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

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

Try to avoid performing function or expression operations in the where clause

6. It is best not to use select * from t, replace "*" with a specific list of fields, and do not return any fields that are not used.

7, in and not in should also be used with caution, otherwise it will lead to full table scanning, such as

select id from t where num IN(1, 2, 3) between and, select id from t where between 1 and 3;

8, select id from t where col like %a%; fuzzy query left % will lead to full table search, if you need full-text search can use full-text search engines such as es, slor

9. limit offset rows For paging query, try to ensure that there is no large offset, such as limit 10000,10 is equivalent to discarding the first 10000 rows of the queried rows and then taking 10 rows. You can add some conditions to filter (complete filtering), instead of using limit to skip the queried data. This is a ==offset to do useless work == problem. Corresponding to the actual project, to avoid the occurrence of large page numbers, try to guide users to do conditional filtering

The above is "MySQL database performance optimization example analysis" all the content of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

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.