熟悉的存储引擎有: Myisam,innodb
什么是存储引擎:
数据表存储数据的一种格式。
使用不同格式存储数据,不同格式的特性也是不一样的。例如:innodb 存储引擎的特性有:
支持事物、支持行级锁,支持外键
查看数据库文件位置:
mysql> show global variables like '%datadir%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| datadir | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)
查看
myisam
这种搜索引擎的结构,数据,索引有单独的文件
下面三个文件支持物理的拷贝,粘贴
[root@localhost mysql_optimize]# pwd
/var/lib/mysql/mysql_optimize
[root@localhost mysql_optimize]# ls
db.opt
student.frm # 表结构文件
student.MYD # 表数据文件
student.MYI # 表索引文件
innodb 存储引擎
innodb 存储引擎数据表,有单独的“结构文件”
1. 默认情况下,当前数据库所有innodb 数据表的索引、数据、文件都合并在一起。
[root@localhost mysql_optimize]# ls -lh
total 132K
-rw-r-----. 1 mysql mysql 65 Jun 12 11:41 db.opt
-rw-r-----. 1 mysql mysql 8.5K Jun 12 12:14 student1.frm
-rw-r-----. 1 mysql mysql 96K Jun 12 12:14 student1.ibd
-rw-r-----. 1 mysql mysql 8.5K Jun 12 11:45 student.frm
-rw-r-----. 1 mysql mysql 56 Jun 12 12:13 student.MYD
-rw-r-----. 1 mysql mysql 2.0K Jun 12 12:13 student.MYI
2.数据索引所在位置:
数据库种所有的innodb 数据表的“数据,索引”集中的一个存储文件
[root@localhost mysql]# stat ibdata1
File: ‘ibdata1’
Size: 79691776 Blocks: 155648 IO Block: 4096 regular file
Device: fd01h/64769d Inode: 67162011 Links: 1
Access: (0640/-rw-r-----) Uid: ( 27/ mysql) Gid: ( 27/ mysql)
Context: system_u:object_r:mysqld_db_t:s0
Access: 2017-06-12 04:30:33.791881824 -0400
Modify: 2017-06-12 12:14:05.487200858 -0400
Change: 2017-06-12 12:14:05.487200858 -0400
Birth: -
[root@localhost mysql]# date
Mon Jun 12 12:22:32 EDT 2017
单独设置“数据/索引”文件
要为每个innodb 数据单独设置其“数据/索引”文件
每个innodb 数据表 最后形成的两种格式文件: *.frm 数据/索引文件
查看是否开启:
mysql> show variables like 'innodb_file_per_table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)
mysql> set global innodb_file_per_table=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'innodb_file_per_table%'
-> ;
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.01 sec)
* innodb 方式在写入和读出时进行排序*
CREATE TABLE "student2" (
"id" int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
"name" varchar(20) NOT NULL COMMENT '名字',
"height" decimal(6,2) NOT NULL DEFAULT '0.00' COMMENT '身高',
"introduce" text COMMENT '个人简介',
PRIMARY KEY ("id")
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8;
mysql> select * from student2;
+----+--------+--------+-------------+
| id | name | height | introduce |
+----+--------+--------+-------------+
| 10 | jack | 176.23 | I am jack |
| 16 | yuehan | 176.23 | I am yuehan |
| 19 | mary | 176.23 | I am mary |
| 23 | bob | 176.23 | I am bob |
+----+--------+--------+-------------+
4 rows in set (0.00 sec)
myisam 在进行数据的读出和写入时不进行排序
insert into student values(10,'jack',176.23,'I am jack');
insert into student values(23,'bob',176.23,'I am bob');
insert into student values(16,'yuehan',176.23,'I am yuehan');
insert into student values(19,'mary',176.23,'I am mary');
mysql> mysql> select * from student;
+----+--------+--------+-------------+
| id | name | height | introduce |
+----+--------+--------+-------------+
| 1 | tom | 175.34 | I am tom |
| 2 | tom | 175.34 | I am tom |
| 10 | tom | 175.23 | I am jack |
| 23 | bob | 176.23 | I am bob |
| 16 | yuehan | 176.23 | I am yuehan |
| 19 | mary | 176.23 | I am mary |
+----+--------+--------+-------------+
6 rows in set (0.00 sec)
发现,数据的写入顺序与读出顺序保持一致
启发: 该Myisam 数据表存入数据的时候,不给排序,按照写入的顺序进行存储。这样做的好处,写入速度非常快
innodb 数据表,数据的写入顺序与存储的顺序不一致,需要按照主键的顺序把书摆放到该放的位置上去。写入速度 比 myisam 速度慢
并发性
myisam 并发行稍低,多人同时请求速度稍慢。
锁机制: 每次都锁住整张数据表
innodb 并发行要高一点,多人同时请求,速度、销量高
锁机制:行锁,每次只锁住一条记录信息。
压缩机制
如果一个myisam 数据表存储的数据非常多,就会占据非常很大的硬盘空间,为了优化,可以把myisam 数据表给进行压缩处理。这样节省硬盘空间的资源。
mysql> insert into student select null,name,height,introduce from student ;
Query OK, 786432 rows affected (3.23 sec)
Records: 786432 Duplicates: 0 Warnings: 0
mysql> flush table student;
Query OK, 0 rows affected (0.00 sec)
[root@localhost mysql_optimize]# myisampack student
Compressing student.MYD: (1572864 records)
- Calculating statistics
- Compressing file
58.45%
Remember to run myisamchk -rq on compressed tables
* 重建索引*
根据压缩后的数据把索引重新建立起来
重建索引工具: myisamchk
[root@localhost mysql_optimize]# myisamchk -rq student
- check record delete-chain
- recovering (with sort) MyISAM-table 'student'
Data records: 1572864
- Fixing index 1
[root@localhost mysql_optimize]# ls -lh
-rw-r-----. 1 mysql mysql 8.5K Jun 12 11:45 student.frm
-rw-r-----. 1 mysql mysql 18M Jun 12 14:34 student.MYD
-rw-r-----. 1 mysql mysql 16M Jun 12 14:47 student.MYI
进行压缩后,myisam 表无法再进行数据的写入,
有的数据表适合压缩,数据不频繁发生变换的数据合适,例如全国的邮编信息、用户的收货地址信息。
mysql> insert into student select null,name,height,introduce from student ;
ERROR 1036 (HY000): Table 'student' is read only
* 解压缩*
如果必须要对压缩的数据表进行写操作,就解压:
myisamchk –unpakc 表名
解压缩的同时,索引会自动重建
[root@localhost mysql_optimize]# myisamchk --unpack student
- recovering (with sort) MyISAM-table 'student'
Data records: 1572864
- Fixing index 1
mysql> flush table student ;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into student values(null,'jack',176.23,'I am jack');
Query OK, 1 row affected (0.01 sec)
memory 存储引擎
内存存储引擎,
- 特点: 内部数据运行速度非常快
- 特点:服务器如果断电,就会清空该存储引擎中的数据。