[MySQL] [索引与优化]

• 写在前面：索引对查询的速度有着至关重要的影响，理解索引也是进行数据库性能调优的起点。
  考虑如下情况，假设数据库中一个表有10^6条记录，DBMS的页面大小为4K，并存储100条记录。如果没有索引，查询将对整个表进行扫描，最坏的情况下，如果所有数据页都不在内存，需要读取10^4个页面，如果这10^4个页面在磁盘上随机分布，需要进行10^4次I/O，假设磁盘每次I/O时间为10ms(忽略数据传输时间)，则总共需要100s(但实际上要好很多很多)。如果对之建立B-Tree索引，则只需要进行log100(10^6)=3次页面读取，最坏情况下耗时30ms。这就是索引带来的效果，很多时候，当你的应用程序进行SQL查询速度很慢时，应该想想是否可以建索引。

入门知识

• MySQL支持很多数据类型，选择合适的数据类型存储数据对性能有很大的影响。通常来说，可以遵循以下一些指导原则：

1. 1. 越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。
   2. 简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址。
   3. 尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。

• 如果对多列进行索引(组合索引)，列的顺序非常重要，MySQL仅能对索引最左边的前缀进行有效的查找。例如：
  假设存在组合索引it1c1c2(c1,c2)，查询语句select * from t1 where c1=1 and c2=2能够使用该索引。查询语句select * from t1 where c1=1也能够使用该索引。但是，查询语句select * from t1 where c2=2不能够使用该索引，因为没有组合索引的引导列，即，要想使用c2列进行查找，必需出现c1等于某值。

索引类型

• 索引是在存储引擎中实现的。所以，每种存储引擎的索引都不一定完全相同，并不是所有的存储引擎都支持所有的索引类型。

B-Tree索引

• 一个b-tree组合索引的例子：(其索引包含表中每一行的last_name、first_name和dob列)
  
  [其实就是把每一行的各个索引列按顺序组合起来，然后放到b-tree]
• 索引存储的值按索引列中的顺序排列。
• 如果想使用索引，你必须保证按索引的最左边前缀(leftmost prefix of the index)来进行查询。 [不能跳过某一列，范围查询后面的索引列不能被当做索引使用]
• 支持：
  • 匹配全值
  • 匹配最左前缀
  • 匹配列前缀(例如，你可以利用索引查找last name以J开始的人，这仅仅使用索引中的第1列。)
  • 范围查询
  • 仅对索引进行查询(Index-only queries)：如果查询的列都位于索引中，则不需要读取元组的值。
• 由于B-树中的节点都是顺序存储的，所以可以利用索引进行查找(找某些值)，也可以对查询结果进行ORDER BY。

Hash索引

• MySQL中，只有Memory存储引擎显示支持hash索引，是Memory表的默认索引类型。
• Memory存储引擎支持非唯一hash索引，这在数据库领域是罕见的，如果多个值有相同的hash code，索引把它们的行指针用链表保存到同一个hash表项中。
• 索引的结构demo
  
• 可以看到，索引不存储具体的值(不同于b-tree)，所以非常紧凑。 [Hash值不取决于列的数据类型，一个TINYINT列的索引与一个长字符串列的索引一样大。]
• 限制：
  • 由于索引仅包含hash code和记录指针，所以，MySQL不能通过使用索引避免读取记录。
  • 不能使用hash索引排序
  • Hash索引不支持键的部分匹配，因为是通过整个索引值来计算hash值的。
  • Hash索引只支持等值比较

R-Tree索引

• MyISAM支持空间索引，主要用于地理空间数据类型，例如GEOMETRY。

全文索引

• 全文索引是MyISAM的一个特殊索引类型，主要用于全文检索。

高性能的索引策略

聚簇索引

• Clustered Indexes: 聚簇索引保证关键字的值相近的元组存储的物理位置也相同（所以字符串类型不宜建立聚簇索引，特别是随机字符串，会使得系统进行大量的移动操作），且一个表只能有一个聚簇索引。
• 目前，只有solidDB和InnoDB支持。
• 聚簇索引的结构大致如下：
  
• 一些DBMS允许用户指定聚簇索引，但是MySQL的存储引擎到目前为止都不支持。
• InnoDB对主键建立聚簇索引。如果你不指定主键，InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引，InnoDB会定义一个隐藏的主键，然后对其建立聚簇索引。
• 一般来说，DBMS都会以聚簇索引的形式来存储实际的数据，它是其它二级索引的基础。

InnoDB和MyISAM的数据布局

• 为了更加理解聚簇索引和非聚簇索引，或者primary索引和second索引(MyISAM不支持聚簇索引)，来比较一下InnoDB和MyISAM的数据布局。[MyISAM不支持聚簇索引]
• demo table
  

  CREATE TABLE layout_test (
  
     col1 int NOT NULL,
  
     col2 int NOT NULL,
  
     PRIMARY KEY(col1),
  
     KEY(col2)
  
  );

