跳至主要內容

索引

Qtrue_Akira2025年12月15日大约 9 分钟

索引

索引结构

提示

如果没有指明是哪种索引，通常指的是B+Tree索引。

MySQL的索引是在存储引擎层实现的，不同的存储引擎支持不同类型的索引结构，主要包括：

索引结构说明
B+Tree索引最常用的索引结构，大部分引擎都支持B+树索引，B+树索引适用于范围查询和排序操作。
Hash索引基于哈希表实现的索引结构，适用于等值查询，但不支持范围查询。
R-Tree索引（空间索引）主要用于空间数据类型的索引，如地理信息系统（GIS）数据。
Full-text（全文索引）是一种通过建立倒排索引，快速匹配文档的方式
  • 不同存储结构对各索引结构的支持情况：
索引结构InnoDBMyISAMMEMORY
B+Tree索引支持支持支持
Hash索引支持不支持支持
R-Tree索引不支持支持不支持
Full-text索引5.6版本后支持支持不支持

索引分类

分类说明特点关键字
主键索引针对表中的主键创建的索引默认自动创建，只能有一个PRIMARY KEY
唯一索引保证索引列的值唯一可以有多个UNIQUE
常规索引快速定位特定数据可以有多个
全文索引全文索引查的是文本中的关键值，而不是比较索引中的值可以有多个FULLTEXT
  • 在InnoDB存储引擎中，根据索引的存储形式又可以分为：
分类说明特点
聚集索引（Clustered Index）将数据存储和索引放在一起，索引结构的叶子节点保存行数据必须有，并且每个表只能有一个
二级索引（Secondary Index）将数据和索引分开存储，索引节点的叶子节点关联的是对应的主键每个表可以有多个
分类说明
单列索引只包含一个列的索引
联合索引包含多个列的索引

索引语法

创建索引

CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

查看索引

SHOW INDEX FROM table_name;

删除索引

DROP INDEX index_name ON table_name;

SQL性能分析

SQL执行频率查询

  • 查询服务器的状态信息
SHOW [SESSION|GLOBAL] STATUS;
  • 查询当前数据库INSERT、SELECT、UPDATE、DELETE等语句的执行次数
-- Com后为7个下划线
SHOW [SESSION|GLOBAL] STATUS LIKE 'Com_______';

慢查询日志

  • 慢查询日志记录了所有执行时间超过指定参数(long_query_time，单位秒，默认10秒)的SQL语句。
  • MySQL默认关闭慢查询日志，需要在MySQL的配置文件(/etc/my.cnf)中开启：
# 开启慢查询日志
slow_query_log = 1
# 设置慢查询日志记录时间
long_query_time = 2

慢查询日志文件默认保存于/var/lib/mysql/目录下，文件名格式为<hostname>-slow.log。

profile详情

show profiles能够在做SQL优化时，查看SQL语句的执行时间和各个阶段的时间消耗情况。

  • 查看profile功能是否开启
SELECT @@profiling;
  • 开启profile功能
SET profiling = 1;
  • 查看每一条SQL的基本耗时情况
SHOW PROFILES;
  • 查看指定query_id的SQL语句各阶段耗时情况
SHOW PROFILE FOR QUERY query_id;
  • 查看指定query_id的SQL语句CPU使用情况
SHOW PROFILE CPU FOR QUERY query_id;

explain执行计划

通过EXPLAIN或DESC命令获取MySQL如何执行SELECT语句的详细信息，包括在SELECT语句执行过程中表如何连接及连接的顺序。

-- 直接在SELECT语句前加上EXPLAIN或DESC
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;
  • EXPLAIN结果字段说明：
    • id: select查询的序列号，表示查询中执行select子句或是操作表的顺序，id值越大，优先执行。
    • select_type: select查询的类型，常见的有SIMPLE（简单查询，即不使用表连接或子查询）、PRIMARY（主查询，即外层的查询）、UNION（联合查询中的第二个或后续的查询）、SUBQUERY（SELECT或WHERE后包含了子查询）。
    • type: 表示MySQL选择的连接类型，是影响查询性能的重要因素。性能由好到差依次为：NULL > system > const > eq_ref > ref > range > index > ALL。
    • possible_keys: 显示可能应用在这张表的索引，一个或多个。
    • Key: 实际使用的索引名称，如果为NULL，表示没有使用索引。
    • key_len: 表示索引的长度，越短越好。
    • rows:MySQL认为必须要执行查询的行数，在InnoDB引擎的表中，这个值是估算值。
    • filtered:表示返回结果的行数占需读取行数的百分比，filtered值越高越好。

索引使用

验证索引效率

在未建立索引之前，执行如下SQL语句，查看SQL的耗时。

SELECT * FROM tb_sku WHERE sn = '1000001';

针对字段创建索引

CREATE INDEX idx_sn ON tb_sku(sn);

再次执行上述SQL语句，查看SQL的耗时，比较两次执行时间差

