プライマリーキー(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)
何も考えずにプライマリーキー使っていたら少しはまったというお話でした。
