Sybase iq 审计 sql审计工具

转载

mob6454cc6d5f87 2023-07-07 20:15:31

文章标签 Sybase iq 审计 SQL 字段测试环境 文章分类 Sybase 数据库

OAR (SQL Optimizer And Rewriter) 是一个对 SQL 进行优化和改写的自动化工具。由小米人工智能与云平台的数据库团队开发与维护

一、简介

1、功能特点

跨平台支持（支持 Linux, Mac 环境，Windows 环境理论上也支持，不过未全面测试）
目前只支持 MySQL 语法族协议的 SQL 优化
支持基于启发式算法的语句优化
支持复杂查询的多列索引优化（UPDATE, INSERT, DELETE, SELECT）
支持 EXPLAIN 信息丰富解读
支持 SQL 指纹、压缩和美化
支持同一张表多条 ALTER 请求合并
支持自定义规则的 SQL 改写

此外小米的SOAR与其他产品相比，也有很多优势：

Sybase iq 审计 sql审计工具_SQL

2、SOAR体系机构

SOAR主要有语法解析器、集成环境，优化建议，重写逻辑，工具集五大模块组成

Sybase iq 审计 sql审计工具_字段_02

语法解析和语法检查

一条SQL从文件，标准输入或命令行参数等形式传递给SOAR后首先进入语法解析器，选用了vitess的语法解析库作为SOAR的语法解析库，但随时需求的不断增加我们发现有些复杂需求使用vitess的语法解析实现起来比较逻辑比较复杂。于是参考业办其他数据库产品，于是引入了TiDB的语法解析器做为补充。后来发现这两个解析库还存在一定的盲区，于是又引入了MySQL执行返回结果作为多多版本SQL方言的补充。大家也可以看到在语法解析器这里，SOAR的实现方案是松散的、可插拔的。SOAR并不直接维护庞大的语法解析库，它把各种优秀的语法解析库集成在一起，各取所长。

集成环境

集成环境区分线上环境和测试环境两种，分别用于解决不同场景下用户的SQL优化需求。一种常见的情况是已有表结构需要优化查询SQL的场景，可以从线上环境导出表结构和足够的采样数据到测试环境，在测试环境上就可以放心的执行各种高危操作而不用担心数据被损坏。另一种常见的情况是建一套全新的数据库，需要验证提供的数据字典中是否存在优化的可能。有如下组合环境：

线上环境	测试环境	场景
有	有	日常优化，完整的建议，推荐
无	有	新申请资源，环境初始化测试
无	无	盲测，试用，无EXPLAIN和索引建议
有	无	用线上环境当测试环境，不推荐

优化建议

目前SOAR可以提供的优化建议有基于启发式规则(通常也称之为经验)的优化建议，基于索引优化算法给出的索引优化建议，以及基于EXPLAIN信息给出的解读。

启发式规则建议

下面这段代码是启发式规则的的元数据结构，它由规则代号，危险等级，规则摘要，规则解释，SQL示例，建议位置，规则函数等7部分组成。每一条SQL经过语法解析后会经过数百个启发式规则的逐一检查，命中了的规则将会保存在一个叫heuristicSuggest的变量中传递下去，与其他优化建议合并输出。这里最核心的部分，也是代码最多的部分在heuristic.go，里面包含了所有的启发式规则实现的函数。所有的启发式规则列表保存在rules.go文件中

// Rule 评审规则元数据结构type Rule struct {
    Item     string                  `json:"Item"`     // 规则代号
    Severity string                  `json:"Severity"` // 危险等级：L[0-8], 数字越大表示级别越高
    Summary  string                  `json:"Summary"`  // 规则摘要
    Content  string                  `json:"Content"`  // 规则解释
    Case     string                  `json:"Case"`     // SQL示例
    Position int                     `json:"Position"` // 建议所处SQL字符位置，默认0表示全局建议
    Func     func(*Query4Audit) Rule `json:"-"`        // 函数名
}

