位置：首页-资讯-数据库

数据库index

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

Indexes(索引):

索引可以使mysql快速的找到和检索一张包含百万甚至亿万数据的表中的一组纪录。如果你曾使用过mysql，不管是多久，你可能为了更轻快的得到查询的结果而使用过索引。你也可能发现mysql的索引有时候并不想你想像的那样工作。

对很多使用者来说，索引就像黑色的艺术。有时候奇迹快速地工作，有时候却像是缓慢或者阻碍数据插入。

在本章中，我们将介绍一些索引的概念和mysql提供的各种不同索引。

Indexing Basics(索引基础):

理解mysql如何使用索引，最好首先理解索引的基础工作和特征。一旦你对索引的特征有基本的理解，你就能够更加合理的正确使用它们了。

Index Concepts(索引的概念):

要理解索引让mysql做什么，思考mysql是如何工作才得到查询的结果。想象电话本是一个含有California州大约35000000条电话的电话本集合。无序地记录在大脑里，考虑这样查询：

select * from phone_book where last_name = 'Zawodny';

没有任何类型的索引来咨询，数据库必须读在phone_book这张表里的所有记录，然后比较last_name这个字段是否与字符串"Zawodny"匹配上。当然了，这种方法是低效的。一旦电话的记录增加了，就会需要去找对应给予的记录。在计算机科学中，我们称之为O(n)时间复杂度问题。

然而给定的真实电话本，我们都知道如何快速定位到名字是Zawodny的地方：翻到书的后面，以Z开头的地方。因为第二个单词是a，我们知道所有匹配的会在名单列表以Z开头的未知附近。这个方法是基于数据排序的知识的。

这是作弊，不是吗？并不是。你可以快速找到Zawodnys的原因是它们的姓被按字母排序了。当然了，如此简单的找到Zawodny是因为你知道ABC字母。。。

多数教科书（比如这本）都会在数的背面提供索引。因为这些索引按顺序排列在相应的页码，使你经常快速的找到书中的术语和概念。想要知道数据库热拷贝在哪里有讨论吗？查看索引页久知道了。

数据库索引也是类似的。书的作者或出版者将会在书中选择重要的概念和术语作为索引。你可以在数据库的表中选择特定的字段创建索引。用前面的例子，你会创建姓为索引来快速查找电话号码：

ALTER TABLE phone_book ADD INDEX (last_name)

在这样做的过程中，你会在phone_book表中向数据库要一个按姓排列的顺序列表。每个名字都有自己匹配记录的位置－－就像这本书后面索引中列的各个条目的页码。

从数据库服务器的角度来看，当在执行一个查询时索引的存在可以从固定的结果中快速的删除可能的行。在没有任何索引时，MYSQL(比如一个数据库服务器)会检查表中的每一行数据。这不仅仅是时间上的浪费，也会占用大量磁盘输入输出从而严重弄脏磁盘缓存。

在真实世界中，很少能找到刚刚被排序和已经排序的动态的数据。书是一种特例，它们趋向于保持静止。

因为数据库需要为索引值维护一个单独的列表，并在数据更新同时使它们保持更新。你千万不能希望一个表中的所有字段都作为索引。索引是一种空间和时间的折中。在做每个insert，update，delete查询时使你的大部分（不是全部）查询更快时，你将在磁盘空间和CPU上牺牲一些额外的空间。

大部分数据库的说明书使用了术语索引和键可互换。说last_name是phone_book表中的一个键等同于last_name字段是phone_book表的索引。

Partial indexes(部分索引):

索引是空间和性能的交换。但是有时候你并不希望用空间来交换性能。幸亏，MYSQL为你提供了很多关于通过索引控制空间的方法。当你有一张phone_book表，里面有二十亿数据。在last_name上增加一个索引将需要很多空间，如果每个last_name占8位，你会发现这个数据的索引大约需要16G的空间。无论你要做什么，行指针都要对每条记录增加额外的4-8位。

你可以只将前4位作为索引，而不是将整个last name作为索引。

ALERT TABLE phone_book ADD INDEX (last_name(4))

当你这样做的时候，已经减少了索引所需要部分数据约一半的空间。这个折中的是MYSQL不能消除太多行使用这个索引。比如像下面这样的查询：

SELECT * FROM phone_book WHERE last_name = 'Smith';

取出所有以Smit开头的字段，包括所有名字是Smith，Smitty等人。这个查询会在之后丢弃Smitty等不想关的行。

Multicolumn indexes(多列):

像很多关系数据库引擎一样，MySQL允许你创建的索引由多列组成。

ALERT TABLE phone_book ADD INDEX (last_name, first_name)

如果你经常在where条件中用简短或单一没有足够的种类的列但查询结果是所有列，这种索引可以提高查询速度。当然你也可以使用部分索引减少占用空间：

ALERT TABLE phone_table ADD INDEX (last_name(4), first_name(4))

在任一情况下，查找Josh Woodward的快速执行：

