最简介清晰的MySQL索引介绍

桃扇骨 2023-02-22 10:42 4阅读 0赞

## 一、索引 ##

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度

--------------------

## 二、索引类型 ##

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

### 1. FULLTEXT ###

即为全文索引，Innodb不支持全文索引，如果一定要用的话，最好使用sphinx等搜索引擎。myisam对中文支持的不是很好。不过新版本的Innodb已经支持了。  
其可以在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的优势在于范围查找

--------------------

### 三、索引种类 ###

*  普通索引：仅加速查询
 *  唯一索引：加速查询 + 列值唯一（可以有null）
 *  主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
 *  组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
 *  全文索引：对文本的内容进行分词，进行搜索

--------------------

## 四、索引操作 ##

### 1.创建索引 ###

<直接创建索引>-- 创建普通索引
    CREATE INDEX index_name ON table_name(col_name);
    -- 创建唯一索引
    CREATE UNIQUE INDEX index_name ON table_name(col_name);
    -- 创建普通组合索引
    CREATE INDEX index_name ON table_name(col_name_1,col_name_2);
    -- 创建唯一组合索引
    CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);<通过修改表结构创建索引>ALTER TABLE table_name ADD INDEX index_name(col_name);

### 2.刪除索引 ###

-- 直接删除索引
    DROP INDEX index_name ON table_name;
    -- 修改表结构删除索引
    ALTER TABLE table_name DROP INDEX index_name;

### 3.查看索引 ###

#查看:
    show index from `表名`;
    #或
    show keys from `表名`;

### 4.其他命令 ###

-- 查看表结构
    desc table_name;
    -- 查看生成表的SQL
    show create table table_name;

--------------------

## 五、索引机制 ##

#### 1.为什么我们添加完索引后查询速度为变快？ ####

传统的查询方法，是按照表的顺序遍历的，不论查询几条数据，mysql需要将表的数据从头到尾遍历一遍  
       在我们添加完索引之后，mysql一般通过BTREE算法生成一个索引文件，在查询数据库时，找到索引文件进行遍历(折半查找大幅查询效率)，找到相应的键从而获取数据。

#### 2.索引的代价 ####

*  创建索引是为产生索引文件的，占用磁盘空间。
 *  索引文件是一个二叉树类型的文件，可想而知我们的dml操作同样也会对索引文件进行修改，所以性能会下降。