关于MySQL查询语句的优化详解

目录
  • MySQL 优化
    • 子查询优化
    • 待排序的分页查询的优化
      • 给排序字段添加索引
    • 给排序字段跟 select 字段添加复合索引
    • 给排序字段加索引 + 手动回表
      • 解决办法
    • 排序优化

MySQL 优化

子查询优化

  • 将子查询改变为表连接,尤其是在子查询的结果集较大的情况下;
  • 添加复合索引,其中复合索引的包含的字段应该包括 where 字段与关联字段;
  • 复合索引中的字段顺序要遵守最左匹配原则;
  • MySQL 8 中自动对子查询进行优化;

现有两个表

create table Orders
(
    id         integer AUTO_INCREMENT PRIMARY KEY,
    name       varchar(255) not null,
    order_date datetime     NOT NULL
) comment '订单表';

create table OrderDetails
(
    id           integer AUTO_INCREMENT PRIMARY KEY,
    order_id     integer     not null,
    product_code varchar(20) not null,
    quantity     integer     not null
) comment '订单详情表';

子查询

select * from orders where id in (select order_id from OrderDetails where product_code = 'PC50');

优化1:改为表连接

select * from orders as t1 inner join orderdetails o on t1.id = o.order_id where product_code='PC50';

优化2:给 order_id 字段添加索引

优化3:给 product_code 字段添加索引

结果证明:给 product_code 字段添加索引 的效果优于给 order_id 字段添加索引,因为不用对索引列进行全表扫描

优化4:给 order_id 和 product_code 添加复合索引

优化5:给 product_code 和 order_id 添加复合索引

对于复合索引 idx(order_id, product_code),因为查询中需要判断 product_code 的值是否为 PC51,所以要对 order_id 该列进行全索引扫描,性能较低 [ 因为 product_code 不是有序的,先根据 order_id 进行排序,再根据 product_code 进行排序 ];

对于复合索引 idx(product_code, order_id) ,因为 product_code 本身是有序的,所以可以快速定位到该 product_code 然后快速获取该 order_id性能较高

待排序的分页查询的优化

现有一个电影表

create table film
(
    id           integer auto_increment primary key,
    score        decimal(2, 1) not null,
    release_date date          not null,
    film_name    varchar(255)  not null,
    introduction varchar(255)  not null
) comment '电影表';

对于浅分页

select score, release_date, film_name from film order by score limit 0, 20;

耗时 825ms

对于深分页

select score, release_date, film_name, introduction from film order by score limit 1500000, 20;

耗时 1s 247ms

若不加处理,浅分页的速度快,limit 的深度越深,查询的效率越慢

给排序字段添加索引

给 score 字段添加索引

create index idx_score on film(score);

结果

浅分页的速度为 60 ms,深分页的速度为 1s 134ms

浅分页的情况得到了优化,而深分页依然很慢

查看深分页的执行情况

其并没有走 score 索引,走的是全表的扫描,所以给排序字段添加索引只能优化浅分页的情况

解释

只给 score 添加索引,会造成回表的情况

对于浅分页,回表的性能消耗小于全表扫描,故走 score 索引;

对于深分页,回表的性能消耗大于全表扫描,故走 全表扫描;

给排序字段跟 select 字段添加复合索引

给 score, release_date, film_name 添加复合索引

create index idx_score_date_name on film(score, release_date, film_name);

浅分页的速度为 58 ms,深分页的速度为 357 ms,两者的速度都得到了提升

查看深分页的执行情况

可见其走了复合索引

解释

对于该复合索引,排序的值和查询的值都在索引上,没有进行回表的操作,效率很高。唯一的不足是:若要添加新的查询列,就要更改该索引的列,不够灵活

给排序字段加索引 + 手动回表

改进SQL语句,给 score 字段添加索引

# 给排序字段添加索引 + 手动回表
select score, release_date, film_name,introduction from  film a
join (select id from film order by score limit 1500000, 20) b
on  a.id = b.id;

