最近有个需求,要修改现有存储结构,涉及查询条件和查询效率的考量,看了几篇索引和HBase相关的文章,回忆了相关知识,结合项目需求,说说自己的理解和总结。
错过的朋友可以先回顾下前3篇文章:
上一篇详细介绍了explain命令,通过该命令,可以定位出在哪一步出现了性能问题,下一步就是通过优化索引来解决它。
部分内容摘录了几个博友的文章,最后会给出文章链接,感谢他们的精彩分析。
常见优化方法
联合索引最左前缀原则
复合索引遵守「最左前缀」原则,查询条件中,使用了复合索引前面的字段,索引才会被使用,如果不是按照索引的最左列开始查找,则无法使用索引。
比如在(a,b,c)三个字段上建立联合索引,那么它能够加快a|(a,b)|(a,b,c)三组查询的速度,而不能加快b|(b,a)这种查询顺序。
另外,建联合索引的时候,区分度最高的字段在最左边。
不要在列上使用函数和进行运算
不要在列上使用函数,这将导致索引失效而进行全表扫描。
例如下面的 SQL 语句:
select * from artile where YEAR(create_time) <= '2018';
即使 date 上建立了索引,也会全表扫描,可以把计算放到业务层,这样做不仅可以节省数据库的 CPU,还可以起到查询缓存优化效果。
负向条件查询不能使用索引
负向条件有:!=、<>、not in、not exists、not like 等。
select * from artile where status != 1 and status != 2;
可以使用in进行优化:
select * from artile where status in (0,3)
使用覆盖索引
所谓覆盖索引,是指被查询的列,数据能从索引中取得,而不用通过行定位符再到数据表上获取,能够极大的提高性能。
可以定义一个让索引包含的额外的列,即使这个列对于索引而言是无用的。
避免强制类型转换
当查询条件左右两侧类型不匹配的时候会发生强制转换,强制转换可能导致索引失效而进行全表扫描。
如果phone字段是varchar类型,则下面的SQL不能命中索引:
select * from user where phone=12345678901;
可以优化为:
select * from user where phone='12345678901';
范围列可以用到索引
范围条件有:<、<=、>、>=、between等。
范围列可以用到索引,但是范围列后面的列无法用到索引,索引最多用于一个范围列,如果查询条件中有两个范围列则无法全用到索引。
更新频繁、数据区分度不高的字段上不宜建立索引
更新会变更B+树,更新频繁的字段建立索引会大大降低数据库性能。
「性别」这种区分度不大的属性,建立索引没有意义,不能有效过滤数据,性能与全表扫描类似。
区分度可以使用 count(distinct(列名))/count(*) 来计算,在80%以上的时候就可以建立索引。
索引列不允许为null
单列索引不存null值,复合索引不存全为null的值,如果列允许为 null,可能会得到不符合预期的结果集。
避免使用or来连接条件
应该尽量避免在 where 子句中使用 or 来连接条件,因为这会导致索引失效而进行全表扫描,虽然新版的MySQL能够命中索引,但查询优化耗费的 CPU比in多。
模糊查询
前导模糊查询不能使用索引,非前导查询可以。
优化案例
利用延迟关联或者子查询优化超多分页场景
MySQL 并不是跳过 offset 行,而是取 offset+N 行,然后返回放弃前 offset 行,返回 N 行。
当 offset 特别大的时候,效率非常低下,要么控制返回的总页数,要么对超过特定阈值的页数进行SQL改写。
可以先快速定位需要获取的id段,然后再关联:
selecta.* from 表1 a,(select id from 表1 where 条件 limit 1000000 ,10 ) b where a.id=b.id
如果明确知道只有一条结果返回,limit 1 能够提高效率
虽然自己知道只有一条结果,但数据库并不知道,明确告诉它,让它主动停止游标移动。
如何建立索引
where a=1 and b=1
where b=1
where b=1 order by time desc
建议建立两个索引,即 idx_ab(a,b) 和 idx_b_time(b,time)
MySQL 的查询优化器会自动调整where子句的条件顺序以使用适合的索引,对于上面的第一条 SQL,如果建立索引为idx_ba(b,a) 也是可以用到索引的。
多值匹配和范围匹配
假如有联合索引(empno、title、fromdate),下面的 SQL 是否可以用到索引,如果可以的话,会使用几个?
select * from employee.titles
where emp_no between '10001' and'10010'
and title='软件工程师'
and from_date between '2008-01-01'and '2018-01-01'
可以使用索引,可以用到索引全部三个列,这个 SQL看起来是用了两个范围查询,但作用于empno上的between实际上相当于in,也就是说empno 实际是多值精确匹配。
在 MySQL 中要谨慎地区分多值匹配和范围匹配,否则会对 MySQL 的行为产生困惑。
联合索引的最左匹配原则
假如建立联合索引(a,b,c),下列语句是否可以使用索引,如果可以,使用了那几列?
where a= 3 // 是,使用了a列
where a= 3 and b = 5 // 是,使用了ab列
where a = 3 and c = 4 and b = 5 // 是,使用了 a,b,c 列
where b= 3 // 否
where a= 3 and c = 4 // 是,使用了a列
where a = 3 and b > 10 and c = 7 // 是,使用了 a,b 列
where a = 3 and b like 'xx%' andc = 7 // 是,使用了 a,b 列
根据区分度创建索引
有如下查询语句,查找指定产品已审核(status=1)的评论:
SELECT user_id,title,content FROM `comment`
WHERE status=1 AND product_id=1
LIMIT 0,5 ;
可以建立联合索引,status和product_id,但是哪个放左边就要计算区分度:
SELECT COUNT(DISTINCT status)/COUNT(*) AS audit_rate,
COUNT(DISTINCT product_id)/COUNT(*) AS product_rate
FROM comment;
一般product的区分度会高点,可以创建如下索引:
CREATE INDEX idx_productID_Status ON comment(product_id,status)
排序字段索引
查看某个用户最近20条登录记录,按时间排序:
select * from login_history where uid = $uid order by create_time desc limit 20;
建立uid+timeline复合索引,将排序引入到索引结构中,数据库负载骤降。
参考文章:
欢迎扫描下方二维码,关注我的个人微信公众号,查看更多文章 ~
相关推荐
, 《数据库索引设计与优化》适用于已经具备了SQL 这一关系型语言相关知识,希望通过理解SQL 性能相关的内容,或者希望通过了解如何有效地设计表和索引而从中获益的人员。另外,《数据库索引设计与优化》也同样适用于...
作者通过系统的讲解及大量的案例清晰地阐释了关系型数据库的访问路径选择原理,以及表和索引的扫描方式,详尽地讲解了如何快速地估算SQL 运行的CPU 时间及执行时间,帮助读者从原理上理解SQL、表及索引结构、访问...
深入理解SqlServer索引机制及合理优化数据库
如何选择索引,如何创建高效实用的索引以及如何利用索引优化SQL等等。内容尽在其中, 希望爱学习的小伙伴,一起奋发进步,希望开发小伙伴能够更深层次的理解和了解索引, 合理利用索引来高效服务于我们系统。
MySQL索引 聚集索引 如果你想了解MySQL索引查询优化,你首先应该对MySQL数据组织结构、B-Tree索引、聚集索引,次要索引有一定的了解,才能够更好地理解MySQL查询优化行为。这里主要探讨MySQL InnoDB的聚集索引。
作者通过系统的讲解及大量的案例清晰地阐释了关系型数据库的访问路径选择原理,以及表和索引的扫描方式,详尽地讲解了如何快速地估算SQL运行的CPU时间及执行时间,帮助读者从原理上理解SQL、表及索引结构、访问方式...
作者通过系统的讲解及大量的案例清晰地阐释了关系型数据库的访问路径选择原理,以及表和索引的扫描方式,详尽地讲解了如何快速地估算SQL运行的CPU时间及执行时间,帮助读者从原理上理解SQL、表及索引结构、访问方式...
实际上,您可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引:聚集索引(clustered index,也称聚类索引、簇集索引)和非聚集索引(nonclustered index,也称非聚类索引、非簇集索引)。
作者通过系统的讲解及大量的案例清晰地阐释了关系型数据库的访问路径选择原理,以及表和索引的扫描方式,详尽地讲解了如何快速地估算SQL运行的CPU时间及执行时间,帮助读者从原理上理解SQL、表及索引结构、访问方式...
如何基于索引结构,理解常见的MySQL索引优化思路?索引结构的选择基于这样一个性质:大数据量时,索引无法全部装入内存。为什么索引无法全部装入内存?假设使用树结构组织索引,简单估算一下:假设单个索引节点12B,...
sql数据库优化--使用索引优化存储过程,相信你对数据库的理解一定不会少!对于你开发和设计数据库是不可少的哟!
这是 MySQL数据库性能优化专题 系列的第三篇文章:MySQL 数据库性能优化之索引优化 索引为什么能提高数据访问性能? 很多人只知道索引能够提高数据库的性能,但并不是特别了解其原理,其实我们可以用一个...
本篇文章主要介绍了MySQL——索引与优化,索引对查询的速度有着至关重要的影响,理解索引也是进行数据库性能调优的起点。有兴趣的可以了解一下。
再来看看什么是覆盖索引,有下面三种理解: 解释一: 就是select的数据列只用从索引中就能够取得,不必从数据表中读取,换句话说查询列要被所使用的索引覆盖。 解释二: 索引是高效找到行的一个方法,
在《数据库原理》里面,对聚簇索引的解释是:聚簇索引的顺序就是数据的物理存储顺序,而对非聚簇索引的解释是:索引顺序与数据物理排列顺序无关。正式因为如此,所以一个表最多只能有...我在这里从SQL Server索引优化查询