MySQL操作偶发性乱码问题分析

公司用的MySQL时有保存读取数据乱码现象,该问题在表现上是随机性的发生,感觉上没有特定规律,前一次好的操作下一次执行可能就会有乱码现象了,但是真的是没有规律吗?

于是乎针对这个问题花了一些时间做分析,查看代码,每一次连接数据库后都立马使用set names utf8;操作设置了使用的字符集,每次SQL操作传入的字符串都应该没有问题。
但是乱码问题确实存在在那边,为了尝试使这个问题再现,配合数据库的慢查询日志发现在发生乱码操作的时常伴随着慢查询SQL,猜测会不会是连续好多个SQL,由于某个SQL执行时间过长或操作失败后影响到后面的操作从而产生乱码数据呢?

问题发现了就要解决,php在使用mysql中对mysql的操作做了封装,这样我们在每一次 query 查询之前都先执行一个 set names utf8;命令,保障每一次查询和更新操作都是用的utf8字符集。

希望发现的这个点是产生乱码问题的根本,这么处理后可以解决乱码的问题。

Popularity: 3% [?]

Related

当MySql的Where值类型和数据字段类型不一致时会发生什么?

在多数时候我们都会对数据库建立索引,然后在书写SQL语句的时候尽可能的用上索引中所使用的字段,但是我们这样做后确实用到了这个索引了吗?事实是未必的,比如当SQL中Where条件值类型和字段定义的类型不一致时,这个索引就不会被用上。

定义如下表结构 CREATE TABLE peng_data (
phone varchar(21),
stat varchar(4),
KEY phonestat (stat, phone)
);

插入n多的数据后来做测试,首先查看插入的数据记录数。
SELECT count(*) FROM peng_data;

+----------+
| count(*) |
+----------+
|     5925 |
+----------+
1 row in set (1.85 sec)

我们写两个SQL,一个

SELECT SQL_NO_CACHE count(*) FROM peng_data WHERE stat = 0;

+----------+
| count(*)  |
+----------+
|     3640   |
+----------+
1 row in set (0.05 sec)

再看另一个 SELECT SQL_NO_CACHE count(*) FROM peng_data WHERE stat = ’0′;

+----------+
| count(*)  |
+----------+
|     3566   |
+----------+
1 row in set (0.00 sec)

为什么前面一种写法执行所需要时间较长并且所选择出的记录数多一些呢?

我们 使用EXPLAIN看一下对索引的使用情况

EXPLAIN SELECT SQL_NO_CACHE count(*) FROM peng_data WHERE stat = 0;

+----+-------------+-----------+-------+---------------+-----------+---------+------+------+--------------------------+
| id | select_type | table     | type  | possible_keys | key       | key_len | ref  | rows | Extra                    |
+----+-------------+-----------+-------+---------------+-----------+---------+------+------+--------------------------+
|  1 | SIMPLE      | peng_data | index | phonestat     | phonestat |      75 | NULL | 5925 | Using where; Using index |
+----+-------------+-----------+-------+---------------+-----------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

看rows 5925 实际进行了一次全表检索,索引没有利用上。

再看第二种
EXPLAIN SELECT SQL_NO_CACHE count(*) FROM peng_data WHERE stat = ’0′;

+----+-------------+-----------+------+---------------+-----------+---------+-------+------+--------------------------+
| id | select_type | table     | type | possible_keys | key       | key_len | ref   | rows | Extra                    |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | peng_data | ref  | phonestat     | phonestat |      12 | const | 2968 | Using where; Using index |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

为什么这么样呢,原因就在于所使用的条件的字段值类型和数据库的定义的字段类型不一样时,MySQL就会在内部做数据转化,他的处理行为就会和我们期望的有一些不一样,在这里当我们使用整数来作为定义中的字符串字段做比较时,MySQL会自动的将数据库中的记录转化为整数做比较,而且将空值转化为整数0做比较,所以选出来的记录数多一些。

由此得到当MySQL的Where值类型和数据库定义的字段类型不一致时会发生:

1. 建立的索引实际没有被使用上,导致查询效率低下。
2. 由于数据库对一些值的默认转化,导致查询的结果偏离实际的期望。

Popularity: 2% [?]

Related

常见MySQL数据库备份工具和备份方式比较

原文 Hands on MySQL Backup & Migration

本文在原文的基础上稍有删减。

用以备份的工具

1. mysqldump
http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html

2. mysqlhotcopy
http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html

3.mysqlsnapshot
http://jeremy.zawodny.com/mysql/mysqlsnapshot/

4.ibbackup
http://www.innodb.com/hot-backup/order/

联机备份 .VS. 脱机备份

联机备份通常使用在不能接受数据库停机的情况下,一般来说,脱机备份速度快,并且发生错误的几率少,我们不用担心数据库正在执行事务,锁表等容易发生一致性问题的发生。如果你幸运的可以停下数据库或者有一个主从方式的数据库,请使用脱机方式备份。

Data Dump vs Raw backups