思路:先把 limit 字段的 id 找出来,这里走了 score 索引,效率高。然后再走主键索引根据 id 去寻找;

该语句的执行情况

可见子查询中走了 score 索引,而外查询走了主键索引,效率非常高,执行速度为 297 ms

缺点

由上面的执行计划可见,它创建了一张中间表 ,走的是全表扫描,也就是说,中间表中的记录越多,该执行效率就越慢,观察以下语句,从500000开始查,查找 1500000 条数据;

select score, release_date, film_name,introduction from  film a
join (select id from film order by score limit  500000, 1500000) b
on  a.id = b.id;

消耗的时间为:911ms,接近一秒

所以我们可以通过业务的方法,限制每次查询的条数即可

解决办法

  • 给排序的字段 + select 的字段添加复合索引
  • 给排序的字段加索引 + 手动回表
  • 深分页的性能问题可以通过业务方法解决:限制每次查询的数量等

排序优化

索引的字段要根据排序的字段走,且要满足最左匹配原则

create table t_order (
    id integer primary key auto_increment,
    col_1 int not null ,
    col_2 int not null ,
    col_3 int not null
)

select * from t_order order by col_1, col_2, col_3, 需要创建联合索引 col_1,col_2,col_3

select * from t_order order by col_1, col_2,需要创建联合索引 col_1, col_2, col_3

select * from t_order order by col_1 asc, col_2 desc ,需要创建联合索引 col_1 asc, col_2 desc ,指定索引的排序规则,只有在 MySQL 8 中才支持

索引失效的情况(避免出现 using filesort)

  • 没有遵守最左匹配原则

select * from t_order order by col_1, col_3

select * from t_order order by col_2, col_3

可见都使用到了 ****using filesort

以第一条为例

最左匹配原则的实质是:先根据第一列排序,若第一列的值相同就根据第二列来排序,若第二列的值相同就根据第三列来排序,以此类推

第一条 SQL 中,排序的字段为 col_2 和 col_3 明显 在抛开 col_1 的情况下,col_2 和 col_3 的顺序是无序的,故要使用 using filesort,不能依靠索引来进行排序;

  • 使用了范围查询

select * from t_order where col1 >= 1 and col1 <= 4 order by col_2

select * from t_order where col1 in(1,2,4) order by col_2

若走该复合索引 (col_1, col_2, col_3) ,可以发现查询计划中使用到了 using filesort

解释

经过 col_1 的筛选后,col_2 的数据都是无序的

所以要使用 using filesort 再次根据 col_2 排序

若使用等值查询,则不会出现 using filesort,前提是要满足最左匹配原则

select col_1, col_2 from t_order where col_1 = 2 order by col_2;

若不满足 最左匹配原则

select col_1, col_3 from t_order where col_1 = 2 order by col_3;

则使用到了 using filesort

以上就是关于MySQL查询语句的优化详解的详细内容,更多关于MySQL查询语句优化的资料请关注我们其它相关文章!

(0)

