如何建索引

索引太多,影响性能,每一次修改(增,删,改)都涉及到索引的改动。
所以索引应该是在满足需求的情况下,尽可能少的建。然后利用好联合索引。

首先看索引的概念

密集索引、稀疏索引

密集索引(唯一)

(1)若一个主键被定义,该主键则作为密集索引;
(2)若没有主键被定义,改表的第一个唯一非空索引则作为密集索引;
(3)若不满足以上条件,InnoDB内部会生成一个隐藏主键(密集索引)

针对大部分情况,我们的主键就是这个密集索引。
例如建表的:



PRIMARY KEY (`id`),



id就会是密集索引

稀疏索引

我们建表时的辅助索引



KEY (`uid`)



uid 就 就是稀疏索引。

区别

密集索引的B+树叶子节点包含了,数据所有的信息。
稀疏索引的B+树叶子节点只包含了指向密集索引叶子节点的指针。

所以一般利用稀疏索引的查找,都会回查一次密集索引的叶子节点,这个过程就叫做回表。

覆盖索引

针对上面回表的问题,我们有没有办法优化呢,因为我们业务肯定不能停留在用主键查找上。

索引覆盖就是为了解决这个问题的。



select id from Table where uid between 3 and 5



这时只需要查 id 的值,而 id 的值已经在 uid 索引树上了,因此可以直接提供查询结果,不需要回表了。

联合索引

业务上也不仅仅是利用索引查主键,也会有一些其他查询方式。
这样我们就能利用联合索引了。
联合索引其实就是在稀疏索引上解决覆盖问题的,利用联合索引减少不必要的回表操作。

举例:
一个普通索引是基于uid,去查用户信息,如果有大量的高频请求是仅仅基于uid,查询用户name。
那么像下面这样建一个联合索引就能减少回表操作了。



KEY `name_age` (`uid`,`name`)



当然联合索引也不要乱建,维护索引也是需要消耗性能的,这个就是需要结合实际的业务情况来处理了。

唯一索引vs普通索引

好像阿里对这个有个军规。
唯一索引和普通索引间的选择原则是,能建唯一索引,就一定要是唯一索引。

我们先比较下他们间的区别:

查询过程

普通索引找到满足条件后,会继续查询。
唯一索引找到满足条件后,会停止查询。

普通索引多查的那一下性能克忽略不计。

更新过程

唯一索引因为需要判重,不能用到changebuffer ,性能上会查于普通索引。

举例:
插入一个新记录(4,400)

第一种情况是,更新的目标页在内存中,:
唯一索引:找到3和5之间的位置,判断到没有冲突,插入这个值,语句执行结束
普通索引:找到3和5的位置,插入这个值,语句执行结束
差别: 唯一索引多了一个判断,只会耗费微小的CPU时间。

第二中情况是,更新的目标页不在内存中:

唯一索引: 判断唯一性,从磁盘中读取所在的数据页到内存中,判断到没有冲突,插入值,结束
普通索引: 将更新记录在change buffer,结束

将数据从磁盘读入内存涉及随机io的访问,是数据库里面成本最高的操作之一。changebuffer因为减少了随机磁盘访问,所以对更新性能的提升是会很明显的

所以更新这种情况,普通索引性能更好。

总结

纯从技术角度,写多,读少的场景,使用普通索引会更好。例如账单,日志类。

但是从业务开发和实际场景建议: 如果这个索引有业务唯一属性,就建议能用唯一索引就唯一。

实际场景中,如果业务上这个key有唯一的特性,但是为了性能用上普通索引,这个在后续维护中,我们期望唯一的key,肯定会在某个时刻不唯一(墨菲定律)。
可能那个时候,需要处理的成本远远高于一次change buffer。

  • 第一章_基础篇
  • 安装篇
  • 使用篇(phpmyadmin)
  • 使用篇(命令行)
  • 备份/恢复
  • 第二章_知识篇
  • 为什么是B+树
  • 主键怎么选
  • 查询如何闭坑
  • 索引怎么建(inoodb)
  • 事务
  • 数据库查询流程
  • 日志
  • 数据库安全
  • 第三章_进阶篇
  • 数据一致性
  • 数据库连接池
  • 性能监控
  • 第四章_经验教训篇
  • 微服务下数据库共享
  • update导致删库
  • 服务重试导致数据库连接池打满
  • 一句话case
  • 数据库耦合连带问题