Mysql学习笔记二之索引基础

一、索引结构

1 B-Tree索引

在谈论索引的时候，没有指明类型的时候，指的就是B-Tree索引，使用B-tree数据结构来存储数据，大部分mysql存储引擎都支持这种索引(archive存储引擎除外，在5.1才开始支持自增列Auto_increment的索引)。
　B-tree 索引是顺序组织存储的，所以很适合用来查找范围数据，此时效率会非常高。
　B-tree索引支持:全值匹配，匹配最左前缀、匹配列前缀、匹配范围值、精确匹配某一列并范围匹配另外一列、只访问索引的查询，由于索引树是有序的，所以除按值查询之外，还可以用于查询中的order by 的操作。

B-Tree 的限制：
　 1.如果不是按照索引的最左列开始查找，则无法使用索引，例如建立了key(fast_name,last_name,date) 这个索引，那么在对last_name 和date 进行精确查询时(在不查询fast_name的情况下)。
　 2.不能跳过索引中的列，如上面建立的索引来举例，使用fast_name,date 但是没有指定last_name,此时只使用date做为索引。
　 3.如果查询中有某个列的范围查询，则其右边所有的列都无法使用索引优化查找，例如where fast_name='liutao' and last_name like '%'+'liu'+"%' and date='2017-6-25',这个查询只使用fast_name,last_name这俩项作为索引列。

2 哈希索引

哈希索引基于哈希表实现，只有精确搜索匹配索引所有列的查询才能生效如下表(只在mysql存储引擎位Memory和NDB集群引擎时才能使用)。
　InnoDB引擎有一个特殊的功能叫做“自适应哈希索引”(adaptive hash index)"，当InnoDB注意到某些索引值别使用的非常频繁时，它会在内存中基于B-Tree 索引之上再创建一个哈希索引，这式B-Tree 索引也具有一些哈希索引的一些优点，

create table testhash(
    fname varchar(50) not null,
    lname varchar(50) not null,
    key using hash(fname)
)engin=memory;

哈希表的限制：
1.哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免免读取行。
2.哈希索引数据并不是按照索引值顺序存储的，所有无法使用group by 排序。
3.哈希也不支持分部索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的，例如建立了(a,b,c)这各索引，当查询只有数据列a时是不会使用索引的。
4.哈希索引不支持范围查询
5.如果哈希冲突很多的话，一些索引维护操作的代价也会很高。

3 R-tree(空间数据索引)

MyISAM存储引擎支持空间索引，可以用作地理数据存储。和B-Tree索引不同，这类索引无前缀查询。空间索引会从所有维度来索引数据。查询时可以有效的使用任意维度来组合查询。

二、索引类型

1.常见的索引类别有：

主键索引: primary key

alter table [表名]　add primary key ([列名])

唯一索引: unique

alter table [表名] add unique   ([列名])

普通索引:index

alter table [表名]　add index [索引名称] ([列名]);

组合索引:index

alter table [表名]　add index [索引名称] ([列名],[列名],[列名]....);

全文索引:fulltext

alter table [表名]　add fulltext ([列名]);

2.各索引的区别

普通索引：最基本的索引，没有任何限制
唯一索引：与”普通索引”类似，不同的就是：索引列的值必须唯一，但允许有空值。
主键索引：它是一种特殊的唯一索引，不允许有空值。
全文索引：仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。
组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。

三、删除索引

  drop index index_name on table_name;
  alter table table_name drop index index_name;
  alter table table_name drop primary key

前俩条移除语句式等价的，第三条是单独删除主键的。

Mysql学习笔记二之索引基础