MySQL性能优化

count优化

MyISAM会将总记录数缓存，但如果加上where条件，则缓存值失效。

若有500w条记录，需要统计id > 100的总行数。

一个优化思路，反向操作，降低查询次数。

select 

(select count(*) from student) - count(*) 

from student where id <= 100;

优化Limit分页（深分页问题）

场景模拟

插入10w条样例数据

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `stuid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `stuname` varchar(200) DEFAULT NULL,
  `stuaddress` varchar(200) DEFAULT NULL,
  `classid` int(11) DEFAULT NULL,
  PRIMARY KEY (`stuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DELIMITER // 
create procedure doinsert1()
begin
declare i int;
declare j int;
set i = 1;
set j = 1;
while i<100000 do
insert into student(stuid,stuname,stuaddress,classid)values(i,concat(i,'张',j),'深圳',i+j);
set i = i+1;
set j = j+1;
end while;
end;//

call doinsert1()

执行小的偏移量查询

执行大的偏移量查询

分析

# 打开show profile，收集在执行语句的时候所使用的资源。
SET profiling = 1;

# show profiles;
show profiles;

# 通过对应的QUERY_ID查看
show profile for query 1;

耗时最多的是Sending Data，可以看到查询数据花了大量的时间。

优化方案一

将limit转换为where...between

select * from student where stuid between 90002 and 90021;

优化方案二

通过where缩小范围

如上一页获取到的最后的结果是90001，则下一页则加上classid > 90001，来缩小范围。

select * from student where classid > 90001 order by stuid limit 20;

优化方案三

从业务上进行优化，结合业务，将最大翻页控制在指定范围，例如最多只允许翻到100页。

若想要获得更多数据，需要修改关键词或增加筛选条件。

优化方案四

利用覆盖索引，先查询指定的id，然后用inner join进行关联。

select s.* from student s inner join  (
select stuid from student order by classid  limit 90000,20) as tmp on s.stuid = tmp.stuid;

使用变量减少查询次数

需求是：使用分数排名，存在并列第一或第n的情况。

 create table t10( name char(10) not null default '', score int not null default 0)engine myisam charset utf8;

insert into t10 values('zhang',100),('wang',95),('li',92),('liu',100);

使用变量set @age:=20，查询变量select @age

select name,(@curr:=score) as score,@rank:=if(@curr<>@prev,@rank:=@rank+1,@rank) as rank,@prev:=score as prev from t10 order by score desc;