《高性能MySQL第3版》读书笔记(二)

Published: 03 Feb 2019 Category: mysql

三、服务器性能剖析

1.性能,为完成某件任务所需要的时间度量,性能即响应时间,这是非常重要的原则
2.如果目标是降低响应时间,就需要理解为什么服务器执行查询需要这么多时间,然后去减少或者消除那些对获得查询结果来说不必要的工作。无法测量就无法有效地优化
3.性能剖析(profiling)是测量和分析时间花费在哪里的主要方法,一般有两个步骤:测量任务所花费的时间,对结果进行统计和排序

生成剖析报告:pt-query-digest

剖析单条查询:

  • SHOW PROFILES;
  • SHOW [GLOBAL] STATUS;,返回一些计数器

其他剖析工具:

  • 使用USER_STATISTICS表
  • 使用strace,可以调查系统调用的情况

四、Schema与数据类型优化

4.1 选择优化的数据类型

数据类型的选择原则:

  • 更小的通常更好
  • 简单就好:简单数据类型的操作通常需要更少的CPU周期。例如,整型比字符操作代价更低,因为字符集和校对规则(排序规则)使字符比较比整型比较更复杂。
  • 尽量避免NULL:如果查询中包含可为NULL的列,对MySQL来说更难优化,因为可为NULL的列使得索引、索引统计和值比较都更复杂。

4.2 选择具体类型

DECIMAL VS int: 应该尽量只在对小数进行精确计算时才使用DECIMAL,使用int类型通过程序控制单位效果更好。可以使用DECIMAL存储比BIGINT还大的整数。

TIMESTAMP VS DATETIME: 通常应该尽量使用TIMESTAMP,它比DATETIME空间效率更高;不过,TIMESTAMP允许的时间范围要小得多。

UNSIGNED属性:整数类型有可选的UNSIGNED属性,表示不允许负值,这大致可以使正数的上限提高一倍。有符号和无符号类型使用相同的存储空间,并具有相同的性能,因此可以根据实际情况选择合适的类型。

MySQL可以为整数类型指定宽度,例如INT(11),对大多数应用这是没有意义的:它不会限制值的合法范围,只是规定了MySQL的一些交互工具(例如MySQL命令行客户端)用来显示字符的个数。对于存储和计算来说,INT(1)和INT(20)是相同的。

4.3 VARCHAR VS CHAR

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

VARCHAR需要使用1或2个额外字节记录字符串的长度。InnoDB则更灵活,它可以把过长的VARCHAR存储为BLOB。

CHAR适合存储很短的字符串,或者所有值都接近同一个长度。例如,CHAR非常适合存储密码的MD5值,因为这是一个定长的值。对于经常变更的数据,CHAR也比VARCHAR更好,因为定长的CHAR类型不容易产生碎片。

对于非常短的列,CHAR比VARCHAR在存储空间上也更有效率。例如用CHAR(1)来存储只有Y和N的值,如果采用单字节字符集只需要一个字节,但是VARCHAR(1)却需要两个字节,因为还有一个记录长度的额外字节。

与CHAR和VARCHAR类似的类型还有BINARY和VARBINARY,它们存储的是二进制字符串。

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

4.4 BLOB和TEXT类型

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

实际上,它们分别属于两组不同的数据类型家族:字符类型是TINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT,LONGTEXT;对应的二进制类型是TINYBLOB,SMALLBLOB,BLOB,MEDIUMBLOB,LONGBLOB。BLOB是SMALLBLOB的同义词,TEXT是SMALLTEXT的同义词。

与其他类型不同,MySQL把每个BLOB和TEXT值当作一个独立的对象处理。例如用外部存储区存储,然后数据库中存储一个指针。

MySQL对BLOB和TEXT列进行排序与其他类型是不同的:它只对每个列的最前max_sort_length字节而不是整个字符串做排序。

4.5 使用枚举(ENUM)代替字符串类型

枚举列可以把一些不重复的字符串存储成一个预定义的集合。MySQL在存储枚举时非常紧凑,会根据列表值的数量压缩到一个或者两个字节中。MySQL在内部会将每个值在列表中的位置保存为整数,并且在表的.frm文件中保存“数字-字符串”映射关系的“查找表”。

select e + 0 from an_enum_table;
# 每行数据实际存储为整数,而不是字符串。

枚举最不好的地方是,字符串列表是固定的,添加或删除字符串必须使用ALTER TABLE。因此,对于一系列未来可能会改变的字符串,使用枚举不是一个好主意,除非能接受只在列表末尾添加元素。

4.6 加快ALTER TABLE操作的速度

两种方式:

  • 一是在一台不提供服务的机器上执行ALTER TABLE操作,然后和提供服务的主库进行切换。
  • 二是通过“影子拷贝”,创建一张新表,然后通过重命名和删表操作交换两张表及里面的数据。

五、创建高性能的索引

pass

六、查询性能优化

pass(可以一看)

七、MySQL高级特性

分区表、视图、外键约束、在Mysql内部存储代码、游标、绑定变量、自定义函数、插件、字符集和校对、全文索引、分布式事务、查询缓存。

八、优化服务器设置

pass

九、操作系统和硬件优化

MySQL需要的四种基本资源是:CPU、内存、硬盘以及网络资源。网络一般不会作为很严重的瓶颈出现,而CPU、内存和磁盘通常是主要的瓶颈所在。对MySQL而言,通常希望有很多快速CPU可以用,但如果必须在快和多之间做选择,则一般会选择更快而不是更多(其他条件相同的情况下)。

pass

REF

高性能MySQL【笔记】超详细