存储引擎概念:
nysql最终在磁盘上落实的下来的,不同的存储方式,
目前在mysql常用的两种的存储引擎:MylSAM lnnoDB
mysql存储引擎是数据库的组件,负责数据库的I/O操作,你所执行的操作,是由存储引擎写出到磁盘上
mysql日志种类:
1)错误日志:程序运行,启动类的故障
2)二进制日志:保存数据变更日志:
3)中继日志:缓存二进制日志数据
4)查询日志:记录查询语句的
5)慢查询日志:记录超时的查询语句
6)事务日志:
MylSAM的特点:
1)不支持事务
2)表及锁定形式,数据在更新是锁定整个表,
3)数据库在读写中相互阻塞
4)可以通过key buffer size来设置缓存索引,提高访问性能,减少磁盘IO的压力,但缓存只会索引文件,不会缓存数据
5)速度快,而且占用资源相对较少。
6)只支持全文索引
MylSAM的使用场景:
1)公司不需要事务支持
2)一般单方读取比较多的业务或单方面写入的业务
3)对数据业务一致性要求不高的业务
4)数据修改相对少的业务
5)服务器硬件资源相对差
6)使用读写并发相对较低的业务
lnnoDB介绍:
lnnoDB的特点:
1)支持事务,支持4个事务隔离级别
2)行级锁定,但是全表扫描仍会标记锁定
3)读写阻塞与事务隔离级别相关
4)表与主键以簇的方式存储
5)具有高效的缓存特性,能缓存索引,也能缓存数据
6)支持分区,表空间,类似oracle数据库
lnnoDB使用特点:
1)业务需要事务的支持
2)行级锁定对高并发有很好的适应能力,但需要通过索引来完成
3)业务跟新较频繁---论坛 微博
4)业务数据一致性要求较高 --银行业务
5)硬件设备内存较大
查看存储引擎:
MariaDB [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| CSV | YES | Stores tables as CSV files | NO | NO | NO |
| ARCHIVE | YES | gzip-compresses tables for a low storage footprint | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | YES | Allows to access tables on other MariaDB servers, supports transactions and more | YES | NO | YES |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
10 rows in set (0.00 sec)
创建一个库:
MariaDB [(none)]> create database aaa;
Query OK, 1 row affected (0.00 sec)
创建一个表:
MariaDB [aaa]> create table t1(id int(10) not null,name char(20));
Query OK, 0 rows affected (0.00 sec)
查看表的数据引擎:
MariaDB [aaa]> show table status from aaa where name='t1'\G
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 9437184
Auto_increment: NULL
Create_time: 2019-10-16 19:01:50
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
查看方式:
MariaDB [aaa]> show create table aaa.t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(10) NOT NULL,
`name` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
修改存储引擎:
MariaDB [aaa]> alter table aaa.t1 engine=myisam;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [aaa]> show create table aaa.t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(10) NOT NULL,
`name` char(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
在配置文件里添加默认存储引擎:
[root@localhost ~]# cat /etc/my.cnf
[mysqld]
default-storage-engine=myisam
查看已近改了:
MariaDB [(none)]> show engines;
MyISAM | DEFAULT | Non-transactional engine with good performance and small data footprint | NO | NO | NO |
在创建表的时候更改:
MariaDB [aaa]> create table t2(id int(10) not null, name char(20)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)
MariaDB [aaa]> show create table t2 \G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`id` int(10) NOT NULL,
`name` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)