mysql索引笔记

a未来永远是个未知数

已于 2024-07-08 15:55:16 修改

阅读量630

点赞数 11

分类专栏：数据库 # mysql 文章标签： mysql 笔记数据库

于 2024-07-08 11:05:30 首次发布

本文链接：https://blog.csdn.net/qq_43654518/article/details/140261240

版权

数据库同时被 2 个专栏收录

8 篇文章 0 订阅

订阅专栏

mysql

7 篇文章 0 订阅

订阅专栏

这里想整理一下性能优化中用到的东西，先整理一下优化mysql索引中所查阅到的资料吧。

MySQL索引类型详解

索引的类型和存储引擎有关，每种存储引擎所支持的索引类型不一定完全相同。MySQL 索引可以从存储方式、逻辑角度和实际使用的角度来进行分类。

存储方式区分

根据存储方式的不同，MySQL 中常用的索引在物理上分为 B+树索引和 HASH 索引两类，两种不同类型的索引各有其不同的适用范围。

1.B+树索引

B+树索引又称为 BTREE 索引，目前大部分的索引都是采用 B+树索引来存储的。
MySQL中，索引是在存储引擎层实现的，不同的存储引擎支持的索引类型不同，对索引的组织实现方式也不同。我们平时最常使用的是B+树索引，B+树是为磁盘或其他存取设备设计的一种平衡查找树，所有记录节点按照键值大小顺序存放在同一层的叶节点上，各叶节点通过指针进行链接

B+树索引是一个典型的数据结构，其包含的组件主要有以下几个：

叶子节点：包含的条目直接指向表里的数据行。叶子节点之间彼此相连，一个叶子节点有一个指向下一个叶子节点的指针。
分支节点：包含的条目指向索引里其他的分支节点或者叶子节点。
根节点：一个 B+树索引只有一个根节点，实际上就是位于树的最顶端的分支节点。

基于这种树形数据结构，表中的每一行都会在索引上有一个对应值。因此，在表中进行数据查询时，可以根据索引值一步一步定位到数据所在的行。

其基本特征如下：

非叶节点只存关键字以及索引下一层节点的指针
所有叶节点在同一层，包含全部关键字和指向记录的指针，并且按照关键字从小到大顺序链接

可以看到相比一般二叉树，B+树的单个节点能存储更多信息，减少了磁盘 IO 的次数，从而提升了查找速度，而且叶节点形成有序链表，非常适合进行范围查询。

B+树索引可以进行全键值、键值范围和键值前缀查询，也可以对查询结果进行 ORDER BY 排序。但 B+树索引必须遵循左边前缀原则，要考虑以下几点约束：

查询必须从索引的最左边的列开始。
查询不能跳过某一索引列，必须按照从左到右的顺序进行匹配。
存储引擎不能使用索引中范围条件右边的列。

2.哈希索引

哈希（Hash）一般翻译为“散列”，也有直接音译成“哈希”的，就是把任意长度的输入（又叫作预映射，pre-image）通过散列算法变换成固定长度的输出，该输出就是散列值。

哈希索引也称为散列索引或 HASH 索引。MySQL 目前仅有 MEMORY 存储引擎和 HEAP 存储引擎支持这类索引。其中，MEMORY 存储引擎可以支持 B+树索引和 HASH 索引，且将 HASH 当成默认索引。

HASH 索引不是基于树形的数据结构查找数据，而是根据索引列对应的哈希值的方法获取表的记录行。哈希索引的最大特点是访问速度快，但也存在下面的一些缺点：

MySQL 需要读取表中索引列的值来参与散列计算，散列计算是一个比较耗时的操作。也就是说，相对于 B+树索引来说，建立哈希索引会耗费更多的时间。
不能使用 HASH 索引排序。
HASH 索引只支持等值比较，如“=”“IN()”或“”。
HASH 索引不支持键的部分匹配，因为在计算 HASH 值的时候是通过整个索引值来计算的。

逻辑区分

根据索引的具体用途，MySQL 中的索引在逻辑上分为以下 5 类：

1.普通索引

普通索引是 MySQL 中最基本的索引类型，它没有任何限制，唯一任务就是加快系统对数据的访问速度。
普通索引允许在定义索引的列中插入重复值和空值。
创建普通索引时，通常使用的关键字是 INDEX 或 KEY。

例 1
下面在 tb表中的 id 字段上建立名为 index_id 的索引。

CREATE INDEX index_id ON tb(id);

2. 唯一索引

唯一索引与普通索引类似，不同的是创建唯一性索引的目的不是为了提高访问速度，而是为了避免数据出现重复。
唯一索引列的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯一。
创建唯一索引通常使用 UNIQUE 关键字。

