4.myisam的表可以进行压缩,以节省磁盘的空间
先制作一个大点的表:
mysql> insert into t select * from t;
Query OK, 4194304 rows affected (10.40 sec)
Records: 4194304 Duplicates: 0 Warnings: 0
看一下这个表有56M大小。
[root@rhel131 test]# ls -lh
total 57M
-rw-r--r-- 1 mysql mysql 65 Jul 11 00:17 db.opt
-rw-rw---- 1 mysql mysql 8.4K Oct 24 12:56 t.frm
-rw-rw---- 1 mysql mysql 56M Oct 24 13:05 t.MYD
-rw-rw---- 1 mysql mysql 1.0K Oct 24 13:05 t.MYI
用myisampack工具进行压缩:
[root@rhel131 test]# myisampack t
Compressing t.MYD: (8388608 records)
- Calculating statistics
- Compressing file
85.71%
压缩后只有8.1M了
[root@rhel131 test]# ls -lh
total 8.1M
-rw-r--r-- 1 mysql mysql 65 Jul 11 00:17 db.opt
-rw-rw---- 1 mysql mysql 8.4K Oct 24 12:56 t.frm
-rw-rw---- 1 mysql mysql 8.1M Oct 24 13:05 t.MYD
-rw-rw---- 1 mysql mysql 1.0K Oct 24 13:07 t.MYI
按道理压缩过后的表应该是read only的,不过不知道为什么还是能插数据。
mysql> insert into t values(1);
Query OK, 1 row affected (0.00 sec)
mysql> select count(1) from t;
+----------+
| count(1) |
+----------+
| 8388609 |
+----------+
1 row in set (0.00 sec)
不过这样操作之后,待mysql重启后这张表就会损坏
[root@rhel131 test]# mysqladmin shutdown
[root@rhel131 test]# service mysql start
Starting MySQL.......................... [ OK ]
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t |
+----------------+
1 row in set (0.00 sec)
mysql> check table t;
+--------+-------+----------+----------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+--------+-------+----------+----------------------------------------------------------+
| test.t | check | error | Size of datafile is: 8388669 Should be: 58720263 |
| test.t | check | error | Corrupt |
+--------+-------+----------+----------------------------------------------------------+
2 rows in set (0.00 sec)
check之后发现了表损坏,可以通过repair命令来修复,不过之前的数据已丢失了,所以表在压缩之后一定要注意备份。
mysql> repair table t;
+--------+--------+----------+------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+--------+--------+----------+------------------------------------------+
| test.t | repair | warning | Number of rows changed from 8388609 to 3 |
| test.t | repair | status | OK |
+--------+--------+----------+------------------------------------------+
2 rows in set (0.09 sec)
mysql> select * from t;
+-------+
| id |
+-------+
| NULL |
| 98557 |
| 1 |
+-------+
3 rows in set (0.00 sec)
所以表压缩这方面mysql还不太完美。
5.myisam支持全文索引,不过这个特性还不太成熟和完美,如果想用这个类似的功能,可以通过 instr函数或者第三方插件来实现,instr的用法跟oracle非常相似。
mysql> create table t1 (id int,name varchar(200)) engine=myisam;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values(1,'aaabbcc');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values(2,'ddeeffff');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values(3,'kkjjkkll');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1 where instr(name,'cc')>0;
+------+---------+
| id | name |
+------+---------+
| 1 | aaabbcc |
+------+---------+
1 row in set (0.01 sec)
6.管理表级锁
myisam是通过表级锁来控制表在修改时时候其它的用户的行为,分为write表级锁和read表级锁。
6.1 write 表级锁,表加上write表级锁后只有本进程才能读写操作,其它的进程不能读也不能写。
第一个session将表t的写锁住:
session 1:
mysql> lock table t write;
Query OK, 0 rows affected (0.00 sec)
session 2:
mysql> insert into t values(6);
这时被hang住了
session 1:
session 1可以插入数据
mysql> insert into t values(7);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t;
+-------+
| id |
+-------+
| NULL |
| 98557 |
| 1 |
| 5 |
| 7 |
+-------+
5 rows in set (0.00 sec)
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
session释放锁后,session可以插入数据了
session 2:
mysql> insert into t values(6);
Query OK, 1 row affected (22.00 sec)
mysql> select * from t;
+-------+
| id |
+-------+
| NULL |
| 98557 |
| 1 |
| 5 |
| 7 |
| 6 |
+-------+
6 rows in set (0.00 sec)
跟oracle比较大的不同是,表被锁住后,其它的session也不能读的操作了
session 1:
mysql> lock table t write;
Query OK, 0 rows affected (0.00 sec)
session 2:
mysql> select * from t;
被锁住了
session 1:
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
锁释放后,session才能select数据
session 2:
mysql> select * from t;
+-------+
| id |
+-------+
| NULL |
| 98557 |
| 1 |
| 5 |
| 7 |
| 6 |
+-------+
6 rows in set (33.14 sec)
6.2 read 表级锁,表加上read表级锁后本进程只能读,不能写,其它的进程也是只能读不能写。
session 1:
mysql> lock table t read;
Query OK, 0 rows affected (0.00 sec)
本进程插入数据直接返回错误:
mysql> insert into t values(2);
ERROR 1099 (HY000): Table 't' was locked with a READ lock and can't be updated
mysql> select * from t;
+-------+
| id |
+-------+
| NULL |
| 98557 |
| 1 |
| 5 |
| 7 |
| 6 |
+-------+
6 rows in set (0.01 sec)
session 2:被hang住了
mysql> insert into t values(3);
session 1:
释放锁后,session 2的新数据插入进来了
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t;
+-------+
| id |
+-------+
| NULL |
| 98557 |
| 1 |
| 5 |
| 7 |
| 6 |
| 3 |
+-------+
7 rows in set (0.00 sec)
6.3 另外还有read local的锁,只锁住当前进程目前的数据,不会锁新的数据
session 1:
本进程只能读操作,不能写操作
mysql> lock table t read local;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t values (4);
ERROR 1099 (HY000): Table 't' was locked with a READ lock and can't be updated
session 2:
发现可以插入新的数据
mysql> insert into t values(5);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t;
+-------+
| id |
+-------+
| NULL |
| 98557 |
| 1 |
| 5 |
| 7 |
| 6 |
| 3 |
| 5 |
+-------+
8 rows in set (0.00 sec)
这时如果尝试删除之前的数据,则会被锁会
mysql> delete from t where id=1;
session 1:释放锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
session 2:锁被释放后,删除操作成功
mysql> delete from t where id=1;
Query OK, 1 row affected (48.11 sec)
mysql> select * from t;
+-------+
| id |
+-------+
| NULL |
| 98557 |
| 5 |
| 7 |
| 6 |
| 3 |
| 5 |
+-------+
7 rows in set (0.00 sec)