我有一张有4,000,000条记录的表 . 创建表:(user_id int,partner_id int,PRIMARY_KEY(user_id))engine = InnoDB;我想测试 select 100条记录的性能 . 然后,我测试了以下内容:
mysql> explain select user_id from MY_TABLE use index (PRIMARY) where user_id IN ( 1 );
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------------+
| 1 | PRIMARY | MY_TABLE | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------------+
1 row in set, 1 warning (0.00 sec)
还行吧 . 但是,这个查询是由mysql缓冲的 . 因此,该测试在第一次测试后没有 .
然后,我想到了一个通过随机值选择的sql . 我测试了以下:
mysql> explain select user_id from MY_TABLE use index (PRIMARY) where user_id IN ( select ceil( rand() ) );
+----+-------------+----------+-------+---------------+---------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+---------+--------------------------+
| 1 | PRIMARY | MY_TABLE | index | NULL | PRIMARY | 4 | NULL | 3998727 | Using where; Using index |
+----+-------------+----------+-------+---------------+---------+---------+------+---------+--------------------------+
但是,这很糟糕 . Explain 显示possible_keys为NULL . 因此,计划进行完整的索引扫描,事实上,它比以前的速度慢得多 .
然后,我想请教你如何用索引查找随机值 .
谢谢