Innodb 存储引擎 学习笔记 -Mysql体系结构

Mysql ubuntu安装：https://blog.csdn.net/weixx3/article/details/80782479 

---

数据库：物理操作系统文件或者其他形式文件的集合，Mysql数据库文件可以是frm,ibd,MYD,MYI结尾的文件

数据库实例：Mysql数据库由后台线程以及一个共享内存区组成

即可以这样理解：数据库是文件的集合，数据库实例是程序（位于用户与操作系统之间的一层数据管理软件）

---

Mysql被设计成为一个单进程多线程架构的数据库

MySQL实例在系统上的表现就是一个进程

启动实例时，mysql会去读配置文件，如果没有配置文件，mysql会按照编译时的默认参数设置启动实例。

mysql的配置文件一般放在etc/my.cnf

---

检查mysql状态

dxt@DXT:~/QiuZhao/SharedMem/ShareMem$ systemctl status mysql.service 
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: en
   Active: active (running) since 日 2019-08-11 12:38:21 CST; 3h 26min ago
 Main PID: 10874 (mysqld)
   CGroup: /system.slice/mysql.service
           └─10874 /usr/sbin/mysqld

8月 11 12:38:20 DXT systemd[1]: Starting MySQL Community Server...
8月 11 12:38:21 DXT systemd[1]: Started MySQL Community Server.

 

dxt@DXT:~/QiuZhao/SharedMem/ShareMem$ ps -ef | grep mysql
mysql    10874     1  0 12:38 ?        00:00:10 /usr/sbin/mysqld
dxt      11344 26637  0 16:06 pts/8    00:00:00 grep --color=auto mysql

mysql登录

dxt@DXT:~/QiuZhao/SharedMem/ShareMem$ sudo mysql -uroot -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.27-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.01 sec)

 

---

MySql存储引擎

1.Innodb

Innodb支持事务，其设计目标主要面向在线事务处理（OLTP）的应用

特点：行锁设计，支持外建，非锁定读（默认读取不会产生锁）

Innodb通过多版本控制（MVCC)来获得高并发，并实现了4种隔离级别，默认为：可重复读

2.MyISAM

 适用于ETL操作

MyISAM不支持事务，只有表锁，支持全文索引，它的缓冲池只缓存索引文件，而不缓冲数据文件

MyISAM存储索引表由MYD和MYI组成，MYD存储数据文件，MYI存储索引文件

 

---

连接MySQL:

即一个连接进程和数据库实例进行通信。

进程间通信方式有：管道，命名管道，TCP/IP套接字，UNIX套接字

TCP/IP套接字：

>mysql -h192.168.0.101 -u dxt -p