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

This article mainly introduces the "summary database table / SQL/ index specification". In the daily operation, I believe that many people have doubts in summarizing the database table / SQL/ index specification. The editor has consulted all kinds of information and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "summary database table / SQL/ index specification". Next, please follow the editor to study!

First, the schedule agreement

[mandatory] (1) the storage engine must use InnoDB

Interpretation: InnoDB supports transactions, row-level locks, and better concurrency performance, while CPU and memory cache page optimization make higher resource utilization.

[mandatory] (2) 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: because the way InnoDB organizes data determines the need to have a primary key, and if the primary key ID is monotonously increasing can effectively improve insert performance, avoid excessive page splits, reduce table fragments to 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] (3) utf8mb4 character set must be used

Interpretation: UTF-8 in Mysql is not a "real UTF-8", while "utf8mb4" is the real "UTF-8".

[mandatory] (4) Database tables and table fields must be annotated in Chinese

Interpretation: don't be lazy.

[mandatory] (5) 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] (6) the number of columns in a single table must be less than 30. If it exceeds that, consideration should be given to 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.

[force] (7) prohibit the use of foreign keys, if there are foreign key integrity constraints, need to be controlled by the application

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

[mandatory] (8) the field must be defined as NOT NULL and a default value must be provided

Interpretation:

The columns of NULL make index / index statistics / value comparisons more complex and more difficult for MySQL to optimize

NULL, a type of Msql, needs special processing internally, which increases 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 the NULL in each row in the table or index need extra space to identify.

[force] (9) disable reserved words, such as DESC, RANGE, MARCH, etc. Please refer to Mysql official reserved words

[force] (10) 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.

[recommendation] (11) in some scenarios, consider using TIMESTAMP instead of DATETIME

Interpretation:

These two types of time can express "yyyy-MM-dd HH:mm:ss" format. TIMESTAMP only needs to occupy 4 bytes and can be stored in the range of (1970-2038) years. The time shown is different in different time zones.

The DATETIME type occupies 8 bytes, is insensitive to the time zone, and can be stored in a range of (1001-9999) years.

* [recommendation] (12) beware of automatically generated Schema. It is recommended that all Schema be written manually.

Interpretation: don't trust some database clients too much.

II. SQL Protocol

[recommendation] (1) in order to make full use of cache, custom functions, storage functions and user variables are not allowed.

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.

[force] (2) specify the required columns in the query instead of directly using "*" to return all the columns

Interpretation:

Reading unwanted columns increases the consumption of CPU, IO, and NET

The overlay index cannot be used effectively.

[force] (3) implicit conversion of attributes is not allowed

Interpretation: suppose we add an index to the mobile number column, and then execute the following SQL. Explain SELECT user_name FROM parent WHERE phone=13812345678; obviously means that the index is not valid and the table is scanned.

[recommendation] (4) use a function or expression on the attribute of a WHERE condition

Interpretation: Mysql cannot automatically parse this expression and cannot use the index.

[mandatory] (5) the use of foreign keys and cascading is prohibited, and all foreign key concepts must be solved in the application layer.

Interpretation: foreign keys and cascading updates are suitable for low concurrency on a single machine, but not for distributed and high concurrency clusters; cascading updates are strong blocking and there is a risk of database update storm; foreign keys affect the insertion speed of the database.

[recommendation] (6) try to avoid using or as a connection condition in the WHERE clause

Interpretation: depending on the situation, you can choose to use UNION ALL instead of OR.

[mandatory] (7) Fuzzy queries starting with% are not allowed

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

III. Index specification

[recommendation] (1) avoid setting up separate indexes on columns with frequent updates and low differentiation

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

[mandatory] (2) 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 Mysql's optimizer to weigh a "best" execution plan (the possibility is the factorial of the number of tables), while paying attention to whether the type, length, character encoding, and so on of the associated fields are consistent.

[force] (3) 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] (4) when establishing a federated index, a more differentiated field must be placed on the left

Interpretation: more differentiated columns are placed on the left, which can effectively filter out useless data from the 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.

[recommendation] (5) use overlay index for query operation to avoid returning to the table

Interpretation: overlay query means that the query only needs to get the required DATA through the index, and no longer needs to return to the table query, so the efficiency is relatively high. We are using the result of EXPLAIN, and the column will appear: "using index". It should also be emphasized here that do not use "SELECT *", otherwise it is almost impossible to use an override index.

[recommendation] (6) when building an index on a longer VARCHAR field, such as VARCHAR, 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: the length of the index and the degree of differentiation is a pair of contradictions. Generally, for string type data, if the length of the index is 20, the discrimination degree will be as high as 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 that the character length of the lesson_code field is 20:00 text differentiation.

[recommendation] (7) if there is an ORDER BY scenario, 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, you can sort with the index 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.

[recommendation] (8) columns indexed in where cannot be part of an expression or be arguments to a function

Interpretation: that is, 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.

[recommendation] (9) when we use a range query in a where condition, the index is used for at most one range condition, and the index that follows does not move if more than one

Interpretation: Mysql can use the leftmost first range query of multiple scope conditions, but the latter range query cannot be used.

[recommendation] (10) when multiple tables are externally joined, 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 not limited to field type, field length, character set, collection and so on.

At this point, the study of "Summary Database Table / SQL/ Index Specification" 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.