此篇文章只是个人学习总结
- 索引原理、用处:提高查询效率
- 常见索引结构:哈希索引和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、当修改性能远远大于检索性能时,不应该创建索引;这是因为修改性能和检索性能是互相矛盾的,当增加索引时,会提高检索性能,但是会降低修改性能,当减少索引时,会提高修改性能,降低检索性能,因此,当修改性能远远大于检索性能时,不应该创建索引。