您的位置：首页 > 财经 > 产业 > 望野赏析_安康市有没有滴滴平台_网址查询工具_长春网络优化最好的公司

望野赏析_安康市有没有滴滴平台_网址查询工具_长春网络优化最好的公司

2024/11/16 7:23:50 来源：https://blog.csdn.net/xiaokuer_/article/details/142793846  浏览:  次   关键词：望野赏析_安康市有没有滴滴平台_网址查询工具_长春网络优化最好的公司
望野赏析_安康市有没有滴滴平台_网址查询工具_长春网络优化最好的公司

文章目录

  • 索引
    • 索引的数据结构
    • 索引的具体结构
      • 页的概念
      • 独立表空间文件
      • 页的结构
        • 页文件头和页文件尾
        • 页主体
        • 页目录
        • 数据页头
    • B+树在索引中的应用
    • 索引分类
      • 按数据结构分
      • 按字段特性分
      • 按物理存储分
      • 按字段个数分
    • 索引语法
      • 创建索引
      • 查看索引
      • 删除索引
    • 索引优化
      • SQL执行频率
      • 慢查询日志
      • profile详情
      • explain执行计划
    • 索引使用
      • SQL提示
      • 回表查询
      • 索引覆盖
    • 索引失效
      • 最左前缀法则
      • 范围查询
      • 索引列运算
      • 字符串不使用引号
      • 模糊匹配
      • OR分隔
      • 数据分布影响
    • 索引设计原则

索引是非常重要且知识点非常多的一个章节，希望大家能够耐心看完，一定会有收获！

索引

MySQL的索引是一种数据结构，是用于加速数据检索的重要工具。索引通过一定的规则排列数据表中的记录，使得对表的查询可以通过索引来提高效率。

事务保证了MySQL数据的安全，索引提高了MySQL数据检索的效率。

本文讨论除非特别说明，否则均默认采用InnoDB存储引擎

索引的数据结构

索引主要是用于加速查询的，那么它到底是哪种数据结构呢？

【hash表】

hash表是查询速度非常快的数据结构，查询的时间复杂度为O(1)，但由于hash不支持范围查询和排序，所以没有被采用。

【二叉搜索树】

普通的二叉搜索树可能会出现单边树的情况，此时查询的时间复杂度为O(N)

AVL和红黑树虽然能保证平衡，但本质上还是一棵二叉树，当数据量特别大时，会导致树高过高，此时的查询难以避免地要访问很多结点，每次访问结点对应一次磁盘IO，大量的磁盘IO导致性能下降。

【B树】

B树相比二叉搜索树，确实可以优化树高，但B树的每个结点都会存储实际的数据，这就导致在数据量很大时，树高还是可能较高，还有优化空间。

在这里插入图片描述

【B+树】

B+树相比B树最大的变化是：

  • 叶子节点存储所有实际数据，而非叶子节点只存储键值和指向下一个结点的指针，这就意味着只需要比较键值而非数据行，从而提高了查询效率
  • 叶子结点间构成链表，这使得范围查询时可以从某个叶子结点起向后遍历后面的结点

在这里插入图片描述

MySQL的索引B+树在常规的B+树的基础上做出一些优化：

  • 有更高的扇出度，即每个结点可以存储更多的数据
  • 叶子节点间形成双向循环链表，而非单向链表，进一步提高了查询效率

在这里插入图片描述

总结 B树 与 B+树：

  • B树的非叶子节点既存储数据又存储索引，需要更多的存储空间，而B+树的非叶子结点只存储索引，可以容纳更多的键值，减少了树的高度，从而有效减少IO次数
  • B+树的叶结点通过链表连接，范围查询时只需要对链表进行遍历即可，效率更高
  • B+树所有数据都存储在叶子结点，查询时间复杂度稳定在树高，而B树的查询时间复杂度不稳定

每创建一个索引实际上都会生成一个索引B+树。

索引的具体结构

前面介绍了索引采用优化过的B+树作为结构，那么具体结构是怎样的？

这就要先聊到 页 。

页的概念

页 是InnoDB存储引擎生成的 .ibd 文件中的重要部分，是内存和磁盘交互的最小单位，默认大小为 16KB。

每次内存与磁盘交互时，至少读取一页，尽管实际上并不需要读取完整的一页，所以在磁盘中的每个页内部的地址都是连续的。之所以这样做，是因为在使用数据时，根据局部性原理，将来使用的数据大概率与当前访问的数据是邻近的，当查询临近数据时，只需要从内存中读取过的页中访问即可，这使磁盘IO次数显著减少，从而提高了数据库的性能。

独立表空间文件

独立表空间文件就是.ibd文件，理解.ibd文件，首先要了解InnoDB存储引擎的 表空间管理方式。

InnoDB存储引擎支持两种不同的表空间管理方式：

具体采用哪种方式，取决于MySQL配置文件中的一个变量值（开关）：innodb_file_per_table，这个开关默认是ON(打开)。当此开关打开，就意味着数据库中的每个表都会对应一个.ibd独立表空间文件，而当开关关闭，意味着所有表的数据将存储在同一个共享的独立表空间文件中。

从5.6.6版本开始，innodb_file_per_table开关是默认打开的，这就意味着每个InnoDB数据表都对应一个.ibd文件：

在这里插入图片描述

我们可以通过指令ibd2sdi file_name.ibd查看.ibd文件：

在这里插入图片描述

通过以下SQL查看innodb_file_per_table的值：

SHOW VARIABLES LIKE 'innodb_file_per_table';

在这里插入图片描述

设置innodb_file_per_table的值

SET GLOBAL innodb_file_per_table = [ON|OFF];

  • innodb_file_per_table变量是全局变量，它影响所有的会话，修改了表空间管理方式后，不会对已有的表产生影响，只会影响之后的表的存储方式。

    例如，将innodb_file_per_table值修改为OFF，原有的表不受影响，即原来的表仍然是一表对应一个.ibd文件，只影响后来创建的新表。

  • 使用SQL对innodb_file_per_table值的设置是临时的，当数据库服务重新启动时，该变量值就会恢复默认值，如果想要永久性修改，需要修改配置文件，确保你要进行修改且有足够的权限。

【.ibd文件的结构】

.ibd独立表空间文件的结构按照层次关系，依次是：表空间 -> 段 -> 区 -> 页 -> 行，可表示为：

在这里插入图片描述

  • 表空间：InnoDB存储引擎的最高层，表空间中可以包含多个段
  • 段：常见的有数据段、索引段、回滚段。InnoDB中对于段的管理，都是引擎自身完成，不需要人为控制，一个段中包含多个区
  • 区：区是表空间的单元结构，每个区的大小为1M，一个区中存在64个连续的页
  • 页：页是组成区的最小单元，默认大小为16KB，为了保证区的连续性，InnoDB存储引擎每次从磁盘读取4~5个区
  • 行：InnoDB存储引擎是面向行的，即数据是按行进行存放

