常用SQL之操作数据库索引

Oracle操作索引

创建索引

• 唯一索引

  Oracle自动在表中的主键列创建唯一索引

      Create unique index index_name on table_name(column_name)
  

• 组合索引

  组合索引是在表的多个列上创建索引，索引中列顺序是任意的，
  如果SQL语句的Where子句中引用了组合索引的所有列或大多数列，则可以提高检索效率

      Create index index_name on table_name (column_name1,column_name2);
  

• 反向键索引

  反向键索引反转索引列键值的每个字节，为了实现索引的均匀分配，避免b树不平衡
  通常建立在值是连续增长的列上，使数据均匀地分布在整个索引上
  创建索引时使用REVERSE关键字

      Create index index_name on table_name(column_name) reverse;
  

• 位图索引

  位图索引适合创建在低基数列上
  位图索引不直接存储在ROWID，而是存储字节位到ROWID的映射
  节省空间占用
  如果索引列经常更新的话，不适合建立位图索引
  总体来说，位图索引适合于数据仓库中，不适合OLTP中

      create bitmap index index_name on table_name(column_name);
  
  

• 基于函数的索引

  基于一个或多个列上的函数或表达式创建的索引
  表达式中不能出现聚合函数
  不能在LOB类型的列上创建索引
  创建时必须具有QUERY REWRITE权限

      Create index index_name on table_name(函数(column_name));
      select * from student where upper(sname) = 'TOM';
  

查询索引

• 查询表的索引

  
      select * from all_indexes where table_name = '表名称';
  
  
  

• 查询表的索引列

  
      select * from all_ind_columns where table_name = '表名称';
  
  

删除索引

• 删除指定名称索引

  
      drop index 索引名称;
  
  

MySQL操作索引

建立索引

• 1.添加PRIMARY KEY（主键索引）

  ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 
  

• 2.添加UNIQUE(唯一索引)

  ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 
  

• 3.添加INDEX(普通索引)

  ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
  

• 4.添加FULLTEXT(全文索引)

  ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 
  

• 5.添加多列索引

  ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
  

修改索引

• 1.删除UNIQUE KEY（唯一索引）

  -- 查询表已有索引  
  SHOW INDEX from table_name;
  -- 删除指定名称索引
  ALTER TABLE `table_name` DROP INDEX `index_name`;
  -- 再重新创建索引
  ALTER TABLE `table_name` ADD UNIQUE  index_name ( `column` ) ;
  

索引注意事项

• 最左前缀原则

  MySQL中的索引可以以一定顺序引用多列，称作联合索引。如User表的name和city加联合索引就是(name,city)，而最左前缀原则指的是，如果查询的时候查询条件精确盘匹配索引的左边连续一列或几列，则此列就可以被用到。

      select * from user where name = xx and city = xx;//可以命中索引
      select * from user where name = xx;//可以命中索引
      select * from user where city = xx;//无法命中索引
  

  注意：查询的时候如果两个条件都用上了，但是顺序不同，如 city = xx and name = xx，那么现在自动优化为匹配联合索引的顺序，这样能够命中索引。

  由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDERBY 子句也遵循此规则。

• 避免冗余索引

  冗余索引指的是索引的功能相同，能够命中，就肯定能命中，那么冗余索引如（name,city）和 （name）这两个索引就是冗余索引，能够命中后者的查询肯定是能够命中前者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。