这样写sql避免索引失效

       合理地对数据表加索引可以大大加快数据的访问效率，但索引也不是对任何查询都有效，如果sql的结构不当，也会发生索引失效。所以为避免采坑，写下此文，作为记录。下面的例子是在mysql中创建的一张表，建表语句如下：

CREATE TABLE `user_info` (
  `id` bigint(32) NOT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `address` varchar(32) DEFAULT NULL,
  `nick_name` varchar(32) DEFAULT NULL,
  `role_id` bigint(32) DEFAULT NULL,
  `create_time` date DEFAULT NULL,
  `update_time` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
           

一、尽量选择复合索引

        在对表创建索引时，如果可以使用复合索引，尽量使用复合索引而不是单列索引，理由如下。现创建的一个复合索引：

CREATE INDEX idx_name_age_status ON user(name, age, status);
           

        这里创建了一个名为idx_name_age_status的复合索引，索引涉及到三个字段。在mysql中，这一个索引实际上相当于三个索引，分别是name、 name + age、 name + age + stauts，那么在使用name或name + age或name + age + stauts进行查询时，都会使索引生效。而如果分别给三个字段三个单列索引，在查询这张表时条件中有这三个字段，数据库会选择一个最优的单列索引，而不会使用全部三个索引。

二、最左前缀法

        使用到复合索引时，注意最左前缀法，也就是查询的条件需要从索引的最左列开始，并且不跳过中间列，才能使索引发挥最好的效果。现在创建这样的索引：

CREATE INDEX idx_name_age_status ON user(name, age, status);
           

        使用name或name + age或name + age + status进行查询能使索引生效；使用单个查询条件age或status或者age + status 进行查询不会使索引生效；使用name + stauts进行查询只有name字段的索引生效。

三、使用复合索引时范围查询右边的列索引不会生效

        三个字段在同一个复合索引中，如果查询条件中用到了范围查询，范围查询后面的索引不再生效。比如创建如下索引：

CREATE INDEX idx_name_age_status ON user(name, age, status);
           

        使用这三个字段作为查询条件，处于中间的age如果用到了范围查询，则status上的索引不会生效。

-- name + age + nick_name索引生效
SELECT * FROM user_info WHERE name = '张三' AND age = 22 AND nick_name = '三儿';
-- age用到了范围查询，仅name + age索引生效
SELECT * FROM user_info WHERE name = '张三' AND age > 22 AND nick_name = '三儿';
           

四、在索引列上进行运算操作后，索引将失效

        在索引列上进行运算操作后，索引将失效。比如有这样一个索引：

CREATE INDEX idx_name ON user_info(name);
           

        直接以name作为条件查询索引是生效的，如果在name上使用函数会导致索引失效，比如这样：

SELECT * FROM user_info WHERE substring(name, 1, 2) = '夏侯';
           

五、模糊匹配不生效的情况

        查询条件中的模糊条件，如果是以%开头，不走索引，比如

SELECT * FROM user_info WHERE name like '%三儿%';    -- 不走索引
           

        或者

SELECT * FROM user_info WHERE name like '%三儿';    -- 不走索引
           

        以上两种写法都不会走索引，下面这种写法索引是有效的。

SELECT * FROM user_info WHERE name like '三儿%';    -- 走索引
           

六、字符串没加引号，索引失效

        查询时字符串如果没加引号，索引失效。

        在mysql中，假设有一个字段名是name，类型为VARCHAR，使用name作为条件查询。此时忘记给name的值加上引号，就像这样：

SELECT * FROM user_info WHERE name = 12;    -- name为VARCHAR类型
           

        此时name条件查询的值是12，mysql是可以执行的，如果这里写一个name = 张三，mysql不能执行。虽然这条语句可以执行，并且如果数据表中有name值为12的数据也是可以查出来的。但是，它不会走索引。  

七、作为条件的索引字段位于or两侧，导致该索引失效

      name字段上有索引，所以这样查肯定是走索引的。

SELECT * FROM user_info WHERE name = '张三';
           

        在这张表中另外有一列nick_name，不管这一字段有没有加索引，下面这一个语句都不会走索引。

SELECT * FROM user_info WHERE name = '张三' OR nick_name = '顽皮';
           

八、尽量不使用SELECT *，最好只查询索引列

        尽量使用覆盖索引，也就是尽量只查询索引列，比如复合索引如下：

CREATE INDEX idx_name_age_status ON user(name, age, status);
           

       使用SELECT * FROM user_info WHERE name = '张三' AND age = 12 AND status = 1进行查询，索引自然会生效，但是效率肯定不如SELECT name, age, status FROM user_info WHERE name = '张三' AND age = 12 AND status = 1，这在数据量大的时候可以看出来。

       另外，尽量不要写SELECT * FROM user_info这样的语句，SELECT后面只跟需要用到的列是好的选择。