情報技術で日々を楽しく！: 10月 2015

パフォーマンス改善が必要となり、Java(MyBatis)に実装されていたSQLのロジックを
分割、MySQLのプロシージャへもっていくことになりました。

MyBatisでは<if>タグやバインド変数#～により
動的にSQL(Where句)を組み立てていたのですが、同様のことを
MySQLで実施しようとすると、動的SQLの組み立てが必要となります。

プロシージャ側で動的SQLを組み立てるには文字列の結合が
必要となり、ソースコードの保守性が著しく低下するため
代替え案がないかを検証した記録を残しておきます。

【考えた代替え案】

Where句にパラメータがブランクかどうかの判断も入れてしまう
例) SELECT * FROM TABLE1 WHERE parameter1 = '' or column1 = parameter1

【先に結論】

Where句内でOrでパラメータ有無を指定しても、動的SQLでSQLを組み立てたときと
性能は変わらない。

【チューニングとしては】

サブクエリの一部を切り出して、テンポラリテーブルへ吐き出すようにして、その後に
そのクエリを利用してデータ取得をするようにしました。
このSQLのチューニングの肝としては、サブクエリが多すぎ、深すぎでMySQLでよく言われる
ところの「サブクエリ遅いよ」に対処した。ということになります。

【検証した内容】

・ダミーテーブルを準備。データは100万件。プライマリ、インデックスはあえて作らない。
・ストアドを２セット用意し、片方はパラメータブランクによるSQL、もう片方は動的SQLとする
・ダミーテーブルのカラム数が２つだと比較差異が出なかったため、カラムを１０に
　増やした場合のケースも検証

【実コード】



-- テスト用テーブルの作成
drop table if exists test1;
create table test1 ( k varchar(10) , va varchar(10));

-- テスト用テーブルにデータを作成するプロシージャ
DROP PROCEDURE IF EXISTS test1createsampledate;
delimiter //
CREATE DEFINER=`root`@`localhost` PROCEDURE test1createsampledate()
BEGIN
    declare i decimal(10) default 0;
    while i <= 1000000 do
        insert into test1 values (i,i*10)
                                ,(i+1,(i+1)*10),(i+2,(i+2)*10),(i+3,(i+3)*10),(i+4,(i+4)*10),(i+5,(i+5)*10),(i+6,(i+6)*10),(i+7,(i+7)*10),(i+8,(i+8)*10),(i+9,(i+9)*10)
                                ,(i+11,(i+11)*10),(i+12,(i+12)*10),(i+13,(i+13)*10),(i+14,(i+14)*10),(i+15,(i+15)*10),(i+16,(i+16)*10),(i+17,(i+17)*10),(i+18,(i+18)*10),(i+19,(i+19)*10)
                                ,(i+21,(i+21)*10),(i+22,(i+22)*10),(i+23,(i+23)*10),(i+24,(i+24)*10),(i+25,(i+25)*10),(i+26,(i+26)*10),(i+27,(i+27)*10),(i+28,(i+28)*10),(i+29,(i+29)*10)
                                ,(i+31,(i+31)*10),(i+32,(i+32)*10),(i+33,(i+33)*10),(i+34,(i+34)*10),(i+35,(i+35)*10),(i+36,(i+36)*10),(i+37,(i+37)*10),(i+38,(i+38)*10),(i+39,(i+39)*10)
                                ,(i+41,(i+41)*10),(i+42,(i+42)*10),(i+43,(i+43)*10),(i+44,(i+44)*10),(i+45,(i+45)*10),(i+46,(i+46)*10),(i+47,(i+47)*10),(i+48,(i+48)*10),(i+49,(i+49)*10)
                                ,(i+51,(i+51)*10),(i+52,(i+52)*10),(i+53,(i+53)*10),(i+54,(i+54)*10),(i+55,(i+55)*10),(i+56,(i+56)*10),(i+57,(i+57)*10),(i+58,(i+58)*10),(i+59,(i+59)*10)
                                ,(i+61,(i+61)*10),(i+62,(i+62)*10),(i+63,(i+63)*10),(i+64,(i+64)*10),(i+65,(i+65)*10),(i+66,(i+66)*10),(i+67,(i+67)*10),(i+68,(i+68)*10),(i+69,(i+69)*10)
                                ,(i+71,(i+71)*10),(i+72,(i+72)*10),(i+73,(i+73)*10),(i+74,(i+74)*10),(i+75,(i+75)*10),(i+76,(i+76)*10),(i+77,(i+77)*10),(i+78,(i+78)*10),(i+79,(i+79)*10)
                                ,(i+81,(i+81)*10),(i+82,(i+82)*10),(i+83,(i+83)*10),(i+84,(i+84)*10),(i+85,(i+85)*10),(i+86,(i+86)*10),(i+87,(i+87)*10),(i+88,(i+88)*10),(i+89,(i+89)*10)
                                ,(i+91,(i+91)*10),(i+92,(i+92)*10),(i+93,(i+93)*10),(i+94,(i+94)*10),(i+95,(i+95)*10),(i+96,(i+96)*10),(i+97,(i+97)*10),(i+98,(i+98)*10),(i+99,(i+99)*10)
                                ;
    set i = i + 100;
    end while;
