17.数据库对象：索引

数据库对象：索引(INDEX)

• 例如：select * from table1 where id=10000

  • 数据库在执行时，遍历整个了表，直到ID等于10000的这一行被找到为止

  • 当有了索引后，我们可以通过它快速的定位到ID等于10000的这一行

• 索引是一种可以提高查询效率的一种结构，不同的数据库，对这种结构的设计是不同的

• 数据库索引好比是一本书前面的目录，通过目录我们可以快速定位到我们想要内容的位置

• 索引对数据的统计是数据库自动完成的，应用索引时，只要数据库认为可以使用某个已创建的索引时就会自动应用

• 命名通常以’idx’开头，如：idx_emp_ename

创建索引

• 当某个字段需要索引时，就可以为之创建索引，该索引被称为单列索引

• 索引也可以指定多个字段，这种索引被称为复合索引，也称为多列索引

• 语法：CREATE UNIQUE INDEX index_name ON table_name(字段1,字段2…)

  • index_name：索引名称

  • table_name：表名

  • UNIQUE：表示唯一索引，不允许指定的字段中任何两行数据具有相同索引值

复合索引(例子)

• 经常在ORDER BY子句中使用job和sal作为排序依据，可以建立复合索引：

  • CREATE INDEX idx_emp_job_sal ON emp(job,sal);

• 当做下面的查询时，会自动应用索引：

  • SELECT empno,ename,job,sal FROM emp ORDER BY job,sal;

基于函数的索引

• 索引也可以指定某个函数，如：

  • 需要在emp表的ename列上执行大小写无关的搜索，可以在此列上建立一个基于UPPER函数的索引：

    • CREATE INDEX idx_emp_ename_upper ON emp(UPPER(ename));

  • 当做下面的查询时，会自动应用索引：

    • SELECT * FROM emp WHERE UPPER(ename)=‘KING’;

修改和删除索引

• 如果经常在索引指定的列上执行DML操作，需要定期重建索引，提高索引的空间利用率

  • 语法：ALTER INDEX index_name REBULID;

• 当一个表上有不合理的索引，会导致操作性能下降，需要删除该索引

  • 语法：DROP INDEX index_name;

合理使用索引提升查询效率

1. 为经常出现在WHERE子句中的列创建索引

2. 为经常出现在ORDER BY、DISTINCT后面的字段建立索引，如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致

3. 为经常作为表的连接体条件的列创建索引

4. 不要再经常做DML操作的表上建立索引

5. 不要再小表上建立索引，小表指的是数据量小的

6. 限制表上的索引数目，索引并不是越多越好

7. 删除很少被使用的，不合理的索引

演示：索引

• 创建单列索引

  CREATE INDEX idx_emp_ename ON emp(ename);

• 复合索引

  CREATE INDEX idx_emp_job_sal ON emp(job,sal);

• 基于函数的索引

  CREATE INDEX idx_emp_ename_upper ON emp(UPPER(ename));

• 修改索引

  ALTER INDEX idx_emp_ename REBUILD;

• 删除索引

  DROP INDEX idx_emp_ename;