Mysql索引

1.索引简介

　　为了满足对数据的快速访问，我们通常需要将数据组织成一种有序的方式， 而原始的情况下数据的物理存储顺序便可代表一种“序”，但是由于物理存储的“序”只能是一种，但我们业务的访问模式是多样的，所以我们有了索引,索引是一种以更小代价来组织数据关系的一种“序”，不同的索引可以满足不同的访问模式。 

1.1 根据需求选择索引类型 

(1)hash index主要用于满足精确匹配； 
(2)B-Tree index主要用于满足范围查询、精确匹配； 
(3)fulltext index主要用于全文关键字查询； 

1.2 存储引擎与索引类型

(1)heap引擎支持 hash index； 
(2)MyISAM 、InnoDB引擎支持B-Tree index； 
(3)MyISAM 支持fulltext index； 

    索引具体由存储引擎提供支持，而非MySQL内核，所以使用的是对同一种索引类型，内部的实现方式与效率都可能不同。 

2.B-Tree

　　指B-Tree索引，它通常使用B-Tree数据结构来保存数据。大部分MySQL的存储引擎都支持B-Tree索引(Archive例外)。 
     B-Tree索引加速了数据访问。从B-Tree根开始，借助中间节点页的上界和下界值，可以快速搜寻到叶子页层，最终找到含有需要找的值的叶子页(或者确定无法找到需要的数据)，找到对应的叶子页后可以通过相应的指针直接找到数据表中对应的数据行。这样存储引擎不会扫描整个表得到需要的数据。同时B-Tree索引通常意味着索引中数据保存时有序的，可以利用B-Tree索引来加速排序。 

2.1. InnoDB B-Tree

    存储引擎使用了不同的方式把B-Tree索引保存到磁盘上，它们会表现出不同的性能。例如MyISAM使用前缀压缩的方式以减小索引，而InnoDB不会压缩索引。同时MyISAM的B-Tree索引按照行存储的物理位置来引用被索引的行，但是InnoDB按照主键值引用行。这些不同有各自的优点和缺点。 

2.1.1.InnoDB聚簇索引(cluster index)

    聚簇索引不是一种单独的索引类型，而是一种存储数据的方式。当表有聚簇索引的时候，它的数据行实际保存在索引的叶子页。聚簇是指实际的数据行和相关的键值都保存在一起。每个表只能有一个聚簇索引。由于是存储引擎负责实现索引，并不是所有的存储引擎都支持聚簇索引。当前只有SolidDB和InnoDB是唯一支持聚簇索引的存储引擎。数据与索引在同一个B-Tree上，一般数据的存储顺序与索引的顺序一致。InnoDB聚簇索引每个叶子节点包含primary key 和行数据,非叶子节点只包括被索引列的索引信息。 

聚簇索引的优缺点： 
优点： 
（1）相关的数据保存在一起，利于磁盘存取； 
（2）数据访问快，因为聚簇索引把索引和数据一起存放； 
（3）覆盖索引可以使用叶子节点的primary key的值使查询更快； 

缺点： 
（1）如果访问模式与存储顺序无关，则聚簇索引没有太大的用处； 
（2）按主键顺序插入和读取最快, 但是如果按主键随机插入(特别是字符串)则读写效率降低； 
（3）更新聚簇索引的代价较大，因为它强制InnoDB把每个更新的行移到新的位置； 
（4）建立在聚簇索引上的表再插入新行，或者在行的主键被更新，该行必须被移动的时候会进行分页。分页发生在行的键值要求行必须被放到一个已经放满了数据的页的时候，此时存储引擎必须分页才能容纳该行，分页会导致表占用更多的磁盘空间。 
（5）聚簇表可能会比全表扫描慢，尤其在表存储的比较稀疏或因为分页而没有顺序存储的时候。 
（6）非聚簇索引可能会比预想的大，因为它们的叶子节点包含了被引用行的主键列。 
（7）非聚簇索引访问需要两次索引查找，而不是一次。 

其它需要说明点： 
（1）InnoDB的primary key为聚簇索引, 
（2）不能通过其他方式指定聚簇索引, 
（3）如果InnoDB不指定primary key,InnoDB会找一个unique not null的字段做聚簇索引, 
（4）如果还没有这样的字段，则InnoDB会建一个非可见的系统默认的主键---row_id(6个字节长)作为聚簇索引。 
（5）建议使用数字型auto_increment的字段作为聚簇索引。 
（6）不推荐用字符串字段做聚簇索引(primary key) , 因为字符串往往都较长， 会导致辅助索引过大(辅助索引的叶子节点存储primary key的值),而且字符串往往是乱序。 
（7）聚簇索引乱序插入容易造成插入和查询的效率低下。

