【干货】关于SQL书写建议-&索引优化的总结，Java集合的区别总结

INSERT INTO users VALUES (‘3’, ‘wer’, ‘123’, ‘1’, ‘3’);
INSERT INTO users VALUES (‘4’, ‘左先生’, ‘123’, ‘1’, ‘4’);
INSERT INTO users VALUES (‘5’, ‘qwe1’, ‘wq12’, ‘2’, ‘5’);
INSERT INTO users VALUES (‘6’, ‘qwe’, ‘123’, ‘2’, null);
INSERT INTO users VALUES (‘7’, ‘1’, ‘1’, ‘1’, null);
INSERT INTO users VALUES (‘8’, ‘w’, ‘1’, ‘1’, null);
INSERT INTO users VALUES (‘9’, ‘aa’, ‘1’, null, null);

– 创建生成随机数据的存储过程

DROP PROCEDURE IF EXISTS create_test_data;

DELIMITER ;;

CREATE PROCEDURE create_test_data(n INT)
COMMENT ‘生成若干随机数据’
BEGIN
DECLARE i INT DEFAULT 1;

WHILE i < n DO
INSERT INTO test.users (a, b, c) VALUES
(
get_rand_str (10),
get_rand_str (10),
get_rand_str (10)
);
SET i = i + 1;
END WHILE;

END;;

– 返回随机字符串的函数

DROP FUNCTION IF EXISTS get_rand_str;

DELIMITER ;;
CREATE FUNCTION get_rand_str(n INT)
RETURNS varchar(100)
COMMENT ‘返回随机数’
BEGIN
DECLARE char_str varchar(100) DEFAULT ‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789’;
DECLARE return_str varchar(255) DEFAULT ‘’;
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str = concat(return_str, substring(char_str, FLOOR(1 + RAND()*62), 1));
SET i = i+1;
END WHILE;
RETURN return_str;
END;;

– 生成100w条随机数据
– 预计花费半小时或更久，其实也可以生成1w条。主要是数据多一点，更能反映出索引的重要性
call create_test_data(100*10000);

Explain

Explain是确定一个SQL是否走索引最简单的办法，我们用此方法可以对SQL进行调优，本文章只需关注以下项目，关于Exolain的具体说明可查阅具体说明

• type（  从最好到最差依次是 const  > eq_ref > ref > range > index > all ）
• const 表示通过索引一次就找到了，const用于比较primary key 或者 unique索引
• eq_ref 多表连接中使用primary key或者 unique key作为关联条件
• ref  非唯一性索引扫描，返回匹配某个单独值得所有行，本质上也是一种索引访问，它返回所有匹配某个单独值得行，然而它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
• range 只检索给定范围的行，一般就是where语句中出现了between,in等范围的查询。这种范围扫描索引扫描比全表扫描要好
• index  遍历全表，ALL区别为index类型只遍历索引树 ( select索引列或order by 主键 两种情况，但是where没用到索引 )
• all  遍历全表以找到匹配的行
• 一般保证查询至少达到range级别，最好能达到ref。
• key 本次查询最终用到哪个索引
• key_len 索引使用的前缀长度或整个长度
• row 扫描过的记录行数

– 测试一下,其中b字段有索引，c字段没有索引

SELECT * from users where b=‘随便啦，测试而已’; – 花费0.001s

SELECT * from users where c=‘随便啦，测试而已’; – 花费0.306s

SQL优化建议

少用select *

老生常谈，大家都懂。

合理使用limit 1

如果知道查询结果只有一条或者只要一条记录，建议用limit 1，当然，如果已存在唯一索引就没必要用。

合理使用join

Inner join 内连接，在两张表进行连接查询时，只保留两张表中完全匹配的结果集

left join 在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录

right join 在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录

都满足SQL需求的前提下，推荐优先使用Inner join（内连接），如果要使用left join，左边表数据结果尽量小，如果有条件的尽量放到左边处理。

批量插入数据

数量不大的情况下，一条一条插入问题不大。如果数据量两，使用批量拆入语句效率更高

for(){

INSERT INTOtest.users(a,b,c) VALUES (‘hLQK51GcL6’,‘1DXIzvIS3t’,‘4LsQGKva6U’)

}

更优：

INSERT INTO test.users (a, b, c)
VALUES
– 此处可自行拼接语句，如使用mybatis等
(
‘hLQK51GcL6’,
‘1DXIzvIS3t’,
‘4LsQGKva6U’
),
(
‘hLQK51GcL6’,
‘1DXIzvIS3t’,
‘4LsQGKva6U’
)

尽量用union all替换 union

如果使用union，不管检索结果有没有重复，都会尝试进行合并，然后在输出最终结果前进行排序。如果已知检索结果没有重复记录，使用union all 代替union，这样会提高效率。

– 执行时间0.06s
SELECT
*
FROM
users
LIMIT 0,
10000
UNION ALL
SELECT
*
FROM
users
LIMIT 10000,
20000

– 执行时间0.2s
SELECT
*
FROM
users
LIMIT 0,
10000
UNION
SELECT
*
FROM
users
LIMIT 10000,
20000

会使索引失效的几种情况

• where条件中没有匹配字段类型
• where中使用NOT、!=、IN (“IN” Mysql5.6及以上支持索引)
• where中使用OR连接没有索引的字段
• where中使用in (mysql5.6及以上支持索引)
• like ‘%关键字%’
• where中对字段进行运算或使用函数
• 使用复合索引但没有使用"引导列"

我们知道测试表中b字段是有索引，c没有索引，接下来逐一测试一下

where条件中没有匹配字段类型

– b是字符串类型,where且写了整数，虽然可以正常执行sql，但是不会走索引
EXPLAIN SELECT * from users where b=1;

** NOT、!=**

– 均会使索引失效
EXPLAIN SELECT * from users where b not in(‘a’);
EXPLAIN SELECT * from users where b is not null;
EXPLAIN SELECT * from users where b !=‘a’

OR

– 用or连接没有索引的字段这种情况，假设它走了b的索引，但是走到c查询条件时，它还得全表扫描
– 也就是需要三步过程：全表扫描+索引扫描+合并。所以OR会导致索引失效
– 注意，测试表中c是没索引的，如果c也有索引，用or其实是OK的
EXPLAIN SELECT * from users where b=‘a’ or c=‘a’

小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数初中级Java工程师，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年最新Java开发全套学习资料》送给大家，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频

如果你觉得这些内容对你有帮助，可以添加下面V无偿领取！（备注Java）

最后

整理的这些资料希望对Java开发的朋友们有所参考以及少走弯路，本文的重点是你有没有收获与成长，其余的都不重要，希望读者们能谨记这一点。

其实面试这一块早在第一个说的25大面试专题就全都有的。以上提及的这些全部的面试+学习的各种笔记资料，我这差不多来回搞了三个多月，收集整理真的很不容易，其中还有很多自己的一些知识总结。正是因为很麻烦，所以对以上这些学习复习资料感兴趣，

有没有收获与成长，其余的都不重要，希望读者们能谨记这一点。

[外链图片转存中…(img-eVIbr0oA-1711092303035)]

[外链图片转存中…(img-IoOCexfM-1711092303036)]

其实面试这一块早在第一个说的25大面试专题就全都有的。以上提及的这些全部的面试+学习的各种笔记资料，我这差不多来回搞了三个多月，收集整理真的很不容易，其中还有很多自己的一些知识总结。正是因为很麻烦，所以对以上这些学习复习资料感兴趣，

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录