Shulou(Shulou.com)06/02 Report--

This article will explain in detail how the MySQL specification is, and the content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

The following is divided into table specification, SQL specification, index specification three parts, each part of each article has mandatory, suggested two levels, we in the reference, according to their own company to weigh.

Table-building specification

[mandatory]: ① storage engine must use InnoDB

* * interpretation: * * InnoDB supports transaction, row-level locking, and better concurrency performance. CPU and memory cache page optimization make higher resource utilization.

[mandatory]: ② each table must have a primary key ID, and this primary key ID uses a self-increasing primary key (as short as possible to meet the needs), except in a sub-database and sub-table environment.

* * interpretation: * * A primary key is required because of the way InnoDB organizes data, and if the primary key ID is monotonously increasing, it can effectively improve insert performance, avoid excessive page splits, reduce table fragments and improve space utilization.

In the environment of sub-database and sub-table, it is necessary to distribute the primary key values in each table uniformly, so as to avoid the duplication of primary keys in the whole logical table.

[mandatory]: ③ must use the utf8mb4 character set

* * interpretation: * * UTF-8 in MySQL is not a "real UTF-8", while "utf8mb4" is the real "UTF-8".

[mandatory]: ④ database tables and table fields must be annotated in Chinese

* * interpretation: * * Don't be lazy.

[mandatory]: ⑤ library name, table name, field name are all lowercase, underscore style, no more than 32 characters, must see the meaning of the name, it is forbidden to mix Pinyin with English.

Interpretation: agreement.

[mandatory]: the number of ⑥ columns in a single table must be less than 30. If the number exceeds, you should consider splitting the table.

* * interpretation: * * too many columns in a single table make it too expensive for the MySQL server to process the mapping between the data returned by InnoDB.

[mandatory]: ⑦ prohibits the use of foreign keys. If there is a foreign key integrity constraint, it needs to be controlled by the application.

* * interpretation: * * Foreign keys will lead to table-to-table coupling, and both UPDATE and DELETE operations will involve associated tables, which will greatly affect the performance of SQL and even cause deadlock.

[mandatory]: ⑧ must define the field as NOT NULL and provide a default value

Interpretation:

NULL's columns make index / index statistics / value comparisons more complex and more difficult for MySQL to optimize. NULL, a type of MySQL, needs special processing internally to increase the complexity of database processing records; under the same conditions, when there are more empty fields in the table, the processing performance of the database will be much lower. The NULL value requires more storage emptiness, and the columns of NULL in each row in the table or index need extra space to identify.

[mandatory]: ⑨ disables reserved words, such as DESC, RANGE, MARCH, etc.

* * interpretation: * * Please refer to MySQL official reserved words.

[mandatory]: ⑩ if the length of the stored string is almost equal, use the CHAR fixed-length string type

Interpretation: can reduce space debris, save storage space.

[suggestion]: ⑪ consider using TIMESTAMP instead of DATETIME in some scenarios

Interpretation:

Both types of time can express the "yyyy-MM-dd HH:mm:ss" format. TIMESTAMP only needs to take up 4 bytes in length and can store a range of (1970-2038) years. The time shown varies from time zone to time zone. The DATETIME type occupies 8 bytes, is insensitive to the time zone, and can be stored in a range of (1001-9999) years.

[suggestion]: ⑫ beware of automatically generated Schema. It is recommended that all Schema be written manually.

* * interpretation: * * Don't trust some database clients too much.

SQL protocol

[suggestion]: in order to make full use of cache, ① does not allow the use of custom functions, storage functions, and user variables.

Interpretation: if the query contains any user-defined functions, storage functions, user variables, temporary tables, and system tables in the MySQL library, the query results will not be cached.

For example, the function NOW () or CURRENT_DATE () will return different query results for different query times.

[mandatory]: ② specifies the required columns in the query instead of directly using "*" to return all the columns

Interpretation:

Reading unwanted columns increases CPU, IO, and NET consumption. The overlay index cannot be used effectively.

[mandatory]: ③ does not allow implicit conversion of attributes

* * interpretation: * * suppose we add an index to the mobile number column, and then execute the following SQL. What will happen?

Explain SELECT user_name FROM parent WHERE phone=13812345678; obviously means that the index is not valid and the table is scanned.

[suggestion]: ④ uses a function or expression on the attribute of the WHERE condition

* * interpretation: * * MySQL cannot automatically parse this expression and cannot use the index.

