Mysql索引实战优化(二)

一、分页查询优化

例如业务sql:SELECT * FROM employees LIMIT 10000,10

表示从表 employees 中取出从 10001 行开始的 10 行记录。看似只查询了 10 条记录，实际这条 SQL 是先读取 10010 条记录，然后抛弃前 10000 条记录，然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据，执行效率是非常低的。

当工作中业务出现根据一个字段排序的分页的时候
sql: select * from employees ORDER BY name limit 90000,5;
可以优化为
sql:select * from employees a ,(select id from employees order by name llimit 90000.5) b where a.id=b.id

二、Join关联查询优化

1.创建示例

创建t1表(10000条记录)，t2表(100条记录)（示例）：

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table t2 like t1;

-- 插入一些示例数据
-- 往t1表插入1万行记录
drop procedure if exists insert_t1; 
delimiter ;;
create procedure insert_t1()        
begin
  declare i int;                    
  set i=1;                          
  while(i<=10000)do                 
    insert into t1(a,b) values(i,i);  
    set i=i+1;                       
  end while;
end;;
delimiter ;
call insert_t1();

-- 往t2表插入100行记录
drop procedure if exists insert_t2; 
delimiter ;;
create procedure insert_t2()        
begin
  declare i int;                    
  set i=1;                          
  while(i<=100)do                 
    insert into t2(a,b) values(i,i);  
    set i=i+1;                       
  end while;
end;;
delimiter ;
call insert_t2();

执行关联表sql1: select * from t1 inner join t2 on t1.a= t2.a;(a字段带有索引)
执行计划如下:

顺序是先执行t2表，查出t2全表(100条记录)然后去跟t1表进行关联
底层执行也就是拿到一条一条t2的记录，然后走关联条件，再拿到t1表的对应记录。上图的执行计划中，t1表扫的是索引 idx_a 且row为1也就是说
t2每走一行记录，t1表扫了一次索引拿到对应的一条记录
因此上面的联表是t2表扫一行一路 t1表扫一行记录 总共扫描次数为100+100=200次

上述算法又叫做嵌套循环连接算法 Nested-Loop Join
解释:一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。

执行关联表sql2:select * from t1 inner join t2 on t1.b= t2.b;(b字段没有索引),执行计划如下：

关联字段没有用到索引，那么mysql会使用基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法
执行步骤:直接扫出t2表的所有数据，然后放入一个连接缓存(join buffer)
然后再从t1表里面一行一行取记录，然后去这个连接缓存中做对比(a.b=b.b)
因此执行次数为t1表扫了10000次，且t2表扫了100次
总共扫描是10000 + 100=10100次
假设使用NLJ算法执行上述关联，那么就是10000*100=100万次的扫描

join buffer:,无序的都要过滤一遍，大小:256k。如果join buffer 里面不够放，那么mysql会在里面存放80条然后在80条记录(假设80条记录256k)里面去过滤，拿到数据就结束，拿不到数据的话mysql会把这80条记录清空掉，然后再放入剩下20行记录去筛选

mysql联表：小表(驱动表)驱动大表(被驱动表)，如果数据量差不多的情况下，mysql是可能选错驱动表的有可能选择大表，那么可以使用straight_join

straight_join:如sql:select * from t2 straight_join t1 on t2.a = t1.a代表指定mysql选着 t2 表作为驱动表。straight_join左边的表去驱动右边的表。

因此联表的优化总结:尽量让关联字段是索引，并且尽量不去关联表，关联表的话尽量不超过三张表

三、in和exsits优化

原则也是小表驱动大表
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
 }

解析:也就是in里面的先执行，然后再跟外面的id去匹配值，因此如果B表的数据比较小，可以用in去比对一下

exists：当A表的数据集小于B表的数据集时，exists优于in
　　将主查询A的数据，放到子查询B中做条件验证，根据验证结果（true或false）来决定主查询的数据是否保留

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字段应建立索引

1、EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1替换,官方说法是实际执行时会忽略SELECT清单,因此没有区别
2、EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比
3、EXISTS子查询往往也可以用JOIN来代替，何种最优需要具体问题具体分析

四、count(*)查询优化

-- 临时关闭mysql查询缓存，为了查看sql多次执行的真实时间
mysql> set global query_cache_size=0;
mysql> set global query_cache_type=0;

mysql> EXPLAIN select count(1) from employees;
mysql> EXPLAIN select count(id) from employees;
mysql> EXPLAIN select count(name) from employees;
mysql> EXPLAIN select count(*) from employees;

count(1)的执行计划:

四个sql的执行计划一样，说明这四个sql执行效率应该差不多
字段有索引：count(*)≈count(1)>count(字段)>count(主键 id) //字段有索引，count(字段)统计走二级索引，二级索引存储数据比主键索引少，所以count(字段)>count(主键 id)
字段无索引：count(*)≈count(1)>count(主键 id)>count(字段) //字段没有索引count(字段)统计走不了索引，count(主键 id)还可以走主键索引，所以count(主键 id)>count(字段)

比较count(字段)与count(主键)，其实本质上的原理也是去扫描索引树，
由于扫描字段是扫描二级索引树，上面只存了索引字段-主键id空间更小
而主键索引中存储的是id-整行记录，扫的时候空间更大,5.6之后版本count(id)
他也会判断你表是否有二级索引，然后去选择那个相对较小的二级索引扫描。

有字段索引情况下，比较count(1)与count(字段)
count字段是将索引中的字段的那个拿出来 load内存里面然后去统计个数
而count(1)是直接判断有这个索引，然后在扫索引的索引直接往内存里面每有一条记录就放一个1，最后去统计1的数量。

count(*) 是例外，mysql并不会把全部字段取出来，而是专门做了优化，不取值，按行累加，效率很高，所以不需要用count(列名)或count(常量)来替代 count(*)（5.6之后）。

如果表记录非常多的情况下 count依旧会很慢，那么我们该怎么优化呢？
常见优化方法
1、查询mysql自己维护的总行数
对于myisam存储引擎的表做不带where条件的count查询性能是很高的，因为myisam存储引擎的表的总行数会被mysql存储在磁盘上，查询不需要计算
对于innodb存储引擎的表mysql不会存储表的总记录行数(因为有MVCC机制)，查询count需要实时计算
2、show table status
如果只需要知道表总行数的估计值可以用如下sql查询，性能很高

3、将总数维护到Redis里
插入或删除表数据行的时候同时维护redis里的表总行数key的计数值(用incr或decr命令)，但是这种方式可能不准，很难保证表操作和redis操作的事务一致性(不推荐，数据库跟缓存双写一致，代价非常高)
4、增加数据库计数表
插入或删除表数据行的时候同时维护计数表，让他们在同一个事务里操作

---

总结