mysql数据表索引的使用

概述：

索引是在存储引擎中实现的，mysql中常用的数据引擎有MylSA（小并发高性能）和innoDB（常用，高并发，支持事务处理、外键和行级锁），mysql5.5之后默认使用innoDB。Mylsm使用非聚簇索引：主键索引+辅助键索引，两个B+树独立，叶节点存储的是数据记录的索引。innodb使用聚簇索引：主键索引+辅助键索引，主键索引叶节点是真实记录，辅助键索引叶节点存储从数据表复制的键值和主键，找到主键再到主键索引检索真实数据，下面主要梳理innoDB引擎使用的索引。

 图1.两种类型的索引检索方式（引用网上的图）

1.索引的原理

B树索引的存储结构是：B+树：根节点--->非叶节点--->非叶节点--->叶节点，检索方式是按树型检索，所以时间复杂度O(h)=O(log_dN),h是树深，d阶数，具体检索细节不在赘述

2.innodb索引按存储分类：
聚簇索引(clustered index)：

    顺序结构与数据存储物理结构一致的一种索引，叶子结点中存储实际的数据，到达了叶节点即找到数据，索引中键值的逻辑顺序决定了表中相应行的物理顺序并且一个表的聚簇索引只能有唯一的一条；

    1)  有主键时，根据主键创建聚簇索引
    2)  没有主键时，会用一个唯一且不为空的索引列做为主键，成为此表的聚簇索引
    3) 如果以上两个都不满足那innodb自己创建一个虚拟的聚集索引
辅助索引(secondary index)：

    索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同  

   1)非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。
   2)在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据叶，再通过数据叶中的Page Directory找到数据行。

3.检索流程

  使用innoDB数据引擎的MYSQL库，建表是有主键则自动创建主键索引（聚簇索引）。

         1)当以主键为条件时依据主键索引可以直接找到数据。流程如下图2

图2.依据主键查找聚簇索引流程

     2) 很多情况下为了检索更高效我们还需要在聚簇索引的基础上创建多个非聚簇索引，像联合索引、覆盖索引、前缀索引、唯一索引等用来辅助检索，B+Tree的叶子节点上的data就是数据本身，key为主键，这是聚簇索引。非聚簇索引，叶子节点上的data是主键(所以聚簇索引的key，不能过长)。为什么存放的主键，而不是记录所在地址呢，因为记录所在地址并不能保证一定不会变，但主键可以保证。每次给字段建一个新索引， 字段中的数据就会被复制一份出来， 用于生成索引。通过非聚集索引可以查到记录对应的主键值。再使用主键的值通过聚集索引查找到需要的数据，覆盖索引比较特殊，因为建立的索引字段已经包含了所需字段则可以直接查出来不必去再检索主键索引。图3.非聚簇索引的检索流程。

图3.使用非聚簇索引检索流程

4.索引应用分类

  4.1)覆盖索引：覆盖索引只是特定于具体select语录而言的联合索引。也就是说一个联合索引对于某个select语句，通过索引可以直接获取查询结果，而不再需要回表查询啦，就称该联合索引覆盖了这条select语句。

  4.2)前缀索引：对于列的值较长，比如BLOB、TEXT、VARCHAR，就必须建立前缀索引，即将值的前一部分作为索引。这样既可以节约空间，又可以提高查询效率。但无法使用前缀索引做 ORDER BY 和 GROUP BY，也无法使用前缀索引做覆盖扫描。

        判断标准：使用explain，可以通过输出的extra列来判断，或者在查询语句前面加上:SET STATISTICS PROFILE on.可以通过运行它,来观察你的查询是否合理
  4.3)联合索引：从左到右地使用索引字段，对字段的顺序有一定要求。一个查询可以只使用索引中的一部分，更准确地说是最左侧部分（最左优先）。如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效，前导列一定要是使用最频繁的列。使用规则：

        1)条件列中只要条件相连在一起,无论前后,都会利用上联合索引

        2)查询条件中出现联合索引第一列,或者全部,则能利用联合索引

        3)查询条件中没有出现联合索引的第一列,而出现联合索引的第二列,或者第三列,都不会利用联合索引查询

        格式：ALTER TABLE `test`.`users` ADD INDEX `idx_users_id_name` (`name`(10) ASC, `id` ASC) ;

  4.4)单列索引：只要条件列中出现索引列,无论在什么位置,都能利用索引查询

  4.5)唯一索引：唯一索引比较好理解，就是索引值必须唯一，这样的索引选择性是最好的

  4.6)主键索引：主键索引就是唯一索引，不过主键索引是在创建表时就创建了，唯一索引可以随时创建。

5.索引相关常用命令：
  1) 创建主键
 CREATE TABLE `pk_tab2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a1` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  2) 创建唯一索引
create unique index indexname on tablename(columnname);
alter table tablename add unique index indexname(columnname);

  3) 创建单列一般索引
create index indexname on tablename(columnname);
alter table tablename add index indexname(columnname);

  4) 创建单列前缀索引
create index indexname on tablename(columnname(10));    //单列的前10个字符创建前缀索引
alter table tablename add index indexname(columnname(10)); //单列的前10个字符创建前缀索引

  5) 创建复合索引
create index indexname on tablename(columnname1，columnname2);    //多列的复合索引
create index indexname on tablename(columnname1，columnname2(10));    //多列的包含前缀的复合索引
alter table tablename add index indexname(columnname1，columnname2); //多列的复合索引
alter table tablename add index indexname(columnname1，columnname(10)); //多列的包含前缀的复合索引

  6) 删除索引
drop index indexname on tablename;;
alter table tablename drop  index indexname;

  7) 查看索引
show index from tablename;
show create table pk_tab2;

总结：

1:数据量不大的表，没必要建索引
2:不要设置过多的索引，在没有聚集索引的表中，最大可以设置249个非聚集索引，过多的索引首先会带来更大的磁盘空间，而且在数据发生修改时，对索引的维护是特别消耗性能的。
3:考虑创建包含所有输出列的覆盖索引效率较高。
4:对经常使用的字段，并且重复性不高的，考虑聚集索引。

5.oracle和mysql和sqlserver索引应用有些区别，但原理概念基本一致。