索引，一文搞定 | 数据库系列

前段时间写数据库内核比较多，最近又有朋友问索引的应用了，其实相关索引应用之前或多或少写过，只是可能不这么系统，这一篇简单总结一下，并系统性补充一些概念。

画外音：本文基于MySQL5.6。

 

什么是聚集索引，非聚集索引？

详见《聚集索引，普通索引的差异》。

索引底层数据结构如何？
详见《数据库索引，到底是什么做的？》。

InnoDB到底支不支持hash索引？
详见《InnoDB到底支不支持哈希索引？》。

 

什么是回表查询，索引的覆盖？

详见《如何避免回表查询？什么是索引覆盖？》。

 

什么是联合索引？

多个字段上建立的索引，能够加速复核查询条件的检索。

举例，登录业务需求：

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语句的写法必须满足索引的顺序。

主键与唯一索引约束是啥？
详见《MySQL不为人知的主键与唯一索引约束》，很多人经常在这里采坑。

 

类型不匹配，字符编码不匹配，会不会对索引命中产生影响？

会，详见《两类非常隐蔽的全表扫描，不能命中索引》，介绍得比较详细。

 

负向查询，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)，往往是悲剧的开始》。

 

希望大家对索引有更系统性的认识。

架构师之路-分享技术思路

相关文章：

《缓冲池(buffer pool)，彻底懂了！》
《写缓冲(change buffer)，彻底懂了！》

1024，祝大家节日快乐！谢转。