MySQL定位并优化慢查询sql的详细实例
目录
- 1.如何定位并优化慢查询sql
- a.根据慢日志定位慢查询sql
- b.使用explain等工具分析sql
- c.修改sql或者尽量让sql走索引
- 2.联合索引的最左匹配原则的成因
- 简单说下什么是最左匹配原则
- 最左匹配原则的原理
- 3.索引是建立得越多越好吗
- 总结
1.如何定位并优化慢查询sql
a.根据慢日志定位慢查询sql
SHOW VARIABLES LIKE '%query%' 查询慢日志相关信息
slow_query_log 默认是off关闭的，使用时，需要改为on 打开
slow_query_log_file 记录的是慢日志的记录文件
long_query_time 默认是10S，每次执行的sql达到这个时长，就会被记录
SHOW STATUS LIKE '%slow_queries%' 查看慢查询状态
Slow_queries 记录的是慢查询数量 当有一条sql执行一次比较慢时，这个vlue就是1 （记录的是本次会话的慢sql条数）
注意：
如何打开慢查询 ： SET GLOBAL slow_query_log = ON;
将默认时间改为1S： SET GLOBAL long_query_time = 1;
（设置完需要重新连接数据库，PS：仅在这里改的话，当再次重启数据库服务时，所有设置又会自动恢复成默认值，永久改变需去my.ini中改）
b.使用explain等工具分析sql
在要执行的sql前加上explain 例如：EXPLAIN SELECT menu_name FROM t_sys_menu ORDER BY menu_id DESC;
接着看explain的关键字段
type：
如果发现type的值是最后两个中的其中一个时，证明语句需要优化了。
extra：
c.修改sql或者尽量让sql走索引
mysql查询优化器会根据具体情况自己判断走哪个索引，不一定是走主键（explain中的key可以看到走的哪个key）具体情况根据具体情况来定，当你要强制执行走某一个key时：
在查询的最后加上 force index(primary); 强制走主键的
2.联合索引的最左匹配原则的成因
简单说下什么是最左匹配原则
顾名思义：最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。
例如：b = 2 如果建立(a,b)顺序的索引，是匹配不到(a,b)索引的；但是如果查询条件是a = 1 and b = 2或者a=1(又或者是b = 2 and b = 1)就可以，因为优化器会自动调整a,b的顺序。再比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，因为c字段是一个范围查询，它之后的字段会停止匹配。
最左匹配原则的原理
最左匹配原则都是针对联合索引来说的，所以我们有必要了解一下联合索引的原理。了解了联合索引，那么为什么会有最左匹配原则这种说法也就理解了。
我们都知道索引的底层是一颗B+树，那么联合索引当然还是一颗B+树，只不过联合索引的健值数量不是一个，而是多个。构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树。
例子：假如创建一个（a,b)的联合索引，那么它的索引树是这样的
可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。所以b = 2这种查询条件没有办法利用索引，因为联合索引首先是按a排序的，b是无序的。
同时我们还可以发现在a值相等的情况下，b值又是按顺序排列的，但是这种顺序是相对的。所以最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引。例如a = 1 and b = 2 a,b字段都可以使用索引，因为在a值确定的情况下b是相对有序的，而a>1and b=2，a字段可以匹配上索引，但b值不可以，因为a的值是一个范围，在这个范围中b是无序的。
成因：
当通过（col3，col2）这样的联合索引去查找时，可以看到也是一个B+树的结构向下查找，若直接通过col2去查找，无法直接查找到34、77。也就用不到这个联合索引了。
3.索引是建立得越多越好吗
1.数据量小的表不需要建立索引，建立会增加额外的索引开销。
2.数据变更需要维护索引，因此更多的索引意味着更多的维护成本。
3.更多的索引意味着也需要更多的空间。
总结
到此这篇关于MySQL定位并优化慢查询sql的文章就介绍到这了,更多相关MySQL定位优化慢查询sql内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！
相关推荐
-
记一次因线上mysql优化器误判引起慢查询事件
前言: 收到疯狂的慢查询及请求超时报警,通过metrics分析出来自mysql请求的异常,cli -> show proceslist 看到很多慢查询. 先前该sql是没有的,后面因为数据量的增长才出现了这问题. 虽然feeds表大到一个亿,但因为feeds流信息有近期热的特征,所以不是因为 innodb_buffer_pool_size 低效引起的io频繁. 后来经过进一步explain执行计划分析得出了原因,mysql查询优化器选择了他认为高效的索引. mysql查询优化器大多数情况是靠谱的
-
Mysql慢查询优化方法及优化原则
1.日期大小的比较,传到xml中的日期格式要符合'yyyy-MM-dd',这样才能走索引,如:'yyyy'改为'yyyy-MM-dd','yyyy-MM'改为'yyyy-MM-dd'[这样MYSQL会转换为日期类型] 2.条件语句中无论是等于.还是大于小于,WHERE左侧的条件查询字段不要使用函数或表达式或数学运算 3.WHERE条件语句尝试着调整字段的顺序提升查询速度,如把索引字段放在最前面.把查询命中率高的字段置前等 4.保证优化SQL前后其查询结果是一致的 5.在查询的时候通过将EXPLA
-
MySQL慢查询如何定位详解
前言 相信大家在平时工作中都有过 SQL 优化经历,那么在优化前就必须找到慢 SQL 方可进行分析.这篇文章就介绍下如何定位到慢查询. 慢查询日志是 MySQL 内置的一项功能,可以记录执行超过指定时间的 SQL 语句. 以下是慢查询的相关参数,大家感兴趣的可以看下: 参数 含义 log_output 日志输出位置,默认为 FILE,即保存为文件,若设置为 TABLE,则将日志记录到 mysql.show_log 表中,支持设置多种格式 slow_query_log_file 指定慢查询日志文件
-
简单谈谈MySQL优化利器-慢查询
慢查询 首先,无论进行何种优化,开启慢查询都算是前置条件.慢查询机制,将记录过慢的查询语句(事件),从而为DB维护人员提供优化目标. 检查慢查询是否开启 通过show variables like 'slow_query_log'这条语句,可以找到慢查询的状态(On/Off). 开启慢查询 本文使用的MySQL版本:MariaDB - 10.1.19,请注意,不同版本的MySQL存在差异. 在[mysqld]下加入: [mysqld] port= 3306 slow-query-log=1 #
-
通过MySQL慢查询优化MySQL性能的方法讲解
随着访问量的上升,MySQL数据库的压力就越大,几乎大部分使用MySQL架构的web应用在数据库上都会出现性能问题,通过mysql慢查询日志跟踪有问题的查询非常有用,可以分析出当前程序里有很耗费资源的sql语句. 慢查询日志我们可以通过my.cnf文件设置开启,下面先来看一下相关参数的意义 log-slow-queries <slow_query_log_file> 存放slow query日志的文件.你必须保证mysql server进程mysqld_safe进程用户对该文件有w权限. lo
-
MySQL慢查询优化之慢查询日志分析的实例教程
数据库响应慢问题最多的就是查询了.现在大部分数据库都提供了性能分析的帮助手段.例如Oracle中会帮你直接找出慢的语句,并且提供优化方案.在MySQL中就要自己开启慢日志记录加以分析(记录可以保存在表或者文件中,默认是保存在文件中,我们系统使用的就是默认方式). 先看看MySQL慢查询日志里面的记录长什么样的: Time Id Command Argument # Time: 141010 9:33:57 # User@Host: root[root] @ localhost [] Id: 1
-
MySQL定位并优化慢查询sql的详细实例
目录 1.如何定位并优化慢查询sql a.根据慢日志定位慢查询sql b.使用explain等工具分析sql c.修改sql或者尽量让sql走索引 2.联合索引的最左匹配原则的成因 简单说下什么是最左匹配原则 最左匹配原则的原理 3.索引是建立得越多越好吗 总结 1.如何定位并优化慢查询sql a.根据慢日志定位慢查询sql SHOW VARIABLES LIKE '%query%' 查询慢日志相关信息 slow_query_log 默认是off关闭的,使用时,需要改为on 打开 slow_qu
-
MySql中如何使用 explain 查询 SQL 的执行计划
explain命令是查看查询优化器如何决定执行查询的主要方法. 这个功能有局限性,并不总会说出真相,但它的输出是可以获取的最好信息,值得花时间去了解,因为可以学习到查询是如何执行的. 1.什么是MySQL执行计划 要对执行计划有个比较好的理解,需要先对MySQL的基础结构及查询基本原理有简单的了解. MySQL本身的功能架构分为三个部分,分别是 应用层.逻辑层.物理层,不只是MySQL ,其他大多数数据库产品都是按这种架构来进行划分的. 应用层,主要负责与客户端进行交互,建立链接,记住链接状态,
-
MySQL中数据库优化的常见sql语句总结
目录 1.SHOW ENGINES 2.SHOW PROCESSLIST 3.SHOW STATUS LIKE 'InnoDB_row_lock%' 4.SHOW ENGINE INNODB STATUS 5.SHOW INDEXS 6.ALTER TABLE xx ENGINE = INNODB 7.ANALYZE TABLE 1.SHOW ENGINES 查看执行引擎以及默认引擎. 2.SHOW PROCESSLIST SHOW PROCESSLIST查看当前数据库连接的使用情况,以及各种状
-
查询mysql中执行效率低的sql语句的方法
一些小技巧1. 如何查出效率低的语句?在MySQL下,在启动参数中设置 --log-slow-queries=[文件名],就可以在指定的日志文件中记录执行时间超过long_query_time(缺省为10秒)的SQL语句.你也可以在启动配置文件中修改long query的时间,如: 复制代码 代码如下: # Set long query time to 8 seconds long_query_time=8 2. 如何查询某表的索引?可使用SHOW INDEX语句,如: 复制代码 代码如下
-
MySql批量插入优化Sql执行效率实例详解
MySql批量插入优化Sql执行效率实例详解 itemcontractprice数量1万左右,每条itemcontractprice 插入5条日志. updateInsertSql.AppendFormat("UPDATE itemcontractprice AS p INNER JOIN foreigncurrency AS f ON p.ForeignCurrencyId = f.ContractPriceId SET p.RemainPrice = f.RemainPrice * {0},
-
MySQL性能优化 出题业务SQL优化
先简单介绍一下项目背景.这是一个在线考试练习平台,数据库使用MySQL,表结构如图所示: Question是存储题目的表,数据量在3万左右.AnswerResult表是存储用户作答结果的表,分表之后单表记录大概在300万-400万. 需求:根据用户的作答结果出练习卷,题目的优先级为:未做过的题目>只做错的题目>做错又做对的题目>只做对的题目. 在"做错又做对的题目"中,会按错误次数和正确次数的比例进行权重计算,比如:A.做错10次,做对100次:B.做错10次,做对2
-
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数据库性能优化之子查询
记得在做项目的时候, 听到过一句话, 尽量不要使用子查询, 那么这一篇就来看一下, 这句话是否是正确的. 那在这之前, 需要介绍一些概念性东西和mysql对语句的大致处理. 当Mysql Server的连接线程接收到Client发送过来的SQL请求后, 会经过一系列的分解Parse, 进行相应的分析, 然后Mysql会通过查询优化器模块, 根据该Sql所涉及到的数据表的相关统计信息进行计算分析. 然后在得出一个Mysql自认为最合理最优化的数据访问方式, 也就是我们常说的"执行计划",
-
MySQL巧用sum、case和when优化统计查询
最近在公司做项目,涉及到开发统计报表相关的任务,由于数据量相对较多,之前写的查询语句查询五十万条数据大概需要十秒左右的样子,后来经过老大的指点利用sum,case...when...重写SQL性能一下子提高到一秒钟就解决了.这里为了简洁明了的阐述问题和解决的方法,我简化一下需求模型. 现在数据库有一张订单表(经过简化的中间表),表结构如下: CREATE TABLE `statistic_order` ( `oid` bigint(20) NOT NULL, `o_source` varchar