基于MySQL数据库的索引优化研究

信息与电脑China Computer & Communication2019年第12期

基于MySQL数据库的索引优化研究

郭　冰

（惠州城市职业学院，广东 惠州　516025）

摘　要：合理的逻辑设计和物理设计是数据库能够高性能运行的先决条件。根据系统查询设计数据库的模式，通常需要权衡各种因素。一种比较恰当的优化查询方式是添加计数表和摘要表，但可能导致这些表的维护成本增加。例如，加速某些类型的查询，可以采用反范式设计的方式，但可能减慢其他类型的查询。MySQL的独特功能和实现细节，对性能有很大影响。基于此，聚焦于MySQL数据的索引优化研究，重点探讨MySQL特有的模式设计。

关键词：数据库；MySQL；索引

中图分类号：TP311.13　　文献标识码：A　　文章编号：1003-9767（2019）12-154-04

Research on Index Optimization Based on MySQL Database

Guo Bing

Abstract: Reasonable logical design and physical design are prerequisites for high performance database operation. According

(City College of Huizhou, Huizhou Guangdong 516025, China)

to the system query design database model, usually need to weigh various factors. A more appropriate way to optimize queries is to add types of queries can be done in an anti-paradigm way, but may slow down other types of queries. MySQL's unique functions and on the unique pattern design of MySQL.

Key words: database; MySQL; index

1.3　避免使用空值。

许多表包含可以为空（NULL）列，即应用程序不需要

implementation details have a great impact on performance. Based on this, we focus on the index optimization of MySQL data, focusing

count tables and summary tables, but it may lead to increased maintenance costs for these tables. For example, accelerating certain

１　基于数据类型的优化

MySQL支持的数据类型非常多，深思业务系统需求，选择正确的数据类型，是系统高性能的前提条件。笔者根据多年的实践经验，总结如下几条原则。1.1　选择最小的数据类型

在有多种选择的情况下，设计人员应尽量选择可以正确存储数据的最小数据类型。对计算机而言，较小的数据类型处理最快，因为其占用较少的磁盘空间、内存容量和CPU缓存寻址，只需要更少的CPU周期进行处理。1.2　采用简单数据类型。

简单即高效，简单数据类型操作通常只需要较少的CPU处理时间。例如，CPU对整数操作比字符操作更简单，因为字符集的整理规则（排序规则）比整数的整理规则更加复杂。例如，使用MySQL内置类型存储日期和时间，而不是字符串；以整数存储IP地址。

保存NULL。NULL是列的默认属性，除非需要存储NULL值，否则指定列为NOT NULL[1]。当计算机查询包含为NULL列时，计算机会变得更慢，因为需要为NULL的列建立索引，与直接对值相比，索引的统计查询更加复杂。当索引包含为NULL的列时，每个索引记录都需要一个额外的字节，MylSAM中亦是如此。

通常，NULL的列的性能更改为NOT NULL的可能性相对较小。因此，调整时无需在现有模式中查找和修改此情况。但是，如果计划在列上构建索引，则应避免将其设计为可以是NULL的列。设计人员为存储字段选择数据类型时，应先选择字段类型，如数字、字符串、时间等。

选择具体类型时，MySQL数据库中，同一个数据字段，很多数据类型都可以满足存储需求，只是可以存储的数据长度、范围以及精度可能不同，导致存储数据所占的磁盘空间

作者简介：郭冰(1977—)，男，江西吉安人，硕士研究生，高级讲师。研究方向：软件工程。

—　　　１５４　　　—

2019年第12期

信息与电脑China Computer & Communication数据库技术

不同，查询时内存空间也不同。即使是同一大类型的数据，如果是不同的子类型数据，可能存在一些特殊行为和属性。例如，DATETIME和TIMESAMP列都可以存储相同类型的数据，即时间和日期，且精确到秒。

如果同时不存在true和false，毫无疑问，应使用枚举列而不是集合列。2.5　优化空值的存储

前文提过关于避免使用NULL的优势，但遵循此原则也不要走极端，即真的需要存储一个未知值时仍需使用NULL。某些情况下，使用NULL可能比常量更好，因为从特定类型值字段中选择一个不可能的值，可能导致很多代码更加复杂，且易出现错误。代码如下：

