mysql查询优化理解,MySQL查询优化解读

执行计划解释

a.png

执行计划字段解释

id：SELECT 的查询序列号，体现执行优先级，如果是子查询，id的序号会递增，id 值越大优先级越高，越先被执行；

select_type：表示查询的类型；

table：输出结果集的表，如设置了别名，也会显示；

partitions：匹配的分区；

type：对表的访问方式；

possible_keys：表示查询时，可能使用的索引；

key：表示实际使用的索引；

key_len：索引字段的长度；

ref：列与索引的比较；

rows：扫描出的行数(估算的行数)；

filtered：按表条件过滤的行百分比；

Extra：执行情况的描述和说明。

执行计划字段取值含意

TYPE

对表访问方式，表示 MySQL 在表中找到所需行的方式，又称“访问类型”。

存在的类型有：ALL、index、range、ref、eq_ref、const、system、NULL(从左到右，性能从低到高)。

ALL：(Full Table Scan)MySQL 将遍历全表以找到匹配的行，常说的全表扫描；

Index：(Full Index Scan)Index 与 ALL 区别为 Index 类型只遍历索引树；

Range：只检索给定范围的行，使用一个索引来选择行。

KEY

key 列显示了 SQL 实际使用索引，通常是 possible_keys 列中的索引之一，MySQL 优化器一般会通过计算扫描行数来选择更适合的索引，如果没有选择索引，则返回 NULL。

强制使用一个索引：FORCE INDEX (index_name)、USE INDEX (index_name)；

强制忽略一个索引：IGNORE INDEX (index_name)。

ROWS

rows 是 MySQL 估计为了找到所需的行而要读取(扫描)的行数，可能不精确。

Extra

Using index：查询的列被索引覆盖，并且 where 筛选条件是索引的是前导列，Extra 中为 Using index。意味着通过索引查找就能直接找到符合条件的数据，无须回表。

注：前导列一般指联合索引中的第一列或“前几列”，以及单列索引的情况；这里为了方便理解我统称为前导列。

Using where：说明 MySQL 服务器将在存储引擎检索行后再进行过滤；即没有用到索引，回表查询。

可能的原因：

查询的列未被索引覆盖；where 筛选条件非索引的前导列或无法正确使用到索引。

Using temporary：这意味着 MySQL 在对查询结果排序时会使用一个临时表。

Using filesort：说明 MySQL 会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。

Using index condition：查询的列不全在索引中，where 条件中是一个前导列的范围。

Using where；Using index：查询的列被索引覆盖，并且 where 筛选条件是索引列之一，但不是索引的前导列或出现了其他影响直接使用索引的情况(如存在范围筛选条件等)，Extra 中为 Using where；Using index，意味着无法直接通过索引查找来查询到符合条件的数据，影响并不大。

分页查询优化

用法：LIMIT M,N

LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。

LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。

如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目

常见场景：网页页面中的上一页、下一页。

原因分析

子查询的分页方式：

随着数据量的增加，页数会越来越多，就是越往后分页，LIMIT语句的偏移量就会越大，速度也会明显变慢。

此时，我们可以通过子查询的方式来提高分页效率。

优化前

select user_id,city_id,sender_name,sender_phone,sender_address,

sender_pos,receiver_name,receiver_phone,receiver_address,

receiver_pos,remark from user_order_basic where

id in (select id from user_order_basic where create_time >= 1590940800 and create_time < 1593532800 ) limit 134900,100;

优化后

select user_id,city_id,sender_name,sender_phone,sender_address,sender_pos,

receiver_name,receiver_phone,receiver_address,receiver_pos,remark

from user_order_basic where id >= (select id from user_order_basic

where create_time >= 1590940800 and create_time < 1593532800

ORDER BY create_time desc LIMIT 134900, 1) limit 100;