* * [mandatory]: * * ⑤ forbids the use of foreign keys and cascading, and all foreign key concepts must be resolved at the application layer

* * interpretation: * * Foreign keys and cascading updates are suitable for stand-alone machines with low concurrency, but not for distributed and high concurrency clusters; cascading updates are strongly blocked, and there is a risk of database update storm; foreign keys affect the insertion speed of the database.

[suggestion]: ⑥ should try to avoid using or as a connection condition in the WHERE clause

* * interpretation: * * you can choose to use UNION ALL instead of OR according to the situation.

[mandatory]: ⑦ does not allow fuzzy queries starting with%

* * interpretation: * * according to the leftmost prefix principle of the index, fuzzy queries starting with% cannot use the index and can be retrieved using ES.

Index specification

[recommendation]: ① avoids setting up separate indexes on columns that are updated frequently and with low discrimination.

* * interpretation: * * the optimization effect of creating an index separately for columns with low differentiation is very small, but frequent updates will make the maintenance cost of the index higher.

[mandatory]: no more than five tables are allowed for ② JOIN. For fields that need JOIN, the data types must be absolutely consistent. When querying multiple tables, make sure that the associated fields need to be indexed.

* * interpretation: * * the JOIN of too many tables will make it more difficult for the optimizer of MySQL to weigh a "best" execution plan (the possibility is the factorial of the number of tables), and pay attention to whether the associated fields are consistent in type, length, character encoding, and so on.

[mandatory]: ③ in a federated index, if the index differentiation of the first column is equal to 1, then there is no need to establish a federated index

* * interpretation: * * the index can fully locate the data through the first column, so the latter part of the federated index is not needed.

[mandatory]: when ④ builds a federated index, the more differentiated fields must be placed on the left

* * interpretation: * * more differentiated columns are placed on the left, which can effectively filter out useless data from the very beginning. To improve the efficiency of the index, when we write multiple conditions in the WHERE condition of SQL in Mapper, we need to see whether the current table has a ready-made federated index for direct use, and note that the order of each condition is consistent with the order of the index as far as possible.

[suggestion]: ⑤ uses overlay index for query operation to avoid returning to the table

* * interpretation: * * override query means that the query only needs to get the required DATA through the index, and there is no need to query the table again, so it is relatively efficient.

We are using the result of EXPLAIN, and the extra column appears: "using index". It should also be emphasized here that do not use "SELECT *", otherwise it is almost impossible to use an override index.

[suggestion]: when ⑥ builds an index on a longer VARCHAR field, such as VARCHAR (100), the index length should be specified. There is no need to index the whole field. The index length can be determined according to the actual text differentiation.

* * interpretation: * * there is a contradiction between index length and discrimination. Generally, for string type data, if an index with a length of 20 has a discrimination degree of more than 90%, you can consider creating an index with a length of 20 instead of a full-field index.

For example, you can use SELECT COUNT (DISTINCT LEFT (lesson_code, 20)) / COUNT (*) FROM lesson; to determine text differentiation when the character length of the lesson_code field is 20.

[suggestion]: if there is an ORDER BY scenario in ⑦, please pay attention to the ordering of the index.

The last field of ORDER BY is part of the federated index and is placed at the end of the index combination order to avoid the occurrence of file_sort and affect query performance.

Interpretation:

Suppose there is a query condition that is WHERE aquired? And baked? If there is an index in ORDER BY c;: a_b_c, then index sorting can be used at this time. Counterexample: if a range query is included in the query condition, the index ordering cannot be exploited, for example, the WHERE a > 10 ORDER BY b; index axib cannot be sorted.

[suggestion]: columns indexed by ⑧ in Where cannot be part of an expression, nor can they be arguments to a function

* * interpretation: * * an index has been added to a column, but if the column becomes part of an expression or an argument to a function, MySQL cannot parse the column separately, and the index will not take effect.

[suggestion]: ⑨ when we use a range query in a Where condition, the index is used for one range condition at most, and if more than one, the index that follows does not move.

* * interpretation: * * MySQL can use the leftmost range query of multiple scope conditions, but the latter range query cannot be used.

[suggestion]: when ⑩ joins multiple tables, the associated field types between tables must be exactly the same.

* * interpretation: * * when two tables are Join, indexing will not take effect if the field type is not exactly the same, which includes but is not limited to field type, field length, character set, Collection and so on.

On how the MySQL specification is shared here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.