MySQL 索引初探

MYSQL索引

什么是索引

为什么要建立索引

索引的优缺点和使用原则

索引的分类

  1. 单列索引:一个索引只包含单个列,但一个表中可以有多个单列索引。

    1. 普通索引:MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值;
    2. 唯一索引:索引列中的值必须是唯一的,但是允许为空值;
    3. 主键索引:是一种特殊的唯一索引,不允许有空值;
  2. 组合索引:在表中的多个字段组合上创建的索引;如果列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

    1. 覆盖索引: 如果一个索引包含(或覆盖)所有需要查询的字段的值,称为‘覆盖索引’。即只需扫描索引而无须回表。
  3. 全文索引:只有在MyISAM引擎上才能使用,只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引

  4. 空间索引:空间索引是对空间数据类型的字段建立的索引,MySQL中的空间数据类型有四种,GEOMETRY、POINT、LINESTRING、POLYGON。要求引擎为MyISAM,创建空间索引的列,必须将其声明为NOT NULL。

聚簇索引: 待完善

索引执行策略

注意: 一次查询只会使用一个索引

待完善

ORDER BY优化

  1. Where子句与Order BY子句列组合满足索引最左前缀规则。
  2. 查询满足覆盖索引规则。

GROUP BY 优化

一、GROUP BY 使用索引原理

  1. group by 使用排序来读取数据,所以只能用 Btree 索引,不能使用在hash索引是因为hash索引是一种类似键值对的快速访问方式,这个对于指定某个值查询很好,但无法排序;
  2. 当使用索引排序来查找数据时,不会在 explain 中 extra 列看到有using filesort;
  3. 在group by操作完成后,还会对group出来的结果进行排序,因此如果对排序的结果没有排序的需求,可以考虑在其后面加上order by null;

二、GROUP BY 访问索引的方法

group by 访问数据有两种方法:

  1. 边扫描边执行group操作,叫做松散索引扫描(Loose index scan);
  2. 先执行一个范围(range)扫描,然后在执行group 操作,叫做紧索引扫描(Tight index scan);

松散索引扫描 Loose index scan

最高效的处理group by的方法是,直接访问相应的索引,所以不用排序就能根据索引来读取需要的数据,而对于如聚簇索引(cluster index),我们可以读取前面的一部分的字段索引来获取数据,而不用满足所有的列,这就叫做松散索引扫描,也叫做:前缀索引扫描。

松散索引的条件:

  1. 查询只能针对一个单表进行操作,这个可是个致命的缺点啊,但如果where条件比较多,选出来的数据少的话,还是不用担忧的;
  2. group by使用索引为:对聚簇索引使用前缀索引;
  3. 使用类似group by 的操作的函数有distinct函数,使用此函数时,要么在一个索引上使用,要么在group by时,其group by的字句是索引扫描,否则会引起全表扫描;
  4. 在使用group by语句中,如果使用聚合函数max(), min()等,如果列不在group by的列中,或不在group by 列的聚簇索引的一部分,这将会用到排序操作;
    5、只能对整个列的值排序时使用到索引,而只有前面一部分索引不能用到排序,如: 列 c1 char(20), index(c1(10))、这个只用了一半索引,将无法使用来对整个数据排序;

假设我们在表t1(c1, c2, c3, c4)有聚簇索引index(c1, c2, c3),能使用Loose index scan的例子:

1、SELECT c1, c2 FROM t1 GROUP BY c1, c2;
2、SELECT DISTINCT c1, c2 FROM t1;
3、SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
4、SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
5、SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;
6、SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
7、SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;

紧索引扫描 Tight index scan

使用紧索引扫描和松索引扫描类似,只是会先根据where条件来获取所有的行,然后根据group by的字段来分组,这种的使用方法,一般是,where条件返回的行较少时使用,比如,你的where字句中使用了主键或唯一键=const等,这样的代价是,通过where过滤出来的行很少,再分组操作时也很快的

使用 EXPLAIN 查看SQL执行计划

