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

![](https://upload-images.jianshu.io/upload_images/24195226-47cfab1c1c5bd232.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

![](https://upload-images.jianshu.io/upload_images/24195226-2f65c04179ce5618.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

# 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’

– 优化方式

1.改用 in
SELECT * from users where b in (‘b’,‘bbb’)

2.UNION
– 对于or，我们可以这样优化我们的sql,虽然第二条没有走索引,但是第一条sql就走了索引啦
SELECT * from users where b = ‘b’
UNION
SELECT * from users where c = ‘c’

**LIKE**

– %关键字% 会让索引失效
SELECT * from users where a like ‘%abc%’

– 正例,"关键字%"是可以使用索引提高查询效率,类似前缀索引
SELECT * from users where a like ‘abc%’

**where中对字段进行运算或使用函数**

– 均会使索引失效
EXPLAIN SELECT * from users where YEAR(ctime) = ‘2020’;
EXPLAIN SELECT * from users where d+1=2;

**大于号与小于号**

– 在mysql中大于号小于号是个神奇的东西，使用它有时候会走索引有时候不走，据说是和结果的数量有关的，当数量较少（网上查到是有一个比例）时时使用索引的
– 建议能用BETWEEN就不要用><

– 可以自行按时间筛选出不同的数量测试
SELECT id from users where ctime>‘2020-03-30 19:45:30’

**使用复合索引但没有使用"引导列"**

– 可知表中有复合索引idx_abc(a,b,c),还有一个idx_b索引，我们先把idx_b删除
– 以下sql 没有用到"引导列"所以不会走idx_abc索引，"引导列"只指复合索引的第一个字段
EXPLAIN SELECT * from users where c=‘c’ and b=‘b’ ;

– 正例 只要出现a即可
EXPLAIN SELECT * from users where a=‘a’ and b=‘b’ ;
EXPLAIN SELECT * from users where a=‘a’ and c=‘c’ ;

## limit分页优化

我们日常做分页需求时，一般会用 limit 实现

– 常用做法
SELECT * from users LIMIT 1000000,10

当偏移量最大的时候，查询效率就会越低，因为Mysql并非是跳过偏移量直接去取后面的数据，而是先把偏移量+要取的条数，然后再把前面偏移量这一段的数据抛弃掉再返回的。

优化分页是需要跟业务结合，这里提供几种解决方案，没有最好只有最合适

**where加上时间筛选**

比如只获取最近一年的数据、只获取今年的数据 where createtime>'2020-01-01' 

**放弃选页，即只有上一页下一页**

1.  第一页直接查
2.  获得第一页max(id)，如123，一般是最后一条数据，
3.  然后查询带上索引，这样每次只要扫描10条数据 where id>123 limit 10

**限制页数**

如只允许获取前100页

# 索引优化

## 建立索引

mysql中索引一共分为主键索引、唯一索引、普通索引、全文索引。常用的都是前三种，第一种跟随主键，无需手动创建，而第四种全文索引用于全文搜索。只有InnoDB和 MyISAM存储引擎支持 FULLTEXT索引和仅适用于 CHAR， VARCHAR和 TEXT列，一般比较少用，因为像大文本的检索都会采用一些全文检索框架如elasticsearch，而不是在数据库里检索。

– 单列索引
CREATE INDEX index_name ON users (name);
– 多列索引

最后的话

CodeChina开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频】

无论是哪家公司，都很重视Spring框架技术，重视基础，所以千万别小看任何知识。面试是一个双向选择的过程，不要抱着畏惧的心态去面试，不利于自己的发挥。
同时看中的应该不止薪资，还要看你是不是真的喜欢这家公司，好了希望这篇文章对大家有帮助！
另外本人整理收藏了多家公司面试知识点整理 ，以及各种Java核心知识点免费分享给大家，
下方只是部分截图
厂Java面试题解析+核心总结学习笔记+最新讲解视频】](https://codechina.csdn.net/m0_60958482/java-p7)**

无论是哪家公司，都很重视Spring框架技术，重视基础，所以千万别小看任何知识。面试是一个双向选择的过程，不要抱着畏惧的心态去面试，不利于自己的发挥。
同时看中的应该不止薪资，还要看你是不是真的喜欢这家公司，好了希望这篇文章对大家有帮助！
另外本人整理收藏了多家公司面试知识点整理 ，以及各种Java核心知识点免费分享给大家，
下方只是部分截图