MySQL索引使用

在MySQL中，下面几种情况可能使用到索引：

1、对于多列索引，只要查询的条件中用到了索引的最左边列，索引一般就会被使用

     如下按company_id、money的顺序创建一个复合索引：

CREATE INDEX idx_sales_companyid_money ON sales(company_id,money);

    然后按company_id进行查询：

EXPLAIN SELECT col1 FROM sales WHERE company_id=1 \G;  
  
*************************** 1. row ***************************  
           id: 1  
  select_type: SIMPLE  
        table: sales  
         type: ref
possible_keys: idx_sales_companyid_money  
          key: idx_sales_companyid_money  
      key_len: 5  
          ref: const  
         rows: 1  
        Extra: Using where  

  发现即使where条件中不是用company_id和money的组合条件，索引仍然使用到，这个就是B-TREE索引的前缀特性。但是如果只按money条件查询，索引将不会被使用到，如：

 

EXPLAIN SELECT col1 FROM sales WHERE money=1 \G;  
  
*************************** 1. row ***************************  
           id: 1  
  select_type: SIMPLE  
        table: sales  
         type: ALL
possible_keys: NULL  
          key: NULL  
      key_len: NULL 
          ref: NULL  
         rows: 1000
        Extra: Using where  

 

2、对于使用LIKE的查询，后面如果是常量，并且第一个字符不是%，索引才会被使用

EXPLAIN SELECT col1 FROM company WHERE name LIKE '%3' \G;

EXPLAIN SELECT col1 FROM company WHERE name LIKE '3%' \G;

    上面第一个查询不使用索引，第二个查询使用索引，而且如果LIKE 后面跟的是一个列名，也不会使用索引。

 

 3、避免负向查询，NOT、!=、 <> 、!< 、!> 、NOT EXISTS 、NOT IN 、NOT LIKE

    如果列名是索引，使用column_name IS NULL 将使用索引，如：

mysql> EXPLAIN SELECT * FROM e_enterprise WHERE domain IS NULL \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: e_enterprise
         type: ref
possible_keys: uk_enterprise_domainame
          key: uk_enterprise_domainame
      key_len: 195
          ref: const
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

 但是如果使用column_name IS NOT NULL，则不会使用索引，如：

mysql> EXPLAIN SELECT * FROM e_enterprise WHERE domain IS NOT NULL \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: e_enterprise
         type: ALL
possible_keys: uk_enterprise_domainame
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1423193
        Extra: Using where
1 row in set (0.00 sec)

 

存在索引，但不使用索引的情况：

1、如果MySQL估计使用索引比全表扫描慢，则不使用索引，例如如果列key_part1均匀分部在1和100之间，则下列查询使用索引效果就不是很好：

SELECT col1,col2 FROM table_name WHERE key_part1>1 AND key_part1<90;

 

 2、如果用到MEMORY/HEAP表并且WHERE条件中不使用=进行索引，那么不会用到索引列。HEAP表只有在"="条件下才会使用到索引。

 

 3、使用OR条件的查询，如果只有一边是索引列，则不会使用到索引，如下面查询domain中有索引，但是加上OR查询后就没有使用索引：

mysql> EXPLAIN SELECT * FROM e_enterprise WHERE domain ='ffffff' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: e_enterprise
         type: const
possible_keys: uk_enterprise_domainame
          key: uk_enterprise_domainame
      key_len: 195
          ref: const
         rows: 1
        Extra: 
1 row in set (0.00 sec)


mysql> EXPLAIN SELECT * FROM e_enterprise WHERE domain ='ffffff' OR address='XXX' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: e_enterprise
         type: ALL
possible_keys: uk_enterprise_domainame
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1423193
        Extra: Using where
1 row in set (0.00 sec)

 

 4、如果查询条件不是索引列的第一个字段，则不会使用到索引，B-TREE索引的列前缀特性。

 

 5、如果LIKE查询以%开头，同上。

 

 6、同数据类型的列值比较，原则是数字对数字，字符对字符

 6.1、数值列与字符类型比较

     都同时转换成双精度进行比较，可以使用索引

 6.2、字符列与数值比较

     字符列转换成数值，不会使用索引

    如果列类型是字符类型，则在WHERE条件中一定要加上单引号查询，要么不会使用索引，如下：

mysql> EXPLAIN SELECT * FROM e_enterprise WHERE domain ='6020400' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: e_enterprise
         type: const
possible_keys: uk_enterprise_domainame
          key: uk_enterprise_domainame
      key_len: 195
          ref: const
         rows: 1
        Extra: 
1 row in set (0.00 sec)


mysql> EXPLAIN SELECT * FROM e_enterprise WHERE domain = 6020400 \G; 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: e_enterprise
         type: ALL
possible_keys: uk_enterprise_domainame
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1423193
        Extra: Using where
1 row in set (0.00 sec)

 

 7、隔离列，也就是索引的列不能是表达式的一部分，也不能位于函数中

下面的查询将不能使用actor_id上的索引
SELECT col1 FROM actor WHERE actor_id+1=5;

下面查询也是常见错误
SELECT ... WHERE TO_DAYS(CURRENT_DATE)-TO_DAYS(date_col)<=10;
这个查询date_col值离今天不超过10天的所有行，但是他不能使用索引，因为date_col位于函数中，下面是较好的写法：
SELECT ... WHERE date_col>=DATE_SUB(CURRENT_DATE,INTERVAL 10 DAY);
这个查询可以使用索引，但是还有可以改进的地方，使用CURRENT_DATE将会阻止MySQL将查询结果缓存，可以使用常量换掉CURRENT_DATE，如：
SELECT ... WHERE date_col>=DATE_SUB('2012-08-08',INTERVAL 10 DAY);