MySQL(3)-索引

一、索引类型

　　在MySQL中，存储引擎使用索引，首先在索引中找到对应值，然后根据匹配的索引记录中找到对应的行。

　　无论是多么复杂的ORM工具，在精妙和复杂的索引面前都是"浮云"。这里只详细描述B-Tree，其他的简要说明....

 

1.1 B-Tree

前面介绍过B+树这种数据结构：

 

大多数引擎都支持这种索引。Arch引擎是一个例外：5.1之前Archive不支持任何索引。我们使用B-Tree，是因为这是MySQL中的关键字。实际上底层很可能不同，例如NDB集群存储引擎使用的是T-Tree结构，InnoDB使用的是B+Tree。

不同的实现性能肯定不同，这里不做讨论。

B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子到根的距离相同。下图说明了B-Tree索引的抽象表示，大致反应了InnoDB是如何工作的。

B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫面需要的数据，取而代之的是从索引的根节点开始搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层寻找，最终找到对应的值或者该记录不存在。

叶子节点比较特别，它们的指针指向的被索引的数据，而不是其他的节点页(不同引擎的指针类型不同)。

B+ 树适合范围查找数据，按照字母顺序连续的值就行查找非常合适的。

索引中包含了last_name、first_name和dob列，其索引结果如下图所示：

 

B-Tree索引对其他类型查询有效：依旧以上面的索引为例，last_name+first_name+dob

  • 全值匹配：和索引中的所有列匹配
  • 最左前缀：即只使用索引的第一列
  • 匹配列前缀：匹配某一个列的值的开头部分，这里也只使用索引的第一列。
  • 匹配范围值：也只是第一列
  • 精确匹配某一个列并且范围另一列：即第一列全匹配，第二列范围匹配。
  • 只访问索引的查询：即查询只需要访问索引，无须访问数据行。

因为索引中的节点是有序的，因此索引还可以用于ORDER BY 操作，一般来说，如果B-Tree能以某种方式查找到值，那么也可以按照这种方式排序，因此以上的匹配的方式不但适用于查询，也适用于排序。

以下情形不适用于B-Tree索引：

  • 如果不是从索引最左列开始查找，无法使用索引，必须使用到last_name
  • 不能跳过索引中的列，不能使用last_name+dob
  • 如果查询有某个列的范围查询，则其右边所有列无法使用索引优化查找。例如查询WHERE last_name='Smith' AND first_name LIKE 'J%' AND dob='1976-12-23'。这个查询只能用到索引的前2列，因为LIKE这里是一个范围查询。

查询时索引列的顺序多么的重要：这些限制都和索引列的顺序有关。

 

1.2 Hash索引

hash index： 基于hash表实现，只有精确查询才有效。对于每一行数据，存储引擎都会对所有的索引项计算一个hash code。哈希索引将所有的hash code存储在索引中，并同时在hash表中保存指向每个数据行的指针。

只有Memory引擎显示支持hash索引，也是memory引擎的默认类型。

由于只需要存储对应的hash值，因此索引的结构非常紧凑，所以索引的结构非常紧凑，这也让哈希索引查找的速度非常的快。然后，哈希索引也有它的限制：

  • 由于只包含hash值和行指针，因此不能使用索引的值来避免读取行，你一定要读取到行，哪怕你只访问这一列。
  • hash是无序的，因此不能用于排序
  • 不支持部分索引列匹配，因为hash的是全部内容
  • 只支持=,IN(),<=>，不支持任何范围
  • hash冲突多会导致维护代价

因此，Hash只适用于某些特殊的场合。

注意，前文中说的是只有Memory引擎显示支持这种索引，InnoDB这种会隐式的支持，偷偷的支持。InnoDB有一个功能被称为"自适应HASH索引"。当InnoDB注意到某些索引值被用的非常频繁时，它会在内存中基于B-Tree索引之上再创建一个hash索引，这样就让B-tree索引也具有hash索引的一些优点，比如hash的快速查找。

....

1.3 R-Tree

空间索引。MyISAM的R-Tree一般。这里略过

1.4 全文索引

FullText，搜索中常用的，略，MyISAM支持

1.5 其他

TokuDB使用的分形树索引(fractal tree index)，这是一种新的数据结构，既有B-Tree的很多优点，也避免了B-Tree的一些缺点。

二、索引优点

使用索引可以快速定位到表的指定位置，但是这不是索引的唯一作用。

