熟悉mysql数据库设计和性能优化_MySQL性能优化学习笔记-(1)数据库设计

一、数据库设计

1.数据类型优缺点分析

数据类型的选择要遵循的总体原则

更小的通常更好

一般情况下，应该尽量选择使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们站用更小的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。

简单就好

简单数据类型的操作通常需要更少的CPU周期。

例如，整形比字符串操作代价更低，因为字符集和校对规则(排序规则)使字符串比整形比较更复杂。这里有两个例子：一个是应该使用MySQL内建的类型而不是字符串来存储日期和时间，另一个是应该使用整形存储IP地址。

尽量避免NULL

通常情况下最好指定列为NOT NULL，除非真的需要存储NULL值。因为查询中包含可以NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、统计索引和值比较都更为复杂。同时，可为NULL的列被索引时，每个索引记录需要一个额外的字节。一般，把可为NULL的列改为NOT NULL带来的提升比较少。

(1)整数类型

TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT分别使用8、16、24、32、64位存储空间，它们可以存储的值的范围从-2(N-1)到2(N-1)-1，其中N是存储空间的位数。

(2)实数类型

FLOAT使用4个字节、DOUBLE使用8个字节、DECIMAL使用9个字节。因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL。

(3)字符串类型

这里主要讲CHAR和VARCHAR。

VARCHAR类型用于存储可变长字符串，是最常见的字符串类型。它比定常更省空间，因为它仅使用必要的空间。但是VARCHAR需要使用1或2个额外字节记录字符串的长度。VARCHAR节省了存储空间，所以对性能也有帮助。

以下情况使用VARCAHR是合适的：字符串列的最大长度比平均长度大很多；列的更新很少，所以碎片不是问题；使用了像UTF-8这样复杂的字符集，每个字符都是使用不同的字节数进行存储。

注意，在使用VARCHAR时最好的策略时只分配真正需要的空间。因为更长的列会消耗更多的内粗，MYSQL通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或操作时特别糟糕，在利用磁盘临时表进行排序时也同样糟糕。

(4)BLOD和TEXT类型

BLOD和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。

MYSQL对BLOD 和TEXT列进行排序与其他类型是不同的；它只对每个列的最前max_sort_length字节而不是整个字符串做排序。如果只需要排序前面一小部分字符，则可以减少max_sort_length的配置，或者使用ORDER BY SUBSTRING(column,length)。

MYSQL不能将BLOD和TEXT列全部长度的字符串进行索引，也不能使用这些索引消除排序。

(5)日期和时间类型

MYSQL提供两种类似的日期类型：DATETIME和TIMESTAMP。

DATETIME这个类型能保存更大范围的值，从1001年到9999年，精度为秒，与时区无关。使用8个字节的存储空间。

TIMESTAMP类型保存了从1970年1月1日(格林尼治时间)以来的描述，它和UNIX时间戳相同。TIMESTAMP只使用4个字节的存储空间，因此它的范围比DATETIME小得多，只能表示从1970年到2038年。

如果在多个时区存储和访问数据，TIMESTAMP和DATETIME得行为将很不一样。前者提供得值跟时区有关系，后者则保留文本表示得日期和时间。

除了特殊行为之外，通常也应该尽量使用TIMESTAMP，因为它比DATETIME空间效率更高。

(6)位数据类型

MYSQL把BIT当作字符串类型，而不是数字类型。我们应该谨慎使用BIT类型，对于大部分应用，最好避免使用这种类型。如果想在一个bit得存储空间中存储一个true/false值，另一个方法时拆个年间一个可以为空的CHAR(0)列，该列可以保存空值或者长度为零的字符串

如果需要保存很多true/false值，可以考虑合并这些列到一个SET数据类型，它在MYSQL内部时以一系列打包的位的集合来表示的。这样就有效地利用了存储空间，并且MYSQL有像FIND_IN_SET()和FIELD()这样的函数，方便地在查询中使用。

它主要的缺点时改变列的定义代价高：需要ALTER TABLE，这对大表来说是非常昂贵的操作。一般来说，也无法再SET列上通过索引查找。

2.如何选择标识符(identifier)的数据类型

当选择标识列的类型时，不仅仅需要考虑存储类型，还需要考虑MySQL对这种类型怎么执行计算和比较。

一旦选定了一种个类型，要确保在所有关联表中都是用同样的类型。类型之间需要精确匹配，混用不同数据类型可能导致性能问题，即使没有性能影响，在比较操作时隐式类型转换也可能导致很难发现的错误。

在可以满足值的范围需要，并且预留未来增长空间的前提下，应该选择最小的数据类型。如有一个province_id列存储中国各省份名字，这个就不需要几千或几百万的个值，所以不需要INT，使用TINYINT就足够存储了，而且比INT节省了3个字节。如果这个值作为其他表的外键，3个字节可能导致很大的性能差异。

整数通常是标识列最好的选择，因为他们很快且可以使用AUTO_INCREMENT。同时，如果可能，应该避免使用字符串类型作为标识列，因为他们很消耗空间，并且通常比数字类型慢。

对于完全“ 随机 ”的字符串也需要多加注意，例如MD5()、SHA1()产生的字符串。这些函数生成的新值会任意分布在很大的空间内，这回导致INSERT以及一些SELECT语句变得很慢，因为：

插入值会随机地写到索引的不同位置(索引是按顺序存储)，所以使得INSERT语句更慢。这会导致页分裂、磁盘随机访问，以及对于聚簇存储引擎产生聚簇索引碎片。

