Subscribed unsubscribe Subscribe Subscribe

ふり返る暇なんて無いね

日々のメモ書きをつらつらと

MySQLのLINEAR KEY パーティションでPKで検索しても遅い場合

mysql

プライマリーキー(id)でSELECTしてるのにSlowlogに出てくる不思議なクエリがあるからなんでだろうなと調べてみると、なんと全パーティションを検索していて遅かったという現象がありました。

ちょっとテスト環境で再現してみます。

環境

Ubuntu 12.04のdebで入るmysql-server-5.5で見てます。

> show variables like 'version';
+---------------+-----------------------------+
| Variable_name | Value                       |
+---------------+-----------------------------+
| version       | 5.5.43-0ubuntu0.12.04.1-log |
+---------------+-----------------------------+
1 row in set (0.04 sec)

下準備

実際のテーブル定義からいろいろ改変してます。なので、適当です。

CREATE TABLE user_item (
  id         int(10) unsigned NOT NULL AUTO_INCREMENT,
  user_id    int(10) unsigned NOT NULL,
  item_id    int(10) unsigned NOT NULL,
  count      int(10) unsigned NOT NULL DEFAULT 0,
  type       enum('type1','type2','typ3','type4') NOT NULL DEFAULT 'type1',
  created_at datetime NOT NULL,
  updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (id,user_id),
  UNIQUE KEY   user_item_type (user_id, item_id, type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
PARTITION BY LINEAR KEY (user_id) PARTITIONS 128;

-- 適当にデータ入れる
INSERT INTO user_item (user_id, item_id, count, created_at) VALUES
    (1,1,1, NOW()),
    (1,2,1, NOW()),
    (2,2,1, NOW()),
    (3,3,1, NOW()),
    (3,4,1, NOW()),
    (4,1,1, NOW()),
    (4,4,3, NOW())
;

実際に試す

> explain partitions select * from user_item where id=1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_item
   partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30,p31,p32,p33,p34,p35,p36,p37,p38,p39,p40,p41,p42,p43,p44,p45,p46,p47,p48,p49,p50,p51,p52,p53,p54,p55,p56,p57,p58,p59,p60,p61,p62,p63,p64,p65,p66,p67,p68,p69,p70,p71,p72,p73,p74,p75,p76,p77,p78,p79,p80,p81,p82,p83,p84,p85,p86,p87,p88,p89,p90,p91,p92,p93,p94,p95,p96,p97,p98,p99,p100,p101,p102,p103,p104,p105,p106,p107,p108,p109,p110,p111,p112,p113,p114,p115,p116,p117,p118,p119,p120,p121,p122,p123,p124,p125,p126,p127
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 131
        Extra:
1 row in set (0.00 sec)

プライマリーキーで引いてるのは確かです。ですが、partitionsを見ると全パーティションを見てることが分かります。
実際の現場では512分割してるのでいくらプライマリーキー使ってるとは言え、でかいテーブルに対してそれだけ検索すれば遅くはなります。

よく考えて見ると当たり前の話で、(id, user_id)でプライマリーキー張ってますが、実際のパーティションキーは(user_id)になってます。
キーとなってるuser_idをハッシュ関数に通さない限りどのパーティションにデータがあるかは分からないので、全パーティションなめるのは当然の話でした。


下記はuser_idで引いてる場合、パーティションをちゃんと刈り込んでるのが分かります。

> explain partitions select * from user_item where user_id=1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_item
   partitions: p4
         type: ref
possible_keys: user_item_type
          key: user_item_type
      key_len: 4
          ref: const
         rows: 2
        Extra:
1 row in set (0.00 sec)


何も考えずにプライマリーキー使っていたら少しはまったというお話でした。