MySQL数据库架构--什么影响了MySQL性能-中

上篇我们学习了一些服务器硬件和系统配置参数对MySQL性能的影响,这篇我们看下MySQL本身对性能的影响。

MySQL体系架构

mysql与众不同的就是这种插件式存储引擎了,这种架构将数据库查询处理及其他系统的任务以及数据的存储,提取相分离。

这种数据存储和数据处理相分离的设计呢,可以让我们在使用时,根据存储引擎不同的性能和特征以及其他需要来选择数据存储的方式。

首先要清楚mysql的体系结构:

第一层: 客户端

这一层代表可以连接到mysql服务器的客户端,例如 php java api Odbc jdbc ,跟大部分cs架构一样,这一层主要是,连接处理,授权认证,安全等一些功能,每个链接到mysql客户端都会在服务器进程中拥有一个线程,这个连接查询,只会在这个单独的线程中执行,也就是前面所说的哪个连接的查询只能用到一个cpu的核心

第二层是比较核心的

这一层包括了 查询缓存、查询解析、查询优化,以及内置函数,如常用的DDL ,DML全是在这一层定义的。总之,所有跨存储引擎的功能都是在这一层来实现的,因此这一层称之为mysql服务层。这一层中实现了与存储引擎无关的特性,什么是与存储引擎无关的特性。比如说select语句,如何从文件中获得我们所要查询的数据,这个具体的实现方式是由下一层存储引擎层来实现,这个语句对所有的存储引擎来说,所要实现的功能都是一样的,获取存储在文件中的数据,根据我们的过滤条件进行过滤,然后把数据显示出来,select语句的功能是在mysql服务层实现的,而如何从文件中获得我们要查询的数据,则是有下一层存储引擎层来实现的。

第三层存储引擎层

区别其他数据库最大的地方,最常用的存储引擎,innodb、myisam、XtraDB、CSV、Memory。根据不同的特点选择不同的存储引擎,注意,存储引擎是针对于表的而不是针对于库的,(理论上一个库里面不同的表可以使用不同的存储引擎)

image-20190110004907432

MySQL存储引擎之MyISAM

MyISAM为MySQL5.5之前版本默认存储引擎。

一般的临时表和系统表都是MyISAM。这里的临时表是指:在排序,分组等操作中,当数量超过一定的大小之后,由查询优化器建立的临时表。

MyISAM存储引擎表由MYD(数据文件)和MYI(索引文件)组成。
当我们新建一张表指定MyISAM为存储引擎的时候,会有三个文件对应生成。myIsam.frm(用于记录表结构),myIsam.MYD,myIsam.MYI。

MYD文件存储的是myIsam表的数据信息,MYI存放的是索引的信息

特性:

  • 并发性与锁级别
    是表级锁而不是行级锁,也就是说在对数据表进行修改的时候,需要对整个表进行加锁,对表进行读取时,需要对表进行加共享锁。(读取和写入是互斥的,不过有些情况下可以在读取的时候在表的末尾新增数据,总体而言对读写混合的操作并发性支持不是很友好)

  • 表损坏修复
    对与意外关闭进行修复,这里的修复不是事务修复,对表的修复可能造成数据的丢失。我们可以使用 check table tablename 进行表的检查,使用repair table tablename 进行表的修复。

下面我们看一下修复实例:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
# 新建数据库 指定存储引擎
MySQL [test]> create table myIsam( id int ,c1 varchar(10))engine=myisam;
Query OK, 0 rows affected (0.04 sec)

# 在文件系统中查看生成的文件
[root@wangerxiao test]# pwd
/usr/local/mysql/var/test
[root@wangerxiao test]# ls -l myIsam*
-rw-r----- 1 mysql mysql 8582 Mar 14 19:22 myIsam.frm
-rw-r----- 1 mysql mysql 0 Mar 14 19:22 myIsam.MYD
-rw-r----- 1 mysql mysql 1024 Mar 14 19:22 myIsam.MYI

# 使用check 检查数据表
MySQL [test]> check table myIsam;
+-------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------+-------+----------+----------+
| test.myIsam | check | status | OK |
+-------------+-------+----------+----------+
1 row in set (0.04 sec)

# 使用repair修复数据表
MySQL [test]> repair table myIsam;
+-------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------+--------+----------+----------+
| test.myIsam | repair | status | OK |
+-------------+--------+----------+----------+
1 row in set (0.01 sec)

