论电力关键节点温度监测系统数据库优化

韦方正 朱瀛 戴学琰 苏宁雁 邵率

杭州电力设备制造有限公司建德冠源成套电气制造分公司 浙江杭州 311600

摘要：通常在电力系统中使用Mysql数据库较普遍，Mysql开源、成本较低，性能较高，在大数据的情况下可以做分布式。但在现实使用的场景中存在数据库未合理使用导致性能大大降低，影响Mysql性能的因素有很多，笔者将从数据库结构优化，执行语句，索引等方面对Mysql数据库进行性能优化。

关键词：数据库优化，运行效率，结构合理化，索引优化

数据格式优化

通常数据类型应尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。

简单数据类型需要更少的CPU周期，比如整型比字符操作代价更低，因为字符集和排序规则使字符比较比整型比较更复杂。比如应该用MySQL内建的类型(date, time, datetime)来存储时间和日期，使用整型存储IP地址。

最好指定列为NOT NULL。因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间。可为NULL的列被索引时，每个索引记录需要一个额外的字节，如果想在列上建索引，应该尽量避免设计成可为NULL的列。

TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT分别使用8,16,24,32,64位存储空间。它们可以存储的值得范围从-2(N-1)到2(N-1)-1，如果是UNSIGNED，表示不允许负值，那正数的上限提高一倍

FLOAT、DOUBLE、DECIMAL。float类型使用4个字节存储，double使用8个字节存储，decimal占用的字节数是变长的，一般对精度要求较高的使用decimal存储，比如财务数据，但是当数据量较大时，考虑使用BIGINT代替decimal，将要存储的货币单位根据小数的位数乘以相应的倍数即可。在执行效率方面，float类型最快，decimal最慢。

VARCHAR、CHAR。VARCHAR比CHAR更节省空间，VARCHAR会使用1或2个额外的字节记录字符串的长度：如果列的最大长度小于或等于255，使用1个字节表示，否则使用2个字节表示。如果使用UTF8字符集，应该选择VARCHAR类型。CHAR适合存储很短的字符串，或者所有值都接近同一个长度。比如MD5加密后的值。对于经常变更的数据，CHAR比VARCHAR更好，因为CHAR类型不易产生碎片。

DATETIME、TIMESTAMP。DATETIME使用8个字节的存储空间，TIMESTAMP使用4个字节，一般情况下尽量选择TIMESTAMP类型。

数据库语法优化与索引优化

索引通俗来讲就相当于书的目录，当我们根据条件查询的时候，没有索引，便需要全表扫描，数据量少还可以，一旦数据量超过百万甚至千万，一条查询sql执行往往需要几十秒甚至更多，5秒以上就已经让人难以忍受了。

提升查询速度的方向一是提升硬件(内存、cpu、硬盘)，二是在软件上优化（加索引、优化sql；优化sql不在本文阐述范围之内）。

能在软件上解决的，就不在硬件上解决，毕竟硬件提升代码昂贵，性价比太低。代价小且行之有效的解决方法就是合理的加索引。

索引使用得当，能使查询速度提升上万倍，效果惊人。

索引类型

mysql的索引有5种：主键索引、普通索引、唯一索引、全文索引、聚合索引（多列索引）。

唯一索引和全文索引用的很少，我们主要关注主键索引、普通索引和聚合索引。

主键索引：主键索引是加在主键上的索引，设置主键（primary key）的时候，mysql会自动创建主键索引；

普通索引：创建在非主键列上的索引；

聚合索引：创建在多列上的索引。

EXPLAIN 数据库性能动态分析

EXPLAIN列的解释

table 显示这一行的数据是关于哪张表的

type 这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL

possible_keys 显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

key 实际使用的索引。如果为NULL，则没有使用索引。

key_len 使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref 显示索引的哪一列被使用了，如果可能的话，是一个常数

rows MYSQL认为必须检查的用来返回请求数据的行数

Extra 关于MYSQL如何解析查询的额外信息。

Extra字段值含义：

Distinct 一旦MYSQL找到了与行相联合匹配的行，就不再搜索了

Not exists MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了

Range checked for each Record（index map:#） 没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一

Using filesort 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行

Using index 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候

Using temporary 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上

Where used 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题不同连接类型的解释（按照效率高低的顺序排序）

优化方案

创建索引的技巧

维度高的列创建索引。

数据列中不重复值出现的个数，这个数量越高，维度就越高。

如数据表中存在8行数据a,b ,c,d,a,b,c,d这个表的维度为4。

要为维度高的列创建索引，如性别和年龄，那年龄的维度就高于性别。

性别这样的列不适合创建索引，因为维度过低。

对 where,on,group by,order by 中出现的列使用索引。

对较小的数据列使用索引，这样会使索引文件更小，同时内存中也可以装载更多的索引键。

为较长的字符串使用前缀索引。

不要过多创建索引，除了增加额外的磁盘空间外，对于DML操作的速度影响很大，因为其每增删改一次就得从新建立索引。

使用组合索引，可以减少文件索引大小，在使用时速度要优于多个单列索引。

索引的弊端

不要盲目的创建索引，只为查询操作频繁的列创建索引，创建索引会使查询操作变得更加快速，但是会降低增加、删除、更新操作的速度，因为执行这些操作的同时会对索引文件进行重新排序或更新。

但是，在互联网应用中，查询的语句远远大于DML的语句，甚至可以占到80%~90%，所以也不要太在意，只是在大数据导入时，可以先删除索引，再批量插入数据，最后再添加索引。

分库分表数据优化

数据存储演进思路一：单库单表

