sql优化之模糊查询

索引中禁止头部出现模糊查询

# 如果头部出现%，索引失效

-- 索引有效
explain select * from employee where name like 'zha%';

-- 索引失效
explain select * from employee where name like '%zha';

-- 索引失效
explain select * from employee where name like '%zha%';

[db2inst1@ ~]$ db2expln -d test -statement "select * from employee where  name like 'zha%'" -terminal -graph -opids
Optimizer Plan:

             Rows   
           Operator 
             (ID)   
             Cost   
                     
         4.65661e-05 
           RETURN    
            ( 1)     
           13.5373   
             |       
         4.65661e-05 
            FETCH    
            ( 2)     
           13.5373   
        /           \
   4.65661e-05     200000  
     IXSCAN       Table:   
      ( 3)        DB2INST1 
     13.5369      EMPLOYEE 
       |         
        0        
 Index:          
 DB2INST1        
 EMPLOYEE_INDEX2 

[db2inst1@ ~]$ db2expln -d test -statement "select * from employee where  name like '%zha'" -terminal -graph -opids 

Optimizer Plan:

    Rows   
  Operator 
    (ID)   
    Cost   
          
   20000  
  RETURN  
   ( 1)   
  1234.25 
    |     
   20000  
  TBSCAN  
   ( 2)   
  1234.25 
    |     
  200000  
 Table:   
 DB2INST1 
 EMPLOYEE 

[db2inst1@ ~]$ db2expln -d test -statement "select * from employee where  name like '%zha%'" -terminal -graph -opids

Optimizer Plan:

    Rows   
  Operator 
    (ID)   
    Cost   
          
   20000  
  RETURN  
   ( 1)   
  1234.25 
    |     
   20000  
  TBSCAN  
   ( 2)   
  1234.25 
    |     
  200000  
 Table:   
 DB2INST1 
 EMPLOYEE 

mysql> explain select * from employee where name like 'zha%';
+----+-------------+----------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
| id | select_type | table    | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+----------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | employee | NULL       | range | employee_index2 | employee_index2 | 43      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+----------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from employee where name like '%zha';
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | employee | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 199799 |    11.11 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from employee where name like '%zha%';
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | employee | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 199799 |    11.11 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)