Baby

买了腾讯云的轻量数据库，便宜（其实现在和直接买1c1g的数据库也差不多了）。在做数据迁移的时候直接用了navicat的data transfer（这功能真方便）

突然发现自己还有一个database的分类，想想以前还在为了折腾怎么弄mysql更好，后面被各种数据库折腾（小型公司，不涉及大数据库，都是常规的，象pg/sqlite/sqlserver/mongodb/redis）。

很突然，线上有一台服务器，突然报表不存在。所有的项目只要涉及到数据库的，都直接报表不存在。SSH到服务器上一看，进程也活着，目录下的文件也存在着，怎么就表不存在了呢？

当时也没有注意，觉得可能是程序出错了。就restart了一下数据库，结果，这回出大事了，数据库无法启动。再启动，仍然是这个问题。连忙看了一下日志，发现报错原因居然是磁盘满了。这，不科学啊。df -h 看一下，果然，系统盘自带的20G，剩余空间为0。

怎么办？先清一下apt-cache，发现多出100多M的空间，尝试启动了一下，果然数据库启动了。再检查到底是谁占了？直接du -sh /var/log。居然只有200M，那是怎么回事呢？

继续 找，du -sh 一个目录一个目录的看。发现居然是mail目录写满了，一个mail文件。17G，用tail -n 1000 看了一下。文件居然都来自crontab ，再打开crontab -l，才发现，原来是有一个5年前的svn的勾子在运行。这个勾子，每5秒一次，就这样辛苦的运行了5年，终于通过邮件，硬生生的将磁盘写满了。

知道问题所在就简单了。关闭crontab （因为已经不用了）。同时清空邮件文件。再df -h，发现多出17G空间。好嗨森

习惯了用navicat之后。居然不会用命令行了。。。

原来的数据库是N年前的，默认字符集是latin1，但是有一些旧代码中，连接参数写的是UTF8，于是就这样。。。DB字符集为latin1，连接UTF8，存到数据库也是UTF8，wj但查看的时候显示是???

为了标准统一，于是使用mysqldump -uxx -p database a b c d e > dump.sql，将部分表先导出来

然后修改导出sql的charset=latin1;改为charset=utf8;

至此，操作完成。简单粗暴

-------EOF--

deeka说，如果有200G呢。目前我是停机处理的。因为要处理的数据也不是很多。如果不是因为升级换库的时候出现不能访问我也不会这么做。

但好象也没有特别好的方案。因为直接alter的时候也是各种不正常。才dump的

设置了远程数据库允许IP连接，也设置了bind ip为0.0.0.0 ，但有时候能连，有时候不能连接，而且还设置了是IP连接，报标题所在的错误，即 [ERROR 1042 (HY000): Can't get hostname for your address]。

开始就在想，是不是skip-name-resolve的问题，但因为我不是用host连接的，也不是内部的域名解析的问题。所以开始没注意，但google了之后，还是决定加了skipnameresolve

ave MySQL 5.5 Server setup on a windows machine. I am able to connect to the server from console / app running on the same machine but not from a remote machine. While connecting using the command

mysql -h xx.xx.xx.xx --port=3306 -u root -p
I get error as:

ERROR 1042 (HY000): Can't get hostname for your address
Have tried putting entry of client ip in server's etc\hosts file as

<client-ip> <client-hostname>

I believe this is to do with the fact that MySQL tries to establish the DNS name associated with your IP address on connect. See here for more information at the MySQL site.

You have two options:

1) Fix the connecting machine's reverse DNS. Since you've already added the machine to the hosts file, this might be unnecessary. You should also issue a FLUSH HOSTS statement on the MySQL server. See the same link above for more information about this.

2) Run MySQL with the '--skip-name-resolve' option. However, if you do this, you won't be able to use DNS names in GRANT statements. Instead you'll be restricted to using IP addresses.

2.1) or put in my.ini :

[mysqld]
skip-name-resolve
I'd recommend (1) if you can.

Hope this helps.

Copy from http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci

看到本文其实是源于Yii2 的migrate中的init，它其中就有一段：

if ($this->db->driverName === 'mysql') {
// http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci
$tableOptions = 'CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB';
}

于是我才发现了上面的URL，然后打开看了下，发现是有人在提问：Between utf8_general_ci and utf8_unicode_ci, are there any differences in terms of performance?

然后有一个554个赞的评论，这样写：

There are at least two important differences:

• Accuracy of sorting

  utf8_unicode_ci is based on the Unicode standard for sorting, and sorts accurately in a very wide range of languages.

  utf8_general_ci comes close to correct Unicode sorting in many common languages, but has a number of limitations: in some languages, it won't sort correctly at all. In others, it will merely have some quirks.

• Performance

  utf8_general_ci is faster at comparisons and sorting, because it takes a bunch of performance-related shortcuts.

  utf8_unicode_ci uses a much more complex comparison algorithm which aims for correct sorting according in a very wide range of languages. This makes it slower to sort and compare large numbers of fields.

Unicode defines complex sets of rules for how characters should be sorted. These rules need to take into account language-specific conventions; not everybody sorts their characters in what we would call 'alphabetical order'.

• As far as Latin (ie "European") languages go, there is not much difference between the Unicode sorting and the simplified utf8_general_ci sorting in MySQL, but there are still a few differences:

  For examples, the Unicode collation sorts "ß" like "ss", and "Œ" like "OE" as people using those characters would normally want, whereas utf8_general_ci sorts them as single characters (presumably like "s" and "e" respectively).

