mysql优化之in查询问题

mysql in 条件非常多时， 查询会不会走索引？

表结构：

根据主键查询：

explain
select * from payment where payment_id in (12,37,62,87,112,137,162,187,212,237,262,287,312,337,362,387,
412,437,462,487,512,537,562,587,612,637,662,687,712,737,762,787,812,837,862,887,912,937,962,987,1012,
1037,1062,1087,1112,1137,1162,1187,1212,1237,1262,1287,1312,1337,1362,1387,1412,1437,1462,1487,1512,
1537,1562,1587,1612,1637,1662,1687,1712,1737,1762,1787,1812,1837,1862,1887,1912,1937,1962,1987,2012,
2037,2062,2087,2112,2137,2162,2187,2212,2237,2262,2287,2312,2337,2362,2387,2412,2437,2462,5254,321, 51513,2123, 12345, 11111)

从执行计划可以看到查询走了主键索引：

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: NULL
         rows: 105
     filtered: 100.00
        Extra: Using where  使用了where条件筛选存储引擎返回的数据
1 row in set, 1 warning (0.24 sec)

根据普通索引查询：从执行计划看也是走了索引

explain
select * from payment where rental_id in (12,37,62,87,112,137,162,187,212,237,262,287,312,337,362,387,
412,437,462,487,512,537,562,587,612,637,662,687,712,737,762,787,812,837,862,887,912,937,962,987,1012,
1037,1062,1087,1112,1137,1162,1187,1212,1237,1262,1287,1312,1337,1362,1387,1412,1437,1462,1487,1512,
1537,1562,1587,1612,1637,1662,1687,1712,1737,1762,1787,1812,1837,1862,1887,1912,1937,1962,1987,2012,
2037,2062,2087,2112,2137,2162,2187,2212,2237,2262,2287,2312,2337,2362,2387,2412,2437,2462,5254,321, 51513,2123, 12345, 11111)

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: range
possible_keys: idx_rental
          key: idx_rental
      key_len: 5
          ref: NULL
         rows: 100
     filtered: 100.00
        Extra: Using index condition  使用索引下推
1 row in set, 1 warning (0.00 sec)

从以上测试的执行计划来看in 条件查询 索引列， 无论主键索引还是普通索引，都是走索引查询， 进行索引过滤！

注意：以上测试表数据量为1.6万，若是表数据量太小， mysql会优化走全表扫描！

mysql> select count(*) from payment;
+----------+
| count(*) |
+----------+
|    16049 |
+----------+

从in 查询的执行计划可以看到 type=range; 这跟范围查询有什么区别呢

再来看以下的执行计划：走组合索引 (customer_id, staff_id) 索引长度ken_len: 3

mysql> EXPLAIN
    -> SELECT customer_id, staff_id FROM payment
    -> WHERE
    -> customer_id = 11
    -> AND staff_id = 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ref
possible_keys: idx_customer_staff
          key: idx_customer_staff
      key_len: 3
          ref: const,const
         rows: 13
     filtered: 100.00
        Extra: Using index

**范围查询：**由于customer_id 使用范围查询， 后面的列 staff_id将无法继续使用索引列查询；所以我们可以看到索引长度ken_len: 2 (只使用了部分组合索引列查询)

mysql> EXPLAIN
    -> SELECT customer_id, staff_id FROM payment
    -> WHERE
    -> customer_id > 11
    -> AND staff_id = 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: range
possible_keys: idx_customer_staff
          key: idx_customer_staff
      key_len: 2
          ref: NULL
         rows: 8043
     filtered: 10.00
        Extra: Using where; Using index

**in查询：**从下面执行计划可以看到in查询后面的列可以继续使用索引查询；

mysql> EXPLAIN
    -> SELECT customer_id, staff_id FROM payment
    -> WHERE
    -> customer_id IN
    -> (12,37,62,87,112,137,162,187,212,237,262,287,312,337,362,387,
    -> 412,437,462,487,512,537,562,587,612,637,662,687,712,737,762,787,812,837,862,887,912,937,962,987,1012,1037,
    -> 1062,1087,1112,1137,1162,1187,1212,1237,1262,1287,1312,1337,1362,1387,1412,1437,1462,1487,1512,1537,1562,1587,
    -> 1612,1637,1662,1687,1712,1737,1762,1787,1812,1837,1862,1887,1912,1937,1962,1987
    -> ,2012,2037,2062,2087,2112,2137,2162,2187,2212,2237,2262,2287,2312,2337,2362,2387,2412,2437,2462,5254)
    -> AND staff_id = 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: range
possible_keys: idx_customer_staff
          key: idx_customer_staff
      key_len: 3
          ref: NULL
         rows: 390
     filtered: 100.00
        Extra: Using where; Using index

通俗的讲：in查询就是多个等值条件查询；从效率上是优于范围查询！