Mysql优化（出自官方文档） - 第九篇（优化数据库结构篇）

Mysql优化（出自官方文档） - 第九篇（优化数据库结构篇）

目录

• Mysql优化（出自官方文档） - 第九篇（优化数据库结构篇）
  • 1 Optimizing Data Size
    • Table Columns
    • Row Format
    • Indexes
    • Joins
    • Normalization
  • 2 Optimizing MySQL Data Types
    • Optimizing for Numeric Data
    • Optimizing for Character and String Types
    • Optimizing for BLOB Types
  • 3 Optimizing for Many Tables
    • How MySQL Opens and Closes Tables
    • Disadvantages of Creating Many Tables in the Same Database
  • 4 Internal Temporary Table Use in MySQL

1 Optimizing Data Size

通常来讲，定义表的时候，一个合理的数据类型，往往意味着更少的存储空间，更少的磁盘I/O，索引扫描也会更快，接下来将从五个方面来介绍如何进行这种优化：

• Table Columns

  • 尽量选择更合适的数据类型，比如说，对于integer类型来讲，选择MEDIUMINT比INT能节约25%的存储空间。
  • 如果某列不为NULL，那么尽可能的将其定义为NOT NULL，这样子，不仅能节约1bit的存储空间，更重要的是，Mysql的处理速度能够得到大大的提高，因为这样子Mysql就不需要处理NULL的情况，且索引的处理也能得到加速。

• Row Format

  • InnoDB默认使用DYNAMIC的行格式，如果要使用其他的格式，可以通过配置innodb_default_row_format的方式来指定，或者在创建表/更改表的时候显示指定行格式。

    为了使用紧凑型的行格式，可以指定 COMPACT, DYNAMIC, and COMPRESSED，这三种格式一方面可以减少存储空间，另外一方面，也可以大大提高cache命中率，减少磁盘I/O。

  • 使用ROW_FORMAT=COMPRESSED格式，InnoDB压缩表支持读和写，而MyISAM则只支持读

  • 对于MyISAM表，如果没有指定变长的列（如VARCHAR, TEXT或者BLOB），那么将会默认使用固定长度的类型，对于MyISAM来讲，固定长度的列速度更快，当然代价是会浪费一部分空间。

• Indexes

  • primary key应该尽可能的短，这样子查找的时候效率更高，在InnoDB中，由于每一个primary key在二级索引中都会被复制一份，当拥有很多的二级索引时，这就显得很有必要。
  • 只有需要的时候再去创建索引，索引虽然可以加快访问速度，但是同时也会增加insert和update的开销；如果访问一个表的时候，经常访问多列的组合，那么在这个组合上创建索引，并且尽量保证访问频次最高的列在最前面，千万不要分别在这些列上创建索引。
  • 通常来讲，有很大的可能一个string类型的列有着不同的前缀，这个时候建议只在这些前缀上创建索引，这样子不仅可以增大索引的cache命中率，还可以减少磁盘I/O

• Joins

  • 在某些场景下，将一个经常被扫描的表分成两个是有较大好处的，特别是当这个表是一个dynamic-format表且可以使用一个更小的static format表来索引它的时候。
  • 将需要join的两个表的列设置为类似的数据类型，可以加快join的速度。
  • 使用简单的名字，这样子当查询多个表的时候可以使用一样的名字，并且能够简化join语句，比如：在一个customer表中，使用name，而不要使用customer_name。

• Normalization

  • 通常来讲，尽量保证所有的数据是非冗余的（参考第三范式的定义），避免重复的使用冗长的值（比如names或者addresses之类的），而是选择为他们赋一个唯一的ID，这样子在多个小表中就可以重复使用这些ID，在join的时候尽量使用ID来进行join，这样子就可以加快访问速度。
  • 如果速度的优先级非常高，比磁盘空间和维护多个重复数据的优先级还高，这个时候，就不需要严格遵循这些规范，可以适当的冗余一些信息或者创建一个类似于summary的表来加快访问速度。

2 Optimizing MySQL Data Types

• Optimizing for Numeric Data

  • 对于唯一的ID或者其他可以用string和数字表达的值，尽量使用numeric，因为相对于string类型，numeric占用的字节数更小，并且在处理的时候也会占用更小的内存空间。
  • 如果使用numeric数据，访问的数据库的速度要远比访问文本文件的速度快，因为在数据库中存储的空间更加的紧凑。

• Optimizing for Character and String Types

  • 使用BINARY类型的callation，速度更快
  • 当比较不同列时，将这些列声明为同种字符集和collation，可以避免类型之间的转换。
  • 当列小于8kb的时候，使用二进制形式的VARCHAR来取代BLOB类型，因为GROUP BY和ORDER BY有可能会创建临时表，对于Mysql来讲，如果没有BLOB字段，那么有可能会直接使用MEMORY存储引擎来创建临时表。
  • 如果一个表包含诸如name或者address这样的列，并且这些列的访问频次非常低，那么，请将这些列分割到小表中，并使用join来查询，使用外键来同步更新，因为当Mysql获取某一行的时候，会读取一个data block，这么做可以减少Mysql读取block的大小，从而减少磁盘I/O和内存占用。
  • 在InnoDB中，如果使用随机值作为primary key，那么最好用当前的日期或者时间作为该列的前缀，对于连续的primary key，Mysql在物理上存储的方式也是连续的，这样子可以加快insert和select的速度。

