拜托，别再问我数据库性能优化了！

一、前言

在谈论数据库的时候，经常能够听到“QPS”、“TPS”等词汇，其实吞吐量不过是数据库性能的呈现，对于数据库性能的本质，我更倾向于将其描述为响应时间量，即完成某次查询所需要的时间量。

两者的关系不难察觉，响应时间量减少，意味着每秒查询次数增多，所以数据库的性能优化，就是如何降低响应时间量。

既然目标是减少响应时间，那就需要清楚时间究竟花在了哪里。

在《让SQL飞：InnoDB下的锁策略》一文中，我有画过MySQL的架构图，不过这次讨论的是数据库的性能优化，对于SQL的执行步骤，需要将之前的图做一些修改。

二、优化（数据结构）

物理设计是良好性能的基础，存储方式对于数据库性能的提高至关重要。

1. 更小的与合适的

更小的数据类型意味着占用更少的磁盘、内存和缓存。同时，InnoDB是行存储模式，再以页模式从磁盘中读取数据，当页大小固定（InnoDB默认16k），行数据更小，则每页能够包含更多的行数，所以每次IO访问的行数增加，IO次数减少，性能随之提高。

不仅如此，由于InnoDB下数据在文件中特定的存储形式，当执行写操作时，有可能会导致页分裂与页合并，行数据的增大更是促进了这种情况发生的几率。悲观更新的结果就是索引树上加写锁，锁竞争意味着阻塞，不论是自增主键下的行删除影响了聚簇索引导致索引树重建，还是普通索引下的写操作，都不可能避免这种情况的出现。（单击跳转至《InnoDB中的页合并与分裂》）

稍微有些经验的设计者在做年龄存储时，不可能将age字段设置为int类型，因为他知道更小的数据结构能带来哪些好处。

但更小的并不总是更好的，我们有时需要为将来做些考虑。timestamp比datetime能节省一半的存储空间（timestamp4字节，datetime8字节），但timestamp能表示的范围却要比datetime小很多。

你也许听过一些前辈们的告诫，在存储小数时避开double的选择，它可能计算出错误的结果，但真的需要decimal吗？除非的确是要求如此准确的精度，譬如做财务记录，很多时候 ，double或许是一种不错的选择，要清楚，cpu是直接支持浮点计算的，double的性能远在decimal之上。

做字符串存储时记得char与varchar空间使用的区别，但是别忘了碎片问题，额外开销往往成为性能的瓶颈。

2. 更少的列与冗余的列

微服务将不同的业务拆成不同的模块，在表结构设计中，也需要根据不同的业务将字段拆到不同的表中。

减少单个表中的列有利于表的维护，从内存中向InnoDB拷贝数据时，需要通过行缓冲将编码过的列转换成行数据结构，其代价是非常高昂的，回表操作同样如此，没错，资源是用来消耗的，但不是用来浪费的。

另外，当有更新操作时，更少的列意味着更小的锁粒度，没有人将tb_user与tb_wallet合成一张表，尽管有时我们需要联表来完成某些逻辑。

不过有时我们可能的确需要做一些反范式的设计，即增加一些冗余的列，来避免联表以及减少聚合操作，但利弊相依，代价就是需要更多的存储空间与额外的维护开销，尤其是在频繁更新的数据库表中更要谨慎地权衡两者是否值得这样做。

3. 缓存表与汇总表

缓存表和汇总表与冗余的列有异曲同工之妙，只不过实现在单独的表中。

缓存表中存储与其它的表逻辑冗余的数据，将大表中活跃的数据单独拿出来构建成一张小表，通过减少单个表的数据量来提高查询性能，即所谓的热数据分离，尤其是在大表中的某些小部分数据被频繁访问时更能体现其带来的好处。

汇总表中存储与其它的表逻辑不同的衍生数据，通过减少聚合次数来提高查询性能，同样，这些数据也经常被访问。

但两者都必须面对数据的同步问题，对于允许最终一致性的场景来说，建立缓存表或者汇总表，无疑是一个好主意，但是如果需要实时更新，那就得多花一些心思来斟酌一二了。

4. 拆分与扩展

表的拆分与缓存表的原理十分相似，将大表拆成多个小表来降低单表的数据量，从而提高查询性能。