相关推荐

  • Mysql查询语句优化技巧

    索引优化,查询优化,查询缓存,服务器设置优化,操作系统和硬件优化,应用层面优化(web服务器,缓存)等等.这里的记录的优化技巧更适用于开发人员,都是从网络上收集和自己整理的,主要是查询语句上面的优化,其它层面的优化技巧在此不做记录. 查询的开销指标: 执行时间 检查的行数 返回的行数 建立索引的几个准则: (1).合理的建立索引能够加速数据读取效率,不合理的建立索引反而会拖慢数据库的响应速度. (2).索引越多,更新数据的速度越慢. (3).尽量在采用MyIsam作为引擎的时候使用索引(因为My

  • mysql explain的用法(使用explain优化查询语句)

    首先我来给一个简单的例子,然后再来解释explain列的信息. 表一:catefory 文章分类表: CREATE TABLE IF NOT EXISTS `category` ( `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=MyISAM INSERT INTO `test`.`category` VAL

  • 优化MySQL数据库中的查询语句详解

    很多时候基于php+MySQL建立的网站所出现的系统性能瓶颈往往是出在MySQL上,而MySQL中用的最多的语句就是查询语句,因此,针对MySQL数据库查询语句的优化就显得至关重要!本文就此问题做出详细分析如下: 1.判断是否向MySQL数据库请求了不需要的数据,如下列情况: (1).查询不需要的数据,例如你需要10条数据,但是你选出了100条数据加了limit做限制. (2).多表关联时返回全部列 (3).总是取出全部列select*......取出全部列,会让优化器无法完成索引覆盖扫描这类优

  • mysql优化limit查询语句的5个方法

    mysql的分页比较简单,只需要limit offset,length就可以获取数据了,但是当offset和length比较大的时候,mysql明显性能下降 1.子查询优化法 先找出第一条数据,然后大于等于这条数据的id就是要获取的数据 缺点:数据必须是连续的,可以说不能有where条件,where条件会筛选数据,导致数据失去连续性,具体方法请看下面的查询实例: 复制代码 代码如下: mysql> set profiling=1; Query OK, 0 rows affected (0.00

  • MySQL对limit查询语句的优化方法

    当我们的网站达到一定的规模时,网站的各种优化是必须要进行的.而网站的优化中,针对数据库各种优化是最重点的了.下面作者将要和大家分享一下MySQL数据库中的查询语句有关limit语句的优化. 大家都知道一般limit是用在分页的程序的分页上的,当你的应用数据量够小的时候,也许你感觉不到limit语句的任何问题,但当查询数据量达到一定程度的时候,limit的性能就会急剧下降.这个是通过大量实例得出来的结论. 下面通过具体的案例来说明,这里是对同一张表在不同的地方取10条数据: (1)offset比较

  • file.mkdir()、file.mkdirs()和file.createNewFile()的区别

    file.mkdir()创建单级文件夹,file.mkdirs()创建多级文件夹,file.createNewFile()创建的是一个文件. 下面通过一个demo来验证一下: public class MainActivity extends AppCompatActivity { @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setConten

  • MySQL查询语句过程和EXPLAIN语句基本概念及其优化

    网站或服务的性能关键点很大程度在于数据库的设计(假设你选择了合适的语言开发框架)以及如何查询数据上. 我们知道MySQL的性能优化方法,一般有建立索引.规避复杂联合查询.设置冗余字段.建立中间表.查询缓存等,也知道用EXPLAIN来查看执行计划. 但对MySQL复杂查询语句执行过程和内部机制,MySQL Optimizer本身所做优化以及查询语句调整对性能所产生的影响及其原因知之甚少. 本文试图对其中的一些关键概念如执行过程.索引使用等做比较深入的探讨,知其然,知其所以然, 这样可以避免在原本通

  • 关于MySQL查询语句的优化详解

    目录 MySQL 优化 子查询优化 待排序的分页查询的优化 给排序字段添加索引 给排序字段跟 select 字段添加复合索引 给排序字段加索引 + 手动回表 解决办法 排序优化 MySQL 优化 子查询优化 将子查询改变为表连接,尤其是在子查询的结果集较大的情况下: 添加复合索引,其中复合索引的包含的字段应该包括 where 字段与关联字段: 复合索引中的字段顺序要遵守最左匹配原则: MySQL 8 中自动对子查询进行优化: 现有两个表 create table Orders ( id inte

  • 基于mysql查询语句的使用详解

    1> 查询数据表除了前三条以外的数据. 起初我想到的是这条语句 SELECT * FROM admin WHERE userid NOT IN (SELECT userid FROM admin ORDER BY userid LIMIT 3) ORDER BY userid DESC 但是运行的时候会报 This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery 这个的意思是表示子查询的时候不支持l

  • MYSQL大量写入问题优化详解

    摘要:大家提到Mysql的性能优化都是注重于优化sql以及索引来提升查询性能,大多数产品或者网站面临的更多的高并发数据读取问题.然而在大量写入数据场景该如何优化呢? 今天这里主要给大家介绍,在有大量写入的场景,进行优化的方案. 总的来说MYSQL数据库写入性能主要受限于数据库自身的配置,以及操作系统的性能,磁盘IO的性能.主要的优化手段包括以下几点: 1.调整数据库参数 (1) innodb_flush_log_at_trx_commit 默认为1,这是数据库的事务提交设置参数,可选值如下: 0

  • MySQL Group by的优化详解

    一个标准的 Group by 语句包含排序.分组.聚合函数,比如 select a,count(*) from t group by a ;  这个语句默认使用 a 进行排序.如果 a 列没有索引,那么就会创建临时表来统计 a和 count(*),然后再通过 sort_buffer 按 a 进行排序. 标准的执行流程 结构: create table t1(id int primary key, a int, b int, index(a)); delimiter ;; create proce

  • mysql update语句的用法详解

    首先,单表的UPDATE语句: UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition] [ORDER BY ...] [LIMIT row_count] 其次,多表的UPDATE语句: UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col_name1=expr1 [, col_n

  • MySQL配置文件my.cnf优化详解(mysql5.5)

    MySQL 5.5.13 参数说明: [client] character-set-server = utf8 port = 3306 socket = /data/mysql/3306/mysql.sock [mysqld] character-set-server = utf8 user = mysql port = 3306 socket = /data/mysql/3306/mysql.sock basedir = /usr/local/webserver/mysql datadir =

  • Mysql索引选择以及优化详解

    索引模型 哈希表 适用于只有等值查询的场景,Memory引擎默认索引 InnoDB支持自适应哈希索引,不可干预,由引擎自行决定是否创建 有序数组:在等值查询和范围查询场景中的性能都非常优秀,但插入和删除数据需要进行数据移动,成本太高.因此,只适用于静态存储引擎 二叉平衡树:每个节点的左儿子小于父节点,父节点又小于右儿子,时间复杂度是 O(log(N)) 多叉平衡树:索引不止存在内存中,还要写到磁盘上.为了让一个查询尽量少地读磁盘,就必须让查询过程访问尽量少的数据块.因此,要使用"N 叉"

  • MySQL查询条件常见用法详解

    本文实例讲述了MySQL查询条件常见用法.分享给大家供大家参考,具体如下: 条件 使用where子句对表中的数据筛选,结果为true的行会出现在结果集中 语法如下: select * from 表名 where 条件; 例: select * from students where id=1; where后面支持多种运算符,进行条件的处理 比较运算符 逻辑运算符 模糊查询 范围查询 空判断 比较运算符 等于: = 大于: > 大于等于: >= 小于: < 小于等于: <= 不等于:

  • mysql查询的控制语句图文详解

    mysql查询的控制语句 字段去重 **关键字:distinct** 语法:select distinct 字段名 from 表名; 案例:对部门进行去重 Select distinct dep from emp; ifnull语法 null与任何数字相加都等于null 使用ifnull可以替换null的值 语法:Select ifnull(可能为空的字段名,如果为空替换成什么)from 表名: 案例: 查看员工月薪与佣金之和 Select sal+ifnull(comm,0) from emp

  • Mysql优化order by语句的方法详解

    本篇文章我们将了解ORDER BY语句的优化,在此之前,你需要对索引有基本的了解,不了解的老少爷们可以先看一下我之前写过的索引相关文章.现在让我们开始吧. MySQL中的两种排序方式 1.通过有序索引顺序扫描直接返回有序数据 因为索引的结构是B+树,索引中的数据是按照一定顺序进行排列的,所以在排序查询中如果能利用索引,就能避免额外的排序操作.EXPLAIN分析查询时,Extra显示为Using index. 2.Filesort排序,对返回的数据进行排序 所有不是通过索引直接返回排序结果的操作都

随机推荐