【mysql】之sql查询优化

单表查询优化

建表sql

CREATE TABLE `sys_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v_name` varchar(20) DEFAULT '' COMMENT '姓名',
  `age` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '年龄',
  `grade` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '年级',
  `course` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '所学课程',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='学生表';

查询，年龄(age)=7，年级(grade)>7年级，所学课程(course)最多的学生

explain select * from sys_student a where a.age=7 and a.grade>7 ORDER BY a.course DESC LIMIT 1;

explain分析之后发现，当前sql存在两个问题，1、没有使用到索引ALL全表扫描，2、Using filesort 文件排序

优化1：加索引

ALTER TABLE sys_student ADD INDEX idx_age_grade_course(age,grade,course);

使用到了age和grade两个索引，type是rang是可以接受，但是extra有Using filesort，grade>7说明之后的course索引无法使用。证明这个sql还不是最优方案

-- 删除之前的索引
DROP INDEX idx_age_grade_course on sys_student;
-- 只对 age和course建索引
ALTER TABLE sys_student ADD INDEX idx_age_course(age,course);

再次执行之前sql，使用了索引，并且没有Using filesort。

多表关联查询优化

建表sql

CREATE TABLE `sys_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v_name` varchar(20) DEFAULT '' COMMENT '姓名',
  `age` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '年龄',
  `grade` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '年级',
  `course` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '所学课程',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='学生表';

INSERT INTO `sys_student` VALUES ('1', '张三', '7', '1', '2', '上海', '2020-03-11 17:36:06');
INSERT INTO `sys_student` VALUES ('2', '李四', '7', '1', '2', '北京', '2020-03-12 14:29:04');
INSERT INTO `sys_student` VALUES ('3', '王五', '6', '1', '2', '杭州', '2020-03-12 14:29:25');

CREATE TABLE `sys_subject` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `grade` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '年级',
  `subject` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '科目',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='年级科目表';

INSERT INTO `sys_subject` VALUES ('1', '1', '5');
INSERT INTO `sys_subject` VALUES ('2', '2', '8');

执行sql

EXPLAIN select a.* from sys_student a LEFT JOIN sys_subject b on a.grade=b.grade;

根据执行结果分析，发现两个表都是全表扫描，第一行关联rows是3行，第二行关联rows是2行，执行时间就是全表扫描3行*2行，但是如果把这个数据放大1万倍呢？

-- 在sys_student上建立索引

ALTER TABLE sys_student ADD INDEX idx_grade(grade);

再次执行sql，发现没有任何改变

-- 删除sys_student上建立的索引，在sys_subject上重新建立索引

DROP INDEX idx_grade ON sys_student;
ALTER TABLE sys_subject ADD INDEX idx_grade(grade);

这次执行结果，第二行type已经是ref级别，使用到索引，且Extra显示Using index了，实际执行时间=3行*1行

综上，使用left join查询时（left join 左边是驱动表，右边是被驱动表）

1、左边数(驱动表）会被全表扫描，右边表(被驱动表，join后面的表)关联字段加索引会提高查询效率。

2、left join，选择小表作为驱动表，大表作为被驱动表

3、inner join时，mysql会自动把小结果集的表宣威驱动表

4、子查询尽量不要放在被驱动表，有可能使用不到索引。如果遇到子查询作为被驱动表可以使用两个left优化，若必须用到子查询可以将子查询作为驱动表，因为驱动表是全表扫描，type肯定是all。

order by 优化

mysql支持二种方式的排序，FileSort和Index。Index效率高，它指MySQL扫描索引本身完成排序。FileSort（文件排序）方式效率较低。

建表sql

CREATE TABLE `sys_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v_name` varchar(20) DEFAULT '' COMMENT '姓名',
  `age` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '年龄',
  `grade` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '年级',
  `course` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '所学课程',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='学生表';

执行select查询，发现没有索引且使用Using filesort文件排序

-- age建立单个索引

ALTER TABLE sys_student ADD INDEX idx_age(age);

依旧执行之前的sql

why？没有索引+文件排序，继续优化，尝试使用覆盖索引

因此，在没有where条件检索的情况下，必满足覆盖索引，才会使用索引排序

-- 删除之前idx_age索引，建立idx_age_grade联合索引
DROP INDEX idx_age ON sys_student ;
ALTER TABLE sys_student add INDEX idx_age_grade(age,grade);

order by 满足两种情况才会使用索引，1，order by 语句遵循索引最左原则，2，使用where子句与order by子句条件列组合满足索引最左原则

filesort（文件排序）的两种算法，双路排序和单路排序，请看这里

limit 分页优化

表数据269万+条数据

取出第1000000页的10条数据，

--sql1写法
select * from tbinvestjxbankrecord a ORDER BY id ASC LIMIT 1000000,10;

--sql2写法
SELECT * FROM tbinvestjxbankrecord WHERE id >= (SELECT id FROM tbinvestjxbankrecord  ORDER BY id ASC  LIMIT 1000000, 1) LIMIT 10;

--sql3写法
select * from tbinvestjxbankrecord INNER JOIN

( select id from tbinvestjxbankrecord ORDER BY id ASC LIMIT 1000000,10) a on a.id=tbinvestjxbankrecord.id;

sql2和sql3比sql1要减少将近1倍的时间

这里尤其要注意 select * 和 select id ，得到的id不一致的问题，造成这个问题的主要原因之一是filesort 算法的问题，解决的办法是必须要order by

group by 优化

group by的本质的是先排序再分组

-- 建表sql
CREATE TABLE `sys_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v_name` varchar(20) DEFAULT '' COMMENT '姓名',
  `age` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '年龄',
  `grade` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '年级',
  `course` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '所学课程',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='学生表';