表的拆分没有冗余数据，但设计者需要面对的问题却更加复杂。当拆分出来的小表中存储的数据量呈现爆发增长而不得不再次拆分时。即便不需要提供不间断服务，在做数据迁移时也是一件痛苦的事情，不论采用一致性hash算法还是主从复制完再做处理，都不是一件容易的事情。

三、优化（索引）

索引是存储引擎用于快速找到记录的一种数据结构，同时，由于InnoDB中索引特殊的实现方式，在索引列做排序或者聚合操作时也能够得到非常快的响应，另外，索引可以让查询锁定更少的数据行，锁冲突的减少意味着阻塞减少，性能随之提高。（单击跳转至《让SQL飞：RR级别下的GAP锁范围》）

对于索引优化的讨论离不开索引的实现方式，InnoDB索引采用B+Tree实现，B+Tree是B-Tree的变种，B-Tree能解决的问题，B+Tree也能解决，同时，B+Tree有如下特点：

>多路搜索：单个支节点存储多个关键字，降低树的高度，搜索更快

>左闭合区间：更好地支持自增主键

>叶子结点存储数据：支节点不包含数据域，意味着支节点大小一定时，较B-Tree能存储更多的关键字；由于每次访问都需要找到叶子节点，效率更稳定

>叶子结点由链指针相连：扫表能力

1. 聚簇索引和二级索引

InnoDB的聚簇索引实际上就是在B+Tree的叶子节点中存储了完整的数据行，就是通常所说的主键上的索引。

InnoDB的数据库表必定且仅包含一个聚簇索引，当没有定义主键时，InnoDB会选择一个唯一的非空索引来代替，倘若不存在这样的索引，InnoDB会隐式定义一个主键来做聚簇索引。

定义聚簇索引时要求主键自增，自增的主键在做顺序插入时并发性能最好。uuid等随机字符串不适合在InnoDB中充当主键，随机插入将导致大量的页分裂出现，数据库性能急剧下降，这也是研发者在做分库分表时宁愿多花一些开销来做全局自增主键而不使用uuid的原因。

另外，主键的数据类型要求尽可能的小，同一张表中的所有二级索引都包含了主键值，更大的主键类型将使得二级索引占用更多的资源。

二级索引就是普通索引，B+Tree的叶子节点中仅包含主键，当需要获取完整的数据行时需要回表通过主键来查询，这就是该名字的由来，下面具体说明二级索引的优化。

2. 单列索引和多列索引

多个单列索引与一个多列索引完全不同，尽管在MySQL5.0版本中引入了索引合并，使得多个列在执行and操作时也能够利用到这些索引，但索引合并会耗费大量的cpu和内存资源，同时更说明建立的索引很糟糕。

当explain出现type=index_merge的时候，就需要考虑建立多列索引了。

建立多列索引通常依赖于where条件、同时需要考虑order by排序和group by聚合。

最左匹配原则告诉我们编写查询语句时的诸多限制，多列索引的顺序是如此重要。通常，我们按照选择性给多列索引排序，以便过滤掉更多的数据，但在使用过程中我们或许要考虑地更多，对于那些选择性较低但执行频率很高的列，是否需要适当的调整该索引列的顺序值得商榷。

在MySQL5.6版本的多列索引中，引入了一个叫索引下推的内部优化。对于多列索引中违反最左匹配而不能使用索引的列，在回表时一并将这些条件传到存储引擎，以便减少存储引擎的IO量。

3. 自定义哈希索引

InnoDB中尽管有自适应哈希索引，但它仅仅是作为InnoDB的一个内部优化，存储于内存，且不可控。

不过我们似乎可以从中得到一些启发，假设现在需要存储大量的长字符串，且经常需要对这些字符串做等值匹配。如果在此字段上直接建立B+Tree索引，那索引占用的内存空间将会是巨大的，这个时候我们可以考虑来创建自定义哈希索引解决这个问题，具体过程如下：

a>假设在表tb_net中address是该字段的名称，首先需要做的就是增加一个额外的列address_hash，通过hash函数计算出address的哈希值，将其作为address_hash的内容，address_hash=crc16(address)

b>匹配数据时，条件有两个，select address from tb_net where address_hash=crc16(${address}) and address=${address}