SELECT * FROM tb_sku WHERE sn = '1000001';

最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则，即查询从索引的最左列开始，且不跳过索引中的列。

注意

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

索引失效情况

范围查询

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

EXPLAIN SELECT * FROM tb_sku WHERE price > 100 AND sn = '1000001';

解决方法：在范围查询（>、<）后加上=.

EXPLAIN SELECT * FROM tb_sku WHERE price >= 100 AND sn = '1000001';

索引列运算

索引列上进行运算，会导致索引失效。

EXPLAIN SELECT * FROM tb_sku WHERE price + 100 = 500;

字符串类型不加引号

字符串类型的字段在查询时不加引号，会导致索引失效。

EXPLAIN SELECT * FROM tb_sku WHERE sn = 1000001;

模糊查询

在字符串类型的字段上使用LIKE进行模糊查询时，如果通配符%出现在字符串的开头，会导致索引失效。

  • 简单来说，头部模糊查询后会导致索引失效，尾部则不会。
EXPLAIN SELECT * FROM tb_sku WHERE sn LIKE '%0001';

OR连接多个条件

使用OR连接多个条件时，如果OR前的条件有索引，而OR后的条件没有索引，会导致索引失效。

EXPLAIN SELECT * FROM tb_sku WHERE sn = '1000001' OR price = 500;
  • 解决方法：对OR后的列建立索引。
CREATE INDEX idx_price ON tb_sku(price);

数据分布影响

如果MySQL评估索引比全表扫描更慢，则会放弃使用索引，直接进行全表扫描。

  • 例如：某列的值大部分都相同，只有少部分不同，此时MySQL会选择全表扫描。

SQL提示

SQL提示，是优化数据库的重要手段，可以强制MySQL使用指定的索引，或者不使用索引，从而提高查询效率。

使用指定索引

USE INDEX()提示建议MySQL使用指定的索引，如果MySQL认为使用该索引效率低，则不会使用该索引。

EXPLAIN SELECT * FROM tb_sku USE INDEX(idx_sn) WHERE sn = '1000001';

强制使用指定索引

FORCE INDEX()提示强制MySQL使用指定的索引。

EXPLAIN SELECT * FROM tb_sku FORCE INDEX(idx_sn) WHERE sn = '1000001';

忽略指定索引

IGNORE INDEX()提示建议MySQL忽略指定的索引。

EXPLAIN SELECT * FROM tb_sku IGNORE INDEX(idx_sn) WHERE sn = '1000001';

覆盖索引

尽量使用覆盖索引（查询使用了索引，并且返回需要的列，在该索引中已经能全部找到），避免回表查询，减少SELECT从而提高查询效率。

了解

通过EXPLAIN命令查看Extra会有如下字段(根据MySQL版本不同，字段名称可能有所不同)：

  • using index condition：查找使用了索引，但是需要回表查询数据。
  • using where; using index：查找使用了覆盖索引(即查询使用了索引，并且需要的数据都能在索引列中找到)，不需要回表查询数据。

前缀索引

对于字符串类型(varchar、text)的字段，可以只索引前几个字符，称为前缀索引，可以节省存储空间，提高索引效率。

  • 语法：
    CREATE INDEX index_name ON table_name (column_name(prefix_length));
    • prefix_length：表示索引的前缀长度，即索引前几个字符。
  • 如何确定前缀长度：
    • SQL语句：
    SELECT COUNT(DISTINCT LEFT(column_name,prefix_length))/COUNT(*) FROM table_name;
    • 如果结果等于1，表示前缀长度可以唯一标识该列，这是最好的索引选择性，但是前缀长度会比较长。
    • 为了保证前缀索引的查询效率接近完整索引，不额外增加太多回表的查询成本，在前缀长度最短的情况下通常会要求业务区分度达到95%以上即可。

单列索引与联合索引

  • 单列索引：只包含一个列的索引。
  • 联合索引：包含多个列的索引。

在业务场景中，如果存在多个查询条件，建议创建联合索引，而不是多个单列索引，这样可以提高查询效率，减少索引的存储空间。

了解

多条件联合查询时，MySQL优化器会评估那个字段的索引效率更高，从而选择使用哪个索引。

索引设计原则

  1. 针对数据量大、且查询频繁的表创建索引。
  2. 针对常作为查询条件(WHERE)、排序(ORDER BY)、分组(GROUP BY)的列创建索引。
  3. 尽量选择区分度高的列创建索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
  4. 如果字符串类型的字段，可以考虑建立前缀索引。
  5. 尽量使用联合索引，减少单列索引。因为查询时联合索引很多时候可以覆盖索引,节省空间，避免回表。
  6. 要控制索引数量，索引越多，维护索引结构代价就越大，影响增删改(INSERT、UPDATE、DELETE)等DML操作的效率。
  7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束。当优化器知道每列是否包含NULL值时，可以更好的确定哪个索引最适合查询。
上次编辑于: 2025/12/17 20:59:07
贡献者: akirakur