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
列反应了访问类型。访问类型有很多种,从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。 -
查询优化、索引优化、库表结构优化需要齐头并进,一个不落。