mysql数据优化

字节跳动Java研发面试99题(含答案)：JVM+Spring+MySQL+线程池+锁_java字节跳动外包笔试面试题-优快云博客

https://zhuanlan.zhihu.com/p/431837711

6 in中值太多

对于批量查询接口，我们通常会使用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做限制。

用连接查询代替子查询（子查询 即用in的方式）

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

子查询的例子如下：

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

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

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

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

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

7 增量查询

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

反例：

select * from user;

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

这时该怎么办呢？

正例：

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

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

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

12 控制索引的数量

10 join的表不宜过多

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

13 选择合理的字段类型

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

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

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

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

5 索引优化

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

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

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

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

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

例如：

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

Mysql优化：

1、对查询进行优化，应尽量避免全表扫描，可在where及order by涉及的列上建立索引。

2、避免在where 子句中使用!=或<>操作，否则引擎会放弃使用索引而进行全表扫描。

3、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这样会降低查询和连接的性能，。

因为引擎在处理查询和连接时会对字符串中的每个字符进行比较，而对于数字型而言，只需要比较

一次就够了。

4 任何地方不要使用select * from t,用具体的字段列表代替“*”，不要返回用不到的任何字段。

5、避免频繁创建和删除临时表，以减少系统表资源的消耗。诸如此类，等等等等...

1 通过

select xx from  A a   left join B b tou on a.telephone=b.telephone and b.taskId in ()..

Mysql查询速度居然占用了 30m多，很慢。

通过采用了索引的方法，即在B表中需要连接的字段建立索引（A为主表，必须一个一个查，无法优化，只能B表），即字段

telephone和taskId ，建立索引如下：

create index B_index_tel_tskId on B(telephone,taskId);

此时在

select xx from  A a   left join B b tou on a.telephone=b.telephone and b.taskId in ()..

查询时间不超过1s，时间提升了很大级别。