MySQL数据库的多线程引擎

李妍青，陈建，杨秀芝

(福州大学物理与信息工程学院，福建福州 350116)

0 引言

随着互联网业务的快速发展，微博、淘宝网、支付宝等应用日益普及．当数据量达到几十万甚至几千万时，常会出现查询速度慢、用户等待时间长等瓶颈问题．因此，对数据库性能优化显得十分重要［1］．MySQL具有开源、易用、安全可靠等优势［2］，深受许多中小型业务应用者的喜爱．在TechTarget发起的2012年中国数据管理优先度调查中显示，有45．5%的用户表示愿意迁至MySQL数据库平台［3］．

目前，设计方法主要有两种:单线程操作MySQL连接和多线程同时操作一个MySQL连接．这两种方法均具有较大的局限性:前者主线程必须等待数据返回后才能继续执行其他业务逻辑，执行效率低;而后者串行数据过于庞大且多个线程同时竞争一个MySQL连接，响应速度慢．为更好地满足大数据量操作需求，运用分层架构设计理念，并结合连接池、多线程等技术进行优化设计，从而达到提高业务查询效率，便于移植、扩展和维护的作用．

1 基本思路

目前许多数据库开发应用过程中，业务应用层与数据库底层紧密藕合，业务应用层直接调用数据库连接和更新等API函数，这要求用户必须熟悉相关的API函数．为了让MySQL底层操作透明化、实现代码和其它资源的共享、易于更新底层各个模块而不影响MySQL应用业务，采用动态链接库［4］(dynamic link library，简称DLL)方式，简化应用业务逻辑和程序设计管理，具有模块封装特性．高内聚低耦合是判断程序设计好坏的标准．与DLL简单封装方案不同的是，本研究在保持程序内在联系的前提下，通过分层架构设计来分解程序功能模块，降低程序开发的复杂性，增强程序的可重用性、维护性和扩展性．在此基础上，改进数据库连接池并加入数据库缓存层，大大提高响应速度．如图1所示．

图1 分层架构设计方案图Fig．1 Hierarchical design diagram

2 各分层功能

2．1 业务应用层

与用户关系最密切，大部分业务逻辑都在该层操作．用户不用管其他三层内部如何实现，只需要调用PostTask函数，执行SQL语句．例如:查询数据库当前时间戳PostTask(“SELECT UNIX_TIMESTAMP(NOW())ASTime;”，ReturnTime)，回调函数ReturnTime返回时间戳的值．

2．2 数据库连接池

对于大数据量的操作或操作比较频繁的业务，一个数据库连接往往不够，不仅执行性能低，而且响应速度慢．多线程技术［5］结合连接池，可明显提高性能．

现有数据库连接池采用多线程共享多数据库连接，当有线程请求数据库连接时，从空闲队列查找空闲连接分配给该线程［6］．这种情况下，多个线程既要同时竞争同一个连接资源，又要保证连接资源数据的一致性，因此，线程须互斥．这样既会导致请求使用该资源的其它线程必须等待，直到占用此资源的线程释放，还使响应速度大幅度下降，甚至会造成同一条数据库记录更新顺序错乱．比如，当前数据库某表包含A字段(此字段为数值类型)，业务应用层先后执行数据库操作1(更新A字段值为100)和操作2(更新A字段值为200)，则A字段最终值应为200．如果这两个操作被分配到两个不同线程执行，操作2先于操作1执行，则A字段最终值为100，从而出现错误的结果．

在保证性能优越性和结果正确性的前提下，采用一个线程结合一个MySQL连接作为一个连接单元，多个连接单元组成连接池．并行执行中对同一条记录所有操作的任务分派，均通过此记录主键值哈希映射分配给同一个连接单元的线程执行，保证数据库更新操作的有序性．主线程不进行操作结果的解析(除非必须阻塞情况)，而是将操作保存在任务队列后立即返回，继续执行其他业务逻辑，让后台线程进行数据操作与结果解析，再将结果异步返回给主线程．程序结束时调用join()函数以保证所有子线程执行完，避免丢失某些操作而导致数据没有存储．采用boost库的thread类，操作简便．主线程与子线程均会维护任务队列，因此，需保证该队列多线程安全．采用线程构建模块TBB(thread building blocks)库中的concurrent_queue容器［7］，此容器在并行环境中性能优越．

1)创建连接池．调用CreateDBPool接口传入指定线程数和MySQL服务器IP地址等相关信息，创建多线程和进行MySQL连接操作．

IDBPool* CreateDBPool(unsigned char ucQueueCnt，const char*szDBServer，const char*szLogin-Name，const char*szPassword，const char*szDBName，const char*szCharSet=0，unsigned int uiPort=MYSQL_PORT);

2)应用连接池．主线程执行数据库操作时调用PostTask函数，并立即返回．后台线程执行数据库操作后通过DB_RESULT_CALLBACK函数指针返回操作结果．

class IDBPool

{ public:

virtual～IDBPool(){}

virtual bool PostTask(const char*szSQL，DB_RESULT_CALLBACK callback)=0;

};

回收连接池:调用ReleaseDBPool接口回收多线程资源和断开MySQL连接．

2．3 数据库缓存层

主要用于保存数据库操作返回的数据结果．一个查询操作往往返回多条数据记录，一条数据记录包含多个数据字段［8］．该层对查询结果进行解析与缓存处理．其中包括数据集IRecordset、数据记录IRecord和数据字段IField．

1)IRecordset类包含多个IRecord对象，保存数据记录的信息并根据记录索引获取IRecord对象，同时提供查询返回结果的数据记录个数和数据字段个数．

