亚洲视频二区_亚洲欧洲日本天天堂在线观看_日韩一区二区在线观看_中文字幕不卡一区

• 一、案例背景

• 二、庫表規范

  • 1. 建表相關規范

  • 2. 字段相關規范

  • 3. 索引相關規范

  • 4. 使用相關規范

• 三、建表語句

• 三、語句操作

  • 1. 插入操作

  • 2. 查詢操作

• 四、其他配置

  • 1. 監控活動和性能：

  • 2. 連接數查詢和配置

本文的宗旨在于通過簡單干凈實踐的方式教會讀者，如何更好地使用 MySQL 數據庫。這包括；庫表創建規范、字段的創建規范、索引的創建規范以及SQL使用的相關規范，通過這些內容的講解，讓讀者更好使用 MySQL 數據庫，創建出符合規范的表和字段以及建出合適的索引。

如果你還想學習更深入的 MySQL 知識，建議可以閱讀下官網的參考手冊，這比任何一個資料都要有權威性。

• MySQL 5.7 參考手冊
• MySQL 8.0 參考手冊

本文涉及的工程【導表語句】：https://gitcode.NET/KnowledgePlanet/road-map/xfg-dev-tech-mysql

• road_map_5.6.sql
• road_map_8.0.sql

一、案例背景

此案例背景定位于使用使用一個簡單的訂單表，來講解 MySQL 使用的相關規范。包括；表的引擎、命名約束、字段長度、金額類型、更新時間、索引字段、組合索引等內容，方便大家學習以后，可以基于這些字段的規范演示講解，在自己創建庫表的時候有個參考對照，盡可能創建出性能更佳的庫表和索引。

二、庫表規范

為了能讓讀者更加清晰地看到這些相關規范都是如何體現的，小傅哥這里準備了個大圖，把庫表字段和規范全部整合在一起，方便學習使用。如下；

如上所列規范包括：建表相關規范、字段相關規范、索引相關規范、使用相關規范。

1. 建表相關規范

1. 庫名、表名、字段名，使用小寫和下劃線 _ 分割
2. 庫名、表名、字段名，不超過12個字符。默認支持64個字符。
3. 庫名、表名、字段名，見名知意，建議使用名詞而不是動詞。
4. 使用 InnoDB 存儲引擎。支持；事務、鎖、高并發 性能好。
5. 推薦使用 utf8mb4 可以存emoji
6. 單表字段數，建議不超過40個

2. 字段相關規范

1. 整型定義中不顯示設置長度，如使用 INT，而不是INT(4)
2. 存儲精度浮點數，使用 DECIMAL 替代 FLOAT、DOUBLE
3. 所有字段，都要有 Comment 描述
4. 所有字段應定義為 NOT NULL
5. 超過2038年，用DATETIME存儲
6. 短數據類型 0~80 選用 TINYINT 存儲
7. UUID 有全局唯一統一字段屬性，適合做同步ES使用。
8. IPV4，用無符號 INT 存儲
9. IPV6，用VARBINARY存儲
10. JSON MySql 8.x 新增特性
11. update_time 設置 on update 更新屬性

3. 索引相關規范

1. 要求有自增ID作為主鍵，不要使用隨機性較強的 order_id 作為主鍵，會導致innodb內部page分裂和大量隨機I/O，性能下降。
2. 單表索引建議控制在5個以內，單索引字段數不超過5個。注意：已有idx(a, b)索引，又有idx(a)索引，可以把idx(a)刪了，浪費空間，降低更新、寫入性能。* 單個索引中，每個索引記錄的長度不能超過64KB
3. 利用覆蓋索引來進行查詢操作，避免回表。另外建組合索引的時候，區分度最高的在最左邊。
4. select(count(distinct(字段)))/count(id) = 1 的區分度，更適合建索引。在一些低區分度的字段，例如type、status上建立獨立索引幾乎沒意義，降低更新、寫入性能。
5. 防止因字段不同造成的隱式轉換，導致索引失效。
6. 更新頻繁的字段，不要建索引。

4. 使用相關規范

