MySQL Development specification

Character Design specification:
1. Select the appropriate character set, the short character set is more conducive to transport and storage: usually use the UTF8 character set. If you confirm that the system only needs to support English characters, use latin1; If you only need to support Chinese, then use gbk,gb2312; If you need internationalization, use UTF8
2. Character-set-server server (db,table,filed)---default_character_set client (Connection)-Tools (GUI). Ensure that the three character sets are consistent to avoid garbled problems
3. Check Character set: Collation-server=*_ci,*_bin
4. Specifying the character set and validation rules: Proximity principle (Level high and Low server > Database > Table > column)
5. mysql database script file character set, to be consistent with the database. (Latin1-iso-8859-1;gbk,gb2312-utf8;utf8-utf8) To ensure that MySQL executes these scripts without garbled

MySQL Index specification:
1. The order of the indexes
right matches until a range query is encountered stop matching
equals (=) range (<, >, between, like), join, sort (Order BY, group BY, distinct)
2. Use composite indexes to overwrite single-column indexes
3. Avoid duplicate indexes
the same table has (a) a single-column index, (a, b) a combined index and (A,B,C) a combined index
4. Do not use functions in indexed columns: Max (ID), MySQL does not support function indexing
5. Try to use a high-selection field as the prefix index
COUNT (distinct column)/count (*), the higher the better
6. The number of fields in the index does not exceed 5
7. Unique and primary key repeats
8. ORDER BY, Group BY, the distinct field needs to be added after the index
9. SQL on-line view explain check that the execution plan is reasonable and avoid the extra column with a using Filesort, using Temporay
. VarChar field index, no more than 15 character length prefix index
The following table adds a column of URL_CRC32, then indexes the URL_CRC32, reduces the length of the indexed field, improves efficiency, and the preceding URL field is preferably a unique character, increasing the selection rate
CREATE TABLE all_url (
ID INT UNSIGNED NOT NULL auto_increment PRIMARY KEY,
URL VARCHAR (255) isn't null DEFAULT 0,
Url_crc32 BIGINT UNSIGNED not NUL L DEFAULT 0,
Index I_URL_CRC32 (URL_CRC32)
) engine = InnoDB;
Reasonable creation of a federated index (avoid redundancy), (A,B,C) index can already overwrite (a), (A, B), there is no need to create the latter two indexes the
reasonable use of the overwrite index, that is, as far as possible in the Select to include the index of the field, reduce the return to the table read I/o read
11. Type implicit conversion causes an execution plan to change

MySQL DDL specification:
1. All changes submitted at least 1 days in advance
2. All DLL requirements to be submitted after team leader approval
3. All new on-line tables must be indexed before they can be launched

MySQL SQL statement development:
1. Use fewer triggers
2. Do not use *, select uses a specific field name
3. Reduce lock waits and competition, avoid using large transactions, use short transactions
4. Prohibit using the% prefix fuzzy query where like '%xxx '
5. Some low-version MySQL try to use join instead of sub-query
6. Using deferred correlation to resolve large offset paging issues
7. Disable concurrent execution of Count (*)
8. Prohibit the use of order by rand ()
9. batch update and delete, do not update large amounts of data at once
SQL uses in instead of or (or more efficiently than in)

Naming conventions:
1. Database name, table name and field name do not exceed 32 characters
2. Disabling special symbols
3. Do not conflict with RDBMS reserved words
4. library/table/field/index name all lowercase

Table Design Specifications:
1. Only use the InnoDB storage engine, support transactions, MyISAM engine does not support transactions and only table-level locks
2. Auto_increment must Have and self-increment the primary key with the business is transparent, because the transparent primary key has no effect on the business operation, master-slave synchronization also takes precedence according to the primary key
3. Prohibit union fields and character fields master key, the primary key field is too long cause performance problem, union field can cause deadlock problem easily
4. Do not store pictures, files and other big data in the database
5. Prohibit the use of partitioned tables
6. Avoid using database reserved fields

Column Design Specification:
1. Minimize storage space
2. Use numeric type + unsigned as much as possible
3. Use less Blog/text field and, if necessary, divide the table into primary and sub-table with primary key one by one, child table only holds Blog/text field and primary key corresponding to primary table
4. Prohibit the use of external health, the integrity check is a time-consuming and resource-intensive process for each insert, update, and delete of all foreign keys in a database, and it can affect performance, especially when dealing with complex or entangled connection trees.
5. Storing non-negative integers using unsigned
6. VarChar (N) is the number of characters represented
7. Replace enum-type enum and set-type set with tinyint
8. Time use datetime or use timestamp

The lock that the statement implements
1. Select ... from, consistent non-locking read
2. Lock in shared mode, shared
3. Select ... for update, exclusive
4. Update/delete, Exclusive
5. Insert ... on duplicate key update, exclusive
6. Replace, no conflict/Repeat, same as insert
7. Insert into T Select ... from S, T table plus exclusive
8. CREATE TABLE t ... select, t table plus exclusive
9. Replace into T Select ... from s where or update t ... where col in (select ... from), will be exclusive on the S table

MySQL Development specification