Mysql一之索引概述

左手的ㄟ右手 2022-11-20 09:35 126阅读 0赞

### 索引概念 ###

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑[指针][Link 1]清单。索引的作用相当于图书的[目录][Link 2]，可以根据目录中的[页码][Link 3]快速找到所需的内容。

索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。

当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量[数据库系统][Link 4]时间，并造成大量磁盘[I/O][I_O]操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。

### 索引类型 ###

以Mysql为例，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”条件下高效，对于范围查询、排序及组合索引仍然效率不高。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NDcwNzMz_size_16_color_FFFFFF_t_70][]

3. BTREE

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NDcwNzMz_size_16_color_FFFFFF_t_70 1][]

4. RTREE

RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。相对于BTREE，RTREE的优势在于范围查找。

### 索引种类 ###

*  普通索引：它的结构主要以B+树和哈希索引为主，主要是对数据表中的数据进行精确查找；
 *  唯一索引：在创建唯一索引时要不能给具有相同的索引值，加速查询 + 列值唯一（可以有null）；
 *  主键索引：在我们给一个字段设置主键的时候，它就会自动创建主键索引，用来确保每一个值都是唯一的。加速查询 + 列值唯一（不可以有null）+ 表中只有一个；
 *  组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并；
 *  全文索引：对文本的内容进行分词，进行搜索数据表中的字段是不是包含我们搜索的关键字，就像搜索引擎中的模糊查询；
 *  聚集索引：我们在表中添加数据的顺序，与我们创建的索引键值相同，而且一个表中只能有一个聚集索引；
 *  覆盖索引：-select b,c,d from t1 ;
    
                       select b,c,d from t1 where b=1 and c =1 and d=1 ;
    
                       select a,b,c,d from t1 where b=1 and c =1 and d=1;
    
    【a是主键，给bcd建立联合索引】，如上几个sql，select出来的内容，和where条件字段，刚好和建立的索引一致.
    
    回表-使用非聚簇索引进行查找数据时，需要根据主键值去聚簇索引中再查找一遍完整的用户记录，这个过程叫做回表.
    
    我们sql写这样子：
    
    select a,b,c,d from t1 where b=15 and c=16 and d=17
    
    a是主键列，但是在联合索引的叶子节点上，页存储了对应的主键值，所以依旧不需要回表操作。
    
    总结：使用覆盖索引，我们需要select出来的列，都已经存在了索引树的叶子节点上。所以不需要回表操作，如果我们select出来的某列，不在该联合索引的叶子节点上（比如上表的e列），那就需要根据对应索引值，去聚簇索引树上回表查询对应的e列值了。

**如有披露或问题欢迎留言或者入群探讨**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NDcwNzMz_size_16_color_FFFFFF_t_70 2][]

[Link 1]: https://baike.baidu.com/item/%E6%8C%87%E9%92%88/2878304
[Link 2]: https://baike.baidu.com/item/%E7%9B%AE%E5%BD%95/96183
[Link 3]: https://baike.baidu.com/item/%E9%A1%B5%E7%A0%81/7716178
[Link 4]: https://baike.baidu.com/item/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%B3%BB%E7%BB%9F/215176
[I_O]: https://baike.baidu.com/item/I%2FO/84718
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NDcwNzMz_size_16_color_FFFFFF_t_70]: /images/20221120/6f681766cd0a49bb96e57bc08fd3407b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NDcwNzMz_size_16_color_FFFFFF_t_70 1]: /images/20221120/b575df24fa38432395981c9284da576a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NDcwNzMz_size_16_color_FFFFFF_t_70 2]: /images/20221120/7014b0666ec24a139cbc70afe8a677ca.png