2.1.2.InnoDB辅助索引(secondary index)

   InnoDB 中非聚簇索引的所有索引都是辅助索引。辅助索引的查询代价变大，需要两次B-Tree查询，一次辅助索引, 一次聚簇索引。所以在建立聚簇索引和辅助索引的时候需要考虑到这点。当辅助索引满足覆盖索引时，只需要一次B-Tree查询并且直接在辅助索引便可获取所需数据，不需要再进行数据读取，提高了效率。我们在设计索引和写SQL语句的时候就可以考虑利用到覆盖索引的优势。建议尽量减少对primary key的更新, 因为辅助索引叶子节点包含primary key的值(这样避免当行被移动或枫叶时更新辅助索引), primary key的变化会导致所有辅助索引的更新。

2.1.3.InnoDB动态哈希(adaptive hash index) 

   动态哈希索引是InnoDB为了加速B-Tree上的节点查找而保存的hash表 。 B-Tree上经常被访问的节点将会被放在动态哈希索引中。 

    注意点： 
    MySQL重启后的速度肯定会比重启前慢, 因为InnoDB的innodb_buff_pool和adaptive hash index都是内存型的，重启后消失，需要预热(访问一段时间) 后性能才能慢慢上来。 

3.1.2.1.4.InnoDB覆盖索引(covering index) 

    索引通常是用于找到行的，但也可以用于找到某个字段的值而不需要读取整个行，因为索引中存储了被索引字段的值,只读索引不读数据, 这种情况下的索引就叫做覆盖索引。 

    覆盖索引是很有力的工具，可以极大地提高性能。它主要的优势如下： 
    1.索引记录通常远小于全行大小，因此只读索引，MySQL就能极大的减少数据访问量。这对缓存的负载是非常重要的，它大部分的响应时间都花在拷贝数据上。对于I/O密集型的负载也有帮助。因为索引比数据小很多，能很好的装入内存。 

   2.索引是按照索引值来进行排序的，因此I/O密集型范围访问将会比随机地从磁盘提取每行数据要快的多。 

    3.覆盖索引对于InnoDB来说非常有用，因为InnoDB的聚集缓存。InnoDB的辅助索引在叶子节点保存了主键值，因此，覆盖了查询的第二索引在主键上避免了另外一次索引查找。 

3.1.2.2. MyISAM B-Tree 
    
    MyISAM B-Tree索引和InnoDB B-Tree索引都是采用B-Tree的存储方式，但是在数据布局上是不同的。 
     与InnoDB不同，MyISAM的主键和其它索引没有结构上的区别。 
     主键只是一个唯一的，名为primary key的非空索引。 
     MyISAM B-Tree 支持前缀压缩, 压缩后的索引称为MyISAM packed index。 
     索引可以压缩为原来的1/10, 其实是CPU/mem/disk 之间的一个tradeoff, create  table的时候可以为索引指定 pack_keys (alter table t engine=MyISAM pack_keys=1)，但是packed index对反向order和binary-search效率差。 

3.1.3.hash 

3.1.3.1. hash索引介绍 

    哈希索引建立在哈希表的基础上，它只对使用了索引的每一列的精确查找有用。对于每一行，存储引擎计算出了被索引列的哈希码，它是一个较小的值(可能对具有相同索引值的不同行计算出的哈希值不同)。索引中包含哈希码和对应指向数据行的指针。 

    在MySQL中，只有Memory引擎支持显式的哈希索引。尽管Memory引擎也支持B-Tree索引，但它是Memory表的默认索引类型。 

3.1.3.2. hash索引的合理使用 
    
   除了Memory引擎显式支持hash索引，NDB cluster存储引擎支持唯一的hash索引。它的功能是该存储引擎特有的。 
   
   同时InnoDB 存储引擎也有一个特别的功能较自适应hash索引。当InnoDB注意到一些索引值被很频繁地访问的时候，它就会在B-Tree的顶端为这些值建立起内存中的索引。这使B-Tree索引有了一些hash索引的特性。 
   
   同时如果存储引擎不支持hash索引，可以按照InnoDB使用的方式模拟自己的hash索引。 

