如何进行sql优化？_意昂体育家装全屋定制集团

您当前所在位置：主页 > 新闻中心 > 公司新闻

如何进行sql优化？

发布日期：2024-05-06 05:13　　浏览次数：次

4个第一性原理

1. 没有银弹.

2. 复杂计算无法约简: 复杂计算要么基于hash, 要么基于sort; 现场计算发生了约简，或意味预计算，ingestion时做了hash分组或者sort，或者ingestion前数据已经分好组或者有序.

3. 等效别名原理: 如果有一个系统提出了某种新机制，使计算加速，那么其中之一的情形是，它采用了与hash分组或sort等效的方法，所谓的新机制只是老机制的别命.

4.优化即抽象泄漏: 复杂计算可约简的前提是，计算本身属于特殊情形，可以特殊问题特殊处理，然而特殊处理无法泛化到一般情形，并且需要优化器作出路径选择或者需要动态自适应执行作出路径选择. 这是一个系统提出新机制的第二种方法.

把任何一个目标数据库的下面5个问题，研究清楚，便知道做SQL优化了.

SQL优化不知道怎么入手, 可以尝试从下面5个方面思考;
总结和学习SQL优化技巧，可以尝试归纳于5个方面之一.
简言之：

读得少: 活儿少
计划好: 干得巧
算得快: 手速快
并行: 拼人数
预计算: 提前干

sql优化是一个大家都比较关注的热门话题，无论你在面试，还是工作中，都很有可能会遇到。

如果某天你负责的某个线上接口，出现了性能问题，需要做优化。那么你首先想到的很有可能是优化sql语句，因为它的改造成本相对于代码来说也要小得多。

最近无意间获得一份BAT大厂大佬写的刷题笔记，一下子打通了我的任督二脉，越来越觉得算法没有想象中那么难了。

[BAT大佬写的刷题笔记，让我offer拿到手软](这位BAT大佬写的Leetcode刷题笔记，让我offer拿到手软)

那么，如何优化sql语句呢？

这篇文章从15个方面，分享了sql优化的一些小技巧，希望对你有所帮助。

很多时候，我们写sql语句时，为了方便，喜欢直接使用select *，一次性查出表中所有列的数据。

反例：

select * from user where id=1;

在实际业务场景中，可能我们真正需要使用的只有其中一两列。查了很多数据，但是不用，白白浪费了数据库资源，比如：内存或者cpu。

此外，多查出来的数据，通过网络IO传输的过程中，也会增加数据传输的时间。

还有一个最重要的问题是：select *不会走覆盖索引，会出现大量的回表操作，而从导致查询sql的性能很低。

那么，如何优化呢？

正例：

select name,age from user where id=1;

sql语句查询时，只查需要用到的列，多余的列根本无需查出来。

我们都知道sql语句使用union关键字后，可以获取排重后的数据。

而如果使用union all关键字，可以获取所有数据，包含重复的数据。

反例：

(select * from user where id=1) 
union 
(select * from user where id=2);

排重的过程需要遍历、排序和比较，它更耗时，更消耗cpu资源。

所以如果能用union all的时候，尽量不用union。

正例：

(select * from user where id=1) 
union all
(select * from user where id=2);

除非是有些特殊的场景，比如union all之后，结果集中出现了重复数据，而业务场景中是不允许产生重复数据的，这时可以使用union。

小表驱动大表，也就是说用小表的数据集驱动大表的数据集。

假如有order和user两张表，其中order表有10000条数据，而user表有100条数据。

这时如果想查一下，所有有效的用户下过的订单列表。

可以使用in关键字实现：

select * from order
where user_id in (select id from user where status=1)

也可以使用exists关键字实现：

select * from order
where exists (select 1 from user where order.user_id=user.id and status=1)

前面提到的这种业务场景，使用in关键字去实现业务需求，更加合适。

为什么呢？

因为如果sql语句中包含了in关键字，则它会优先执行in里面的子查询语句，然后再执行in外面的语句。如果in里面的数据量很少，作为条件查询速度更快。

而如果sql语句中包含了exists关键字，它优先执行exists左边的语句（即主查询语句）。然后把它作为条件，去跟右边的语句匹配。如果匹配上，则可以查询出数据。如果匹配不上，数据就被过滤掉了。

这个需求中，order表有10000条数据，而user表有100条数据。order表是大表，user表是小表。如果order表在左边，则用in关键字性能更好。

总结一下：

in 适用于左边大表，右边小表。
exists 适用于左边小表，右边大表。

不管是用in，还是exists关键字，其核心思想都是用小表驱动大表。

如果你有一批数据经过业务处理之后，需要插入数据，该怎么办？

反例：

for(Order order: list){
   orderMapper.insert(order):
}

在循环中逐条插入数据。

insert into order(id,code,user_id) 
values(123,'001',100);

该操作需要多次请求数据库，才能完成这批数据的插入。

但众所周知，我们在代码中，每次远程请求数据库，是会消耗一定性能的。而如果我们的代码需要请求多次数据库，才能完成本次业务功能，势必会消耗更多的性能。

那么如何优化呢？

正例：

orderMapper.insertBatch(list):

提供一个批量插入数据的方法。

