海量数据处理专题(七)——数据库索引及优化

索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。

数据库索引

什么是索引

数据库索引好比是一本书前面的目录,能加快数据库的查询速度。

例如这样一个查询:select * from table1 where id=44。如果没有索引,必须遍历整个表,直到ID等于44的这一行被找到为止;有了索引之后(必须是在ID这一列上建立的索引),直接在索引里面找44(也就是在ID这一列找),就可以得知这一行的位置,也就是找到了这一行。可见,索引是用来定位的。

索引分为聚簇索引和非聚簇索引两种,聚簇索引 是按照数据存放的物理位置为顺序的,而非聚簇索引就不一样了;聚簇索引能提高多行检索的速度,而非聚簇索引对于单行的检索很快。

概述

建立索引的目的是加快对表中记录的查找或排序。

为表设置索引要付出代价的:一是增加了数据库的存储空间,二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。

B树索引-Sql Server索引方式

B树索引-Sql Server索引方式

为什么要创建索引

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

第一,通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
第二,可以大大加快数据的检索速度,这也是创建索引的最主要的原因。
第三,可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。
第四,在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。
第五,通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。

也许会有人要问:增加索引有如此多的优点,为什么不对表中的每一个列创建一个索引呢?因为,增加索引也有许多不利的方面。

第一,创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。
第二,索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。
第三,当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。

在哪建索引

索引是建立在数据库表中的某些列的上面。在创建索引的时候,应该考虑在哪些列上可以创建索引,在哪些列上不能创建索引。一般来说,应该在这些列上创建索引:

在经常需要搜索的列上,可以加快搜索的速度;
在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构;
在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度;在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的;
在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间;
在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。

同样,对于有些列不应该创建索引。一般来说,不应该创建索引的的这些列具有下列特点:

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

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

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

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

数据库优化

此外,除了数据库索引之外,在LAMP结果如此流行的今天,数据库(尤其是MySQL)性能优化也是海量数据处理的一个热点。下面就结合自己的经验,聊一聊MySQL数据库优化的几个方面。

首先,在数据库设计的时候,要能够充分的利用索引带来的性能提升,至于如何建立索引,建立什么样的索引,在哪些字段上建立索引,上面已经讲的很清楚了,这里不在赘述。另外就是设计数据库的原则就是尽可能少的进行数据库写操作(插入,更新,删除等),查询越简单越好。如下:

数据库设计

数据库设计

其次,配置缓存是必不可少的,配置缓存可以有效的降低数据库查询读取次数,从而缓解数据库服务器压力,达到优化的目的,一定程度上来讲,这算是一个“围魏救赵”的办法。可配置的缓存包括索引缓存(key_buffer),排序缓存(sort_buffer),查询缓存(query_buffer),表描述符缓存(table_cache),如下图:

配置缓存

配置缓存

第三,切表,切表也是一种比较流行的数据库优化方法。分表包括两种方式:横向分表和纵向分表,其中,横向分表比较有使用意义,故名思议,横向切表就是指把记录分到不同的表中,而每条记录仍旧是完整的(纵向切表后每条记录是不完整的),例如原始表中有100条记录,我要切成2个表,那么最简单也是最常用的方法就是ID取摸切表法,本例中,就把ID为1,3,5,7。。。的记录存在一个表中,ID为2,4,6,8,。。。的记录存在另一张表中。虽然横向切表可以减少查询强度,但是它也破坏了原始表的完整性,如果该表的统计操作比较多,那么就不适合横向切表。横向切表有个非常典型的用法,就是用户数据:每个用户的用户数据一般都比较庞大,但是每个用户数据之间的关系不大,因此这里很适合横向切表。最后,要记住一句话就是:分表会造成查询的负担,因此在数据库设计之初,要想好是否真的适合切表的优化:

分表

分表

第四,日志分析,在数据库运行了较长一段时间以后,会积累大量的LOG日志,其实这里面的蕴涵的有用的信息量还是很大的。通过分析日志,可以找到系统性能的瓶颈,从而进一步寻找优化方案。

性能分析

性能分析

以上讲的都是单机MySQL的性能优化的一些经验,但是随着信息大爆炸,单机的数据库服务器已经不能满足我们的需求,于是,多多节点,分布式数据库网络出现了,其一般的结构如下:

分布式数据库结构

分布式数据库结构

这种分布式集群的技术关键就是“同步复制”。。。《未完待续。。。》

做人要厚道,转载请注明出处:http://diducoder.com/mass-data-topic-7-index-and-optimize.html

 

海量数据处理专题(七)——数据库索引及优化》上有10条评论

  1. 有一段时间没来看你博客了,今天碰巧有时间.下午好.....

  2. 您好,这句话:
    分表包括两种方式:横向分表和纵向分表,其中,纵向分表比较有使用意义,但是分表会造成查询的负担,因此在数据库设计之初,要想好。其中”纵向分表比较有使用意义“和图中:
    纵向 字段较多可以考虑,一般用处不大 这两句是不是有点矛盾呀??

  3. 睡了吗?能不能和我联系下。我的邮箱ceo@moical.com。找你有事。或者加QQ:226547633。谢谢了,等你消息。

  4. 博主,你既然这么懂博客,微博相比也玩吧。非常先关注你,从你这样比较好的博客里,可以看到很多适合经营微博的方法。

  5. hi,期待博主能对索引常使用的数据结构,如B-Tree/BitMap等做个深入一点的分析和对比

  6. 第二,对于那些只有很少数据值的列也不应该增加索引
    很少数值(低基数)的列上也可以建索引,根据不同的场景,可以建立位图索引、位图联接索引或者函数索引。位图索引要建立在极少修改的列上,大规模修改会导致死锁。用多个位图索引联合查询数据,或者做聚合查询时候用位图 索引比较合适,如查询性别为男的总人数。

  7. Pingback引用通告: 海量数据处理专题(一)——开篇 | 帝都码农

发表评论

电子邮件地址不会被公开。 必填项已用*标注