MySql索引详解

索引的概念 

索引是一种数据结构，用于提高数据的检索效率。在计算机科学中，索引是为了快速访问和搜索数据而创建的数据结构。

在数据库中，索引是对数据库表中一个或多个列的值进行排序和组织的结构。通过创建索引，可以大大加快数据库的查询速度，因为索引使得数据库系统可以更快地定位和访问特定的数据行，而不需要逐行扫描整个表。

在搜索引擎中，索引是为了快速搜索大量文档而构建的数据结构。搜索引擎会对文档进行分析和索引化，以便根据用户的查询快速匹配并返回相关的文档。

索引通常使用树状结构（如二叉树、B树）或哈希表等数据结构来实现。它们根据指定的关键字或值对数据进行排序和组织，以便能够快速定位和检索所需的数据。

简单来说：索引是一种特殊的数据，保存的是其它数据的位置，查找数据时先查找索引再通过索引查找数据，提升查找效率  

2.索引的优缺点

由上述，可以了解到索引的作用是加快查找的速度，提升查询的效率。

索引的优点：
可以大大加快数据的检索速度，这也是创建索引的最主要的原因。 
可以加速表和表之间的连接
在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。 
创建唯一性索引，保证数据库表中每一行数据的唯一性;

索引的缺点：
索引需要占物理空间
创建索引时需要较长时间
当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度

索引的数据结构

索引的数据结构使用B+Tree

B+Tree是从B-Tree演变来的，B-Tree是平衡多叉树，采用二分查找搜索，树的查找效率跟树的高度有关，高度越低，效率越高

 

B+Tree在B-Tree的基础上进行优化，将除叶子节点外的所有数据，放到叶子节点，每一层只保存键和指针，每层能保存更多节点，树的高度降低，查找效率提升

索引的类型

1.按照功能分：
        1）普通索引
        最基本的索引类型，没有唯一性之类的限制。

create index 索引名 on 表(列);
alter table 表 add index 索引名(列);

        2）唯一索引
        唯一索引是不允许其中任何两行具有相同索引值的索引。

alter table 表 add unique index 索引名(列);

        3）主键索引
        数据库表中一列或列组合（字段）的值唯一标识表中的每一行。该列称为表的主键。

alter table 表 add primary key 索引名(列);

        4）全文索引
        对数据库中长文本进行匹配，text类型

alter table 表 add fulltext index 索引名(列);

        5）组合索引
        将数据库中多个列组合到一起进行查询

alter table 表 add index 索引名(列,列,列,列);

        6）删除索引

drop index 索引名 on 表;

        7）查询索引

show index from 表;

按索引结构分为：

• 聚簇索引

  索引键的顺序和实际数据的顺序一致

  类似：新华字典的拼音目录

  一个表只能有一个聚簇索引

  主键属于聚簇索引

• 非聚簇索引

  索引键的顺序和实际数据的顺序不一致

  类似：新华字典的偏旁目录

  一个表可以有多个

 

索引的使用

索引的使用场景

1. 表存在大量数据（百万以上）

2. 字段经常用于查询或排序

3. 字段不能存在大量的空值

4. 字段不能存在大量的重复

索引生效和失效情况

生效的情况：

1. 在列创建索引后，使用列排序或查询

失效的情况：

1. 模糊查询，关键字前面加% like '%关键字'

2. 使用or关键字，or两边的字段上都要有索引否则会失效

3. 查询条件中字段上使用函数， where length(name) = 5

4. 查询条件中字段参与运算，where price + 1 > 4

5. 查询条件中字段使用>、<、>=、<= 没有同时设置最大值和最小值

6. 查询条件中使用组合索引，没有使用最左边的字段（最左前缀原则，将最重要的列放左边） 组合索引 a,b,c where b = xx and c = xx

.索引的使用情况
使用explain关键字来查询索引使用情况：

explain 查询语句

 

1）id

SQL执行的顺序的标识,SQL从大到小的执行

2）select_type

表示查询类型，分为简单查询(SIMPLE)和复杂查询(PRIMARY)，复杂查询又分为三类：简单子查询、派生表（from语句中的子查询）、union 查询。

3）table

显示当前行的数据是属于哪张表的，有事看到的不是真实的表名，看到的时derivenN，N表示第N步执行的结果。

4）type

表示MySQL在表中找到所需行的方式，又称“访问类型”

依次从最优到最差分别为：system > const > eq_ref > ref> fulltext > ref_or_null > index_merge > unique_subquery > index_subquery> range > index > ALL

一般来说，需要保证查询至少达到range级别，最好能达到ref

type值	含义
system	表中只有一条记录 ( 等于系统表 ) ，这是const类型的特例，平时不会出现，这个可以忽略不计。
const	表示通过索引一次找到了，const用于primary key 或者 unique 索引。因为只匹配一行数据，所以很快，将主键置于where列表中，mysql 就能将该查询转换成一个常量。
eq_ref	唯一性索引扫描，对于每个索引键，表中只有一条记录于之匹配。常见于主键和唯一索引扫描。
ref	非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
range	只检索给定范围的行，使用一个索引来选择行。keyl列显示使用了哪个索引，一般就是在你的where 语句中出现了between、< 、> 、in 等的查询。这种范围扫描比全表扫描要好，因为它只需要开始于索引的某一点，二结束于另一点，不用扫描全部的索引。
index	Full index scan , index 与All 区别为index类型只遍历索引树。这通常比All块，因为索引文件通常比数据文件小。（也就是说虽然All 和 index 都是读全表，但是index 是从索引中读取的，而all 是从硬盘中读取的）。
ALL	Full table Scan，将遍历全表以找到匹配的行。
index_merge	在查询过程中需要多个索引组合使用，通常出现在有or的关键字的sql中
ref_or_null	对于某个字段既需要关联条件，也需要null值的情况下，查询优化器会选择用ref_or_null连接查询。
index_subquery	利用索引来关联子查询，不再全表扫描。
snique_subquery	该类型类似于index_subquery。子查询中的唯一索引。

 

5）possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

6）key

显示MySQL实际决定使用的键（索引）

7）key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

8）ref

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。常见的有：const（常量），字段名（例：film.id）

9）rows

MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数，注意这个不是结果集里的行数。

10）Extra

展示额外信息