MySQL优化策略

1、 sql优化

1.1 SQL 语句简化，简化是 SQL 优化的一大利器，因为简单，所以优越。

1.2 尽可能避免或者杜绝多表复杂关联，大表关联是大表处理的噩梦，一旦打开了这个口子，越来越多的需求需要关联，性能优化就没有回头路了

1.4 SQL 中尽可能避免反连接，避免半连接，这是优化器做得薄弱的一方面，什么是反连接，半连接？

其实比较好理解，举个例子：not in，not exists 就是反连接，in，exists 就是半连接，在千万级大表中出现这种问题，性能是几个数量级的差异。

1.3 多表关联时，通过关联字段多虑不必要的表，

比如我们有商品表goods、订单表order、订单地址表address，但虚拟商品是没有地址的，所有查询虚拟商品时就不需要关联address地址表，可以通过关联字段过滤掉address表查询（and g.goods_type = '实物' ）， 如下

select * from order o left join goods g on o.good_name=g.name 
left join address a on o.order_no=a.order_no and g.goods_type = '实物' 

1.4 分批处理

不带分页参数的查询或者影响大量数据的update和delete操作，我们要把它打散分批处理；

比如说，我们的定时任务需要每天定时更新用户所有已过期的优惠券为不可用状态。

update status=0 FROM `coupon` WHERE expire_date <= #{currentDate} and status=1;

如果大量优惠券需要更新为不可用状态，执行这条SQL可能会堵死其他SQL，分批处理伪代码如下：

int pageNo = 1;int PAGE_SIZE = 100;
while(true) {   
 List<Integer> batchIdList = queryList('select id FROM `coupon` WHERE expire_date <= #{currentDate} and status = 1 limit #{(pageNo-1) * PAGE_SIZE},#{PAGE_SIZE}');    
  if (CollectionUtils.isEmpty(batchIdList)) {        
  return;    
  }    
  update('update status = 0 FROM `coupon` where status = 1 and id in #{batchIdList}')    pageNo ++;
}

1.5 OR优化

在Innodb引擎下，or无法使用组合索引，比如：

select id，product_name from orders where mobile_no = '13421800407' or user_id = 100;

OR无法命中mobile_no + user_id的组合索引，可采用union，如下所示：

(select id，product_name from orders where mobile_no = '13421800407') union(select id，product_name from orders where user_id = 100);

此时id和product_name字段都有索引，查询才最高效。

1.6 IN 优化

IN适合主表大子表小，EXIST适合主表小子表大。由于查询优化器的不断升级，很多场景这两者性能差不多一样了。

1.7 不做列运算

通常在查询条件列运算会导致索引失效，如下所示：

//查询当日订单
select id from order where date_format(create_time，'%Y-%m-%d') = '2019-07-01';

date_format函数会导致这个查询无法使用索引，改写后：

select id from order where create_time between '2019-07-01 00:00:00' and '2019-07-01 23:59:59';

1.8 Limit优化

limit用于分页查询时越往后翻性能越差，解决的原则：缩小扫描范围，如下所示

select * from orders order by id desc limit 0,10
//耗时0.4秒

select * from orders order by id desc limit 1000000,10
//耗时5.2秒

可采用缩小范围方法，如下

select * from orders where id > (select id from orders order by id desc  limit 1000000, 1) order by id desc limit 0,10
//耗时0.5秒

如果查询条件仅有主键ID，写法如下：

select id from orders where id between 1000000 and 1000010 order by id desc
//耗时0.3秒，但是这要求表中数据没有被删除过

如果以上方案依然很慢呢？只好用游标了，感兴趣的朋友阅读JDBC使用游标实现分页查询的方法

1.9 避免Select all

如果不查询表中所有的列，避免使用SELECT *，它会进行全表扫描，不能有效利用索引。

select * from order;
//全表扫描
select order_no from order;
//会走索引

1.10 操作符<>优化