insert into order(id,code,user_id) 
values(123,'001',100),(124,'002',100),(125,'003',101);

这样只需要远程请求一次数据库，sql性能会得到提升，数据量越多，提升越大。

但需要注意的是，不建议一次批量操作太多的数据，如果数据太多数据库响应也会很慢。批量操作需要把握一个度，建议每批数据尽量控制在500以内。如果数据多于500，则分多批次处理。

俗话说：在家靠父母、出门靠朋友，为了感谢各位粉丝朋友对，苏三的关注和支持，我特地为粉丝提供了一些工作的内推群的机会，这些公司分布在北京、上海、广州、深圳、杭州、重庆、武汉、南京、郑州、西安、苏州、东莞等城市，内推人是我本人或者工作中/生活中/网络中认识的朋友们，来自各个大厂，非常的靠谱，直接真内推渠道能帮您增加面试的机会和通过的机率。

坑不多，没有需求的，就不要进了。

进群方式，加微信：su_san_java，备注：内推

有时候，我们需要查询某些数据中的第一条，比如：查询某个用户下的第一个订单，想看看他第一次的首单时间。

反例：

select id, create_date 
 from order 
where user_id=123 
order by create_date asc;

根据用户id查询订单，按下单时间排序，先查出该用户所有的订单数据，得到一个订单集合。然后在代码中，获取第一个元素的数据，即首单的数据，就能获取首单时间。

List<Order> list=orderMapper.getOrderList();
Order order=list.get(0);

虽说这种做法在功能上没有问题，但它的效率非常不高，需要先查询出所有的数据，有点浪费资源。

那么，如何优化呢？

正例：

select id, create_date 
 from order 
where user_id=123 
order by create_date asc 
limit 1;

使用limit 1，只返回该用户下单时间最小的那一条数据即可。

此外，在删除或者修改数据时，为了防止误操作，导致删除或修改了不相干的数据，也可以在sql语句最后加上limit。

例如：

update order set status=0,edit_time=now(3) 
where id>=100 and id<200 limit 100;

这样即使误操作，比如把id搞错了，也不会对太多的数据造成影响。

对于批量查询接口，我们通常会使用in关键字过滤出数据。比如：想通过指定的一些id，批量查询出用户信息。

sql语句如下：

select id,name from category
where id in (1,2,3...100000000);

如果我们不做任何限制，该查询语句一次性可能会查询出非常多的数据，很容易导致接口超时。

这时该怎么办呢？

select id,name from category
where id in (1,2,3...100)
limit 500;

可以在sql中对数据用limit做限制。

不过我们更多的是要在业务代码中加限制，伪代码如下：

public List<Category> getCategory(List<Long> ids){
   if(CollectionUtils.isEmpty(ids)){
      return null;
   }
   if(ids.size() > 500){
      throw new BusinessException("一次最多允许查询500条记录")
   }
   return mapper.getCategoryList(ids);
}

还有一个方案就是：如果ids超过500条记录，可以分批用多线程去查询数据。每批只查500条记录，最后把查询到的数据汇总到一起返回。

不过这只是一个临时方案，不适合于ids实在太多的场景。因为ids太多，即使能快速查出数据，但如果返回的数据量太大了，网络传输也是非常消耗性能的，接口性能始终好不到哪里去。

有时候，我们需要通过远程接口查询数据，然后同步到另外一个数据库。

反例：

select * from user;

如果直接获取所有的数据，然后同步过去。这样虽说非常方便，但是带来了一个非常大的问题，就是如果数据很多的话，查询性能会非常差。

这时该怎么办呢？

正例：

select * from user 
where id>#{lastId}and create_time >=#{lastCreateTime}
limit 100;

按id和时间升序，每次只同步一批数据，这一批数据只有100条记录。每次同步完成之后，保存这100条数据中最大的id和时间，给同步下一批数据的时候用。

通过这种增量查询的方式，能够提升单次查询的效率。

有时候，列表页在查询数据时，为了避免一次性返回过多的数据影响接口性能，我们一般会对查询接口做分页处理。

在mysql中分页一般用的limit关键字：

select id,name,age 
from user limit 10,20;

如果表中数据量少，用limit关键字做分页，没啥问题。但如果表中数据量很多，用它就会出现性能问题。

比如现在分页参数变成了：

select id,name,age 
from user limit 1000000,20;

mysql会查到1000020条数据，然后丢弃前面的1000000条，只查后面的20条数据，这个是非常浪费资源的。

那么，这种海量数据该怎么分页呢？

优化sql：

select id,name,age 
from user where id > 1000000 limit 20;

先找到上次分页最大的id，然后利用id上的索引查询。不过该方案，要求id是连续的，并且有序的。

还能使用between优化分页。

select id,name,age 
from user where id between 1000000 and 1000020;

需要注意的是between要在唯一索引上分页，不然会出现每页大小不一致的问题。

坑不多，没有需求的，就不要进了。

进群方式，加微信：su_san_java，备注：内推

mysql中如果需要从两张以上的表中查询出数据的话，一般有两种实现方式：子查询和连接查询。

子查询的例子如下：

select * from order
where user_id in (select id from user where status=1)

