Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about the specifications of the MySQL database, which may not be well understood by many people. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

Basic specification

(1) InnoDB storage engine must be used

Description: support for transactions, row-level locks, better concurrency performance, CPU and memory cache page optimization for higher resource utilization

(2) the new library uses the utf8mb4 character set.

Description: universal code, no need to transcode, no risk of garbled code, save space

(3) data tables and data fields must be annotated in Chinese.

Description: mainly to avoid leaving office. No one knows what these fields mean.

(4) prohibit the use of stored procedures, views, triggers and Event

Description: for big data's Internet business with high concurrency, the architecture design idea is to "liberate the database CPU and transfer the computing to the service layer". In the case of a large amount of concurrency, these functions are likely to drag the database to death, and putting the business logic into the service layer has better scalability and can easily achieve "increase the machine and add performance". Databases are good at storage and indexing, so CPU computing should be moved up.

(5) it is forbidden to store large files or photos.

Explanation: why let the database do what it is not good at? Large files and photos are stored in the file system, and the database is more used to store URI.

Naming convention

(1) only private network domain names are allowed to connect to the database instead of ip.

The intranet domain names of online environment, development environment and test environment database follow the naming convention.

Business name: xxx

Online environment: dj.xxx.db

Development environment: dj.xxx.rdb

Test environment: dj.xxx.tdb

The slave library is marked by-s after the name, and the standby library is marked by-ss after the name.

Online slave library: dj.xxx-s.db

Online library: dj.xxx-sss.db

(2) Library name, table name, field name: lowercase, underscore style, no more than 32 characters, must see the meaning of the name, it is forbidden to mix Pinyin with English.

Table name t_xxx, non-unique index name idx_xxx, unique index name uniq_xxx

Table design specification

(1) the number of tables in a single instance must be less than 500, the number of columns in a single table must be less than 30, and the table must have primary keys, such as self-increasing primary keys.

Description:

Primary key increment, data row writing can improve insertion performance, avoid page splitting, reduce table fragmentation and improve space and memory use.

To choose a shorter data type for the primary key, the general index of the Innodb engine will save the value of the primary key. The shorter data type can effectively reduce the disk space of the index and improve the caching efficiency of the index.

Table deletion without primary key, in the master-slave schema of row mode, will cause the standby database to be tamped.

(2) prohibit the use of foreign keys. If there is a foreign key integrity constraint, it needs to be controlled by the application.

Description: foreign keys will lead to table-to-table coupling, update and delete operations will involve associated tables, which will greatly affect the performance of sql, and even cause deadlock. In the case of high concurrency, it is easy to cause database performance. Big data's high concurrency business scenario database gives priority to performance.

Field design specification

(1) the field must be defined as NOT NULL and provide a default value.

Description:

The columns of null make index / index statistics / value comparisons more complex and more difficult for MySQL to optimize

Null, a type of MySQL, needs special processing internally, which increases 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 much lower.

The null value requires more storage emptiness, and the columns of null in each row in the table or index need extra space to identify

When dealing with null, only is null or is not null can be used, but not =, in, = '2017-02-15' will lead to full table scanning. The correct way to write it is: SELECT uid FROM t_user WHERE day > = unix_timestamp ('2017-02-1500 is not null 0000')

(5) No negative queries and fuzzy queries starting with%

Description:

Negative query conditions: NOT,! =,!,!, NOT IN, NOT LIKE, etc., which will cause a full table scan

A fuzzy query at the beginning of% will cause a full table scan

(6) large tables are prohibited from using JOIN queries and subqueries.

Description: it will produce temporary tables, consume more memory and CPU, and greatly affect the performance of the database.

(7) the use of OR condition is prohibited and must be changed to IN query.

Description: the old version of Mysql OR query can not hit the index, even if it can hit the index, why let the database spend more CPU to help implement query optimization?

(8) the application must catch SQL exceptions and handle them accordingly

Code of conduct

Prohibit manual access to online databases using accounts in the application configuration file

Non-DBA is prohibited from writing to the online database. To modify online data, you need to submit a work order, which is executed by DBA. The submitted SQL statement must be tested.

Assign a non-DBA to a read-only account, and you must access the authorized slave library through the VPN+ jumper.

Development, testing, online environment isolation

After reading the above, do you have any further understanding of the specifications of the MySQL database? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.