大数据环境下的MySQL优化技术探讨

2018-11-20韦美雁段华斌周新林

现代计算机 2018年30期

韦美雁，段华斌，周新林

（1.湖南科技学院电子与信息工程学院，永州 425199；2.湖南祁阳大忠桥第二中学，永州 426100）

0 引言

MySQL数据库是目前互联网公司最常用的数据库，因为其具有开源、高性能、跨平台支持、简单易用、支持多用户等特点而被广大用户喜欢。然而在互联网时代，各类数据量井喷，面对高并发、高访问量的情况，数据库研发人员和DBA对数据库的优化便显得尤为重要。

1 MySQL优化策略

为了提高数据库的效率，我们需要考虑实际的应用环境，不同的应用环境下，我们选择的优化措施会有不同的特点。

1.1 存储引擎的选择

存储引擎是MySQL的概念，选择存储引擎其实就是为数据存储选择合适的存储机制和相应的功能机制，MySQL常用的存储引擎有InnoDB、MyISAM、Memo⁃ry、Merge[1，2]等。它们的性能特点详见表1常见引擎性能比较。

（1）InnoDB

InnoDB存储引擎是目前MySQL唯一拥有事务控制能力的存储引擎、除了外键这一特点，从MySQL 5.6开始不仅支持全文索引，同时作为系统默认的存储引擎存在。其优点在于能够进行事务处理，具有一定的并发控制能力以及系统崩溃时的修复能力，相对于MyISAM而言，它的读写效率相对较低，占用数据空间相对较大[3]。

表1 常见引擎性能比较

（2）Memory

Memory，支持hash索引，访问速度快，并且因为它的数据保留在内存中，容易因为服务关闭（或重启）而丢失，并且无法对它进行恢复。因此，数据的安全性很低。

（3）MyISAM

在MySQL 5.5之前，MyISAM存储引擎是默认的存储引擎，它支持3种存储格式，分别是静态类型、动态类型和压缩类型的，系统默认为静态类型，此时，字段长度固定；对于动态类型的其包含变长字段；压缩类型则需要使用myisampack工具。

该类存储引擎的特点是占用空间小，处理速度较快，但是不能进行事务处理。对表操作时以读和插入为主。

（4）Merge

Merge存储引擎表是由一组MyISAM表组成的逻辑结构，但是，这些表的结构要求完全相同。它可作为一个对象被使用，类似于视图，本身也不存储数据。

我们在建立数据表时需要根据应用特点去选择合适的存储引擎，当应用是以插入、读操作为主，且对事务的ACID特性没有要求时，选择MyISAM存储引擎比较合适；如果要数据量大于单个MyISAM的大小，选择Merge，它将不同的表分布在数个不同的磁盘上，从而改善Merge表的读写效率；如果对事务ACID要求较高，对数据的操作不仅有查询和插入，还有更新、删除等，那么应该选取InnoDB存储引擎；如果要求读写速度很快，且对数据的安全性要求较低，同时数据表比较小，那么可以选择Memory。

1.2 SQL优化

（1）开发期的优化

在应用开发初期，开发人员考虑更多的是功能的实现，然而在产品投入使用后，随着数据量的激增，一些SQL语句可能会成为整个系统的性能瓶颈，因此，在产品上线前，作为开发者尽量能够对SQL语句优化处理，避免一些可以预知的问题的产生。

①避免进行全表扫描

为了查询方便，我们往往会在在where子句和or⁃der by子句相关的列上创建索引。理想的情况是，查询时直接使用索引从而提高效率。但是，会有如下几种情况[4]导致系统放弃索引不用而进行全表扫描的现象，这样势必会降低系统的查询性能。

●where子句中对null进行判断。

如出现select sname from s where sdept is null，那么会出现全表扫描的现象，为了避免全表扫描，建议可以先对sdept进行默认数据设置，确保查询列中没有null。

●where子句中使用了!=或者＜>操作符。

●where子句中使用了or连接运算符。

where子句中使用or运算符时容易导致全表扫描发生，此时可以使用union all进行连接从而避免全表扫描现象。假设我们从学生选课系统中选择计算机系和数学系的学生时有命令A：select sname from s where sdept='cs'or sdept='ma';命令 B:select sname from s where sdept='cs'union all select sname where sdept='ma';两种表达，执行命令A会进行全表扫描，执行命令B会进行索引查询（前提是以sdept为关键字创建了索引。）

