Database Development Code Collection

Code description

High concurrency Big data of the Internet business, the architecture design idea is "to liberate the database CPU, transfer computing to the service layer" in the case of large concurrency, these features are likely to bring the database down, business logic to the service layer with better scalability, to achieve increased performance of the machine

Database specification

1. Using the InnoDB storage engine

Supports transactions, row-level locks, better concurrency, and CPU and memory cache page optimizations for higher resource utilization

2. Using the UTF8 character set

Universal code, no transcoding, no garbled risk, space-saving

3. Prohibit the use of stored procedures, views, triggers, Event

The database is good at storing and indexing, CPU computing or moving up.

4. Prohibit storing large files or large photos

Large files and photos are stored in the file system, and the database is saved with URIs.

Table Design Specifications

1. The table must have a primary key, such as a self-increment primary key

A. Primary key increment, data row write can improve insert performance, avoid page splitting, reduce table fragmentation and improve memory usage

B. Primary key to select a shorter data type, the InnoDB engine Normal index will hold the value of the primary key, the shorter data type can effectively reduce the index disk space, improve the efficiency of the index cache

C. Table deletion without a primary key, the master-slave architecture in row mode causes the repository to be compacted

2. Prohibit the use of foreign keys, if there are foreign key integrity constraints, need to apply program control

A foreign key causes the table to be coupled to the table, and both the update and delete operations involve the associated tables, which can affect SQL performance and even deadlock. High concurrency is prone to database performance, big Data high concurrency Business Scenario database use Performance first

Field Design Specification

1. You must define the field as NOT null and provide a default value

A. Null column makes index/index statistics/value comparisons more complex and harder to optimize for MySQL

B. Null this type of MySQL internally requires special processing to increase 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 reduced a lot

C. A null value requires more storage space, either a table or a column that is null in each row in the index, which requires additional space to identify

D. When dealing with NULL, only is NULL or is not NULL, but cannot use =, in, <, <>,! =, not in these operation symbols. such as: where name!= ' AA ', if there is a record of name null value, The query result will not contain a record with the name null value

2. Prohibit the use of Text,blob type

Waste more disk and memory space, unnecessary large size query will eliminate hot data, resulting in a sharp reduction in memory hit rate, affecting database performance

3. Prohibit the use of fractional storage currency

Using integer types, decimals can easily cause money to be

Index Design Specification

1. Single-table index recommended control within 5

2. The number of single indexed fields does not allow more than 5

When there are more than 5 fields, the actual effect of filtering data is not available.

3. Prohibit indexing on columns with very high frequency of updates and low sensitivity

A. Updates change the B+tree tree, and updated field indexing can significantly reduce database performance

B. Like "Sex" this is a less differentiated attribute with only two values, the index is not very meaningful, performance and full table scan similar

4. To set up a composite index, you must put the high-sensitivity field in front

Ability to filter data more efficiently

SQL Usage Specification

1. Disable the use of select *, get only the necessary fields, need to display the Description column properties

A. Read unwanted columns increases CPU, IO, net consumption

B. Unable to effectively use the overlay index

C. Using SELECT * Easy to add or remove fields after the program bug

2. Disable the use of attribute implicit conversions

SELECT uid from T_user WHERE phone=13812345678 causes full table scan numbers to be automatically converted to string types

Select UID from T_user WHERE phone= "13812345678" hit Index

3. Prohibit the use of functions or expressions on the properties of a Where condition

SELECT uid from T_user WHERE from_unixtime (day) >= ' 2018-02-15 ' causes full table scan

SELECT uid from T_user WHERE day>= unix_timestamp (' 2018-02-15 00:00:00 ') hit index

4. Suppress negative queries, as well as the fuzzy query starting with%

A. Negative query conditions: not,! =, <>,!<,!>, not in, no like, etc., resulting in a full table scan

B.% start of fuzzy query, will cause full table scan

5. Prohibit large tables from using a join query, prevent large tables from using subqueries

Generates temporary tables, consumes more memory and CPUs, and greatly affects database performance

6. Prohibit the use or condition, must change in query

or consumes system resources more than in

Database Development Code Collection