高性能MySQL-初探


高性能MySQL-初探

MySQL数据库作为Service对外提供服务,使用MySQL的基本流程是:client->MySQL。其中client有很多,包括mysql命令行、mysql_pdo等。我们在client中输入SQL并发送给MySQL服务器,当MySQL接收到发送过来的SQL后,经过一系列的处理,返回查询的结果。其中一系列的处理包括两部分,一部分是MySQL的动作,包括查询解析、分析、优化、缓存以及所有的内置函数处理,所有跨存储引擎的功能(存储过程、触发器、视图等)都是MySQL实现的;还有一部分是存储引擎的动作。存储引擎负责对数据进行存取。因为MySQL有很多种存储引擎,MySQl通过API与存储引擎交互,这使得存储引擎间的差异对上层查询是透明的。因为API的存在,每种存储引擎的实现方式都各不相同,每种存储引擎都有它的优势和劣势,这些我们在后面的文章中再进行讨论。

MySQl的SQL执行过程大致如下

  1. client连接到MySQL;
  2. MySQl为每个客户端的连接维护一个线程,这个连接的查询只会在这个单独的线程中进行;

    >虽然每个客户端都是在单独的线程中独立进行工作,但是并不意味着客户端断开后就会销毁线程,MySQL会维护一个线程池缓存线程。
    
  3. MySQL解析SQL并创建内部数据结构(解析树),然后对其进行优化,在解析SQL之前,MySQL会优先检查查询缓存(Query Cache),如果能够在缓存中找到对应的查询,就不会继续进行动作,而是直接返回缓存的结果集;

    >这里需要注意的是,虽然优化器不关心底层使用的是什么存储引擎,但存储引擎对于优化器优化的查询是有影响的(选择合适的存储引擎往往会使优化器更好的工作),优化器可能会需要存储引擎提供容量或者某个具体操作的开销,例如决定表的读取顺序以及选择合适的索引等。我们可以通过`explain`查询优化器优化的各个过程,也可以通过特殊的关键字`hint`影响它的决策过程;
    
  4. MySQL把优化过的解析树,通过API发送到存储引擎层,存储引擎根据自己的实现,查找(修改)对应的数据行,并将结果返回到MySQL层;
  5. MySQL层把结果返回给客户端。

以上是SQL查询的整个过程,我们必须了解MySQL的工作原理,才能成就高性能的查询。但刚才我们只是简单的描述了SQL的整个生命周期,其中的一些关键的细节并没有指出,例如并发控制,锁,事物等,上面只是为了让读者对MySQL的工作原理有一个初步的了解。

基本概念

现在这里声明一下,如果不指明存储引擎时,默认的存储引擎是InnoDB。

因为MySQL是多线程模式,所以当多个线程会同时操作同一行数据时,不加以控制会有意想不到的结果。锁机制的引入就是为了解决这一问题,也成为并发控制。在MySQL有读锁和写锁两种锁。

  • 读锁(共享锁)

读锁也被叫做共享锁。当某个线程需要读取某行数据时,需要对读取的行加上读锁,如果能成功的加上锁就证明可以读取此行数据。因为读操作实际上是没有竞争的,你先读,我先读,大家一起读,结果都是一定的,所以同一行数据可以被施加多个读锁,所以我们管读锁也叫共享锁。

  • 写锁(排他锁)

写锁也被叫做排他锁。当某个线程需要修改某行数据时,必须先获得数据行的写锁才能进行动作,不然就只能等待(等待的结果无非两种,获得锁,死锁)。同一行数据只会被一个进程加上写锁,所以写锁是相互对立的,这是为什么叫做排他锁的原因,这里需要注意的是,写锁和读锁也是独立的。

锁是存在优先级的:写锁>读锁。MySQl会维护一个队列,这个队列中是线程对于数据行的加锁顺序,因为写锁的优先级大于读锁,所有即便队列中存在先来的读锁,只要锁还没有被操作,后来的写锁有优先放置到读锁前面。

锁的颗粒度

锁这一操作是存储引擎层的操作(表锁也可能是MySQL层的操作),所以具体的锁的实现,依赖于存储引擎,并不依赖于MySQL。加锁是一件不怎么讨人喜欢的操作,锁的存在,就会存在锁的竞争,这回导致并发程度降低,而且加锁的操作会有服务器的开销,这也会导致查询性能的下降。为了解决这些问题,不同的存储引擎使用了不同的策略,这些策略没有最优解,都是一种平衡考量后的结果,施加锁的目标不同,开销不一样,并发程度也不同。

  • 表锁

表锁是MySQL中最基本的锁策略,并且是开销最小的策略。表锁一次锁定一整张表,读锁是相互不阻塞的,但是会和写锁竞争,所有表锁拥有最小的锁开销,但是并发程度并不是很好。在特定的场景中,表锁也可能有良好的性能,例如某一张表只会存在读操作,不会存在写操作,这是表锁是最好的选择。

  • 行锁

