MySQL数据库学习-进阶索引(1)

2025-08-12

索引概述

索引index是帮助MySQL高效的获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

优缺点：提高数据检索效率，降低数据库IO成本；降低数据排序成本，降低CPU消耗。索引列占用空间，索引大大提高了查询效率，同时也降低了更新表速度，如对表进行INSERT、UPDATE、DELETE时，效率降低。

索引结构

MySQL索引在引擎层实现，不同引擎不同的索引。

二叉树——红黑树——多路平衡查找树（B树）——B+树

B树：B树是一个多路平衡查找树，每个节点包含多个键值（而不是二叉树的一个键值），节点内的键值是有序的。B树的每个节点有多个子节点，每个子节点对应着一个范围的键值。所有叶子节点都在同一层级，保证了查询的平衡。每个节点可以存储多个键值对，节点之间的分裂和合并都在内部进行，避免频繁的磁盘读取。

B+树：B+树是B树的一个变种，所有的键值都存储在叶子节点，内部节点仅存储索引信息。叶子节点通常通过指针链表连接，形成一个有序链表，便于范围查询。内部节点不存储实际数据，只有指向子节点的指针，使得B+树的查询更加高效。

MySQL索引在此基础上增加了一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。InnoDB采用B+树索引，一是考虑都层浅，效率更高；二是相对于B树，页只存储指针和键值，子节点才存储数据，这样使得每一页可以有更多的指针和键值，层相对于B树更浅；三是可以支持范围查询和排序，而hash索引不支持。

【https://www.cs.usfca.edu/~galles/visualization/BTree.html】

Hash：哈希索引就是采用一定的hash算法，将键值换成新的hash值，映射到对应的槽位上，然后存储到hash表中。特点：Hash索引只能对等比较(= in)，不支持范围查询；无法利用索引完成排序操作；查询效率高，通常只需要一次检索就可以了，效率通常更高于B+tree索引。引擎支持：Memory支持hash索引，但在InnoDB中也有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。

索引分类

分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建，只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是全文比较索引中的值	可以有多个	FULLTEXT

索引存储形式分类

聚集索引	将数据存储与索引放到一块，索引结构的叶子节点保存了行数据	必须有，只有一个
二级索引	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

聚集索引：如果存在主键，主键索引就是聚集索引。如果不存在主键，将使用第一个唯一UNIQUE索引作为聚集索引。如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

回表查询：就是二级索引查到主键后返回到聚集索引获取行数据。

索引语法

创建索引：CREATE [ UNIQUE|FULLTEXT ]INDEX index_name ON table_name (index_col_name,...);
查看索引：SHOW INDEX FROM table_name;
删除索引：DROP INDEX index_ name ON table_name;

SQL性能分析

SQL执行频率：插入、更新、查询、删除频率。SHOW [SESSION|GLOBAL] STATUS LIKE 'Com_______'

**慢查询日志：**用来记录所有执行时间超过long_query_time单位秒，默认10。存储在/var/lib/mysql MySQL中慢查询日志默认没有开启，需要在MySQL中配置文件/etc/my.cnf中配置如下信息： slow_query_log=1 # 开启 long_query_time=2 # 设置时间

**profile详情：**show profiles可以帮助我们了解SQL时间都耗费到哪里了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作：select @@have_profiling;

select @@have_profiling;
select @@profiling;
set @@profiling=1;
use industrial_project_management;
select project_name from projects where status='Planning';
show profiles;
show profile for query 3;  --查看某个具体语句的细节
show profile cpu for query 3;  --查看某个具体语句的细节

**explain：**常用，直接在语句之前加入explain；

id：select查询的序列号，表示查询中执行select子句或者是操作表的顺序，id相同，执行顺序从上到下，id不同，值越大越先执行。
select_type：查询的类型，常见的SIMPLE(简单表，即不使用表连接或者子查询)、PRIMARY(主查询，即外层的查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY(SELECT/WHERE之后包含了子查询)等。
**type：**表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all。
**possible_key：**显示可能应用在这张表上的索引，一个或多个。
**key：**实际用到的索引。
**ken_len：**使用的索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，再不损失精确性的前提下，长度越短越好。
**rows：**MySQL认为必须执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不准确。
filtered：表示返回结果的行数占需读取行数的百分比，filtered的值越大越好。

索引使用

最左前缀法则：如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则是指查询从索引的最左列开始，并且不跳过索引中的列。
范围查询：联合索引中，出现范围查询（>,<），范围查询右侧的列索引失效，所以尽量使用>=、<=。
索引失效情况
在索引列上进行运算操作如函数运算等【不清楚新版本mysql是否修正，待验证。】
字符串没用单引号。
模糊查询：尾部模糊匹配索引不会失效，头部模糊匹配，索引失效。
or连接的情况：用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么设计的索引都不会被用到。
数据分布影响：如果MySQL评估使用索引比全表更慢，则不使用索引。
使用提示
是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些认为的提示来达到优化操作的目的。
use index使用索引:explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';
ignore index不使用索引:explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';
force index强制引:explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';
索引使用：
覆盖索引：尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少select *。Extra：Using index condition，需要回表查询但是优化了。using where，回表查询。 using index所需要数据都在，无需在回表查询。
前缀索引：当字段类型为字符串（varchar，text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，节省索引空间，提高索引效率。 create index idx_xxx on table_name(column(n));
单列索引和联合索引：优先使用联合索引。

索引设计原则

针对数据量较大，且查询比较频繁的表建立索引。
针对常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
如果是字符串类型的字段，字段的长度较长，可以针对字段的特点，建立前缀索引。
尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
要控制索引的数量，索引并不是多多益善，索引越多，维护索引的代价也就越大，会影响增删改的效率。
如果索引列不能存储NULL值，请在创建表时使用NOT NULL来约束。当优化器知道每列是否包含NULL值，它可以更好的确认哪个索引能更有效地用于查询。

← 返回