• In non-latin languages, such as Asian languages or languages with different alphabets, there may be a lot more differences between Unicode sorting and the simplified utf8_general_ci sorting. The suitability of utf8_general_ci will depend heavily on the language used. For some languages, it'll be quite inadequate.

Some Unicode characters are defined as ignorable, which means they shouldn't count toward the sort order and the comparison should move on to the next character instead. utf8_unicode_ci handles these properly.

What should you use?

There is almost never any reason to use utf_general_ci anymore, as we have left behind the point where CPU speed is low enough that the performance difference would be important. Your database will almost certainly be limited by quite other bottlenecks than this nowadays. The difference in performance is only going to be measurable in extremely specialised situations, and if that's you, you'd already know about it. If you're experiencing slow sorting, in almost all cases it'll be an issue with your indexes/query plan. Changing your collation function should not be high on the list of things to troubleshoot.

When I originally wrote this answer (over 4 years ago) I said that if you wanted, you could use utf8_general_ci most of the time, and only use utf8_unicode_ci when sorting was going to be important enough to justify the performance cost. However, the performance cost is no longer really relevant (and it may not have been back then, either). It's more important to sort properly in whichever language your users are using.

One other thing I'll add is that even if you know your application only supports the English language, it may still need to deal with people's names, which can often contain characters used in other languages in which it is just as important to sort correctly. Using the Unicode rules for everything helps add peace of mind that the very smart Unicode people have worked very hard to make sorting work properly.

还有一些代码，不过我就不贴了，建议查看原网页，别说没地址。。

在修改数据库结构的时候，突然间报这个错：Data truncated for column 'xxxxx' at row 1，xxxxx是数据库的字段名。

检查了一下，原来刚才在修改字段的时候，加了一个允许 null，然后默认的值都变成null，可是后来我又修改表结构为not null default '';所以，报会这个错

到数据库里：update xxxx set xxx = '';，再修改表结构，一切Over。

说实话，关于存储过程的博客还真的不多，有几个是值得看一下的

1、官方；http://dev.mysql.com/doc/refman/5.1/zh/stored-procedures.html

2、http://www.netingcn.com/tag/%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B

3、http://blog.why100000.com/?p=711

也发现，如果不做复杂查询，存储过程对我来说几乎没有，本来是想解决查找GEO相关的信息的，但发现这样的SQL：

SELECT userid,lat,lng,gender,
( 6371 * acos( cos( radians(31.000700) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(120.000099) ) + sin( radians(31.000700) ) * sin( radians( lat ) ) ) ) AS distance
FROM `user_geo` WHERE last_activity_time > '2013-03-11 00:00:00'
ORDER BY distance ASC limit 100

这其中的复杂度就在于distance每次都要计算，所以我尝试换成了存储过程：

DROP PROCEDURE IF EXISTS search_around_user;
DELIMITER //
CREATE PROCEDURE search_around_user
(
s_lat float(10,6),
s_lng float(10,6),
s_last_act datetime,
s_gender tinyint,
s_number tinyint,
s_page tinyint
)
LABEL_PROC:
BEGIN
if s_number <= 1 then
set s_number = 20;
end if;
if s_page <= 0 then
set s_page = 0;
end if;
if s_gender <= 0 then
set @genderQuery = "";
else
set @genderQuery = concat(" and gender = " , s_gender , " ");
end if;
set @limitQuery = concat("LIMIT " , s_page * s_number , " , " , s_number , " ");

set @strsql = CONCAT("select userid, ",
"( 6371 * acos( cos( radians(",s_lat,") ) * cos( radians( lat ) ) * cos( radians( lng ) - radians( ",s_lng," ) ) ",
"+ sin( radians( ",s_lat," ) ) * sin( radians( lat ) ) ) ) AS distance ",
" FROM user_geo where last_activity_time >= '", s_last_act , "' " , @genderQuery , " ORDER BY distance " , @limitQuery) ;

prepare stmtsql from @strsql;
execute stmtsql;


END LABEL_PROC;
//
DELIMITER ;

然后再次调用：

call search_around_user(31.000700,120.000099,'2013-03-11 00:00:00',0,20,0)

所耗费的时间和上述直接写SQL的时间是几乎一样的。想来，这也是因为distance的计算不能被优化而导致的。。。于是乎，放弃用存储过程

继昨天的处理之后，又来新的笔记 ，这次的笔记纯粹是个人的测试，与实际条件有关，比如，我要查询的字段不超过varchar的255的长度，所以我才这么做。

昨天做普通索引后，1100万条记录，索引 为220M，改成全文索引后，索引文件为1.1G，存储空间上，涨了5倍左右。

以下是笔记 ，请不要笑话，场景不同而已

• 经过测试
  • title 字段改为全文索引后，在1100万条的时候
    • 优点：
      • 速度也为0.0x秒级。速度非常快
      • 即使有or条件，只要带了limit参数，速度也非常快
    • 缺点：
      • 如果查询不带limit ,直接卡死，因为他要计算total count
      • select count() 卡死
      • 如果查询不存在的关键字，卡死
  • 使用方法
    • 尽量不做select count 查询 （数量低于100万时可以考虑，超过100万时，其实已经没有必要）
    • 查询一定要带上limit条件
    • 每次查询到不存在的关键字时，记录到关键词库，每次有新增记录时，select 关键词库一下，如果新增房间中有关键字，则将关键词去除，避免卡死
• 暂时不使用coreseek（sphinx）/xunsearch等第三方工具
  • xunsearch只支持分词查询，不支持完全匹配
  • 第三方工具，耗内存，而且增量的时候，不够及时