# TiDB数据库命名规范

# 1. 数据库命名
– 使用小写字母和下划线
– 格式：业务_模块，如：fgedu_order、fgedu_user
– 长度不超过32个字符
– 避免使用保留字

# 2. 表命名
– 使用小写字母和下划线
– 格式：模块_表名，如：fgedu_orders、fgedu_users
– 表名使用复数形式
– 长度不超过64个字符

# 3. 字段命名
– 使用小写字母和下划线
– 主键：id 或 表名_id
– 外键：关联表名_id
– 时间字段：created_at、updated_at
– 状态字段：status、is_deleted

# 4. 索引命名
– 主键：PRIMARY
– 唯一索引：uk_字段名
– 普通索引：idx_字段名
– 组合索引：idx_字段1_字段2

# SQL编写规范

# 1. 关键字使用大写
SELECT id, username, email
FROM fgedu_users
WHERE status = ‘active’
ORDER BY created_at DESC
LIMIT 10;

# 2. 避免使用SELECT *
# 错误
SELECT * FROM fgedu_orders;

# 正确
SELECT order_id, user_id, amount, status
FROM fgedu_orders;

# 3. 使用显式字段列表插入
# 错误
INSERT INTO fgedu_users VALUES (1, ‘fgedu01’, ‘fgedu01@fgedu.net.cn’);

# 正确
INSERT INTO fgedu_users (id, username, email)
VALUES (1, ‘fgedu01’, ‘fgedu01@fgedu.net.cn’);

# 4. 使用预编译语句
# 推荐
PREPARE stmt FROM ‘SELECT * FROM fgedu_users WHERE id = ?’;
SET @id = 1;
EXECUTE stmt USING @id;

# 5. 避免在WHERE条件中使用函数
# 错误
SELECT * FROM fgedu_users WHERE DATE(created_at) = ‘2024-04-09’;

# 正确
SELECT * FROM fgedu_users
WHERE created_at >= ‘2024-04-09 00:00:00’
AND created_at < '2024-04-10 00:00:00';

# 索引设计规范

# 1. 主键索引
– 每个表必须有主键
– 主键建议使用自增ID或雪花算法ID
– 避免使用业务字段作为主键

# 2. 唯一索引
– 用于唯一约束的字段
– 如：用户名、邮箱、手机号

# 3. 普通索引
– 频繁查询的字段
– 外键字段
– 排序字段

# 4. 组合索引
– 遵循最左前缀原则
– 区分度高的字段放在前面
– 避免过多字段组合

# 示例
CREATE TABLE fgedu_orders (
order_id BIGINT PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT NOT NULL,
order_no VARCHAR(32) NOT NULL,
amount DECIMAL(10,2) NOT NULL,
status VARCHAR(20) DEFAULT ‘pending’,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_order_no (order_no),
KEY idx_user_id (user_id),
KEY idx_status_created (status, created_at)
);

# 查看执行计划
mysql> EXPLAIN SELECT * FROM fgedu_users WHERE id = 1;
+————————-+———-+———–+—————+——————————–+
| id | estRows | task | access object | operator info |
+————————-+———-+———–+—————+——————————–+
| Point_Get_1 | 1.00 | root | table:fgedu_users | handle:1 |
+————————-+———-+———–+—————+——————————–+
1 row in set (0.00 sec)

# 复杂查询执行计划
mysql> EXPLAIN SELECT u.username, o.order_no, o.amount
-> FROM fgedu_users u
-> JOIN fgedu_orders o ON u.id = o.user_id
-> WHERE o.status = ‘completed’
-> ORDER BY o.amount DESC
-> LIMIT 10;
+——————————+———-+———–+————————+——————————–+
| id | estRows | task | access object | operator info |
+——————————+———-+———–+————————+——————————–+
| TopN_12 | 10.00 | root | | fgedu_orders.amount:desc, offset:0, count:10 |
| └─HashJoin_17 | 10.00 | root | | inner join, equal:[eq(fgedu_users.id, fgedu_orders.user_id)] |
| ├─TableReader_23(Build) | 10000.00 | root | | data:Selection_22 |
| │ └─Selection_22 | 10000.00 | cop[tikv]| | eq(fgedu_orders.status, “completed”) |
| │ └─TableFullScan_21 | 100000.00| cop[tikv]| table:o | keep order:false, stats:pseudo |
| └─TableReader_20(Probe) | 10000.00 | root | | data:TableFullScan_19 |
| └─TableFullScan_19 | 10000.00 | cop[tikv]| table:u | keep order:false, stats:pseudo |
+——————————+———-+———–+————————+——————————–+
7 rows in set (0.01 sec)