最常见的B-Tree索引，按照顺序进行存储数据，因此MySQL可以用来做ORDER BY和GROUP BY 操作。因为数据是有序的，所以B-Tree会将相关的列值存储在一起。最后因为索引中存储了实际的列值，索引某些查询只使用索引就能完成全部查询。据此特性，有三个有点：

  • 大大减少了服务器扫面的数据量
  • 帮助服务器避免排序和临时表。(大规模排序可能用到临时表的)
  • 索引可以将随机I/O变为顺序I/O。(还是那样说，机械硬盘顺序IO很重要，跟事务日志差不多)

索引的内容非常庞大，这里基本只是简介... 比如如何评价一个索引是否适合某个查询的有"三星系统"：

　　将相关记录放到一起获得一星

　　索引中数据顺序和查找中的排列顺序一致则二星

　　索引中包含了全部列则是三星。。。

尽管有这么优点？索引是最好的解决方案吗？

　　不是。索引会带来额外的开销，只有利大于弊才值得。对于非常小的表，不需要，全表扫描即可。对于中到大型的表，索引就非常有效。对于特别大的表，索引就可能会成为负担。此时需要一种技术直接区分查询出需要的一组数据，而不是一条一条，比如分区技术。

　　如果表的数量特别多，则可以建立一个元数据信息表。路由表等等。这样在查询时可以忽略那些没有必要的表，这是种常用的技巧。对于TB级别的数据，定位单条数据的意义不大，所以经常会使用块级别元数据技术来替代索引。

 三、高性能索引策略

3.1 独立的列

我们经常会看到一些查询不当地使用索引，或者使得MySQL无法使用已经存在的索引。如果查询的列不是独立的，则MySQL就不会使用索引。"独立的列"是指索引列不能是表达式的一部分，也不能是函数的参数。

例如：

　　SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

　　SELECT ... WHERE TO_DAYS(CURRENT_DATE) -TO_DAYS(date_col) <= 10;

 

3.2 前缀索引和索引选择性

有时候需要索引很长的字符列，这回让索引变的大而且慢。

可以索引开始的部分字符。

一般情况下某个列前缀的选择性也是足够高的，足以满足查询性能了，对于BLOB,TEXT或者很长的VARCHAR，必须使用前缀索引。

诀窍在于要选择足够长的前缀以保证较高的选择性。

为了要找合适长度，需要找到常见的值列表，然后进行比较。

以一个表进行说明，首先查询最频繁的字段...

上面的值都出现多次，再找到最频繁出现的城市前缀，先从3个前缀字母开始：

 

 嗯，前缀太频繁了，这证明3这个长度不够...继续尝试下增加到7

 

目前到7的话，感觉差不多，其实还有一种更好的计算方式...

看到上图中6->7七本没有什么增长，7 这个数字是比较合适的，因此创建索引...

ALTER TABLE sakila.city_demo ADD KEY (city(7)); 

 此时前缀索引更小、更快，但是有缺点：没有办法进行ORDER BY 和GROUP BY，也无法使用前缀索引做覆盖扫描。

 3.3 多列索引

 很多人对多列索引的理解都不够。一个常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序创建多列索引。

先来看第一个问题，为每个列创建独立的索引，SHOW CREATE TABLE一下，可能出现：

这种索引策略，比如说"把WHERE条件里面的列都建上索引"，这样的建议是非常错误的。

你这样最好的情况只是"一星"索引，其性能比起真正优秀的索引可能只差几个数量级。有时如果无法创建一个"三星"索引，那么不如忽略掉WHERE字句，集中精力优化索引列的顺序，或者创建一个全覆盖索引。

MySQL在5.0之后引入了一种"索引合并"的策略，一定程序上可以使用表的多个单列来定位指定的行。

例如，表film_actor在字段film_id和actor_id上各有一个单列索引。创建SQL语句如下：

CREATE TABLE `film_actor` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `actor_id` int(11) NOT NULL,
  `film_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `film_actor_film_id` (`film_id`) USING BTREE,
  KEY `film_actor_actor_id` (`actor_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=40001 DEFAULT CHARSET=latin1;

　对于下面这个语句，这2个索引不是个好的选择：

老版本的MySQL中，这甚至可能会使用全表扫描。除非改为下面的语句:

