高性能mysql1001无标题_高性能mysql：schema与数据类型优化

选择优化的数据类型

mysql支持的数据类型非常多,正确选择的数据类型对于获得高性能至关重要。不管存储哪种类型的数据，下面几个简单的原则都有助于做出更好的选择：

更小通常更好，尽量使用可以正确存储数据的最小数据类型，更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少

简单就好，简单数据类型的操作通常需要更少的CPU周期。例如，整型比字符操作代价更低，因为字符集和校对规则(排序规则)使字符比较比整型比较更复杂。使用mysql内建的类型(data、time、datatime)而不是字符串来存储日期和时间，用整型存储IP地址

尽量避免NULL，最好指定列为NOT NULL，除非真的需要存储NULL值。如果查询中包含可为NULL的列，对mysql来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在mysql中也需要特殊处理。当可为NULL的列被索引时，每个索引记录需要一个额外的字节。InnoDB使用单独的位(bit)存储NULL值。

整数类型

有两种类型的数字：整数和实数。如果存储整数，可以使用这几种整数类型：tinyint、smallint、mediumint、int、bigint，分别使用：8、16、24、32、64位存储空间，它们可以存储值的范围从-2^(N-1)到(2^(N-1))-1，其中N是存储空间的位数。

整数类型有可选的unsigned属性，表示不允许负值，这大致可以使正数的上限提高一倍，例如无符号型tinyint可以存储的范围是0~255，而tinyint的存储范围是-128~127。有符号和无符号类型使用相同的存储空间，并具有相同的性能，因此可以根据实际情况选择合适的类型。

mysql可以为整数类型提供宽度，如int(11)，对大多数应用这是没有意义的：它不会限制值的合法范围，只是规定了mysql的一些交互工具用来显示字符的个数。对于存储和计算来说，int(1)和int(20)相同。

实数类型

实数是带有小数部分的数字，然而，它们不只是为了存储小数部分，也可以使用decimal存储比bigint还大的整数。mysql既支持精确类型，也支持不精确类型：

float和double类型支持使用标准的浮点运算进行近似计算 decimal类型用于存储精确的小数 浮点和decimal类型都可以指定精度，对于decimal列，可以指定小数点前后所允许的最大位数，这会影响列的空间消耗。mysql 5.0和更高版本将数字打包保存到一个二进制字符串中(每4个字节存9个数字)。例如，decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节。mysql 5.0和更高版本中的decimal类型允许最多65个数字。

浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。float使用4个字节存储，double占用8个字节，相比float有更高的精度和更大的范围。和整数类型一样，能选择的只是存储类型；mysql使用double作为内部浮点计算的类型。

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用decimal——例如存储财务数据。但在数据量比较大的时候，可以考虑使用bigint代替decimal，将需要存储的货币单位根据小数的位数乘以相应的倍数即可，这样可以同时避免浮点存储计算不精确和decimal精确计算代价高的问题。

mysql允许使用非标准语法：float(m,d)、real(m,d)或double(m,d)，这里(m,d)表示该值一共保存m位数字，其中d位数字在小数点后面。例如，定义为float(7,4)的列保存值的范围：-999.9999~999.9999，在实际保存值时会四舍五入，如果在float(7,4)列内插入999.00009，实际保存值999.0001。

decimal和numeric在mysql中视为相同的类型，它们用于保存精确值，例如财务数据。当定义列为该类型时，可以指定精度和标度，例如，decimal(5,2)中5是精度，2是标度，精度表示可以保存数字的总位数，标度表示小数点后可以保存数字的位数。

字符串类型

mysql支持多种字符串类型，每种类型还有很多变种。

varchar和char varchar和char是两种最主要的字符串类型。

varchar类型用于存储可变长字符串，是最常见的字符串数据类型，它比定长类型更节省空间，因为它仅使用必要的空间(例如，越短的字符串使用越少的空间)。在mysql 5.0或更高版本，存储和检索varchar时会保留末尾空格。varchar需要使用1或2个额外字节记录字符串的长度：如果列的最大长度小于或等于255字节，需额外使用1个字节，否则使用2个字节。 下面这些情况下使用varchar是合适的：

字符串列的最大长度比平均长度大很多

列的更新很少，所以碎片不是问题

使用了像utf-8这样复杂的字符集，每个字符都使用不同的字节数进行存储 char类型是定长的：mysql总是根据定义的字符串长度分配足够的空间。当存储char值时，mysql会删除所有的末尾空格。char适合存储很短的字符串，或者所有值都接近同一个长度：

