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

This article mainly introduces "what is MySQL naming convention". In daily operation, I believe many people have doubts about what MySQL naming convention is. Xiaobian consulted all kinds of materials and sorted out simple and easy operation methods. I hope to help you answer the doubts of "what MySQL naming convention is"! Next, please follow the small series to learn together!

I. Database command specifications

All database object names must be lowercase and separated by underscores.

MySQL reserved keywords are prohibited for all database object names (you need to enclose them in single quotes if the table name contains keyword queries).

Database objects should be named so that they can be recognized by the name, and finally no more than 3 2 characters.

Temporary library tables must be prefixed with tmp_and suffixed with date, backup tables must be prefixed with bak_and suffixed with date (timestamp).

All column names and column types that store the same data must be consistent (generally as associated columns. If the associated column types are inconsistent during query, implicit data type conversion will be automatically performed, which will cause the index on the column to become invalid and reduce query efficiency).

II. Basic design specifications for databases

All tables must use the InnoDB storage engine If there are no special requirements (i.e., functions that InnoDB cannot meet, such as column storage, storage space data, etc.), all tables must use the InnoDB storage engine (Myisam is used by default before MySQL 5.5, and InnoDB is used by default after MySQL 5.6). InnoDB supports transactions, row-level locking, better recovery, and better performance under high concurrency.

Database and table character sets unified use UTF8 compatibility is better, unified character set can avoid garbled characters due to character set conversion, different character sets need to be converted before comparison will cause index failure.

All tables and fields need to be commented. Use comment clauses to add comments to tables and columns. Maintain the data dictionary from the beginning.

Try to control the size of data in a single table. It is recommended to control it within 5 million.

5 million is not the limit of MySQL database, too large will cause great problems in modifying table structure, backup and recovery. You can control the data size by means of historical data archiving (applied to log data), database and table division (applied to business data), etc.

Careful use of MySQL partition tables

Partitioned tables are physically represented as multiple files, and logically represented as a table. Choose partition keys carefully, and cross-partition query efficiency may be lower. It is recommended to manage large data in the form of physical tables.

Try to separate hot and cold data and reduce the width of the table

MySQL limits each table to store up to 4096 columns, and the size of each row of data cannot exceed 65535 bytes. Reduce disk IO and ensure the memory cache hit rate of hot data (the wider the table, the larger the memory occupied when loading the table into the memory buffer pool, and the more IO will be consumed). More effective use of cache to avoid reading useless cold data. Frequently used columns are placed in a table (avoid more associative operations).

7. Reserved fields are forbidden to be created in the table. It is difficult to name reserved fields. Reserved fields cannot confirm the stored data type. Therefore, appropriate types cannot be selected. Modifying reserved field types will lock the table.

8. It is forbidden to store large binary data such as pictures and files in the database. Usually the file is very large, which will cause rapid growth of data volume in a short time. When the database reads the database, a large number of random IO operations will usually be carried out. When the file is very large, IO operations are very time-consuming. Usually stored in the file server, and the database only stores file address information.

9. Do not perform database stress tests online

10. It is forbidden to connect directly to the build environment database from the development environment and test environment.

III. Database field design specification

1. Prioritize the smallest data type that meets your storage needs

reason

The larger the column field, the more space is required to build the index, so the fewer and fewer index nodes can be stored in a page, the more IO times are required during traversal, and the worse the index performance.

method

Convert strings to numeric types for storage, such as:

Convert IP addresses into shaped data.

MySQL provides two ways to handle IP addresses:

inet_aton converts ip to unsigned integer (4-8 bits)inet_ntoa converts integer ip to address

Before inserting data, use inet_aton to convert IP addresses to integers to save space.

When displaying data, use inet_ntoa to convert integer IP address to address display

2. For non-negative data (such as self-increasing ID, integer IP), unsigned integers are preferred for storage.

Because unsigned can double the storage space relative to signed.

SIGNED INT -2147483648~2147483647UNSIGNED INT 0~4294967295

