MySQL（4）索引实践（2）

一、分页优化
limit 1000 10， 其实不是只查询出10条记录，mysql底层会查询出1100条，然后舍去前1000条
所以，随着页的增多，查询效率会降低
1、可以使用取范围的方式比如id>1000 方式优化
2、使用关联查询优化，子表使用覆盖索引，不用查出来所有数据，主表关联子表查询出数据
二、关联表执行过程
（1）两种算法
1、嵌套循环连接 Nested-Loop Join(NLJ) 算法
一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动
表）里取出满足条件的行，然后取出两张表的结果合集
2、基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法
把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比
，join_buffer默认是256k，如果驱动表中数据大于join_buffer的容量，会分配取数据到join_buffer

例如：select * from t1 inner join t2 on t1.a= t2.a;
驱动表先执行
优化器一般会优先选择小表做驱动表。所以使用 inner join 时，排在前面的表并不一定就是驱动表。
当使用left join时，左表是驱动表，右表是被驱动表，
当使用right join时，右表时驱动表，左表是被驱动表，
当使用join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表。

在被驱动表的关联字段中有索引的情况下
上面sql的大致流程如下：
1. 从表 t2 中读取一行数据（如果t2表有查询过滤条件的，会从过滤结果里取出一行数据）；
2. 从第 1 步的数据中，取出关联字段 a，到表 t1 中查找；
3. 取出表 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端；
4. 重复上面 3 步。
如果被驱动表关联字段没有索引
上面sql的大致流程如下：
1. 把 t2 的所有数据放入到 join_buffer 中
2. 把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比
3. 返回满足 join 条件的数据
（2）关联sql的优化
1、关联字段加索引
2、小表驱动大表，可以使用straight_join 明确驱动表，例如select * from t2 straight_join t1 on t2.a = t1.a; 代表指定mysql选着 t2 表作为驱动表。
，只适用inner join，left right join已经明确了驱动表和被驱动表
（3）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
}
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
}
1、EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1替换,官方说法是实际执行时会
忽略SELECT清单,因此没有区别
2、EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比
3、EXISTS子查询往往也可以用JOIN来代替，何种最优需要具体问题具体分析

三、count
1、对比效率
（1）select count(1) from user;
（2）select count(id) from user;
（3）select count(name) from user;
（4）select count(*) from user;
效率对比,如果name有索引  4=1>3>2， 如果没有 4=1>2>3
count(*)mysql做了优化，不会把所有字段查出来，不取值，按行累加
count（1）也是不取值，按行累加，
count（name）会把name值取出来，累加
count（id）会把id取出来，累加
因为count（id）走的主键索引，count（name）走的二级索引，二级索引数据量相对小
所以count（name）会快一点，但是count（id）mysql也会优化，不走主键索引走二级索引，如果有的话
注意一点的count（字段）如果字段值为null，不计分count，而count（*）会计入null值
2、优化count
如果表数据大，count需要优化
myisam储存引擎表不带where条件的count查询很快，因为总行数会被mysql储存在磁盘上
如果是innodb，不会维护count，因为有mvcc
如果不需要精确获取的话，可以用 show table status like 'table名';获取count
如果需要请求可以把count维护在redis或者数据表中