微服务中共享库写入是一个死胡同

ALTER TABLE orders 
DROP COLUMN customer_notes,
ADD COLUMN internal_processing_id INT,
MODIFY COLUMN status ENUM('pending','processing','shipped','delivered');

SELECT * FROM orders WHERE customer_id = ?

SELECT * FROM orders 
WHERE customer_id = ? 
  AND status NOT IN ('cancelled', 'refunded')

SELECT o.*, c.tier_level,
  CASE 
    WHEN c.tier_level = 'premium' THEN o.amount * 0.95
    WHEN o.created_date > DATE_SUB(NOW(), INTERVAL 30 DAY) THEN o.amount * 0.98
    ELSE o.amount
  END as effective_amount
FROM orders o 
JOIN customers c ON o.customer_id = c.id
WHERE o.customer_id = ?
  AND o.status NOT IN ('cancelled', 'refunded')
  AND (o.payment_status = 'completed' OR c.tier_level = 'premium')

SELECT
c.customer_id,
c.email,
SUM(o.total) as lifetime_value,
COUNT(o.id) as order_count,
AVG(p.profit_margin) as avg_margin,
MAX(o.created_date) as last_order
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
LEFT JOIN products p ON o.product_id = p.id
WHERE c.created_date > DATE_SUB(NOW(), INTERVAL 1 YEAR)
GROUP BY c.customer_id
HAVING COUNT(o.id) > 5
ORDER BY lifetime_value DESC
LIMIT 1000;

-- 订单服务账户配置
CREATE USER 'order_service'@'%' IDENTIFIED BY 'secure_password_123!';
GRANT SELECT, INSERT, UPDATE, DELETE ON ecommerce.orders TO 'order_service'@'%';
GRANT SELECT ON ecommerce.customers TO 'order_service'@'%';

-- 客户服务账户配置
CREATE USER 'customer_service'@'%' IDENTIFIED BY 'secure_password_456@'; 
GRANT SELECT ON ecommerce.orders TO 'customer_service'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON ecommerce.customers TO 'customer_service'@'%';

-- 分析服务账户配置(只读所有表)
CREATE USER 'analytics_service'@'%' IDENTIFIED BY 'secure_password_789#';
GRANT SELECT ON ecommerce.* TO 'analytics_service'@'%';

-- 密码策略增强
ALTER USER 'order_service'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'customer_service'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'analytics_service'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;

-- 添加新列（带有默认值）
ALTER TABLE orders ADD COLUMN estimated_delivery_days INT DEFAULT 3;

-- 创建新索引（非阻塞式）
CREATE INDEX idx_orders_region ON orders(region) ALGORITHM=INPLACE, LOCK=NONE;

-- 分批次执行（每个变更单独事务）
BEGIN;
ALTER TABLE orders DROP COLUMN legacy_shipping_code;
COMMIT;

-- 在低峰期执行
SET SESSION lock_wait_timeout = 300;
DROP INDEX idx_orders_legacy ON orders;

ALTER TABLE products ADD CONSTRAINT chk_positive_stock
CHECK (stock_quantity >= 0) NOT ENFORCED; -- 先设置为不强制执行

ALTER TABLE products ALTER CONSTRAINT chk_positive_stock ENFORCED;

ALTER TABLE orders ADD CONSTRAINT fk_order_customer
FOREIGN KEY (customer_id) REFERENCES customers(id)
ON DELETE RESTRICT -- 禁止删除有订单的客户
DEFERRABLE INITIALLY DEFERRED; -- 允许事务结束时验证

CREATE FUNCTION validate_order_status_change(
    old_status VARCHAR(20),
    new_status VARCHAR(20)
) RETURNS BOOLEAN AS $$
BEGIN
    RETURN (
        (old_status = 'pending' AND new_status IN ('processing', 'cancelled')) OR
        (old_status = 'processing' AND new_status IN ('shipped', 'cancelled')) OR
        (old_status = 'shipped' AND new_status = 'delivered') OR
        (old_status = ANY(ARRAY['delivered', 'cancelled']) AND new_status = old_status)
    );
END;
$$ LANGUAGE plpgsql;

ALTER TABLE orders ADD CONSTRAINT chk_valid_status_transition
CHECK (validate_order_status_change(
    (SELECT status FROM orders WHERE id = OLD.id),
    NEW.status
));

-- 使用行级锁的库存扣减事务
BEGIN;
-- 显式锁定要修改的行(避免丢失更新)
SELECT stock_quantity FROM products 
WHERE id = ? AND stock_quantity >= ? 
FOR UPDATE NOWAIT; -- 如果无法立即获取锁则失败

