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

Today, the editor will bring you an article about the development and naming conventions of MySQL. The editor thinks it is very practical, so I will share it for you as a reference. Let's follow the editor and have a look.

I. basic norms

(1) use INNODB storage engine

(2) UTF8 is used in the table character set

(3) all tables need to be annotated

(4) the amount of data in a single table is recommended to be less than 5000W.

(5) do not store pictures, files, etc. Big data in the database

(6) it is forbidden to do database stress testing online.

(7) No direct connection to databases from test and development environments

II. Naming rules

(1) the database name table name field name must have a fixed naming length of less than 12 characters

(2) Library name, table name and field name are prohibited to exceed 32 characters. You must see the meaning of the name.

(3) Library name, table name and field name are prohibited from using MySQL reserved words.

(4) the names of temporary libraries and tables must be prefixed with tmp and suffixed with date.

(5) backup libraries and tables must be prefixed with bak and suffixed with date.

III. Specification for development and design of libraries, tables and fields

(1) prohibit the use of partition tables

(2) split large fields and fields with low access frequency, and separate hot and cold data

(3) enter the hash table with HASH, and use the suffix of the table name to make the index number. The subscript starts with 0.

(4) the table by date and time must conform to the format of YYYY [MM] [DD] [HH]

(5) adopt the appropriate strategy of database and table. For example, ten tables in thousands of libraries, ten hundred tables in ten libraries, etc.

(6) avoid using TEXT and BLOB types as much as possible

(7) use DECIMAL instead of FLOAT and DOUBLE to store exact floating point numbers

(8) the simpler the better: convert characters to numbers and use TINYINT instead of ENUM types

(9) all fields are defined as NOT NULL

(10) use UNSIGNED to store non-negative integers

(11) the INT type always occupies 4 bytes of storage

(12) use timestamp to store time

(13) use INT UNSIGNED to store IPV4

(14) use VARBINARY to store case-sensitive variable-length strings

(15) it is prohibited to store plaintext passwords in the database. The passwords are encrypted and stored.

(16) make good use of numeric type fields

Tinyint (1Byte)

Smallint (2Byte)

Mediumint (3Byte)

Int (4Byte)

Bigint (8Byte)

Types

Byte

Minimum value

Maximum value

(signed / unsigned)

(signed / unsigned)

TINYINT

one

-128

one hundred and twenty seven

Unsigned

0

two hundred and fifty five

SMALLINT

two

-32768

32767

Unsigned

0

65535

MEDIUMINT

three

-8388608

8388607

Unsigned

0

16777215

INT

four

-2147483648

2147483647

Unsigned

0

4294967295

BIGINT

eight

-9223372036854775808

9223372036854775807

Unsigned

0

18446744073709551615

If the numeric field is not that large, do not use bigint

(17) Storage ip is best stored in int rather than char (15)

(18) ENUM is not allowed

(19) avoid using NULL fields

The NULL field is difficult to query and optimize, the index of the NULL field needs extra space, and the composite index of the NULL field is invalid.

(20) the performance of using less text/blob,varchar will be much higher than that of text, and blob can not be avoided. Please disassemble the meter.

(21) large files or photos are not allowed to be stored in the database. You can put large objects on disk and store their paths in the database.

IV. Index specification

1. The number of indexes should be controlled:

(1) the number of indexes in a single table shall not exceed 5.

(2) the number of fields in a single index does not exceed 5.

(3) use the prefix index on the string. The prefix index length is no more than 8 characters.

(4) it is recommended to give priority to prefix indexing, and pseudo columns can be added and indexed if necessary.

2. Primary key criterion

(1) A table must have a primary key

(2) do not use frequently updated columns as primary keys

(3) try not to select string columns as primary keys.

(4) do not use UUID MD5 HASH as primary keys (the values are too discrete)

(5) the unique key that is not empty is used as the primary key by default.

(6) it is recommended to choose a self-adding or numbering device.

3. Important SQL must be indexed, such as:

(1) WHERE conditional column of UPDATE and DELETE statements

(2) Fields of ORDER BY, GROUPBY and DISTINCT

4. Note the following for the fields of multi-table JOIN:

(1) the field with the highest degree of differentiation is placed in front.

