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 大表在外_小表驱动大表
10 声望
1 粉丝
推荐阅读
Appache httpclient证书认证过程
tcp三次握手后,client开始和server进行SSL连接,默认client用jdk的证书库对服务器证书进行认证,如果证书非法抛异常。如下示例代码
ThinkFault阅读 645
MySQL索引优化(万字详解)
前言索引优化这四个字说实话我认为其实挺难理解的。看到这四个字我脑门上是:????索引还要优化吗?调优SQL一般来说不就是看它有没有走索引,没走索引给它加上索引就好了吗?嗯,所以你是怎么给它加索引的?看...
阿紫阅读 253
分享会上狂吹MySQL的4大索引结构,没想到大家的鉴赏能力如此的~~~~
索引(index)是帮助MySQL高效获取数据的数据结构(有序)。在数据之外,数据库系统还维护着满足 特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据, 这样就可以在这些数据结构 上实现高级查找算法...
博学谷狂野架构师阅读 223
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。