mysql索引分为哪几类,聚簇索引和非聚簇索引的区别,MySQL索引失效的情况有哪几种情况，MySQL索引优化的手段，MySQL回表

本文主要是介绍mysql索引分为哪几类,聚簇索引和非聚簇索引的区别,MySQL索引失效的情况有哪几种情况，MySQL索引优化的手段，MySQL回表，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

索引分为哪几类？

从大类来分：分为聚簇索引和非聚簇索引；

从具体的种类来分有：

主键索引: 也简称主键。它可以提高查询效率，并提供唯一性约束。一张表中只能有一个主键。

普通索引：就是普普通通的索引。

唯一索引：索引的值不能重复。

复合索引：在工作中用得比较频繁的一个索引；

当有多个查询条件时，我们推荐使用复合索引。比如：我们经常按照 A列 B列 C列进行查询时，通常的做法是建立一个由三个列共同组成的复合索引而不是对每一个列建立普通索引。

创建方式： 复合索引中的索引的顺序是非常重要的；

alert table test add idx_a1_a2_a3 table (a1,a2,a3) 

使用复合索引可以极大的减少回表的带来的性能开销；（体现在 复合索引可以进行更多的索引覆盖（因为你索引的个数明显更加多了呀），即便是回表也是携带更少的主键进行回表查询（与MySQL5.7后的索引下推有关））

复合索引是基于第一个索引的，比如你建立了一个（a,b,c）的复合索引，那么你不能跳过a索引直接去查询b索引，因为在建立（a,b,c）这个复合索引的时候，是会创建（a），（a,b），（a,b,c）这三个索引的，你会发现它们都是基于a索引的； (并不会单独的创建(a,c)这个索引)

hash索引：hash天然快（最快o(1),最慢o(n),树化(lon(n))），但是天然无序；

空间索引；

全文索引

聚簇索引和非聚簇索引的区别

什么是聚簇索引？（重点）

聚簇索引就是将数据(一行一行的数据)跟索引结构放到一块，InnoDB存储引擎使用的就是聚簇索引；

注意点：

1、InnoDB使用的是聚簇索引（聚簇索引默认使用主键作为其索引），将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。

2、若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

聚簇索引具有唯一性，由于聚簇索引是将数据(一行一行的数据)跟索引结构放到一块，因此一个表仅有一个聚簇索引，其他辅助索引可能是只有几个列的数据和索引放在一起！

表中行的物理顺序和索引中行的物理顺序是相同的，在创建任何非聚簇索引之前创建聚簇索引，这是因为聚簇索引改变了表中行的物理顺序，数据行 按照一定的顺序排列，并且自动维护（有序就一定需要维护）这个顺序；

聚簇索引中的索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一且非空的索引代替。如果没有这样的索引，InnoDB 会隐式定义一个6个字节大小的row_id来作为主键，这个主键会作为聚簇索引中的索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可。

非聚簇索引

非聚簇索引在 InnoDB 引擎中，也叫二级索引

在 MySQL 的 InnoDB 引擎中，每个索引都会对应一颗 B+ 树，而聚簇索引和非聚簇索引最大的区别在于叶子节点存储的数据不同，聚簇索引叶子节点存储的是行数据，因此通过聚簇索引可以直接找到真正的行数据；而非聚簇索引叶子节点存储的是主键信息，所以使用非聚簇索引还需要回表查询，因此我们可以得出

聚簇索引和非聚簇索引的区别主要有以下几个：

• 聚簇索引叶子节点存储的是行数据；而非聚簇索引叶子节点存储的是聚簇索引（通常是主键 ID）。

• 聚簇索引查询效率更高，而非聚簇索引需要进行回表查询，因此性能不如聚簇索引。

• 聚簇索引一般为主键索引，而主键一个表中只能有一个，因此聚簇索引一个表中也只能有一个，而非聚簇索引则没有数量上的限制。

什么叫回表？（重点）

如果一个查询是先走辅助索引（聚簇索引外的索引都叫辅助索引）的，那么通过这个辅助索引（innodb中的辅助索引的data存储的是主键）没有获取到我们想要的全部数据，那么MySQL就会拿着辅助索引查询出来的主键去聚簇索引中进行查询，这个过程就是叫回表；

MySQL索引失效的几种情况（重点）

①like查询以%开头，因为会导致查询出来的结果无序；

②类型转换，列计算也会可能会让索引失效，因为结果可能是无序的，也可能是有序的；

③在一些查询的语句中，MySQL认为走全表扫描比索引更加快也会导致索引失效；

④如果条件中有or并且or连接的字段中有列没有索引，那么即使其中有条件带索引也不会使用索引 (这是因为MySQL判断即便你开始走了索引查询，但是它发现查询中有Or ，也就是说or 后面的还是需要走全表扫描（因为or会导致后面的数据是无序的），所以MySQL还不如一开始就直接走全表扫描，这也是为什么尽量少用or的原因)要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引，当检索条件有or但是所有的条件都有索引时，索引不失效，可以走【两个索引】，这叫索引合并（取二者的并集）;