1. 單表數據量不超過500萬行，ibc 文件大小不超過 2G
2. 水平分表用取模，日志、報表類，可以用日期
3. 單實例表數目小于 500
4. alter表之前，先判斷表數據量，對于超過100W行記錄的表進行alter table，必須在業務低峰期執行。因為alter table會產生表鎖，期間阻塞對于該表的所有寫入
5. SELECT語句必須指定具體字段名稱，禁止寫成 “*”select * 會將不需要讀的數據也從MySQL里讀出來，造成網卡壓力，數據表字段一旦更新，但model層沒有來得及更新的話，系統會報錯
6. insert語句指定具體字段名稱，不要寫成 insert into t1 values(…)
7. insert into…values(XX),(XX),(XX).. 這里XX的值不要超過5000個，值過多會引起主從同步延遲變大。
8. union all 和 union，不要超過5個子句，如果沒有去重的需求，使用union all性能更好。
9. in 值列表限制在500以內，例如 select… where userid in(….500個以內…)，可以減少底層掃描，減輕數據庫壓力。
10. 除靜態表或小表（100行以內），DML語句必須有where條件，且盡量使用索引查找
11. 生產環境禁止使用 hint，如 sql_no_cache，force index，ignore key，strAIght join等。 要相信MySQL優化器。hint是用來強制SQL按照某個執行計劃來執行，但隨著數據量變化我們無法保證自己當初的預判是正確的。
12. where條件里，等號左右字段類型必須一致，否則會造成隱式的類型轉化，可能導致無法使用索引
13. 生產數據庫中強烈不推薦在大表執行全表掃描，查詢數據量不要超過表行數的25%，否則可能導致無法使用索引
14. where子句中禁止只使用全模糊的LIKE條件進行查找，如like ‘%abc%’，必須有其他等值或范圍查詢條件，否則可能導致無法使用索引
15. 索引列不要使用函數或表達式，如 where length(name)=10 或 where user_id+2=1002，否則可能導致無法使用索引
16. 減少使用or語句 or有可能被 mysq l優化為支持索引，但也要損耗 mysql 的 cpu 性能。可將or語句優化為union，然后在各個where條件上建立索引。如 where a=1 or b=2 優化為 where a=1… union …where b=2, key(a),key(b) 某些場景下，也可優化為 in
17. 分頁查詢，當limit起點較高時，可先用過濾條件進行過濾。如 select a,b,c from t1 limit 10000,20; 優化為 select a,b,c from t1 where id>10000 limit 20;
18. 同表的字段增刪、索引增刪等，合并成一條DDL語句執行，提高執行效率，減少與數據庫的交互。
19. replace into 和 insert on duplicate key update 在并發環境下執行都可能產生死鎖（后者在5.6版本可能不報錯，但數據有可能產生問題），需要catch異常，做事務回滾，具體的鎖沖突可以關注next key lock和insert intention lock
20. TRUNCATE TABLE 比  DELETE 速度快，且使用的系統和事務日志資源少，但 TRUNCATE 無事務且不觸發 trigger ，有可能造成事故，故不建議在開發代碼中使用此語句。說明： TRUNCATE TABLE 在功能上與不帶  WHERE 子句的  DELETE 語句相同。

三、建表語句

環境說明；

• MySQL 8.0.32 - 可使用 Docker 安裝，腳本放到本案例倉庫了。
• Sequel Ace

# ************************************************************
# Sequel Ace SQL dump
# 版本號： 20050
#
# https://sequel-ace.com/
# https://Github.com/Sequel-Ace/Sequel-Ace
#
# 主機: localhost (MySQL 8.0.32)
# 數據庫: road_map
# 生成時間: 2023-08-12 07:19:03 +0000
# ************************************************************


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
SET NAMES utf8mb4;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE='NO_AUTO_VALUE_ON_ZERO', SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


# 轉儲表 user_order
# ------------------------------------------------------------

DROP TABLE IF EXISTS `user_order`;