END;
//
DELIMITER ;

-- テスト用データの作成
call test1createsampledate();

-- テストデータ内容の確認
select count(*) from test1;
select * from test1 limit 0,100;


-- パラメータ=''を条件に使う
DROP PROCEDURE IF EXISTS getk;
delimiter //
CREATE DEFINER=`root`@`localhost` PROCEDURE getk( p_k varchar(10) , p_va varchar(10))
BEGIN
    SELECT * FROM test1
    WHERE k = p_k and ((p_va = '') OR va = p_va);
END;
//
DELIMITER ;

-- パラメータを元にSQLを組み立てる
DROP PROCEDURE IF EXISTS getk2;
delimiter //
CREATE DEFINER=`root`@`localhost` PROCEDURE getk2( p_k varchar(10) , p_va varchar(10))
BEGIN
    declare query varchar(4000);
    set query = CONCAT('SELECT * FROM test1 WHERE k = "' , p_k , '"');
    if (p_va <> '') THEN
        set query = CONCAT(query,' and va = "' , p_va , '"');
    end if;

    select query into @qry;
    PREPARE ddl_stmt from @qry;
    EXECUTE ddl_stmt;
    DEALLOCATE PREPARE ddl_stmt;

END;
//
DELIMITER ;

call getk('1','');
call getk2('1','');


-- 比較にならなそうなのでカラムを増やす
alter table test1 add va2 varchar(10);
alter table test1 add va3 varchar(10);
alter table test1 add va4 varchar(10);
alter table test1 add va5 varchar(10);
alter table test1 add va6 varchar(10);
alter table test1 add va7 varchar(10);
alter table test1 add va8 varchar(10);
alter table test1 add va9 varchar(10);

-- ダミーデータをセット
update test1 set
    va2 = va
   ,va3 = va
   ,va4 = va
   ,va5 = va
   ,va6 = va
   ,va7 = va
   ,va8 = va
   ,va9 = va
;

-- パラメータ=''を条件に使う
DROP PROCEDURE IF EXISTS getk_v2;
delimiter //
CREATE DEFINER=`root`@`localhost` PROCEDURE getk_v2( p_k varchar(10) , p_va varchar(10),p_va2 varchar(10),p_va3 varchar(10),p_va4 varchar(10),p_va5 varchar(10),p_va6 varchar(10),p_va7 varchar(10),p_va8 varchar(10),p_va9 varchar(10))
BEGIN
    SELECT * FROM test1
    WHERE k = p_k
        and ((p_va = '')  OR va  = p_va)
        and ((p_va2 = '') OR va2 = p_va2)
        and ((p_va3 = '') OR va3 = p_va3)
        and ((p_va4 = '') OR va4 = p_va4)
        and ((p_va5 = '') OR va5 = p_va5)
        and ((p_va6 = '') OR va6 = p_va6)
        and ((p_va7 = '') OR va7 = p_va7)
        and ((p_va8 = '') OR va8 = p_va8)
        and ((p_va9 = '') OR va9 = p_va9)
    ;
