高性能MySQL

可以使用B-Tree索引的查询类型

key(last_name, first_name, dob)

前面所述的索引对如下类型的查询有效：
- 全值匹配：全值匹配指的是和索引中的所有列进行匹配，例如前面提到的索引可用于查找姓名为Cuba Allen、出生于1960-01-01的人。
- 匹配最左前缀：前面提到的索引可用于查找所有姓为Allen的人，即只使用索引的第一列。
- 匹配列前缀：也可以只匹配某一列的值的开头部分。例如前面提到的索引可用于查找所有以J开头的姓的人。
- 匹配范围值：例如前面提到的索引可用于查找姓在Allen和Barrymore之间的人。这里也只使用了索引的第一列。
- 精确匹配某一列并范围匹配另外一列：前面提到的索引也可用于查找所有姓为Allen，并且名字是字母K开头（比如Kim、Karl等）的人。即第一列last_name全匹配，第二列first_name范围匹配。
- 只访问索引的查询：B-Tree通常可以支持“只访问索引的查询”，即查询只需要访问索引，而无需访问数据行。后面我们将单独讨论这种“覆盖索引”的优化。
下面是一些关于B-Tree索引的限制：
- 如果不是按照索引的最左列开始查找，则无法使用索引。例如上面例子中的索引无法用于查找名字为Bill的人，也无法查找某个特定生日的人，因为这两列都不是最左数据列。类似地，也无法查找姓氏以某个字母结尾的人。
- 不能跳过索引中的列。也就是说，前面所述的索引无法用于查找姓为Smith并且某个特定日期出生的人。如果不指定名 (first_name)，则MySQL只能使用索引的第一列。
- 如果查询中有某个列的查询范围，则其右边所有列都无法使用索引优化查询。例如有查询WHERE last_name=’Smith’ AND first_name LIKE ‘J%’ AND dob = ‘1976-12-23’，这个查询只能使用索引的前两列，因为这里LIKE是一个范围条件（但是服务器可以把其余列用于其他目的）。如果范围查询列值的数量有限，那么可以通过使用多个等于条件来代替范围条件。
高性能的索引策略
- 独立的列：独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。
- 前缀索引和索引选择性
  
  有时候需要索引很长的字符列，这会让索引变得大且慢。
  
  通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指，不重复的索引值（也称为基数，cardinality）和数据表的记录总数（#T）的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
  
  一般情况下某个列前缀的选择性也是足够高的，足以满足查询性能。对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。
  
  诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。换句话说，前缀的“基数”应该接近于完整列的“基数”。
- 多列索引
- 选择合适的索引列顺序
  
  在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY、GROUP BY和DISTINCT等子句的查询需求。所以多列索引的列顺序至关重要。
  
  对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引的最前列。当不需要开率排序和分组时，将选择性最高的列放在前面通常时很好的。这时候索引确实能够最快地过滤出需要的行，对于在WHERE子句中只使用了索引部分前缀列的查询来说选择性也更高。然而，性能不只是依赖于所有索引列的选择性，也和查询条件的具体值有关，也就是和值的分布有关。这和前面介绍的选择前缀的长度需要考虑的地方一样。可能需要根据那些运行频率最高的查询来调整索引列的顺序，让这种情况下索引的选择性最高。
- 聚簇索引
  
  聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。
  
  当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中。属于“聚簇”表示数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
  
  聚簇索引的优点：
  - 可以把相关数据保存在一起。例如实现电子邮件时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘I/O。
  - 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
  - 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
  聚簇索引的缺点：
  - …
  - 二级索引（非聚簇索引）访问需要两次索引查找，而不是一次。
    
    为什么二级索引需要两次索引查找？答案在于二级索引中保存的“行指针”的实质。要记住，二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。
    
    这意味这通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得相应的主键值，然后根据这个值去聚簇索引中查找到对应的行。这里做了重复的工作：两次B-Tree查找而不是一次。
  InnoDB和MyISAM的数据分布对比
  - MyISAM的数据分布
    
    MyISAM按照数据插入的顺序存储在磁盘上。在行的旁边显示了行号，从0开始递增。
    
    MyISAM中主键索引和其他索引在结构上没有什么不同。主键索引就是一个名为PRIMARY的唯一非空索引。
  - InnoDB的数据分布
    
    在InnoDB中，聚簇索引“就是”表，所以不想MyISAM那样需要独立的行存储。
    
    聚簇索引的每一个叶子节点都包含了主键值、事务ID、用于事务和MVCC的回滚指针以及所有的剩余列。
    
    还有一点和MyISAM的不同是，InnoDB的二级索引和聚簇索引很不相同。InnoDB二级索引的叶子节点中存储的不是“行指针”，而是主键值，并以此作为指向行的“指针”。
  在InnoDB表中按主键顺序插入行
  
  如果正在使用InnoDB表并且没有什么数据需要聚集，那么可以定义一个代理键作为主键，这种主键的数据应该和应用无关，最简单的方法是使用AUTO_INCREMENT自增列。这样可以保证数据行是按顺序写入，对于根据主键做关联操作的性能也会更好。
  
  最好避免随机的（不连续且值的分布范围非常大）聚簇索引，特别时对于I/O密集型的应用。例如，从性能的角度，使用UUID来作为聚簇索引则会很糟糕：它使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性。
  
  注意到向UUID主键插入行不仅花费的时间更长，而且索引占用的空间页更大。这一方面是由于主键字段更长，另一方面毫无疑问是由于页分裂和碎片导致的。
  
  因为主键的值是顺序的，所以InnoDB把每一条记录都存储在上一条记录的后面。当达到页的最大填充因此时（InnoDB默认的最大填充因子时页大小的15/16，留出部分空间用于以后修改），下一条记录就会写入新的页中。一旦数据按照这种顺序的方式加载，主键页就会近似于被顺序的记录填满，这也正是所期望的结果。
  
  对比一下向第二个使用了UUID聚簇索引的表插入数据，看看有什么不同。因为新行的主键值不一定比之前插入的大，所以InnoDB无法简单地总是把新行插入到索引的最后，而是需要为新的行寻找合适的位置，并导致数据分布不够优化。
