mysql的myisam存储引擎特性（二）

来源：互联网发布：如何写发明专利知乎编辑：程序博客网时间：2024/05/19 17:26

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)

check之后发现了表损坏，可以通过repair命令来修复，不过之前的数据已丢失了，所以表在压缩之后一定要注意备份。

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)