MySql 优化详解（二）高性能的索引

MySql 优化详解（二）高性能的索引

索引

索引是存储引擎用于快速查到记录的一种数据结构。索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

索引基础

• 索引的优点

  • 大大减轻了服务器需要扫描的数据量，从而提高了数据的检索速度
  • 帮助服务器避免排序和临时表
  • 可以将随机 I/O 变为顺序 I/O

• 索引的缺点

  • 在创建索引和维护索引 会耗费时间,随着数据量的增加而增加
  • 索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间
  • 当对表的数据进行 INSERT,UPDATE,DELETE 的时候,索引也要动态的维护,这样就会降低数据的维护速度

• 索引的类型

  • B-Tree索引

    

    m-way查找树:

    性质：

    • 每个节点的键值数小于m
    • 每个节点的度小于等于m
    • 键值按顺序排列
    • 子树的键值要完全小于或大于或介于父节点之间的键值

    假如有表如下：

    CREATE TABLE People (
        last_name varchar(50)    not null,
        first_name varchar(50)    not null,
        dob        date           not null,
        gender     enum('m', 'f') not null,
        key(last_name, first_name, dob)
    );

    其索引包含表中每一行的last_name、first_name和dob列。其结构大致如下：

    

    索引存储的值按索引列中的顺序排列。可以利用 B-Tree 索引进行全关键字、关键字范围和关键字前缀查询，当然，如果想使用索引，你必须保证按索引的最左边前缀 (leftmost prefix of the index) 来进行查询。

    • 匹配全值 (Match the full value)：对索引中的所有列都指定具体的值。例如，上图中索引可以帮助你查找出生于 1960-01-01 的 Cuba Allen 。
    • 匹配最左前缀 (Match a leftmost prefix)：你可以利用索引查找 last name 为 Allen 的人，仅仅使用索引中的第 1 列。
    • 匹配列前缀 (Match a column prefix)：例如，你可以利用索引查找 last name 以 J 开始的人，这仅仅使用索引中的第 1 列。
    • 匹配值的范围查询 (Match a range of values)：可以利用索引查找 last name 在 Allen 和 Barrymore 之间的人，仅仅使用索引中第 1 列。
    • 匹配部分精确而其它部分进行范围匹配 (Match one part exactly and match a range on another part)：可以利用索引查找 last name 为 Allen ，而 first name 以字母 K 开始的人。
    • 仅对索引进行查询 (Index-only queries)：如果查询的列都位于索引中，则不需要读取元组的值。

    由于B-树中的节点都是顺序存储的，所以可以利用索引进行查找(找某些值)，也可以对查询结果进行 ORDER BY 。当然，使用 B-tree 索引有以下一些限制：

    • 查询必须从索引的最左边的列开始。关于这点已经提了很多遍了。例如你不能利用索引查找在某一天出生的人。
    • 不能跳过某一索引列。例如，你不能利用索引查找 last name 为 Smith 且出生于某一天的人。
    • 存储引擎不能使用索引中范围条件右边的列。例如，如果你的查询语句为 WHERE last_name="Smith" AND first_name LIKE 'J%' AND dob='1976-12-23'，则该查询只会使用索引中的前两列，因为 LIKE 是范围查询。

    更多资料：MySql索引算法原理解析 B-tree

  • Hash索引

    MySQL 中，只有 Memory 存储引擎显示支持 hash 索引，是 Memory 表的默认索引类型。InnoDB 可以创建 hash 索引但是实际上仍然是 B-tree 的索引，不过 InnoDB 有还有一种实现方法：自适应哈希索引。InnoDB 存储引擎会监控对表上索引的查找。假设观察到建立哈希索引能够带来速度的提升，则建立哈希索引。参考：警惕 InnoDB 和 MyISAM 创建 Hash 索引陷阱。

    假如有表如下：

    CREATE TABLE testhash (
        fname VARCHAR(50) NOT NULL,
        lname VARCHAR(50) NOT NULL,
        KEY USING HASH(fname)
    ) ENGINE=MEMORY;

    包含数据如下：

    

    索引使用hash函数f()，将返回如下值：

    f('Arjen') = 2323
    f('Baron') = 7437
    f('Peter') = 8784
    f('Vadim') = 2458

    则哈希索引的数据结构：

    

    当你执行：

    SELECT lname FROM testhash WHERE fname='Peter';

    MySQL 会计算’Peter’的 hash 值，然后通过它来查询索引的行指针。因为 f('Peter') = 8784 ，MySQL 会在索引中查找 8784 ，得到指向记录 3 的指针。因为索引自己仅仅存储很短的值，所以，索引非常紧凑。Hash 值不取决于列的数据类型，一个 TINYINT 列的索引与一个长字符串列的索引一样大。

    Hash索引 有以下一些限制：

    • 由于索引仅包含 hash code 和记录指针，所以，MySQL 不能通过使用索引避免读取记录。但是访问内存中的记录是非常迅速的，不会对性造成太大的影响。
    • 不能使用 hash 索引排序。
    • Hash 索引不支持键的部分匹配，因为是通过整个索引值来计算 hash 值的。
    • Hash 索引只支持等值比较，例如使用 =，IN() 和 <=>。对于 WHERE price>100 并不能加速查询。
    • 可能会导致 hash 冲突

  • 空间(R-Tree)索引

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

  • 全文(Full-text)索引

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

