Java+MySQL development specification

Mysql development specification

Do not use SELECT * in SQL script. Return the required fields.
To count rows, use select count(*) from XXX
All SQL keywords and system functions are capitalized. For example: SELECT, INSERT, UPDATE, CURRENT_TIMESTAMP(), ORDER BY, GROUP BY, INNER JOIN, LEFT JOIN.
When multiple tables Join, alias must be specified, as shown in the following example: Note: minimize the number of table links, and pay attention to the selection of driving tables, indexes, etc.

SELECT tb1.field1
    ,tb2.field2
FROM domain_table1 tb1
INNER JOIN domain_table2 tb2
    ON tb1.id = tb2.id
WHERE tb1.field2 = 'xxx'
ORDER BY tb1.in_date DESC
Copy the code

Database, table, and field encoding uses UTF8MB4
Field names should be readable. Common fields such as mobile phone numbers and email addresses should be named the same.
InDate,EditDate suggests database time NOW(3)
In general, a table must have the following general fields: ID, status, system_STATUS,in_user_id,in_user_name,in_date, edit_user_id, edit_user_name, edit_date

use test;
DROP TABLE IF EXISTS `test`.`system_role`;
CREATE TABLE `test`.`system_role` (
  `id` BIGINT AUTO_INCREMENT NOT NULL COMMENT 'System Number',
  `role_name` VARCHAR(45) NOT NULL COMMENT 'Role Name',
  `status` TINYINT NOT NULL COMMENT 'Status: 0-- invalid, 1-- valid',
  `system_status` TINYINT NOT NULL COMMENT 'System status: 0-- deleted, 1-- normal',
  `in_user_id` BIGINT NOT NULL COMMENT 'Creator System Number',
  `in_user_name` VARCHAR(45) NOT NULL COMMENT 'Name of founder',
  `in_date` DATETIME NOT NULL COMMENT 'Creation time',
  `edit_user_id` BIGINT NOT NULL COMMENT 'Last Editor System Number',
  `edit_user_name` VARCHAR(45) NOT NULL COMMENT 'Last Name of editor',
  `edit_date` DATETIME NOT NULL COMMENT 'Last Edit Time',
  PRIMARY KEY PK_system_role(`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COMMENT 'System Roles';
Copy the code

Database fields should not be NULL
Default values are not used for database fields.
Primary key, foreign key, usually used for retrieval of date fields indexed, with a small number of status values not indexed

MySQL design counterexample and relevant experience summary

Review some of the bad designs I’ve encountered on projects I’ve worked on in the past year or two:

The field length is too long, such as ProjectName vARCHar (500), which feels a little unreasonable. Later I checked the production database and found that the maximum length is only 131, so the maximum redundancy of 255 characters is reasonable.
Improper field name design: the longitude and latitude of the project location are set as LocationX and LocationY. Well, obviously it’s not very good. It’s supposed to be longitude longitude and latitude latitude.
The value of the database common field CommonStatus is defined as -999– deleted (logically deleted), 0– invalid, and 1– valid. Now if you look back, you should split it into two fields: “SystemStatus “(SystemStatus TINYINT, 0– deleted, 1– normal) and DataStatus (DataStatus TINYINT, 0– disabled, 1– enabled).
InDate,EditDate suggests the database time NOW(3). Some scenarios are particularly time-sensitive, such as cloud-to-cloud data collection in the Internet of Things, where vendor data reporting times are carried in the message body, the platform drops the message to MQ when it receives the data, and MQ subscribers persist the message when they receive it. These points in time should be treated separately in this scenario based on business requirements. For example, MySQL system time NOW(3) is recommended for data persistence.
In some relational table designs, it is recommended to use specific and unique fields as unique indexes (for example, role_id, user_id). Such as system_role_user

use test;
DROP TABLE IF EXISTS `test`.`system_role_user`;
CREATE TABLE `test`.`system_role_user` (
  `id` BIGINT AUTO_INCREMENT NOT NULL COMMENT 'System Number',
  `role_id` BIGINT NOT NULL COMMENT 'Role Number',
  `user_id` BIGINT NOT NULL COMMENT 'User number',
  `system_status` TINYINT NOT NULL COMMENT '0-- deleted, 1-- normal ',
  `in_user_id` BIGINT NOT NULL COMMENT 'Creator System Number',
  `in_user_name` VARCHAR(45) NOT NULL COMMENT 'Name of founder',
  `in_date` DATETIME NOT NULL COMMENT 'Creation time',
  `edit_user_id` BIGINT NOT NULL COMMENT 'Last Editor System Number',
  `edit_user_name` VARCHAR(45) NOT NULL COMMENT 'Last Name of editor',
  `edit_date` DATETIME NOT NULL COMMENT 'Last Edit Time',
  PRIMARY KEY PK_system_role_user(`id`),
  UNIQUE INDEX `IUX_role_id_user_id` (`role_id` ASC, `user_id` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COMMENT 'Relationship between roles and users';
Copy the code

SQL_CALC_FOUND_ROWS performance is unstable when used on tables with large amounts of data. It is recommended to use the count(*) statement to count the total number of lines. Or in some business scenarios where the total number of lines can be ignored, it can be ignored, such as the scene of scrolling to the bottom of the APP to load the next page.
Do not have too many values IN the IN statement, for example, limit the number to no more than 20. Business scenarios that exceed the threshold should be written in a different way, such as using temporary tables + joins.
Full Table Scan or Full Index scan may be caused by inconsistent field types (such as one string and one number) or encoding (such as one UTF8 and one UTF8MB4) in join and WHERE conditions.
Pay special attention to where statements where complex subqueries such as OR, union, etc. In this case, you can generally use temporary tables + joins to optimize performance.

DROP TEMPORARY TABLE IF EXISTS tmp_pro;
CREATE TEMPORARY TABLE tmp_pro
(
  ProjectSysNo INT,
  KEY IX_ProjectSysNo(ProjectSysNo)
)ENGINE=MEMORY;

INSERT INTO tmp_pro
(
ProjectSysNO
)
...
union
...

# In the query below, use tmp_pro to drive the connection
Copy the code

Minimize table joins and, if necessary, pay special attention to join fields, filter criteria, sort criteria, indexes (e.g. consider overwriting indexes, reducing filesort, etc.), and drive table selection. When there are too many connections, the code is hard to understand and it’s easy to get lost when problems occur.
In large-table paging scenarios, large table connections should be minimized. You can first paging from the primary key of a large table, fetching data on the current page, and then fetching data on each row back to the table. Such as

DROP TEMPORARY TABLE IF EXISTS tmp_current_page; CREATE TEMPORARY TABLE tmp_current_page ( SysNo INT, KEY IX_SysNo(SysNo) )ENGINE=MEMORY; INSERT INTO tmp_current_page ( SysNo ) SELECT a.`SysNo` FROM `xxx_warning`.`projectintellidevicewarning` AS a <! --<if test="filter.userSysNo ! = null and filter.userSysNo ! = "">
    inner join `xxx_warning`.`projectintelliwarninguser` u
    on a.ProjectSysNo = u.ProjectSysNo and a.AppSysNo = u.AppSysNo AND u.UserSysNo = #{filter.userSysNo}
    and u.CommonStatus=1
</if>-->
<where>
    <if test="filter.projectSysNo ! = null and filter.projectSysNo > 0">
        AND a.ProjectSysNo=#{filter.projectSysNo}
    </if>
    <if test="filter.appSysNo ! = null and filter.appSysNo > 0">
        AND a.AppSysNo=#{filter.appSysNo}
    </if>
</where>
ORDER BY
a.SysNo DESC
LIMIT #{offset},#{pageSize};

### instead of using IN, use tmp_current_page to drive the current_page function.; SELECT ... FROM `xxx_warning`.`projectintellidevicewarning` AS a WHERE a.SysNo IN(SELECT SysNo FROM tmp_current_page) ORDER BY a.`SysNo` DESC;Copy the code

Java Development Specification

All Java beans (Dtos) must implement Serializable and generate serialVersionUID

@Data
public class ProjectAuditQueryFilter extends BaseQueryFilter implements Serializable {
    private static final long serialVersionUID = 8460765917245508495L;
}
Copy the code

Log detailed logs such as detailed and meaningful warning logs for important process nodes. It can be divided into operation and maintenance related and business related. For example, if the user does not register for a channel or is not allowed to push, the push notification will not be written. At this point, the warning log should be recorded so that we can troubleshoot problems and pay attention to the business action process.
Mybatis. Type-aliases -package is not used in Mybatis, and full class name is used in mapper
ResultMap is used in Mybatis, more parameter types and other information are specified in parameters, and mybatis -Generator is used to generate relevant codes.
gitlab merge request: We use the Merge Request of GitLab. Everyone pulls the latest branch based on Develop and merges develop into their own branch before submitting code. Resolve conflicts by themselves first. Make sure the compile passes, the smoke test passes, and then submit the Merge Request, which is reviewed by the appropriate people, and merge into Develop.
Business method names should be intuitive and easy to understand, and method parameters can only appear simple primary key types, such as soId, supplierSkuId, retrunId, etc. Other scenarios must be defined using DomainModel instead of simple parameters. You can define a different DomainModel for each scene, such as a SyncCameraRequest for a synchronization camera,

Some performance, security summary

Try to reduce the amount of data: When retrieving a large amount of data, it will consume more DB, Redis, ES and other disks, memory, CPU, network and other resources.
Lazy data loading: Try to load only the data that the user needs to see at first glance. Other data is driven by the user’s operation behavior.
Critical high-concurrency services require pressure measurement to measure system capacity with data so that capacity can be assessed.
There should be clear monitoring, disaster recovery and other operations and maintenance plan.

The resources

MySQL high performance table design specification
MySQL database design specification
Mysql SQL_CALC_FOUND_ROWS uses things like count(*) for higher performance
MySQL Documentation
A slow query exposes hidden problems
==MySQL index and query optimization summary ==
==Spring MVC/Boot unified exception handling best practice ==

Pay attention to the public number exchange learning

Java+MySQL development specification

Mysql development specification

MySQL design counterexample and relevant experience summary

Java Development Specification

Some performance, security summary

The resources

Pay attention to the public number exchange learning

Related Posts

Talk about processClientBeat for nacOS Service

Grayscale release – the last kilometer before going online

Do you know the built-in COMMAND line tools in the JDK?