【MySQL】索引基础介绍

刺骨的言语ヽ痛彻心扉 2024-03-31 11:06 60阅读 0赞

## 一、常见的存储引擎有哪些？区别是什么？ ##

*  Mysql 默认的存储引擎是：InnoDB，具有支持事务、行级锁、高并发访问性能。
 *  MyIsAM：访问速度快，实际运用如果是以读和插入操作为主，并对事物完整性和并发性要求不高，推荐选择，因为 MyISAM 最小的锁粒度是表锁，一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。（在 MySQL 5.5之前，当您创建表而未明确指定存储引擎时，MyISAM 是默认存储引擎，这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一）
 *  Memory：不支持事务安全，支持表锁，不支持外键，所有数据储存在内存中，访问速度快。常用来做缓存。
 *  InnoDB：Innodb 是 MySQL 5.5 之后默认的存储引擎，支持事务安全，锁机制是行锁，支持外键，一般运用在对数据完整性较高的核心数据。（选择最多）

## 二、索引结构 ##

![d6da1073f954a05bde52f074711b7cae.png][]

*  **二叉树：****弊端当数据顺序插入时，纵深过长就导致性能降低**
 *  **红黑树：****是自平衡的二叉树，本质也是一个二叉树，也有大数据层级深，导致检索速度慢**
 *  **B-Tree：****多路平衡查找树（几个指针就是几阶，每个指针指向一个key），每一个子节点都会放数据**
 *  **B+Tree：****（最大度数为4,4阶B+Tree），所有的元素都会出现在叶子结点，且所有的数据都会储存在叶子结点，叶子结点组成了一个双向链表。** 
 *  **Hash：****Hash索引只能用于对等比较，不支持范围查询，无序，查询效率高，高于B+Tree索引。通常只需要一次检索就可以了，除遇到hash碰撞，（槽位后面的链表）。**

### InnoDB 为什么选择 B+Tree ###

![92b24a753a0c55cd903253906b3f4d2b.png][]

## 三、索引分类 ##

![22b036fecf1570b8695dc0634eb8e575.png][]

*  主键索引

索引列中的值必须是唯一的，不允许有空值。

*  唯一索引

索引列中的值必须是唯一的，但是允许为空值。

*  全文索引

只能在文本类型 CHAR，VARCHAR，TEXT 类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，**在进行 like 模糊查询时效率比较低**，这时可以创建全文索引。

## 三、什么是聚簇索引和非聚簇索引 ##

*  **聚簇索引**：将数据存储的顺序与索引顺序相同，找到索引也就找到了数据
 *  **非聚簇索引**：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，本质上**非聚簇索引存储的是聚簇索引的值，比如主键ID**

每个InnoDB表具有一个特殊的索引称为聚簇索引（也叫聚集索引，聚类索引，簇集索引），**聚集索引选取规则：**

*  如果表上定义有主键，该主键索引就是聚簇索引。
 *  如果未定义主键，MySQL 取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB 使用它作为聚簇索引。如果没有这样的列，InnoDB就自己产生一个这样的ROWID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。

表中的聚簇索引（clustered index ）就是一级索引，除此之外，表上的其他非聚簇索引都是二级索引，又叫辅助索引（secondary indexes）。

![23de467e555652d2dc8ec6e7fa92cd87.png][]

### 聚簇索引( 主键索引）和非聚簇索引（非主键索引）有什么区别？ ###

聚集索引就是主键，主键id下面挂的是这一行的数据，而二级索引下面挂的是这一数据的id值

![ae370b794efba78fd20554650413994d.png][]

**当执行select \* from user where name=‘ARM’这条sql语句时**

**通过name查询，数据库会先到二级索引中去查到询该name所在的id，再根据id到聚集索引中去查到name=‘ARM’这一条具体数据。这一过程被称为** **回表查询。**

归根到底是因为，普通索引无法直接定位行记录。

所以，并不是创建完索引就完事的，在写 sql 语句的时候，尽量避免回表。

## 四、索引建立原则有哪些？ ##

**创建索引的命名规范**：idx\_user\_name，idx 是 index 的简写，后面跟表名 user 跟字段名 name

