First, the index optimization principle 1, the leftmost prefix matching principle, the joint index, MySQL will match from do right until encounters the scope query (>, <, between, like) to stop the match, for example a = 1 and B = 2 and C > 3 and D = 4 if established (A, B,C,D) The index of the order, D is not indexed, if the establishment (A,B,D,C) of the index can be used, a,b,d order can be arbitrarily adjusted. 2, = and in can be disorderly, such as a = 1 and B = 2 and c = 3 build (a,b,c) index can be in any order, the MySQL query optimizer will help you optimize the index can be identified by the form of 3, the index column cannot participate in the calculation, keep the column "clean", such as
from_unixtime (create_time) = ' 2014-05-29 'Can not be used to the index, the reason is very simple, B + tree is stored in the Data table field values, but for retrieval, you need to apply all the elements of the function to compare, obviously the cost is too large. So the statement should be written
create_time = Unix_timestamp (' 2014-05-29 ')4, when using the index, the index field is best small and unique, avoid the case of SELECT * 5, as far as possible to expand the index, do not create a new index. For example, the table already has an index of a, now to add (A, b) of the index, then only need to modify the original index, the establishment of unnecessary indexes will increase the MySQL space 6, if you determine how many data, using limit restrictions, MySQL will find the corresponding bar number of data, the time of the search for the table of the 7 , using the query cache, many times MySQL will cache the query results, but the corresponding "dynamic" data will not cache, for example: 1 SELECT username from user WHERE signup_date >= curdate () cannot use cache 2 SELECT username from user WHERE signup_date >= '2017-05-06' can cacheAfter using the MySQL write function, MySQL cannot determine that the result is variable, so there is no cache, and now (), Rand () also does not open the cache 8, join syntax, try to put the small table in front, on the need on the field, data type consistency, and set the corresponding index, otherwise MySQL cannot use the index to join Query 9, do a large number of updates on large tables, if the full table will be locked, you need to split execution, to avoid long lock table, causing other requests to accumulate too much (InnoDB supports row locks, but only if the WHERE clause needs to be indexed, No index is the same as lock full table)
1 while(1) {2 //only 1000 at a time.3mysql_query ("DELETE from logs WHERE log_date <= ' 2009-11-01 ' LIMIT");4 if(mysql_affected_rows () = =0) {5 //There 's nothing to delete, quit! 6 Break;7 }8 //take a break every time .9Usleep50000);Ten}
MySQL Index Optimization principle
