余瑜的博客
  • DB
  • mysql
余瑜
2019-04-01
目录

高性能mysql学习笔记

# 第一章 MySQL架构

# 1.1 MySQL逻辑架构

graph TB
E(客户端)---A
A(连接/线程处理)---B(查询缓存)
A---C(解析器)
C---B
C---D(优化器)
1
2
3
4
5
6
graph TB
E(存储引擎)
1
2
  • 最上层的服务并不是MySQL独有的,大多数基于网络的客户端/服务器工具或者服务都有类似的结构.比如连接处理.授权认证.安全等等
  • 第二层机构是MySQL比较有意思的部分.包括查询解析.分析.优化.缓存以及所有的内置函数(例如日期.数学.加密函数),所有跨存储引擎的功能都在这一层实现:存储过程.触发器.视图等.
  • 第三层包含了存储引擎.存储引擎负责MySQL中数据的存储和提取.服务器通过API与存储引擎进行通信.这些接口屏蔽了不同存储引擎之间的差异,使得这些差异对上层的查询过程透明.存储引擎API包含几十个底层函数,用于执行诸如"开始一个事务"或者"根据主键提取一行记录"等操作.但存储引擎不会去解析SQL,不同的存储引擎之间也不会相互通信,而只是简单的响应上层服务器的请求.

# 1.1.1 连接管理与安全性

每一个客户端连接都会在服务器进程中拥有一个线程,这个连接的查询只会在这个单独的线程中执行,该线程只能轮流在某个CPU核心或者CPU中运行.服务器会负责缓存线程,因此不需要为每个新建的连接创建或者销毁线程

# 1.1.2 优化与执行

  • MySQL会解析查询,并创建内部数据结构(解析树),然后对其进行各种优化,包括重写查询.决定表的读取顺序,以及选择合适的索引等.
  • 优化器并不关心表使用的是什么存储引擎,但存储引擎对于优化查询是有影响的.优化器会请求存储引擎提供容量或某个具体操作的开销信息,以及表数据的统计信息等.
    • 例如,某些存储引擎的某种索引,可能对一些特定的查询有优化.
  • 对于SELECT语句,在解析查询之前,服务器会先检查查询缓存(query cache),如果能够在其中找到对应的查询,服务器就不必在执行查询解析.优化和执行的整个过程,而是直接返回查询缓存中的结果集.

# 1.2 并发控制

无论何时,只要有多个查询需要在同一时刻修改数据,都会产生并发控制的问题.MySQL在两个层面的并发控制

  • 服务层并发控制
  • 存储引擎层并发控制

# 1.2.1 读写锁

在处理并发读或者写时,可以通过实现一个由两种类型的锁组成的锁系统来解决问题.这两种类型的锁通常被称为共享锁(shared lock)和排它锁(exclusive lock),也叫读锁(read lock)和写锁(write lock). 锁的概念:

  • 读锁是共享的,或者说是相互不阻塞的.多个客户在同一时刻可以同时读取同一个资源,而互不干扰.
  • 写锁则是排他的,也就是说一个写锁会阻塞其他的写锁和读锁,这是出于安全策略的考虑,只有这样,才能确保在给定的时间里,只有一个用户能执行写入,并防止其他用户读取正在写入的统一资源.

在实际的数据库系统中,没事没课都在发生锁定,当某个用户在修改某一部分数据是,MySQL会通过锁定防止其他用户读取统一数据.大多数时候,MySQL锁的内部管理都是透明的.

# 1.2.2 锁粒度

一种提高共享资源并发性的方式就是让锁定对象更有选择性.尽量只锁定需要修改的部分数据而不是所有资源.更理想的方式是,支队会修改的数据片进行精确的锁定.任何时候,在给定的资源上,锁定的数据量越少,则系统的并发成都越高,只要相互之间不发生冲突即可.

问题是枷锁也需要消耗资源

锁的各种操作,包括获得锁,检查锁是否已经解除.释放锁等,都会增加系统的开销.如果系统话费大量的时间来管理所,而不是存取数据,那么系统的性能可能会因此受影响.

  • 所谓的锁策略,就是在锁的开销和数据的安全性之间寻求平衡,这种平衡当然也会影响到性能.大多数商业数据库系统并没有提供更多的选择,一般都是在表上施加行级锁(row-level lock), 并以各种复杂的方式来实现,一遍在锁比较多的情况下尽可能的提供更好的性能.
  • MySQL提供了多种选择.每种MySQL存储引擎都可以实现自己的锁策略和锁粒度.
    • 将锁粒度固定在某个级别,可以为某些特定的应用场景提供更好的性能,但同时却会失去对另外一些应用场景的良好支持. 表锁(table lock)

表锁是MySQL中最基本的锁策略,并且是开销最小的策略.

  • 表锁非常类似于有奖枷锁机制:他会锁定整张表.一个用户在对表进行写操作(插入.删除.更新等)钱,取消先获得写锁,这会阻塞其他用户对该表的所有读写操作.只有没有写锁时,其他读取的用户才能获得读锁,读锁之间是不相互阻塞的.
  • 在特定的场景中,表锁也可能有良好的性能.例如: READ LOCAL表锁支持某些类型的并发写操作.另外,写锁也比读锁有更高的优先级,因此一个写锁请求可能会被插入到读锁列队的前面(写锁可以插入到锁雷对中读锁的前面,繁殖读锁则不能插入到写锁的前面).
  • 尽管存储引擎可以管理自己的锁,MySQL本身还是会使用各种有效的表锁来实现不同的目的.例如: 服务器会为诸如ALTER TABLE之类的语句使用表锁,而忽略存储引擎的锁机制.

行级锁(row lock)

行级锁可以最大程度的支持并发处理(同时也带来了最大的锁开销).

  • MySQL服务器层没有实现行级锁,服务器层完全不了解存储引擎的锁实现.

# 1.3 事务

事务内的语句,要么全部执行成功,要么全部执行失败.

  • 原子性(atomicity)
    • 一个事务必须被视为一个不可分割的最小工作单元,整个事务中的所有操作要么全部提交成功,要么全部失败回滚,对于一个事务来说,不可能只执行其中的一部分操作,这就是事务的原子性.
  • 一致性(consistency)
    • 数据库总是从一个一致性的状态切换到另外一个一致性的状态.在前面的例子中,一致性确保了,即使在执行第三四条语句之间时系统崩溃,支票账户中也不会损失200$,因为事务最终没有提交,所以事务中所做的修改也不会保存到数据库中.
  • 隔离性(isolation)
    • 通常来说,一个事务所做的修改在最终提交之前,对其他事务是不可见的.在前面的例子中,当执行完第三条语句.第四条语句还未开始时,此时有另外一个账户汇总程序开始运行,则其看到的支票账户的月并没有被减去200$.
  • 持久性(durability)
    • 一旦事务提交,则其所做的修改就会永久保存到数据库中.此时即使系统崩溃,修改的数据也不会丢失.持久性是个有点模糊的概念,因为实际上持久性也分很多不同的级别.有些持久性策略能够提供非常强的安全保障,而有些则未必.而且不可能有能做到100%的持久性保证的策略.

# 1.3.1 隔离级别

隔离性其实比想象中的要复杂.在SQL标准中定义了四中隔离级别,每一种级别都规定了一个事务中所做的修改,哪些在事务内和事务间是可见的,哪些是不可见的.较低级别的隔离通常可以执行更高的并发,系统的开销也更低.

  • READ UNCOMMITTED(未提交读)
    • 在READ UNCOMMITTED级别,事务中的修改,即使没有提交,对其他 事务也都是可见的.事务可以读取未提交的数据,这也被称为脏读(Rirty Read).这个级别会导致很多的问题,从性能上来说,READ UNCOMMITTED不会比其他的级别好太多,但却缺乏其他级别的很多好处,除非真的有非常必要的理由,在实际应用中一般很少使用.
  • READ COMMITTED(提交读)
    • 大多数数据库系统的more隔离级别都是READ COMMITTED(但MySQL不是).READ COMMITTED满足前面提到的隔离性的简单定义: 一个事务开始时,只能"看见"已经提交的事务所做的修改.换句话说,一个事务从开始直到提交之前,所做的任何修改对其他事务都是不可见的.这个级别有时候也叫做不可重复读(nonrepeatable read),因为两次执行同样的查询,可能会得到不一样的结果.
  • REPEATABLE READ(可重复读)
    • REPEATABLE READ解决了脏读的问题.该级别保证了在同一个事务中多次读取同样记录的结果是一致的.但是理论上,可重复读隔离级别还是无法解决另外一个幻读(Phantom Read)的问题.所谓幻读,指的是当某个事务在读取某个范围内的记录时,另外一个事务又在该范围内插入了新的记录,当之前的事务再次读取该范围的记录时,会产生幻行(Phantom Row). 该级别为MySQL的默认事务隔离级别.
  • SERIALIZABLE(可串行化)

    • SERIALIZABLE是最高的隔离级别.它通过强制事务串行执行,避免了前面说的幻读的问题.简单来说,SERIALIZABLE会在读取的每一行数据上都加锁,所以可能导致大量的超时和锁争用的问题.实际应用中也很少用到这个隔离级别,只有在非常需要确保数据的一致性并且可以次接受没有并发的情况下,才考虑采用该级别.

      隔离级别 | 脏读 | 不可重复读 | 幻读 | 加锁读
