《扫一眼系列之MySQL索引》

最新推荐文章于 2024-10-18 18:39:07 发布

流动红旗

最新推荐文章于 2024-10-18 18:39:07 发布

阅读量466

点赞数 1

分类专栏：扫一眼文章标签： mysql 索引

本文链接：https://blog.csdn.net/weixin_44096631/article/details/109348079

版权

扫一眼专栏收录该内容

2 篇文章 0 订阅

订阅专栏

来来来一起扫一眼！！

前言

MySQL索引想必大家都很熟悉了，不过没事er也要扫一眼

Tips:本文所有内容均来源于网络。用于整理学习使用。

什么是MySQL索引？

维基百科定义：数据库索引是数据库管理系统中的一个排序结构，以协助快速查询更新数据库表中数据。

我们可以这么理解：就像我们从一本 500 页的书里面去找特定的一小节的内容，肯定不可能从第一页开始翻。那

么这本书有专门的目录，它可能只有几页的内容，它是按页码来组织的，可以根据拼音或者偏旁部首来查找，只要

确定内容对应的页码，就能很快地找到我们想要的内容。

MySQL 中存储引擎使用类似的方式进行查询，先去索引中查找对应的值，然后根据匹配的索引找到对应的数据行。

使用索引有什么好处？

提高数据的检索速度，降低数据库IO成本：使用索引的意义就是通过缩小表中需要查询的记录的数目从而加快搜索的速度。
降低数据排序的成本，降低CPU消耗：索引之所以查的快，是因为先将数据排好序，若该字段正好需要排序，则正好降低了排序的成本。

换而言之

索引大大减少了服务器需要扫描的数据量。
索引可以帮助服务器避免排序和临时表
索引可以将随机IO变为顺序IO

使用索引带来的坏处？

占用存储空间：索引其实也是一张表，记录了主键和索引字段，一般以索引文件的形式存储在磁盘上
降低更新表的速度：表数据发生了变化，对应的索引也要跟着一起变化，从而降低更新速度；如果不更新索引，可能索引对应的物理数据时不正确的。

索引的使用场景是什么？

对非常小的表，大部分情况下全表扫描效率更高。
对中大型表，索引非常有效。
特大型的表，建立和使用索引的代价随着增长，可以使用分区分库分表等技术来解决。目前的解决方案有 Sharding Sphere、MyCAT 等等

索引并不总是最好的工具。总的来说，只有索引能够帮助存储引擎快速查找到记录带来的好处大于其所带来的额外工作时，索引才是有效的。

索引的类型都有哪些呢？

索引都实现在存储引擎层，主要有如下六种类型：

普通索引：最基本的索引，没有任何约束。
唯一索引：与普通索引类似，但具有唯一性约束。
主键索引：特殊的唯一索引，不允许有空值。
复合索引：将多个列组合在一起创建索引，可以覆盖多个列。
外键索引：只有InnoDB类型的表才可以使用外键索引，保证数据的一致性、完整性和实现级联操作。
全文索引：MySQL 自带的全文索引只能用于 InnoDB、MyISAM ，并且只能对英文进行全文检索，一般使用全文索引引擎。

常用的全文索引引擎的解决方案有 Elasticsearch、Solr 等等。最为常用的是 ES

MySQL索引的创建原则是什么？

最适合索引的列是出现在 WHERE 子句中的列，或连接子句中的列，而不是出现在 SELECT 关键字后的列。
索引列的基数越大，索引效果越好。

MySQL索引基数理解相对简单
根据情况创建复合索引，复合索引可以提高查询效率。

因为复合索引的基数会更大。
避免创建过多的索引，索引会额外占用磁盘空间，降低写操作效率。
主键尽可能选择较短的数据类型，可以有效减少索引的磁盘占用提高查询效率
对字符串进行索引，应该定制一个前缀长度，可以节省大量的索引空间。

MySQL 索引的使用注意事项是什么？

应尽量避免在 WHERE 子句中使用 != 或 <> 操作符，否则引擎将放弃使用索引而进行全表扫描。优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。
应尽量避免在 WHERE 子句中使用 OR 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：SELECT id FROM t WHERE num = 10 OR num = 20 。
应尽量避免在 WHERE 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。
应尽量避免在 WHERE 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。
不要在 WHERE 子句中的 = 左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
复合索引遵循前缀原则。
如果 MySQL 评估使用索引比全表扫描更慢，MySQL会放弃使用索引。如果此时你想要索引，可以在语句中添加强制索引。
列类型是字符串类型，查询时一定要给值加引号，否则索引失效。
LIKE 查询，% 不能在前，因为无法使用索引。如果需要模糊匹配，可以使用全文索引。

like “value%” 可以使用索引，但是对于 like “%value%” 这样的方式，执行全表查询

想知道一个查询用到了哪个索引，如何查看?

在 SELECT 语句前加上 EXPLAIN

`EXPLAIN` 字段详解如下：

explain select * from table where name = 'chedx';

概要描述：
id:选择标识符
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明

