慢查询与MySQL语句优化 1 慢查询 2 Mysql语句优

发表于

如果我们了解了Mysql中的索引原理之后，（详见探秘数据库 —— 事务 + InnoDB存储引擎），如何利用索引并对一些执行较慢的sql进行优化也是必要的，所以我们可以结合索引的原理来探究一下慢查询与优化的知识。

1 慢查询

MySQL的慢查询，全名慢查询日志，

是MySQL提供的一种日志记录，用来记录在MySQL中应时间超过阈值的语句。

默认情况下，MySQL数据库并不启动慢查询，需要手动来设置这个参数。

如果不是调优需要的话，一般不建议启动该参数，开启慢查询日志会或多或少带来一定的性能影响。

慢查询日志可用于查找需要很长时间才能执行的查询，因此是优化的候选者。

  • 查看“慢查询”的配置信息：
1
mysql复制代码SHOW VARIABLES LIKE "%slow%";

)​

  • 查看“慢查询”的时间定义
1
mysql复制代码SHOW VARIABLES LIKE "long_query_time";

)​

  • 设置“慢查询”的时间定义
1
mysql复制代码SET long_query_time = 2;

)​

  • 开启慢日志
1
mysql复制代码SET GLOBAL slow_query_log = "ON";

2 Mysql语句优化

2.1 数据准备

为了做实验，我们需要现在表中插入很多很多条数据，以观察查询时候的性能差异，这里我们插入一千万条数据。

  • 创建表：
1
2
3
4
5
6
7
8
9
mysql复制代码CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `email` varchar(30) DEFAULT NULL,
  `phone` char(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

  • 创建插入1000万条数据的存储过程：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql复制代码\d //
create procedure p1()
begin
set @i=1;
while @i<=10000000 do
insert into users values(
    null,
    concat('user:',@i),
    concat('user:',@i,'@qq.com'),
    concat('13701',FLOOR(RAND()*500000 + 500000)),
    floor(rand()*100),
    if(floor(rand() * 2) = 1 , '男' , '女')
    );
set @i=@i+1;
end while;
end;
//
\d ;

  • 调用存储过程，完成数据插入
1
mysql复制代码call p1();

运行时间会比较久，在我的电脑上是168分钟左右，大家耐心等待哦~~

)​

我们再插入一条特殊的数据：

1
mysql复制代码insert into users values(null,"zhangsan","zhangsan@qq.com",13701383017,25,'女');

  • 查询刚刚插入的数据
1
mysql复制代码select * from users where name = "zhangsan";

)​

可以看出想要查询出这条数据需要的时间非常久，相应的也存储到了慢查询的日志里面了，对应的日志内容如下：

1
2
3
4
5
mysql复制代码# Time: 200916 15:19:54
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 6.708004  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 10000001
SET timestamp=1600240794;
select * from users where name = "zhangsan";

2.2 EXPLAIN语句

一条查询语句在经过MySQL查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划

这个执行计划展示了接下来具体执行查询方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。

MySQL为我们提供了EXPLAIN语句来帮助我们查看某个语句的具体执行计划。

  • 使用EXPLAIN分析SQL语句

)​

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

  • id 在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
  • select type SELECT 关键字对应的那个查询的类型
  • table 表名
  • partitions 匹配的分区信息
  • type 针对单表的访问方法
  • possible_keys 可能用到的索引
  • key 实际上使用的索引
  • key_len 实际使用到的索引长度
  • ref 当使用索引列等值查询时，与索引列进行等值匹配的对象信息
  • rows 预估的需要读取的记录条数
  • filtered 某个表经过搜索条件过滤后剩余记录条数的百分比
  • Extra 一些额外的信息

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

)​

只用了0.02秒，explain一下的结果如下，也验证了该理论：

)​

2.3 添加索引

  • 尝试给name字段加普通索引
1
mysql复制代码 alter table users add index index_name(name);

之后再使用name字段来查询，发现速度提升了不少，原因就在于我们将name字段设置成了索引项：

)​

使用explain查看一下：

)​

大家看到，索引能给数据检索提高的效率非常明显

那么是否意味着我们只要尽可能多的去建立索引就可以了呢？

每建立一个索引都会建立一棵B+树，并且需要维护，这是很费性能和存储空间的。

2.4 适当建立索引

1.创建并使用自增数字来建立主键索引

2.经常作为where条件的字段建立索引

3.添加索引的字段尽可能的保持唯一性

4.可考虑使用联合索引并进行索引覆盖

2.5 合理使用索引

  1. MySQL索引通常是被用于提高 WHERE 条件的数据行匹配时的搜索速度，
  2. 在索引的使用过程中，存在一些使用细节和注意事项。
  3. 因为不合理的使用可能会导致建立了索引之后，不一定就使用上了索引

2.5.1 不要在列上使用函数和进行运算

  • 不要在列上使用函数，将导致索引失效
1
mysql复制代码select * from news where year(publish_time) = 2017;

  • 改造为使用索引
1
mysql复制代码select * from news where publish_time = '2017-01-01';

  • 不要在列上进行运算，也会导致索引失效
1
mysql复制代码select * from news where id / 100 = 1;

  • 改造为使用索引
1
mysql复制代码select * from news where id = 100;

)​

)​

2.5.2 类型要匹配

当查询条件左右两侧类型不匹配的时候会发生隐式转换，隐式转换带来的影响就是可能导致索引失效而进行全表扫描。

  • 修改一下表中的数据
1
mysql复制代码 update users set name = '123456' where id = 10086;

  • 正常查询与含有隐式转换的对比

)​

发现如果出现隐式数据类型转换时，查询时间会非常长。

2.5.3 首部不出现通配符

  • 当在尾部使用通配符时可以使用索引

)​

  • 当在头部使用通配符时，会导致索引失效

)​

2.5.4 多个单列索引并不是最佳选择

Mysql只能使用一个索引，会从多个索引中选择一个（限制最严格的）索引，因此，为多个列创建单列索引并不能提高Mysql的查询性能。

使用多个单列索引的情况：

1
mysql复制代码alter table users add index index_name(name);

)​

看上去很美好，但文件可能会非常的大。

)​

事实上，MySQL只能使用一个单列索引。这样既浪费了空间，又没有提高性能（因为需要回行）为了提高性能，可以使用复合索引保证列都被索引覆盖。

复合索引：

1
mysql复制代码alter table users add index in_x(email,phone,name);

)​

)​

2.5.5 小心复合索引的最左侧原则

查询条件中使用了复合索引的第一个字段，索引才会被使用。因此，在复合索引中索引列的顺序至关重要。如果不是按照索引的最左列开始查找，则无法使用索引。

)​

发现如果只按照email查询时索引失效了。

2.5.6 尽可能达成索引覆盖

如果一个索引包含所有需要的查询的字段的值，直接根据索引的查询结果返回数据，而无需读表，能够极大的提高性能。因此，可以定义一个让索引包含的额外的列，即使这个列对于索引而言是无用的。

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%