---|---|---|---|---
READ UNCOMMITTED | Yes | Yes | Yes | No
READ COMMITTED | No | Yes | Yes | No
REPEATABLE READ | No | No | Yes | No
SERIALIZABLE | No | No | No | Yes

# 1.3.2 死锁

死锁是指两个或者多个事务在同一资源上相互占用,并请求锁定对方占用的资源,从而导致恶性循环的现象.当多个事务试图以不同的顺序

为了解决死锁问题,数据库系统实现了各种死锁检测和死锁超时时机制.

  • 越复杂的系统,比如InnoDB存储引擎,越能检测到死锁的循环依赖,并立即返回一个错误.这种解决方式很有效,否则死锁会导致出现非常慢的查询.
  • 还有一种解决方式,就是当查询的时间达到锁等待超时的设定后放弃锁请求,这种方式通常来说不太好.InnoDB目前处理死锁的方法是: 将持有最少行级排他锁的事务进行回滚(这是相对比较简单的死锁回滚算法)

锁的行为和顺序是和储存引擎相关的.以同样的语句执行语句,有些储存引擎会产生死锁,有些则不会.死锁的产生有双重原因:

  • 有些是因为真正的数据冲突,这种情况通常很难避免
  • 有些则完全是由于存储引擎的实现方式导致的

死锁发生以后,只有部分或者全部回滚其中一个事务,才能打破死锁.对于事务型的系统,这是无法避免的,所以应用程序在设计时必须考虑如何处理死锁.大多数情况下只需要重新执行死锁所回滚的事务即可.

# 1.3.3 事务日志

  1. 事务日志可以帮助提高事务的效率.使用事务日志,存储引擎在修改表的数据时只需要修改器内存拷贝,
  2. 再把改修改行为记录到持久在硬盘上的事务日志中,而不用每次都将修改的数据本身持久到磁盘.
  3. 事务日志采用的是追加的方式,因此写日志的操作是磁盘上一小块区域内的顺序I/O,而不像随机I/O需要在磁盘的多个地方移动磁头,所以采用事务日志的方式相对来说要快得多.
  4. 事务日志持久以后,内存中被修改的数据在后台可以慢慢的刷回到磁盘.

++目前大多数储存引擎都是这样实现的,我们通常称之为预写式日志(Write-Ahead Logging),修改数据需要些两次磁盘.++

如果数据的修改已经记录到事务日志并持久化,但数据本身还没有写回磁盘,此时系统崩溃,存储引擎在重启时能够自动回复这一部分修改的数据.具体的回复方式则视存储引擎而定.

# 1.3.4 MySQL中的事务

MySQL提供了两种事务型的存储引擎: InnoDB和NDB Cluster. 另外还有一些第三方储存引擎也支持事务,比较知名的包括XtraDB和PBXT.

  • 自动提交(AUTOCOMMIT)
    • MySQL默认采用自动提交模式.也就是说,如果不是显式的开始一个事务,则每个查询都被当做一个事务执行提交操作.在当前连接中,可以通过设置AUTOCOMMIT变量来启用或者禁用自动提交模式:
mysql> SHOW VARIABLES LIKE 'AUTOCOMMIT';
mysql> SET AUTOCOMMIT = 1;
1
2
      • 1或者ON表示启用,0或者OFF表示禁用.当AUTOCOMMIT=0时,所有的查询都是在一个事务中,知道显式的执行COMMIT提交或者ROLLBACK回滚,该事务结束,同时开始了另一个新事务.
      • 另外还有一些命令,在执行之前会强制执行COMMIT提交当前的活动事务.典型的例子,在数据定义语言(DDL)中,如果是会到时大量数据改变的操作,比如ALTER TABLE,就是如此.此外还有LOCK TABLES等其他语句也会导致同样的结果.
    • MySQL可以通过执行SET TRANSACTION ISOLATION LEVEL命令来设置隔离级别.新的隔离级别会在下一个事务开始的时候生效.可以再配置文件中设置证而过数据库的隔离级别,也可以只改变当前会话的隔离级别:
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
1
      • MySQL能够识别所有的四个ANSI隔离级别,InnoDB引擎也支持所有的隔离级别.

  • 在事务中混合使用存储引擎

    • MySQL服务器层不管理事务,事务是由下层的存储引擎实现的.所以在同一个事务中,使用多种存储引擎是不可靠的.
    • 如果在事务中混合使用了事务型和非事务型的表(例如InnoDB和MyISAM表),在正常提交的情况下不会有什么问题.
    • 但如果该事务需要回滚,非事务型的表上的变更就无法撤销.这会导致数据库处于不一致的状态,这种情况很难修复,事务的最终结果将无法确定.所以,为每张表选择合适的存储引擎非常重要.
    • 在非事务型的表上执行事务相关操作的时候,MySQL通常不会发出提醒,也不会报错.有时候只有回滚的时候才会发出一个警告:"某些非事务型的表上的变更不能被回滚".但大多数情况下,对非事务型的表的操作都不会有提示.

  • 隐式和显式锁定

    • InnoDB采用的是两阶段锁定协议(two-phase locking protocol).在事务执行过程中,随时都可以执行锁定,锁只有在执行COMMIT或者ROLLBACK的时候才会释放,并且所有的锁是在同一时刻被释放.前面描述的锁定都是隐式锁定,InnoDB会根据隔离级别在需要的时候自动加锁.
      • InnoDB也支持通过特定的语句进行显式锁定,这些语句不属于SQL规范:
SELECT ... LOCK IN SHARE MODE
SELECT ... FOR UPDATE
1
2
    • MySQL也支持LOCK TBALES和UNLOCK TABLES语句,这是在服务器层实现的,和存储引擎无关

  • LOCK TABLES和事务之间相互影响的话,情况会变得非常复杂,在某些MySQL版本中甚至会产生无法预料的结果.

  • 除了事务中禁用了AUTOCOMMIT,何以实用LOCK TABLES之外,其他任何时候都不要显式地执行LOCK TABLES, 不关使用的是什么存储引擎.

# 1.4 多版本并发控制

MySQL的大多数事务型存储引擎实现的都不是简单的行级锁.基于提升并发性能的考虑,他们一般都同时实现了多版本并发(MVCC).可以认为MVCC是行级锁的一个变种,但是它在很多情况下避免了加锁操作,因此开销更低.

MVCC的实现,是通过保存数据在某个时间点的快照来实现的.也就是说,不管需要执行多长时间,每个事物看到的数据都是一致的.

**InnoDB的MVCC,是通过在每行记录后面保存两个隐藏的列来实现的.这两个列,一个保存了行的创建时间,一个保存行的过期时间(或删除时间).当然存储的并不是实际的时间值,而是系统版本号(system version numer).每开始一个新的事物,系统版本号都会自动递增.事物开始时刻的系统版本号会作为事物的版本号,用来和查询到的每行记录的版本号进行比较.下面看一下在REPEATBALE READ隔离级别下,MVCC具体是如何操作的.

  • SELECT

    • InnoDB会根据以下两个条件检查每行记录:
      • InnoDB只查找版本早于当前事务版本的数据行(也就是,行的系统版本号小鱼或等于事务的系统版本号),这样可以确保事务读取的行,要么是在事务开始前已经存在的,要么是事务自身插入或者修改过的.
      • 行的删除版本要么未定义,要么大于当前事物版本号.这可以确保事务读取到的行,在事务开始之前未被删除.
    • 只有符合上述两个条件,才能返回作为查询结果.

  • INSERT

    • InnoDB为新插入的每一行保存当前系统版本号作为行版本号.

  • DELETE

    • InnoDB为删除的每一行保存当前系统版本号作为行删除标示.

  • UPDATE

    • InnoDB为插入一行新的记录,保存当前系统版本号作为行版本号,同时保存当前系统版本号到原来的行作为行删除标识.

  • 保存这连个额外系统版本号,使大多数操作都可以不用加锁.这样设计使得读数据操作很简单,性能很好,并且也能保证智慧读取到符合标准的行.不足之处是每行记录都需要额外的储存空间,需要做更多的行检查工作,以及额外的维护工作.

  • MVCC只在REPEATABLE READ和READ COMMITED两个隔离级别下工作.其他两个隔离级别都和MVCC不兼容,因为READ UNCOMMITED总是读取最新的数据行,而不是符合当前事物版本的数据行.而SERIALIZABLE则会对所有读取的行都加锁.

