您必须知道的第一件事是,索引是一种避免扫描整个表以获得所需结果的方法。
索引有不同的类型,它们是在存储层中实现的,因此它们之间没有标准,而且它们还依赖于您使用的存储引擎。
InnoDB和B+树索引
对于InnoDB,最常见的索引类型是基于B+树的索引,它按排序顺序存储元素。另外,您不必访问实际的表来获取索引值,这使得查询返回的速度更快。
关于这个索引类型的“问题”是您必须查询最左边的值才能使用索引。所以,如果索引有两列,比如姓和名,那么查询这些字段的顺序
事情很多
.
因此,给出下表:
CREATE TABLE person (
last_name VARCHAR(50) NOT NULL,
first_name VARCHAR(50) NOT NULL,
INDEX (last_name, first_name)
);
此查询将利用索引:
SELECT last_name, first_name FROM person
WHERE last_name = "John" AND first_name LIKE "J%"
但是下面的一个不会
SELECT last_name, first_name FROM person WHERE first_name = "Constantine"
因为你在查询
first_name
列优先,它不是索引中最左边的列。
最后一个例子更糟:
SELECT last_name, first_name FROM person WHERE first_name LIKE "%Constantine"
因为现在,您要比较索引中最右边字段的最右边部分。
哈希索引
不幸的是,这是一种不同的索引类型,只有内存后端支持。它的速度非常快,但只对完全查找有用,这意味着您不能将它用于以下操作
>
,
<
或
LIKE
.
因为它只适用于内存后端,所以您可能不会经常使用它。我现在能想到的主要情况是,在内存中用另一个select的一组结果创建一个临时表,并使用哈希索引在这个临时表中执行许多其他选择。
如果你有一个大
VARCHAR
字段,您可以在使用B树时“模拟”哈希索引的使用,方法是创建另一列并在其上保存大值的哈希。假设您在一个字段中存储一个URL,并且值很大。您还可以创建一个名为
url_hash
并使用类似于
CRC32
或任何其他哈希函数,以便在插入URL时对其进行哈希。然后,当需要查询这个值时,可以这样做:
SELECT url FROM url_table WHERE url_hash=CRC32("http://gnu.org");
上述示例的问题在于,
CRC32
函数生成一个非常小的哈希,最终会在哈希值中产生很多冲突。如果需要精确的值,可以通过执行以下操作来解决此问题:
SELECT url FROM url_table
WHERE url_hash=CRC32("http://gnu.org") AND url="http://gnu.org";
即使碰撞次数很高,仍然值得散列,因为您将只对重复散列执行第二次比较(字符串1)。
不幸的是,使用这种技术,您仍然需要点击表来比较
url
字段。
包扎
每次你想谈论优化时,你都会考虑到一些事实:
-
整数比较比字符串比较快得多。它可以用一个关于哈希索引模拟的例子来说明。
InnoDB
.
-
也许,在一个过程中添加额外的步骤会使它更快,而不是更慢。它可以通过这样一个事实来说明:您可以优化
SELECT
通过将其拆分为两个步骤,使第一个步骤将值存储在新创建的内存表中,然后在此第二个表上执行更重的查询。
MySQL也有其他索引,但我认为B+树索引是有史以来使用最多的,哈希索引是一个好消息,但是您可以在
MySQL documentation
.
我强烈建议您阅读“高性能MySQL”这本书,上面的答案肯定是基于它关于索引的章节。