MySQL索引

1. 什么地方适合建索引,什么地方不适合建索引

适合:
  1. 表的主键、外键必须有索引;
  2. 数据量超过300的表应该有索引;
  3. 经常与其他表进行连接的表,在连接字段上应该建立索引;
  4. 经常出现在Where子句中的字段,特别是大表的字段,应该建立索引;
  5. 索引应该建在选择性高的字段上;
  6. 索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引;
  7. 复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替:
    where, order by, group by
不适合:
  1. 表记录太少
  2. 经常插入/删除/修改的表
  3. 数据重复分布平均的表数据
  4. 经常和主字段一块查询但主字段索引比较多的字段

2. 索引查询需要注意的地方

最左前缀
建短索引
字段为null的不能建索引
like应该匹配字符串后半段,like “xxx%”
不要在列上进行运算
不使用not in

3. 索引优缺点

索引优点

创建索引可以大大提高系统的性能。

  1. 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
  2. 可以大大加快数据的检索速度,这也是创建索引的最主要的原因。
  3. 可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。
  4. 在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。
  5. 通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。
索引缺点
  1. 创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。
  2. 索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。
  3. 当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。

4. 聚簇索引和非聚簇索引

聚簇索引

表数据按照索引的顺序来存储的,也就是说索引项的顺序与表中记录的物理顺序一致。对于聚集索引,叶子结点即存储了真实的数据行,不再有另外单独的数据页。
a) 一个索引项直接对应实际数据记录的存储页,可谓“直达”
b) 主键缺省使用它
c) 索引项的排序和数据行的存储排序完全一致,利用这一点,想修改数据的存储顺序,可以通过改变主键的方法(撤销原有主键,另找也能满足主键要求的一个字段或一组字段,重建主键)
d) 一个表只能有一个聚簇索引,真实数据的物理顺序只能有一种

非聚簇索引

表数据存储顺序与索引顺序无关。对于非聚集索引,叶结点包含索引字段值及指向数据页数据行的逻辑指针,其行数量与数据表行数据量一致。
a) 不能“直达”,可能链式地访问多级页表后,才能定位到数据页
b) 一个表可以有多个非聚簇索引

5. 索引数据结构

B树索引

B树中每个节点包含了键值和键值对应的数据对象存放地址指针,所以成功搜索一个对象可以不用到达树的叶节点。
成功搜索包括节点内搜索和沿某一路径的搜索,成功搜索时间取决于关键码所在的层次以及节点内关键码的数量。
在B树中查找给定关键字的方法是:首先把根结点取来,在根结点所包含的关键字K1,…,kj查找给定的关键字(可用顺序查找或二分查找法),若找到等于给定值的关键字,则查找成功;否则,一定可以确定要查的关键字在某个Ki或Ki+1之间,于是取Pi所指的下一层索引节点块继续查找,直到找到,或指针Pi为空时查找失败。

B+树索引

B+树非叶节点中存放的关键码并不指示数据对象的地址指针,非叶节点只是索引部分。所有的叶节点在同一层上,包含了全部关键码和相应数据对象的存放地址指针,且叶节点按关键码从小到大顺序链接。如果实际数据对象按加入的顺序存储而不是按关键码次数存储的话,叶节点的索引必须是稠密索引,若实际数据存储按关键码次序存放的话,叶节点索引时稀疏索引。
B+树有2个头指针,一个是树的根节点,一个是最小关键码的叶节点。
所以 B+树有两种搜索方法
一种是按叶节点自己拉起的链表顺序搜索。
一种是从根节点开始搜索,和B树类似,不过如果非叶节点的关键码等于给定值,搜索并不停止,而是继续沿右指针,一直查到叶节点上的关键码。所以无论搜索是否成功,都将走完树的所有层。
B+ 树中,数据对象的插入和删除仅在叶节点上进行。

不同之处
  1. B树中同一键值不会出现多次,并且它有可能出现在叶结点,也有可能出现在非叶结点中。而B+树的键一定会出现在叶结点中,并且有可能在非叶结点中也有可能重复出现,以维持B+树的平衡。
  2. 因为B树键位置不定,且在整个树结构中只出现一次,虽然可以节省存储空间,但使得在插入、删除操作复杂度明显增加。B+树相比来说是一种较好的折中。
  3. B树的查询效率与键在树中的位置有关,最大时间复杂度与B+树相同(在叶结点的时候),最小时间复杂度为1(在根结点的时候)。而B+树的时间复杂度对某建成的树是固定的。

可参考博客:
http://zhuyuehua.iteye.com/blog/1872202

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值