# 1.5 MySQL的存储引擎

在文件系统中,MySQL将每个数据库保存为数据目录下的一个子目录.创建表时,MySQL会在数据库子目录下创建一个和表同名的.frm文件保存表的定义.例如创建一个名为mytble的表,MySQL会在mytable.frm文件中保存该表的定义.因为MySQL使用文件系统的沐浴露和文件来保存数据库和表的定义,大小写敏感性和具体的平台密切相关.在Windows中,大小写是不敏感的;而在类Unix中则是敏感的.不同的存储引擎保存数据和索引的方式是不同的,但表的定义则是在MySQL服务层统一处理的.

可以使用SHOW TABLE STATUS LIKE 't_order'命令来显示表的相关信息

  • Name
    • 表明
  • Engine
    • 表的存储引擎类型.在旧版本中,该列的名字叫Type,而不是Engine.
  • Row_format
    • 行的格式.对于MyISAM表,可选的值为Dynamic.Fixed或者Compressed.Dynamic的行长度是可变的,一般包含可变长度的字段,入VARCHAR或BLOB.Fixed的行长度则是固定的,只包含固定长度的列,入CHAR和INTEGER.Compressed的行则只在压缩表中存在.
  • Rows
    • 表的行数. 对于MyISAM和其他一些存储引擎,该值是精确的,但对于InnoDB,该值是估计值.
  • Avg_row_length
    • 平均每行包含的字节数;
  • Data_length
    • 表数据的大小(以字节为单位)
  • Max_data_length
    • 表数据的最大容量,该值和存储引擎有关.
  • Index_length
    • 索引的大小(以字节为单位)
  • Data_free
    • 对于MyISAM表,表示已分配但目前没有使用的空间.这部分空间包括了之前删除的行,以及后续可以被INSERT利用到的空间.
  • Auto_increment
    • 下一个AUTO_INCREMENT的值.
  • Create_time
    • 表的创建时间.
  • Update_time
    • 表数据的最后修改时间.
  • Check_time
    • 使用CKECK TABLE命令或者myisamchk工具最后一次检查表的时间
  • Collation
    • 表的默认字符集和字符列排序规则.
  • Checksum
    • 如果启用,保存的是整个表的实时校验和.
  • Create_options
    • 创建表时指定的其他选项.
  • Comment
    • 该列包含了一些其他的额外信息.保存的是表在创建时带的注释.

# 1.5.1 InnoDB存储引擎

InnoDB是MySQL的默认事务型引擎,也是最重要.使用最广泛的存储引擎.它被设计用来处理大量的短期(short-lived)事务,短期事务大部分情况是正常提交的,很少被回滚.InoDB的性能和自动崩溃回复特性,是的它在非事务型存储的需求中也很流行.除非有非常特别的原因需要使用其他的存储引擎,否则应该优先考虑InnoDB引擎.

InnoDB的概览

  • InnoDB的数据存储在表空间(tablespace)中,表空间是由InnoDB管理的一个黑盒子,由一系列的数据文件组成.在MySQL4.1以后的版本中,InnoDB可以将每个表的数据和索引存放在单独的文件中.InnoDB也可以使用裸设备作为表空间的存储兼职,但现代的文件系统使得裸设备不再是必要的选择.
  • InnoDB采用MVCC来支持高并发,并且实现了四个标准的隔离级别.其默认级别是REPEATABLE READ(可重复读),并且通过间隙锁(next-key locking)策略防止幻读的出现.间隙锁使得InnoDB不仅仅锁定查询涉及的行,还会对索引中的间隙进行锁定,以防止幻影行的插入.
  • InnoDB表时基于聚簇索引建立的.InnoDB的索引结构和MySQL的其他存储引擎有很大的不同,聚簇索引对主键查询有很高的性能.不过它的二级索引(secondary index,非主键索引)中必须包含主键列,所以如果主键列很大的话,其他的所有索引都会很大.因此,若表上的索引较多的话,主键应当尽可能的小.InnoDB的存储格式是平台独立的,也就是说可以将数据和索引文件从Intel平台复制到PowerPC或者Sun SPARC平台.
  • InnoDB内部做了很多的优化,包括从磁盘读取数据时采用的可预测性预读,能够自动在内存中创建hash索引以加速读操作的自适应哈希索引(adaptive hash index),以及能够加速插入操作的插入缓冲区(insert buffer)等.
  • InnoDB的行为是非常复杂的,不容易理解.阅读: 官方手册中的"InnoDB事务模型和锁".
  • 作为事务型的存储引擎,InnoDB通过一些机制和工具支持真正的热备份,Oracle提供的MySQL Enterprise Backup. Percona 提供的开源的XtraBackup都可以做到这一点.MySQL的其他存储引擎不支持热备份,要获取一致性识图需要停止对所有表的写入,而在读写混合场景中,停止写入可能也意味着停止读取.

# 1.5.2 MyISAM存储引擎

在MySQL 5.1及之前的版本,MyISAM是默认的存储引擎.MyISAM提供了大量的特性,包括全文索引.压缩.空间函数(GIS)等,但MyISAM不支持事务和行级锁,而且有一个毫无疑问的缺陷就是崩溃后无法安全修复.正式由于MyISAM引擎的缘故,即使MySQL支持事务已经很长时间了,在很多人的概念里MySQL还是非事务型的数据库.尽管MyISAM引擎不支持事务.不支持崩溃后的安全修复,但它绝不是一无是处的. 对于只读的数据,或者表比较小.可以忍受修复(repair)操作,则依然可以继续使用MyISAM(单请不要默认使用MyISAM,而是应当默认使用InnoDB).

  • 存储
    • MyISAM会在表存储在两个文件中: 数据文件和索引文件,分贝仪.MYD和.MYI为扩展名.MyISAM表可以包含动态或者静态(长度固定)行.MySQL会根据表的定义来决定采用何种格式.MyISAM表可以存储的行记录数,一般受限于可用的磁盘空间,或者操作系统中耽搁文件的最大尺寸.
  • MyISAM特性
    • 加锁与并发

MyISAM对整张表加锁,而不是针对行.读取时会对需要读到的所有表加共享锁,写入时则对表加排它锁.但是在表有读取查询的时候,也可以往表中插入新的记录(这被称为并发插入,CONCURRENT INSERT).

    • 修复

对于MyISAM表,MySQL可以手工或者自动执行检查和修复操作,但这里说的修复和事务恢复以及崩溃恢复是不同的概念.执行表的修复可能导致一些数据丢失,而且修复操作是非常慢的. 可以通过 CHECK TABLE mytable 检查表的错误,如果有错误可以通过执行 REPAIR TABLE mytable 进行修复. 另外,如果MySQL服务器已经关闭,也可以通过myisamchk命令行工具进行检查和修复操作.

    • 索引特性

对于MyISAM表,即使是BLOB和TEXT等长字段,也可以基于其前500个字符创建索引.MyISAM也支持全文索引,这是一种基于分词创建的索引,可以支持复杂的查询.

    • 延迟更新索引键(Delayed Key Write)

创建MyISAM表的时候,如果指定了DELAY_KEY_WRITE选项,在每次修改执行完成时,不会立刻将修改的索引数据写入磁盘,而是会写到内存中的键缓冲区(in-memory key buffer),只有在清理键缓冲区或者关闭表的时候才会将对应的索引块吸入到磁盘.这种方式可以极大地提升写入性能,但是在数据库或者主机崩溃时会造成索引损坏,需要执行修复操作.延迟更新索引键的特性,可以在全局设置,也可以为耽搁表设置.

  • MyISAM压缩表
    • 如果表在创建并导入数据以后,不会再进行修改操作,那么这样的表或许适合采用MyISAM压缩表.
    • 可以使用myisampack对MyISAM表进行压缩(也叫打包pack).压缩表是不能进行修改的(除非现将表接触压缩,修改数据,然后再次压缩). 压缩表可以极大地减少磁盘空间占用,因此也可以减少磁盘I/O,从而提升查询性能. 压缩表也支持索引,但索引也是只读的.
    • 以现在的硬件能力,对大多数应用场景,读取压缩表数据时的解压带来的开销影响并不大,而减少I/O带来的好处则要大得多.压缩时表中的记录时独立压缩的,所以读取单行的时候不需要去解压整个表(甚至也不解压行所在的整个页面).
  • MyISAM性能
    • MyISAM引擎设计简单,数据以紧密格式存储,所以在某些场景下性能很好.MyISAM有一些服务器级别的性能扩展限制,比如对索引键缓冲区(key cache)的Mutex锁,MariaDB基于段(segment)的索引键缓冲区机制来避免该问题.但MyISAM最典型的性能问题还是表锁的问题,如果你发现所有的查询都长期处于"Locked"状态,那么毫无疑问表锁就是罪魁祸首.

