Programmers must know the MySQL usage specification manual

mysql tutorial Column introduction MySQL usage specification manual

More related free learning recommendations: mysql tutorial (Video)

As time goes by and the number of users of a platform or system increases, database operations tend to slow down; and in Java application development, the database is particularly important, absolutely In most cases, the performance of the database determines the performance of the program. If there are more pits buried in the early stage, the database will become the bottleneck of the entire system in the later stage; therefore, more standardized use of MySQL is indispensable in development.

1. MySQL database naming convention

1. All table prefixes in the database use the acronym of the project name;

2. All object names in the database use lowercase letters, and Words are separated by underscores;

3. MySQL reserved words and keywords are prohibited in all database object names. SQL queries involving keywords need to enclose the keywords in single quotes;

4. The names of all objects in the database should not exceed 32 characters, and the naming should follow the principle of knowing the name;

5. The database temporary table must be prefixed with pro_tmp_ and suffixed with date 20190917, backup The table must be prefixed with pro_bac and suffixed with timestamp; (pro is the acronym of the project name)

6. All column names and column types in the database that store the same data must be consistent.

2. Basic design specifications for MySQL database

1. Unless otherwise specified, Innodb storage engine will be used when creating tables.

Choosing the right engine can improve database performance, such as InnoDB and MyISAM. InnoDB and MyISAM are the two most commonly used table types when many people use MySQL. Both table types have their own advantages and disadvantages, depending on the specific situation. Depends on the application;

The basic difference is: the MyISAM type does not support advanced processing such as transaction processing, but the InnoDB type does; the MyISAM type table emphasizes performance, and its execution times are faster than the InnoDB type, but It does not provide transaction support, while InnoDB provides transaction support and advanced database functions such as foreign keys;

Therefore, its support for transaction processing, support for foreign keys, support for crash repair capabilities and concurrency control are our preferred storage when building tables engine.

2. The character sets of databases and tables uniformly use UTF8

The character sets of databases and tables uniformly use utf8. If there are fields that need to store emoji expressions, etc., set the table or field It becomes utf8mb4; because utf8 is known as universal code, which does not require transcoding, has no risk of garbled characters and saves space, and utf8mb4 is backward compatible with utf8.

3. When designing the database, comments must be added to all tables and fields.

Use the Comment clause to add comments about tables and columns, or add comments directly in the comment column of the database connection tool, starting from the beginning of the project. Perform data dictionary maintenance.

Use Comment clause to add comments such as:

-- 1、创建表：
CREATE TABLE t1(id varchar2(32) primary key,name VARCHAR2(8) NOT NULL,age number);
-- 2、添加表注释：
Comment on table t1 is '个人信息';
-- 3、添加字段注释：
comment on column t1.id is 'id';
comment on column t1.nameis '姓名';
comment on column t1.age is '年龄';

Use database connection tool to add comments:

4. Data size of a single table Control it within 5 million

Try to control the size of the data in a single table. It is recommended to control it within 5 million; 5 million is not the limit of the MySQL database, but too much data is not conducive to modifying and backing up the table structure. and restore data, and appropriately use methods such as sub-database and sub-table to control the size of the data in a single table.

5. Be careful when using MySQL partitioned tables

Partitioning is to divide the data of a table into multiple smaller ones that are easier to manage in a certain way, such as according to the month in time. part, but is still one table logically; a partitioned table appears physically as multiple files, but still appears as the same table logically, and the partition key needs to be carefully selected; cross-partition query efficiency may be lower, and it is recommended to use physical partitions Manage big data through tables and other methods.

6. Try to separate hot and cold data and reduce the width of tables.

MySQL limits each table to store up to 4096 columns, and the size of each row of data does not exceed 65535 bytes. In order to reduce The overhead of disk IO threads requires appropriate control of the width of the table, because the wider the table, the larger the memory occupied when loading the table into the memory buffer pool, and more IO threads will be consumed; in addition, In order to ensure the memory cache hit rate of hot data, use the cache more effectively and avoid reading useless cold data, try to put frequently used columns into the same table to avoid unnecessary correlation operations.

7. Be careful when establishing reserved fields

When some friends design database tables, they not only design the currently required fields, but also set aside several fields as backup. For example, I designed a person table (Person), which has added various necessary fields, including name (Name), gender (Sex), date of birth (birthday), etc.;

