No one dares to say that you are a database master of the tips of these tables.

No one dares to say that you are a database master of the tips of these tables.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.

I recently reviewed financial database-related database SQL statements and found many SQL statements that do not comply with the basic specifications. After communication with related developers, I found that many developers and even senior engineers who have been working for many years may not be very clear about the basic specifications for database table creation. They should be able to understand the basic specifications in a timely manner, I don't know why. Some colleagues thought that I was deliberately embarrass him. When I handed the SQL example to him and made a basic explanation, he was suddenly enlightened. I am even very grateful. The reason for thanks is that no one has told him that this must be done and why it should be done. This article does not discuss it from the perspective of paradigm or from the perspective of database Global Database Table design. It is based on the most basic specification for creating tables in the database, so that every developer and friends can quickly master the basic specification for creating tables.

The following is a summary of several basic specifications for table creation as a DBA. I hope that you can understand and use these specifications to Create Table structures.

1. When creating a table, you must specify the primary key. It is recommended that you do not need to modify the data after the primary key is created.

Mysql uses the innodb Engine by default from Version 5.5. The innodb table is a clustered index table, that is, data is clustered through the primary key (the data of the row is stored under the primary key, and the index points to the primary key value), as shown in:

It is precisely because of this deconstruct that if the value corresponding to the primary key is modified subsequently, it will lead to frequent split of the index node, and the performance will be greatly reduced. Therefore, we recommend that developers use auto-incrementing IDs that are not associated with the business as the primary key (do not use uuid as the primary key ), in addition, you can also consider using other methods to produce auto-increment IDs, such as using Twitter's snowflake algorithm or zk's DistributedAtomicLong. The main advantages of using auto-incrementing primary keys instead of uuid for primary keys are as follows:

A. The occupied data volume is smaller.

B. Increasing Data Order does not cause frequent split of index nodes

C. The numeric type is more efficient than the character type

Correct syntax: use auto-increment id as the primary key

2. When creating a table selection field, make it as small as possible to meet business requirements, and make the data type as simple as possible

Data types should be as simple and easy to understand as possible. For example, it is more efficient to use numeric types than to use character types because it does not involve proofreading rules or character sets. The field size should be as small as possible to meet the business needs. The advantage is that smaller field types occupy less memory, less disk space, less disk I/O, and less bandwidth. For example, if a varchar (50) field stores several characters, you still need to apply for 50 bytes of memory during query.

3. explicitly declare the storage engine when creating a table

There is nothing to say about it. If you use any engine, you can declare it to prevent it from being defaulted. There is a saying: if you do not select, you will be selected. The selected results may not necessarily be what you want. Destiny is still in your own hands.

Correct syntax: explicitly declare ENGINE = xxxx;

4. explicitly declare the character set when creating a table

If you don't want to be troubled by garbled characters, you can honestly declare the character set, the selected result is not necessarily what you want. Destiny is still in your own hands.

Correct syntax: explicitly declare default charset = xxxx;

5. When creating a table, add indexes or composite indexes to columns that are frequently queried.

Indexes directly affect the query performance, especially when the data size is large. As a DBA who has been engaged in DBA for more than five years, he has encountered numerous online Faults due to no indexing. Please keep in mind that, you must add indexes or composite indexes to columns that are frequently queried to prevent online accidents.

Correct syntax: add the corresponding INDEX when creating the table

6. Add COMMENT to fields and tables when creating a table

This is mainly to facilitate subsequent maintenance. When I was a DBA in a small company, I took over the database because there was no COMMENT, as a result, I don't know what the corresponding table and field are for, so I have to bother my colleagues who are working on it. This tips is explained by a classic advertisement: Hello, me.

Correct syntax: Add COMMENT to both columns and tables;

7. Do not add the drop operation when creating a table.

Some developers like to add a TABLE before creating it. drop table if exists tablexxxx; then create table again. We recommend that you do not do this because I have encountered an online fault. Drop the tables in use online. We recommend that you change it to: create table if not exists tablexxx .......;

Correct syntax: create table if not exists tablexxx .......;

8. Do not set the field to NULL when creating a table.

The solution is to set the field to not null and set the default value of the field. The reason why the field should not be NULL is as follows:

A. NULL requires additional storage space;

B. The comparison will be more complicated and will lead to inaccurate select (column ).

C. Columns with NULL values may affect SQL optimization, especially in composite indexes.

The specific NULL will bring you can refer to: https://dev.mysql.com/doc/refman/5.7/en/problems-with-null.html

Correct syntax: not null default 'xxxxx ';

We have attached a simple standard SQL example for the machine. When creating a table, you can refer to the following example SQL to create it. The example is as follows:

Create database if not exists 'dev _ ops_db ';

Create table if not exists 'dev _ ops_db '. 'Monitor _ table_holiday '(

'Id' int unsigned not null AUTO_INCREMENT COMMENT 'id ',

'Holiday _ date' date not null default '2017-01-01 'comment' holiday date ',

'Holiday _ name' varchar (36) not null default ''' COMMENT 'holiday name ',

Primary key ('id '),

KEY 'holiday _ date' ('holiday _ date ')

) ENGINE = InnoDB default charset = utf8 COMMENT = 'holiday data table ';