SQL慢查询与优化-阿里云开发者社区

开发者社区> 武汉-新零售-宋波> 正文

SQL慢查询与优化

简介: MySQL的慢查询,全名慢查询日志,是MySQL提供的一种日志记录,用来记录在MySQL中应时间超过阈值的语句。
+关注继续查看

1.慢查询

MySQL的慢查询,全名慢查询日志,是MySQL提供的一种日志记录,用来记录在MySQL中应时间超过阈值的语句。
默认情况下,MySQL数据库并不启动慢查询,需要手动来设置这个参数。
如果不是调优需要的话,一般不建议启动该参数,开启慢查询日志会或多或少带来一定的性能影响。
慢查询日志可用于查找需要很长时间才能执行的查询,因此是优化的候选者。

查看“慢查询”的配置信息
SHOW VARIABLES LIKE "%slow%";
image.png

查看“慢查询”的时间定义
SHOW VARIABLES LIKE "long_query_time";
image.png

设置“慢查询”的时间定义
SET long_query_time = 2;
image.png

开启“慢查询”
SET GLOBAL slow_query_log = "ON";

2.Mysql语句优化

2.1EXPLAIN语句
一条查询语句在经过MySQL查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划。这个执行计划展示了接下来具体执行查询方式,比如多表连接的顺序是什么,对于每个表采用什么访问方法来具体执行查询等等。
MySQL为我们提供了EXPLAIN语句来帮助我们查看某个语句的具体执行计划。
使用EXPLAIN分析SQL语句
image.png

对输出结果的参数解释如下,其中重要的已经在上图标明:

  1. id在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
  2. select_type SELECT关键字对应的那个查询的类型
  3. table 表名
  4. type 针对单表的访问方法
  5. possible_keys 可能用到的索引
  6. key 实际上使用的索引
  7. key_len 实际使用到的索引长度
  8. ref 当使用索引列等值查询时,与索引列进行等值匹配的对象信息
  9. rows 预估的需要读取的记录条数
  10. Extra 一些额外信息

当我们换一种方法来查找这一条数据时,比如使用id来查询,由于id默认为主键索引,所以查询速度较快:
image.png

只用了0.02秒,explain一下的结果如下,也验证了该理论:
image.png

2.2添加索引
尝试给name字段加普通索引
alter table users add index index_name(name);
之后再使用name字段来查询,发现速度提升了不少,原因就在于我们将name字段设置成了索引项:
image.png

使用explain查看一下:
image.png

可以看到,索引能给数据检索提高的效率非常明显
那么是否意味着我们只要尽可能多的去建立索引就可以了呢?
每建立一个索引都会建立一棵B+树,并且需要维护,这是很费性能和存储空间的
2.3适当建立索引

  1. 创建并使用自增数字来建立主键索引
  2. 经常作为where条件的字段建立索引
  3. 添加索引的字段尽可能的保持唯一性
  4. 可考虑使用联合索引并进行索引覆盖

2.4合理使用索引
MySQL索引通常是被用于提高 WHERE 条件的数据行匹配时的搜索速度,在索引的使用过程中,存在一些使用细节和注意事项,因为不合理的使用可能会导致建立了索引之后,不一定就使用上了索引。
2.4.1不要在列上使用函数和进行运算
不要在列上使用函数,将导致索引失效
select * from news where year(publish_time) = 2017;
改造为使用索引
select * from news where publish_time = '2017-01-01';
不要在列上进行运算,也会导致索引失效
select * from news where id / 100 = 1;
image.png
image.png

改造为使用索引
select * from news where id = 100;
2.4.2类型要匹配
当查询条件左右两侧类型不匹配的时候会发生隐式转换,隐式转换带来的影响就是可能导致索引失效而进行全表扫描。
修改一下表中的数据
update users set name = '123456' where id = 10086;
发现如果出现隐式数据类型转换时,查询时间会非常长。
2.4.3首部不出现通配符
当在尾部使用通配符时可以使用索引
image.png

当在头部使用通配符时,会导致索引失效
image.png

2.4.4多个单列索引并不是最佳选择
Mysql只能使用一个索引,会从多个索引中选择一个(限制最严格的)索引,因此,为多个列创建单列索引并不能提高Mysql的查询性能。
使用多个单列索引的情况:
alter table users add index index_name(name);
image.png

看上去很美好,但文件可能会非常的大。
image.png

事实上,MySQL只能使用一个单列索引。这样既浪费了空间,又没有提高性能(因为需要回行)为了提高性能,可以使用复合索引保证列都被索引覆盖。
复合索引:
alter table users add index in_x(email,phone,name);
2.4.5小心复合索引的最左侧原则
查询条件中使用了复合索引的第一个字段,索引才会被使用。因此,在复合索引中索引列的顺序至关重要。如果不是按照索引的最左列开始查找,则无法使用索引。
image.png

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
SQL Server 查询性能优化 相关文章
来自: SQL Server 查询性能优化——堆表、碎片与索引(一) SQL Server 查询性能优化——堆表、碎片与索引(二) SQL Server 查询性能优化——覆盖索引(一) SQL Server 查询性能优化——覆盖索引(二) SQL Server 查询性能优化——创建索引原则...
788 0
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
8546 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
10382 0
SQL 子查询怎么优化?写的很深!
子查询(Subquery)的优化一直以来都是 SQL 查询优化中的难点之一。关联子查询的基本执行方式类似于 Nested-Loop,但是这种执行方式的效率常常低到难以忍受。当数据量稍大时,必须在优化器中对其进行去关联化(Decoorelation 或 Unnesting),将其改写为类似于 Semi-Join 这样的更高效的算子。
9 0
使用OpenApi弹性释放和设置云服务器ECS释放
云服务器ECS的一个重要特性就是按需创建资源。您可以在业务高峰期按需弹性的自定义规则进行资源创建,在完成业务计算的时候释放资源。本篇将提供几个Tips帮助您更加容易和自动化的完成云服务器的释放和弹性设置。
11914 0
MySQL 性能调优之查询优化
见原文: http://www.cnblogs.com/markjiao/p/5665775.html
760 0
2
文章
0
问答
来源圈子
更多
阿里云GTS能力中心(浩鲸智能),从交付的视角探讨数字化转型过程中大型软件开发实践、以及阿里云产品在各行业被集成的案例分享、技术沉淀等内容。敬请关注!
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载