MySQL性能优化——Explain使用分析( 二 ) 执行计划是分析SQL性能的利器

文章插图
执行结果

range ，使用范围查询，通过索引字段范围获取表中部分数据。通常使用=,>,<,between,in等等。 EXPLAIN SELECT * from user_info where id BETWEEN 1 and 5

文章插图
执行结果

index ，全索引扫描，和全表扫描类似，只不过扫描的是索引全部，性能稍好于全表扫描。 EXPLAIN SELECT name from user_info

文章插图
执行结果

ALL ，全表扫描，性能最低的查询方式。 EXPLAIN SELECT * from user_info

文章插图
查询结果
type的性能比较，通常的关系如下：
ALL < index < range < ref < eq_ref < const < system
我们内部要求SQL的查询要在range以上。
possible_keys：MySQL在查询的时候，能够使用到的索引。只是可能使用到的，真正使用的索引在key中体现。
key：MySQL在当前查询中使用的索引。
key_len：表示查询优化器使用了索引的字节数. 这个字段可以评估联合索引是否完全被使用, 或只有最左部分字段被使用到.
key_len 的计算规则如下:

字符串

char(n): n 字节长度varchar(n): 如果是 utf8 编码, 则是 3 n + 2字节; 如果是 utf8mb4 编码, 则是 4 n + 2 字节.

数值类型:

TINYINT: 1字节SMALLINT: 2字节MEDIUMINT: 3字节INT: 4字节BIGINT: 8字节

时间类型

DATE: 3字节TIMESTAMP: 4字节DATETIME: 8字节

字段属性

NULL 属性占用一个字节. 如果一个字段是 NOT NULL 的, 则没有此属性.

举两个例子：
EXPLAIN SELECT * FROM order_info WHERE user_id < 3 AND product_name = 'p1' AND productor = 'WHH' ;

文章插图
执行结果
上面的例子是从表 order_info 中查询指定的内容, 而我们从此表的建表语句中可以知道, 表 order_info 有一个联合索引： KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)
不过此查询语句 WHERE user_id < 3 AND product_name = 'p1' AND productor = 'WHH' 中, 因为先进行 user_id 的范围查询, 而根据最左前缀匹配原则, 当遇到范围查询时, 就停止索引的匹配, 因此实际上我们使用到的索引的字段只有 user_id,
因此在 EXPLAIN 中, 显示的 key_len 为 9. 因为 user_id 字段是 BIGINT, 占用 8 字节, 而 NULL 属性占用一个字节, 因此总共是 9 个字节. 若我们将user_id 字段改为 BIGINT(20) NOT NULL DEFAULT '0', 则 key_length 应该是8.
接下来看另一个例子：
EXPLAIN SELECT * FROM order_info WHERE user_id = 1 AND product_name = 'p1'

文章插图
执行结果
这次的查询中, 我们没有使用到范围查询, key_len 的值为 161. 为什么呢? 因为我们的查询条件 WHERE user_id = 1 AND product_name = 'p1' 中, 仅仅使用到了联合索引中的前两个字段, 因此 keyLen(user_id) + keyLen(product_name) = 9 + 50 * 3 + 2 = 161 。