• 索引的种类

  • 主键索引
  • 普通索引
  • 唯一索引
  • 全文索引

索引的创建原则

• 选择索引的数据类型

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

  • 越小的数据类型通常更好

    越小的数据类型通常在磁盘、内存和 CPU 缓存中都需要更少的空间，处理起来更快。

  • 简单的数据类型更好

    整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在 MySQL 中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储 IP 地址。

  • 尽量避免NULL

    应该指定列为 NOT NULL ，除非你想存储 NULL 。在 MySQL 中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用 0、一个特殊的值或者一个空串代替空值。

• 选择标识符

  选择合适的标识符是非常重要的。选择时不仅应该考虑存储类型，而且应该考虑 MySQL 是怎样进行运算和比较的。一旦选定数据类型，应该保证所有相关的表都使用相同的数据类型。

  • 整型

    通常是作为标识符的最好选择，因为可以更快的处理，而且可以设置为 AUTO_INCREMENT 。

  • 字符串

    尽量避免使用字符串作为标识符，它们消耗更好的空间，处理起来也较慢。而且，通常来说，字符串都是随机的，所以它们在索引中的位置也是随机的，这会导致页面分裂、随机访问磁盘，聚簇索引分裂（对于使用聚簇索引的存储引擎）。

• 选择索引和利用索引查询的原则

  • 单行访问是很慢的。

    如果服务器从存储中读取一个数据块只是为了获取其中一行，那么就浪费了很多工作。最好读取的快中包含尽可能多所需要的行。利用索引可以创建位置引用以提升效率。

  • 按顺序访问范围数据是很快的。

    • 顺序 I/O 不需要多次磁盘寻道，所以比随机 I/O 快很多（特别是对机械硬盘）
    • 如果服务器能够按需要顺序读取数据，那么旧不再需要额外的排序操作，并且 group by 查询也无需在做排序和将行按组进行聚合计算了。

  • 索引覆盖查询是很快的。

    如果一个索引包含了查询需要的所有列，那么存储引擎就你不需要再回表查行。这避免了大量的单行访问。

高性能的索引策略

• 独立的列

  独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。

  SELECT * FROM it WHERE id+1=5;

• 前缀索引和索引选择性

  如果要索引很长的字符列，索引会变得很大并且很慢。这个时候通常可以索引开始的部分字符来节约索引空间，提高索引效率。但是这样也会降低索引的 选择性（不重复的索引值和数据表的记录总数的比值）。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySql过滤更多的行。

  创建前缀索引：

  ALTER TABLE it ADD KEY(name(2))

