数据库—索引

适当的使用索引可以提高数据检索速度，可以给经常需要进行查询的字段创建索引 oracle的索引分为5种:唯一索引，组合索引，反向键索引，位图索引，基于函数的索引

索引的创建

Oracle和MySQL创建索引的过程基本相同，语法：

CREATE INDEXindex_name ON table_name (column_list)

哪些语句不会走索引

• SELECT `sname` FROM `stu` WHERE `age`+10=30;--不会使用索引，因为所有索引列参与了计算
• SELECT `sname` FROM `stu` WHERE LEFT(`date`,4)<1990;--不会使用索引，因为使用了函数运算，原理与上面相同
• SELECT * FROM `houdunwang` WHERE `uname` LIKE'后盾%'--走索引
• SELECT * FROM `houdunwang` WHERE `uname` LIKE "%后盾%"--不走索引
• 组合索引遵循“最左前缀”的原则
• 比如：创建一个索引

ALTER TABLE `myIndex` ADD INDEX `name_city_age`(vc_Name(10),vc_City,i_Age);

A:会走索引

SELECT * FROM myIndexWHREE vc_Name=”erquan” AND vc_City=”郑州”

SELECT * FROM myIndexWHREE vc_Name=”erquan”

B:不会走索引

SELECT * FROM myIndexWHREE i_Age=20 AND vc_City=”郑州”

SELECT * FROM myIndexWHREE vc_City=”郑州”

什么时候创建索引

• 表的主关键字：自动建立唯一索引
• 表的字段唯一约束：ORACLE利用索引来保证数据的完整性
• 直接条件查询的字段：在SQL中用于条件约束的字段
• 查询中与其它表关联的字段：字段常常建立了外键关系
• 查询中排序的字段：排序的字段如果通过索引去访问那将大大提高排序速度
• 查询中统计或分组统计的字段

什么情况下应不建或少建索引

• 表记录太少：如果一个表只有5条记录，采用索引去访问记录的话，那首先需访问索引表，再通过索引表访问数据表，一般索引表与数据表不在同一个数据块，这种情况下ORACLE至少要往返读取数据块两次。而不用索引的情况下ORACLE会将所有的数据一次读出，处理速度显然会比用索引快。
• 经常插入、删除、修改的表：对一些经常处理的业务表应在查询允许的情况下尽量减少索引。
• 数据重复且分布平均的表字段：假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。

对千万级MySQL数据库建立索引的事项及提高性能的手段：

l 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

l 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0

l 应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。

l 应尽量避免在 where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num=10 or num=20可以这样查询：select id from t where num=10 union all select id from t wherenum=20

l 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where num/2=100应改为:selectid from t where num=100*2

l 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：selectid from t where substring(name,1,3)='abc' ，name以abc开头的id，应改为: select id from t where name like'abc%'

l 很多时候用 exists 代替 in 是一个好的选择：select num from a where num in(select num from b)用下面的语句替换：select num from a where exists(select 1 from b where num=a.num)

l 任何地方都不要使用 select *from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。