数据库两个基本概念

数据库 (Block)

数据块是数据库中数据在磁盘中存储的最小单位,也是一次IO访问的最小单位。一个数据块通常可以存储多条记录,数据块大小是DBA在创建数据库或表空间时指定。一个数据库可以包括多个数据文件,一个数据文件内又包含多个数据块。

RowId

ROWID是每条记录在数据库中的唯一标识。

优化方案

减少数据访问

索引 (Index)

索引表不是万能的,它的存在会大大增加表记录的DML(INSERT,UPDATE,DELETE)开销,正确的索引可以让性能提升100,1000倍以上,不合理的索引也可能会让性能下降100倍。

索引是啥

B-TREE索引也称为平衡树索引(Balance Tree),它是一种按字段排好序的树形目录结构,主要用于提升查询性能和唯一约束支持

叶子节点内容 索引字段内容+表记录ROWID

根节点,分支节点内容 当一个数据块中不能放下所有索引字段数据时,就会形成树形的根节点或分支节点,根节点与分支节点保存了索引树的顺序及各层级间的引用关系。

如果把表的内容看做一本字典,则索引相当于字典的目录,具体如下图所示:

所以在一个表中可以建多个索引,就如一本字典可以建多个目录一样(按拼音、笔划、部首等等);一个索引也可以由多个字段组成,称为组合索引,如上图就是一个按部首+笔划的组合目录

什么情况下使用索引?

=, >(=), <(=), BETWEEN, IN, LIKE(后导模糊查询)和两个表通过索引字段关联

什么情况下不使用索引?

不等于(<>, NOT IN, !=), 函数运算, LIKE(前导模糊查询), IS NULL, 隐式转换(相当于加了一层函数), 给索引查询的值应是已知数据(不能是未知字段值), 组合索引第一个字段不能使用索引的(如建立索引(id, name), 如果SQL使用WHERE NAME LIKE ?则不使用索引)

在什么字段上建立索引?

  1. 字段出现在查询条件中,并且查询条件可以使用索引;
  2. 语句执行频率高,一天会有几千次以上;
  3. 通过字段条件可筛选的记录集很小,那数据筛选比例是多少才适合?

    小表:(记录数小于10000行的表):筛选比例<10%;

    大表:(筛选返回记录数)<(表总记录数*单条记录长度)/10000/16

需要建立索引的字段:主键、外键和有标示意义的字段(如HASH_CODE, IDCARD等)

需评估建立索引的字段:日期、年月、状态标志、类型、区域、操作人员、数值和长字符

不适合建立索引的字段:描述备注和大字段

索引对DML(INSERT,UPDATE,DELETE)附加的开销有多少?

(仅供参考)

索引对于Insert性能降低56%

索引对于Update性能降低47%

索引对于Delete性能降低29%

因此对于写IO压力比较大的系统,表的索引需要仔细评估必要性,另外索引也会占用一定的存储空间。

只通过索引访问数据

有些时候,我们只是访问表中的几个字段,并且字段内容较少,我们可以为这几个字段单独建立一个组合索引,这样就可以直接只通过访问索引就能得到数据,一般索引占用的磁盘空间比表小很多,所以这种方式可以大大减少磁盘IO开销。

例如:

SELECT id, name FROM company WHERE type = 2;

如果这个SQL经常使用,我们可以在type,id,name上创建组合索引。还是拿字典举例:有一个需求,需要查询一本汉语字典中所有汉字的个数,如果我们的字典没有目录索引,那我们只能从字典内容里一个一个字计数,最后返回结果。如果我们有一个拼音目录,那就可以只访问拼音目录的汉字进行计数。如果一本字典有1000页,拼音目录有20页,那我们的数据访问成本相当于全表访问的50分之一。切记,性能优化是无止境的,当性能可以满足需求时即可,不要过度优化。在实际数据库中我们不可能把每个SQL请求的字段都建在索引里,所以这种只通过索引访问数据的方法一般只用于核心应用,也就是那种对核心表访问量最高且查询字段数据量很少的查询。

优化SQL执行计划

返回更少的数据

SQL分页

只返回需要的字段

好理解,不多说

杂项

  1. 尽量使用列明代替*
  2. 尽量使用WHERE代替HAVING,如有GROUP BY则必须使用HAVING
  3. 尽量使用多表查询代替子查询