CREATE TABLE `user_order` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID；【必須保留自增ID，不要將一些有隨機特性的字段值設計為主鍵，例如order_id，會導致innodb內部page分裂和大量隨機I/O，性能下降】int 大約21億左右，超過會報錯。bigint 大約9千億左右。',
  `user_name` varchar(64) NOT NULL COMMENT '用戶姓名；',
  `user_id` varchar(24) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用戶編號；',
  `user_mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用戶電話；使用varchar(20)存儲手機號，不要使用整型。手機號不會做數學計算、涉及到區號或者國家代號，可能出現+-()、支持模糊查詢，例如：like“135%”',
  `sku` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '商品編號',
  `sku_name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '商品名稱',
  `order_id` varchar(64) NOT NULL COMMENT '訂單ID',
  `quantity` int NOT NULL DEFAULT '1' COMMENT '商品數量；整形定義中不顯示規定顯示長度，比如使用 INT，而不使用 INT(4)',
  `unit_price` decimal(10,2) NOT NULL COMMENT '商品價格；小數類型為 decimal，禁止使用 float、double',
  `discount_amount` decimal(10,2) NOT NULL COMMENT '折扣金額；',
  `tax` decimal(4,2) NOT NULL COMMENT '費率金額；',
  `total_amount` decimal(10,2) NOT NULL COMMENT '支付金額；(商品的總金額 - 折扣) * (1 - 費率)',
  `order_date` datetime NOT NULL COMMENT '訂單日期；timestamp的時間范圍在1970-01-01 00:00:01到2038-01-01 00:00:00之間',
  `order_status` tinyint(1) NOT NULL COMMENT '訂單狀態；0 創建、1完成、2掉單、3關單 【不要使用 enum 要使用 tinyint 替代。0-80 范圍，都可以使用 tinyint】',
  `is_delete` tinyint(1) NOT NULL DEFAULT '0' COMMENT '邏輯刪單；0未刪除，1已刪除 【表達是否概念的字段必須使用is_】',
  `uuid` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '唯一索引；分布式下全局唯一，用于binlog 同步 ES 方便使用',
  `ipv4` int unsigned NOT NULL DEFAULT '2130706433' COMMENT '設備地址；存儲IPV4地址，通過MySQL 函數轉換，inet_ntoa、inet_aton 示例；SELECT INET_ATON(‘209.207.224.40′); 3520061480 SELECT INET_NTOA(3520061480); 209.207.224.40所有字段定義為NOT NULL，并設置默認值，因為null值的字段會導致每一行都占用額外存儲空間\n數據遷移容易出錯，在聚合函數計算結果偏差（如count結果不準）并且null的列使索引/索引統計/值比較都更加復雜，MySQL內部需要進行特殊處理，表中有較多空字段的時候，數據庫性能下降嚴重。開發中null只能采用is null或is not null檢索，而不能采用=、in、<、<>、!=、not in這些操作符號。如：where name!=’abc’，如果存在name為null值的記錄，查詢結果就不會包含name為null值的記錄',
  `ipv6` varbinary(16) NOT NULL COMMENT '設備地址；存儲IPV6地址，VARBINARY(16)  插入：INET6_ATON(''2001:0db8:85a3:0000:0000:8a2e:0370:7334'') 查詢：SELECT INET6_NTOA(ip_address) ',
  `ext_data` json NOT NULL COMMENT '擴展數據；記錄下單時用戶的設備環境等信息(核心業務字段，要單獨拆表)。【select user_name, ext_data, ext_data->>''$.device'', ext_data->>''$.device.machine'' from `user_order`;】',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_orderid` (`order_id`),
  UNIQUE KEY `uq_uuid` (`uuid`),
  KEY `idx_order_date` (`order_date`),
  KEY `idx_sku_unit_price_total_amount` (`sku`,`unit_price`,`total_amount`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

LOCK TABLES `user_order` WRITE;
/*!40000 ALTER TABLE `user_order` DISABLE KEYS */;

INSERT INTO `user_order` (`id`, `user_name`, `user_id`, `user_mobile`, `sku`, `sku_name`, `order_id`, `quantity`, `unit_price`, `discount_amount`, `tax`, `total_amount`, `order_date`, `order_status`, `is_delete`, `uuid`, `ipv4`, `ipv6`, `ext_data`, `update_time`, `create_time`)
VALUES
 (1,'小傅哥','U001','13512345678','SKU001','Mac Pro M2 貼膜','ORD001',2,10.99,2.00,0.50,19.48,'2023-08-12 10:00:00',0,0,'uuid001',2130706433,X'20010DB885A3000000008A2E03707334','{"device": {"machine": "iphone 14 Pro", "location": "shanghai"}}','2023-08-12 10:00:00','2023-08-12 10:00:00'),
 (2,'福祿娃','U002','13698765432','SKU002','IPad mini4 外套','ORD002',1,25.99,0.00,1.50,24.49,'2023-08-12 11:30:00',1,0,'uuid002',2130706433,X'20010DB885A3000000008A2E03707334','{"device": {"machine": "PC windows", "location": "BeiJing"}}','2023-08-12 11:30:00','2023-08-12 11:30:00'),
 (3,'拎瓢沖','U003','13755555555','SKU003','數據線','ORD003',3,9.99,1.50,0.00,26.97,'2023-08-12 13:45:00',0,0,'uuid003',2130706433,X'20010DB885A3000000008A2E03707334','{"device": {"machine": "PC Windows", "location": "BeiJing"}}','2023-08-12 13:45:00','2023-08-12 13:45:00'),
 (4,'熏5null','U004','13812345678','SKU004','U盤','ORD004',1,15.99,0.00,0.75,15.24,'2023-08-12 14:20:00',1,0,'uuid004',2130706433,X'20010DB885A3000000008A2E03707334','{"device": {"machine": "PC Windows", "location": "BeiJing"}}','2023-08-12 14:20:00','2023-08-12 14:20:00'),
 (5,'溫柔一刀','U005','13999999999','SKU005','坐墊','ORD005',2,12.50,1.25,0.25,23.75,'2023-08-12 15:55:00',0,0,'uuid005',2130706433,X'20010DB885A3000000008A2E03707334','{"device": {"machine": "PC Windows", "location": "BeiJing"}}','2023-08-12 15:55:00','2023-08-12 15:55:00');

/*!40000 ALTER TABLE `user_order` ENABLE KEYS */;
UNLOCK TABLES;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

三、語句操作

1. 插入操作

INSERT INTO `user_order` (`id`, `user_name`, `user_id`, `user_mobile`, `sku`, `sku_name`, `order_id`, `quantity`, `unit_price`, `discount_amount`, `tax`, `total_amount`, `order_date`, `order_status`, `is_delete`, `uuid`, `ipv4`, `ipv6`, `ext_data`, `update_time`, `create_time`)
VALUES
 (9,'小傅哥','U001','13512345678','SKU001','Mac Pro M2 貼膜','ORD0101',2,10.99,2.00,0.50,19.48,'2023-08-12 10:00:00',0,0,'uuid010',INET_ATON('127.0.0.1'),INET6_ATON('2001:0db8:85a3:0000:0000:8a2e:0370:7334'),'{"device": {"machine": "IPhone 14 Pro", "location": "shanghai"}}','2023-08-12 10:00:00','2023-08-12 10:00:00');

• 其實列了這個 SQL 主要讓大家注意到 IPV4、IPV6 的存儲需要用到轉換函數。也就是 MySQL 自己提供的 INET_ATON、INET6_ATON 轉換和對應的 INET_NTON、INET6_NTON 解析。
• 此外你還可以單獨測試這個函數；select INET6_NTOA(INET6_ATON('2001:0db8:85a3:0000:0000:8a2e:0370:7334'))

2. 查詢操作

2.1 IP 查詢

select user_name, sku, INET_NTOA(ipv4), INET6_NTOA(ipv6) from `user_order`;

小傅哥 SKU001 127.0.0.1 2001:db8:85a3::8a2e:370:7334
福祿娃 SKU002 127.0.0.1 2001:db8:85a3::8a2e:370:7334
拎瓢沖 SKU003 127.0.0.1 2001:db8:85a3::8a2e:370:7334
熏5null SKU004 127.0.0.1 2001:db8:85a3::8a2e:370:7334
溫柔一刀 SKU005 127.0.0.1 2001:db8:85a3::8a2e:370:7334

2.2 JSON 查詢

select user_name, ext_data, ext_data->>'$.device', ext_data->>'$.device.machine' from `user_order`;

小傅哥 {"device": {"machine": "IPhone 14 Pro", "location": "shanghai"}} {"machine": "IPhone 14 Pro", "location": "shanghai"} IPhone 14 Pro
福祿娃 {"device": {"machine": "PC Windows", "location": "BeiJing"}} {"machine": "PC Windows", "location": "BeiJing"} PC Windows
拎瓢沖 {"device": {"machine": "PC Windows", "location": "BeiJing"}} {"machine": "PC Windows", "location": "BeiJing"} PC Windows
熏5null {"device": {"machine": "PC Windows", "location": "BeiJing"}} {"machine": "PC Windows", "location": "BeiJing"} PC Windows
溫柔一刀 {"device": {"machine": "PC Windows", "location": "BeiJing"}} {"machine": "PC Windows", "location": "BeiJing"} PC Windows

• MySQL 8.0 提供了 JSON 這樣的專屬存放方式，你可以通過 JSON 字段的內容來讀取對應的信息。

2.3 索引使用

# 使用 order_id 唯一索引
EXPLAIN select user_name, sku, INET_NTOA(ipv4), INET6_NTOA(ipv6) from `user_order` where order_id = 'ORD002';

# 使用組合索引
EXPLAIN select sku,total_amount,order_date from `user_order` where total_amount > 10 and order_date between '2023-08-09 00:00:00' and '2023-08-09 23:59:59';

2.4 數量統計

select count(*) from `user_order`

• 不要使用 count(列名) 或 count(常量) 來替代 count(*) ，count(*) 是 SQL 92 定義的標準統計行數的語法，跟數據庫無關，跟 NULL 和非 NULL 無關。

2.5 for update

START TRANSACTION;

SELECT user_name, sku, total_amount, order_date, order_status FROM `user_order` WHERE order_id = 'ORD002' FOR UPDATE;

-- 在這里執行其他操作，其他會話無法修改 order_id 為 ORD002 的訂單信息

COMMIT;

2.6 行級鎖

UPDATE `user_order` SET order_status = 0 WHERE order_id = 'ORD002' AND order_status = 3 FOR UPDATE;

• order_id 是唯一索引，如果沒有索引，將會執行全表掃描。在這種情況下，MySQL會對整個user_order 表進行鎖定，而不僅僅是符合條件的行。
• 即使你沒有顯式地添加 FOR UPDATE 語句，更新語句仍會鎖定符合條件的行。這是因為MySQL 默認會使用行級鎖來保證并發事務的一致性。

2.7 表鎖

• ALTER TABLE語句：當執行ALTER TABLE語句修改表結構時，MySQL會自動獲取一個排它鎖（X鎖），這會阻塞其他會話對該表的讀寫操作，直到ALTER TABLE操作完成。

• LOCK TABLES語句：當使用LOCK TABLES語句手動鎖定表時，會對被鎖定的表使用表級別的鎖，阻塞其他會話對該表的讀寫操作。

• TRUNCATE TABLE語句：TRUNCATE TABLE語句會獲取一個排它鎖（X鎖），阻塞其他會話對該表的讀寫操作，直到TRUNCATE TABLE操作完成。

四、其他配置

1. 監控活動和性能：

在MySQL中，你可以使用以下命令來監控MySQL服務器的活動和性能：

• SHOW PROCESSLIST;：該命令用于顯示當前正在運行的所有MySQL連接和查詢。它將顯示每個連接的ID、用戶、主機、數據庫、執行時間和當前執行的查詢。
• SHOW STATUS;：該命令用于顯示MySQL服務器的各種狀態信息，例如連接數、線程狀態、查詢緩存命中率等。
• SHOW ENGINE INNODB STATUS;：該命令用于顯示InnoDB存儲引擎的詳細狀態信息，包括死鎖信息、事務信息和緩沖池狀態等。
• EXPLAIN：在查詢語句前加上EXPLAIN關鍵字，可以獲取查詢執行計劃的詳細信息。這將顯示查詢的表訪問順序、使用的索引和可能的性能問題。
• mysqladmin extended-status：該命令用于顯示MySQL服務器的擴展狀態信息，包括各種計數器和性能指標。

2. 連接數查詢和配置

查看MySQL服務器的可用連接數和設置連接數，可以使用以下方法：

1. 查看當前可用連接數：
   • 使用命令行客戶端登錄到MySQL服務器。
   • 執行以下SQL查詢語句：SHOW VARIABLES LIKE 'max_connections';
   • 這將顯示MySQL服務器當前配置的最大連接數。
2. 設置連接數：
   • 編輯MySQL服務器的配置文件（通常是my.cnf或my.ini）。
   • 找到[mysqld]部分。
   • 添加或修改以下行：max_connections = <desired_value>
   • 將<desired_value>替換為你希望設置的連接數。
   • 保存并關閉配置文件。
   • 重啟MySQL服務器，以使更改生效。

設置連接數需要權衡服務器的可用資源和性能。如果設置的連接數過高，可能會導致服務器負載過重，影響性能。建議根據服務器的硬件規格和預期的負載量來調整連接數。另外，某些MySQL版本或發行版可能對最大連接數有特定的限制，請確保你的設置在允許范圍內。

注意：1核1G可配置300個連接、2核4G可配置1000個連接、4核16G可配置4000個連接、8核32G可配置8000個連接。