Oracle上でWHERE句内でIN句を利用した場合とEXISTS句を利用した場合の性能を測定してみた

SQLのWHERE句でレコードの存在チェックを行う時などの表現を、IN句でもEXISTS句でも行えるが、EXISTS句は条件に一致するレコードが1件でもあればそこで処理を終了するのに比べて、IN句は条件に当てはまるデータを全て抽出するため、EXISTS句を利用した方が処理が速くなることが多い。

今回は、WHERE句内でIN句を利用した場合とEXISTS句を利用した場合それぞれで、SQLの実行速度を測定するプログラムを作成してみたので、共有する。

なお、SQL文の性能改善を行う方法については、以下のサイトを参照のこと。
https://sites.google.com/site/orapeform/sql_minaoshi

前提条件

下記記事の実装が完了していること。

Oracle上でselect文で「*」を利用した場合とカラム名を利用した場合の性能を測定してみたSQL SELECT文を記載する際、「SELECT *」よりは「SELECT (カラム名)」を、「SELECT COUNT(*)」よりは...

サンプルプログラムの作成

作成したサンプルプログラムの構成は、以下の通り。

なお、上記の赤枠は、前提条件のプログラムから変更したプログラムである。

Mapperインタフェース・Mapper XMLの内容は以下の通りで、IN句を利用した場合・EXISTS句を利用した場合それぞれで、生年月日が基準日と一致するデータを取得するSQLを用意している。

package com.example.demo;

import org.apache.ibatis.annotations.Mapper;

import java.util.Date;
import java.util.List;

@Mapper
public interface UserDataMapper {

    /**
     * 指定したIDをもつユーザーデータテーブル(user_data)のデータを取得する
     * @param id ID
     * @return ユーザーデータテーブル(user_data)の指定したIDのデータ
     */
    UserData findById(Long id);

    /**
     * IN句を使って、ユーザーデータテーブル(user_data)の生年月日が基準日と
     * 一致するデータを全件取得する
     * @param basicDate 基準日
     * @return ユーザーデータテーブル(user_data)のリスト
     */
    List<UserData> findByBirthdayIn(Date basicDate);

    /**
     * EXISTS句を使って、ユーザーデータテーブル(user_data)の生年月日が基準日と
     * 一致するデータを全件取得する
     * @param basicDate 基準日
     * @return ユーザーデータテーブル(user_data)のリスト
     */
    List<UserData> findByBirthdayExists(Date basicDate);

}

package com.example.demo;

import org.apache.ibatis.annotations.Mapper;

import java.util.Date;

import java.util.List;

@Mapper

