PolarDB-Xにindexを追加してみて効果を確認

## 前提：
- Alibaba Cloudのコンソール画面上ですでにPolarDB-Xのインスタンスが作られている
- インターネットからアクセスできるアドレスがすでに作られている
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/28664/d3bc2136-c12f-d820-5b5a-1d0f86c07237.png)

## 準備
- databaseの作成
```
mysql> CREATE DATABASE partitioning_test MODE='AUTO';
```
- tableの作成
```
mysql> CREATE TABLE ptest1 (
  id INTEGER UNSIGNED NOT NULL,
  k INTEGER UNSIGNED DEFAULT '0' NOT NULL,
  c CHAR(20) DEFAULT '' NOT NULL,
  pad CHAR(60) DEFAULT '' NOT NULL,
  PRIMARY KEY(id)
);
```
- table構造の確認
    - primay keyの`id`はpartition keyとして利用されて、自動的に16個のpartitionが作られた。
```
mysql> show full create table ptest1;
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE  | CREATE TABLE                                                                                                                                                                                                                                                                                                         |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ptest1 | CREATE PARTITION TABLE `ptest1` (
	`id` int(10) UNSIGNED NOT NULL,
	`k` int(10) UNSIGNED NOT NULL DEFAULT '0',
	`c` char(20) NOT NULL DEFAULT '',
	`pad` char(60) NOT NULL DEFAULT '',
	PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`id`)
PARTITIONS 16
/* tablegroup = `tg7` */ |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
```
- tableのtopologyの確認
    - ptest1は自動的に16個の物理tableに分割された。
```
mysql> show topology from ptest1;
+----+--------------------------------+-------------------+----------------+--------------------------+---------------------------------+
| ID | GROUP_NAME                     | TABLE_NAME        | PARTITION_NAME | PHY_DB_NAME              | DN_ID                           |
+----+--------------------------------+-------------------+----------------+--------------------------+---------------------------------+
|  0 | PARTITIONING_TEST_P00000_GROUP | ptest1_lUCK_00000 | p1             | partitioning_test_p00000 | pxc-xdb-s-pxcsprz41t3a5khov5f04 |
|  1 | PARTITIONING_TEST_P00000_GROUP | ptest1_lUCK_00002 | p3             | partitioning_test_p00000 | pxc-xdb-s-pxcsprz41t3a5khov5f04 |
|  2 | PARTITIONING_TEST_P00000_GROUP | ptest1_lUCK_00004 | p5             | partitioning_test_p00000 | pxc-xdb-s-pxcsprz41t3a5khov5f04 |
|  3 | PARTITIONING_TEST_P00000_GROUP | ptest1_lUCK_00006 | p7             | partitioning_test_p00000 | pxc-xdb-s-pxcsprz41t3a5khov5f04 |
|  4 | PARTITIONING_TEST_P00000_GROUP | ptest1_lUCK_00008 | p9             | partitioning_test_p00000 | pxc-xdb-s-pxcsprz41t3a5khov5f04 |
|  5 | PARTITIONING_TEST_P00000_GROUP | ptest1_lUCK_00010 | p11            | partitioning_test_p00000 | pxc-xdb-s-pxcsprz41t3a5khov5f04 |
|  6 | PARTITIONING_TEST_P00000_GROUP | ptest1_lUCK_00012 | p13            | partitioning_test_p00000 | pxc-xdb-s-pxcsprz41t3a5khov5f04 |
|  7 | PARTITIONING_TEST_P00000_GROUP | ptest1_lUCK_00014 | p15            | partitioning_test_p00000 | pxc-xdb-s-pxcsprz41t3a5khov5f04 |
|  8 | PARTITIONING_TEST_P00001_GROUP | ptest1_lUCK_00001 | p2             | partitioning_test_p00001 | pxc-xdb-s-pxcsprz41t3a5khov8886 |
|  9 | PARTITIONING_TEST_P00001_GROUP | ptest1_lUCK_00003 | p4             | partitioning_test_p00001 | pxc-xdb-s-pxcsprz41t3a5khov8886 |
| 10 | PARTITIONING_TEST_P00001_GROUP | ptest1_lUCK_00005 | p6             | partitioning_test_p00001 | pxc-xdb-s-pxcsprz41t3a5khov8886 |
| 11 | PARTITIONING_TEST_P00001_GROUP | ptest1_lUCK_00007 | p8             | partitioning_test_p00001 | pxc-xdb-s-pxcsprz41t3a5khov8886 |
| 12 | PARTITIONING_TEST_P00001_GROUP | ptest1_lUCK_00009 | p10            | partitioning_test_p00001 | pxc-xdb-s-pxcsprz41t3a5khov8886 |
| 13 | PARTITIONING_TEST_P00001_GROUP | ptest1_lUCK_00011 | p12            | partitioning_test_p00001 | pxc-xdb-s-pxcsprz41t3a5khov8886 |
| 14 | PARTITIONING_TEST_P00001_GROUP | ptest1_lUCK_00013 | p14            | partitioning_test_p00001 | pxc-xdb-s-pxcsprz41t3a5khov8886 |
| 15 | PARTITIONING_TEST_P00001_GROUP | ptest1_lUCK_00015 | p16            | partitioning_test_p00001 | pxc-xdb-s-pxcsprz41t3a5khov8886 |
+----+--------------------------------+-------------------+----------------+--------------------------+---------------------------------+
```

