Sql优化

Sql优化

文章目录

1.用连接查询代替子查询2.join的表不宜过多3.join时要注意4.控制索引的数量5.选择合理的字段类型6.提升group by的效率7.索引优化

1.用连接查询代替子查询

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

2.join的表不宜过多

根据阿里巴巴开发者手册的规定，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字段，保存需要查询出的数据。

3.join时要注意

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

left 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会自动选择小表,去驱动大表。

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关联查询时，左边要用小表，右边可以用大表。如果能用inner join的地方，尽量少用left join。

4.控制索引的数量

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

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

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

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

2.加索引后每次都会建立一颗B+树,每一课B+树的每一个节点都是一个数据页,默认为16kb,所以索引过多的话存储空间过大

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

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

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

5.选择合理的字段类型

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

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

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

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

6.提升group by的效率

提升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条件在分组前，就把多余的数据过滤掉了，这样分组时效率就会更高一些。

7.索引优化

explain命令，查看mysql的执行计划。

索引失效的原因:

有时候mysql会选错索引。

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