Mysql索引优化

一、建立索引与查询优化

关键字：区分度

1.组合索引创建

【推荐】建组合索引的时候，区分度最高的在最左边。
正例：如果 where a=? and b=?，a 列的几乎接近于唯一值，那么只需要单建 idx_a 索引即可。
说明： 存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如： where c>? and d=? 
那么即使 c 的区分度更高，也必须把 d 放在索引的最前列，即建立组合索引 idx_d_c。

2.模糊查询

4.  【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。
说明：索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

3.order by 查询

【推荐】 如果有 order by 的场景，请注意利用索引的有序性。 order by  最后的字段是组合索
引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。
正例：where a=? and b=? order by c;  索引：a_b_c
反例：索引如果存在范围查询，那么索引有序性无法利用，如：WHERE a>10 ORDER BY b;  索引 a_b 无
法排序。

二、覆盖索引优化

涉及概念：回表查询，聚集索引，普通索引

1.覆盖索引介绍

InnoDB：

• 有两大索引，聚集索引和普通索引；聚集索引存储行数据，普通索引存储主键值
• 回表查询，以普通索引查询行的全部数据，必须走两张索引表，先到普通索引获取主键，再到聚集索引获取行数据。
• 覆盖索引，需要查询的数据在索引信息里面已经全部包含，不需要再回表。

举例

create table user (
id int primary key,
name varchar(20),
sex varchar(5),
index(name)
)engine=innodb;

表user的普通索引name，

查询1，只要name信息，不用回表，因为nane已经在索引中
select name from user where name='xxx';

查询2，sex信息需要从行记录里获取，需要回表
select name,sex from user where name ='xxx';

查询2的覆盖索引优化，对标user建立索引index(name, sex)

总结:覆盖索引，就是建立必要的联合索引，查询信息限定在联合索引中，多余的不要查

2.覆盖索引典型使用

• 优化超多分页场景

7.  【推荐】利用延迟关联或者子查询优化超多分页场景。
说明：MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当
offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL
改写。
正例：先快速定位需要获取的 id 段，然后再关联：
SELECT t1.* FROM  表 1 as t1, (select id from  表 1 where  条件  LIMIT 100000,20 ) as t2 where t1.id=t2.id

解读:假如用SELECT t1.* FROM 表 1 LIMIT 100000,20;直接查询，要取100020行，返回最后20行，速度明显比较慢。
正例中：通过子查询，覆盖索引先命中20条，然后再join两张表就快多了，子查询命中索引，关联操作再通过索引，没有遍历多条记录。

• 不等于查询。不等于要甚用,mysql 在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描，如果定要需要使用不等于,请用覆盖索引。

三、其他最佳实践

1.避免使用!=或＜＞、IS NULL或IS NOT NULL、IN ，NOT IN等这样的操作符.这些会导致mysql全表扫描

2.合理使用EXISTS,NOT EXISTS子句。

3.小表驱动大表:主要目的是通过减少表连接创建的次数，加快查询速度 。

---

参考资料
mysql覆盖索引与回表
阿里巴巴java开发手册嵩山版
mysql 不等于 优化_Mysql优化
MYSQL查询语句优化
mysql leftjoin 大表在外_小表驱动大表