char非常适合存储密码的md5值，因为这是一个定长的值

对于经常变更的数据，char也比varchar更好，因为定长的char类型不容易产生碎片

对于非常短的列，char也比varchar在存储空间上也更有效率

VARCHAR(5)和VARCHAR(200)存储'hello'的空间开销是一样的.更长的列会消耗更多的内存,应为mysql通常会分配固定大小的内存块来保存内部值.尤其是使用内存临时表进行排序或操作时会特别糟糕.再利用磁盘临时表进行排序时也同样糟糕.所以最好的策略是只分配真正需要的空间.

varbinary和binary

varbinary和binary类型存储的是二进制字符串。二进制字符串和常规字符串非常相似，但是二进制字符串存储的是字节码而不是字符，填充也不一样：mysql填充binary采用的是\0(零字节)而不是空格，在检索时也不会去掉填充值。

当需要存储二进制数据，并且希望mysql使用字节码而不是字符进行比较时，这些类型是非常有用的。二进制比较的优势并不仅仅体现在大小写敏感上。mysql比较binary字符串时，每次按一个字节，并且根据该字节的数值进行比较。因此，二进制比较比字符比较简单很多，所以也就更快。

blob和text

blob和text都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。实际上，它们分别属于两组不同的数据类型家族：字符类型是tinytext、text、mediumtext、longtext；对应的二进制类型是tinyblob、blob、mediumblob、longblob。

与其它类型不同，mysql把每个blob和text值当作一个独立的对象处理。存储引擎在存储时通常会做特殊处理。当blob和text值太大时，InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后在外部存储区域存储实际的值。

blob和text家族之间仅有的不同是blob类型存储的是二进制数据，没有排序规则或字符集，而text类型有字符集和排序规则。

mysql对blob和text列进行排序与其他类型是不同的：它只对每个列的最前max_sort_length字节而不是整个字符串做排序。

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

日期和时间类型

mysql能存储的最小时间粒度为秒，提供两种相似的日期时间类型：datetime和timestamp，提供日期类型：date，提供时间类型：time。

datetime类型能保存大范围的值，从1001年到9999年，精度为妙，使用8个字节的存储空间。

timestamp类型保存了从1970年1月1日午夜(格林尼治标准时间)以来的秒数，它和UNIX时间戳相同，使用4个字节的存储空间，因此它的范围比datetime小的多：只能表示从1970年到2038年。timestamp显示的值也依赖于时区，mysql服务器、操作系统，以及客户端连接都有时区设置。

默认情况下,如果插入时没有制定第一个TIMESTAMP列的值,mysql则设置这个列的值为当前时间.再插入一行记录时,mysql默认也会更新第一个TIMESTAMP列的值.TIMESTAMP列默认为NOT NULL.

mysql没有提供合适的数据类型存储比秒更小粒度的日期和时间,可以使用BIGINT类型存储微秒级别的时间戳,或者用double存储之后的小数部分.

位数据类型

BIT 在mysql5.0之前BIT和TINYINT是同义词.5.0以及更新版本mysql把BIT当做字符串.最大长度为64位

选择标识符

为标识列(identifier column)选择合适的数据类型非常重要。一旦选定了一种类型，要确保在所有关联表中都使用同样的类型，类型之间需要精确匹配。在可以满足值的范围的需求，并且预留未来增长空间的前提下，应该选择最小的数据类型:

整数通常是标识列最好的选择，因为它们很快并且可以使用AUTO_INCREMENT 尽量避免使用字符串类型作为标识符，因为它们很消耗空间，并且通常比数字类型慢。对于完全随机的字符串也需要多加注意，随机产生的值会任意分布在很大的空间内，这会导致insert以及一些select语句变的很慢

插入值会随机地写到索引的不同位置

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

随机值导致缓存对所有类型的查询语句效果都很差.

特殊类型数据

经常使用varchar(15)列来存储IP地址，然而，它们实际上是32位无符号整数，不是字符串，用小数点将地址分成四段的表示方法只是为了阅读。所以应该用无符号整数存储IP地址，mysql提供INET_ATON()、INET_NTOA()函数在这两种表示方法之间转换，示例如下。

`ip` int(10) unsigned DEFAULT '0';

​

UPDATE tb_test SET ip = INET_ATON('192.168.1.1');

​

SELECT INET_NTOA(ip) FROM tb_test;