- レコードをinsertする
```
mysql> INSERT INTO ptest1(id, k, c, pad) VALUES
    -> (1, 1, "c", "pad"),(2, 2, "c", "pad"),(3, 3, "c", "pad"),
    -> (4, 4, "c", "pad"),(5, 5, "c", "pad"),(6, 6, "c", "pad");

mysql> select * from ptest1;
+------+------+------+------+
| id   | k    | c    | pad  |
+------+------+------+------+
|    3 |    3 | c    | pad  |
|    1 |    1 | c    | pad  |
|    4 |    4 | c    | pad  |
|    2 |    2 | c    | pad  |
|    5 |    5 | c    | pad  |
|    6 |    6 | c    | pad  |
+------+------+------+------+
```


## クエリの実行計画を確認
polardb-xには以下の２つの実行計画確認方法があり、詳細な使い方はpolardb-xのdoumentをご参考ください。

https://www.alibabacloud.com/help/en/polardb-x/latest/explain

1. `EXPLAIN`: polardb-xのcompute node上でSQL実行計画に関する基本的な情報を表示(分散型データベースにしかなくて、MySQLにはない情報)
2. `EXPLAIN EXECUTE`: data node上でindexが使われているかどうか、tabll full scanされているかどうかが確認できる。MySQLの`EXPLAIN `と同じ結果が表示される


### primary keyを持っているカラムに対して検索
以下のクエリはpimary keyを使って検索するため、一発目で`ptest1[p14]`、つまり`p14 `のpartitionが特定できた。
```
mysql> EXPLAIN SELECT pad FROM ptest1 WHERE id = 1;
+--------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                            |
+--------------------------------------------------------------------------------------------------+
| LogicalView(tables="ptest1[p14]", sql="SELECT `pad` FROM `ptest1` AS `ptest1` WHERE (`id` = ?)") |
| HitCache:false                                                                                   |
| Source:PLAN_CACHE                                                                                |
| TemplateId: 56877fe9                                                                             |
+--------------------------------------------------------------------------------------------------+
```
- `type`は`const`であり、`key`にはPrimary keyが利用されていることが分かる。 MySQLの実行計画の見方は以下をご参考ください。

https://zenn.dev/hk_206/articles/ec5f4e347caff4

```
mysql> EXPLAIN EXECUTE SELECT pad FROM ptest1 WHERE id = 1;
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra           |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| 1  | SIMPLE      | ptest1 | NULL       | const | PRIMARY       | PRIMARY | 4       | const | 1    | 100      | Using pk access |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
```


### primary keyではないカラムに対して検索
- クエリ`k`というカラムにはインデックスがないため、`ptest1[p1,p2,p3,...p16]`、つまりすべてのpartitionに対して検索をすることが分かる

