mysql索引问题

 一，索引的存储分类

      索引是在mysql的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引。mysql目前提供了4种索引

   （1）B-Tree索引：最常见的索引类型，大部分引擎支持B树索引。

   （2）HASH索引：只有memory引擎支持，使用场景简单。

   （3）R-Tree索引（空间索引）：空间索引是mysam的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。

    （4）Full-text（全文索引）：全文索引也是myisam的一个特殊索引类型，主要用于全文索引，innodb目前提供对全文索引的支持。

  mysql目前不支持函数索引，但是能对列的前面某一部分进行索引，如：title字段，可以只取前面的10个字符进行索引，这个特性可以大大缩小索引文件的大小，但是前缀索引也有缺点，在排序(order by)和分组(group by)操作的时候无法使用。

   下面我们看一下各类存储引擎对索引的支持情况：

存储引擎索引支持情况

索引	myisam引擎	innodb引擎	memory引擎
B-Tree索引	支持	支持	支持
HASH索引	不支持	不支持	支持
R-Tree索引	支持	不支持	不支持
Full-text索引	支持	支持	不支持

      比较常用到的索引就是B-Tree索引和HASH索引。HASH相对简单，只有memory引擎支持，其使用于key-value，不适用于范围查询，如< ,> ,<= ,>=这类操作。

扫描性能：

        all  <  index   <  range    <   ref    <   eq_ref    <    const , system   < null

all：全表扫描 ，遍历整个表

index ：索引扫描 ，遍历整个索引

range ：索引范围扫描，常见于<  ,> ,<=  ,>= ,between

ref ： 使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值的记录行

eq_ref ： 类似ref，区别在使用的索引是唯一索引，对于每个索引值，表中只有一条记录匹配；就是多表连接使用primary key 或unique index关联条件

const/system ： 单表中最多只有一个匹配行

null ： 不用访问表或索引，如select 1 from t1 where 1;

二，B-Tree索引

     B-Tree索引是最常见的索引，构造类型二叉树，能根据键值提供一行或者一个行集的快速访问，通常只需要很少的读操作就可以找到正确的行。不过，需要注意B-Tree索引中的B不代表二叉树，而是代表平衡树

    
 我们先创建表t17 ，并插入数据

mysql> create table t17(id int auto_increment not null primary key,name varchar(63),age int,intro varchar(63));
Query OK, 0 rows affected (0.32 sec)

mysql> insert into t17(name,age,intro)values('fzy1',1,'intro1'),('fzy2',2,'intro2'),('fzy3',3,'intro3');
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t17;
+----+------+------+--------+
| id | name | age  | intro  |
+----+------+------+--------+
|  1 | fzy1 |    1 | intro1 |
|  2 | fzy2 |    2 | intro2 |
|  3 | fzy3 |    3 | intro3 |
+----+------+------+--------+
3 rows in set (0.00 sec)
 

     1，匹配全值，对索引中所有列都指定具体值，即对索引中的所有列都有等值匹配的条件（关于各explain下各值的参考我们可以看一下：http://blog.csdn.net/fuzhongyu2/article/details/52741253）

例子：

##创建全值索引
mysql> create index idx_full on t17(name,age,intro);
Query OK, 0 rows affected (0.29 sec)
Records: 0  Duplicates: 0  Warnings: 0

##执行语句
mysql> desc select * from t17 where name='fzy1' and age=1 and intro='intro1' \G; 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t17
   partitions: NULL
         type: ref
possible_keys: idx_full
          key: idx_full
      key_len: 137
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.01 sec)

ERROR: 
No query specified

     由上，我们可以看到查询的时候使用了索引idx_full进行优化扫描

       2，匹配值的范围查询，对索引的值能够进行范围查找。

<pre name="code" class="java">##删除全值索引
mysql> drop index idx_full on t17;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

##对age字段建立索引
mysql> create index idx_age on t17(age);Query OK, 0 rows affected (0.33 sec)Records: 0 Duplicates: 0 Warnings: 0

##查询
mysql> desc select * from t17 where age>1 and age<3 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t17
   partitions: NULL
         type: range
possible_keys: idx_age
          key: idx_age
      key_len: 5
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified    

      3，匹配最左端前缀，仅仅使用索引中的最左端进行查询

  就我们在上面讲述到的全值索引为例，我们能够用name,age组合，name,intro组合，name,age,intro组合，name组合使用复合索引，但不能用age组合，intro组合,age,into组合，换句话说不是name开头的就不会利用到复合索引,这个就是最左匹配原则。

      

mysql> drop index idx_age on t17;
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index idx_full on t17(name,age,intro);
Query OK, 0 rows affected (0.31 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc select * from t17 where name='fzy1' and intro='intro1' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t17
   partitions: NULL
         type: ref
possible_keys: idx_full
          key: idx_full
      key_len: 66
          ref: const
         rows: 1
     filtered: 33.33
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

mysql> desc select * from t17 where age='1' and intro='intro1' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t17
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_full
      key_len: 137
          ref: NULL
         rows: 3
     filtered: 33.33
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

    从上面我们看到了possible_keys的的差别，就是我们所说的最左原则。但实际上这是一个覆盖索引，最终执行的时候还是用到了idx_full这个索引。

4，仅仅对索引进行查询，当查询的列都在索引的字段中时，查询的效率更高。

  

mysql> desc select age from t17 where age=1 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t17
   partitions: NULL
         type: ref
possible_keys: idx_age
          key: idx_age
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

5，匹配列前缀，仅仅使用索引中的第一列，并且只包含索引第一列的开头一部分进行查找。

mysql> create index idx_name on t17(name(2));
Query OK, 0 rows affected (0.35 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> insert into t17(name,age,intro)values('hsq',4,'intro4');
Query OK, 1 row affected (0.04 sec)

mysql> desc select * from t17 where name like'hs%' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t17
   partitions: NULL
         type: range
possible_keys: idx_name
          key: idx_name
      key_len: 5
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

6，能够实现索引匹配部分精确而其他部分进行范围匹配

mysql> desc select * from t17 where name like 'hs%' and id>2 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t17
   partitions: NULL
         type: range
possible_keys: PRIMARY,idx_name
          key: idx_name
      key_len: 9
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition; Using where
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

7，如果列名是索引，则使用name is null 就会使用索引

mysql> desc select name from t17 where name is null \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t17
   partitions: NULL
         type: ref
possible_keys: idx_name
          key: idx_name
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

二，存在索引但不能被使用的典型场景

  1，以%开头的like查询不能够利用B-Tree索引

mysql> desc select * from t17 where name like '%hs' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t17
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 25.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

  2，数据类型出现隐式转换的时候也不会使用索引，特别是当列类型是字符串，那么一定记得在where条件中把字符串常量用引号引起来，否则即便这个列上有索引，mysql也不会用到的，因为mysql默认把输入的常量值进行转换后才进行检索。

 

  3，复合索引情况下，假如查询条件不包含索引列最左边部分，即不满足最左原则，是不会使用索引的（个人在实验的时候，并不是这样，上面有例子，如有能解释原因的请务必留言）

  4，如果mysql估计使用索引比全表扫描慢，则不使用索引。

  5，用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到，innodb存储引擎用了or就不能用索引。