●in，not in也会导致全表扫描，尽量避免使用，如果可以用between解决，就不用in。

●where子句中使用参数。

如：select sname from s where sdept=@sdept。此时，可以改为使用强制查询使用索引命令：select sname from s with(index(索引名))where sdept=@sdept。

●like模糊查询时字符串首字符为不确定值。

●在where子句中对属性列进行表达式操作。

例如：select sname from s where substring(sdept,1,2)='ma';可改为 select sname from s where sdept like'ma%';又如：select bookid,bookname from books where price/2＜30;改写为 select bookid,bookname from books where price＜30*2;

总之，对属性进行求库函数或者其他的表达式操作会导致表扫描，建议where子句的表达式左端只有属性名不带任何其他的附加操作，把操作都置于比较运算符的右边为好。

②临时表的使用

●使用临时表会占用较多的系统资源。若不断的创建删除，会降低系统的性能，因此，我们对临时表的使用要注意节制。

●临时表使用完成后，首先truncate显式清除表中的所有数据，再drop表结构，以免占用空间同时可能被长时间锁定。

③其他

●避免大事务操作，提高系统并发能力；

●某操作导致向客户端返回数据量极大，需要重新审视应用需求是否合理；

●游标操作数据量比较大（超过1万）时，建议改用其他方式完成，因为游标效率低；

●建立聚簇索引时要注意考虑索引列是否会常被更新，如果更新频繁，则该列不适合创建聚簇索引。

（2）上线后的SQL优化

如果应用系统已经上线，那么对SQL优化就需要进行性能瓶颈定位，通常我们会首先使用show status确定服务器状态，初步了解应用是以查询为主还是以更新为主等情况；其次，慢查询日志可以对低效的SQL进行定位，而explain可以了解低效SQL执行计划，show profile分析资源消耗、trace优化器等一系列操作来确定瓶颈所在。步骤[5，6]如下：

①show status命令

show status展示的参数有356条（5.7版本），其中以com开头的参数记录了各种数据库的操作（创建、修改、删除等）数，如 com_insert，执行 insert的次数（批量插入的insert操作，计数一次）。如果是InnoDB开头的参数，那么则只针对InnoDB存储引擎表，这些参数记录了缓冲区、读写等状态。

我们还可以通过show status like来获取具体的某类或者某个参数的状态信息。例如：show status like'InnoDB_ROW%';从而更有目的地了解系统的状态。

②定位SQL执行效率低的语句

定位SQL低效的语句可以通过慢查询日志或者show processlist两种方法。其中使用show processlist命令后显示信息如图1。各参数含义如表2。表2中的state的状态有26种之多，它是一个非常重要的参数，通过该参数状态的表达，我们可以了解SQL正在检查数据表或者被锁或者正在做排序，等等，从而，我们可以分析出低效SQL。

③explain分析低效SQL执行计划[5，6]

当我们打开慢查询捕捉到执行效率差的SQL，此时我们还需要知道该SQL的执行计划，例如是全表扫描，还是索引扫描，这些都需要通过explain去完成。explain命令是查看优化器决定执行查询的方法，该命令有助于理解MySQL的优化器计划决策。

图1 show processlist命令

④show profile、trace

show profile命令是展示刚执行过的SQL语句的持续使用资源的时间，show profiles则会把命令show warning使用资源的时间也列出来。如果前面执行了数条SQL语句，而此时想查看指定的语句的资源使用情况，则可以根据show profile命令展示的query_ID来进行，例如前面执行了5条SQL命令（show profile之类的除外），编号query_ID从1到5代表相对应的SQL命令，我们可以使用命令show profile for query编号查看具体的SQL的资源使用情况。show profile后面还可以直接跟type for query编号，此时的type可以是all、block IO、context switches、CPU、memory等特定的资源，从而更准确地了解SQL的资源使用情况。

另外，我们也可以使用trace优化器来了解跟踪SQL语句。打开trace，设置格式为JSON，接着运行想跟踪的 SQL语句，查询 information_schema.optimiz⁃er_trace表，跟踪文件会展示优化器逻辑优化和物理优化全过程，并据此确定选择执行计划。

表2 show processlist的参数及含义

⑤确定响应优化措施

由上述步骤可以了解问题所在，采取相应措施，进行优化，提高执行效率。