• MyISAM
  • 布局十分简单，MyISAM按照插入顺序在磁盘上存储数据，如下：
    
  • 因为元组的大小固定，所以MyISAM可以很容易地从表的开始位置找到某一字节的位置。
  • primary key的索引结构大致如下：
    
  • MyISAM不支持聚簇索引，索引中每一个叶子节点仅仅包含行号(row number)，且叶子节点按照col1的顺序存储。  [这里感觉是b+ tree的结构吧 = =]
  • 再看col2的索引结构
    
  • 实际上，在MyISAM中，primary key和其它索引没有什么区别。Primary key仅仅只是一个叫做PRIMARY的唯一，非空的索引而已。
• InnoDB的数据布局
  • InnoDB按聚簇索引的形式存储数据，所以它的数据布局有着很大的不同。
  • 存储结构大致如下
    
  • 注：聚簇索引中的每个叶子节点包含primary key的值，事务ID和回滚指针(rollback pointer)——用于事务和MVCC，和余下的列(如col2)。
  • 相对于MyISAM，二级索引与聚簇索引有很大的不同。InnoDB的二级索引的叶子包含primary key的值，而不是行指针(row pointers)，这减小了移动数据或者数据页面分裂时维护二级索引的开销，因为InnoDB不需要更新索引的行指针。其结构大致如下：  [本质上就是把所有数据按聚簇索引来存储了，而对非聚簇索引，就是先get聚簇索引的值，然后再在btree搜一次]
    
• 聚簇索引和非聚簇索引表的对比：
  

Others

• 按primary key的顺序插入行(InnoDB): 如果你用InnoDB，而且不需要特殊的聚簇索引，一个好的做法就是使用代理主键(surrogate key)——独立于你的应用中的数据。最简单的做法就是使用一个AUTO_INCREMENT的列，这会保证记录按照顺序插入，而且能提高使用primary key进行连接的查询的性能。应该尽量避免随机的聚簇主键，例如，字符串主键就是一个不好的选择，它使得插入操作变得随机。
• 优点：
  • 可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有聚簇索引，则每封邮件都可能多一次磁盘IO。
  • 数据访问更快。聚簇索引将索引和数据保存在同一个B+Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
• 缺点：
  • 插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。但如果不是按照主键顺序加载数据，那么在加载完成后最好使用optimize table命令重新组织一下表。
  • 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。
  • 基于聚簇索引的表插入新行，或者主键被更新导致需要移动行的时候，可能面临"页分裂（page split)"的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次分裂操作。页分裂会导致表占用更多的磁盘空间。
  • 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
  • 二级索引访问需要两次索引查找，而不是一次。

 覆盖索引

• Covering Indexes: 如果索引包含满足查询的所有数据，就称为覆盖索引。覆盖索引是一种非常强大的工具，能大大提高查询性能。
• 只需要读取索引而不用读取数据有以下一些优点：
  • 索引项通常比记录要小，所以MySQL访问更少的数据；
  • 索引都按值的大小顺序存储，相对于随机访问记录，需要更少的I/O；
  • 大多数据引擎能更好的缓存索引。比如MyISAM只缓存索引。
  • 覆盖索引对于InnoDB表尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了

利用索引排序

• MySQL中，有两种方式生成有序结果集：
  • 使用filesort：当MySQL不能使用索引进行排序时，就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序，如果内存装载不下，它会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集（实际上就是外排序）。对于filesort，MySQL有两种排序算法。
    • 两遍扫描算法(Two passes)：实现方式是先将需要排序的字段和可以直接定位到相关行数据的指针信息取出，然后在设定的内存（通过参数sort_buffer_size设定）中进行排序，完成排序之后再次通过行指针信息取出所需的Columns。
      注：该算法是4.1之前采用的算法，它需要两次访问数据，尤其是第二次读取操作会导致大量的随机I/O操作。另一方面，内存开销较小。
    • 一次扫描算法(single pass)：该算法一次性将所需的Columns全部取出，在内存中排序后直接将结果输出。
      注：从 MySQL 4.1 版本开始使用该算法。它减少了I/O的次数，效率较高，但是内存开销也较大。如果我们将并不需要的Columns也取出来，就会极大地浪费排序过程所需要的内存。在 MySQL 4.1 之后的版本中，可以通过设置 max_length_for_sort_data 参数来控制 MySQL 选择第一种排序算法还是第二种。当取出的所有大字段总大小大于 max_length_for_sort_data 的设置时，MySQL 就会选择使用第一种排序算法，反之，则会选择第二种。为了尽可能地提高排序性能，我们自然更希望使用第二种排序算法，所以在 Query 中仅仅取出需要的 Columns 是非常有必要的。
  • 按索引顺序扫描：利用索引进行排序操作是非常快的，而且可以利用同一索引同时进行查找和排序操作。

 

Reference

• 索引与优化