CREATE TABLE ...(

dt DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'

伪造的全零值可能会导致很多问题（可以配置MySQL的SQL_MODE，禁用不可能的日期）。值得注意的是，MySQL可在索引中存储NULL值，而Oracle不能。

２　基于ＭｙＳＱＬ模式设计的优化

基于MySQL模式设计的优化总体来讲，设计师设计MySQL数据库时，没有遵循原则，存在缺陷，需要优化，且MySQL的实现机制也可引起缺陷。设计缺陷可能是MySQL的特性所决定，即同样的设计，在其他数据库系统中没有缺陷，而在MySQL数据库中可能存在缺陷。2.1　优化同一个表的字段

MySQL的存储引擎API通过行缓冲区格式在服务器和存储引擎层之间复制数据工作。将缓冲的内容在服务器中解码为单独列，对CPU而言开销非常高。MylSAM的固定长度行结构实际上与服务器层的行结构相匹配，不需要转换。但是，MylSAM的可变长度行结构和InnoDB的行结构需要转换，转换的CPU开销取决于列的数量[2]。当查看非常高的CPU使用率时，会发现客户端使用了大量表字段，甚至长达数千个字段。2.2　优化表的关联

众所周知，设计人员一般使用“实体-属性-值”（EAV）的设计模式。MySQL数据库管理产品设计之初，每个关联操作只有61个表，但EAV数据库需要大量自我关联。笔者见过很多超过此的EAV数据库。事实上，在少于61个表的情况下，解析和优化查询成本将成为MySQL的问题之一。根据一个粗略的经验法则，如果希望查询快速执行，并具有良好的并发性，单个查询应只与12个表相关联。2.3　优化全能的枚举

注意防止过度使用枚举（ENUM)，代码如下：CREATE TABLE...(

country enum(‘’,’0’,’1’,’2’,,,,’31’)这种模式设计非常凌乱，在任何支持枚举类型的数据库中使用此枚举值类型都可能存在问题。因此，应使用整数作为外键关联字典表或查找表，以查找特定值。但是，MySQL中，当需要向枚举列表添加新国家时，需要执行ALTER TABLE操作。ALTER TABLE是MySQL 5.0及更早版本中的阻塞操作。即使在5.1及更高版本中，如果不在列表末尾添加值，则需要执行ALTER TABLE操作。2.4　优化变相的枚举

枚举列允许将一组定义值中的单个值存储在列中，并设置列允许将一个或多个值存储在一组定义值中。代码如下：

CREATE TABLE ...(

is_default set(‘Y’,’N’) NOT NULL default 'N'

３　基于范式和反范式的优化

对于任何给定的数据，通常有许多表示，从完整范式到完全非规范化。标准化数据库中，每个事实数据只出现一次。而非规范化数据库中，信息冗余，可以存储在多个位置，如表1所示。

表1　雇员-部门-领导关系

雇员姓名张三李四王五马六

部门名称财务部工程部财务部工程部

部门领导张三李四张三李四

此表的问题是修改数据时可能出现不一致。如果王五接任财务部门负责人，则需要修改多行数据，以反映这一变化。此外，如果删除财务部门的所有员工，此设计不能代表没有员工信息的部门，则失去了这个部门的所有记录。为了避免此问题，需要通过拆分员工和部门项规范化此表。拆分后，可以使用表2和表3存储employee表。

表2　雇员部门关系

雇员姓名张三李四王五马六

表3　部门与部门领导关系

部门名称财务部工程部

部门领导张三李四部门名称财务部工程部财务部工程部

以这种方式设计的两个表符合第二范式，但第二范式只是许多范式中的一种。

４　基于缓存表和汇总表的优化

提高性能的最佳方法是将派生冗余数据保存在同一个表中，但需要创建一个完全的摘要或缓存表，以满足搜索的需求。如果可以容忍少量脏数据，这是一种非常好的方法。术语“缓存表”和“汇总表”没有标准含义。使用术语“缓

—　　　１５５　　　—

数据库技术

信息与电脑China Computer & Communication2019年第12期

存表”引用一个表，该表存储在其他模式中相对容易检索数据，但每次采集的速率较慢（如逻辑冗余数据）。保存时，术语“摘要表”是使用GROUP BY语句聚合数据的表（如数据在逻辑上不是冗余），也称之为术语“累积表”（Roll-Up Table）。

以网站为例，假设需要计算过去24 h内发送的邮件数量，且无法在非常繁忙的网站上维护实时准确的计数器。作为替代方案，可以每小时生成一张汇总表。这可以通过简单的查询完成，且比实时维护计数器有效，但缺点是计数器不能保证100%精确。如果必须获得过去24 h内发送的确切消息数量（没有遗漏），可以根据每小时汇总表，将统计表的前23 h的计数相加。最后，添加开始和结束阶段不完整小时的计数。假设统计表称作msg_per_hr且定义为：

CREATE TABLE msg_per_hr ( hr DATETIME NOT NULL, cnt INT UNSIGNED NOT NULL,PRIMARY KEY(hr));

通过添加以下三个语句的结果，获取过去24 h内发送的邮件总数。使用LEFT（N0W（），14）获得当前日期和时间：

mysql〉SELECT SUM(cnt) FROM msg_per_hr ->WHERE hr BETWEEN

->CONCAT(LEFT(NOW(),14),'00:00')-INTERVAL 23 HOUR

-> AND C0NCAT(LEFT(N0W(),14),'00:00')-INTERVAL 1 HOUR; mysql〉SELECT C0UNT(*) FROM message

->WHERE posted >= N0M()-INTERVAL 24 HOUR->AND postedmysql〉SELECT C0UNT(*) FROM message

->WHERE posted>=C0NCAT(LEFT(N0W(),14),'00:00');无论哪种方式，不严格计算或用小范围查询填充严格的间隙计数，比计算消息表的所有行更有效，这也是建立汇总表的关键原因。实时计算统计数据是一项非常昂贵的操作，因为需要扫描表中的大量数据，或者查询只能在某些索引上高效运行，且这些特定索引通常会对UPDATE操作产生影响。

一般情况下，需要建立多种索引并组合，以加快查询速度，此时需要创建一个只包含主表列子集的缓存表。一个较好的解决办法是对缓存表使用不同的存储引擎。假如主表需要使用InnoDB，笔者建议使用MylSAM作为缓存表的引擎，如此索引将会占用比较小的空间，实现全文搜索。如果表较大，笔者建议把整个表从MySQL数据库中导出，插入到专门的搜索系统中，以获得更高的搜索效率。

使用缓存表和汇总表时，设计人员必须确定是实时还是定期维护数据。两者效果没有可比性，主要取决于应用程序。但是，定期重建不仅节省资源，而且可以保证表没有太

多碎片。重建汇总表和缓存表时，通常需要确保数据运行时仍然可用。这需要通过使用“影子表”实现。影子表指在真实表后创建的表。当表构造操作完成时，可以通过原子重命名操作切换影子表和原始表。例如，如果需要重建my_summary，可以先创建my_summary_new，后填入数据，最后切换到真实表。

mysql〉DROP TABLE IF EXISTS my_summary_new, my_summary_old;

mysql〉CREATE TABLE my_summary_new LIKE my_summary;

--populate my_summary_new as desired

mysql>RENAME TABLE my_suumary TO my_summary_old,my_summary_new TO my_summary;

如果将名称my_summary分配给新创建的表，需将原始my_summary表重命名为my_summary_old，同时，可以保留旧版本的数据，直到下次重建。如果新表出现问题，可以轻松快速回滚，查找问题原因。

５　基于更改表操作的速度优化

MySQL数据库中，对于一般的小表，ALTER TABLE操作没有问题，但当表达到一定程度，ALTER TABLE就会很慢。从MySQL数据库的操作机理来讲，更改表结构的第一步是创建一个新结构的空表，第二步是从旧表中查找所需的所有数据，第三步是把所需的数据插入新表，第四一步是删除旧表。这可能需要花费很长时间，特别没有足够内存时。MySQL 5.1及更高版本支持某些类型的“在线”操作，整个操作过程不需要锁定表。最新版本的InnoDB还支持索引，促使索引更快，布局更紧凑。

通常情况下，绝大多数修改表的运维操作都会导致MySQL服务中断。笔者在此展示一些在DDL操作中比较常见的技巧，但只适用于某些特殊情况。只有两种技术可用于常见场景，一种是在不提供服务的机器上执行ALTER TABLE操作，然后切换到提供服务的主库；另外一种技巧是“影子拷贝”。卷影副本的技巧是创建一个于所需表结构的源表的新表，通过重命名和删除表交换两个表。有一些工具可以帮助完成影子拷贝工作，例如数据库运维团队的“online schema change”工具、Shlomi Noach的openark toolkit以及Percona Toolkit。如果使用Flexviews，也可通过其CDC工具执行无锁的表结构变更。不是所有的ALTER TABLE操作都会引起表重建，也有两种方法可以更改或删除列的默认值。如果要修改电影的默认租借期限，将其从三天更改为五天，代码入下：

mysql> ALTER TABLE sakila.film

-> MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5;

当执行SHOW STATUS命令时，系统显示此语句已完成

（下转第163页）

—　　　１５６　　　—

2019年第12期

信息与电脑China Computer & Communication网络与通信技术

与市场中其他企业进行合作，不同的合作企业拥有不同的性质与合作要求，对石油企业信息资产所提出的要求与标准也有所不同。因此，石油企业在开展信息安全管理工作时，需要对本企业内部所产生的各类信息进行有针对、有计划的管理与整合，有效提升信息安全管理价值，保证资源利用率，降低不必要的资源浪费。石油企业立足于自身发展现状，根据合作企业对信息资产所提出的要求与标准，对本单位信息进行分级分类管理，将相同价值、相同范畴的信息整合到一起，构建一个完整的石油企业信息系统，根据信息资产价值对本企业所产生的各类信息进行有针对性的管理，对资产价值较高的信息进行重点管理，着重细化，使此类信息具有较强的精准性，并对其进行多方位管理与整合，保证信息的安全性与稳定性。

3.5　强化人员管理，进行安全培训教育

人力资源是石油企业发展的基础。在石油企业实施信息化生产、信息化管理时，需要有相应的人力资源为其提供强有力的支持。在石油企业信息化生产运营过程中，员工的不当操作会为石油企业网络安全带来一定的安全隐患。为此，石油企业需要全面提高对员工管理的重视，定期对企业内部员工开展安全教育培训、对计算机操作人员进行专业技能培训，不断提升员工的网络安全意识与专业技术水平，使每一位员工均能规范个人行为，避免人为因素对网络信息安全带来的负面影响。图2为网络安全培训内容。

为减少网络安全风险对石油企业所带来的负面影响，石油企业需要从病毒、设备、安全、信息以及人员等视角出发，全面提升石油企业网络信息的安全性能。

图2　网络安全培训内容

参考文献

[1]叶铭,陈刚,黄林.浅谈油田企业网络安全问题及防火墙防护策略[J].信息系统工程,2019(2):67.

[2]张技峰.基于信息网络技术的油田设备精细化管理的分析与研究[J].化工管理,2017(18):23.

[3]王晶.“互联网+”背景下油田生产精细化管理系统的设计与实现[D].北京:首都经济贸易大学,2017:32.

[4]赵强,叶秀芬,刘峰.油气田企业中的网络安全问题与防火墙安全防护策略分析[J].化工管理,2017(15):161,163.

[5]郭培龙,宋颖杰,张振宇.在油田企业中提高网络安全的可行性分析[J].信息系统工程,2012(5):63-.

４　结　语

在“互联网+”背景下，先进的计算机设备、网络技术被陆续运用到石油企业的各项生产、运营活动中，有效提升石油企业的现代化技术水平，网络信息安全问题也随之产生。（上接第156页）

1 000次读取和1 000次插入操作。换言之，将整个表复制到一个新表，列类型、大小和NULL属性保持不变。理论上，MySQL可以跳过创建新表的步骤，将需要修改的列的默认值存放在表的.frm文件中，如此可以直接修改文件，而无需大动干戈更改整个表。但是，MySQL没有采用这种优化方法，所有的MODIFY COLUMN操作都会导致表重构。了解MySQL的工作机理后，可以采用ALTER COLUMN操作的方法，直接更改需要修改的列的默认值：

mysql>ALTER TABLE sakila.film

->ALTER COLUMN rental_duration SET DEFAULT 5；通过此语句可直接修改.frm文件，不需修改表中的数据。

数据类型，除非实际数据模型中需要；否则，应尽量避免使用NULL值。第三，尝试使用相同的数据类型存储相似或相关值，尤其是相关条件中使用的值；第四，注意可变长度字符串，其可能导致临时表和排序期间以最大长度分配内存。第五，尽量使用整型定义标识列；第六，避免使用MySQL已放弃的功能，例如指定浮点数的精度或整数显示宽度；第七，ALTER TABLE是一个繁琐的操作，大多数情况下会锁定表并重建整个表。虽然笔者展示了一些可以使用黑客方法的特殊场景，但是大多数情况下，必须使用更通用的方法。

参考文献

[1]BaronSchwartz,PeterZaitsev,VadimTkachenko,等.高性能 MySQL[M].北京:电子工业出版社,2013:175.

[2]PaulDuBois.MySQL技术内幕[M].第2版.北京:人民邮电出版社,2015:72.

６　结　语

基于MySQL数据库的索引优化有规律可循，具有普适性。第一，尽量避免过度设计，例如极其复杂的查询模式设计或者包含许多列的表设计；第二，使用小而简单的合适的

—　　　１６３　　　—