高性能Mysql-笔记

 

1.Mysql 的逻辑架构

每个客户端连接在服务器进程中都拥有自己的线程 ， 每个连接所属的查询都会在指定的某个单独线程中完成，这些线程轮流运行在某个 CPU 核心或者 CPU 上。服务器负载缓存线程，因此不需要为每个新的连接重建或撤销线程。

 

Mysql 会解析查询，并创建一个内部数据结构（解析树），然后对其进行各种优化。其中包括重写查询，决定查询的读表顺序，以及选择须使用的索引等。用户可以通过特殊的关键字给优化器传递各种提示（ Hint ），影响它的决策过程。

 

优化器并不关心 某个表使用哪种存储引擎，但存储引擎对服务器的查询优化过程有影响。优化器会请求存储引擎，为某种具体操作提供性能与开销方面的信息，以及表内数据的统计信息。

 

 

2 ．多版本并发控制（ MVCC ）

 

大多数 Mysql 的事务存储引擎，例如 InnoDB/Falcon/PBXT ，不是简单地使用行加锁的机制，而是选用一种叫做多版本并发控制（ MVCC ， Multiversion Concurrency Control ）的技术，和行加锁机制关联使用，以应对更多的并发处理问题。这种技术在其他数据库系统中也有使用。

 

可以将 MVCC 设想成一种行级加锁的变形，它避免了很多情况下的加锁操作，大大降低了系统的开销。依赖于具体技术实现，它可以在读取期间锁定需要的记录的同时，还允许非锁定读取。

 

MVCC 是通过及时保存在某些时刻的数据快照，而得以实现的。这意味着同一事务的多个实例 ，在同时运行时，无论每个实例运行多久，它们看到的数据视图是一致 的；而同一时间，对于同一张表 ，不同事务看到的数据却是不同 的 。

 

下面通过描述 InnoDB 简化版的行为方式，举例说明 MVCC 的工作原理。

 

InnoDB 通过为每个数据行增加两个隐含值的方式来实现 MVCC 。这两个隐含值记录了行的创建时间，以及它的过期时间（或者叫删除时间）。每一行都存储了时间发生时的系统版本号（ System Version Number ），用来替代事件发生时的实际时间。每一次，开始一个新事 务 时，版本号都会自动递增。每个事务都会保存它在开始时的“当前系统版本”的记录，而每个查询都会根据事务的版本号，检查每行数据的版本号。下面看一下，当事务隔离级别设置为 REPEATABLE READ 时， MVCC 在实际操作中的应用方式：

 

SELECT ： InnoDB 检查每行数据，确保它们符合两个标准， InnoDB 只查找版本早于当前事务版本的数据行（也就是数据行的版本必须小于等于事务的版本），这确保了当前事务读取的行都是在事务开始前已经存在的，或者是由当前事务创建或修改的行。数据行的删除版本必须是未定义的，或是大于事务版本的，这保证了事务读取的行，在事务开始是未被删除的。只有通过上述两项测试的数据行，才会被当作查询结果返回。

 

INSERT ： InnoDB 为每个新增行级记录当前系统版本号。

 

DELETE ： InnoDB 为删除行级记录当前系统版本号，作为删除标识。

 

UPDATE ： InnoDB 会为每个需要更新的行，建立一个新的行拷贝，并且为新的行拷贝，记录当前的系统版本号。同时，也为更新前的旧行，记录系统的版本号，作为旧行的删除版本标识。

 

保存这些额外记录的好处，是使大多数读操作都不必申请加锁 ，这使得读操作变得尽可能的快，因为读操作只要选取符合标准的行数即可。这种方式的缺点是，存储引擎必须为每行数据，存储更多的额外数据，做更多的行检查工作，以及处理一些额外的整理操作（ Housekeeping Operations ）。

 

MVCC 只工作在 REPEATABLE READ 和 READ COMMITTED 两个隔离级别。 READ UNCOMMITTED 隔离级不兼容 MVCC ，因为在任何情况下，该隔离级下的查询，不读取符合当前事务版本的数据行，而读取最新版本的数据行。 SERIALIZABLE 隔离级也不兼容 MVCC ，因为该级下的读操作会对每一个返回行都进行加锁。

 

 

3.Mysql 的存储引擎

 

         在文件系统中， Mysql 会把每个数据库保存为数据目录下的一个子目录。当创建一个表时， Mysql 会在和表名同名的，以 .frm 为后缀的文件中存储表的定义。

 

         MyISAM 引擎

 

         作为 Mysql 的默认存储引擎（现在新版本的可能是 InnoDB ），在性能和可用特征之间， MyISAM 提供一种良好的平衡，这些特征包括全文检索（ Full-Text-Indexing ），压缩，空间函数（ GIS ）。 MyISAM 不支持事务和行级锁。 MyISAM 将每个表存储成两个文件：数据文件和索引文件。两个文件的扩展名分别为 .MYD 和 .MYI 。

 

