MySQL performance optimization

MySQL optimization in three directions ① optimize MySQL Server Core (this optimization is usually done by operations personnel). ② tuning MySQL configuration parameters (my.cnf) This optimization requires a stress test to adjust the parameters. ③ to SQL statements and table optimizations. MySQL parameter optimization 1:mysql The default maximum number of connections is 100, which can be viewed in the MySQL client using the following command
Mysql> Show variables like ' max_connections ';
2: View the threads that are currently accessing MySQL
Mysql> show Processlist;
3: Set maximum number of connections
Mysql>set globle max_connections = 5000;
Max can set 16384, more than useless
4: View the currently used connections
Mysql>show globle status like ' max_user_connections ' 16 lessons on MySQL statement performance optimizations ① query cache optimization Queries ②explain Our select query (you can see the number of rows executed) ③ When you use limit 1④ to index a search field as long as one row of data ⑤ use a fairly typed column in the Join table and index it ⑥ do not ORDER by RAND () ⑦ Avoid select *⑧ always set a id⑨ for each table you can use enum instead of Varch Ar⑩ use not NULL as much as possible? is a fixed-length table faster? Split vertically? Split a hit delete or INSERT statement? The smaller the column the faster? Choose the right storage engine? Careful "permalink" is described as follows: (i) query cache optimization queries most MySQL servers have query caching turned on. This is one of the most effective ways to improve performance, and this is handled by the MySQL engine. When many of the same queries are executed multiple times, the results of these queries are placed in a cache so that subsequent identical queries do not have to be manipulated to directly access the cached results. The main problem here is that this is a very easy thing to ignore for our programmers. Because some of our query statements will let MySQL not use the cache, the example is as follows: 1:select username from user WHERE signup_date >= curdate ()
2:select username from user WHERE signup_date >= ' 2014-06-24 '
The difference between the two SQL statements above is curdate (), and the MySQL query cache does not work for this function. Therefore, SQL functions such as now () and RAND () or whatever, do not turn on 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. (b) Using the Explain keyword detection query using the Explain keyword lets us know how MySQL handles SQL statements, which can help us analyze the performance bottlenecks of our query statements or table structures The explain query results also tell us how the index primary key is used, how the data table is searched or sorted .... Wait a minute. The syntax format is: EXPLAIN +select statement; As we can see, the previous result shows a search of 7883 rows, and the second one searches only 9 and 16 rows of two tables. Looking at the rows column allows us to find potential performance issues. (c) using limit 1 plus limit 1 for as long as one row of data can increase performance. Instead of continuing to query for the next qualifying data record, the MySQL database engine stops the search after finding a single piece of data. (iv) Indexing a search field is not necessarily a primary key or a unique field, and if there is a field in the table that is often used to search, it needs to be indexed. The operation of the index is as follows: 1. Create an indexYou can create an index when you execute the CREATE TABLE statement, or you can add indexes to the table by using the CREATE INDEX or ALTER TABLE alone. 1.1> alter Tablealter table is used to create a normal index, a unique index, a primary key index, and a full-text index of ALTER TABLE TABLE_NAME ADD index index_name (column_list); ALTER TABLE table_name ADD UNIQUE (column_list); ALTER TABLE table_name ADD PRIMARY KEY (column_list); ALTER Table table_name Add fulltext (column_list), where table_name is the name of the table to increase the index name, column_list indicates which column columns are indexed, and columns are separated by commas using a half-width comma. Index name index_name is optional, and if you do not specify an index name, MySQL will automatically specify the index name based on the first indexed column, and ALTER TABLE allows multiple tables to be changed in a single statement, so you can create multiple indexes at the same time. 1.2> Create Indexcreate Index You can add a normal or unique index to a table and a full-text index, but you cannot add a primary key index to a table CREATE index index_name on table_name (column_ list); CREATE UNIQUE index_name on table_name (column_list); CREATE Fulltext INDEX_NAME on table_name (column_list); table_name, index_name, and column_list have the same meaning as in the ALTER TABLE statement. The index name must be specified. In addition, the primary key index cannot be created with the CREATE INDEX statement. 2. Index typeNormal index: Applicable to name, email, and other general properties unique indices unique: Similar to normal indexes, different unique indexes require that indexed field values are unique in a table, similar to a primary key index, except that a unique index allows null values. The unique index generally applies to the ID number, user account, etc. not allowed to have duplicate attribute fields. Primary KEY index: In fact, is the primary key, the general construction of the table is specified, do not need to add additional. Full-Text Search: Fields that apply only to varchar and text types. Note: full-text indexes and ordinary indexes are very different, if the establishment of a normal index, generally using like to fuzzy query, only the first part of the query content is valid, that is, only the previous query does not use wildcards valid, if there are wildcard characters before and after, the normal index will not work. For full-text indexing, there is a unique way to match the query, such as when we index the title and content of an article: ALTER TABLE article ADD fulltext (' title ', ' content '); The following syntax is required for retrieval: SELECT * from article where MATCH (' title ', ' content ') against (' query string '); Considerations when using Full-text indexing: MySQL comes with a full-text index only for data tables that are MyISAM by the database engine, and if it is a different data engine, the full-text index does not take effect. In addition, MySQL's own full-text index can only be full-text search in English, currently cannot be full-text retrieval of Chinese. We need to use Sphinx (Sphinx)/coreseek technology to deal with Chinese if we need to do full-text retrieval of text data including Chinese. In addition to using MySQL's own full-text index, if the length of the query string is too short, you will not get the desired search results. The default minimum length of words that can be found for a MySQL full-text index is 4 characters. In addition, if the queried string contains a stop word, the stop word is ignored. 3. Combined IndexThe combined index is multiprocessing the column index, which is to specify multiple field properties when the index is established. A bit like a dictionary directory, such as query ' Guo ' this pinyin word, first find the G-letter, and then in the search range of G to query the second letter is the list of U, and finally in the range of U to find the last letter O word. For example, the combination index (A,B,C), ABC is well-ordered, in any section of a of the B is a good order, any section of B below C is a well-ordered combination index of the effective principle is used in order to take effect, if an intermediate index is not used, then the index part before the breakpoint function, The index after the breakpoint does not work; The cause of the breakpoint: Any index in front of it does not participate in the query, and all behind it does not take effect. Any one of the index fields in front of you participates in a range query and will not take effect later. Breakpoints are unrelated to the position of the index Word field in the SQL statement, only with respect to existence. A good example is found on the Internet: for example: [SQL]View PlainCopy

