☰

JOINクエリでインデックスを使用するにはどうすればいいですか? 質問する

tag-icon • tag-icon mysql sql join query-optimization
JOINクエリでインデックスを使用するにはどうすればいいですか? 質問する

テーブルが 2 つあります。

CREATE TABLE `articles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(1000) DEFAULT NULL,
  `last_updated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `last_updated` (`last_updated`),
) ENGINE=InnoDB AUTO_INCREMENT=799681 DEFAULT CHARSET=utf8 

CREATE TABLE `article_categories` (
  `article_id` int(11) NOT NULL DEFAULT '0',
  `category_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`article_id`,`category_id`),
  KEY `category_id` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

これが私の質問です:

SELECT a.*
FROM
    articles AS a,
    article_categories AS c
WHERE
    a.id = c.article_id
    AND c.category_id = 78
    AND a.comment_cnt > 0
    AND a.deleted = 0
ORDER BY a.last_updated
LIMIT 100, 20

そして、EXPLAINそれに対する答え:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
         type: index
possible_keys: PRIMARY
          key: last_updated
      key_len: 9
          ref: NULL
         rows: 2040
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: c
         type: eq_ref
possible_keys: PRIMARY,fandom_id
          key: PRIMARY
      key_len: 8
          ref: db.a.id,const
         rows: 1
        Extra: Using index

ソートには最初のテーブルの完全なインデックス スキャンを使用しますlast_updatedが、結合 (説明内) にはインデックスを使用しませんtype: index。これはパフォーマンスに非常に悪影響を及ぼし、非常に頻繁に実行されるクエリであるため、データベース サーバー全体が停止します。

を使ってテーブルの順序を逆にしてみましたSTRAIGHT_JOINが、 となりfilesort, using_temporary、さらに悪い結果になりました。

MySQL で結合とソートに同時にインデックスを使用する方法はありますか?

=== 更新 ===

私はこれについて本当に必死です。何らかの非正規化がここで役立つでしょうか?

ベストアンサー1

カテゴリが多数ある場合、このクエリは効率的に実行できません。 では、1 つのインデックスで 2 つのテーブルを同時にカバーすることはできませんMySQL。

非正規化を行う必要があります。last_updated、has_commentsをdeletedに追加しますarticle_categories。

CREATE TABLE `article_categories` (
  `article_id` int(11) NOT NULL DEFAULT '0',
  `category_id` int(11) NOT NULL DEFAULT '0',
  `last_updated` timestamp NOT NULL,
  `has_comments` boolean NOT NULL,
  `deleted` boolean NOT NULL,
  PRIMARY KEY (`article_id`,`category_id`),
  KEY `category_id` (`category_id`),
  KEY `ix_articlecategories_category_comments_deleted_updated` (category_id, has_comments, deleted, last_updated)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

次のクエリを実行します。

SELECT  *
FROM    (
        SELECT  article_id
        FROM    article_categories
        WHERE   (category_id, has_comments, deleted) = (78, 1, 0)
        ORDER BY
                last_updated DESC
        LIMIT   100, 20
        ) q
JOIN    articles a
ON      a.id = q.article_id

もちろん、article_categories内の関連列を更新するたびに、同様に更新する必要がありますarticle。これはトリガーで実行できます。

列はhas_commentsブール値であることに注意してください。これにより、等価述語を使用してインデックスに対して単一の範囲スキャンを実行できるようになります。

また、LIMITサブクエリに入ることにも注意してください。これにより、MySQLデフォルトでは使用されない遅延行検索が使用されます。パフォーマンスが向上する理由については、私のブログのこの記事を参照してください。

SQL Server を使用している場合は、クエリに対してインデックス可能なビューを作成できます。これにより、基本的に、article_categoriesサーバーによって自動的に維持される追加フィールドを含む、非正規化されたインデックス付きコピーが作成されます。

残念ながら、MySQLこれはサポートされていないため、このようなテーブルを手動で作成し、基本テーブルとの同期を維持するための追加コードを記述する必要があります。

おすすめ記事