メインコンテンツへ移動

スロークエリ最適化

スロークエリとは​

スロークエリとは、実行に時間がかかるクエリを指します。
通常、データベースクエリは短時間で完了しますが、テーブル内のデータ件数が非常に多い場合（例：数万件以上）、これらのテーブルへの頻繁なクエリ実行はデータベースへの負荷を増大させます。その結果、CPU使用率やメモリ使用量、システム負荷などのハードウェアリソースが過剰に使われ、クエリの処理が遅延し、数百ミリ秒〜数秒単位の遅延要因となります。

クエリの最適化には、以下のような対策を取ることができます。

  • インデックス最適化：適切なインデックスを作成し、クエリのスピードを向上させます。
  • クエリ最適化：効率的なクエリ手法を採用し、不要な計算やデータ転送を回避します。
  • ハードウェア最適化：サーバーのハードウェアリソースを増強し、大規模データの処理性能を向上させます。
  • マルチクラスタデータストレージ：アプリケーションデータのマルチクラスタストレージや行レコードのマルチクラスタストレージを活用し、事業拡大による単一データベースへの継続的な負荷の緩和や垂直スケーラビリティの向上を図ります。

インデックス最適化​

インデックスの基本や作成・管理方法についてはHAP ヘルプセンターをご参照ください。

クエリ最適化​

数十万件以上のデータが登録されたワークシートの場合、クエリの実行方法や対象となるフィールドへの事前インデックス作成が重要です。

最も効率的な方法は、等しい 条件等の精密なクエリ（＝精密検索）を用いることです。特に、値が重複せず既にインデックスが設定されているフィールドでのクエリは非常に高速です。

一方、以下のようなクエリ方法はインデックスが利用されません：

  • 「等しくない」「含まない」「○○で始まらない」などの否定型条件
  • フィルターにおける「含む」「空白」等の条件
  • フィルターの中で「または(OR)」条件を追加する場合
  • ワークシート右上の検索ボックスによるクエリ（通常検索）はインデックスを利用しません
  • 選択項目型で1つの値が多くのレコードに設定されている場合、インデックスは利用されません
  • 文字列フィールドをピンインでソートする場合、並び替えインデックスは有効になりません

データ量が多いワークシートでは、上記のような大量クエリ条件の使用を最小限にして、データベース負荷軽減を推奨します。

ハードウェア最適化​

  • CPUコア数の増加：
    MongoDBはマルチスレッドアプリケーションのため、CPUコア数を増やすことで同時実行性能が向上します。また、クエリ・書き込み処理には高性能なマルチコアCPUが適しています。

  • メモリ増設：
    メモリはMongoDBのパフォーマンスに直結します。十分なメモリを確保することで、ワーキングセットキャッシュ効率が高まり、ディスクI/O回数を削減し、クエリ性能が向上します。

  • 高性能SSDディスクの利用：
    SSDは従来のHDDよりアクセスレイテンシが低く、スループットも高い特性があります。MongoDBのデフォルトストレージエンジン（WiredTiger）は、SSDに最適化されています。

インデックス最適化の実用的なヒント​

ログ検索​

MongoDBプロセスでCPU使用率が高い場合、原因の多くは大量のスロークエリステートメントです。MongoDBログからこれらのスロークエリを特定し、更なる最適化を進めることができます。

MongoDBログのデフォルトパスは、/data/mingdao/script/volume/data/logs/mongodb.logです。
カスタムデータパスを設定している場合は、実際のデータパス配下でmongodb.logファイルを検索してください。

ログ解析例​

以下は2つのログ解析例です。

例1​

2023-03-08T15:50:43.075+0800 I COMMAND [conn38294] command mdwsrows.ws63083dafdf551460042a73 command: find { find: "ws63083dafdf551460042a73", filter: { 63e0bafdafa62a19751ee00: "4115fd005", status: 1.0 }, sort: { utime: -1 }, projection: { _id: 0, status: 0, sharerange: 0, wsutime: 0, keywords: 0, discussunreads: 0, users: 0, owners: 0, unreads: 0 }, limit: 1 } planSummary: COLLSCAN keysExamined:0 docsExamined:381665 hasSortStage:1 cursorExhausted:1 numYields:2981 nreturned:1 reslen:1456 locks:{ Global: { acquireCount: { r: 5964 } }, Database: { acquireCount: { r: 2982 } }, Collection: { acquireCount: { r: 2982 } } } protocol:op_query 1023ms

項目の意味：

  • mdwsrows：データベース名
  • ws63083dafdf551460042a73：ワークシート名
  • command：操作内容
  • find：検索条件
  • 63e0bafdafa62a19751ee00：フィールド（コントロールID）
  • COLLSCAN：全件スキャン
  • docsExamined:381665：シート内総レコード数
  • op_query 1023ms：処理時間

