MySQL数据库in太多不⾛索引案例
Limiting Memory Use for Range Optimization
To control the memory available to the range optimizer, use the  system variable:
A value of 0 means “no limit.”
With a value greater than 0, the optimizer tracks the memory consumed when considering the range access method. If the specified limit is about to be exceeded, the range access method is abandoned and other methods, including a full table scan, are considered instead. This could be less optimal.
If this happens, the following warning occurs (where N is the current  value):
同样的SQL语句，in ⼏百个。在⼀台实例上⾛索引，在另⼀个实例上不⾛索引，第⼀感觉是和eq_range_index_dive_limit 之类的参数，调了⼏次之后⽆法复现问题，在看帮助⽂档时才注意到 range_optimizer_max_mem_size 参数也会引起全表扫描。
问题复现如下：
mysql> show create table t1;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                  | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`od` int(11) DEFAULT NULL,
`name` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `od` (`od`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from t1;
+-----+------+------+
| id  | od  | name |
+-----+------+------+
|  1 |    1 | a    |
|  2 |    2 | b    |
|  3 |    3 | c    |
|  4 |    4 | d    |
| 100 |  100 | f    |
+-----+------+------+
5 rows in set (0.00 sec)
mysql> show variables like 'range_optimizer_max_mem_size';
+------------------------------+---------+
| Variable_name                | Value  |
+------------------------------+---------+
| range_optimizer_max_mem_size | 8388608 |
+------------------------------+---------+
1 row in set (0.00 sec)
mysql> explain select * from t1 where id in(1,3,5);
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra      |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL      | range | PRIMARY      | PRIMARY | 4      | NULL |    3 |  100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> set range_optimizer_max_mem_size=1;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> explain select * from t1 where id in(1,3,5);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra      |查看mysql索引
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL      | ALL  | PRIMARY      | NULL | NULL    | NULL |    6 |    50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set,
2 warnings (0.00 sec)

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。