mysql80> INSERT INTO t1 VALUES (1, 'one'), (2, 'two'), (3, 'three'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql80> ALTER TABLE t1 ADD KEY (val) INVISIBLE; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `num` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `val` varchar(32) DEFAULT NULL, UNIQUE KEY `num` (`num`), KEY `val` (`val`) /*!50800 INVISIBLE */ ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 1 row in set (0.01 sec) mysql80> explain SELECT * FROM t1 WHERE val = 'two'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql80> explain SELECT * FROM t1 FORCE INDEX(val) WHERE val = 'two'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
おお、ホントだ FORCE INDEXでも使われない!!
けど、これってどうやって使わせるの…? たぶん SELECT /*+ .. */ スタイルの構文で食わせそうな気がするんだけど、まだ情報が見当たらない…(´・ω・`)
テーブルの1/3をフェッチするからコスト的に選ばれてないアレじゃないよね? と思ってフツーのINDEXも作ったけど、こっちはちゃんと選ばれる。ということはちゃんとインビジボーなのだね。
mysql80> ALTER TABLE t1 ADD KEY (val); Query OK, 0 rows affected, 1 warning (0.04 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql80> SHOW CREATE TABLE t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `num` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `val` varchar(32) DEFAULT NULL, UNIQUE KEY `num` (`num`), KEY `val` (`val`) /*!50800 INVISIBLE */, KEY `val_2` (`val`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) mysql80> explain SELECT * FROM t1 WHERE val = 'two'; +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | ref | val_2 | val_2 | 131 | const | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
で、どうやって選ばせればいいの…。ドキュメントはよ…。
0 件のコメント :
コメントを投稿