Mysql in_MySQL 多个in 条件需要注意的地方

MySQL当对一列进行操作时，如果in的条件太多，即使这列上有索引，也是导致执行计划不走索引

因为搜索的记录数太多，MySQL会认为全表扫描可能会更快

对一个表进行删除操作，如果这个列上没有索引，或者执行计划没有走搜索，会导致删除锁住全部的列

sesson1

mysql> show indexes from city1;

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| city1 | 0 | PRIMARY | 1 | ID | A | 3764 | NULL | NULL | | BTREE | | |

| city1 | 1 | idx_code | 1 | CountryCode | A | 232 | NULL | NULL | | BTREE | | |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

2 rows in set (0.00 sec)

mysql> alter table city1 drop index idx_code;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql>

mysql>

mysql> select * from city1 limit 10;

+----+----------------+-------------+---------------+------------+

| ID | Name | CountryCode | District | Population |

+----+----------------+-------------+---------------+------------+

| 1 | Kabul | AFG | Kabol | 1780000 |

| 2 | Qandahar | AFG | Qandahar | 237500 |

| 3 | Herat | AFG | Herat | 186800 |

| 4 | Mazar-e-Sharif | AFG | Balkh | 127800 |

| 5 | Amsterdam | NLD | Noord-Holland | 731200 |

| 6 | Rotterdam | NLD | Zuid-Holland | 593321 |

| 7 | Haag | NLD | Zuid-Holland | 440900 |

| 8 | Utrecht | NLD | Utrecht | 234323 |

| 9 | Eindhoven | NLD | Noord-Brabant | 201843 |

| 10 | Tilburg | NLD | Noord-Brabant | 193238 |

+----+----------------+-------------+---------------+------------+

10 rows in set (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> update city1 set name='aaa' where CountryCode='AFG';

Query OK, 4 rows affected (0.01 sec)

Rows matched: 4 Changed: 4 Warnings: 0

session2

mysql> begin

-> ;

Query OK, 0 rows affected (0.00 sec)

mysql> delete from city1 where CountryCode='NLD';

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

如果添加上索引，则上面的执行没有问题，不会有阻塞。

alter table city1 add index idx_code (CountryCode);

MySQL强制走索引

select * from ws_shop a force index(create_time)

where date(create_time-interval 6 hour) > '2016-10-01 06:00:00';

force index 只支持select ，不支持delete