索引优化

等值查询优化

1）单列等值查询，为该等值列加索引

2）多列等值查询，每列求取散粒度，按从大到小排序取前N列添加到索引（N可排至）

SELECT * FROM tbl WHERE a = 123;
SELECT * FROM tbl WHERE a = 123 AND b = 456;
SELECT * FROM tbl WHERE a IS NULL;
SELECT * FROM tbl WHERE a <=> 123;
SELECT * FROM tbl WHERE a IS TRUE;
SELECT * FROM tbl WHERE a IS FALSE;
SELECT * FROM tbl WHERE a IS NOT TRUE;
SELECT * FROM tbl WHERE a IS NOT FALSE;
SELECT * FROM tbl WHERE a IN ("xxx"); -- IN单值

非等值查询优化

1）单列非等值查询，为该非等值列加索引；

2）多列非等值查询，每列求取散粒度，为散粒度最大的列加索引

思考：对于多列非等值，为filtered最小列加索引可能比较好。因为输入可变，所以现在只按散粒度排序。对于高版本MySQL如果开启了Index Merge，考虑为非等值列加单列索引可能会比较好

SELECT * FROM tbl WHERE a >= 123 -- <, <=, >=, >, !=, <>
SELECT * FROM tbl WHERE a BETWEEN 22 AND 44; -- NOT BETWEEN
SELECT * FROM tbl WHERE a LIKE 'blah%'; -- NOT LIKE
SELECT * FROM tbl WHERE a IS NOT NULL;
SELECT * FROM tbl WHERE a IN ("xxx"); -- IN多值

等值&非等值组合查询优化

1）先按照等值查询优化为等值列添加索引

2）再将非等值查询优化的列追加在等值列索引后

SELECT * FROM tbl WHERE c = 9 AND a > 12 AND b > 345; -- INDEX(c, a)或INDEX(c, b)

OR操作符

如果使用了OR操作符，即使OR两边是简单的查询条件也会对优化器带来很大的困难。一般对OR的优化需要依赖UNION ALL或Index Merge等多索引访问技术来实现。SOAR目前不会对使用OR操作符连接的字段进行索引优化。

GROUP BY子句

GROUP BY相关字段能否加入索引列表需要依赖WHERE子句中的条件。当查询指定了WHERE条件，在满足WHERE子句只有等值查询时，可以对GROUP BY字段添加索引。当查询未指定WHERE条件，可以直接对GROUP BY字段添加索引。

按照GROPU BY的先后顺序添加索引;

GROUP BY字段出现常量，数学运算或函数运算时会给出警告;

ORDER BY子句

ORDER BY相关字段能否加入索引列表需要依赖WHERE子句和GROUP BY子句中的条件。当查询指定了WHERE条件，在满足WHERE子句只有等值查询且无GROUP BY子句时，可以对ORDER BY字段添加索引。当查询未指定WHERE条件，在满足无GROUP BY子句时，可以对ORDER BY字段添加索引。

多个字段之间如果指定顺序相同，按照ORDER BY的先后顺序添加索引；

多个字段之间如果指定顺序不同，所有ORDER BY 字段都不添加索引；

ORDER BY字段出现常量，数字运算或者函数时给出警告；

JOIN索引优化算法

1）LEFT JOIN为右表加索引；

2）RIGHT JOIN为左表加索引；

3）INNER JOIN 两张表都加索引；

4）NATURAL 的处理方法参考前三条；

5）STRAIGHT_JOIN为后面的表加索引；

针对SUBQUERY和UNION的复杂查询

对于使用了IN，EXIST等词的SUBQUERY或UNION类型的SQL，先将其拆成多条独立的SELECT语句。然后基于上面简单查询索引优化算法，对单条SELECT查询进行优化。SUBQUERY的连接列暂不考虑添加索引

SELECT * FROM film WHERE language_id = (SELECT language_id FROM language LIMIT 1);
 