子查询语句可以通过in关键字实现，一个查询语句的条件落在另一个select语句的查询结果中。程序先运行在嵌套在最内层的语句，再运行外层的语句。

子查询语句的优点是简单，结构化，如果涉及的表数量不多的话。

但缺点是mysql执行子查询时，需要创建临时表，查询完毕后，需要再删除这些临时表，有一些额外的性能消耗。

这时可以改成连接查询。具体例子如下：

select o.* from order o
inner join user u on o.user_id=u.id
where u.status=1

根据阿里巴巴开发者手册的规定，join表的数量不应该超过3个。

反例：

select a.name,b.name.c.name,d.name
from a 
inner join b on a.id=b.a_id
inner join c on c.b_id=b.id
inner join d on d.c_id=c.id
inner join e on e.d_id=d.id
inner join f on f.e_id=e.id
inner join g on g.f_id=f.id

如果join太多，mysql在选择索引的时候会非常复杂，很容易选错索引。

并且如果没有命中中，nested loop join 就是分别从两个表读一行数据进行两两对比，复杂度是 n^2。

所以我们应该尽量控制join表的数量。

正例：

select a.name,b.name.c.name,a.d_name 
from a 
inner join b on a.id=b.a_id
inner join c on c.b_id=b.id

如果实现业务场景中需要查询出另外几张表中的数据，可以在a、b、c表中冗余专门的字段，比如：在表a中冗余d_name字段，保存需要查询出的数据。

不过我之前也见过有些ERP系统，并发量不大，但业务比较复杂，需要join十几张表才能查询出数据。

所以join表的数量要根据系统的实际情况决定，不能一概而论，尽量越少越好。

我们在涉及到多张表联合查询的时候，一般会使用join关键字。

而join使用最多的是left join和inner join。

left join：求两个表的交集外加左表剩下的数据。
inner join：求两个表交集的数据。

使用inner join的示例如下：

select o.id,o.code,u.name 
from order o 
inner join user u on o.user_id=u.id
where u.status=1;

如果两张表使用inner join关联，mysql会自动选择两张表中的小表，去驱动大表，所以性能上不会有太大的问题。

使用left join的示例如下：

select o.id,o.code,u.name 
from order o 
left join user u on o.user_id=u.id
where u.status=1;

如果两张表使用left join关联，mysql会默认用left join关键字左边的表，去驱动它右边的表。如果左边的表数据很多时，就会出现性能问题。

要特别注意的是在用left join关联查询时，左边要用小表，右边可以用大表。如果能用inner join的地方，尽量少用left join。

众所周知，索引能够显著的提升查询sql的性能，但索引数量并非越多越好。

因为表中新增数据时，需要同时为它创建索引，而索引是需要额外的存储空间的，而且还会有一定的性能消耗。

阿里巴巴的开发者手册中规定，单表的索引数量应该尽量控制在5个以内，并且单个索引中的字段数不超过5个。

mysql使用的B+树的结构来保存索引的，在insert、update和delete操作时，需要更新B+树索引。如果索引过多，会消耗很多额外的性能。

那么，问题来了，如果表中的索引太多，超过了5个该怎么办？

这个问题要辩证的看，如果你的系统并发量不高，表中的数据量也不多，其实超过5个也可以，只要不要超过太多就行。

但对于一些高并发的系统，请务必遵守单表索引数量不要超过5的限制。

那么，高并发系统如何优化索引数量？

能够建联合索引，就别建单个索引，可以删除无用的单个索引。

将部分查询功能迁移到其他类型的数据库中，比如：Elastic Seach、HBase等，在业务表中只需要建几个关键索引即可。

char表示固定字符串类型，该类型的字段存储空间的固定的，会浪费存储空间。

alter table order 
add column code char(20) NOT NULL;

varchar表示变长字符串类型，该类型的字段存储空间会根据实际数据的长度调整，不会浪费存储空间。

alter table order 
add column code varchar(20) NOT NULL;

如果是长度固定的字段，比如用户手机号，一般都是11位的，可以定义成char类型，长度是11字节。

但如果是企业名称字段，假如定义成char类型，就有问题了。

如果长度定义得太长，比如定义成了200字节，而实际企业长度只有50字节，则会浪费150字节的存储空间。

如果长度定义得太短，比如定义成了50字节，但实际企业名称有100字节，就会存储不下，而抛出异常。

所以建议将企业名称改成varchar类型，变长字段存储空间小，可以节省存储空间，而且对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

我们在选择字段类型时，应该遵循这样的原则：

能用数字类型，就不用字符串，因为字符的处理往往比数字要慢。
尽可能使用小的类型，比如：用bit存布尔值，用tinyint存枚举值等。
长度固定的字符串字段，用char类型。
长度可变的字符串字段，用varchar类型。
金额字段用decimal，避免精度丢失问题。

还有很多原则，这里就不一一列举了。

我们有很多业务场景需要使用group by关键字，它主要的功能是去重和分组。

通常它会跟having一起配合使用，表示分组后再根据一定的条件过滤数据。

反例：

select user_id,user_name from order
group by user_id
having user_id <=200;

这种写法性能不好，它先把所有的订单根据用户id分组之后，再去过滤用户id大于等于200的用户。

