MYSQL的候选索引怎么建_Mysql_索引怎么建(inoodb)

如何建索引

索引太多，影响性能，每一次修改(增，删，改)都涉及到索引的改动。

所以索引应该是在满足需求的情况下，尽可能少的建。然后利用好联合索引。

首先看索引的概念

密集索引、稀疏索引

密集索引(唯一)

(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。