02 复杂搜索条件下的访问方式分析

复杂搜索条件下的访问方式分析

表结构

对于这个表：

CREATE TABLE single_table ( 
   id INT NOT NULL AUTO_INCREMENT, 
   key1 VARCHAR(100), 
   key2 INT, 
   key3 VARCHAR(100), 
   key_part1 VARCHAR(100), 
   key_part2 VARCHAR(100), 
   key_part3 VARCHAR(100), 
   common_field VARCHAR(100), 
   PRIMARY KEY (id), 
   KEY idx_key1 (key1), 
   UNIQUE KEY idx_key2 (key2), 
   KEY idx_key3 (key3), 
   KEY idx_key_part(key_part1, key_part2, key_part3) 
) Engine=InnoDB CHARSET=utf8;

  • id主键

  • key1是二级索引

  • key2是唯一二级索引

  • key3是二级索引

  • key_part1, key_part2, key_part3组成联合索引

复杂sql分析方法

SELECT * FROM single_table WHERE  
       (key1 > 'xyz' AND key2 = 748 ) OR 
       (key1 < 'abc' AND key1 > 'lmn') OR 
       (key1 LIKE '%suf' AND key1 > 'zzz' AND (key2 < 8000 OR common_field = 'abc')) ;

  1. 找出搜索涉及的列：key1、key2、common_field

  2. 找出可能用到的索引：idx_key1、idx_key2

  3. 对于每个可能用到的索引进行分析：

    1. 假设使用idx_key1进行查询

      将用不到的查询条件替换成TRUE，原因是后续在回表后再用这些条件进行过滤，此时用不到：

      (key1 > 'xyz' AND TRUE ) OR 
      (key1 < 'abc' AND key1 > 'lmn') OR 
      (TRUE AND key1 > 'zzz' AND (TRUE OR TRUE))

      化简后如下：

      (key1 > 'xyz') OR 
      (key1 < 'abc' AND key1 > 'lmn') OR 
      (key1 > 'zzz')

      # 继续化简（中间的条件永假，后面的条件与第一个取并集）
      (key1 > 'xyz')

    2. 假设使用idx_key2进行查询

      将用不到的查询条件替换成TRUE，原因是后续在回表后再用这些条件进行过滤，此时用不到：

      (TRUE AND key2 = 748 ) OR 
      (TRUE AND TRUE) OR 
      (TRUE AND TRUE AND (key2 < 8000 OR TRUE))

      化简后如下：

      key2 = 748 OR TRUE

      # 即
      TRUE

      因此相当于用二级索引进行全表扫描再回表，不如直接回表

  4. 得出结论，可能会采用idx_key1的range+回表的方式，或者是全表扫描的方式进行。

发布于