SQL INDEX のあれこれ

# はしがき

ORM を使いながら、いくつかのテーブルを sql 句で join したことがありました。

join の条件(`ON`)に CASE を使えば、インデックスはしないって、インデックスがなんのか？探して整理します。

# インデックス(index)とは？

インデックスは検索のスピードの向上のために使う仕組みです。

テーブルのカラムをインデックス化して、該当のテーブルのレコードを full scan ではなく、インデックスを利用して、高いスピードの検索を行います。

![unnamed.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3582489/cf83f36c-fe8f-1a8c-cdff-c0a507a9130e.gif)
url: https://knowtechstuffz.blogspot.com/2015/04/how-sql-indexes-work-internally.html

# インデックスの使い方

### 生成方法

```SQL
CREATE [UNIQUE] INDEX インデックスネーム ON テーブル (カラム) [ASC | DESC]
```

UNIQUE オプションは重複しない UNIQUE なインデックスをつくります。

### 削除

```SQL
DROP INDEX インデックスネーム ON テーブル
```

PRIMARY KEY、また、UNIQUE KEY から自動で作られたインデックスは DROP INDEX で削除することができません。

もし、ALTER TABLE で、PRIMARY KEY、UNIQUE KEY を削除すれば、自動で作られたインデックスも削除できます。

### テスト

テーブルを作ります。

```sql
CREATE TABLE hello_world (
    id mediumint(9) NOT NULL auto_increment,
    hell varchar(10) default NULL,
    world tinyint(3) unsigned default NULL,
    PRIMARY KEY (id)
);
```

インデックスを確認しましょう。

```sql
SHOW INDEX FROM hello_world;
```

![スクリーンショット 2024-04-21 11.45.17.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3582489/5e794c48-d2f1-957b-e582-f7530905c1f2.png)

やはり、一つしかありませんね。

インデックスを作って、確認します。

```sql
CREATE INDEX index_hello_world ON hello_world (hell);

SHOW INDEX FROM hello_world;
```

![スクリーンショット 2024-04-21 11.47.05.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3582489/a4635343-0f15-8a7a-37e6-a7157f293dbe.png)

index_hello_world という index が作られました！

また、インデックスがどうやって実行されるかのために、EXPLAIN を使いましょう。

> In SQL, the EXPLAIN keyword provides a description of how the SQL queries are executed by the databases. These descriptions include the optimizer logs, how tables are joined and in what order, etc.

https://www.geeksforgeeks.org/explain-in-sql/

SQL には EXPLAIN を使って、SQL 句が実行される方法の説明を受けることができます。

```sql
EXPLAIN SELECT * FROM hello_world WHERE id > 4;
```
![スクリーンショット 2024-04-21 11.58.01.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3582489/3f8ca401-8f17-056a-5572-348fe45c6194.png)
---
```sql
EXPLAIN SELECT * FROM hello_world WHERE hell LIKE ('h%');
```
![スクリーンショット 2024-04-21 11.57.04.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3582489/b0ac73e0-6fcc-9aa4-73e7-88a67bcb35db.png)

key を確認すると PRIMARY KEY と自分が設定した、index_hello_world がインデックスとして使われました！

## では、使われない時もある？

そうですね。このインデックスを使わない場合もあります。

### 関数や calculate する時

```sql
EXPLAIN SELECT * FROM hello_world WHERE UPPER(hell) = 'HELL';

EXPLAIN SELECT * FROM hello_world WHERE id + 1 > 4;
```

![スクリーンショット 2024-04-21 12.09.07.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3582489/a7527a08-d404-3fd3-0241-8e78549a6176.png)

インデックスの場合データが sort されています。
この sort されたデータが、関数や calculate を通じて、データが変わりますので、sort されない可能性があります。
インデックスは sort が前提なので、インデックスを使いません。

### LIKE 句の wildcard の位置

さっき検索したことを再利用します。

```sql
EXPLAIN SELECT * FROM hello_world WHERE hell LIKE ('%h');
```

