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

In this issue, Xiaobian will bring you about MySQL development design specifications. The article is rich in content and analyzed and described from a professional perspective. After reading this article, I hope you can gain something.

naming conventions

Library name, table name and field name are prohibited to exceed 32 characters.

All database object names must be lowercase and underlined.

Different database names DbName dbname

Different table names Table tabLe

l All database object names (library name, table name, field name) MySQL reserved keywords are prohibited

select id,username,from,age from tb_user

There are two from,MySQL is not clear what the difference between the two from, execution above will report an error

select id,username,`from`,age from tb_user

l Database objects should be named in a way that makes sense, and preferably no more than 32 characters.

For example: user account table user_account

user database aisino_userdb

Temporary library tables must start with tmp_and end with date, e.g. tmp_test01_20161218

l Backup repository backup tables must start with bak_and end with a date stamp, e.g. bak_test01_20161218

All column names and column types that store the same data must be consistent

CREATE TABLE customer_inf(

customer_inf id int unsigned auto increment not null comment 'autoincrement id',

customer_id int unsigned not null comment 'self incrementing id of customer_login table,

.........................................

CREATE TABLE order_master(

order_id int unsigned not null auto increase comment 'order ID',

customer_id int unsigned not null comment 'next individual ID',

.........................................

base specification

l Use INNODB storage engine

Support transactions, row-level locking, better recovery, better performance under high concurrency

l INNODB table must have primary key column, use auto_increment

l Database and table character sets use UTF8 uniformly

The Unified Character Set avoids garbled characters due to character set conversion

MySQL UTF8 character set Chinese characters account for 3 bytes, ASCII code accounts for 1 byte

l Table must have primary key

All tables and fields need comments.

Use comment to add comments to tables and columns

Benefits: Maintain and organize data dictionaries from the beginning

l The number of tables shall not exceed 300

l Try to control the size of single table data. It is recommended that the data be controlled within 5 million rows.

5 million is not the limit of MySQL database, MySQL can store up to how many thousands of data?

This limitation depends on the storage settings and file system

You can use historical data archiving, sub-library sub-table and other means to control the size of the data

l It is forbidden to store binary data such as pictures, videos and files in the database

storing pictures or files in corresponding file servers, and storing only address information of pictures or files in databases

Usually the file is very large, query IO operation time consuming, will affect database performance

Avoid reading useless cold data with more efficient use of cache

Columns that are often used together are put into a table

l Do database stress testing online is prohibited

l It is forbidden to directly connect the production environment database from the development environment and test environment.

Database environments are isolated

Temporary tables and backup tables must be cleaned regularly (backup archiving)

database table design

l Prohibiting the use of partition tables

Partitioned tables physically appear as multiple files and logically appear as one table

Choose partition keys carefully, cross-partition queries may be less efficient

For large tables, it is recommended to manage large data by physical table division.

l Split large fields and fields with low access frequency to separate hot and cold data

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

MySQL limits storage to 4096 columns, and the size of each row of data cannot exceed 65535 bytes.

Reduce disk IO and ensure memory cache hit rate for hot data

l Table by date and time shall conform to YYYY[MM][DD][HH] format

l Adopt appropriate strategy of dividing library and table.

field design

Avoid using TEXT,BLOB data types

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

l Prioritize the smallest data type that meets the storage needs, and use INT UNSIGNED to store IPV4

Convert string to numeric type Store:

INET_ATON('255.255.255.255')=4294967295

Convert numeric types to strings:

INET_NTOA(4294967295)='255.255.255.255'

Use TINYINT instead of ENUM type

l Table Character Set Choose UTF8 as much as possible

Avoid ENUM enumeration data 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

l All fields are defined as NOT NULL

Index NULL columns require extra space to hold, so take up more space

NULL values need special treatment when comparing and calculating, and the index will be invalid.

l Use UNSIGNED to store nonnegative integers

unsigned value range:

UNSIGNED INT (0--4294967295)

l Amount data related to finance must use decimal type

Decimal type is a precise floating point number, which does not lose precision when calculated

The footprint is determined by the defined width

Can be used to store integer data larger than bigint

l Try to avoid using strings to store date data

Disadvantage 1: Cannot compare and calculate with date function

Cons 2: Storing dates in strings takes up more space

l Storage time using TIMESTAMP or DATATIME type

TIMESTAMP 1970-01-01 00:00:01 --2038-01-19 03:14:07

l INT type fixed 4 bytes storage,TIMESTAMP 4 bytes and INT the same, but more readable than INT

l Prohibits storing plaintext passwords in the database

index specification

Limit the number of indexes per table. It is recommended that the number of indexes in a single table should not exceed 5.

l It is forbidden to create a separate index for each column in the table. The number of fields in a single index cannot exceed 5.

Each Innodb table must have a primary key.

Do not use frequently updated columns as primary keys, do not use multi-column primary keys

Primary key is recommended to use self-increasing ID value

l Do not use frequently updated columns

Indexing fields that often require sorting, grouping, and union operations

Common index column recommendations

Columns in WHERE clause of SELECT,UPDATE,Delete statement

Fields contained in ORDER BY,GROUP BY,DISTINCT

Associated columns of multi-table JOIN

index fields that are often used as query criteria

Delete indexes that are no longer used or rarely used

The leftmost prefix matching principle is a very important principle.

l Try to select columns with high discrimination as indexes

Avoid redundant and duplicate indexing

index(a,b,c), index(a,b) ,index(a)

column a is redundant index

l Give preference to using overlay indexes for frequent queries

Overlay index: An index that contains all the query fields

All fields include columns appearing in select clauses, order by clauses and group by clauses

Benefits of Overriding Indexing: Avoiding Double Lookups for Indexing Innodb Tables

Random IO can be changed into sequential IO to speed up query efficiency

SQL Design Specification

Avoid implicit conversions, which can lead to index invalidation.

l Make full use of prefix index

l must be the leftmost prefix

JOIN consumes more memory and generates temporary tables

Avoid doing math in databases

l Where clause prohibits function transformation and calculation of columns

Functional conversion or calculation of columns causes indexes to be unavailable

where date(createtime)='20160901'

Improvements:

where createtime >= '20160901' and createtime < '20160902'

L use is not equal to (!= or), index cannot be used

l Use LIKE operations (e.g.'%abc... '), the index cannot be used

l Reject large SQL and split it into small SQL

SQL parallel query is not supported. MySQL can only use one CPU for calculation.

Avoid using JOIN to associate too many tables

Each join table takes up an extra portion of memory (join_buffer_size)

Temporary table operations will occur, affecting query efficiency

MySQL allows a maximum of 61 tables to be associated, and it is recommended that no more than 5 tables be associated.

l The program connects different databases using different accounts, and cross-database queries are prohibited.

Use UNION ALL instead of UNION

UNION will put all the data into the temporary table before demultiplexing

UNION ALL will no longer deduplicate the result set

l Prohibits queries using select * and insert operations without field lists

l Prohibits a single SQL statement from updating multiple tables simultaneously

l Try not to use select *, but use SELECT query

SELECT * returns results that contain many unnecessary fields, consuming more CPU and IO and network bandwidth resources

Cannot use overlay index

Code of Conduct

l Batch import and export data must be notified in advance to assist DBA observation

Batch write operations of more than 1 million lines should be performed in batches for multiple times.

l The modification of large table data structure must be careful, which will cause serious table locking operation, especially in the production environment, which is intolerable.

Use pt-online-schema-change for large tables to modify the table structure without locking the table

Avoid master-slave delays caused by large table modifications

Avoid locking tables while modifying table fields

l Promotion activities or launch of new features must be notified in advance DBA, request stress assessment

l Follow the principle of minimum permissions for program connection database accounts

l In principle, the account used by the program is not allowed to have drop permission.

l Do not use super permissions to connect to the database

It is forbidden to grant super permission to accounts used by programs

super permission can only be reserved for DBAs who handle the problem

l Multiple alter operations on a single table must be combined into one operation

l Notify DBA timely to assist in troubleshooting when there is a fault not caused by database.

l Database data loss, timely contact DBA for recovery

l Database scheme selection and design of major projects must be notified to DBA in advance to participate

l Communicate with DBAs in advance to determine maintenance and backup priorities for particularly important library tables

l Do not update and query the database in batches during peak business hours

l Batch import and export data must notify DBA in advance and ask for assistance in observation

l Database DDL and important SQL early mention DBA review

l Mention online DDL requirements, all SQL statements must have comments

The above is what the MySQL development design specification shared by Xiaobian is. If there is a similar doubt, you may wish to refer to the above analysis for understanding. If you want to know more about it, please pay attention to the industry information channel.

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.