3.1.3.3. hash索引与B-Tree索引的优劣势 
   
   B-Tree索引能很好地用于全键值、键值范围或者键前缀查找。主要对以下类型的查询有用： 
  1.匹配全名，全键值匹配指和索引中的所有列匹配。 
  2.匹配最左前缀，B-Tree索引只能匹配索引的最左部分。 
  3.匹配前缀索引，可以匹配某列值的开头部分。 
  4.匹配范围值。 
  5.只访问索引的查询，覆盖索引讨论的就是这种应用。 

   同时当B-Tree能以某种特殊的方式找到某行，那么它能以同样的方式对行进行排序。因此如上的查找方式也可以同等地应用于order by。但是B-Tree也有一些局限： 
  1.如果查找没有从索引列的最左开始，它就没有什么用，即最左原则。 
  2.不能跳过索引中的列。 
  3.存储引擎不能优化访问任何在第一个范围条件右边的列。 

   而hash索引本身只保存简单的hash值，hash索引显得非常紧凑。hash值的长度不会依赖于索引的列。查找速度是很快的，但是hash索引有一些局限： 

   1.索引只包含了hash值和行指针，而不是值本身，MySQL不能使用索引中的值来避免读取行。 
   2.MySQL不能使用hash索引进行排序，因为不是按序包含行。 
   3.hash索引不支持部分键匹配，因为它是由被索引的全部值计算出来的。 
   4.hash索引只适用于=、in相等的比较。不能加快范围查询。 
   5.访问hash索引中的数据非常快，除非是碰撞率很高。当发生碰撞时，存储引擎必须访问链表中的每一个行指针，然后逐行进行数据比较，以确定正确的数据。 

   3.1.4.前缀索引 
   3.1.4.1.前缀索引介绍 
    在MySQL 中，索引只能从字段内容的最左端开始建， 查询的时候也只能从索引的最左端开始查， 对字段内容只建从左开始的部分字节的索引，而非全部做索引的这种index 就叫做前缀索引(prefix index)。 
    前缀索引的优缺点： 
    优点 
    在索引满足一定的区分度的情况下，索引变得更小，更有利于放入或将更多的索引放入内存，减少I/O操作，提高效率。 
    缺点 
    前缀索引不支持covering index和order by 。 
    举例说明下：假如表account上有如下索引 (balance,customer_email(50),account_number); 
   其中字段customer_email的定义为varchar(100)，那如下的两个SQL并不能完全使用该索引。 

Java代码  

1. select account_number   
2. from account   
3. where balance=100.1 and customer_email=’1@1.com’;  
4. select account_id   
5. from account   
6. where balance=100.1 and customer_email=’1@1.com’ order by account_number;  

3.1.4.2. 前缀索引适合的字段类型 
    前缀索引一般可以提供高性能所需的选择。如果索引blob和text列，或者很长的varchar列，就必须定义前缀索引，这样既能节约空间同时能得到好的性能。 
int型的不建议使用prefix index, 虽然可以提升效率，但是却不能使用order by, covering index等， 建议使用更小的数字类型如tinyint,bit等来满足。 

3.1.4.3. 前缀索引的合理长度选择 
    前缀索引涉及索引到底建多长的选择。短的索引可以节约空间。但是前缀又应该足够长，使他的选择性能够接近索引整个列，因此前缀的基数性应该接近于全列的基数性。 
    
    设计索引的时候结合记录数、字符集大小、字段长度、字段内容的重复程度、字符之间的相关性等考虑索引长度，索引长度不当将使索引过于庞大， 内存资源利用不高， 造成 IO较重，程序效率降低。合理的索引长度，可以在满足较好索引区分度的情况下减少索引所占空间，我们的目标就是找到索引空间大小与索引区分度的一个平衡点。 

    选择索引长度的方法： 

    1.首先了解表中记录的总体情况，如果表中数据还不存在或者很少，应该通过了解业务去构造和模拟符合业务和产品特点的数据，使用这些数据来模拟上线后的真实数据。 

    2.show table status\G； 
      能看到 avg_row_length (每行的平均长度, 不准确) 、rows(不准确) 、data所占空间、已有索引所占空间等信息。 

    3.select count(*) from table；查看准确的总体行数。 

    4.查看欲建立索引的字段的总体情况 

    5.通过select * from t procedure analyse()\G; 
    能看到表中所有字段的min_value 、max_value 、min_length 、max_length 、是否为null、字段平均长度、字段类型优化建议等信息。其中字段长度的相关信息很重要，它给出了字段的大致信息，对索引长度的选择很有帮助， 而字段类型优化则是在已有内容基础上给出的类型优化， 
    例如：如果你的表中有1000万行， 字段name为字符串， 但是却只有”a”,”b”,”c”三个值，则会建议优化字段类型为enum(“a”,”b”,”c”), 这样查询和索引效率都会大大提高。 

    6.查看欲建立字段的最佳索引区分度，select count(distinct city)/count(*) from city_demo；是该字段全部内容长度都做索引能达到的最理想的区分度，这个首先可以用来衡量该字段是否适合做索引。 

    7.看不同索引长度的区分度, 这个是个平均值例如： 
    

Java代码  