分组是一个相对耗时的操作，为什么我们不先缩小数据的范围之后，再分组呢？

正例：

select user_id,user_name from order
where user_id <=200
group by user_id

使用where条件在分组前，就把多余的数据过滤掉了，这样分组时效率就会更高一些。

其实这是一种思路，不仅限于group by的优化。我们的sql语句在做一些耗时的操作之前，应尽可能缩小数据范围，这样能提升sql整体的性能。

sql优化当中，有一个非常重要的内容就是：索引优化。

很多时候sql语句，走了索引，和没有走索引，执行效率差别很大。所以索引优化被作为sql优化的首选。

索引优化的第一步是：检查sql语句有没有走索引。

那么，如何查看sql走了索引没？

可以使用explain命令，查看mysql的执行计划。

例如：

explain select * from `order` where code='002';

结果：

通过这几列可以判断索引使用情况，执行计划包含列的含义如下图所示：

如果你想进一步了解explain的详细用法，可以看看我的另一篇文章《explain | 索引优化的这把绝世好剑，你真的会用吗？》

说实话，sql语句没有走索引，排除没有建索引之外，最大的可能性是索引失效了。

下面说说索引失效的常见原因：

如果不是上面的这些原因，则需要再进一步排查一下其他原因。

此外，你有没有遇到过这样一种情况：明明是同一条sql，只有入参不同而已。有的时候走的索引a，有的时候却走的索引b？

没错，有时候mysql会选错索引。

必要时可以使用force index来强制查询sql走某个索引。

至于为什么mysql会选错索引，后面有专门的文章介绍的，这里先留点悬念。

最近我建了新的技术交流群，打算将它打造成高质量的活跃群，欢迎小伙伴们加入。

我以往的技术群里技术氛围非常不错，大佬很多。

点击这个链接可以加群

如果这篇文章对您有所帮助，或者有所启发的话，帮忙扫描下发二维码关注一下，您的支持是我坚持写作最大的动力。

求一键三连：点赞、转发、在看。

最近无意间获得一份阿里大佬写的刷题笔记，一下子打通了我的任督二脉，进大厂原来没那么难。

链接：https://pan.baidu.com/s/1UECE5yuaoTTRpJfi5LU5TQ 密码：bhbe

不会有人刷到这里还想白嫖吧？点赞对我真的非常重要！在线求赞。加个关注我会非常感激！ @苏三说技术

此外，本文GitHubhttps://github.com/dvsusan/susanSayJava已经收录，有大厂面试完整考点，工作经验分享，欢迎Star。

SQL优化，算是数据库优化的一个子集。

因此，吹大牛的候选人简历上，会赫然写着”擅长MySQL数据库优化“，而吹小牛的候选人简历上，往往会写”擅长SQL优化“。

但结局是殊途同归的，就是当问他们用什么方式做的优化，他们都会说上三个字：”加索引“。

当然，好一点儿的会说可以加联合索引，它有最左前缀匹配原则（8.0以后的版本就不完全对了）之类的，还能说说覆盖索引。

那么，我的这篇文章，就好好聊聊这个面试话题。

先教大家一个小窍门，最好大家在回答面试官这个问题的时候，最好可以做到跟自己简历中项目的进行真实场景带入，这样会给面试官一种可以理论结合实践的感觉，是个大大的加分项。

举个例子，话术为：

“当时我负责电商平台的商品中心优化，我发现在展示商品列表的时候，一旦深分页就会出现加载缓慢的问题，然后我就看了一下对应的SQL语句，是这样写的：

select id, name, status, detail from product limit 10, 30;

那么一旦在深分页的话，SQL语句就会变成这样：

select id, name, status, detail from product limit 100000, 30;

那么MySQL的执行方式为：一共需要查100030条数据，然后丢弃前面的100000条，只返回后面的30条数据，这样做是非常浪费资源的。

于是我把SQL改为：

select id, name, status, detail from product where id > 100000 limit 30;

100000为上次分页中最大的商品ID，先找到它，然后再根据主键ID扫描后续30条数据。

这样做性能很高，把SQL语句从原先的耗时4300ms，降低到了18ms。”

好了，下面我正式给大家列举一下，SQL优化的N种技巧，select * 这种的就不写了哈。

上文已经讲解了，仔细看下即可。

反例：

select * from employee where address like '%通州区%';
select * from employee where address like '%通州区';

正解：

select * from employee where address like '%北京市通州区';

原因：

（1）全模糊查询，或者左边出现%的模糊查询，会导致索引实效，应该尽量从查询方式或表结构设计上避免。

（2）若无法避免，且数据量庞大的情况下，一定要使用ElasticSearch进行替代。

反例：

select product_id from orders where id=100
union
select product_id from orders where id=200;

正解：

select product_id from orders where id=100
union all
select product_id from orders where id=200;

原因：

union：对两个结果集进行并集操作，不包括重复行，相当于distinct，同时进行默认规则的排序；

union all：对两个结果集进行并集操作，包括重复行，不进行排序；

union因为要进行重复值扫描，所以在结果集庞大的情况下，效率极低，因此建议使用union all。

