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

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

无法使用索引的语句

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

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

2. 当索引的查询字段中存在null值时

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

3. 当语句中索引字段使用函数数据时

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

4. 当有语句中有隐式转换时

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

5. 数据量太小时

   当一次表中的数据太小，以至于最小的数据块即可包含大部分数据时，将不会使用索引查询。

6. !=或者<>(不等于)

7. not in和not exist

8. 通配符传询字符串，通配符在第一位时

   因为首位任意匹配，所以只能全表查询

9. 表字段的属性设置不当导致聚簇因子过大时

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

监控索引的使用情况

监控Oracle索引使用情况

1. 开启指定索引使用监控

   ALTER INDEX <index_name> MONITORING USAGE;
   

2. 创建索引

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

3. 删除索引

   DROP INDEX <indexName> ON <tableName>; 
   

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

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

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

监控mysql索引使用情况

1. 创建索引

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

2. 添加索引

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

3. 删除索引

   DROP INDEX <indexName> ON <tableName>; 
   

4. 查询索引使用情况

   show status like 'Handler_read%';
   

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

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