分享几种常用的SQL优化改写方法

自从前些年阿里开始去IOE行动以来，这些年在现在的企业很难再见到ORACLE的身影，尤其是互联网行业。目前应该只有传统行业里某些部门或者金融银行等行业ORACLE还仍在服役，其他都已经被MYSQL,PG,等关系型数据库所替代了，所以不得不说技术迭代更新必须要不断更新自己的技能储备才不能不被淘汰。本人已经已有年头没有碰过ORACLE了，顺应趋势一直在研究学习MYSQL和大数据相关技术工作，不过无论你的数据框架如何变化，对于开发来讲，底层仍是我们之前所了解的核心知识，那就是SQL。下面整理了一些关于SQL的优化方法，本文只适用于ORACLE和MYSQL之类的传统关系型数据库使用。

【WHERE条件中OR的优化方法】 【一次Using intersect优化方法】 【大表DML操作的优化方法】 【高水位的优化方法】

一，WHERE过滤中OR的优化方法

我们在开发需求的时候会发现有这样的需求，就是前端WEB有一个模糊匹配的对话框，内容需要全局搜索某几项。
例如：

那么我们第一想到的SQL是这样写的：

注:sql不代表真正业务含义，只描述优化思想

select  t.id,
		t.name,
		t.code,
		t.tel,
		t.alis,
		t.address
from product t 
where 1=1
and (t.product_name like '%xxx%' or t.shop_name like '%xxx%');

如果这样子写你会发现你的WEB界面响应会炒鸡慢，production_name和shop_name如果有索引，因为前置模糊匹配的原则导致这2个字段也不会用到索引，再加上OR判断会增加更多的计算，如果这2个字段还在多表上，就要做多表关联，再加上要是数据量大结果集的返回就会很慢了。所以此类优化可以用2个维度方法解决：

1. SQL层面优化 ，成本低，见效快；
   前提需要对表进行做表时间分区，时间创建索引，过滤条件也要有索引，改成UNION方式解决全局模糊匹配。

select  t.id,
		t.name,
		t.product_name,
		t.shop_name
from product t 
where 1=1
and t.create_at > '2019-01-01 00:00:00'
and t.create_at < '2019-01-30 00:00:00'
and t.product_name like '%xxx%'
union 
select  t.id,
		t.name,
		t.code,
		t.tel,
		t.alis,
		t.address
from product t
where 1=1
and t.create_at > '2019-01-01 00:00:00'
and t.create_at < '2019-01-30 00:00:00'
and t.shop_name like '%xxx%';

2. 业务层面优化 ，修改需求方案，变通解决；
   修改全局搜索为多级联动，做到缩小范围，缩小类别等进行过滤，后台SQL需要根据增加联动字段进行过滤.并配合分页提高整体性能。并且过滤条件字段按需做索引优化，创建最优的索引策略。
   
3. 设备层面优化 ，堆硬件用缓存；
   堆硬件目前是最粗暴的解决方式，如果你不差钱那就这么去做吧，上HANA集群，堆一体机，设备用最好的，内存用最大的，这时你不用考虑索引该怎么建，数据类型怎么取，SQL要怎么写等等，那绝对能解决你大部分问题，但是随着业务的增长，这种方式不是一个最理智的解决方法，我们要从根本上去解决核心问题，避免出现不必要的浪费，例如，你的MYSQL可以做读写分离，主从高可用，把核心的数据放到缓存中，大量的查询可以上列存储数据库，有必要可以用内存类型的，所以一切优化都是基于你的业务，只有最合适，没有最完备的。

二，一次Using intersect优化方法

先上SQL：

explain
SELECT 	t.id,
		t.no,
		t.name
 FROM orders t
	 WHERE 1 = 1
	  AND t.created_at>='2019-03-01 00:00:00'
	  AND t.created_at<='2019-03-05 00:00:00'
	  and t.status = 'xxx'
	  AND t.type = 'eee'

id|select_type|table |type       |possible_keys                                     |key                                    |key_len|ref   |rows  |Extra                                                                             
--|-----------|------|-----------|--------------------------------------------------|---------------------------------------|-------|------|------|----------------------------------------------------------------------------------
1 |SIMPLE     |t     |index_merge|created_at,orders_status_IDX,orders_type_IDX|orders_status_IDX,orders_type_IDX|1,40   |[NULL]|849953|Using intersect(orders_status_IDX,orders_type_IDX); Using where; Using index

