mysql里的sql语句如何优化?

最新推荐文章于 2022-09-19 22:34:37 发布

小象子

最新推荐文章于 2022-09-19 22:34:37 发布

阅读量548

点赞数 1

分类专栏： SQL 文章标签： mysql

本文链接：https://blog.csdn.net/weixin_41805792/article/details/86078417

版权

SQL 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

1.如果是别人写的sql,我们要优化,那怎么知道找出那些sql比较慢的sql呢?

使用慢查询日志:
比如说需要找出数据库中比如超过3s的慢SQL，你是怎么找的？用下面的方法+
1.确保慢查询日志开启

查看是否开启： show variables like ‘%slow_query_log%’; 会查出日志的位置和是否开启
开启慢查询日志：set global slow_query_log=1; (重启会失效)–>想要永久生效要改配置文件
开启了慢查询日志后，什么样的SQL才会记录到查询日志里面？
这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒
查看命令： show variables like ‘long_query_time%’;
修改慢查询定义的时间
set global long_query_time=3
（修改为阀值到3秒钟的就是慢sql）
如果使用show variables like ‘long_query_time%’;命令查询不生效的话
需要重新连接或新开一个会话才能看到修改值。 show variables like ‘long_query_time%’;
或者直接 show global variables like ‘long_query_time’;
查看慢查询日志：
show variables like ‘%slow_query_log%’; 里记录了log的地址
日志分析工具mysqldumpslow

-s 按照那种方式排序 
c：访问计数 l：锁定时间 
r:返回记录 
al：平均锁定时间 
ar：平均访问记录数 
at：平均查询时间 
-t 是top n的意思，返回多少条数据。 
-g 可以跟上正则匹配模式，大小写不敏感。

得到返回记录最多的20个sql：mysqldumpslow -s r -t 20 sqlslow.log
得到平均访问次数最多的20条sql：mysqldumpslow -s ar -t 20 sqlslow.log
得到平均访问次数最多,并且里面含有ttt字符的20条sql：mysqldumpslow -s ar -t 20 -g “ttt” sqldlow.log
注意：
1、如果出现 -bash: mysqldumpslow: command not found 错误，请执行：ln -s /usr/local/mysql/bin/mysqldumpslow /usr/bin
2、如果出现如下错误，Died at /usr/bin/mysqldumpslow line 161, <> chunk 405659.说明你要分析的sql日志太大了，请拆分后再分析
拆分的命令为：tail -100000 mysql-slow.log>mysql-slow.20180725.log

2.如果是自己写sql需要注意那些内容呢?

使用explain查看Explain执行计划
比如说:EXPLAIN SELECT *FROM ap_article
在这里插入图片描述

id:select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序

id如果相同，可以认为是一组，从上往下顺序执行；
在所有组中，id值越大，优先级越高，越先执行

select_type:用于区别普通查询、联合查询、子查询等的复杂查询

在这里插入图片描述

table:显示这一行的数据是关于哪张表的

type:显示查询索引使用了何种类型

从最好到最差依次是：
system>const>eq_ref>ref>range>index>ALL
在这里插入图片描述

possible_keys

显示可能应用在这张表中的索引，一个或多个。
查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

key

实际使用的索引。如果为NULL，则没有使用索引
查询中若使用了覆盖索引，则该索引和查询的select字段重叠

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

rows

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

Extra

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

怎么避免索引失效

有一个口诀:

全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE百分写最右，覆盖索引不写*；
不等空值还有OR，索引影响要注意；
VAR引号不可丢， SQL优化有诀窍。

什么意思呢?下面给大家介绍
具体的可以看下这个:https://www.jianshu.com/p/d5b2f645d657
1、全值匹配我最爱
建立几个复合索引字段，最好就用上几个字段。且按照顺序来用。
2、最佳左前缀法则
如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始，不跳过索引中间的列。（带头大哥不能死，中间兄弟不能丢）
3、不再索引列上做任何操作(计算、函数、（自动or手动）类型转换)，会导致索引失效而转向权标扫描
4、存储引擎不能使用索引中范围条件右边的列。（范围之后全失效）
5、使用like查询 %要写在右边,不能写在左边,不然索引会失效,尽量使用覆盖索引（只访问索引的查询(索引列和查询列一致)），减少select* LIKE百分写最右，覆盖索引不写*；
6、Mysql在使用不等于(!=、<>)或like的左模糊的时候无法试用索引会导致全表扫描。
7、IS NULL和IS NOT NULL也无法使用索引
8、字符串不加单引号索引失效
9、少用or，用它来连接时索引会失效。

3、Profiling 的使用

mysql除了提供explain命令用于查看命令执行计划外，还提供了profiling工具用于查看语句查询过程中的资源消耗情况。首先我们要使用以下命令开启Profiling功能：
set profiling = 1;
接下来我们执行一条查询命令：

mysql> select * from emp where empno=413345;
+--------+--------+----------+-----+------------+---------+--------+--------+
| empno  | ename  | job      | mgr | hiredate   | sal     | comm   | deptno |
+--------+--------+----------+-----+------------+---------+--------+--------+
| 413345 | vvOHUB | SALESMAN |   1 | 2014-10-26 | 2000.00 | 400.00 |     11 |
+--------+--------+----------+-----+------------+---------+--------+--------+
1 row in set (6.44 sec)

在开启了Query Profiler功能之后,MySQL就会自动记录所有执行的Query的profile信息了。然后我们通过以下命令获取系统中保存的所有 Query 的 profile 概要信息：
show profiles; ：获取系统中保存的所有 Query 的 profile 概要信息
在这里插入图片描述
show profile cpu, block io for query 4; :获取前面的query_id=4的执行过程中详细的profile信息了

该profile显示了每一步操作的耗时以及cpu和Block IO的消耗，这样我们就可以更有针对性的优化查询语句了。
可以看到，由于这是一次全表扫描，
这里耗时最大是在sending data上。
除了这种情况，以下几种情况也可能耗费大量时间：converting HEAP to MyISAM(查询结果太大时,把结果放在磁盘)、
br>create tmp table(创建临时表,如group时储存中间结果)、Copying to tmp table on disk(把内存临时表复制到磁盘)、
locked(被其他查询锁住) 、
logging slow query(记录慢查询)。