页的结构

通过上面的的介绍，我们大体了解了什么是页以及页在独立表空间文件中的层级，每个页实际就对应索引B+树的结点，即索引B+树的每个结点都是一个页。

页的类型有很多，最常见的就是数据页和索引页，分别对应索引B+树的叶子结点和非叶子结点。我们把数据页单独拿出来，介绍其结构。

其大体结构框架如下：

在这里插入图片描述

页文件头和页文件尾

页文件头（File Header） 和 页文件尾（File Trailer） 分别位于数据页的头部和尾部，如图：

页文件头和页文件尾分别存储了不同的信息，如下图所示：

在这里插入图片描述

我们需要特别关注的是页文件头中的 上一页页号 FIL_PAGE_PREV 和 下一页页号 FIL_PAGE_NEXT，它们使得页与页之间构成了双向链表结构，即我们前面提到的MySQL索引B+树将常规B+树的叶子节点的单向链表优化为了双向链表。

页主体

页主体是页中保存真实数据的主要区域。 每当创建一个新的页，都会自动分配两行，一个是最小行Infimun，一个是最大行Supremun，这两行不存储任何真实数据，而是作为数据行链表的头和尾，每次插入的新数据会作为一个新数据行插入到最小行和最大行之间，按照主键从小到大的顺序进行连接。

除最大行外，其他所有数据行都会存在一个 next_record 来记录下一行的地址偏移量，因此数据行构成了一个单向链表。如图：

在这里插入图片描述

其中，最小行和最大行之间存在用户数据区 和 空闲区，用户数据区就是存储用户数据的区域；空闲区是数据页中未被使用的空间。

当插入新记录时，数据库管理系统会将记录插入到空闲区（如果当前数据页有足够的空闲空间），否则，MySQL可能会拓展数据页、创建新的数据页或合并新的数据区。

页目录

查询某条记录，最简单的方法就是从最小行Infimun开始，遍历数据行单向链表。但是一个页通常会存放很多行数据，每次查询遍历都遍历这么多行数据，无法满足高效查询。

为了解决每次查询都可能遍历整个数据行链表的问题，InnoDB引入了页目录（Page Directory）。

页目录 将页内包括最小行和最大行在内的所有行进行分组，约定最小行单独为一组，其他每个组最多含有8条数据，最大行就在最后一个分组的末尾。每个分组最后一行在页中的地址将会按照主键从小到大记录在页目录的每一个 槽（Slot） 中（即每一个槽对应一个分组），当某个分组数据行达到上限时，就会分裂出一个新的分组。

画图表示：

在这里插入图片描述

查找某行数据时，首先会采用类似二分查找的算法，先找到对应的槽，在遍历对应的槽即可，大大增加了查询效率。

例如，要查询主键值为8的数据行，首先找到槽2，然后遍历槽对应分组的元素，即可找到主键值为8的元素，如果遍历完槽中所有元素还是没有找到，说明没有主键值为8的数据行。

数据页头

实际上，在页文件头（File Header）下面还有一个区域数据页头（Page Header）。

File Header位于数据文件的开头，包含了关于整个数据文件的一些基本信息，如文件校验和、页的数量等。而Page Header则位于每个数据页的开头，包含了关于该数据页的一些信息，如页的类型、记录的数量等。

具体包含的信息如下图：
在这里插入图片描述

B+树在索引中的应用

【根据索引查找的过程】

在这里插入图片描述

这里主要梳理一下查询的过程，以查询id为4记录为例（仅举例）：

  1. 加载索引页1，比较4 < 7，所以到达索引页2
  2. 加载索引页2，比较3 < 4 < 5，命中，所以加载数据页2
  3. 加载数据页2后，在页内部，根据id为4查找对应的槽
  4. 找到对应的槽后，遍历对应的分组，查找数据行

总结：先根据索引页命中数据页，在数据页内部寻找对应的槽，遍历槽对应分组的数据行。 具体步骤如下：

  1. 根据索引页找到对应的数据页：当执行查询时，MySQL会根据查询条件中的索引键值来定位到相应的索引页。索引页包含了指向数据页的指针，通过这些指针可以找到包含所需的数据页。
  2. 在数据页内部寻找对应的槽：一旦找到了正确的数据页，MySQL会在该页内查找与查询条件匹配的槽。每个槽代表一个记录组，槽中存储了记录的元数据信息，如记录的长度、偏移量等。
  3. 遍历槽对应分组的数据行：一旦找到了匹配的槽，MySQL会读取槽中的元数据信息，根据元数据中记录的长度和偏移量，从数据页中提取出实际的数据行。然后，MySQL会对这些数据行进行进一步的处理，如过滤、排序等，以满足查询的要求。

【体会索引查找效率】

先粗略计算一下三层树高的B+树可以存放多少条记录：

  • 假设一条记录大小为1KB，忽略数据页自身属性空间占用的情况下，一个页可以存放16条数据

  • 索引页一条记录：主键常用的BIGINT类型占8Byte，下一页的地址占用6Byte，由于B+树每个结点能存放的下一页的地址信息比主键多一个，设一个索引页能够存放 n 个主键值，那么可以列出以下式子：

    n * 8 + (n + 1) * 6 = 16 * 1024 解得：n ≈ 1170 ，即按照假设一个索引页可以存放1170个主键值

  • 此时三层高的B+索引树可以存放16 * 1170 * 1170 = 21902400条记录

从这么多记录中查询只需要进行三次IO（因为树高为3）即可完成对数据的检索。

索引分类

索引有很多种分类，具体怎么分，就要看分类依据了，下面我们对这些分类依据具体介绍。

按数据结构分

按数据结构的分类，具体如下表：

索引类型说明
B+树索引基于B+树实现，是最常见的索引结构，大部分引擎均支持
Hash索引基于哈希表实现，适合等值查询，即精确匹配索引列的查询，不支持范围查询
R-tree索引基于R-tree，一种自平衡的n维数据结构实现，主要用于处理多维空间数据，适用于地理信息系统等应用
全文索引基于倒排索引实现，主要用于对文本内容进行索引，适合复杂的文本搜索功能，如模糊匹配，同义词匹配

不同存储引擎的支持情况：（以常见的三种为例）

索引类型InnoDBMyISAMMemoey
B+树索引√√√
Hash索引××√
R-tree索引×√×
全文索引5.6版本后支持√×

我们常说的索引，除非特别指明，否则均为B+树索引。包括之后讨论的，也都是默认B+树索引。

按字段特性分