• Optimizing for BLOB Types

  • 当存储一个非常大的BLOB列，且该列包含文本数据时，考虑压缩它，但是如果表已经被设定为压缩了，那么不要这样做。
  • 类似于string类型的优化方式，如果BLOB的访问频次不高，为了减少内存占用，考虑将BLOB分离为一个小表。
  • 鉴于获取BLOB类型列的性能需求非常不同于其他数据类型，考虑将只有BLOB类型的表放在不同的存储设备或者其他的数据库实例中，比如：因为BLOB类型涉及到大量的顺序读写，那么将BLOB存储在传统机械磁盘上速度将可能优于SSD设备。
  • 当需要测试一段非常长的文本是否相同时，考虑使用一个专门的列来存储这段文本的HASH值（使用Mysql的MD5()和CRC32()函数来实现），并且对其创建索引，因为哈希函数可能会产生相同的结果，所以，在查询的时候，除了检测HASH列外，还需要加上AND blob_column = long_string_value来确保出现这种情况时的正确性，这种优化方式可以大大加快BLOB类型列的处理速度。

3 Optimizing for Many Tables

我们已经知道为了加快查询速度，将数据分割为多个表是一种非常方便的优化方式，但是，当表的数量增大到上千个甚至上万个时，优化方式将会有些不一样。

• How MySQL Opens and Closes Tables

  使用mysqladmin status命令，可以看到如下结果：

  Uptime: 426 Running threads: 1 Questions: 11082
  Reloads: 1 Open tables: 12
  

  为了提高性能，Mysql针对每一个session都会open一个table，这样子，如果有多个客户端连接到Mysql，那么这里的open tables可能比实际的表要多。（注意，MyISAM是不一样的，所有的session共享文件描述符）

  出现下面的情况，Mysql将会关闭一个没有用的table，并且将其从cache中移除：

  • cache满了，此时有一个线程尝试打开不在cache中的table
  • cache中包含超过table_open_cache 的table，此时cache中有没有被任何线程使用的table
  • 当进行table-flushing操作时，通常是通过FLUSH TABLES ， 或者 mysqladmin flush-tables or mysqladmin refresh 触发。

  如果一个table cache满了，但是此时有需要打开一个table，Mysql将会使用如下策略：

  • 那些没有被使用的table将会被释放，采用LRU原则
  • 如果一个table必须被打开，但是cache又满了，此时，cache会被临时扩充，在扩充状态下的cache，一旦某个table变成unused状态，那么会被直接移除。

  对于MyISAM表，该表没被访问一次，就会被打开一次，也就是说如果有两个线程访问一样的表或者说一张线程访问了该表两次，那么该表会被open两次；第一次打开表的时候需要两个文件描述符：第一个是数据file，另外一个是索引file，其中数据file每个线程使用一个，索引file所有线程共享。

• Disadvantages of Creating Many Tables in the Same Database

  很容易会导致table cache不够用，然后出现不断打开关闭的情况，降低效率。

4 Internal Temporary Table Use in MySQL

很多情况下，Mysql都会进行创建临时表（内存表或者会下盘的表），这些条件如下（因为原文太长，所以这里省略和合并部分条件）：

• 绝大多数union语句，只有部分特殊情况才不会进行此操作（下面会进行讨论）。
• 对于一些views的评估，如使用了TEMPTABLE算法，UNION或者聚合函数
• derived tables， common table expressions，带有子查询的创建表操作或者semijoin
• ORDER BY和GROUP BY的列不同，或者在join中，ORDER BY和GROUP BY的列包含除了第一个表以外其他表的列。
• ORDER BY并且带有DISTINCT可能会需要创建临时表
• 带有SQL_SMALL_RESULT 标识符的语句，会使用一个内存临时表，除非该语句包含必须创建on-disk临时表的部分。
• INSERT ... SELECT形式的语句。
• 多表UPDATE操作，GROUP_CONCAT或者COUNT(DISTINCT)语句，window functions

对于一些查询，Mysql无法使用in-memory临时表，这个时候就只能创建on-disk临时表，这些情况包括：

• 包含BLOB或者TEXT列的表，然而，对于Mysql8.0.13，TempTable这种in-memory存储引擎，已经支持了大的二进制类型。
• 在SELECT中，如果使用了UNION或者UNION ALL，并且string类型的列包含了超过512（对于二进制来讲单位是bytes，对于非二进制类型来讲是字符数），那么将无法使用in-memory存储引擎。
• 对于 SHOW COLUMNS 和DESCRIBE，且使用了BLOB类型的语句

某些情况下，UNION也不一定会使用临时表，这些情况包括：

• union类型是 UNION ALL, not UNION or UNION DISTINCT.
• 没有全局的ORDER BY语句
• 对于{INSERT | REPLACE} ... SELECT ...语句，union不在最外层。

在具体实现时，内部in-memory临时表存储在TempTable或者MEMORY存储引擎，on-disk临时表存储在InnoDB中：

在Mysql8.0.16之前，可以通过internal_tmp_disk_storage_engine=MYISAM参数来制定具体的存储引擎，但是其或者之后，该参数就已经被抛弃了，用户将无法配置使用哪种存储引擎。

NOTE:

当使用MEMORY存储引擎时，刚开始临时表会被创建在内存中，但是当临时表变得比较大了，Mysql会开始创建临时文件，并使用映射的方式映射到内存中。