最近有个需求, 要修改现有存储结构, 涉及查询条件和查询效率的考量, 看了几篇索引和 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 复合索引, 将排序引入到索引结构中, 数据库负载骤降.
来源: https://juejin.im/post/5b14e0fd6fb9a01e8c5fc663