若结果集去重是强需求，则在应用程序代码上进行去重，因为数据库资源要比应用服务器资源更加珍贵。

straight_join功能同inner join类似，但能让左边的表来驱动右边的表，通过改变优化器对于联表查询的执行顺序的方式，获取更好的性能。

btw：若驱动表（左边）的数据量小于（被驱动表），它的执行性能要高于，驱动表（左边）的数据量大于（被驱动表）。

举个例子：

select * from t2 straight_join t1 on t2.a=t1.a;

比如上面这个，如果我们事先知道t2表的数据量一定小于t1表的话，就可以使用上面的方式指定t2表为驱动表。

需要注意的点：

（1）straight_join只适用于inner join，并不适用于left join，right join。

（2）大部分情况下，MySQL优化器是可以做出正解的。因此，使用straight_join一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。

select * from student where school_id in (select id from school);
select * from police p where exists (select 1 from user u where u.id=p.id);

如果子查询得出的结果集数据较少，主查询中的表较大且又有索引时，应该用in；反之，如果外层的主查询数据较少，子查询中的表大，又有索引时使用exists。

如果是exists，那么以外层表为驱动表，先被访问。
如果是in，那么先执行子查询。

in 是把外表和内表作 hash 连接，而 exists 是对外表作 loop 循环，每次 loop 循环再对内表进行查询。所以，我们会以驱动表的快速返回为目标，目标是以小表驱动大表，这是性能优化的本质。

之前有一种比较扯淡的说法，“exists 比 in 效率高”，大家试想一下，如何一个事物在任何场景下，都优于另外一个事物，那另外一个事物就没有存在的必要性了。

反例：

delete from user;

正解：

truncate user;

原因：

（1）truncate是直接把表删除，然后再重建表结构，性能很高，但删除操作记录不记入日志，不能回滚。

delete语句执行删除的过程是每次从表中删除一行，性能较低，但该行的删除操作会作为事务记录在日志中保存，以便进行进行回滚操作。

（2）truncate后，表和索引所占用的空间会恢复到初始大小，而delete只是将被删除的记录标记为已删除，不会立即减少表或索引所占用的空间。

反例：

insert into student(name, sex, age) values('Tom', 1, 20);
insert into student(name, sex, age) values('Tony', 1, 18);

正解：

insert into student(name, sex, age) values('Tom', 1, 20), ('Tony', 1, 18);

原因：

SQL批量操作，即一次数据库操作中插入多个数据行，相比于单条插入，可减少大量的IO交互和SQL解析开销，从而提高了插入效率。

反例

select city, avg（area） from country  group by city having city='beijing' or city='shanghai';

正解：

select city, avg（area） from country  where city='beijing' or city='shanghai'  group by city;

原因：

记住，无论是分组还是排序，或者多表join，如果可以的话，第一件事就是把用不到的记录先过滤掉。

反例：

select * from article where left(title, 4)='环球资讯';

正解：

select * from article where title=left('环球资讯', 4);

原因：

如果在索引列上使用函数，会导致索引实效。

一般情况下，应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常会使SQL执行更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。

但是，要确保没有低估需要存储的值的范围，因为在表schema中修改数据类型是一件非常耗时和痛苦的操作（特指表数据量很大的场景）。如果无法确定哪个数据类型是最好的，就选择你认为不会超过范围的最小类型。

举个例子：如果确定只需要存0—200，tinyint unsigned类型是最适合的。

char：定长，存取效率高，一般用于固定长度的表单提交数据存储，例如：身份证号，手机号，电话，密码等，长度不够的时候，会采取右补空格的方式。

varchar：不定长，更节省空间，需要用一个或者两个字节来存储数据的长度。具体规则是：如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节。

varchar由于行是变长的，在UPDATE时可能使行变得比原来更长，会导致分裂页和产生碎片。

有人认为，既然varchar是变长的，那我就尽量给它设置得大一些，以备不时之需，反正没有坏处。

其实，varchar(5) 和 varchar(200)是不一样的！

我们看下《高性能MySQL》一书中的原话：

因此，当你把varchar的长度调整为最小可用，是可以帮助你优化SQL排序性能的。

频繁作为查询条件的字段应该创建索引，频繁更新的字段不适合创建索引；
多表关联查询中的关联字段，查询中统计或者分组字段，查询中排序字段，应该创建索引；
尽量使用数据量少或区分度高的字段创建索引；
多条件组合查询优先创建组合索引，熟悉组合索引的最左前缀原则，不要创建冗余索引；
禁止使用全文索引，可以用前缀索引进行替代；
善于利用覆盖索引来优化查询；
delete和update语句的where条件必须由索引，否则会导致锁表；

适当的索引策略，经过业务取舍后，可以使SQL执行得更快。

MySQL查询优化器在执行SQL语句时，会选择它认为最合适的索引，但有时却并不准确，不是实际上最快的索引，此时可以用force index人为指定索引。

force index 跟着表名后面，用于强制使用指定的索引名(key)。

如下列所示：

select * from msg force index(idx_dest_src) where dest='18736809673' and src in ('15144804019', '18674654894');

据说，阿里巴巴开发者手册规定，join表的数量不应该超过3个，这个我还真没看到。

