MYSQL索引设计原则 - 《MYSQL》

索引设计案例实战：陌生人社交APP的MYSQL索引设计实战
模板方法
工厂模式
策略模式
观察者模式：

针对SQL语句里的where条件、order by条件以及group by条件去设计索引。最好让各个where、order by和group by和group

后面跟的字段都是联合索引的最左侧开始的部分字段，这样他们都能用上索引。
例如，快递订单索引设计，会根据APP短搜索比较频繁的条件来设计索引：
（1）根据运单号搜索创建运单号索引
（2）根据收件人手机号搜索，多个订单按照时间进行排序创建收件人手机号+时间联合索引
（3）根据服务站ID+快递公司+状态进行搜索，多个订单按照时间进行排序创建服务站ID+快递公司+时间联合索引
不要在枚举值字段建立索引，因为没法进行快速的二分查找，还不如全表扫描，建立索引尽量使用那些基数比较大的字段，就是值比较多的字段

尽量对那些字段类型比较小的列来设计索引，比如tinyint之类，占用空间小，搜索性能更好

如果需要对比较长的字符串值设计索引，可以针对前20个字符建立索引，只是这种只适用于where条件查      询，不适用于order by和group by和group

索引设计不宜过多，会影响增删改数据性能，建议两三个联合索引就够了
不要在索引列上进行函数运算
主键建议自增，不要用UUID，因为UUID会导致聚簇索引频繁页分裂。
如果不能where和group by都用上索引，那么优先保证where用上索引，先使用where根据索引筛选出一部分数据，然后再进行排序
把经常做范围查询的字段放到联合索引最后一个，因为一旦某个字段做范围查询用到了索引，那么接下来的字段都不能使用到索引。

重要原则：尽量利用一两个复杂的多字段联合索引，抗下80%以上的查询，然后用一两个辅助索引，抗下剩余的20%的非典型查询，保证99%以上的查询都能充分利用到索引，保证查询速度和性能。

索引设计案例实战：陌生人社交APP的MYSQL索引设计实战

搜索20-35岁的用户，按照评分进行排序：
select * from user_info where age between 20 and 25 order by score;
假设创建了一个联合索引（age, score），那筛选是可以用上索引来筛选，但是排序无法利用索引
那这时候是针对where去设计索引，还是针对order by设计索引？
一般这时候都是让where条件去使用索引来快速筛选出一部分指定的数据，然后进行排序，然后针对排序的数据拿出来一页数据
因为基于索引筛选往往可以筛选出想要的数据，如果数据量不太大的话，后面做排序分页的成本也不会太高。

那么需要把哪些字段包含到索引里面去？在联合索引里，字段的顺序要如何排列？
首先在联合索引里包含省份、城市、性别三个字段。
因为这个搜索条件是搜索频率最高的。
可以把基数较低但是查询频繁的省份、城市、性别三个字段放到联合索引最左侧去。(province, city ,sex)
如果需要增加年龄范围查询，可以将年龄字段放到联合索引最后(province, city ,sex, age)
如果不指定性别，而指定年龄进行查询，如select from user_info where province = xxx and city = xxx and age between xx and xx;
此时age没法用到索引，因为city和age之间隔了一个sex。此时可以加上sex字段，值设置为sex in (‘male’, ‘female’)
SQL语句：
select from user_info where province = xxx and city = xxx and sex in (‘male’, ‘female’) and age between xx and xx;
此时保证所有字段都能用上索引
如果需要加上其他字段，则在age之前加即可，为什么非要让age在最后，因为age是范围查询，一旦SQL里面范围查询用到了索引，那么后面的条件都不能用到索引
如果需要带上一个字段，是否在7天内登录过，lastest_login_time。那么SQL语句里面可能会这么写lastest_login_time > xxx
如果lastest_login_time也加入联合索引，那么只有age范围查询能用上索引，lastest_login_time是用不了索引的，因为范围查询的时候只有第一个范围查询是可以用到索引，第一个范围查询后的范围查询是用不上索引的。
解决办法：可以在设计表的时候，设计一个字段：does_login_in_latest_7_days，int类型，表示这个人是否在最近7天登录过APP，然后将这个字段加入联合索引age前面即可。

