mysql索引及原理

Mysql有哪些索引？

从数据结构角度看：

1.B+树索引（O（logn））

2.hash索引：

——仅仅能满足“=”“IN”“<=>”查询，不能使用范围查询。

——其检索效率非常高，索引的检索可以一次定位，不像B-Tree索引需要从根节点到叶子节点，最后才能访问到叶子节点这样多次的IO访问，所以Hash索引的查询效率要远高于B-Tree。

——只有Memory存储引擎显示支持Hash索引。

3.FullText索引（目前MyIsam和InnoDB引擎都支持了）

4.R-Tree索引（用于GIS数据类型创建Spatial索引）

从物理角度看：

1.聚集索引（clustered index）

2.非聚集索引（non-clustered index）

从逻辑角度看：

1.主键索引：主键索引是一种特殊的唯一索引，不允许有空值

2.普通索引或者单列索引

3.多列索引（复合索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用，使用复合索引时遵循最左前缀集合

4.唯一索引或者非唯一索引

5.空间索引：空间索引是对空间数据类型的字段建立的索引，Mysql中的空间数据类型有4种，分别是Geometry、Point、LineString、PolyGon。Mysql使用SPatial关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在储存引擎为MyIsam的表中创建。

6.

CREATE TABLE table_name[col_name data type]
[unique|fulltext|spatial][index|key][index_name](col_name[length])[asc|desc]

——unique|fulltext|spatial为可选参数，分别表示唯一索引、全文索引和空间索引；

——index和key为同义词，两者作用相同，用来指定创建索引

——col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择；

——index_name指定索引的名称，为可选参数，如果不指定，MYSQL默认col_name为索引值；

——length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；

——asc或desc指定升序或降序的索引值存储

索引原理？

想要理解索引原理必须清楚一种数据结构（平衡树），也就是B tree或者B+ Tree，当然有的数据库也使用哈希桶作为索引的数据结构，然后主流的rdbms都是把平衡树当作数据表默认的索引数据结构的。

聚集索引：

我们平时建表的时候都会为表加上主键，在某些关系数据库中，如果建表时不指定主键，数据库会拒绝建表的语句执行，事实上，一个加上了主键的表，并不能成为为表。一个没加主键的表，他的数据无序的放置在磁盘存储器上，一行一行的排列的很整齐，跟人们认知中的表很接近。如果给表加上了主键，那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构，也就是上面所说的平衡树结构，换句话说，就是整个表就变成了一个索引，也就是所谓的聚集索引。这就是为什么一个表只能有一个主键，一个表只能有一个聚集索引，因为主键的作用就是把表的数据格式转换成了索引（平衡树）的格式放置。

其中树的所有节点（底部除外）的数据都是由主键字段中的数据构成，也就是通常我们指定主键的id字段。最下面部分是真正表中的数据。假如一张表有一亿条数据，需要查找其中某一条数据，按照常规逻辑，一条一条的去匹配的话，最坏的情况下需要匹配一亿次才能得到结果，最坏时间复杂度O（n），这是无法接受的，而且这一亿条数据显然不能一次性的读入内存供程序使用。如果把这张表转换成平衡树结构，假设这棵树有20层，那么只需要20次IO开销就能查找到所需要的数据，速度以指数级别提升，时间复杂度O（logn）。

非聚集索引：

非聚集索引和聚集索引一样，同样是采用平衡树作为索引的数据结构。索引树结构中各个节点的值来自于表中的索引字段，假如给user表的name字段加上索引，那么索引就是由name字段中的值构成，在数据改变时，dbms需要一直维护索引结构的正确性。如果给表中多个字段加上索引，那么就会出现多个独立的索引结构，每个索引（非聚集索引）互相之间不存在关联。

每次给字段建一个新索引，字段中的数据就会被复制一份出来，用于生成索引。因此，给表添加索引，会增加表的体积，占用磁盘存储空间。

非聚集索引和聚集索引的区别：

通过聚集索引可以查到需要查找的数据，而通过非聚集索引可以查到记录对应的主键值，在使用主键的值通过聚集索引查找到需要的数据。

不管以任何方式查询表，最终都会利用主键通过聚集索引来定位到数据，聚集索引（主键）是通往真实数据所在的唯一路径。

然而，有一种例外可以不使用聚集索引就能查询出所需要的数据，这种方法称之为“覆盖索引”查询，也就是平时所说的复合索引或者多字段索引查询。

例如：

create index index_age on user_info(age);    //建立索引

select user_name from user_info where age=`20`    //查询年龄是20的用户的用户名

这句的sql语句执行过程：

首先，通过非聚集索引index_age查找age等于20的所有记录的主键ID值；然后通过得到的主键ID值执行聚集索引查找，找到主键ID值对就是真实数据存储的位置；最后，从得到的真实数据中取得user_name字段的值返回，也就是取得最终的结构。

我们将age字段上的索引改成双字段的覆盖索引

create index index_age_and_user_name on user_info(age,user_name);

这条sql语句的执行过程就会变为：

通过非聚集索引index_age_and_user_name查找age等于20的叶节点的内容，然而，叶节点中除了有user_name表主键ID的值以外，user_name字段的值也在里面，因此不需要通过主键ID值的查找数据行的真实所在，直接取得叶节点中user_name的值返回即可。