1. where A=3 and b=45 and c=5 .... #这种三个索引顺序使用中间没有断点, all play a role;
2. where A=3 and c=5 ... #这种情况下b就是断点, A has an effect, C has no effect
3. where b=3 and c=4 ... #这种情况下a就是断点, the index behind a does not work, and this joint index has no effect;
4. where b=45 and a=3 and c=5 .... #这个跟第一个一样, it all works, ABC just use it, it's not about the order of writing .

(a,b,c) Three columns with a federated index (that is, the federated index is not indexed separately on each column) but instead establishes a, (a, B), (A,B,C) three indexes, and (A,B,C) the Multi-column index (A,C,B) is not the same.
Specific examples can illustrate: [SQL]View PlainCopy

2. (0) select * from mytable where a=3 and b=5 and c=4;
4. #abc三个索引都在where conditions are used, and they all play a role .
6. (1) select * from mytable where c=4 and b=6 and a=3;
8. #这条语句为了说明 The combined index has nothing to do with the position in SQL,where the condition order is automatically optimized by MySQL before query, the effect is the same as the previous sentence
10. (2) select * from mytable where a=3 and c=7;
12. #a用到索引, B is useless, so C is not used for the index effect
14. (3) select * from mytable where a=3 and b>7 and c=3;
16. #a用到了, B is also used, C is not used, this place B is the range value, but also the breakpoint, but the use of the index itself
18. (4) select * from mytable where b=3 and c=4;
20. #因为a索引没有使用, so BC doesn't have an index effect here.
22. (5) select * from mytable where a>4 and b=7 and c=9;
24. #a用到了 B is not used, C is not used
26. (6) select * from mytable where a=3 order by B;
28. #a用到了索引, B is also used in the results of the index of the effect, said before, a below any paragraph of the b is a good order
30. (7) select * from mytable where a=3 order by C;
32. #a用到了索引, but this place C did not play the sorting effect, because in the intermittent point, use explain can see Filesort
34. (8) select * from mytable where b=3 order by A;
36. #b没有用到索引, sort a also does not have an index effect

