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

I. basic norms

(1) InnoDB storage engine must be used

Interpretation: support for transactions, row-level locking, better concurrency performance, CPU and memory cache page optimization for higher resource utilization

(2) UTF8 character set must be used.

Interpretation: universal code, no need to transcode, no risk of garbled code, save space

(3) data tables and data fields must be annotated in Chinese.

Interpretation: n years later, who tm knows what this R1 field, R2 field, R3 field is for?

(4) prohibit the use of stored procedures, views, triggers and Event

Interpretation: the architecture design idea of big data's Internet business with high concurrency is to "liberate the database CPU and transfer the computing to the service layer". In the case of a large amount of concurrency, these functions are likely to drag the database to death, and putting the business logic into the service layer has better scalability and can easily achieve "increase the machine and add performance". Database is good at storage and indexing. CPU calculation should be moved up.

(5) it is forbidden to store large files or photos.

Interpretation: why let the database do what it is not good at? Large files and photos are stored in the file system, and URI is stored in the database.

II. Naming rules

(6) only private network domain names are allowed to connect to the database instead of ip.

(7) the intranet domain names of online environment, development environment and test environment database follow the naming convention.

Business name: xxx

Online environment: dj.xxx.db

Development environment: dj.xxx.rdb

Test environment: dj.xxx.tdb

The slave library is marked by-s after the name, and the standby library is marked by-ss after the name.

Online slave library: dj.xxx-s.db

Online library: dj.xxx-sss.db

(8) Library name, table name, field name: lowercase, underscore style, no more than 32 characters, must see the meaning of the name, it is forbidden to mix Pinyin with English.

(9) Table name t_xxx, non-unique index name idx_xxx, unique index name uniq_xxx

III. Specification for watch design

(10) the number of single instance tables must be less than 500

(11) the number of columns in a single table must be less than 30

(12) A table must have a primary key, such as a self-increasing primary key.

Interpretation:

A) Primary key increment, data row writing can improve insertion performance, avoid page splitting, reduce table fragmentation and improve space and memory use

B) the primary key should choose a shorter data type, and the general index of the Innodb engine will save the value of the primary key. The shorter data type can effectively reduce the disk space of the index and improve the caching efficiency of the index.

C) Table deletion without primary key, in the master-slave schema of row mode, will cause the standby database to be tamped.

(13) the use of foreign keys is prohibited. If there are foreign key integrity constraints, application control is required.

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. In the case of high concurrency, it is easy to cause database performance. Big data's high concurrency business scenario database gives priority to performance.

IV. Specification for field design

(14) the field must be defined as NOT NULL and provide a default value

Interpretation:

A) the columns of null make index / index statistics / value comparisons more complex and more difficult for MySQL to optimize

B) the type of MySQL like null 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.

C) the null value requires more storage emptiness, and the columns of null in each row in the table or index need additional space to identify

D) when dealing with null, only is null or is not null can be used, but not =, in, = '2017-02-15' will lead to full table scan

The correct way to write it is: SELECT uid FROM t_user WHERE day > = unix_timestamp ('2017-02-1500 00')

(27) No negative queries and fuzzy queries starting with%

Interpretation:

A) negative query conditions: NOT,! =,!,!, NOT IN, NOT LIKE, etc., which will cause full table scanning

B) A fuzzy query at the beginning of% will result in a full table scan

(28) prohibit the use of JOIN queries and subqueries for large tables

Interpretation: will produce temporary tables, consume more memory and CPU, and greatly affect the performance of the database

(29) the use of OR condition is prohibited and must be changed to IN query.

Interpretation: the old version of Mysql OR query can not hit the index, even if it can hit the index, why let the database spend more CPU to help implement query optimization?

(30) the application must catch SQL exceptions and handle them accordingly

The above is the whole content of this article, I hope it will be helpful to your study, and I also hope that you will support it.

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.