昨日の TokuDBでパーティションが変に遅い件 との比較。
mysql> SHOW CREATE TABLE t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `num` int(10) unsigned NOT NULL, `val` varchar(32) DEFAULT NULL, PRIMARY KEY (`num`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.08 sec) mysql> SHOW CREATE TABLE t2\G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `num` int(10) unsigned NOT NULL, `val` varchar(32) DEFAULT NULL, PRIMARY KEY (`num`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY HASH (num) PARTITIONS 10 */ 1 row in set (0.05 sec) mysql> SHOW CREATE TABLE t3\G *************************** 1. row *************************** Table: t3 Create Table: CREATE TABLE `t3` ( `num` int(10) unsigned NOT NULL, `val` varchar(32) DEFAULT NULL, PRIMARY KEY (`num`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (num) (PARTITION p0 VALUES LESS THAN (100000) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (200000) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (300000) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (400000) ENGINE = InnoDB, PARTITION p4 VALUES LESS THAN (500000) ENGINE = InnoDB, PARTITION p5 VALUES LESS THAN (600000) ENGINE = InnoDB, PARTITION p6 VALUES LESS THAN (700000) ENGINE = InnoDB, PARTITION p7 VALUES LESS THAN (800000) ENGINE = InnoDB, PARTITION p8 VALUES LESS THAN (900000) ENGINE = InnoDB, PARTITION p9 VALUES LESS THAN (1000000) ENGINE = InnoDB, PARTITION px VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ 1 row in set (0.00 sec)
昨日のテーブルをデータ入ったままALTER TABLE .. Engine= InnoDBしただけ。
mysql> SELECT COUNT(*) FROM t1; +----------+ | COUNT(*) | +----------+ | 1000000 | +----------+ 1 row in set (0.46 sec) mysql> SELECT COUNT(*) FROM t2; +----------+ | COUNT(*) | +----------+ | 1000000 | +----------+ 1 row in set (0.49 sec) mysql> SELECT COUNT(*) FROM t3; +----------+ | COUNT(*) | +----------+ | 1000000 | +----------+ 1 row in set (0.49 sec)
同じクエリーを4回投げて一番遅いのを捨てて中央値を取るのはいっしょ。
プルーニングが効かない全スキャンなのでt1が一番速いとはいえ、やっぱりこんなもんだよね。
mysql> SELECT COUNT(*) FROM t1 WHERE num > 900000; +----------+ | COUNT(*) | +----------+ | 100000 | +----------+ 1 row in set (0.06 sec) mysql> SELECT COUNT(*) FROM t2 WHERE num > 900000; +----------+ | COUNT(*) | +----------+ | 100000 | +----------+ 1 row in set (0.05 sec) mysql> SELECT COUNT(*) FROM t3 WHERE num > 900000; +----------+ | COUNT(*) | +----------+ | 100000 | +----------+ 1 row in set (0.05 sec)
プルーニング効かないはずのt2のが速い。。
mysql> explain partitions SELECT COUNT(*) FROM t1 WHERE num > 900000; +----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+--------------------------+ | 1 | SIMPLE | t1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 214438 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+--------------------------+ 1 row in set (0.03 sec) mysql> mysql> explain partitions SELECT COUNT(*) FROM t2 WHERE num > 900000; +----+-------------+-------+-------------------------------+-------+---------------+---------+---------+------+--------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------------------------------+-------+---------------+---------+---------+------+--------+--------------------------+ | 1 | SIMPLE | t2 | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9 | range | PRIMARY | PRIMARY | 4 | NULL | 201950 | Using where; Using index | +----+-------------+-------+-------------------------------+-------+---------------+---------+---------+------+--------+--------------------------+ 1 row in set (0.00 sec) mysql> explain partitions SELECT COUNT(*) FROM t3 WHERE num > 900000; +----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+--------------------------+ | 1 | SIMPLE | t3 | p9,px | range | PRIMARY | PRIMARY | 4 | NULL | 49983 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+--------------------------+ 1 row in set (0.00 sec)
XtraDBはちゃんとプルーニング効きつつtype: rangeになった。
0 件のコメント :
コメントを投稿