V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
flikecn
V2EX  ›  程序员

kingshard SQL 黑名单功能介绍

  •  
  •   flikecn ·
    flike · 2016-01-25 09:26:35 +08:00 · 2687 次点击
    这是一个创建于 2985 天前的主题,其中的信息可能已经有所发展或是发生改变。

    kingshard SQL 黑名单功能介绍

    1. 应用场景介绍

    在 kingshard 开源之后,有用户多次提到能不能在 kingshard 中加入 SQL 黑名单机制,让 kingshard 能够根据特定的规则来拦截在黑名单中的 SQL 。有几个比较典型的应用场景:

    1. DBA 定义一些比较危险的 SQL ,放在 SQL 黑名单文件中。可以避免前端应用发过来的 SQL 对数据库造成危害。这种 SQL 有可能是开发者粗心编写的,也有可能是被 SQL 注入生成的 SQL 。例如:delete from mytable,这种不带 where 条件的 SQL ,会把整个表删除。
    2. 在 kingshard 项目上线后,通过 log 发现存在大量某条 SQL 给 DB 造成了很大的压力。这时候可以动态地将这条 SQL 加入黑名单,阻止该 SQL 的执行,从而使数据库压力降低。例如:select count(*) from mytable where xxxx,这类 SQL 如果没有优化得当,是很容易造成系统的 IO 过高的。

    2. 功能介绍

    在 kingshard 如果想使用 SQL 黑名单功能,只需要在配置中添加:

    blacklist_sql_file: /Users/flike/blacklist

    然后我们在 blacklist 定义 SQL 黑名单,这样 kingshard 在转发的时候,就会阻止黑名单中 SQL 的转发。

    黑名单 SQL 以正则表达式的形式定义。对于 SQL 中的值用??+代替。为保证黑名单有效,最好手动验证一下, kingshard 是否正确拦截了黑名单中的 SQL 。定义规则(上一条是原 SQL ,对应的下一条是黑名单形式的 SQL )可以参考下列例子:

    SELECT c FROM t WHERE id=1
    select c from t where id=?
    
    SELECT * FROM prices.rt_5min where id=1
    select * from prices.rt_5min where id=?
    
    select null, 5.001, 5001. from foo
    select ?, ?, ? from foo
    
    select 'hello', '\nhello\n', \"hello\", '\\'' from foo
    select ?, ?, ?, ? from foo
    
    select 'hello'\n
    select ?
    
    select * from t where (base.nid IN  ('1412', '1410', '1411'))
    select * from t where (base.nid in(?+))
    
    select * from foo where a in (5) and b in (5, 8,9 ,9 , 10)
    select * from foo where a in(?+) and b in(?+)
    
    select * from foo limit 5
    select * from foo limit ?
    
    select * from foo limit 5, 10
    select * from foo limit ?, ?
    
    select * from foo limit 5 offset 10
    select * from foo limit ? offset ?
    
    INSERT INTO t (ts) VALUES (NOW())
    insert into t (ts) values(?+)
    
    insert into foo(a, b, c) values(2, 4, 5)
    insert into foo(a, b, c) values(?+)
    
    CALL foo(1, 2, 3)
    call foo
    
    LOAD DATA INFILE '/tmp/foo.txt' INTO db.tbl
    load data infile ? into db.tbl
    
    administrator command: Init DB
    administrator command: Init DB
    
    use `foo`
    use ?
    

    3.功能演示

    在 blacklist 加入如下 SQL:

    select count(*) from test_shard_hash where id > ?
    select count(*) from test_shard_range
    SELECT * FROM WORLD
    DELETE FROM WORLD
    

    连接 kingshard ,执行 SQL 显示如下:

    mysql> select * from world;
    ERROR 1105 (HY000): sql in blacklist.
    mysql> select * from world where a > 0;
    +------+------+
    | a    | b    |
    +------+------+
    |   10 |   23 |
    |   45 |  565 |
    +------+------+
    2 rows in set (0.00 sec)
    
    mysql> delete from world;
    ERROR 1105 (HY000): sql in blacklist.
    mysql> delete from world where a =10;
    Query OK, 1 row affected (0.00 sec)
    #注意在 SQL 黑名单中该 SQL 是大于后面有个空格,必须要严格匹配,否则#kingshard 不会认为是黑名单 SQL
    mysql> select count(*) from test_shard_hash where id >1;
    +----------+
    | count(*) |
    +----------+
    |       24 |
    +----------+
    1 row in set (0.02 sec)
    
    mysql> select count(*) from test_shard_hash where id > 1;
    ERROR 1105 (HY000): sql in blacklist.
    

    用 sysbench 测试了一下存在 blacklist 时 kingshad 的性能,发现性能并没有明显下降,所以可以放心使用该功能。

    kingshard 数据库中间件 github 地址: https://github.com/flike/kingshard

    8 条回复    2016-01-25 14:00:16 +08:00
    flikecn
        1
    flikecn  
    OP
       2016-01-25 09:38:17 +08:00
    感兴趣的可以来聊聊,你们公司如何出来 SQL 黑名单的?
    mengzhuo
        2
    mengzhuo  
       2016-01-25 10:09:42 +08:00
    = =||
    我司用 ORM ,不需要黑名单
    flikecn
        3
    flikecn  
    OP
       2016-01-25 10:23:10 +08:00
    @mengzhuo 恩, ORM 可以避免第一个场景,第二个场景可能就需要回滚代码了。:)
    evakiss
        4
    evakiss  
       2016-01-25 10:53:44 +08:00 via Android
    看到你的头像好伤感
    flikecn
        5
    flikecn  
    OP
       2016-01-25 11:36:34 +08:00
    @evakiss 哈哈。这是我以前用 jobdeer 提供的工具生成的图片。 jobdeer 挺不错的,可惜倒闭了。
    mengzhuo
        6
    mengzhuo  
       2016-01-25 11:39:20 +08:00
    @flikecn 第二个……这样业务不受影响么……
    incompatible
        7
    incompatible  
       2016-01-25 11:45:45 +08:00 via iPhone
    @mengzhuo 只影响这一个业务,比让这个烂 sql 拖慢整个系统的业务要好一些。
    flikecn
        8
    flikecn  
    OP
       2016-01-25 14:00:16 +08:00
    @incompatible 恩,我也是怎么认为的。因为拖慢了数据库有可能引起雪崩。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   1116 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 30ms · UTC 18:49 · PVG 02:49 · LAX 11:49 · JFK 14:49
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.