就像我解释before,我的mysql数据库服务器有些问题.我会知道你的意见,并有一些想法,因为我在一个黑洞,我不知道,因为发生了服务器的行为.我会尝试解释所有的环境.我有1个DB,有很多表.我们在java中创建了一个导出工具,...
就像我解释before,我的mysql数据库服务器有些问题.我会知道你的意见,并有一些想法,因为我在一个黑洞,我不知道,因为发生了服务器的行为.
我会尝试解释所有的环境.我有1个DB,有很多表.我们在java中创建了一个导出工具,用于导出数据库中的所有数据.数据存储在5个不同的表中,我需要将数据连接到5个表中.这些是表格：
DB的结构是从一些传感器接收信息并存储它的系统.
测量表：我们从传感器接收的测量值.
+--------------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| version | bigint(20) | NO | | NULL | |
| counter | char(2) | YES | | NULL | |
| datemeasurement_id | datetime | NO | MUL | NULL | |
| datereal_id | datetime | NO | MUL | NULL | |
| delayed | bit(1) | NO | | NULL | |
| frequency | tinyint(4) | YES | | NULL | |
| measuringentity_id | bigint(20) | NO | MUL | NULL | |
| real | bit(1) | NO | | NULL | |
| tamper | bit(1) | NO | | NULL | |
| value | float | NO | | NULL | |
+--------------------+------------+------+-----+---------+----------------+
measuring_entity表：一个传感器可以测量多个物体(温度,湿度).这些是实体.
+--------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| version | bigint(20) | NO | | NULL | |
| household_id | varchar(4) | NO | MUL | NULL | |
| operative | bit(1) | NO | | NULL | |
| type | char(20) | NO | | NULL | |
| unit | char(3) | NO | | NULL | |
| interval | float | YES | | NULL | |
+--------------+------------+------+-----+---------+----------------+
sensor_measuring_entity：一个传感器可以关联多个实体.
+--------------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+------------+------+-----+---------+-------+
| sensor_id | bigint(20) | NO | PRI | NULL | |
| measuringentity_id | bigint(20) | NO | PRI | NULL | |
| version | bigint(20) | NO | | NULL | |
+--------------------+------------+------+-----+---------+-------+
传感器表：传感器的信息,与上表中的测量实体相关.
+---------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+-------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| version | bigint(20) | NO | | NULL | |
| battery | bit(1) | NO | | NULL | |
| identifier | char(6) | NO | | NULL | |
| installationdate_id | datetime | NO | MUL | NULL | |
| lastreceiveddate_id | datetime | YES | MUL | NULL | |
| location_id | bigint(20) | NO | MUL | NULL | |
| operative | bit(1) | NO | | NULL | |
| tampererror | smallint(6) | NO | | NULL | |
+---------------------+-------------+------+-----+---------+----------------+
位置表：传感器放置在何处.
+------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| version | bigint(20) | NO | | NULL | |
| height | tinyint(4) | YES | | NULL | |
| operative | bit(1) | NO | | NULL | |
| place | char(15) | NO | MUL | NULL | |
| room | char(15) | NO | | NULL | |
| typesensor | char(15) | NO | | NULL | |
| formaster | bit(1) | YES | | NULL | |
+------------+------------+------+-----+---------+----------------+
导出信息的算法,像这样,跨越数据,尝试导出我们可以在分离的csv文件中的所有类型的传感器的分离信息：
for (int i = 0; i < households.length; i++) {
openConnection();
for (int j = 0; j < values.length; j++) {
for (int k = 0; k < rooms.length; k++) {
if (places.length > 0) {
for (int l = 0; l < places.length; l++) {
for (int m = 0; m < height.length; m++) {
export(startDate2, endDate,
households[i], values[j],
rooms[k], places[l],height[m]);
}
}
} else {
for (int m = 0; m < height.length; m++) {
export(startDate2, endDate,
households[i], values[j],
rooms[k], null, height[m]);
}
}
}
}
try {
connection.close();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
public void export(String startTime, String endTime, String household,
String type, String room, String place, String height)
throws ExporterException {
String sql = buildSQLStatement(startTime, endTime, household, type,
room, place, height);
Statement query;
try {
query = connection.createStatement();
ResultSet result = query.executeQuery(sql);
…
(The exporting to csv code)
…
private String buildSQLStatement(String startTime, String endTime,
String household, String type, String room, String place,
String height) {
String sql = "select HIGH_PRIORITY m.datemeasurement_id, me.type, l.place, m.value, l.room, l.height, s.identifier "
+ "FROM measurement as m STRAIGHT_JOIN measuring_entity as me ON m.measuringentity_id = me.id "
+ "STRAIGHT_JOIN sensor_measuring_entity as sme ON me.id = sme.measuringentity_id "
+ "STRAIGHT_JOIN sensor as s ON sme.sensor_id = s.id "
+ "STRAIGHT_JOIN location as l ON l.id = s.location_id"
+ " WHERE m.datemeasurement_id "
+ " >"
+ "'"
+ startTime
+ "'"
+ " AND m.datemeasurement_id"
+ " <"
+ "'"
+ endTime
+ "'"
+ " AND m.measuringentity_id"
+ " IN (SELECT me.id FROM measuring_entity AS me WHERE me.household_id="
+ "'"
+ household
+ "'"
+ ")";
我的一个大问题是：有时这个带有来自DB的代码的应用程序真的很慢. MySQL的工作速度非常慢,而且其他时候MYSQL的工作速度非常快.我们无法理解为什么会发生这种行为差异.
例如,当它很慢(CPU的0.3-0％)时,从DB导出所有数据可能需要大约3天(大约200,000个查询),但就像我之前所说的那样,有一些时刻服务器会执行在30-40分钟内完成相同的工作(占CPU的85％).
我们看到的问题是,当行为缓慢时,mysql花费大量时间处于“准备”状态(每个查询大约140s),尝试优化查询,但就像我说的,这只发生了一些时间.不是每次都是.
1016 | root | localhost:53936 | OptimAAL | Query | 10 | preparing | select HIGH_PRIORITY m.datemeasurement_id, me.type, l.place, m.value, l.room, l.height, s.identifier
这些是可以执行的查询之一：
EXPLAIN select HIGH_PRIORITY m.datemeasurement_id, me.type,
l.place,m.value, l.room, l.height, s.identifier
FROM measurement as m
STRAIGHT_JOIN measuring_entity as me ON m.measuringentity_id=me.id
STRAIGHT_JOIN sensor_measuring_entity as sme ON me.id=sme.measuringentity_id
STRAIGHT_JOIN sensor as s ON sme.sensor_id=s.id
STRAIGHT_JOIN location as l ON l.id=s.location_id
WHERE m.datemeasurement_id >'2012-01-19 06:19:00'
AND m.datemeasurement_id <'2012-01-19 06:20:00'
AND m.measuringentity_id IN (SELECT me.id FROM measuring_entity AS me
WHERE me.household_id='0022')
AND (height = '0')
AND (type = 'Brightness')
AND (place = 'Corner')
AND (room = 'Living room')
ORDER BY datemeasurement_id
这是解释的结果：
+----+--------------------+-------+-----------------+-----------------------------------------------+--------------------+---------+-------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+-----------------+-----------------------------------------------+--------------------+---------+-------------------------------+------+-------------+
| 1 | PRIMARY | m | range | FK93F2DBBC6292BE2,FK93F2DBBCA61A7F92 | FK93F2DBBC6292BE2 | 8 | NULL | 4 | Using where |
| 1 | PRIMARY | me | eq_ref | PRIMARY | PRIMARY | 8 | OptimAAL.m.measuringentity_id | 1 | Using where |
| 1 | PRIMARY | sme | ref | PRIMARY,FK951FA3ECA61A7F92,FK951FA3ECF9AE4602 | FK951FA3ECA61A7F92 | 8 | OptimAAL.m.measuringentity_id | 1 | Using index |
| 1 | PRIMARY | s | eq_ref | PRIMARY,FKCA0053BA3328FE22 | PRIMARY | 8 | OptimAAL.sme.sensor_id | 1 | |
| 1 | PRIMARY | l | eq_ref | PRIMARY,place | PRIMARY | 8 | OptimAAL.s.location_id | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | me | unique_subquery | PRIMARY,FK11C7EA07E6EB51F2 | PRIMARY | 8 | func | 1 | Using where |
+----+--------------------+-------+-----------------+-----------------------------------------------+--------------------+---------+-------------------------------+------+-------------+
显然,如果我们改变日期间隔的值,数据量会增加很多,因为我们在数据库中有100万次测量.
我尝试了一切：
更改mysQL配置文件(/etc/my.cnf)：
[mysqld]
#bind-address = 141.21.8.197
max_allowed_packet = 128M
sort_buffer_size = 512M
max_connections=500
query_cache_size = 512M
query_cache_limit = 512M
query-cache-type = 2
table_cache = 80
thread_cache_size=8
key_buffer_size = 512M
read_buffer_size=64M
read_rnd_buffer_size=64M
myisam_sort_buffer_size=64M
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=700M
innodb_additional_mem_pool_size=20M
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#Enable logs
log = /var/log/mysql/mysql-log.log
log-error = /var/log/mysql/mysql-error.log
long_query_time = 1
log-slow-queries = /var/log/mysql/mysql-slow.log
[mysqld_safe]
log-error=/var/log/mysql/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
>使用不同类型的JOIN强制它来避免优化查询.
>使用nice -20为procces提供最高的prio.
>使用存储过程而不是代码中的查询.
>关闭与数据库的任何其他连接,只为我提供数据库,没有任何其他连接.
>更改并尝试优化查询.
> ……
就像你可以看到我尝试了一切,我不知道我能做些什么来确保服务器一直都很快.
这些是服务器的信息：
MySQL version: 5.1.61-log / x86_64
RAM: 8 GB
OS: CentOS release 6.2 (Final)
CPU: 4 Cores / Xeon E6510 @ 1.73GHz
我真的很感激你的帮助,
编辑：
我想补充一点,现在我遇到的最大问题是为什么会发生服务器的不同行为.因为我知道可以优化查询,但有时可以使用此代码快速工作.
我现在的噩梦就是知道为什么有时会快速工作,而不是总是如此.现在我正在与IT人员核实是否存在硬件访问,硬盘或类似问题.
看起来也可能是SQL配置的问题,或者它可能是MYSQL中的查询优化器,但我无法发现我的黑洞的解决方案是什么.
非常感谢你的帮助
解决方法:
我可以想到你可以做的一些优化.
首先,使用绑定变量和准备好的语句.
PreparedStatment stmt = connection.prepareStatement(
"select HIGH_PRIORITY m.datemeasurement_id, me.type, l.place, m.value, l.room, l.height, s.identifier "
+ "FROM measurement as m STRAIGHT_JOIN measuring_entity as me ON m.measuringentity_id = me.id "
+ "STRAIGHT_JOIN sensor_measuring_entity as sme ON me.id = sme.measuringentity_id "
+ "STRAIGHT_JOIN sensor as s ON sme.sensor_id = s.id "
+ "STRAIGHT_JOIN location as l ON l.id = s.location_id"
+ " WHERE m.datemeasurement_id > ? "
+ " AND m.datemeasurement_id < ? "
+ " AND m.measuringentity_id IN (SELECT me.id FROM measuring_entity AS me WHERE me.household_id= ? )";
stmt.setDate(1, startDate);
stmt.setDate(2, endDate);
stmt.setString(3, household);
stmt.executeQuery();
其次,删除IN – 你不能在这里使用单独的连接来对照measurement_entity吗？ IN通常表现不佳.
第三,您可以使用批处理来执行插入吗？批量插入应该可以显着提高速度.
这些是我能想到的一些东西.如果您的查询未在Java端进行优化,那么全世界的所有SQL调优都无济于事.
沃梦达教程
本文标题为：java – MySQL在使用非常奇怪的行为准备语句时被阻止
基础教程推荐
猜你喜欢
- MyBatisCodeHelperPro最新激活方法(有效方法) 2022-09-03
- MyBatis-Plus插件机制及通用Service新功能 2023-02-28
- Java实例讲解注解的应用 2022-12-11
- javaGUI实现多人聊天功能 2023-05-19
- 在IDEA中运行Java程序 2023-10-08
- springcloud如何获取网关封装的头部信息 2023-01-13
- SpringBoot 转发请求至指定页面的操作方法 2023-07-01
- 在Idea中新建Java项目 2023-10-08
- java中String.intern()方法功能介绍 2022-11-29
- Java线程优先级变量及功能 2022-12-16