按照字段特性分类，可以分为：主键索引、唯一索引、普通索引、前缀索引以及全文索引

  1. 主键索引

    主键索引是一种唯一性索引。只有被PRIMARY KEY约束的字段才会生成主键索引，这个过程是自动的，即当某个(些)字段设置了主键约束(可能是复合主键)，MySQL会自动为主键字段创建主键索引，如果将主键索引删除，主键索引也会随之消失。一张表最多存在一个主键索引（因为一张表最多存在一个主键）。

  2. 唯一索引

    唯一索引是一种特殊类型的索引，它用于确保表中的某一列或多列的值是唯一的。唯一索引可以应用于任何列，当某个字段被UNIQUE约束，MySQL会自动为该字段创建唯一索引。一张表可以存在多个唯一索引。

  3. 普通索引

    普通索引是最基本的索引类型，没有任何限制，可以用于任何字段，允许重复和空值。

  4. 前缀索引

    前缀索引只会索引字段的前几个字符，而不是整个字段。适用于字符串类型的字段，特别是当字符串长度较长时，前缀索引可以减少索引的大小，节省存储空间，提高查询性能。但是前缀索引可能会导致查询结果不准确，因为它只考虑了字段的一部分。

前缀索引具体索引前几个字符呢？ 这就要谈到 前缀长度，前缀长度的确定是人为的，在创建时指定。

前缀长度的确定可以根据索引的 选择性 来决定。索引的选择性指不重复的索引值数（基数）和数据表记录总数的比值。索引的选择性越高，查询效率越高。最好的索引选择性是唯一索引的选择性，为1。

  1. 全文索引

    同上文

按物理存储分

按照物理存储分类，分为聚集索引 和 非聚集索引。

  • 聚集索引

    聚集索引又叫聚簇索引，聚集索引的叶子节点存储实际的数据记录，包括所有列，而非叶子结点包含索引信息，数据的物理存储与其逻辑顺序一致。

    ★每张表有且仅有一个聚集索引，聚集索引的确定：

    1. 如果表中存在主键索引，那么主键索引就是聚集索引
    2. 如果表中没有主键索引，那么InnoDB将第一个定义的非空且唯一（NOT NULL + UNIQUE）列的唯一索引作为聚集索引
    3. 如果既没有主键索引也没有符合条件的唯一索引，那么InnoDB会自动生成一个隐藏的自动递增的ROW_ID字段，将它作为聚集索引列创建聚集索引

  • 非聚集索引

    非聚集索引又叫做二级索引，聚集索引以外的索引均为非聚集索引。其叶子结点存储索引列的值、主键值以及指向聚集索引树中的叶子节点的指针，这种设计可以提高查询效率并减少数据冗余。

按字段个数分

按照字段个数分类，可以分为单列索引和复合索引。

  • 单列索引

    单列索引即只包含一个字段的索引。适用于对单个字段进行快速查询的场景

  • 复合索引

    复合索引又叫做联合索引，即包含多个字段的索引。复合索引可用于在多个字段上进行高效的范围查询和排序操作。

索引语法

上面只是在理论层面介绍了索引相关内容，接下来介绍一些基本的索引SQL语法。

创建索引

主键索引 通常是通过定义主键（PRIMARY KEY）来自动创建的，语法如下：

# 创建表时定义主键
CREATE TABLE [IF NOT EXISTS] tbl_name (field1 datatype PRIMARY KEY,field2 datatype,field3 datatype
);CREATE TABLE [IF NOT EXISTS] tbl_name (field1 datatype,field2 datatype,field3 datatype,PRIMARY KEY (field_name)
);# 为表中的列增加主键
ALTER TABLE tbl_name ADD PRIMARY KEY (field_name);
  • 以上方法均是通过创建主键约束来使MySQL自动创建主键索引

唯一索引 可以在创建唯一约束时由MySQL自动创建，也可以用户手动创建；普通索引 不能通过创建约束的方式自动生成；前缀索引 的创建只需要在字段后加上一个小括号，括号里为前缀长度。

# 方法一
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON tbl_name(column1[(prefix_length)],[column2[(prefix_length)]...]);# 方法二
CREATE TABLE [IF NOT EXISTS] tbl_name (field1 datatype,field2 datatype,field3 datatype,[UNIQUE|FULLTEXT] INDEX index_name (field1[(prefix_length)],field2[(prefix_length)]……)
);# 方法三
ALTER TABLE tbl_name ADD [UNIQUE|FULLTEXT] INDEX index_name (field1[(prefix_length)],field2[(prefix_length)]……);

  • [UNIQUE|FULLTEXT]是可选项，如果指定UNIQUE，表示创建唯一索引；如果指定FULLTEXT，则表示创建全文索引

    需要注意的是：当要创建唯一索引的字段中已经存在重复值，则会报错，创建失败，因为唯一索引不接受重复

  • column1,[column2...]和field1,field2……都表示可以选择对一个或多个列创建索引，对应单列索引和联合索引

  • [(prefix_length)]在列名之后，[]表示可选，可选内容为(prefix_length)，表示创建前缀索引，prefix_length表示前缀长度

注意：

  1. 前缀索引一般用于长度较长的字符串类型的字段上，用于减少索引长度。
  2. MySQL 不允许对主键字段创建前缀索引。
  3. 在唯一索引上创建前缀索引时，MySQL根据前缀来验证唯一性约束，例如，如果前缀长度设置为3，则要保证该列所有值的前三个字符不能有重复值，否则，会创建索引失败。

【演示】

以下例子均为语法演示，仅关注语法本身即可！

-- 创建表
CREATE TABLE IF NOT EXISTS demo (id BIGINT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(32),sn VARCHAR(20),gender TINYINT,testColumn VARCHAR(100)
);-- 为name字段创建唯一索引
# 方法一
CREATE UNIQUE INDEX idx_demo_name ON demo(name);
# 方法二
ALTER TABLE demo ADD UNIQUE INDEX idx_demo_name (name);-- 为sn和gender字段创建联合(普通)索引
# 方法一
CREATE INDEX idx_demo_sn_gender ON demo(sn, gender);
# 方法二
ALTER TABLE demo ADD INDEX idx_demo_sn_gender (sn, gender);-- 为testColumn字段创建前缀长度为3的前缀(普通)索引
# 方法一
CREATE INDEX idx_demo_testColumn ON demo(testColumn(3));
# 方法二
ALTER TABLE demo ADD INDEX idx_demo_testColumn (testColumn(3));

还原在唯一索引上创建前缀索引可能发生的错误：

-- 1.插入两条数据，其中字段testColumn的前三个字符相等
INSERT INTO demo VALUES (null,'张三','23142323',1,'aaabbbccc'),(null,'李四','23147878',0,'aaacccddd');-- 2.尝试为testColumn字段创建前缀长度为3的前缀唯一约束
CREATE UNIQUE INDEX idx_demo_testColumn ON demo(testColumn(3));

报错信息如下：

在这里插入图片描述

这是因为MySQL会根据前缀来验证唯一性约束，两条记录的前3个字节对应的都是'aaa'，这不符合唯一性。

