昨日の 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になった。
