The following article is primarily about MySQL 5 query optimization method is very easy to use, people familiar with SQL statements are clear, if you want to operate on a task, then, SQL statements can be written there are many related, but different written query performance may be worlds apart. This article lists the five MySQL query optimization methods, of course, there are many optimization methods.
1, optimization of data types MySQL in a variety of data types, if you are a DBA, is optimized in accordance with the principles of strict data type checking, but the developers may choose to they think the easiest program to accelerate the encoding speed, or choose the most obvious choice, so you may face are not the best option, if possible, you should try to change these general guidelines to determine.
(1) avoid the use of NULLNULL For most of the databases require special handling, MySQL is no exception, it requires more code, more logical examination and a special index, some developers did not realize that created the table is the default value of NULL But most of the time should be used NOTNULL, or use a special value, such as 0, -1 as the default value.
(2) may use only a smaller fieldMySQL from Disk Read data is stored Memory In, and then use the cpu cycles and disk I / O read it, this means smaller data type of space occupied by the smaller package from the disk to read or Memory The efficiency is better, but do not decrease too much attachment data type, if what happens after the application is no space.
Modify the table will require reconstruction, indirectly, may lead to code changes, this is a very troublesome problem, so need to find a balance.
2, careful character set conversion Client or application may use the character set and the table itself, not the same character set, which need to run MySQL in the process of implicit conversion, in addition, to determine the character set such as UTF-8 whether to support multi-byte characters, so they need more storage space.
3, optimize count (my_col) and count (*)If you use MyISAM tables, in the absence of circumstances where clause, count (*) very rapidly, because the line number of statistics are very accurate, so MySQL will not go line by line, and then get travel a few, such as not null value my_col out, then and said that the situation will be the same as before, that count (my_col) speed will soon.
Where clause if the use count (), basically could not be more optimized, in the where clause beyond the obvious index column, the complex where clauses, and only use covered index be useful. Addition to the above recommendations, you can also use the summary table, which allows you to keep updating the contents of the table, you can use triggers or application logic to maintain the matrix is always up to date, or periodically run a batch handling to maintain the latest data fill, if you use the latter, your information will be very close, but not accurate, depending on how long to run a batch job, it needs to balance the application of the precise information needs, and maintain data update overhead, we must find a balance between these two points.
4, sub-query optimization Encountered when subquery, MySQL Query Optimization Engine is not always the most effective, which is why often subquery into a join query because the optimizer has been able to properly handle join queries, and of course to pay attention to point is to ensure that connection table (second table) there is a connection column index in the first table MySQL is usually compared to a second table of the query a subset of full table scans, which is nested loops algorithm part.
5, optimization UNION Across multiple different databases using UNION is an interesting optimization method, UNION on two unrelated tables to return data, which means that does not appear to repeat the line, the data must also be sort, we know that sorting is resource-intensive, especially for large tables ranking. UNIONALL can greatly accelerate the speed, if you already know that your data will not include duplicate rows, or you do not care whether the duplication of the line, in both cases more suitable for use UNIONALL. In addition, the application logic in some of the methods used to avoid duplicate rows, so that the result returned UNIONALL and UNION are the same, but UNION ALL will not be sorted.
|