一、 索引特性

1. 索引（Index）是一种数据结构，其目的在于提高查询效率。
2. 索引列对数据进行排序，降低数据排序的成本，降低了 CPU 的消耗，减少磁盘 I/O。
3. 虽然索引提高了查询速度，但是会降低更新表的速度，如对表进行 insert,update 和 delete。
4. 索引提升了数据查找的效率，降低了数据操作的效率，在设计表和索引的时候，要根据实际情况在两者之间权衡。
5. 在使用 order by、group by 子句进行数据检索时，利用索引可以减少排序和分组的时间。

二、 索引使用

• 创建普通（唯一）索引

Oracle

create [unique] index 索引名 on 表名(字段名1,字段名2,......,字段名n);

---

MySQL

create [unique] index 索引名 on 表名(字段名1(length));
或
alter 表名 add [unique] index 索引名 on (字段名1(length));

• 删除索引

Oracle

drop index 索引名;

注：drop index 无法删除用于强制唯一/主键的索引，删除主键要用以下语句：
alter table 表名 drop constraint 主键名;

---

MySQL

drop index 索引名 on 表名

• 修改索引

Oracle

修改索引名：
alter index 索引名 rename to 新索引名;

重建索引：减少硬盘碎片、提高数据库系统性能
alter index 索引名 rebuild;

---

MySQL

使用 alter 命令添加和删除索引进行修改

三、 索引使用注意点

1. 索引使用场景

适用场景

• 某字段频繁作为查询条件，特别是数据量大的表字段
• 通常使用 order by、group by 子句时
• 经常与其他表进行连接的表，在连接字段上应该建立索引

不适用的场景

• 表字段经常进行更新操作（增删改）
• 表数据量较少

2. 索引使用常见问题及分析

（1）最左前缀原则

• 使用 like 模糊查询时，使用 “code%” 索引可用，“%code” 索引失效
• 使用组合索引时需要包含从左到右的字段，若不包含左边的字段，则会导致索引失效

例：create index i_pmbb_employee on pmbb_employee(id,code);
索引未使用的情况：select code,name from pmbb_employee where code="test";

（2）字符匹配原则

• 数据类型不匹配会导致索引失效
• 联合查询时，多张表若字符集不匹配会导致索引失效

（3）使用不等于情况

• 不等于导致索引失效，不等于的情况包括（!= 、<、>、not in）

（4）其他情况

• 字段内容为 null, 导致索引失效
• or 前后条件都包含索引则走索引，or 前后有一个不包含索引索引失效
• 添加索引的字段上使用函数或者计算，导致索引失效
• 使用组合索引及 order by 子句时，避免不同列排序规则不同

四、 索引使用情况查询及分析：explain

Oracle

执行计划是一条查询语句在 Oracle 中的执行过程或访问路径的描述。
执行计划描述了 SQL 引擎为执行 SQL 语句进行的操作；分析 SQL 语句相关的性能问题或仅仅质疑查询优化器的决定时，必须知道执行计划；所以执行计划常用于 sql 调优。

常用语句：

explain plan for *SQL*;
-- 查看索引使用明细
select * from table(dbms_xplan.display);

例：

explain plan for select MRL_CODE, PROCESS_STATE from UQCM_CHK_BILL where code='WIP-20200909-6766';
select * from table(dbms_xplan.display);

结果：

MySQL

使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MYSQL 是如何处理 SQL 语句的，可以用来分析查询语句或是表的结构的性能瓶颈，作用如下:

1. 表的读取顺序
2. 哪些索引可以使用
3. 数据读取操作的操作类型
4. 那些索引被实际使用
5. 表之间的引用
6. 每张表有多少行被优化器查询

常用语句：

explain *SQL*;

例：

`explain select CREATE_ID, WORK_ORDER_GID from umpp_lot where LOT_CODE='BAT-20220105-0000113';`

结果：