MySQL5.0之后，查询能够同时使用这2个单列索引进行扫描，并且将结果合并。这种算法有三个变种：

　　OR条件的联合(union)

　　AND条件的相交

　　组合前2种情况.

使用explain看到结果如下：

MariaDB [demo]> explain select film_id, actor_id from film_actor where actor_id=100 or film_id=100\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: index_merge
possible_keys: film_actor_film_id,film_actor_actor_id
key: film_actor_actor_id,film_actor_film_id
key_len: 4,4
ref: NULL
rows: 398
Extra: Using union(film_actor_actor_id,film_actor_film_id); Using where

MySQL会使用这类技术优化复杂查询。索引合并策略有时候是一种优化的结果，但是实际上更多时候说明表上的索引真的很糟糕。

  • 当服务器出现对多个索引做相交操作时(通常意味有多个AND条件)，通常意味着需要一个多列索引，而不是独立的单列索引。
  • 当服务器需要对多个索引做联合操作(通常有多个OR条件)，通常要耗费大量的CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回大量数据的时候。

更重要的是，优化器不会把这些计算到"查询成本"中，优化器只关心随机页面读取。这会使得查询成本被"低估"，导致该执行计划还不如全表扫描。这样不但会消耗更多的CPU和内存资源，还可能影响查询的并发性，但是如果是单独运行这样的查询则往往会忽略对并发性的影响。通常来说，还不如像在MySQL4.1或更早的时代，将查询改写成UNION的方式更好。

当看到EXPLAIN中看到有索引合并，应该好好检查一下查询和表的结构，是否可以优化?也可使用optimizer_switch来关闭索引合并功能。也可以使用IGNORE INDEX提示让优化器忽略掉某些索引。

 

3.4 选择合适的索引顺序

在一个多列的B-Tree索引中，索引列的顺序意味着首先按照最左列进行排序，其次是第二列等等。所以，索引可以按照升降序扫描...等等。

在"三星索引"中，列的顺序至关重要。

有一个经验法则： 将选择性最高的列放到索引最前列，这个建议有用吗？某些场景下有用，但是通常不如避免随机IO和排序那么重要。

当不需要考虑排序和分组的时候，上述经验是很好的。这时候索引的作用只是用于优化WHERE条件的查找。这样设计的索引确实能最快的过滤出需要的行。然后，性能不是只依赖于所有索引列的选择性(整体基数)，也和具体的查询值有关系。

以下面的查询为例：

SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584;

额，是应该创建一个(staff_id, customer_id)还是换个顺序？可以跑一些查询来确定表中值的分布情况，并确定哪个列的选择性最高。可以先用查询预测一下，看各个WHERE条件的分支对应的数据基数有多大：

貌似应该把customer_id放到前面去，因为瞬间过滤出了只剩30条。

但是，有个问题，这样非常依赖于选定的具体值，对其他值可能很不公平。

因此，综合考虑，还是应该依靠全局基数和选择性，而不是某个具体查询

不同的customer_id出现的更多，选择性更高，所以应该把customer_id放置在第一列。

ALTER TABLE payment ADD KEY(customer_id, staff_id)

但是当使用前缀所以你的时，在某些条件值的基数比正常值高情况下，即某些特殊的情况，比如说cutomer_id=1 staff_id=100的时候，staff_id就能过滤出更少，问题就来了，你考虑的是一般情况，总会有特殊情况。这实际上是一个非常典型的问题。

下面举一个真实案例：在一个用户分享购买商品和购买经验的论坛上，有一个特殊表的查询非常慢：

使用explain进行分析：

使用了联合索引，而且考虑了全局基数，非常合理。但是，考虑这个值的特殊情况：

。。。userId=1288826的最少，userId在前面才是合理的。

最后解决办法是：修改应用程序，禁止针对这类用户和组执行这个查询。

3.5 聚簇索引

 聚簇索引不是一种单独的索引类型，而是一种数据存放方式。数据存放是由存储引擎所实现的，InnoDB的聚簇引擎实际上在同一个结构中保存了B-Tree索引和数据行。

当表中有聚簇索引时，它的数据行实际上存放在索引的叶子页(leaf page)中。"聚簇"表示数据行和相邻的键值紧凑的存储在一起。因为无法同时把数据行存放在2个不同的地方，所以一个表只能有一个聚簇索引。(不过，覆盖索引可以模拟多个聚簇索引的情况)。