我个人觉得，多表关联需要控制量，但没必要完全一杆子拍死。

如果某个系统中，有很多多表关联的大SQL，那确实意味着表结构设计有问题，或者需要引入ES等技术方案了。

整体就这么多，后续如果有新的，我再补充。

最后，再给知友们来一波福利。

本人在之前看机会的时候，也从网上找遍了各式各类的八股文资料，但总觉得答案还不够准确，深度还有所欠缺，或是内容组织的逻辑性还不够清晰。

于是，我便自己动手，丰衣足食地自己总结了一套博采众家之长的八股文，那可真是字字斟酌，题题验证。

现在，我“大公无私”地把它分享出来，希望更多的同学可以由此受益。

Java技术栈的经典八股文

最后，祝大家工作顺利，纵情向前，人人都能收获自己满意的offer。

一定要善用EXPLAIN

做MySQL优化，我们要善用 EXPLAIN 查看SQL执行计划。

语法：

?type列: 连接类型。一个好的sql语句至少要达到range级别。杜绝出现all级别

?key列: 使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式

?key_len列: 索引长度

?rows列: 扫描行数。该值是个预估值

?Extra列: 详细说明。注意常见的不太友好的值有：Using filesort, Using temporary

设计：存储引擎，字段类型，范式与逆范式

功能：索引，缓存，分区分表。

架构：主从复制，读写分离，负载均衡。

合理SQL：测试，经验。

IO 永远是数据库最容易瓶颈的地方，这是由数据库的职责所决定的，大部分数据库操作中超过90%的时间都是 IO 操作所占用的，减少 IO 次数是 SQL 优化中需要第一优先考虑，当然，也是收效最明显的优化手段。

对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。

2、复合索引

3、索引不会包含有NULL值的列

4、使用短索引

5、排序的索引问题
6、like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
7、不要在列上进行运算
select * from users where YEAR(adddate)<2007;
将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成
select * from users where adddate<‘2007-01-01';
8、不使用NOT IN和<>操作

9、如果排序字段没有用到索引,就尽量少排序

10、如果限制条件中其他字段没有索引，尽量少用or

or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。很多时候使用 union all 或者是union(必要的时候)的方式来代替“or”会得到更好的效果

11、尽量用union all代替union

12、尽量使用inner join，避免left join

参与联合查询的表至少为2张表，一般都存在大小之分。如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表。

13、合理利用索引

被驱动表的索引字段作为on的限制字段。

14、巧用STRAIGHT_JOIN

基本上都是这么多，不过优化没有绝对的方案，适合才是最重要的！

1、反例

SELECT * FROM user

2、正例

SELECT id,username,tel FROM user

3、理由

节省资源、减少网络开销。
可能用到覆盖索引，减少回表，提高查询效率。

注意：为节省时间，下面的样例字段都用*代替了。

1、反例

SELECT * FROM user WHERE id=1 OR salary=5000

2、正例

（1）使用union all

SELECT * FROM user WHERE id=1 
UNION ALL
SELECT * FROM user WHERE salary=5000

（2）分开两条sql写

SELECT * FROM user WHERE id=1

SELECT * FROM user WHERE salary=5000

3、理由

使用or可能会使索引失效，从而全表扫描；
对于or没有索引的salary这种情况，假设它走了id的索引，但是走到salary查询条件时，它还得全表扫描；
也就是说整个过程需要三步：全表扫描+索引扫描+合并。如果它一开始就走全表扫描，直接一遍扫描就搞定；
虽然mysql是有优化器的，出于效率与成本考虑，遇到or条件，索引还是可能失效的；

1、正例

主键（id）：primary key优先使用数值类型int，tinyint
性别（sex）：0代表女，1代表男；数据库没有布尔类型，mysql推荐使用tinyint

2、理由

因为引擎在处理查询和连接时会逐个比较字符串中每一个字符；
而对于数字型而言只需要比较一次就够了；
字符会降低查询和连接的性能，并会增加存储开销；

1、反例

`address` char(100) DEFAULT NULL COMMENT '地址'

2、正例

`address` varchar(100) DEFAULT NULL COMMENT '地址'

3、理由

varchar变长字段按数据内容实际长度存储，存储空间小，可以节省存储空间；
char按声明大小存储，不足补空格；
其次对于查询来说，在一个相对较小的字段内搜索，效率更高；

1、char的长度是固定的，而varchar2的长度是可以变化的。

比如，存储字符串“101”，对于char(10)，表示你存储的字符将占10个字节（包括7个空字符），在数据库中它是以空格占位的，而同样的varchar2(10)则只占用3个字节的长度，10只是最大值，当你存储的字符小于10时，按实际长度存储。

2、char的效率比varchar2的效率稍高。

3、何时用char，何时用varchar2?

char和varchar2是一对矛盾的统一体，两者是互补的关系，varchar2比char节省空间，在效率上比char会稍微差一点，既想获取效率，就必须牺牲一点空间，这就是我们在数据库设计上常说的“以空间换效率”。