public interface UserDataMapper {

/**

* 指定したIDをもつユーザーデータテーブル(user_data)のデータを取得する

* @param id ID

* @return ユーザーデータテーブル(user_data)の指定したIDのデータ

UserData findById(Long id);

/**

* IN句を使って、ユーザーデータテーブル(user_data)の生年月日が基準日と

* 一致するデータを全件取得する

* @param basicDate 基準日

* @return ユーザーデータテーブル(user_data)のリスト

List<UserData> findByBirthdayIn(Date basicDate);

/**

* EXISTS句を使って、ユーザーデータテーブル(user_data)の生年月日が基準日と

* 一致するデータを全件取得する

* @param basicDate 基準日

* @return ユーザーデータテーブル(user_data)のリスト

List<UserData> findByBirthdayExists(Date basicDate);

}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.UserDataMapper">
    <resultMap id="userDataResultMap" type="com.example.demo.UserData" >
        <id column="id" property="id" jdbcType="BIGINT" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <result column="birth_year" property="birthY" jdbcType="VARCHAR" />
        <result column="birth_month" property="birthM" jdbcType="VARCHAR" />
        <result column="birth_day" property="birthD" jdbcType="VARCHAR" />
        <result column="sex" property="sex" jdbcType="VARCHAR" />
        <result column="memo" property="memo" jdbcType="VARCHAR" />
    </resultMap>
    <select id="findById" parameterType="java.lang.Long" resultMap="userDataResultMap">
        SELECT id, name, birth_year, birth_month, birth_day, sex, memo 
        FROM USER_DATA WHERE id = #{id}
    </select>
    <select id="findByBirthdayIn" parameterType="java.util.Date" 
            resultMap="userDataResultMap">
        SELECT u1.id, u1.name, u1.birth_year, u1.birth_month, u1.birth_day
            , u1.sex, u1.memo
        FROM USER_DATA u1
        WHERE u1.id IN (
            SELECT u2.id FROM USER_DATA u2
            WHERE TO_DATE(TO_CHAR(u2.birth_year)
                || LPAD(TO_CHAR(u2.birth_month), 2, '0')
                || LPAD(TO_CHAR(u2.birth_day), 2, '0'), 'yyyyMMdd') = #{date}
        )
    </select>
    <select id="findByBirthdayExists" parameterType="java.util.Date" 
            resultMap="userDataResultMap">
        SELECT u1.id, u1.name, u1.birth_year, u1.birth_month, u1.birth_day
            , u1.sex, u1.memo
        FROM USER_DATA u1
        WHERE EXISTS (
            SELECT u2.id FROM USER_DATA u2
            WHERE u2.id = u1.id
            AND TO_DATE(TO_CHAR(u2.birth_year)
                || LPAD(TO_CHAR(u2.birth_month), 2, '0')
                || LPAD(TO_CHAR(u2.birth_day), 2, '0'), 'yyyyMMdd') = #{date}
        )
    </select>
</mapper>

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mapper

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

</resultMap>

SELECT id, name, birth_year, birth_month, birth_day, sex, memo

FROM USER_DATA WHERE id = #{id}

</select>

<select id="findByBirthdayIn" parameterType="java.util.Date"

resultMap="userDataResultMap">

SELECT u1.id, u1.name, u1.birth_year, u1.birth_month, u1.birth_day

, u1.sex, u1.memo

FROM USER_DATA u1

WHERE u1.id IN (

SELECT u2.id FROM USER_DATA u2

WHERE TO_DATE(TO_CHAR(u2.birth_year)

|| LPAD(TO_CHAR(u2.birth_month), 2, '0')

|| LPAD(TO_CHAR(u2.birth_day), 2, '0'), 'yyyyMMdd') = #{date}

)

</select>

<select id="findByBirthdayExists" parameterType="java.util.Date"

resultMap="userDataResultMap">

SELECT u1.id, u1.name, u1.birth_year, u1.birth_month, u1.birth_day

, u1.sex, u1.memo

FROM USER_DATA u1

WHERE EXISTS (

SELECT u2.id FROM USER_DATA u2

WHERE u2.id = u1.id

AND TO_DATE(TO_CHAR(u2.birth_year)

|| LPAD(TO_CHAR(u2.birth_month), 2, '0')

|| LPAD(TO_CHAR(u2.birth_day), 2, '0'), 'yyyyMMdd') = #{date}

)

</select>

</mapper>

また、サービスクラスのサブクラスの内容は以下の通りで、それぞれのSQLを呼び出し、それぞれの実行時間を表示するようにしている。

package com.example.demo;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.Calendar;
import java.util.List;

@Service
public class DemoServiceSub {

    /**
     * ユーザーデータテーブル(user_data)へアクセスするマッパー
     */
    @Autowired
    private UserDataMapper userDataMapper;

