如何优化sql查询效率_查询优化的一般准则

有一个 ?

遇到这样一个疑问：当where查询中In一个索引字段作为条件，那么在查询中还会使用到索引吗？

SELECT * FROM table_name WHERE column_index in (expr)

代码100分

上面的sql语句检索会使用到索引吗？带着这个问题，在网上查找了很多文章，但是有的说 in 会导致放弃索引，全表扫描；有的说Mysql5.5之前的版本不会走，之后的innodb版本会走索引…

越看越迷糊，那答案到底是怎样的呢？

唯有实践是检验真理的唯一方式！

拿出我们的利刃——EXPLAIN，去剖析 SELECT 语句，一探究竟！

EXPLAIN 的用法

在 SELECT 语句前加上 EXPLAIN 就可以了 ，例如：

代码100分EXPLAIN SELECT * FROM table_name [WHERE Clause]

EXPLAIN 的输出

EXPLAIN 命令的输出内容为一个表格形式，表的每一个字段含义如下：

select_type

table

表示查询涉及的表或衍生表 。 这也可以是以下值之一：

• <unionM,N>：该行指的是具有和id值的行 的 M并集 N。
• 
  ：该行是指用于与该行的派生表结果id的值 N。派生表可能来自FROM子句中的子查询 。
  
• 
  ：该行是指该行的物化子查询的结果，其id 值为N。
  

partitions

查询将匹配记录的分区。该值适用NULL于未分区的表。

type

联接类型。 提供了判断查询是否高效的重要依据依据。通过 type 字段，我们判断此次查询是全表扫描还是索引扫描等。 从最佳类型到最差类型：

• system： 该表只有一行（=系统表）。这是const联接类型的特例 。

• const： 针对主键或唯一索引的等值查询扫描，最多只返回一行数据。const 查询速度非常快，因为它仅仅读取一次即可 。

  SELECT * FROM tbl_name WHERE primary_key=1;
  
  SELECT * FROM tbl_name
    WHERE primary_key_part1=1 AND primary_key_part2=2;
  

• eq_ref： 此类型通常出现在多表的 join 查询，表示对于前表的每一个结果，都只能匹配到后表的一行结果。并且查询的比较操作通常是 =，查询效率较高

  代码100分SELECT * FROM ref_table,other_table
    WHERE ref_table.key_column=other_table.column;
  
  SELECT * FROM ref_table,other_table
    WHERE ref_table.key_column_part1=other_table.column
    AND ref_table.key_column_part2=1;
  

• ref ： 此类型通常出现在多表的 join 查询，针对于非唯一或非主键索引，或者是使用了最左前缀规则索引的查询。ref可以用于使用=或<=> 运算符进行比较的索引列。

  SELECT * FROM ref_table WHERE key_column=expr;
  
  SELECT * FROM ref_table,other_table
    WHERE ref_table.key_column=other_table.column;
  
  SELECT * FROM ref_table,other_table
    WHERE ref_table.key_column_part1=other_table.column
    AND ref_table.key_column_part2=1;
  

• ref_or_null： 这种连接类型类似于 ref，但是除了MySQL会额外搜索包含NULL值的行。此联接类型优化最常用于解析子查询。

  SELECT * FROM ref_table
    WHERE key_column=expr OR key_column IS NULL;
  

• unique_subquery： 只是一个索引查找函数，它完全替代了子查询以提高效率。

  value IN (SELECT primary_key FROM single_table WHERE some_expr)
  

• index_subquery：此连接类型类似于 unique_subquery。它代替IN子查询，但适用于以下形式的子查询中的非唯一索引。

• range： 表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录。这个类型通常出现在 =，<>，>，>=，<，<=，IS NULL，<=>，BETWEEN，IN() 操作中。

  当 type 是 range 时，那么 EXPLAIN 输出的 ref 字段为 NULL，并且 key_len 字段是此次查询中使用到的索引的最长的那个 。

  SELECT * FROM tbl_name
    WHERE key_column = 10;
  
  SELECT * FROM tbl_name
    WHERE key_column BETWEEN 10 and 20;
  
  SELECT * FROM tbl_name
    WHERE key_column IN (10,20,30);
  
  SELECT * FROM tbl_name
    WHERE key_part1 = 10 AND key_part2 IN (10,20,30);
  