查看索引

语法如下：

# 简单查看，根据约束简单判断索引
DESC tbl_name;# 查看详细索引信息，两种方式等价
SHOW KEYS FROM tbl_name;
SHOW INDEX FROM tbl_name;

演示，执行如下SQL：

CREATE TABLE IF NOT EXISTS demo1(id BIGINT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(30),gender TINYINT
);
DESC demo1;             -- 1.
SHOW KEYS FROM demo1;   -- 2.
SHOW INDEX FROM demo1;  -- 3.

在这里插入图片描述

  • 通过DESC即查看表结构，如返回的结果集的某个(些)字段的Key字段显示PRI，我们就可以在推断出这个(些)字段上创建了主键索引

  • 查看详细信息返回的结果表的字段含义（垂直表示）：

    字段说明
    Table表名，表明索引所在的表
    Non_unique0：不允许重复，表示索引是唯一的 （主键索引或唯一索引）
    1：允许重复，表示索引不是唯一的（普通索引或全文索引）
    Key_name索引名称，主键索引的名称通常是PRIMARY，普通索引和唯一索引的名称可以自定义
    Seq_in_index索引中的列顺序号，从1开始，对于联合索引，这个字段表示该列在索引中的位置
    Column_name索引涉及的列名
    Collation列在索引中的排序规则：
    A：升序
    D：降序
    NULL：不排序
    Cardinality索引中不同值的数量的估计值，该值是MySQL估算的，用于优化查询计划，数值越高，索引的选择性越好
    Sub_part如果索引是部分索引（前缀索引），则这个字段表示索引的长度（以字节为单位）。如果是完整索引，则为NULL
    Packed索引是否被压缩：
    NULL：未压缩
    其他值：表示压缩方式
    Null表示该列是否可以包含NULL值：
    YES：可以包含NULL值
    ""：不可以包含NULL值
    Index_type索引类型，可能的值包括：
    BTREE：B-Tree索引
    FULLTEXT：全文索引
    HASH：哈希索引
    RTREE：R-Tree索引
    Comment备注信息，通常为空
    Index_comment索引的注释，通常为空
    Visible表示索引是否可见：
    YSE：索引可见，查询优化器可以使用该索引
    NO：索引不可见，查询优化器不会使用该索引
    不可见索引不会被查询优化器考虑，但仍然存在于表结构中，可以进行维护和管理
    Expression表示索引是否基于表达式创建：如果索引是基于列创建的，这个字段为NULL，如果索引是基于表达式创建的，这个字段会显示表达式的具体内容

删除索引

这里将 删除主键索引 和 删除其他索引 区分开。

【删除主键索引】

删除主键索引是通过删除主键约束来实现的，具体语法如下：

ALTER TABLE tbl_name DROP PRIMARY KEY;

我们在删除主键时可能会报出以下错误：1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key，原因是：由于表中存在 AUTO_INCREMENT 属性的列，并且该列被定义为键（通常是主键）。如果一个表中有 AUTO_INCREMENT 列，那么这个列必须是某个索引的一部分。当尝试删除主键时，如果 AUTO_INCREMENT 列依赖于这个主键，MySQL 会抛出错误 1075。

具体解决方案：

  1. 先删除AUTO_INCREMENT属性，（这里补充一个点：准确来说，AUTO_INCREMENT不是约束，而是列的属性）

    ALTER TABLE tbl_name MODIFY field_name datatype;

    • 为什么上面的语句不会删除主键约束呢？

      ALTER TABLE 语句的 MODIFY 子句主要用于更改列的定义，如数据类型、是否允许为空、默认值等。它可以修改一些列级别的约束，但不包括表级别的约束，如主键约束、外键约束等。

  2. 再删除主键

    ALTER TABLE tbl_name DROP PRIMARY KEY;

【删除其他索引】

# 方法一
DROP INDEX index_name ON tbl_name;# 方法二
ALTER TABLE tbl_name DROP INDEX index_name;
  • 主键索引不能通过DROP INDEX语句删除

【演示】

CREATE TABLE IF NOT EXISTS demo (id BIGINT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(32),sn VARCHAR(20),gender TINYINT,testColumn VARCHAR(100)
);
CREATE UNIQUE INDEX idx_demo_name ON demo(name);
CREATE INDEX idx_demo_testColumn ON demo(testColumn(10));-- 删除主键索引
ALTER TABLE demo MODIFY id BIGINT;
ALTER TABLE demo DROP PRIMARY KEY;-- 删除表中的唯一索引
DROP INDEX idx_demo_name ON demo;-- 删除表中的前缀索引
ALTER TABLE demo DROP INDEX idx_demo_testColumn;-- 删除后查看索引
SHOW KEYS FROM demo;

在这里插入图片描述

索引优化

实际的SQL优化主要是针对SELECT查询语句，而其中的索引优化是SQL优化的关键部分，接下来介绍几种SQL性能分析方式来帮助我们进行索引优化。

SQL执行频率

SQL执行频率 是我们是否值得创建索引的一个关键指标。如果一个数据库的SELECT查询频率相当高，那么我们就要考虑创建合适的索引来优化查询效率了。

语法：

SHOW [GLOBAL|SESSION] STATUS LIKE 'COM_______';
  • 模糊匹配的_一共有7个
  • 查询返回的结果集中很容易找到查询的执行频率Com_select，根据此参数的占比情况，就能基本确定是否需要索引来优化查询

在这里插入图片描述

慢查询日志

确定了某个数据库要进行查询优化，此时要针对哪些SELECT语句涉及的哪些字段创建索引呢？

慢日志查询能够定位执行效率低（耗时长）的语句。它记录了所有执行时间超过指定参数long_query_time的所有SQL语句。

可以通过以下SQL语句查看开关：

SHOW VARIABLES LIKE 'slow_query_log';
  • slow_query_log是一个全局配置选项，它不区分会话级别和全局级别。

【设置慢查询日志的开关状态以及设置慢查询日志的时间阈值】

# 设置慢查询日志开关
SET GLOBAL slow_query_log = [OFF|ON];# 设置时间阈值
SET [GLOBAL|SESSION] long_query_time = time; -- 单位：s
  • 时间阈值long_query_time区分会话级别和全局级别，这就允许我们在不同的会话中采用不同的时间阈值，提高了灵活性
  • 通过SQL语句设置的慢查询日志开关状态是临时的，永久修改必须修改配置文件

【查询慢查询日志的文件名和路径】

SHOW VARIABLES LIKE 'slow_query_log_file';

  • 如果返回的只有文件名，那就代表该文件在MySQL默认日志文件路径中，通过 SHOW VARIABLES LIKE 'datadir'; 语句可以查看该默认路径，找到默认路径后，就可以通过查询到的文件名去路径中寻找。

    在这里插入图片描述

    在这里插入图片描述

profile详情

profile详情 能够展示出SQL语句的耗时，帮助我们定位执行效率低的SQL语句，以便我们进行优化。

【查看是否支持profile操作】

SELECT @@have_profiling;
  • 默认profiling是关闭的，可以通过set语句在session|global级别开启

【查看profiling开关的开启状况 和 设置profiling】

# 查看全局级别或会话级别的profiling参数
SELECT @@[GLOBAL.|SESSION.]profiling;# 设置profiling开关
SET [GLOBAL|SESSION] profiling = [1|0];
  • profiling参数是区分会话级别和全局级别的，会话级别只会影响当前会话，全局级别会影响所有的会话，通过SQL设置都是临时的，在重启数据库服务时将恢复默认值。如果想要永久修改，必须修改配置文件。

【查看profiling详情】

# 查看每一条SQL的耗时基本情况
SHOW PROFILES;# 查看指定query_id的SQL语句的情况
SHOW PROFILE [ALL] FOR QUERY query_id;# 查看指定query_id的SQL语句CPU使用情况
SHOW PROFILE CPU FOR QUERY query_id;
  • 对于第二个SQL语法，如果没有加上ALL，查询的将是各个执行阶段的时间消耗；如果加上ALL，将查询到更详细的信息，不仅包括每个阶段的时间消耗，还包括 CPU 使用情况、块 I/O 操作等。

演示：

在这里插入图片描述
在这里插入图片描述

explain执行计划

explain执行计划 用于获取如何执行SQL语句的信息，帮助我们了解查询的执行过程。虽然任何语句（插入、删除、更新、查询）都可以查看执行计划，但是我们这里仅讨论与索引强相关的SELECT查询语句的执行计划。

语法：在SELECT查询语句前添加EXPLAIN或者DESC

EXPLAIN SELECT...;DESC SELECT...;

查询结果将返回一张执行计划表，如图示例：

在这里插入图片描述

对执行计划表的字段进行解释：

  1. id：查询的标识符（序列号），对于复杂查询，每个子查询也会有一个唯一的id。它能够表示查询中的执行顺序，规则：id相同，执行顺序从上到下；id不同，值越大，越先执行。
  2. select_type：查询的类型，常见的类型包括：
    • SIMPLE：简单查询，不包含子查询或联合查询
    • PRIMARY：主查询，如果查询中有子查询，则最外层的查询就是主查询
    • SUBQUERY：子查询
    • DERIVED：派生表（子查询中的FROM子句）
    • UNION：联合查询中的第二个或后续查询
    • UNION RESULT：从联合查询中获取结果
  3. table：正在访问的表
  4. partitions：匹配的分区（如果有）
  5. type：连接类型，表示MySQL如何查找行。性能从好到差的连接类型依次为：NULL、system、const、eq_ref、ref、range、index、All
  6. possible_key：可能使用的索引
  7. key：实际使用的索引，如果为NULL表示没有使用索引
  8. key_len：使用的索引长度或字节数，该值为索引字段的最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
  9. ref：显示哪个列或常量与key一起用于从表中选择行
  10. rows：MySQL估计需要读取的行数，是一个估计值，可能不准确
  11. filtered：表示返回结果的行数占需要读取行数的百分比，该值越大越好
  12. Extra：关于查询的额外信息。

这么多字段，我们需要重点关注的是：id、select_type、type、possible_key、key以及key_len。

再回头看示例图中执行计划表，各个字段显示的值就能够读懂了。

索引使用

SQL提示

SQL提示是优化数据库查询性能的一种方式，SQL提示的语法有很多，我们这里仅介绍 USE INDEX、IGNORE INDEX、FORCE INDEX。

这三种SQL提示的语法如下：

SELECT ... FROM ... [USE INDEX(index_list)|IGNORE INDEX(index_list)|FORCE INDEX(index_list)] ...;
  • USE INDEX(index_list)：提示（建议）MySQL优化器使用指定的索引，这里只是建议，优化器会考量用户的建议做出更好的选择（接受或者拒绝）。
  • IGNORE INDEX(index_list)：提示优化器忽略指定的索引
  • FORCE INDEX(index_list)：强制优化器使用指定的索引，即使它不是最优选择

SQL演示：

-- 创建student表
CREATE TABLE IF NOT EXISTS student(id BIGINT PRIMARY KEY AUTO_INCREMENT,sn VARCHAR(20),name VARCHAR(32),gender TINYINT
);-- 向表中插入数据
INSERT INTO `student` VALUES (1, '09982', '黑旋风李逵', 1);
INSERT INTO `student` VALUES (2, '00835', '菩提老祖', 1);
INSERT INTO `student` VALUES (3, '00391', '白素贞', 0);
INSERT INTO `student` VALUES (4, '00031', '许仙', 1);
INSERT INTO `student` VALUES (5, '00054', '不想毕业', 0);
INSERT INTO `student` VALUES (6, '51234', '好好说话', 1);
INSERT INTO `student` VALUES (7, '83223', 'tellme', 0);
INSERT INTO `student` VALUES (8, '09527', '老外学中文', 0);SHOW INDEX FROM student;-- 为sn字段创建单列索引
CREATE INDEX idx_student_sn ON student(sn);
-- 为sn和name字段创建联合索引
CREATE INDEX idx_student_sn_name ON student(sn,name);-- SQL提示演示
# 不加SQL提示，默认使用了单列索引idx_student_sn
EXPLAIN SELECT * FROM student WHERE sn = '00391';# 使用USE INDEX的SQL提示，建议优化器使用联合索引idx_student_sn_name，优化器采取了我们的建议
EXPLAIN SELECT * FROM student USE INDEX (idx_student_sn_name) WHERE sn = '00391';
# 使用USE INDEX的SQL提示，建议优化器使用主键索引，优化器拒绝了我们的建议
EXPLAIN SELECT * FROM student USE INDEX (PRIMARY) WHERE sn = '00391';# 使用IGNORE INDEX的SQL提示，提示优化器忽略单列索引idx_student_sn，此时优化器采用了联合索引
EXPLAIN SELECT * FROM student IGNORE INDEX (idx_student_sn) WHERE sn = '00391';# 使用FORCE INDEX的SQL提示，强制优化器使用联合索引idx_student_sn_name
EXPLAIN SELECT * FROM student FORCE INDEX (idx_student_sn_name) WHERE sn = '00391';

在介绍回表查询和索引覆盖前，必须回忆一些知识：

  1. 每创建一个索引就会生成一棵索引B+树

  2. 非聚集索引和聚集索引生成的B+树有所不同，主要体现在叶子结点存储的信息上：

    聚集索引的索引B+树的叶子节点会存储整个数据行；非聚集索引的索引B+树的叶子节点不存储整个数据行，但会存储索引列和主键列的值以及指向数据行的指针

回表查询

