一．索引失效的情况
第1种情况（一些特殊符号）：
例如： select * from emp where ename like '%T'; 字段ename上即使添加了索引，也不会走索引，为什么？
原因模糊匹配like当中以“%”开头了，mysql找不到。另外<>，not exists，not in也不会走索引；
第2种情况：
使用or,or两边的条件字段都有索引,才会走索引;其中任意一边的任何一个字段没有索引，那么另一个字段上的索引也会失效。
解决方案：可以使用union联合查询;
例如：dept表，loc字段有索引，deptname字段没有索引；
select * from dept where loc = '福建' or deptname = '信息部';
查询位于福建的或者信息部的部门信息，由于deptname没有索引，会导致loc的索引失效，可以使用union联合查询，这样loc字段的索引还是生效的；
select * from dept where loc = '福建' union select * from dept where deptname = '信息部';
第3种情况：
使用复合索引的时候，没有用到最左侧的字段作为查找条件，索引失效
备注：什么是复合索引？两个字段，或者更多的字段联合起来添加一个索引，叫做复合索引。
create index emp_job_sal_index on emp(job,sal);
索引正常：explain select * from emp where job = 'MANAGER';
索引失效：explain select * from emp where sal = 800;
第4种情况：
在where当中索引列使用了运算（+，-，*，/，!，<> 等），索引失效。
create index emp_sal_index on emp(sal);
索引正常：explain select * from emp where sal = 800;
索引失效：explain select * from emp where sal+1 = 800;
第5种情况：
在where当中索引列使用了函数
ename字段有索引
explain select * from emp where lower(ename) = 'smith';
第6种情况：
小表查询或查询的数据量大于表数据量的30%，走全表扫描更快，会不使用索引；
第7种情况：
隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误. 例如表的字段tu_mdn定义为varchar2(20),但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效.
错误的例子：select * from test where tu_mdn=13333333333;
正确的例子：select * from test where tu_mdn='13333333333';
第8种情况：
没有查询条件，或表中未建立索引, 或where条件中不包含索引对应列；
第9种情况：
索引本身失效；特别是短时间内，有大量的数据变更，统计信息不准确，导致索引失效；oracle压测经常碰到；需在线重建索引+数据分析；如果仍然不能解决，需DBA介入，进行索引固化；
二.SQL语句执行计划解析，查看SQL的索引使用情况
MySQL的Explain关键字
在select语句之前增加explain关键字，可以模拟优化器，分析查询语句或结构的性能瓶颈，返回执行计划的信息，而不是执行这条SQL；执行结果显示如下几列。
注意：如果from中包含子查询，仍会执行该子查询，将结果放入临时表中
id列
select的序号列, 有几个select就有几个id, 并且id的顺序是按select出现的顺序增长的, id越大执行优先级越高, 相同则由上而下执行, null则最后执行
select_type列
select_type列表示对应简单还是复杂的查询.
simple: 简单查询, 语句不包含子查询和union
primary: 复杂查询中最外层的select
subquery: 包含在select中的子查询(不在from字句中)
derived: 包含在from子句中的子查询, Mysql会将结果保存在一个临时表中, 也叫派生表(derive)
union: 在union中的第二个和随后的select
table列
表示这一列的一行正在访问哪一个表；
type列
表示关联类型或访问类型, 即Mysql决定如何查找表中的行, 一般要保证到range以上, 最好到ref
NULL: 这种情况是不需要去表中查询数据, 可以直接从索引中获取
system: const的特例, 整张表中只有一条数据
const: 通过主键或者唯一的键来查询数据, 表中只有一条匹配数据, 只需要查询一次, 速度非常快
eq_ref: primary key 或unique key索引的所有部分被连接使用，最多只会返回一条符合条件的记录。
这可能是在const之外最好的联接类型了，简单的select查询不会出现这种type。
ref: 相比eq_ref不使用唯一索引, 而是使用非唯一索引或唯一索引部分前缀, 索引要和某个值比较, 可能返回多个符合列
range: 范围扫描通常出现在in(), between, <>=等语句中, 使用索引给定范围的行
index: 通过扫描二级索引就可以获取到需要的字段, 不需要再进行回表获取数据
(主键索引在叶子节点会存储所有数据, 而二级索引只存储主键, 所以通常来说所需字段如果能通过二级索引全部获取 到的话会优先从二级索引获取)
这里是因为表中只有id和name属性, 通过idx_name二级索引可以直接获取所有字段
ALL: 扫描全表, 扫描聚簇索引的所有叶子结点.
possible_keys列
这一列显示可能使用哪些索引来查找.
可能出现possible_keys有值，而key显示NULL的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查where子句看是否可以创造一个适当的索引来提高查询性能，然后用explain查看效果。
key列
这一列显示mysql实际采用哪个索引来优化对该表的访问。如果没有使用索引，则该列是NULL。
如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用force index、ignore index。
key_len列
这一列显示了mysql在索引里使用的字节数, 通过这个值可以算出具体使用了索引中的哪些列。
ref列
这一列显示了key列索引中使用的列或常量, 常见的有const, 字段名
rows列
这一列显示的是mysql预估要读取的行数, 并不是准确值
Extra列
这一列展示的是额外信息, 常见重要值如下
Using index: 通过覆盖索引直接获取数据
Using where: 使用where进行查询, 但是查询列未被索引覆盖
Using index condition: 查询的列不完全被索引覆盖, where条件中是一个前导列范围
Using temporary: mysql需要使用临时表来处理查询, 这种情况通常需要优化
Using filesort: 使用外部排序而不是索引排序, 数据较小时在内存排序, 较大时在磁盘排序
Select tables optimized away: 使用了某些聚合函数
三．索引创建,删除，和重建
1.分析表和索引(analyze不会重建索引)
analyze table tablename compute statistics 等同于
analyze table tablename compute statistics for table for all indexs for all columns
for talbe的统计信息存在于试图 user_tables,all_tables,dba_tables;
for all indexs的统计信息存在于试图 user_indexs,all_indexs,dba_indexs;
for all columns的统计信息存在于试图 user_tab_columns,all_tab_columns,dba_tab_columns;
也可以手工分析索引(analyze index idx_t_compute statistics);
2.创建，删除索引：
create index indexname on tablename;
drop index indexname on tablename;
3.索引重建；
---ORACLE
alter index indexname rebuild; 或alter index indexname rebuild online;
---MYSQL
ALTER INDEX INDEX_NAME REBUILD online;
REPAIR TABLE tbl_name QUICK;
repair table XXX USE_FRM;
优先使用online,实在不行也要在空闲的时候用rebuild，非必要不用drop index/create index;
alter index rebuild online实质上是扫描表而不是扫描现有的索引块来实现索引的重建;
alter index rebuild 只扫描现有的索引块来实现索引的重建。
online时能够在该索引的基表上执行DML，在在对基表操做的同时能够REBUILD INDEX，可是不能执行DDL语句，因此他们的锁机制是不样的。 建立索引时一般会对该表设置一个表级共享(DML)锁,若是设置ONLINE , 若是是非ONLINE方式，一般会对该表设置一个表级共享(DML)锁，那么就对DML语句冲突;若是设置ONLINE ,(会使用临时日志IOT表来记录中间改变的数据)，但要使用两倍于传统方法的空间.表会变成行级共享锁,在建立索引或者ALTER完成后,对临时日志表 与基表进行MERGE ;
注意并行处理，DDL，位图索引不能使用ONLINE。