例 2
下面在 tb 表中的 id 字段上建立名为 index_id 的索引，SQL 语句如下：

CREATE UNIQUE INDEX index_id ON tb(id);

3. 主键索引

顾名思义，主键索引就是专门为主键字段创建的索引，也属于索引的一种。
主键索引是一种特殊的唯一索引，不允许值重复或者值为空。
创建主键索引通常使用 PRIMARY KEY 关键字。不能使用 CREATE INDEX 语句创建主键索引。

4. 空间索引

空间索引是对空间数据类型的字段建立的索引，使用 SPATIAL 关键字进行扩展。
创建空间索引的列必须将其声明为 NOT NULL，空间索引只能在存储引擎为 MyISAM 的表中创建。
空间索引主要用于地理空间数据类型 GEOMETRY。对于初学者来说，这类索引很少会用到。

例 3
下面在 tb 表中的 line 字段上建立名为 index_line 的索引，SQL 语句如下：

CREATE SPATIAL INDEX index_line ON tb(line);

其中，tb 表的存储引擎必须是 MyISAM，line 字段必须为空间数据类型，而且是非空的。

5. 全文索引

全文索引主要用来查找文本中的关键字，只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。
全文索引允许在索引列中插入重复值和空值。
不过对于大容量的数据表，生成全文索引非常消耗时间和硬盘空间。
创建全文索引使用 FULLTEXT 关键字。

例 4
在 tb 表中的 info 字段上建立名为 index_info 的全文索引，SQL 语句如下：

CREATE FULLTEXT INDEX index_info ON tb(info);

其中，index_info 的存储引擎必须是 MyISAM，info 字段必须是 CHAR、VARCHAR 和 TEXT 等类型。

实际使用区分

索引在逻辑上分为以上 5 类，但在实际使用中，索引通常被创建成单列索引和组合索引。

1. 单列索引

单列索引就是索引只包含原表的一个列。在表中的单个字段上创建索引，单列索引只根据该字段进行索引。
单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。

例 5
下面在 tb 表中的 address 字段上建立名为 index_addr 的单列索引，address 字段的数据类型为 VARCHAR(20)，索引的数据类型为 CHAR(5)。SQL 语句如下：

CREATE INDEX index_addr ON tb(address(5));

这样，查询时可以只查询 address 字段的前 5 个字符，而不需要全部查询。

2. 多列索引

组合索引也称为复合索引或多列索引。相对于单列索引来说，组合索引是将原表的多个列共同组成一个索引。多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。

例如，在表中的 id、name 和 sex 字段上建立一个多列索引，那么，只有查询条件使用了 id 字段时，该索引才会被使用。

例 6
下面在 tb 表中的 name 和 address 字段上建立名为 index_na 的索引，SQL 语句如下：

CREATE INDEX index_na ON tb(name,address);

该索引创建好了以后，查询条件中必须有 name 字段才能使用索引。

提示：一个表可以有多个单列索引，但这些索引不是组合索引。一个组合索引实质上为表的查询提供了多个索引，以此来加快查询速度。比如，在一个表中创建了一个组合索引(c1，c2，c3)，在实际查询中，系统用来实际加速的索引有三个：单个索引(c1)、双列索引(c1，c2)和多列索引(c1，c2，c3)。

索引的优缺点主要体现在：

优势：可以快速检索，减少I/O次数，加快检索速度；根据索引分组和排序，可以加快分组和排序；
劣势：索引本身也是表，因此会占用存储空间，一般来说，索引表占用的空间的数据表的1.5倍；索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大；构建索引会降低数据表的修改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表；

多表联查如何建立索引

在 MySQL 数据库中，设计索引主要是为了提高查询的效率，降低数据库的压力。当我们进行多表查询时，正确设计索引非常重要。

具体方法与建议

为连接列创建索引：在多表查询中，连接列通常是性能瓶颈。为这些列创建索引可以显著提高查询性能。
考虑表的关联顺序：在JOIN语句中，表的顺序可能会影响性能。通常，你应该从具有最小数量的唯一行的表开始，然后逐步添加其他表。
考虑使用覆盖索引：如果查询只涉及某些列，并且这些列在索引中包含了所有需要的值，那么可以使用覆盖索引来提高性能。
考虑联合索引：如果你有多个列在查询中经常一起出现，那么可以考虑创建联合索引。
不要过度索引：虽然索引可以提高性能，但是过多的索引也会增加存储空间的开销，并可能降低插入、更新和删除操作的性能。因此，要平衡索引的使用。

原则与建议

