高性能Mysql

文章基于mysql5.5

一 mysql架构与历史

1.1 mysql逻辑架构

下图为mysql各组件之间协同工作的架构图
mysql逻辑架构图

  1. 最上层不是mysql特有的，大多数基于网络的客户端/服务器的工具或者服务都有类似的架构。比如连接处理、授权认证、安全等等。
  2. 第二层为大多数mysql核心服务所在的位置，包括解析、分析、优化、缓存及所有内置函数、所有跨存储引擎的功能（存储过程、触发器、视图等）都在这一层实现。
  3. 第三次包含了存储引擎，负责数据都存储与提取。

1.1.1 连接管理与安全性

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

1.1.2 优化与执行

mysql会解析查询，并创建内部数据结构（解析树），然后对其进行优化。包括重新给查询、决定表的读取顺以及选择合适的索引等。

对于select语句在解析查询之前，服务器会先检查查询缓存如果能能在其中找到对应的查询，服务器就不必在执行查询解析。

1.2 并发控制

无论何时，只要有多个查询需要在同一时刻修改数据，都会产生并发控制的问题。下面将从2个方面在说明并发控制：服务器层与存储引擎层。

1.2.1 读写锁

读取数据的时候，即使同一时刻有多个用户读取数据也不会有什么问题，因为此时数据没有发生改变，用户读到到数据都是同一份。

但是如果在读取到时候另一个用户试图修改数据，就有可能读错或者读取到数据不一致到情况。

解决这类问题到方法就是并发控制，在处理并发读或者写时，可以通过实现由2种类型的锁组成的锁系统来解决问题。也就是俗称的共享锁与排他锁，也叫做读锁或者写锁。读锁是共享的，或者说是互不阻塞的，多个用户可以在统一时刻读取‍同一资源而且互不干扰。写锁是排他的，也就是说一个写锁会阻塞其他的写锁或者读锁，确保在给定时间内只有一个用户能执行写入操作，防止其他用户读取正在写入的其他资源，这也是处于安全策略的考虑。

1.2.2 锁粒度

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

问题是加锁也需要消耗资源。锁的各种操作，包括获得锁，检查锁是否以及接触、释放锁等都会增加系统的开销。如果花费大量时间来管理锁而不是存取数据，那么系统的性能也会因此受到影响。

所谓锁策略，就是在锁开销与数据安全性之前平衡，这种平衡也会影响到性能，这是不可避免的。大多数都是在表上加行级锁，并以各种复杂的方式来实现，以便在锁较多的情况下尽可能地提供更好的性能。

在mysql中每种存储引擎都可以实现自己的锁策略和锁粒度。将锁粒度固定在某个级别可以更好为某些特定场景提供性能支持，但同时也会失去对另一些应用场景对良好支持。

1.2.3 表锁

表锁是mysql最基本的锁策略，并且是开销最小的锁策略。他的工作思路是会锁住整张表，一个用户对表进行插入、更新、删除操作需要先获得锁，这回阻塞娶她用户对这张表的读写操作。只要没有写锁时，其他用户才能获得读锁，读锁之前是不互相阻塞的。

在某些特定的场景下，表锁也有良好的性能。比如READ LOCAL支持某些类型的并发写操作。另外，写锁也比读锁拥有更高的优先级，因此一个写锁请求可能会被插入到读锁请求队列前面，但是反之则不能。

1.2.4 行级锁

行级锁能够最大程度的支持并发处理，但是也会带来最大的锁开销。行级锁只能子啊存储引擎中实现。

1.3 事务

ACID

一个运行良好的事务处理系统，必须满足下列4点：原子性、一致性、隔离性、持久性。

  • 原子性 ：

    • 一个事务必须被视为不可分割的最小工作单元，整个事务中的所有操作要么全部执行成功，要么全部失败回滚。对于一个事务来说，不可能只执行其中的某一部分。

  • 一致性：

    • 数据库总是从一个一致性状态转换到另外一个一致性状态。

  • 隔离性：

    • 通常来说，一个事务所做的修改在最终提交以前对其他事务来说是不可见的。但是数据库对隔离级别会影响这个操作。

  • 持久性：

    • 一旦事务提交，其所做对修改就会永久保存到数据库中。即使此时系统崩溃，修改的数据也不会丢失。

1.3.1 事务隔离级别

  1. 未提交读（READ UNCOMMITED）

    在此级别事务的修改即使没有提交，对其他事务也是可见的。事务可以读到未提交到数据，这也被称为脏读。这个级别会导致很多问题，但性能也不会比别的级别好太多，但却缺乏别的级别的好处，所以一般条件下不会使用。

  2. 读以提交（READ COMMITED）

    一个事务从开始知道提交之前，所做的任何修改对其他事务都是不可见的。这个级别也叫做不可重复读。因为两次执行相同的查询，可能读到到结果不一样。

  3. 可重复度（REPEATABLE READ）

    可重复度解决了脏读的问题，该级别保证了在同一事物多次读取同样的记录的结果是一致的。但是可重复读还是无法解决另外一个问题--幻读。所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的纪录，当之前的事务再次读取该范围的记录时，就会产生幻行。INnoDb和XtraDB存储引擎通过多版本并发控制MVCC解决了幻读的问题。

  4. 可串行化（SERIALIZABLE）

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