varchar2虽然比char节省空间，但是假如一个varchar2列经常被修改，而且每次被修改的数据的长度不同，这会引起“行迁移”现象，而这造成多余的I/O，是数据库设计中要尽力避免的，这种情况下用char代替varchar2会更好一些。char中还会自动补齐空格，因为你insert到一个char字段自动补充了空格的,但是select后空格没有删除，因此char类型查询的时候一定要记得使用trim，这是写本文章的原因。

如果开发人员细化使用rpad()技巧将绑定变量转换为某种能与char字段相比较的类型（当然，与截断trim数据库列相比，填充绑定变量的做法更好一些，因为对列应用函数trim很容易导致无法使用该列上现有的索引），可能必须考虑到经过一段时间后列长度的变化。如果字段的大小有变化，应用就会受到影响，因为它必须修改字段宽度。

正是因为以上原因，定宽的存储空间可能导致表和相关索引比平常大出许多，还伴随着绑定变量问题，所以无论什么场合都要避免使用char类型。

1、反例

SELECT * FROM user WHERE age IS NOT NULL

2、正例

SELECT * FROM user WHERE age>0

3、理由

并不是说使用了is null或者is not null就会不走索引了，这个跟mysql版本以及查询成本都有关；
如果mysql优化器发现，走索引比不走索引成本还要高，就会放弃索引，这些条件!=，<>，is null，is not null经常被认为让索引失效；
其实是因为一般情况下，查询的成本高，优化器自动放弃索引的；
如果把null值，换成默认值，很多时候让走索引成为可能，同时，表达意思也相对清晰一点；

1、反例

SELECT * FROM user WHERE salary!=5000

SELECT * FROM user WHERE salary<>5000

2、理由

使用!=和<>很可能会让索引失效
应尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描
实现业务优先，实在没办法，就只能使用，并不是不能使用

三种连接如果结果相同，优先使用inner join，如果使用left join左边表尽量小。

inner join 内连接，只保留两张表中完全匹配的结果集；
left join会返回左表所有的行，即使在右表中没有匹配的记录；
right join会返回右表所有的行，即使在左表中没有匹配的记录；

为什么？

如果inner join是等值连接，返回的行数比较少，所以性能相对会好一点；
使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少；
这是mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优；

1、反例

先分组，再过滤

select job, avg（salary） from employee 
group by job
having job='develop' or job='test';

2、正例

先过滤，后分组

select job，avg（salary） from employee 
where job='develop' or job='test' 
group by job;

3、理由

可以在执行到该语句前，把不需要的记录过滤掉

truncate table在功能上与不带where子句的delete语句相同：二者均删除表中的全部行。但truncate table比delete速度快，且使用的系统和事务日志资源少。

delete语句每次删除一行，并在事务日志中为所删除的每行记录一项。truncate table通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

truncate table删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用drop table语句。

对于由foreign key约束引用的表，不能使用truncate table，而应使用不带 where子句的 DELETE 语句。由于truncate table不记录在日志中，所以它不能激活触发器。

truncate table不能用于参与了索引视图的表。

1、降低写错SQL的代价

清空表数据可不是小事情，一个手抖全没了，删库跑路？如果加limit，删错也只是丢失部分数据，可以通过binlog日志快速恢复的。

2、SQL效率很可能更高

SQL中加了limit 1，如果第一条就命中目标return，没有limit的话，还会继续执行扫描表。

3、避免长事务

delete执行时,如果age加了索引，MySQL会将所有相关的行加写锁和间隙锁，所有执行相关行会被锁住，如果删除数量大，会直接影响相关业务无法使用。

4、数据量大的话，容易把CPU打满

如果你删除数据量很大时，不加 limit限制一下记录数，容易把cpu打满，导致越删越慢。

5、锁表

一次性删除太多数据，可能造成锁表，会有lock wait timeout exceed的错误，所以建议分批操作。

UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如：

select username,tel from user
union
select departmentname from department

这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。推荐方案：采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。

1、多条提交

INSERT INTO user (id,username) VALUES(1,'哪吒编程');

INSERT INTO user (id,username) VALUES(2,'妲己');

2、批量提交

INSERT INTO user (id,username) VALUES(1,'哪吒编程'),(2,'妲己');

3、理由

默认新增SQL有事务控制，导致每条都需要事务开启和事务提交，而批量处理是一次事务开启和提交，效率提升明显，达到一定量级，效果显著，平时看不出来。

1、表连接不宜太多，一般5个以内

关联的表个数越多，编译的时间和开销也就越大
每次关联内存中都生成一个临时表
应该把连接表拆开成较小的几个执行，可读性更高
如果一定需要连接很多表才能得到数据，那么意味着这是个糟糕的设计了
阿里规范中，建议多表联查三张表以下

2、索引不宜太多，一般5个以内

索引并不是越多越好，虽其提高了查询的效率，但却会降低插入和更新的效率；
索引可以理解为一个就是一张表，其可以存储数据，其数据就要占空间；
索引表的数据是排序的，排序也是要花时间的；
insert或update时有可能会重建索引，如果数据量巨大，重建将进行记录的重新排序，所以建索引需要慎重考虑，视具体情况来定；
一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否有存在的必要；

1、反例

SELECT * FROM user WHERE DATE_ADD(birthday,INTERVAL 7 DAY) >=NOW();