行锁可以最大程度地支持并发处理,同时也带来了最大的开销。InnoDB和XtraDB以及一些其他引擎实现了行级锁。

所以综合考量自己的应用程序,选择适合自己的锁颗粒度。鱼和熊掌不可兼得。

事务

同一个连接中的SQL是顺序执行的,但有很多的时候,应用程序对于数据最终的结果不是一条SQL能完成的,需要多条SQL顺序执行。有些情况后面的SQL会依赖于前面的SQL的结果。但是SQL执行并不是百分之百成功。我们似乎需要一种这样的情况,我要进行一系列的数据操作,并希望一系列的操作的最终结果:是要么数据全部操作正确,要么不进行任何的数据改变。这里就需要引入事务这个概念:事物是一组原子性的SQL查询。事务的概念并不是MySQL独有的,很多关系型数据库都有事务的概念。

  • 事务的ACID

判定某个数据库是否支持事务,需要对数据库进行严格的ACID测试。

  • A-原子性(atomicity):一个事务必须被视为一个不可分割的最小工作单元,整个事务中的所有操作要么全部成功,要么全部失败。
  • C-一致性(consistency):数据库总是从一个一致性的状态转换到另外一个一致性的状态。
  • I-隔离性(isolation):通常来说,一个事务所做出的修改在最终提交之前,对其他事务是不可见的。
  • D-持久性(durability):一旦事务提交,则其所做的修改就会永久保存到数据库中(但这其实不是100%准确的描述)。

隔离级别

隔离级别是一个比较复杂的概念。在SQL的标准中定义了四种个隔离级别:未提交读(read uncommitted)提交读(read committed)可重复读(repeatable read)可串行化(serializable)。具体的隔离级别随着数据库不同的表现和期望可能不太一样,应当充分了解你所使用的数据库对于此部分的介绍。

  • 未提交读:是指事务中的修改即使没有提交,对其他事物也是可见的。这时其他事物读取的数据被称为脏数据,这种现象被称为脏读。为了解决脏读的问题有了第二种事务隔离级别--提交读。
  • 可重复读:为了解决脏读现象,可重复读只会读取已经已经提交的事务所修改的数据,但是这会造成幻读--幻读是指当前事务在事务中读取到了刚刚被提交事务的修改。为了解决这个问题InnoDB引擎通过多版本并发控制(MVCC)解决了幻读的问题。

    > 可重复读是MySQL的默认事务隔离级别,这点与其他数据库有所不同。大部分应用使用这一隔离级别就够了。
    
  • 提交读:提交读有时也被称为不可重复度,是指当前事务所作的修改在没有提交之前,所做的任何修改对其他事务是不可见的。换句话说一个事务中能读到的数据都是已经提交的事务修改的数据。提交读解决了脏读和幻读的问题,这也是大多数数据库的默认事务隔离级别(MySQL不是)。
  • 可串行化:这是所有事物隔离级别中最高的级别。可串行化强制事物串行执行,所以可能导致大量的超时和锁竞争问题。如果不是非常需要确保数据的一致性等一般不会选择这个事物隔离级别。

死锁

死锁是指两个以上的事物在同一资源上互相占用,并请求锁定对方占用的资源,从而导致的恶行循环的现象。举个简单的例子:两个事物(a,b)要做同样的事情把1->2,2->1,事物a先把2->1,事物b先把1-2,因为事物a在2->1这个操作的时候已经对2上了写锁(锁的释放要在整个事务提交之后,而不是操作结束)。当事务b想要去2->1的时候,必须先获取2的写锁(此时写锁在事务a下面),而此时事务a想要1->2(此时写锁在事务b的下面),事务ab都要等待对方释放锁才能提交事务,但彼此都不能提交事务,这种恶性循环被称为死锁。

在两个session中交替执行如下SQL就会产生死锁:

事务1:

START TRANSACTION;
UPDATE test.test SET num = 2 WHERE id = 1;
UPDATE test.test SET num = 1 WHERE id = 2;
COMMIT;

事务2:

START TRANSACTION;
UPDATE test.test SET num = 1 WHERE id = 2;
UPDATE test.test SET num = 2 WHERE id = 1;
COMMIT;

不同数据库对于处理死锁的方式不尽相同。基本上死锁是没有办法避免的,InnoDB目前处理死锁的方法是,将优先级较低(持有最少行级写锁)的事务回滚。

声明:Iamzz|版权所有,违者必究|如未注明,均为原创|本网站采用BY-NC-SA协议进行授权

转载:转载请注明原文链接 - 高性能MySQL-初探


人生如逆旅,我亦是行人。