END;
//
DELIMITER ;

-- パラメータを元にSQLを組み立てる
DROP PROCEDURE IF EXISTS getk2_v2;
delimiter //
CREATE DEFINER=`root`@`localhost` PROCEDURE getk2_v2( p_k varchar(10) , p_va varchar(10),p_va2 varchar(10),p_va3 varchar(10),p_va4 varchar(10),p_va5 varchar(10),p_va6 varchar(10),p_va7 varchar(10),p_va8 varchar(10),p_va9 varchar(10))
BEGIN
    declare query varchar(4000);
    set query = CONCAT('SELECT * FROM test1 WHERE k = "' , p_k , '"');
    if (p_va <> '') THEN
        set query = CONCAT(query,' and va = "' , p_va , '"');
    end if;
    if (p_va2 <> '') THEN
        set query = CONCAT(query,' and va2 = "' , p_va2 , '"');
    end if;
    if (p_va3 <> '') THEN
        set query = CONCAT(query,' and va3 = "' , p_va3 , '"');
    end if;
    if (p_va4 <> '') THEN
        set query = CONCAT(query,' and va4 = "' , p_va4 , '"');
    end if;
    if (p_va5 <> '') THEN
        set query = CONCAT(query,' and va5 = "' , p_va5 , '"');
    end if;
    if (p_va6 <> '') THEN
        set query = CONCAT(query,' and va6 = "' , p_va6 , '"');
    end if;
    if (p_va7 <> '') THEN
        set query = CONCAT(query,' and va7 = "' , p_va7 , '"');
    end if;
    if (p_va8 <> '') THEN
        set query = CONCAT(query,' and va8 = "' , p_va8 , '"');
    end if;
    if (p_va9 <> '') THEN
        set query = CONCAT(query,' and va9 = "' , p_va9 , '"');
    end if;

    select query into @qry;
    PREPARE ddl_stmt from @qry;
    EXECUTE ddl_stmt;
    DEALLOCATE PREPARE ddl_stmt;

END;
//
DELIMITER ;

-- 項目１つ
call getk_v2('1','','','','','','','','','');
call getk2_v2('1','','','','','','','','','');

-- 項目２つ。
call getk_v2('1','','','','','','','','','');
call getk_v2('1','10','','','','','','','','');
call getk_v2('1','','10','','','','','','','');
call getk_v2('1','','','10','','','','','','');
call getk_v2('1','','','','10','','','','','');
call getk_v2('1','','','','','10','','','','');
call getk_v2('1','','','','','','10','','','');
call getk_v2('1','','','','','','','10','','');
call getk_v2('1','','','','','','','','10','');
call getk_v2('1','','','','','','','','','10');

call getk2_v2('1','','','','','','','','','');
call getk2_v2('1','10','','','','','','','','');
call getk2_v2('1','','10','','','','','','','');
call getk2_v2('1','','','10','','','','','','');
call getk2_v2('1','','','','10','','','','','');
call getk2_v2('1','','','','','10','','','','');
call getk2_v2('1','','','','','','10','','','');
call getk2_v2('1','','','','','','','10','','');
call getk2_v2('1','','','','','','','','10','');
call getk2_v2('1','','','','','','','','','10');

-- 結果どのフィールドで検索してもどちらで実装しても処理結果は変わらない

情報技術で日々を楽しく！

2015年10月30日金曜日

MySQLでWhere句を動的に変更する場合の実装・性能について

【考えた代替え案】

【先に結論】

【チューニングとしては】

【検証した内容】

【実コード】