单库单表是最常见的数据库设计，例如，有一张用户(user)表放在数据库db中，所有的用户都可以在db库中的user表中查到。

数据存储演进思路二：单库多表

随着用户数量的增加，user表的数据量会越来越大，当数据量达到一定程度的时候对user表的查询会渐渐的变慢，从而影响整个DB的性能。如果使用mysql, 还有一个更严重的问题是，当需要添加一列的时候，mysql会锁表，期间所有的读写操作只能等待。

可以通过某种方式将user进行水平的切分，产生两个表结构完全一样的user_0000,user_0001等表，user_0000 + user_0001 + …的数据刚好是一份完整的数据。

数据存储演进思路三：多库多表

随着数据量增加也许单台DB的存储空间不够，随着查询量的增加单台数据库服务器已经没办法支撑。这个时候可以再对数据库进行水平区分。

Mysql数据库分库分表规则

设计表的时候需要确定此表按照什么样的规则进行分库分表。例如，当有新用户时，程序得确定将此用户信息添加到哪个表中；同理，当登录的时候我们得通过用户的账号找到数据库中对应的记录，所有的这些都需要按照某一规则进行。

路由

通过分库分表规则查找到对应的表和库的过程。如分库分表的规则是user_id mod 4的方式，当用户新注册了一个账号，账号id的123,我们可以通过id mod 4的方式确定此账号应该保存到User_0003表中。当用户123登录的时候，我们通过123 mod 4后确定记录在User_0003中。

下面是分库分表产生的问题，及注意事项

分库分表维度的问题

假如用户购买了商品,需要将交易记录保存取来，如果按照用户的纬度分表，则每个用户的交易记录都保存在同一表中，所以很快很方便的查找到某用户的购买情况，但是某商品被购买的情况则很有可能分布在多张表中，查找起来比较麻烦。反之，按照商品维度分表，可以很方便的查找到此商品的购买情况，但要查找到买人的交易记录比较麻烦。

所以常见的解决方式有：

通过扫表的方式解决，此方法基本不可能，效率太低了。

记录两份数据，一份按照用户纬度分表，一份按照商品维度分表。

通过搜索引擎解决，但如果实时性要求很高，又得关系到实时搜索。

联合查询的问题

联合查询基本不可能，因为关联的表有可能不在同一数据库中。

避免跨库事务

避免在一个事务中修改db0中的表的时候同时修改db1中的表，一个是操作起来更复杂，效率也会有一定影响。

尽量把同一组数据放到同一DB服务器上

例如将卖家a的商品和交易信息都放到db0中，当db1挂了的时候，卖家a相关的东西可以正常使用。也就是说避免数据库中的数据依赖另一数据库中的数据。

一主多备

在实际的应用中，绝大部分情况都是读远大于写。Mysql提供了读写分离的机制，所有的写操作都必须对应到Master，读操作可以在Master和Slave机器上进行，Slave与Master的结构完全一样，一个Master可以有多个Slave,甚至Slave下还可以挂Slave,通过此方式可以有效的提高DB集群的QPS.

所有的写操作都是先在Master上操作，然后同步更新到Slave上，所以从Master同步到Slave机器有一定的延迟，当系统很繁忙的时候，延迟问题会更加严重，Slave机器数量的增加也会使这个问题更加严重。

此外，可以看出Master是集群的瓶颈，当写操作过多，会严重影响到Master的稳定性，如果Master挂掉，整个集群都将不能正常工作。

当读压力很大的时候，可以考虑添加Slave机器的分式解决，但是当Slave机器达到一定的数量就得考虑分库了。

当写压力很大的时候，就必须得进行分库操作。

MySQL使用为什么要分库分表

可以用说用到MySQL的地方，只要数据量一大，马上就会遇到一个问题，要分库分表。

其实是可以处理的大表的.我所经历的项目中单表物理上文件大小在80G多，单表记录数在5亿以上，而且这个表属于一个非常实用的表:朋友关系表。

但这种方式可以说不是一个最佳方式。 因为面临文件系统如Ext3文件系统对大于大文件处理上也有许多问题。

这个层面可以用xfs文件系统进行替换，但MySQL单表太大后有一个问题是不好解决: 表结构调整相关的操作基本不在可能，所以大项在使用中都会面监着分库分表的应用。

从Innodb本身来讲数据文件的Btree上只有两个锁， 叶子节点锁和子节点锁,可以想而知道，当发生页拆分或是添加新页时都会造成表里不能写入数据。

所以分库分表还就是一个比较好的选择了。

经测试在单表1000万条记录一下，写入读取性能是比较好的。 这样在留点buffer，那么单表全是数据字型的保持在800万条记录以下，有字符型的单表保持在500万以下。

如果按 100库100表来规划，如用户业务:500万*100*100 = 50000000万 = 5000亿记录.

当单表数据量到达一定量的情况下，做分库分表能够比较便捷的提高检索速度。

结论

在设计系统前期时，创建数据库中一定要考虑业务需求，流出一部分冗余后选择最合适的字段类型，按照数据格式优化方法建表能够避免后期数据量超负荷导致数据检索速度慢，占用硬盘空间多，CPU使用率偏高。

在系统建设中期，编写代码时，按照需求调整可能存在的检索字段设置合理的索引可以大大提高检索效率。在使用时可以用Explain语句进行性能评估，选择最合适的索引和查询语句。

在系统完成后，数据量达到一定亿级时，建议选择合理的分库分表，调整系统结构，将数据量较多的表按照业务进行水平或垂直进行切分，避免大数据量导致数据库堵塞。

第5页 /共 5页