    /**
     * 性能検証を行うための個別サービス
     */
    public void verifyPerformanceEach() {
        // 初回DB接続時は時間がかかるため、あえて性能測定対象外のSQLを1度実行しておく
        userDataMapper.findById(Long.valueOf(1));

        // 検索時に利用する基準日(2012/1/20)を生成する
        Calendar basicDate = Calendar.getInstance();
        basicDate.set(2012, 0, 20, 0, 0, 0);
        basicDate.set(Calendar.MILLISECOND, 0);

        System.out.println("--- IN句を使って、ユーザーデータテーブル(user_data)の生年月日"
                + "が基準日と一致するデータを全件取得するSQLを実行します start. ---");

        // 処理前の現在時刻を取得
        long startTime = System.currentTimeMillis();

        // IN句を使って、ユーザーデータテーブル(user_data)の生年月日が基準日と
        // 一致するデータを全件取得する
        List<UserData> userDataList 
            = userDataMapper.findByBirthdayIn(basicDate.getTime());

        // 処理後の現在時刻を取得
        long endTime = System.currentTimeMillis();

        System.out.println("取得件数 : " + userDataList.size());
        System.out.println("処理時間：" + (endTime - startTime) + " ms");
        System.out.println("--- IN句を使って、ユーザーデータテーブル(user_data)の生年月日"
                + "が基準日と一致するデータを全件取得するSQLを実行します end. ---");
        System.out.println();

        System.out.println("--- EXISTS句を使って、ユーザーデータテーブル(user_data)の生年月日"
                + "が基準日と一致するデータを全件取得するSQLを実行します start. ---");

        // 処理前の現在時刻を取得
        startTime = System.currentTimeMillis();

        // EXISTS句を使って、ユーザーデータテーブル(user_data)の生年月日が基準日と
        // 一致するデータを全件取得する
        userDataList = userDataMapper.findByBirthdayExists(basicDate.getTime());

        // 処理後の現在時刻を取得
        endTime = System.currentTimeMillis();

        System.out.println("取得件数 : " + userDataList.size());
        System.out.println("処理時間：" + (endTime - startTime) + " ms");
        System.out.println("--- EXISTS句を使って、ユーザーデータテーブル(user_data)の生年月日"
                + "が基準日と一致するデータを全件取得するSQLを実行します end. ---");
        System.out.println();
    }
}

package com.example.demo;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Service;

import java.util.Calendar;

import java.util.List;

@Service