1. Select count(distinct left(city, 3))/count(*) as sel3,        
2. Count(distinct left(city, 4))/count(*) as sel4,  
3. Count(distinct left(city, 5))/count(*) as sel5,          
4. Count(distinct left(city, 6))/count(*) as sel6,          
5. Count(distinct left(city, 7))/count(*) as sel7         
6. From city_demo;  
7.       

   8.查看到city字段做3个字节索引、4个字节索引、5个字节索引、6个字节索引、7个字节索引的区分度, 可以一直增加索引长度来探测结果。 

    9.如果随着索引长度的增加， 索引区分度在很明显地增大， 那说明我们应该继续增加索引长度，使当我们增加索引长度时，索引区分度没有明显变化，我们仍然应该继续增加索引长度探测。 

    10.那么探测到何时为止呢？当我们发现继续增加很多索引长度但是区分度却没有明显提升而现有区分度接近第3条中的最佳区分度时，这个时候的索引长度可能就比较合理了。 

    11.截止上面的步骤， 我们找的都是平均分布，有可能出现的是平均区分度很好而少量数据集中出现区分度极差的情况, 所以我们还需要查看一下区分度分布是否均匀。 

    12.查看区分度是否均匀： 

Java代码  

1. select count(*) as cnt,city   
2. from city_demo   
3. group by city    
4. order by cnt desc limit 100;  
5.   
6. select count(*) as cnt,left(city,3) as pref   
7. from city_demo   
8. group by pref   
9. order by cnt desc limit 100;  
10.   
11. select count(*) as cnt,left(city,4) as pref   
12. from city_demo   
13. group by pref   
14. order by cnt desc limit 100;  
15.   
16. select count(*) as cnt,left(city,5) as pref   
17. from city_demo   
18. group by pref   
19. order by cnt desc limit 100;  

    13.索引选择的最终长度应该在平均区分度(前4条)与区分度是否均匀(第5条)之间长度做一个综合的选择。 

    14.建完索引后 show table status 查看索引大小。这是一个收尾且非常重要的工作， 我们必须清楚的知道建立这个索引的代价。 

    3.1.5.全文检索 
    3.1.5.1.全文检索介绍 

    大部分的查询都可能有where语句，用于比较相等性，过滤数据等。但是有时也需要执行关键字搜索，它基于数据的关联性，而不是相互比较。全文检索就是为这个目的设计的。 
    
    MySQL中只有MyISAM存储引擎支持全文索引。可以在上面搜索基于字符的内容(char，varchar和text列)，并且它支持自然语言搜索和布尔搜索。 
    
    MyISAM全文索引操作了一个全文集合，它由单个表中的一个或者多个字符列组成。实际上，MySQL在集合中通过联接列构造索引，并且把他们当成很长的字符号串进行索引。 

    MyISAM全文索引是一种特殊的具有两层结构的B树。第一层保存了关键字，然后对每个关键字，第2层包含了一个列表，它由相关的文档指针组成，这些指针指向包含该关键字的全文集合。索引不会包含集合中的每一个词。它按照下面的方式进行调整： 
    
    一个停用字清单把无意义的词剔除了，这样它们就不会被索引。停用字列表基于常用的英语语法，但是可以使用ft_stopword_file选项用一个外部列表替换掉它。 

    除非一个词长度大于ft_min_word_len并且小于ft_max_word_len，否则它就会被忽略。 

    全文索引没有存储关键字发生的列信息，所以如果要对组合的列进行搜索，就要创建多个索引。 

3.1.5.2. 布尔全文搜索 

    在布尔搜索中，查询自身定义了匹配单词的相对相关性。布尔搜索使用了停用词表(stopword list)来过滤无用的单词，但是要禁用单词的长度必须大于ft_min_word_len且小于ft_max_word_len这一选项。 

   布尔搜索的结果是没有排序的。但是可以使用前缀来修改搜索字符串每个关键词的相对排名。常用的修饰符见下表： 

示例 含义 
dinosaur 含有“ dinosaur”的行排名较高 
~ dinosaur 含有“dinsaur”的行排名较低 
+ dinosaur 行必须含有“dinosaur” 
- dinosaur 行不能含有“dinsaur” 
dino* 含有以“dino”打头的单词的行排名较高 
布尔全文搜索实际不需要全文索引。如果有全文索引的话，他就会使用索引，如果没有的话，它就会扫描整个表。甚至可以对多个表使用布尔全文搜索。 

3.1.6. 空间(R-Tree)索引 
    R-Tree 索引可能是在其他数据库中很少见的一种索引类型，主要用来解决空间数据检索的问题。 