select * from phone_book where last_name = 'Woodward' and first_name = 'Josh'

拥有last_name和first_name两个索引意味着MYSQL可以基于两个字段消除行。于是更大程度上减少需要考虑的行。毕竟，在电话本上姓氏以Wood开头的人远远多于姓氏以Wood开头并且名以Josh开头的人。

在讨论多列索引时，你可能会看到单独的索引列被称为键部分或“部分键”。多列索引也被称为综合索引或混合索引。

那么为什么不直接创建两个索引，一个建在last_name上，另一个建在first_name上？你也可以那么做，但是MYSQL不会同时使用它们。事实上，MYSQL在每次查询的时候只会在每张表中使用一个索引－－除了UNIONs之外[3].这个事实足以说明MySQL对于每张表的每次查询将永远只使用一个索引。

对于拆分索引上的first_name和last_name，MySQL会选择其中一个。这个选择是通过一个很有根据的猜想得到结果的，即判断哪个索引匹配到的行比较少。我们之所以说这是一个很有根据的猜想，是因为MySQL会对这个索引的踪迹进行统计来支撑他觉得哪个索引更好的推断。当然，这个统计是概括性的。虽然他们经常让MySQL做出明智的决定，但是如果你有非常团结的数据，MySQL可能会做出关于索引使用的次优选择。在某些地方如果索引的键是稀疏的（比如以X开头的名字）且其他地方是高度集中的（比如名字是Smith的以英语为母语的国家），我们把这种数据称为次优数据。这是一个重要的点，我们在书的稍后内容中将会提到。

Index order(索引的顺序):

MySQL是如何在索引中对值排序的？如果你使用过其他RDBMS，你可能希望MySQL对索引有按升序、降序或其他顺序排序的指定句法。MySQL提供了一个内部没有控制排序方法的索引值。这是有原因的，在4.0版本中，这个特性很好的优化了导致其他数据库性能降低的问题。

例如，有些数据库可能会执行这个快速查询：

SELECT * FROM phone_book WHERE last_name = 'Zawodny' ORDER BY first_name DESC

然后这个查询变慢：

SELECT * FROM phone_book WHERE last_name = 'Zawodny' ORDER BY first_name ASC

为什呢？因为有些数据库按降序排序存储索引，并对按这个顺序读取索引进行了优化。在第一个例子中，数据库使用了多列索引定位到所有匹配的记录，因为这些记录已经按降序被存储了，已经没有必要再对他们进行排序了。但是在第二个例子中，服务器找到所有匹配的记录，然后对这些行执行第二遍，以对它们进行排序。

在必要时，MySQL能够向后遍历索引。这也能使查询变得非常快，他能实现在任何情况下都不需要对记录进行排序。

Indexes as constraints(索引作为约束):

索引并不全是用于查询定位匹配行的。唯一索引指定特定值只能在给定列中出现一次。在电话本的例子中，你可以对电话号码创建一个唯一索引，以确保每个电话号码只出现一次。

ALERT TABLE phone_book ADD UNIQUE (phone_number)

唯一索引具有双重目的，当你根据一个电话号查询的时候他的功能和其他索引一样。

SELECT * FROM phone_book WHERE phone_number = '555-7271'

不过，他在插入和更新每条记录的同时还能检查以确保这条值是否已经存在。唯一索引以这种方式进行约束。

唯一索引和非唯一索引使用的空间一样多。记录每个字段的值和位置。如果使用唯一索引作为约束而从不作为索引那就是一种浪费。换句话说，你可以依靠唯一索引强制执行唯一性，但是不要写使用唯一键的查询。在这个例子中，MySQL没有必要将每个记录的位置存储在索引中：因为你永远不会使用他们。

不幸的，没有办法向MySQL表示你的意图。未来，我们将特别介绍这个具体的例子。MyISAM存储引擎已经支持了不带索引的唯一字段(它使用基于散列的系统)，但是该机制尚未在SQL级别公开。

Clustered and secondary indexes(集群和二级索引):

使用MyISAM表，索引保持在一个完全隔离的文件中，包含了主键（也可能有二级键）列表和表示记录的字节偏移量的值。这些确保了MySQL可以找到并且快速跳到在数据库中的那个点来查找记录。MySQL必须用这种方法存储索引，因为记录基本上是以乱序进行存储的。

集群索引，主键和记录本身聚集在一起，并且记录都以主键顺序存储。InnoDB使用聚集的索引。在Oracle世界，聚集的索引被称为索引组织表。它将帮助你记录主键和行顺序的关系。

当你的数据几乎都通过他的主键查找时，聚集的索引可以使查找快得难以置信。使用标准MyISAM索引，有两个查找，一个到索引，另一个到表自身，通过索引指定得位置。使用聚集的索引，有一个直接指向相关记录的查找。

有些操作使用集群索引是低效的。例如，当使用一个二级索引时，回到电话本的例子，当你需要将last_name作为主索引，phone_number作为二级索引，你做以下查询：