Using intersect是MERGE的一种索引合并的执行计划，会自动在ON的相关索引字段进行合并，有时因为合并导致性能很差，所以我们要人为的改变引擎认为对的执行计划，那么方法就是设置MYSQL的全局参数：SET optimizer_switch = ‘index_merge_intersection=off’，让引擎不自动合并我们所需要的索引字段，另一种方法就是自两个字段删除各自索引，创建组合索引，第三种方法就是改写SQL,此方法适用于小范围场景，可以根据自己具体情况进行如下方法的改写，代码如下：

explain
SELECT 	t.id,
		t.no,
		t.name
 FROM orders t
	 WHERE 1 = 1
	 AND t.created_at>='2019-03-01 00:00:00'
	  AND t.created_at<='2019-03-05 00:00:00'
	  AND t.type = 'eee'
	  AND exists(select 1 from orders s
	  WHERE 1=1
	  AND s.id = t.id
	  AND s.`status` = 'xxx')
	  
id|select_type       |table |type  |possible_keys                   |key                  |key_len|ref               |rows   |Extra                   
--|------------------|------|------|--------------------------------|---------------------|-------|------------------|-------|------------------------
1 |PRIMARY           |t     |ref   |created_at,orders_type_IDX|orders_type_IDX|40     |const             |1699907|Using where; Using index
2 |DEPENDENT SUBQUERY|s     |ref   |PRIMARY,orders_status_IDX       |PRIMARY              |4      |db_production.t.id|1      |Using where             

最后我们验证下数据量是否正确

select 
	max(old) as old,
	max(new) as new
from (
	SELECT	count(*) as old,
			null as new
	 FROM orders t
	 WHERE 1 = 1
	  AND t.created_at>='2019-03-01 00:00:00'
	  AND t.created_at<='2019-03-05 00:00:00'
	  and t.status = 'xxx'
	  AND t.type = 'eee'
	union all 
	 SELECT  null as old,
			count(*) as new
	 FROM orders t
	 WHERE 1 = 1
	 AND t.created_at>='2019-03-01 00:00:00'
	  AND t.created_at<='2019-03-05 00:00:00'
	  AND t.type = 'eee'
	  AND exists(select 1 from orders s
	  WHERE 1=1
	  AND s.id = t.id
	  AND s.`status` = 'xxx')
  ) t；
  
-------------  
  old |new   
------|------
295868|295868

三，大表DML操作的优化方法

我们在项目中经常会遇到大量数据需要DML操作的工作，这里我整理了目前比较实用的一些千古不变的方法，但是线上有诸多实现方式，可以根据自己的实际场景进行调整。
这里我主要讲下MYSQL的大表DML方法，因为MYSQL没有ROWID所以我们在MYSQL中只能用主键ID进行切片DML。原理基本保持一致，首先例如我们要对一张1KW数据量的表进行DELETE，那我们首先要做什么？首先要确定是否有主键，如果该没有主键就要用造一个类O的ROWID的主键，Innodb表中在没有默认主键的情况下会生成一个6byte空间的自动增长主键，因此我们最好要确认表中是否有主键。然后我们就用主键进行切片，1KW我们可以切成1000份，每个事务1W条进行提交COMMIT，如下伪代码：

//伪代码：
select count(*) into total from table_t;
var cnt=10000
	for i in total/cnt loop 
		delete from table_t t 
		where 1=1
		and t.id <= :cnt;
		commit;
	 cnt:=cnt+10000;
	end loop;

切片DML可以根据需求进行并行执行，每个并行执行单独事务，具体代码可具体根据自己场景进行调整，另外，DELETE会产生高水位问题，如果有查询不到结果性能又差空间并未释放可排查是否出在高水位导致的，高水位解决方法可以参考下面的案例。

四，高水位的优化方法

网上很多关于MYSQL高水位的解决方法，但是没有具体分析使用场景，如果利用不到会导致业务故障，所以下面我针对网上的集中方式进行场景分析：

• 执行 OPTIMIZE TABLE 表名：只对MyISAM, BDB和InnoDB表起作用，而且会锁表！

不建议用此方法在生产进行执行，最好在可执行的时间窗进行，而且并不是所有表都需要进行碎片整理，一般只需要对包含可变长度的文本数据类型的表进行整理即可。

• 写一SQL，创建新表，删除旧表，新表重命名：生产环境中不停机情况下，大数据比较难处理。

create table xxx_tmp like xxx;  //创建一个备份表结构
insert into xxx_tmp select * from xxx; //把主表数据插入到备份表，如果数据量大，需要一定时间窗。
drop table xxx; //把主表删除，立即执行RENAME  
rename table xxx_tmp to xxx;//毫秒级执行

不建议对高水位的处理在生产时间执行，最好在特定时间窗进行。

Authors:西红柿牛男
EMAIL:gotoywh@hotmail.com