存储引擎概念:

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)