索引相关问题

适当的使用索引可以提高数据检索速度，可以给经常需要进行查询的字段创建索引 

一、索引的概念
        索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中，索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。

二、索引的特点
    1.索引可以加快数据库的检索速度
    2.索引降低了数据库插入、修改、删除等维护任务的速度
    3.索引创建在表上，不能创建在视图上
    4.索引既可以直接创建，也可以间接创建
    5.可以在优化隐藏中，使用索引
    6.使用查询处理器执行SQL语句，在一个表上，一次只能使用一个索引
    7.其他

三、索引的优点
    1.创建唯一性索引，保证数据库表中每一行数据的唯一性
    2.大大加快数据的检索速度，这也是创建索引的最主要的原因
    3.加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
    4.在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
    5.通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能。

四、索引的缺点
    1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
    2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大
    3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度

五、索引分类
    1.直接创建索引和间接创建索引
    直接创建索引： CREATE INDEX mycolumn_index ON mytable (myclumn)
    间接创建索引：定义主键约束或者唯一性键约束，可以间接创建索引
    2.普通索引和唯一性索引
    普通索引：CREATE INDEX mycolumn_index ON mytable (myclumn)
    唯一性索引：保证在索引列中的全部数据是唯一的，对聚簇索引和非聚簇索引都可以使用
    CREATE UNIQUE COUSTERED INDEX myclumn_cindex ON mytable(mycolumn)
    3.单个索引和复合索引
    单个索引：即非复合索引
    复合索引：又叫组合索引，在索引建立语句中同时包含多个字段名，最多16个字段
    CREATE INDEX name_index ON username(firstname,lastname)
    4.聚簇索引和非聚簇索引(聚集索引，群集索引)
   聚簇索引：物理索引，与基表的物理顺序相同，数据值的顺序总是按照顺序排列
    CREATE CLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn) WITH
    ALLOW_DUP_ROW(允许有重复记录的聚簇索引)
   非聚簇索引：CREATE UNCLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn)

 表的主关键字

自动建立唯一索引

如zl_yhjbqk（用户基本情况）中的hbs_bh（户标识编号）

表的字段唯一约束

ORACLE利用索引来保证数据的完整性

如lc_hj（流程环节）中的lc_bh+hj_sx（流程编号+环节顺序）

直接条件查询的字段

在SQL中用于条件约束的字段

如zl_yhjbqk（用户基本情况）中的qc_bh（区册编号）

select * from zl_yhjbqk where qc_bh=’7001’

查询中与其它表关联的字段

字段常常建立了外键关系

如zl_ydcf（用电成份）中的jldb_bh（计量点表编号）

select * from zl_ydcf a,zl_yhdb b where a.jldb_bh=b.jldb_bh and b.jldb_bh=’540100214511’

查询中排序的字段

排序的字段如果通过索引去访问那将大大提高排序速度

select * from zl_yhjbqk order by qc_bh（建立qc_bh索引）

select * from zl_yhjbqk where qc_bh=’7001’ order by cb_sx（建立qc_bh+cb_sx索引，注：只是一个索引即复合索引，其中包括qc_bh和cb_sx字段）

查询中统计或分组统计的字段

select max(hbs_bh) from zl_yhjbqk

select qc_bh,count(*) from zl_yhjbqk group by qc_bh

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

表记录太少

如果一个表只有5条记录，采用索引去访问记录的话，那首先需访问索引表，再通过索引表访问数据表，一般索引表与数据表不在同一个数据块，这种情况下ORACLE至少要往返读取数据块两次。而不用索引的情况下ORACLE会将所有的数据一次读出，处理速度显然会比用索引快。

如表zl_sybm（使用部门）一般只有几条记录，除了主关键字外对任何一个字段建索引都不会产生性能优化，实际上如果对这个表进行了统计分析后ORACLE也不会用你建的索引，而是自动执行全表访问。如：

select * from zl_sybm where sydw_bh=’5401’（对sydw_bh建立索引不会产生性能优化）

经常插入、删除、修改的表

对一些经常处理的业务表应在查询允许的情况下尽量减少索引，如zl_yhbm，gc_dfss，gc_dfys，gc_fpdy等业务表。

数据重复且分布平均的表字段

假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。

经常和主字段一块查询但主字段索引值比较多的表字段

如gc_dfss（电费实收）表经常按收费序号、户标识编号、抄表日期、电费发生年月、操作 标志来具体查询某一笔收款的情况，如果将所有的字段都建在一个索引里那将会增加数据的修改、插入、删除时间，从实际上分析一笔收款如果按收费序号索引就已 经将记录减少到只有几条，如果再按后面的几个字段索引查询将对性能不产生太大的影响。

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

一、注意事项：

首先，应当考虑表空间和磁盘空间是否足够。我们知道索引也是一种数据，在建立索引的时候势必也会占用大量表空间。因此在对一大表建立索引的时候首先应当考虑的是空间容量问题。

其次，在对建立索引的时候要对表进行加锁，因此应当注意操作在业务空闲的时候进行。

二、性能调整方面：

首当其冲的考虑因素便是磁盘I/O。物理上，应当尽量把索引与数据分散到不同的磁盘上（不考虑阵列的情况）。逻辑上，数据表空间与索引表空间分开。这是在建索引时应当遵守的基本准则。

其次，我们知道，在建立索引的时候要对表进行全表的扫描工作，因此，应当考虑调大初始化参数db_file_multiblock_read_count的值。一般设置为32或更大。

再次，建立索引除了要进行全表扫描外同时还要对数据进行大量的排序操作，因此，应当调整排序区的大小。

    9i之前，可以在session级别上加大sort_area_size的大小，比如设置为100m或者更大。

    9i以后，如果初始化参数workarea_size_policy的值为TRUE，则排序区从pga_aggregate_target里自动分配获得。

最后，建立索引的时候，可以加上nologging选项。以减少在建立索引过程中产生的大量redo，从而提高执行的速度。

MySql在建立索引优化时需要注意的问题

设计好MySql的索引可以让你的数据库飞起来，大大的提高数据库效率。设计MySql索引的时候有一下几点注意：

1，创建索引

对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。如果不加

索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下

降。但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。

2，复合索引

比如有一条语句是这样的：select * from users where area=’beijing’ and age=22;

如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效

率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age,

salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀

特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。

3，索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

4，使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

5，排序的索引问题

mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

6，like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

7，不要在列上进行运算

select * from users where

YEAR(adddate)

8，不使用NOT IN和操作

NOT IN和操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id3则可使用id>3 or id

单索引与复合索引举例：
表table1 字段：col1,col2
试验如下：
1.建立索引idx1 on col1
执行语句1：select * from table1 where col1='A'  使用idx1
执行语句2：select * from table1 where col1='A' and col2='B' 也使用idx1
2.删除索引idx1,然后建立idx2 on (col1,col2)复合索引，根据最佳左前缀实际为 (col1,col2)索引和（col1）
执行以上两个查询，也都使用idx2
3.如果两个索引idx1,idx2都存在
并不是 where col1='A'用idx1；where col1='A' and col2='B' 用idx2。
都可以用idx2，但语句2可以索引1与索引2，但使用复合索引效率才更高。

根据估计的查询条件决定复合索引包含的字段。
即使查询条件变化较大，（如有时用date限制，有时用sex限制...)命中常用的字段来建立复合索引，才是最经济合理的。