SELECT语句会变得更慢，因为逻辑上相邻的行为会分布在磁盘和内存的不同地方。

随机值导致缓存对所有类型的查询语句效果都很差，因为会使得缓存赖以工作的访问局部性原理失效。如果整个数据集都一样的“热”，那么缓存任何一部分特定数据到内存都没有好处；如果工作集比内存大，缓存将会有很多刷新和不命中。

二、范式和反范式

对于任何给定的数据通常都有多种表示方法，从完全的范式化到完全的反范式化，以及两者的这种。在范式化数据库中，每个事实数据会出现并只出现一次。相反，在反范式化的数据库中，信息是冗余的，可能会存储在多个地方。

1.范式的优点和缺点

当为性能问题寻求帮助时，经常会被建议对schema进行范式化设计，尤其是写密集的场景。这通常是个好建议，因为范式化能带来一下好处：

范式化的更新操作通常比反范式化要快

当数据较好地范式化时，就只有很少或者没有重复数据，所有只需要修改更少的数据

范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快

很少有多余的数据意味着检索列表时更少需要DISTINCT或者GROUP BY语句

但是范式化设计的schema的缺点通常需要关联。稍微复杂一些的查询语句在符合范式的schema上都可能需要至少一次关联，也许更多。这不但代价昂贵，也可能使一些索引策略无效

2.反范式化的优点和缺点

反范式化的schema因为所有数据都在一张表中，可以很好地避免关联。

如果不需要关联表，则对大部分查询最差的情况，即使表没有使用索引(是全表扫描)。当数据比内存大时，这可能比关联要快得多，因为这样避免了随机I/O(全表扫描基本上是顺序I/O)。

同时，单独的表也能使用更有效的索引策略。

3.混用范式化和反范式化

事实是，完全的范式化和完全的反范式化schema都是实验室才有的东西，一般真实应用场景经常是混合使用，可能使用部分范式化的schema、缓存表以及其他技巧。

三、缓存表和汇总表

有时提升性能最好的方法是在同一张表中保存冗余的数据。有时候也需要创建一张完全独立的汇总表或缓存表(特别是为满足检索的需求)。

缓存表是表示存储哪些可以比较简单地从schema其他表获取(每次获取的速度比较慢)数据的表。

汇总表保存的是使用GROUP BY语句聚合数据的表。

例如下表要获取过去24小时准确的消息发送数量。

如果采用实时计算统计，这个操作是很昂贵的，因为要么需要扫描表中的大部分数据，要么查询语句只能在某些特定的索引上才能有效运行，而这类特定索引一般会对UPDATE操作有影响，所以一般不希望创建这样的索引。

缓存表则相反，其对优化搜索和检索查询语句很有效。这些查询语句经常需要特殊的表和索引结构。

在使用缓存表和汇总表时，必须决定是实时维护数据还是定期重建。那个更好依赖于应用程序，但是定期重建并不只是节省资源，也可以保持表不会有很多碎片，以及有完全顺序组织的索引。

四、计数器表

计数器表在Web应用中很常见，可以用这种表缓存一个用户的朋友数、文件下载次数等。创建一张表独立的存储计数器通常是是个好主意，这样可以使计数器表小且快。使用独立的表可以帮助避免查询缓存失效。

但是，如果应用在表中保存计数器，则在更新计数器时可能碰到并发问题。

加入有一个计数器表，只有一行数据，记录网站的点击次数：

网站每次点击都会导致对计数器进行更新：

问题在于看，对于任何想要更新这一行的事务来说，这条记录上都有一个全局的互斥锁。这会使得这些事物只能串行执行。要获得更高的并发性能，可以将计数器保存在多行中，每次随机选择一行进行更新。这样做需要对计数器表进行如下修改：

然后预先在这张表增加100行数据。现在选择一个随机的槽进行更新：

要获得统计结果，需要使用下面这样的聚合查询：

更快地读，更慢地写

为了提升读查询的速度，经常会需要建一些额外索引，增加冗余列，甚至是创建缓存表和汇总表。这些方法会增加写查询的负担，也需要额外的维护任务，但在设计高性能数据库时，这些都是常见的技巧；虽然写操作变得更慢了，但更显著提高了读操作的性能。

然而，写操作变慢并不是读操作变快所付出的唯一代价，还可能同时增加了读操作和写操作的开发难度。

五、小结

尽量避免过度设计，例如设计极其复杂查询的schema设计，或者有很多列的表设计

使用小而简单的合适数据类型，除非真实数据模型中有确切的需要，否则应该尽可能地避免使用NULL值

尽量使用相同的数据类型存储相似的值，尤其是要在关联条件中使用的列

注意可变长字符串，其在临时表和排序时可能导致悲观的按最大长度分配内存

尽量使用整形定义标识列

小心使用ENUM和SET，最好避免使用BIT

范式是好的，但是反范式有时也是必须的，并且能带来好处。预先计算、缓存或生成汇总表也可能获得大的好处

ALTER TABLE是让人痛苦的操作，因为大部分情况下，它都会锁表并且重建整张表。 解决方法有两种：一种是在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换；另一种方法是“影子拷贝”，即用要求的表结构创建一张和源表无关的新表，然后通过重命名和删表操作交换两张表

在查找表时采用整数逐渐而避免采用基于字符串的值进行关联

打赏

微信扫一扫，打赏作者吧～