当执行一个查询时，如果查询条件使用的是非聚集索引，并且所需列不在或不完全在该索引中，此时就需要根据非聚集索引叶子节点中存储的指向数据行的指针回到聚集索引树中访问实际的数据行（以找到所需要的所有行），此时就发生了回表查询。简单来说，回表查询指的就是非聚集索引B+树回到聚集索引B+树中访问实际数据行的行为。

SQL演示：

-- 创建student1表
CREATE TABLE IF NOT EXISTS student1(id BIGINT PRIMARY KEY AUTO_INCREMENT,sn VARCHAR(20),name VARCHAR(32),gender TINYINT
);-- 插入数据
INSERT INTO `student1` VALUES (1, '09982', '黑旋风李逵', 1);
INSERT INTO `student1` VALUES (2, '00835', '菩提老祖', 1);
INSERT INTO `student1` VALUES (3, '00391', '白素贞', 0);
INSERT INTO `student1` VALUES (4, '00031', '许仙', 1);
INSERT INTO `student1` VALUES (5, '00054', '不想毕业', 0);
INSERT INTO `student1` VALUES (6, '51234', '好好说话', 1);
INSERT INTO `student1` VALUES (7, '83223', 'tellme', 0);
INSERT INTO `student1` VALUES (8, '09527', '老外学中文', 0);-- 为sn字段创建单列普通索引
CREATE INDEX idx_student1_sn ON student1(sn); -- 根据学号查询该同学的所有信息
SELECT * FROM student1 WHERE sn = '51234';

分析SELECT * FROM student1 WHERE sn = '51234';查询语句：

  • 首先可以确定，该表中的索引有两个，一个是主键索引，一个是为sn字段创建的单列普通索引
  • 其次，WHERE条件中涉及到sn字段，查询时会走sn的单列索引
  • 查询请求了所有列的信息 (SELECT *)，而索引中仅包含 sn 字段，所以数据库需要根据非聚集索引树中找到的指针去实际的数据行中查找其他所需的信息，此时发生了回表查询

索引覆盖

索引覆盖（覆盖索引） 指的是查询可以通过非聚集索引树直接获取所有需要的数据，而无需访问表中的实际数据行（聚集索引）。索引覆盖即避免了回表查询，不需要回到聚集索引树中访问实际数据行，提高了查询的性能。所以，实际业务中要尽量发生索引覆盖，避免回表查询。

SQL演示：

-- 创建student1表
CREATE TABLE IF NOT EXISTS student1(id BIGINT PRIMARY KEY AUTO_INCREMENT,sn VARCHAR(20),name VARCHAR(32),gender TINYINT
);-- 插入数据
INSERT INTO `student1` VALUES (1, '09982', '黑旋风李逵', 1);
INSERT INTO `student1` VALUES (2, '00835', '菩提老祖', 1);
INSERT INTO `student1` VALUES (3, '00391', '白素贞', 0);
INSERT INTO `student1` VALUES (4, '00031', '许仙', 1);
INSERT INTO `student1` VALUES (5, '00054', '不想毕业', 0);
INSERT INTO `student1` VALUES (6, '51234', '好好说话', 1);
INSERT INTO `student1` VALUES (7, '83223', 'tellme', 0);
INSERT INTO `student1` VALUES (8, '09527', '老外学中文', 0);-- 为sn和name字段创建联合索引
CREATE INDEX idx_student1_sn_name ON student1(sn,name);-- 根据学号查询该同学的id和name信息
SELECT id,name FROM student1 WHERE sn = '09527';

分析SELECT id,name FROM student1 WHERE sn = '09527';查询语句：

  • 查询的WHERE条件涉及sn字段，所以会走联合索引
  • 该联合索引的B+树的叶子结点中存储了主键列id的值、联合索引列sn和name的值，能够满足查询需求，所以此时不需要去访问聚集索引树中的实际数据行，即发生了索引覆盖

可以将执行计划表的Extra字段的值作为判断是否发生了索引覆盖或回表查询的依据：

  • Using index：发生了索引覆盖
  • Using where; Using index：发生了索引覆盖，并且使用了WHERE子句进行过滤
  • Using index condition：发生了回表查询

当Extra出现其他值，特别是为NULL的时候，不代表没有发生索引覆盖或回表查询，要根据其他字段、索引、以及查询语句综合判断。

索引失效

最左前缀法则

最左前缀法则针对的是联合索引，其具体指：如果一个索引是由多个列组成的复合索引，那么查询条件必须从索引的最左列开始，并且连续使用索引列，才能有效地利用索引。如果跳过某一列，索引将会部分失效(后面的字段索引失效)。

演示准备：

-- 创建student2表
CREATE TABLE IF NOT EXISTS student2(id BIGINT PRIMARY KEY AUTO_INCREMENT,sn VARCHAR(20),name VARCHAR(32),mail VARCHAR(50),gender TINYINT
);-- 插入数据
INSERT INTO `student2` VALUES (1, '09982', '黑旋风李逵', 'xuanfeng@qq.com', 1);
INSERT INTO `student2` VALUES (2, '00835', '菩提老祖', NULL, 1);
INSERT INTO `student2` VALUES (3, '00391', '白素贞', NULL, 0);
INSERT INTO `student2` VALUES (4, '00031', '许仙', 'xuxian@qq.com', 1);
INSERT INTO `student2` VALUES (5, '00054', '不想毕业', NULL, 0);
INSERT INTO `student2` VALUES (6, '51234', '好好说话', 'say@qq.com', 0);
INSERT INTO `student2` VALUES (7, '83223', 'tellme', NULL, 1);
INSERT INTO `student2` VALUES (8, '09527', '老外学中文', 'foreigner@qq.com', 0);-- 为sn、name、mail字段创建联合索引，注意创建联合索引时字段的顺序
CREATE INDEX idx_student2_sn_name_mail ON student2(sn,name,mail);

先查看以下执行计划：

# 1. 
EXPLAIN SELECT * FROM student2 WHERE sn = '00031' AND name = '许仙' AND mail = 'xuxian@qq.com';
# 2.
EXPLAIN SELECT * FROM student2 WHERE sn = '00031' AND name = '许仙';
# 3.
EXPLAIN SELECT * FROM student2 WHERE sn = '00031';

结果如下：

在这里插入图片描述

根据上述结果可以大体判断：

sn字段的索引长度为83，name字段的索引长度为131，mail字段的索引长度为203

接着执行以下SQL语句：

# 索引完全失效
EXPLAIN SELECT * FROM student2 WHERE name = '许仙' AND mail = 'xuxian@qq.com';
EXPLAIN SELECT * FROM student2 WHERE mail = 'xuxian@qq.com';# 索引部分失效
EXPLAIN SELECT * FROM student2 WHERE sn = '00031' AND mail = 'xuxian@qq.com';

三条SQL语句的执行结果如下图所示：

