Shulou(Shulou.com)05/31 Report--

This article mainly introduces "Optimization | important MySQL development specifications are here". In daily operation, I believe many people have doubts about optimization | important MySQL development specifications. The editor has consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "Optimization | important MySQL development specifications are here"! Next, please follow the editor to study!

1. InnoDB engine is used by default

[Lao Ye's point of view] has called for many times that InnoDB is applicable to almost 99% of MySQL application scenarios, and the system table in MySQL 5.7 has been changed to InnoDB, so there is no reason to stick to MyISAM.

In addition, for InnoDB tables that read and write frequently, be sure to use integers with self-increment / sequential characteristics as explicit primary keys.

2. Select utf-8 for character set

[Lao Ye's point of view] in order to save disk space, latin1 is recommended. The recommended choice of utf-8 is usually for the so-called "versatility", but in fact the utf-8 data submitted by users can also be stored in the latin1 character set.

The trouble with storing utf-8 data in latin1 is that if there is a Chinese-based retrieval, it may not be 100% accurate (Lao Ye himself simply tested the regular Chinese search without a problem, that is, the general Chinese comparison is not a problem).

The practice of using latin1 character set to store utf-8 data is: on the web side (user side), the character set is utf-8, and the back-end program also uses utf-8 to deal with it, but character_set_client, character_set_connection, character_set_results, character_set_database and character_set_server are all latin1, and the character set of data tables and fields is also latin1. Or latin1 is used in the data table, and SET NAMES LATIN1 can be executed after each connection.

3. The physical length of row records in InnoDB table does not exceed 8KB.

[Lao Ye's point of view] the default data page of InnoDB is 16KB. Based on the characteristics of B+Tree, at least 2 records need to be stored in a data page. Therefore, when the actual storage length exceeds the large column (large column) of 8KB (especially TEXT/BLOB columns), it will cause "page-overflow storage", similar to "row migration" in ORACLE.

Therefore, if you must use large columns (especially the TEXT/BLOB type) and read and write frequently, it is best to split these columns into child tables and not store them with the main table. If not too often, consider keeping it in the main table.

Of course, if you change the innodb_page_size option to 8KB, it is recommended that the physical length of the row record not exceed 4KB.

[reference]: [MySQL Optimization case] Series-optimize the storage efficiency of BLOB columns in InnoDB.

4. Whether or not to use partition tables

[Lao Ye's point of view] in some scenarios where the use of partition tables can obviously improve performance or the convenience of operation and maintenance, it is recommended to use partition tables.

For example, under the premise that zabbix's database uses TokuDB engine, Lao Ye uses partition tables according to the time dimension. This advantage is to ensure that the daily application of zabbix will not be affected, it is convenient for administrators to delete past data routinely, only need to delete the corresponding partition, and there is no need to execute a very slow DELETE and affect the overall performance.

Reference: migrate Zabbix database to TokuDB.

5. Whether or not to use stored procedures and triggers

[Lao Ye's point of view] in some appropriate scenarios, it is no problem to use stored procedures and triggers.

We used to use storage to complete the game business logic processing, the performance is not a problem, and once the requirements change, only need to modify the stored procedure, the cost of change is very low. We also use triggers to maintain a frequently updated table, and all changes to this table synchronously update some fields to another table (similar to the disguised implementation of materialized views), and there is no performance problem.

Do not regard MySQL stored procedures and triggers as scourges, if you use them well, there will be no problem, and it is never too late to optimize them if you encounter problems. In addition, MySQL doesn't have materialized views, so use them as little as possible if you don't need them.

6. Choose the right type

[Lao Ye's point of view] in addition to the common suggestions, there are several other key points:

6.1. use INT UNSIGNED to store IPV4 addresses and convert them with INET_ATON () and INET_NTOA (). Basically, it is not necessary to use CHAR (15) to store.

The internal storage mechanism of ENUM,ENUM is TINYINT or SMALLINT (not CHAR/VARCHAR). The performance is not bad at all. Remember never to use CHAR/VARCHAR to store enumerated data.

6.3. in addition to the "common sense misleading" that has been spreading earlier, it is suggested that TIMESTAMP should be used instead of DATETIME. In fact, from 5.6 onwards, it is recommended to choose DATETIME storage date and time first, because its available range is larger than TIMESTAMP, physical storage is only 1 byte more than TIMESTAMP, and the overall performance loss is not great.

6.4.The NOT NULL constraint is added by default in all field definitions, unless it must be NULL (but I can't think of any scenario in which null values must be stored in the database, which can be represented by 0). When doing COUNT () statistics on this field, the statistical results are more accurate (those with a value of NULL will not be counted by COUNT), or you can quickly return the results when performing WHERE column IS NULL retrieval.

6.5. try not to read all fields directly by SELECT *, especially if there are large TEXT/BLOB columns in the table. You may not need to read these columns, but because you are lazy to write as SELECT *, the in-memory buffer pool is washed out by the "junk" data that really needs to be buffered.

8. About the index

[Lao Ye's point of view] in addition to the common suggestions, there are several other key points:

8.1. For a string column that is more than 20 in length, it is best to create a prefix index rather than an entire column index (for example: ALTER TABLE T1 ADD INDEX (user (20), which can effectively improve index utilization, but its disadvantage is that the prefix index is not used when sorting this column. The length of the prefix index can be based on the statistics of the field, which is generally slightly larger than the average length.

Check and delete duplicate indexes regularly with the pt-duplicate-key-checker tool. For example, if the index idx1 (a, b) index already covers index idx2 (a), you can delete the idx2 index.

8.3. When there is a joint index of multiple fields, the field order of the filter conditions in WHERE does not need to be the same as that of the index, but if there is sorting and grouping, it must be the same.

For example, if there is a federated index idx1 (a, b, c), then the following SQL can fully use the index:

[MySQL FAQ] Series-what do you need to pay attention to when moving from MyISAM to InnoDB

[MySQL FAQ] series-Why do InnoDB tables recommend self-incrementing columns as primary keys

On MySQL character set

[MySQL Optimization case] Series-optimizing the storage efficiency of BLOB columns in InnoDB tables

Migrate Zabbix database to TokuDB

[MySQL optimization case] Series-paging optimization

[MySQL Optimization case] Series-RAND () Optimization

[MySQL FAQ] Series-under what circumstances will temporary tables be used

[MySQL FAQ] Series-what information should be paid attention to in EXPLAIN results

At this point, the study of "Optimization | all the important MySQL development specifications are here" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.