3.1.6.1. R-Tree索引介绍 
    
    在 MySQL 中，支持一种用来存放空间信息的数据类型geometry，且基于 OpenGIS 规范。在MySQL 5.0.16之前的版本中，仅MyISAM 存储引擎支持该数据类型，但是从 MySQL 5.0.16版本开始，BDB、InnoDB、NDBCluster 和 Archive 存储引擎也开始支持该数据类型。当然，虽然多种存储引擎都开始支持 geometry 数据类型，但是仅仅之后的 MyISAM 存储引擎支持 R-Tree 索引。 

    在 MySQL 中采用了具有二次分裂特性的 R-Tree 来索引空间数据信息，然后通过几何对象(MRB)信息来创建索引。 

    虽然只有 MyISAM 存储引擎支持空间索引(R-Tree Index)，但是如果是精确的等值匹配，创建在空间数据上面的 B-Tree 索引同样可以起到优化检索的效果，空间索引的主要优势在于使用范围查找的时候，可以利用R-Tree 索引，而B-Tree 索引就无能为力了。
3.2.索引的合理设计和使用 
3.2.1.索引的字段及长度 
3.2.1.1.主键和候选键上的索引 

    在create table语句中，我们可以指定主键和候选键。主键和候选键都有unique约束，这些列不会包含重复值。MySQL自动为主键和每个候选键创建一个唯一索引，以便新值的唯一性可以很快检查，而不必扫描全表。同时加速对于这些列上确定值的查找。主键的索引名为prmariy，候选键的名为该键包含的第一列的列名。如果存在多个候选键的名字以同一个列名开头，就在该列明后放置一个顺序号码区别。 

3.2.1.2. 连接列上创建索引 

   一般会在表的连接列上建立索引，尤其是该表频繁参与连接操作。对于一个比较大的连接操作，如果被驱动表的连接列上没有索引的话，由于MySQL的连接算法是nested loop算法，会造成多次扫描被驱动表，对数据库造成的压力和开销是巨大的。 

3.2.1.3. 在高选择度的列上创建索引 
    属性列上的选择度是指该列所包含的不重复的值和数据表中总行数(T)的比值，它的比值在1到1/T之间。选择度越大，越适合建索引。因为对于要查找这个列上的一个值的行，通过索引可以过滤掉大部分数据行，剩下的符合要求的行数较少，可以快速在数据表中定位这些行。相反，如果列的选择度比较小，通过索引过滤后的行数依然很大，和全表扫描的开销没有明显的改善，甚至会更大(全表扫描带来的是顺序I/O，而通过索引过滤后的扫描可能是随机I/O)。因此，在选择索引列时的首要条件就是候选列的选择度。索引要建立在那些选择度高的索引上，在选择度低的列上尽量避免建索引。 

3.2.1.4. 创建联合索引的选择 
    在很多时候，where子句中的过滤条件并不是只针对某一个字段，经常会有多个字段一起作为查询过滤条件存在于where子句中。在这时候，就需要判断是该仅仅为过滤性最好的(选择度最大)的列建立索引，还是在所有过滤条件中所有列上建立组合索引。对于这个问题，需要衡量两种方案各自的优劣。当where子句中的这些字段组成的联合索引过滤性远大于其中过滤性最高的单列，就适合建联合索引。这样就意味着where子句中对应的每个列过滤性都不高，但是这些单列的过滤性乘在一起后过滤性就高了。 
    
   例如：要从存储着学籍信息的表中查找来自中国，大连的女性学生，使用的SQL的where子句如下： 
   where country = ‘china’and city = ‘dalian’ and gender = female； 
   country和city的联合（country，city）的选择性会比country和city各自的选择性高，同时因为gender本身的选择性低，将其加入对于提高总体选择性贡献不大，所以在此情境下适合建立（country，city）的联合索引。 
   同时从性能角度讲，MySQL使用联合索引比使用index_merge算法来使用各个单列索引的效率要高，性能要好。因此对于经常一起出现在where子句中的过滤条件组合，优先考虑建立这些条件列的联合索引，而不是为每个单列建立索引。 

3.2.1.5. 通过索引列属性的前缀控制索引的长度 

    索引占用的空间越小，对于MySQL获得高性能越有益。不管是什么类型的索引，在查询中使用都是需要从磁盘中加载到内存中去的，无论是MyISAM对应的key cache，还是InnoDB对应的buffer pool。这些受到程序自身和硬件条件限制都是有大小限制的，如果索引大小比较大的话，会造成这些存放索引的内存区域无法存下整个索引数据，根据LRU算法频繁地淘汰索引，加载新的索引进去，这就造成比较大的I/O开销。 

    如果要建索引的列是很长的字符串的话，它会使索引变大。如果大小超过限制的话，可以考虑建前缀索引，即只索引数据列中存储的数据的前几个字符，而不是全部的值，这样可以有效地减小索引的大小。当然这样做的前提是保证索引的选择性。在选择列上要索引的字符长度时，考虑选择性不能只看平均值，还要考虑最坏情况下的选择性。因为使用前缀索引而索引的字符数不足的话，容易造成数据分布不均匀。如果这种情况比较极端，可能会造成索引的作用下降。 