Data dump 输出一系列SQL 语句序列,可以在后来用来重新创建数据库的结构并恢复数据。mysqldump 是这个领域的首选工具,他可以用在任意类型的表上面,无论是本地的还是网络的。当然,由于要产生很多额外的SQL语句,导出结果将是一个很大的文件并且占用很多CPU资源,最重要的是,当数据恢复后需要一次完全的索引重建。

更有效率的方法是是对MySQL数据库的物理文件做一次快照(snapshot)。因为我们跳过了很多转化步骤,因此处理起来比较高效。 做一个MyISM数据表的备份只要拷贝磁盘上数据文件和索引文件。对InnoDB,需要备份对应表空间和关联的事务日志。

mysqldump / mysqlhotcopy / mysqlsnapshot / ibbackup

mysqldump – (online, dump) – 最一般的工具,他会通过锁表的方式从一个联机数据库中做数据导出并写到指定的文件中(磁盘或网络上)。他只适合小的数据库。

# typical mysql dump backup and restore usage
mysqldump -u root -pPassword -x --all-databases > db_dump.sql
mysql -u root -pPassword < db_dump.sql

# dump into 'backup' folder (local machine), into two text files <data, table_structure>
mysqldump -T backup --fields-terminated-by=',' database-name -u root -pPassword

# compress the dumped data on the fly
mysqldump -u root -pPassword --all-databases | bzip2 -c > db_dump.bz2

mysqlhotcopy – (online, raw) 将对由 ISAM或MyISAM 表构成的数据库做一个完全的物理备份。他的操作方式:对所有表获取一个只读锁=>做文件拷贝=>释放锁。

# perform an online backup into /backup/location
mysqlhotcopy -u root -p password database_name /backup/location

mysqlsnapshot – (online, raw) 一个非常好的工具用来在联机方式下获得MySQL数据库的一个快照。可以配置它来压缩数据,并/或 为每一个数据库提供一个分离的tar文件。 不过他只适合 MyISAM 类型数据库。

# save a full database snapshot of an online database into /backup/location
mysqlsnapshot -u root -pPassword -s /backup/location

# restore a snapshot
tar -xvf /backup/location/db.tar

ibbackup – (online, raw) 可以对使用InnoDB和MyISAM表的任何数据库做联机备份。是一个很好的工具就是要收费.当然如果你是一个InnoDB的用户,还是值得花钱购买的。

# perform online backup of MyISAM / InnoDB tables
ibbackup /etc/my.cnf /etc/ibbackup.cnf

# restore recent backup (as configured in ibbackup.cnf)
ibbackup --restore /etc/ibbackup.cnf

cp, scp, nc – (offline, raw) 如果你可以停下数据库,则可以使用这几个工具直接拷贝数据库目录下的文件。是获取数据库快照的最安全方法。

Popularity: 3% [?]

Related

MySQL性能优化工作小结

1. 首先将数据库的慢查询日志功能打开,修改 my.conf

long_query_time=1

log-slow-queries=/var/log/mysqld_slow.log

log-queries-not-using-indexes

2. 运行tuning-primer.sh脚本查看数据库状态。

在数据库运行一段时间后(两天以上)首先使用tuning-primer.sh脚本, 查看一下my.conf中的配置参数设置,按照运行结果的建议做一下my.conf的参数调整。(http://www.day32.com/MySQL/)

3. 使用 mysqlreport 脚本做定期分析,持续观察DB的运行状况

检验每次参数变化后是否起作用(一些数据要在参数变化后运行两天以上捕捉到的结果和变化之前做比较才有价值),具体的分析结果所代表的含义网上有介绍( http://hackmysql.com/)

4. 查看 http://www.mysqlperformanceblog.com/tools/ 的一些资源,做数据库服务器的性能辅助分析,并可以看 http://www.mysqlperformanceblog.com上一些性能调优方面的讨论,针对自己的情况做具体取舍。

5. 根据慢查询日志分析存在问题的SQL,针对性的做SQL和业务逻辑的优化处理

  1. 通过 mysqlslowdump

-s 排序,c,t,l,r分别按照query次数,时间,lock的时间和返回的记录数来排序,前面添加a便是倒序

-t top n 的意思,即是返回前面多少条数据

-g 后面可以写一个正则表达式,大小写不敏感

典型查询

mysqldumpslow –s c –t 20 host-slow.log

mysqldumpslow –s r –t 20 host-slow.log

mysqldumpslow –s t –t 10 –g “left join” host-slow.log

  1. 在mysql环境中通过explain sql 查看 查询语句的索引命中情况等,一些慢速的查询是由于没有使用到恰当的索引,通过添加调整索引可以得到很明显的速度提升
  2. explain extent sql explain更多一些关于sql查询的信息

6. 其他,sql查询的一些语句书写准则(参考mysql手册第七章关于优化的章节)

mySQL的优化不是一步到位的,要持续观察改善,一部分优化可以通过服务器端的配置来得到(添加硬件,对系统的参数做优化) ,还有一部分要根据实际业务中使用的数据量和SQL来做分析,考虑换种SQL写法,换一个实现方法等,添加业务层的cache减少对数据库的访问等方式来得到增强。

Popularity: 3% [?]

Related

← Previous Page