贵州巨友科技有限责任公司 - 贵州|贵阳|APP开发|微信小程序|WEB系统开发|OA系统|网站开发|微信公众号开发|VR应用开发

贵州巨友科技有限责任公司

新闻中心
网站首页 > 资讯 > 技术资料

浅谈索引的优缺点和建立索引的原则

2017-09-06 10:05:22 贵州巨友科技有限责任公司 阅读
  • 索引的优点

  • 索引的缺点

  • 建索引的几大原则

索引的优点

  • 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。

  • 可以大大加快数据的检索速度,这也是创建索引的最主要的原因。

  • 可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。

  • 在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。

  • 通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能

索引的缺点

  • 创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。

  • 索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。

  • 当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。

建索引的几大原则

  • 1.最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。

  • 2.=和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式

  • 3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录

  • 4.索引列不能参与计算,保持列“干净”,比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

  • 5.尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可


    1. Where 子句中经常使用的字段应该创建索引,分组字段或者排序字段应该创建索引,两个表的连接字段应该创建索引。

  • 7.like 模糊查询中,右模糊查询(321%)会使用索引,而%321 和%321%会放弃索引而使用全局扫描

应该建立索引的情况

  • 在经常需要搜索的列上,可以加快搜索的速度;

  • 在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构;

  • 在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度;

  • 在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的;

  • 在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间;

  • 在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。

不应该建立索引的情况

  • 对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。

  • 对于那些只有很少数据值的列也不应该增加索引。这是因为,由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。

  • 对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少。

  • 当修改性能远远大于检索性能时,不应该创建索引。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因此,当修改性能远远大于检索性能时,不应该创建索引。



作者:六尺帐篷
链接:http://www.jianshu.com/p/107e5bdf4148
來源:简书
著作权归作者所有。
Powered by MetInfo 6.0.0 ©2008-2018 www.metinfo.cn