儒猿-索引 - 《MySQL》

1、查询成本
2、semi join
3、mysql使用错误的索引怎么办？
4、针对一个商品几十万评论的深度分页

constant：主键索引或者唯一索引
ref ：普通索引，或者主键、唯一索引搞了个is null 或者 is not null
range : 范围查找
index ：只要遍历二级索引就可以拿到想要的数据，不需要回表，叫做index访问方式
select * from table where x1 = ? and x2 > 1; 如果索引是两个分别为：index（x1,x3）、index(x2,x4),那么优化器会查看x1,x2两个索引分别对应的扫描行数谁比较小，那么就采用哪个索引查找出对应的主键（比如这边x1的行数比较少），回表查出满足记录的内容加载到内存中，再对x2的条件进行筛选

1、查询成本

IO成本，MySQL都是一页一页读，读一页的成本约定为1.0

CPU成本，一般约定读取和检测一条数据是否符合条件的成本是0.2

## show table status like 'table_name'
## sql成本 = IO成本 + CPU成本
## IO成本 = Data_length 除以 1024 获得KB为单位的值  再除以16KB 获得数据页的数量 再乘以 1.0 + 微调值
## CPU成本 = Rows * 0.2 + 微调值

2、semi join

不需要把表里的数据跟物化临时表里的数据join上，只要表里的一条数据去物化临时表中去匹配，这就是semi join，半连接优化
关闭半连接优化：set optimizer_switch=’semijoin=off’

儒猿MySQL 111讲

3、mysql使用错误的索引怎么办？

explain select * from products 
where category = 'xx' and sub_category = 'xx'
order by id desc limit xx,xx

基础的索引存在，key index_category(category,sub_category)
但是explain的时候，发现实际的key是PRIMARY,而且Extra中写明了using where

解决的方法：强制使用索引

select * from products force index(index_category)
where category = 'xx' and sub_category = 'xx'
order by id desc limit xx,xx

遗留的问题：
- 为什么MySQL默认选择对主键的聚簇索引进行扫描？
- 为什么没使用index_category这个索引？
- 为什么MySQL以前没有问题，现在突然就有问题？
解答：因为如果一个表的数据量特别大，然后mysql会判断使用二级索引查询到很多数据，还有根据id利用临时磁盘进行排序，效率不是很高。然后MySQL会直接扫描主键，因为主键都是已经排序的，然后只要返回limit的条数就可以了。为什么之前没有问题？可能是因为管理人员新加了类别和子类别，然而没有对应的商品，导致数据一直无法匹配，然后扫描了全表的数据。慢查询就那么产生了。

4、针对一个商品几十万评论的深度分页

select * from comments
where product_id = 'xx'
and is_good_comment = '1'
order by id desc limit 100000,20

存在的索引：index_product_id(product_id)
执行的步骤：根据index_product_id查出满足条件的数据，由于索引中没有is_good_comment字段，所以回表后再根据is_good_comment进行筛选，如果表中数据很多，那么进行大量回表，然后再根据id排序，排完序之后基于limit 1000000，20获取20条数据返回，效率很低！

优化：

select * from comment a,
(
select id from comment
where  product_id = 'xx'
and is_good_comment = '1'
order by id desc
limit 100000,20
) b
where a.id = b.id;

执行步骤：会优先执行子查询，通过聚簇索引筛选出符合条件的数据，而且并不需要把所有符合条件的数据都筛选出来，只有100000+20条，再根据id去查完整的数据，达到优化的效果。减少数据查询量，和回表的操作