(2) Core SQL gives priority to overriding index.

(3) avoid redundant and duplicate indexes.

(4) the index should comprehensively evaluate the data density and distribution, and consider the query and update ratio.

5. Index taboo

(1) do not index on low cardinality columns, such as "gender"

(2) Mathematical and functional operations are not performed in the index column.

6. Try not to use foreign keys

(1) Foreign keys are used to protect referential integrity, which can be implemented on the business side.

(2) the operations on parent and child tables will affect each other and reduce availability.

7. Index naming: non-unique indexes must be named after idx_ field 1 _ field 2, unique so they must be named after uniq_ field 1 _ field 2, and index names must be all lowercase

8. The new unique index must not be duplicated with the primary key

9. The default value of the index field cannot be NULL. It should be changed to another default or empty. NULL greatly affects the query efficiency of the index.

10. Repeatedly check the SQL related to the table, and index in accordance with the characteristics of the leftmost prefix. For a statement with multiple duplicate fields, the order of the conditional fields of the statement should be modified to establish a joint index to reduce the number of indexes.

11. if you can use a unique index, you must use a unique index to improve the query efficiency.

12. Research and development should use explain frequently. If poor index selectivity is found, they must learn to use hint.

V. SQL specification

(1) the sql statement is as simple as possible.

Find ways to break large sql into small sql statements (make full use of QUERYCACHE and make full use of multicore CPU)

(2) the transaction should be simple, and the length of the whole transaction should not be too long.

(3) avoid using triggers, functions and stored procedures

(4) reduce the degree of business coupling and leave room for sacle out and sharding

(5) avoid entering mathematical operations in the database (MySQL is not good at mathematical operations and logical judgments)

(4) do not use select *. Query which fields are select.

(5) the use of OR in sql is rewritten with IN () (or is not as efficient as in)

(6) it is recommended to limit the number of numbers in in to less than 1000.

(7) limit pagination should pay attention to efficiency. The larger the Limit, the lower the efficiency. You can rewrite limit, such as an example:

Select id from tlimit 10000, 10; = > select id from t where id > 10000limit10

(9) use union all instead of union

(10) avoid JOIN with large tables

(11) grouping and automatic sorting using group by

(12) updates to data should be updated in batches after breaking up. Do not update too much data at once.

(13) reduce the number of interactions with the database

(13) pay attention to using performance analysis tools

Sql explain / showprofile / mysqlsla

(14) the SQL statement requires all research and development. The SQL keyword is all capitalized, and only one space is allowed for each word.

(15) implicit conversion is not allowed in SQL statements, such as selectid from table where id='1'

(16) the amount of data in the IN condition should be less than 500. you should learn to use exist instead of in,exist to query faster than in in some scenarios.

(17) if you don't need NOTIN, you don't need NOTIN. There are too many holes. Will find out the empty and NULL.

(18) in the SQL statement, prohibit the use of like with a prefix of%

(19) do not use negative queries, such as not in/like

(19) about paging query: the program recommends rational use of paging to improve efficiency. Limit,offset should match the use of subqueries.

(20) it is forbidden to run large queries in the database

(21) it is more efficient to precompile sentences and pass only parameters than to pass SQL statements; parse once and use it many times; reduce the probability of SQL injection

(22) it is prohibited to use order by rand ()

(23) do not update multiple tables at the same time with a single SQL statement

VI. Process specification

(1) all table creation operations need to inform the query sql involved in the table in advance.

(2) all tables need to determine which indexes to build before they can go online.

(3) all table structure and indexing operations need to send the query sql related to the modified table to DBA and other relevant personnel.

(4) before adding fields to a new form, R & D is required to email at least 3 days in advance to give dba time to evaluate, optimize and review.

(5) batch import and export data must be notified to DBA in advance to assist in observation.

(6) it is prohibited to perform background management and statistical queries from the database online.

(7) prohibit the existence of application accounts with super permissions

(8) DBA must be notified in advance of promotional activities or new features to evaluate the incoming traffic.

(9) do not update or query the database in batches during the peak period of business

These are the details of the development and naming conventions of MySQL. Have you gained anything after reading them? If you want to know more about it, you are welcome to follow the industry information!

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.