2、正例

SELECT * FROM user WHERE  birthday >=DATE_ADD(NOW(),INTERVAL 7 DAY);

3、理由

使用索引列上内置函数，索引失效。

排序时应按照组合索引中各列的顺序进行排序，即使索引中只有一个列是要排序的，否则排序性能会比较差。

create index IDX_USERNAME_TEL on user(deptid,position,createtime);
select username,tel from user where deptid=1 and position='java开发' order by deptid,position,createtime desc;

实际上只是查询出符合deptid=1 and position='java开发'条件的记录并按createtime降序排序，但写成order by createtime desc性能较差。

1、创建复合索引

ALTER TABLE employee ADD INDEX idx_name_salary (name,salary)

2、满足复合索引的最左特性，哪怕只是部分，复合索引生效

SELECT * FROM employee WHERE NAME='哪吒编程'

3、没有出现左边的字段，则不满足最左特性，索引失效

SELECT * FROM employee WHERE salary=5000

4、复合索引全使用，按左侧顺序出现 name,salary，索引生效

SELECT * FROM employee WHERE NAME='哪吒编程' AND salary=5000

5、虽然违背了最左特性，但MySQL执行SQL时会进行优化，底层进行颠倒优化

SELECT * FROM employee WHERE salary=5000 AND NAME='哪吒编程'

6、理由

复合索引也称为联合索引，当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。

联合索引不满足最左原则，索引一般会失效。

模糊查询，程序员最喜欢的就是使用like，但是like很可能让你的索引失效。

1、反例

select * from citys where name like '%大连' (不使用索引)

select * from citys where name like '%大连%' (不使用索引)

2、正例

select * from citys where name like '大连%' (使用索引) 。

3、理由

首先尽量避免模糊查询，如果必须使用，不采用全模糊查询，也应尽量采用右模糊查询，即like ‘…%’，是会使用索引的；
左模糊like ‘%...’无法直接使用索引，但可以利用reverse + function index的形式，变化成like ‘…%’；
全模糊查询是无法优化的，一定要使用的话建议使用搜索引擎。

1、type

system：表仅有一行，基本用不到；
const：表最多一行数据配合，主键查询时触发较多；
eq_ref：对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型；
ref：对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取；
range：只检索给定范围的行，使用一个索引来选择行。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range；
index：该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小；
all：全表扫描；
性能排名：system > const > eq_ref > ref > range > index > all。
实际sql优化中，最后达到ref或range级别。

2、Extra常用关键字

Using index：只从索引树中获取信息，而不需要回表查询；
Using where：WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。需要回表查询。
Using temporary：mysql常建一个临时表来容纳结果，典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时；

1、设计表的时候，所有表和字段都添加相应的注释。

2、SQL书写格式，关键字大小保持一致，使用缩进。

3、修改或删除重要数据前，要先备份。

4、很多时候用 exists 代替 in 是一个好的选择

5、where后面的字段，留意其数据类型的隐式转换。

未使用索引

SELECT * FROM user WHERE NAME=110

（1）因为不加单引号时，是字符串跟数字的比较，它们类型不匹配；

（2）MySQL会做隐式的类型转换，把它们转换为数值类型再做比较；

6、尽量把所有列定义为NOT NULL

NOT NULL列更节省空间，NULL列需要一个额外字节作为判断是否为NULL的标志位。NULL列需要注意空指针问题，NULL列在计算和比较的时候，需要注意空指针问题。

7、伪删除设计

8、数据库和表的字符集尽量统一使用UTF8

（1）可以避免乱码问题；

（2）可以避免，不同字符集比较转换，导致的索引失效问题；

9、select count(*) from table；

这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。

10、避免在where中对字段进行表达式操作

（1）SQL解析时，如果字段相关的是表达式就进行全表扫描；

（2）字段干净无表达式，索引生效；

11、关于临时表

（1）避免频繁创建和删除临时表，以减少系统表资源的消耗；

（2）在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log；

（3）如果数据量不大，为了缓和系统表的资源，应先create table，然后insert；

（4）如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除。先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定；

12、索引不适合建在有大量重复数据的字段上，比如性别，排序字段应创建索引

13、去重distinct过滤字段要少

带distinct的语句占用cpu时间高于不带distinct的语句
当查询很多字段时，如果使用distinct，数据库引擎就会对数据进行比较，过滤掉重复数据
然而这个比较、过滤的过程会占用系统资源，如cpu时间

14、尽量避免大事务操作，提高系统并发能力

15、所有表必须使用Innodb存储引擎

Innodb「支持事务，支持行级锁，更好的恢复性」，高并发下性能更好，所以呢，没有特殊要求（即Innodb无法满足的功能如：列存储，存储空间数据等）的情况下，所有表必须使用Innodb存储引擎。

16、尽量避免使用游标

因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

来源：哪吒
https://blog.csdn.net/guorui_java/article/details/126542005

欢迎来技术小站阅读，持续更新优质技术文章：

Java知音

标签：索引 in 数据

上一篇：深度解读最流行的优化算法：梯度下降
下一篇：第 62 章第 62 章

NEWS新闻中心

如何进行sql优化？

020-88888888