MySQL数据库（五）索引

一索引概述

1 介绍：MySQL索引是一种有序数据结构，它能够高效帮助数据库系统快速定位到表中的特定记录，从而显著提高查询效率。索引可以被看作是书的目录，通过它可以迅速找到所需的信息而不需要逐页翻阅整本书。

2 优缺点

二索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种：

兼容性

数据结构可视化网站

Data Structure Visualization (usfca.edu)

三索引分类

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种

回表查询

回表查询：先根据二级索引查找到目标行，但是没有所需要的字段，那么首先定位到主键再对数据库进行查询从而得到需要的字段信息

四索引语法

五 SQL性能分析

执行频率

代码实现

sql"># 查询的是整个数据库的各种功能的查询次数
show global status like 'Com_______';

慢查询日志

profile详情

explain执行计划

六索引使用

1 最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。

最左前缀法则指的是查询过程从索引的最左列开始，并且不跳过索引中的列。

如果跳跃某一列，索引将部分失效（后面的字段索引失效）。

同时：

2 范围查询

联合查询中，出现范围查询（>,<）范围查询右侧的列索引失效

在业务允许的条件下尽量使用>=,<=

3 索引列运算

不要在索引列上进行运算操作，否则索引将失效。

4 字符串不加引号

字符串类型字段使用时，不加引号，索引将失效。

5 模糊查询

如果是对尾部进行模糊匹配，索引不会失效，如果是对头部进行模糊匹配，索引失效。

6 or连接的条件

用 or 分隔开的条件，如果 or 不是两侧都有索引，那么涉及的索引都不会被用到。

只有两侧都有索引的时候，索引才会生效。

7 数据分布影响

如果MySQL评估使用索引比全表更慢，则不使用索引。

8 SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

联合索引和单列索引(不指定)

建议

忽略

强制

9 覆盖索引

覆盖索引（Covering Index），也称为索引覆盖，是数据库查询优化中一个非常重要的概念。它指的是当一个索引包含了查询所需的所有字段时，MySQL可以直接从这个索引中获取所有需要的数据，而不需要再去访问数据表中的实际行记录，从而避免了回表操作（即通过主键索引再次查找数据的过程）

覆盖索引的工作原理

在InnoDB存储引擎中，每个表都有一个聚簇索引（Clustered Index），通常是主键索引。除了聚簇索引之外的所有其他索引都被称为非聚簇索引或二级索引（Secondary Index）。在二级索引的叶子节点中，除了存储索引列的值外，还会存储对应行的主键值。当执行查询时，如果所需的全部数据都在二级索引中，则无需再通过主键去查找完整的行记录，这就是所谓的覆盖索引。

profession,age,status属于联合索引（二级索引） id 属于主键索引（聚簇索引）

所以查找到一个就可以直接查询检索到其他的，而name属于另一个二级索引，需要回表查询（先

根据前面的联合查询结果得到对应的id值再利用id值查询对应的行如何查找name）。

10 前缀索引

当字段类型为字符串，有时候需要索引很长的字符串，这回让索引变的很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀建立索引，这样可以大大节约索引空间，从而提高索引检索效率。

前缀索引的基本思想是通过对字段值的前n个字符建立索引，以此来减少索引数据量。

前缀索引特别适用于那些列值较长且前几个字符就足以区分大多数记录的情况。例如，对于姓名、电子邮件地址或URL等字段，前缀索引可能会非常有用。但是，对于像身份证号码这样的字段，由于前几位数字往往相同，使用前缀索引可能不是最佳选择

代码实现

潜在问题

但是如果查找的结果为多个引擎会重新匹配

11 索引使用

单列索引与联合索引

单列索引：一个索引只包含单个列

联合索引：一个索引包含多个列

单列索引

这种情况下就会出现回表查询，先根据索引查询到phone 再回表根据id查找到id值对应的这一行再检查name是否正确（这种情况下使用联合查询更好）

联合查询

联合索引则是在多个列上创建的索引，它能够覆盖多列上的查询需求。联合索引的优势在于它可以同时加速涉及这些列的查询，并且遵循所谓的“最左前缀原则”。

七索引设计原则

1 针对数据量较大且查询频繁的表建立索引，对于这样的表合理地创建索引可以显著提高查询性能。确保索引能够覆盖最常见的查询模式，这有助于快速定位所需的数据行，减少不必要的全表扫描。
2 针对常作为查询条件（WHERE）、排序（ORDER BY）、分组（GROUP BY）操作的字段建立索引，通常在WHERE子句中使用的字段是理想的索引候选者。这些字段上的索引可以显著减少查询时扫描的数据行数。经常用于ORDER BY和GROUP BY的字段也应该建立索引，以加速排序和分组操作，这样可以避免数据库执行额外的排序步骤，提升查询效率。
3 尽量选择区分度高的列作为索引，尽量建立唯一索引。选择那些具有高基数即不同值的比例较高的列来创建索引，这样可以使得查询更加高效。例如使用身份证号码作为索引比使用性别作为索引要好得多。尽量建立唯一索引不仅能保证数据的唯一性，还能提高查询效率，因为它可以直接定位到特定的记录而不需要进一步检查。
4 如果是字符串类型的字段，字段的长度较长，可以针对字段的特点，建立前缀索引。对于长字符串类型的字段，考虑只对前缀建立索引来减少索引大小并提高查询效率。例如如果经常查询姓名的前几个字符，可以创建一个前缀索引，这样可以在不影响查询准确性的同时减小索引的存储空间。
5 尽量使用联合索引，减少单列索引。查询时联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。通过合理安排列的顺序通常将区分度最高的列放在前面，可以使联合索引更加有效地服务于多种查询需求，从而提高查询速度和效率。
6 控制索引的数量，索引并不是多多益善，索引越多维护索引结构的代价也就越大，会影响增删改的效率。定期评估索引的有效性，移除那些不再需要或者极少使用的索引，以减轻维护负担并节省存储空间。保持适量的索引是数据库优化的关键之一。
7 处理NULL值，如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询，这对于优化查询计划至关重要。
8 避免索引列参与计算或函数调用，在查询语句中如果索引列被函数或者表达式包裹，则MySQL优化器可能无法使用该索引，导致全表扫描。因此应该尽可能保持索引列“干净”，直接基于原始值进行比较而不是经过任何转换或计算。
9 注意LIKE模糊查询，在进行LIKE查询时如果模式是以通配符开头如'%abc'，则索引不会被使用。为了利用索引，应该尽量避免这样的查询方式，优先考虑从左侧开始匹配的模式，比如'abc%'，这样可以充分利用索引的优势。
10 定期评估和调整索引策略，随着业务的发展和数据的变化，索引策略也需要相应地进行调整和优化。定期评估索引的有效性，并根据实际查询需求进行调整，确保索引设计始终符合当前的查询模式和数据分布情况。这样可以持续保持数据库的最佳性能状态。