# 1.5.3 MySQL内建的其他存储引擎

# 1.5.4 第三方存储引擎

OLTP类引擎

  • Persona的XtraDB存储引擎是基于InnoDB引擎的一个改进版本,已经包含在Percona Server和MariaDB中,它的改进点主要集中在性.可测量性和操作灵活性方面. XtraDB可以作为InnoDB的一个完全的替代产品甚至可以兼容地读写InnoDB的数据文件,并支持InnoDB的所有查询.
  • 还有一些和InnoDB非常类似的OLTP类存储引擎,比如都支持ACID事务和MVCC. 其中一个就是PBXT,由Paul McCullagh和 Primebbase GMBH开发. 它支持引擎级别的复制.外键约束,并且以一种比较复杂的架构对固态存储(SSD)提供了适当的支持,还对较大的值类型比如BLOB也做了优化.PBXT是一款社区支持的存储引擎,MariaDB包含了该引擎.
  • TokuDB引擎使用了一种新的叫做分形数(Fractal Trees)的索引数据结构. 该结构是缓存无关的,因此即使其大小超过内存性能也不会下降,也就没有内存生命周期和碎片的问题.TokuDB是一种大数据(Big Data)存储引擎,因为其拥有很高的压缩比,可以在很大的数据量上创建大量索引.

面向列的存储引擎

MySQL默认是面向行的,每一行的数据是一起存储的,服务器的查询也是以行为单位处理的. 而在大数据量处理时,面向列的方式可能效率更高. 如果不需要整行的数据,面向列的方式可以传输更少的数据. 如果每一列都单独存储,那么压缩的效率也会更高.

  • Infobright是最有名的面向列的存储引擎. 在非常大的数据量(数十TB)时,该引擎工作良好. Infobright是为数据分析和数据仓库应用设计的. 数据高度压缩,按照块进行排序,每个块都对应有一组元数据. 在处理查询时,访问元数据可以决定跳过该块,甚至可能只需要元数据即可满足查询的需求.但该引擎不支持索引,不过在这么大的数据量级,即使有索引也很难发挥作用,而且块结构也是一种准索引(quasi-index). Infobright需要对MySQL服务器做定制,因为一些地方需要修改以适应面向列存储的需要. 如果查询无法再存储层使用面向列的模式执行,则需要在服务器层转换成按处理,这个过程会很慢.Infobright有社区版和商业版两个版本.

社区存储引擎 慎用

# 1.5.5 选择合适的引擎

大部分情况下,InnoDB都是正确的选择,所以Oracle在MySQL5.5版本时终于将InnoDB作为默认的存储引擎.除非需要用到某些InnoDB不具备的特性,并且没有其他办法可以替代,否则都应该优先选择InnoDB引擎,除非万不得已,否则建议不要混合使用多种存储引擎,否则可能带来一些列复杂的问题,以及一些潜在的bug和边间问题.

如果应用需要不同的存储引擎,清先考虑一下几个因素

  • 事务

    • 如果应用需要事务支持,那么InnoDB(或者XtraDB)是目前最稳定并且经过验证的选择.如果不需要事务,并且主要是 SELECT 和 INSERT 操作,那么MyISAM是不错的选择.一般日志型的应用比较符合这一特性.

  • 备份

    • 备份的需求也会影响存储引擎的选择. 如果可以定期地关闭服务器来执行备份,那么备份的因素可以忽略. 反之,如果需要在线热备份,那么选择InnoDB就是基本的要求.

  • 崩溃回复

    • 数据量比较大的时候,系统崩溃后如何快速地恢复是一个需要考虑的问题. 相对而言,MyISAM崩溃后发生损坏的概率比InnoDB要高很多,而且恢复速度也要慢. 因此,即使不需要事务支持,很多人也选择InnoDB引擎,这是一个非常重要的因素.

  • 特有的特性

    • 有些应用可能依赖一些存储引擎所独有的特性或者优化,比如很多应用依赖聚簇索引的优化. 另外MySQL中也只有MyISAM支持地理空间搜索. 如果一个存储引擎拥有一些关键的特性,同时却又缺乏一些必要的特性,那么有时候不得不做这种的考虑,或者在架构设计上做一些取舍. 某些存储引擎无法直接支持的特性,有时候通过变通也可以满足需求.

  • 日志型应用

    • 假设你需要试试地记录一台中心电话交换机的每一通电话的日志到MySQL中,或者通过Apache的mod_log_sql模块将网站的所有访问信息直接记录到表中. 这一类应用的插入速度有很高的要求,数据库不能成为瓶颈.MyISAM或者archive存储阴影对这类应用比较合适,因为他们开销低,而且插入速度非常快.
    • 如果需要对记录的日志做分析报表,则事情就会变得有趣了.生成报表的SQL很有可能会导致插入效率明显降低,两种解决办法:
      • 利用MySQL内置的复制方案将数据复制一份到备库,然后在备库上执行比较消耗时间的CPU的查询. 这样主库只用于高效的插入工作,而备库上执行的查询也无需担心影响到日志的插入性能. 当然也可以在系统负载较低的时候执行报表查询操作,但应用在不断变化,如果依赖这个策略可能以后会导致问题.
      • 在日志记录表的名字中包含年和月的信息,比如web_logs_2012_01. 这样可以在已经没有插入操作的历史表上做频繁的查询操作,而不会干扰到最新的当前表上的插入操作.

  • 只读或者大部分情况下只读的表

    • 有些表的数据用于编制类目或者分列清单(如工作岗位.竞拍.不动产等),这种应用场景是典型的读多写少的雨雾. 如果不介意MyISAM的崩溃回复问题,选用MyISAMy引擎是合适的. 不过不要低估崩溃恢复问题的重要性,有些存储引擎不会保证将数据安全地写入到磁盘中,而许多用户实际上并不清楚这样有多大的封建(MyISAM 只讲数据写到内存中,然后等待操作系统定期将数据刷出到磁盘上).
    • 当设计上述类型的应用时,建议采用InnoDB.MyISAM引擎在一开始可能没有任何问题,但随着应用压力的上升,则可能迅速恶化. 各种锁争用.崩溃后的数据丢失问题都会随之而来.

  • 订单处理

    • 如果涉及订单处理,那么支持事务就是必要选项. 半完成的订单是无法用来吸引用户的. 另外一个重要的考虑点事存储引擎对外键的支持情况. InnoDB是订单处理类应用的最佳选择.

  • 电子公告牌和主题讨论论坛

    • 对于MySQL用户,主题讨论区是个很有意思的话题. 当前有成百上千的基于PHP或者Perl的免费系统可以支持主题讨论.其中大部分的数据库操作效率都不高,因为它们大多倾向于在一次请求中执行尽可能多的查询语句. 另外还有部分系统设计为不采用数据库,当然就无法阿里用到数据库提供的一些方便的特性. 主题讨论群区一般都有更新技术器,并且会为各个主题计算访问统计信息. 多大胡引用只设计了几张表来保存所有的数据,所以核心表的读写压力可能非常大. 为保证这些核心表的数据一致性,锁成为资源争用的主要因素.
    • 尽管有这些设计缺陷,但大多数应用在中低负载时可以工作得很好. 如果web站点的规模迅速扩展,流量随之猛增,则数据库访问可能变得非常慢. 此时一个典型的解决方案是更改为支持更高读写的存储引擎,但有时用户会发现这么做反而导致系统变得更慢了. 用户可能没有意识到这是由于某些特殊查询的缘故,典型的如:
mysql> SELECT COUNT(*) FROM table;
1
    • 问题就在于不是所有的存储引擎运行上述查询都非常快 : 对于MyISAM确实会很快,但其他的可能都不行. 每种存储引擎都嫩个找出类似的对自己有利的例子.
  • CD-ROM应用
    • 如果发布一个基于CD-ROM或者DVD-ROM并且使用MySQL数据文件的应用,可以考虑使用MyISAM表或者MyISAM压缩表,这样表之间可以隔离并且可以在不同介质上相互拷贝.MyISAM压缩表比未压缩的表要节约很多空间,但压缩表时只读的. 在某些应用中这可能是个大问题. 但如果数据放到只读介质的场景下,压缩表的只读特性就不是问题,就没有理由不采用压缩表了.
  • 大数据量
    • 如果数据量增长到10TB以上级别,可能就需要建立数据仓库. Infobright是 MySQL数据仓库最成功的解决方案.也有一些大数据库不适合Infobright,却可能适合TokuDB.

