Mysql 性能优化

Max函数优化

我们有一张学生表

select * from student

我们想知道学生表中的最大年龄

select MAX(age) from student;

查看执行计划

explain select MAX(age) from student\G

可以看到进行了全表扫描，效率并不高

那么要如何优化呢？很简单，给age这一列创建一个索引

create index inx_age on student(age);

创建索引后再次查看执行计划

explain select MAX(age) from student\G

通过索引该查询并没有进行全表扫描，而是通过索引查询，效率得到了提高。

COUNT函数

我们创建一张表

create table t(id int);

插入三条数据

insert into t values(1),(2),(null);

count(*)

select count(*) from t;

count(id)

select count(id) from t;

Count（id）不包含null值
Count（*）包含null值

子查询的优化

子查询是我们在开发过程中经常使用的一种方式，在通常情况下，需要把子查询优化为join查询但在优化是需要注意关联键是否有一对多的关系，要注意重复数据。

查看我们所创建的t表

show create table t;

接下来我们创建一个t1表

create table t1(tid int);

插入两条数据

insert into t1 values(1),(1);

我们要进行一个子查询，需求：查询t表中id在t1表中tid的所有数据

select * from t where t.id in (select t1.tid from t1);

查看执行计划

explain select * from t where t.id in (select t1.tid from t1)\G

接下来我们用join的操作来进行操作

select id from t join t1 on t.id =t1.tid;

查看执行计划

explain select id from t join t1 on t.id =t1.tid\G

相比第一种查询而言，没有用到临时表，效率得到了提高。

但是在这种情况下出现了一对多的关系，会出现数据的重复，我们为了方式数据重复，不得不使用distinct关键词进行去重操作

select distinct id from t join t1 on t.id =t1.tid;

limit优化

Limit常用于分页处理，时长会伴随order by从句使用，因此大多时候回使用Filesorts这样会造成大量的IO问题。

需求：查询学生信息，并根据学生姓名进行排序，取出从序号110条开始的5条数据。

select * from student order by name limit 10,5;

查看执行计划

explain select * from student order by name limit 10,5\G

使用了文件排序，效率很低
对于这种操作，我们该用什么样的优化方式了？
优化步骤1：
使用有索引的列或主键进行order by操作，因为大家知道，innodb是按照主键的逻辑顺序进行排序的。可以避免很多的IO操作。

select * from student order by id limit 10,5;

查看执行计划

explain select * from student order by id limit 10,5\G

虽然避免了文件排序但是依然扫描了十五行
随着我们翻页越往后，IO操作会越来越大的，如果一个表有几千万行数据，翻页越后面，会越来越慢，因此我们要进一步的来优化。

优化步骤2：
记录上次返回的主键， 在下次查询时使用主键过滤。（说明：避免了数据量大时扫描过多的记录）
上次limit是10,5的操作，因此我们在这次优化过程需要使用上次的索引记录值，

select * from student where id > 21 order by id limit 5;

查看执行计划

explain select * from student where id > 21 order by id limit 5\G

扫描行数不变，执行计划是很固定，效率也是很固定的