随着数据量增长，哈希冲突必然随之提高，需要时可以考虑更换哈希算法来降低哈希冲突。但是记住，哈希算法选择计算结果是数字类型的，而像MD5等，不仅计算出的哈希值长度大，且字符串在InnoDB中的计算效率远不如数字类型。

此外，可以看出，建立伪哈希索引类似于数据结构优化中的冗余列，所以也必须考虑维护该哈希列的代价。

4. 前缀索引

前缀索引与自定义哈希索引的目标相同，前缀索引通过索引字符串的开始部分来节约索引空间，但选择性随之降低，性能是否提高取决于索引的长度是否合理，需要在消耗资源与选择性之间做一个平衡。

前缀索引不支持排序、聚合，这点需要特别注意。

5. 覆盖索引

如果一个索引包含所有需要查询的字段，该索引就叫做覆盖索引，所以某些查询只需要使用索引就能够完成，而不需要回表。

这里举个例子，假设有个表tb_user，里面有字段name、age、phone，现在需要根据name排序，然后将name和age读取出来。name字段建立索引支持排序，但不可避免需要回表，如果我们建立的索引是index_name_age，就可以在索引中拿到所有需要的数据，这是典型的空间与时间权衡。

四、优化（查询语句）

查询语句的优化涉及到整个语句的执行步骤，没有一个放之四海而皆准的原则。

人们都知道select * from tb_*将导致回表，但在工作中很多人依然这样做，因为他们觉得代码复用带来的好处更多。

联表操作时需要小表驱动大表，目的是为了减少loop次数，但是倘若排序字段在大表里，恐怕小表驱动大表就不一定合适了。

在选择exist和in实现相同的功能时，经常根据索引来判断哪种方式更有效率，但不要忘了loop次数与hash join带来的开销，两者同样关键。

尽管没有通用的法则，但是从这几点来考虑肯定没有错：

1. 索引是否被使用，例如条件左侧有没有表达式

2. 回表次数能否减少，例如延迟关联

3. IO消耗能否降低，例如只返回需要的列

五、优化（配置）

倘若对你来说提升性能真的如此必要，即便牺牲一些安全也无所谓。

1. innodb_autoinc_lock_mode = 2

该参数为自增锁模式，可取3个值：

    0：语句结束释放，语句内的自增键连续

    1：普通insert立即释放，批量insert语句结束释放

    2：立即释放

当设置成2的时候，由于锁占有时间最少，所以在插入时并发性能最高，但语句内的自增键不能保证连续。

2. binlog_format = statement

该参数为binlog的存储格式，可取3个值：

    statement：每一条修改SQL的语句都会被记录

    row：每一条数据的修改都会被记录

    mixed：根据不同的情况选择以上两种

当设置成statement的时候，binlog相对于其它两种格式占用的空间最少，但是主从复制时可能出现问题，例如insert语句中包含一些特殊的函数。

3. sync_binlog = 0

该参数为binlog的持久化方式，可取3个值：

0：定时写入os buffer，调用sync()

    1：commit时写入os buffer，调用sync()

2：commit时写入os buffer，定时调用sync()

当设置成0的时候，commit时log仅存在于binlog buffer中，由于不必每次提交时做文件系统交换，所以这种配置下的性能最好，但是假如系统崩溃，将丢失掉这段时间内的日志，导致数据不完整。

4. innodb_flush_log_at_trx_commit = 0

该参数为redolog持久化方式，参数配置与sync_binlog相同，有自己的redolog buffer。

在InnoDB中，事务的持久性由redolog实现，所以系统一旦崩溃，持久性将不能得到保证；另外，undolog的持久化由redolog实现，事务的原子性不能保证。

六、结束语

不考虑业务场景而单独讨论数据库优化没有意义，很多时候都是在做权衡取舍，读写分离下需要考虑延迟问题，第三方缓存组件下需要考虑数据一致性问题。

但这些都不是糟糕设计的理由。

--------------------------------

公众号：以镒称铢

出自<孙子兵法 · 军形>，故胜兵若以镒称铢，败兵若以铢称镒。胜者之战民也，若决积水于千仞之溪者，形也。

长按下图二维码关注，你将发现一个不一样的世界，君子引而不发，跃如也~