authors
的表上使用了这个查询(包含AuthorNum
、AuthorFName
、AuthorLName
等字段)。Create index Index_1 on Authors ([What to put here]);
我知道我需要输入一个列名,但是是哪个列名呢?
当用户查询表时,在Where语句中必须输入将要进行比较的列名吗?
authors
的表上使用了这个查询(包含AuthorNum
、AuthorFName
、AuthorLName
等字段)。Create index Index_1 on Authors ([What to put here]);
我知道我需要输入一个列名,但是是哪个列名呢?
当用户查询表时,在Where语句中必须输入将要进行比较的列名吗?
索引是数据库中的一个独立数据结构,数据冗余。它的主要目的是通过逻辑排序提供索引数据的有序表示,该排序与物理排序无关,使用双向链表和称为平衡搜索树(B 树)的树结构来实现。B 树非常好用,因为它们保持数据排序并允许在对数时间内进行搜索、访问、插入和删除。由于双向链表,我们能够轻松地在索引上向前或向后进行各种查询。插入变得简单,因为我们只需要重新排列指向不同数据片段的指针。数据库使用这些双向链表将叶子节点(通常位于 B+ 树或 B 树中)连接起来,每个叶子节点都存储在一个页面中,并在叶子节点之间建立逻辑排序。更新或插入等操作会变慢,因为它们实际上是文件系统中的两个写操作(一个用于表数据,一个用于索引数据)。
要定义最佳索引,你必须不仅了解索引的工作原理,还必须了解应用程序如何查询数据。例如,你必须知道出现在 WHERE 子句中的列组合。
查询 LAST_NAME 和 FIRST_NAME 列的常见限制与大小写敏感性有关。例如,我们希望与所有结果匹配,如 HoTingEr 等,而不是像 Hotinger 这样进行精确搜索。在 WHERE 子句中这很容易实现:我们只需说 WHERE UPPER(LAST_NAME) = UPPER('Hotinger')
然而,如果我们定义了一个LAST_NAME
的索引并进行查询,实际上会执行全表扫描,因为查询不是在LAST_NAME
上进行的,而是在UPPER(LAST_NAME)
上进行的。从数据库的角度来看,这是完全不同的。因此,在这种情况下,应该改为在UPPER(LAST_NAME)
上定义索引。
索引不一定只针对一个列。例如,如果主键是复合键(由多个列组成),则会创建连接索引,也称为组合索引。请注意,连接索引的排序对其可用性和可扩展性有重大影响,因此必须谨慎选择。基本上,排序应与WHERE
子句中的排序方式相匹配。
通配符字符的位置非常重要。LIKE
子句仅在树遍历期间使用通配符之前的字符;其余字符不会缩小扫描索引范围。前缀越具有选择性,LIKE
子句扫描的索引范围就越窄。这使得索引查找更快。提示一下,避免以通配符开头的LIKE
子句,例如"%OTINGER%"
。对于全文搜索,MySQL提供了MATCH
和AGAINST
关键字。从MySQL 5.6开始,您可以使用全文索引。有关这些结果的索引的更深入讨论,请查看MySQL的全文搜索函数。
使用 text
列或非常大的 varchar
列时,您将无法在整个 text
/varchar
长度上创建索引,因为有一些限制(约为1024个ASCII字符的长度)。
在这种情况下,您需在索引声明中指定长度。
CREATE INDEX `my_limited_length_index` ON `my_table`(`long_text_content`(512));
-- please notice the use of the numeric length of the index after the column name
索引不仅仅是从一个列中构建的,有些可能是从多个列中构建的,还有一些可能只是从某些列的部分信息中构建的。例如,如果您有一个完整的datetime
列,但您知道您只会通过date
过滤记录,则可以基于datetime
列构建一个索引,但只包含date
信息。
-- `my_table` has a `created` column of type timestamp
CREATE INDEX `my_date_created` ON `my_table`(DATE(`created`));
-- please notice the use of the DATE function which extracts only
-- the date from the `created` timestamp
WHERE
子句中比较的列上创建索引以加快查询速度。AuthorFName
搜索,则在该列上创建索引。如果按AuthorLName
搜索,则在该列上创建索引。WHERE AuthorLName = 'something'
和WHERE AuthorLName LIKE 'something%'
可以通过在{AuthorLName}上建立索引来加速。WHERE AuthorLName = 'something AND AuthorFName = 'something else'
可以通过在{AuthorLName,AuthorFName}或者{AuthorFName,AuthorLName}上建立复合索引来加速。WHERE AuthorLName = 'something OR AuthorFName = 'something else'
(虽然没有太多意义,但这里用作示例)可以通过建立两个索引:在{AuthorLName}和{AuthorFName}中,来加速。WHERE AuthorLName LIKE '%something'
无法通过B-Tree索引来加速(请考虑全文索引)。索引应跨越您将在WHERE语句中使用的列。
为了更好地理解,这里有一个例子:
SELECT * FROM Authors WHERE AuthorNum > 10 AND AuthorLName LIKE 'A%';
SELECT * FROM Authors WHERE AuthorLName LIKE 'Be%';
如果您经常使用上述查询,建议您拥有两个索引:
Create index AuthNum_AuthLName_Index on Authors (AuthorNum, AuthorLName);
Create index AuthLName_Index on Authors (AuthorLName);
WHERE
语句中使用的列相同的组合。where
子句中提供的值以相同的方式(使用相同的哈希函数)进行哈希处理,然后与表中记录的哈希值进行比较。这意味着哈希表索引不能用于比较或相似性,只能用于严格的相等性。这意味着在需要比较时将使用B树索引。 - Mihai Stancu