MySQL 索引知识点

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

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

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

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

mysql> ALTER TABLE sakila.city_demo ADD INDEX (city(7));

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

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

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

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

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 无法使用索引的其余列       
  

理想情况下扫描的行数和返回的行数应该是相同的。

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

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