在这里插入图片描述

由于第一条和第二条语句的WHERE条件没有从索引最左列sn开始，违背了最左前缀法则，所以联合索引全部失效；第三条语句的WHERE条件中存在最左列sn，但是没有中间的name字段，所以索引会部分失效，根据上面的字段索引长度83可知，第三条语句只有sn字段的索引生效了，name以及其后的mail字段的索引都失效了（尽管WHERE条件中存在mail字段）。

如果查询条件涉及到了联合索引中的全部字段，但条件 AND 的顺序与建立索引时的顺序不一致，通常不会导致索引失效。数据库优化器通常能够识别并优化这种情况，使得索引仍然可以被有效利用。

即：要求最左列存在即可

【最左前缀法则的思考】

最左前缀法则规定，查询条件必须从索引的最左列开始，此时索引才能生效。因此，我们在创建联合索引时要注意顺序：

  • 将选择性高的列放在前面。 选择性高的列意味着该列中的值分布较为均匀，可以更有效地缩小搜索范围。
  • 将查询频率高的列放在前面。 查询效率高意味着更常出现在如WHERE条件的后面，这样能有效避免违背最左前缀法则。

范围查询

联合索引中使用了范围查询（<、>），范围查询右侧的列索引失效。

演示：

-- 创建student3表
CREATE TABLE IF NOT EXISTS student3(id BIGINT PRIMARY KEY AUTO_INCREMENT,sn VARCHAR(20),name VARCHAR(32),age INT,gender TINYINT
);-- 插入数据
INSERT INTO `student3` VALUES (1, '09982', '黑旋风李逵', 24, 1);
INSERT INTO `student3` VALUES (2, '00835', '菩提老祖', 66, 1);
INSERT INTO `student3` VALUES (3, '00391', '白素贞', 18, 0);
INSERT INTO `student3` VALUES (4, '00031', '许仙', 23, 1);
INSERT INTO `student3` VALUES (5, '00054', '不想毕业', 43, 0);
INSERT INTO `student3` VALUES (6, '51234', '好好说话', 32, 0);
INSERT INTO `student3` VALUES (7, '83223', 'tellme', 15, 1);
INSERT INTO `student3` VALUES (8, '09527', '老外学中文', 87, 0);-- 为age、sn、name字段创建联合索引 
CREATE INDEX idx_student3_age_sn_name ON student3(age,sn,name);-- 演示
EXPLAIN SELECT * FROM student3 WHERE age > 18 AND sn = '00054';

对于演示SQL的执行结果如下：

在这里插入图片描述

结果显示，只有age字段的索引生效了，而sn字段的索引（长度为83）没有生效。

将EXPLAIN SELECT * FROM student3 WHERE age > 18 AND sn = '00054';的范围查询的>改为>=，观察执行计划：

EXPLAIN SELECT * FROM student3 WHERE age >= 18 AND sn = '00054';

在这里插入图片描述

结果显示，此时执行计划的key_len字段的值为88，即使用了sn字段的索引，查询效率会得到优化。

【思考】

在业务允许的情况下，尽可能的使用类似于>=或<=这类的范围查询，而避免使用>或<。

索引列运算

当在查询条件中对索引列进行运算时，可能会导致索引失效。这是因为当在查询条件中对索引列进行运算时，数据库引擎需要先计算出运算的结果，然后再进行比较。这个过程破坏了索引的有序性，使得数据库无法直接利用索引进行快速查找。

演示：

-- 创建student4表
CREATE TABLE IF NOT EXISTS student4(id BIGINT PRIMARY KEY AUTO_INCREMENT,sn VARCHAR(20),name VARCHAR(32),gender TINYINT,adm_grade DECIMAL(5,2)
);-- 插入数据
INSERT INTO `student4` VALUES (1, '09982', '黑旋风李逵', 1, 580.55);
INSERT INTO `student4` VALUES (2, '00835', '菩提老祖', 1, 655.77);
INSERT INTO `student4` VALUES (3, '00391', '白素贞', 0, 701.10);
INSERT INTO `student4` VALUES (4, '00031', '许仙', 1, 661.32);
INSERT INTO `student4` VALUES (5, '00054', '不想毕业', 0, 590.00);
INSERT INTO `student4` VALUES (6, '51234', '好好说话', 0, 675.15);
INSERT INTO `student4` VALUES (7, '83223', 'tellme', 1, 689.34);
INSERT INTO `student4` VALUES (8, '09527', '老外学中文', 0, 720.10);-- 为adm_grade字段创建索引
CREATE INDEX idx_student4_adm_grade ON student4(adm_grade);-- 不进行列运算
EXPLAIN SELECT * FROM student4 WHERE adm_grade > 650;-- 字段列函数运算
EXPLAIN SELECT * FROM student4 WHERE ABS(adm_grade) > 600;-- 字段列表达式运算
EXPLAIN SELECT * FROM student4 WHERE adm_grade + 100 > 690;

三个执行计划的结果如图所示：

在这里插入图片描述

当不对索引列进行运算时，索引生效，但是当对索引列进行函数运算和表达式运算时，索引失效了。

其实除了函数运算和表达式运算外，类型转换（包括隐式类型转换）、使用常量表达式 都可能会导致索引失效。

字符串不使用引号

字符串类型的字段不使用引号时，索引就会失效。 这是因为如果不加引号，MySQL会将字符串视为未定义的标识符，进行隐式类型转换，进而导致索引失效。

但是，不加引号不会影响返回的结果集，因为MySQL会执行全表扫描。

演示：

-- 创建student5表
CREATE TABLE IF NOT EXISTS student5(id BIGINT PRIMARY KEY AUTO_INCREMENT,sn VARCHAR(20),name VARCHAR(32)
);-- 插入数据
INSERT INTO `student5` VALUES (1, '09982', '黑旋风李逵');
INSERT INTO `student5` VALUES (2, '00835', '菩提老祖');
INSERT INTO `student5` VALUES (3, '00391', '白素贞');
INSERT INTO `student5` VALUES (4, '00031', '许仙');
INSERT INTO `student5` VALUES (5, '00054', '不想毕业');
INSERT INTO `student5` VALUES (6, '51234', '好好说话');
INSERT INTO `student5` VALUES (7, '83223', 'tellme');
INSERT INTO `student5` VALUES (8, '09527', '老外学中文');-- 为sn字段创建索引
CREATE INDEX idx_student5_sn ON student5(sn);-- 加引号
EXPLAIN SELECT * FROM student5 WHERE sn = '00054';-- 不加引号
EXPLAIN SELECT * FROM student5 WHERE sn = 00054;

两个执行计划的结果如下：

在这里插入图片描述

加引号的执行计划显示索引生效；不加引号的执行计划显示索引失效。

所以，我们在使用字符串类型的字段时一定要加上引号，防止发生隐式类型转换从而导致索引失效。