3.2.2.如何在操作中利用索引 
3.2.2.1.索引与排序操作 
    
    MySQL有两种产生排序结果的方式：使用文件排序(filesort)，或者使用扫描有序的索引。explain的输出中type列的值为“index”，这说明MySQL会扫描索引。 
    
    MySQL能为排序和查找行使用同样的索引。如果可能，按照这样一举两得的方式设计索引是个好主意。按照索引对结果进行排序，只有当order by子句中的顺序和索引最左前缀顺序完全一致，并且所有列排序的方向(升序或降序)一样才可以。order by无需定义索引的最左前缀的一种情况是索引中其它前导列在where子句中为常量。如果查询联接了多个表，只有在order by子句的所有列引用的是第一个表才可以。 

3.2.2.2. 索引与分组操作 
    
    group by实际上也同样需要进行排序操作，而且与order by相比，group by主要只是多了排序之后的分组操作。当然，如果在分组时还是用了其他一些聚合函数，还需要一些聚合函数的计算。所以在group by的实现过程中，与order by一样可以使用索引。同时使用索引带来的性能提升是巨大的。 
    
    在MySQL中group by使用索引的方式有两种： 
    使用松散(loose)索引扫描；使用紧凑索引扫描。 
    
    松散索引扫描实现group by是指MySQL完全利用索引扫描来实现group by时，并不需要扫描所有满足条件的索引键即可完成操作，得出结果。如果MySQL使用了这种方式，则在explain的extra行会出现“using index for group-by”。要利用到松散索引扫描实现group by，需要至少满足以下几个条件： 
    1.group by条件列必须处在同一个索引的最左连续位置； 
    2.在使用group by同时，只能使用max和min这两个聚合函数； 
    3.如果引用到了该索引中group by条之外的列条件，它就必须以常量形式出现。 
    紧凑索引扫描实现group by是指MySQL需要在扫描索引时，读取所有满足条件的索引键值，然后再根据读取到的数据来完成group by操作，已得到相应的结果。这时的执行计划中就不会出现“using index for group-by”。使用这种索引扫描方式要满足的条件是：group by条件列必须是索引中的列，同时索引中位于该条件列左边的列必须以常数的形式出现在where子句中。 
    
    除了上述两种使用索引扫描来完成group by外，还可以使用临时表加filesort实现。但是这种方式带来的性能开销比较大，一般也比较费时。所以group by最好实现方式是松散索引扫描，其次是紧凑索引扫描，最后是使用临时表和filesort。 

3.2.2.3. 索引与求distinct查询 
    
    distinct实际上和group by操作非常相似，只是在group by之后的每组中只取其中一条记录而已。所以，distinct的实现方式和group by也基本相同。同样通过松散索引扫描或者紧凑索引扫描的方式实现要优于使用临时表实现。但在使用临时表时，MySQL仅是使用临时表缓存数据，而不需要进行排序，也就省了filesort操作。 

3.2.2.4. 索引与带有limit子句的查询 
    
    含有limit子句的查询往往同时含有order by子句(如果没有order by子句则优化方法和普通查询一样)。这样的查询最好在排序时使用索引扫描进行排序。否则即使limit子句中只取排序后起始部分很少的数据都会引起MySQL取出全部符合条件的数据进行排序。如果使用索引扫描的话，则不需要对所有数据排序，只需扫描索引取出满足limit限制的数据即可。 
同时对于limit子句中的大偏移量的offset，比如limit 10000,20，它就会产生10020行数据，并且丢掉前10000行。这个操作的代价太大。一个提高效率的简单技巧是在覆盖索引上进行偏移，而不是对全行数据进行偏移。也可以将从覆盖索引上提取出来的数据和全表数据进行连接，然后取得需要的数据。 
3.2.2.5. 索引与连接操作 
    在MySQL中，只有一种连接join算法即nested loop join。该算法就是通过驱动表的结果集作为循环的基础数据，然后将该结果集中的数据作为过滤条件一条条地到下一个表中查询数据，最后合并结果。所以在通过结果集中的数据作为过滤条件到下一个表中地位数据时，最好是通过索引，而不是扫表。因为如果结果集中的数据比较多，要是每次都通过扫描来定位的话，造成的开销和对MySQL的压力是巨大的。因此，最好在被驱动表的连接列上建立索引，并且使MySQL在连接过程中使用索引。 

