MySQL performance optimization

Performance optimization is divided into four chunks:

Table StructureOptimization (with index)SQLOptimization of the statement bottomParametersOptimizationHardwareConfiguration optimization   table structure     Selecting the appropriate data type for a field (Int>char>varchar fast)     table structure design, decomposing the field multiple tables into multiple tables, increasing the intermediate table,    Mixed paradigm and inverse paradigm, appropriate redundancy (available triggers update redundant columns)     index:        1. Types of indexes:          &N Bsp     1.b+ index, see MySQL trilogy--1     Database why use B + tree structure, InnoDB and MyISAM difference "                2.Hash index                     Cons: Hash indexes are not stored in index value order, So cannot be used for sort                                 only support Equivalent lookup, not available for range lookup                               &NBS P Hash conflicts need to be resolved, if a hash index is established on a low-selectivity column, conflict resolution costs a lot                 3.r-tree    & nbsp               MyISAM support spatial index, R-Tree is another form of B-tree   to multidimensional space, it divides object space by scope, each node corresponds to a region and            &nbsp           A disk page where the region of all its child nodes is stored in a disk page of non-leaf nodes, and the area of all sub-nodes of non-leaf nodes falls within its region;        & nbsp The            leaf node's disk page stores the bounding rectangle of all spatial objects within its range. R-Tree is a dynamic index structure. (Most people do not use)                 4. Full-text index               &N Bsp      SolveThis problem occurs so that "word breaker" becomes the key technology for full-text indexing.                    There are two basic methods: two-yuan method it looks at all possible combinations of every 22 characters as a phrase, so there is no cost to maintain the thesaurus.                Thesaurus it makes it possible to use words in a thesaurus as a criterion for segmentation, so that the thesaurus doesn't keep up with vocabulary development unless you maintain the thesaurus. 5. Other indexes some storage engines use different types of indexes, such as TOKUDB using the Fractal Tree Index Note: In most cases, a simple full table scan is more efficient when the amount of data is low. Indexes are very efficient for medium to large tables. For a very large table, the cost of establishing and using an index will grow with it (maintaining the index), at which point you can directly differentiate the set of data that the query requires, rather than a match, listed asPartition Table (http://www.cnblogs.com/zemliu/archive/2013/07/21/3203511.html) Two. SQL optimization 1. Optimizing subqueries: MySQL needs to establish a temporary table for the query results of the inner query statement. MySQL needs to pin these temporary tables. So you can use connection queries in MySQL instead of subqueries. Connection queries do not need to establish temporary tables, which are faster than subqueries.use join instead of sub-query such as: 2.1 Single quote: When the data is int, where id = ' 10 ' will not use index, index note single quotation mark2.2distinct: Can be used for de-weight, (instead of group by) select distinct name from student;2.3order by: The default is from low to ascending order (ASC), Desc is descending (does not write the default order, one column corresponds to a liter or drop)2. optimize your query for query cachingSQL functions such as now () and RAND () or whatever, do not open the query cache because the return of these functions is variable. So all you need to do is use a variable instead of the MySQL function to turn on the cache.3. use LIMIT 1 when only one row of data is used 4. in the Join table, use a fairly type of example and index it, 5. Avoid SELECT * 6. always set an ID for each table, use the VARCHAR type when the primary key will be used performance degradation, in the MySQL data engine, there are some operations need to use the primary key, in these cases, the primary key performance and settings become very important, such as cluster, partition ... 7. using enum instead of VARCHAR , the 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. 8. obtaining recommendations from PROCEDURE analyse ()
PROCEDURE analyse () will let MySQL help you analyze your fields and their actual data, and will give you some useful advice. These suggestions will only become useful if there is actual data in the table, because it is necessary to have data as a basis for making some big decisions. 9. use not NULL where possible non-buffered queries Mysql_unbuffered_query () sends an SQL statement to MySQL instead of automatically fethch and caches the results like mysql_query (). This can save a lot of considerable memory, especially those that produce a lot of results, and you don't have to wait until all the results are returned, and you can start working on the query results as soon as the first row of data is returned. fixed-length tables are faster char > VarChar Watch out for "permanent links" you only have limited number of links, memory issues, file handles, and so on. Three. Bottom-level parameter optimization    MySQL parameter optimization: Memory will be reserved for MySQL part of the buffer. These buffers can increase the speed of MySQL. The size of the buffer is set in the MySQL configuration file.

Several important parameters are described in detail below:

• Key_buffer_size: Represents the size of the index cache. The higher the value, the faster the query is made using an index
• Table_cache: Indicates the number of tables that are open at the same time. The larger the value, the more tables can be opened at the same time. This value is not as large as possible, because too many tables open at the same time can affect the performance of the operating system.
• Query_cache_size: Indicates the size of the query buffer. Query buffers can be used to increase the speed of queries. This method only uses a situation that is less than the modification operation and frequently performs the same query operation; The default value is 0.
• Query_cache_type: Indicates the open state of the query buffer. 0 indicates off, 1 means open.
• Max_connections: Represents the maximum number of connections for the database. The greater the number of connections, the better, because connections can waste memory resources.
• Sort_buffer_size: The size of the sort buffer, the larger the value, the faster the sort.
• Innodb_buffer_pool_size: Represents the maximum cache for tables and indexes of type Innodb. The higher the value, the faster the query will be. This value is too large to affect the performance of the operating system.

Four. Hardware configuration optimizationCPU Memory Solid State Drive Architecture:

MySQL performance optimization