基于lnmp.org 的 lnmp包，mysql版本5.5.28 主数据库服务器：192.168.2.113 […]
Month: 11月 2013
mysql观察（二）
mysql 获取随机数据 数据量为5500W
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
# myisam SELECT * FROM `table_b` AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `table_b`)) AS id) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id ASC LIMIT 5; # 耗时 0.010 SELECT * FROM `table_b` WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM `table_b`))) ORDER BY id LIMIT 1; # 耗时 0.100 SELECT * FROM `table_b` WHERE id >= (SELECT FLOOR( RAND() * ((SELECT MAX(id) FROM `table_b`)-(SELECT MIN(id) FROM `table_b`)) + (SELECT MIN(id) FROM `table_b`))) ORDER BY id LIMIT 1; # 耗时 0.100 SELECT * FROM `table_b` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `table_b`)-(SELECT MIN(id) FROM `table_b`))+(SELECT MIN(id) FROM `table_b`)) AS id) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id LIMIT 1; # 耗时 0.010 |
[…]
mysql技巧（二）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
mysql> select * from a; +----+----+ | fa | fb | +----+----+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 1 | 2 | | 2 | 3 | | 1 | 3 | | 1 | 1 | | 2 | 2 | | 3 | 3 | | 1 | 2 | | 2 | 3 | | 1 | 3 | +----+----+ 12 rows in set (0.00 sec) # 将数据输出到外部文件 mysql> SELECT * INTO OUTFILE 'D:/1.txt' FROM `a`; Query OK, 12 rows affected (0.00 sec) # 清空数据 mysql> truncate a; Query OK, 0 rows affected (0.00 sec) # 添加唯一索引 mysql> ALTER TABLE `a` ADD UNIQUE KEY ( `fa`, `fb` ); Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 # 查看表信息 mysql> desc a; +-------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+-------+ | fa | int(10) unsigned | NO | PRI | NULL | | | fb | int(10) unsigned | NO | PRI | NULL | | +-------+------------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) # 将数据导回表 mysql> LOAD DATA INFILE 'D:/1.txt' REPLACE INTO TABLE `a`; Query OK, 18 rows affected (0.03 sec) Records: 12 Deleted: 6 Skipped: 0 Warnings: 0 # 查看结果 mysql> SELECT * FROM a; +----+----+ | fa | fb | +----+----+ | 1 | 1 | | 1 | 2 | | 1 | 3 | | 2 | 2 | | 2 | 3 | | 3 | 3 | +----+----+ 6 rows in set (0.00 sec) |
浅谈MySQL存储引擎 InnoDB和MyISAM 以及行锁注意事项（转载）
mysql技巧（一）
|
1 2 3 4 5 6 7 8 9 10 11 |
# myisam SELECT * FROM table_b ORDER BY id LIMIT 100000, 10000; # 耗时：1.065s SELECT * FROM table_b WHERE id > 100000 ORDER BY id LIMIT 10000; # 耗时：0.069s # innodb SELECT * FROM table_b ORDER BY id LIMIT 100000, 10000; # 耗时：0.062s SELECT * FROM table_b WHERE id > 100000 ORDER BY id LIMIT 10000; # 耗时：0.002s |
ftp安装与配置
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
rpm -qa | grep vsftpd # 查看是否安装fps服务 # 如果未安装，使用yum来安装 yum -y install vsftpd # 安装完成后，进行配置 vim /etc/vsftpd/vsftpd.conf # 修改ftp不能匿名登录 anonymous_enable=NO # 保存退出后，开启ftp服务，并且设置为开机启动 service vsftpd start chkconfig vsfitpd on # 创建ftp用户 useradd -d /path/ -s /sbin/nologin username # 添加一个 不能使用终端登录的用户username passwd username # 设置密码 # 设置目录权限，及所属着 chown -R username /path/ chmod -R 0755 /path/ # OK,配置完成。 |
[crayon-61b1574 […]
mysql观察（一）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
# innodb SELECT COUNT(*) FROM table_a # 3145728 SELECT * FROM `table_a` ORDER BY pid LIMIT 1000000 , 30 # 耗时：3.158s SELECT * FROM `table_a` WHERE pid >= ( SELECT pid FROM `table_a` ORDER BY pid LIMIT 1000000 , 1 ) LIMIT 30 # 耗时：1.350s SELECT COUNT(*) FROM table_a # 耗时：0.719s SELECT COUNT(id) FROM table_a # 耗时：0.804s SELECT COUNT(pid) FROM table_a # 耗时：0.840s SELECT COUNT(NAME) FROM table_a # 耗时：0.910s SELECT COUNT(*) FROM table_a WHERE pid = 1 # 耗时：0.838s SELECT COUNT(id) FROM table_a WHERE pid = 1 # 耗时：1.006s SELECT COUNT(NAME) FROM table_a WHERE pid = 1 # 耗时：1.172s SELECT COUNT(pid) FROM table_a WHERE pid = 1 # 耗时：0.945s # 增加数据后 INSERT INTO table_a(`name`,`pid`) SELECT `name`,`pid` FROM table_b # 共 55956321 行受到影响, 耗时:13 min 49 sec SELECT COUNT(*) FROM table_a; # 55956321 耗时：26.468s |
[crayon-61b15741845fa1 […]