模板方法

优点：

定义算法骨架，保证核心流程不被篡改，将一些步骤的具体实现交给子类；
提取公共代码，便于维护、扩展
行为由父类控制，具体由子类实现

缺点：

每一个不同的实现都需要一个子类来实现，导致类的个数增加，使得系统更加庞大。

类结构图：

抽象父类Game：

public abstract class Game {
    public abstract void initialize();
    public abstract void startPlay();
    public abstract void endPlay();
    //模板
    public final void play() {
        //初始化游戏
        initialize();
        //开始游戏
        startPlay();
        //结束游戏
        endPlay();
    }
}

子类Cricket：

public class Cricket extends Game {
    @Override
    public void initialize() {
        System.out.println("Cricket 游戏初始化");
    }
    @Override
    public void startPlay() {
        System.out.println("Cricket 游戏开始");
    }
    @Override
    public void endPlay() {
        System.out.println("Cricket 游戏结束");
    }
}

子类FootBall：

public class FootBall extends Game {
    @Override
    public void initialize() {
        System.out.println("FootBall 游戏初始化");
    }
    @Override
    public void startPlay() {
        System.out.println("FootBall 游戏开始");
    }
    @Override
    public void endPlay() {
        System.out.println("FootBall 游戏结束");
    }
}

调用：

public class TemplatePatternDemo {
    public static void main(String[] args) {
        Game cricket = new Cricket();
        cricket.play();
        Game footBall = new FootBall();
        footBall.play();
    }
}

输出：

Cricket 游戏初始化
Cricket 游戏开始
Cricket 游戏结束
FootBall 游戏初始化
FootBall 游戏开始
FootBall 游戏结束

工厂模式

解决对象创建问题。
优点： 1、一个调用者想创建一个对象，只要知道其名称就可以了。 2、扩展性高，如果想增加一个产品，只要扩展一个工厂类就可以。 3、屏蔽产品的具体实现，调用者只关心产品的接口。
缺点：每次增加一个产品时，都需要增加一个具体类和对象实现工厂，使得系统中类的个数成倍增加，在一定程度上增加了系统的复杂度，同时也增加了系统具体类的依赖。这并不是什么好事。

类图：

接口Shape：

public interface Shape {
    void draw();
}

实现类Circle：

public class Circle implements Shape {
    @Override
    public void draw() {
        System.out.println("Circle draw");
    }
}

实现类Rectangle：

public class Rectangle implements Shape {
    @Override
    public void draw() {
        System.out.println("Rectangle draw");
    }
}

实现类Square：

public class Square implements Shape {
    @Override
    public void draw() {
        System.out.println("Square draw");
    }
}

生产Bean对象的工厂ShapeFactory：

public class ShapeFactory {
    //使用getShape方法，根据类型获取对应的对象
    public Shape getShape(String shapeType) {
        if(shapeType == null){
            return null;
        }
        if(shapeType.equalsIgnoreCase("CIRCLE")){
            return new Circle();
        } else if(shapeType.equalsIgnoreCase("RECTANGLE")){
            return new Rectangle();
        } else if(shapeType.equalsIgnoreCase("SQUARE")){
            return new Square();
        }
        return null;
    }
}

demo测试：

public class FactoryPatternDemo {
    public static void main(String[] args) {
        ShapeFactory shapeFactory = new ShapeFactory();
        Shape circle = shapeFactory.getShape("CIRCLE");
        circle.draw();
        Shape rectangle = shapeFactory.getShape("RECTANGLE");
        rectangle.draw();
        Shape square = shapeFactory.getShape("SQUARE");
        square.draw();
    }
}

输出：

Circle draw
Rectangle draw
Square draw

策略模式

优点： 1、算法可以自由切换。 2、避免使用多重条件判断。 3、扩展性良好。
缺点： 1、策略类会增多。 2、所有策略类都需要对外暴露。