1.3.2 死锁

死锁是指两个或者多个事务在统一资源上相互占用，并请求锁定对方占的资源，从而导致恶心循环的现象。当多个事务视图以不同的顺序锁定资源时，就坑会产生死锁。多个事务同时锁定同一个资源时，也会产生死锁。

为了解决这种问题，数据库系统实现了各种死锁检测和死锁超时机制。还有一个解决方式，就是当查询的时候达到锁等待超时的设定后放弃锁请求，这种方式通常来说不太好。InnoDB目前处理死锁的方式是，将持有最少行级排他锁的事务进行回滚。

多版本并发控制

Mysql的大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑。他们一般都同时实现了多版本并发控制（MVCC）。

可以认为MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制有所不同，但大都实现了非阻塞但读操作，写操作也只锁定必要但行。

MVCC的实现是通过保存数据在某个时间点的快照来实现的。也就是说，不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

不同的存储引擎的MVCC实现是不同的，典型的又乐观并发控制和悲观并发控制。

InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现的。这两个列一个保存了行的创建时间，一个保存行的过期时间。当然保存的并不是实际的时间值，而是系统的版本号。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

下面看一下Repeatable read隔离级别下MVCC具体是如何操作的。

SELECT

InnoDB会根据以下两个条件检查每行记录：
a：InnoDB指查找版本早于当前事务的数据行，这样可以确保事务读取的行要么是在事务开始前就已经存在的，要么就是事务自身插入或者修改过的。
b：行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。
只有复核上述两个条件的记录才能返回作为查询结果。

INSERT

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

DELETE

InnoDB为删除的每一行保存当前系统版本号作为行删除标识。

UPDATE

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

保存这两个额外系统版本号，是大多数读操作都可以不用加锁。这是设计使得读数据操作很简单，性能很好
并且也能保证只会读取到复核标准的行。不足指出是每行记录都需要额外的存储空间，需要做更多的行检查工作以及一些额外的维护工作。

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

1.5 Mysql的存储引擎

在文件系统中，Mysql将每个数据库（也可以称之为Schema）保存为数据目录下的一个子目录。创建表时，Mysql会在数据哭子目录下创建一个和表同名的.frm文件保存表的定义。可以使用SHOW TABLE STATUS命令显示表的相关信息。

1.5.1 InnoDB存储引擎

InnoDB是mysql的默认事务型引擎，也是最重要使用最广泛的存储引擎。它被设计用来处理大量的短期事务，短期事务大部分情况是正常提交的。很少会被回滚。InnoDB的性能和自动崩溃恢复特性，使得它在非事务型存取的需求中也很流行。

InnoDB的数据存储在表空间中，表空间是由InnoDB管理的一个黑盒子，由一系列的数据文件组成。InnoDB可以将每个表的数据和索引存放在单独的文件中。

InnoDB采用MVCC来支持高并发，并且实现来四个标准的隔离级别。其默认级别是PEPEABTABLE READ（可重复度），并且通过间隙锁策略防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。

InnoDB是基于聚簇索引建立的，聚簇索引对主键查询有很高的性能。不过它的二级索引（非主键索引）中必须包含主键列，索引主键列很大的话，其他的所有索引都会很大，所以标上的所以比较多的话，主键应当尽可能的小。

二 创建高性能索引

索引是存储引擎用于快速找到记录的一种数据结构。

2.1 索引基础

索引可以包含一个或者多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为mysql只能高校地使用索引的最左前缀列。

索引的类型

B-Tree索引

它使用B-tree数据结构来存储数据，大多数mysql引擎都支持这种索引。

B-tree通常意味着所有的值是按顺序存储的，并且每一个叶子页到根到距离相同。下图为建立在B-tree结构上到索引：

B-tree索引能够加快范根数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根结点开始进行搜索。根结点的槽中存放来指向自节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值看可以找到合适的指针进入下层子节点，这些指针实际上定义来子节点页中值的上限和下限。最终存取引擎要么是找到对应的值，要么该记录不存在。

叶子节点比较特别，他们的指针指向的是被索引的数据，而不是其他的节点页。树的深度和表的大小之间相关。

B-tree对索引列是顺序组织存储的，所以很适合查找范围数据。例如，在一个基于文本域的索引树上，按字母顺序传递连续的值进行查找是非常合适的。

可以使用B-tree索引的查询类型。B-tree索引适用于全键值、键值范围活键前缀查询。其中键前缀查找只适用于根据最左前缀的查找。前面所述的索引对如下类型的查询有效。