The N in VARCHAR(N) represents the number of characters, not bytes. Use UTF8 to store 255 Chinese characters Varchar(255)=765 bytes. Excessive length consumes more memory

2. Avoid using TEXT, BLOB data types, the most common TEXT type can store 64k data

It is recommended that BLOB or TEXT columns be separated into separate extended tables

MySQL memory temporary tables do not support large data types such as TEXT and BLOB. If such data is included in the query, memory temporary tables cannot be used for sorting operations, and disk temporary tables must be used.

And for this kind of data, MySQL still has to perform secondary queries, which will make SQL performance very poor, but it does not mean that such data types must not be used.

If you must use it, it is recommended to separate BLOB or TEXT columns into separate extended tables. When querying, you must not use select * and only need to take out the necessary columns. Do not query the columns when you do not need the data of the TEXT column.

TEXT or BLOB types can only use prefix indexes

Because MySQL has a limit on the length of index fields, TEXT types can only use prefix indexes, and TEXT columns cannot have default values.

3. Avoid ENUM types

Alter statement is required to modify ENUM value

ORDER BY operations of ENUM type are inefficient and require extra operations

Use of numeric values as enumeration values for ENUM is prohibited

4. Define all columns as NOT NULL whenever possible

Reason:

The index NULL column requires extra space to hold, so it takes up more space.

NULL values are treated differently when comparing and calculating.

5. Store time using TIMESTAMP (4 bytes) or DATETIME type (8 bytes)

TIMESTAMP is stored from 1970-01-01 00:00:01 to 2038-01-19-03:14:07.

TIMESTAMP takes up 4 bytes and INT is the same, but more readable than INT, beyond the TIMESTAMP value range using DATETIME type storage.

Often someone stores date data in strings (incorrect):

Disadvantage 1: Unable to calculate and compare with date functions.

Cons 2: Storing dates in strings takes up more space.

6. Amount class data related to finance must use decimal type

Inexact floating point: float, double

Precision float: decimal

Decimal type is a precise floating point number that does not lose precision when calculated. The space occupied is determined by the width of the definition, each 4 bytes can store 9 digits, and the decimal point takes up one byte. Can be used to store integer data larger than bigint.

IV. Index design specifications 1. Limit the number of indexes on each table. It is recommended that no more than 5 indexes be placed on a single table.

The more indexes, the better! Indexing can increase efficiency as well as decrease efficiency; indexing can increase query efficiency, but it can also decrease insert and update efficiency, and even decrease query efficiency in some cases.

Because MySQL optimizer in choosing how to optimize the query, according to the unified information, each index can be used to evaluate, to generate a best execution plan, if there are many indexes can be used for the query, it will increase the MySQL optimizer to generate execution plan time, will also reduce query performance.

2. Prohibiting separate indexes for each column in a table

5.6 Before version 5.6, a SQL can only use one index in a table. After 5.6, although there is an optimization method of merging indexes, it is still far from using a joint index query method.

3. Each InnoDB table must have a primary key

InnoDB is an index organized table: the logical order of data storage is the same as the order of indexes. Each table can have multiple indexes, but there can only be one storage order for tables. InnoDB organizes tables in the order of primary key indexes.

Do not use frequently updated columns as primary keys, do not apply to multi-column primary keys (equivalent to union index) Do not use UUID, MD5, HASH, string columns as primary keys (can not guarantee the order of data growth) It is recommended to use self-increasing ID value for primary key.

V. Common index column recommendations

Columns that appear in WHERE clauses of SELECT, UPDATE, Delete statements.

Fields included in ORDER BY, GROUP BY, DISTINCT.

Do not create an index for all columns that match the fields in 1 and 2. It is usually better to create a joint index for the fields in 1 and 2.

Associated columns of multi-table JOIN.

At this point, the study of "What is MySQL naming convention" is over, hoping to solve everyone's doubts. Theory and practice can better match to help everyone learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!

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.