- 覆盖索引
  
  如果索引的叶子节点中已经包含要查询的数据，那么还有什么必要再回表查询呢？如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。
  
  当发区一个被索引覆盖的查询（也叫做索引覆盖查询）时，在EXPLAIN的EXTRA列可以看到Using index的信息。
- 使用索引扫描来做排序
  
  MySQL有两种方式可以生成有序的结果：通过排序操作，或者按索引顺序扫描，如果EXPLAIN出来的type列的值为index，则说明mySQL使用了索引扫描来做排序。
  
  扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录就都回表查询一次对应的行。这基本上都是随机I/O，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢，尤其是在I/O密集型的工作负载时。
  
  只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求；否则，MySQL都需要执行排序操作，而无法利用索引排序。
  
  UNIQUE KEY rental_date (rental_date, inventory_id, customer_id)
  
  下面这个查询没有问题，因为ORDER BY使用的两列就是索引的最左前缀：
  
  … WHERE rental_date > ‘2005-05-25’ ORDER BY rental_date, inventory_id;
查询性能优化
- 慢查询基础：优化数据访问
  1. 是否向数据库请求了不需要的数据：查询不需要的记录(LIMIT)、多表关联时返回全部列、总是取出全部列(SELECT *)、重复查询相同的数据（缓存）
  2. MySQL是否在扫描额外的记录
- 重构查询的方式
  - 一个复杂的查询还是多个简单的查询
  - 切分查询
    
    有时候对于一个大查询我们需要“分而治之”，将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。
    
    删除旧的数据就是一个很好的例子。定期地清除大量数据时，如果用一个大的语句一次性完成的话，则可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。将一个大的DELETE语句切分成多个较小的查询可以尽可能小地影响MySQL性能，同时还可以减少MySQL复制的延迟。
  - 分解关联关系
- 查询执行的基础
  
  当向MySQL发送一个请求的时候，MySQL到底做了些什么：
  1. 客户端发送一条查询给服务器。
  2. 服务端先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。
  3. 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。
    - 查询优化器
      
      下面是一些MySQL能够处理的优化类型：
      - 重新定义关联表的顺序
      - 将外连接转化成内连接
      - 使用等价变换规则
      - 优化COUNT()、MIN()和MAX()：要找到某一列的最小值，只需要查询对应B-Tree索引最左端的记录，MySQL可以直接获取索引的第一行记录。在优化器生成执行计划的时候就可以利用这一点，在B-Tree索引中，优化器会将这个表达式作为一个常数对待。类似的，如果要查找一个最大值，也只需要读取B-Tree索引的最后一条记录。类似的，没有任何WHERE条件的COUNT(*)查询通常也可以使用存储引擎提供的一些优化（例如，MyISAM维护了一个变量来存放数据表的行数）。
      - 预估并转化为常数表达式：当MySQL检测到一个表达式可以转化为常数时，就会一直把该表达式作为常数进行优化处理。
      - 覆盖索引扫描：当索引中的列包含所有查询中需要使用的列的时候，MySQL就可以使用索引返回需要的数据，而无需查询对应的数据行。
      - 子查询优化
      - 提前终止查询
      - 等值传播
      - 列表IN()的比较：二分查找O(log n)，OR查询O(n)
    - 关联查询优化器
    - 排序优化
  4. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。
  5. 将结果返回给客户端。

CATALOG

FEATURED TAGS

FRIENDS