理解数据和查询：在设计索引之前，首先需要理解你的数据和查询。知道哪些列经常被用于 WHERE，ORDER BY，GROUP BY 等子句，以及哪些列经常被 JOIN。这些列可能需要被索引。
单列索引：如果某一列经常被独立用于搜索，那么可以为其创建单列索引。例如，如果经常在 user 表上通过 email 列进行搜索，那么可以为 email 列创建索引。
复合索引：如果有多个列经常一起被用于搜索，那么可以为这些列创建复合索引。复合索引中列的顺序对性能有很大影响。在复索引中，索引的顺序应该是：最常用于搜索条件的列（高选择性）放在前面，不经常用于搜索条件的列（低选择性）放在后面。
覆盖索引：如果一个查询可以通过使用一个索引获取所有的信息，那么这个索引被称为覆盖索引。覆盖索引可以大大提高查询性能，因为 MySQL 可以只通过索引就获取所有需要的信息，而无需回表。
利用索引合并：MySQL 可以在一次查询中使用多个索引，这被称为索引合并。索引合并可以优化复杂的查询条件，但并不总是最佳选择。如果可能，应该尝试创建一个复合索引来替代索引合并。
避免全表扫描：设计索引的目的之一是避免全表扫描。全表扫描非常低效，应该尽量避免。

这些原则可以帮助你设计索引，但是具体的索引策略还需要根据你的具体情况进行调整。不同的数据、查询和硬件可能需要不同的索引策略。

下面是一个具体的实验步骤，可以参考进行操作：

创建测试数据库和测试表，插入一些测试数据。
执行你的查询，记录查询的时间和性能。
创建一些索引，然后再次执行你的查询，比较查询的时间和性能。
调整索引（例如，改变复合索引的列的顺序，添加或删除某些索引），然后再次执行你的查询，比较查询的时间和性能。
通过比较查询的时间和性能，找出最佳的索引策略。

索引优化分析

可以通过索引来优化查询语句的执行效率。MySQL 中，可以使用 EXPLAIN 命令来查看查询语句的执行计划，进而优化查询。如果查询语句没有使用索引，可以考虑添加索引或者修改查询语句的条件，使其能够利用索引来加快查询速度。

需要注意的是，虽然索引可以加快查询速度，但是过多的索引也会影响数据库的性能，因为索引需要占用存储空间，并且在修改表数据时也会增加操作的复杂度。因此，在创建索引时需要根据实际情况进行选择和权衡，避免过度使用索引。

索引的优化是非常必要的，因为索引可以极大地提高数据库的查询效率，特别是对于大量数据的表。在建立索引时，需要权衡利弊。一般来说，对于经常被查询、查询效率需要提高的列，可以建立索引；而对于不经常被查询的列，或者存储空间比较紧张的情况下，可以考虑不建立索引。同时，可以考虑对于一些查询频繁但数据更新较少的列建立索引，并定期进行索引维护来保证查询效率。因此，正确的创建和使用索引是实现高性能查询的基础。

尽量避免负向查询

负向查询指的是在查询中使用不等于（<>）或不包含（NOT IN、NOT EXISTS等）的条件，即查询不满足某些条件的记录。负向查询通常会导致数据库执行全表扫描，影响查询性能。

避免使用select *

查询时尽量不要使用select *，而是只查出需要的字段，因为select * 无法利用覆盖索引优化，还会为服务器带来额外的IO、内存和cpu的消耗

避免创建冗余索引

在数据库中，创建过多的索引会导致查询性能下降、插入/更新/删除操作变慢等问题，而创建冗余索引则是其中一种常见的问题。冗余索引指的是已经存在一条索引可以满足查询条件，但是又创建了另一条重复的索引。这种索引不仅浪费存储空间，还会使得数据库维护索引的代价更大，影响数据库性能。

避免创建冗余索引的方法包括：

仔细分析查询需求，只创建必要的索引。
定期检查数据库中的索引，及时删除冗余的索引。
尽量避免创建覆盖索引，因为它可能包含多个不必要的字段。

需要注意的是，索引的设计并不是一成不变的，需要根据具体的业务需求和数据特征不断进行调整和优化。

如何防止你的索引失效

使用多列作为索引，需要遵循最左前缀匹配原则（查询从索引的最左前列开始并且不跳过索引中的列）
不在索引列上做任何操作，例如：计算、函数、自动or手动的类型转换，会导致索引失效而转向全表扫描
如果你对列进行了（+，-，*，/，！）、函数、or运算，那么都将不会走索引
尽量使用索引覆盖（只访问索引列的查询），减少select * ，覆盖索引能减少回表次数
mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描
like以通配符开头（%abc），mysql索引会失效变成全表扫描的操作
字符串不加单引号会导致索引失效（可能发生了索引列的隐式转换）
例如 select * from tb where name = name;