サマリ

BINARYキーワードを使用して大文字と小文字を区別し、インデックスを作成する方法

コラムをVARBINARYに変換し、インデックスを設定します.推奨

2WHERE LOGIN_ID = BINARY 'value'検索値の選択-緊急

1.大文字と小文字の区別

デフォルトでは、MySQLのvarcharデータ型は大文字と小文字を区別しません.

SELECT *
FROM USER
WHERE LOGIN_ID = 'aaaa';

2. BINARY

1)定義

BINARYは、文字列をバイナリとして位置づけ、バイトごとに比較します.

2）ホットスポット

カラムをBINARYに配置すると、既存のインデックスは有効になりません.

WHERE BINARY LOGIN_ID = 'aaaa';

良い方法は、値をバイナリに鋳造することです.

WHERE LOGIN_ID = BINARY 'aaaa';

最も良い方法は、コラムをVARBINARYに変更し、インデックスを設定することです.
テストで理解してみましょう.

3.テスト

1）テストケース

長さ4の大文字と小文字の区切り文字列が作成されました.

生成されたコードは次のとおりです.
52^4は約700万回生成する必要がありますが、10万回入れても1時間以上かかり、10万回テストします.

const lower = Array.from(Array(26).keys()).map(x => String.fromCharCode('a'.charCodeAt(0) + x)).join("")
const upper = lower.toUpperCase()

const alphabet = lower + upper

const loginIdList = []

const makeLoginId = (val, cnt) => {
    if(cnt > 3) {
        loginIdList.push(val)
        return
    }

    for(let i=0; i<52; i++) {
        makeLoginId(val + alphabet.charAt(i), cnt + 1)
    }
}

makeLoginId('', 0)

console.log(loginIdList)

2）コラムをバイナリにする

クエリコスト10644
実行ルールは「index」と表示されますが、実際にはFULL INDEX SCANです.すべてのLowを表示します.

EXPLAIN
SELECT ID, LOGIN_ID
FROM USER
WHERE BINARY LOGIN_ID = 'aaaa';

3）値をバイナリに鋳造する

クエリコスト2.83
「aaaa」~「AAAA」を見つけ、そこで値を比較します.

EXPLAIN
SELECT ID, LOGIN_ID
FROM USER
WHERE LOGIN_ID = BINARY 'aaaa';

4）コラムをVARBINARYに変更し、インデックスを設定

クエリコスト0.45

5）比較

クエリコストの差は9300:2:0.45です.
これは現在10万個のテストデータで行われているデータです.実際に多くのデータがインデックスされている場合は、インデックスがない場合は10秒スキップされます.

4.整理

実際の状況では、多くの原因があり、原因があります.
大文字と小文字を区別するコラムは間違っているが、これはすでに発生している.
BINARYのせいで止めないでください.
コラムのタイプを変更し、インデックスを作成します.
デザインも変えられるといいですね.