目录
7. 类型不匹配,字符编码不匹配,会不会对索引命中产生影响?
8. 负向查询,IN,OR,UNION哪些会对索引命中有影响?
前段时间写数据库内核比较多,最近又有朋友问索引的应用了,其实相关索引应用之前或多或少写过,只是可能不这么系统,这一篇简单总结一下,并系统性补充一些概念。
画外音:本文基于MySQL5.6。
1. 什么是聚集索引,非聚集索引?
详见《聚集索引,普通索引的差异》。
2. 索引底层数据结构如何?
详见《数据库索引,到底是什么做的?》。
3. InnoDB到底支不支持hash索引?
详见《InnoDB到底支不支持哈希索引?》。
4. 什么是回表查询,索引的覆盖?
详见《如何避免回表查询?什么是索引覆盖?》。
5. 什么是联合索引?
多个字段上建立的索引,能够加速复核查询条件的检索。
举例,登录业务需求:
select uid, login_time from t_user where
login_name=? and passwd=?
可以建立(login_name, passwd)的联合索引。
联合索引能够满足最左侧查询需求,例如(a, b, c)三列的联合索引,能够加速a | (a, b) | (a, b, c) 三组查询需求。
这也就是为何不建立(passwd, login_name)这样联合索引的原因,业务上几乎没有passwd的单条件查询需求,而有很多login_name的单条件查询需求。
SQL语句里,where条件中的and前后的顺序,是否会影响索引的命中?
不会(这是很多朋友容易搞错的地方)。
举个例子:
select uid, login_time from t_user where
passwd=? and login_name=?
能否命中(login_name, passwd)这个联合索引?
可以,最左侧查询需求,并不是指SQL语句的写法必须满足索引的顺序。
6. 主键与唯一索引约束是啥?
详见《MySQL不为人知的主键与唯一索引约束》,很多人经常在这里采坑。
7. 类型不匹配,字符编码不匹配,会不会对索引命中产生影响?
会,详见《两类非常隐蔽的全表扫描,不能命中索引》,介绍得比较详细。
8. 负向查询,IN,OR,UNION哪些会对索引命中有影响?
举个例子:
假设订单业务表结构为:
order(oid, date, uid, status, money, time,…)
相关字段上也建立了索引。
假设订单有三种状态:0已下单,1已支付,2已完成
业务需求,查询未完成的订单,哪个SQL更快呢?
(1)select * from order where status!=2
(2)select * from order where status IN(0,1)
(3)select * from order where status=0 or status=1
(4)select * from order where status=0
union all
select * from order where status=1
第一个:select * from order where status!=2
负向查询肯定不可以命中索引,无争议。
画外音:
前导模糊查询,像like '%XX'是同样的道理,无法命中索引;
非前导模糊查询,像like 'XX%',则可以命中索引。
最后一个:
select * from order where status=0
union all
select * from order where status=1
两个SQL结果集合并,肯定可以命中status索引,无争议。
IN查询:
select * from order where status in (0,1)
explain下试试看?是可以命中索引的。
OR查询:
select * from order where status=0 or status=1
新版MySQL,可以优化为IN查询,故也是可以命中索引的。
其他一些实践:
(1)数据区分度不大的字段,不宜使用索引。
例如:
select * from user where sex=1
因为,性别只有男,女,每次过滤掉的数据很少,性能和全表扫描差不多。
(2)属性上的计算,不能命中索引。
select * from order where YEAR(date) < ='2019'
即使date上建立了索引,也会全表扫描,可优化为值计算:
select * from order where date < =CURDATE()
或者:
select * from order where date < ='2019-10-24'
后两者可以命中索引。
(3)如果列允许null值,查询会有巨大的坑。
例如:
select * from user where name != 'shenjian'
假设数据表有:
(1, shenjian)
(2, zhangsan)
(3, lisi)
(4, null)
你猜猜结果集中会包含哪些记录?
画外音:《数据库允许空值(null),往往是悲剧的开始》。