mysq积累

MySQL 用 limit 为什么会影响性能

表结构：

mysql> desc test;
+--------+---------------------+------+-----+---------+----------------+
| Field  | Type                | Null | Key | Default | Extra          |
+--------+---------------------+------+-----+---------+----------------+
| id     | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| val    | int(10) unsigned    | NO   | MUL | 0       |                |
| source | int(10) unsigned    | NO   |     | 0       |                |
+--------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

id为自增主键，val为非唯一索引。

 

灌入大量数据，共500万：

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|  5242882 |
+----------+
1 row in set (4.25 sec)

 

我们知道，当limit offset rows中的offset很大时，会出现效率问题：

mysql> select * from test where val=4 limit 300000,5;
+---------+-----+--------+
| id      | val | source |
+---------+-----+--------+
| 3327622 |   4 |      4 |
| 3327632 |   4 |      4 |
| 3327642 |   4 |      4 |
| 3327652 |   4 |      4 |
| 3327662 |   4 |      4 |
+---------+-----+--------+
5 rows in set (15.98 sec)

 

为了达到相同的目的，我们一般会改写成如下语句：

mysql> select * from test a inner join (select id from test where val=4 limit 300000,5) b on a.id=b.id;
+---------+-----+--------+---------+
| id      | val | source | id      |
+---------+-----+--------+---------+
| 3327622 |   4 |      4 | 3327622 |
| 3327632 |   4 |      4 | 3327632 |
| 3327642 |   4 |      4 | 3327642 |
| 3327652 |   4 |      4 | 3327652 |
| 3327662 |   4 |      4 | 3327662 |
+---------+-----+--------+---------+
5 rows in set (0.38 sec)

时间相差很明显。

分析：先查询id字段做为右表，然后进行内联查询

 

MySQL允许指定外部ip访问权限

背景：公司的新项目mysql腾讯云被外网攻击，数据库被删除，并留下消息，要求我们转0.6给比特币给他，就把数据库备份给我们。

初始化的mysql用户权限只能localhost访问，外部机器访问需要添加权限：

mysql> Grant all privileges on *.* to 'root'@'%' identified by 'password' with grant option;

(%表示任意ip，如果指定ip，改为对应ip即可；‘root’是指要使用的用户名)

mysql> flush privileges;  (刷新权限，或者重启mysql)

把账号root、密码123授权给任意ip访问：

Grant all privileges on *.* to 'root'@'%' identified by '123' with grant option;

2 设置指定ip访问mysql数据库

GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.251.226.172' IDENTIFIED BY 'czp' WITH GRANT OPTION;
flush privileges;

第一行中，10.251.226.172是阿里云内网的一个地址，这个是允许远程访问的IP的值。自行改为其他值。root是账户名，后面的czp是密码。即，允许来自10.251.226.172的连接并使用root账户和czp这个密码进行访问。ALL PRIVILEGES ON 后面的*.*表示所有数据库，即完全访问权限，可以指定为特定数据库。而IP这里，可以使用%来表示所有IP。

Mysql中的排序规则utf8_unicode_ci、utf8_general_ci的区别总结

ci是 case insensitive, 即 "大小写不敏感", a 和 A 会在字符判断中会被当做一样的;

utf8_general_ci 不区分大小写，这个你在注册用户名和邮箱的时候就要使用。
utf8_general_cs 区分大小写，如果用户名和邮箱用这个 就会照成不良后果

新建数据库，选择排序规则utf8_unicode_ci