MyISAM 特征：加锁与并发， MyISAM 对整张表进行加锁，而不是行 。 读取程序在需要读取数据时，在所有表上都可以获得共享锁（读锁），而写入程序可以获得排他锁（写锁）。用户在运行 select 查询时，可以在同一张表内插入新行（也成为并发插入） ；自动修复 ， Mysql 支持对 MyISAM 表的自动检查和自动修复；手工修复 ；索引特征 ，用户可以基于 BLOB 或 TEXT 类型列的前 500 个字符，创建相关索引，支持全文索引 ，它可以更具个别单词，为复杂的搜索选项创建相关索引；延迟更新索引 ，使用表创建选项 DELAY_KEY_WRITE 创建的 MyISAM 表，在查询结束后，不会将索引的该表数据写入磁盘，而是在内存的键缓冲区中缓存索引改变数据，它只会在清理缓冲区，或者关闭表时，才将索引块转储到磁盘。对于数据经常该表，并且使用频繁的表，这个模式大大提高了表的处理性能。当服务器崩溃时， Mysql 可以使用自动恢复选项进行修复，或者手工修复，这个特性可以单独为个别表配置。

 

InnoDB 引擎

InnoDB 作为事务处理设计的一款存储引擎，特别是用于处理大量短期 事务，短期事务是指一般能正常完成，不需要回滚的事务。 InnoDB 将所有数据共同存储在一个或几个数据文件中，这种文件一般称为表空间 。表空间本质上是一种“黑盒（ Black box ）”，在“黑盒”内， InnoDB 自我管理一切数据。 InnoDB 使用 MVCC 机制获得高并发性能，并且实现所有四个标准隔离级。它的默认隔离级为 REPEATABLE READ ，它使用间隙锁策略防止“幻读”文件的产生：不仅对查询中读取的行加锁，而且还对索引结构中的间隙（ Gaps ）加锁，防止幻影（ Phantom ）插入。 InnoDB 表是基于聚簇索引建立的。 InnoDB 的索引结构，它的辅助索引（ Secondary Index ，也就是非主键索引）也会包含主键列，所以，如果主键定义的比较大，其他索引也将很大。 InnoDB 不会压缩索引。

 

 

4. 架构优化和索引

 

下面有简单的优化原则：

 

更小通常更好 ： 更小的数据类型通常更快，因为它们使用了更少的磁盘空间 / 内存和 CPU 缓存，而且需要的 CPU 周期也更少。

 

简单就好 ：越简单的数据类型，需要的 CPU 周期就越少。例如整数的代价比字符小。

 

尽量避免 NULL ：要尽可能地把字段定义为 NOT NULL ，即使应用程序无需保存 NULL 。因为 Mysql 难以优化引用了可空列的查询，它会使索引 / 索引统计和值更加复杂。可空列需要更多的存储空间，还需要在 Mysql 内部进行特殊处理。把 NULL 列改为 NOT NULL 带来的性能提升很小，所以除非确定它引入了问题，否则不要把它当成优先的优化措施。

 

要尽量地避免使用字符串做标识符 ，因为它们占用了很多空间并且通常比整数类型要慢。特别注意不要在 MyISAM 表上使用字符串标识符。 MyISAM 默认情况下为字符串使用了压缩索引，这使得查找更为缓慢。特别要注意使用“随机”的字符串，如 MD5 ， SHA1 等产生的。它们占用了很大空间范围，导致分页 / 随机磁盘访问及聚集存储引擎上的聚集索引碎片，而且逻辑上相邻的行会分布在磁盘和内存中的各个地方，从而减慢 INSERT 及一些 SELECT 查询。

 

MySql 支持的索引有 B-tree 索引（有序），哈希索引，空间（ R-Tree ）索引，全文索引等。

 

对于字符类型，前缀（后缀）索引是一个很好的选择，索引占用空间少，从而提高了查询速度。但是 Mysql 不能在 ORDER BY 和 GROUP BY 中使用前缀索引 ，也不能把它们用作覆盖索引。

 

聚集索引：聚集索引不是一种单独的索引类型，而是一种存储数据的方式 。其具体细节依赖于实现方式，但是 InnoDB 的聚集索引实际上在同样的结构中保存了 B-Tree 索引和数据行。 InnoDB 按照主键进行聚集 ，如果没有定义主键， InnoDB 会试着使用唯一的非空索引来代替。

 

覆盖索引：索引是找到行的高效方式，但是 Mysql 也能使用索引来接受列的数据，这样就可以不用读取行数据。包含所有满足查询需要的数据的索引叫做覆盖索引（ Covering Index ）。

 

