Insert

表中索引的个数是影响insert性能的最关键因素。索引越多，执行速度越慢。insert语句没有where子句，因此也不能直接受益于索引。

往表中插入新的一行记录涉及几个步骤：

• 首先，数据库必须找到一个地方来存储数据行。对于堆表而言，数据库可以把数据存储在任意有足够空间的块。这是一个非常简单和快速的过程。之后数据库所要做的就是将新数据添加到相应的数据块中。
• 如果表建有索引，数据库必须保证新数据被添加到该表的每一个索引中。因此，索引的数量与insert语句的成本呈正比关系。

在索引中新增条目比堆结构要慢得多，因为需要维护索引的顺序和索引树的平衡。索引的维护是insert成本中最昂贵的。

优化insert的性能的方法：

• 尽可能少建索引
• 在insert时临时删除索引，插入完成后再重建（常用于数据仓库）

Delete

与insert不同，delete的where子句直接受益于索引。

理论上，delete在一张没有索引的表中执行性能应该是最好的（就像insert那样）。然而，如果没有索引，数据库必须读取全表来找到需要删除的行。这意味着删除操作虽然很快，但是查询会非常慢。

类似select，如果需要删除大部分数据，应该选择不使用索引。

Update

update语句必须重新调整索引条目的位置。数据库需要删除旧条目，在另一个位置添加新条目。

update语句的性能也取决于表中索引的数量。与insert、delete不同的是，update不一定会涉及到所有字段，因此只会影响到包含被更新字段的索引。

数据结构

堆表(heap table)数据插入时存储位置是随机的，主要由数据库内部块的空闲情况决定，写入以后不会再移动存储位置，全表扫描时也不一定先插入的数据先查到。

在堆表上建立聚集索引(clustered index)后变成索引表(index-organized table)，数据按照索引的排序存储。好处在于：

1. 表数据存储在B树的叶节点中，节省了堆结构的空间
2. 通过聚集索引查询数据只需要一次index-only scan

但是，当聚集索引无法覆盖，需要在其基础上建另一个索引(secondary index)时，索引表的缺点就会显露出来。索引表中的数据不像堆表中那样静态存储，随着表中数据的更新，数据可以随时移动以维持索引的顺序。因此，二级索引中不能直接存储行的物理位置，必须使用一个逻辑键来代替。

嵌套循环连接 (nested loops)

1. 首先选择一张表作为连接的驱动表 (driving table)，这张表也成为外部表 (outer table)。由驱动表进行驱动连接的表称为被驱动表，也叫内部表 (inner table)。
2. 提取驱动表中符合条件的记录，逐行与被驱动表的连接列进行关联查询符合条件的记录。

数字格式的字符串

1
2
3

SELECT …
   FROM …
  WHERE numeric_string = 42

如果将一个字符串和一个数字作比较，数据库会隐式把字符串转换成数字。此时，在 NUMERIC_STRING 字段上建立的索引不能起作用。

合并多个字段

1
2
3
4

SELECT …
   FROM …
  WHERE ADDTIME(date_column, time_column)
      > DATE_ADD(now(), INTERVAL -1 DAY)

SQL Server 中的 filtered index 允许我们针对部分行建索引。

1
2
3
4

SELECT message
   FROM messages
  WHERE processed = 'N'
    AND receiver  = ?

>, < 和 between

INDEX RANGE SCAN 的最大性能风险是叶节点的遍历。

分析以下 query：

在传值的时候使用动态参数 @param 的方式有以下两个好处：

1. 防止 SQL 注入的最佳方式
2. 提高查询性能

像 SQL Server 和 Oracle 这样具有执行计划缓存的数据库，在多次执行同一语句时可以重复使用执行计划。这节省了重建执行计划的工作，但只有在 SQL 语句完全相同的情况下才能发挥作用。如果在 SQL 语句中放入不同的值，数据库会像处理不同的语句一样处理它，并重新创建执行计划。当使用动态参数时，SQL 语句就不会发生变化。

1
2
3

SELECT first_name, last_name, phone_number
   FROM employees
  WHERE UPPER(last_name) = UPPER('winand')

虽然 LAST_NAME 已有索引，但是执行计划并没有使用索引，仍然是全表扫描。因为数据库认为函数的计算结果跟传入参数之间并不相关。为了支持该查询，需要新建一个涵盖实际搜索词的索引。

1
2

CREATE INDEX emp_up_name 
     ON employees (UPPER(last_name))

这种在定义中包含函数或者运算表达式的索引称为基于函数的索引 (function-based index)。建索引时执行相关的计算并把计算结果存到索引中。

where 子句定义了 SQL 语句的搜索条件，因此它属于索引的核心功能：快速查找数据。一个写得不好得 where 子句是慢查询的第一要素。

接下来的内容将会围绕不同的操作符是如何影响索引的使用，以及如何确保一个索引可以用于尽可能多的查询来展开。

索引是什么

An index makes the query fast.

索引是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。它需要占用额外的空间来存储一份表中数据的副本，并且指向对应数据的实际存储位置。

在索引中检索数据就像使用字典的目录。为了保证能快速找到相关条目的位置，索引中的数据必须是排好序的。数据库索引比字典目录复杂的地方在于，数据库中信息会随时发生更新。执行insert, delete和update语句之后，在不操作大量数据的前提下，如果保持索引有序成了一个问题。