20 Practical tuning MySQL performance optimization experience

?http://www.searchdatabase.com.cn/showcontent_58391.htm  "Optimize your query for query caching" like Now () and RAND () Or any of the other SQL functions will not turn on the query cache, because the return of these functions will be volatile and variable. So all you need to do is use a variable instead of the MySQL function to turn on the cache.    "EXPLAIN Your Select query" uses the EXPLAIN keyword to let you know how MySQL handles your SQL statements.   "Use LIMIT 1 whenever one row of data" when you query a table, you already know that the result will only have one result, but because you might need to fetch the cursor, or you might want to check the number of records returned.   Jianjian Index for search words is not necessarily a primary key or a unique field. If you have a field in your table that you will always use to do a search, then index it.   "Use a fairly type of example in the Join table and index It" if your application has many join queries, you should confirm that the fields for join in two tables are indexed.　　In this way, MySQL internally initiates the mechanism for you to optimize the SQL statement for join. Also, the fields that are used for join should be of the same type. For example, if you want to join a DECIMAL field with an INT field, MySQL cannot use its index. For those string types, you also need to have the same character set. (The character set of two tables may be different)   "Avoid select *"   "always set an ID for each table" We should set an ID for each table in the database as its primary key, and the best one.   "Use enum instead of VARCHAR" enum type is very fast and compact. In fact, it holds the TINYINT, but it appears as a string on its appearance.　　In this way, using this field to make a list of options becomes quite perfect. If you have a field such as "gender", "Country", "nation", "state" or "department", you know that the values of these fields are limited and fixed, then you should use ENUM instead of VARCHAR.   "Get advice from PROCEDURE analyse ()  " Use not NULL when possible " " Save IP address as UNSIGNED INT " " fixed-length table faster " "Vertical split   Smaller columns faster   Select the right storage engine    other data MySQL performance optimization-slow query analysis, optimized indexing and configuration: http://www.oicto.com/ mysql-explain-show/  

20 kinds of experience tuning MySQL performance optimization