按照索引对结果进行排序，只有当索引的顺序和 ORDER BY 子句中的顺序完全一致，并且所有列排序的方向（升序或降序）一样才可以。

 

重复索引： Mysql 允许你在同一个列上创建多个索引，它不会 注意到你的错误，也不会为错误提供保护。 Mysql 不得不单独维护每一个索引，并且查询优化器在优化查询的时候会逐个考虑它们。这会严重影响性能和空间。

 

多余索引：例如列（ A ， B ）上的有索引，另外一个列（ A ）上的索引是多余的。对于复合索引， Mysql 会使用最左前缀 ，上个例子就是用了最左前缀了。

 

 

 

5. 重要的引擎介绍

 

MyISAM 存储引擎

 

表锁 ： MyISAM 表有表级锁。注意不要让它成为瓶颈。

 

不支持自动数据恢复 ：如果 Mysql 服务器崩溃或断电，就应该在使用之前进行检查和执行可能的恢复。如果有大型表，这可能会花几个小时。

 

不支持事务 。

 

只有索引被缓存在内存中 ： MyISAM 只缓存 Mysql 进程内部的索引，并保存在键缓冲区。操作系统缓存了表的数据，因此在 Mysql5.0 中进行昂贵的系统调用来取得它。

 

紧密存储 ：行被紧紧地保存在一起，这样磁盘上的数据就能得到小的磁盘占用和快速的全表扫描。

 

InnoDB 存储引擎

 

事务性 ： InnoDB 支持事务和四种事务隔离级别。

 

外键 ：在 Mysql5.0 中， InnoDB 是唯一支持外键的存储引擎。另外的存储引擎在 CREATE TABLE 命名中可以接受外键，但却不强制执行。

 

行级锁 ：锁设定与行一级，不会向上传递并且也不会阻塞选择 — 标准选择根本不会设定任何锁，它有很好的并发特性。

 

多版本 ： InnoDB 使用多版本并发控制，这样在默认情况下可能会选择读取陈旧的数据。事实上，它的 MVCC 架构添加了很多复制和意料之外的性能。（可以参考 InnoDB 手册）

 

按主键聚集 ：所有的 InnoDB 表都是按主键聚集的，可以在架构设计中运用这一点。

 

所有索引包含主键列 ：索引按照主键引用行，因此，如果不把主键维持得很短，索引就增长得很大。

 

优化的缓存 ： InnoDB 把数据和内存缓存在缓冲区池里。它也会自动构建哈希索引以加快行读取。

 

未压缩的索引 ：索引没有使用前缀压缩，因此可能会比 MyISAM 表的索引大很多。

 

数据装载缓慢 ：在 Mysql5.0 中， InnoDB 不会特别优化数据加载。它一次构建一行的索引，而不是按照排序进行构建。这会导致数据加载很慢。

 

阻塞 AUTO_INCREMENT ：在 Mysql5.1 之前的版本中， InnoDB 使用了标记锁来产生每个新的 AUTO_INCREMENT 值。

 

没有缓存的 COUNT （ * ）值 ：和 MyISAM 表或者 Memory 表不同， InnoDB 不会把表的行数据保存在表中，这意味着没有 WHERE 子句的 count(*) 查询不会被优化掉，并且需要全表或索引扫描。

 

 

6. 查询性能优化

 

在优化数据库中，查询优化，索引优化和架构优化 三者相辅相成。

 

Mysql 执行查询的一般过程：

 

1. 客户端将查询发送到服务器。 2. 服务器检查查询缓存，如果找到了，就从缓存中返回结果，否则进行下一步。 3. 服务器解析，预处理和优化查询，生成执行计划。 4. 执行引擎调用存储引擎 API 执行查询。 5. 服务器将结构发送回客户端。

 

Mysql 检查查询缓存命中的方式是查询一张查找表，查找的键就是查询文本，当前数据库，客户端协议的版本，以及其他少数会影响实际查询结果的因素之哈希值。在检查缓存的时候， Mysql 不会对语句进行解析，正则化或者参数化。只要字符大小写，空格或者注释有一点点不同，查询缓存就认为这是一个不同的查询。查询缓存不会缓存有不确定结果的查询。因此，任何包含不确定函数的查询都不会被缓存 。

 

Mysql 查询缓存可以改善性能，但是在使用的时候有些问题值得注意。首先，开启缓存对于读写都增加了某些额外的开销：读取查询在开始之前必须要检查缓存；如果查询是可以被缓存的，但是不在缓存中，那么在产生结果之后进行保存会带来一些额外的开销；最后，写入数据的查询也会有额外的开销，因为它不学使缓存中相关的数据表失效。这些开销相对比较小，所有查询缓存还是有好处的。

 

 