2)IRecord类聚合多个IField对象，保存数据字段信息并根据字段索引或字段名称获取IField对象，其中包括主键字段的获取(通过获取到的IField对象即可获取此字段值)．

3)IField类用于提供常用的类型转换函数和保存字段相关信息，包括MySQL数据类型type、数据字段标志flag、数据长度len、字段名name以及字段数据value．flag主要用来判断数据是否有符号，value记录数据库存储值，C++语言不支持MySQL中的数据类型，因此保存在内存中需根据type对其进行相应转换．转换关系如表1所示．

表1 MySQL与C++数据类型转换Tab．1 Data type conversion between MySQL and C++

2．4 数据库驱动层

位于最底层，主要负责MySQL连接．所有的数据库操作均通过该层调用MySQL连接句柄来执行，包括数据库的连接、查询、结果返回以及MySQL服务器信息的获取等等．应用程序查询前需要调用Connect()接口进行连接处理和设置连接选项等相关初始化操作．查询时调用Execute()接口执行SQL语句后，调用StoreResult()接口保存操作结果．GetErrorMsg()和GetErrorNo()接口返回当前数据库操作相关错误信息和错误码，有助于应用程序查错解决问题．

3 异常处理

3．1 数据库超时

1)如果连接的IP地址不存在或出错，会造成连接超时(一般MySQL默认超时时间为20 s)．该情况下若依旧连接成功，且ping也执行成功，但是操作数据库时却无响应．处理方式:设置只要超时10 s，就认为连接失败，不允许执行下一步操作．

2)如果MySQL连接很久(默认为8 h)没有数据投送过来，就会自动断开连接．处理方式:将INTERACTIVE_TIMEOUT和WAIT_TIMEOUT设置为最大值(延长连接超时时间，以避免短时间断开再连接处理带来额外的时间开销)，并启用断开自动重连机制．

3．2 数据库连接断开

MySQL服务关闭或自动断开客户连接会导致投送数据操作失败．为了保证数据操作投送的顺序(不乱序)，采用间隔一段时间重试，直到此次数据操作成功为止，才能继续下一步数据操作．需要一直重试的情况如下:

错误码1317 //Query execution was interrupted

错误码 2003 //Can＇t connect to MySQL server on‘db’(10061)

错误码2006 //MySQL server has gone away

错误码2013 //Lost Connection During Query

4 性能测试

1)通过VS2010性能分析工具，对每隔1 ms执行一次数据库操作的CPU使用率进行平均采样，单线程和本方案的CPU平均使用情况如图2和图3所示．

图2 单线程CPU平均使用率Fig．2 Single thread CPU utilization on average

图3 本方案CPU平均使用率Fig．3 Average CPU utilization in this thesis proposal

对比图2和图3可知，两种方案在前期数据库初始化和后期资源回收处理阶段占用的CPU使用率都较大．单线程方案中，数据库操作执行过程由于串行数据较多，且要执行SQL语句和解析结果导致长时间竞争CPU资源．因此，CPU使用率波动较大，大概在20% ～40%之间．而本方案均衡执行SQL和解析结果操作，不会长时间竞争CPU资源，因此CPU使用率较平稳，基本维持在20%以内．

2)对1万次数据库查询操作，每次查询返回10条记录的执行时间进行比较．实验结果见表2．

①比较实验1～4可知，线程数相同时，有连接池比无连接池(即连接数为1)执行时间少、响应速度快;而连接数相同时，多线程数据库操作明显比单线程执行时间少．

②由表2可知，多线程数据库操作明显比单线程响应速度快．多线程情况下线程数越多，执行时间会稍微增多，但不会相差太大．因为线程越多时，各线程相互竞争CPU时间片造成挂起的次数增多，导致额外消耗了时间．但是，考虑到每个线程的负载与指定次操作响应速度问题，大规模数据库操作仍应优先选择较多的线程．假设一次查询开销1 ms，在1万次查询情况下，2个线程平均分到5 000次查询，第5 000次查询结果返回需要5 s;而4个线程时平均会分到2 500次查询，第5 000次查询结果返回只需要2．5 s．第5 000次查询响应速度明显快，所以，选择较多线程会较优越(当然也不是线程越多越好，具体线程数视操作规模而定)．

表2 线程数、连接数与执行时间关系Tab．2 The relationship between the number of threads，the number of connections and the execution time

5 结语

采用分层架构设计理念对数据库引擎进行设计与分析，结合连接池、多线程等技术，利用VS2010软件进行编程与测试．测试结果表明，该数据库引擎设计基本达到预期目标．将其引入自主研发的机顶盒中效果良好，明显提高了执行效率．该设计在其它数据库领域的实际应用中同样具有重要意义．

［1］谷伟，陈莲君．基于MySql的查询优化技术研究［J］．微型电脑应用，2013，30(7):48－50．

［2］Axmark D，Larsson A，Widenius M，et al．MySql 5．0 reference manual［M］．Sweden:MySqlAB，2006．

［3］吴沧舟，兰逸正，张辉．基于MySQL数据库的优化［J］．电子科技，2013，26(9):182－184．

［4］伊翠香，孙玲玲，张富强．动态链接库DLL编程的相关应用技术与探讨［J］．试验技术与试验机，2008，48(1):55－58．

［5］葛亮．零点起飞学编程零点起飞学Visual C++［M］．北京:清华大学出版社，2013．

［6］刘菲，游达章．基于Java的数据库连接池的设计与优化［J］．微型电脑应用，2008，24(10):7－9．

［7］郑晓薇，张建强．基于TBB任务调度器的N皇后多核并行算法［J］．计算机工程与设计，2010，31(15):3 423－3 426．

［8］姜承尧．MySQL技术内幕:InnoDB存储引擎［M］．2版．北京:机械工业出版社，2013．