高性能Mysql读书笔记(2)- Mysql 的索引

 

一：总体说明

         Mysql的索引是在存储引擎层而不是服务层实现的，即多个存储引擎支持同一类型的索引，其底层实现不一定一样。默认情况下Mysql索引都是指B-Tree索引.但InnoDB存储数据时会按照聚簇索引（即B+树）的方式存储，在此基础上我们可以再建二级索引（除开聚簇索引，建在表上的其他索引都成为二级索引，在innodb 里面，所有的二级索引都包含了主键列）

 

  二：

1. B-Tree索引 说明

 一位大牛的文章

http://www.cnblogs.com/v-July-v/archive/2011/06/07/2075992.html

 

  2. 举例说明 B-Tree索引的限制

假设有如下数据表：

Create table People（
      last_name           varchar(50)  not null,

      first_name           varchar(50)  ,

      dob                       date,

      gender                  enum('m','f') not null,

     key(last_name,first_name,dob)   

）

 

 2.1  如果不是按照索引的最左列开始查找，则无法使用索引。例如上面的例子无法查找名字为bill的人，也无法查找某个特定生日的人。

 2.2 不能跳过索引中的列，例如不能利用索引查找名字为smith且指定日期出生的人。如果不指定first_name则mysql只能使用索引的第一列。

 2.3 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。例如有查询where last_name='smith' and first_namelike '%J%'

        and dob ='1978' 这个查询只能使用索引的前两列，因为like是范围条件。

 

 

三：聚簇索引

聚簇索引如下图为聚簇所有的存储方式，聚簇实际不是一种索引，而是一种数据的存储方式，InnoDB的聚簇事假在同一个结构中保存了B-Tree索引和数据行。

   当表有聚簇索引时，他的数据行实际存在放叶子页。InnoDb通过主键聚集数据，如果没有定义主键则InnoDB会选择一个唯一的非空索引代替。

 1.聚簇索引的特性

1.1当表存在主键时，Innodb 使用 主键作为聚簇索引

1.2当表没有主键时，Innodb 使用第一个唯一约束索引（这个唯一索引必须是不包含null列的）作为聚簇索引

1.3当表没有主键，也没有合适的唯一索引时，Innodb隐含创建一个包含rowid 的聚簇索引

 

 下面展示了聚族索引中的记录是如何存放的。注意到，叶子页包含了行的全部数据，但是节点页只包含了索引列。

2.    聚族索引的优点

• 可以把相关数据保存在一起。例如实现电子邮件时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚族索引，则每封邮件都可能导致一次磁盘I/O；

• 数据访问更快。聚族索引将索引和数据保存在同一个B-Tree中，因此从聚族索引中获取数据通常比在非聚族索引中查找更快。
• 使用覆盖索引扫描的查询可以直接使用节点中的主键值。

3  聚族索引的缺点

• 聚簇数据最大限度的提高了I/O密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没有那么重要了，聚簇索引也就没有那么优势了；
• 插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。但如果不是按照主键顺序加载数据，那么在加载完成后最好使用OPTIMIZE TABLE命令重新组织一下表。
• 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。
• 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次分裂操作。页分裂会导致表占用更多的磁盘空间。

• 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。

• 二级索引（非聚簇索引）可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。

• 二级索引访问需要两次索引查找，而不是一次。

 

4. InnoDB和MyISAM的数据分布对比

    聚簇索引和非聚簇索引的数据分布有区别，以及对应的主键索引和二级索引的数据分布也有区别。

4.1、MyISAM的主键索引和二级索引

    MyISAM的数据分布非常简单，MyISAM按照数据插入的顺序存储在磁盘上。在行的旁边显示了行号，从0开始递增。因为行是定长的，所以MyISAM可以从表的开头跳过所需的字节找到需要的行。这种分布方式很容易创建索引。并且，MyISAM中主键索引和其他索引在结构上没有什么不同。主键索引就是一个名为primary的唯一非空索引。如下图：

