MySQL Development specification

Naming Conventions

> Library name, table name, field name must use lowercase letters and be split with underscores
> Library name, table name, field name prohibit more than 32 characters, must see the name to understand
* Library name, table name, field name support up to 64 characters, unified specification, easy to identify and reduce transmission volume not more than 32
> Library name, table name, field name prohibit the use of MySQL reserved keywords
> Temporary library, temporary table name must be prefixed with TMP and date suffix
> Backup library, Backup table name must be prefixed with bak and date suffix

Basic Specifications

> Using the InnoDB storage engine
* 5.5 After default breaking, support transactions, row-level lock, better recoverability, high concurrency under better performance, multi-core, large memory, SSD and other hardware support better
> table Character set using UTF8
* Use the UTF8 character set, if it is a Chinese character, 3 bytes, but ASCII code character is 1 bytes
* Unified, there will be no conversion of garbled risk
> All tables need to add comments
> Single-table data volume recommended control within 50 million
> does not store big data such as pictures, files, etc. in the database
> Prohibit online database stress testing
> Prohibit direct database from test, development environment

Library table Design specification

> Prohibit use of partitioned tables
* MySQL partition table actual performance is not very good, and management maintenance costs are high
> Split large and low-frequency fields to separate hot and cold data
> Hash table with hash, table name suffix using decimal number, subscript starting from 0
* The first sub-table as many points as possible to avoid two sub-tables, two times the difficulty and cost of the table is higher
> By date Time table must conform to YYYY[MM][DD][HH] format
> Using the appropriate sub-database sub-table strategy, such as the thousands of library ten tables, 10 library hundred tables, etc.

Index Design Specification

An index is a double-edged sword that improves query efficiency but also lowers the speed of insertions and updates and consumes disk space
> No more than 5 indexes in a single table
> The number of fields in a single index does not exceed 5
> Use prefix index for string, prefix index length not exceeding 10 characters
* If there is a char (200) column, if the majority value is unique within the first 10 characters, do not index the entire column. Indexing the first 10 characters can save a lot of index space, and may make queries faster
The > table must have a primary key
> Do not use updates frequently columns as primary keys
> Try not to select a string column as the primary key
> Do not use UUID, MD5, HASH as primary key
> Default use of non-null unique keys
> Primary key suggest to choose the self-increment or the generator
> Important SQL must be indexed
* The Where Condition column of the SELECT, UPDATE, DELETE statement
* ORDER by, GROUP by, distinct fields
* Fields for multiple table joins
> The most differentiated fields are placed in front of the index
> Core SQL Overrides overriding index
* Select data columns are only available from the index and do not have to read rows of data, in other words, query columns are overwritten by the index being built
> Avoid redundant or duplicate indexes
* Reasonable creation of a federated index (avoid redundancy), index (A,B,C) equivalent to index (a), index (A, B), index (A,B,C)
> do not index as much as possible, create as needed
* Each additional index consumes additional disk space and reduces the performance of write operations
> Do not index on low cardinality columns, such as ' gender '
> does not perform mathematical operations and function operations on indexed columns
> Try not to use foreign keys
* Foreign keys are used to protect referential integrity and can be implemented on the business side
* Actions on parent and child tables affect each other, reducing availability
* InnoDB's own limitations on online DDL
> do not use% leading queries, such as like "%xxx"
* Cannot use Index
> Do not use reverse queries, such as not in/not like
* Index cannot be used, resulting in full table scan
* Full table scan results in reduced buffer pool utilization

Field Design Specification