• index： 表示全索引扫描(full index scan)和 ALL 类型类似，只不过 ALL 类型是全表扫描，而 index 类型则仅仅扫描所有的索引，而不扫描数据。

  index 类型通常出现在: 所要查询的数据直接在索引树中就可以获取到，而不需要扫描数据。当是这种情况时，Extra 字段 会显示 Using index

• ALL： 表示全表扫描，这个类型的查询是性能最差的查询之一。

  我们的查询不应该出现 ALL 类型的查询，因为这样的查询在数据量大的情况下，对数据库的性能是巨大的灾难。如一个查询是 ALL 类型查询，那么一般来说可以对相应的字段添加索引来避免 。

possible_keys

表示 MySQL 在查询时，能够使用到的索引。

即使有些索引在 possible_keys 中出现，但是并不表示此索引会真正地被 MySQL 使用到。MySQL 在查询时具体使用了哪些索引，由 key 字段决定。

key

是 MySQL 在当前查询时所真正使用到的索引。

key_len

表示查询优化器使用了索引的字节数。

这个字段可以评估组合索引是否完全被使用，或只有最左部分字段被使用到。key_len 的计算规则如下：

• 字符串
  • char(n): n 字节长度
  • varchar(n): 如果是 utf8 编码, 则是 3n + 2字节; 如果是 utf8mb4 编码, 则是 4n + 2 字节
• 数值类型
• TINYINT: 1字节
• SMALLINT: 2字节
• MEDIUMINT: 3字节
• INT: 4字节
• BIGINT: 8字节
• 时间类型
• DATE: 3字节
• TIMESTAMP: 4字节
• DATETIME: 8字节
• 字段属性： NULL 属性 占用一个字节。如果一个字段是 NOT NULL 的, 则没有此属性

rows

查询优化器根据统计信息，估算 SQL 要查找到结果集需要扫描读取的数据行数。这个值非常直观显示 SQL 的效率好坏，原则上 rows 越少越好。

这个 rows 就是 mysql 认为必须要逐行去检查和判断的记录的条数。举个例子来说，假如有一个语句 select * from t where column_a = 1 and column_b = 2; 全表假设有 100 条记录，column_a 字段有索引（非联合索引），column_b没有索引。column_a = 1 的记录有 20 条， column_a = 1 and column_b = 2 的记录有 5 条。

Extra

EXplain 中的很多额外的信息会在 Extra 字段显示，常见的有以下几种内容：

• Using filesort：当 Extra 中有 Using filesort 时，表示 MySQL 需额外的排序操作，不能通过索引顺序达到排序效果。一般有 Using filesort，都建议优化去掉，因为这样的查询 CPU 资源消耗大。
• Using index：”覆盖索引扫描”，表示查询在索引树中就可查找所需数据，不用扫描表数据文件，往往说明性能不错
• Using temporary：查询有使用临时表，一般出现于排序，分组和多表 join 的情况，查询效率不高，建议优化
• Using where： WHERE子句用于限制哪些行与下一个表匹配或发送给客户端 。

得出结论

说到最后，那 WHERE column_index in (expr) 到底走不走索引呢？ 答案是不确定的。

走不走索引是由 expr 来决定的，不是一概而论走还是不走。

SELECT * FROM a WHERE id in (1,23,456,7,8)
-- id 是主键，查询是走索引的。type = range,key = PRIMARY
SELECT * FROM a WHERE id in (SELECT b.a_id FROM b WHERE some_expr)
-- id 是主键，如果 some_expr 是一个索引查询，那么 select a 将走索引；
-- some_expr 不是索引查询，那么 select a 将全表扫描；

上面是两个通用案例，但到底对不对了，还是自己去实践最好了，拿起EXPLAIN去剖析吧~

参考文章： https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain