辨析覆盖索引/索引覆盖/三星索引

辨析覆盖索引/索引覆盖
既然多个列可以组合起来构建为联合索引，那么辅助索引自然也可以由多个列组 成。 覆盖索引也是我们经常见到的名词，InnoDB存储引擎支持覆盖索引(covering
index，或称索引覆盖)，即从辅助索引中就可以得到查询的记录，而不需要查询聚

集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信 息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。所以记住，覆盖索 引可以视为索引优化的一种方式，而并不是索引类型的一种。 除了覆盖索引这个概念外，在索引优化的范围内，还有前缀索引、三星索引等一系 列概念，都会在我们后面的课程中学习到。

深入思考索引在查询中的使用

索引在查询中的作用到底是什么？在我们的查询中发挥着什么样的作用呢？ 请记住：

1、一个索引就是一个B+树，索引让我们的查询可以快速定位和扫描到我们需要的数据。记录上，加快查询的速度
2、一个select查询语句在执行过程中一般最多能使用一个二级索引来加快查询，即使在where条件中用了多个二级索引。

索引的代价

世界上从来没有只有好处没有坏处的东西，如果你有，请你一定要告诉我，让我也感受 一下。虽然索引是个好东西，在学习如何更好的使用索引之前先要了解一下使用它的代 价，它在空间和时间上都会拖后腿。
空间上的代价 这个是显而易见的，每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点 都是一个数据页，一个页默认会占用16KB的存储空间，一棵很大的B+树由许多数据页组 成会占据很多的存储空间。
时间上的代价 每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。而且我们讲 过，B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是

叶子节点中的记录，还是非叶子内节点中的记录都是按照索引列的值从小到大的顺序而 形成了一个单向链表。 而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间 进行一些记录移位，页面分裂、页面回收的操作来维护好节点和记录的排序。如果我们 建了许多索引，每个索引对应的B+树都要进行相关的维护操作，这必然会对性能造成影 响。 既然索引这么有用，我们是不是创建越多越好？既然索引有代价，我们还是别创建了 吧？当然不是！按照经验，一般来说，一张表6-7个索引以下都能够取得比较好的性能权 衡。
那么创建索引的时候有什么好的策略让我们充分利用索引呢？
高性能的索引创建策略
正确地创建和使用索引是实现高性能查询的基础。前面我们已经了解了索引相关的数据 结构，各种类型的索引及其对应的优缺点。现在我们一起来看看如何真正地发挥这些索 引的优势。

索引列的类型尽量小
我们在定义表结构的时候要显式的指定列的类型，以整数类型为例，有TTNYINT、 NEDUMNT、INT、BIGTNT这么几种，它们占用的存储空间依次递增，我们这里所说的类型 大小指的就是该类型表示的数据范围的大小。能表示的整数范围当然也是依次递增，如 果我们想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引 列使用较小的类型，比如我们能使用INT就不要使用BIGINT，能使用NEDIUMINT就不要使 用INT，这是因为:
·数据类型越小，在查询时进行的比较操作越快（CPU层次)
·数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记 录，从而减少磁盘/0带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中， 从而加快读写效率。 这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他所有 的二级索引的节点处都会存储一份记录的主键值，如果主键适用更小的数据类型，也就 意味着节省更多的存储空间和更高效的I/0。

利用索引选择性和前缀索引
索引的选择性/离散性
创建索引应该选择选择性/离散性高的列。索引的选择性/离散性是指，不重复的索引值
（也称为基数，cardinality)和数据表的记录总数（N)的比值，范围从1/N到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多 的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。 很差的索引选择性就是列中的数据重复度很高，比如性别字段，不考虑政治正确的情况 下，只有两者可能，男或女。那么我们在查询时，即使使用这个索引，从概率的角度来 说，依然可能查出一半的数据出来。
比如下面这个表：

哪列做为索引字段最好？当然是姓名字段，因为里面的数据没有任何重复，性别字段是 最不适合做索引的，因为数据的重复度非常高。 怎么算索引的选择性/离散性？比如order_exp这个表：

select	COUNT(DISTINCT  order_no)/count(*)  cnt  from  order_exp;

select	COUNT(DISTINCT  order_status)/count(*)  cnt  from  order_exp;

很明显，order_no列上的索引就比order_status列上的索引的选择性就要好，原因很简 单，因为order_status列中的值只有-1,0,1三种。
前缀索引 有时候需要索引很长的字符列，这会让索引变得大且慢。一个策略是前面提到过的模拟 哈希索引。
模拟哈希索引： order_exp表中order_note字段很长，想把它作为一个索引，我们可以增加一个 order_not_hash字段来存储order_note的哈希值，然后在order_not_hash上建立索引，相对于之前的索引速度会有明显提升，一个是对完整的 order_note做索引，而后者则是
用整数哈希值做索引，显然数字的比较比字符串的匹配要高效得多。 但是缺陷也很明显：
1、需要额外维护order_not_hash字段；
2、哈希算法的选择决定了哈希冲突的概率，不良的哈希算法会导致重复值很多；
3、不支持范围查找。 还可以做些什么改进呢？还可以索引开始的部分字符，这样可以大大节约索引空间，从 而提高索引效率。但这样也会降低索引的选择性。一般情况下我们需要保证某个列前缀 的选择性也是足够高的，以满足查询性能。（尤其对于BLOB、TEXT或者很长的VARCHAR类 型的列，应该使用前缀索引，因为MySQL不允许索引这些列的完整长度）。 诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间)。 前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。按照《阿里最新Java编程规范泰山版》中《(二) 索引规约》中的说法：
中建议，这个前缀的长度为20比较合适，但是其实后面的“count(distinct left(列名, 索引长度))/count(*)的区分度来确定”会更合适点。在工程实践中具体如何做呢？ 可以这样：

SELECT  COUNT(DISTINCT  LEFT(order_note,3))/COUNT(*)  AS  sel3,
COUNT(DISTINCT  LEFT(order_note,4))/COUNT(*)AS  sel4, 
COUNT(DISTINCT  LEFT(order_note,5))/COUNT(*)  AS  sel5, 
COUNT(DISTINCT  LEFT(order_note,  6))/COUNT(*)  As  sel6, 
COUNT(DISTINCT  LEFT(order_note,  7))/COUNT(*)  As  sel7, 
COUNT(DISTINCT  LEFT(order_note,  8))/COUNT(*)  As  sel8, 
COUNT(DISTINCT  LEFT(order_note,  9))/COUNT(*)  As  sel9, 
COUNT(DISTINCT  LEFT(order_note,  10))/COUNT(*)  As  sel10, 
COUNT(DISTINCT  LEFT(order_note,  11))/COUNT(*)  As  sel11, 
COUNT(DISTINCT  LEFT(order_note,  12))/COUNT(*)  As  sel12, 
COUNT(DISTINCT  LEFT(order_note,  13))/COUNT(*)  As  sel13, 
COUNT(DISTINCT  LEFT(order_note,  14))/COUNT(*)  As  sel14, 
COUNT(DISTINCT  LEFT(order_note,  15))/COUNT(*)  As  sel15, 
COUNT(DISTINCT  order_note)/COUNT(*)  As  total
FROM  order_exp;

可以看见，从第10个开始选择性的增加值很高，随着前缀字符的越来越多，选择度也在 不断上升，但是增长到第15时，已经和第14没太大差别了，选择性提升的幅度已经很小 了，都非常接近整个列的选择性了。 那么针对这个字段做前缀索引的话，从第13到第15都是不错的选择，甚至第12也不是不 能考虑。
在上面的示例中，已经找到了合适的前缀长度，如何创建前缀索引:

ALTER  TABLE  order_exp  ADD  KEY  (order_note(14));

建立前缀索引后查询语句并不需要更改：
select * from order_exp where order_note = ‘xxxx’ ; 前缀索引是一种能使索引更小、更快的有效办法，但另一方面也有其缺点MySQL无法使用 前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。
有时候后缀索引 (suffix index)也有用途（例如，找到某个域名的所有电子邮件地 址)。MySQL原生并不支持反向索引，但是可以把字符串反转后存储，并基于此建立前缀 索引。可以通过触发器或者应用程序自行处理来维护索引。

只为用于搜索、排序或分组的列创建索引

也就是说，只为出现在WHERE 子句中的列、连接子句中的连接列创建索引，而出现在查 询列表中的列一般就没必要建立索引了，除非是需要使用覆盖索引。又或者为出现在 ORDER BY或GROUP BY子句中的列创建索引，这句话什么意思呢？比如：