类结构图：

Strategy策略接口：

public interface Strategy {
    public int doOperation(int num1, int num2);
}

求和策略OperationAdd：

public class OperationAdd implements Strategy {
    @Override
    public int doOperation(int num1, int num2) {
        return num1 + num2;
    }
}

求差策略：

public class OperationSubstract implements Strategy {
    @Override
    public int doOperation(int num1, int num2) {
        return num1 - num2;
    }
}

求乘积策略：

public class OperationMultiply implements Strategy {
    @Override
    public int doOperation(int num1, int num2) {
        return num1 * num2;
    }
}

使用 Context 来查看当它改变策略 Strategy 时的行为变化：

public class Context {
    private Strategy strategy;
    public Context(Strategy strategy) {
        this.strategy = strategy;
    }
    public int executeStrategy(int num1, int num2) {
        return strategy.doOperation(num1, num2);
    }
}

demo测试：

public class StrategyPatternDemo {
    public static void main(String[] args) {
        Context context = new Context(new OperationAdd());
        System.out.println(context.executeStrategy(5, 10));
        Context sustractContext = new Context(new OperationSubstract());
        System.out.println(sustractContext.executeStrategy(15, 10));
        Context multiplyContext = new Context(new OperationMultiply());
        System.out.println(multiplyContext.executeStrategy(5, 10));
    }
}

输出：

15
5
50

观察者模式：

解决问题：一个对象状态改变时，通知其他对象，考虑易用和低耦合，保证高度的协作。

优点： 1、观察者和被观察者是抽象耦合的。 2、建立一套触发机制。
缺点： 1、如果一个被观察者对象有很多的直接和间接的观察者的话，将所有的观察者都通知到会花费很多时间。 2、如果在观察者和观察目标之间有循环依赖的话，观察目标会触发它们之间进行循环调用，可能导致系统崩溃。 3、观察者模式没有相应的机制让观察者知道所观察的目标对象是怎么发生变化的，而仅仅只是知道观察目标发生了变化

类图结构：

Subject：

public class Subject {
    //观察者列表
    private List<Observer> observers = new ArrayList<>();
    //状态
    private int state;
    public int getState() {
        return state;
    }
    public void setState(int state) {
        this.state = state;
        //状态更新时，通知所有的观察者
        notifyAllObservers();
    }
    /**
     * 增加观察者
     * @param observer
     */
    public void attach(Observer observer) {
        observers.add(observer);
    }
    /**
     * 通知所有的观察者
     */
    private void notifyAllObservers() {
        for (Observer observer : observers) {
            observer.update();
        }
    }
}

观察者接口Observer：

public abstract class Observer {
    protected Subject subject;
    public abstract void update();
}

观察者1 BinaryObserver：

public class BinaryObserver extends Observer {
    public BinaryObserver(Subject subject) {
        this.subject = subject;
        this.subject.attach(this);
    }
    @Override
    public void update() {
        System.out.println("Binary String:"+ Integer.toBinaryString(subject.getState()));
    }
}

观察者2 HexaObserver：

public class HexaObserver extends Observer {
    public HexaObserver(Subject subject) {
        this.subject = subject;
        this.subject.attach(this);
    }
    @Override
    public void update() {
        System.out.println( "Hex String: "
                + Integer.toHexString( subject.getState() ) );
    }
}

观察者3 OctalObserver：

public class OctalObserver extends Observer {
    public OctalObserver(Subject subject) {
        this.subject = subject;
        this.subject.attach(this);
    }
    @Override
    public void update() {
        System.out.println( "Octal String: "
                + Integer.toOctalString( subject.getState() ) );
    }
}

调用demo：

public class ObserverPatternDemo {
    public static void main(String[] args) {
        Subject subject = new Subject();
        new HexaObserver(subject);
        new OctalObserver(subject);
        new BinaryObserver(subject);
        System.out.println("First state change: 15");
        subject.setState(15);
        System.out.println("Second state change: 10");
        subject.setState(10);
    }
}