```
mysql> EXPLAIN SELECT pad FROM ptest1 WHERE k = 1;
+------------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                                        |
+------------------------------------------------------------------------------------------------------------------------------+
| Gather(concurrent=true)                                                                                                      |
|   LogicalView(tables="ptest1[p1,p2,p3,...p16]", shardCount=16, sql="SELECT `pad` FROM `ptest1` AS `ptest1` WHERE (`k` = ?)") |
| HitCache:false                                                                                                               |
| Source:PLAN_CACHE                                                                                                            |
| TemplateId: 2c129255                                                                                                         |
+------------------------------------------------------------------------------------------------------------------------------+
```
- `type`: `all`, `key`:NULLであり、つまりすでののpartitionにして全件スキャンしていることが分かる。
```
mysql> EXPLAIN EXECUTE SELECT pad FROM ptest1 WHERE k = 1;
+----+-------------+--------+------------+------+---------------+-----+---------+-----+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+-----+---------+-----+------+----------+-------------+
| 1  | PRIMARY     | ptest1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1    | 100      | Using where |
| 2  | UNION       | ptest1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1    | 100      | Using where |
| 3  | UNION       | ptest1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1    | 100      | Using where |
| 4  | UNION       | ptest1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1    | 100      | Using where |
| 5  | UNION       | ptest1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1    | 100      | Using where |
| 6  | UNION       | ptest1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1    | 100      | Using where |
| 7  | UNION       | ptest1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1    | 100      | Using where |
| 8  | UNION       | ptest1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1    | 100      | Using where |
+----+-------------+--------+------------+------+---------------+-----+---------+-----+------+----------+-------------+
```


## インデックスの追加と効果の確認
### 普通のindexを追加してみる
```
mysql> ALTER TABLE `partitioning_test`.`ptest1`
    -> ADD INDEX `__advise_index_ptest1_k`(`k`);
```

```
mysql> SHOW FULL CREATE TABLE ptest1;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE  | CREATE TABLE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ptest1 | CREATE PARTITION TABLE `ptest1` (
	`id` int(10) UNSIGNED NOT NULL,
	`k` int(10) UNSIGNED NOT NULL DEFAULT '0',
	`c` char(20) NOT NULL DEFAULT '',
	`pad` char(60) NOT NULL DEFAULT '',
	PRIMARY KEY (`id`),
	GLOBAL INDEX /* __advise_index_ptest1_k_$d977 */ `__advise_index_ptest1_k` (`k`) PARTITION BY KEY (`k`, `id`) PARTITIONS 16,
	LOCAL KEY `_local___advise_index_ptest1_k` (`k`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`id`)
PARTITIONS 16
/* tablegroup = `tg7` */ |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
```

```
mysql> EXPLAIN SELECT pad FROM ptest1 WHERE k = 1;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                                                                                   |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Project(pad="pad")                                                                                                                                                      |
|   BKAJoin(condition="id = id", type="inner")                                                                                                                            |
|     Gather(concurrent=true)                                                                                                                                             |
|       IndexScan(tables="__advise_index_ptest1_k_$7628[p14]", sql="SELECT `id` FROM `__advise_index_ptest1_k_$7628` AS `__advise_index_ptest1_k_$7628` WHERE (`k` = ?)") |
|     Gather(concurrent=true)                                                                                                                                             |
|       LogicalView(tables="ptest1[p1,p2,p3,...p16]", shardCount=16, sql="SELECT `id`, `pad` FROM `ptest1` AS `ptest1` WHERE ((`k` = ?) AND (`id` IN (...)))")            |
| HitCache:false                                                                                                                                                          |
| Source:PLAN_CACHE                                                                                                                                                       |
| TemplateId: 2c129255                                                                                                                                                    |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
```

