04-28MySql索引（二）

对于小表定义的明确

在两个表按照各自的条件过滤后，计算join的各个字段的总数据量，数据量小的那个表，就是“小表”，也就是驱动表。

关联sql的优化

• 关联字段加索引， t1 inner join t2 on t1.a= t2.a ，在t1和t2的a字段上都做索引。这样mysql做join操作时会选择NLJ算法。驱动表因为需要全部查询出来，所以过滤的条件也尽量要走索引，避免全表扫描，总之，能走索引的过滤条件尽量都走索引。
• 小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间。
  • straight_join能改变连表查询的执行顺序，能指定让左边的表来驱动右边的表。
  • select * from t2 straight_join t1 on t2.a = t1.a; 代表指定mysql选着 t2 表作为驱动表
  • straight_join只适用于inner join，不能用在left join和right join。一般不建议使用，让优化器去判断

in和exsits优化原则，小表驱动大表

in：当A表的数据集大于B表的数据集时，in优于exists

select * from A where id in (select id from B)  
/*
从B表读取一条记录，然后去a表匹配
*/
for(select id from B){
      select * from A where A.id = B.id
}

exists：当A表的数据集小于B表的数据集时，exists优于in

select * from A where exists (select 1 from B where B.id = A.id)
/*
从a表读取一条记录，然后去b表匹配
*/
for(select * from A){
      select * from B where B.id = A.id
}

EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1替换,官方说法是实际执行时会忽略SELECT清单,因此没有区别。

count(*)查询优化

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

上面的4种写法执行计划一样，都能走索引（ken_len=140）。

字段有索引：count(*)≈count(1)>count(字段)>count(主键 id)

字段有索引，count(字段)统计走二级索引，二级索引存储数据比主键索引少，所以count(字段)>count(主键 id)

字段无索引：count(*)≈count(1)>count(主键 id)>count(字段)

字段没有索引count(字段)统计走不了索引，count(主键 id)还可以走主键索引，所以count(主键 id)>count(字段)

• count(*) ，mysql专门做了优化，效率很高。
• count(1)，不需要取出字段统计，就用常量1做统计，所以理论上count(1)比count(字段)会快一点。
• count(字段)走二级索引，取出字段去统计。
• count(id)，是走mysql辅助索引，因为二级索引相对主键索引存储数据更少，检索性能应该更高

常见count优化方法

查询mysql自己维护的总行数

• myisam存储引擎的表的总行数会被mysql存储在磁盘上，查询不需要计算
• innodb存储引擎的表mysql不会存储表的总记录行数,查询count需要实时计算

show table status

如果只需要知道表总行数的估计值(只是个估算值)可以用如下sql查询，性能很高

将总数维护到Redis里

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

增加数据库计数表

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