mysql sql调优

最新推荐文章于 2023-09-07 17:05:49 发布
最新推荐文章于 2023-09-07 17:05:49 发布
阅读量253 收藏
点赞数
文章标签： mysql sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/gy13477001651/article/details/105582242
版权

文章目录


个人感觉mysql的执行优化器和oracle比还是有很大的差距，oracle的很智能，好了，既然使用了mysql就不说这些的了。
要分析sql必然少不了执行计划，只有搞清楚执行激活每个标识的意思，各个子查询的执行顺序，才能做出好的优化

一：mysql执行计划详解

Explain语法
EXPLAIN SELECT ……
变体：

  1. EXPLAIN EXTENDED SELECT ……
    将执行计划“反编译”成SELECT语句，运行SHOW WARNINGS 可得到被MySQL优化器优化后的查询语句
  2. EXPLAIN PARTITIONS SELECT ……
    用于分区表的EXPLAIN
    执行计划包含的信息
    在这里插入图片描述
    id
    包含一组数字，表示查询中执行select子句或操作表的顺序在这里插入图片描述
    id相同，执行顺序由上至下在这里插入图片描述
    如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行 在这里插入图片描述
    id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

select_type
表示查询中每个select子句的类型（简单 OR复杂）
在这里插入图片描述

a.SIMPLE：查询中不包含子查询或者UNION
b.查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY
c.在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY
d.在FROM列表中包含的子查询被标记为：DERIVED（衍生）
e.若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在 FROM子句的子查询中，外层SELECT将被标记为：DERIVED
f.从UNION表获取结果的SELECT被标记为：UNION RESULT

type

表示MySQL在表中找到所需行的方式，又称“访问类型”，常见类型如下：
在这里插入图片描述
由左至右，由最差到最好

a.ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

在这里插入图片描述

b.index：Full Index Scan，index与ALL区别为index类型只遍历索引树
在这里插入图片描述
c.range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询
在这里插入图片描述
range访问类型的不同形式的索引访问性能差异
在这里插入图片描述
d.ref：非唯一性索引扫描，返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找
在这里插入图片描述
在这里插入图片描述

e.eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
在这里插入图片描述

f.const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量
在这里插入图片描述
system是const类型的特例，当查询的表只有一行的情况下， 使用system
g.NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引
在这里插入图片描述
possible_keys

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

key
显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL
TIPS：查询中若使用了覆盖索引，则该索引仅出现在key列表中
在这里插入图片描述

key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度

在这里插入图片描述
在这里插入图片描述
key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

ref
表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

在这里插入图片描述
本例中，由key_len可知t1表的idx_col1_col2被充分使用，col1匹配t2表的col1，col2匹配了一个常量，即 ’ac’

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

在这里插入图片描述
Extra
包含不适合在其他列中显示但十分重要的额外信息

a.Using index

该值表示相应的select操作中使用了覆盖索引（Covering Index）
在这里插入图片描述
TIPS：覆盖索引（Covering Index）
MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件
包含所有满足查询需要的数据的索引称为 覆盖索引（Covering Index）

注意：
如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降

b.Using where

表示MySQL服务器在存储引擎受到记录后进行“后过滤”（Post-filter）,
如果查询未能使用索引，Using where的作用只是提醒我们MySQL将用where子句来过滤结果集

在这里插入图片描述
c.Using temporary

表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询

在这里插入图片描述

d.Using filesort

MySQL中无法利用索引完成的排序操作称为“文件排序”

在这里插入图片描述
在这里插入图片描述

在这里插入图片描述

MySQL执行计划的局限

•EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
•EXPLAIN不考虑各种Cache
•EXPLAIN不能显示MySQL在执行查询时所作的优化工作
•部分统计信息是估算的，并非精确值
•EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划

二：mysql中常见的hint

如果可以通过改写sql来实现，则优先改写sql，不要随意使用hint，一定要严格测试，使用hint后不一定比之前快，不要想当然
有时写的sql,通过查看执行计划，发现没有按照最优的路线走，并且执行缓慢，则可以通过hint提示来调优
对于经常使用oracle的朋友可能知道，oracle的hint功能种类很多，对于优化sql语句提供了很多方法。同样，在mysql里，也有类似的hint功能。下面介绍一些常用的。
强制索引 FORCE INDEX
SELECT * FROM TABLE1 FORCE INDEX (INDEX2) …
以上的SQL语句只使用INDEX2索引。
忽略索引 IGNORE INDEX
SELECT * FROM TABLE1 IGNORE INDEX (FIELD1, FIELD2) …
在上面的SQL语句中，TABLE1表中FIELD1和FIELD2上的索引不被使用。
关闭查询缓冲 SQL_NO_CACHE
SELECT SQL_NO_CACHE field1, field2 FROM TABLE1;
有一些SQL语句需要实时地查询数据，或者并不经常使用（可能一天就执行一两次）,这样就需要把缓冲关了,不管这条SQL语句是否被执行过，服务器都不会在缓冲区中查找，每次都会执行它。
强制查询缓冲 SQL_CACHE
SELECT SQL_CALHE * FROM TABLE1;
如果在my.ini中的query_cache_type设成2，这样只有在使用了SQL_CACHE后，才使用查询缓冲。

