Mysql优化篇

Mysql优化篇——Limit

1.背景

闲来无事，上班摸鱼，突然想练一下mysql优化了，找朋友搬过来一个sql脚本，生成了一个800W行的数据表，脚本文档底部。

mysql> select count(*)  from EMP;
+----------+
| count(*) |
+----------+
|  8000000 |
+----------+
1 row in set (4.62 sec)

mysql> describe EMP;
+----------+--------------------------+------+-----+---------+-------+
| Field    | Type                     | Null | Key | Default | Extra |
+----------+--------------------------+------+-----+---------+-------+
| empno    | int(6) unsigned zerofill | NO   | PRI | NULL    |       |
| ename    | varchar(10)              | YES  |     | NULL    |       |
| job      | varchar(9)               | YES  |     | NULL    |       |
| mgr      | int(4) unsigned zerofill | YES  |     | NULL    |       |
| hiredate | datetime                 | YES  |     | NULL    |       |
| sal      | decimal(7,2)             | YES  |     | NULL    |       |
| comm     | decimal(7,2)             | YES  |     | NULL    |       |
| deptno   | int(2) unsigned zerofill | YES  |     | NULL    |       |
+----------+--------------------------+------+-----+---------+-------+
8 rows in set (0.08 sec)

可以看到，表有800W行数据，全表扫描一遍需要跑很久。

2.优化一下limit

说干就干，先玩一下limit

select * from EMP limit 0,10;     #0.01秒     
select * from EMP limit 10000,10;  #0.02秒
select * from EMP limit 100000,10; #0.15秒
select * from EMP limit 1000000,10; #1.63秒

可以看到，当100W时已经无法接受了，这还是单线程下的情况。

explain检查一下,发现是全表扫描：

mysql> explain select * from EMP limit 1000000,10;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | EMP   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 7783957 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
1 row in set, 1 warning (0.00 sec)

给不懂的小伙伴科普一下几个我常看的字段（其他自行了解）：

一、possible_key：当前sql可能会使用到的索引

二、key：当前sql实际命中的索引

三、key_len：索引占用的大小

四、type：这条sql的连接的类型 （性能好->坏为null、system、const、eq_ref、ref、range、index、all）

为什么会跑的这么慢?

大白话，查询默认查询前1000000+10行，回表了（所以慢），然后丢弃offset行。

如何解决？

———————减少回表+索引就完了————————

第一次想到的🤔 ：

1.63秒->0.49秒。empno字段做主键，然后子查询是“覆盖索引”，不用回表，筛掉一大部分，最后emp表和一个10行数据的子表根据主键内联查找一下。

select e.* from EMP e, (select empno from EMP limit 1000000,10) et  where e.empno=et.empno;

能再快一点吗🤔 ：

之前用的是隐式内联，现在改成显示内联，0.49秒->0.21秒，离谱，建议以后大家还是用显示联接吧。

select e.* from EMP e inner join (select empno from EMP limit 1000000,10) et  on e.empno=et.empno;

3.后续

继续上班去了，后面接着更新mysql优化实践，包括索引失效、分库分表、redis缓存搭配mysql、多机部署mysql服务，做全面的mysql优化。

构建800W数据的脚本,在mysql里 source /绝对地址目录/脚本.sql ，自己生成一下哈，不会百度：

drop database if exists `bit_index`;
create database if not exists `bit_index` default character set utf8;
use `bit_index`;

-- 构建一个8000000条记录的数据
-- 构建的海量表数据需要有差异性，所以使用存储过程来创建， 拷贝下面代码就可以了，暂时不用理解

-- 产生随机字符串
delimiter $$
create function rand_string(n INT)
returns varchar(255)
begin
declare chars_str varchar(100) default
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i = i + 1;
end while;
return return_str;
end $$
delimiter ;


-- 产生随机数字
delimiter $$
create function rand_num( )
returns int(5)
begin
declare i int default 0;
set i = floor(10+rand()*500);
return i;
end $$
delimiter ;

-- 创建存储过程，向雇员表添加海量数据
delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into EMP values ((start+i)
,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
until i = max_num
end repeat;
commit;
end $$
delimiter ;

-- 雇员表
CREATE TABLE `EMP` (
  `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',
  `ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '雇员职位',
  `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',
  `hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
  `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);


-- 执行存储过程，添加8000000条记录
call insert_emp(100001, 8000000);