sql 数据库索引的使用规则

使用索引前需要用explain查看下执行计划，然后根据基数是否需要创建索引。

巧用索引
a.如果表中大部分status是1,2 但我们需要查询查询少量的3时，也可以为status添加索引
b.为sql语句添加额外的条件，如查询员工时可以添加城市等字段过滤
c.如果表数据按城市进行查询慢时，可以换个思路如通过城市找到staffId然后再通过staffId查询，这样可以增加区分度加快查询效率
优化前：

explain
select * from a where
reserve_date BETWEEN '2019-07-01' and '2019-07-31'
and city_code = 'shanghai' ;

优化后：

select * from table where 
reserve_date BETWEEN '2019-07-01' and '2019-07-31'
and staff_id in (select id from staff where city_code='shanghai' and virtual=0 and working_state in(3,4));

d.统计个数时可以把查询语句放到select中，这样可以避免生成派生表
优化前：

explain
select 
   a.id,c.skillNum
from staff a,staff_skill b,(
		SELECT
			ssc.staff_id,
			count(1) AS skillNum
		FROM
			staff_skill ssc
		GROUP BY
			ssc.staff_id
	) c where a.id = b.staff_id and b.skill_id=24 and a.id=c.staff_id
order by c.skillNum desc;

优化后：

explain
select 
   a.id,(
		SELECT
			count(1)
		FROM
			staff_skill ssc
		WHERE
			ssc.staff_id = a.id 
      
		GROUP BY
			ssc.staff_id
	)skillNum
from staff a,staff_skill b
 where a.id = b.staff_id and b.skill_id=24 
order by skillNum desc;

1.应该建索引的字段：
         a.经常作为查询条件的字段
         b.外键
         c.经常需要排序的字段
         d.分组排序的字段。 
        e.有些需要联合使用的需要考虑使用联合索引查询，比如查询男的员工可以使用加上其他的查询条件比如城市等等

2.应该少建或者不建索引的字段有：
          a.表记录太少
          b.经常需要插入，删除，修改的表
          c.表中数据重复且分布平均的字段

3.一些SQL的写法会限制索引的使用：
         a.where子句中如果使用in、or、like、!= <>，均会导致索引不能正常使用，将"<>"换成">and<";将"is not null "换成">=chr(0)";
         b.使用函数时，该列就不能使用索引，+号属于函数所以会停用索引。
         c.比较不匹配数据类型时，该索引将会被忽略。

停用索引例子：

不使用索引: SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT !=0; 　　
使用索引: SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT >0; 　

不使用索引: SELECT ACCOUNT_NAME,AMOUNT FROM TRANSACTION
            WHERE ACCOUNT_NAME||ACCOUNT_TYPE='AMEXA'; 　
使用索引: SELECT ACCOUNT_NAME,AMOUNT FROM TRANSACTION
          WHERE ACCOUNT_NAME = ‘AMEX' AND ACCOUNT_TYPE='A'; 　　
不使用索引: SELECT ACCOUNT_NAME, AMOUNT FROM TRANSACTION
            WHERE AMOUNT + 3000 >5000; 　　
使用索引: SELECT ACCOUNT_NAME, AMOUNT FROM TRANSACTION
          WHERE AMOUNT > 2000 ;
相同的索引列不能互相比较,这将会启用全表扫描

不使用索引: SELECT ACCOUNT_NAME, AMOUNT FROM TRANSACTION
            WHERE ACCOUNT_NAME = NVL(:ACC_NAME,ACCOUNT_NAME); 　　
使用索引: SELECT ACCOUNT_NAME, AMOUNT FROM TRANSACTION
          WHERE ACCOUNT_NAME LIKE NVL(:ACC_NAME,'%'); 　　

4.一些SQL语句优化的写法：
      1.如果from是双表的查询时，大表放在前面，小表放在后面(基础表)。最后面的表是基础表。(只在基于规则的优化器中有效)
      2.如果三表查询时，选择交叉表(intersection table)作为基础表.(只在基于规则的优化器中有效)
      3.写where条件时，有索引字段的判断在前，其它字段的判断在后；如果where条件中用到复合索引，按照索引列在复合索引中出现的顺序来依次写where条件；
      4.查询数量较大时，使用表连接代替IN，EXISTS，NOT IN，NOT EXISTS等。
      5.ORACLE采用自下而上的顺序解析WHERE子句,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。

5.某些SELECT 语句中的WHERE子句不使用索引. 这里有一些例子.
在下面的例子里, ‘!=' 将不使用索引. 记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中.
不使用索引:

SELECT ACCOUNT_NAME
FROM TRANSACTION
WHERE AMOUNT !=0;

使用索引:

SELECT ACCOUNT_NAME
FROM TRANSACTION
WHERE AMOUNT >0;

下面的例子中, ‘||'是字符连接函数. 就象其他函数那样, 停用了索引.

不使用索引:
SELECT ACCOUNT_NAME,AMOUNT
FROM TRANSACTION
WHERE ACCOUNT_NAME||ACCOUNT_TYPE='AMEXA';

使用索引:
SELECT ACCOUNT_NAME,AMOUNT
FROM TRANSACTION
WHERE ACCOUNT_NAME = ‘AMEX'
AND ACCOUNT_TYPE='A';

下面的例子中, ‘+'是数学函数. 就象其他数学函数那样, 停用了索引.

不使用索引:
SELECT ACCOUNT_NAME, AMOUNT
FROM TRANSACTION
WHERE AMOUNT + 3000 >5000;

使用索引:
SELECT ACCOUNT_NAME, AMOUNT
FROM TRANSACTION
WHERE AMOUNT > 2000 ;

下面的例子中,相同的索引列不能互相比较,这将会启用全表扫描.

不使用索引:
SELECT ACCOUNT_NAME, AMOUNT
FROM TRANSACTION
WHERE ACCOUNT_NAME = NVL(:ACC_NAME,ACCOUNT_NAME);

使用索引:
SELECT ACCOUNT_NAME, AMOUNT
FROM TRANSACTION
WHERE ACCOUNT_NAME LIKE NVL(:ACC_NAME,'%');