因为是存储引擎负责实现索引，因此不是所有的存储引擎都支持聚簇索引，这里主要关注InnoDB。

上图展示了聚簇索引的记录如何存放:

　　1. 这里是有一个字段是索引,11, 21, 91 ,1 ,2...

　　2. 这里叶子节点中包含了全部行的数据

InnoDB通过主键聚集数据，也就说"被索引的列"就是主键.

如果没有定义主键，InnoDB会选择一个唯一的非空索引来替代。如果没有这样的索引，InnoDB会隐式的定义一个主键来作为一个聚簇索引。InnoDB只聚集同一个页面中的记录，包含相邻键值的页面可能会相距甚远。

 聚簇主键可能对性能有帮助，也可能导致严重的性能问题。

优点：

  • 可以把相关数据保存在一起。
  • 数据访问更快，索引和数据保存在同一个B-Tree中，因此更快。
  • 使用覆盖索引扫描的查询

缺点：

  • 聚簇数据最大限度的提高了I/O密集型应用的性能，但是如果数据全部放置在内存中，那么聚簇索引不重要。
  • 插入速度严重依赖于插入顺序，按照主键的顺序是加载数据到InnoDB表中速度最快的方式，但是如果不是按照主键顺序加载，最好在加载完成之后使用OPTIMIZE TABLE命令重新组织一下表。
  • 更新聚簇索引列的代价很高，因为InnoDB会将每个被更新的行移动到新的位置。
  • 基于聚簇索引的表插入新行，或者主键被更新导致需要移动行的时候，可能面临"页分裂"问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成2个页面来容纳该行，这就是一次页分裂操作。这会造成占用更大的空间。
  • 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候
  • 二级索引(非聚簇索引)可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。
  • 二级索引的访问需要二次查找

 为什么需要二次查找？因为二级索引中保存的不是指向行的物理指针，而是行的主键值。因此二级索引去查找，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找对应的行。这里做了重复的工作：二次B-Tree查找而不是一次。

 

在InnoDB表中按照主键顺序插入行

　　如果正在使用InnoDB表并且没有什么数据需要聚集，那么可以定义一个代理键，就是一个和应用无关的id，最简单的方法就是使用AUTO_INCREMENT自增列。这样可以保证数据行是顺序写入，根据主键做关联操作的性能也会更好。

下面以书中的一个基准测试为例，创建2张表做测试，一张自增id，一张不是。

首先，在一个有足够容纳能力的索引服务器上向这2张表各插入100w条数据，然后继续插入300w，让索引大小超过服务器的内存容量，结果如下：

结果说明UUID的主键插入花费时间更长，索引占用的空间也更大。

  • 如果主键是顺序的，InnoDB可以把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时(InnoDB默认的最大填充因子是页大小的15/16，留出部分空间用于以后的修改)，下一条记录会写入新的页中。
  • 如果主键不是顺序（UUID）的，新行的主键值不一定比之前的大（索引肯定要是有序的，才好查找），所以InnoDB无法简单地总是把新行插入到索引的最后，而是要寻找新的文职，这需要额外工作且数据分布不够优化。

下面总结的是UUID的缺点：

写入的目标页可能已经刷入的磁盘或者内存缓存中已经移除了，或者还没有被加载到缓存中，InnoDB在插入前不得不先找磁盘读取这目标页，这就是随机IO，还是大量的随机IO.

因为写入乱序，InnoDB不得不频繁页分裂，以便为新的行为分配空间，页分裂导致大量移动数据，一次插入最少修改3个页(一个分裂成2个，再移动就是3个)，而不是1个页。

由于频繁的分裂，页的分布会非常的稀疏，而且不规则地被填充，所以最终数据会有碎片。

 

 

 3.6 覆盖索引

通常大家优化索引都是根据查询的WHERE条件。不过优秀的索引方案应该考虑整个语句。

比如说如果一个索引包含(或者覆盖)所有需要查询的字段的值，我们就 称为"覆盖"索引。

如果可以只扫描索引：

  • 索引条目通常远小于数据行。极大的减少数据访问量，这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷贝上。
  • 索引是按照列值顺序存储的，至少在单个页如此，所以对于I/O密集型的范围查询比会随机磁盘读取每一行的数据少的多。对于某一些引擎，比如MyISAM和Percona XtraDB， 甚至可以通过OPTIMIZE命令使得索引完全顺序排列。
  • 一些存储引擎如MyISAM在内存中只缓存索引。
  • 由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用，InnoDB的二级索引在叶子节点中保存了行的主键值，如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。