1.3 表的优化

（1）表的数据类型优化

MySQL支持的数据类型很多，要获取高性能数据库，选择正确的数据类型起着及其重要的作用。我们在选择数据类型时要遵循2个原则[9,10]：

●更小原则。这里的小，指的是存储大小，例如使用tinyint就可以存储数据，就不要再选用int来进行存储数据。在能够保证数据正确存储的前提下，我们选择更小的数据类型。因为节约资源（无论是CPU缓存还是磁盘亦或是内存，甚至于系统处理数据对CPU的需求周期），并且读写速度更高效。

●简单原则。整数类型小于字符类型，这是由于字符集和排序规则导致字符类型数据更复杂。MySQL内建类型（timestamp，date）优于使用字符串保存。简单数据类型的操作通常需要更少的CPU周期。

如果表已经创建好，属于上线使用的情况，那么，想优化表的数据类型，可以通过函数procedure analyse()对当前应用的表进行分析，函数procedure analyse()可还以给对数据表中的列的数据类型提出优化建议。当然，还需要用户最终自己确定是否采纳。

（2）表的拆分

预测到单表数据未来会一直不断上涨，整型表数据达千万级，字符串为主的表达500万级的情况下，可以考虑拆分表，但是拆分不作为首选技术，因为拆分会带来逻辑、部署、运维的各种复杂度。常用的拆分方法有两种：垂直拆分和水平拆分[10,11]。

①垂直拆分

垂直拆分是把表中的属性按照常用和不常用两部分进行区分，然后将主码和常用的属性列部分放到一个表中，主码和另外的不常用的属性列放到另外一个表中。这样一个表被分成两个表，表的数量增加，但是常用的部分属性列在一起，使用效率得以提高，而且降低计算机服务器的缓存等资源的占有率。

②水平拆分

水平拆分目的是通过某种策略（例如一列或者多列的列值）将数据分片来存储。水平拆分有库内分表和分库两部分。库内分表其实就是分区，此时，在一定程度上能提升效能，但并不能真正的达到分布式的效果；分库时数据会分散到不同的MySQL库，从而达到分布式的效果，降低数据对同一个服务器的I/O操作，从而提高性能。表经过水平拆分后，能够支持非常大的数据量。

拆分原则：

●能不拆分就不拆分，可进行SQL优化处理；

●拆分时分片数量要尽量少，分片尽量均匀分布在多个数据结点上，因为一个查询SQL跨分片越多，则总体性能越差。可以根据需要在必要的时候扩容。

●分片规则需要慎重选择做好提前规划，分片规则的选择，需要考虑数据增加特点和访问特点，同时也要考虑分片关联性问题，以及分片扩容问题等。常用的分片策略为范围分片、枚举分片、一致性Hash分片，这几种分片都有利于扩容。

水平拆分牵涉的逻辑比较复杂，我们可以采用一些客户端架构或者代理架构来解决，如：MySQL官方出品的代理架构MySQL Fabric，阿里巴巴的代理架构Co⁃bar，阿里巴巴客户端架构Cobar client，淘宝的客户端架构TDDL等。

（3）逆规范化

逆规范化是一种通过添加冗余数据的数据库优化技术，其目的是为了是减少表与表的连接，减少外键和索引的数量，减少表的数量，它具有检索速度快而简单的特点。缺点是更新和插入操作更费事，脚本更难写，数据可能不一致并且存在数据冗余。做逆规范前，要仔细考虑得与失，应该首先分析应用的存取数据的需求和性能特点，如果可以使用好的索引或者其他优化方法能够解决性能问题，就不必采用逆规范的做法。

常用的逆规范技术有增加冗余列、增加派生列、重新组表和拆分表[6,11]。

●增加冗余列：在表中增加其他表中已经存在的列，它的存在往往是为了避免查询时的连接操作。

●增加派生列：指增加的列来自其他表中的数据，由其他表中的数据经过计算生成。增加派生列的作用是在查询时减少连接操作，避免使用集函数。

●重新组表：当两个表连接后的结果数据经常被查询使用，那么把这两个表重新组成一个表来减少连接以提高性能。

由于逆规范技术操作，可能破坏数据的完整性，为了管理好数据，我们通常采用触发器等方式进行维护。而触发器有着良好的实时性、维护简单的特点，故它也是也是逆规范技术推荐的维护策略。