mysql索引类型和索引方式

转
 2022-09-02
©版权
原文地址：https://blog.csdn.net/baidu_35813686/article/details/84434404

1.什么是索引

在MySQL中， 索引 （index）也叫做“键（key）”，它 是 存储引擎用于快速找到记录的一种 数据结构 。

2.索引的分类

在MySQL中，通常我们所指的索引类型，有以下几种：

主键索引（PRIMARY KEY）

也简称主键。它可以提高查询效率，并提供唯一性约束。一张表中只能有一个主键。被标志为自动增长的字段一定是主键，但主键不一定是自动增长。一般把主键定义在无意义的字段上（如：编号），主键的数据类型最好是数值。

    ADD PRIMARY KEY (`name`) USING BTREE;

全文索引（FULL TEXT）
旧版的MySQL的全文索引只能用在MyISAM表格的char、varchar和text的字段上。
不过新版的MySQL5.6.24上InnoDB引擎也加入了全文索引
添加全文索引

    ADD FULLTEXT INDEX `idx_full`(`en_name`);

跟普通索引稍有不同
使用全文索引的格式： MATCH (columnName) AGAINST (‘string’)
eg:

       SELECT * FROM `student` WHERE MATCH(`name`) AGAINST('聪')

常规索引(INDEX或KEY)

单列索引

     ALTER TABLE `testDB`.`user` 
     ADD INDEX `idx_name`(`name`) USING BTREE

组合索引

    ADD INDEX `idx_mult`(`name`, `address`) USING BTREE

组合索引最左前缀原则
例如上面我们创建了一个name, address的组合索引
select * from user where name = ‘xxx’ 此时，会走索引
select * from user where address = ‘xxx’ 则不会走索引

唯一索引（UNIQUE KEY）

    ADD UNIQUE INDEX `idx_unique`(`en_name`);

主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。
主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。
唯一性索引列允许空值，而主键列不允许为空值。
主键列在创建时，已经默认为空值 + 唯一索引了。
主键可以被其他表引用为外键，而唯一索引不能。
一个表最多只能创建一个主键，但可以创建多个唯一索引。
主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

202209022300043251.png

3.索引方式

一般有四种索引方式
BTREE，RTREE， HASH ，FULLTEXT。
BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中
BTREE又分两种，一种是B-TREE，如下图

202209022300051152.png
另外一种是B+TREE，结构如下图

202209022300058223.png
B+Tree相对于B-Tree有几点不同：

  1. 非叶子节点只存储键值信息。
  2. 所有叶子节点之间都有一个链指针。
  3. 数据记录都存放在叶子节点中。

BTREE在MyISAM里的形式和Innodb稍有不同
在 Innodb里，有两种形态：一是primary key形态，其leaf node里存放的是数据，而且不仅存放了索引键的数据，还存放了其他字段的数据。二是secondary index，其leaf node和普通的BTREE差不多，只是还存放了指向主键的信息.
而在MyISAM里，主键和其他的并没有太大区别。不过和Innodb不太一样的地方是在MyISAM里，leaf node里存放的不是主键的信息，而是指向数据文件里的对应数据行的信息.