开发人员MySQL调优-实战篇1-单_双表查询优化

2019独角兽企业重金招聘Python工程师标准>>>

单表案例分析

准备数据脚本

DROP TABLE IF EXISTS `tb_tmp_vote`;
CREATE TEMPORARY TABLE `tb_tmp_vote` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `user_id` varchar(20) NOT NULL DEFAULT '' COMMENT '用户Id',
    `vote_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '投票数',
    `group_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用户组id 0-未激活用户 1-普通用户 2-vip用户 3-管理员用户',
    `status` tinyint(2) unsigned NOT NULL DEFAULT '1' COMMENT '状态 1-正常 2-已删除',
    `create_time` date DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',
    PRIMARY KEY (`id`)
)COMMENT='投票记录表';
​
DROP TABLE IF EXISTS `tb_vote`;
CREATE TABLE `tb_vote` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `user_id` varchar(20) NOT NULL DEFAULT '' COMMENT '用户Id',
    `vote_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '投票数',
    `group_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用户组id 0-未激活用户 1-普通用户 2-vip用户 3-管理员用户',
    `status` tinyint(2) unsigned NOT NULL DEFAULT '1' COMMENT '状态 1-正常 2-已删除',
    `create_time` date DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='投票记录表';
​
-- 创建生成长度为n的随机字符串的函数
DELIMITER // -- 修改MySQL delimiter：'//'
DROP FUNCTION IF EXISTS `rand_string` //
SET NAMES utf8 //
CREATE FUNCTION `rand_string` (n INT) RETURNS VARCHAR(255) CHARSET 'utf8'
BEGIN 
    DECLARE char_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    DECLARE return_str varchar(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
        SET return_str = concat(return_str, substring(char_str, FLOOR(1 + RAND()*62), 1));
        SET i = i+1;
    END WHILE;
    RETURN return_str;
END //
​
-- 创建插入数据的存储过程
DROP PROCEDURE IF EXISTS `add_tb_vote_memory` //
CREATE PROCEDURE `add_tb_vote_memory`(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE vote_num INT DEFAULT 0;
    DECLARE group_id INT DEFAULT 0;
    DECLARE status TINYINT DEFAULT 1;
    WHILE i < n DO
        SET vote_num = FLOOR(1 + RAND() * 10000);
        SET group_id = FLOOR(0 + RAND()*3);
        SET status = FLOOR(1 + RAND()*2);
        INSERT INTO `tb_tmp_vote` VALUES (NULL, rand_string(20), vote_num, group_id, status, NOW());
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;  -- 改回默认的 MySQL delimiter：';'
​
call add_tb_vote_memory(500000);
​
INSERT INTO tb_vote SELECT * FROM `tb_tmp_vote`;

需求：你的项目经理说要实现一个查询某一天投票数超过某一个值的用户，并按用户ID升序排列

#在这里传递过来的值写成了固定值，代码里面用绑定变量（占位符）
select user_id,vote_num
  from tb_vote 
 where create_time = '2018-03-03' 
   and vote_num > 9000 
 order by user_id;

查看执行计划

一眼看过去，type=ALL,extra中出现了Using filesort，这两个是需要去掉的

看看sql语句中使用到的字段，先在create_time,vote_num,user_id上创建索引

create index idx_tb_vote_c_v_u on tb_vote(create_time,vote_num,user_id);

type=ALL变成了type=range，为什么喃？因为虽然走了索引扫描，但是条件有一个vote_num > 9000

虽然type=ALL优化掉了，但是Using filesort还在，为什么喃？索引虽然是有序的，但是如果只按照user_id排序的话，MySQL对整个结果集的user_id,vote_num的顺序与索引中默认的顺序不一致，导致MySQL必须重新排序。我想可以有两个方法来解决整个问题

1.在order by 中添加vote_num字段

select user_id,vote_num
  from tb_vote 
 where create_time = '2018-03-03' 
   and vote_num > 9000 
 order by vote_num,user_id;

2.再创建一个索引

create index idx_tb_vote_c_u on tb_vote(create_time,user_id);

比较这两种方案：

按照前面说的type=ref优于type=range,但读取的rows的值ref却比range多很多，因为MySQL会将所有create_time='2018-03-03'的数据行找出来比较是否vote_num>9000，所以最终ref还会比range慢。但是第一种方案和需求有一点变化，这种时候就需要和项目经理沟通，项目经理再和客户沟通，如果对业务没什么影响，就可以采取第一种方案

到这里该SQL的问题算是解决了，实际生产中绝对没有这么简单，因为一个表的字段可能会非常多，比如电信的详单，有几十个字段，可能涉及到的列有十多个，如何创建索引将会是一个非常复杂的问题，最好和DBA一起商量着来（一般大系统都会有DBA按天监控一些重要表的DDL操作，定期收集表的统计信息，固化执行计划等）

注意在写SQL的时候，如果是字符类型的列，一定要加上单引号，否则生成执行计划的时候会出问题，你认为它会用到索引，它就不会让你如愿以偿

两张表案例分析

根据上面的表造一份user数据

create table tb_v_user
 select user_id,left(user_id,5) as user_name,floor(40+rand()*(20)) as age,floor(1+rand()*2) as gendor 
   from tb_vote;

这样取值的话可以user_name就会有重复，执行下面的语句拿到一些重复的user_name

select user_name,count(*) 
    from tb_v_user
 group by user_name 
having count(*)>5;

user_name count(*)
1F7sJ   6
2fXTq   6

删除之前创建的索引，方便从头开始分析

#使用了删除索引的两种写法
alter table tb_vote drop index idx_tb_vote_c_v_u;
drop index idx_tb_vote_c_u on tb_vote;

 

需求1：查询出user_name为‘1F7sJ’的投票结果

select a.user_name,a.user_id,b.vote_num from tb_v_user a 
  join tb_vote b on a.user_id = b.user_id 
 where a.user_name = '1F7sJ';

现在两张表在关联的字段上面都没有索引，MySQL首先加载tv_vote表，也就是关联查询右边的表，意思就是先拿到tb_vote的所有数据和tb_v_user关联，再判断是不是1F7sJ用户，两张表都使用了ALL（全表扫描），然后在内存中对join。接下来尝试在b表上创建索引。

create index idx_tb_vote_user_id on tb_vote(user_id);

可以看到b表type=ref了，这结果非常好。MySQL估算出来b表针对每个a表的关联只会捞一条数据出来。

我们现在是在b表上创建的索引，如果刚才是在a表上创建索引会是什么效果喃？

drop index idx_tb_vote_user_id on tb_vote;
create index idx_tb_v_user_user_id on tb_v_user(user_id);

现在a\b两张表的加载顺序变了，a表的type=ref。可以推断出MySQL针对同样的SQL，自己会做优化，并不会死板的认为一定先加载关联查询右边的表。

可以设想另外一种情况，如果两张表关联，一张表很小，另外一张表相对要大很多，按照上面的处理会是什么样的效果喃？有兴趣的同学可以试试。按照上面学习的理论，我推测小表上建索引是没有用的，小表一定会先加载作为驱动表。

再来将两张表的user_id都加上索引试试

create index idx_tb_vote_user_id on tb_vote(user_id);

然而并没有什么变化，为什么喃？因为作为驱动的a表，要找到符合条件的‘1F7sJ’，依然需要全表扫描

那么再到a表的user_name上创建一个索引

create index idx_tb_v_user_user_name on tb_v_user(user_name);

 

这个效果就很好了，所有type=ref，而且估算出来需要捞的数据总共才7行，非常快。

此时如果需求变了，还要对结果做一些排序怎么办喃，可以参考单表优化的经验试试

总结一下表关联的情况：找到驱动表，减少驱动表需要关联数据行的代价，被关联表的关联字段一定要有索引

转载于:https://my.oschina.net/weiweiblog/blog/1829803