![スクリーンショット 2024-04-21 12.09.07.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3582489/a7527a08-d404-3fd3-0241-8e78549a6176.png)

wildcard が後ろにある時は、インデックスを使えますが、前にある時は、使いません。
これは `すべての文字列` + `検索内容`なので、整列とは別にインデックスを使いません。

### OR を使うとき

```sql
EXPLAIN SELECT * FROM hello_world WHERE id > 2 OR hell LIKE ('h%');
```
![スクリーンショット 2024-04-21 12.11.54.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3582489/3b248747-ec26-1240-35c4-528ba4a99756.png)

OR を使うと、二つ以上の field を同時に比較するので、使える key が二つありましたが、実際に使う key はありません。

### 別のカラムを WHERE から比較するとき

```sql
EXPLAIN SELECT * FROM hello_world WHERE id = world;
```

![スクリーンショット 2024-04-21 12.40.13.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3582489/f5c80faf-899c-a879-525b-689932e50709.png)

インデックスを使う時は、インデックス scan する前に、全ても値を type conversion しますので、カラムの data type が違うことを比較するクエリを実行するとき、インデックスの performance が低下してしまう可能性があります。

### IN を使いすぎるとき

```sql
EXPLAIN SELECT * FROM hello_world WHERE id IN (1, 2);
```

![スクリーンショット 2024-04-21 12.46.31.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3582489/1df64d75-25bb-6b6e-b3f3-8e89a90609e5.png)

このように少ない場合は問題なく実行します。が。。。

```sql
EXPLAIN SELECT * FROM hello_world WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164, 165, 166, 167, 168, 169, 170, 171, 172, 173, 174, 175, 176, 177, 178, 179, 180, 181, 182, 183, 184, 185, 186, 187, 188, 189, 190, 191, 192, 193, 194, 195, 196, 197, 198, 199, 200, 201, 202, 203, 204, 205, 206, 207, 208, 209, 210, 211, 212, 213, 214, 215, 216, 217, 218, 219, 220, 221, 222, 223, 224, 225, 226, 227, 228, 229, 230, 231, 232, 233, 234, 235, 236, 237, 238, 239, 240, 241, 242, 243, 244, 245, 246, 247, 248, 249, 250, 251, 252, 253, 254, 255, 256, 257, 258, 259, 260, 261, 262, 263, 264, 265, 266, 267, 268, 269, 270, 271, 272, 273, 274, 275, 276, 277, 278, 279, 280, 281, 282, 283, 284, 285, 286, 287, 288, 289, 290, 291, 292, 293, 294, 295, 296, 297, 298, 299, 300, 301, 302, 303, 304, 305, 306, 307, 308, 309, 310, 311, 312, 313, 314, 315, 316, 317, 318, 319, 320, 321, 322, 323, 324, 325, 326, 327, 328, 329, 330, 331, 332, 333, 334, 335, 336, 337, 338, 339, 340, 341, 342, 343, 344, 345, 346, 347, 348, 349, 350, 351, 352, 353, 354, 355, 356, 357, 358, 359, 360, 361, 362, 363, 364, 365, 366, 367, 368, 369, 370, 371, 372, 373, 374, 375, 376, 377, 378, 379, 380, 381, 382, 383, 384, 385, 386, 387, 388, 389, 390, 391, 392, 393, 394, 395, 396, 397, 398, 399, 400, 401, 402, 403, 404, 405, 406, 407, 408, 409, 410, 411, 412, 413, 414, 415, 416, 417, 418, 419, 420, 421, 422, 423, 424, 425, 426, 427, 428, 429, 430, 431, 432, 433, 434, 435, 436, 437, 438, 439, 440, 441, 442, 443, 444, 445, 446, 447, 448, 449, 450, 451, 452, 453, 454, 455, 456, 457, 458, 459, 460, 461, 462, 463, 464, 465, 466, 467, 468, 469, 470, 471, 472, 473, 474, 475, 476, 477, 478, 479, 480, 481, 482, 483, 484, 485, 486, 487, 488, 489, 490, 491, 492, 493, 494, 495, 496, 497, 498, 499, 500);
```

