SQL 高阶 （sql优化）

批量插入：

sql 批量插入：

INSERT INTO users(name, age) VALUES ('Tom', 20), ('Jerry', 22), ('Bob', 19); --  该方式 适合 500条以内。

多线程插入批量插入，能够大幅提升插入速度。
经过测试：系统性能，8核16G 插入10w条数据。

性能测试	50个线程	100个线程
一次插入100条	32402ms	27261ms
一次插入500条	32712ms	27916ms
一次插入1000条	44174ms	30129ms

总结：批量插入，多线程具有优势（根据cpu和磁盘读写能力 ），数据量控制在100-500 之间

     //插入核心代码如下
    @GetMapping(value = "add")
    public void add(int thread,int insert,int batch) {
        if(batch>0)COUNT = batch;
        executor = Executors.newFixedThreadPool(thread);
        finish = 0;
        startTime = System.currentTimeMillis();
        int mj = insert/COUNT;
        for (int i = 0; i < mj; i++) {
            insert(COUNT);
        }
        if(insert%COUNT>0){
            insert(insert%COUNT);
        }
    }
    public void insert(int count) {
        executor.submit(() -> {
            List<Object[]> users = new ArrayList<>();
            Random random = new Random();
            for (int i = 0; i < count; i++) {
                Object[] a = new Object[5];
                a[name] = getMin(random);
                a[age] = i % 10 + 20;
                a[phone] = 13000000000L + (random.nextLong() % 100000000L);
                a[profession] = professions[random.nextInt(professions.length - 1)];
                a[mark] = marks[random.nextInt(marks.length - 1)];
                users.add(a);
            }
            int[] ints = jdbcTemplate.batchUpdate("insert into users (name,age,phone,profession,mark) values(?,?,?,?,?)", users);
            long temp = System.currentTimeMillis()-startTime;
            System.out.println("线程"+(finish++)+"完成时间："+temp);
        });
    }

load 插入

几万几百万：
下面的方式 是通过文件加载进入数据库。
文件放到 D:/users.sql 中。liunx 就输入 liunx 文件目录。 你的sql 在哪里执行，文件就在那个服务器上。
格式如下：

1,Tom,20   -- 数据的顺序要和的表 属性顺序一致
2,Jerry,22
3,Bob,19

load data local infile 'D:/users.sql' into table user_test fields terminated by ',' lines terminated by '\n';
-- fields terminated by ',' 表示 属性和属性之间用 ‘,’ 分割。
-- lines terminated by '\n' 表示 数据行用 ‘换行’ 分割。

innDB 在存储数据时 ，是分区域分页分行存储的。为啥查询速度快，数据会更具id的大小进行顺序存储，当数据插入数据的id是uuid时，就会导致乱序插入，频发触发存储地址的整理，导致插入效率变低。

主键设计原则

1、满足业务需求的情况下，尽量降低主键的长度，可以节省磁盘读写速度。
2、插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键，。
3、尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
4、业务操作时，避免对主键的修改。

order by 优化：

--例子一
create index idx_age_phone on users(age,phone); --创建索引
-- 查询的数据要和索引一致，否则无效 order by 的参数属性也有和创建的顺序一致，否则只能生效第一个
EXPLAIN select id,phone,age from users order by age,phone
 
--例子二 ASC和DESC 排序也要一致。（mysql8.0以前，不支持ASC 和DESC ）
create index idx_age_phone on users(age ASC,phone DESC)
EXPLAIN select id,age,phone from users ORDER BY age asc,phone desc

group by 优化

-- 创建索引，联合与否，看你需求
create index idx_profession_age on users(profession,age)
-- 注意，索引的顺序和你的用法要一直，否则无效
EXPLAIN select profession,age,count(*) num from users group by profession,age;

limit 优化（分页优化）

select * from users limit 1000000,10; -- 150w条数据 耗时 11.271s
-- 用主键id去分页，然后再通过id去查询对应的数据。 这里in不支持。
select u.* from users u, (select id from users limit 1100000,10) d where u.id = d.id; --50w条数据 耗时3.694

count() 优化。

select count(*) from users --目前innoDb 没有特殊的手段，如需优化，可以通过缓存或者自己计数的方式。

update 优化：

create index idx_name on users(name) --创建索引
begin;
update users set mark='xxx' where name ='张三'  --因为有索引，所以本次修改为行锁，否则就表锁
commit;