ログ解説：
ws63083dafdf551460042a73ワークシート内で、フィールド63e0bafdafa62a19751ee00が4115fd005であるレコードを検索していますが、全件スキャンとなっており、1023msかかっています。

インデックス追加推奨：
63e0bafdafa62a19751ee00フィールドへインデックスを追加してください。

例2​

2023-04-25T08:12:10.736+0000 I COMMAND [conn1696545] command mdwsrows.ws629f242342bb5f060f3da4 command: aggregate { aggregate: "ws629f242342bb5f060f3da4", pipeline: [ { $match: { 62c9c6076c186b941274189f: "90123c1d-1498-43a4-b96b-001a69fd4bb9", 62ccea346123b943f747acf: { $in: [ "0cda5ce1-32139-4c52-b2f4-b324545ccfd1", "2e0e92ec-a935-43fa-a4a3-5fbdee85c0c0" ] }, $and: [ { 62c9c6076c186b941274189f: "90123c1d-1498-43a4-b96b-001a69fd4bb9" }, { 62ccea346123b943f747acf: { $in: [ "0cda5ce1-32139-4c52-b2f4-b324545ccfd1", "2e0e92ec-a935-43fa-a4a3-5fbdee85c0c0" ] } } ], status: 1.0 } }, { $group: { _id: "null", count: { $sum: 1 } } } ], cursor: {}, $db: "mdwsrows", lsid: { id: UUID("d09a5780-0115-4182-8eb2-9de7d76fd834") }, $clusterTime: { clusterTime: Timestamp(1682410329, 2213), signature: { hash: BinData(0, 6B87048FC23C103EE23E4E026B9B246CD45F), keyId: 720303239516495874 } } } planSummary: COLLSCAN keysExamined:0 docsExamined:468021 cursorExhausted:1 numYields:3656 nreturned:1 queryHash:97671D20 planCacheKey:97671D20 reslen:282 locks:{ ReplicationStateTransition: { acquireCount: { w: 3658 } }, Global: { acquireCount: { r: 3658 } }, Database: { acquireCount: { r: 3658 } }, Collection: { acquireCount: { r: 3658 } }, Mutex: { acquireCount: { r: 2 } } } storage:{ data: { bytesRead: 12110018, timeReadingMicros: 11575 } } protocol:op_msg 1043ms

項目の意味：

  • mdwsrows：データベース名
  • ws629f242342bb5f060f3da4：ワークシート名
  • command：操作内容
  • aggregate：集計操作
  • $match：検索条件
  • 62c9c6076c186b941274189f：フィールド（コントロールID）
  • 62ccea346123b943f747acf：フィールド（コントロールID）
  • $in：値を含む
  • COLLSCAN：全件スキャン
  • docsExamined:468021：シート内総レコード数
  • op_msg 1043ms：処理時間

ログ解説：
mdwsrowsデータベースのws629f242342bb5f060f3da4ワークシートに対して、62c9c6076c186b941274189fフィールドが"90123c1d-1498-43a4-b96b-001a69fd4bb9"、かつ62ccea346123b943f747acfフィールドが"0cda5ce1-32139-4c52-b2f4-b324545ccfd1"または"2e0e92ec-a935-43fa-a4a3-5fbdee85c0c0"を含むレコードで集計した例です。全件スキャンのため、1043msかかっています。

インデックス追加推奨：
62c9c6076c186b941274189fと62ccea346123b943f747acfの複合インデックスを追加してください。

ログ内フィールドIDとワークシート上の項目対応​

ワークシートID取得後は、systemURL/worksheet/ワークシートIDにアクセスすることでワークシートページを表示できます。

例えば、ログ上でシート名が ws643e4f73c70d5c2cc7285ce3 の場合、先頭のwsを除いた 643e4f73c70d5c2cc7285ce3 がワークシートIDとなります。
systemURL/worksheet/643e4f73c70d5c2cc7285ce3 にアクセスしてください。

ワークシート画面では、アプリ管理者がAPIドキュメントを閲覧できます。

APIドキュメントにて、フィールドIDと項目名の対応表を確認できます。

インデックスの追加方法​

方法1：ワークシート設定画面で追加​

APIドキュメントで対象フィールドを特定後、ワークシート設定画面でインデックスを追加できます。

方法2：データベースで追加​

上記フィールドへのインデックス追加は、MongoDBにログインし次のコマンドで実行します。

use mdwsrows
db.ws63083dafdf551460042a73.createIndex({"63e0bafdafa62a19751ee00":1},{background: true})
db.ws629f242342bb5f060f3da4.createIndex({"62c9c6076c186b941274189f":1,"62ccea346123b943f747acf":1},{background: true})