[Java分散式架構實戰]Java+MySQL開發規範

戒嗔發表於2019-01-14
Java分散式架構MySql

Mysql開發規範

  • 嚴禁在sql script中使用SELECT * , 業務當中需要使用哪些欄位, 就返回哪些欄位.
  • 統計行數時，使用 select count(*) from xxx
  • 所有的SQL關鍵字以及系統函式都使用全大寫的方式. 例如: SELECT, INSERT, UPDATE, CURRENT_TIMESTAMP(), ORDER BY, GROUP BY, INNER JOIN, LEFT JOIN.
  • 多個表Join時, 必須指定別名, 參考如下的例子: 注意：應儘量減少錶連結，同時注意驅動表的選擇，索引等問題。
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
複製程式碼
  • 資料庫，表，欄位的編碼使用utf8mb4
  • 欄位名命名可讀性要強，常用欄位比如手機號，郵箱要保持相同的命名。
  • InDate,EditDate建議取資料庫時間NOW(3)
  • 通常表中要有以下通用欄位, 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 '系統編號',
  `role_name` VARCHAR(45) NOT NULL COMMENT '角色名稱',
  `status` TINYINT NOT NULL COMMENT '狀態：0--無效，1--有效',
  `system_status` TINYINT NOT NULL COMMENT '系統狀態：0--已刪除，1--正常',
  `in_user_id` BIGINT NOT NULL COMMENT '建立人系統編號',
  `in_user_name` VARCHAR(45) NOT NULL COMMENT '建立人姓名',
  `in_date` DATETIME NOT NULL COMMENT '建立時間',
  `edit_user_id` BIGINT NOT NULL COMMENT '最後編輯人系統編號',
  `edit_user_name` VARCHAR(45) NOT NULL COMMENT '最後編輯人姓名',
  `edit_date` DATETIME NOT NULL COMMENT '最後編輯時間',
  PRIMARY KEY PK_system_role(`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COMMENT '系統角色';
複製程式碼
  • 資料庫欄位儘量不為NULL
  • 資料庫欄位不使用預設值。
  • 主鍵，外來鍵，常用於檢索的日期欄位加索引，只有少量狀態值的欄位不加索引

MySQL設計反例及相關經驗總結

回顧過去一兩年參與的專案中遇到的一些不好的設計：

  • 欄位長度過長，比如ProjectName varchar(500), 感覺真的有點不太合理，後來我專門去生產環境資料庫中查詢了一下最大長度只有131，所以根據這個情況，最大冗餘255個字元就相對合理了。
  • 欄位名設計不合理：專案的地理位置經度，緯度被設計為LocationX, LocationY。呃，顯然不太好，應該是經度longitude，緯度latitude。
  • 資料庫通用欄位CommonStatus的值被定義為-999--已刪除（邏輯刪除），0--無效，1--有效。現在回過頭來看，應該拆分為兩個欄位"系統狀態"(SystemStatus TINY INT, 0--已刪除，1--正常),資料狀態(DataStatus TINYINT, 0--禁用，1--啟用)
  • InDate,EditDate建議取資料庫時間NOW(3)。有一些場景特別關注時間的，比如物聯網雲對雲模式採集資料時，供應商資料上報時間會攜帶在訊息體中，平臺在接收到資料時會將訊息扔到MQ中，MQ的訂閱者在接收到訊息時，會持久化。在這個場景中應該根據業務要求分別對待這些時間點。比如在資料持久化時建議使用MySQL系統時間NOW(3)。
  • 在一些關係表設計中，建議使用具體唯一性的欄位做為唯一索引（比如role_id, user_id）。比如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 '系統編號',
  `role_id` BIGINT NOT NULL COMMENT '角色編號',
  `user_id` BIGINT NOT NULL COMMENT '使用者編號',
  `system_status` TINYINT NOT NULL COMMENT '0--已刪除，1--正常',
  `in_user_id` BIGINT NOT NULL COMMENT '建立人系統編號',
  `in_user_name` VARCHAR(45) NOT NULL COMMENT '建立人姓名',
  `in_date` DATETIME NOT NULL COMMENT '建立時間',
  `edit_user_id` BIGINT NOT NULL COMMENT '最後編輯人系統編號',
  `edit_user_name` VARCHAR(45) NOT NULL COMMENT '最後編輯人姓名',
  `edit_date` DATETIME NOT NULL COMMENT '最後編輯時間',
  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 '角色與使用者的關係';
複製程式碼
  • SQL_CALC_FOUND_ROWS在資料量比較大的表上使用時，效能不穩定。建議還是使用count(*)語句來計算總行數。或者在一些可以不關注總行數的業務場景中，可以不計算，比如app上的滾動到底部載入下一頁的場景。
  • 在IN語句中不要有過多的值，比如限定不能有超過20個。超過了閾值的業務場景應該考慮換一種寫法，比如使用臨時表+連線的方式。
  • 在join,where的條件中如果欄位的型別(比如一個是字串，一個是數字)或編碼不一致（比如一個是utf8,一個是utf8mb4）時，可能會導致full table scan或full index scan。
  • 要特別注意where語句中出現or, union等複雜子查詢的場景。這種情況一般可以使用臨時表+連線來優化效能。
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
...

# 在下面的查詢中，使用tmp_pro來驅動連線
複製程式碼
  • 儘量減少表連線，如果需要使用，那麼需要特別關注相關連線欄位，篩選條件，排序條件是否有索引，索引是否合理（比如考慮覆蓋索引，減少filesort等等），驅動表選擇是否合理。當連線比較多時，會導致程式碼難以理解，遇到問題時很容易無從下手。
  • 在大表分頁場景，應儘量減少大表連線，可以先從大表主鍵分頁，取當前分頁的資料，然後再回表取每一行的資料。比如
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};

### 這裡不用IN，然後由小表tmp_current_page驅動效果可能會更好吧!
;SELECT
...
FROM
`xxx_warning`.`projectintellidevicewarning` AS a
WHERE a.SysNo IN(SELECT SysNo FROM tmp_current_page)
ORDER BY a.`SysNo` DESC;
複製程式碼

Java開發規範

  • 所有Java Bean(DTO)必須實現Serializable,並生成serialVersionUID
@Data
public class ProjectAuditQueryFilter extends BaseQueryFilter implements Serializable {
    private static final long serialVersionUID = 8460765917245508495L;
}
複製程式碼
  • 記錄詳細的日誌，比如重要的流程節點記錄詳細的有意義的warning log。又可以分為運維相關和業務相關。比如在寫業務待辦通知時，如果使用者沒有註冊通道或者不允許推送，那麼在寫業務待辦時就不會寫推送通知。此時應該記錄warning log, 以便於我們排查問題，關注業務動作過程。
  • mybatis中不使用mybatis.type-aliases-package, 在mapper中使用全類名
  • mybatis中使用resultMap，引數中指定更多的引數型別等資訊，使用mybatis-generator來生成相關程式碼。
  • gitlab merge request: 我們使用gitlab的merge request來搞，每個人基於develop拉取最新的分支，每次提交程式碼前將develop（現在是新專案，就直接是develop, 以後是各個feature分支）合併到自己的分支，自己先將衝突解決掉，保證編譯通過，冒煙測試通過，然後提交merge request, 由相關的人進行程式碼稽核，稽核通過後，會merge到develop。
  • 業務方法命名要直觀容易理解，同時方法引數只能出現主鍵類簡單型別，比如soId, supplierSkuId, retrunId等，其它場景 必須使用DomainModel來定義，不能直接使用簡單引數。可以針對每個場景定義不同的DomainModel, 比如同步攝像頭可以定義為SyncCameraRequest,

一些效能，安全方面的總結

  • 儘量減少取資料的數量: 取大量資料時，會耗費更多DB，redis， es等等磁碟，記憶體，CPU，網路等資源。
  • 延遲載入資料：儘量只載入使用者第一眼需要看的資料，其它資料由使用者的操作行為驅動。
  • 關鍵的高併發業務需要有壓測，用資料來衡量系統容量，進而才能做好容量評估。
  • 要有明確的監控，災難恢復等運維兜底方案。

參考資料

關注公眾號交流學習

關注Java分散式架構實戰, 持續精進

聯絡我，學習交流

相關文章