模糊匹配

当模糊匹配中以通配符_或%开头时，索引会失效。

演示：

-- 创建student6表
CREATE TABLE IF NOT EXISTS student6(id BIGINT PRIMARY KEY AUTO_INCREMENT,sn VARCHAR(20),name VARCHAR(32)
);-- 插入数据
INSERT INTO `student6` VALUES (1, '09982', '黑旋风李逵');
INSERT INTO `student6` VALUES (2, '00835', '菩提老祖');
INSERT INTO `student6` VALUES (3, '00391', '白素贞');
INSERT INTO `student6` VALUES (4, '00031', '许仙');
INSERT INTO `student6` VALUES (5, '00054', '不想毕业');
INSERT INTO `student6` VALUES (6, '51234', '好好说话');
INSERT INTO `student6` VALUES (7, '83223', 'tellme');
INSERT INTO `student6` VALUES (8, '09527', '老外学中文');-- 为name字段创建索引
CREATE INDEX idx_student6_name ON student6(name);-- 前缀匹配
EXPLAIN SELECT * FROM student6 WHERE name LIKE '许%';
EXPLAIN SELECT * FROM student6 WHERE name LIKE '许_';-- 后缀匹配
EXPLAIN SELECT * FROM student6 WHERE name LIKE '%仙';
EXPLAIN SELECT * FROM student6 WHERE name LIKE '_仙';-- 包含匹配
EXPLAIN SELECT * FROM student6 WHERE name LIKE '%好%';
EXPLAIN SELECT * FROM student6 WHERE name LIKE '_好_';
EXPLAIN SELECT * FROM student6 WHERE name LIKE '好%说%';
EXPLAIN SELECT * FROM student6 WHERE name LIKE '好_说_';

8个执行计划的结果如图所示（按顺序）：
在这里插入图片描述

易验证规律：只有以通配符_或%开头的模糊匹配才会导致索引失效

因此，模糊匹配时要避免以通配符开始。

OR分隔

OR连接条件中只要有一个字段没有索引，则OR连接的所有字段均不会使用索引

演示：

-- 创建student7表
CREATE TABLE IF NOT EXISTS student7(id BIGINT PRIMARY KEY AUTO_INCREMENT,sn VARCHAR(20),name VARCHAR(32),gender TINYINT
);-- 插入数据
INSERT INTO `student7` VALUES (1, '09982', '黑旋风李逵', 1);
INSERT INTO `student7` VALUES (2, '00835', '菩提老祖', 1);
INSERT INTO `student7` VALUES (3, '00391', '白素贞', 0);
INSERT INTO `student7` VALUES (4, '00031', '许仙', 1);
INSERT INTO `student7` VALUES (5, '00054', '不想毕业', 0);
INSERT INTO `student7` VALUES (6, '51234', '好好说话', 1);
INSERT INTO `student7` VALUES (7, '83223', 'tellme', 0);
INSERT INTO `student7` VALUES (8, '09527', '老外学中文', 1);-- 为name和sn字段分别创建索引
CREATE INDEX idx_student7_name ON student7(name);
CREATE INDEX idx_student7_sn ON student7(sn);-- OR连接条件的字段均有索引
EXPLAIN SELECT * FROM student7 WHERE name = '许仙' OR sn = '09527';-- OR连接条件的字段不全有索引
EXPLAIN SELECT * FROM student7 WHERE gender = 1 OR name = '许仙';
EXPLAIN SELECT * FROM student7 WHERE name = '许仙' OR gender = 1;

三个执行计划的结果如图所示：

在这里插入图片描述

针对OR分隔的索引失效场景，我们可以采用以下方式避免：

  • UNION代替OR：将 OR 语句拆分为多个查询，使用 UNION 将结果合并。
  • 使用子查询：将 OR 语句中的每个条件放在子查询中，然后将结果合并。

数据分布影响

如果MySQL优化器评估使用索引比全表扫描慢，就会不使用索引。

这样的数据分布主要有：

  • 低选择性：如果一个索引列的选择性很低，即该列中大部分值都相同或重复很多次，那么索引的效果就会大打折扣，会导致索引失效，但其实我们不会考虑为低选择性的字段创建索引。
  • 数据倾斜：数据倾斜是指数据在某些值上的分布极不均匀，某些值出现的频率远高于其他值。此时可能会发生索引失效。
  • 小表全表扫描：对于小表，全表扫描可能比使用索引更快。此时索引可能失效。
  • 统计信息不准确：如果统计信息不准确，优化器可能会做出错误的决策，导致索引失效。
  • 大量删除和插入操作：频繁的删除或插入操作可能导致索引碎片化，影响索引的性能，从而导致索引失效。

索引设计原则

通过以上所有对索引的讨论，我们可以总结出一些索引设计原则，分三个方面：

  • 对哪些数据库的哪些表使用索引
  • 对表中的哪些字段使用索引
  • 创建什么索引

基于上面三个方面，总结 如下：

  1. 对数据量大且查询频率高的表尝试建立索引
  2. 对于常作为查询条件WHERE、排序ORDER BY、分组GROUP BY操作的字段建立索引
  3. 选择查询频率高且区分度高的列作为索引，尽量建立唯一索引，区分度（选择性）越高，使用索引的效率就越高
  4. 如果要对一个字符串类型的字段建立索引，且字符串的长度较长时，要考虑建立前缀索引，减少索引的大小，增加查询的效率
  5. 尽量使用联合索引而不是单列索引，联合索引更容易出现索引覆盖，避免回表查询，提高查询效率
  6. 建立必要的索引，控制索引的数量，因为每创建一个索引，都会生成一棵索引B+树，过多的索引会导致维护索引的开销过高，影响增删改的效率
  7. 如果索引列不能存储NULL值，建议在创建表时使用NOT NULL约束它，当优化器知道每列是否包含NULL值时，它可以选择更有效的索引用于查询

完

版权声明:

本网仅为发布的内容提供存储空间，不对发表、转载的内容提供任何形式的保证。凡本网注明“来源：XXX网络”的作品，均转载自其它媒体，著作权归作者所有，商业转载请联系作者获得授权，非商业转载请注明出处。

我们尊重并感谢每一位作者，均已注明文章来源和作者。如因作品内容、版权或其它问题，请及时与我们联系，联系邮箱：809451989@qq.com，投稿邮箱：809451989@qq.com

最新新闻

热搜词

数据治理：如何建立数据共享机制 WIN Act. memo YOLOv11 C++ TensorRT 2024.11.142024.11.15模拟赛 [转]SAP模块一句话入门 边缘计算模块和工控机有什么区别？

声明：本站所有新闻及新闻图片来源于其他网站，如有侵权，请及时联系我们！

客户服务 | 关于我们 | 版权声明

版权所有：

Copyright 2024 尧图网 All Rights Reserved.QQ:809451989