19 mysql optimization methods in database management
Date of update: 28 november 2017 11:24:15 submission: laozhang
The compendium provides an overview of 19 very practical ways to optimize mysql databases, which every server manager must know and learn together。
The optimization of the mysql database not only reduces the redundancy of the database, but also allows for a change in the speed at which the database will run. The following is a reference for the optimization of the 19 very good mysql databases that we have compiled。
Declares that the following optimisation schemes are based on "mysql-index-btreetype"
I. Explain
Do mysql optimization, and we'll use explain to view the sql implementation plan。
Here's a simple example of the data we're focusing on
Type column, connect type. A good sql statement must at least reach the range level. Do not appear in the all-level key column and use the index name. If no index is selected, the value is null. You can use mandatory indexing for key len columns, index lengthrows columns, scan rows. The value is a valuation extra column, detailed. Note that commonly unfriendly values are: using filesort, using temporary ii, sql statements that contain not too many values
Mysql optimizes in by storing all the constants in in in one array, which is sequenced. However, if the values are higher, the resulting consumption is larger. For example: self id from t where num in (1,2,3) can be replaced with bitween。
Iii. Select statements must indicate field names
Select* increases many unnecessary consumption (cpu, io, memory, network bandwidth); increases the possibility of using an over-cover index; and updates are required when table structure changes. Therefore, please rename the field directly after self。
Iv. Use limit 1 when only one data is required
This is to make the top of the top column in explain
V. If a sorting field is not indexed, as little sorting as possible
Vi. Minimize the use of non-indexed fields or
In the field on either side, if one is not an index field and the other condition is not an index field, the query will not be indexed. It's better to replace "or" with "union all" many times
Vii. Union all for union
The difference between union and union all is mainly that the former need to aggregate the results and then perform sole filtering, which involves sequencing, increasing the volume of cpu operations, increasing resource consumption and delays. Of course, the precondition for all of the results is that there is no repetition of data in the two result sets。
Viii. Not use order by rand()
Self id from `dynamic ' order by rant() 1,000;
Sql above, optimized to
* (select max(id) from `dynamic ' ) as nid) t2 on t1. Id > t2. Nid limit 1000;
Ix. In and exists, not in and not exists
* from table a where id in (local id from table b)
The above sql statement equals
* from table a where experts * from table b where table b. Id = table a. Id)
The distinction between in-laws and exists is mainly the result of a change in the sequence of drivers (which is the key to the change of performance) and, in the case of exists, the outer surface is the drive table, first accessed and, in the case of in, first executed sub-inquiries. So in is suitable for a large and small appearance; exists is suitable for a small and large appearance。
With regard to not in and not subjects, not only efficiency issues, not in may have logical problems. How efficiently do you write a sql statement instead of not exists
Original sql statement
If you're going to have to do something about it, you're going to have to do something about it
Efficient sql statement
If you're going to be able to use your name... From table a left job on where a. Id =b. Id where b. Id is full
The extract results set below shows that table a is not the data in table b
X. Use of reasonable page breaks to increase the efficiency of page breaks
Well, i'm not sure if you're gonna do that
When page breaks are made using the above-mentioned sql statement, one may find that, as the volume of table data increases, the direct use of the limit page query is slower。
The optimised approach is as follows: the id of the maximum number of lines on the previous page can be taken and the starting point on the next page can be limited according to this largest id. For example, the largest id on the previous page is 866612. Sql can read as follows:
If you're going to have to do it, you're going to have to do it
Sub-paragraph queries
In some user selection pages, some users may choose too much time horizon, resulting in slow searching. The main reason is the excessive number of lines scanned. This can be done through a process, a segment, a loop, and the results can be combined for presentation。
This sql statement is shown below, and can be used for segment queries when scanned lines are in millions or more
Xii. Avoid full value judgement of fields in where sub-words
The determination of null will result in the engine abandoning indexing and full table scanning。
Xiii. Do not recommend fuzzy query with prefix %
For example, like '%name 'or like '%name ', such queries cause the index to expire and complete table scanning. But you can use like "name %"。
How do you ask for %name
As shown in the figure below, although indexed for the secret field, the results were not used
So how do we solve this problem, answer: use the full text index
Often used in our queries as select id, {\fnum, fdst from dynamic 201606 where user name like '%zhangsan%'; in such a statement, the normal index cannot meet the search needs. Fortunately, in mysql, there is a full-text index to help us。
The sql syntax for creating the full text index is:
Alter table `dynamic 201606 `add fultltex `idx user name ' (`user name ' );
The sql statement using full text index is:
{\bord0\shad0\alphah3d}select id, {\fnum, fdst from dynamic 201606 where match(user name) against ('zhangsan' in boolean mode);
Note: before creating a full-text index, please contact dba to determine if it can be created. At the same time, attention is drawn to the difference between the writing of query statements and the normal index
Xiv. Avoid expression of fields in sub-words
Like what
If you want to be a part of this project, you should be able to use itIt's not like it's a good idea, but it's a good idea;
A field can only be calculator, which causes the engine to drop using the index, and it is suggested to replace it with
If you want to be a part of this project, you should be able to use itWe're going to have to do this, just as where age is = 36/2;
Xv. Avoiding implicit type conversion
Where the type conversion occurs when the type of column field appears to be inconsistent with the type of parameter entered in the sub-statement, it is suggested to determine the type of parameter in the location first
Xvi. For a joint index, follow the law of the left prefix
Lists the columns in which the index contains fields id, name, school, either directly or in the order id, name, but neither of them can use this index. So when creating a joint index, be careful with the order of the index fields, with the usual query fields at the top
Xvii. Use force index if necessary
Sometimes the mysql optimizer uses the index it considers appropriate to retrieve the sql statement, but perhaps it does not use the index we want. So you can use the force index to force the optimizer to use the index that we've developed。
Xviii. Note scope query statements
For a joint index, if there is a range query, like between, >,