除了使用repair外,我们还可以使用命令行myisamchk --help修复,不过要事先关闭mysql服务,否则会造成更多的数据丢失。

  • MyISAM表支持的索引类型
    支持全文索引

    而且是在mysql5.7之前版本中,唯一原生就是支持全文索引的官方存储引擎,另外myisam表还支持,test,belog等字段前字符的前缀索引,如果myisam是一张很大的只读表的时候,也就是在表创建完,导入数据后,就不会在对表进行任何操作了,那么我们就可以对这样的表进行压缩操作,这样可以减少磁盘io。

  • MyISAM表支持数据压缩
    命令: myisampack

    如果对一张表压缩,可以使用myisampack命令来对表进行压缩表中数据,对表中的数据是独立进行压缩的,在读取单数据的时候呢,不必对整个表来进行解压,下面演示下,如何对一张表来进行压缩,myisampack -b -f myIsam.MYI,对压缩后的表只能进行读操作,不能进行写操作。

实例如何进行压缩:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 进行压缩
[root@wangerxiao test]# myisampack -b -f myIsam.MYI
Compressing myIsam.MYD: (0 records)
- Calculating statistics
- Compressing file
Empty file saved in compressed format

# 压缩之前的文件为 .OLD
[root@wangerxiao test]# ls -lh myIsam*
-rw-r----- 1 mysql mysql 8.4K Mar 14 19:22 myIsam.frm
-rw-r----- 1 mysql mysql 50 Mar 14 19:28 myIsam.MYD
-rw-r----- 1 mysql mysql 1.0K Mar 14 19:38 myIsam.MYI
-rw-r----- 1 mysql mysql 0 Mar 14 19:28 myIsam.OLD

# 压缩之后,我们尝试插入数据表

MySQL [test]> insert into myIsam values(1,'aa');
ERROR 1036 (HY000): Table 'myIsam' is read only

# 提示错误,因为在压缩之后,数据表变成只读。

限制

如果在MySQL5.0之前的版本默认表大小为4G
如果想要存储大表则要修改 MAX_RowsAVG_ROW_LEnGTH
两个参数相乘的大小,就是表可能达到的最大的大小

大表修改这两个参数,等于对表进行重建,会花费一些时间

在5.0版本之后,单表支持256TB,足够我们使用

适用场景

  • 非事务型应用(报表类)

  • 只读类应用(支持压缩)

  • 空间类应用(GPS数据)

    在mysql5.7之前myisam是唯一个支持空间函数的一个存储引擎。

MySQL存储引擎之Innodb

在MySQL5.5以后版本默认引擎为Innodb

和MyISAM不同,Innodb是一种支持事务的存储引擎,更适合处理大量的小事务。Innodb使用表空间进行存储数据,具体存储在哪个存储空间是由innodb_file_per_table参数决定的。
当参数为ON时,则会为每个Innodb表建立一个以tablename.ibd命名的独立表空间。
当参数为OFF时,则会把数据存储到共享表空间(系统表空间)ibdataX(X代表数字)

查看当前数据库参数:

1
2
3
4
5
6
7
MySQL [test]> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.05 sec)

新建一张innodb的数据表:

1
2
MySQL [test]> create table myinnodb(id int,c1 varchar(10)) engine='innodb';
Query OK, 0 rows affected (0.07 sec)

查看文件系统中的存储方式:

1
2
3
[root@wangerxiao test]# ls -lh   myinno*
-rw-r----- 1 mysql mysql 8.4K Mar 14 19:58 myinnodb.frm
-rw-r----- 1 mysql mysql 96K Mar 14 19:58 myinnodb.ibd

frm文件同样是表结构文件,ibd文件就是表的存储空间。

现在我们能看当参数为OFF的时候,表的存储方式:
先将参数设置为OFF状态:

1
2
3
4
5
6
7
8
9
10
MySQL [test]> set  global innodb_file_per_table=off;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF |
+-----------------------+-------+
1 row in set (0.00 sec)

新建一张数据表

1
2
MySQL [test]> create table myinnodb_g(id int,c1 varchar(10))engine='innodb';
Query OK, 0 rows affected (0.03 sec)

查看文件系统保存方式

1
2
3
4
[root@wangerxiao test]# ls -lh   myinnodb*
-rw-r----- 1 mysql mysql 8.4K Mar 14 19:58 myinnodb.frm
-rw-r----- 1 mysql mysql 8.4K Mar 14 20:03 myinnodb_g.frm
-rw-r----- 1 mysql mysql 96K Mar 14 19:58 myinnodb.ibd

我们发现只有 frm文件,而没有ibd文件。
实际上数据存储到了数据的共享表空间(ibdata1中)中:

1
2
3
4
5
6
7
8
9
10
[root@wangerxiao test]# cd ..
[root@wangerxiao var]# ls -lh
total 218M
-rwxr-xr-x 1 mysql mysql 16K Oct 8 20:29 aria_log.00000001
-rwxr-xr-x 1 mysql mysql 52 Oct 8 20:29 aria_log_control
-rwxr-xr-x. 1 mysql mysql 56 Aug 19 2016 auto.cnf
-rw-r----- 1 mysql mysql 557 Dec 22 18:06 ib_buffer_pool
-rwxr-xr-x. 1 mysql mysql 74M Mar 14 20:03 ibdata1
-rwxr-xr-x. 1 mysql mysql 64M Mar 14 20:03 ib_logfile0
-rwxr-xr-x. 1 mysql mysql 64M Aug 19 2016 ib_logfile1

系统表空间和独立表空间要如何选择:

比较:

* 系统表空间无法简单的收缩文件大小(非常麻烦)
* 独立表空间可以通过`optimize table`命令收缩文件系统文件
     在清理大表数据之后,可以只对这一个表进行optimize操作,也会对表进行重建,但是不重启数据库服务器,不影响访问
* 系统表空间会产生IO瓶颈
     因为只存在一个表空间,因此当同时对多个表空间进行数据刷新时,在文件系统              上时顺序执行的,会产生一定得IO瓶颈。
* 独立表空间可以同时向多个文件刷新数据

建议:

对Innodb使用独立表空间(5.6版本之后已是默认)

如何把之前的版本的的共享表空间的数据表转换到独立表空间:
步骤:

  1. 使用mysqldump导出所有数据库表数据数据(数据库中使用存储过程,触发器,计划事件等一起导出)
  2. 停止服务MySQL服务,修改参数,并删除原来Innodb相关文件(磁盘空间的回收)
  3. 重启MySQL服务,重建Innodb系统表空间
  4. 重新导入数据

当我们把数据表从共享空间转移到独立表空间中,在共享表空间中还存在着一些重要的东西:Innodb 数据字典信息,Undo回滚段

Innodb存储引擎的特性:

* Innodb是一种事务性存储引擎

* 完全支持事务的ACID特性

* Redo Log 和 Undo Log

     Redo Log 主要用于实现事务的持久性,由两部分组成:内存中的重做日志缓冲区,重做日志文件。
     Undo Log 主要用于未提交事务进行回滚和实现mvcc。

     正是利用`redo Log`和`Undo Log`来实现事务的原子性,持久性,一致性。

查看缓冲区的大小:(字节为单位,每一秒就把缓冲区刷新到磁盘上)

1
2
3
4
5
6
7
8
MySQL [test]> 
MySQL [test]> show variables like 'innodb_log_buffer_size';
+------------------------+---------+
| Variable_name | Value |
+------------------------+---------+
| innodb_log_buffer_size | 8388608 |
+------------------------+---------+
1 row in set (0.00 sec)

重做日志文件的设置:

1
2
3
4
5
6
7
8
9
10
11
12
13
MySQL [test]> show variables like 'innodb_log_files_in_group';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| innodb_log_files_in_group | 2 |
+---------------------------+-------+
1 row in set (0.00 sec)

我们看到有两个log
[root@wangerxiao var]# ls -lh
total 218M
-rwxr-xr-x. 1 mysql mysql 64M Mar 14 20:03 ib_logfile0
-rwxr-xr-x. 1 mysql mysql 64M Aug 19 2016 ib_logfile1

  • Innodb 支持行级锁
  • 行级锁可以最大程度的支持并发
    进行写操作时,需要锁定的资源更少
  • 行级锁是由存储引擎层实现的(MySQL服务器是完全不知道锁的实现方式)

什么是锁?
锁是数据库系统区别文件系统的一个重要特性

  • 锁的主要作用是管理共享资源的并发访问(邮件服务,如果没有锁对邮箱进行控制,两个邮件同时对同一个邮箱进行投递,就会造成两封邮件数据重叠。有了锁之后,就会形成阻塞)

  • 锁用于实现事务的隔离性(未提交的事务,锁定的数据是不能被其他的事务所查询到的)

锁的类型

  • 共享锁(读锁)
    不会被阻塞的,多个线程可以在同一时间读取同一个资源

  • 独占锁(写锁)
    写锁是独占的,排他的,会阻塞其他的写锁或读锁,只有一个线程能够写

写锁和其他的锁都是不兼容的,读锁是和读锁是兼容的。读锁和写锁都是行级锁。

