Skip to content

MySQL 索引知识点

  1. 存储引擎负责 MySQL 中数据的存储和提取。

  2. 索引是存储引擎用于快速找到记录的一种数据结构。

  3. 索引可以包含一个或多个列的值。如果索引包含多个列,那么列的顺序也十分重要,因为 MySQL 只能高效地使用索引的最左前缀列。

  4. 有时候需要索引很长的字符列,这会让索引变得大且慢。对于 BLOBTEXT 或者很长的 VARCHAR 类型的列,必须使用前缀索引,因为 MySQL 不允许索引这些列的完整长度。

    mysql> ALTER TABLE sakila.city_demo ADD INDEX (city(7));
    
  5. 索引合并策略 type: index_merge 有时候是一种优化的结果,但实际上更多时候说明了表上的索引建得很糟糕:

    • 当出现服务器对多个索引做相交操作时 Extra: Using intersect(film_id,actor_id)(通常有多个 AND 条件),通常意味着需要一个包含所有相关列的多列索引,而不是多个独立的单列索引。

    • 当服务器需要对多个索引做联合操作时 Extra: Using union(film_id,actor_id)(通常有多个 OR 条件),通常需要耗费大量 CPU 和内存资源在算法的缓存、排序和合并操作上。

  6. 如果一个索引包含所有需要查询的字段的值,我们就称之为 覆盖索引 Extra: Using index

  7. MySQL 可以使用同一个索引既满足排序,又用于查找行。因此,如果可能,设计索引时应该尽可能地同时满足这两种任务。如果 EXPLAIN 出来的 type: index,则说明 MySQL 使用了索引扫描来做排序。

    • 只有当索引的列顺序和 ORDER BY 子句的顺序完全一致,并且所有列的排序方向都一样时,MySQL 才能够使用索引来对结果做排序。

    • ORDER BY 子句和查找型查询的限制是一样的:需要满足索引的最左前缀的要求;否则,MySQL 都需要执行排序操作,而无法利用索引排序。有一种情况下 ORDER BY 子句可以不满足索引的最左前缀的要求,就是前导列为常量的时候。

      # 例如,KEY rental_date(rental_date,inventory_id,customer_id)
      
      # 可以使用 rental_date 索引做排序的查询示例:
      ... WHERE rental_date = '2005-05-25' ORDER BY inventory_id, customer_id;
      ... WHERE rental_date = '2005-05-25' ORDER BY inventory_id DESC;
      ... WHERE rental_date > '2005-05-25' ORDER BY rental_date, inventory_id; // ORDER BY 使用的两列就是索引的最左前缀
      
      # 不可以使用 rental_date 索引做排序的查询示例:
      ... WHERE rental_date = '2005-05-25' ORDER BY inventory_id DESC, customer_id ASC; // 使用了两种不同的排序方向
      ... WHERE rental_date = '2005-05-25' ORDER BY inventory_id, staff_id; // 引用了一个不在索引中的列
      ... WHERE rental_date = '2005-05-25' ORDER BY customer_id; // 无法组合成索引的最左前缀
      ... WHERE rental_date > '2005-05-25' ORDER BY inventory_id, customer_id; // 索引列的第一列上是范围条件,所以 MySQL 无法使用索引的其余列       
      
  8. 理想情况下扫描的行数和返回的行数应该是相同的。

  9. EXPLAIN 语句中的 type 列反应了访问类型。访问类型有很多种,从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。

  10. 查询优化、索引优化、库表结构优化需要齐头并进,一个不落。

Post a Comment

Your email is never published nor shared. Required fields are marked *
*
*