1、MyISAM数据行分布

2、MyISAM的主键分布

3、MyISAM上的其他索引分布

4.2、InnoDB的主键索引和二级索引

    InnoDB的数据分布，因为InnoDB支持聚簇索引，索引使用非常不同的方式存储这样的数据，如下图：

    仔细查看，会注意到该图显示了整个表，而不是只有索引。因为在InnoDB中，聚簇索引“就是”表，所以不像MyISAM那样需要独立的行存储。聚簇索引的每个叶子节点都包含了主键值、事务ID、用于事务和MVCC的回滚指针以及所有的剩余列。如果主键是一个列前缀索引，InnoDB也会包含完整的主键列和剩下的其他列。

    还有一点和MyISAM的不同是，InnoDB的二级索引和聚簇索引很不相同。InnoDB二级索引的叶子节点中存储的不是“行指针”，而是主键值，并以此作为指向行的“指针”。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值当作指针会让二级索引占用更多的空间，换来的好处是，InnoDB在移动行时无需更新二级索引中的这个“指针”。下图就是InnoDB的二级索引：

4.3、MyISAM和InnoDB的对比

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

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

    最好避免随机的聚簇索引，特别对于I/O密集型的应用。例如，从性能的角度考虑，使用UUID作为聚簇索引会很糟糕：它使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性。通过测试，向UUID主键插入行不仅花费的时间更长，而且索引占用的空间也更大。这一方面是由于主键字段更长，另一方面毫无疑问是由于页分裂和碎片导致的。

    这是由于当主键的值是顺序的，则InnoDB把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时（InnoDB默认的最大填充因子是页大小的15/16，留出的部分空间用于以后修改），下一条记录就会写入新的页中。一旦数据按照这样顺序的方式加载，主键页就会近似于被顺序的记录填满，这也是所期望的结果。

    而当采用UUID的聚簇索引的表插入数据，因为新行的主键值不一定比之前的插入值大，所以InnoDB无法简单的总是把新行插入到索引的最后，而是需要为新的行寻找合适的位置----通常是已有数据的中间位置----并且分配空间。这会增加很多额外的工作，并导致数据分布不够优化。下面是总结的一些缺点：

• 写入目标页可能已经刷到磁盘上并从缓存中移除，或者是还没有被加载到缓存中，InnoDB在插入之前不得不先找到并从磁盘读取目标页到内存中，这将导致大量的随机I/O；
• 因为写入是乱序的，InnoDB不得不频繁的做页分裂操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页。
• 由于频繁的页分裂，页会变得稀疏并被不规则的填充，所以最终数据会有碎片。
• 把这些随机值载入到聚簇索引以后，需要做一次optimize table来重建表并优化页的填充。

注意：顺序主键也有缺点：对于高并发工作负载，在InnoDB中按主键顺序插入可能会造成明显的争用。主键的上界会成为“热点”。因为所有的插入都发生在这里，所以并发插入可能导致间隙锁竞争。另一个热点可能是auto_increment锁机制；如果遇到这个问题，则可能需要考虑重新设计表或者应用，或者更改innodb_autonc_lock_mode配置。

6.其他说明

1.覆盖索引

 一个索引包含（或者说覆盖）所有需要查询的字段，我们成为覆盖索引。

 

2.使用索引扫描来排序

Mysql有两种方式可以生成有序的结果：通过排序操作；或者按照索引顺序扫描。如果Explain出来的type列值为“index”则说明mysql使用索引扫描来做排序。

只有当索引的顺序和Order By子句的顺序完全一致，并且所有列的排序方向（倒序或者正序）都一样时，Msyql才能使用索引对结果进行排序。

 

3. 维护索引和表

1.如果碰到古怪的问题，或者莫须有的主键冲突，可以运行 check table来查看表是否有损坏，如果有损坏可以通过repair table 来修复表，也可以通过一个不做任何操作的alter命令来重建表，例如修改表的引擎为当前引擎。