Mysql基础原理以及应用

来源：互联网发布：lrc编辑器 mac 编辑：程序博客网时间：2024/05/21 09:56

1、数据库实现原理

第一，就是将所要保存的数据，写入文本文件。这个文本文件就是你的数据库。

为了方便读取，数据必须分成记录，每一条记录的长度规定为等长。比如，假定每条记录的长度是800字节，那么第5条记录的开始位置就在3200字节。大多数时候，我们不知道某一条记录在第几个位置，只知道主键（primary key）的值。这时为了读取数据，可以一条条比对记录。但是这样做效率太低，实际应用中，数据库往往采用B树（B-tree）格式储存数据。

要理解B树，必须从二叉查找树（Binary search tree）讲起。二叉查找树是一种查找效率非常高的数据结构，它有三个特点。

1）、每个节点最多只有两个子树。

2）、左子树都为小于父节点的值，右子树都为大于父节点的值。

3）、在n个节点中找到目标值，一般只需要log(n)次比较。

二叉查找树的结构不适合数据库，因为它的查找效率与层数相关。越处在下层的数据，就需要越多次比较。极端情况下，n个数据需要n次比较才能找到目标值。对于数据库来说，每进入一层，就要从硬盘读取一次数据，这非常致命，因为硬盘的读取时间远远大于数据处理时间，数据库读取硬盘的次数越少越好。

B树是对二叉查找树的改进。它的设计思想是，将相关数据尽量集中在一起，以便一次读取多个数据，减少硬盘操作次数。

B树的特点也有三个。

1）、一个节点可以容纳多个值。比如上图中，最多的一个节点容纳了4个值。

2）、除非数据已经填满，否则不会增加新的层。也就是说，B树追求"层"越少越好。

3）、子节点中的值，与父节点中的值，有严格的大小对应关系。一般来说，如果父节点有a个值，那么就有a+1个子节点。比如上图中，父节点有两个值（7和16），就对应三个子节点，第一个子节点都是小于7的值，最后一个子节点都是大于16的值，中间的子节点就是7和16之间的值。

这种数据结构，非常有利于减少读取硬盘的次数。假定一个节点可以容纳100个值，那么3层的B树可以容纳100万个数据，如果换成二叉查找树，则需要20层！假定操作系统一次读取一个节点，并且根节点保留在内存中，那么B树在100万个数据中查找目标值，只需要读取两次硬盘。

数据库以B树格式储存，只解决了按照"主键"查找数据的问题。如果想查找其他字段，就需要建立索引（index）。所谓索引，就是以某个字段为关键字的B树文件。假定有一张"雇员表"，包含了员工号（主键）和姓名两个字段。可以对姓名建立索引文件，该文件以B树格式对姓名进行储存，每个姓名后面是其在数据库中的位置（即第几条记录）。查找姓名的时候，先从索引中找到对应第几条记录，然后再从表格中读取。

2、Mysql体系结构

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。为了方便数据的存储和管理，它将数据按照特定的规律存储在磁盘上。通过数据库管理系统，可以有效地组织和管理存储在数据库中的数据。

当然我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。所以，现在我们使用关系型数据库管理系统来存储和管理的大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

Mysql是最流行的关系型数据库管理系统，在WEB应用方面MySQL是最好的关系数据库管理系统)应用软件之一。了解MySql必须牢牢记住其体系结构图，Mysql是由SQL接口，解析器，优化器，缓存，存储引擎组成的。

1）、最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

2）、第二层架构主要完成大多少的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

3）、存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。

4）、数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

3、MySQL基本命令

连接接mysql的命令：

# 指定socket文件和用户名、密码

mysql -S/tmp/mysql.sock -uroot -p

# 指定IP和端口

mysql -h127.0.0.1 -P3306 -uroot -p

查看mysq状态：

mysql> status--------------mysql  Ver 14.14 Distrib 5.6.35, for Linux (x86_64) using  EditLine wrapperConnection id:6Current database:Current user:root@localhostSSL:Not in useCurrent pager:stdoutUsing outfile:''Using delimiter:;Server version:5.6.35-log Source distributionProtocol version:10Connection:127.0.0.1 via TCP/IPServer characterset:utf8Db     characterset:utf8Client characterset:utf8Conn.  characterset:utf8TCP port:3306Uptime:1 hour 2 min 2 secThreads: 1  Questions: 12  Slow queries: 0  Opens: 70  Flush tables: 1  Open tables: 63  Queries per second avg: 0.003