实际小栗子:
我们开启两个连接,开启两个事务,我们在其中一个连接中开启一个事务,并为第一行添加一个独占锁。
查看数据表的内容:

1
2
3
4
5
6
7
8
MySQL [test]> select * from  myinnodb;
+------+------+
| id | c1 |
+------+------+
| 2 | bb |
| 3 | cc |
+------+------+
2 rows in set (0.00 sec)

开启事务,新增锁,但是不提交:

1
2
3
4
5
6
MySQL [test]> begin;
Query OK, 0 rows affected (0.01 sec)

MySQL [test]> update myinnodb set c1="bbbb" where id = 2;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0

我们在另一个连接中查看修改的数据:

1
2
3
4
5
6
7
MySQL [test]> select  * from myinnodb where id = 2;
+------+------+
| id | c1 |
+------+------+
| 2 | bb |
+------+------+
1 row in set (0.01 sec)

我们发现,独占锁并没有阻塞其他的查询,这是因为Innodb利用了上面说的Undolog,我们在这里查到的数据实际上存储的是Undolog的数据。并不是第一个连接修改后的数据。

锁的粒度

指的是被加锁的资源的最小单位。比如在行上加锁,锁的最小单位是行,锁被称为行级锁。还有页级锁,表级锁。一种提高资源并发性的方式是让锁定义的方向尽可能的小。任何时候,锁定的资源越少,并发性越高。

  • 表级锁
    开销最小的策略(开销小,则并发性底)在MySQL服务器层使用。

  • 行级锁
    行级锁可以最大程度的支持并发处理,同时锁的开销也比表级锁的要大,行级锁只在存储引擎中实现,而MySQL服务器层并没有实现。

实际栗子:
我们看下建表的结构:

1
2
3
4
5
6
7
8
9
10
MySQL [test]> show  create  table myinnodb;
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| myinnodb | CREATE TABLE `myinnodb` (
`id` int(11) DEFAULT NULL,
`c1` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

表示Innodb,因此是默认加的行级锁,我们看下如何为表添加表级锁:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 我们在一个连接中创建一个表级的独占锁
MySQL [test]> lock table myinnodb write;
Query OK, 0 rows affected (0.00 sec)

# 然后在另一个连接中进行查询
MySQL [test]> select * from myinnodb where id = 2;

# 我们把第一个连接的表进行解锁
MySQL [test]> unlock tables;
Query OK, 0 rows affected (0.00 sec)

# 再在另一个连接中进行查询
MySQL [test]> select * from myinnodb where id = 2;
+------+------+
| id | c1 |
+------+------+
| 2 | bb |
+------+------+
1 row in set (0.00 sec)

我们发现第一个连接的表锁定时,另一个连接并没有返回查询结果,发生了阻塞。当
解锁之后,另一个连接可以正常查询。

阻塞和死锁

  • 什么是阻塞
    数据库阻塞的现象:第一个连接占有资源没有释放,而第二个连接需要获取这个资源。如果第一个连接没有提交或者回滚,第二个连接会一直等待下去,直到第一个连接释放该资源为止。对于阻塞,数据库无法处理,所以对数据库操作要及时地提交或者回滚。阻塞是为了确保事务可以并发且可以正常的运行,但如果一个系统出现大量的阻塞就说明系统出现了问题,也许是在一个被频繁更新的表上出现了慢查询,或是一个频繁使用的资源加上了写锁,阻塞过多的时候可以是数据库连接大量的堆积,从而占用大量的系统资源,使得系统性能整体下降。

  • 什么是死锁
    数据库死锁的现象:第一个连接占有资源没有释放,准备获取第二个连接所占用的资源,而第二个连接占有资源没有释放,准备获取第一个连接所占用的资源。这种互相占有对方需要获取的资源的现象叫做死锁。对于死锁,数据库处理方法:牺牲一个连接(占用资源少的),保证另外一个连接成功执行。可以由系统自动处理,如果只有少量的死锁并不会对系统造成什么样的影响,只要在应用程序中发现死锁并进行处理就可以,但是如果一个系统频繁的出现大量的死锁,通常死锁可以在多个事务中按这个相同顺序,访问所需要资源来解决,也可以通过增加相关索引来解决。

Innodb状态检查

性能检测工具

1
show engine innodb status

这个是统计上次输出之后的平均值,两次采样之间至少间隔30s

查看具体输出:

1
MySQL [test]> show  engine innodb  status \G;

适用场景

Innodb适合于大多数OLTP应用(并且支持全文索引,和空间函数)

知识就是财富
如果您觉得文章对您有帮助, 欢迎请我喝杯水!