网站怎么seo关键词排名优化推广,asp网站出现乱码,胶州做网站的,网站用哪些系统做的好索引(在MySQL中也叫做“键#xff08;key)”)是存储引擎用于快速找到记录的一种数据结构。这是索引的基本功能#xff0c;除此之外#xff0c;本章还将讨论索引其他一些方面有用的属性。 索引对于良好的性能非常关键。尤其是当表中的数据量越来越大时#xff0c;索引对性能…索引(在MySQL中也叫做“键key)”)是存储引擎用于快速找到记录的一种数据结构。这是索引的基本功能除此之外本章还将讨论索引其他一些方面有用的属性。 索引对于良好的性能非常关键。尤其是当表中的数据量越来越大时索引对性能的影响愈发重要。在数据量较小且负载较低时不恰当的索引对性能的影响可能还不明显但当数据量逐渐增大时性能则会急剧下降。 不过索引却经常被忽略有时候甚至被误解所以在实际案例中经常会遇到由糟糕索引导致的问题。这也是我们把索引优化放在了靠前的章节甚至比查询优化还靠前的原因。 索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高几个数量级“最优”的索引有时比一个“好的”索引性能要好两个数量级。创建一个真正“最优”的索引经常需要重写查询所以本章和下一章的关系非常紧密。 5.1 索引基础
要理解MySQL中索引是如何工作的,最简单的方法就是去看看一本书的“索引”部分:如果想在一本书中找到某个特定主题一般会先看书的“索引”找到对应的页码。 在MySQL中存储引擎用类似的方法使用索引其先在索引中找到对应值然后根据匹配的索引记录找到对应的数据行。假如要运行下面的查询: mysql SELECT first_name FROH sakila.actor wHERE actor_id 5; 如果在actor_id列上建有索引则 MySQL将使用该索引找到actor_id为5的行也就是说MySQL先在索引上按值进行查找然后返回所有包含该值的数据行。 索引可以包含一个或多个列的值。如果索引包含多个列那么列的顺序也十分重要因为MySQL 只能高效地使用索引的最左前缀列。创建一个包含两个列的索引和创建两个只包含一列的索引是大不相同的下面将详细介绍。 5.1.1 索引的类型
索引有很多种类型可以为不同的场景提供更好的性能。在MySQL中索引是在存储引擎层而不是服务器层实现的。所以并没有统一的索引标准:不同存储引擎的索引的工作方式并不一样也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引其底层的实现也可能不同。 下面我们先来看看MySQL支持的索引类型以及它们的优点和缺点。 B-Tree索引
当人们谈论索引的时候如果没有特别指明类型那多半说的是B-Tree索引它使用B-Tree数据结构来存储数据生2。大多数MySQL引擎都支持这种索引。Archive引擎是一个例外:5.1之前Archive不支持任何索引直到5.1才开始支持单个自增列(AUTO_INCREMENT的索引。 我们使用术语“B-Tree”,是因为MySQL在 CREATE TABLE和其他语句中也使用该关键字。不过底层的存储引擎也可能使用不同的存储结构例如NDB集群存储引擎内部实际上使用了T-Tree结构存储这种索引即使其名字是BTREE ﹔InnoDB则使用的是BTree,各种数据结构和算法的变种不在本书的讨论范围之内。 存储引擎以不同的方式使用B-Tree索引性能也各有不同各有优劣。例如MyISAM使用前缀压缩技术使得索引更小但InnoDB则按照原数据格式进行存储。再如MyISAM索引通过数据的物理位置引用被索引的行而InnoDB则根据主键引用被索引的行。 B-Tree通常意味着所有的值都是按顺序存储的并且每一个叶子页到根的距离相同。图5-1展示了B-Tree索引的抽象表示大致反映了InnoDB索引是如何工作的。MyISAM使用的结构有所不同但基本思想是类似的。 B-Tree索引能够加快访问数据的速度因为存储引擎不再需要进行全表扫描来获取需要的数据取而代之的是从索引的根节点图示并未画出开始进行搜索。根节点的槽中存放了指向子节点的指针存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么是找到对应的值要么该记录不存在。 叶子节点比较特别它们的指针指向的是被索引的数据而不是其他的节点页不同引擎的“指针”类型不同)。图5-1中仅绘制了一个节点和其对应的叶子节点其实在根节点和叶子节点之间可能有很多层节点页。树的深度和表的大小直接相关。 B-Tree对索引列是顺序组织存储的所以很适合查找范围数据。例如在一个基于文本域的索引树上按字母顺序传递连续的值进行查找是非常合适的所以像“找出所有以I到K开头的名字”这样的查找效率会非常高。 请注意索引对多个值进行排序的依据是CREATE TABLE语句中定义索引时列的顺序。看一下最后两个条目两个人的姓和名都一样则根据他们的出生日期来排列顺序。 可以使用B-Tree索引的查询类型。B-Tree索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找生3。前面所述的索引对如下类型的查询有效。 全值匹配
全值匹配指的是和索引中的所有列进行匹配例如前面提到的索引可用于查找姓名为Cuba Allen、出生于1960-01-01的人。 匹配最左前缀
前面提到的索引可用于查找所有姓为Allen的人即只使用索引的第一列。 匹配列前缀
也可以只匹配某一列的值的开头部分。例如前面提到的索引可用于查找所有以J开头的姓的人。这里也只使用了索引的第一列。 匹配范围值
例如前面提到的索引可用于查找姓在Allen和Barrymore之间的人。这里也只使用了索引的第一列。 精确匹配某一列并范围匹配另外一列
前面提到的索引也可用于查找所有姓为Allen并且名字是字母K开头比如Kim、Karl等)的人。即第一列last_name全匹配第二列first_name范围匹配。 只访问索引的查询
B-Tree通常可以支持“只访问索引的查询”即查询只需要访问索引而无须访问数据行。后面我们将单独讨论这种“覆盖索引”的优化。 因为索引树中的节点是有序的所以除了按值查找之外索引还可以用于查询中的ORDER BY操作按顺序查找)。一般来说如果B-Tree可以按照某种方式查找到值那么也可以按照这种方式用于排序。所以如果ORDER BY子句满足前面列出的几种查询类型则这个索引也可以满足对应的排序需求。 下面是一些关于B-Tree索引的限制:
· 如果不是按照索引的最左列开始查找则无法使用索引。例如上面例子中的索引无法用于查找名字为Bill的人也无法查找某个特定生日的人因为这两列都不是最左数据列。类似地也无法查找姓氏以某个字母结尾的人。
· 不能跳过索引中的列。也就是说前面所述的索引无法用于查找姓为Smith并且在某个特定日期出生的人。如果不指定名(first_name)则 MySQL只能使用索引的第一列。
· 如果查询中有某个列的范围查询则其右边所有列都无法使用索引优化查找。例如有查询wHERE last_name Smith AND first_name LIKE %’AND dob 1976-12-23这个查询只能使用索引的前两列因为这里LIKE是一个范围条件但是服务器可以把其余列用于其他目的)。如果范围查询列值的数量有限那么可以通过使用多个等于条件来代替范围条件。在本章的索引案例学习部分我们将演示一个详细的案例。 到这里读者应该可以明白前面提到的索引列的顺序是多么的重要:这些限制都和索引列的顺序有关。在优化性能的时候可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。 也有些限制并不是B-Tree本身导致的而是 MySQL优化器和存储引擎使用索引的方式导致的,这部分限制在未来的版本中可能就不再是限制了。 哈希索引
哈希索引 (hash index基于哈希表实现只有精确匹配索引所有列的查询才有效。对于每一行数据存储引擎都会对所有的索引列计算一个哈希码(hash code)哈希码是一个较小的值并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中同时在哈希表中保存指向每个数据行的指针。 在MySQL 中只有Memory引擎显式支持哈希索引。这也是Memory引擎表的默认索引类型Memory引擎同时也支持B-Tree索引。值得一提的是Memory引擎是支持非唯一哈希索引的这在数据库世界里面是比较与众不同的。如果多个列的哈希值相同索引会以链表的方式存放多个记录指针到同一个哈希条目中。 MySQL先计算Peter的哈希值并使用该值寻找对应的记录指针。因为f( Peter )8784所以MySQL在索引中查找8784可以找到指向第3行的指针最后一步是比较第三行的值是否为Peter以确保就是要查找的行。 因为索引自身只需存储对应的哈希值所以索引的结构十分紧凑这也让哈希索引查找的速度非常快。然而哈希索引也有它的限制: ·哈希索引只包含哈希值和行指针而不存储字段值所以不能使用索引中的值来避免读取行。不过访问内存中的行的速度很快所以大部分情况下这一点对性能的影响并不明显。
·哈希索引数据并不是按照索引值顺序存储的所以也就无法用于排序。
·哈希索引也不支持部分索引列匹配查找因为哈希索引始终是使用索引列的全部内容来计算哈希值的。例如,在数据列(A,B)上建立哈希索引,如果查询只有数据列A,则无法使用该索引。
·哈希索引只支持等值比较查询包括、IN()、(注意◇和是不同的操作)。也不支持任何范围查询例如 WHERE price 100。
·访问哈希索引的数据非常快除非有很多哈希冲突不同的索引列值却有相同的哈希值)。当出现哈希冲突的时候存储引擎必须遍历链表中所有的行指针逐行进行比较直到找到所有符合条件的行。
·如果哈希冲突很多的话一些索引维护操作的代价也会很高。例如如果在某个选择性很低哈希冲突很多的列上建立哈希索引那么当从表中删除一行时存储引擎需要遍历对应哈希值的链表中的每一行找到并删除对应行的引用冲突越多代价越大。 因为这些限制哈希索引只适用于某些特定的场合。而一旦适合哈希索引则它带来的性能提升将非常显著。举个例子在数据仓库应用中有一种经典的“星型”schema需要关联很多查找表哈希索引就非常适合查找表的需求。
.
除了Memory引擎外NDB集群引擎也支持唯一哈希索引且在NDB集群引擎中作用非常特殊但这不属于本书的范围。 InnoDB引擎有一个特殊的功能叫做“自适应哈希索引 (adaptive hash index)”。当InnoDB注意到某些索引值被使用得非常频繁时它会在内存中基于B-Tree索引之上再创建一个哈希索引这样就让B-Tree索引也具有哈希索引的一些优点比如快速的哈希查找。这是一个完全自动的、内部的行为用户无法控制或者配置不过如果有必要完全可以关闭该功能。 创建自定义哈希索引。如果存储引擎不支持哈希索引则可以模拟像InnoDB一样创建哈希索引这可以享受一些哈希索引的便利例如只需要很小的索引就可以为超长的键创建索引。 思路很简单:在B-Tree基础上创建一个伪哈希索引。这和真正的哈希索引不是一回事因为还是使用B-Tree进行查找但是它使用哈希值而不是键本身进行索引查找。你需要做的就是在查询的WHERE子句中手动指定使用哈希函数。 下面是一个实例例如需要存储大量的URL并需要根据URL进行搜索查找。如果使用B-Tree来存储URL存储的内容就会很大因为URL本身都很长。正常情况下会有如下查询: mysql SELECT id FROM ur1 NHERE urlhttp://nnw.mysq1.com; 若删除原来URL列上的索引而新增一个被索引的url_crc列使用CRC32做哈希就可以使用下面的方式查询: mysqlSELECT id FROM ur1 NHERE ur1http://vwrw.mysql.com
-AND url_crcCRC32( http://winw.mysql.com); 这样做的性能会非常高因为MySQL优化器会使用这个选择性很高而体积很小的基于url_crc列的索引来完成查找在上面的案例中索引值为1560514994)。即使有多个记录有相同的索引值查找仍然很快只需要根据哈希值做快速的整数比较就能找到索引条目然后一一比较返回对应的行。另外一种方式就是对完整的URL字符串做索引那样会非常慢。 这样实现的缺陷是需要维护哈希值。可以手动维护也可以使用触发器实现。下面的案例演示了触发器如何在插入和更新时维护url_crc列。首先创建如下表: 然后创建触发器。先临时修改一下语句分隔符这样就可以在触发器定义中使用分号: 剩下的工作就是验证一下触发器如何维护哈希索引: 如果采用这种方式记住不要使用SHA1()和 MD5()作为哈希函数。因为这两个函数计算出来的哈希值是非常长的字符串会浪费大量空间比较时也会更慢。SHA1()和MD5()是强加密函数设计目标是最大限度消除冲突但这里并不需要这样高的要求。简单哈希函数的冲突在一个可以接受的范围同时又能够提供更好的性能。 如果数据表非常大CRC32()会出现大量的哈希冲突则可以考虑自己实现一个简单的64位哈希函数。这个自定义函数要返回整数而不是字符串。一个简单的办法可以使用MD5()函数返回值的一部分来作为自定义哈希函数。这可能比自己写一个哈希算法的性能要差参考第7章)不过这样实现最简单: 因为所谓的“生日悖论”注5出现哈希冲突的概率的增长速度可能比想象的要快得多。CRC32()返回的是32位的整数当索引有93 000条记录时出现冲突的概率是1%。例如我们将/usr/share/dict/words中的词导入数据表并进行CRC32()计算,最后会有98569行。这就已经出现一次哈希冲突了冲突让下面的查询返回了多条记录: 要避免冲突问题必须在WHERE条件中带入哈希值和对应列值。如果不是想查询具体值例如只是统计记录数不精确的)则可以不带入列值直接使用CRC32()的哈希值查询即可。还可以使用如FNV64()函数作为哈希函数这是移植自Percona Server的函数可以以插件的方式在任何MySQL版本中使用哈希值为64位速度快且冲突比CRC32()要少很多。 空间数据索引(R-Tree)
MyISAM表支持空间索引可以用作地理数据存储。和B-Tree索引不同这类索引无须前缀查询。空间索引会从所有维度来索引数据。查询时可以有效地使用任意维度来组合查询。必须使用MySQL 的GIS相关函数如MBRCONTAINS()等来维护数据。MySQL的GIS支持并不完善所以大部分人都不会使用这个特性。开源关系数据库系统中对GIS的解决方案做得比较好的是PostgresQL的 PostGIS。 全文索引
全文索引是一种特殊类型的索引它查找的是文本中的关键词而不是直接比较索引中的值。全文搜索和其他几类索引的匹配方式完全不一样。它有许多需要注意的细节如停用词、词干和复数、布尔搜索等。全文索引更类似于搜索引擎做的事情而不是简单的wHERE条件匹配。 在相同的列上同时创建全文索引和基于值的B-Tree索引不会有冲突全文索引适用于MATCH AGAINST操作而不是普通的 WHERE条件操作。 我们将在第7章讨论更多的全文索引的细节。 其他索引类别
还有很多第三方的存储引擎使用不同类型的数据结构来存储索引。例如TokuDB使用分形树索引 (fractal tree index)这是一类较新开发的数据结构既有B-Tree的很多优点也避免了B-Tree的一些缺点。如果通读完本章可以看到很多关于InnoDB的主题包括聚簇索引、覆盖索引等。多数情况下针对InnoDB的讨论也都适用于TokuDB。
ScaleDB使用Patricia tries (这个词不是拼写错误)其他一些存储引擎技术如InfiniDB和Infobright则使用了一些特殊的数据结构来优化某些特殊的查询。 5.2 索引的优点
索引可以让服务器快速地定位到表的指定位置。但是这并不是索引的唯一作用到目前为止可以看到根据创建索引的数据结构不同索引也有一些其他的附加作用。 最常见的B-Tree索引按照顺序存储数据所以MySQL可以用来做ORDER BY和GROUPBY操作。因为数据是有序的所以B-Tree也就会将相关的列值都存储在一起。最后因为索引中存储了实际的列值所以某些查询只使用索引就能够完成全部查询。据此特性总结下来索引有如下三个优点: 索引大大减少了服务器需要扫描的数据量。索引可以帮助服务器避免排序和临时表。索引可以将随机1O变为顺序1/O。 “索引”这个主题完全值得单独写一本书如果想深入理解这部分内容强烈建议阅读由Tapio Lahdenmaki和Mike Leach编写的Relational Database Index Design and theOptimizers (Wiley出版社一书该书详细介绍了如何计算索引的成本和作用、如何评估查询速度、如何分析索引维护的代价和其带来的好处等。 Lahdenmaki和Leach在书中介绍了如何评价一个索引是否适合某个查询的“三星系统”(three-star system):索引将相关的记录放到一起则获得一星﹔如果索引中的数据顺序和查找中的排列顺序一致则获得二星﹔如果索引中的列包含了查询中需要的全部列则获得“三星”。后面我们将会介绍这些原则。 5.3 高性能的索引策略
正确地创建和使用索引是实现高性能查询的基础。前面已经介绍了各种类型的索引及其对应的优缺点。现在我们一起来看看如何真正地发挥这些索引的优势。 高效地选择和使用索引有很多种方式其中有些是针对特殊案例的优化方法有些则是针对特定行为的优化。使用哪个索引以及如何评估选择不同索引的性能影响的技巧则需要持续不断地学习。接下来的几个小节将帮助读者理解如何高效地使用索引。 5.3.1 独立的列
我们通常会看到一些查询不当地使用索引或者使得MySQL无法使用已有的索引。如果查询中的列不是独立的则MySQL就不会使用索引。“独立的列”是指索引列不能是表达式的一部分也不能是函数的参数。 例如下面这个查询无法使用actor_id列的索引:
mysqlSELECT actor_id FROM sakila.actor WHERE actor_id 1 5; 凭肉眼很容易看出 MHERE中的表达式其实等价于actor_id 4但是MySQL无法自动解析这个方程式。这完全是用户行为。我们应该养成简化WHERE条件的习惯始终将索引列单独放在比较符号的一侧。
下面是另一个常见的错误:
mysql SELECT ... MHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) 10; 5.3.2 前缀索引和索引选择性
有时候需要索引很长的字符列这会让索引变得大且慢。一个策略是前面提到过的模拟哈希索引。但有时候这样做还不够还可以做些什么呢? 通常可以索引开始的部分字符这样可以大大节约索引空间从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指不重复的索引值也称为基数cardinality)和数据表的记录总数#T)的比值范围从1/#T到1之间。索引的选择性越高则查询效率越高因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1这是最好的索引选择性性能也是最好的。 一般情况下某个列前缀的选择性也是足够高的足以满足查询性能。对于BLOB、TEXT或者很长的VARCHAR类型的列必须使用前缀索引因为MySQL不允许索引这些列的完整长度。 诀窍在于要选择足够长的前缀以保证较高的选择性同时又不能太长以便节约空间)。前缀应该足够长以使得前缀索引的选择性接近于索引整个列。换句话说前缀的“基数”应该接近于完整列的“基数”。 为了决定前缀的合适长度需要找到最常见的值的列表然后和最常见的前缀列表进行比较。在示例数据库Sakila中并没有合适的例子,所以我们从表city中生成一个示例表这样就有足够的数据进行演示: 现在我们有了示例数据集。数据分布当然不是真实的分布﹔因为我们使用了RAND()所以你的结果会与此不同但对这个练习来说这并不重要。首先我们找到最常见的城市列表: 每个前缀都比原来的城市出现的次数更多因此唯一前缀比唯一城市要少得多。然后我们增加前缀长度直到这个前缀的选择性接近完整列的选择性。经过实验后发现前缀长度为7时比较合适: 计算合适的前缀长度的另外一个办法就是计算完整列的选择性并使前缀的选择性接近于完整列的选择性。下面显示如何计算完整列的选择性: 通常来说尽管也有例外情况)这个例子中如果前缀的选择性能够接近0.031基本上就可用了。可以在一个查询中针对不同前缀长度进行计算这对于大表非常有用。下面给出了如何在同一个查询中计算不同前缀长度的选择性: 查询显示当前缀长度到达7的时候再增加前缀长度选择性提升的幅度已经很小了。 只看平均选择性是不够的也有例外的情况需要考虑最坏情况下的选择性。平均选择性会让你认为前缀长度为4或者5的索引已经足够了但如果数据分布很不均匀可能就会有陷阱。如果观察前缀为4的最常出现城市的次数可以看到明显不均匀: 如果前缀是4个字节则最常出现的前缀的出现次数比最常出现的城市的出现次数要大很多。即这些值的选择性比平均选择性要低。如果有比这个随机生成的示例更真实的数据就更有可能看到这种现象。例如在真实的城市名上建一个长度为4的前缀索引对于以“San”和“New”开头的城市的选择性就会非常糟糕因为很多城市都以这两个词开头。 在上面的示例中已经找到了合适的前缀长度下面演示一下如何创建前缀索引: mysql ALTER TABLE sakila.city_demo ADD KEY (city(7)); 前缀索引是一种能使索引更小、更快的有效办法但另一方面也有其缺点:MySQL无法使用前缀索引做ORDER BY和GROUP BY也无法使用前缀索引做覆盖扫描。 一个常见的场景是针对很长的十六进制唯一ID使用前缀索引。在前面的章节中已经讨论了很多有效的技术来存储这类ID信息但如果使用的是打包过的解决方案因而无法修改存储结构那该怎么办?例如使用vBulletin或者其他基于MySQL的应用在存储网站的会话(SESSION)时需要在一个很长的十六进制字符串上创建索引。此时如果采用长度为8的前缀索引通常能显著地提升性能并且这种方法对上层应用完全透明。 5.3.3 多列索引
很多人对多列索引的理解都不够。一个常见的错误就是为每个列创建独立的索引或者按照错误的顺序创建多列索引。 我们会在5.3.4节中单独讨论索引列的顺序问题。先来看第一个问题为每个列创建独立的索引从 SHOw CREATE TABLE中很容易看到这种情况: 这种索引策略一般是由于人们听到一些专家诸如“把 wHERE条件里面的列都建上索引”这样模糊的建议导致的。实际上这个建议是非常错误的。这样一来最好的情况下也只能是“一星”索引其性能比起真正最优的索引可能差几个数量级。有时如果无法设计一个“三星”索引那么不如忽略掉WHERE子句集中精力优化索引列的顺序或者创建一个全覆盖索引。 在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL5.0和更新版本引入了一种叫“索引合并”(index merge)的策略一定程度上可以使用表上的多个单列索引来定位指定的行。更早版本的MySQL 只能使用其中某一个单列索引然而这种情况下没有哪一个独立的单列索引是非常有效的。例如表film_actor在字段film_id和 actor_id上各有一个单列索引。但对于下面这个查询wHERE条件这两个单列索引都不是好的选择: 但在MySQL 5.0和更新的版本中查询能够同时使用这两个单列索引进行扫描并将结果进行合并。这种算法有三个变种:OR条件的联合(union) ,AND条件的相交( intersection)组合前两种情况的联合及相交。下面的查询就是使用了两个索引扫描的联合通过EXPLAIN中的Extra列可以看到这点: MySQL会使用这类技术优化复杂查询所以在某些语句的Extra列中还可以看到嵌套操作。 索引合并策略有时候是一种优化的结果但实际上更多时候说明了表上的索引建得很糟糕:
·当出现服务器对多个索引做相交操作时通常有多个 AND条件)通常意味着需要一个包含所有相关列的多列索引而不是多个独立的单列索引。
·当服务器需要对多个索引做联合操作时通常有多个0R条件)通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高需要合并扫描返回的大量数据的时候。
·更重要的是优化器不会把这些计算到“查询成本”(cost)中优化器只关心随机页面读取。这会使得查询的成本被“低估”,导致该执行计划还不如直接走全表扫描。这样做不但会消耗更多的CPU和内存资源还可能会影响查询的并发性但如果是单独运行这样的查询则往往会忽略对并发性的影响。通常来说还不如像在MySQL4.1或者更早的时代一样将查询改写成UNION的方式往往更好。 如果在EXPLAIN中看到有索引合并应该好好检查一下查询和表的结构看是不是已经是最优的。也可以通过参数optimizer_switch来关闭索引合并功能。也可以使用IGNORE INDEX提示让优化器忽略掉某些索引。 5.3.4 选择合适的索引列顺序
我们遇到的最容易引起困惑的问题就是索引列的顺序。正确的顺序依赖于使用该索引的查询并且同时需要考虑如何更好地满足排序和分组的需要顺便说明本节内容适用于B-Tree索引﹔哈希或者其他类型的索引并不会像B-Tree索引一样按顺序存储数据)。 在一个多列B-Tree索引中索引列的顺序意味着索引首先按照最左列进行排序其次是第二列等等。所以索引可以按照升序或者降序进行扫描以满足精确符合列顺序的ORDER BY、GROUP BY和 DISTINCT等子句的查询需求。 所以多列索引的列顺序至关重要。在Lahdenmaki和 Leach的“三星索引”系统中列顺序也决定了一个索引是否能够成为一个真正的“三星索引”(关于三星索引可以参考本章前面的5.2节)。在本章的后续部分我们将通过大量的例子来说明这一点。 对于如何选择索引的列顺序有一个经验法则:将选择性最高的列放到索引最前列。这个建议有用吗﹖在某些场景可能有帮助但通常不如避免随机IO和排序那么重要考虑问题需要更全面(场景不同则选择不同没有一个放之四海皆准的法则。这里只是说明这个经验法则可能没有你想象的重要)。 当不需要考虑排序和分组时将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化WHERE条件的查找。在这种情况下这样设计的索引确实能够最快地过滤出需要的行对于在WHERE子句中只使用了索引部分前缀列的查询来说选择性也更高。然而性能不只是依赖于所有索引列的选择性整体基数)也和查询条件的具体值有关也就是和值的分布有关。这和前面介绍的选择前缀的长度需要考虑的地方一样。可能需要根据那些运行频率最高的查询来调整索引列的顺序让这种情况下索引的选择性最高。 以下面的查询为例: 是应该创建一个(staff_id, customer_id)索引还是应该颠倒一下顺序﹖可以跑一些查询来确定在这个表中值的分布情况并确定哪个列的选择性更高。先用下面的查询预测一下看看各个 WHERE 条件的分支对应的数据基数有多大: 这样做有一个地方需要注意查询的结果非常依赖于选定的具体值。如果按上述办法优化可能对其他一些条件值的查询不公平服务器的整体性能可能变得更糟或者其他某些查询的运行变得不如预期。 如果是从诸如pt-query-digest这样的工具的报告中提取“最差”查询那么再按上述办法选定的索引顺序往往是非常高效的。如果没有类似的具体查询来运行那么最好还是按经验法则来做因为经验法则考虑的是全局基数和选择性而不是某个具体查询: 当使用前缀索引的时候在某些条件值的基数比正常值高的时候问题就来了。例如在某些应用程序中对于没有登录的用户都将其用户名记录为“guset”在记录用户行为的会话(session表和其他记录用户活动的表中“guest”就成为了一个特殊用户ID。一旦查询涉及这个用户那么和对于正常用户的查询就大不同了因为通常有很多会话都是没有登录的。系统账号也会导致类似的问题。一个应用通常都有一个特殊的管理员账号和普通账号不同它并不是一个具体的用户系统中所有的其他用户都是这个用户的好友所以系统往往通过它向网站的所有用户发送状态通知和其他消息。这个账号的巨大的好友列表很容易导致网站出现服务器性能问题。 这实际上是一个非常典型的问题。任何的异常用户不仅仅是那些用于管理应用的设计糟糕的账号会有同样的问题﹔那些拥有大量好友、图片、状态、收藏的用户也会有前面提到的系统账号同样的问题。 下面是一个我们遇到过的真实案例在一个用户分享购买商品和购买经验的论坛上这个特殊表上的查询运行得非常慢: 这个查询看似没有建立合适的索引所以客户咨询我们是否可以优化。EXPLAIN的结果如下: MySQL 为这个查询选择了索引(groupId,userId)如果不考虑列的基数这看起来是一个非常合理的选择。但如果考虑一下user ID和group ID条件匹配的行数可能就会有不同的想法了: 从上面的结果来看符合组(groupId条件几乎满足表中的所有行符合用户(userId)条件的有130万条记录——也就是说索引基本上没什么用。因为这些数据是从其他应用中迁移过来的迁移的时候把所有的消息都赋予了管理员组的用户。这个案例的解决办法是修改应用程序代码区分这类特殊用户和组禁止针对这类用户和组执行这个查询。 从这个小案例可以看到经验法则和推论在多数情况是有用的但要注意不要假设平均情况下的性能也能代表特殊情况下的性能特殊情况可能会摧毁整个应用的性能。 最后尽管关于选择性和基数的经验法则值得去研究和分析但一定要记住别忘了WHERE子句中的排序、分组和范围条件等其他因素这些因素可能对查询的性能造成非常大的影响。. 5.3.5聚簇索引
聚簇索引些并不是一种单独的索引类型而是一种数据存储方式。具体的细节依赖于其实现方式但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。 当表有聚簇索引时,它的数据行实际上存放在索引的叶子页(leaf page)中。术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在两个不同的地方,所以一个表只能有一个聚簇索引(不过,覆盖索引可以模拟多个聚簇索引的情况本章后面将详细介绍)。 因为是存储引擎负责实现索引因此不是所有的存储引擎都支持聚簇索引。本节我们主要关注InnoDB但是这里讨论的原理对于任何支持聚簇索引的存储引擎都是适用的。 图5-3展示了聚簇索引中的记录是如何存放的。注意到叶子页包含了行的全部数据但是节点页只包含了索引列。在这个案例中索引列包含的是整数值。 一些数据库服务器允许选择哪个索引作为聚簇索引但直到本书写作之际还没有任何一个MySQL内建的存储引擎支持这一点。InnoDB将通过主键聚集数据这也就是说图5-3中的“被索引的列”就是主键列。 如果没有定义主键InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引InnoDB会隐式定义一个主键来作为聚簇索引。InnoDB 只聚集在同一个页面中的记录。包含相邻键值的页面可能会相距甚远。 聚簇主键可能对性能有帮助但也可能导致严重的性能问题。所以需要仔细地考虑聚簇索引尤其是将表的存储引擎从InnoDB改成其他引擎的时候反过来也一样)。 聚集的数据有一些重要的优点:
·可以把相关数据保存在一起。例如实现电子邮箱时可以根据用户ID来聚集数据这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引则每封邮件都可能导致一次磁盘I/O。
·数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
·使用覆盖索引扫描的查询可以直接使用页节点中的主键值。 如果在设计表和查询时能充分利用上面的优点那就能极大地提升性能。同时聚簇索引也有一些缺点: ·聚簇数据最大限度地提高了I/O密集型应用的性能但如果数据全部都放在内存中则访问的顺序就没那么重要了聚簇索引也就没什么优势了。
·插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。但如果不是按照主键顺序加载数据那么在加载完成后最好使用OPTIMIZE TABLE命令重新组织一下表。
·更新聚簇索引列的代价很高因为会强制InnoDB将每个被更新的行移动到新的位置。
·基于聚簇索引的表在插入新行或者主键被更新导致需要移动行的时候可能面临“页分裂(page split)”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时存储引擎会将该页分裂成两个页面来容纳该行这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间。
·聚簇索引可能导致全表扫描变慢尤其是行比较稀疏或者由于页分裂导致数据存储不连续的时候。
·二级索引(非聚簇索引可能比想象的要更大因为在二级索引的叶子节点包含了引用行的主键列。
·二级索引访问需要两次索引查找,而不是一次。 最后一点可能让人有些疑惑为什么二级索引需要两次索引查找?答案在于二级索引中保存的“行指针”的实质。要记住二级索引叶子节点保存的不是指向行的物理位置的指针而是行的主键值。 这意味着通过二级索引查找行存储引擎需要找到二级索引的叶子节点获得对应的主键值然后根据这个值去聚簇索引中查找到对应的行。这里做了重复的工作:两次B-Tree查找而不是一次性9。对于InnoDB自适应哈希索引能够减少这样的重复工作。 InnoDB和 MyISAM的数据分布对比
聚簇索引和非聚簇索引的数据分布有区别以及对应的主键索引和二级索引的数据分布也有区别通常会让人感到困扰和意外。来看看InnoDB和 MyISAM是如何存储下面这个表的: 假设该表的主键取值为1 ~10 000按照随机顺序插入并使用OPTIAIZE TABLE命令做了优化。换句话说数据在磁盘上的存储方式已经最优但行的顺序是随机的。列col2的值是从1~ 100之间随机赋值所以有很多重复的值。 MyISAM的数据分布。MyISAM的数据分布非常简单所以先介绍它。MyISAM按照数据插入的顺序存储在磁盘上如图5-4所示。 在行的旁边显示了行号从О开始递增。因为行是定长的所以MyISAM可以从表的开头跳过所需的字节找到需要的行(MyISAM并不总是使用图5-4中的“行号”而是根据定长还是变长的行使用不同策略)。 这种分布方式很容易创建索引。下面显示的一系列图隐藏了页的物理细节只显示索引中的“节点”索引中的每个叶子节点包含“行号”。图5-5显示了表的主键。 这里忽略了一些细节例如前一个B-Tree节点有多少个内部节点不过这并不影响对非聚簇存储引擎的基本数据分布的理解。 那col2列上的索引又会如何呢?有什么特殊的吗?回答是否定的:它和其他索引没有什么区别。图5-6显示了col2列上的索引。 事实上MyISAM中主键索引和其他索引在结构上没有什么不同。主键索引就是一个名 InnoDB 的数据分布。因为InnoDB支持聚簇索引所以使用非常不同的方式存储同样的数据。InnoDB以如图5-7所示的方式存储数据。 第一眼看上去感觉该图和前面的图5-5没有什么不同但再仔细看细节会注意到该图显示了整个表而不是只有索引。因为在InnoDB中聚簇索引“就是”表所以不像MyISAM那样需要独立的行存储。 聚簇索引的每一个叶子节点都包含了主键值、事务ID、用于事务和MvCCt10的回滚指针以及所有的剩余列在这个例子中是col2)。如果主键是一个列前缀索引InnoDB也会包含完整的主键列和剩下的其他列。 还有一点和MyISAM的不同是InnoDB的二级索引和聚簇索引很不相同。InnoDB二级索引的叶子节点中存储的不是“行指针”而是主键值并以此作为指向行的“指针”。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值当作指针会让二级索引占用更多的空间换来的好处是InnoDB在移动行时无须更新二级索引中的这个“指针”。 图5-8显示了示例表的col2索引。每一个叶子节点都包含了索引列这里是col2)紧接着是主键值(col1)。 图5-8展示了B-Tree的叶子节点结构但我们故意省略了非叶子节点这样的细节。InnoDB的非叶子节点包含了索引列和一个指向下级节点的指针下一级节点可以是非 如果还没有理解聚簇索引和非聚簇索引有什么区别、为何有这些区别及这些区别的重要性也不用担心。随着学习的深入尤其是学完本章剩下的部分以及下一章以后这些问题就会变得越发清楚。这些概念有些复杂需要一些时间才能完全理解。 在InnoDB表中按主键顺序插入行
如果正在使用InnoDB表并且没有什么数据需要聚集那么可以定义一个代理键(surrogate key)作为主键这种主键的数据应该和应用无关最简单的方法是使用AUTO_INCREMENT自增列。这样可以保证数据行是按顺序写入对于根据主键做关联操作的性能也会更好。 最好避免随机的不连续且值的分布范围非常大聚簇索引特别是对于I/O密集型的应用。例如从性能的角度考虑使用UUID来作为聚簇索引则会很糟糕:它使得聚簇索引的插入变得完全随机这是最坏的情况使得数据没有任何聚集特性。 为了演示这一点我们做如下两个基准测试。第一个使用整数ID插入userinfo表: 第二个例子是userinfo_uuid表。除了主键改为UUID其余和前面的userinfo表完全相同。 我们测试了这两个表的设计。首先我们在一个有足够内存容纳索引的服务器上向这两个表各插入100万条记录。然后向这两个表继续插入300万条记录使索引的大小超过服务器的内存容量。表5-1对测试结果做了比较。 注意到向UUID主键插入行不仅花费的时间更长而且索引占用的空间也更大。这一方面是由于主键字段更长﹔另一方面毫无疑问是由于页分裂和碎片导致的。 为了明白为什么会这样来看看往第一个表中插入数据时索引发生了什么变化。图5-10显示了插满一个页面后继续插入相邻的下一个页面的场景。 如图5-10所示因为主键的值是顺序的所以InnoDB把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时(InnoDB默认的最大填充因子是页大小的15/16留出部分空间用于以后修改)下一条记录就会写入新的页中。一旦数据按照这种顺序的方式加载,主键页就会近似于被顺序的记录填满,这也正是所期望的结果(然而二级索引页可能是不一样的)。 对比一下向第二个使用了UUID聚簇索引的表插入数据看看有什么不同图5-11显示了结果。 因为新行的主键值不一定比之前插入的大所以InnoDB无法简单地总是把新行插入到索引的最后而是需要为新的行寻找合适的位置--—通常是已有数据的中间位置——并且分配空间。这会增加很多的额外工作并导致数据分布不够优化。下面是总结的一些缺点: ·写入的目标页可能已经刷到磁盘上并从缓存中移除或者是还没有被加载到缓存中InnoDB在插入之前不得不先找到并从磁盘读取目标页到内存中。这将导致大量的随机I/O。
·因为写入是乱序的InnoDB不得不频繁地做页分裂操作以便为新的行分配空间。页分裂会导致移动大量数据一次插入最少需要修改三个页而不是一个页。
·由于频繁的页分裂页会变得稀疏并被不规则地填充所以最终数据会有碎片。 在把这些随机值载入到聚簇索引以后也许需要做一次OPTIMIZE TABLE来重建表并优化页的填充。 从这个案例可以看出使用InnoDB时应该尽可能地按主键顺序插入数据并且尽可能地使用单调增加的聚簇键的值来插入新行。 5.3.6 覆盖索引
通常大家都会根据查询的WHERE条件来创建合适的索引不过这只是索引优化的一个方面。设计优秀的索引应该考虑到整个查询而不单单是WHERE条件部分。索引确实是一种查找数据的高效方式但是MySQL也可以使用索引来直接获取列的数据这样就不再需要读取数据行。如果索引的叶子节点中已经包含要查询的数据那么还有什么必要再回表查询呢﹖如果一个索引包含(或者说覆盖所有需要查询的字段的值我们就称之为“覆盖索引”。 覆盖索引是非常有用的工具能够极大地提高性能。考虑一下如果查询只需要扫描索引而无须回表会带来多少好处: ·索引条目通常远小于数据行大小所以如果只需要读取索引那 MySQL就会极大地减少数据访问量。这对缓存的负载非常重要因为这种情况下响应时间大部分花费在数据拷贝上。覆盖索引对于I/O密集型的应用也有帮助因为索引比数据更小更容易全部放入内存中这对于MyISAM尤其正确因为MyISAM能压缩索引以变得更小)。
·因为索引是按照列值顺序存储的至少在单个页内是如此)所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少得多。对于某些存储引擎例如MyISAM和Percona XtraDB甚至可以通过OPTIMIZE命令使得索引完全顺序排列这让简单的范围查询能使用完全顺序的索引访问。
·一些存储引擎如MyISAM在内存中只缓存索引数据则依赖于操作系统来缓存因此要访问数据需要一次系统调用。这可能会导致严重的性能问题尤其是那些系统调用占了数据访问中的最大开销的场景。
·由于InnoDB的聚簇索引覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值所以如果二级主键能够覆盖查询则可以避免对主键索引的二次查询。
·在所有这些场景中在索引中满足查询的成本一般比查询行要小得多。 不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值而哈希索引、空间索引和全文索引等都不存储索引列的值所以MySQL 只能使用B-Tree索引做覆盖索引。另外不同的存储引擎实现覆盖索引的方式也不同而且不是所有的引擎都支持覆盖索引(在写作本书时Memory存储引擎就不支持覆盖索引)。 当发起一个被索引覆盖的查询也叫做索引覆盖查询时在EXPLAIN的Extra列可以看到“Using index”的信息生12。例如表sakila.inventory有一个多列索引(store_id,film_id)。MySQL 如果只需访问这两列就可以使用这个索引做覆盖索引如下所示: 索引覆盖查询还有很多陷阱可能会导致无法实现优化。MySQL查询优化器会在执行查询前判断是否有一个索引能进行覆盖。假设索引覆盖了WHERE条件中的字段但不是整个查询涉及的字段。如果条件为假(false)MySQL 5.5和更早的版本也总是会回表获取数据行尽管并不需要这一行且最终会被过滤掉。 来看看为什么会发生这样的情况以及如何重写查询以解决该问题。从下面的查询开始: 这里索引无法覆盖该查询有两个原因: ·没有任何索引能够覆盖这个查询。因为查询从表中选择了所有的列而没有任何索引覆盖了所有的列。不过理论上 MySQL还有一个捷径可以利用:wHERE条件中的列是有索引可以覆盖的因此MySQL可以使用该索引找到对应的actor并检查title是否匹配过滤之后再读取需要的数据行。
·MySQL不能在索引中执行LIKE操作。这是底层存储引擎API的限制MySQL 5.5和更早的版本中只允许在索引中做简单比较操作例如等于、不等于以及大于)。MySQL能在索引中做最左前缀匹配的LIKE比较因为该操作可以转换为简单的比较操作但是如果是通配符开头的LIKE查询存储引擎就无法做比较匹配。这种情况下MySQL服务器只能提取数据行的值而不是索引值来做比较。 也有办法可以解决上面说的两个问题需要重写查询并巧妙地设计索引。先将索引扩展至覆盖三个数据列(artist, title, prod_id)然后按如下方式重写查询: 我们把这种方式叫做延迟关联(deferred join)因为延迟了对列的访问。在查询的第一阶段MySQL可以使用覆盖索引在FROM子句的子查询中找到匹配的 prod_id然后根据这些prod_id值在外层查询匹配获取需要的所有列值。虽然无法使用索引覆盖整个查询但总算比完全无法利用索引覆盖的好。 这样优化的效果取决于WHERE条件匹配返回的行数。假设这个products表有100万行我们来看一下上面两个查询在三个不同的数据集上的表现每个数据集都包含100万行: 1第一个数据集Sean Carrey出演了30 000部作品其中有20 000部的标题中包含
了Apollo。
2第二个数据集Sean Carrey出演了30 000部作品其中40部的标题中包含了
Apollo。
3第三个数据集Sean Carrey出演了50部作品其中10部的标题中包含了Apollo。
使用上面的三种数据集来测试两种不同的查询得到的结果如表5-2所示。 下面是对结果的分析: ·在示例1中查询返回了一个很大的结果集因此看不到优化的效果。大部分时间都花在读取和发送数据上了。
·在示例2中经过索引过滤尤其是第二个条件过滤后只返回了很少的结果集优化的效果非常明显﹔在这个数据集上性能提高了5倍,优化后的查询的效率主要得益于只需要读取40行完整数据行而不是原查询中需要的30 000行。
·在示例3中显示了子查询效率反而下降的情况。因为索引过滤时符合第一个条件的结果集已经很小所以子查询带来的成本反而比从表中直接提取完整行更高。 在大多数存储引擎中覆盖索引只能覆盖那些只访问索引中部分列的查询。不过可以更进一步优化InnoDB。回想--下InnoDB的二级索引的叶子节点都包含了主键的值这意味着InnoDB 的二级索引可以有效地利用这些“额外”的主键列来覆盖查询。 例如sakila.actor使用InnoDB存储引擎并在last_name字段有二级索引虽然该索引的列不包括主键actor_id但也能够用于对actor_id做覆盖查询: 5.3.7使用索引扫描来做排序
MySQL有两种方式可以生成有序的结果:通过排序操作﹔或者按索引顺序扫描施13﹔如果EXPLAIN出来的type列的值为“index”则说明MySQL使用了索引扫描来做排序不要和 Extra列的“Using index”搞混淆了)。 扫描索引本身是很快的因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列那就不得不每扫描一条索引记录就都回表查询一次对应的行。这基本上都是随机IO因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢尤其是在IO密集型的工作负载时。 MySQL可以使用同一个索引既满足排序又用于查找行。因此如果可能设计索引时应该尽可能地同时满足这两种任务这样是最好的。 只有当索引的列顺序和ORDER BY子句的顺序完全一致并且所有列的排序方向倒序或正序都一样时MySQL才能够使用索引来对结果做排序生1。如果查询需要关联多张表则只有当ORDER BY子句引用的字段全部为第一个表时才能使用索引做排序。ORDER BY子句和查找型查询的限制是一样的:需要满足索引的最左前缀的要求﹔否则MySQL都需要执行排序操作而无法利用索引排序。 有一种情况下ORDER BY子句可以不满足索引的最左前缀的要求就是前导列为常量的时候。如果 WHERE子句或者J0IN子句中对这些列指定了常量,就可以“弥补”索引的不足。 例如Sakila示例数据库的表rental在列rental_date,inventory_id, customer_id)上有名为rental_date的索引。 MySQL可以使用rental_date索引为下面的查询做排序从EXPLAIN中可以看到没有出现文件排序(filesort操作: 即使ORDER BY子句不满足索引的最左前缀的要求也可以用于查询排序这是因为索引的第一列被指定为一个常数。 还有更多可以使用索引做排序的查询示例。下面这个查询可以利用索引排序是因为查询为索引的第一列提供了常量条件而使用第二列进行排序将两列组合在一起就形成了索引的最左前缀: ... WHERE rental_date - 2005-05-25’ORDER BY inventory_id DESc; 下面这个例子理论上是可以使用索引进行关联排序的但由于优化器在优化时将film_actor表当作关联的第二张表所以实际上无法使用索引: 使用索引做排序的一个最重要的用法是当查询同时有ORDER BY和LIMIT子句的时候。后面我们会具体介绍这些内容。 5.3.8 压缩前缀压缩索引
MyISAM使用前缀压缩来减少索引的大小从而让更多的索引可以放入内存中这在某些情况下能极大地提高性能。默认只压缩字符串但通过参数设置也可以对整数做压缩。 MyISAM压缩每个索引块的方法是先完全保存索引块中的第一个值然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分把这部分存储起来即可。例如索引块中的第一个值是“perform”第二个值是“performance”那么第二个值的前缀压缩后存储的是类似“7,ance”这样的形式。MyISAM对行指针也采用类似的前缀压缩方式。 压缩块使用更少的空间代价是某些操作可能更慢。因为每个值的压缩前缀都依赖前面的值所以MyISAM查找时无法在索引块使用二分查找而只能从头开始扫描。正序的扫描速度还不错但是如果是倒序扫描-例如ORDER BY DESC一--就不是很好了。所有在块中查找某一行的操作平均都需要扫描半个索引块。 测试表明对于CPU密集型应用因为扫描需要随机查找压缩索引使得MyISAM在索引查找上要慢好几倍。压缩索引的倒序扫描就更慢了。压缩索引需要在CPU内存资源与磁盘之间做权衡。压缩索引可能只需要十分之一大小的磁盘空间如果是I/О密集型应用对某些查询带来的好处会比成本多很多。 可以在CREATE TABLE语句中指定PACK_KEYS参数来控制索引压缩的方式。 5.3.9冗余和重复索引
MySQL 允许在相同列上创建多个索引无论是有意的还是无意的。MySQL需要单独维护重复的索引并且优化器在优化查询的时候也需要逐个地进行考虑这会影响性能。 重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引发现以后也应该立即移除。 有时会在不经意间创建了重复索引例如下面的代码: 一个经验不足的用户可能是想创建一个主键先加上唯一限制然后再加上索引以供查询使用。事实上MySQL的唯一限制和主键限制都是通过索引实现的因此上面的写法实际上在相同的列上创建了三个重复的索引。通常并没有理由这样做除非是在同一列上创建不同类型的索引来满足不同的查询需求. 冗余索引和重复索引有一些不同。如果创建了索引(A B)再创建索引(A)就是冗余索引因为这只是前一个索引的前缀索引。因此索引(AB)也可以当作索引(A)来使用这种冗余只是对B-Tree索引来说的)。但是如果再创建索引(BA)则不是冗余索引索引(B)也不是因为B不是索引(AB)的最左前缀列。另外其他不同类型的索引例如哈希索引或者全文索引)也不会是B-Tree索引的冗余索引而无论覆盖的索引列是什么。 冗余索引通常发生在为表添加新索引的时候。例如有人可能会增加一个新的索引(A B)而不是扩展已有的索引(A)。还有一种情况是将一个索引扩展为(AID)其中ID是主键对于InnoDB来说主键列已经包含在二级索引中了所以这也是冗余的。 大多数情况下都不需要冗余索引应该尽量扩展已有的索引而不是创建新索引。但也有时候出于性能方面的考虑需要冗余索引因为扩展已有的索引会导致其变得太大从而影响其他使用该索引的查询的性能。 例如如果在整数列上有一个索引现在需要额外增加一个很长的VARCHAR列来扩展该索引那性能可能会急剧下降。特别是有查询把这个索引当作覆盖索引或者这是MyISAM表并且有很多范围查询由于MyISAM的前缀压缩的时候。 考虑一下前面“在InnoDB中按主键顺序插入行”一节提到的userinfo表。这个表有1000 000行对每个state_id值大概有20 000条记录。在state_id列有一个索引对下面的查询有用假设查询名为Q1 : 索引扩展后Q2运行得更快了但是Q1却变慢了。如果我们想让两个查询都变得更快就需要两个索引尽管这样一来原来的单列索引是冗余的了。表5-3显示这两个查询在不同的索引策略下的详细结果分别使用MyISAM和InnoDB存储引擎。注意到只有state_id_2索引时InnoDB引擎上的查询Q1的性能下降并不明显这是因为InnoDB没有使用索引压缩。 可以看到表中的索引越多插入速度会越慢。一般来说增加新索引将会导致INSERT、UPDATE、DELETE等操作的速度变慢特别是当新增索引后导致达到了内存瓶颈的时候。 解决冗余索引和重复索引的方法很简单删除这些索引就可以但首先要做的是找出这样的索引。可以通过写一些复杂的访问INFORMATION_SCHEMA表的查询来找不过还有两个更简单的方法。可使用Shlomi Noach的common_schema中的一些视图来定位common_schema是一系列可以安装到服务器上的常用的存储和视图http:l/code.google.com/plcommon-schema/)。这比自己编写查询要快而且简单。另外也可以使用PerconaToolkit中的pt-duplicate-key-checker该工具通过分析表结构来找出冗余和重复的索引。对于大型服务器来说使用外部的工具可能更合适些﹔如果服务器上有大量的数据或者大量的表查询INFORMATION_SCHEM表可能会导致性能问题。 在决定哪些索引可以被删除的时候要非常小心。回忆一下在前面的InnoDB的示例表中因为二级索引的叶子节点包含了主键值所以在列(A)上的索引就相当于在(AID)上的索引。如果有像WHERE A 5 ORDER BY ID这样的查询这个索引会很有作用。但如果将索引扩展为(A B)则实际上就变成了(A, B,ID)那么上面查询的ORDER BY子句就无法使用该索引做排序而只能用文件排序了。所以建议使用Percona 工具箱中的pt-upgrade工具来仔细检查计划中的索引变更。 5.3.10未使用的索引
除了冗余索引和重复索引可能还会有一些服务器永远不用的索引。这样的索引完全是累赘建议考虑删除性18。有两个工具可以帮助定位未使用的索引。最简单有效的办法是在Percona Server或者MariaDB中先打开userstates服务器变量默认是关闭的)然后让服务器正常运行一段时间再通过查询INFORMATION_SCHEMA.INDEX_STATISTICS就能查到每个索引的使用频率。 另外还可以使用Percona Toolkit中的pt-index-usage该工具可以读取查询日志并对日志中的每条查询进行EXPLAIN操作然后打印出关于索引和查询的报告。这个工具不仅可以找出哪些索引是未使用的还可以了解查询的执行计划——例如在某些情况有些类似的查询的执行方式不一样这可以帮助你定位到那些偶尔服务质量差的查询优化它们以得到一致的性能表现。该工具也可以将结果写入到MySQL 的表中方便查询结果。 5.3.11索引和锁
索引可以让查询锁定更少的行。如果你的查询从不访问那些不需要的行那么就会锁定更少的行从两个方面来看这对性能都有好处。首先虽然InnoDB的行锁效率很高内存使用也很少但是锁定行的时候仍然会带来额外开销﹔其次锁定超过需要的行会增加锁争用并减少并发性。 InnoDB只有在访问行的时候才会对其加锁而索引能够减少InnoDB访问的行数从而减少锁的数量。但这只有当InnoDB在存储引擎层能够过滤掉所有不需要的行时才有效。如果索引无法过滤掉无效的行那么在InnoDB检索到数据并返回给服务器层以后MySQL服务器才能应用WHERE子句些1。这时已经无法避免锁定行了:InnoDB已经锁住了这些行到适当的时候才释放。在 MySQL 5.1和更新的版本中InnoDB可以在服务器端过滤掉行后就释放锁但是在早期的MySQL版本中InnoDB 只有在事务提交后才能释放锁。 通过下面的例子再次使用数据库Sakila很好地解释了这些情况: 这条查询仅仅会返回2~4之间的行但是实际上获取了1~4之间的行的排他锁。InnoDB会锁住第1行这是因为MySQL为该查询选择的执行计划是索引范围扫描: 换句话说底层存储引擎的操作是“从索引的开头开始获取满足条件actor_id 5的记录”服务器并没有告诉InnoDB可以过滤第1行的 WHERE条件。注意到EXPLAIN的Extra列出现了“Using where”这表示MySQL服务器将存储引擎返回行以后再应用WHERE过滤条件。 下面的第二个查询就能证明第1行确实已经被锁定尽管第一个查询的结果中并没有这个第1行。保持第一个连接打开然后开启第二个连接并执行如下查询: 这个查询将会挂起直到第一个事务释放第1行的锁。这个行为对于基于语句的复制(将在第10章讨论的正常运行来说是必要的。 就像这个例子显示的即使使用了索引InnoDB也可能锁住一些不需要的数据。如果不能使用索引查找和锁定行的话问题可能会更糟糕MySQL会做全表扫描并锁住所有的行而不管是不是需要。 关于InnoDB、索引和锁有一些很少有人知道的细节 :InnoDB在二级索引上使用共享(读锁但访问主键索引需要排他写锁。这消除了使用覆盖索引的可能性并且使得SELECT FOR UPDATE比 LOCK IN SHARE MODE或非锁定查询要慢很多。 5.4索引案例学习
理解索引最好的办法是结合示例所以这里准备了一个索引的案例。 假设要设计一个在线约会网站用户信息表有很多列包括国家、地区、城市、性别、眼睛颜色等等。网站必须支持上面这些特征的各种组合来搜索用户还必须允许根据用户的最后在线时间、其他会员对用户的评分等对用户进行排序并对结果进行限制。如何设计索引满足上面的复杂需求呢﹖ 出人意料的是第一件需要考虑的事情是需要使用索引来排序还是先检索数据再排序。使用索引排序会严格限制索引和查询的设计。例如如果希望使用索引做根据其他会员对用户的评分的排序则wHERE条件中的age BETWEEN 18 AND 25就无法使用索引。如果MySQL使用某个索引进行范围查询也就无法再使用另一个索引(或者是该索引的后续字段进行排序了。如果这是很常见的WHERE条件那么我们当然就会认为很多查询需要做排序操作例如文件排序filesort)。
5.4.1支持多种过滤条件
现在需要看看哪些列拥有很多不同的取值哪些列在WHERE子句中出现得最频繁。在有更多不同值的列上创建索引的选择性会更好。一般来说这样做都是对的因为可以让MySQL更有效地过滤掉不需要的行。 country列的选择性通常不高但可能很多查询都会用到。sex列的选择性肯定很低但也会在很多查询中用到。所以考虑到使用的频率还是建议在创建不同组合索引的时候将(sex, country)列作为前缀。 但根据传统的经验不是说不应该在选择性低的列上创建索引的吗?那为什么这里要将两个选择性都很低的字段作为索引的前缀列﹖我们的脑子坏了? 我们的脑子当然没坏。这么做有两个理由:第一点如前所述几乎所有的查询都会用到sex列。前面曾提到几乎每一个查询都会用到sex列甚至会把网站设计成每次都只能按某一种性别搜索用户。更重要的一点是索引中加上这一列也没有坏处即使查询没有使用sex列也可以通过下面的“诀窍”绕过。 这个“诀窍”就是:如果某个查询不限制性别那么可以通过在查询条件中新增ANDSEX IN( m, f)来让 MySQL选择该索引。这样写并不会过滤任何行和没有这个条件时返回的结果相同。但是必须加上这个列的条件MySQL才能够匹配索引的最左前缀。这个“诀窍”在这类场景中非常有效但如果列有太多不同的值就会让IN()列表太长这样做就不行了。 这个案例显示了一个基本原则:考虑表上所有的选项。当设计索引时不要只为现有的查询考虑需要哪些索引还需要考虑对查询进行优化。如果发现某些查询需要创建新索引但是这个索引又会降低另一些查询的效率那么应该想一下是否能优化原来的查询。应该同时优化查询和索引以找到最佳的平衡而不是闭门造车去设计最完美的索引。 接下来需要考虑其他常见WHERE条件的组合并需要了解哪些组合在没有合适索引的情况下会很慢。(sex,country, age)上的索引就是一个很明显的选择另外很有可能还需要(sex, country, region, age)和( sex, country, region, city, age)这样的组合索引。 这样就会需要大量的索引。如果想尽可能重用索引而不是建立大量的组合索引可以使用前面提到的IN()的技巧来避免同时需要(sex, country, age)和(sex, country,region, age)的索引。如果没有指定这个字段搜索就需要定义一个全部国家列表或者国家的全部地区列表来确保索引前缀有同样的约束组合所有国家、地区、性别将会是一个非常大的条件)。 这些索引将满足大部分最常见的搜索查询但是如何为一些生僻的搜索条件比如has_pictures、eye_color、hair_color和education)来设计索引呢﹖这些列的选择性高、使用也不频繁可以选择忽略它们让 MySQL多扫描一些额外的行即可。另一-个可选的方法是在age列的前面加上这些列在查询时使用前面提到过的IN()技术来处理搜索时没有指定这些列的场景。 你可能已经注意到了我们一直将age列放在索引的最后面。age列有什么特殊的地方吗?为什么要放在索引的最后﹖我们总是尽可能让MySQL使用更多的索引列因为查询只能使用索引的最左前缀直到遇到第一个范围条件列。前面提到的列在WHERE子句中都是等于条件,但是age列则多半是范围查询例如查找年龄在1825岁之间的人)。 当然也可以使用IN()来代替范围查询例如年龄条件改写为IN(18192021,22232425),但不是所有的范围查询都可以转换。这里描述的基本原则是尽可能将需要做范围查询的列放到索引的后面以便优化器能使用尽可能多的索引列。 前面提到可以在索引中加入更多的列并通过IN(〉的方式覆盖那些不在MERE子句中的列。但这种技巧也不能滥用否则可能会带来麻烦。因为每额外增加一个IN()条件优化器需要做的组合都将以指数形式增加最终可能会极大地降低查询性能。考虑下面的WHERE子句: 前面提到可以在索引中加入更多的列并通过IN(〉的方式覆盖那些不在MHERE子句中的列。但这种技巧也不能滥用否则可能会带来麻烦。因为每额外增加一个IN()条件优化器需要做的组合都将以指数形式增加最终可能会极大地降低查询性能。考虑下面的WHERE子句: 优化器则会转化成4×3×2. 24种组合执行计划需要检查WHERE子句中所有的24种组合。对于MySQL来说24种组合并不是很夸张但如果组合数达到上千个则需要特别小心。老版本的MySQL在IN()组合条件过多的时候会有很多问题。查询优化可能需要花很多时间并消耗大量的内存。新版本的MySQL在组合数超过一定数量后就不再进行执行计划评估了这可能会导致MySQL不能很好地利用索引。 组合。对于MySQL来说24种组合并不是很夸张但如果组合数达到上千个则需要特别小心。老版本的MySQL在IN()组合条件过多的时候会有很多问题。查询优化可能需要花很多时间并消耗大量的内存。新版本的MySQL在组合数超过一定数量后就不再进行执行计划评估了这可能会导致MySQL不能很好地利用索引。 5.4.2 避免多个范围条件
假设我们有一个last_online列并希望通过下面的查询显示在过去几周上线过的用户: WHEREeye_color IN( brown , blue , hazel)
AND hair_color IN(black, red , blonde , brown )AND sex
IN(M , F)
AND last_online DATE_sUB(NOw()INTERVAL 7 DAY)AND age
BETWEEN 18 AND 25 这个查询有一个问题:它有两个范围条件last_online列和 age列MySQL可以使用last_online列索引或者age列索引但无法同时使用它们。 如果条件中只有last_online而没有age那么我们可能考虑在索引的后面加上last_online列。这里考虑如果我们无法把age字段转换为一个IN()的列表并且仍要求对于同时有last_online和 age这两个维度的范围查询的速度很快那该怎么办﹖答案是很遗憾没有一个直接的办法能够解决这个问题。但是我们能够将其中的一个范围查询转换为一个简单的等值比较。为了实现这一点我们需要事先计算好一个active列这个字段由定时任务来维护。当用户每次登录时将对应值设置为1并且将过去连续七天未曾登录的用户的值设置为0。. 这个方法可以让MySQL使用active, sex, country, age)索引。active列并不是完全精确的但是对于这类查询来说对精度的要求也没有那么高。如果需要精确数据可以把last_online列放到MHERE子句但不加入到索引中。这和本章前面通过计算URL哈希值来实现URL的快速查找类似。所以这个查询条件没法使用任何索引但因为这个条件的过滤性不高即使在索引中加入该列也没有太大的帮助。换个角度来说缺乏合适的索引对该查询的影响也不明显。 到目前为止我们可以看到:如果用户希望同时看到活跃和不活跃的用户可以在查询中使用IN()列表。我们已经加入了很多这样的列表但另外一个可选的方案就只能是为不同的组合列创建单独的索引。至少需要建立如下的索引 :(active,sex, country,age)(active, country, age)(sex, country, age)和 (country, age)。这些索引对某个具体的查询来说可能都是更优化的但是考虑到索引的维护和额外的空间占用的代价这个可选方案就不是一个好策略了。 在这个案例中优化器的特性是影响索引策略的一个很重要的因素。如果未来版本的MySQL能够实现松散索引扫描就能在一个索引上使用多个范围条件那也就不需要为上面考虑的这类查询使用IN()列表了。 5.4.3优化排序
在这个学习案例中最后要介绍的是排序。使用文件排序对小数据集是很快的但如果一个查询匹配的结果有上百万行的话会怎样?例如如果WHERE子句只有sex列如何排序? 对于那些选择性非常低的列可以增加一些特殊的索引来做排序。例如可以创建sex,rating)索引用于下面的查询: mysql SELECT cols FROM profiles MHERE sexMORDER BY rating LIAIT 10; 这个查询同时使用了ORDER BY和LIMIT如果没有索引的话会很慢。 即使有索引如果用户界面上需要翻页并且翻页翻到比较靠后时查询也可能非常慢。下面这个查询就通过ORDER BY和 LIMIT偏移量的组合翻页到很后面的时候: mysq1 SELECT cols FROM profiles wHERE sexN’ORDER BY rating LIMIT 10000010; 无论如何创建索引这种查询都是个严重的问题。因为随着偏移量的增加MySQL需要花费大量的时间来扫描需要丢弃的数据。反范式化、预先计算和缓存可能是解决这类查询的仅有策略。一个更好的办法是限制用户能够翻页的数量实际上这对用户体验的影响不大因为用户很少会真正在乎搜索结果的第10 000页。 优化这类索引的另一个比较好的策略是使用延迟关联通过使用覆盖索引查询返回需要的主键再根据这些主键关联原表获得需要的行。这可以减少MySQL扫描那些需要丢弃的行数。下面这个查询显示了如何高效地使用(sex, rating索引进行排序和分页: 5.5维护索引和表
即使用正确的类型创建了表并加上了合适的索引工作也没有结束﹔还需要维护表和索引来确保它们都正常工作。维护表有三个主要的目的:找到并修复损坏的表维护准确的索引统计信息减少碎片。 5.5.1找到并修复损坏的表
表损坏(corruption是很糟糕的事情。对于MyISAM存储引擎表损坏通常是系统崩溃导致的。其他的引擎也会由于硬件问题、MySQL本身的缺陷或者操作系统的问题导致索引损坏。 损坏的索引会导致查询返回错误的结果或者莫须有的主键冲突等问题严重时甚至还会导致数据库的崩溃。如果你遇到了古怪的问题——例如一些不应该发生的错误——-可以尝试运行CHECK TABLE来检查是否发生了表损坏注意有些存储引擎不支持该命令﹔而有些引擎则支持以不同的选项来控制完全检查表的方式)。CHECK TABLE通常能够找出大多数的表和索引的错误。 可以使用 REPAIR TABLE命令来修复损坏的表但同样不是所有的存储引擎都支持该命令。如果存储引擎不支持也可通过一个不做任何操作(no-op的 ALTER操作来重建表例如修改表的存储引擎为当前的引擎。下面是一个针对InnoDB表的例子: mysql ALTER TABLE innodb_tb1 ENGINEINNODB; 此外也可以使用一些存储引擎相关的离线工具例如myisamchky或者将数据导出一份然后再重新导入。不过如果损坏的是系统区域或者是表的“行数据”区域而不是索引那么上面的办法就没有用了。在这种情况下可以从备份中恢复表或者尝试从损坏的数据文件中尽可能地恢复数据。 如果InnoDB引擎的表出现了损坏那么一定是发生了严重的错误需要立刻调查一下原因。InnoDB一般不会出现损坏。InnoDB的设计保证了它并不容易被损坏。如果发生损坏一般要么是数据库的硬件问题例如内存或者磁盘问题有可能)要么是由于数据库管理员的错误例如在MySQL 外部操作了数据文件有可能)抑或是InnoDB本身的缺陷不太可能)。常见的类似错误通常是由于尝试使用rsync备份InnoDB导致的。不存在什么查询能够让InnoDB表损坏也不用担心暗处有“陷阱”。如果某条查询导致InnoDB 数据的损坏那一定是遇到了bug而不是查询的问题。 如果遇到数据损坏最重要的是找出是什么导致了损坏而不只是简单地修复否则很有可能还会不断地损坏。可以通过设置innodb_force_recovery参数进入InnoDB的强制恢复模式来修复数据更多细节可以参考MySQL手册。另外还可以使用开源的InnoDB数据恢复工具箱(InnoDB Data Recovery Toolkit)直接从 InnoDB数据文件恢复出数据下载地址:http://www.percona.com/software/mysql-innodb-data-recovery-tools/)。 5.5.2更新索引统计信息
MySQL的查询优化器会通过两个API来了解存储引擎的索引值的分布信息以决定如何使用索引。第一个API是 records_in_range()通过向存储引擎传入两个边界值获取在这个范围大概有多少条记录。对于某些存储引擎该接口返回精确值例如MyISAM﹔但对于另一些存储引擎则是一个估算值例如. InnoDB。 第二个API是info()该接口返回各种类型的数据包括索引的基数每个键值有多少条记录)。 如果存储引擎向优化器提供的扫描行数信息是不准确的数据或者执行计划本身太复杂以致无法准确地获取各个阶段匹配的行数那么优化器会使用索引统计信息来估算扫描行数。MySQL优化器使用的是基于成本的模型而衡量成本的主要指标就是一个查询需要扫描多少行。如果表没有统计信息或者统计信息不准确优化器就很有可能做出 错误的决定。可以通过运行ANALYZE TABLE来重新生成统计信息解决这个问题。 每种存储引擎实现索引统计信息的方式不同所以需要进行ANALYZE TABLE的频率也因不同的引擎而不同每次运行的成本也不同: ·Memory 引擎根本不存储索引统计信息。
·MyISAM将索引统计信息存储在磁盘中ANALYZE TABLE需要进行一次全索引扫描来计算索引基数。在整个过程中需要锁表。
·直到MySQL 5.5版本InnoDB也不在磁盘存储索引统计信息而是通过随机的索引访问进行评估并将其存储在内存中。 可以使用SHOw INDEX FROM命令来查看索引的基数Cardinality)。例如: 这个命令输出了很多关于索引的信息在 MysQL手册中对上面每个字段的含义都有详细的解释。这里需要特别提及的是索引列的基数(Cardinality)其显示了存储引擎估算索引列有多少个不同的取值。在MySQL 5.0和更新的版本中还可以通过INFORMATION_SCHEMA.STATISTICS表很方便地查询到这些信息。例如基于工NFORMATION_SCHEMA的表可以编写一个查询给出当前选择性比较低的索引。需要注意的是如果服务器上的库表非常多则从这里获取元数据的速度可能会非常慢而且会给MySQL带来额外的压力。 InnoDB的统计信息值得深入研究。InnoDB引擎通过抽样的方式来计算统计信息首先随机地读取少量的索引页面然后以此为样本计算索引的统计信息。在老的
InnoDB版本中样本页面数是8新版本的InnoDB可以通过参数innodb_stats_sample_pages来设置样本页的数量。设置更大的值理论上来说可以帮助生成更准确的索引信息特别是对于某些超大的数据表来说但具体设置多大合适依赖于具体的环境。 InnoDB会在表首次打开或者执行ANALYZETABLE抑或表的大小发生非常大的变化(大小变化超过十六分之一或者新插入了20亿行都会触发的时候计算索引的统计信息。 InnoDB在打开某些INFORMATION_SCHEMA表或者使用SHO TABLE STATUS和 SHOWINDEX抑或在MySQL客户端开启自动补全功能的时候都会触发索引统计信息的更新。如果服务器上有大量的数据这可能就是个很严重的问题尤其是当I/O比较慢的时候。客户端或者监控程序触发索引信息采样更新时可能会导致大量的锁并给服务器带来很多的额外压力这会让用户因为启动时间漫长而沮丧。只要SHOW INDEX查看索引统计信息就一定会触发统计信息的更新。可以关闭innodb_stats_on_metadata参数来避免上面提到的问题。 如果使用Percona版本使用的就是XtraDB引擎而不是原生的InnoDB引擎那么可以通过innodb_stats_auto_update参数来禁止通过自动采样的方式更新索引统计信息这时需要手动执行ANALYZE TABLE命令来更新统计信息。如果某些查询执行计划很不稳定的话可以用该办法固化查询计划。我们当初引入这个参数也正是为了解决一些客户的这种问题。 如果想要更稳定的执行计划并在系统重启后更快地生成这些统计信息那么可以使用系统表来持久化这些索引统计信息。甚至还可以在不同的机器间迁移索引统计信息这样新环境启动时就无须再收集这些数据。在Percona 5.1版本和官方的5.6版本都已经加入这个特性。在Percona版本中通过innodb_use_sys_stats_table参数可以启用该特性官方5.6版本则通过innodb_analyze_is_persistent参数控制。 一旦关闭索引统计信息的自动更新那么就需要周期性地使用ANALYZE TABLE来手动更新。否则索引统计信息就会永远不变。如果数据分布发生大的变化可能会出现一些很糟糕的执行计划。 5.5.3 减少索引和数据的碎片
B-Tree索引可能会碎片化这会降低查询的效率。碎片化的索引可能会以很差或者无序的方式存储在磁盘上。 根据设计B-Tree需要随机磁盘访问才能定位到叶子页所以随机访问是不可避免的。然而如果叶子页在物理分布上是顺序且紧密的那么查询的性能就会更好。否则对于范围查询、索引覆盖扫描等操作来说速度可能会降低很多倍﹔对于索引覆盖扫描这一点更加明显。 表的数据存储也可能碎片化。然而数据存储的碎片化比索引更加复杂。有三种类型的数据碎片。 行碎片(Row fragmentation)
这种碎片指的是数据行被存储为多个地方的多个片段中。即使查询只从索引中访问一行记录行碎片也会导致性能下降。 行间碎片(Intra-row fragmentation)
行间碎片是指逻辑上顺序的页或者行在磁盘上不是顺序存储的。行间碎片对诸如全表扫描和聚簇索引扫描之类的操作有很大的影响因为这些操作原本能够从磁盘上顺序存储的数据中获益。 剩余空间碎片(Free space fragmentation)
剩余空间碎片是指数据页中有大量的空余空间。这会导致服务器读取大量不需要的数据从而造成浪费。 对于MyISAM 表这三类碎片化都可能发生。但InnoDB不会出现短小的行碎片﹔InnoDB会移动短小的行并重写到一个片段中。 可以通过执行OPTIMIZE TABLE或者导出再导入的方式来重新整理数据。这对多数存储引擎都是有效的。对于一些存储引擎如MyISAM可以通过排序算法重建索引的方式来消除碎片。老版本的InnoDB没有什么消除碎片化的方法。不过最新版本InnoDB新增了“在线”添加和删除索引的功能可以通过先删除然后再重新创建索引的方式来消除索引的碎片化。 对于那些不支持OPTIMIZETABLE的存储引擎可以通过一个不做任何操作(no-op)的ALTER TABLE操作来重建表。只需要将表的存储引擎修改为当前的引擎即可: mysql ALTER TABLE table ENGINEengine; 对于开启了expand_fast_index_creation参数的Percona Server按这种方式重建表则会同时消除表和索引的碎片化。但对于标准版本的MySQL则只会消除表实际上是聚簇索引的碎片化。可用先删除所有索引然后重建表最后重新创建索引的方式模拟Percona Server的这个功能。 应该通过一些实际测量而不是随意假设来确定是否需要消除索引和表的碎片化。Percona的XtraBackup有个--stats参数以非备份的方式运行而只是打印索引和表的统计情况包括页中的数据量和空余空间。这可以用来确定数据的碎片化程度。另外也要考虑数据是否已经达到稳定状态如果你进行碎片整理将数据压缩到一起可能反而会导致后续的更新操作触发一系列的页分裂和重组这会对性能造成不良的影响直到数据再次达到新的稳定状态)。 5.6总结
通过本章可以看到索引是一个非常复杂的话题!MySQL和存储引擎访问数据的方式加上索引的特性使得索引成为一个影响数据访问的有力而灵活的工作无论数据是在磁盘中还是在内存中)。 在MySQL中大多数情况下都会使用B-Tree索引。其他类型的索引大多只适用于特殊的目的。如果在合适的场景中使用索引将大大提高查询的响应时间。本章将不再介绍更多这方面的内容了最后值得总的回顾一下这些特性以及如何使用B-Tree索引。 在选择索引和编写利用这些索引的查询时有如下三个原则始终需要记住: 1单行访问是很慢的。特别是在机械硬盘存储中(SSD的随机I/О要快很多不过这一点仍然成立)。如果服务器从存储中读取一个数据块只是为了获取其中一行那么就浪费了很多工作。最好读取的块中能包含尽可能多所需要的行。使用索引可以创建位置引用以提升效率。
2按顺序访问范围数据是很快的,这有两个原因。第一,顺序I/O不需要多次磁盘寻道所以比随机I/О要快很多特别是对机械硬盘)。第二如果服务器能够按需要顺序读取数据那么就不再需要额外的排序操作并且 GROUP BY查询也无须再做排序和将行按组进行聚合计算了。
3索引覆盖查询是很快的。如果一个索引包含了查询需要的所有列那么存储引擎就不需要再回表查找行。这避免了大量的单行访问而上面的第1点已经写明单行访问是很慢的。 总的来说编写查询语句时应该尽可能选择合适的索引以避免单行查找、尽可能地使用数据原生顺序从而避免额外的排序操作并尽可能使用索引覆盖查询。这与本章开头提到的Lahdenmaki和Leach 的书中的“三星”评价系统是一致的。 如果表上的每一个查询都能有一个完美的索引来满足当然是最好的。但不幸的是要这么做有时可能需要创建大量的索引。还有一些时候对某些查询是不可能创建一个达到“三星”的索引的例如查询要按照两个列排序其中一个列正序另一个列倒序)。这时必须有所取舍以创建最合适的索引或者寻求替代策略例如反范式化或者提前计算汇总表等)。 理解索引是如何工作的非常重要应该根据这些理解来创建最合适的索引而不是根据一些诸如“在多列索引中将选择性最高的列放在第一列”或“应该为WHERE子句中出现的所有列创建索引”之类的经验法则及其推论。 那如何判断一个系统创建的索引是合理的呢?一般来说我们建议按响应时间来对查询进行分析。找出那些消耗最长时间的查询或者那些给服务器带来最大压力的查询第3章中介绍了如何测量)然后检查这些查询的schema、SQL和索引结构判断是否有查询扫描了太多的行是否做了很多额外的排序或者使用了临时表是否使用随机I/O访问数据或者是有太多回表查询那些不在索引中的列的操作。 如果一个查询无法从所有可能的索引中获益则应该看看是否可以创建一个更合适的索引来提升性能。如果不行也可以看看是否可以重写该查询将其转化成一个能够高效利用现有索引或者新创建索引的查询。这也是下一章要介绍的内容。 如果根据第3章介绍的基于响应时间的分析不能找出有问题的查询呢?是否可能有我们没有注意到的“很糟糕”的查询需要一个更好的索引来获取更高的性能?一般来说不可能。对于诊断时抓不到的查询那就不是问题。但是这个查询未来有可能会成为问题,因为应用程序,数据和负载都在变化。如果仍然想找到那些索引不是很合适的查询并在它们成为问题前进行优化则可以使用pt-query-digest的查询审查“review”功能分析其EXPLAIN出来的执行计划。
分析其EXPLAIN出来的执行计划。