展现当前链接：

mysql> show processlist;

+----+------+-----------------+------+---------+------+-------+------------------+

+----+------+-----------------+------+---------+------+-------+------------------+

+----+------+-----------------+------+---------+------+-------+------------------+

1 row in set (0.00 sec)

SQL是一种用于访问和处理数据库的标准计算机语言。

# 查看当前有哪些数据库：

show databases;

# 使用名为test的数据库：

use test;

# 创建一张学生表，每一张表都需要包含一个主键，主键唯一标识一条记录，唯一的字段，不可重复不能为空，通过`primary key`关键字来定义：

create table stu ( id int(10) , name varchar(20) , age int(10) , primary key(id)) ;

# 查看创建好的表：

show create table stu;

# 新加一个字段：

alter table stu add column gender varchar(20);

# 修改一个字：

alter table stu modify column gender varchar(40);

# 删除一个字段：

alter table stu drop column gender;

# 删除表：

drop table stu;

# 查看当前数据库中的表：

show tables;

# 向表中插入数据：

insert into stu(id,name,age) values(1,'pw',28);

# 插入全部字段时可以只写表名：

insert into stu values(2,'nss',29);

# 查看刚才添加的数据,"*"代表查询全部字段：

select * from stu;

# 如果只想查询两个字段，则只写要查询的字段名：

select name, age from stu;

# 也可以根据某个条件进行查询，比如只查询id为1的记录：

select name age from stu where id=1;

# 更新语句：

update stu set age=29 where id=1;

# 删除表中的数据：

delete from stu where id=1;

4、Mysql存储引擎

存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）。在Oracle 和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而MySql数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。但是，这里重点介绍InnoDB引擎。

Innodb存储引擎内存由以下三个部分组成：缓冲池、重做日志缓存、额外的内存池。Innodb存储引擎后台有7个线程，其中，4个IO线程，1个master thread，一个lock监控线程，一个错误监控线程。在以下场合下，使用InnoDB是最理想的选择：

1）、更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求。

2）、.事务。InnoDB存储引擎是支持事务的标准MySQL存储引擎。

3）、.自动灾难恢复。与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。

4）、.外键约束。MySQL支持外键的存储引擎只有InnoDB。

5）、.支持自动增加列AUTO_INCREMENT属性。

一般来说，如果需要事务支持，并且有较高的并发读取频率，InnoDB是不错的选择。

innodb引擎架构：

InnoDB的多个内存块组成了内存池，负责如下工作：

1)、维护所有进程/线程需要访问的多个内部数据结构。

2)、缓存磁盘上的数据，方便快速的读取，并且在对磁盘文件的数据进行修改之前在这里缓存。

3).、重做日志缓存。

5、Mysql事务管理

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。事务用来管理 insert,update,delete 语句.

事务测试：

mysql> use RUNOOB;Database changedmysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb;  # 创建数据表Query OK, 0 rows affected (0.04 sec) mysql> select * from runoob_transaction_test;Empty set (0.01 sec) mysql> begin;  # 开始事务Query OK, 0 rows affected (0.00 sec) mysql> insert into runoob_transaction_test value(5);Query OK, 1 rows affected (0.01 sec) mysql> insert into runoob_transaction_test value(6);Query OK, 1 rows affected (0.00 sec) mysql> commit; # 提交事务Query OK, 0 rows affected (0.01 sec) mysql>  select * from runoob_transaction_test;+------+| id   |+------+| 5    || 6    |+------+2 rows in set (0.01 sec) mysql> begin;    # 开始事务Query OK, 0 rows affected (0.00 sec) mysql>  insert into runoob_transaction_test values(7);Query OK, 1 rows affected (0.00 sec) mysql> rollback;   # 回滚Query OK, 0 rows affected (0.00 sec) mysql>   select * from runoob_transaction_test;   # 因为回滚所以数据没有插入+------+| id   |+------+| 5    || 6    |+------+2 rows in set (0.01 sec) mysql>

6、Mycat

Mycat就是一个数据库中间件，数据库的代理，它屏蔽了物理数据库，应用连接mycat，然后mycat再连接物理数据库。它支持水平拆分（分库分表，通过分库达到分表)，支持多种分片规则，比如范围切片、自然月分片、hash取模分片等。它支持mysql、oracle、mongodb、sql server，并且支持数据库集群。

使用网上的两张图片表示Mycat解决的问题：

没有Mycat以前：

有了Mycat以后：

阅读全文

0 0