Just in case, for example, the Person table may involve graduate school, work unit, marriage status, photos, etc., so five varchar2 fields are added, called Text1, Text2...Text5; This operation seems to be a precautionary measure, but in fact it is not necessary, because a large number of reserved fields will waste space, the reserved fields cannot be known by name, the stored data type cannot be confirmed, and it is difficult to modify the field type. It may cause problems such as table locking.

For this situation, you can refer to the following two solutions:

1. If the quantity is small and the nature of the information is closely related to the original table, then you can directly add it to the original table. Add fields to the table and update the relevant data;

2. If the number is large, or it is not a crucial attribute of the original table object, then you can add a new table, Then connect them through key values;

8. It is forbidden to store large binary data such as pictures and files in the database

If you store files in the database table, and the files are usually Very large. When the database performs a read operation, a large number of random IO operations will be performed. Large files make IO operations time-consuming and performance-consuming, causing the amount of data to grow rapidly in a short period of time; therefore, pictures and files are usually stored on the file server. , the database is only used to store file address information.

3. MySQL database field design specifications

1. Give priority to the smallest data type that meets storage needs.

Mainly consider the performance of the index, because the larger the field of the column, the larger the space required to build the index, so the number of index nodes that can be stored in a page will be smaller. When traversing The more IO times required, the worse the index performance will be.

2. Avoid using TEXT and BLOB data types

Avoid using TEXT and BLOB data types. The most common TEXT type can store 64K data. MySQL memory temporary tables do not support TEXT and BLOB. Big data types. If the query contains such data, you cannot use memory temporary tables when performing operations such as sorting. You must use disk temporary tables to perform operations;

TEXT and BLOB types can only use prefix indexes ( When the index is a very long sequence of characters, the index will take up a lot of memory and be very slow. At this time, a prefix index will be used; the so-called prefix index is to use the first few letters of the index as an index, but it needs Reduce the repetition rate of the index, so we must also judge the repetition rate of the prefix index;), because MySQL has a limited length for the index field, the TEXT type can only use the prefix index, and there cannot be a default value on the TEXT column;

If you need to use it, it is recommended to separate the BLOB or TEXT columns into a separate extended table, and do not use select * when querying, just take out the necessary columns.

3. Avoid using the ENUM enumeration type

Modifying the ENUM value requires the use of the ALTER statement;

ORDER BY operation of the ENUM type is inefficient;

Prohibited Use numeric values ​​as enumeration values ​​for ENUM.

4. The default value of all columns is defined as NOT NULL

All NULL columns in the database require additional space to store, so they will take up more space;

The database needs to handle NULL values ​​specially when performing comparisons and calculations.

5. Use TIMESTAMP (4 bytes) or DATETIME (8 bytes) type to store time

TIMESTAMP The time range stored is: 1970-01-01 00:00:01 ~ 2038 -01-19-03:14:07;

TIMESTAMP occupies 4 bytes, which is the same as INT, but is more readable than the INT type. If it exceeds the TIMESTAMP value range, use the DATETIME type storage;

Disadvantages of using string type to store time: date function cannot be used for comparison calculations, and string storage takes up more space.

6. Financial related amount data must use decimal type

Precise floating point: decimal

Non-precise floating point: float, double

Decimal The type is a precise floating point number, which will not lose precision during calculation; the space occupied is determined by the defined width. Every 4 bytes can store 9 digits, and the decimal point also occupies one byte; in addition, the Decimal type can be used for storage A larger data type than bigint.

4. MySQL index design specifications

1. The number of indexes for each table should not exceed 5

Indexes can increase query efficiency, but they will also reduce insertion and updates. The efficiency may even reduce the query efficiency in some cases, so more is not always better, and the number must be controlled.

2. Each Innodb table must have a primary key

Innodb is an index-organized table, and the logical order of data storage is the same as the order of the index;

Each table can have multiple indexes, but the storage order of the table can only be one. Innodb organizes the table in the order of primary key indexes, so do not use frequently updated columns, UUID, MD5, HASH and string columns as primary keys. , these columns cannot guarantee the sequential growth of data, and it is recommended to use auto-incrementing ID values ​​for primary keys.

3. Try to avoid using foreign key constraints

It is not recommended to use foreign key constraints, but you must create an index on the associated key between tables;

Although foreign keys can ensure the referential integrity of data, foreign keys will also affect the write operations of the parent table and child tables, thereby reducing performance, and making the tables more coupled. It is recommended to implement them on the business side.

五、MySQL数据库SQL开发规范

1、建议使用预编译语句进行数据库操作

预编译语句可以重复使用，相同的SQL语句可以一次解析，多次使用，减少SQL编译所需要的时间，提高处理效率；此外，还可以有效解决动态SQL带来的SQL注入问题。