# 1.5.6 转换表的引擎

ALTER TABLE

将表从一个引擎修改为另一个引擎最简单的办法是使用ALTER TABLE语句.

mysql> ALTER TABLE mytable ENGINE = InnoDB;
1
  • 上述语法可以使用任何存储引擎. 但有一个问题: 需要执行很长时间. MySQL会按行将数据从原表复制到一张新的表中, 在复制期间可能会消耗系统所有的I/O能力,同时原表上会加上读锁. 所以,在繁忙的表上执行此操作要特别小心. 一个替代方案是采用导出导入的方法,手工进行表的复制.

  • 如果转换表的存储引擎,将会失去和原引擎相关的所有特性. 例如,如果将一张InnoDB表装换为MyISAM,然后再转换回InnoDB,原InnoDB表上所有的外键将丢失.

导出与导入

为了更好地控制转换的过程,可以使用mysqldump工具将数据导出到文件,然后修改文件中CREATE TABLE语句的存储引擎选项,注意同时修改表明,因为同一个数据库中不能存在相同的表明,即使它们使用的是不同的存储引擎. 同时哟啊注意mysqldump默认会自动在CREATE TABLE 语句前加上DROP TABLE语句,不住与这一点可能会导致数据丢失.

创建与查询(CREATE和SELECT)

此方法综合了第一种方法的高效和第二种方法的安全.不需要导出整个表的数据,而是先创建一个新的存储引擎的表,然后利用INSERT...SELECT 语法来导数据:

mysql> CREATE TABLE innodb_table LIKE myisam_table;
mysql> ALTER TABLE innodb_table ENGINE=InnoDB;
mysql> INSERT INTO innodb_table SELECT * FROM myisam_table;
1
2
3

数据量不大的话,这样做工作很好. 如果数据量很大,则可以考虑分批处理, 针对每一段数据执行事务提交操作, 以避免大事务产生过多的undo. 假设有主见字段id, 重复运行一下语句(最小值x和最大值y进行相应的替换)将数据导入到新表:

mysql> START TRANSACTION;
mysql> INSERT INTO innodb_table SELECT * FROM myisam_table 
     > WHERE id BETWEEN x And y;
mysql> COMMIT;
1
2
3
4

这样操作完成以后,新表是原表的一个全量复制,原表还在,如果需要可以删除原表. 如果有必要,可以在执行的过程中对原表加锁,以确保新表和原表的数据一致.

Percona Toolkit提供了一个pt_online_schema_change的工具(基于Facebook的在线schema变更技术),可以比较简单.方便地执行上述过程,避免手工操作可能导致的失误和繁琐.

# 第二章 MySQL基准测试

基准测试(benchmark)是MySQL新手和专家都需要掌握的一项基本技能. 简单地说,基准测试是针对系统设计的一种压力测试. 通常的目标是为了掌握系统的行为. 但也有其他原因,如重现某个系统状态,或者是做新硬件的可靠性测试.

# 第三章 服务器性能剖析

# 3.1 性能优化简介

**性能: **

  • 为完成某件任务所需要的时间度量,换句话说,性能即相应时间.
  • 数据库的性能用查询的相应时间来度量,单位是每个查询花费的时间.
    • 查询的相应时间更能体现数据库版本升级后的性能是不是变得更好.
  • 如果把性能仅仅看成是提升每秒查询量,这其实只是吞吐量优化.吞吐量的提升可以看做性能优化的副产品.
  • 测量性能更需要合适的测量范围. 有两种比较常见的情况会导致不适合的测量:
    • 在错误的时间启动和停止测量.
    • 测量的是聚合后的信息,而不是目标活动本身.

# 3.1.1 通过性能剖析进行优化

性能剖析是测量和分析时间花费在哪里的主要方法.性能剖析一般有两个步骤:测量任务所花费的时间,然后对结果进行统计和排序,将重要的任务排到前面.

# 3.1.2 理解性能剖析

  • 值得优化的查询
    • 一些只占总响应时间比重很小的查询是不值得优化的.
    • 如果花费了1000$去优化一个任务,但业务的收入没有任何增加,那么可以说反而导致业务被逆化了1000$. 如果优化成本大于收益,就应当停止优化.
  • 异常情况
    • 某些任务即使没有出现在性能剖析输出的前面也需要优化.比如某些任务执行次数很少,但每次执行都非常慢,严重影响用户体验.因为其执行频率低,所以总的响应时间占比并不冲突
  • 未知的未知
    • 一款好的性能剖析工具会显示可能的"丢失的时间". 丢失的时间指的是任务的总时间和实际测量到的时间之间的差. 例如,如果处理器的CPU时间是10秒,而剖析到的任务总时间是9.7秒,那么就有300好眠的丢失时间.这可能是有些任务没有测量到,也可能是由于测量的误差和精确度问题的缘故.如果工具发现了这类问题,则要引起重视,因为有可能错过了某些重要的事情. 即使性能剖析没有发现丢失时间,也需要注意考虑这类问题存在的可能性,这样才不会错过重要的信息.
  • 被掩藏的细节
    • 性能剖析无法显示所有相应时间的分布.只相信平均值是非常危险的,它会隐藏很多信息,而且无法表达全部情况.

# 第四章 Schema与数据类型优化

# 4.1 选择优化的数据类型

  • 更小的通常更好
    • 一般情况下,应该尽量使用可以正确存储数据的最小数据类型. 更小的数据类型常更快,因为它们占用更少的磁盘.内存和CPU缓存,并且处理时需要的CPU周期也更少.
    • 但是要确保没有低估需要存储的值的范围.因为在schema中的多个地方增加数据类型的范围是一个非常耗时和痛苦的操作.
  • 简单就好
    • 简单数据类型的操作通常需要更少的CPU周期. 例如, 整形比字符操作代价更低,因为字符集和校对柜子(排序规则)使字符比较比整形比较更加复杂.这里有两个例子:
      • 应该使用MySQL内建的类型而不是字符串来存储日期和时间.
      • 应该用整形存储IP地址.
  • 尽量避免NULL
    • 很多表都包含可谓NULL的列,即使应用程序并不需要保存NULL也是如此,这是因为可谓NULL是列的默认属性. 通常情况下最好指定列为NOT NULL,除非真的需要存储NULL值.
    • 如果查询中包含可谓NULL的列,对MySQL来说更难优化,因为可为NULL的列使得索引.索引统计和值比较都更复杂.可为NULL的列会使用更多的存储空间,在MySQL里也需要特殊处理. 当可为NULL的列被索引时,每个索引记录需要一个额外的字节,在MyISAM里甚至可能导致固定大小的索引(例如只有一个整数列的索引)编程可变大小的索引.
    • 通常把可为NULL的列改为NOT NULL带来的性能提升比较小,所以(调优时)没有必要首先在现有schema中查找并修改掉这种情况,除非确定这会导致问题.但是,如果计划在列上建索引,就应该尽量避免设计成可为NULL的列.
  • -当然也有例外,例如InnoDB使用单独的为(bit)存储NULL值,所以对于稀疏数据有很好的空间效率.但这一点不是哟用于MyISAM.
      • 稀疏数据: 很多值为NULL,只有少数行的列有非NULL值.

DATETIME和TIMESAMP列都可以存储相同类型的数据:时间和日期,精确到秒. 然而TIMESTAMP只使用DATETIME一半的存储空间,并且会根据时区变化,具有特殊的自动更新能力.另一方面,TIMESTAMP允许的时间范围要小得多,有时候它的特殊能力会成为障碍.

# 4.1.1 整数类型

有两种烈性的数字: 整数(whole number)和实数(real number). 如果存储整数,可以使用这几种整数类型: tinyint, smallint, mediumint, int, bigint. 分别使用8,16,24,32,64位存储空间. 他们可以存储的值的范围从-2(n-1)到2(n-1),其中n是存储空间的位数.

  • 整数类型有可选的UNSIGNED属性,表示不允许负值,这大致可以使正数的上线提高一倍.例如 TINYINT.UNSIGNED 可以存储的范围是0~255,而TINYINT的存储范围是-128~127.
  • 有符号和无符号类型使用相同的存储空间,并具有相同的性能,因此可以根据实际情况选择合适的类型.
  • 类型的选择是决定MySQL是怎么在内存和磁盘中保存数据的. 然而,整数计算一般使用64位的BIGINT整数,即使在32位环境也是如此.(一些聚合函数是例外,它们使用DECIMAL或DOUBLE进行计算).
  • MySQL可以为整数类型指定宽度,例如INT(11).对大多数应用这是没有意义的: 他不会限制值的合法范围,只是规定MySQL的一些交互工具(例如MySQL命令行客户端)用来显示字符的个数. 对于存储和计算来说,INT(1)和INT(20)是相同的.