SELECT * FROM phone_book WHERE phone_number = '555-7271'

MySQL遍历了phone_number索引去查找555-7271项，包含了主键项Zawodny，因为电话本的主键是last name，MySQL会在数据库自身中跳到相关项。

换句话说，基于主键的查询将会非常快，基于二级索引的查找速度基本上和MyISAM索引是一致的。

但是在正确（或者说错误）的情况下，聚集索引事实上可能会降低性能。当你和二级索引一起使用时，你必须考虑这种结合对存储的影响。二级索引指向主键而不是特定的行。于是，如果你在一个很大的值上设索引，并且同时有一些二级索引，你将最终获得很多主索引的许多重复副本，首先作为与记录一起存储的聚集索引，然后再次以相同的次数与次级索引指向这些聚集索引。使用小的值作为主键，可能就不会这么差了，但是如果你使用某些个别数据将特别长的东西作为索引，例如URL，主键在磁盘上的重复存储将会导致存储问题。

另一个不常见的但是同样会发生问题的条件是：当数据在主键插入时主键在记录中被修改。这是聚集索引最昂贵的功能。一些事情的发生可以使这样的操作有更多的性能冲击。

根据查询的结果在问题中插入记录

基于插入数据的记录，为记录决定新的主键

搬迁存储的记录一次这个问题中的记录将在表空间中被移动到正确的位置

更新指向主键的所有二级索引

你可能在想，如果你为一些记录插入了主键，在执行UPDATE命令时将会花费相当一部分时间，特别是在更大的表中。更明智地选择主键。尽量使用几乎不会改变的值，例如使用社会安全账号而不是姓，使用序列号而不是产品名等等。

Unique indexes versus primary keys(唯一索引与主键):

如果你用过其他关系型数据库，你可能会好奇在MySQL中主键和唯一索引之间有什么不同。通常这取决于，在MyISAM 表这并没有什么区别。主键中唯一不同的是它的值不能是NULL的。主键只是一个不为空的唯一索引（NOT NULL UNIQUE INDEX）命名为键（PRIMARY）。MyISAM表不要求你声明一个主键。

InnoDB和BDB要求每个表都有主键，不过没有要求你指定其中某一个。如果你不指定，存储引擎将自动为你创建一个隐藏的主键。在这两种例子下，主键只是简单的增加数值，类似于一个自动增加列。如果你稍后要加一个主键，只需要使用ALTER TABLE添加一个。两个存储引擎将会丢弃它们自己内部的密钥去使用你的密钥。堆表不要求一个主键但是也会为你创建一个。事实上你可以创建一个无锁的堆表。

Indexing NULLs:

SQL在执行逻辑操作时使用三态逻辑是很难记住的。除非一个字段声明了NOT NUL，有三种可能的结果在逻辑比较中。这个比较可能由于它们的值是等价的所以结果是对的；也有可能由于它们的值是不等价的所以结果是错误的；又或者它会因为值是NULL而不会匹配。无论何时比较的一个值是NULL那么结果也是NULL。

程序员经常认为NULL就相当于没有被定义或未知的。这是一种告诉数据库服务器“一个未知的数据来了”的方式。所以NULL值的数据怎么能影响索引呢？

NULL值将会使用常规索引（即非唯一索引），所有数据库服务器都是这样。然而，不像狠多数据库服务器，MySQL允许你在唯一索引上使用NULL值[6]。你可以在唯一索引上存储你想存储尽可能多的NULL值。这看起来感觉有点违反直觉，但这就是NULL的本质。因为NULL代表了一个没有被定义的值。如果它在唯一索引只允许单个值，MySQL需要宣称所有的NULL值都是一样的。

去做一些更有意思的事，一个NULL值在主键中可能值出现一次。为什么呢？因为SQL标准主宰了这一行为。这是在MySQL少数方法中唯一不同于唯一键的主键。而且如果你想知道，在索引中允许NULL值并不会影响性能。

[1]这个有点偏差。数据库不只是存储匹配记录的位置，我们很快久知道为什么。

[2]这也是一种过度简化，MySQL使用一些策略来减少索引的大小，同时付出一些代价。

[3]在UNION中，每个逻辑是单独运行然后进行合并的。

[4]除了NULL，当然了，NULL总是特殊的情况。

[5]然而在现实世界中，这或许是一个很差的例子，任何人都可以和几个室友共用一个电话和你打电话。

[6]MySQL版本3.23及以前的版本不支持这个功能，4.0及以后的版本支持。

原文来自：https://www.safaribooksonline.com/library/view/high-performance-mysql/0596003064/ch04.html#ftn.hpmysql-CHP-4-FNOTE-4

另外的问题：

1.多列索引如：ALERT TABLE phone_table ADD INDEX (last_name(4), first_name(4))，下面各种情况都能够利用这个多列索引进行快速查找吗

(a)select * from phone_book where last_name = 'Woodward' and first_name = 'Josh'；

此时这个查询可以利用多列索引进行快速查找。