分页查询优化

select * from employees limit 10000,10;
分析这条语句：底层实际是查询了10010条数据，然后舍弃前面10000条数据，只留下10条记录，其实性能是不高的。

根据自增且连续的主键排序的分页查询

第一种优化方式：必须是自增，连续的
image.png

  • 缺点：如果中间删除一条记录，后面的数据就会乱了

根据非主键字段排序的分页查询

select from employees ORDER BY name limit 9000,10;

explain select from employees ORDER BY name limit 9000,10;

image.png
结果分析：用到了order by，但是也是走的聚簇索引的全表扫描，数据量太多了，还要回表，损耗性能，查询的时候用到了全表，还是Using filesort。

上面sql的优化：覆盖索引

explain SELECT * FROM employees e INNER JOIN (SELECT id FROM employees ORDER BY name LIMIT 9000,5) em on e.id = em.id;
image.png
结果分析： (SELECT id FROM employees ORDER BY name LIMIT 9000,5)生成的临时表只有5条记录， 这条语句只是在二级索引里面查出9005条id，然后摒弃掉前面的9000条，然后携带剩余5条记录去根据主表匹配。
几乎都用到了索引，而且没有用到Using filesort；
image.png

Join关联查询优化

用到的sql：创建两张表，t1和t2，都用到a字段未索引字段，t1表10000条数据，t2表100条数据。

  2. CREATE TABLE `t1` (
  3.   `id` int(11) NOT NULL AUTO_INCREMENT,
  4.   `a` int(11) DEFAULT NULL,
  5.   `b` int(11) DEFAULT NULL,
  6.   PRIMARY KEY (`id`),
  7.   KEY `idx_a` (`a`)
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  10. create table t2 like t1;
  12.  drop procedure if exists insert_t1;
  13.  delimiter ;;
  14.  create procedure insert_t1()
  15.  begin
  16.  declare i int;
  17.  set i=1;
  18.  while(i<=10000)do
  19.  insert into t1(a,b) values(i,i);
  20.  set i=i+1;
  21.  end while;
  22.  end;;
  23.  delimiter ;
  24.  call insert_t1();
  26.  drop procedure if exists insert_t2;
  27.  delimiter ;;
  28.  create procedure insert_t2()
  29.  begin
  30.  declare i int;
  31.  set i=1;
  32.  while(i<=100)do
  33.  insert into t2(a,b) values(i,i);
  34.  set i=i+1;
  35.  end while;
  36.  end;;
  37.  delimiter ;
  38.  call insert_t2();

嵌套循环链接Nest-Loop Jion（NLJ）算法

EXPLAIN SELECT * FROM t1 INNER JOIN t2 on t1.a = t2.a;
结果分析：先执行第一个id的sql语句，查询t2表，再查t1表进行关联
t2表示ALL，获取到所有的数据（100行记录），然后获取一条，去t1（t1用到了索引）表里面用这个条件去过滤，把关联到的结果集缓存到某个位置，最后一起返回，相对来说，t2扫描一次，t1扫描一次，最后总的扫描数据行200行。
image.png

基于块的嵌套循环链接Block Nest-Loop Join（BNL）算法

EXPLAIN SELECT FROM t1 INNER JOIN t2 on t1.b = t2.b;
结果分析：也是先查询t2表中的数据，然后放到join-buffer（内存汇中无序的，默认256k，如果放不下，可以分段放）中，把t1表中的每一行数据取出来，跟join-buffer中的数据做比对，返回满足join条件的数据。t1和t2都是全表扫描，t1表1万次，t2表100次。总次数10000100，1百万次，因为内存进行比对。
image.png

join-buffer：内存汇中无序的，默认256k，如果放不下，可以分段放到join-buffer中。
需要优化：
在没有索引的情况下，NLJ性能比BNL性能更高。一个磁盘对比，一个内存对比。
驱动表和被驱动表，驱动表一般是小表，先执行的，被驱动表是数据量比较大的，优化器一般会优先选择小表做驱动表。
被驱动表尽量走索引。
大表关联的字段一定要加索引，否则会全表扫描。极其影响性能

注意：如果发现sql底层优化的时候，没有取小表做驱动表，可以手动进行更改

  • select * from t2 straight_join t1 on t2.a = t1.a;手动进行强制修改驱动表，选择t2位驱动表。
  • straight_join只适用于inner join，不适应其它的left join与right join，（因为left join，right join已经代表指定了表的执行顺序）

in和exists
原则：也是小表驱动大表
in：当B表的数据量比A表的数据量少的时候，in性能优于exists

select from A where id in (select id from B)
#等价于：
for(select id from B){
select from A where A.id = B.id
}

exists与in相反，当A表的数据量比B表少的时候，exists由于in

select from A where exists (select 1 from B where B.id = A.id)
#等价于:
for(select from A){
select * from B where B.id = A.id
}
#A表与B表的ID字段应建立索引

count（*）查询优化

  1. set global query_cache_size=0;
  2. set global query_cache_type=0;
  4. EXPLAIN select count(1) from employees;
  5. EXPLAIN select count(id) from employees;
  6. EXPLAIN select count(name) from employees;
  7. EXPLAIN select count(*) from employees;

这四条语句5.7之后性能基本差不多，count就是扫索引树
字段有索引的情况：count（*）约等于count（1）>count（字段）>count（id）

  • count（字段）与count（id）比较，就是扫描二级索引，比主键索引效率要高
  • count（1）与count（字段）比较：count（字段）会从索引树里拿出来，再进行累加，count（1）直接扫描进行统计
  • count（）与count（1）比较：count（）做了特殊优化，不会取出字段，专门做了优化，按行累加，效率很高。

字段没有索引的情况：count（*）约等于count（1）>count（id）>count（字段）

如果表记录很大：

count（*）会计算null值的，count（字段）不会统计列的null值的。

常见优化方法

查询mysql自己维护的总行数

对于myisam存储引擎的表做不带where条件的count查询性能很高的，因为myisam会维护一个计算总数的，查询不需要计算，效率非常高
而对于innodb存储引擎的表，mysql不会存储总记录行数，需要进行计算。

show table status

如果就是用innodb存储引擎，可以使用上面这条sql语句
但是不是十分准确

总数维护到Redis中

插入或删除表数据行的时候同时维护redis里的表总行数key的计数值(用incr或decr命令)，但是这种方式可能不准，很难保证表操作和redis操作的事务一致性

增加数据库基数表

插入或删除表数据行的时候同时维护计数表，让他们在同一个事务里操作