SELECT * FROM order_exp ORDER BY insert_time, order_status,expire_time; 

查询的结果集需要先按照insert_time值排序，如果记录的insert_time值相同，则需要 按照order_status来排序，如果order_status的值相同，则需要按照expire_time排序。 回顾一下联合索引的存储结构，u_idx_day_status索引本身就是按照上述规则排好序 的，所以直接从索引中提取数据，然后进行回表操作取出该索引中不包含的列就好了。 当然ORDER BY的子句后边的列的顺序也必须按照索引列的顺序给出，如果给出ORDER BY order_status,expire_time, insert_time的顺序，那也是用不了B+树索引的，原因不用 再说了吧。

SELECT insert_time, order_status,expire_time,count(*) FROM order_exp GROUP BY insert_time, order_status,expire_time;

这个查询语句相当于做了3次分组操作： 先把记录按照insert_time值进行分组，所有insert_time值相同的记录划分为一组。 将每个insert_time值相同的分组里的记录再按照order_status的值进行分组，将 order_status值相同的记录放到一个小分组里。 再将上一步中产生的小分组按照expire_time的值分成更小的分组。 然后针对最后的分组进行统计，如果没有索引的话，这个分组过程全部需要在内存里实 现，而如果有了索引的话，恰巧这个分组顺序又和我们的u_idx_day_status索引中的索 引列的顺序是一致的，而我们的B+树索引又是按照索引列排好序的，这不正好么，所以 可以直接使用B+树索引进行分组。和使用B+树索引进行排序是一个道理，分组列的顺序 也需要和索引列的顺序一致。

合理设计多列索引

很多人对多列索引的理解都不够。一个常见的错误就是，为每个列创建独立的索引，或 者按照错误的顺序创建多列索引。 我们遇到的最容易引起困惑的问题就是索引列的顺序。正确的顺序依赖于使用该索引的 查询，并且同时需要考虑如何更好地满足排序和分组的需要。反复强调过，在一个多列 B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等 等。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY、 GROUP BY和DISTINCT等子句的查询需求。 所以多列索引的列顺序至关重要。对于如何选择索引的列顺序有一个经验法则：将选择 性最高的列放到索引最前列。当不需要考虑排序和分组时，将选择性最高的列放在前面
通常是很好的。这时候索引的作用只是用于优化WHERE条件的查找。在这种情况下，这样 设计的索引确实能够最快地过滤出需要的行，对于在WHERE子句中只使用了索引部分前缀 列的查询来说选择性也更高。 然而，性能不只是依赖于索引列的选择性，也和查询条件的有关。可能需要根据那些运 行频率最高的查询来调整索引列的顺序，比如排序和分组，让这种情况下索引的选择性 最高。 同时，在优化性能的时候，可能需要使用相同的列但顺序不同的索引来满足不同类型的 查询需求。

尽可能设计三星索引
三星索引概念

对于一个查询而言，一个三星索引，可能是其最好的索引。
如果查询使用三星索引，一次查询通常只需要进行一次磁盘随机读以及一次窄索引片的 扫描，因此其相应时间通常比使用一个普通索引的响应时间少几个数量级。
三星索引概念是在《Rrelational Database Index Design and the optimizers》 一书 ，中提出来的。原文如下：
The index earns one star if it places relevant rows adjacent to each other, a second star if its rows are sorted in the order the query needs,
and a final star if it contains all the columns needed for the query. 索引将相关的记录放到一起则获得一星； 如果索引中的数据顺序和查找中的排列顺序一致则获得二星； 如果索引中的列包含了查询中需要的全部列则获得三星。

三星（宽索引星）：
在满足了二星的情况下，如果索引中所包含了这个查询所需的所有列（包括 where 子句 和 select 子句中所需的列，也就是覆盖索引），这样一来，查询就不再需要回表了， 减少了查询的步骤和IO请求次数，性能几乎可以提升一倍。 一星按照原文稍微有点难以理解，其实它的意思就是：如果一个查询相关的索引行是相 邻的或者至少相距足够靠近的话，必须扫描的索引片宽度就会缩至最短，也就是说，让 索引片尽量变窄，也就是我们所说的索引的扫描范围越小越好。 这三颗星，哪颗最重要？第三颗星。因为将一个列排除在索引之外可能会导致很多磁盘 随机读（回表操作）。第一和第二颗星重要性差不多，可以理解为第三颗星比重是50%， 第一颗星为27%，第二颗星为23%，所以在大部分的情况下，会先考虑第一颗星，但会根 据业务情况调整这两颗星的优先度。
达成三星索引
现在有表

