Mysql优化学习笔记

in Mysql with 1 comment

Mysql优化学习笔记

一、数据库设计

转自: 《MySQL性能优化学习笔记-(1)数据库设计》 | shuwoom.com

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操作,然后和提供服务的主库进行切换;另一种方法是“影子拷贝”,即用要求的表结构创建一张和源表无关的新表,然后通过重命名和删表操作交换两张表
在查找表时采用整数逐渐而避免采用基于字符串的值进行关联

Comments are closed.