SQL查询优化——数据结构设计

本文部分内容会涉及mysql，可能在其他数据库中并不适用。本章节只针对数据库结构设计做讨论，查询优化的其他内容待续。
数据库设计及使用是WEB开发程序员必备的一项基础技能，在大数据量和高并发场景，合理的数据结构及SQL查询优化对项目来说都会显得格外重要。大部分有经验的程序员都能了解到，程序的瓶颈往往不在程序本身，而在数据访问层。造成数据访问效率低下的原因有很多，如何解决这些问题，直接影响到应用的稳定性、健壮性。以下列举几个常见的问题：
数据库锁表，查询阻塞
高并发场景下，链接数量瓶颈
查询效率低下，程序长时间无法退出
写入性能低下，造成读写竞争激烈

以上只是列出了数据库使用过程中比较常见的问题，出现这些问题的常见原因列举如下：

数据结构设计不合理
索引设计糟糕
程序维护数据链接不合理
程序员太懒惰，数据库做了不擅长的工作
数据冗余
SQL太渣

本节只对数据结构设计不合理进行讨论，后续章节会继续讨论其他内容。

一直觉得作为一个中级以上水平的程序员，查询优化是一项必备的基础技能。良好的数据结构设计，直接影响到后期软件的性能、健壮性、可维护性、可扩展性。见过很多因为数据结构设计不合理而造成软件最终难以扩展，难以维护的场景。要避免这些问题，我们就要掌握良好的数据结构设计能力。

怎样的数据结构才是合理的？这并没有一个完美通用的解决方案，要考虑具体的应用场景。但有一些准则，使我们应该尝试去遵守的。列举如下：

根据业务查询场景，考虑数据结构分布
如果没有业务主键，应建立ID自增主键
保证使用较小的数据类型，避免空间浪费
合理控制表的字段数量，必要时分表存储
添加字段注释

针对以上几点，分别详述如下：

1、根据业务场景，考虑数据结构分布

业务场景，决定了你要存储什么样的数据，但它不会决定你要如何存储这些数据。你可以简单的将这些信息存储到一张表里，例如user表。但当我们需要更多的信息，例如用户的附属属性（学校，住址等），如果全部塞到一张表里，对于小数据量的数据库不会有太大问题，但当遇到大数据量的场景时，查询就有可能变的缓慢。分表会是一个更好的解决方案，根据不同的业务场景，将这些信息分为两类，存储在不同的表里，是更加合理的解决方案。

这里要说的其实是，不要为了方便把所有的东西都塞到一张表里，虽然这样会让你的程序编写起来容易很多，但是会造成更多的问题。例如有些人会把1:N的关系存储到一张表里，这样就会带来数据冗余，坏处有很多，例如：针对N的写改删查都会变得很复杂；表体积变大、字段增多，造成查询缓慢；其他表链表查询时速度缓慢等等。

2、如果没有业务主键，应建立ID自增主键

主键是一条记录的唯一标志，没有主键在很多时候我们无法得心应手的操作数据。可能在某些场景下，我们确实没有设置主键的必要，但无论你是否主动设置主键，数据库都会有一个主键（如果你没有主动设置，数据库默认会有一个ROW_ID列，而这一列是你看不到的）。主键在连表、查询等方面业务提供很大帮助，所以无论如何，建立一个主键是很必要的

3、保证较小的数据类型，避免空间浪费

较小的数据类型意味着较小的存储代价，且数据库能够更高效的利用缓存空间。存储引擎都会采用不同的方式对索引或者数据缓存在内容中，较小的数据类型意味着在有限的内容空间中，你能够存储更多有价值的数据。对于可变长度的varchar类型，如果我们设置的是20长度，但实际占用的只有10个长度，在载入内存时，占用的空间依旧是20而不是10。所以对于可变长度类型，合理的长度更为重要。

4、合理控制表的字段数量，必要时分表存储

字段数量过多如果不是因为业务需且数据结构设计合理，大多会产生以下几个问题：

数据冗余
索引过多
表体积大

这里要提醒避免不必要的数据冗余，针对数据冗余的讨论我们暂且放在后面。

因为字段数据量多，往往查询场景也会非常复杂多变，所以索引也就跟着变多了。索引多会直接影响到表的写入性能，这个性能的损耗是非常大的，可能是数以十倍计算的时间损耗。在写入频繁的场景，有可能会出现写入瓶颈。由于写入而影响读取性能的问题也很多。

表体积大意味着数据库在读取数据的时候需要扫描更多更大的数据块，载入内存做缓存时也不能充分利用缓存带来的效果。表大小对于表的性能也是由为重要的。

分表是解决字段过多的一个解决方案，数据库分表后，程序可能会改动比较大，但我们应该追求合理完美的软件设计，摒弃糟粕。分表后使用链表查询，或者在程序中做两次查询。有些人可能会觉得连表，性能一定很差，其实不然。连表意味着我们在同一个SQL中，可以使用两个索引，但是单表查询我们只能使用一个索引。如果索引设计合理，在大多数场景下（应该是大数据量场景），连表查询会比单表查询性能更高，甚至高出太多。曾经有过这样的场景，优化分表后画面变得没好多了。

5、添加字段注释

这里只是为了提示规范化数据库设计。

原创文章，转载请注明： 转载自始终不够

本文链接地址: SQL查询优化——数据结构设计