**创建唯一索引**需要加unique，命名规范：uniq\_idx\_user\_name，

**创建索引之前要做SQL性能分析**，查看服务器的状态信息、查看数据库的 增删改查的访问频率： SHOW GLOBAL STATUS LIKE 'Com\_\_\_\_\_\_\_'; ，**可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次**等。

## 五、哪些情况下索引会失效？ ##

*  索引使用最左前缀法则 如果索引了多列（联合索引)，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列如果跳跃某一列，索引将部分失效(后面的字段索引失效)。 （如果跳过最左边的列后面的索引就会失效）
 *  范围查询，联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效，如何规避：使用>=或<=
 *  字符串不加引号，字符串类型的字段使用时不加引号，索 引将会失效
 *  模糊查询，如果仅仅是尾部模糊匹配，索引不会失效，如果是头部模糊匹配，索引失效（"%工"）
 *  or连接的条件用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。只有两侧都有索引的时候所有索引才不会失效

## 六、explain 查看 sql 语句 type 说明 ##

表示连接类型，性能由好到差的连接类型为（system > const > eq\_ref > ref > range > index > all ），其中 system、const 只是理想类型，基本达不到。我们自己实际能优化到 ref、range 这两个类型，ref 级别基本达不到。要对 type 优化的前提是，需要有索引，如果你连索引都没有创建，那你就不用优化了，肯定是 all。

*  NULL（对正常的业务系统来说，不可能优化为 null），一般情况下不会为 NULL，只有在没有查询任何表的时候才会返回 NULL；

explain select 'Hello World'

*  system（**表中仅有一行**（相当于系统表），这是 const 联结类型的一个特例）
 *  const（表示通过索引一次就找到，const 用于比较 primary key 或者 unique 索引。**因为只匹配一行数据**，所以如果主键置于 where 列表中， mysql 能将该查询转换为一个常量。）
 *  eq\_ref（唯一性索引扫描，对于每一个索引键，表中只有一条记录与之匹配。常见于**唯一索引**或者**主键扫描**。）
 *  ref（**非唯一性的索引扫描**，返回匹配某个单独值得所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，可能会找多个符合条件的行，属于查找和扫描的混合体）
 *  range（使用索引返回一个范围中的行，**比如使用 between、<、>等的查询**。这种范围扫描索引扫描比全表扫描要好，因为它开始于索引的某一个点，而结束另一个点，不用全表扫描。）
 *  index（用了索引，但是扫描了全部的索引）
 *  all（查询全部数据，扫描全表，这一般比较糟糕，尽量避免）

注意：一般保证查询至少达到 range 级别，最好能达到 ref 级别。

## 六、平常遇到的一些总结 ##

#### 1、一个值只有0和1的情况，需不需要创建索引 ####

不需要

#### 2、经常删除操作的sql，对索引有没有影响 ####

会有影响，insert 开销会比较大。delete 开销稍微小一点，但是删除大批量的数据会有很大影响。update 操作除非修改的是索引字段的值，不然对索引没有影响。

## 七、参考文档 ##

*  [聚簇索引与非聚簇索引概念][Link 1]
 *  [Mysql 索引总结][Mysql]
 *  [索引的缺点（3）：降低增删改的性能][3]

[d6da1073f954a05bde52f074711b7cae.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/6b9bf594215b42c483c9efaa6c39d8e1.png
[92b24a753a0c55cd903253906b3f4d2b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/a25770805f2146bcbed47883e5c3dd16.png
[22b036fecf1570b8695dc0634eb8e575.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/24ac6d78ccac4d7a8073655938e06f57.png
[23de467e555652d2dc8ec6e7fa92cd87.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/aada229759ac4e2f9d7480747fba9756.png
[ae370b794efba78fd20554650413994d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/7a30eb49f13e4c4893cd629670a623d6.png
[Link 1]: https://www.cnblogs.com/gaopengpy/p/15977479.html
[Mysql]: https://blog.csdn.net/weixin_43889841/article/details/117299867
[3]: https://blog.csdn.net/sqlserverdiscovery/article/details/79129776