# 4.1.2 实数类型

实数是带有小数部分的数字. 然而,它们不只是为了存储小数部分;也可以使用DECIMAL存储比BIGIT还大的整数.MySQL即支持精确类型,也支持不精确类型.

**在MySQL 5.0以及更高版本中,MySQL服务器自身实现了DECIMAL的高精度计算.**相对而言CPU直接支持原生浮点计算,所以浮点运算明显更快.

# 4.1.3 字符串类型

VARCHAR

  • VARCHAR类型用于存储可变长的字符创,是最常见的字符串数据类型.它比定长类型更节省空间,因为它仅使用必要的空间.有一种情况例外,如果MySQL表使用TOW_FORMAT=FIXED创建的话,每一行都会使用定长存储,这会浪费空间.
  • VARCHAR需要使用1或2个二外字节记录字符串的长度: 如果列的最大长度系哦啊与或等于255字节,则只使用1个字节表示,否则使用2个字节. 假设采用latinl字符集,一个VARCHAR(10)的列需要11个字节的存储空间.VARCHAR(1000)的列则需要1002个字节,因为需要2个字节存储长度信息.
  • VARCHAR节省了存储空间,所以对性能也有帮助. 但是, 由于行是变长的,在UPDATE时可能使行变得比原来更长,这就导致需要做额外的工作. 如果一行占用的空间增长,并且在页内没有更多的空间可以存储,在这种情况下,不同的存储引擎的处理方式是不一样的. 例如MyISAM会将行拆成不同的片段存储,InnoDB则需要分裂页来使行可以放进业内.其他一些存储引擎也许从不在元数据位置更新数据.夏明这些情况下适合VARCHAR
    • 在字符串列的最大长度比平均长度大很多;
    • 列的更新很少,所以碎片不是问题;
    • 使用了像UTF-8这样复杂的字符集,每个字符都使用不同的字节数进行存储.

CAHR

  • CAHR类型是定场地: MySQL总是根据定义的字符串长度分配足够的空间. 当存储CAHR值时,MySQL会删除所有的末尾空格.CAHR值会根据需要采用空格进行填充方便比较.
  • CAHR适合存储很短的字符串,或者所有的值都接近同一个长度.例如,CAHR非常适合存储密码的MD5值,因为这是一个定长的值. 对于经常变更的数据,CAHR也比VARCHAR更好,因为定长的CAHR类型不容易产生碎片. 对于非常短的列,CAHR比VARCHAR在存储空间上也更有效率. 例如用CAHR(1)来存储只有Y和N的值, 如果采用单字节字符集只需要一个字节,但是VARCHAR(1)缺需要两个字节,因为还有一个记录长度的额外字节.
  • 与CHAR和VARCHAR累次的类型还有BINARY和VARBINARY,它们窜出的是二进制字符串.二进制字符串跟常规字符串非常相似,但是二进制字符串存储的是字节码而不是字符. 填充也不一样:MySQL填充BINARY采用的是\0(零字节)而不是空格,在检索时也不会去掉填充值.
  • 慷慨是不明智的
    • 使用VARCHART(5)和VARCHAR(200)存储'hello'的空间开销是一样的.但更长的列会消耗更多的内存,因为MySQL通常会分配苦丁大小的内存块来保存内部值. 尤其是使用内存临时表进行排序或者操作时会特别糟糕. 在利用磁盘临时表进行排序时也同样糟糕. 所以最好的策略是只分配真正需要的空间.

BLOB和TEXT类型

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

  • 字符类型是TINYTEXT, SMALLTEXT, TEXT, MEDIUMTEXT, LONGTEXT;对应的二进制类型是TINYBLOB, SMALLBLOB, BLOB, MEDIUMBLOB, LONGBLOB.
  • 与其他类型不同,MySQL吧每个BLOB和TEXT值当作一个独立的对象处理. 存储引擎在存储是通常会做特殊处理. 当BLOB和TEXT值太大时, InnoBD会使用专门的"外部"存储区域来进行存储,此时每个值在行内需要1~4个字节存储一个指针,然后在外部存储区域存储实际的值.
  • BLOB和TEXT家族之间仅有的不同是BLOB类型存储的是二进制数据,没有排序规则或字符集,而TEXT类型有字符集和排序规则.

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

有时候可以使用枚举列代替常用的字符串类型. 枚举列可以吧一些不容副的字符串存储成一个预定义的集合. MySQL在存储枚举时非常紧凑,会根据列表值的数量压缩到一个或两个字节中. MySQL在内部会将每个值在列表中的位置保存为整数,并且在表的.frm文件中保存"数字-字符串"映射关系的"查找表".

CREATE TABLE enum_test(
	e ENUM('fish', 'apple', 'dog') NOT NULL
);

SELECT e+0 FROM enum_test;
1
2
3
4
5

# 4.1.4 日期和时间类型

MySQL可以使用许多类型来保存日期和时间值,例如YEAR和DATE.MySQL能啐出最小时间力度为秒.但是MySQL也可以使用微妙级粒度进行临时运算.

  • DATETIME
    • 这个类型能保存大范围的值,从1001年到9999年,精度为秒. 它吧日期和时间封装到可是为YYYYMMDDHHMMSS的整数中,与时区无关. 使用8个字节的存储空间.
    • 默认情况下,MySQL以一种可排序的.无歧义的格式显示DATETIME值,例如"2008-01-16 22:37:08". 这是ANSI标准定义的日期和时间表示方法.
  • TIMESTAMP
    • TIMESTAMP类型保存了从1970年1月1日午夜依赖的秒数,它和UNIX时间戳相同. TIMESTAMP只使用4个字节的存储空间,因此它的范围比DATETIME小得多:只能表示从1997年到2038年.
    • TIMESTAMP显示的值依赖于时区. MySQL服务器.操作系统,以及客户端连接都有时区设置.
  • 除了特殊行为之外,通常也应该尽量使用TIMESTAMP,因为它比DATETIME空间效率更高.有时候人们会将Unix时间戳存储为整数值,但这不会带来任何收益. 用整数保存时间戳的格式通常不方便处理,所以我们不推荐这样做.

# 4.1.5 位数据类型

MySQL有少数几种存储类型使用紧凑的位存储数据.所有的这些位类型,不关底层存储格式和处理方式如何,从技术上来说都是字符串类型.

  • BIT
  • SET
  • 在整数列上进行按位操作

# 4.1.6 选择标识符(identifier)

为标识列选择合适的数据类型非常重要. 当选择标识列的类型时,不仅仅需要考虑存储类型,还需要考虑MySQL对这种类型怎么执行几所和比较. 例如MySQL在内部使用整数存储ENUM和SET类型,然后在做比较操作时转换为字符串.

  • 整数类型
    • 整数通常是标识列最好的选择,因为他们很快并且可以使用AUTO_INCREMENT.
  • ENUM和SET类型
    • 对于标识列来说,EMUM和SET类型通常是一个糟糕的选择,尽管对某些只包含固定状态或者类型的静态"定义表"来说可能是没有问题的. ENUM和SET列适合存储固定的信息,例如有序的状态.产品类型.人的性别.
  • 字符串类型
    • 如果可能,应该避免使用字符串类型作为标识列,因为它们很消耗空间,并且通常比数字类型慢.
    • 如果存储UUID值,则应该一处"-"符号;或者更好的做法是,用UNHEX()函数转换UUID值为16字节的数字,并且存储在一个BINARY(16)列中.检索时可以通过HEX()函数来格式化为十六进制格式.
  • 当心自动生成的schema

# 4.1.7 特殊类型数据

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

# 4.2 MySQL schema设计中的陷阱

太多的列

  • MySQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据,然后在服务器层将缓冲内容解码成各个列. 从行缓冲中将斑马过多的列转换成行数据结构的操作代价是非常高的. 上千字段 太多的关联
  • 所谓的"实体-属性-值"(EAV)设计模式是一个常见的糟糕设计模式,尤其是在MySQL下不能靠谱地工作. MySQL限制了每个关联操作最多只能有61张表,但是EAV数据库需要许多自关联.事实上在许多关联少于61张表的情况下,解析和优化查询的代价也会成为MySQL的问题.如果希望查询执行得快速且并发性好,耽搁查询最好在12个表以内做关联.

全能的枚举

注意防止过度使用枚举.

