本文共 2083 字，大约阅读时间需要 6 分钟。

MySQL索引的使用与优化实践

索引是数据库优化最常用也是最重要的手段之一。通过合理使用索引，可以显著提升MySQL的查询效率，解决大多数性能优化问题。本文将从实际操作角度，详细介绍索引的使用方法和常见优化技巧。

1. 验证索引提升查询效率

在实际项目中，索引对提高查询效率的作用可以通过以下实验验证。

数据背景

我们创建了一个名为 tb_item 的数据库表，该表存储了约250万记录。表结构如下：

create table `tb_item` (    `id` int auto_increment primary key,    `title` varchar(255),    `price` decimal(19,2),    `num` int,    `categoryid` int,    `status` enum('1','0'),    `sellerid` int,    `createtime` datetime,    `updatetime` datetime) engine=innodb default charset=utf8mb4;

查询效率对比

mysql> select * from tb_item where id=1800;

执行时间为0.00秒，效率非常高。这是因为 id 字段是主键，自动拥有了主键索引。

2. 使用 title 字段查询：

mysql> select * from tb_item where title='货物1000号';

执行时间为3.68秒，效率较低。原因在于 title 字段没有索引。

索引创建与验证

为了提升 title 字段的查询效率，我们为其创建一个单列索引：

create index idx_item_title on tb_item(title);

创建索引后，再次执行查询：

mysql> select * from tb_item where title='货物1000号';

此时执行时间降至0.07秒，验证了索引对查询效率的显著提升。

---

2. 索引的使用原则

索引的使用并非一概而治，需要遵循以下原则，才能真正发挥其优势。

2.1 准备环境

在实际应用中，索引的选择和设计需要充分考虑查询需求。以下是一个典型的索引设计案例：

create index idx_seller_name_sta_addr on tb_seller(name, status, address);

此索引覆盖了 name、status 和 address 三个字段，适用于多字段查询。

---

3. 避免索引失效

索引失效会导致查询效率大幅下降，常见原因包括以下几种：

3.1 全值匹配

索引生效的前提是查询条件必须匹配索引中的所有列。例如：

select * from tb_seller where name='小米科技' and status='1' and address='北京市';

此查询使用了 idx_seller_name_sta_addr 索引。

3.2 最左前缀法则

索引的查询必须从最左边的列开始，并且不能跳过中间的列。例如：

select * from tb_seller where name='小米科技' and status='1';

此查询符合最左前缀法则，索引生效。

3.3 范围查询右边的列

索引失效的常见情况是使用范围查询。例如：

select * from tb_seller where status>='1' and name='小米科技';

此查询使用了 idx_seller_name_sta_addr 索引。

3.4 不要在索引列上进行运算操作

如 substring(name,3,2)，会导致索引失效。正确的做法是避免在索引列上进行复杂操作。

3.5 范围查询右边的列

索引失效的另一种情况是使用范围查询。例如：

select * from tb_seller where name like '%科技';

此查询如果以百分号开头，索引可能失效。

3.6 使用 OR 分割条件

当 OR 前的条件有索引，而后面的条件没有索引时，索引可能失效。例如：

select * from tb_seller where name='小米科技' or nickname='小米官方旗舰店';

此查询索引失效。

---

4. 查看索引使用情况

为了监控索引的使用情况，可以使用以下MySQL命令：

show global status like 'Handler_read%';

此命令可以显示索引的读取情况，帮助我们了解索引是否真正被使用。

---

5. 优化建议

3. 尽量使用复合索引，而不是单列索引。
4. 避免全表扫描，通过优化查询条件，尽量覆盖索引。
5. 定期检查索引，删除过时或不再使用的索引。
6. 监控索引性能，通过查看 Handler_read 系统变量，评估索引的使用效果。

通过以上方法，可以最大化索引的利用率，显著提升MySQL的查询性能。

转载地址：http://rhdfk.baihongyu.com/