多列单个索引和联合索引的区别

利用spring boot 构建了200w的数据记录

package com.example.demo.init;

import com.example.demo.dao.StudentDao;
import com.example.demo.model.StudentEbo;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;

/**
 * @author duanxiaoqiu
 * @Date 2019-06-17 20:12
 **/
@Slf4j
@Order(value = 0)
@Component
public class initData implements CommandLineRunner {

    @Autowired
    private StudentDao studentDao;

    @Override
    public void run(String... args) throws Exception {
        log.info("=======init=======");
        long start = System.currentTimeMillis();
        List<StudentEbo> studentEbos = new ArrayList<>();
        int num = 0;
        for (int i = 0; i < 2000000; i++) {
            StudentEbo studentEbo = new StudentEbo();
            studentEbo.setName("xq" + i);
            studentEbo.setColor("color" + i);
            studentEbo.setSize("size" + i);
            studentEbos.add(studentEbo);
            num++;
            if (num == 10000) {
                log.info("=======");
                long time=System.currentTimeMillis();
                studentDao.insertByBatch(studentEbos);
                log.info("==time:{}===",System.currentTimeMillis()-time);
                studentEbos.clear();
                num = 0;
            }
        }
        if (studentEbos != null && studentEbos.size() > 0) {
            studentDao.insertByBatch(studentEbos);
        }
        log.info("======cost:{}======", System.currentTimeMillis() - start);
    }
}

 

select * from t_student WHERE size='size10001'

耗时：0.747s

1.新增索引

ALTER TABLE `t_student` ADD INDEX `index1` (`size`) USING BTREE ;

查询耗时：0.248s

 

多列索引和联合索引参考：

https://www.cnblogs.com/musings/p/10890563.html