1, SQL optimization mainly involves to the index, the SQL query, can be used to explain the SQL statement parsing, you can see from the inside of the SQL statement execution order, such as a database table query is how to connect, when it connects, first check urinate again big table, and may be used to the index, which index, specific use and some other fields, can be reflected by the results of the explain, in fact, we can according to the results of optimized if there is no use to the index, so, we can not use the reason to the index, to rewrite the SQL statement,

2, index of the bottom is a B + tree, for each field added indexes will generate a B + tree, the primary key index is also called the cluster index, the index of leaf node hold values in the table directly, while other indexes, save is index field as well as the primary key value, so after the other index of common query, also need to back to clustering index table query, in order to prevent the back table query, also can do cover index, ordinary index for such ordinary index storage is the field in the table, you can need not go back to the table clustering index there do second query, but generally is not recommended,

Here some of the common application, for example, like statements when not to indexes; Can't proceed to expression the where the back; Behind the select must specify the fields, and so on are not introduced in detail, believe that we all know,

The same or conditions, can also cause do not use index, can use union all, instead of the or to rewrite the SQL statements,

Read some articles before, says the SQL used in statement expression, will cause no index, actually is wrong, in expressions, but also can use the index, but cannot connect in too many conditions, if the condition is continuous, can use between instead of in, if it is contains of SQL query, can also use the exists, the not exist to replace in, exist and the difference in the main is that is driven by the subquery outer query, or driven by the outer query to query, exist mainly outer table driven sub queries, when the outer drive is greater than the subquery table, use the exist; Or use in, in advanced muck query expressions, and then driven by the subquery outer query, concrete is performed first outer query or query, explain can also be used to check the result,

Now often say, behind the where clause cannot have a null value judgment and is not equal to judgment, can lead to not use the index, actually this is wrong, has a null SQL may use to the index, can use the explain to parse a contain null statement, will find that is actually used to the index, null data, will be stored in the leftmost node b + tree, so also can walk index,

Actually walk not to walk the index, the main reason is that the mysql internal optimizer decision, for each SQL statement, mysql will be analyzed, a null judgments, for example, if the index number of null values is very large, after query to all null values, the need for the cluster index, carries on the back table query, if the amount of data is too large, the effect of the query is slower than a full table scan, mysql optimizer will automatically choose a full table scan, and not walk index, mysql has an algorithm that calculates a query will blur out the value of the article roughly how many, the optimizer will not only depending on the situation to decide to not use the index, also involves the use of index priority, such as two table join, the optimizer will choose smaller table index field for query, if you need to force which index, can be used to force index and index name, forced to choose to use index, can also use straight_join to force on the left side of the table to become the driver table (general use in the inner join, and the table on the left little to the right of the table is big, big table driven by a small table, because the join condition that traverse the driver table, again by the conditions of the driver table to find the driver table, if the driver table are small, can reduce cycle times),

3, for paging SQL, use limit if the data volume is too big, need to jump in front of the number of the article is too much, will cause the SQL query is very slow, this is the need for SQL processing, for example: if it is the growth of ids, and there is no delete data, you can jump page on id for operation, turn to page 5, for example, can add a ID> in front of the page; 5 * pageSize conditions, if can be delete operation, then can restrict the jump page, only allowed page, so you can get the best of the previous page ID condition judgment (paging condition here is not limited to, ID can also be a time, the premise is conditions must add index),

When large amount of data, the need for table depots, level after break up, on a field of cross-database paging query, will cause the problem, in general it is when the page which compares two libraries will take the same page, and take on both sides of the data, the combined the smallest one page,

But this will have a problem, is when the page is too large, the more data will obtain, paging performance will drop rapidly, the need to use the above said, according to the table fields with rectification, for example, if according to the growth of ID to carry on the table, and banned jump page, the biggest ID can be according to the previous page to modify the SQL skip ID to get close to the biggest one page, two machines in the data to return to this page in the data comparison, the minimum to get the data returned to the front pages, so that you can avoid when the page is too large for each table of data is too large,

If you can ensure the table when the field is very uniform, can skip the corresponding data, for example, there are two table, each page 10 to check page 5, at this time for two table, each table, jumping over 20 record query, each table 5 records, this method may be lost the data accuracy, but still very convenient business permits,

Besides, there are also a way to ensure accuracy, and can avoid the query data paging methods too much, the above said to leap the amount of data method, because they don't know what is the biggest ID should jump, so can only be split in more than one table, assuming that should know what is the biggest ID jump, can definitely need jump the ID of the position in every table, precise location, then the corresponding page article number for Mosaic can be need query page of accurate data, so this page we can query twice, the first query, in order to locate the biggest ID, need to jump at every location in the table, in order to find out what is the biggest ID need to jump; Second query in the query merge data mentioned above, then how to find the maximum id need a jump? We can remember to share search, for example, there are three piece of table, if you want to jump the 30th records, that each table, jump 10 records, and then obtain the smallest data from three table compares, then the other two table to get the minimum of the data table of data records for second query, second query other two table data, the result set contains more than 1 time data query, also contains a table 1 minimum data, to their respective minimum data record, then can go to, need to jump record of article 30 in each position of the table, locate the need to jump the biggest ID, the location of the later can take out what you want paging data (note: the above mentioned ID just case fields, other regular field in the same way),

CodePudding user response:

Thanks for sharing advice into blogs