Performance optimization (Optimize) refers to a method that can respond to requests more quickly under the premise of ensuring the correctness of the system. Moreover, some performance problems, such as slow queries, etc., if accumulated to a certain extent or encounter a rapid increase in concurrent requests, will lead to serious consequences, ranging from busy services to making applications unavailable. For us, it is like a time bomb that is about to be detonated, threatening us all the time. Therefore, strict checks are required before launching the project to ensure that MySQL can run in an optimal state. At the same time, in the actual work, there are knowledge points about MySQL optimization in the interview, which are the key points of the interviewer’s investigation.
Our interview question in this class is, what are the optimization schemes for MySQL?
Typical Answer
Common optimization methods for MySQL databases are divided into three levels: SQL and index optimization, database structure optimization, system hardware optimization, etc. However, each major direction contains multiple small optimization points, as follows Let’s take a look at it in detail.
1.SQL and index optimization
This optimization scheme refers to improving the operating efficiency of the MySQL database by optimizing SQL statements and indexes. The details are as follows.
① Use the correct index
Index is one of the most important concepts in the database, and it is also one of the most effective means to improve the performance of the database. It was born to improve the efficiency of data query, just like the directory of a dictionary, through which you can quickly find relevant content ,As shown below:
If we do not add an index, then a full table scan will be triggered during the query, so the query data will be a lot, and the query efficiency will be very low. In order to improve the query performance, we need to add the most commonly used query fields, Add the corresponding index, so as to improve the performance of the query.
Tips: We should use primary key queries instead of other index queries as much as possible, because primary key queries will not trigger back-to-table queries, thus saving some time and improving query performance in disguise.
In versions prior to MySQL 5.0, try to avoid using or queries. You can use union or subqueries instead, because the use of or queries in earlier MySQL versions may cause index failure. In versions after MySQL 5.0, index merging is introduced. Simple In other words, it is the function of merging the result sets of multi-condition queries, such as or or and queries, for intersection or union, so the problem of index failure will not be caused.
Avoid using != or <> operators in where query conditions, because these operators will cause the query engine to abandon the index and perform a full table scan.
Proper use of prefix index, MySQL supports prefix index, which means we can define a part of the string as an index. We know that the longer the index takes up more disk space, the less index values can be placed in the same data page, which means that the query time required to search the index will be longer, and the query efficiency will be improved. Reduced, so we can properly choose to use prefix indexes to reduce space occupation and improve query efficiency. For example, the suffix of the mailbox is fixed “@xxx.com”, then a field like this with a fixed value in the last few digits is very suitable to be defined as a prefix index.
② Query specific fields instead of all fields
Try to avoid using select *, but query the required fields, which can improve the speed and reduce the bandwidth pressure of network transmission.
③ Optimize subqueries
Try to use the Join statement instead of the subquery, because the subquery is a nested query, and the nested query will create a new temporary table, and the creation and destruction of the temporary table will occupy certain system resources and take a certain amount of time, but Join statement does not create a temporary table, so the performance will be higher.
④ Pay attention to the query result set
We should try to use the small table to drive the large table to query, that is, if the data in table B is smaller than the data in table A, the order of execution is to check table B first and then table A. The specific query statement is as follows:
select name from A where id in (select id from B);
⑤ Do not perform operations on columns
Do not perform arithmetic operations or other expression operations on column fields, otherwise the query engine may not be able to use the index correctly, thereby affecting the efficiency of the query.
⑥ Appropriately increase redundant fields
Adding redundant fields can reduce a large number of linked table queries, because the performance of multiple tables linked table queries is very low, so redundant fields can be added appropriately to reduce the linked query of multiple tables. Time optimization strategy.
2. Database structure optimization
① Minimum data length
Generally speaking, the smaller the database table, the faster its query speed. Therefore, in order to improve the efficiency of the table, the fields of the table should be set as small as possible. For example, the ID number can be set to char(18) and not Set to varchar(18).
② Use the simplest data type
Do not use varchar type if you can use int type, because int type is more efficient than varchar type in query.
③ Define as few text types as possible
The query efficiency of the text type is very low. If you must use text to define a field, you can separate this field into a subtable. When you need to query this field, use a joint query, which can improve the query efficiency of the main table.
④ Appropriate sub-table and sub-database strategy
The sub-table and sub-database schemes are also what we often call vertical separation (sub-table) and horizontal separation (sub-database).
Split table means that when there are more fields in a table, you can try to split a large table into multiple sub-tables, put the main information that is used more frequently into the main table, and put the other Sub-tables, so that most of our queries can be completed only by querying the main table with fewer fields, thus effectively improving the efficiency of the query.
Split database refers to dividing a database into multiple databases. For example, we split a database into multiple databases, one main database is used to write and modify data, and the other is used to synchronize the main data and provide it to the client for query, so that the reading and writing pressure of one database is shared. Multiple libraries are provided, which improves the overall operating efficiency of the database.
3. Hardware optimization
MySQL’s hardware requirements are mainly reflected in three aspects: disk, network and memory.
① Disk
The disk should try to use a disk with high-performance read and write capabilities, such as a solid-state disk, so that the I/O running time can be reduced, thereby improving the overall operating efficiency of MySQL.
The disk can also use multiple small disks instead of one large disk as much as possible, because the rotation speed of the disk is fixed, and having multiple small disks is equivalent to having multiple disks running in parallel.
② Network
Ensuring unobstructed network bandwidth (low latency) and sufficient network bandwidth are the basic conditions for the normal operation of MySQL. If conditions permit, you can also set up multiple network cards to improve the operating efficiency of the MySQL server during network peak hours.
③ Memory
The larger the memory of the MySQL server, the more information is stored and cached, and the performance of the memory is very high, which improves the operating efficiency of the entire MySQL.
Analysis of test sites
There are many MySQL performance optimization solutions, so it can comprehensively examine whether a programmer has rich experience. Of course, the answer to this question can be deep or shallow, and different positions have different requirements for the answer to this question. This question can also lead to more interview questions, such as:
- What problems should be paid attention to in joint index?
- How to troubleshoot slow queries?
Knowledge expansion
Correct use of joint index
MySQL database engines that use B + trees, such as the InnoDB engine, match data from left to right each time a composite field is queried, so when creating a joint index, you need to pay attention to the order in which the index is created. For example, if we create a joint index idx(name,age,sex), then when we use the leftmost prefix query conditions such as name + age + gender, name + age, name, etc., the joint index will be triggered query; however, if the query condition is not the leftmost match, for example, the query condition of gender + name will not trigger the joint index.
Of course, when we already have the joint index (name, age), we generally don’t need to create a separate index on the name field, so that we can maintain one less index.
Slow query
Slow query The usual troubleshooting method is to first use the slow query log function to query the slow SQL statement, then query the execution plan of the SQL statement through explain, and finally analyze and locate the root cause of the problem. Do it again.
Slow query log refers to the recording function of slow query log that can be enabled through configuration in MySQL, and the SQL exceeding the long_query_time value will be recorded in the log. We can enable slow query by setting “slow_query_log=1”. There are two ways to enable it:
- To open it through the MySQL command line mode, you only need to execute “set global slow_query_log=1”, but this configuration mode will become invalid after restarting the MySQL service;
- Another way can be enabled by modifying the MySQL configuration file. We need to configure “slow_query_log=1” in my.cnf, and the slow query log can be configured by setting “slow_query_log_file=/tmp/mysql_slow.log” The storage directory, but after the configuration is completed in this way, the MySQL server needs to be restarted to take effect.
It should be noted that after the slow log function is enabled, it will have a certain impact on the performance of MySQL, so this function should be used with caution in a production environment.
An example SQL for using the explain execution plan is as follows:
explain select * from person where uname = 'Java';
Its execution result is shown in the following figure:
A summary description is shown in the table below:
The most important of the above fields is the type field, and all its values are as follows:
When the type is all, it means a full table scan, so the efficiency will be relatively low. At this time, you need to check why this is the case. Is there no index created or there is a problem with the index creation? In this way, the speed of the entire MySQL operation is optimized.
Summary
In this class, we talked about the optimization methods of MySQL from three dimensions: SQL and index optimization, database structure optimization, and system hardware optimization; at the same time, we went deep into each dimension and introduced the specific optimization details of MySQL in detail; finally we talked about The leftmost matching principle of the joint index, and the specific solution to the slow query.
Featured Comments
*Jay:
This course is well worth its price
**1757:
When the leftmost prefix query conditions such as name + age + gender, name + age, name, etc., will trigger the joint index to query; however, if the query condition is not the leftmost match, for example, the query condition of gender + name will not Will trigger the joint index. ————————————————– —-If it is age + name, the bottom layer of this query will be optimized successfully, let’s use the joint index
**Dragon:
This class is good, and it is very comprehensive according to the outline. worth a dollar
**Mr. Guo:
When the leftmost prefix query conditions such as name + age + gender, name + age, name, etc., will trigger the joint index to query; however, if the query condition is not the leftmost match, for example, the query condition of gender + name will not Will trigger the joint index. No, it will be triggered, there will be optimization at the bottom layer
Instructor Response:
You can use mysql’s explain to see Kazakhstan.
**duo:
I remember that in the latest version of MySQL, the leftmost matching principle of the joint index has been resolved
Instructor Response:
It will be frequently asked in mainstream versions and interviews, and it will be more stable if you understand the concepts and principles.
**build:
very nice