schema设计中的陷阱

在mysql特定实现下，设计schema时需要避免的错误：

太多的列

mysql的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。InnoDB的行结构总是需要转换，转换的代价依赖于列的数量，如果计划使用数千个字段，必须意识到服务器的性能运行特征会有一些不同

太多的关联

mysql限制了每个关联操作最多只能有61张表。一个粗略的经验法则，如果希望查询执行的快速且并发性好，单个查询最好在12个表以内做关联

全能的枚举

注意防止过度使用ENUM

变相的枚举

非此发明的NULL

范式和反范式

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

范式的优缺点

因为性能问题而寻求帮助时，经常会被建议对schema进行范式化设计，尤其是写密集的场景：

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

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

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

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

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

反范式的优缺点

反范式化的schema因为所有数据都在一张表中，可以很好的避免关联。如果不需要关联表，则对大部分查询最差的情况——即使表没有使用索引——是全表扫描。当数据比内存大时这可能比关联要快的多，因为这样避免了随机I/O。

混用范式化和反范式化

事实是，完全的范式化和完全的反范式化schema都是实验室里才有的东西，在实际应用中经常需要混用，可能使用部分范式化的schema、缓存表，以及其他技巧。

最常见的反范式化数据的方法是复制或者缓存，在不同的表中存储相同的特定列。在mysql 5.0和更新版本中，可以使用触发器更新缓存值，这使得实现这样的方案变的更简单。

缓存表和汇总表

有时提升性能最好的方法是在同一张表中保存衍生的冗余数据。然而，有时也需要创建一张完全独立的汇总表或缓存表(特别是为满足检索的需求时)。如果能容许少量的脏数据，这是非常好的方法，但是有时确实没有选择的余地(例如，需要避免复杂、昂贵的实时更新操作)。

术语“缓存表”和“汇总表”没有标准的含义。我们用术语“缓存表”来表示存储那些可以比较简单地从schema 其他表获取(但是每次获取的速度比较慢)数据的表(例如，逻辑上冗余的数据)。而术语“汇总表”时，则保存的是使用GROUP BY语句聚合数据的表(例如，数据不是逻辑上冗余的)。也有人使用术语“累积表(Roll-Up Tables)”称呼这些表。因为这些数据被“累积”了。

仍然以网站为例，假设需要计算之前24 小时内发送的消息数。在一个很繁忙的网站不可能维护一个实时精确的计数器。作为替代方案，可以每小时生成一张汇总表。这样也许一条简单的查询就可以做到，并且比实时维护计数器要高效得多。缺点是计数器并不是100% 精确。

如果必须获得过去24 小时准确的消息发送数量(没有遗漏)，有另外一种选择。以每小时汇总表为基础，把前23 个完整的小时的统计表中的计数全部加起来，最后再加上开始阶段和结束阶段不完整的小时内的计数。

假设统计表叫作msg_per_hr 并且这样定义：

CREATE TABLE msg_per_hr (

hr DATETIME NOT NULL,

cnt INT UNSIGNED NOT NULL,

PRIMARY KEY(hr)

);

可以通过把下面的三个语句的结果加起来，得到过去24 小时发送消息的总数。我们使用LEFT(NOW(),14) 来获得当前的日期和时间最接近的小时：

mysql> SELECT SUM(cnt) FROM msg_per_hr

-> WHERE hr BETWEEN

-> CONCAT(LEFT(NOW(), 14), '00:00') - INTERVAL 23 HOUR

-> AND CONCAT(LEFT(NOW(), 14), '00:00') - INTERVAL 1 HOUR;

mysql> SELECT COUNT() FROM message

-> WHERE posted >= NOW() - INTERVAL 24 HOUR

-> AND posted < CONCAT(LEFT(NOW(), 14), '00:00') - INTERVAL 23 HOUR;

mysql> SELECT COUNT() FROM message

-> WHERE posted >= CONCAT(LEFT(NOW(), 14), '00:00');

不管是哪种方法——不严格的计数或通过小范围查询填满间隙的严格计数——都比计算message 表的所有行要有效得多。这是建立汇总表的最关键原因。实时计算统计值是很昂贵的操作，因为要么需要扫描表中的大部分数据，要么查询语句只能在某些特定的索引上才能有效运行，而这类特定索引一般会对UPDATE 操作有影响，所以一般不希望创建这样的索引。计算最活跃的用户或者最常见的“标签”是这种操作的典型例子。缓存表则相反，其对优化搜索和检索查询语句很有效。这些查询语句经常需要特殊的表和索引结构，跟普通OLTP 操作用的表有些区别。