![スクリーンショット 2024-04-21 12.47.51.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3582489/7dd70a3e-2a2a-87ca-6ae7-ae3bbf33affd.png)

このように使いすぎると、使わなくなります。

### JOIN に CASE を使うとき

まず、hello_world の id を reference しているテーブルを作ります。

```sql
CREATE TABLE another_world (
    id mediumint(9) NOT NULL auto_increment,
    hello_world_id mediumint(9) NOT NULL,
    tenkoku varchar(10) default NULL,
    kita tinyint(3) unsigned default NULL,
    PRIMARY KEY (id),
    FOREIGN KEY(hello_world_id) REFERENCES hello_world (id)
);

INSERT INTO another_world (hello_world_id, tenkoku, kita) VALUES (1, 'uu', 52), (5, 'not', 32), (1, 'worl', 11);

SELECT * FROM another_world;
```

![スクリーンショット 2024-04-21 12.55.02.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3582489/68e5afe1-4f80-a7fe-d3e3-bfdfdd4cebd2.png)

インデックスも確認します。

```sql
SHOW INDEX FROM another_world;
```

![スクリーンショット 2024-04-21 13.24.02.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3582489/205ec0d8-c7d1-e06b-4796-728967f0f7fd.png)

FOREIGN KEY もインデックスされましたね。

また、LEFT JOIN と LEFT JOIN の ON に CASE WHEN を使って実行します。

```sql
EXPLAIN SELECT * FROM hello_world as hw
LEFT JOIN another_world as aw
ON hw.id = aw.hello_world_id;
```

![スクリーンショット 2024-04-21 13.09.35.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3582489/a3bb6b96-e953-a812-e388-f1d708155e5f.png)

```sql
EXPLAIN SELECT * FROM hello_world as hw
LEFT JOIN another_world as aw
ON  hw.id = CASE WHEN aw.hello_world_id = 1 THEN 225 ELSE aw.hello_world_id END;
```

![スクリーンショット 2024-04-21 13.34.48.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3582489/ea323f77-c8e1-b688-3eeb-43ea9847a6a5.png)

なんとか、another_world から設定されていた index がなくなりました。

いくつかの理由がありますが、自分が考える理由は CASE の条件によって、結果が不明です。
今回の場合は、hello_world_id をインデックスしていているので、インデックスは以下のようにセーブされている可能性があります。

another_world の hello_world_id の index

|another_world の hello_world_id の値|location|
|-|-|
|1|another_world の location|
|4|another_world の location|
|5|another_world の location|

ですが、`CASE WHEN aw.hello_world_id = 1 THEN 225 ELSE aw.hello_world_id END` を使って、hello_world_id が1の場合1をインデックスするか225をインデックスするかのような不明点があるので、インデックスしないと思います。

以外は、条件が複雑になったので、DB なりの最適化ができないので、インデックスしないと予想できます。

## 結果になんか using where や using index condition が表示しましけど、これの違さは？

using where: 単純に WHERE・ON がついているテーブルから、データを取得して、return します。

using index condition: 条件にインデックされていない row が含まれています。
一先、インデックされた row を確認して、テーブルから他の条件の row を探します。

# 参考したページ

https://knowtechstuffz.blogspot.com/2015/04/how-sql-indexes-work-internally.html

https://products.sint.co.jp/siob/blog/index

https://style.potepan.com/articles/25208.html

https://qiita.com/hmatsu47/items/97f10cd07c5db47c0988

https://stackoverflow.com/questions/25672552/whats-the-difference-between-using-index-and-using-where-using-index-in-the

https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-extra-information

https://dba.stackexchange.com/questions/260682/what-should-i-index-when-joining-on-a-table-with-conditions

https://webmonkeyuk.wordpress.com/2010/09/27/what-makes-a-good-mysql-index-part-2-cardinality/