使用准备语句 （例如 select * from tablename where a=? and b=? ）语句会比执行多次执行查询效率高得多，具体原因如下：服务器只需要解析一次查询 ，这节约了解析和其他的开销；因为服务器缓存了一部分执行计划，所以它只需要执行某些优化步骤一次；通过二进制发送参数比通过 ASCII 码快得多。比如，通过二进制发送 DATE 类型的参数需要 3 个字节，但通过 ASCII 码发送需要 10 个字节。节约的效果都与 BOLB 和 TEXT 类型最为显著，因为它们可以称快地发送，而不是一个个发送。二进制协议也帮客户端节约了内存，同时减少了网络开销和数据从本身的类型转换为非二进制协议的开销；整个查询不会被发送到服务器，只有参数才会被发送，这减少了网络流量； Mysql 直接把参数保存在服务器的缓冲区内，不需要在内存中到处拷贝数据。而且准备语句也减少了 SQL 遭受注入攻击和其他攻击的可能。

 

准备语句的局限：准备语句只针对一个连接 ，所以另外的连接不能使用同样的句柄，一个先断开再重新连接的客户端会丢失句柄（连接池或持续连接会减轻这个问题）。准备语句不能使用 Mysql5.0 以前的版本缓存。使用准备语句并不总是高效的。如果只是用一次准备语句，那么准备它花费的时间可能比执行一次平常的 sql 语句更长 。现在不能使用存储函数使用准备语句，但是可以在存储过程中使用准备语句。如果忘记销毁准备语句，那么就有可能引起资源泄漏。

 

Mysql 对服务器的每个数据库，每个表都有默认的字符集和排序规则。这形成了创建时影响其字符集的默认值的继承关系。例如，当创建一个数据的时候，它从服务器继承了 character_set_server 设置；当创建表的时候，它从数据库继承字符集；当创建列的时候，它从表继承字符集。

 

某些人推荐在所有的地方都使用 UTF-8. 但是在意性能的话，这不是好注意。 UTF-8 使用更多的磁盘空间 。

 

 

7.Mysql 配置

 

Mysql 的配置设置有几种作用域。一些设置在整个服务器内部有效（全局域 ）；另外一些针对每个连接（会话域 ）；还有一些只对对象有效 。许多会话域的变量和全局变量是一样的，可以认为是全局变量提供了默认值。如果修改了会话域变量的值，它只会对当前连接内有效，连接关闭后值就消失了。

 

动态设置变量有出人意料的副作用，比如会清空缓冲区。要注意在线更改的设置，因为它可能会导致服务器做大量的工作。

 

下面来看某些重要的变量及动态地改变它们造成的影响：

 

Key_buffer_size: 设置这个变量给键缓冲区分配制定大小的空间。但是操作系统只有在实际用到这些空间的时候才会进行分配。例如将键缓存区设置为 1GB ，并不意味着服务器就会真正地分给它 1GB 空间。可以创建多个键缓存，把每个索引从特定的缓存移到默认的缓存中。对一个已有的缓存设置非零值将会冲洗缓存。在技术上来说，这是一个在线操作，但是它会阻止所有访问该缓存的动作，知道缓冲区冲洗完成。

 

Table_cache_size ：设置这个变量不会立即生效，要等到下一个线程打开表的时候才会生效。

 

Thread_cache_size ：设置这个变量不会立即生效。

 

设置变量的时候要小心。更大的值并不总是好事情。如果将值设的太高，容易引发许多问题：耗尽内存，导致服务器使用交换区，耗尽地址空间等。

 

可以用下面的方式进行 Mysql 的内存调优： 1. 决定 Mysql 能使用的内存的绝对上限。 2. 决定 Mysql 会为每个连接使用多少内存，比如排序缓冲区和临时表。 3. 决定操作系统需要多少内存来很好地运行自身，包括机器上的其他程序，比如周期性的工作。 4. 假设上面的工作都已完成，就可以把剩余的内存分配给 Mysql 的缓存，比如 InnoDB 的缓存池。

 

对于大部分用户来说，下面的这些缓存是最重要的：操作系统为 MyISAM 的数据提供的缓存； MyISAM 键缓存； InnoDB 缓存池；查询缓存。

 

InnoDB 缓存池也许会比其他的东西需要更多的内存。 InnoDB 缓存池不仅仅保存了索引，它还保存了行数据及子使用的哈希索引，插入缓冲区，锁及其他的内部结构。 InnoDB 也使用了缓冲池帮助延迟写入，这样它就可以合并更多的写入然后顺序地执行他们 。 InnoDB 严重依赖于缓冲池，并且应该给它分配足够的内存。 Mysql 手册建议在专用服务器上把 80% 的物理缓存分配给缓冲区池。