• 多列索引

  对于同时使用到多个单列索引的查询，mysql会将结果进行合并。
  

  Mysql的这种索引合并策略是一种优化的结果，但是实际上更多时候说明表的索引建得很糟糕：

  • 当出现服务器对多个索引做相交操作时（多个 AND 条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
  • 当服务器需要对多个索引做联合操作时(多个 OR 条件)，通常需要消耗大量CPU和内存资源在算法的缓存、排序和合并操作上。
  • 更重要的是，优化器不会把这些计算到查询成本(cost)中，优化器只关心随机页面读取。

• 选择合适的索引列顺序

  一个经验法则是：将 选择性 最高的列放到索引最前列。但是场景不同，试用的准则也可能不同，所以对于合适索引列顺序的选择还需要具体分析。

• 聚簇索引

  聚簇索引 并不是一种单独的索引类型，而是一种数据存储方式。InnoDB 的聚簇索引实际上在同一个结构中保持了 B-Tree 索引和数据行。当表有聚簇索引时，它的数据行实际上存放在索引的叶子页（leaf page）中。聚簇 表示数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
  聚簇索引 的结构：

  

  InnoDB 对主键建立聚簇索引。如果你不指定主键，InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引，InnoDB 会定义一个隐藏的主键，然后对其建立聚簇索引。一般来说，DBMS` 都会以聚簇索引的形式来存储实际的数据，它是其它二级索引的基础。

• 覆盖索引

  如果索引包含满足查询的所有数据，就称为覆盖索引。覆盖索引是一种非常强大的工具，能大大提高查询性能。只需要读取索引而不用读取数据有以下一些优点：

  • 索引项通常比记录要小，所以 MySQL 访问更少的数据；
  • 索引都按值的大小顺序存储，相对于随机访问记录，需要更少的 `I/O；
  • 大多数据引擎能更好的缓存索引。比如 MyISAM 只缓存索引。
  • 覆盖索引对于 InnoDB 表尤其有用，因为 InnoDB 使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了。

  覆盖索引不能是任何索引，只有 B-TREE 索引存储相应的值。对于索引覆盖查询 (index-covered query)，使用 EXPLAIN 时，可以在 Extra 一列中看到 Using index。

• 利用索引排序

  MySQL 中，有两种方式生成有序结果集：一是使用 filesort ，二是按索引顺序扫描。利用索引进行排序操作是非常快的，而且可以利用同一索引同时进行查找和排序操作。当索引的顺序与 ORDER BY 中的列顺序相同且所有的列是同一方向 (全部升序或者全部降序) 时，可以使用索引来排序。如果查询是连接多个表，仅当 ORDER BY 中的所有列都是第一个表的列时才会使用索引。其它情况都会使用 filesort 。
  当 MySQL 不能使用索引进行排序时，就会利用自己的排序算法 (快速排序算法) 在内存 (sort buffer) 中对数据进行排序，如果内存装载不下，它会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集（实际上就是外排序）。对于 filesort ，MySQL 有两种排序算法。

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

• 索引和锁

  索引对于 InnoDB 非常重要，因为它可以让查询锁更少的元组。这点十分重要，因为MySQL 5.0中，InnoDB 直到事务提交时才会解锁。有两个方面的原因：首先，即使 InnoDB 行级锁的开销非常高效，内存开销也较小，但不管怎么样，还是存在开销。其次，对不需要的元组的加锁，会增加锁的开销，降低并发性。
  InnoDB 仅对需要访问的元组加锁，而索引能够减少 InnoDB 访问的元组数。但是，只有在存储引擎层过滤掉那些不需要的数据才能达到这种目的。一旦索引不允许 InnoDB 那样做（即达不到过滤的目的），MySQL 服务器只能对 InnoDB 返回的数据进行 WHERE 操作，此时，已经无法避免对那些元组加锁了：InnoDB 已经锁住那些元组，服务器无法解锁了。