1. SELECT * FROM film;
2. SELECT language_id FROM language LIMIT 1;
SELECT * FROM city a LEFT JOIN country b ON a.country_id=b.country_idUNIONSELECT * FROM city a RIGHT JOIN country b ON a.country_id=b.country_id;
 
1. SELECT * FROM city a LEFT JOIN country b ON a.country_id=b.country_id;
2. SELECT * FROM city a RIGHT JOIN country b ON a.country_id=b.country_id;

无法使用索引

如下类型的查询条件无法使用索引或者SOAR无法给出正确的索引建议

-- MySQL无法使用索引
SELECT * FROM tbl WHERE a LIKE '%blah%';
SELECT * FROM tbl WHERE a IN (SELECT...)
SELECT * FROM tbl WHERE DATE(dt) = 'xxx'SELECT * FROM tbl WHERE LOWER(s) = 'xxx'SELECT * FROM tbl WHERE CAST(s …) = 'xxx'SELECT * FROM tbl where a NOT IN()
-- SOAR不支持的索引建议
SELECT * FROM tbl WHERE a = 'xxx' COLLATE xxx -- vitess语法暂不支持
SELECT * FROM tbl ORDER BY a ASC, b DESC -- 8.0+支持
SELECT * FROM tbl WHERE `date` LIKE '2016-12%' -- 时间数据类型隐式类型转换

重写逻辑

上面提到的优化建议是早期实现的主要功能，早期的功能还只是停留在建议上，对于一些初级用户看到建议也不一定会改写。为了进一步简化SQL优化的成本，SOAR又进一步挖掘了自动SQL重写的功能。现在提供几十种常见场景下的SQL等价转写，不过相比SQL优化建议还有很大的改进空间。

工具集

除了SQL优化和改写以外，为了方便用户使用以及美化输出展现形式，SOAR还提供了一些辅助的小工具，比如markdown转HTML工具，SQL格式化输出工具等等。可以在常用命令中找到这些小工具的使用方法

二、实践

1、go语言安装

yum -y install go

2、安装-下载二进制安装包

wget https://github.com/XiaoMi/soar/releases/download/0.9.0/soar.linux-amd64 -O soar
chmod a+x soar

3、安装验证

echo 'select * from user' | ./soar -report-type json
{
  "AC4262B5AF150CB5": {
    "CLA.001": {
      "Item": "CLA.001",
      "Severity": "L4",
      "Summary": "最外层SELECT未指定WHERE条件",
      "Content": "SELECT语句没有WHERE子句，可能检查比预期更多的行(全表扫描)。对于SELECT COUNT(*)类型的请求如果不要求精度，建议使用SHOW TABLE STATUS或EXPLAIN替代。",
      "Case": "select id from tbl",
      "Position": 0
    },
    "COL.001": {
      "Item": "COL.001",
      "Severity": "L1",
      "Summary": "不建议使用SELECT * 类型查询",
      "Content": "当表结构变更时，使用*通配符选择所有列将导致查询的含义和行为会发生更改，可能导致查询返回更多的数据。",
      "Case": "select * from tbl where id=1",
      "Position": 0
    }
  }
}

4、源码下载

wget https://codeload.github.com/xiyangxixian/soar-web/zip/master -O soar-web-master.zip
unzip soar-web.zip

5、soar web环境安装

soar-web环境安装 https://github.com/xiyangxixian/soar-web

6、使用配置文件

SOAR提供了丰富的配置参数，可以根据不同的使用场景指定不同的配置项来定制私有的评审建议，即使不使用配置文件，也可以使用SOAR的基础作用

SOAR的配置文件为YAML格式，在一般情况下只需要配置online-dsn, test-dsn, log-output等少数几个参数，就可以体验到SOAR提供的绝大部分的功能了；

7、配置环境

由于很多功能需要根据数据库的元数据来进行判断，所以当不指定数据库环境的时候，功能不能使用完全，为了保护线上数据库的安全，将数据库环境区分为线上和测试两种环境，但是这并不意味着一定需要两个数据库环境，但至少指定一个测试环境。

