LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@dennis_wang(dennis wang)

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

Posted at

前提：

  • Alibaba Cloudのコンソール画面上ですでにPolarDB-Xのインスタンスが作られている
  • インターネットからアクセスできるアドレスがすでに作られている
    image.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をご参考ください。

  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の実行計画の見方は以下をご参考ください。

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を試してみる

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  |
+----+-------------+-----------+------------+------+--------------------------------+--------------------------------+---------+-------+------+----------+-------+

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

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?