1
-- 读取数据和版本号
2
SELECT id,name,stock,version FROM products WHERE id = 1;
3
-- 假设读出来version=5
4
-- 更新时带上版本号
5
UPDATE products
6
SET stock = stock -1,version = version+1
7
WHERE id = 1 AND version = 5;
8
-- 检查影响行数
9
-- 让一个一返回0说明被别人改过， 需要重试或报错

1
-- 假设读出来stock = 100
2
UPDATE products
3
SET stock = 99
4
WHERE id = 1 AND stock = 100;

1
//处理器接口
2

3
abstract class Handler{
4
    protected Handler nextHandler;
5
    public void setNextHandler(Handler nextHandler){
6
        this.nextHandler = nextHandler;
7
    }
8
    public abstract void handleRequest(String request);
9
}
10

11
//具体处理器A
12
class ConcreteHandlerA extends Handler{
13
    @Override
14
    public void handleRequest(String request){
15
        if("A".equals(request)){
16
            System.out.println("ConcreteHandlerA handled request:"+request);
17
        }else if(nextHandler!=null){
18
            nextHandler.handleRequest(request);
19
        }else{
20
            System.out.println("No handler for request:"+request);
21
        }
22
    }
23
}
24
//具体处理器B
25
class ConcreteHandlerB extends Handler{
26
    @Override
27
    public void handleRequest(String request){
28
        if("B".equals(request)){
29
            System.out.println("ConcreteHandlerB handled request:"+request);
30
        }else if(nextHandler!=null){
31
            nextHandler.handleRequest(request);
32
        }else{
33
            System.out.println("No handler for request:"+request);
34
        }
35
    }
36
}
37

38
//客户端
39
public class Main{
40
    public static void main(String[] args){
41
        Handler handlerA = new ConcreteHandlerA();
42
        Handler handlerB = new ConcreteHandlerB();
43
        handlerA.setNextHandler(handlerB);
44

45
        handlerA.handleRequest("A");    //Output:ConcreteHandlerA handled request:A
46
        handlerA.handleRequest("B");    //Output:ConcreteHandlerA handled request:B
47
        handlerA.handleRequest("C");    //Output:No Handler for request:C
48
    }
49
}

