MySQL索引的使用

一、索引的概念

以MySQL为例，索引是一种用于提高数据库检索效率的数据结构。它类似于书籍的目录，能够加速数据库查询操作。索引的使用可以大大减少数据库系统需要扫描的数据量，从而加快检索速度。

MySQL对字段创建索引后，索引数据会保存在磁盘上。MySQL使用存储引擎来管理数据的存储和检索，而不同的存储引擎对索引的处理方式可能会有所不同。一般情况下，MySQL的InnoDB存储引擎会将表的数据和索引保存在磁盘上的数据文件中。创建索引后，MySQL会在磁盘上生成索引文件，以加速对相应字段的检索操作。

索引文件的存在并不意味着所有的索引数据都直接存储在文件中，而是存储了一些索引的数据结构，实际的数据可能分散在磁盘的不同位置。这样的设计是为了提高对大型表的查询性能。

---

二、索引的使用场景

• 数据量大：添加索引可以提高数据检索的效率，降低数据排序的成本。
• 经常用于查询的字段：经常查询的字段，使用索引可以显著提高查询性能。
• 作为连接操作的字段：在连接多个表进行查询时，通过索引连接操作的列可以提高连接的速度。
• 排序和分组的字段：如果经常对某个列进行排序或分组，使用索引可以提高排序和分组操作的性能。
• 频繁用于条件的字段：该字段经常出现在where的后面，以条件的形式存在。
• 该字段很少的DML(insert delete update)操作：因为DML之后，索引需要重新排序。

---

三、索引类型

索引的类型可分为两大类：单列索引和组合索引。

• 单列索引：
  1.主键索引：数据库表的主键字段会自动创建索引。
  2.唯一索引：当某个列添加了Unique约束，也会自动创建唯一索引，要求值必须是唯一的。
  3.普通索引：给普通字段添加索引就是普通索引。

• 组合索引：
  1.多个字段合起来创建一个索引，只有当查询条件中使用了组合索引的第一个字段，索引才会被使用，使用组合索引是遵循最左前缀原则。（create index index_name on table1(id,name)；）

---

四、索引创建语句

• 主键索引：一般在创建主键的时候自动创建主键索引
• 唯一索引：create unique index 要创建的索引名 on 要创建索引的表名(要创建索引的字段);
• 普通索引：create index 要创建的索引名 on 要创建索引的表名(要创建索引的字段);
• 组合索引：create index 要创建的索引名 on 要创建索引的表名(要创建索引的字段,要创建索引的字段，...)；

删除索引：drop index 要删除的索引名 on 要删除的索引所在的表名;

---

五、索引实现原理

索引在MySQL当中都是一个树的形式存在。

索引的实现原理实际上就是缩小扫描范围，避免全表扫描。

---

六、索引失效的情况

• 模糊查询时以通配符开头
  原因是因为模糊匹配当中以“%”开头了，MySQL找不到，MySQL索引失效会变成全表扫描的操作，当%加在右边时可以使用，所以尽量避免模糊查询的时候以“%”开始。

例：select * from user where username like '%abc';

• 使用 or 操作
  使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有索引，才会走索引，如果or其中一边有一个字段没有索引，那么另一个字段上的索引也会失效。
  不建议使用or或使用union联合查询。
  user表，loc字段有索引，name字段没有索引，索引失效

例：select * from user where loc = '广东' or name = '小明';
可修改为：select * from user where loc = '广东' union select * from user where name = '小明';

• 使用复合索引的时候，没有用到左侧的字段作为查找条件，索引失效
  创建复合索引，但是没有遵循最左前缀法则，则会出现索引失效的情况。

例：create index emp_job_sal_index on emp(job,sal);
索引正常： select * from emp where job = 'MANAGER';
索引失效： select * from emp where sal = 800;

• 在where当中索引列参加了运算或函数，索引失效
  因为索引是按照列值的顺序存储的，而运算或函数操作可能改变列值的顺序，使得索引无法被有效使用，导致无法直接使用索引进行精确匹配。

例：create index emp_sal_index on emp(sal);     create index emp_date_index on emp(hire_date);
索引正常：select * from emp where sal = 800;
索引失效：select * from emp where sal+1 = 800;
索引失效：select * from emp where year(hire_date) = 2024;

• 字符串不加单引号索引失效
  在 SQL 中，字符串常量通常需要使用单引号括起来。如果在查询中使用字符串常量而没有加上单引号，可能导致索引失效，因为数据库系统可能无法正确地识别字符串。

例：create index emp_department_index on employees (department);
索引正常：select * from employees where department = 'HR';
索引失效：select * from employees where department = HR;