Database Catch-30

Database Catch-30

1. Basic specifications

(1) InnoDB storage engine must be used

Interpretation: Supports transactions, row-level locks, better concurrency performance, CPU and memory cache page optimization, resulting in higher resource utilization

(2) UTF8 character set must be used

Interpretation: Unicode, no need to transcode, no risk of garbled characters, saving space

(3) Chinese comments must be added to data tables and data fields

Interpretation: Who in N years will know what the r1, r2, r3 fields are used for

(4) It is forbidden to use stored procedures, views, triggers, and Events

Interpretation: High concurrency For data Internet business, the architectural design idea is"liberating the database CPU and transferring calculations to the service layer". When the amount of concurrency is large, these functions are very important. It is possible to drag the database to death, and put the business logic in the service layer for better scalability, and can easily achieve"increase the machine and increase the performance". Databases are good at storage and indexing, so CPU calculations should be moved up

(5) It is forbidden to store large files or large photos

Interpretation: Why let the database do things it is not good at? Large files and photos are stored in the file system. How good is it to store URIs in the database?

2. Naming specifications

(6) Only intranet domain names are allowed, not IP Connecting to the database

(7) Online environment, development environment, and test environment The intranet domain name of the database follows the naming convention

● Business name: xxx

● Online environment: dj .xxx.db

● Development environment: dj.xxx.rdb

● Test environment: dj.xxx.tdb

● Add -s logo after the name of the slave library , add the -ss mark after the name of the standby database

● Online slave database: dj.xxx-s.db

● Online standby database: dj.xxx-sss.db

(8) Library name, table name, field name: lowercase, underline style, no more than 32 characters, the name must be clearly understood, mixed use of pinyin and English is prohibited

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

3. Table design specifications

(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) The table must have a primary key, such as an auto-incrementing primary key

Interpretation:

* a) The primary key is incremented and the data row is written Insertion can improve insertion performance, avoid `page` splitting, reduce table fragmentation and improve space and memory usage

* b) Select a shorter data type for the primary key. The Innodb engine ordinary index will save the value of the primary key. Shorter data types can effectively reduce the disk space of the index and improve the cache efficiency of the index

* c) Deletion of tables without primary keys in the master-slave architecture of row mode will cause the standby database to be blocked

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

Interpretation: Foreign keys will cause the table to Coupled with tables, update and delete operations will involve related tables, which will greatly affect the performance of SQL and even cause deadlocks. High concurrency can easily cause database performance. In big data high concurrency business scenarios, database use should prioritize performance

4. Fields Design specifications

(14) The field must be defined as NOT NULL and a default value provided

Interpretation:

* a) Null columns enable index/index statistics / Value comparisons are more complex, and it is more difficult to optimize for MySQL

* b) This type of null requires special processing inside MySQL, which increases the complexity of database processing records; under the same conditions, there are more in the table When there are multiple null fields, the processing performance of the database will be reduced a lot

* c) Null values ​​require more storage space, and null columns in each row in the table or index require additional space. Identification

* d) When processing null, you can only use `is null` or `is not null`, but not `=, in, <, <>, !=, not in` these operation symbols. For example: where name!='shenjian', if there is a record with a null value in name, the query result will not include the record with a null value in name

(15) It is forbidden to use TEXT and BLOB types

Interpretation: It will waste more disk and memory space, and a large number of unnecessary large field queries will eliminate hot data, resulting in a sharp decrease in memory hit rate and affecting database performance

(16) Prohibit the use of decimals Store currency

Interpretation: Use integers, decimals can easily lead to money mismatch

(17) You must use varchar(20) to store mobile phone numbers

Interpretation:

* a) When it comes to area codes or country codes, ` -()`

* may appear. b) Will the mobile phone number do mathematical operations?

* c) varchar can support fuzzy queries, for example: `like "138%"`

(18) The use of ENUM is prohibited, TINYINT can be used instead

Interpretation:

* a) Adding a new ENUM value requires a DDL operation

* b) The actual internal storage of ENUM is an integer. Do you think you are defining a string?

5. Index design specifications

(19) It is recommended to control the number of single table indexes within 5

(20) The number of single index fields is not allowed to exceed 5

Interpretation: When there are more than 5 fields, it is no longer effective in filtering data

(21) It is prohibited to create indexes on attributes that are updated very frequently and have low differentiation

Interpretation:

* a) Updates will change the B-tree, and indexing frequently updated fields will greatly reduce database performance

* b) The distinction between "gender" is not For large attributes, it is meaningless to create an index. It cannot effectively filter data. The performance is similar to that of a full table scan.

(22) When building a combined index, you must put the fields with high differentiation in front

Interpretation: Can filter data more effectively

6. SQL usage specifications

(23) It is prohibited to use SELECT *, only obtain necessary fields, and need to display instructions Column attributes

Interpretation:

* a) Reading unnecessary columns will increase CPU, IO, and NET consumption

* b) Covering indexes cannot be effectively utilized

* c) Using `SELECT *` is prone to program bugs after adding or deleting fields

(24) It is prohibited to use INSERT INTO t_xxx VALUES(xxx), and the specified inserted column attributes must be displayed

Interpretation: Program BUG is likely to occur after adding or deleting fields

(25) It is prohibited to use attribute implicit conversion

Interpretation: `SELECT uid FROM t_user WHERE phone=13812345678` Yes This results in a full table scan, but cannot hit the phone index. Guess why? (This online question has appeared more than once)

(26) It is prohibited to use functions or expressions on the attributes of WHERE conditions

Interpretation: `SELECT uid FROM t_user WHERE from_unixtime(day)> ;='2017-02-15'` will cause a full table scan

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

(27) Negative queries and fuzzy queries starting with % are prohibited

Interpretation:

* a) Negative query conditions: `NOT, !=, < ;>, !<, !>, NOT IN, NOT LIKE`, etc., will cause a full table scan

* b) Fuzzy queries starting with `%` will cause a full table scan

(28) Prohibit large tables from using JOIN queries and prohibit large tables from using subqueries

Interpretation: Temporary tables will be generated, consuming more memory and CPU, and greatly affecting database performance

(29) The use of OR conditions is prohibited and must be changed to IN query

Interpretation: The OR query of the old version of Mysql cannot hit the index. Even if it can hit the index, why should the database consume more CPU to help implement the query? What about optimization?

(30) The application must capture SQL exceptions and handle them accordingly

Summary: Internet businesses with large amounts of data and high concurrency will not be allowed to use anything that greatly affects database performance. Use it.

Recommended learning: MySQL tutorial

The above is the detailed content of Database Catch-30. For more information, please follow other related articles on the PHP Chinese website!