关于MySQL IN LIKE OR使用索引的问题

以前在网上看了一些资料，有些人说话不严谨，导致一直被误导，最近在实际开发中发现一些结论有问题，因此特地整理了一下，防止下次继续犯错。

以下前提是有对这个字段建立索引（简直废话，没建的肯定不会使用索引啊）

首先建立一张表：

CREATE DATABASE `test_lkc` CHARATER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE `students` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `stuno` varchar(9) NOT NULL DEFAULT '' COMMENT '学号',
  `name` varchar(64) NOT NULL DEFAULT '' COMMENT '姓名',
  `major` varchar(255) NOT NULL DEFAULT '' COMMENT '专业',
  `class` int(10) unsigned NOT NULL DEFAULT '1' COMMENT '班级',
  `id_card` varchar(18) NOT NULL DEFAULT '' COMMENT '身份证号码',
  `memo` varchar(255) NOT NULL DEFAULT '' COMMENT '备注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `stuno` (`stuno`) USING BTREE,
  UNIQUE KEY `id_card` (`id_card`) USING BTREE,
  KEY `major_class` (`major`,`class`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

 数据我是用php批量生成的

<?php
$pdo = new PDO("mysql:host=127.0.0.1;dbname=test_lkc","root","root");
//数据
$addList = [];
$majorList = ['CN','EN','JP','KO','RU','SV','CU','DE','FR','RO'];
$prefixList = ['A','B','C','D','E','F','G','H','I','J'];
for ($i=0;$i<1000;$i++){
    $row = [];
    $id = sprintf('%03d',$i);
    $row['stuno'] = $prefixList[$i/100] . '2017' . sprintf('%04d',$i + 1);
    $row['name'] = $prefixList[$id[0]] . $prefixList[$id[1]] . $prefixList[$id[2]];
    $row['major'] = $majorList[$id[0]];
    $row['class'] = $i%3 + 1;
    $row['id_card'] = '320106194910290' . $id;

    $addList[] = $row;
}

$res = addAll($pdo,$addList);
var_dump($res);

function addAll($pdo,$addList){
    //字段
    $sql = "INSERT INTO `students` (`" . implode("`,`",array_keys($addList[0])) . "`) VALUES ";
    //拼接SQL语句
    foreach ($addList as $value){
        $sql .= "('";
        $sql .= implode("','", $value);
        $sql .= "'),";
    }
    $sql = rtrim($sql,',');

    $res = $pdo->exec($sql);
    return $res ? $res : $pdo->errorInfo();
}

 

1.IN到底会不会使用索引？

答：会！

explain select * from students where id_card IN ('320106194910290999','320106194910290998');

如果没有走索引，那么可能是你的使用姿势不对，比如

(1)字符串类型没有加单引号，

explain select * from students where id_card IN (320106194910290999,320106194910290998);

(2)或者mysql认为使用全表扫描要比使用索引快（我自己遇到的是数据太少或者查询出来的结果很多的时候会出现，比如图中预估的行数是952）,则不使用索引

explain select * from students where major IN ('RO','CN');

 

2.OR到底会不会使用索引？

答：会！

explain select * from students where id = 1 or stuno = 'A20170002';

如果没有走索引，那么肯定又是你的使用姿势不对，比如说

(1)两个条件里面其中一个字段没有建立索引;

explain select * from students where id = 1 or name = 'AAB';

(2)字符串类型的没有加单引号;

explain select * from students where id = 1 or id_card = 320106194910290999;

(3)mysql认为使用全表扫描要比使用索引快,则不使用索引

explain select * from students where id = 1 or major = 'CN';

 

3.LIKE会不会使用索引

答：LIKE 'xxx%'会；

（1）LIKE'%xxx'或者LIKE'%xxx%'不会

explain select * from students where stuno like 'A2017%';

explain select * from students where stuno like '%20170001';

 

（2）mysql认为使用全表扫描要比使用索引快,则不使用索引

explain select * from students where major like 'C%';

 

 

说来惭愧，我自己被错误的结论误导了那么久，希望大家引以为戒，网络上的答案也不一定就是对的，还是要自己多试试。实践才是检验真理的唯一标准

 

在查询语句前面加explain ，根据type和key来分析查询到底有没有使用索引

type排行：
    连接类型
    system          表只有一行
    const           表最多只有一行匹配，通用用于主键或者唯一索引比较时
    eq_ref          每次与之前的表合并行都只在该表读取一行，这是除了system，const之外最好的一种，
                    特点是使用=，而且索引的所有部分都参与join且索引是主键或非空唯一键的索引
    ref             如果每次只匹配少数行，那就是比较好的一种，使用=或<=>，可以是左覆盖索引或非主键或非唯一键
    fulltext        全文搜索
    ref_or_null     与ref类似，但包括NULL
    index_merge     表示出现了索引合并优化(包括交集，并集以及交集之间的并集)，但不包括跨表和全文索引。
                    这个比较复杂，目前的理解是合并单表的范围索引扫描（如果成本估算比普通的range要更优的话）
    unique_subquery 在in子查询中，就是value in (select...)把形如“select unique_key_column”的子查询替换。
                    PS：所以不一定in子句中使用子查询就是低效的！
    index_subquery  同上，但把形如”select non_unique_key_column“的子查询替换
    range           常数值的范围
    index           a.当查询是索引覆盖的，即所有数据均可从索引树获取的时候（Extra中有Using Index）；
                    b.以索引顺序从索引中查找数据行的全表扫描（无 Using Index）；
                    c.如果Extra中Using Index与Using Where同时出现的话，则是利用索引查找键值的意思；
                    d.如单独出现，则是用读索引来代替读行，但不用于查找
    all             全表扫描