表结构

--- 用户表
CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `phone` char(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `email` varchar(50) NOT NULL,
  `sex` tinyint(1) NOT NULL,
  `birthday` char(10) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `phone` (`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=202001 DEFAULT CHARSET=utf8mb4;


---用户资产表
CREATE TABLE `user_assets` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `money` decimal(10,2) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=202001 DEFAULT CHARSET=utf8mb4;

1、in里面值的类型，和查询列定义类型不一致的情况，不走索引

mysql> DESC SELECT * FROM `user` where phone in (15370225886);
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | phone         | NULL | NULL    | NULL | 201057 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set (0.03 sec)

mysql> DESC SELECT * FROM `user` where phone in ("15370225886");
+----+-------------+-------+------------+-------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | const | phone         | phone | 44      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+-------+---------+-------+------+----------+-------+
1 row in set (0.04 sec)

2、in查询更新，更新操作不走索引

mysql> DESC UPDATE `user_assets` SET money = 0 where id in (SELECT id FROM `user` where sex = 1);
+----+--------------------+-------------+------------+-----------------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type        | table       | partitions | type            | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+--------------------+-------------+------------+-----------------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | UPDATE             | user_assets | NULL       | index           | NULL          | PRIMARY | 4       | NULL | 202032 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | user        | NULL       | unique_subquery | PRIMARY       | PRIMARY | 4       | func |      1 |    10.00 | Using where |
+----+--------------------+-------------+------------+-----------------+---------------+---------+---------+------+--------+----------+-------------+
2 rows in set (0.04 sec)
mysql> DESC UPDATE `user_assets` SET money = 0 where id in (SELECT id FROM `user` where id < 100);
+----+--------------------+-------------+------------+-----------------+---------------+---------+---------+------+--------+----------+--------------------------+
| id | select_type        | table       | partitions | type            | possible_keys | key     | key_len | ref  | rows   | filtered | Extra                    |
+----+--------------------+-------------+------------+-----------------+---------------+---------+---------+------+--------+----------+--------------------------+
|  1 | UPDATE             | user_assets | NULL       | index           | NULL          | PRIMARY | 4       | NULL | 202032 |   100.00 | Using where              |
|  2 | DEPENDENT SUBQUERY | user        | NULL       | unique_subquery | PRIMARY       | PRIMARY | 4       | func |      1 |   100.00 | Using where; Using index |
+----+--------------------+-------------+------------+-----------------+---------------+---------+---------+------+--------+----------+--------------------------+
2 rows in set (0.05 sec)

mysql> DESC UPDATE `user_assets` SET money = 0 where id in (SELECT id FROM `user` where id < 1000);
+----+--------------------+-------------+------------+-----------------+---------------+---------+---------+------+--------+----------+--------------------------+
| id | select_type        | table       | partitions | type            | possible_keys | key     | key_len | ref  | rows   | filtered | Extra                    |
+----+--------------------+-------------+------------+-----------------+---------------+---------+---------+------+--------+----------+--------------------------+
|  1 | UPDATE             | user_assets | NULL       | index           | NULL          | PRIMARY | 4       | NULL | 202032 |   100.00 | Using where              |
|  2 | DEPENDENT SUBQUERY | user        | NULL       | unique_subquery | PRIMARY       | PRIMARY | 4       | func |      1 |   100.00 | Using where; Using index |
+----+--------------------+-------------+------------+-----------------+---------------+---------+---------+------+--------+----------+--------------------------+