Mysql Explain 详解详细的可以浏览博客

MySQL索引原理大全

MySQL索引背后的数据结构及算法原理
MySQL索引原理
深入理解MySQL索引原理和实现——为什么索引可以加速查询？

很好的几篇文章。

MySQL都有哪些索引？

B-Tree 索引。
Hash 索引。

实际场景下我们仅仅使用B-Tree索引。

什么是B-Tree索引呢？

B-Tree 是为磁盘等外存储设备设计的一种平衡查找树。

系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。
InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB 存储引擎中默认每个页的大小为 16 KB，可通过参数 innodb_page_size 将页的大小设置为 4K、8K、16K ，在 MySQL 中可通过如下命令查看页的大小：
```
mysql> show variables like 'innodb_page_size';
```
而系统一个磁盘块的存储空间往往没有这么大，因此 InnoDB 每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小 16KB 。InnoDB 在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘 I/O 次数，提高查询效率。

B-Tree结构，去找找博客，这里不再多说。

什么是B+Tree索引呢？

B+Tree 是在 B-Tree 基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用 B+Tree 实现其索引结构。

在通过查看博客了解完B-Tree以后呢， B-Tree 结构图中可以看到，每个节点中不仅包含数据的 key 值，还有 data 值。而每一个页的存储空间是有限的，如果 data 数据较大时将会导致每个节点（即一个页）能存储的 key 的数量很小，当存储的数据量很大时同样会导致 B-Tree 的深度较大，增大查询时的磁盘 I/O 次数，进而影响查询效率。在 B+Tree 中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储 key 值信息，这样可以大大加大每个节点存储的 key 值数量，降低 B+Tree 的高度。

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

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

B+Tree的图可以网上找一下。

通常在 B+Tree 上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对 B+Tree 进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

可能上面例子中只有 22 条数据记录，看不出 B+Tree 的优点，下面做一个推算：

InnoDB 存储引擎中页的大小为 16KB，一般表的主键类型为 INT（占用4个字节）或 BIGINT（占用8个字节），指针类型也一般为 4 或 8 个字节，也就是说一个页（B+Tree 中的一个节点）中大概存储 16KB/(8B+8B)=1K 个键值（因为是估值，为方便计算，这里的 K 取值为〖10〗^3）。也就是说一个深度为 3 的 B+Tree 索引可以维护10^3 10^3 10^3 = 10亿条记录。
实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree 的高度一般都在 2~4 层。MySQL 的 InnoDB 存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要 1~3 次磁盘 I/O 操作。

B+Tree有哪些索引类型呢？

在 B+Tree 中，根据叶子节点的内容，索引类型分为主键索引和非主键索引。

主键索引的叶子节点存的数据是整行数据( 即具体数据 )。在 InnoDB 里，主键索引也被称为聚集索引（clustered index）。
非主键索引的叶子节点存的数据是整行数据的主键，键值是索引。在 InnoDB 里，非主键索引也被称为辅助索引（secondary index）。

二级索引的叶节点存储的是主键值，而不是行指针，这是为了减少当出现行移动或数据页分裂时二级索引的维护工作，但会让二级索引占用更多的空间。

辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，需要进过两步：

首先，InnoDB 存储引擎会遍历辅助索引找到主键。
然后，再通过主键在聚集索引中找到完整的行记录数据。

另外，InnoDB 通过主键聚簇数据，如果没有定义主键，会选择一个唯一的非空索引代替，如果没有这样的索引，会隐式定义个主键作为聚簇索引。

使用聚集索引应当注意的方面有哪些？

插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于 InnoDB 表，我们一般都会定义一个自增的 ID 列为主键。
更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB 表，我们一般定义主键为不可更新。

MySQL默认是可以更新主键的；MongoDB是不可以更新主键的。

二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。 回表

当然，有一种情况可以无需二次查找，基于非主键索引查询，但是查询字段只有主键 ID ，那么在二级索引中就可以查找到

主键 ID 建议使用整型。因为，每个主键索引的 B+Tree 节点的键值可以存储更多主键 ID ，每个非主键索引的 B+Tree 节点的数据可以存储更多主键 ID 。

什么是索引的最左匹配原则？

当 B+Tree 的数据项是复合的数据结构，比如索引 (name, age, sex) 的时候，B+Tree 是按照从左到右的顺序来建立搜索树的。

比如当 (张三, 20, F) 这样的数据来检索的时候，B+Tree 会优先比较 name 来确定下一步的所搜方向，如果 name 相同再依次比较 age 和 sex ，最后得到检索的数据。
但当 (20, F) 这样的没有 name 的数据来的时候，B+Tree 就不知道下一步该查哪个节点，因为建立搜索树的时候 name 就是第一个比较因子，必须要先根据 name 来搜索才能知道下一步去哪里查询。
比如当 (张三, F) 这样的数据来检索时，B+Tree 可以用 name 来指定搜索方向，但下一个字段 age 的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是 F 的数据了。

这个是非常重要的性质，即索引的最左匹配特性。