3.2.2.6. 索引与隔离列 
   
    如果在查询中没有隔离索引的列，MySQL通常不会使用索引。“隔离”列意味着它不是表达式的一部分，也没有位于函数中。 
    例如，下面的查询不能使用actor_id上的索引： 
    select account_id from account where account_id+1=5; 
    人们能轻易地看出where子句中的actor_id等于4，但是MySQL却不会帮你求解方程。应该主动去简化where子句，把被索引的列单独放在比较运算符的一边。 
    下面是另外一种常见的问题： 
    
    select expenditure from consume where to_days(current_date)-to_days(consume_time) <=10; 
    
   这个查询将会查找date_col值离今天不超过10的所有行，但是它不会使用索引，因为使用了to_days()函数。下面是一种比较好的方式： 
    
   select expenditure from consume where consume_time>= date_sub(current_date,interval 10 day) ; 

   这个查询就可以使用索引，但是还可以改进。使用current_date将会阻止查询缓存把结果缓存起来，可以使用常量替换掉current_date的值： 
    
   select expenditure from consume where consume_time>= date_sub('2010-12-12',interval 10 day); 

3.2.3.索引创建的建议 
3.2.3.1. 对联合索引中包含属性列的选择 

    对于where子句中过滤条件涉及的属性列大致相同的一系列SQL建立共同的索引。如果共同涉及的属性列是多个的话，则应建立联合索引。在确定联合索引应该包含这些共同涉及的属性列中的哪些时，应该考察这些WHERE子句对于涉及这些列上的过滤条件的形式。对于那些是范围条件对应的列，由于B-Tree索引本身的限制，只能选取其中一个选择度比较高的列进入联合索引。而对于那些等值条件对应的列，原则上都可以进入联合索引，但是需要综合考虑联合索引最后的大小和进入索引的列的选择度。如果属性列的选择度非常低的话，把它放入索引对于联合索引的选择度贡献比较小，但是会增大索引大小，引起其它开销。所以不要把这样的列加入到索引中去。如3.2.1.4节中提到的例子，gender列的选择性较低，加入联合索引对于提高联合索引的选择性没有太大帮助，但却增加了联合索引的大小。 

3.2.3.2. 正确创建联合索引中各列的顺序 

    对于MySQL普遍使用的B-Tree索引，索引列的顺序对于SQL使用该索引至关重要。如果索引中列的顺序不合理，在使用过程中往往会使该索引无法被使用或者通过该索引得到的过滤能力大大减弱。 
    
    首先由于B-Tree索引的数据结构限制，只有当SQL的where子句使用索引的最左前缀的时候，索引才能被使用、发挥作用。所以在创建索引、决定索引的顺序时，应提取希望使用该索引SQL的where子句中的过滤条件，提炼出其中的最常出现的条件和其对应的属性列。按照这些列的选择度由高到低排列这些属性列，按照这个顺序创建这个索引。同时相关SQL的where子句中出现的过滤条件顺序，以尽量让这些SQL可以使用建立的索引的最左前缀。 
对于联合索引中包含的属性列中，有一列对应在相关SQL的where子句的过滤条件是以范围条件出现，而索引中其他属性列是以等于条件出现，则应该把这些等值条件对应的列放在索引的前面，把范围条件对应的列放在索引的最后。 

    select account_id from consume where account_payee =72478814 and expenditure>1.00; 
    为上述SQL创建对应的联合索引时：如果创建索引(expenditure,account_payee)，由于expenditure列上是范围条件，所以索引(expenditure,account_payee)无法使用完全(只能使用索引中的expenditure部分)；如创建索引(account_payee, expenditure)，SQL则可以完全使用此索引。所以针对上述SQL应该创建联合索引(account_payee, expenditure)。 

3.2.3.3. 避免重复索引 
    MySQL允许你在同一列上创建多个索引，它不会注意到你的错误，也不会为错误提供保护。MySQL不得不单独维护每一个索引，并且查询优化器在优化查询的时候会逐个考虑它们，这会严重影响性能。重复索引是类型相同，以同样的顺序在同样的列上创建的索引。应该避免创建重复索引，并且在发现它时把它移除掉。 
有时会在不经意间创建重复索引。例如下面的代码： 
    create table test( 
       id int not null primary key， 
        unique(id)， 
        index(id) 
    )； 
   对于id列，首先它是primary key，同时unique(id)使MySQL自动为id创建了名为id的索引，最后index(id)，现在给id列创建了三个索引。这通常是不需要的，除非需要为同一列建立不同类型的索引，如B-Tree，fulltext等类型索引。 

3.2.3.4. 避免多余索引 
    
   多余索引和重复索引不同。例如列(A，B)上有索引，那么另外一个索引(A)就是多余的。也就是说(A，B)上的索引能被当成索引(A)(这种多余只适合B-Tree索引)。多余索引通常发生在向表添加索引的时候，例如，有人也许会在(A,B)上添加索引，而不是对索引(A)进行扩展。 
   对于B-Tree类型索引，有单列索引对应的属性列出现在了某个联合索引的第1位置上，那么这个单列索引可能是多余的。 
