数据库学习（二）---索引知识点

此篇文章只是个人学习总结

• 索引原理、用处：提高查询效率
• 常见索引结构：哈希索引和B+tree索引
• oracle中创建索引：
  Create Index Index-Name On Table_Name (Column_Name);

        举例：
        Create Index USERNAMEA On USER (USERNAME);

• 创建索引的另两种方法：
  方法一：创建表时

 CREATE TABLE 表名 (
                  字段名1  数据类型 [完整性约束条件…],
                  字段名2  数据类型 [完整性约束条件…],
                  [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY 
                  [索引名]  (字段名[(长度)] [ASC |DESC]) 

      方法一示例：

create table t1(   
               id int,
               name char,
               age int,    
               sex enum('male','female'),
               unique key uni_id(id),
               index ix_name(name) #index没有key    
                );

       方法二：ALTER TABLE在已存在的表上创建索引:

ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (字段名[(长度)]  [ASC |DESC]) ;

       方法二示例：

 alter table t1 add index ix_sex(sex);

• 删除索引：
  drop index index_name;

• 查找数据库中所有的索引：
  select * from ALL_INDEXES WHERE TABLE_NAME = ‘xxx’;

• MySQL 数据库索引类型：normal，unique，full text
  normal：表示普通索引
  unique：表示唯一的，不允许重复的索引，身份证号等做索引
  full text：表示全文搜索的索引，搜索一篇全文文章效果最好
  （可以通过explain命令查看，且此命令只适用于MySQL 数据库）

• like %keyword ：索引失效

• like keyword%：索引有效

• like %keyword %：索引失效

• 索引失效的几种情况：
  1、查询的数量是大表的大部分，应该是30%以上；
  2、没有查询条件，或者查询条件没有建立索引；
  3、当变量采用的是times变量，而表的字段采用的是date变量时，或相反情况；
  4、在查询条件中没有使用引导列；
  5、隐式转换导致索引失效，例：在查询时把该字段作为number类型，以where条件转给oracle，这样会导致索引失效；
  错误示例：select * from test where tu_mdn = 13333;
  正确示例：select * from test where tumdn = ‘13333’;
  （字符型字段为数字时在where条件里不添加引号）
  6、对索引列进行运算导致索引失效（+，-，*，/，！等）
  错误示例：select * from test where id -1 = 9;
  正确示例：select * from test where id = 10;
  7、使用oracle内部函数导致索引失效，对于这样情况应当创建基于函数的索引；
  错误示例：select * from test where round(id) = 10;
  正确示例：create index test_id_idx on test(round(id)); select * from test where round(id) = 10;
  8、表没分析；
  9、单独引用复合索引里非第一位置的索引列；
  10、not in ，not exist。

• 索引的其中一个缺点就是需要占用磁盘空间

  如何减少占用的空间？
  （1）减少字段个数
  （2）占用字节数少的字段

• 对于索引有一些错误的观点：

  （1）主键就是索引
  （2）只要建立索引就能显著提高查询速度
  （3）把所有需要提高查询速度的字段都加进去聚集索引，以提高查询速度

• 一般来说一下规则是正确的：
  （1）用聚集索引比引用非聚集索引的主键速度快
  （2）用聚集索引比用一般的主键做order by时速度快，特别是在小数据量情况下
  （3）使用聚集索引内的时间段，搜索时间会按数据占整个数据表的百分比成比例减少，而无论聚集索引使用了多少个
  （4）不要过度使用索引

• 聚集索引检索效率比普通索引高，索引占用硬盘存储空间小（1%左右），但对数据的新增/修改/删除的速度影响比较大。

• 非聚集索引检索效率比聚集索引低，索引占用硬盘存储空间大（30%-40%），对数据新增/修改/删除的影响很小。

• 聚集索引：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。
  单单从定义来看是不是显得有点抽象，打个比方，一个表就像是我们以前用的新华字典，聚集索引就像是拼音目录，而每个字存放的页码就是我们的数据物理地址，我们如果要查询一个“哇”字，我们只需要查询“哇”字对应在新华字典拼音目录对应的页码，就可以查询到对应的“哇”字所在的位置，而拼音目录对应的A-Z的字顺序，和新华字典实际存储的字的顺序A-Z也是一样的，如果我们中文新出了一个字，拼音开头第一个是B，那么他插入的时候也要按照拼音目录顺序插入到A字的后面，现在用一个简单的示意图来大概说明一下在数据库中的样子：
  
  注：第一列的地址表示该行数据在磁盘中的物理地址，后面三列才是我们SQL里面用的表里的列，其中id是主键，建立了聚集索引。
  结合上面的表格就可以理解这句话了吧：数据行的物理顺序与列值的顺序相同，如果我们查询id比较靠后的数据，那么这行数据的地址在磁盘中的物理地址也会比较靠后。而且由于物理排列方式与聚集索引的顺序相同，所以也就只能建立一个聚集索引了。

• 非聚集索引：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

• 有些列不应该建立索引：
  1、对于那些在查询中很少使用或者参考的列不应该创建索引；
  2、对于那些只有很少数据值的列也不应该增加索引；
  3、对于那些定义为test，image和bit数据类型的列不应该增加索引；
  4、当修改性能远远大于检索性能时，不应该创建索引；这是因为修改性能和检索性能是互相矛盾的，当增加索引时，会提高检索性能，但是会降低修改性能，当减少索引时，会提高修改性能，降低检索性能，因此，当修改性能远远大于检索性能时，不应该创建索引。