一、建立索引与查询优化

关键字:区分度

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 大表在外_小表驱动大表


ThinkFault
16 声望2 粉丝