通常<>操作符无法使用索引，举例如下，查询金额不为100元的订单：

select id from orders where amount != 100;

如果金额为100的订单极少，这种数据分布严重不均的情况下，有可能使用索引。鉴于这种不确定性，采用union聚合搜索结果，改写方法如下：

(select id from orders where amount > 100) union all(select id from orders where amount < 100 and amount > 0)

2、索引优化

2.1 索引分类

• 普通索引：最基本的索引；
• 组合索引：多个字段上建立的索引，能够加速复合查询条件的检索；
• 唯一索引：与普通索引类似，但索引列的值必须唯一，允许有空值；
• 组合唯一索引：列值的组合必须唯一；
• 主键索引：特殊的唯一索引，用于唯一标识数据表中的某一条记录，不允许有空值，一般用primary key约束；
• 全文索引：用于海量文本的查询，MySQL5.6之后的InnoDB和MyISAM均支持全文索引。由于查询精度以及扩展性不佳，更多的企业选择Elasticsearch；

2.2 索引优化

• 必须有主键。
• SQL 查询基于索引或者唯一性索引，使得查询模型尽可能简单；
• 尽可能杜绝范围数据的查询，范围扫描在千万级大表情况下还是尽可能减少，因为范围查询会锁表，业务高峰期影响太大；
• 分页查询很重要，如果查询数据量超过30%，MYSQL不会使用索引；
• 单表索引数不超过5个、单个索引字段数不超过5个；
• 字符串可使用前缀索引，前缀长度控制在5-8个字符；
• 字段唯一性太低，增加索引没有意义，如：是否删除、性别；
• 索引不会包含有NULL值的列，只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

索引使用不当导致索引失效的情况：

• 复合索引使用时遵循最左匹配原则，如果不是从最左列开始时，整个索引失效，如果最左匹配则依次往右使用索引，直到碰到不匹配的地方之后生效之前匹配到的索引，比如，我们索引是a,b,c，但是使用的时候是b,c,a那索引就不会生效；但是如果是a,c,b还是生效的，不过生效的只是索引a

• < 小于、 > 大于、 <= 、>= 这个根据实际查询数据来判断，如果全盘扫描速度比索引速度要快则不走索引 。也就是说，表数据量少时不需要走索引，数据量大时才需要走索引

• 使用like时通配符在前

• 使用or 语句： or 后面的语句使用了非索引字段，整个SQL也将无法使用到索引

• 使用负向查询（not ，not in， not like ，<> ,!= ,!> ,!< ，is null，is not null） 不会使用索引

• 关联查询时索引失效，如果以下两个条件未满足，基本是用不上索引的

  • 1、两表关联使用的条件字段中字段的长度是否是一致的
  • 2、两表关联使用的条件字段中字段的编码是否是一致的

3、表优化

一般根据业务类型把数据分为三种：

3.1. 流水型数据

流水型数据是无状态的，多笔业务之间没有关联，每次业务过来的时候都会产生新的单据。

比如交易流水、支付、充值流水，只要能插入新单据就能完成业务，特点是后面的数据不依赖前面的数据，所有的数据按时间流水进入数据库。

优化方向：根据业务拆分表，最简单的，可以按照时间来分表，每个月的数据一张表；

3.2.状态型数据

状态型数据是有状态的，多笔业务之间依赖于有状态的数据，而且要保证该数据的准确性，比如充值时必须要拿到原来的余额，才能支付成功，或者是消息表，消息的状态是必须的。

优化方向：根据数据状态拆分表，把表分成历史表（数据状态已经完结）和实时表，这样实时表数据量就不会很大，历史表也可以分表，或者把无意义的过期的数据定时删除

3.3.配置型数据、基础数据

此类型数据数据量较小，而且结构简单，一般为静态数据，变化频率很低。比如，用户表，设备配置表。

优化方向：根据业务类型尽量把表拆分成小而简，避免大而全。