例如，可能会需要很多不同的索引组合来加速各种类型的查询。这些矛盾的需求有时需要创建一张只包含主表中部分列的缓存表。一个有用的技巧是对缓存表使用不同的存储引擎。例如，如果主表使用InnoDB，用MyISAM 作为缓存表的引擎将会得到更小的索引占用空间，并且可以做全文搜索。有时甚至想把整个表导出MySQL，插入到专门的搜索系统中获得更高的搜索效率，例如Lucene 或者Sphinx 搜索引擎。

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

当重建汇总表和缓存表时，通常需要保证数据在操作时依然可用。这就需要通过使用“影子表”来实现， “ 影子表”指的是一张在真实表“背后”创建的表。当完成了建表操作后，可以通过一个原子的重命名操作切换影子表和原表。例如，如果需要重建 my_summary，则可以先创建 my_summary_new，然后填充好数据，最后和真实表做切换：

mysql> DROP TABLE IF EXISTS my_summary_new, my_summary_old;

mysql> CREATE TABLE my_summary_new LIKE my_summary;

-- populate my_summary_new as desired

mysql> RENAME TABLE my_summary TO my_summary_old, my_summary_new TO my_summary;

物化视图

许多数据库管理系统(例如Oracle 或者微软SQL Server)都提供了一个被称作物化视图的功能。物化视图实际上是预先计算并且存储在磁盘上的表，可以通过各种各样的策略刷新和更新。MySQL 并不原生支持物化视图。

Flexviews 比完全自己实现的解决方案要更精细，并且提供了很多不错的功能使得可以更简单地创建和维护物化视图。它由下面这些部分组成：

变更数据抓取(Change Data Capture，y CDC)功能，可以读取服务器的二进制日志并且解析相关行的变更。

一系列可以帮助创建和管理视图的定义的存储过程。

一些可以应用变更到数据库中的物化视图的工具。

对比传统的维护汇总表和缓存表的方法，Flexviews 通过提取对源表的更改，可以增量地重新计算物化视图的内容。这意味着不需要通过查询原始数据来更新视图。

先写出一个SELECT 语句描述想从已经存在的数据库中得到的数据。这可能包含关联和聚合(GROUP BY)。Flexviews 中有一个辅助工具可以转换SQL 语句到Flexviews 的API 调用。Flexviews 会做完所有的脏活、累活：监控数据库的变更并且转换后用于更新存储物化视图的表。现在应用可以简单地查询物化视图来替代查询需要检索的表。

计数器表

应用在表中保存计数器，则在更新计数器时可能碰到并发问题。创建一张独立的表存储计数器通常是个好主意，这样可使计数器表小且快。使用独立的表可以帮助避免查询缓存失效。

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

CREATE TABLE hit_counter(

cnt int unsigned not null

) ENGINE=InnoDB;

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

UPDATE hit_counter set cnt = cnt + 1;

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

CREATE TABLE hit_counter(

slot tinyint unsigned not null primary key,

cnt int unsigned not null

) ENGINE=InnoDB;

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

UPDATE hit_counter set cnt = cnt + 1 where slot = RAND() * 100;

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

SELECT SUM(cnt) FROM hit_counter;

一个常见的需求是每隔一段时间开始一个新的计数器。如果需要这么做，则可以简单的修改一下表设计：

CREATE TABLE daily_hit_counter(

day date not null,

slot tinyint unsigned not null,

cnt int unsigned not null,

primary key(day,slot)

) ENGINE=InnoDB;

如果希望减少表的行数，以避免表变得太大，可以写一个周期执行的任务，合并所有结果到0 号槽，并且删除所有其他的槽：

mysql> UPDATE daily_hit_counter as c

-> INNER JOIN (

-> SELECT day, SUM(cnt) AS cnt, MIN(slot) AS mslot

-> FROM daily_hit_counter

-> GROUP BY day

-> ) AS x USING(day)

-> SET c.cnt = IF(c.slot = x.mslot, x.cnt, 0),

-> c.slot = IF(c.slot = x.mslot, 0, c.slot);

mysql> DELETE FROM daily_hit_counter WHERE slot <> 0 AND cnt = 0;

总结：

尽量避免过度设计

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

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

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

尽量使用整型定义标识列。

避免使用MySQL已经遗弃的特性。

小心使用ENUM和SET。