Note: When querying, MySQL can use only one index, and if you are building multiple single-column generic indexes, the query will be queried by selecting one of the most restrictive singleton indexes based on the index field of the query. None of the other indexes will take effect. 4. View Index mysql> Show index from Tblname;
Mysql> show keys from Tblname; 5. Deleting an indexDelete the indexed MySQL format:D ORP index indexname on tab_name; Note: You cannot use an index when you use the like to make a wildcard fuzzy query with like, if you use wildcard characters between the end of the index, the indexes are invalid.  Suppose the keyword for the query content is ' ABC ' SELECT * from Tab_name WHERE index_column like ' abc% ';  #索引是有效的SELECT * from Tab_name WHERE index_column like '%abc ';  #索引是无效的SELECT * from Tab_name WHERE index_column like '%CBA ';  #索引是有效的SELECT * from Tab_name WHERE index_column like '%abc% '; #索引是无效的当检索的字段内容比较大而且检索内容前后部分都不确定的情况下, you can change to a full-text index and use a specific retrieval method. (v) When the join table is using a fairly typed column and indexes it if there are many join queries in the program, you should ensure that the fields of join in the two tables are indexed. This way MySQL temporal will start the mechanism of optimizing the SQL statement of the join. Note: These fields that are used for join should be of the same type. For example, if you join a DECIMAL field with an INT field, MySQL cannot use their index. For those string types, you also need to have the same character set. (The character set of two tables may not be the same) for example: SELECT company_name from the users left JOIN companies on (users.state = companies.state) WHERE users.id = "USER_ID"
The two state fields should be indexed and should be of the same type, with the same character set. (vi) Remember not to use ORDER by RAND () if you really want to disrupt the data rows returned, you have n ways to do this. This use only degrades the performance of your database exponentially. The problem here is that MySQL will have to execute the rand () function (which consumes CPU time), and this is done for each row of records to be recorded and then sorted. Even if you use limit 1 is useless (because you want to sort) (vii) Avoid using SELECT * to read more data from the database, the query becomes slower. And, if our database server and Web server are two separate servers, this also increases the load on the network transport. So, we should develop a good habit of taking whatever we need.
Hibernate performance will be poor, it does not have *, but it will be all the fields of the whole table to find out all
Advantages: Fast Development (eight) always set an ID primary key for each table we should set an ID for each table in the database as its primary key, and the best is an int type (recommended to use unsigned), and set the automatic addition of the AUTO_INCREMENT flag. Even if we have a field in the users table that has a primary key called "email", let's not make it a primary key. Use the VARCHAR type to degrade performance when the primary key is used. In addition, in our program, we should use the ID of the table to construct our data structure. Also, under the MySQL data engine, there are some operations that need to use primary keys, in which case the performance and settings of the primary key become very important, such as clustering, partitioning ... In this case, there is only one exception, which is the "foreign key" of the "association table", that is, the primary key of the table, which consists of the primary key of several other tables. We call this the "foreign key". For example: There is a "student table" has a student ID, there is a "curriculum" has a course ID, then, "Score table" is the "association table", which is associated with the student table and curriculum, in the score table, student ID and course ID is called "foreign key" it together to form a primary key.   (ix) using enums instead of the Varcharenum 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 we have a field such as "gender", "Country", "nation", "state" or "department", we know that the values of these fields are limited and fixed, then we should use ENUM instead of VARCHAR. (10) Do not assign null if it is not a special case, as far as possible do not use NULL. In MySQL, for the int type, empty is 0, and NULL is a null value. In Oracle, NULL and empty strings are the same. Null also takes up storage space and makes our programs more complex to judge. The reality is very complex and there are still cases where we need to use null values. Here is an excerpt from MySQL's own documentation: "NULL columns require additional space in the row to record whether their values is NULL. For MyISAM tables, each of the NULL column takes one bit extra, rounded up to the nearest byte. "   (11) Fixed-length tables are faster if all the fields in the table are"Fixed-length", the entire table will be considered "static" or "Fixed-length". For example, there are no fields of the following type in the table: Varchar,text,blob. As long as we include one of these fields, the table is not a fixed-length static table, so the MySQL engine will handle it in a different way. Fixed-length tables can improve performance because MySQL searches faster because these fixed lengths are easy to calculate the offset of the next data, so the nature of reading will be fast. And if the field is not fixed, then every time you want to find the next one, you need the program to find the primary key. Also, fixed-length tables are more likely to be cached and rebuilt. However, the only side effect is that a fixed-length field wastes some space, because the field is set to allocate so much space whether we use it or not. In addition, to remove the value of the time to use trim to remove the space   (12) Vertical Segmentation "vertical segmentation" is a method of making a table in a database into several tables, which can reduce the complexity of the table and the number of fields, so as to achieve optimization purposes. (13) Split large Delete or insert if we need to perform a large delete or insert query on an online website, we need to be very careful to avoid our actions to stop our entire site from being appropriate. Because these two operations will lock the table, the table is locked, the other operations are not in. Apache will have a lot of child processes or threads. So, it works quite efficiently, and our servers don't want to have too many child processes, threads and database links, which is a huge amount of server resources, especially memory. If we lock our watch for a period of time, say 30 seconds, then for a site with a high level of access, this 30-second cumulative number of access processes/threads, database links, and open files may not only crash our web service, but may also leave our entire server hanging up. So use the limit control quantity to manipulate the number of records when using. (14) The smaller the column the faster  
For most database engines, hard disk operations can be the most significant bottleneck. So it's very helpful to have our data compact, because it reduces access to the hard drive. See MySQL documentation Storage Requirements View all data types. If a table has only a few columns (for example, a dictionary table, a configuration table), then we have no reason to use INT to master the keys, using Mediumint, SMALLINT or smaller TINYINT will be more economical. If we don't need to record time, using date is much better than DATETIME.   (15) Choose the right storage engine there are two storage engines MyISAM and InnoDB in MySQL, each with a few pros and cons. MyISAM is suitable for applications that require a large number of queries, but support for a large number of write operations is not very good. Even an UPDATE statement will perform a lock table operation, when all the processes that read the table are unable to operate until the write operation is complete. In addition MyISAM for select  count (*) This kind of calculation is very fast incomparable. The InnoDB trend will be a very complex storage engine, and for some small applications it will be slower than MyISAM. It supports "row lock", so when the write operation is more, it will be more excellent. Also, he supports more advanced applications, such as: transactions. MyISAM is the default storage engine of the MYSQL5.5 version, based on the traditional ISAM type, which supports B-tree, full-text indexing, but is not transaction-safe and does not support foreign keys. Does not have atomic nature. Support Lock table. InnoDB is a transactional engine that supports acid transactions (implementation of 4 transaction isolation mechanisms), rollback, crash resiliency, row locks. and provides a consistent, non-locking read with Oracle. InnoDB stores its tables and indexes in a table space, a tablespace can contain multiple files. MyISAM and InnoDB are compared as shown in: For Linux versions of MySQL   config file in/etc/my.cnf after 5.5 The default storage engine is InnoDB can be modified individually or modified when creating a table: ALTER table tab _name ENGINE INNODB; (16) Be careful to permanently link the "permalink" purpose to reduce the number of times the MySQL link is re-created. When a link is created, it will always be in a connected state, even if the database operation is finished. And since our Apache has started reusing its child processes-that is, the next HTTP request will reuse Apache's subprocess and reuse the same MySQL link.  
And, Apache runs in an extremely parallel environment, creating a lot of processes. This is why this "permanent link" mechanism is not working well. Before we decide to use permanent link, we need to think about the architecture of our entire system.

MySQL performance optimization