不是所有类型的索引都能成为覆盖索引，覆盖索引必须要存储了索引列的值，而哈希索引，空间索引和全文索引都不存储，因此B-Tree索引才能成为覆盖索引。

当发起一个覆盖索引时，explain中的Extra列中可以看到"Using index"的信息。

索引的覆盖查询可能还有很多陷阱。MySQL查询优化器在执行查询前判断是否有一个索引能进行覆盖。假设你的索引覆盖了WHERE条件中的字段，但是没有覆盖查询的字段。如果条件为假，MySQL5.5和更早的版本也总是会回表获取数据行，尽管不需要这行，下面是一个例子。

  • 没有索引能够覆盖这个额查询。因此你是select *。不过还有一个捷径：WHERE条件列是可以覆盖的，因此MySQL可以使用该索引找到对应的actor并检查title是否匹配先，过滤之后再去读取需要的数据行。
  • MySQL不能在索引中执行LIKE操作。这是底层存储引擎API的限制。MySQL能在索引中做最左前缀匹配的LIKE比较，因为该操作可以转换为简单的比较操作。

 有一种非常巧妙的方式可以解决该问题，首先扩充索引为3个数据列(actor, title, prod_id)，然后用如下方式重写查询：

 

 

我们称这种方式为延迟关联，因为延迟了对列的访问。在查询的第一阶段可以利用到覆盖索引：

SELECT prod_id FROM products WHERE actor ='SEAN CARREY' AND title like '%APOLLO%';

这条语句中使用了actor，因此可以用到最左索引，同时索引中包含了查询需要的prod_id，因此是覆盖索引，先过滤出所有的prod_id，然后根据聚簇索引去关联。

在大所数存储引擎中，覆盖索引只能覆盖那么只访问索引中部分列的查询。不过InnoDB的二级索引不一样，它还包含了主键的值，这是一个额外的列。

3.7 使用索引扫描来排序

MySQL有2种方式来生成有序的结果：

　　通过排序操作；

　　或者按照索引进行顺序扫描；

如果EXPLAIN出来的type列为index，则表明MySQL使用了索引扫描来排序。和("Using index"完全不同)。

扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但是如果索引不能覆盖到全部需要的列，那就不得不扫描一条记录都回表查询一次，基本上都是随机IO，因此按照索引读取数据的速度通常比顺序扫描全表慢，尤其是在I/O密集型的工作负载时。

MySQL可以使用同一个索引既满足排序，又用于查找行。因此，如果可能，设计索引时应该尽可能地同时满足这2种任务，这样是最好的。

只有索引的列顺序和ORDER BY字句的顺序完全一致，并且所有的列排序方向(倒序或者正序)都一样时，MySQL才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当ORDER BY 字句引用的字段全部为第一个表时，才能使用索引做排序。ORDER BY字句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求；否则，MySQL都要执行排序操作，而无法利用索引排序。

有一种情况特殊，如果条件中规定了第一列是常数，则可以不考虑最左前缀。比如rental表有一个索引(rental_date, inventory_id, customer_id)。则可以：

 

 下面列举一些不使用索引排序的例子：

(1) 一个正序，一个逆序

... WHERE rental_date='2015-05-25' ORDER BY inventory_id DESC, customer_id ASC;

(2) 列不在索引中

... WHERE rental_date='2015-05-25' ORDER BY inventory_id , staff_id;

(3) WHERE和ORDER BY无法构成最左前缀:

... WHERE rental_date='2015-05-25' ORDER BY customer_id;

(4) WHERE不是常量且无法构成最左前缀

... WHERE rental_date>'2015-05-25' ORDER BY inventory_id, customer_id;

(5) WHERE条件上有多个等于，也是范围查询

... WHERE rental_date='2015-05-25' AND inventory_id IN (1,2) ORDER BY customer_id;

 

3.8 重复索引问题

重复索引：在相同的列上按照相同的顺序创建的相同类型的索引。

例如：

　　

MySQL的唯一约束和主键约束都是通过索引。因此，上面写法非常重复。

冗余索引则不同，如果索引(A,B)，那么(A)就是一个冗余的，如果是(B,A)就不是冗余的。

