## モジュール 2: 自動シャーディングにおける AUTO_RANDOM の利用 ![demo_203-tidb-x-beyond-mysql-ja.jpg 85%](https://lab-static.pingcap.com/images/2025/12/2/c360a2c2d71e11c2bb5235f4414b9b0e21f80906.jpg) TiDB は MySQL と高い互換性を提供していますが、「Not Only MySQL（MySQLだけではない）」を体現する分散型アーキテクチャにより、重要な違いも存在します。TiDB の主な利点の一つは、手動によるシャーディング（分散）作業が不要となり、これが自動的に処理されることです。 テーブルの主キーに、MySQL の単調増加な **AUTO_INCREMENT** ではなく TiDB 独自の **AUTO_RANDOM** を利用することで、TiDB はデータを自動的に複数シャードへ分散し、AUTO_INCREMENT に起因する「書き込みホットスポット」問題を効果的に緩和できます。 ### 手順 1. AUTO_RANDOM の仕組みを理解する - TiDB では、主キー列に `AUTO_RANDOM(<シャードビット数>)` を指定することで、自動シャーディングを有効にできます。各シャード内では MySQL の **AUTO_INCREMENT** と同様に値が振られます。この機能を利用するには、対象となるカラムが BIGINT 型（64ビット）である必要があります。最初の1ビットが符号ビット、次の数ビットがシャーディング用、それ以外が **AUTO_INCREMENT** 用に使われます。 ![AutoRandomValueStructure_aa_001_20240125.png 70%](https://lab-static.pingcap.com/images/2025/11/19/357034cd28824fe7d2950338331c5d0f3f2eb2c6.png) - 例えば `bigint AUTO_RANDOM(5)` と定義した場合、TiDB は5ビットをシャーディングに利用し、2の5乗=32の正数シャードと32の負数シャードが割り当てられます。 ### 手順 2. bookshop スキーマにおける AUTO_RANDOM の確認 - [ターミナル 1](ssh://monitor1-ssh#1) にてラボ用データベースへ再び接続します。 ```bash mysql \ --comments -u '${LAB:SERVERLESS_CLUSTER_USERNAME}' -p'${LAB:SERVERLESS_CLUSTER_PASSWORD}' \ -h ${LAB:SERVERLESS_CLUSTER_HOST} -P ${LAB:SERVERLESS_CLUSTER_PORT} \ --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/ssl/certs/ca-certificates.crt \ -D ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME} ``` - `books` テーブル作成スクリプトのSQL文を確認します。`id` 列は AUTO_RANDOM を使うため BIGINT 型でなければなりません。 ```sql SYSTEM cat create_bookshop_schema_in_current_db.sql | egrep -A18 'CREATE TABLE `books`' | egrep '(`books`|`id`)'; ``` ```output CREATE TABLE `books` ( `id` bigint(20) AUTO_RANDOM, PRIMARY KEY (`id`) ``` - `books` テーブル定義も確認します。主キー `id` に AUTO_RANDOM が利用されているほか、この属性はテーブルヒントとしても宣言されています。 ```sql SHOW CREATE TABLE books\G ``` ```output *************************** 1. row *************************** ... `id` bigint(20) NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */, ... PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */ ... ``` - information_schema からも AUTO_RANDOM 設定を確認できます。デフォルトではランダムビット数は5です。 ```sql SELECT TABLE_NAME, TIDB_ROW_ID_SHARDING_INFO FROM information_schema.tables WHERE TABLE_NAME='books' AND TABLE_SCHEMA='${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}'; ``` ```output +------------+---------------------------+ | TABLE_NAME | TIDB_ROW_ID_SHARDING_INFO | +------------+---------------------------+ | books | PK_AUTO_RANDOM_BITS=5 | +------------+---------------------------+ ` rows in set (0.01 sec) ``` ### 手順 3. AUTO_RANDOM の挙動を確認 - `books` テーブル作成時のスクリプトでは、システム変数 `allow_auto_random_explicit_insert` を設定することで、AUTO_RANDOM カラムに対して値を明示的に挿入できるようにしています。これにより既存システムから TiDB へのデータ移行が容易になります。 ```sql SYSTEM head -n 1 create_bookshop_schema_in_current_db.sql; ``` ```output SET @@allow_auto_random_explicit_insert = true; ``` - そのため、テーブル内の100冊の本の `id` 値は、SQLスクリプト実行時に明示的に 1 から 100 まで割り当てられており、AUTO_RANDOM によって自動生成されたものではありません。 ```sql SELECT MIN(id), MAX(id), COUNT(id) FROM books; ``` ```output +---------+---------+-----------+ | MIN(id) | MAX(id) | COUNT(id) | +---------+---------+-----------+ | 1 | 100 | 100 | +---------+---------+-----------+ 1 row in set (0.01 sec) ``` - 新しい本を挿入すると、AUTO_RANDOM により主キーに自動的にシャーディングが適用されます。この挙動を観察するため、10回のトランザクションでそれぞれ10冊ずつ本（既存の本のタイトルに `v2` を付加したもの）を追加します。 ```sql INSERT INTO books (title, type, published_at, stock, price) SELECT CONCAT('v2_',title), type, NOW(), 0, price FROM books WHERE id BETWEEN 1 and 10; INSERT INTO books (title, type, published_at, stock, price) SELECT CONCAT('v2_',title), type, NOW(), 0, price FROM books WHERE id BETWEEN 11 and 20; INSERT INTO books (title, type, published_at, stock, price) SELECT CONCAT('v2_',title), type, NOW(), 0, price FROM books WHERE id BETWEEN 21 and 30; INSERT INTO books (title, type, published_at, stock, price) SELECT CONCAT('v2_',title), type, NOW(), 0, price FROM books WHERE id BETWEEN 31 and 40; INSERT INTO books (title, type, published_at, stock, price) SELECT CONCAT('v2_',title), type, NOW(), 0, price FROM books WHERE id BETWEEN 41 and 50; INSERT INTO books (title, type, published_at, stock, price) SELECT CONCAT('v2_',title), type, NOW(), 0, price FROM books WHERE id BETWEEN 51 and 60; INSERT INTO books (title, type, published_at, stock, price) SELECT CONCAT('v2_',title), type, NOW(), 0, price FROM books WHERE id BETWEEN 61 and 70; INSERT INTO books (title, type, published_at, stock, price) SELECT CONCAT('v2_',title), type, NOW(), 0, price FROM books WHERE id BETWEEN 71 and 80; INSERT INTO books (title, type, published_at, stock, price) SELECT CONCAT('v2_',title), type, NOW(), 0, price FROM books WHERE id BETWEEN 81 and 90; INSERT INTO books (title, type, published_at, stock, price) SELECT CONCAT('v2_',title), type, NOW(), 0, price FROM books WHERE id BETWEEN 91 and 100; ``` ```output Records: 10 Duplicates: 0 Warnings: 0 ... Records: 10 Duplicates: 0 Warnings: 0 ``` - AUTO_RANDOM(5) 利用時は、5ビット分がシャーディングに利用され、さらに1ビットが符号ビットとして付加されます。 - `id` 値を58ビット右シフトすることで、シャーディングに使われる最上位6ビットが確認でき、それにより各行がどのシャードに分散されたかが分かります。下記クエリは各シャードごとの最初の `id` と、タイトルに `_v2` が付く本に注目しています。例では100行が9つのシャードに分散していますが、結果は8, 9, 10 いずれかのシャード数になる場合もあります（10回の挿入トランザクションによる分布です）。 ```sql SELECT MIN(id) first_id, id >> 58 as shard, COUNT(1) as rows_by_shard FROM books WHERE title LIKE 'v2_%' GROUP BY shard ORDER BY first_id; ``` ```output +---------------------+-------+---------------+ | first_id | shard | rows_by_shard | +---------------------+-------+---------------+ | 1729382256910270585 | 6 | 10 | | 2017612633061982359 | 7 | 10 | | 2594073385365405887 | 9 | 10 | | 4323455642275676261 | 15 | 10 | | 4611686018427388065 | 16 | 10 | | 5188146770730811573 | 18 | 10 | | 5764607523034235011 | 20 | 20 | | 6341068275337658479 | 22 | 10 | | 8070450532247929003 | 28 | 10 | +---------------------+-------+---------------+ 9 rows in set (0.01 sec) ``` ### 手順 4. AUTO_RANDOM 使用前後の違いを比較する - 下記クエリの結果を自身で読み取ってみてください。AUTO_RANDOM によりランダム値が生成されるため、出力内容は毎回異なり得ます。 ```sql SELECT 'books', MIN(id), MAX(id), COUNT(id) FROM books WHERE title NOT LIKE 'v2_%' UNION ALL SELECT 'books_v2', MIN(id), MAX(id), COUNT(id) FROM books WHERE title LIKE 'v2_%' ORDER BY 1; ``` ```output +----------+---------------------+---------------------+-----------+ | books | MIN(id) | MAX(id) | COUNT(id) | +----------+---------------------+---------------------+-----------+ | books | 1 | 100 | 100 | | books_v2 | 1729382256910270585 | 8070450532247929012 | 100 | +----------+---------------------+---------------------+-----------+ 2 rows in set (0.01 sec) ``` ### 手順 5. クリーンアップ - 最初の100冊を残し、追加で挿入された本を削除します。 ```sql DELETE FROM books WHERE id > 100; ANALYZE TABLE books; ``` ```output Query OK, 100 rows affected (0.2 sec) ``` - **AUTO_RANDOM** によって高い同時挿入でもホットスポットが緩和できる仕組みについては、インストラクター主導のハンズオン [CLUSTERED インデックスと NONCLUSTERED インデックス](/labs/dba_307_lab_ff0)」でもさらに詳しく学べます。今回はこのままデータベース接続を維持したまま、次のモジュールに進んでください。