优先操作 HIGH_PRIORITY
HIGH_PRIORITY可以使用在select和insert操作中，让MYSQL知道，这个操作优先进行。
SELECT HIGH_PRIORITY * FROM TABLE1;
滞后操作 LOW_PRIORITY
LOW_PRIORITY可以使用在insert和update操作中，让mysql知道，这个操作滞后。
update LOW_PRIORITY table1 set field1= where field1= …
延时插入 INSERT DELAYED
INSERT DELAYED INTO table1 set field1= …
INSERT DELAYED INTO，是客户端提交数据给MySQL，MySQL返回OK状态给客户端。而这时并不是已经将数据插入表，而是存储在内存里面等待排队。当mysql有空余时，再插入。另一个重要的好处是，来自许多客户端的插入被集中在一起，并被编写入一个块。这比执行许多独立的插入要快很多。坏处是，不能返回自动递增的ID，以及系统崩溃时，MySQL还没有来得及插入数据的话，这些数据将会丢失。

强制连接顺序 STRAIGHT_JOIN
SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE …
由上面的SQL语句可知，通过STRAIGHT_JOIN强迫MySQL按TABLE1、TABLE2的顺序连接表。如果你认为按自己的顺序比MySQL推荐的顺序进行连接的效率高的话，就可以通过STRAIGHT_JOIN来确定连接顺序。
STRAIGHT_JOIN只适用于inner join，并不适用于left join，right join，因为left join，right join已经代表指定了表的执行顺序

强制使用临时表 SQL_BUFFER_RESULT
SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE …
当我们查询的结果集中的数据比较多时，可以通过SQL_BUFFER_RESULT.选项强制将结果集放到临时表中，这样就可以很快地释放MySQL的表锁（这样其它的SQL语句就可以对这些记录进行查询了），并且可以长时间地为客户端提供大记录集。
分组使用临时表 SQL_BIG_RESULT和SQL_SMALL_RESULT
SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM TABLE1 GROUP BY FIELD1;
一般用于分组或DISTINCT关键字，这个选项通知MySQL，如果有必要，就将查询结果放到临时表中，甚至在临时表中进行排序。SQL_SMALL_RESULT比起SQL_BIG_RESULT差不多，很少使用。

三：优化例子

1.连接顺序调整，及强制使用索引

1）如下，tb1,大表，不应该被作为开始表，tb4分页子查询，最多只有15条数据，应该被作为开始表，优化前执行时间0.341秒
在这里插入图片描述
2）使用STRAIGHT_JOIN做如下优化，优化后执行时间0.224秒，感觉优化力度不大，有可能是tb1的数据量还不够大
在这里插入图片描述
3）第二步优化后发现连接tb1的时候还是走的全表扫描，没有使用上主键索引，这里强制使用主键索引FORCE INDEX (primary) ，优化后执行时间0.024
在这里插入图片描述

参考文档
https://www.cnblogs.com/ggjucheng/archive/2012/11/11/2765237.html
https://www.cnblogs.com/jpfss/p/11490765.html