⑤复合索引不满足最左原则就不能使用全部索引

MySQL索引优化手段有哪些？

① 尽可能的使用复合索引而不是索引的组合；

②创建索引尽量让辅助索引进行索引覆盖 而不是回表；

③在可以使用主键id的表中，尽量使用自增主键id，这样可以避免页分裂；

④查询的时候尽量不要使用select * ，这样可以避免大量的回表；

⑤尽量少使用子查询，能使用外连接就使用外连接，这样可以避免产生笛卡尔集；

⑥能使用短索引就是用短索引，这样可以在非叶子节点存储更多的索引列降低树的层高，并且减少空间的开销；

什么叫回表？（重点）

如果一个查询是先走辅助索引（聚簇索引外的索引都叫辅助索引）的，那么通过这个辅助索引（innodb中的辅助索引的data存储的是主键）没有获取到我们想要的全部数据，那么MySQL就会拿着辅助索引查询出来的主键去聚簇索引中进行查询，这个过程就是叫回表；

什么叫索引覆盖？（重点）

如果一个查询是先走辅助索引的，那么通过这个辅助索引就直接获取到我们想要的全部数据了，不需要进行回表，这个过程就叫做索引覆盖；

什么是索引？

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。

更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。

索引有哪些优缺点？

索引的优点

可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
索引的缺点

时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
空间方面：索引需要占物理空间。

索引有哪几种类型？

从大类来分：分为聚簇索引和非聚簇索引；

从具体的种类来分有：

主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。

唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。

可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引

可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引

普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。

可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引

可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引

全文索引： 是目前搜索引擎使用的一种关键技术。

可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引
索引的数据结构（b树，hash）
索引的数据结构和具体存储引擎的实现有关，在MySQL中使用较多的索引有Hash索引，B+树索引等，而我们经常使用的InnoDB存储引擎的默认索引实现为：B+树索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。

1）B树索引

mysql通过存储引擎取数据，基本上90%的人用的就是InnoDB了，按照实现方式分，InnoDB的索引类型目前只有两种：BTREE（B树）索引和HASH索引。B树索引是Mysql数据库中使用最频繁的索引类型，基本所有存储引擎都支持BTree索引。通常我们说的索引不出意外指的就是（B树）索引（实际是用B+树实现的，因为在查看表索引时，mysql一律打印BTREE，所以简称为B树索引）

创建索引的三种方式，删除索引
第一种方式：在执行CREATE TABLE时创建索引

CREATE TABLE user_index2 (id INT auto_increment PRIMARY KEY,first_name VARCHAR (16),last_name VARCHAR (16),id_card VARCHAR (18),information text,KEY name (first_name, last_name),FULLTEXT KEY (information),UNIQUE KEY (id_card)
);

第二种方式：使用ALTER TABLE命令去增加索引

ALTER TABLE table_name ADD INDEX index_name (column_list);

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。

索引名index_name可自己命名，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

第三种方式：使用CREATE INDEX命令创建

CREATE INDEX index_name ON table_name (column_list);

CREATE INDEX可对表增加普通索引或UNIQUE索引。（但是，不能创建PRIMARY KEY索引）

删除索引

根据索引名删除普通索引、唯一索引、全文索引：alter table 表名 drop KEY 索引名

alter table user_index drop KEY name;
alter table user_index drop KEY id_card;
alter table user_index drop KEY information;

删除主键索引：alter table 表名 drop primary key（因为主键只有一个）。这里值得注意的是，如果主键自增长，那么不能直接执行此操作（自增长依赖于主键索引）：

需要取消自增长再行删除：

alter table user_index

– 重新定义字段

MODIFY id int,
drop PRIMARY KEY

但通常不会删除主键，因为设计主键一定与业务逻辑无关。

创建索引时需要注意什么？
非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；
取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；
索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。
使用索引查询一定能提高查询的性能吗？为什么
通常，通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。

索引需要空间来存储，也需要定期维护， 每当有记录在表中增减或索引列被修改时，索引本身也会被修改。 这意味着每条记录的INSERT，DELETE，UPDATE将为此多付出4，5 次的磁盘I/O。 因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能，索引范围查询(INDEX RANGE SCAN)适用于两种情况:
基于一个范围的检索，一般查询返回结果集小于表中记录数的30%
基于非唯一性索引的检索
百万级别或以上的数据如何删除
关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。

所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）
然后删除其中无用数据（此过程需要不到两分钟）
删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。
与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。

这篇关于mysql索引分为哪几类,聚簇索引和非聚簇索引的区别,MySQL索引失效的情况有哪几种情况，MySQL索引优化的手段，MySQL回表的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---