京东联盟网站建设电脑版,网店推广方法有哪些,怎样建立网站视频教程,织梦的手机端网站模板下载地址一.知识回顾
学习本篇文章之前呢#xff0c;我们可以先看一下【强烈建议收藏:MySQL面试必问系列之SQL语句执行专题】#xff0c;看完这篇文章再来学习本篇文章可谓是如虎添翼。好的#xff0c;那我们也不讲太多的废话#xff0c;直接开始。
二.如何做慢SQL查询优化呢我们可以先看一下【强烈建议收藏:MySQL面试必问系列之SQL语句执行专题】看完这篇文章再来学习本篇文章可谓是如虎添翼。好的那我们也不讲太多的废话直接开始。
二.如何做慢SQL查询优化呢
2.1 MySQL 慢查询的相关参数解释
slow_query_log是否开启慢查询日志ON(1)表示开启, OFF(0) 表示关闭。slow-query-log-file新版5.6及以上版本MySQL数据库慢查询日志存储路径。long_query_time 慢查询阈值当查询时间多于设定的阈值时记录日志。log_output: 是指定日志的存储方式。log-queries-not-using-indexes未使用索引的查询也被记录到慢查询日志中。如果调优的话建议开启这个选项。
2.2 慢查询配置方式
默认情况下slow_query_log的值为OFF表示慢查询日志是禁用的,我们可以通过以下的命令查看当前慢查询日志是否已经开发
mysql show variables like %slow_query_log%;
---------------------------------------------------
| Variable_name | Value |
---------------------------------------------------
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/test-slow.log |
---------------------------------------------------可以通过设置slow_query_log的值来开启命令实操如下所示:
mysql set global slow_query_log1;使用 set global slow_query_log1命令开启了慢查询日志此时只对当前数据库生效MySQL重启后则会失效。如果要永久生效就必须修改配置文件my.cnf其它系统变量也是如此 特别说明:以下实操是在Linux操作系统上完成的。 打开my.cnf配置文件
# 编辑配置
vim /etc/my.cnf添加如下内容
slow_query_log 1
slow_query_log_file/var/lib/mysql/ruyuan-slow.log配置成功后重启MySQL
service mysqld restart
mysql show variables like %slow_query%;
-----------------------------------------------------
| Variable_name | Value |
-----------------------------------------------------
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/ruyuan-slow.log |
-----------------------------------------------------开启了慢查询日志后什么样的SQL才会记录到慢查询日志里面呢 这个是由参数 long_query_time控制默认情况下long_query_time的值为10秒。
mysql show variables like long_query_time;
----------------------------
| Variable_name | Value |
----------------------------
| long_query_time | 10.000000 |
----------------------------mysql set global long_query_time5;
Query OK, 0 rows affected (0.00 sec)mysql show variables like long_query_time;
----------------------------
| Variable_name | Value |
----------------------------
| long_query_time | 10.000000 |
----------------------------修改了变量long_query_time但是查询变量long_query_time的值还是5我们修改了为什么没有显示呢 注意使用命令 set global long_query_time5 修改后需要重新连接或新开一个会话才能看到修改值。
mysql show variables like long_query_time;
---------------------------
| Variable_name | Value |
---------------------------
| long_query_time | 5.000000 |
---------------------------log_output 参数是指定日志的存储方式。log_outputFILE 表示将日志存入文件默认值是’FILE’。log_outputTABLE 表示将日志存入数据库这样日志信息就会被写入到 mysql.slow_log 表中。
mysql SHOW VARIABLES LIKE %log_output%;
----------------------
| Variable_name | Value |
----------------------
| log_output | FILE |
----------------------MySQL数据库支持同时两种日志存储方式配置的时候以逗号隔开即可如log_output‘FILE,TABLE’。日志记录到系统的专用日志表中要比记录到文件耗费更多的系统资源因此对于需要启用慢查询日志又需要能够获得更高的系统性能那么建议优先记录到文件。 系统变量 log-queries-not-using-indexes未使用索引的查询也被记录到慢查询日志中默认是关闭的。
mysql show variables like log_queries_not_using_indexes;
--------------------------------------
| Variable_name | Value |
--------------------------------------
| log_queries_not_using_indexes | OFF |
--------------------------------------mysql set global log_queries_not_using_indexes1;
Query OK, 0 rows affected (0.00 sec)mysql show variables like log_queries_not_using_indexes;
--------------------------------------
| Variable_name | Value |
--------------------------------------
| log_queries_not_using_indexes | ON |
--------------------------------------2.3 慢查询测试
执行 test_index.sql 脚本,监控慢查询日志内容
[rootlocalhost mysql]# tail -f /var/lib/mysql/ruyuan-slow.log
/usr/sbin/mysqld, Version: 5.7.30-log (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument执行下面的SQL,执行超时 超过我们刚才设置的5s 我们去查看慢查询日志
SELECT * FROM test_index WHERE name jack OR id 1 OR name tom OR id 2;查看日志内容并对日志做分析
我们得到慢查询日志后最重要的一步就是去分析这个日志。我们先来看下慢日志里到底记录了哪些内容。
如下所示该内容是慢日志里其中一条SQL的记录内容可以看到有时间戳用户查询时长及具体的SQL等信息
# Time: 2022-02-23T13:50:45.005959Z
# UserHost: root[root] localhost [] Id: 3
# Query_time: 6.724273 Lock_time: 0.000371 Rows_sent: 5 Rows_examined: 5000000
SET timestamp1842325245;
SELECT * FROM test_index WHERE name jack OR id 1 OR name tom OR id 2;Time: 执行时间User: 用户信息 ,Id信息Query_time: 查询时长Lock_time: 等待锁的时长Rows_sent:查询结果的行数Rows_examined: 查询扫描的行数SET timestamp: 时间戳SQL的具体信息
2.4 慢查询SQL优化思路
2.4.1 SQL性能下降的原因
等待时间长锁表导致查询一直处于等待状态之前的文章中我们学习过MySQL锁的机制需要的同学可以看一下之前的文章。执行时间长导致执行时间长的原因也有很多比如说我们的查询语句写的有问题、查询没有走索引导致失效失效、 关联查询过多、服务器调优及各个参数的设置等方面。
2.4.2 慢查询优化思路 优先选择优化高并发执行的SQL,因为高并发的SQL发生问题带来后果更严重。 比如下面两种情况: SQL1: 每小时执行10000次, 每次40个IO 优化后每次35个IO,每小时节省5万次IO SQL2: 每小时执行10次,每次40000个IO,每次优化减少5000个IO,每小时节省5万次IO SQL2更难优化,SQL1更好优化.但是第一种属于高并发SQL,更急需优化 成本更低 定位优化对象的性能瓶颈(一定要在优化之前了解性能瓶颈在哪不能没有目标的瞎优化) 在优化SQL时,选择优化分方向有三个: 1.IO(数据访问消耗的了太多的时间,查看是否正确使用了索引) , 2.CPU(数据运算花费了太多时间, 数据的运算分组 排序是不是有问题) 3.网络带宽(加大网络带宽) 明确优化目标 需要根据数据库当前的状态、数据库中与该条SQL的关系、当前SQL的具体功能 、最好的情况消耗的资源,最差情况下消耗的资源,优化的结果只有一个给用户一个好的体验。说到底还是要写出好的SQL语句。 explain执行计划分析慢SQL语句 explain可以展示当前SQL的执行状态具体的一些细节我们后面展开学习。 永远用小的结果集驱动大的结果集 小的数据集驱动大的数据集,减少内层表读取的次数类似于嵌套循环。如果小的循环在外层,对于数据库连接来说就只连接10次,进行10000次操作,如果1000在外,则需要进行1000次数据库连接,从而浪费资源增加消耗.这就是为什么要小表驱动大表。 for(int i 0; i 10; i){for(int i 0; i 1000; i){//具体操作}
}尽可能在索引中完成排序 排序操作用的比较多,order by 后面的字段如果在索引中,索引本来就是排好序的,所以速度很快,没有索引的话,就需要从表中拿数据,在内存中进行排序,如果内存空间不够还会发生落盘操作 不要使用select *,只获取自己需要的列 不要使用select * ,select * 很可能不走索引,而且数据量过大 只使用最有效的过滤条件 误区 where后面的条件越多越好,但实际上是应该用最短的路径访问到数据 尽可能避免复杂的join和子查询 每条SQL的JOIN操作 建议不要超过三张表 将复杂的SQL, 拆分成多个小的SQL 单个表执行,获取的结果 在程序中进行封装 如果join占用的资源比较多,会导致其他进程等待时间变长 合理设计并利用索引 后续我们会专门开一个专题来学习MySQL索引这块的知识内容。此处先做一个简单的了解。 问题如何判定是否需要创建索引? 1.较为频繁的作为查询条件的字段应该创建索引. 2.唯一性太差的字段不适合单独创建索引即使频繁作为查询条件.唯一性太差的字段主要是指哪些呢如状态字段类型字段等等这些字段中的数据可能总共就是那么几个几十个数值重复使用当一条Query所返回的数据超过了全表的15%的时候就不应该再使用索引扫描来完成这个Query了. 3.更新非常频繁的字段不适合创建索引.因为索引中的字段被更新的时候不仅仅需要更新表中的数据同时还要更新索引数据以确保索引信息是准确的. 4.不会出现在WHERE子句中的字段不该创建索引. 问题2:如何选择合适索引? 1.对于单键索引尽量选择针对当前Query过滤性更好的索引. 2.选择联合索引时,当前Query中过滤性最好的字段在索引字段顺序中排列要靠前. 3.选择联合索引时,尽量索引字段出现在where中比较多的索引.
三.上面提到的explain详细讲讲有哪些主要字段
3.1 explain分析SQL语句的性能瓶颈
使用 explain 关键字可以模拟优化器来执行SQL查询语句从而知道MySQL是如何处理我们的SQL语句的。分析出查询语句或是表结构的性能瓶颈。
MySQL查询过程 通过explain我们可以获得以下信息
表的读取顺序数据读取操作的操作类型哪些索引可以被使用哪些索引真正被使用表的直接引用每张表的有多少行被优化器查询了
Explain使用方式: explainsql语句, 通过执行explain可以获得sql语句执行的相关信息
explain select * from users;3.2 explain解释执行后的SQL语句有哪些字段
idselect 查询序列号。id相同执行顺序由上至下id不同id值越大优先级越高越先被执行。select_type查询数据的操作类型其值如下 simple简单查询不包含子查询或 union primary:包含复杂的子查询最外层查询标记为该值 subquery在 select 或 where 包含子查询被标记为该值 derived在 from 列表中包含的子查询被标记为该值MySQL 会递归执行这些子查询把结果放在临时表 union若第二个 select 出现在 union 之后则被标记为该值。若 union 包含在 from 的子查询中外层 select 被标记为 derived union result从 union 表获取结果的 select table显示该行数据是关于哪张表partitions匹配的分区type表的连接类型其值性能由高到底排列如下 (1)type字段显示的是表示的是用什么样的方式来获取数据它描述了找到所需数据所使用的扫描方式, 是较为重要的一个指标。 (2)一般来说,需要保证查询至少达到 range级别,最好能到ref,否则就要就行SQL的优化调整。 下面介绍type字段不同值表示的含义:
type类型解释system不进行磁盘IO,查询系统表,仅仅返回一条数据const查找主键索引,最多返回1条或0条数据. 属于精确查找eq_ref查找唯一性索引,返回数据最多一条, 属于精确查找ref查找非唯一性索引,返回匹配某一条件的多条数据,属于精确查找,数据返回可能是多条.range查找某个索引的部分索引,只检索给定范围的行,属于范围查找. 比如: 、 、in 、betweenindex查找所有索引树,比ALL快一些,因为索引文件要比数据文件小.ALL不使用任何索引,直接进行全表扫描
possible_keys显示 MySQL 理论上使用的索引查询涉及到的字段上若存在索引则该索引将被列出但不一定被查询实际使用。如果该值为 NULL说明没有使用索引可以建立索引提高性能key显示 MySQL 实际使用的索引。如果为 NULL则没有使用索引查询key_len表示索引中使用的字节数通过该列计算查询中使用的索引的长度。在不损失精确性的情况下长度越短越好 显示的是索引字段的最大长度并非实际使用长度ref显示该表的索引字段关联了哪张表的哪个字段rows根据表统计信息及选用情况大致估算出找到所需的记录或所需读取的行数数值越小越好filtered返回结果的行数占读取行数的百分比值越大越好extra包含不合适在其他列中显示但十分重要的额外信息常见的值如下 extra是 explain输出中另外一个很重要的列该列显示MySQL在查询过程中的一些详细信息如下所示:
extra类型解释Using filesortMySQL中无法利用索引完成的排序操作称为 “文件排序”Using index表示直接访问索引就能够获取到所需要的数据覆盖索引不需要通过索引回表Using index condition搜索条件中虽然出现了索引列但是有部分条件无法使用索引 会根据能用索引的条件先搜索一遍再匹配无法使用索引的条件。Using join buffer使用了连接缓存, 会显示join连接查询时,MySQL选择的查询算法Using temporary表示MySQL需要使用临时表来存储结果集常见于排序和分组查询Using where意味着全表扫描或者在查找使用索引的情况下但是还有查询条件不在索引字段当中
四.如何进行分页查询优化
4.1 分页查询格式
一般的分页查询使用简单的 limit 子句就可以实现。limit格式如下
SELECT * FROM 表名 LIMIT [offset,] rows第一个参数指定第一个返回记录行的偏移量注意从0开始第二个参数指定返回记录行的最大数目如果只给定一个参数它表示返回最大的记录行数目
4.2 偏移量和返回的数目对分页查询效率的影响
思考1如果偏移量固定返回记录量对执行时间有什么影响
select * from user limit 10000,1;
select * from user limit 10000,10;
select * from user limit 10000,100;
select * from user limit 10000,1000;
select * from user limit 10000,10000;结果在查询记录时返回记录量低于100条查询时间基本没有变化差距不大。随着查询记录量越大所花费的时间也会越来越多。
思考2如果查询偏移量变化返回记录数固定对执行时间有什么影响
select * from user limit 1,100;
select * from user limit 10,100;
select * from user limit 100,100;
select * from user limit 1000,100;
select * from user limit 10000,100;结果在查询记录时如果查询记录量相同偏移量超过100后就开始随着偏移量增大查询时间急剧的增加。
4.3 分页查询优化
优化1: 通过索引进行分页
直接进行limit操作会产生全表扫描,速度很慢。Limit限制的是从结果集的M位置处取出N条输出,其余抛弃。假设ID是连续递增的,我们根据查询的页数和查询的记录数可以算出查询的id的范围然后配合 limit使用
EXPLAIN SELECT * FROM user WHERE id 100001 LIMIT 100;优化2利用子查询优化
首先定位偏移位置的id通过子查询找到比较的值使用覆盖索引进行优化。
# 根据获取到的id值向后查询
EXPLAIN SELECT * FROM user_contacts WHERE id
(SELECT id FROM user_contacts LIMIT 100000,1) LIMIT 100;特别感谢:部分引用来自马士兵教育 文章转载自： http://www.morning.wqbbc.cn.gov.cn.wqbbc.cn http://www.morning.qfwfj.cn.gov.cn.qfwfj.cn http://www.morning.ljsxg.cn.gov.cn.ljsxg.cn http://www.morning.dzdtj.cn.gov.cn.dzdtj.cn http://www.morning.rdlxh.cn.gov.cn.rdlxh.cn http://www.morning.qmsbr.cn.gov.cn.qmsbr.cn http://www.morning.gyxwh.cn.gov.cn.gyxwh.cn http://www.morning.bbgn.cn.gov.cn.bbgn.cn http://www.morning.pbygt.cn.gov.cn.pbygt.cn http://www.morning.bzfld.cn.gov.cn.bzfld.cn http://www.morning.hjwzpt.com.gov.cn.hjwzpt.com http://www.morning.phtqr.cn.gov.cn.phtqr.cn http://www.morning.rbqlw.cn.gov.cn.rbqlw.cn http://www.morning.cfnht.cn.gov.cn.cfnht.cn http://www.morning.zlnmm.cn.gov.cn.zlnmm.cn http://www.morning.elmtw.cn.gov.cn.elmtw.cn http://www.morning.dlwzm.cn.gov.cn.dlwzm.cn http://www.morning.bssjp.cn.gov.cn.bssjp.cn http://www.morning.ncqzb.cn.gov.cn.ncqzb.cn http://www.morning.zmbzl.cn.gov.cn.zmbzl.cn http://www.morning.tymwx.cn.gov.cn.tymwx.cn http://www.morning.mrbmc.cn.gov.cn.mrbmc.cn http://www.morning.dbrpl.cn.gov.cn.dbrpl.cn http://www.morning.xjkfb.cn.gov.cn.xjkfb.cn http://www.morning.tbrnl.cn.gov.cn.tbrnl.cn http://www.morning.zpqk.cn.gov.cn.zpqk.cn http://www.morning.fmswb.cn.gov.cn.fmswb.cn http://www.morning.rqlbp.cn.gov.cn.rqlbp.cn http://www.morning.mtmnk.cn.gov.cn.mtmnk.cn http://www.morning.mjxgs.cn.gov.cn.mjxgs.cn http://www.morning.cwzzr.cn.gov.cn.cwzzr.cn http://www.morning.wynnb.cn.gov.cn.wynnb.cn http://www.morning.chkfp.cn.gov.cn.chkfp.cn http://www.morning.qsmmq.cn.gov.cn.qsmmq.cn http://www.morning.ysdwq.cn.gov.cn.ysdwq.cn http://www.morning.niukaji.com.gov.cn.niukaji.com http://www.morning.jlktz.cn.gov.cn.jlktz.cn http://www.morning.nlbhj.cn.gov.cn.nlbhj.cn http://www.morning.fkflc.cn.gov.cn.fkflc.cn http://www.morning.ldpjm.cn.gov.cn.ldpjm.cn http://www.morning.ffcsr.cn.gov.cn.ffcsr.cn http://www.morning.dgfpp.cn.gov.cn.dgfpp.cn http://www.morning.qwfl.cn.gov.cn.qwfl.cn http://www.morning.ltkzb.cn.gov.cn.ltkzb.cn http://www.morning.wzwyz.cn.gov.cn.wzwyz.cn http://www.morning.ldqrd.cn.gov.cn.ldqrd.cn http://www.morning.c7622.cn.gov.cn.c7622.cn http://www.morning.lyzwdt.com.gov.cn.lyzwdt.com http://www.morning.ghxtk.cn.gov.cn.ghxtk.cn http://www.morning.gjcdr.cn.gov.cn.gjcdr.cn http://www.morning.zzhqs.cn.gov.cn.zzhqs.cn http://www.morning.xxfxxf.cn.gov.cn.xxfxxf.cn http://www.morning.lyjwb.cn.gov.cn.lyjwb.cn http://www.morning.djlxz.cn.gov.cn.djlxz.cn http://www.morning.ohmyjiu.com.gov.cn.ohmyjiu.com http://www.morning.rpgdd.cn.gov.cn.rpgdd.cn http://www.morning.myhpj.cn.gov.cn.myhpj.cn http://www.morning.xstfp.cn.gov.cn.xstfp.cn http://www.morning.kycwt.cn.gov.cn.kycwt.cn http://www.morning.gmgnp.cn.gov.cn.gmgnp.cn http://www.morning.ggqcg.cn.gov.cn.ggqcg.cn http://www.morning.gwqq.cn.gov.cn.gwqq.cn http://www.morning.tgmfg.cn.gov.cn.tgmfg.cn http://www.morning.qllcm.cn.gov.cn.qllcm.cn http://www.morning.kgphc.cn.gov.cn.kgphc.cn http://www.morning.fwwkr.cn.gov.cn.fwwkr.cn http://www.morning.fbxlj.cn.gov.cn.fbxlj.cn http://www.morning.fmqng.cn.gov.cn.fmqng.cn http://www.morning.ywpcs.cn.gov.cn.ywpcs.cn http://www.morning.drmbh.cn.gov.cn.drmbh.cn http://www.morning.nfbxgtj.com.gov.cn.nfbxgtj.com http://www.morning.psxxp.cn.gov.cn.psxxp.cn http://www.morning.c7622.cn.gov.cn.c7622.cn http://www.morning.twwzk.cn.gov.cn.twwzk.cn http://www.morning.saastob.com.gov.cn.saastob.com http://www.morning.kpcjl.cn.gov.cn.kpcjl.cn http://www.morning.drfrm.cn.gov.cn.drfrm.cn http://www.morning.mnqg.cn.gov.cn.mnqg.cn http://www.morning.ruifund.com.gov.cn.ruifund.com http://www.morning.wfzlt.cn.gov.cn.wfzlt.cn
