mysql优化之索引查询优化：原理与原则

terry 2年前 (2023-09-26) 阅读数 44 #数据库

关于索引的更多内容，分以下几点来讲解：

1.索引概述（什么是索引，索引的优点和缺点）

2. 索引基本用法（创建索引）

3. 索引基础知识（访谈重点）

4. 索引数据结构（B 树、哈希）

5. 创建索引原理（重中之重，面试必问！收藏！）

6.删除百万级以上数据

1.索引概述

1）什么是索引？

索引是特殊文件（InnoDB数据表中的索引是表空间的一部分）。它们包含对数据表中所有记录的引用。用更通俗的话说，索引相当于目录。当您使用新华字典时，目录将会被删除。如果你查以某个单词开头的成语，你只能从第一页到第一千页。疲劳的！将文件夹发送回给您，以便您可以快速找到它！

2）索引的优缺点：

可以显着加快数据检索速度，这也是创建索引的主要原因。，并且通过使用索引，可以在查询过程中使用优化隐藏器来提高系统性能。但索引也有缺点：索引需要额外的维护成本；由于索引文件是一个独立的文件，添加、修改、删除数据都会导致对索引文件进行额外的操作，而这些操作需要额外的IO。，添加/修改/删除的执行效率会降低。？ TABLE用于创建普通索引、UNIQUE索引或PRIMARY KEY索引。

Table_name是要索引的表名，column_list表示要索引哪些列，如果有多列，用逗号分隔。

您可以自己给索引名称index_name起一个名字。默认情况下，MySQL 根据第一个索引列分配名称。此外，ALTER TABLE 允许在单个语句中更改多个表，从而允许同时创建多个索引。

第三种方式：使用CREATE INDEX命令创建

CREATE INDEX可以为表添加普通索引或者UNIQUE索引。（但是无法创建PRIMARY KEY索引）

3.索引基础知识（我不想像其他文章一样废话）

索引用于快速查找具有特定值的记录。如果没有索引，执行查询时通常会遍历整个表。

索引原理很简单：将无序数据转换为有序查询

1。对创建索引的列的内容进行排序

2。为排序后的结果生成倒排表

3。将数据地址链添加到倒排表的内容中

4。运行查询时，必须首先检索倒排表的内容，然后删除数据地址链以检索特定数据

4。索引数据结构（b 树、哈希）

1）B 树索引

mysql 通过存储引擎检索数据。基本上90%的人都使用InnoDB。从实现方式来看，目前InnoDB的索引类型只有两种。类型：BTREE（B树）索引和HASH索引。 B树索引是Mysql数据库中最常用的索引类型，基本上所有的存储引擎都支持B树索引。通常我们所说的索引是指（B树）索引（实际上是用B+树实现的，因为查看表索引时mysql总是打印BTREE，所以称为B树索引）

查询方法：

主键索引区：PI（关联存储数据的地址），通过主键查询，

通用索引区：si（关联ID的地址，然后范围上述地址）。因此，通过主键查询是最快的

B+树属性：

1。）n个子树的节点包含n个关键字，这些关键字不是用来存储数据的，而是用来索引数据形式的。

2.) 所有叶节点包含有关所有关键字的信息以及对包含这些关键字的记录的引用，并且叶节点本身根据关键字的大小按从最小到最大的顺序链接。

3.）所有非终端节点都可以视为索引成员，并且该节点仅包含其子树中最大（或最小）的关键字。

4.）在B+树中，数据对象的插入和删除只在叶子节点上进行。

5.）B+树有2个主指针，一个是树的根节点，另一个是最小键的叶节点。

2）哈希索引

简而言之，类似于数据结构上简单实现的HASH表（哈希表），我们在mysql中使用哈希索引时，主要是通过哈希算法来完成的（常见的是使用的哈希算法有直接寻址法、平方居中法、折叠法、除数余数法、随机数法），将数据库字段数据转换成定长的哈希值，与该行指针一起存入哈希表。数据。对应位置；如果发生Hash冲突（两个不同关键字的Hash值相同），则以链表的形式存储在对应的Hash键下。显然这只是一个简化的模拟图。

ps：关于数据结构，有兴趣了解更多的朋友可以关注我，看看专题【数据结构】。我这里就不详细解释了。

5。索引创建原则（最优先）

虽然索引很好，但它们的使用并不是无限制的。最好遵守以下原则

1）最左前缀匹配原则，组合索引一个很重要的原则，mysql一直向右匹配，直到遇到范围查询（>、 3 and d = 4。 if (a,按照b,c,d顺序索引)，d不用于索引。如果创建 (a, b, d, c) 的索引，则可以使用它。 a、b、d的顺序可以任意调整。？未知，最多三种，区分度太低）

5）尽可能扩展索引，不要创建新索引。比如表中已经有a的索引，现在想添加(a,b)的索引，那么只需调整原来的索引即可。

6) 使用外键定义的数据列必须建立索引。

7）不要为很少涉及查询的列和重复值较多的列创建索引。

8) 不要为定义为文本、图像和位数据类型的列创建索引。

删除百万级别以上的数据

关于索引：由于索引需要额外的维护成本（因为索引文件是一个单独的文件），如果我们添加、更改或删除数据，我们会产生额外的成本发生。对索引文件的操作需要额外的IO，会降低添加/修改/删除的执行效率。因此，当我们删除数据库中的数百万数据时，我们查阅MySQL官方手册得知，数据删除的速度与创建的索引数量成正比。