*使用方式

  1. EXPLAIN SELECT ……
  2. EXPLAIN EXTENDED SELECT ……
    将执行计划"反编译"成SELECT语句,运行SHOW WARNINGS,可得到被MySQL优化器优化后的查询语句。
  3. EXPLAIN PARTITIONS SELECT ……
    用于分区表的EXPLAIN生成QEP的信息

执行计划包含的信息

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

1. id
包含一组数字,表示查询中执行select子句或操作表的顺序

  • id相同,可以认为是一组,从上往下顺序执行;执行顺序由上至下,
  • 如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行,
  • 在所有组中,id值越大,优先级越高,越先执行

2. select_type
查询中每个select子句的类型

  • SIMPLE:查询中不包含子查询或者UNION
  • 查询中若包含任何复杂的子部分,最外层查询则被标记为:PRIMARY
  • 在SELECT或WHERE列表中包含了子查询,该子查询被标记为:SUBQUERY
  • 在FROM列表中包含的子查询被标记为:DERIVED(衍生)用来表示包含在from子句中的子查询的select,mysql会递归执行并将结果放到一个临时表中。服务器内部称为"派生表",因为该临时表是从子查询中派生出来的
  • 若第二个SELECT出现在UNION之后,则被标记为UNION;若UNION包含在FROM子句的子查询中,外层SELECT将被标记为:DERIVED
  • 从UNION表获取结果的SELECT被标记为:UNION RESULT

SUBQUERY和UNION还可以被标记为DEPENDENT和UNCACHEABLE。
DEPENDENT意味着select依赖于外层查询中发现的数据。
UNCACHEABLE意味着select中的某些 特性阻止结果被缓存于一个item_cache中。

3. table
使用到的数据表

4. type

表示MySQL在表中找到所需行的方式,又称“访问类型”,常见类型如下:
all < index < range < ref < eq_ref < const < system < NULL
从左到右,性能从最差到最好

  • ALL:Full Table Scan, MySQL将遍历全表以找到匹配的行
  • index:Full Index Scan,index与ALL区别为index类型只遍历索引树
  • range:索引范围扫描,对索引的扫描开始于某一点,返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<, >查询。当mysql使用索引去查找一系列值时,例如IN()和OR列表,也会显示range(范围扫描)
  • ref:使用非唯一索引扫描或者唯一索引的前缀扫描,返回匹配某个单独值的记录行
  • eq_ref:类似ref,区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配,简单来说,就是多表连接中使用primary key或者 unique key作为关联条件
  • const、system:当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于where列表中,MySQL就能将该查询转换为一个常量
  • NULL:MySQL在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成。

5. possible_keys
指出MySQL能使用哪个索引在表中找到记录,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用

6. key
显示MySQL在查询中实际使用的索引,若没有使用索引,显示为NULL

7. key_len
表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度(key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的)

8. ref
这一列显示了在key列记录的索引中,表查找值所用到的列或常量,常见的有:const(常量),func,NULL,字段名(例:film.id)

9. rows
表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数

10. Extra
包含不适合在其他列中显示但十分重要的额外信息

  • Using index: 表示相应的select操作中使用了覆盖索引(Covering Index)
  • using index condition:在5.6版本后加入的新特性(Index Condition Pushdown);Using index condition 会先条件过滤索引,过滤完索引后找到所有符合索引条件的数据行,随后用 WHERE 子句中的其他条件去过滤这些数据行;
  • Using where: 在查找使用索引的情况下,需要回表去查询所需的数据
  • Using temporary: 表示MySQL需要使用临时表来存储结果集,常见于排序和分组查询
  • Using filesort: MySQL中无法利用索引完成的排序操作称为“文件排序”
本作品采用《CC 协议》,转载必须注明作者和本文链接
本帖由系统于 5年前 自动加精
讨论数量: 3

很赞,学习 了!

5年前 评论

全文索引(FULLTEXT Index)在 Innodb 也可以使用,條件是要 MySQL 5.6 以上

Ref: https://dev.mysql.com/doc/refman/8.0/en/in...

5年前 评论

“Using where: 在查找使用索引的情况下,需要回表去查询所需的数据”

这里应该解释有误!

4年前 评论
praglody (楼主) 4年前

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!