laravel mysql超时时间_记一次我的 MySQL 调优经历

某一天突然发现我们的管理后台的一个请求很慢，接口调用时间达到了8 s。很纳闷，一个简单的用户列表接口，用户数据才 4k+，还使用了分页，为什么会这么慢呢。

经过调试发现是 mysql 执行时间太长。这儿我们模拟两张表和表数据：

create table `users` (

`id` int(10) unsigned not null auto_increment,

`name` varchar(255) default 'name',

`a` varchar(255) default 'aaaaaaaaaaaaaaaa',

`b` varchar(255) default 'bbbbbbbbbbbbbbbb',

`c` varchar(255) default 'cccccccccccccccc',

primary key (`id`)

) engine=innodb;

delimiter ;;

create procedure usersdata()

begin

declare i int;

set i=1;

while(i<=4000)do

insert into users(`name`) values('name');

set i=i+1;

end while;

end;;

delimiter ;

call usersdata();

create table `user_enterprises` (

`id` int(11) unsigned not null auto_increment,

`user_id` int(11) default null,

primary key (`id`)

) engine=innodb;

delimiter ;;

create procedure enterprisesdata()

begin

declare i int;

set i=1;

while(i<=4000)do

insert into user_enterprises(`user_id`) values(i);

set i=i+1;

end while;

end;;

delimiter ;

call enterprisesdata();

把我们需要执行的 sql 打印出来：

select * from `users` left join `user_enterprises` on `users`.`id` = `user_enterprises`.`user_id` order by `users`.`id` desc;

使用 explain 命令：

我们看到在 join 的时候使用了 BNL 算法。它的过程大概是：

首先把 users 表的所有数据加入到 join buffer 中。

扫描整个 user_enterprises 表的每一行数据，与 join buffer 中的 users 数据作对比，将满足条件的加入结果集。

虽然操作量很大，但都是在内存中完成的。

查询扫描行数：

/* 打开 optimizer_trace，只对本线程有效 */

SET optimizer_trace='enabled=on';

/* @a 保存 Innodb_rows_read 的初始值 */

select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 执行语句 */

select * from `users` left join `user_enterprises` on `users`.`id` = `user_enterprises`.`user_id` order by `users`.`id` desc;

/* @b 保存 Innodb_rows_read 的当前值 */

select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 计算 Innodb_rows_read 差值 */

select @b-@a;

我们得到的扫描行数是 12000。在执行 join 的时候，扫描行数应该是 4000 + 4000，还有 4000 的扫描行数我推测应该是回表取了数据。

启用 optimizer_trace 调试：

/* 打开 optimizer_trace，只对本线程有效 */

set optimizer_trace='enabled=on';

/* 执行语句 */

select * from `users` left join `user_enterprises` on `users`.`id` = `user_enterprises`.`user_id` order by `users`.`id` desc;

/* 查看 OPTIMIZER_TRACE 输出 */

select * from `information_schema`.`optimizer_trace`;

我们看到使用的排序方法是 rowid 排序，select @@max_length_for_sort_data 的结果为 1024，即参与排序的字段大于了这个值，mysql 会把排序字段和主键取出来放入 sort buffer，完成排序后回表取数据。在这儿还用到了临时表。所以大致执行过程应该是 join 之后把数据存在了临时表，然后使用 rowid 排序。

从上面我们发现这个过程是复杂的，如果在 user_enterprises 表上给 user_id 加上索引。

alter table `user_enterprises` add key `user_id_index` (`user_id`);

再次使用 explain 查看结果：

首先 join 的执行流程发生了变化，大体流程是：

在 users 表里取出一行数据

根据索引在 user_enterprises 表里获取结果，组成结果集

我们发现使用到了索引后，就没有在 join buffer 里那些复杂操作了。因为索引的有序性，排序也免了，整个查询过程所需要的时间也大大减少。

~由此可见索引是多么的重要啊！！！

本作品采用《CC 协议》，转载必须注明作者和本文链接

Persevere,Vtr!