-- 检查库存是否充足
IF NOT FOUND THEN
    ROLLBACK;
    RAISE EXCEPTION 'Insufficient stock or product not found';
END IF;

-- 执行库存扣减
UPDATE products SET stock_quantity = stock_quantity - ? 
WHERE id = ?;

-- 记录库存变更流水
INSERT INTO inventory_transactions 
(product_id, quantity_change, remaining_stock)
VALUES (?, -?, (SELECT stock_quantity FROM products WHERE id = ?));

COMMIT;

SELECT * FROM products 
WHERE category_id = ?
FOR UPDATE SKIP LOCKED
LIMIT 100;

UPDATE products 
SET stock_quantity = stock_quantity - ?,
    version = version + 1
WHERE id = ? AND version = ?;

ALTER SYSTEM SET deadlock_timeout = '1s';

CREATE TABLE data_audit_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  table_name VARCHAR(50),
  operation VARCHAR(10),
  row_id INT,
  old_values JSON,
  new_values JSON,
  changed_by VARCHAR(50),
  service_name VARCHAR(50),
  timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Trigger example for orders table
DELIMITER $$
CREATE TRIGGER orders_audit_update 
AFTER UPDATE ON orders FOR EACH ROW
BEGIN
  INSERT INTO data_audit_log (
    table_name, operation, row_id, old_values, new_values, 
    changed_by, service_name
  ) VALUES (
    'orders', 'UPDATE', NEW.id,
    JSON_OBJECT('status', OLD.status, 'total', OLD.total),
    JSON_OBJECT('status', NEW.status, 'total', NEW.total),
    USER(), 'detected_via_trigger'
  );
END$$
DELIMITER ;

-- Daily check for schema changes
SELECT 
  TABLE_NAME,
  COLUMN_NAME,
  DATA_TYPE,
  IS_NULLABLE,
  COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY TABLE_NAME, ORDINAL_POSITION;

-- Alert if schema changed without going through approval process

-- Log all queries with service identification
SELECT 
  DATE(timestamp) as query_date,
  service_name,
  table_accessed,
  operation_type,
  COUNT(*) as query_count
FROM query_audit_log 
WHERE timestamp >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY DATE(timestamp), service_name, table_accessed, operation_type
ORDER BY query_count DESC;

-- Check for orphaned records
SELECT COUNT(*) as orphaned_orders
FROM orders o 
LEFT JOIN customers c ON o.customer_id = c.id 
WHERE c.id IS NULL;

-- Check for negative inventory
SELECT COUNT(*) as negative_stock_products
FROM products 
WHERE stock_quantity < 0;

-- Check for invalid order totals
SELECT COUNT(*) as invalid_order_totals
FROM orders 
WHERE total != (subtotal + tax + shipping);

-- Example: Fixing inventory corruption
-- Step 1: Put products table in read-only mode
FLUSH TABLES products WITH READ LOCK;

-- Step 2: Calculate correct inventory from order history
CREATE TEMPORARY TABLE correct_inventory AS
SELECT 
  p.id,
  p.initial_stock - COALESCE(SUM(oi.quantity), 0) as correct_stock
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.id  
WHERE o.status IN ('shipped', 'delivered')
GROUP BY p.id;

-- Step 3: Update with correct values
UPDATE products p
JOIN correct_inventory ci ON p.id = ci.id
SET p.stock_quantity = ci.correct_stock;

-- Step 4: Remove read-only lock
UNLOCK TABLES;

-- Always test rollback before making changes
-- Example: Rolling back a column addition

-- Original change:
-- ALTER TABLE orders ADD COLUMN priority INT DEFAULT 1;

-- Rollback plan:
BEGIN;
-- Check if any service is using the new column
SELECT COUNT(*) FROM orders WHERE priority != 1;
-- If count > 0, coordinate with teams before rollback

-- Safe rollback (only if no data in new column)
ALTER TABLE orders DROP COLUMN priority;
COMMIT;

Instead of direct database access:
Customer Service → GET /api/orders/by-customer/123

Order Service exposes:
GET /api/orders/{id}
GET /api/orders/by-customer/{customerId}  
POST /api/orders
PUT /api/orders/{id}/status

Week 1: 10% of reads go through APIs
Week 4: 50% of reads go through APIs
Week 8: 90% of reads go through APIs  
Week 12: 100% reads through APIs, remove direct DB access

-- Create separate databases
CREATE DATABASE customer_service_db;
CREATE DATABASE order_service_db;

-- Migrate data with consistency checks
-- Use database replication for zero-downtime migration