关于索引使用与优化时需要用到和了解的知识
关于索引使用与优化时需要了解的知识
无法使用索引的语句
-
查询的是语句中索引使用的边界不明确时
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语句优化