得之
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL——如何让你的查询更加高效？全方面优化查询数据
lianghongliang66的博客
05-26 1598
查询性能优化 优化数据访问 查询不需要的记录 MySQL是先返回全部结果集再进行计算 多表关联时返回全部列 总是取出全部列 去除全部列会让优化器无法完成索引覆盖扫描这类优化 重复查询相同的数据 比较好的方案是：初次查询时将这个数据缓存起来，需要的时候从缓存中取出 衡量查询开销的三个指标： 响应时间 扫描的行数 返回的行数 响应时间 看到时先想想这是否一个合理的值 这个查询需要哪些索引，执行计划是什么，需要多少个顺序和随机IO 扫描的行数和返回
官方修正典藏版SQL 语句大全（三）
lghtdw1314的博客
04-14 583
----日期函数---- DAY() --函数返回 date_expression 中的日期值 MONTH() --函数返回date_expression中的月份值 YEAR() --函数返回 date_expression 中的年份值 DATEADD(<datepart>,<number>,date>) --函数返回指定日期 date 加上指定的额外日期间隔 number 产生的新日期 DATEDIFF(<datepart>,<number>,dat
redis 优化 mysql
qq_45980518的博客
01-16 327
文章目录一、实验环境准备二、配置 nginx 并启动三、配置 php-fpm并启动四、配置 mysql 并启动五、测试网站连通姓六、安装 Redis 与 phpredis七、让 php 支持 redis八、进入 MySQL 插入数据九、编写 reids 脚本十浏览器访问测试 一、实验环境准备 [root@dal ~]#yum -y install gcc* [root@dal ~]# unzip redis-mysql.zip [root@dal ~]# cd redis-mysql [root@dal r
十四.Nginx作为负载均衡——实战演练
qq_41650233的博客
08-06 284
配置语法 Syntax：upstream name {...} Default：—— Context：http 演示 准备两台虚拟主机192.168.96.188、192.168.96.188 在188这台主机上创建3个html静态文件 vi /opt/app/code1/index.html <html> <head> <meta char...
Mysql一些优化方式及Redis一些介绍及安装使用方式
qq_34302802的博客
05-22 214
1、mysql优化型问题 ① explain的使用： 可以对索引进行查阅，查看给制定字段添加索引是否成功。若成功则显示1，不成功则会将整张表中的数据条数全部显示出来。 ② 在设计表时候考虑主键选型，测试会发现int速度远远高于string类型 ③ 在设计表字段时，尽量写见名知意的字母拼接，尽量不要将当前表中的每个字段名都写的很长，这会影响查询速度 ④ 避免使用select *，需要查询什么字段用什么字段，不然会将整张表中的所有字段全部扫描，影响查询速度 ⑤ 对查询量很高的字段建立索引（普通索引、
总结MySQL、Redis的优化措施与使用 mysql_upgrade升级数据结构
ZHUZIH6的博客
04-04 679
总结mysql、redis优化措施与解决mysql报错：ERROR 3009 (HY000): Column count of mysql.user is wrong. Expected 45, found 42. Created with MySQL 50556, now running 50741. Please use mysql_upgrade to fix this error.
mysql查询提示_MySQL自成一派的查询提示
weixin_42389113的博客
01-28 621
【查询提示】MySQL中可以给select语句各种提示，比如告诉它“查询的结果集特别大，请直接用磁盘临时表”，“请让这条select优先执行” ....【查询提示：与结果集相关】与结果集相关的查询提示只有两个“distinct”,“all”1): distinct 表示去掉重复行2): all 表示不要去重(也就是说默认的就是all)select all id,name from person ;...
mysql 笔记
回南山种豆的专栏
09-05 3433
转自：http://snailxr.iteye.com/blog/1143508 Mysql中的Null 逻辑比较中如果出现null整个值就是null,如 select 2=null 返回为null 如果要查找为null的值应使用is null,而不能用=null如： Select id fromuser where name is null; 包含null的表达式总会导出null，
Mysql SQL优化基础
爱与不爱，一念之间
01-11 338
sql的查询优化 查询速度为什么这么慢？ 优化数据访问 查询缓存 查询优化器的提示 优化特定类型的查询 查询速度为什么这么慢？ &nbsp；如果把查询看作是一个任务,那么它由一系列子任务组成,每个子任务都会消耗一定的时间。如果要优化查询,实际上要优化其子任务,要么消除其中一些子任务,要么减少子任务的执行次数,要么让子任务运行的更快 (通常来说,查直询的生命周期大致可以按照顺字来看:从客...
MySQL执行顺序及优化
qq_48157004的博客
01-07 2775
在上述例子中，如果我们join_buffer更小，被驱动表就会被多次扫描，而且这个语句执行时间超过 1 秒，就会在再次扫描t2表的时候，把t2表的数据页移到 LRU 链表头部。在多个条件下，无法判断哪一个会先执行，往往会先执行1=1，在大数据执行情况下会消耗MySQL的性能，在使用mybatis时，可以用where标签代替。max_length_for_sort_data表示MySQL用于排序行数据的长度的一个参数，如果单行的长度超过这个值，MySQL 就认为单行太大，就换rowid 排序。
京东十年老架构：MySQL——GROUP BY详解与优化
weixin_71690375的博客
06-01 531
以下是GROUP BY子句的基本语法：sql复制代码其中，col1col2, ...是要分组的列名，是用于聚合数据的函数，如SUMAVGMAXMIN等。table_name是要从中检索数据的表的名称，condition是可选的查询条件。
MySQL hint
lianzhouxiaowu的专栏
07-25 615
STRAIGHT_JOIN多表连结时，MySQL优化器要确定以谁为驱动表，也就是说以哪个表为基准。在处理此类问题时，MySQL优化器采用了简单粗暴的解决方法：哪个表的结果集小，就以哪个表为驱动表，当然MySQL优化器实际的处理方式会复杂许多，具体可以参考： [MySQL优化器如何选择索引和JOIN顺序](http://www.orczhou.com/index.php/2013/04/how-mys
SQL优化思路+经典案例分析
m0_71777195的博客
10-07 1895
大家好，捡田螺的小男孩。SQL调优这块呢，大厂面试必问的。最近金九银十嘛，所以整理了SQL的调优思路，并且附几个经典案例分析。
mysql 一个表同时只有一个线程访问_MySql学习(七) —— 查询性能优化 深入理解MySql如何执行查询...
weixin_39649660的博客
11-28 462
本篇深入了解查询优化和服务器的内部机制，了解MySql如何执行特定查询，从中也可以知道如何更改查询执行计划，当我们深入理解MySql如何真正地执行查询，明白高效和低效的真正含义，在实际应用中就能扬长避短。声明：本人使用的数据库版本为MySql 5.1回到顶部一、基本原则：优化数据访问查询性能低下的最基本原因就是访问了太多数据，一些查询要不可避免地筛选大量的数据，大部分性能欠佳的查询都可以用减少数据...
看一遍就理解：数据库group by详解
程序员小灰的博客
07-20 7996
前言 大家好，我是程序员小灰。日常开发中，我们经常会使用到group by。亲爱的小伙伴，你是否知道group by的工作原理呢？group by和having有什么区别呢？group by的优化思路是怎样的呢？使用group by有哪些需要注意的问题呢？本文将跟大家一起来学习，攻克group by~使用group by的简单例子group by 工作原理group by + where 和 ha...
阿里p8总结的10条 SQL 优化方案（非常实用）
aa119101的博客
06-13 4884
一、SQL语句及索引的优化SQL语句的优化索引的优化/如何避免索引失效二、数据库表结构的优化：使得数据库结构符合三大范式与BCNF三、系统配置的优化四、硬件的优化在开始介绍如何优化sql前，先附上mysql内部逻辑图让大家有所了解（1）连接器： 主要负责跟客户端建立连接、获取权限、维持和管理连接（2）查询缓存： 优先在缓存中进行查询，如果查到了则直接返回，如果缓存中查询不到，在去数据库中查询。MySQL缓存是默认关闭的，也就是说不推荐使用缓存，并且在MySQL8.0 版本已经将查询缓存的整块功能删掉了。这主
SQL数据库查询group by优化策略
最新发布
09-07 481
因为如果group by 的过程因为内存临时表放不下数据，从而用到磁盘临时表的话，是比较耗时的。group by 就是分组统计的意思，一般情况都是配合聚合函数如（count(),sum(),avg(),max(),min())一起使用。当然，平时大家使用的时候，group by还是配合聚合函数使用的，除非一些特殊场景，比如你想去重，当然去重用distinct也是可以的。如果执行过程中，会发现内存临时表大小到达了上限（控制这个上限的参数就是tmp_table_size），会把内存临时表转成磁盘临时表。
03.19 Day 60 - 重温 Day 50-54
stevenchen1989的博客
03-19 182
大家好，我是 Snow Hide，作为《MySQL 实战》这个专栏的学员之一，这是我打卡的第 60 天，也是我第 122 次进行这种操作。 今天我温习了该专栏里叫《到底可不可以使用join？》、《join语句怎么优化？》、《什么时候会使用内部临时表？》、《insert语句的锁为什么这么多？》、《grant之后要跟着flush privileges吗？》、《为什么临时表可以重名？》的文章。 关键词总...
MySQL 数据库 group by 语句怎么优化？
Q54665642ljf的博客
11-28 4636
首先 group by 用到索引，那就在索引树上索引数据，但是因为加了 where 条件，还是需要在去表里检索几乎所有的数据， 这样子，还不如直接去表里进行全表扫，这样还更快些。就比如上面这个 sql，如果存在这个商品的名称被修改过了，但是它们的 id 确还是一样的，那么这个时候展示的商品名称是修改前的还是修改后的呢?注意这里的临时表可能是内存上的临时表，也有可能是硬盘上的临时表，理所当然基于内存的临时表的时间消耗肯定要比基于硬盘的临时表的实际消耗小。因为上面也说了，临时表也分为内存临时表和磁盘临时表。
SQL使用using关键字详解
热门推荐
shadow_zed的博客
04-08 3万+
sql/92标准可以使用using关键字来简化连接查询，但是只是在查询满足下面两个条件时，才能使 用using关键字进行简化。1.查询必须是等值连接。2.等值连接中的列必须具有相同的名称和数据类型。 例如：使用using关键字，如下： select emptno,ename,sal,deptno,dname from emp e inner join dept d using(deptno);...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值