public class DemoServiceSub {

/**

* ユーザーデータテーブル(user_data)へアクセスするマッパー

@Autowired

private UserDataMapper userDataMapper;

/**

* 性能検証を行うための個別サービス

public void verifyPerformanceEach() {

// 初回DB接続時は時間がかかるため、あえて性能測定対象外のSQLを1度実行しておく

userDataMapper.findById(Long.valueOf(1));

// 検索時に利用する基準日(2012/1/20)を生成する

Calendar basicDate = Calendar.getInstance();

basicDate.set(2012, 0, 20, 0, 0, 0);

basicDate.set(Calendar.MILLISECOND, 0);

System.out.println("--- IN句を使って、ユーザーデータテーブル(user_data)の生年月日"

+ "が基準日と一致するデータを全件取得するSQLを実行します start. ---");

// 処理前の現在時刻を取得

long startTime = System.currentTimeMillis();

// IN句を使って、ユーザーデータテーブル(user_data)の生年月日が基準日と

// 一致するデータを全件取得する

List<UserData> userDataList

= userDataMapper.findByBirthdayIn(basicDate.getTime());

// 処理後の現在時刻を取得

long endTime = System.currentTimeMillis();

System.out.println("取得件数 : " + userDataList.size());

System.out.println("処理時間：" + (endTime - startTime) + " ms");

System.out.println("--- IN句を使って、ユーザーデータテーブル(user_data)の生年月日"

+ "が基準日と一致するデータを全件取得するSQLを実行します end. ---");

System.out.println();

System.out.println("--- EXISTS句を使って、ユーザーデータテーブル(user_data)の生年月日"

+ "が基準日と一致するデータを全件取得するSQLを実行します start. ---");

// 処理前の現在時刻を取得

startTime = System.currentTimeMillis();

// EXISTS句を使って、ユーザーデータテーブル(user_data)の生年月日が基準日と

// 一致するデータを全件取得する

userDataList = userDataMapper.findByBirthdayExists(basicDate.getTime());

// 処理後の現在時刻を取得

endTime = System.currentTimeMillis();

System.out.println("取得件数 : " + userDataList.size());

System.out.println("処理時間：" + (endTime - startTime) + " ms");

System.out.println("--- EXISTS句を使って、ユーザーデータテーブル(user_data)の生年月日"

+ "が基準日と一致するデータを全件取得するSQLを実行します end. ---");

System.out.println();

}

その他のソースコード内容は、以下のサイトを参照のこと。
https://github.com/purin-it/java/tree/master/spring-boot-oracle-performance-in-exists/demo

ウズウズカレッジJavaコースはわかりやすい動画教材と充実した就業サポートで優良企業を目指せるプログラミングスクールだったJavaは、世界中で広く使われていて、現在の需要が高く将来性もある開発言語になります。 https://www.acrovision....

サンプルプログラムの実行結果

サンプルプログラムの実行結果は以下の通りで、IN句よりEXISTS句を利用した方が、SQLが速くなることが確認できる。

1) 以下を実行し、USER_DATAテーブルに、生年月日が2012/1/20のデータ50,000件と、生年月日が2012/1/21のデータ50,000件のデータを、それぞれ追加する。

begin
   execute immediate 'TRUNCATE TABLE user_data';
   for i in 1..25000 loop
      INSERT INTO user_data VALUES (i, 'テスト　プリン' || TO_MULTI_BYTE(i)
          , 2012, 1, 20, TO_CHAR(MOD(i, 2) + 1), 'テスト');
   end loop;
   for i in 25001..50000 loop
      INSERT INTO user_data VALUES (i, 'テスト　プリン' || TO_MULTI_BYTE(i)
          , 2012, 1, 21, TO_CHAR(MOD(i, 2) + 1), 'テスト');
   end loop;
   for i in 50001..75000 loop
      INSERT INTO user_data VALUES (i, 'テスト　プリン' || TO_MULTI_BYTE(i)
          , 2012, 1, 20, TO_CHAR(MOD(i, 2) + 1), 'テスト');
   end loop;
   for i in 75001..100000 loop
      INSERT INTO user_data VALUES (i, 'テスト　プリン' || TO_MULTI_BYTE(i)
          , 2012, 1, 21, TO_CHAR(MOD(i, 2) + 1), 'テスト');
   end loop;
   COMMIT;
end;
/

begin

execute immediate 'TRUNCATE TABLE user_data';

for i in 1..25000 loop

INSERT INTO user_data VALUES (i, 'テスト　プリン' || TO_MULTI_BYTE(i)

, 2012, 1, 20, TO_CHAR(MOD(i, 2) + 1), 'テスト');

end loop;

for i in 25001..50000 loop

INSERT INTO user_data VALUES (i, 'テスト　プリン' || TO_MULTI_BYTE(i)

, 2012, 1, 21, TO_CHAR(MOD(i, 2) + 1), 'テスト');

end loop;

for i in 50001..75000 loop

INSERT INTO user_data VALUES (i, 'テスト　プリン' || TO_MULTI_BYTE(i)

, 2012, 1, 20, TO_CHAR(MOD(i, 2) + 1), 'テスト');

end loop;

for i in 75001..100000 loop

INSERT INTO user_data VALUES (i, 'テスト　プリン' || TO_MULTI_BYTE(i)

, 2012, 1, 21, TO_CHAR(MOD(i, 2) + 1), 'テスト');

end loop;

COMMIT;

end;

実行後、以下のように、レコード数が100,000件で、そのうち、生年月日が2012/1/20であるデータが50,000件になっていることが確認できる。

select count(*) from user_data
where to_date(TO_CHAR(birth_year)
                || LPAD(TO_CHAR(birth_month), 2, '0')
                || LPAD(TO_CHAR(birth_day), 2, '0'), 'yyyyMMdd') = '2012/01/20'

select count(*) from user_data

where to_date(TO_CHAR(birth_year)

|| LPAD(TO_CHAR(birth_month), 2, '0')