mysql实战-表结构设计，索引选择。

之前看到一个系统的表结构，每张表都有外键，并且设置了级联更新，这令我非常震惊，我的知识范围让我无法想象为什么会有这样的表设计，我相信很多程序猿都看过阿里巴巴开发手册，里面明确表示不能使用外键，并且从业务上做了说明，这篇文章将从技术角度说一说表的结构设计，仅限于innodb。

为什么不能使用外键？

外键的强关联性限制表的重构，级联更新会影响数据库的性能，更可怕的是级联更新的操作不会记录在binlog中，这意味着什么我相信大家是有数的。

表主键是自增好还是UUID好？

在我工作之后，系统中的id字段几乎都是使用工具生成的64位UUID，但是某银行的系统他们所有的员工id均为数字，对外的系统如果担心id被利用进行暴力破解，可以使用UUID，如果从性能上考虑，自增ID是首选，当然业务端能够确保自增就更好了。为什么这么说呢，我们从索引的建立和查询的角度分析。如图（图中忽略主键id）：

 

我们假设这是一个表的主键索引树，不难看出，主键索引是字符串类型。那么你很容易就能想到，如果新增一条数据，是没有办法直接放进树中的，因为树是有顺序的，而字符串没有办法直接比较大小，这与UUID是完全一样的。那么你可能会说，我用数字不就行了吗，是的，但是这样并不能完全解决问题，因为更大的问题在于，没有办法顺序插入，因为即便是用数字，在业务端没有确保是递增的情况下而表也没有设置自增的话，后一次插入的数据主键值可能比先插入的小，就如图中，假设我现在插入一条东方青天3.5，那么根据b+树的性质你不难看出，数据必然会插入2,3,4所在的结点，并且如果单结点数量达到限制，为了保持树结构，就会发生结点分裂，顺序插入并不能完全避免这样的情况，但是相比非顺序插入，出现结点分裂的概率会低得多，性能也会更强。

非唯一索引与普通索引如何选择？

可能会有一些人说，当然是唯一索引啊，唯一索引能准确命中一行，查询速度不是快的多吗？而且前面的文章也提到，唯一索引的锁优化，命中行的时候锁的范围小于普通索引。并发度更高。那么我要先泼一盆冷水，为什么呢？

首先，唯一索引在查询数据的时候，效率的确更高，但这个差距微乎其微，如图：

假设index是一个索引，那么在查询index=1这一行时，由于mysql是按页顺序查询，所以这个会将index=2也查出来并且放入内存。 这个时候,普通索引仅仅是需要将内存中的数据进行判断操作，而这个判断操作耗时是非常小的。

其次是更新，这里不得不说mysql基于更新的优化，change buffer。如图：

 由于这里机制相近我们为了方便理解，简单的将change buffer当成 innodb buffer pool。

可以看到在change buffer中有三种类型的内存页。未使用页就是顾名思义就是还没有被使用过的页。干净页就是已经刷过的脏页，脏页就是有新的改动，还没有flush到硬盘的数据页。

现在修改数据有两种情况：

第一，数据在内存中，那么就直接更新内存。

第二，数据不在内存中，那么将更新操作记录在change buffer中。直到下次查询的时候再将脏页刷新到硬盘中。

不难看出，如果是唯一索引的话是没有办法利用change buffer走第二种情况的，因为唯一索引的插入和修改必须要检测唯一性。所以必须查询硬盘上的数据到内存。这样会有两个缺点

第一： 没办法减少硬盘随机写，性能不佳。

第二：由于会查询大量数据到内存，会使内存命中率急速下滑，懂得都懂。

说了change buffer的优点，再来说说缺点，由于change buffer 在查询数据时需要将符合的脏页flush到硬盘，所以在修改完数据马上就要执行查询的场景下，change buffer 优化是起不了作用的，反而会因为需要额外维护一个change buffer而降低性能。

字符串的索引应该怎么建？

我们用前面文章中的表来说明这个问题。如图：

首先我们知道，mysql是支持前缀索引的，索引的长度可以手动指定，不指定默认为字段长度。

如果我们在在username上建立一个索引，取前四位，显然，这个索引不仅没有作用还会占用空间。当然你会说直接全字符串建索引不就好了吗，反正也没有多长，那我要说，如果是身份证号呢？碰到这种前面一大截重复或者区分度不高的字段，一个简单的办法就是直接将字符串倒过来建立索引。不仅如此，前缀索引会对索引覆盖造成影响。因为使用前缀索引就必须回表查询，以图中id=10的这行数据为例，我们假设现在username是前缀索引，长度为6，语句如下

select * from where username ='东方青天10'

很明显，如果不查出id=10再回表查询，根本没有办法确定id=10的这一行username=东方青天10，因为很有可能有一条数据是username=东方青天101。

 

文章编写不易，转载请注明出处。