CREATE TABLE ...(
    country enum('', '0', '1', '2', ..., '31')
)
1
2
3
  • 这种模式的schema设计非常凌乱. 折磨使用枚举类型也许在任何支持枚举类型的数据库都是一个有问题的设计方案,这里应该用整数作为外键关联到字典表或者查找表来查找具体值.

变相的枚举

枚举列允许在列中存储一组定义值中的单个值,集合(SET)列则允许在列中存储一组定义值中的一个或多个值.有时候这可能比较容易导致混乱

CREATE TABLE ... (
    is_default set('Y', 'N')NOT NULL default 'N'
)
1
2
3
  • 如果这里和真假两种情况不会同时出现,那么毫无疑问应该使用枚举列代替集合列

非此发明(Not Invent Here)的NULL

我们在之前说避免使用NULL的好处,并且建议尽可能考虑替代方案. 即使需要存储一个事实上的"空值"到表中时,也不一定非得使用NULL. 也许可以使用0或者空字符串作为代替.但是不要走极端. 当确实需要表示未知值时也不要害怕使用NULL.

# 4.3范式和反范式

对于任何给定的数据通常都有很多表示方法,从完全的范式化到完全的反范式化.

雇员,部门,部门领导

EMPLOYEE DEPARTMENT HEAD
Jones Accounting Jones
Smith Engineering Smith
Brown Accounting Jones
Green Accounting Smith
  • 这个schema的问题是修改数据时可能发生不一致.假如Say Brown接任Accounting部门的领导,需要修改多行数据来反映这个变化,这很痛苦.
  • 这个设计在没有雇员信息的情况下就无法表示一个部门---如果我们删除了所有的Accounting部门雇员,我们就事情了关于这个部门本身的所有记录
  • 要避免这些问题,我们需要对这个表进行范世华,方式就是拆分雇员和部门项.

雇员表

EMPLOYEE DEPARTMENT
Jones Accounting
Smith Engineering
Brown Accounting
Green Accounting

部门表

DEPARTMENT HEAD
Accounting Jones
Engineering Smith

# 4.3.1 范式的优点和缺点

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

  • 范式化的更新操作通常比反范式化要快.
  • 当数据较好地范式化时,就只有很少或者没有重复数据,所以只需要修改更少的数据.
  • 范式化的表通常更小,可以更好地放在内存里,所以执行操作会更快.
  • 很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句.
    • 在非范式化的结构中必须使用DISTINCT或者GROUP BY才能获得一份唯一的部门列表,但是如果部门是一张单独的表,则只需要简单的查询这张表就行了.

范式化设计的schema的缺点是通常需要关联. 稍微复杂一些的查询语句在符合范式的schema上都可能需要至少一次关联,也许更多. 这不但代价昂贵,也可以使一些索引策略无效. 例如,范式化可能键列存放在不同的表中,而这些列如果在一个表中奔可以属于同一个索引.

# 4.3.2 反范式的优点和缺点

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

# 4.3.3 混用范式化和反范式化

完全的范式化和完全的反范式化schema都是实验室里才有的东西: 在真实世界中很少会这么极端地使用. 在实际应用中经常需要混用,可能使用部分范式化的schema.缓存表,以及其他技巧.

# 4.4 缓存表和汇总表

有时提升性能最好的方法是在同一张表中保存衍生的冗余数据. 然而,有时也需要创建一张完全独立的汇总表或缓存表.

  • 缓存表
    • 表示存储那些可以比较简单地从schema其他表获取(但是每次获取的速度比较慢)数据的表
  • 汇总表
    • 保存的是使用GROUP BY语句聚合数据的表,也有人使用术语"累计表".
  • 以网站为例,假设需要机选24小时内发送的消息数. 在一个很繁忙的网站不可能维护一个实时精确的计数器. 作为替代方案,也已每小时生成一张汇总表. 这样也许一条很简单的查询就可以做到,并且比实时维护计数器要高效得多.缺点是计数器并不是100%的精确.
  • 另外一种选择是: 以每小时汇总表为基础,把前23个完整的小时的统计表的计数全部加起来,最后再加上开始阶段和结束阶段不完整的小时内的技术.假设统计表为msg_per_hr
CREATE TABLE msg_per_hr(
    hr DATETIME NOT NULL,
    cnt INT UNSIGEND NOT NULL,
    PRIMARY KEY(hr)
)
1
2
3
4
5
  • 可以通过吧下面的三个语句的结果加起来,得到过去24小时发送消息的总数.我们使用LEFT(NOW(),14)来获得当前的日期和时间最接近的小时:
现在1:30
昨天2点到今天0点总次数
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;
昨天1:30到凌晨2点
mysql> SELECT COUNT(*) FROM message
    WHERE posted >= NOW() - INTERVAL 24 HOUR
    AND posted < CONCAT(LEFT(NOW(), 14),'00:00') - INTERVAL 23 HOUR;
今天1点到现在
mysql> SELECT COUNT(*) FROM message
    WHERE posted >= CONCAT(LEFT(NOW(), 14),'00:00');
1
2
3
4
5
6
7
8
9
10
11
12
  • 不管是哪种方法---不严格的技术或者通过小范围查询填满简写的严格技术都比直接计算message表所有行要有效的多.这是建立汇总表的关键原因.
  • 缓存表正好相反,其对优化搜索和检索查询语句很有效.
  • 缓存表可能会需要很多不同的索引组合来加速各种类型的查询. 这些矛盾的需求有时需要创建一张只包含主表中部分列的缓存表. 一个有用的技巧是对缓存表使用不同的存储引擎.
    • 如果主表使用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;
mysql> RENAME TABLE my_summary TO my_summary_old, my_summary_new TO my_summary;
1
2
3

# 4.4.1 物化视图

物化视图实际上是预先计算并且存储在磁盘上的表,可以通过各种各样的策略刷新和更新. MySQL并不原生支持物化视图. 然而,使用Justin Swanhart的开源工具Flexviews,也可以自己实现物化视图. Flexviews比完全自己实现的解决方案要精细,并且提供了很多不错的功能使得可以更简单地创建和维护物化视图. 它由下面这些部分组成:

  • 变更数据抓取(Change Date Capture CDC)功能,可以读取服务器的二进制日志而且解析相关行的变更.
  • 一系列可以帮助创建和管理视图的定义的存储过程.
  • 一些可以应用变更到数据库中的物化视图的工具.

# 4.4.2 计数器表

如果应用在表中保存计数器,则在更新计数器时可能碰到并发问题. 计数器表在Web应用中很常见. 可以用这种表缓存一个用户的朋友数.文件下载次数等. 创建一张独立的表存储计数器可以使计数器表小且快. 使用独立的表可以帮助避免查询缓存失败,并且可以使用一些高级技巧.

  • 计数器表
CREATE TABLE hit_counter(
    cnt int unsigned not null 
)ENGINE=InnoDB;
1
2
3
  • 网站的每次点击都会导致对计数器进行更新:
UPDATE hit_counter SET cnt = cnt + 1;
1
  • 问题在于,对于任何想要更新这一行的事务来说,这条记录上都有一个全局的互斥锁(mutex). 这会使得这些事务只能串行执行. 要活的更高的并发更新性能,也可以将计数器保存在多行中,每次随机选择一行进行更新. 这样需要对计数器表进行如下修改;
CREATE TABLE hit_counter(
    slot tinyint unsigned not null primary key,
    cnt int unsigned not null 
)ENGINE=InnoDB;
1
2
3
4
  • 在这张表增加100行数据. 现在选择一个随机的槽(slot)进行更新:
UPDATE hit_counter SET cnt = cnt + 1 WHERE slot = RAND() * 100;
1
  • 要活的统计结果,需要使用下面的聚合查询:
SELECT SUM(cnt) FROM hit_counter;
1
  • 一个常见的需求是每隔一段时间开始一个新的计数器(例如,每天一个). 如果需要这么做,则可以再简单地修改一下表设计:
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;
1
2
3
4
5
6
  • 在这个场景中,可以不用像前面的例子那样预先生成行,而用ON DUPLICATE KEY UPDATE代替:
INSERT INTO daily_hit_counter(day, slot, cnt)
    VALUES(CURRENT_DATE, RAND()*100, 1)
    ON DUPLICATE KEY UPDATE cnt = cnt + 1;
1
2
3
  • 如果希望减少表的行数,以避免表变得太大,可以写一个周期执行的函数,合并所有结果到0号槽,并且删除所有其他的槽:
<!--
x表中day被group by了所以每天的东西被汇总,比如今天就只有一条数据,以今天为例和c表进行关联后,关联语句为day相同,那么x表的今天的数据会和c表中的所有今天的数据结合成一条新的数据. 
然后set , 如果槽位相同也就是0; 那么c表中的cnt会变为x表的cnt,同时slot会变为0,         否则cnt变为0,槽位还是原来的槽位.
有两条set并且都有if是因为怕表中没有slot=0,那么久将最小的那个slot赋值为0
-->