1
//抽象类
2
abstract class DataProcessor{
3
    //模板方法
4
    public final void process(){
5
        readData();
6
        processData();
7
        writeData();
8
    }
9

10
    protected abstract void readData();
11
    protected abstract void processData();
12
    protected void writeData(){
13
        System.out.println("Writing data to output.");
14
    }
15
}
16
//具体实现类A
17
class CSVDataProcessor extends DataProcessor{
18
    @Override
19
    protected void readData(){
20
        System.out.println("Reading data from CSV file.");
21
    }
22
    @Override
23
    protected void processData(){
24
        System.out.println("Processing CSV data.");
25
    }
26
}
27
//具体实现类B
28
class JSONDataProcessor extends DataProcessor{
29
    @Override
30
    protected void readData(){
31
        System.out.println("Reading data from JSON file.");
32
    }
33
    @Override
34
    protected void processData(){
35
        System.out.println("Processing JSON data.");
36
    }
37
}
38
//客户端
39
public class Main{
40
    public static void main(String[] args){
41
        DataProcessor csvProcessor = new CSVDataProcessor();
42
        csvProcessor.process();
43

44
        JSONDataProcessor jsonProcessor = new JSONDataProcessor();
45
        jsonProcessor.process();

1
-- MyISAM 表，不带 WHERE 条件
2
SELECT count(*) FROM myisam_table;  -- 直接返回维护好的行数，极快
3
SELECT count(*) FROM myisam_table WHERE status = 1;  -- 带条件就要扫描了

1
-- 查看 MySQL 是否用了并行扫描
2
EXPLAIN SELECT count(*) FROM large_table;
3
-- Extra 列可能出现 Parallel scan

1
-- 统计有多少用户填写了手机号
2
SELECT count(phone) FROM user;
3

4
-- 统计不同状态的订单数量
5
SELECT status, count(status) FROM orders GROUP BY status;
6

7
-- 配合 DISTINCT 统计去重后的数量
8
SELECT count(DISTINCT city) FROM user;  -- 用户分布在多少个城市

1
import java.util.ArrayList;
2
import java.util.List;
3

4
// 观察者接口
5
interface Observer{
6
    void update(String message);
7
}
8

9
// 被观察者接口
10
interface Subject{
11
    void addObserver(Observer observer);
12
    void removeObserver(Observer observer);
13
    void notifyObservers(String message);
14
}
15

16
// 具体被观察者
17
class ConcreteSubject implements Subject{
18
    private List<Observer> observers = new ArrayList<>();
19
    private String state;
20

21
    @Override
22
    public void addObserver(Observer observer){
23
        observers.add(observer);
24
    }
25

26
    @Override
27
    public void removeObserver(Observer observer){
28
        observers.remove(observer);
29
    }
30

31
    @Override
32
    public void notifyObservers(String message){
33
        for(Observer observer : observers){
34
            observer.update(message);
35
        }
36
    }
37
    public void setState(String state){
38
        this.state = state;
39
    }
40
}
41

42
// 具体观察者
43
class ConcreteObserver implements Observer{
44
    private String name;
45

46
    public ConcreteObserver(String name){
47
        this.name = name;
48
    }
49

50
    @Override
51
    public void update(String message){
52
        System.out.println(name + " received message: " + message);
53
    }
54
}
55

56
//客户端
57
public class Main{
58
    public static void main(String[] args){
59
        ConcreteSubject subject = new ConcreteSubject();
60
        ConcreteObserver observer1 = new ConcreteObserver("Observer1");
61
        ConcreteObserver observer2 = new ConcreteObserver("Observer2");
62

63
        subject.addObserver(observer1);
64
        subject.addObserver(observer2);
65

66
        subject.setState("New State");
67
        subject.notifyObservers("Hello Observers!");
68
    }
69
}

1
-- 一个典型的分析案例
2
EXPLAIN SELECT * FROM orders WHERE user_id = 10086 ORDER BY create_time;
3

4
-- 如果 Extra 显示 Using filesort，说明 create_time 排序没走索引
5
-- 优化方案：建联合索引 (user_id, create_time)

1
-- 索引失效
2
SELECT * FROM users WHERE YEAR(birthday) = 1990;
3
-- 优化后
4
SELECT * FROM users WHERE birthday >= '1990-01-01' AND birthday < '1991-01-01';

1
-- phone 是 varchar 类型，传入数字会触发隐式转换
2
SELECT * FROM users WHERE phone = 13800138000;  -- 索引失效
3
SELECT * FROM users WHERE phone = '13800138000';  -- 正常走索引

1
-- 假设 name 有索引，age 没有
2
SELECT * FROM users WHERE name = '张三' OR age = 25;  -- 全表扫描

1
-- 索引是 (a, b, c)
2
SELECT * FROM t WHERE b = 1;  -- 索引失效
3
SELECT * FROM t WHERE a = 1 AND c = 3;  -- 只能用到 a

1
-- 索引失效
2
SELECT * FROM users WHERE name LIKE '%张三';
3
-- 优化后
4
SELECT * FROM users WHERE name LIKE '张三%';

1
-- 假设索引是 (a, b)
2
SELECT * FROM t WHERE a = 1 AND b = 2;  -- 可以用到 a, b
3
SELECT * FROM t WHERE b = 2 AND a = 1;  -- 索引失效，优化器会选择用 b 索引

1
-- 开启慢查询日志
2
SET GLOBAL slow_query_log = ON;
3
-- 设置阈值，执行时间超过 1 秒的 SQL 会被记录
4
SET GLOBAL long_query_time = 1;
5
-- 记录没有使用索引的查询
6
SET GLOBAL log_queries_not_using_indexes = ON;
7

8
-- 查看配置
9
SHOW VARIABLES LIKE '%slow_query%';
10
SHOW VARIABLES LIKE 'long_query_time';

1
-- 深分页，性能差
2
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;
3

4
-- 游标分页，性能好
5
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;

1
// 接口
2
interface Subject {
3
    void request();
4
}
5

6
// 真实对象
7
class RealSubject implements Subject {
8
    @Override
9
    public void request() {
10
        System.out.println("RealSubject: Handling request.");
11
    }
12
}
13

14
// 代理对象
15
class ProxySubject implements Subject {
16
    private RealSubject realSubject;
17

18
    @Override
19
    public void request() {
20
        if (realSubject == null) {
21
            realSubject = new RealSubject(); // 延迟初始化
22
        }
23
        System.out.println("Proxy: Logging before delegating request.");
24
        realSubject.request();
25
        System.out.println("Proxy: Logging after delegating request.");
26
    }
27
}
28

29
// 客户端
30
public class Main {
31
    public static void main(String[] args) {
32
        Subject proxy = new ProxySubject();
33
        proxy.request();
34
    }
35
}

1
public void refresh() throws BeansException, IllegalStateException {
2
    prepareRefresh();                      // 准备刷新，记录启动时间，校验必需属性
3
    ConfigurableListableBeanFactory beanFactory = obtainFreshBeanFactory(); // 创建 BeanFactory
4
    prepareBeanFactory(beanFactory);       // 配置 BeanFactory，注册一些默认组件
5
    postProcessBeanFactory(beanFactory);   // 子类扩展点
6
    invokeBeanFactoryPostProcessors(beanFactory); // 执行 BeanFactoryPostProcessor
7
    registerBeanPostProcessors(beanFactory);      // 注册 BeanPostProcessor
8
    initMessageSource();                   // 初始化国际化组件
9
    initApplicationEventMulticaster();     // 初始化事件广播器
10
    onRefresh();                           // 子类扩展点，SpringBoot 在这里启动内嵌 Tomcat
11
    registerListeners();                   // 注册事件监听器
12
    finishBeanFactoryInitialization(beanFactory); // 实例化所有非懒加载的单例 Bean
13
    finishRefresh();                       // 发布 ContextRefreshedEvent
14
}