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

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

索引原理、用处：提高查询效率
常见索引结构：哈希索引和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）减少字段个数
（2）占用字节数少的字段
对于索引有有一些错误的观点：

（1）主键就是索引
（2）只要建立索引就能显著提高查询速度
（3）把所有需要提高查询速度的字段都加进去聚集索引，以提高查询速度
一般来说一下规则是正确的：
（1）用聚集索引比引用非聚集索引的主键速度快
（2）用聚集索引比用一般的主键做order by时速度快，特别是在小数据量情况下
（3）使用聚集索引内的时间段，搜索时间会按数据占整个数据表的百分比成比例减少，而无论聚集索引使用了多少个
（4）不要过度使用索引
聚集索引检索效率比普通索引高，索引占用硬盘存储空间小（1%左右），但对数据的新增/修改/删除的速度影响比较大。
非聚集索引检索效率比聚集索引低，索引占用硬盘存储空间大（30%-40%），对数据新增/修改/删除的影响很小。
聚集索引：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。
单单从定义来看是不是显得有点抽象，打个比方，一个表就像是我们以前用的新华字典，聚集索引就像是拼音目录，而每个字存放的页码就是我们的数据物理地址，我们如果要查询一个“哇”字，我们只需要查询“哇”字对应在新华字典拼音目录对应的页码，就可以查询到对应的“哇”字所在的位置，而拼音目录对应的A-Z的字顺序，和新华字典实际存储的字的顺序A-Z也是一样的，如果我们中文新出了一个字，拼音开头第一个是B，那么他插入的时候也要按照拼音目录顺序插入到A字的后面，现在用一个简单的示意图来大概说明一下在数据库中的样子：

注：第一列的地址表示该行数据在磁盘中的物理地址，后面三列才是我们SQL里面用的表里的列，其中id是主键，建立了聚集索引。
结合上面的表格就可以理解这句话了吧：数据行的物理顺序与列值的顺序相同，如果我们查询id比较靠后的数据，那么这行数据的地址在磁盘中的物理地址也会比较靠后。而且由于物理排列方式与聚集索引的顺序相同，所以也就只能建立一个聚集索引了。
非聚集索引：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

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

相关推荐