> try not to use text, blob type
* Deleting this value leaves a large "void" in the data table
* Consider separating a BLOB or text column into a separate table
> Storing exact floating-point numbers in decimal instead of float and double
* The advantage of floating-point numbers relative to the fixed-point number is that floating-point numbers can represent a larger range of data in the case of certain lengths
The disadvantage of floating-point numbers is that it can cause accuracy problems
> Convert characters to Numbers
> Using tinyint instead of enum type
> Field lengths try to allocate as much as you need, and do not allocate a large amount of capacity arbitrarily
* The best strategy are to allocate only as much space as you really need
* VARCHAR (n), n means the number of characters is not the number of bytes, such as varchar (255), you can store up to 255 characters, you need to choose according to the actual width of N
* VARCHAR (n), n as small as possible, because the maximum length of all VARCHAR fields in a table is 65,535 bytes, when sorting and creating a temporary table class of memory operations, use the length of N to request memory
> If possible, all fields are defined as NOT NULL
> Storing non-negative integers using unsigned
* The same number of bytes, stored in a larger range of values. If the tinyint has a symbol of-128-127, unsigned 0-255
> Fixed 4 byte storage for int type
> Storage time with timestamp
* Because timestamp uses 4 bytes, DateTime uses 8 bytes, while timestamp has auto-assignment and auto-update features
> Using int UNSIGNED storage IPV4
> Using varbinary to store case-sensitive variable-length strings
> Prohibit storing plaintext passwords in the database

SQL Design Specification

> Using pre-compiled statements prepared statement
* Only pass parameters, more efficient than passing SQL statements
* One parse, multiple use
* Reduce SQL injection probability
The Java method is as follows: Protected Boolean updatesalary (Connection conn,bigdecimal x,string ID) throws sqlexception{PreparedStatement pstmt = null; try {pstmt = conn.preparestatement ("UPDATE EMPLOYEES SET SALARY =?") WHERE ID =? "); Pstmt.setbigdecimal (1, x); Pstmt.setstring (2, ID); return true; } finally{if (pstmt!=null) {pstmt.close ();}}
}
> try to avoid the same statement due to the different writing format, resulting in multiple parsing
> Avoid implicit conversions
* Causes index invalidation, such as select UserID from table where userid= ' 1234 '
> Make full use of the prefix index
* Must be the leftmost prefix
* Two range conditions cannot be used at the same time
> Avoid using stored procedures, triggers, events, etc.
* Let the database do the best thing
* Reduce business coupling and leave room for scale out and sharding.
* Avoid bugs
> Avoid joins with large tables
* MySQL is best at a single table of primary key/two-level index query
* Join consumes more memory, resulting in temporary tables
> Avoid mathematical operations in the database
* Easy to couple business logic with DB
* MySQL is not good at mathematical arithmetic and logic judgment
* Cannot use Index
> Reduce the number of interactions with the database
* Insert ... on duplicate key update
* Replace into, insert ignore, insert into values (), (), () ...
* Update ... where ID in (1,2,3,4)
* Alter table tbl_name Add column col1, add column col2
> Reject large SQL, split into small SQL
* Make full use of query cache
* Take advantage of multi-core CPUs
> no more than 1000 values using in instead of Or,in
> prohibit the use of order by rand ()
* Because ORDER BY RAND () reads data from disk, sorts it, consumes a lot of Io and CPU, and can get a rand in the program

Value, and then by getting the corresponding value from the database
> Using UNION ALL instead of union
The > program should have a handling mechanism for catching SQL exceptions
> Disallow single SQL statements to update multiple tables at the same time
> Do not use SELECT *
* Consumes CPU and IO, consumes network bandwidth
* Cannot use overwrite index
* Reduce the impact of table structure changes

Code of Conduct

> Bulk Import, export data must be notified in advance to DBA for assistance observation
> Batch update of data, such as update,delete operations, requires a DBA to review and observe the service during execution
> Disable the ability to perform background management and statistics classes from the library
> Prohibit application account with super privilege in existence
Notify DBA to assist in the event of a non-database-caused failure in > products
> Promotions or on-line new features must notify DBAs in advance for traffic assessment
> Database data loss, timely contact DBA for recovery
> multiple ALTER operations on single table must be combined into one operation
> does not store business logic in MySQL database
> Database project selection and design of major projects must inform DBA in advance
> for particularly important library tables, communicate with DBA in advance to prioritize maintenance and backup
> Do not batch update, query database during peak business hours
> Submit online form requirements, you must specify all relevant SQL

MySQL Development specification