MySQL 索引
索引是对数据库表中一列或多列的值进行排序的一种结构。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。
简单类比一下，数据库如同书籍，索引如同书籍目录，假如我们需要从书籍查找与 xx 相关的内容，我们可以直接从目录中查找，定位到 xx 内容所在页面，如果目录中没有 xx 相关字符或者没有设置目录（索引），那只能逐字逐页阅读文本查找，效率可想而知。
1、索引的优缺点
索引可以大大提高MySQL的检索速度，为什么不对表中的每一个列创建一个索引呢？
1.1 优点
- 索引大大减小了服务器需要扫描的数据量，从而大大加快数据的检索速度，这也是创建索引的最主要的原因。
案例：假设有一个100万行的用户表，没有索引的情况下，要查找用户名为"张三"的用户，数据库需要扫描整个表100万行。如果在用户名字段上创建了索引，数据库只需要在索引树上查找，可能只需要扫描几十个节点就能找到结果，大大减少了扫描的数据量。
- 索引可以避免排序和创建临时表
案例：在订单表上有一个created_time字段，我们需要按创建时间排序查询。如果没有索引，数据库需要将所有数据加载到内存中进行排序，可能需要创建临时表。如果在
created_time字段上创建了索引，数据库可以直接利用索引的有序性，按顺序读取数据，避免了排序操作。
- 索引可以将随机IO变成顺序IO
案例：在没有索引的情况下，查询可能需要随机访问表中的不同位置，导致磁盘随机IO。有索引的情况下，数据库可以按索引的顺序进行读取，将随机IO转换为顺序IO，大大提高了I/O效率。
- 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。（不一定成功）
案例：UNIQUE INDEX
- 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
案例：在订单表和用户表之间进行连接查询，
users.id = orders.user_id。如果在users.id字段上有索引，连接操作可以更快地找到匹配的记录，而不需要扫描整个用户表。
- 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
案例：在销售表中，按产品类别分组并计算每个类别的总销售额。如果没有索引，数据库需要对所有销售记录进行排序和分组。如果在
category_id字段上有索引，数据库可以利用索引的有序性，更快地完成分组和排序。
1.2 缺点
- 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
- 索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间，如果需要建立聚簇索引，那么需要占用的空间会更大
- 对表中的数据进行增、删、改的时候，索引也要动态的维护，这就降低了整数的维护速度
- 对于非常小的表，大部分情况下简单的全表扫描更高效；
- 二次访外存
2、创建索引准则
索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。
2.1 应该创建索引的列
-
在经常使用在WHERE子句中的列上面创建索引，可能加快条件的判断速度。
-
在经常需要查询的列上，可以加快返回的速度
-
在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构（主动）
-
在经常用在连接（JOIN）的列上，这些列主要是一外键，可以加快连接的速度
-
在经常需要根据范围（<，<=，=，>，>=，BETWEEN，IN）进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
-
在经常需要排序（order by）的列（没有where子句）上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；（group by）
-
只读型数据 多多益善
2.2 不该创建索引的列
对于那些在查询中很少使用或者参考的列不应该创建索引。
若列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
对于那些只有很少数据值或者重复值多的列也不应该增加索引。
这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
当修改操作的频率远高于查询操作时，不应该创建索引。（索引表达式涉及到的列）这是因为：
索引会增加每次修改的开销
索引更新需要额外的CPU和I/O资源
修改操作的累积影响远大于查询操作的性能提升
3、索引结构
MySQL中常用的索引结构（索引底层的数据结构）有：B-TREE ，B+TREE ，HASH 等。
3.1 B-TREE
B-树就是B树，多路搜索树，树高一层意味着多一次的磁盘I/O
B树的特征：
- 关键字分布在整颗树中；
- 任何一个关键字出现且只出现在一个结点中；
- 搜索有可能在非叶子结点结束；
- 结点中存储关键字和其对应的data
忽快忽慢
3.2 B+TREE
B+树是B-树的变体，也是一种多路搜索树.
B+树的特征：
- 所有关键字都出现在叶子结点的链表中，且链表中的关键字是有序的；
- 非叶子结点相当于是叶子结点的索引，不存储data，叶子结点存储data；
- 每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。
3.3 HASH
哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。
Hash索引仅仅能满足"=",“IN"和”<=>"查询，不能使用范围查询。也不支持任何范围查询，例如WHERE price > 100。
由于Hash索引比较的是进行Hash运算之后的Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤
4、索引分类
MySQL 的索引有两种分类方式：逻辑分类和物理分类。
4.1 逻辑分类
有多种逻辑划分的方式，比如按功能划分，按组成索引的列数划分等.
4.1.1 按功能划分
(1)主键索引
一张表只能有一个主键索引，不允许重复、不允许为 NULL；
PRIMARY KEY 自动创建
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
(2)唯一索引
数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
UNIQUE INDEX 手动
CREATE UNIQUE INDEX 索引名称 ON 表名(字段名(length));
# 或者
ALTER TABLE 表名 ADD UNIQUE (字段名);
(3)普通索引—辅助索引—二级索引
一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入；
CREATE INDEX 索引名称 ON 表名(字段名，....);
4.1.2 按列数划分
(1)单例索引
一个索引只包含一个列，一个表可以有多个单例索引。
(2)组合索引(联合索引)
一个组合索引包含两个或两个以上的列。查询的时候遵循 mysql 组合索引的 “最左前缀”原则，即使用 where 时条件要按照建立索引的时候字段的排列方式放置索引才会生效。
5、InnoDB和MyISAM索引实现
5.1 InnoDB索引实现
InnoDB使用B+TREE存储数据，除了主键索引为聚簇索引，其它索引均为非聚簇索引。
一个表中只能存在一个聚簇索引（主键索引），但可以存在多个非聚簇索引。
InnoDB表的索引和数据是存储在一起的，.idb表数据和索引的文件
通过MySQL8.0自带工具ibd2sdi解析.ibd文件
ibd2sdi innodb_test.ibd
["ibd2sdi"
,
{
"type": 1,
"id": 429,
"object":
{
"mysqld_version_id": 80042,
"dd_version": 80023,
"sdi_version": 80019,
"dd_object_type": "Table",
"dd_object": {
"name": "innodb_test",
"mysql_version_id": 80042,
"created": 20250929054934,
"last_altered": 20250929054934,
"hidden": 1,
"options": "avg_row_length=0;encrypt_type=N;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;",
"columns": [
{
"name": "id",
"type": 4,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 1,
"ordinal_position": 1,
"char_length": 11,
"numeric_precision": 10,
"numeric_scale": 0,
"numeric_scale_null": false,
"datetime_precision": 0,
"datetime_precision_null": 1,
"has_no_default": true,
"default_value_null": false,
"srs_id_null": true,
"srs_id": 0,
"default_value": "AAAAAA==",
"default_value_utf8_null": true,
"default_value_utf8": "",
"default_option": "",
"update_option": "",
"comment": "",
"generation_expression": "",
"generation_expression_utf8": "",
"options": "interval_count=0;",
"se_private_data": "table_id=1091;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 2,
"column_type_utf8": "int",
"elements": [],
"collation_id": 45,
"is_explicit_collation": false
},
{
"name": "name",
"type": 16,
"is_nullable": true,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 1,
"ordinal_position": 2,
"char_length": 200,
"numeric_precision": 0,
"numeric_scale": 0,
"numeric_scale_null": true,
"datetime_precision": 0,
"datetime_precision_null": 1,
"has_no_default": false,
"default_value_null": true,
"srs_id_null": true,
"srs_id": 0,
"default_value": "",
"default_value_utf8_null": true,
"default_value_utf8": "",
"default_option": "",
"update_option": "",
"comment": "",
"generation_expression": "",
"generation_expression_utf8": "",
"options": "interval_count=0;",
"se_private_data": "table_id=1091;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 4,
"column_type_utf8": "varchar(50)",
"elements": [],
"collation_id": 45,
"is_explicit_collation": false
},
{
"name": "age",
"type": 4,
"is_nullable": true,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 1,
"ordinal_position": 3,
"char_length": 11,
"numeric_precision": 10,
"numeric_scale": 0,
"numeric_scale_null": false,
"datetime_precision": 0,
"datetime_precision_null": 1,
"has_no_default": false,
"default_value_null": true,
"srs_id_null": true,
"srs_id": 0,
"default_value": "",
"default_value_utf8_null": true,
"default_value_utf8": "",
"default_option": "",
"update_option": "",
"comment": "",
"generation_expression": "",
"generation_expression_utf8": "",
"options": "interval_count=0;",
"se_private_data": "table_id=1091;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "int",
"elements": [],
"collation_id": 45,
"is_explicit_collation": false
},
{
"name": "email",
"type": 16,
"is_nullable": true,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 1,
"ordinal_position": 4,
"char_length": 400,
"numeric_precision": 0,
"numeric_scale": 0,
"numeric_scale_null": true,
"datetime_precision": 0,
"datetime_precision_null": 1,
"has_no_default": false,
"default_value_null": true,
"srs_id_null": true,
"srs_id": 0,
"default_value": "",
"default_value_utf8_null": true,
"default_value_utf8": "",
"default_option": "",
"update_option": "",
"comment": "",
"generation_expression": "",//计算列 生成表达式
"generation_expression_utf8": "",
"options": "interval_count=0;",
"se_private_data": "table_id=1091;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "varchar(100)",
"elements": [],
"collation_id": 45,
"is_explicit_collation": false
},
{
"name": "DB_TRX_ID",
"type": 10,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 2,
"ordinal_position": 5,
"char_length": 6,
"numeric_precision": 0,
"numeric_scale": 0,
"numeric_scale_null": true,
"datetime_precision": 0,
"datetime_precision_null": 1,
"has_no_default": false,
"default_value_null": true,
"srs_id_null": true,
"srs_id": 0,
"default_value": "",
"default_value_utf8_null": true,
"default_value_utf8": "",
"default_option": "",
"update_option": "",
"comment": "",
"generation_expression": "",
"generation_expression_utf8": "",
"options": "",
"se_private_data": "table_id=1091;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "",
"elements": [],
"collation_id": 63,
"is_explicit_collation": false
},
{
"name": "DB_ROLL_PTR",
"type": 9,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 2,
"ordinal_position": 6,
"char_length": 7,
"numeric_precision": 0,
"numeric_scale": 0,
"numeric_scale_null": true,
"datetime_precision": 0,
"datetime_precision_null": 1,
"has_no_default": false,
"default_value_null": true,
"srs_id_null": true,
"srs_id": 0,
"default_value": "",
"default_value_utf8_null": true,
"default_value_utf8": "",
"default_option": "",
"update_option": "",
"comment": "",
"generation_expression": "",
"generation_expression_utf8": "",
"options": "",
"se_private_data": "table_id=1091;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "",
"elements": [],
"collation_id": 63,
"is_explicit_collation": false
}
],
"schema_ref": "zzz",
"se_private_id": 1091,
"engine": "InnoDB",
"last_checked_for_upgrade_version_id": 0,
"comment": "",
"se_private_data": "",
"engine_attribute": "",
"secondary_engine_attribute": "",
"row_format": 2,
"partition_type": 0,
"partition_expression": "",
"partition_expression_utf8": "",
"default_partitioning": 0,
"subpartition_type": 0,
"subpartition_expression": "",
"subpartition_expression_utf8": "",
"default_subpartitioning": 0,
"indexes": [
{
"name": "PRIMARY",
"hidden": false,
"is_generated": false,
"ordinal_position": 1,
"comment": "",
"options": "flags=0;",
"se_private_data": "id=200;root=4;space_id=29;table_id=1091;trx_id=21782;",
"type": 1,
"algorithm": 2,
"is_algorithm_explicit": false,
"is_visible": true,
"engine": "InnoDB",
"engine_attribute": "",
"secondary_engine_attribute": "",
"elements": [
{
"ordinal_position": 1,
"length": 4,
"order": 2,
"hidden": false,
"column_opx": 0
},
{
"ordinal_position": 2,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 4
},
{
"ordinal_position": 3,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 5
},
{
"ordinal_position": 4,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 1
},
{
"ordinal_position": 5,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 2
},
{
"ordinal_position": 6,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 3
}
],
"tablespace_ref": "zzz/innodb_test"
},
{
"name": "idx_name",
"hidden": false,
"is_generated": false,
"ordinal_position": 2,
"comment": "",
"options": "flags=0;",
"se_private_data": "id=201;root=5;space_id=29;table_id=1091;trx_id=21782;",
"type": 3,
"algorithm": 2,
"is_algorithm_explicit": false,
"is_visible": true,
"engine": "InnoDB",
"engine_attribute": "",
"secondary_engine_attribute": "",
"elements": [
{
"ordinal_position": 1,
"length": 200,
"order": 2,
"hidden": false,
"column_opx": 1
},
{
"ordinal_position": 2,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 0
}
],
"tablespace_ref": "zzz/innodb_test"
}
],
"foreign_keys": [],
"check_constraints": [],
"partitions": [],
"collation_id": 45
}
}
}
}
]
5.1.1 聚簇索引（主键索引）
B+树 叶子节点包含数据表中行记录就是聚簇索引（索引和数据是存放在一块的）
可以看到叶子节点包含了完整的数据记录，这就是聚簇索引。因为InnoDB的数据文件（.idb）按主键聚集，所以InnoDB必须有主键（MyISAM可以没有）
如果没有显示指定主键，则选取首个为唯一且非空的列作为主键索引
如果还没具备，则MySQL自动为InnoDB表生成一个隐含字段ROWID作为主键，这个字段长度为6个字节，无符号整数 0-2^48-1(281万亿)。
主键索引结构分析：
- B+树单个叶子节点内的行数据按主键顺序排列，物理空间是连续的（聚簇索引的数据的物理存放顺序与索引顺序是一致的）；
- 叶子节点之间是通过指针连接，相邻叶子节点的数据在逻辑上也是连续的（根据主键值排序），实际存储时的数据页（叶子节点）可能相距甚远。
5.1.2 非聚簇索引（辅助索引或二级索引）
在聚簇索引之外创建的索引（不是根据主键创建的）称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行数据记录，而是主键值。首先通过辅助索引找到主键值，然后到主键索引树中通过主键值找到数据行。
5.2MyISAM索引实现
MyISAM也使用B+Tree作为索引结构，但具体实现方式却与InnoDB截然不同。MyISAM使用的都是非聚簇索引。
5.2.1 MyISAM主键索引
叶子节点的存放的是数据记录的地址。也就是说索引和行数据记录是没有在一起的
5.2.2 MyISAM辅助索引
在MyISAM中，主键索引和辅助索引（Secondary key）在结构上没有任何区别，只是主键索引要求key是唯一的，而辅助索引的key可以重复。 MyISAM辅助索引也是非聚簇索引。
5.3 InnoDB和MyISAM的索引检索过程
对于InnoDB和MyISAM而言，主键索引是根据主关键字来构建的B+树存储结构，辅助索引则是根据辅助键来构造的B+树存储结构，彼此的索引树都是相互独立的。
InnoDB中的辅助索引的访问需要两次索引查找（覆盖索引优化），第一次从辅助索引树找到主键值，第二次根据主键值到主键索引树中找到对应的行数据。
MyISAM使用的是非聚簇索引，表数据存储在独立的地方，这两棵（主键和辅助键）B+树的叶子节点都使用一个地址指向真正的表数据。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。
6、操作索引
6.1 常规创建索引
索引名称 index_name 是可以省略的，省略后，索引的名称和索引列名相同。
-- 创建普通索引
CREATE INDEX index_name ON table_name(col_name);
-- 创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name(col_name);
index_name不同??????
-- 创建组合索引
CREATE INDEX index_name ON table_name(col_name_1,col_name_2);
-- 创建唯一组合索引
CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);
6.2 创建索引另一种表达方式
ALTER TABLE table_name ADD INDEX index_name(col_name);
6.3 删除索引
-- 直接删除索引
DROP INDEX index_name ON table_name;
-- 删除索引另一种表达方式
ALTER TABLE table_name DROP INDEX index_name;
7、索引使用技巧
7.1 最左前缀原则
-- 创建联合索引（组合索引）
CREATE INDEX idx_name_age_salary ON employees(name, age, salary);
- 第一层：按姓名排序
- 第二层：姓名相同时，按年龄排序
- 第三层：姓名和年龄都相同时，按薪资排序
查询要使用这个索引，必须从最左边开始连续使用
本质：不是"必须用所有列"，而是"必须从最左边开始，连续使用"
CREATE INDEX idx_name_age_salary ON employees(name, age, salary);
-- name -> age -> salary
-- 1. 只用最左边的列
SELECT * FROM employees WHERE name = '张三'; #可以使用
-- 2. 用前两个列
SELECT * FROM employees WHERE name = '张三' AND age = 25; #可以使用
-- 3. 用全部三个列
SELECT * FROM employees WHERE name = '张三' AND age = 25 AND salary = 8000; #可以使用
-- 4. 跳过了最左边的列
SELECT * FROM employees WHERE age = 25; -- ❌ 不能用
SELECT * FROM employees WHERE salary = 8000; -- ❌ 不能用
-- 5. 不连续使用
SELECT * FROM employees WHERE name = '张三' AND salary = 8000; -- ❌ 只能用name部分
- 能用索引：
第一列、第一列+第二列、第一列+第二列+第三列 - 不能用索引：
第二列、第三列、第一列+第三列
“宁可少用，不可跳用；从左开始，连续使用”
7.2 覆盖索引
覆盖索引是指索引包含查询所需的所有字段，无需回表查询数据
覆盖索引的查询效率极高，原因在与其不用做回表查询
我们建立的辅助索引的叶子节点只有主键值和columns
-- 创建包含所需所有字段的复合索引
CREATE INDEX idx_user_status_age_name ON users(status, age, username);
-- 查询只使用索引中的字段，无需访问表数据
SELECT username, age FROM users WHERE status = 'active' AND age > 25;
7.3 索引失效
-- 1. 创建测试表
DROP TABLE IF EXISTS test_index;
CREATE TABLE test_index (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT,
salary DECIMAL(10,2),
email VARCHAR(100),
status TINYINT DEFAULT 1,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
description TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 2. 插入30条测试数据
INSERT INTO test_index (name, age, salary, email, status, description) VALUES
-- 年龄20-29岁
('张三', 25, 8000.00, 'zhangsan@email.com', 1, 'Java开发工程师，擅长后端开发'),
('李四', 28, 9500.00, 'lisi@email.com', 1, '前端开发专家，精通Vue和React'),
('王五', 22, 6000.00, 'wangwu@email.com', 1, 'Python数据分析员'),
('赵六', 26, 8500.00, 'zhaoliu@email.com', 1, '全栈工程师，技术全面'),
('钱七', 24, 7000.00, 'qianqi@email.com', 1, '数据库管理员'),
('孙八', 29, 10000.00, 'sunba@email.com', 1, '系统架构师'),
('周九', 23, 6500.00, 'zhoujiu@email.com', 1, '初级开发工程师'),
('吴十', 27, 9000.00, 'wushi@email.com', 1, '移动应用开发者'),
('郑一', 21, 5500.00, 'zhengyi@email.com', 1, '实习生，学习中'),
('冯二', 25, 8200.00, 'fenger@email.com', 1, 'Web安全专家'),
-- 年龄30-39岁
('陈三', 35, 15000.00, 'chensan@email.com', 1, '技术总监，管理团队'),
('林四', 32, 12000.00, 'linsi@email.com', 1, '高级项目经理'),
('何五', 38, 18000.00, 'hewu@email.com', 1, 'CTO，公司技术负责人'),
('高六', 31, 11000.00, 'gaoliu@email.com', 1, '资深架构师'),
('马七', 36, 16000.00, 'machi@email.com', 1, '大数据专家'),
('罗八', 33, 13000.00, 'luoba@email.com', 1, '云计算工程师'),
('梁九', 39, 19000.00, 'liangjiu@email.com', 1, '人工智能研究员'),
('宋十', 34, 14000.00, 'songshi@email.com', 1, 'DevOps工程师'),
('谢一', 30, 10500.00, 'xieyi@email.com', 1, 'SRE站点可靠性工程师'),
('韩二', 37, 17000.00, 'haner@email.com', 1, '区块链开发者'),
-- 年龄40-49岁
('杨三', 45, 20000.00, 'yangsan@email.com', 1, '首席科学家'),
('朱四', 42, 18500.00, 'zhusi@email.com', 1, '技术顾问'),
('秦五', 48, 22000.00, 'qinwu@email.com', 1, '副总裁'),
('尤六', 41, 17500.00, 'youliu@email.com', 1, '技术布道师'),
('许七', 46, 21000.00, 'xuqi@email.com', 1, '创新实验室负责人'),
('何八', 43, 19000.00, 'heba@email.com', 1, '研发总监'),
('吕九', 49, 23000.00, 'lvjiu@email.com', 1, '院士级专家'),
('施十', 44, 19500.00, 'shishi@email.com', 1, '技术战略官'),
('孔一', 40, 17000.00, 'kongyi@email.com', 1, '资深技术专家'),
('曹二', 47, 21500.00, 'caoeer@email.com', 1, '技术合伙人');
-- 使用递归CTE（MySQL 8.0+推荐）
INSERT INTO test_index (name, age, salary, email, status, description)
WITH RECURSIVE nums AS (
SELECT 1 as n
UNION ALL
SELECT n + 1 FROM nums WHERE n < 1000
)
SELECT
CONCAT('user_', 30 + n) as name,
FLOOR(20 + (RAND() * 30)) as age, -- 年龄20-49岁
ROUND(5000 + (RAND() * 20000), 2) as salary, -- 薪资5000-25000
CONCAT('user_', 30 + n, '@company.com') as email,
CASE WHEN RAND() > 0.1 THEN 1 ELSE 0 END as status, -- 90%状态为1
CONCAT('员工描述信息 - ', n) as description
FROM nums;
-- 更新表统计信息（重要！）
ANALYZE TABLE test_index;
7.3.1 最左前缀原则失效
-- 创建一个联合索引
CREATE INDEX idx_name_age_salary ON employees(name, age, salary);
-- ❌ 失效：跳过最左列
EXPLAIN SELECT * FROM test_index WHERE age = 25 AND salary = 8000;
-- ✅ 有效：从最左开始
EXPLAIN SELECT * FROM test_index WHERE name = '张三' AND age = 25;
7.3.2 > < 范围查询
联合索引中，当前面的列使用范围查询（>、<、BETWEEN等）时，后续列只能进行等值匹配，无法再使用范围查询。
-- 创建联合索引
CREATE INDEX idx_name_age_salary ON test_index(name, age, salary);
-- 部分有效 name age 使用了
EXPLAIN SELECT * FROM test_index
WHERE name = '张三' AND age > 25 AND salary = 8000;
-- 部分有效 name使用了
EXPLAIN SELECT * FROM test_index
WHERE name > '李' AND age = 25 AND salary = 8000;
7.3.3 like %xx
搜索键值以通配符%开头（如：like '%abc'），则索引失效，直接全表扫描；若只是以%结尾，则不影响索引构建。
-- 创建联合索引
CREATE INDEX idx_name_age_salary ON test_index(name, age, salary);
-- ✅ 有效：前缀匹配
EXPLAIN SELECT * FROM test_index WHERE name LIKE '张%';
-- ❌ 失效
EXPLAIN SELECT * FROM test_index WHERE name LIKE '%三';
-- ❌ 失效
EXPLAIN SELECT * FROM test_index WHERE name LIKE '%三%';
7.3.4 对索引列进行运算
如果查询条件中含有函数或表达式，将导致索引失效而进行全表扫描。
-- ❌ 失效：对列使用函数
EXPLAIN SELECT * FROM test_index WHERE UPPER(name) = '张三';
-- ✅ 有效
EXPLAIN SELECT * FROM test_index WHERE name = '张三';
7.3.5 or 条件索引问题
OR连接不同列时会破坏最左前缀匹配的连续性.
当OR连接不同列的查询条件时，联合索引的最左前缀匹配会被打断，导致索引无法有效使用。
-- ❌ 失效：OR连接不同列
EXPLAIN SELECT * FROM test_index WHERE name = '张三' OR age = 25;
-- ✅ 有效：同一列的OR
EXPLAIN SELECT * FROM test_index WHERE name = '张三' OR name = '李四';
7.3.6 数据类型不一致（隐式类型转换导致的索引失效）
CREATE INDEX idx_age ON test_index(age);
-- ❌ 失效：隐式类型转换
EXPLAIN SELECT * FROM test_index WHERE name = 16; -- 字符串与数字比较
-- 实际执行
WHERE TO_NUMBER(name) = 16
-- ✅ 有效：类型匹配
EXPLAIN SELECT * FROM test_index WHERE name = '16';
7.3.7 != 问题
!= 操作符不会导致索引完全失效，但会显著降低索引的使用效率！
特殊情况会导致完全失效
-- 如果'张三'只占1%的数据
WHERE name != '张三' -- 需要返回99%的数据
-- 优化器可能认为全表扫描更快
WHERE name != 'zzz'
-- 不会包含 name IS NULL 的记录
-- 需要额外处理NULL值
7.3.8 order by问题
ORDER BY 本身不会导致索引失效，但如果排序字段无法利用索引的有序性，就会产生额外的排序操作，严重影响性能！
CREATE INDEX idx_name_age ON test_index(name ASC, age ASC);
-- 生效
EXPLAIN SELECT * FROM employees WHERE name = '张三' ORDER BY age ASC;
-- 失效1：排序字段不在索引中
-- 索引：(name, age)
EXPLAIN SELECT * FROM employees WHERE name = '张三' ORDER BY salary -- salary不在索引中
-- 失效2：排序方向不一致
-- 索引：(name ASC, age ASC)
EXPLAIN SELECT * FROM employees WHERE name = '张三' ORDER BY name ASC, age DESC -- 方向不一致
-- 跳过中间列
-- 索引：(name, age, salary)
EXPLAIN SELECT * FROM employees WHERE name = '张三' ORDER BY name, salary -- 跳过了age
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