如果某一索引是主键的超集，那么这个索引除非有特殊理由(如希望使用覆盖索引)，否则也是多余索引。因为主键是唯一索引，过滤能力很强，和它建立联合索引意义不大。 
在大部分情况下，多余索引都是不好的，为了避免它，应该扩展已有索引，而不是添加新的索引。但是，还有一些情况出于性能考虑需要多余索引。使用多余索引的主要原因是扩展已有索引的时候，它会变得很大。 

3.2.3.5. 使用覆盖索引 
  
    索引是找到行的高效方式，但是MySQL也能使用索引来接收数据，这样就可以不用读取行数据。 
  
   包含所有满足查询需要的数据的索引叫覆盖索引。 
   
    覆盖索引和任何一种索引都不一样。覆盖索引必须保存它包含的列的数据。MySQL只能使用B-Tree索引来覆盖查询。 
在SQL执行中要使用覆盖索引的话，需要相应的索引包含SQL中where子句中涉及的列都在索引中且满足最左前缀，同时SQL的返回列也必须在索引中。同是where子句中的过滤条件中不能包含like等操作符和函数。MySQL使用了覆盖索引，会在explain输出中出现“using index”字样。 

    有关使用覆盖索引的案例请参见第4章4.2.3.6使用covering index优化select语句。 

3.2.4. 索引的维护 

3.2.4.1. 数据的optimize和analyze操作 

     MySQL查询优化器在决定如何使用索引的时候会调用两个API，以了解索引如何分布。第一个调用接受范围结束点并且返回该范围内记录的数量；第二个调用返回不同类型的数据，包括数据基数性(每个键值有多少记录)。当存储引擎没有向优化器提供查询检查的行的精确数量的时候，优化器会使用索引统计来估计行的数量，统计可以通过运行analyze table重新生成。MySQL的优化器基于开销，并且主要的开销指标是查询会访问多少数据。如果统计永远没有产生，或者过时了，优化器就会做出不好的决定。解决方案是运行analyze table。 
每个存储引擎实现索引统计的方式不同，由于运行analyze table的开销不同，所以运行它的频率也不一样。 

    1.Memory存储引擎根本就不保存索引统计。 
    2.MyISAM把索引统计保存在磁盘上，并且analyze table执行完整的索引扫描以计算基数性。整个表都会在这个过程中被锁住。 

    3.InnoDB不会把统计信息保存到磁盘上，同时不会时时去统计更新它们，而是在第一次打开表的时候利用采样的方法进行估计。InnoDB上的analyze table命令就使用了采样方法，因此InnoDB统计不够精确，除非让服务器运行很长的时间，否则不要手动更新它们。同样，analyze table在InnoDB上不是阻塞性的，并且相对不那么昂贵，因此可以在不大影响服务器的情况下在线更新统计。 

    B-Tree索引能变成碎片，它降低了性能。碎片化的索引可能会以很差或非顺序的方式保存在磁盘上。同是表的数据存储也能变得碎片化。碎片化对于数据的读取，尤其是范围数据的读取，会使读取速度慢很多。为了消除碎片，可以运行optimize table解决。 

3.2.4.2. 索引的修复——MyISAM 
   
   MyISAM引擎在下列情况下可能会出现数据和索引不一致的情况，出现索引错误或者数据错误： 
   1.MySQL进程在写中间被杀掉； 
   2.发生未预期的计算机关闭； 
   3.硬件故障； 
   4.可能同时在正被server程序修改的表上使用外部程序(如myisamcheck)； 
   5.MySQL或MyISAM代码的缺陷； 

   一个损坏的表的典型症状如下： 
   incorrect key file for table：’…’.try to repair it. 
   遇到MyISAM出现数据和索引不一致的情况时，可以用 check table语句来检查MyISAM表的健康，并用repair table修复。当MySQL不运行时，也可以使用myisamcheck命令检查或修复这个问题。 

3.2.5. 索引的其他说明 
3.2.5.1. 索引对插入、更新的影响和避免 

    索引是独立于基础数据之外的一部分数据。 
    假设在table t中的column c创建了索引idx_t_c，那么任何更新column c的操作包括插入insert，update， 
    MySQL在更新表中column c的同时，都必须更新column c上的索引idx_t_c数据，调整因为更新带来键值变化的索引信息。 
    而如果没有对column c建立索引，则仅仅是更新表中column c的信息就可以了。 
    这样因调整索引带来的资源消耗是更新带来的I/O量和调整索引所致的计算量。 
    基于以上的分析，在更新非常频繁地字段不适合创建索引。 
    很多时候是通过比较同一时间内被更新的次数和利用该列作为条件的查询次数来判断的，如果通过该列的查询并不多，可能几个小时或者更长时间才会执行一次，更新反而比查询更频繁，那么这样的字段肯定不适合创建索引。 

转载于:https://www.cnblogs.com/blueoverflow/p/4741080.html