```
mysql> EXPLAIN EXECUTE SELECT pad FROM ptest1 WHERE k = 1;
+----+-------------+-------------------------------+------------+------+--------------------------------+--------------------------------+---------+-------+------+----------+-------------+
| id | select_type | table                         | partitions | type | possible_keys                  | key                            | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------------------------------+------------+------+--------------------------------+--------------------------------+---------+-------+------+----------+-------------+
| 1  | SIMPLE      | __advise_index_ptest1_k_$b02b | NULL       | ref  | auto_shard_key_k               | auto_shard_key_k               | 4       | const | 1    | 100      | Using index |
| 1  | PRIMARY     | ptest1                        | NULL       | ref  | _local___advise_index_ptest1_k | _local___advise_index_ptest1_k | 4       | const | 1    | 100      | NULL        |
| 2  | UNION       | ptest1                        | NULL       | ref  | _local___advise_index_ptest1_k | _local___advise_index_ptest1_k | 4       | const | 1    | 100      | NULL        |
| 3  | UNION       | ptest1                        | NULL       | ref  | _local___advise_index_ptest1_k | _local___advise_index_ptest1_k | 4       | const | 1    | 100      | NULL        |
| 4  | UNION       | ptest1                        | NULL       | ref  | _local___advise_index_ptest1_k | _local___advise_index_ptest1_k | 4       | const | 1    | 100      | NULL        |
| 5  | UNION       | ptest1                        | NULL       | ref  | _local___advise_index_ptest1_k | _local___advise_index_ptest1_k | 4       | const | 1    | 100      | NULL        |
| 6  | UNION       | ptest1                        | NULL       | ref  | _local___advise_index_ptest1_k | _local___advise_index_ptest1_k | 4       | const | 1    | 100      | NULL        |
| 7  | UNION       | ptest1                        | NULL       | ref  | _local___advise_index_ptest1_k | _local___advise_index_ptest1_k | 4       | const | 1    | 100      | NULL        |
| 8  | UNION       | ptest1                        | NULL       | ref  | _local___advise_index_ptest1_k | _local___advise_index_ptest1_k | 4       | const | 1    | 100      | NULL        |
+----+-------------+-------------------------------+------------+------+--------------------------------+--------------------------------+---------+-------+------+----------+-------------+
```
- `type`= `ref`,そしてpartitionごとに`key`も使われていることが分かる。
- が、やはりすべてのpartitionをアクセスしてしまったことも分かる。


### clustered indexを試してみる
https://www.alibabacloud.com/help/en/polardb-x/latest/clustered-indexes
```
mysql> ALTER TABLE ptest1 ADD CLUSTERED INDEX c_k(k);
```

```
mysql> EXPLAIN SELECT pad FROM ptest1 WHERE k = 1;
+----------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                    |
+----------------------------------------------------------------------------------------------------------+
| Gather(concurrent=true)                                                                                  |
|   IndexScan(tables="c_k_$9148[p14]", sql="SELECT `pad` FROM `c_k_$9148` AS `c_k_$9148` WHERE (`k` = ?)") |
| HitCache:false                                                                                           |
| Source:PLAN_CACHE                                                                                        |
| TemplateId: 2c129255                                                                                     |
+----------------------------------------------------------------------------------------------------------+
```
- clustered indexを追加したら、partitionまで特定できて、一発で見つかった。
```
mysql> EXPLAIN EXECUTE SELECT pad FROM ptest1 WHERE k = 1;
+----+-------------+-----------+------------+------+--------------------------------+--------------------------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys                  | key                            | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+--------------------------------+--------------------------------+---------+-------+------+----------+-------+
| 1  | SIMPLE      | c_k_$2814 | NULL       | ref  | _local___advise_index_ptest1_k | _local___advise_index_ptest1_k | 4       | const | 1    | 100      | NULL  |
+----+-------------+-----------+------------+------+--------------------------------+--------------------------------+---------+-------+------+----------+-------+
```
https://zenn.dev/m_yamashii/articles/mysql-index-cardinality#%E3%82%AF%E3%83%A9%E3%82%B9%E3%82%BF%E3%83%BC%E5%8C%96%E3%82%A4%E3%83%B3%E3%83%87%E3%83%83%E3%82%AF%E3%82%B9

:::note info
MySQLではclustered index＝主キーのインデックスであり、つまりclustered indexはprimay keyにしかないのですが、
polardb-xでは普通のカラムに対してもclustered indexが作られる。
:::