大多数情况下都不需要冗余索引，应该是扩展索引而不是创建新索引。但是有时候也可能要使用冗余索引，因为扩展已经存在的索引会导致其太大。

而且索引越多，插入速度会越慢。

因此，统计索引的使用频率也很关键，最简单有效的办法是在Percona Server或者MariaDB中打开userstates服务器变量，让服务器运行一段时间，再通过查询INFOMATION_SCHEMA.INDEX_STATISTICS可以查询索引的使用频率。

还可以使用Percona Toolkit中的pt-index-usage，该工具可以读取查询日志。

四、一个例子

在MySQL中，可以使用命令:

show index from <tbl.name>\G

 来查看索引详细信息。MySQL手册中对每个字段都有说明，这里想要说明的是Cardinality表示是索引列的基数，即存储引擎估计索引列有多少不同的值。

 

假设这一个网站的用户表设计：
　　字段有国家、地区、城市、性别、眼睛颜色、等等。
　　查询必须根据用户的最后在线时间，其他会员对用户的评分等进行排序。

一、首选得支持多种条件

考虑每个字段的选择性：

　　country列的选择性通常不高，但是可能很多查询会用到。
　　sex的选择性太低，也会在查询用到
　　但是考虑到查询频率，还是建议在创建不同组合时候将(sex, country)列作为前缀。

但是之前说过，选择性低不需要索引。但是这样做的理由有2个：
　　如果前述几乎所有的查询都会用到sex列，甚至可能有些禽兽只搜索女用户，加上这列也没啥坏处

　　如果你没有用sex列，还可以加上条件SEX IN ('m','f')来让MySQL选择索引么，这招对sex这样的列很有效，国家country还是算了吧，country太多了，IN()列表太长。

不过这表明了一个原则：你得考虑表中所有的选项。

接下来考虑其他常见WHERE条件的组合，并要了解哪些情况下会很慢。
　　(sex,country,age)上的索引就很明显
　　(sex,country,region,age)
　　(sex,country,region,city,age)这样的组合可能都有需要。。。

这样下去索引很多，这也没办法，先这样吧。。。实在不行你IN很多国家也是可以的，别太多，太多的IN可能极大降低查询性能。
你可能注意到了，age会被设计在最后面，为什么？因为age一般都是范围查询，范围查询一般都放置在最后面。


(二)、避免多个范围条件

前面说过：范围条件的话后面索引无法用到！
但是，要注意：
　　where acotr_id > 45 是范围查询
　　where actor_id in (1,2,3) 不是，这是多个等值查询

(三)、优化排序

使用文件排序对小数据集是很快的，但是如果查询匹配的结果有上百万行的话会怎么样。

SELECT <cols> FROM profiles WHERE sex='M' ORDER BY rating LIMIT 10;

这个查询用到了ORDER BY 和 LIMIT，没有索引的话会很慢...
如果翻到很后面的页...就像这样

SELECT <cols> FROM profiles WHERE sex='M' ORDER BY rating LIMIT 100000,10;


无论如何创建索引，这种查询都是个严重的问题。因为随着偏移量的增加，MySQL需要花费大量的时间来扫描需要丢弃的数据。反范式化、预先计算和缓存可能是解决这类查询的仅有策略。

一个更好的办法是限制用户能够翻页的数量，实际上这对用户的体验影响不大，因为用户很少会真正在乎搜索结果的第100000页。

优化这类索引的另一个比较好的策略是用延迟关联。再根据主键关联原表获取所需要的行。这可以减少MySQL扫描那些需要丢弃的行数。下面这个查询展示了如何高效的使用(sex, rating)索引进行排序和分页。

select <cols> FROM profiles INNER JOIN (
    SELECT <primary key cols> FROM profiles
    WHERE x.sex='M' ORDER BY rating LIMIT 1000000,10
) AS x USING (primary key cols);

这条sql很漂亮，内部sql用到了排序，且用了覆盖索引，首先where条件中是一个常量 sex，ORDER BY条件中使用的是rating，刚好能够使用到(sex,rating)索引，同时，查询出来的数据是primary key，我们知道由于InnoDB中的使用的是聚簇索引，二级索引中的叶子节点中包含了主键信息，因此是一个覆盖索引。最后通过内关联根据id搜索即可，这才是我们需要的查询路线。

 

posted @ 2016-11-18 15:51  carl_ysz  阅读(391)  评论(0)  编辑  收藏  举报