Mysql索引的类型（单列索引、组合索引 btree索引 聚簇索引等）

一、索引的类型
Mysql目前主要有以下几种索引类型：FULLTEXT，HASH，BTREE，RTREE。

1. FULLTEXT
   即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。

全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。

2. HASH
   由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。

HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。

3. BTREE
   BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。

4. RTREE
   RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。

相对于BTREE，RTREE的优势在于范围查找。
————————————————
版权声明：本文为CSDN博主「liutong123987」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/liutong123987/article/details/79384395

二、索引的种类
1、单列索引和组合（复合、多列）索引
单列索引
包括普通索引 主键索引 单列索引
主键索引,不允许有空值,(在B+TREE中的InnoDB引擎中,主键索引起到了至关重要的地位)

主键索引建立的规则是 int优于varchar,一般在建表的时候创建,最好是与表的其他字段不相关的列或者是业务不相关的列.一般会设为 int 而且是 AUTO_INCREMENT自增类型的

唯一索引,与普通索引类似,但是不同的是唯一索引要求所有的类的值是唯一的,这一点和主键索引一样.但是他允许有空值,如果是组合索引，则列值的组合必须唯一

1、不按索引最左列开始查询（多列索引） 例如index(‘c1’, ‘c2’, ‘c3’) where ‘c2’ = ‘aaa’ 不使用索引,where c2 = aaa and c3=sss 不能使用索引

2、查询中某个列有范围查询，则其右边的所有列都无法使用查询（多列查询）

Where c1= ‘xxx’ and c2 like = ‘aa%’ and c3=’sss’ 改查询只会使用索引中的前两列,因为like是范围查询

3、不能跳过某个字段来进行查询,这样利用不到索引,比如我的sql 是

explain select * from award where nickname > ‘rSUQFzpkDz3R’ and account = ‘DYxJoqZq2rd7’ and created_time = 1449567822; 那么这时候他使用不到其组合索引

聚集索引和普通索引是一对 InnoDB普通索引的叶子节点存储主键值。

回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。

覆盖索引 不管是SQL-Server官网，还是MySQL官网，都表达了：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

主键索引与唯一索引的区别：

1. 主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。

2. 主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。

3. 唯一性索引列允许空值，而主键列不允许为空值。

4. 主键索引在创建时，已经默认为非空值+ 唯一索引了。

5. 一个表最多只能创建一个主键索引，但可以创建多个唯一索引。

6. 主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

7. 主键可以被其他表引用为外键，而唯一索引不能。
   ————————————————
   版权声明：本文为CSDN博主「战之殇」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
   原文链接：https://blog.csdn.net/qq_38852289/article/details/80817156

聚簇索引与非聚簇索引

1、关于聚簇索引和非聚簇索引的特点

聚簇索引：
a、一个表有且仅有一个聚簇索引
b、聚簇索引B+树的叶子节点存储的行数据
c、聚簇索引建立的列规则，按照优先级为：主键->第一个非空唯一列->InnoDB创建一个隐式row-id作为聚簇索引
d、聚簇索引的存储顺序与物理数据顺序一致

非聚簇索引：
a、也叫普通索引或者辅助索引，一个表可以有0-n个非聚簇索引
b、索引B+树叶子节点存储的是主键值

举个栗子对比说明二者的联系与区别：
假设数据表如下（其中id为主键，name为普通索引）：

则对应的索引结构如下(这里只做实例用，实际的B+树存储，一个节点会存储0-n个值)：

此时，如果我们执行where id=2,则索引如绿色箭头所示，直接通过聚簇索引的B+树，检索到指定位置，并取到行数据记录

如果执行where name='wangwu'，则需要执行的步骤如红色箭头所示，分两步：

第一步：通过辅助索引，检索name=‘wangwu’,并取到叶子节点的主键值为3
第二步：通过主键id=3,到聚簇索引中检索，然后到叶子节点取到id=3的行数据

通过对比我们可以知道，聚簇索引的检索效率明显高于辅助索引的效率，因为辅助索引需要执行两次B+树索引的检索。

2、关于回表查询。

类似上面通过name=‘xxx’这种，需要两次检索的过程，第一次通过辅助索引检索得到主键值，然后通过主键值到聚簇索引中检索出实际行记录的过程，称为回表查询。所以少写 select *

3、关于索引覆盖以及常用实现。

只用在一个索引树上就能得到SQL所需的全部数据而无需回表，称为索引覆盖，索引覆盖的好处是效率快。常见的索引覆盖是通过建立联合索引。

举个栗子：
查询条件select id,name,age from xxx where id=2,此时直接走的聚簇索引,速度是很快的

查询条件select id,name from xxx where name='zhangsan',由于name、id都在辅助索引中存在，无需回表（所需要的数据都能直接拿到）

查询条件select id,name,age from xxx name='zhangsan'，此时由于age不在辅助索引中，必须通过回表才能取到age列，为了实现索引覆盖，可以建立联合索引，将age加入到联合索引中。

注意事项

聚簇索引，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。