UPDATE daily_hit_counter ad 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);
    
DELETE FROM daily_hit_counter WHERE slot <> 0 AND cnt = 0;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

更快地读,更慢地写 为了提升查询的速度,经常会需要建一些额外索引,增加冗余列,甚至是创建缓存表和汇总表. 这些方法会增加写查询的负担,也需要额外的维护任务,但在设计高性能数据库时,这些都是常见的技巧: 虽然写操作变得更慢了,但更显著地提高了读操作的性能. 然而,写操作变慢并不是读操作变得更快所付出的唯一代价,还可能同时增加了读操作和写操作的开发难度.

# 4.5 加快ALTER TABLE操作的速度

MySQL执行大部分修改表结构操作的方法是用新的结构创建一个空表,从旧表中查出所有数据插入新表,然后删除旧表.

MySQL 5.1 以及更新版本包含一些类型的"在线"操作的支持,这些功能不需要在整个操作过程中锁表.最近版本的InnoDB也支持通过排序来创建索引,这使得建索引更快并且有一个紧凑的索引布局.

  • 修改表结构
    • 现在一台不提供服务的机器上执行ALTER TABLE操作,然后和提供服务的主库进行切换
    • 影子拷贝---用要求的表结构创建一张和源表无关的新表,然后通过重命名和删表操作交换两张表.也有一些工具可以帮助完成影子拷贝工作:Facebook数据库运维团队的"online schema change"工具.
  • 不是所有的ALTER TABLE 操作都会引起表重建. 例如,有两种方法可以改变或者删除一个列的默认值
## 慢的方式
ALTER TABLE sakila.film 
MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5;

//SHOW STATUS显示这个语句做了1000此读和1000此插入操作. 
//换句话说,它拷贝了整张表到一张新的表,甚至列的操作.大小和可否为NULL属性都没改变.
1
2
3
4
5
6
  • 理论上,MySQL可以跳过创建新表的步骤. 列的默认值实际上存在表的.frm文件中,所以直接修改这个文件而不需要改动表本身.
## 这个语句会直接修改.frm文件而不设计表数据. 所以这个操作是非常快的
ALTER TABLE sakila.film 
MODIFY COLUMN rental_duration SET DEFAULT 5;
1
2
3

# 4.6 总结

  • 尽量避免过度设计,例如会导致机器复杂查询的schema设计,或者有很多列的表设计
  • 使用小而简单的合适数据类型,除非真实数据模型中有确切的需要,否则应该尽可能地避免使用NULL值.
  • 尽量使用相同的数据类型存储相似或相关的值,尤其是要在关联条件中使用的列.
  • 注意可变长字符串,其在临时表和排序时可能导致悲观的按最大长度分配内存.
  • 尽量使用整形定义标识列.
  • 避免使用MySQL已经一起的特性,例如指定浮点数的精度,或者整数的显示宽度.
  • 小心使用ENUM和SET.虽然它们用起来很方便,但是不要滥用,否则有时候会编程陷阱.最好避免使用BIT.

范式是好的,但是反范式有时也是必需的,并且能带来好处.

# 第五章 创建高性能的索引

索引时存储引擎用于快读找到记录的一种数据结构.索引对于良好的性能非常关键.尤其是当表中的数据量越来越大时,索引对性能的影响语法重要.在数据量较小且负载较低时,不恰当的索引对性能的影响可能还不明显,但当数据量逐渐增大时,性能则会急剧下降.

# 5.1 索引基础

在MySQL中,存储引擎用先在索引中找到对应的值,然后根据匹配的索引找到对应的数据行. 索引可以包含一个或多个列的值. 如果索引包含多个列,那么列的书序也十分重要,因为MySQL只能高效地使用索引的最左前缀列. 创建一个包含两个列的索引,和创建两个只包含一列的索引是大不相同的.

# 5.1.1 索引的类型

B-Tree索引

当人们谈论索引的时候,如果没有特别指明类型,那多半说的是B-Tree索引,它使用B-Tree数据结构来存储数据.大多数MySQL引擎都支持这种索引.

  • B-Tree同创意味着所有的值都是按顺序存储的,并且每一个椰子页到根的距离相同.B-Tree索引能够加快访问数据的速度,因为存储引擎不再需要进行全表扫描来获取需要的数据,取而代之是从索引的根节点开始进行搜索. 根节点的草种存放了只想子节点的指针,存储引擎根据这些指针向下层查找. 通过比较节点页的值和要查找的值可以找到合适的指针进入下层子几点,这些指针实际上定义了子节点页中值的上限和下限. 最终存储引擎要么是找到对应的值,要么该记录不存在.
  • B-Tree对索引列是顺序组织存储的,所以很合适查找范围数据. 例如,在一个机遇文本域的索引树上,按字母数据传递连续的值进行查找是非常合适的,所以像"找出所有以I到K开头的名字"这样的查找效率非常高.
CREATE TABLE People(
    last_name  varchar(50) not null,
    first_name varchar(50) not null,
    dob        date        not null,
    gender  enum('m', 'f') not null,
    key(last_name, first_name, dob)
);
## 索引对多个值进行排序的依据是CREATE TABLE语句中定义索引时列的顺序.
1
2
3
4
5
6
7
8
  • 全值匹配
    • 全值匹配值得是和索引中所有列进行匹配,例如前面提到的索引可用于查找邢明伟Cuba Allen.出生于1960-01-01的人.
  • 匹配最左前缀
    • 前面提到的索引可用于查找所有姓为Allen的人,即只使用索引的第一列.
  • 匹配列前缀
    • 也可以只匹配某一列的值的开头部分. 例如前面提到的索引可用于查找所有以J开头的姓的人.这里也只使用了索引的第一列.
  • 匹配范围值
    • 索引可用于查找姓在AllenHE Barrymore之间的人.这里也只使用了索引的第一列.
  • 精确匹配某一列并范围匹配另外一列
    • 索引也可用于查找所有姓为Allen,并且名字是字母K开头的人. 即第一列last_name全匹配.第二列first _name范围匹配.
  • 只访问索引的查询
    • B-Tree通常可以支持"只访问索引的查询",即查询只需要访问索引,而无需访问数据行.
  • 一些关于B-Tree索引的限制:
  • 如果不是按照索引的最左列开始查找,则无法使用索引. 例如上面的例子中索引无法用于查找名字为Bill的人,也无法查找某个特定生日的人,因为这两列都不是最左数据列. 类似地,也无法查找形式以某个字母结尾的人.
  • 不能跳过索引中的列. 也就是说,前面所述的索引无法用于查找姓为Smith并且在某个特定日期出生的人. 如果不指定名(first_name),则MySQL只能使用索引的第一列.
  • 如果查询中有某个列的范围查询,则其右边所有列都无法使用索引优化查找. 例如
WHERE last_name='Smith' AND first_name LIKE 'J%' AND job = '1976-12-23'
1
  • 这个查询只能使用索引的前两列,因为这里的LIKE是一个范围条件. 如果范围查询列值的数量有限,那么可以通过使用多个等于条件来代替范围条件.

# 5.2 索引的优点

索引可以让服务器快速地定位到表的制定位置. B-Tree索引按照顺序存储数据,所以MySQL可以用来做ORDER BY 和 GROUP BY 操作. 因为数据时有序的,所以B-Tree也就会将相关的列值都存储在一起. 最后,因为索引中存储了实际的列值,所以某些查询只使用索引就能完成全部查询.

  • 索引大大减少了服务器需要扫描的数据量
  • 索引可以帮助服务器避免排序和临时表.
  • 索引可以将随机I/O变为顺序I/O.

# 5.3 高性能呢固定索引策略

# 5.3.1 独立的列

我们经常会看到一些查询不当地使用索引,或者使得MySQL无法使用已有的索引. 如果查询的列不是独立的,则MySQL就不会使用索引. "独立的列"是指索引列不能是表达式的一部分,也不能是函数的参数.

## 这个查询无法使用actorid列的索引:
SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;
1
2

很容易可以看出WHERE中的表达式其实等价于actor_id = 4,但是MySQL无法自动解析这个方程式. 这完全是用户行为. 我们应当养成简化WHAERE条件的习惯,始终将索引列单独放在比较符号的一次.

## 下面是另一个常见错误: 其中date_col是索引
SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10;
1
2
上次更新: 2021/02/20, 19:26:07

← MySQL中的函数→

Theme by Vdoing | Copyright © 2018-2022 逆光世间 | 备案号: 京ICP备19016086号