# 优化前：全表扫描
mysql> EXPLAIN SELECT * FROM fgedu_orders WHERE user_id = 10001;
+————————-+———-+———–+——————+——————————–+
| id | estRows | task | access object | operator info |
+————————-+———-+———–+——————+——————————–+
| TableReader_7 | 10000.00 | root | | data:Selection_6 |
| └─Selection_6 | 10000.00 | cop[tikv] | | eq(fgedu_orders.user_id, 10001)|
| └─TableFullScan_5 | 100000.00| cop[tikv] | table:fgedu_orders | keep order:false |
+————————-+———-+———–+——————+——————————–+
3 rows in set (0.00 sec)

# 添加索引
mysql> CREATE INDEX idx_user_id ON fgedu_orders(user_id);
Query OK, 0 rows affected (2.34 sec)

# 优化后：索引扫描
mysql> EXPLAIN SELECT * FROM fgedu_orders WHERE user_id = 10001;
+————————+———-+———–+——————+——————————–+
| id | estRows | task | access object | operator info |
+————————+———-+———–+——————+——————————–+
| IndexLookUp_10 | 10.00 | root | | |
| ├─IndexRangeScan_8(Build)| 10.00 | cop[tikv] | table:fgedu_orders, index:idx_user_id(user_id) | range:[10001,10001], keep order:false |
| └─TableRowIDScan_9(Probe)| 10.00 | cop[tikv] | table:fgedu_orders | keep order:false |
+————————+———-+———–+——————+——————————–+
3 rows in set (0.00 sec)

# 优化前：单条插入
mysql> INSERT INTO fgedu_logs (user_id, action) VALUES (1, ‘login’);
Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO fgedu_logs (user_id, action) VALUES (2, ‘login’);
Query OK, 1 row affected (0.04 sec)

# 优化后：批量插入
mysql> INSERT INTO fgedu_logs (user_id, action) VALUES
-> (1, ‘login’), (2, ‘login’), (3, ‘login’), (4, ‘login’), (5, ‘login’);
Query OK, 5 rows affected (0.06 sec)
Records: 5 Duplicates: 0 Warnings: 0

# 更大批量（建议每批1000-5000条）
# 使用LOAD DATA批量导入
mysql> LOAD DATA LOCAL INFILE ‘/data/fgedu_logs.csv’
-> INTO TABLE fgedu_logs
-> FIELDS TERMINATED BY ‘,’
-> LINES TERMINATED BY ‘\n’
-> (user_id, action, created_at);
Query OK, 100000 rows affected (5.23 sec)
Records: 100000 Deleted: 0 Skipped: 0 Warnings: 0

# 优化前：逐条更新
# 错误做法，性能差

# 优化后：使用CASE WHEN批量更新
mysql> UPDATE fgedu_products
-> SET price = CASE product_id
-> WHEN 1 THEN 45000.00
-> WHEN 2 THEN 2800.00
-> WHEN 3 THEN 14000.00
-> END
-> WHERE product_id IN (1, 2, 3);
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3 Changed: 3 Warnings: 0

# 或者使用JOIN更新
mysql> UPDATE fgedu_products p
-> JOIN fgedu_price_updates pu ON p.product_id = pu.product_id
-> SET p.price = pu.new_price
-> WHERE pu.update_date = ‘2024-04-09’;学习交流加群风哥QQ113257174
Query OK, 50 rows affected (0.15 sec)
Rows matched: 50 Changed: 50 Warnings: 0

# 优化前：大事务
START TRANSACTION;
— 插入100万条数据
INSERT INTO fgedu_logs …;
COMMIT;
— 可能导致OOM或锁超时

# 优化后：分批提交
SET @batch_size = 1000;
SET @total = 1000000;
SET @offset = 0;

WHILE @offset < @total DO START TRANSACTION; INSERT INTO fgedu_logs (user_id, action, created_at) SELECT user_id, 'batch_action', NOW() FROM fgedu_users LIMIT @offset, @batch_size; COMMIT; SET @offset = @offset + @batch_size; END WHILE; # 实际执行 mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO fgedu_logs (user_id, action)
-> SELECT id, ‘batch_insert’ FROM fgedu_users LIMIT 1000;
Query OK, 1000 rows affected (0.05 sec)
Records: 1000 Duplicates: 0 Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.02 sec)