2、避免数据类型的隐式转换

隐式转换如：SELECT 1 + "1"；数值型 + 字符型 的隐式转换有可能会导致索引失效，以及一些意想不到的结果等。

3、充分利用表中存在的索引

1）避免使用双%号的查询条件

如 WHERE first_name like '%James%'，若无前置%，只有后置%，则执行SQL语句时会用到列上的索引，双%号则不会使用列上的索引。

2）一条SQL语句只能使用复合索引中的一列进行范围查询

例如有weight、age、sex三列的联合索引，在查询条件中有weight列的范围查询，则在age和sex列上的索引将不会被使用；因此，在定义联合索引时，若某列需要用到范围查询，则将该列放到联合索引的右侧。

3）使用not exists 代替not in

因为not in 在SQL语句中执行时会导致索引失效。

4、杜绝使用SELECT * ，必须使用SELECT af1c1993ef39498a90c973a43161c636 查询

因为使用SELECT * 查询会消耗更多的CPU、IO和网络宽带资源，并且查询时无法使用覆盖索引。

5、禁止使用不含字段列表的INSERT 语句

如：INSERT into table_name values ('1','2','3'); 改为带字段列表的INSERT 语句：INSERT into table_name('c1','c2','c3') values ('1','2','3');

6、避免使用子查询，可以把子查询优化为join 关联操作

但是，通常子查询在in 子句中，且子查询中为简单SQL(即不包含union、group by、order by、limit从句)时，才可以把子查询转化为join关联查询进行优化；

子查询性能差的原因：

• 子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响；

• 由于子查询会产生大量的临时表也没有索引，所以会消耗过多的CPU和IO资源，产生大量的慢查询。

7、避免使用JOIN 关联太多表

1）在Mysql中，对于同一个SQL关联（join）多个表，每个join 就会多分配一个关联缓存，如果在一个SQL中关联的表越多，所占用的内存也就越大；

2）如果程序中大量的使用了多表关联的操作，同时join_buffer_size(MySQL允许关联缓存的个数)设置的也不合理的情况下，就容易造成服务器内存溢出的情况，就会影响服务器数据库性能的稳定性；

3）此外，对于关联操作来说，会产生临时表影响查询效率，而Mysql最多允许关联61个表，建议不超过5个；

8、对同一列对象进行or 判断时，使用in 替代or

in 的值只要涉及不超过500个，则in 操作可以更有效的利用索引，or 大多数情况下很少能利用到索引。

9、禁止使用order by rand() 进行随机排序

10、禁止在WHERE 从句中对列进行函数转换和计算

因为在WHERE 从句中对列进行函数转换或计算时会导致索引无法使用。

No推荐：

where date(end_time)='20190101'

推荐：

where end_time >= &#39;20190101&#39; and end_time < &#39;20190102&#39;

11、在明显不会有重复值时使用UNION ALL 而不是UNION

1）UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作；

2）UNION ALL 不会再对结果集进行去重操作；

12、把复杂、较长的SQL 拆分为为多个小SQL 执行

1）大SQL在逻辑上比较复杂，是需要占用大量CPU 进行计算一条SQL语句；

2）在MySQL中，一条SQL 语句只能使用一个CPU 进行计算；

3）SQL拆分后可以通过并行执行来提高处理效率。

六、MySQL数据库行为规范

1、超过100万行数据的批量操作(update delete insert)，分多次进行

大批量操作可能回造成严重的主从延迟；

binlog日志为row格式时会产生大量的日志；

避免产生大事物操作。

2、对于大表使用pt-online-schema-change 修改表结构

1）避免大表修改产生的主从延迟、避免在对表字段进行修改时进行锁表；

2) pt-online-schema-change It will first create a new table with the same structure as the original table, modify the table structure on the new table, and then copy the data in the original table to the new table. , and add some triggers to the original table; then, copy the new data in the original table to the new table. After copying all data in the row, name the new table the original table and delete the original table. , which is to break down the original DDL operation into multiple small batches for execution.

3. It is forbidden to grant super permission to the account used by the program.

When the maximum number of connections is reached, running a user with super permission to connect super permission can only be left to the DBA to handle the problem. account to use.

4. For the program to connect to the database account, follow the principle of least permissions

The database account used by the program can only be used under one database, and in principle, the account used by the program does not grant drop permissions.

The above is the detailed content of Programmers must know the MySQL usage specification manual. For more information, please follow other related articles on the PHP Chinese website!