Sybase iq 审计 sql审计工具_测试环境_03

这里需要注意的是，因为我们会在测试环境中做一些风险相对较高的操作，为了防止用户由于配置疏忽导致线上数据受损，我们添加了allow-online-as-test参数来保证您确实是希望将线上环境做为测试环境来使用。所以当您指定的两个集成环境相同时，请指定allow-online-as-test为true，否则SOAR将不会进行评审。

默认文件会按照/etc/soar.yaml, ./etc/soar.yaml, ./soar.yaml顺序加载，找到第一个后不再继续加载后面的配置文件。如需指定其他配置文件可以通过-config参数指定。即使不创建配置文件SOAR仍然会给出基本的启发式建议，关于数据库权限online-dsn需要相应库表的SELECT权限

本地只搭建了测试环境，如下：

vim soar.yml
 
# 线上环境配置
#online-dsn:
#addr: 127.0.0.1:3306
#schema: mytest
#user: dba_user
#password: msds007
#disable: false
# 测试环境配置
test-dsn:
addr: 192.168.221.140:3306
schema: test01
user: root
password: root
disable: false
# 是否允许测试环境与线上环境配置相同
allow-online-as-test: true

创建表结构如下：

CREATE TABLE `t6` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `info` char(233) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100000 DEFAULT CHARSET=utf8

进行sql查询，无索引

使用mysql的explain:

mysql> explain select info from t6 where age < 100 order by name;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | t6    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

使用soar测试：

echo "select info from t6 where age < 100 order by name;" |./soar -config ./soar.yml --report-type json
{
  "24C592BB08046BCD": {
    "EXP.000": {
      "Item": "EXP.000",
      "Severity": "L0",
      "Summary": "Explain信息",
      "Content": "| id | select\\_type | table | partitions | type | possible_keys | key | key\\_len | ref | rows | filtered | scalability | Extra |\n|---|---|---|---|---|---|---|---|---|---|---|---|---|\n| 1  | SIMPLE | *t6* | NULL | ALL |  | NULL |  |  | 1 | ☠️ **100.00%** | ☠️ **O(n)** | Using where; Using filesort |\n\n",

"Case": "### Explain信息解读\n\n#### SelectType信息解读\n\n* **SIMPLE**: 简单SELECT(不使用UNION或子查询等).\n\n#### Type信息解读\n\n* ☠️ **ALL**: 最坏的情况, 从头到尾全表扫描.\n\n#### Extra信息解读\n\n* **Using where**: WHERE条件用于筛选出与下一个表匹配的数据然后返回给客户端. 除非故意做的全表扫描, 否则连接类型是ALL或者是index, 且在Extra列的值中没有Using Where, 则该查询可能是有问题的.\n\n* ☠️ **Using filesort**: MySQL会对结果使用一个外部索引排序,而不是从表里按照索引次序读到相关内容. 可能在内存或者磁盘上进行排序. MySQL中无法利用索引完成的排序操作称为'文件排序'.\n",

"Position": 0
    },
    "IDX.001": {
      "Item": "IDX.001",
      "Severity": "L2",
      "Summary": "为optimizer_210420021045_xtme6s7wd8kcduhh库的t6表添加索引",
      "Content": "为列age添加索引;为列name添加索引",
      "Case": "ALTER TABLE `optimizer_210420021045_xtme6s7wd8kcduhh`.`t6` add index `idx_age_name` (`age`,`name`) ;\n",
      "Position": 0
    }
  }
}

根据soar的建议添加索引

mysql> alter table t6 add index idx_age_name(age,name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> explain select info from t6 where age < 100 order by name;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | t6    | NULL       | range | idx_age_name  | idx_age_name | 5       | NULL |    1 |   100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

结论：经过soar分析后的sql语句，执行效率更好

内容参考官网： https://github.com/XiaoMi/soar

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。