关于索引使用与优化时需要用到和了解的知识

关于索引使用与优化时需要了解的知识

无法使用索引的语句

查询的是语句中索引使用的边界不明确时

A、B两个字段组成一个索引，这时SELECT * fROM T WHERE B = 'xxx',那么这个索引将不会生效，但是如果是SELECT B fROM T WHERE B = 'xxx'，这个时候就会使用索引查询。
当索引的查询字段中存在null值时

A字段上建立索引，SELECT COUNT(A) FROM T,这个时候因为A字段中存在空值，那么不可能使用索引查询，除非在包含子查询语句对A数值上做限制，SELECT COUNT(A) FROM T WHERE A NOT NULL,有或者在设计表时，将A设置成not null属性也可以。
当语句中索引字段使用函数数据时

A字段上建立索引，SELECT * FROM T WHERE FUN(A) = 'xxx',这个时候实际上不是使用字段A的数据，而是A字段计算之后的数据，如果希望这个语句使用索引查询，那么索引的建立应该建立在函数上，CREATE INDEX IDX FUNT ON T(FUN(A));
当有语句中有隐式转换时

其实类似于<u>第三点</u>,部分字段当类型是字符串，但是给出的条件是数字时，会被优化器优化，隐式的使用函数，将字符串转换成数字，要么重新添加基于函数的索引，要么就是修改sql语句中子查询语句中比较参数的类型，让查询字段数据类型与比较参数数据类型一致。
数据量太小时

当一次表中的数据太小，以至于最小的数据块即可包含大部分数据时，将不会使用索引查询。
!=或者<>(不等于)
not in和not exist
通配符传询字符串，通配符在第一位时

因为首位任意匹配，所以只能全表查询
表字段的属性设置不当导致聚簇因子过大时

因为字符串和数字类型在数据库中存储的方式是不一样的，所以计算索引的方式有区别，当索引字段设置不当，导致聚簇因子，数据库会放弃使用索引而直接全表查询。

监控索引的使用情况

监控Oracle索引使用情况

开启指定索引使用监控

ALTER INDEX <index_name> MONITORING USAGE;

创建索引

CREATE INDEX <indexName> ON <tableName>(<columnName>);

删除索引

DROP INDEX <indexName> ON <tableName>;

查询相关表指定表中的索引使用情况

SELECT * FROM V$OBJECT_USAGE WHERE TABLE_NAME = '<TABLE_NAME>';

oracle可以直观的看到表中的索引是否使用过，网上也有一些脚本，可以统计一段时间内没有使用的索引，相对于mysql来讲比较容易优化索引的使用情况。

监控mysql索引使用情况

创建索引

CREATE INDEX <indexName> ON <tableName>(<columnName>(length));

添加索引

ALTER TABLE <tableName> ADD INDEX <indexName>(<columnName>)

删除索引

DROP INDEX <indexName> ON <tableName>;

查询索引使用情况
```
show status like 'Handler_read%';
```

mysql中没有类似Oracle的针对单个索引监控的功能，只能粗放性的查询到全局中索引使用的结果统计

参考资料：
sql中索引不会被用到的几种情况
 如何监控ORACLE索引使用与否
 mysql使用率监控
 MySQL 关于索引以及使用效率对比，附测试数据代码
 Oracle 建立索引及利用索引的SQL语句优化

最后编辑于：2018.10.22 15:20:59