create table customer( cno int,
lname varchar(10), fname varchar(10), sex int,
weight int,
city varchar(10));

建立索引
create index idx_cust on customer(city,lname,fname,cno); 对于下面的SQL而言，这是个三星索引
select cno,fname from customer where lname =’xx’ and city =’yy’ order by fname;
来评估下： 第一颗星：所有等值谓词的列，是组合索引的开头的列，可以把索引片缩得很窄，符 合。
第二颗星：order by的fname字段在组合索引中且是索引自动排序好的，符合。 第三颗星：select中的cno字段、fname字段在组合索引中存在，符合。 达不成三星索引
现在有表

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(100) DEFAULT NULL,
  `sex` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `c_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
   ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

SQL语句如下：
select user_name,sex,age from test where user_name like ‘test%’ and sex =1 ORDER BY age
如果我们建立索引(user_name,sex,age)： 第三颗星，满足
第一颗星，满足
第二颗星，不满足，user_name 采用了范围匹配，sex 是过滤列，此时age 列无法保证 有序的。 上述我们看到，此时索引(user_name,sex,age)并不能满足三星索引中的第二颗星（排 序）。
于是我们改改，建立索引(sex, age，user_name)： 第一颗星，不满足，只可以匹配到sex，sex选择性很差，意味着是一个宽索引片， 第二颗星，满足，等值sex 的情况下，age是有序的， 第三颗星，满足，select查询的列都在索引列中，

对于索引(sex,age，user_name)我们可以看到，此时无法满足第一颗星，窄索引片的需 求。 以上2个索引，都是无法同时满足三星索引设计中的三个需求的，我们只能尽力满足2 个。而在多数情况下，能够满足2颗星，已经能缩小很大的查询范围了，具体最终要保留 那一颗星（排序星 or 窄索引片星），这个就需要看查询者自己的着重点了，无法给出 标准答案。

主键尽量是很少改变的列

我们知道，行是按照聚集索引物理排序的，如果主键频繁改变(update)，物理顺序会改 变，MySQL要不断调整B+树，并且中间可能会产生页面的分裂和合并等等，会导致性能会 急剧降低。

处理冗余和重复索引

MySQL允许在相同列上创建多个索引，无论是有意的还是无意的。MySQL需要单独维护重 复的索引，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能。重复 索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复 索引，发现以后也应该立即移除。 有时会在不经意间创建了重复索引，例如下面的代码:

CREATE TABLE test (
ID INT NOT NULL PRIMARY KEY, A INT NOT NULL,
B INT NOT NULL, UNIQUE(ID), INDEX(ID)
) ENGINE=InnoDB;

这里创建了一个主键，又加上唯一限制，然后再加上索引以供查询使用。事实上，MySQL 的唯一限制和主键限制都是通过索引实现的，因此，上面的写法实际上在相同的列上创 建了三个重复的索引。通常并没有理由这样做，除非是在同一列上创建不同类型的索引 来满足不同的查询需求。
冗余索引和重复索引有一些不同。如果创建了索引(A B)，再创建索引(A)就是冗余索 引，因为这只是前一个索引的前缀索引。因此索引(AB)也可以当作索引(A)来使用（这种 冗余只是对B-Tree索引来说的)。但是如果再创建索引 (B,A)，则不是冗余索引，索引 (B)也不是，因为B不是索引(A,B)的最左前缀列。 已有的索引(A)，扩展为(A，ID)，其中ID是主键，对于InnoDB来说主键列已经包含在二 级索引中了，所以这也是冗余的。
解决冗余索引和重复索引的方法很简单，删除这些索引就可以，但首先要做的是找出这 样的索引。可以通过写一些复杂的访问INFORMATION_SCHEMA表的查询来找。
删除未使用的索引
除了冗余索引和重复索引，可能还会有一些服务器永远不用的索引。这样的索引完全是 累赘，建议考虑删除。