全值匹配
全值匹配 指的是和索引中所有列进行匹配。

匹配最左前缀
只使用索引第一列进行匹配。

匹配列前缀
页可以指匹配某一列的值的开头部分， 例如匹配姓名为谭开头的人。

匹配范围值
匹配在范围之间的人，只使用索引的第一列。

精确匹配某一列并范围匹配另外一列
例如查询所有姓为jack而且名字是字母k开头的人。即第一列全匹配，第二轮范围匹配。

只访问索引的查询
即查询只需要访问索引而无需访问数据行。

因为索引树的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的ORDER BY操作。一般来说，如果B-Tree可以按照某种方式查找到值，那么页可以按照这种方式用于排序。所以如果BRDER BY子句满足前面列出的集中查询类型，则这个索引也可以满足对应的排序需求。

下面是一些关于B-Tree索引的限制：

  • 如果不是按照索引的最左列开始查询，则无法使用索引。
  • 不能跳过索引中的列。
  • 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。

哈希索引
哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对搜有的索引列计算一个哈希码，哈希码是一个比较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

实际上，使用哈希索引的引擎只有Memory。所以这边不过多赘述。

空间数据索引R-Tree

MyISAM表支持空间索引，可以用作地理数据存储。和B-Tree索引不同，这类索引无需前缀查询。空间索引会从所有纬度来索引数据。查询时，可以有效地使用任意维度来组合查询。

全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是之间比较索引中的值。全文搜索和其他的几类索引的匹配方式完全不一样。它有许多需要注意的细节，如停用词、词干和附属、布尔搜索等。全文索引更类似于搜索引擎做的失去，而不是简单的where条件匹配。

其他索引类别

还有很多第三方的存取引擎使用不同的数据结构累存储索引。例如TokuDB使用分形树索引，它既有b-
tree的很多有点也避免来b-tree的一些缺点。

2.2 索引的优点

索引可以让服务器快速地定位到表到指定位置。但是这并不少索引到唯一作用，到目前为止可以看到，根据创建索引到数据结构不同，索引也有一些其他到附加作用。

最常见到B-tree索引，按照顺序存储数据，所以mysql可以用来做Order by 和Group by操作。因为数据是有序到，所以B-Tree也就会将相关到列值都存储在一起，最后，因为索引中存储都实际都列值，所以某些查询只使用索引就能够完成全部查询，根据此特性，总结下来索引有如下三个优点：

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

索引并不总是最好的工具。总的来说，只有当索引帮助存储引擎快速查找到记录带来带好处大于其带来带额外工作时，索引才是有效带。对于非常小的表，大部分情况下简单的全表扫描更高效。对于中到大型到表，索引就非常有效。但对于特大型但表，建立和使用索引但代价将随之增长。这种情况下，则需要一种技术可以直接区分出查询需要的一组数据，而不是一条记录一条记录的匹配。例如可以使用分区技术。如果表的数量特别多，可以建立一个元数据信息表，用来查询需要用到的某些特性。例如指向那些需要聚合多个应用分布在多个表的数据的查询，则需要记录哪个用户的信息存储在哪个表中的元数据，这样查询时就可以直接忽略那些不包含指定用户信息的表。对于TB级别的数据，定位单条记录的意义不大，索引经常回使用块级别元数据技术来代替索引。

2.3 高性能的索引策略

正确地场景和使用索引是实现高性能查询地基础。

2.3.1 独立地列

独如果查询中的列不是独立的，那么Mysql就不会使用索引。立的列是值索引列不能是表达式的一部分，也不能是函数的参数。

例如，下面这个查询无法泗洪actor_id列的索引

select actor_id from actor where actor_id + 1 = 5 ;

凭开发者是可以知道where表达式其实等价于actor_id = 4，但是mysql无法字段解析这个方程式。这完全就是用户行为，所以我们应该始终将所以列单独放在比较符号的一侧。

下面的例子也是如此：

select ... where TO_DAYS(current_date) - TO_DAYS(date_col) <= 10 ;

2.3.2 前缀索引和索引选择性

有时候需要索引很长的字符列，这会让索引变得大且慢。一个策略就是模拟哈希索引。但是这还不够。

通常可以索引开始的部分字符，这样可以节约索引空间，从而提高索引效率。但是这样也会降低索引的选择性。

索引的选择性是指，不重复的索引值（也称为基数）和数据表的记录总数（#T）的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让mysql在查询时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

一般情况下某个列前缀的选择性也是足够高的，足以满足查询性能。但是对于BLOB、TEXT或者很长但VARCHAR类型但列必须使用前缀索引，因为mysql不允许索引这些列完整长度。

诀窍在于要选择足够长但前缀以保证较高的选择性，同时又不能太长（节约空间）。前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。换句话说，前缀的基数应该接近于完整列的基数。

一条小咸鱼