MySQL架构组成

MySQL逻辑架构

MySQL架构介绍

MySQL 由连接池、SQL 接口、解析器、优化器、缓存、存储引擎等组成，可以分为四层，即连接 层、 服务层、引擎层和文件系统层

MySQL基础架构图：

  • 连接层
    • 最上面是一些客户端和连接服务, 不是MySQL特有的，所有基于网络的C/S的网络应用程序都应该包括 连接处理、认证、安全管理等。
  • 服务层
    • 中间层是MySQL的核心，包括查询解析、分析、优化和缓存等。同时它还提供跨存储引擎的功能，包括 存储过程、触发器和视图等。
  • 引擎层
    • 存储引擎层，它负责存取数据。服务器通过API可以和各种存储引擎进行交互。不同的存储引擎具有不同 的功能,我们可以根据实际需求选择使用对应的存储引擎
  • 存储层
    • 数据存储层,主要是将数据存储在运行于裸设备的文件系统之上,并完成与存储引擎的交互

SQL查询流程

我们用一条 SQL SELECT 语句的执行轨迹来说明客户端与 MySQL 的交互过程，如下图所示：

  1. 通过客户端/服务器通信协议与 MySQL 建立连接
  2. 查询缓存，这是 MySQL 的一个可优化查询的地方，如果开启了 Query Cache 且在查询缓存过程中查 询到完全相同的 SQL 语句，则将查询结果直接返回给客户端；如果没有开启Query Cache 或者没有查询到 完全相同的 SQL 语句则会由解析器进行语法语义解析，并生成解析树。
  3. 预处理器生成新的解析树。
  4. 查询优化器生成执行计划。
  5. 查询执行引擎执行 SQL 语句，此时查询执行引擎会根据 SQL 语句中表的存储引擎类型，以及对应的 API 接口与底层存储引擎缓存或者物理文件的交互情况，得到查询结果，由MySQL Server 过滤后将查询结 果缓存并返回给客户端。若开启了 Query Cache，这时也会将SQL 语句和结果完整地保存到 Query Cache 中，以后若有相同的 SQL 语句执行则直接返回结果。

MySQL物理文件

物理文件包括：日志文件，数据文件，配置文件

日志文件

日志文件包括：

  • error log 错误日志 排错 /var/log/mysqld.log【默认开启】
  • bin log 二进制日志 备份 增量备份 DDL DML DCL
  • Relay log 中继日志 复制 接收 replication master
  • slow log 慢查询日志 调优 查询时间超过指定值
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 查看错误日志文件路径
show variables like 'log_error';
+---------------+---------------------+
| Variable_name | Value |
+---------------+---------------------+
| log_error | /var/log/mysqld.log |
+---------------+---------------------+

-- 慢查询日志文件路径
show variables like 'slow_query_log_file';
+---------------------+-----------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------+
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+


-- bin log 日志文件 需要在 my.cnf 中配置
log-bin=/var/log/mysql-bin/bin.log
server-id=2
-- 查看 relay log 相关参数
show variables like '%relay%';

配置文件

配置文件 my.cnf

在 my.cnf 文件中可以进行一些参数设置, 对数据库进行调优。

1
2
3
4
5
6
7
8
9
10
11
12
13
[client] #客户端设置，即客户端默认的连接参数
port = 3307 #默认连接端口
socket = /data/mysqldata/3307/mysql.sock #用于本地连接的socket套接字
default-character-set = utf8mb4 #编码
[mysqld] #服务端基本设置
port = 3307 MySQL监听端口
socket = /data/mysqldata/3307/mysql.sock #为MySQL客户端程序和服务器之间的本地通讯指定一
个套接字文件
pid-file = /data/mysqldata/3307/mysql.pid #pid文件所在目录
basedir = /usr/local/mysql-5.7.11 #使用该目录作为根目录（安装目录）
datadir = /data/mysqldata/3307/data #数据文件存放的目录
tmpdir = /data/mysqldata/3307/tmp #MySQL存放临时文件的目录
character_set_server = utf8mb4 #服务端默认编码（数据库级别）

数据文件

1
2
3
4
5
6
7
-- 查看数据文件的位置
show variables like '%dir%';
+-----------------------------------------+----------------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------------+
| datadir | /var/lib/mysql/ |
+-----------------------------------------+----------------------------+
  • .frm文件 不论是什么存储引擎，每一个表都会有一个以表名命名的.frm文件，与表相关的元数据（meta)信息都存放在 此文件中，包括表结构的定义信息等。
  • .MYD文件 myisam存储引擎专用，存放myisam表的数据（data)。每一个myisam表都会有一个.MYD文件与之呼应，同 样存放在所属数据库的目录下
  • .MYI文件 也是myisam存储引擎专用，存放myisam表的索引相关信息。每一个myisam表对应一个.MYI文件，其存放的 位置和.frm及.MYD一样
  • .ibd文件 存放innoDB的数据文件（包括索引）。
  • . db.opt文件 此文件在每一个自建的库里都会有，记录这个库的默认使用的字符集和校验规。

MySQL的备份与恢复

数据库备份的应用场景

  • 数据丢失应用场景
    • 系统硬件或软件故障
    • 自然灾害,比如水灾 火灾 地震等
    • 黑客攻击,非法访问者故意破坏
    • 误操作 , 人为的误操作占比最大
  • 非数据丢失应用场景
    • 开发测试环境数据库搭建
    • 数据库或者数据迁移

数据备份的类型

按照业务方式分

  • 完全备份
    • 将数据库的全部信息进行备份，包括数据库的数据文件、日志文件，还需要备份文件的存储 位置以及数据库中的全部对象和相关信息
  • 差异备份
    • 备份从最近的完全备份后对数据所做的修改，备份完全备份后变化了的数据文件、日志文件 以及数据库中其他被修改的内容
  • 增量备份
    • 增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增 加或者被修改的文件

完全备份 差异备份 增量备份
备份方式 备份所有文件 一次全备份后，备份与全备份差异的部分 一次全备份后，备份与上次备份的差异部分
备份速度 最慢 较快 最快
恢复速度 最快 较快 最慢
空间要求 最多 较多 最少
优势 最快的恢复速度，只需要上一次完全备份就能恢复 相比增量，更快也更简单，并且只需要最近一次的 完全备份和最后一次的差异备份就能恢复 备份速度快，较少的空间需求，没有重复的备份文件
劣势 最多的空间需求大量重复的备份 较慢的备份速度，仍然会存在许多的备份文件 最慢的恢复速度恢复需要最近一次完全备份和全部增量备份

备份的组合方式

  • 完全备份与差异备份

以每周数据备份为例，可以在星期一进行完全备份，在星期二至星期六进行差异备份。如果在星期 六数据被破坏了，则只需要还原星期一完全的备份和星期五的差异备份。

这种策略备份数据需要较多的时间，但还原数据使用较少的时间。

  • 完全备份与增量备份

以每周数据备份为例，在星期一进行完全备份，在星期二至星期六进行增量备份。如果在星期六数 据被破坏了，则需要还原星期一正常的备份和从星期二至星期五的所有增量备份。

这种策略备份数据需要较少的时间，但还原数据使用较长的时间。

MySQL冷备份和热备份

冷备份和热备份指的是, 按照数据库的运行状态分类

冷备份

冷备份指的是当数据库进行备份时, 数据库不能进行读写操作, 即数据库要下线

冷备份的优点：

  • 是操作比较方便的备份方法（只需拷贝文件）
  • 低度维护，高度安全

冷备份的缺点：

  • 在实施备份的全过程中，数据库必须要作备份而不能作其它工作
  • 若磁盘空间有限，只能拷贝到磁带等其它外部存储设备上，速度比较慢慢
  • 不能按表或按用户恢复

热备份

热备份是在数据库运行的情况下，备份数据库操作的sql语句，当数据库发生问题时，可以重新执行一遍备份的sql语句

热备份的优点：

  • 可在表空间或数据文件级备份，备份时间短。
  • 备份时数据库仍可使用。
  • 可达到秒级恢复（恢复到某一时间点上）

热备份的缺点：

  • 不能出错，否则后果严重
  • 因难维护，所以要特别仔细小心

实战演练

冷备份实战

关闭SELinux

  • 修改 selinux 配置文件,将SELINUX=enforcing改为SELINUX=disabled，保存后退出
1
2
3
vim /etc/selinux/config

SELINUX=disabled
  • 修改后需要重启

找到MySQL数据文件位置,停止MySQL服务

1
2
3
4
SHOW VARIABLES LIKE '%dir%';
-- 结果显示, 数据目录就是datadir的所在位置，即 /var/lib/mysql/
service mysqld stop -- 停止mysql

进入到 /mysql 目录, 执行打包命令 将数据文件打包备份

1
2
cd /var/lib/ # 进入其上级目录
tar jcvf /root/backup.tar.bz2 mysql/ # 打包压缩到 root目录下

删除掉数据目录下的所有数据

1
2
# 删除原目录
rm -rf /var/lib/mysql/

恢复数据 (使用tar命令)

1
2
3
4
5
# 解压
tar jxvf backup.tar.bz2 mysql/
# 把备份的文件移动到/var/lib/里面去替代原来的mysql
mv /root/mysql/ /var/lib/

启动MySQL, 然后登陆MySQL,查看数据是否丢失, 如果数据正常代表冷备成功

1
service mysqld start

热备份实战

  • mysqldump 备份工具

mysqldump是MySQL数据库用来备份和数据转移的一个工具，一般在数据量很小的时候（几个G） 可以用于备份。热备可以对多个库进行备份，可以对单张表或者某几张表进行备份。

  • 备份单个数据库

创建文件夹 , 备份数据

1
2
3
mkdir databackup
cd databackup
mysqldump -uroot -p test_bap > test_bap.sql

模拟数据丢失，删除数据库,然后重新创建一个新的库

1
2
DROP DATABASE test_bap;
CREATE DATABASE test_bap CHARACTER SET 'utf8';

恢复数据

1
2
3
cd databackup

mysql -uroot -p test_bap < test_bap.sql
  • 备份数据库的某些表

备份 表数据

1
mysqldump -uroot -p test_bap course course_lesson > backup_table.sql

模拟数据丢失，删除数据表

1
2
DROP TABLE course;
DROP TABLE course_lesson;

恢复数据

1
mysql -uroot -p test_bap < backup_table.sql
  • 直接将MySQL数据库压缩备份

备份数据

1
mysqldump -uroot -p test_bap | gzip > test_bap.sql.gz

模拟数据丢失，删除数据库

1
2
DROP DATABASE test_bap;
CREATE DATABASE test_bap CHARACTER SET 'utf8';

恢复数据

1
gunzip < test_bap.sql.gz | mysql -uroot -p test_bap

MySQL查询和慢查询日志分析

SQL性能下降的原因

执行性能下降可以体现在以下两个方面：

  • 等待时间长
    • 锁表导致查询一直处于等待状态
  • 执行时间长
    • 查询语句写的烂
    • 索引失效
    • 关联查询太多join
    • 服务器调优及各个参数的设置

需要遵守的优化原则

查询优化是一个复杂的工程，涉及从硬件到参数配置、不同数据库的解析器、优化器实现、SQL 语句 的执行顺序、索引以及统计信息的采集等等方面

介绍几个编写SQL的关键原则，可以帮助我们编写出更加高效的 SQL 查询

  • 第一条: 只返回需要的结果
    • 一定要为查询语句指定 WHERE 条件，过滤掉不需要的数据行
    • 避免使用 select * from , 因为它表示查询表中的所有字段
  • 第二条: 确保查询使用了正确的索引
    • 经常出现在 WHERE 条件中的字段建立索引，可以避免全表扫描；
    • 将 ORDER BY 排序的字段加入到索引中，可以避免额外的排序操作；
    • 多表连接查询的关联字段建立索引，可以提高连接查询的性能；
    • 将 GROUP BY 分组操作字段加入到索引中，可以利用索引完成分组
  • 第三条: 避免让索引失效
    • 在 WHERE 子句中对索引字段进行表达式运算或者使用函数都会导致索引失效
    • 使用 LIKE 匹配时，如果通配符出现在左侧无法使用索引
    • 如果 WHERE 条件中的字段上创建了索引，尽量设置为 NOT NULL

SQL的执行顺序

  • 程序员编写的SQL

select distinct 、from、 join on、where、group by、having、order by、limit

  • MySQL执行的SQL

from、on、join、where、group by、having、select、distinct、order by、limit

1
2
3
4
5
6
7
8
9
10
1. FORM子句 : 左右两个表的笛卡尔积
2. ON: 筛选满足条件的数据
3. JOIN： 如果是 inner join 那就正常,如果是 outer join 则会添加回来上面一步过滤掉的一些行
4. WHERE： 对不满足条件的行进行移除, 并且不能恢复
5. GROUP BY: 分组后只能得到每组的第一行数据,或者聚合函数的数值
6. HAVING： 对分组后的数据进行筛选
7. SELECT： 执行select操作，获取需要的列。
8. DISTINCT： 去重
9. ORDER BY: 排序
10. LIMIT：取出指定行的记录, 并将结果返回。
  • 查看下面的SQL 分析执行顺序
1
2
3
4
5
6
7
8
9
10
select
	id,
	sex,
	count(*) AS num
from
	employee
where name is not null
group by sex
order by id

上面的SQL执行执行顺序如下：

1
2
3
4
5
1. 首先执行 FROM 子句, 从 employee 表组装数据源的数据
2. 执行 WHERE 子句, 筛选 employee 表中所有name不为 NULL 的数据
3. 执行 GROUP BY 子句, 按 "性别" 列进行分组
4. 执行select操作，获取需要的列。
5. 最后执行order by，对最终的结果进行排序。

JOIN查询的七种方式

7种JOIN ,可以分为四类: 内连接 、左连接 、右连接、 全连接

JOIN查询SQL编写

创建表 插入数据

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
-- 部门表
DROP TABLE IF EXISTS `t_dept`;
CREATE TABLE `t_dept` (
    `id` varchar(40) NOT NULL,
    `name` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 员工表
DROP TABLE IF EXISTS `t_emp`;
CREATE TABLE `t_emp` (
    `id` varchar(40) NOT NULL,
    `name` varchar(40) DEFAULT NULL,
    `age` int(3) DEFAULT NULL,
    `deptid` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`),
    KEY `deptid` (`deptid`),
    CONSTRAINT `deptid` FOREIGN KEY (`deptid`) REFERENCES `t_dept` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 插入部门数据
INSERT INTO `t_dept` VALUES ('1', '研发部');
INSERT INTO `t_dept` VALUES ('2', '人事部');
INSERT INTO `t_dept` VALUES ('3', '财务部');
-- 插入员工数据
INSERT INTO `t_emp` VALUES ('1', '赵四', 23, '1');
INSERT INTO `t_emp` VALUES ('2', '刘能', 25, '2');
INSERT INTO `t_emp` VALUES ('3', '广坤', 27, '1');
INSERT INTO `t_emp` VALUES ('4', '玉田', 43, NULL);

内连接

1
SELECT * FROM t_emp e INNER JOIN t_dept d ON e.deptid = d.id

左连接：

1
SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptid = d.id

左连接去重叠部分：

1
SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptid = d.id WHERE e.deptid IS NULL;

右连接：

1
SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptid = d.id

右连接去重叠部分：

1
2
SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptid = d.id
WHERE e.id IS NULL;

全连接：

1
2
3
SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptid = d.id
UNION
SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptid = d.id;

全连接各自独有：

1
2
3
4
5
SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptid = d.id
WHERE e.deptid IS NULL
UNION
SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptid = d.id
WHERE e.id IS NULL;

慢查询日志分析

慢查询介绍

  • MySQL的慢查询，全名是慢查询日志，是MySQL提供的一种日志记录，用来记录在MySQL中响应时间 超过阈值的语句。
  • 默认情况下，MySQL数据库并不启动慢查询日志，需要手动来设置这个参数。
  • 如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影 响。 慢查询日志支持将日志记录写入文件和数据库表。

慢查询参数

执行下面的语句：

1
SHOW VARIABLES LIKE "%query%";

MySQL 慢查询的相关参数解释:

  • slow_query_log：是否开启慢查询日志， 1 表示开启， 0 表示关闭。
  • slow-query-log-file：(5.6及以上版本）MySQL数据库慢查询日志存储路径。
  • long_query_time： 慢查询阈值，当查询时间多于设定的阈值时，记录日志

慢查询配置方式

默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的

1
2
3
4
5
6
7
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-----------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+

可以通过设置slow_query_log的值来开启

1
2
3
4
5
6
7
8
9
mysql> set global slow_query_log=1;
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-----------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+

使用 set global slow_query_log=1 开启了慢查询日志只对当前数据库生效，MySQL重启后则 会失效。如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 编辑配置
vim /etc/my.cnf
-- 添加如下内容
slow_query_log =1
slow_query_log_file=/var/lib/mysql/lagou-slow.log

-- 重启MySQL
service mysqld restart

mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/lagou-slow.log |
+---------------------+-------------------------------+

那么开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢？ 这个是由参数 long_query_time 控制，默认情况下long_query_time的值为10秒.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

mysql> set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

修改了变量long_query_time，但是查询变量long_query_time的值还是10，难道没有修改到 呢？注意：使用命令 set global long_query_time=1 修改后，需要重新连接或新开一个会话才能 看到修改值

1
2
3
4
5
6
7
mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+

log_output 参数是指定日志的存储方式。 log_output=’FILE’ 表示将日志存入文件，默认值 是’FILE’。 log_output=’TABLE’ 表示将日志存入数据库，这样日志信息就会被写入到 mysql.slow_log 表中

1
2
3
4
5
6
mysql> SHOW VARIABLES LIKE '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+

MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如： log_output=’FILE,TABLE’。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资 源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件

系统变量 log-queries-not-using-indexes ：未使用索引的查询也被记录到慢查询日志中（可选 项）。如果调优的话，建议开启这个选项

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF |
+-------------------------------+-------+

mysql> set global log_queries_not_using_indexes=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON |
+-------------------------------+-------+
1 row in set (0.00 sec)

MySQL存储引擎

存储引擎介绍

存储引擎就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方 法。就像汽车的发动机一样, 存储引擎好坏 决定的数据库提供的功能和性能

存储引擎的作用:

  • 并发性
  • 事务支持
  • 引用完整性
  • 索引支持

常见的3种存储引擎

MySQL给用户提供了很多种类的存储引擎, 主要分两大类：

  • 事务安全表: InnoDB
  • 非事务安全表: MyISAM、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、 CSV、BLACKHOLE、FEDERATED等

查看MySQL数据的存储引擎有哪些：

1
SHOW ENGINES;

查看当前的默认存储引擎 (MySQL5.7 默认使用 InnoDB):

1
SHOW VARIABLES LIKE '%default_storage_engine%';

在MySQL中,不需要整个服务器都是用同一种引擎,针对具体的需求,可以对 每一个表使用不同的存 储引擎 .并且想要进一步优化, 还可以自己编写一个存储引擎

InnoDB

InnoDB是一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用，为用户操作非常大 的数据存储提供了一个强大的解决方案。InnoDB还引入了行级锁定和外键约束，在以下场合下，使用 InnoDB是最理想的选择

优点：

  • lnnodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别
  • 支持多版本并发控制的行级锁，由于锁粒度小，写操作和更新操作并发高、速度快
  • 支持自增长列
  • 支持自增长列
  • 适合于大容量数据库系统，支持自动灾难恢复

缺点：

  • 它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表

应用场景：

  • 当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率
  • 更新密集的表, InnoDB存储引擎特别适合处理多重并发的更新请求

MyISAM

MyISAM引擎, 不支持事务、也不支持外键，优势是访问速度快，对事务完整性没有 要求或者以select， insert为主的应用基本上可以用这个引擎来创建表

优点：

  • MyISAM表是独立于操作系统的，这说明可以轻松地将其从Windows服务器移植到Linux服务器
  • MyISAM存储引擎在查询大量数据时非常迅速，这是它最突出的优点
  • 进行大批量插入操作时执行速度也比较快

缺点：

  • MyISAM表没有提供对数据库事务的支持
  • 不支持行级锁和外键
  • 不适合用于经常UPDATE（更新）的表，效率低

应用场景：

  • 以读为主的业务，例如：图片信息数据库，博客数据库，商品库等业务。
  • 对数据一致性要求不是非常高的业务（不支持事务）
  • 硬件资源比较差的机器可以用 MyiSAM （占用资源少）

MEMORY

MEMORY的特点是 将表中的数据放在内存中，适用于存储临时数据的临时表和数据仓库中的纬度表

优点：

  • memory类型的表访问非常的快，因为它的数据是放在内存中的

缺点：

  • 一旦服务关闭，表中的数据就会丢失掉
  • 只支持表锁，并发性能差，不支持TEXT和BLOB列类型，存储varchar时是按照char的方式

应用场景：

  • 目标数据较小，而且被非常频繁地访问。
  • 如果数据是临时的，而且要求必须立即可用，那么就可以存放在内存表中。
  • 存储在Memory表中的数据如果突然丢失，不会对应用服务产生实质的负面影响。

如何选择存储引擎

不同的存储引擎都有各自的特点，以适应不同的需求，如表所示。为了做出选择，首先要考虑每一个 存储引擎提供了哪些不同的功能

特性 InnoDB MyISAM MEMORY
存储限制(Storage limits) 64TB No YES
支持事物(Transactions) Yes No No
锁机制(Locking granularity) 行锁 表锁 表锁
B树索引(B-tree indexes) Yes Yes Yes
哈希索引(Hash indexes) Yes No Yes
外键支持(Foreign key support) Yes No No
存储空间消耗(Storage Cost) 高 低 低
内存消耗(Memory Cost) 高 低 高
批量数据写入效率(Bulk insert speed) 慢 快 快

提供几个选择标准，然后按照标准，选择对应的存储引擎：

  • 是否需要支持事务
  • 崩溃恢复，能否接受崩溃
  • 是否需要外键支持
  • 存储的限制
  • 对索引和缓存的支持

MySQL索引优化

索引简介

索引就是排好序的，帮助我们进行快速查找的数据结构

简单来讲，索引就是一种将数据库中的记录按照特殊形式存储的数据结构。通过索引，能够显著地提高 数据查询的效率，从而提升服务器的性能

专业一点来说呢，索引是一个排好序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行 的物理地址。在数据库十分庞大的时候，索引可以大大加快查询的速度，这是因为使用索引后可以不用 扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据

  • 没有用索引时执行 select * from where t.Col2 =, 数据从磁盘一条一条拿去最终找到结果， 效率低下
  • 为了加快查找，可以维护一个二叉树，左侧节点小于父节点， 右侧节点大于父节点，每个节点分别保存字段数据和一个指向对应数据记录物理地址的指针、
  • 查找时就可以使用二叉树查找获取相应的数据，从而快速检索出符合条件的记录

索引的种类

  • 普通索引

这是最基本的索引类型，基于普通字段建立的索引，没有任何限制

1
2
3
CREATE INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名);
CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名) );
  • 唯一索引

与普通索引类似，不同的就是：索引字段的值必须唯一，但允许有空值

1
2
3
4
CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);
CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (字段名) ;

  • 复合索引

用户可以在多个列上建立索引，这种索引叫做组复合索引（组合索引）。复合索引可以代替多个单一索引，相比多个单一索引复合索引所需的开销更小。

1
2
3
CREATE INDEX <索引的名字> ON tablename (字段名1，字段名2...);
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1，字段名2...);
CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名1，字段名2...) );

复合索引使用注意事项:

何时使用复合索引，要根据where条件建索引，注意不要过多使用索引，过多使用会对 更新操作效率有很大影响。

如果表已经建立了(col1，col2)，就没有必要再单独建立（col1）；如果现在有(col1)索 引，如果查询需要col1和col2条件，可以建立(col1,col2)复合索引，对于查询有一定提高。

  • 全文索引
    • 查询操作在数据量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很 低。如果使用全文索引，查询速度会比like快很多倍。在MySQL 5.6 以前的版本，只有MyISAM存储引擎支持全文索引，从MySQL 5.6开始MyISAM和InnoDB存储引擎均支持
    • 全文索引方式有自然语言检索 IN NATURAL LANGUAGE MODE 和布尔检索 IN BOOLEAN MODE 两种
1
2
3
CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);
CREATE TABLE tablename ( [...], FULLTEXT KEY [索引的名字] (字段名);
  • 和常用的like模糊查询不同，全文索引有自己的语法格式，使用 match 和 against 关键字，比如:
1
2
3
4
SELECT * FROM users3 WHERE MATCH(NAME) AGAINST('aabb');

-- * 表示通配符,只能在词的后面
SELECT * FROM users3 WHERE MATCH(NAME) AGAINST('aa*' IN BOOLEAN MODE);

全文索引使用注意事项:

1
2
3
全文索引必须在字符串、文本字段上建立。 

全文索引字段值必须在最小字符和最大字符之间的才会有效。（innodb：3-84； myisam：4-84）

索引的优势与劣势

优点：

  • 提高数据检索的效率,降低数据库的IO成本
  • 通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗

缺点：

  • 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
  • 索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间
  • 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速 度

创建索引的原则：

  • 在经常需要搜索的列上创建索引，可以加快搜索的速度
  • 在作为主键的列上创建索引，强制该列的唯一性和组织表中数据的排列结构
  • 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度
  • 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
  • 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快 排序查询时间
  • 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度

索引原理

MySQL中索引的常用数据结构有两种，一种是Hash，另一种是B+Tree

HASH结构

  • Hash底层实现是由Hash表来实现的，是根据键值 存储数据的结构。非常适合根据 key查找value值，也就是单个key查询，或者说等值查询
    • 对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值， 并且不同键值的行计算出来的哈希码也不一样

  • Hash索引的缺点
    • 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取 行。
    • 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
    • 哈希索引只支持等值比较查询。不支持任何范围查询和部分索引列匹配查找。
  • Hsah索引的优点
    • 只需要做等值比较查询，而不包含排序或范围查询的需求，都适合使用哈希索引
    • 访问哈希索引的数据非常快，除非有很多哈希冲突

B+Tree结构

MySQL数据库索引采用的是B+Tree结构，在B-Tree结构上做了优化改造

B+Tree结构：

  • 非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
  • 叶子节点包含了所有的索引值和data数据
  • 叶子节点用指针连接，提高区间的访问性能

EXPLAIN性能分析

EXPLAIN简介

  • 概述

    • 使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的 SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

  • EXPLAIN的作用

    • 表的读取顺序。（对应id）
    • 数据读取操作的操作类型。（对应select_type）
    • 哪些索引可以使用。（对应possible_keys）
    • 哪些索引被实际使用。（对应key）
    • 表直接的引用。（对应ref）
    • 每张表有多少行被优化器查询。（对应rows）

  • EXPLAIN的入门

    • explain使用：explain+sql语句，通过执行explain可以获得sql语句执行的相关信息
    1
    explain select * from course;

EXPLAIN字段介绍

数据准备：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 创建数据库
CREATE DATABASE test_explain CHARACTER SET 'utf8';

-- 创建表
CREATE TABLE L1(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );
CREATE TABLE L2(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );
CREATE TABLE L3(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );
CREATE TABLE L4(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );

-- 每张表插入3条数据
INSERT INTO L1(title) VALUES('test01');
INSERT INTO L2(title) VALUES('test02');
INSERT INTO L3(title) VALUES('test03');
INSERT INTO L4(title) VALUES('test04');

ID介绍

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

  • id相同，执行顺序由上至下
1
EXPLAIN SELECT * FROM L1,L2,L3 WHERE L1.id=L2.id AND L2.id = L3.id;

  • id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
1
2
3
EXPLAIN SELECT * FROM L2 WHERE id = (
SELECT id FROM L1 WHERE id = (SELECT L3.id FROM L3 WHERE L3.title =
'test03'));

select_type和table介绍

查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询

  • simple : 简单的select查询，查询中不包含子查询或者UNION
1
EXPLAIN SELECT * FROM L1;

  • primary : 查询中若包含任何复杂的子部分，最外层查询被标记
1
2
3
EXPLAIN SELECT * FROM L2 WHERE id = (
SELECT id FROM L1 WHERE id = (SELECT L3.id FROM L3 WHERE L3.title =
'test03'));

  • subquery : 在select或where列表中包含了子查询
1
2
EXPLAIN SELECT * FROM L2 WHERE L2.id = (SELECT id FROM L3 WHERE L3.title =
'test03' )

  • derived : 在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查 询， 把结果放到临时表中
  • union : 如果第二个select出现在UNION之后，则被标记为UNION，如果union包含在from子句 的子查询中，外层select被标记为derived
  • union result : UNION 的结果
1
2
3
EXPLAIN SELECT * FROM L2
UNION
SELECT * FROM L3

type介绍

type显示的是连接类型，是较为重要的一个指标。下面给出各种连接类型,按照从最佳类型到最坏类型 进行排序：

1
2
3
4
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge >
unique_subquery > index_subquery > range > index > ALL
-- 简化
system > const > eq_ref > ref > range > index > ALL
  • system : 表仅有一行 (等于系统表)。这是const连接类型的一个特例,很少出现
  • const : 表示通过索引 一次就找到了, const用于比较 primary key 或者 unique 索引. 因为只匹配 一行数据,所以如果将主键 放在 where条件中, MySQL就能将该查询转换为一个常量
1
EXPLAIN SELECT * FROM L1 WHERE L1.id = 1;

  • eq_ref : 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配. 常见与主键或唯一索引扫描
1
EXPLAIN SELECT * FROM L1 ,L2 WHERE L1.id = L2.id;

  • ref : 非唯一性索引扫描, 返回匹配某个单独值的所有行, 本质上也是一种索引访问, 它返回所有匹配 某个单独值的行, 这是比较常见连接类型

未加索引之前：

1
EXPLAIN SELECT * FROM L1 ,L2 WHERE L1.title = L2.title;

加索引之后：

1
2
CREATE INDEX idx_title ON L2(title);
EXPLAIN SELECT * FROM L1 ,L2 WHERE L1.title = L2.title;

  • range : 只检索给定范围的行,使用一个索引来选择行
1
2
EXPLAIN SELECT * FROM L1 WHERE L1.id > 10;
EXPLAIN SELECT * FROM L1 WHERE L1.id IN (1,2);

key显示使用了哪个索引. where 子句后面 使用 between 、< 、> 、in 等查询, 这种范围查询要比 全表扫描好

  • index : 出现index 是 SQL 使用了索引, 但是没有通过索引进行过滤,一般是使用了索引进行排序分 组
1
EXPLAIN SELECT * FROM L1 ORDER BY id;

  • ALL : 对于每个来自于先前的表的行组合,进行完整的表扫描

一般来说,需要保证查询至少达到 range级别,最好能到ref

possible_keys 与 key介绍

  • possible_keys
    • 显示可能应用到这张表上的索引, 一个或者多个. 查询涉及到的字段上若存在索引, 则该索引将 被列出, 但不一定被查询实际使用
  • key
    • 实际使用的索引，若为null，则没有使用到索引。（两种可能，1.没建立索引, 2.建立索引， 但索引失效）。查询中若使用了覆盖索引，则该索引仅出现在key列表中
    • 覆盖索引：一个索引包含(或覆盖)所有需要查询的字段的值,通过查询索引就可以获取到字段 值
  1. 理论上没有使用索引,但实际上使用了

  1. 理论和实际上都没有使用索引
1
EXPLAIN SELECT * FROM L1 WHERE title = 'test01';

  1. 理论和实际上都使用了索引
1
EXPLAIN SELECT * FROM L2 WHERE title = 'test02';

key_len介绍

表示索引中使用的字节数, 可以通过该列计算查询中使用索引的长度

key_len 字段能够帮你检查是否充分利用了索引 ken_len 越长, 说明索引使用的越充分

创建表：

1
2
3
4
5
6
CREATE TABLE T1(
    a INT PRIMARY KEY,
    b INT NOT NULL,
    c INT DEFAULT NULL,
    d CHAR(10) NOT NULL
);
  • 使用explain 进行测试：
1
EXPLAIN SELECT * FROM T1 WHERE a > 1 AND b = 1;

索引中只包含了1列,所以,key_len是4

  • 为b字段添加索引
1
ALTER TABLE T1 ADD INDEX idx_b(b);

再次测试：

1
EXPLAIN SELECT * FROM T1 WHERE a > 1 AND b = 1;

两列都使用了索引,所以,这里ken_len是8

  • 为d字段添加索引
1
ALTER TABLE T1 ADD INDEX idx_d(d);

执行测试

1
EXPLAIN SELECT * FROM T1 WHERE d = '';

字符集是utf8 一个字符3个字节，d字段是 char(10)代表的是10个字符相当30个字节

ref 介绍

  • 显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
    • L1.id=’1’; 1是常量 , ref = const
1
EXPLAIN SELECT * FROM L1 WHERE L1.id='1';

    • L2表被关联查询的时候,使用了主键索引, 而值使用的是驱动表(执行计划中靠前的表是驱动 表)L1表的ID, 所以 ref = test_explain.L1.id 
1
2
EXPLAIN SELECT * FROM L1 LEFT JOIN L2 ON L1.id = L2.id WHERE L1.title =
'test01';

rows 介绍

表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数；越少越好

  • 使用like 查询,会产生全表扫描, L2中有3条记录,就需要读取3条记录进行查找
1
EXPLAIN SELECT * FROM L1,L2 WHERE L1.id = L2.id AND L2.title LIKE '%la%';

  • 如果使用等值查询, 则可以直接找到要查询的记录,返回即可,所以只需要读取一条
1
EXPLAIN SELECT * FROM L1,L2 WHERE L1.id = L2.id AND L2.title = 'test03';

总结: 当我们需要优化一个SQL语句的时候，我们需要知道该SQL的执行计划，比如是全表扫描，还是 索引扫描； 使用 explain 关键字可以模拟优化器执行 sql 语句，从而知道 mysql 是如何处理 sql 语句 的,方便我们开发人员有针对性的对SQL进行优化。

  • 表的读取顺序。（对应id）
  • 数据读取操作的操作类型。（对应select_type）
  • 哪些索引可以使用。（对应possible_keys）
  • 哪些索引被实际使用。（对应key）
  • 每张表有多少行被优化器查询。（对应rows）
  • 评估sql的质量与效率 (对应type)

extra 介绍

Extra 是 EXPLAIN 输出中另外一个很重要的列，该列显示MySQL在查询过程中的一些详细信息

准备数据：

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE users (
    uid INT PRIMARY KEY AUTO_INCREMENT,
    uname VARCHAR(20),
    age INT(11)
);

INSERT INTO users VALUES(NULL, 'lisa',10);
INSERT INTO users VALUES(NULL, 'lisa',10);
INSERT INTO users VALUES(NULL, 'rose',11);
INSERT INTO users VALUES(NULL, 'jack', 12);
INSERT INTO users VALUES(NULL, 'sam', 13);
  • Using filesort

执行结果Extra为 Using filesort ，这说明，得到所需结果集，需要对所有记录进行文件排序。 这类SQL语句性能极差，需要进行优化。

典型的，在一个没有建立索引的列上进行了order by，就会触发filesort，常见的优化方案是，在 order by的列上添加索引，避免每次查询都全量排序。

filtered 它指返回结果的行占需要读到的行(rows列的值)的百分比

  • Using temporary
1
EXPLAIN SELECT COUNT(*),sex FROM users WHERE uid > 2 GROUP BY sex;

执行结果Extra为 Using temporary，这说明需要建立临时表 (temporary table) 来暂存中间结果。 常见与 group by 和 order by，这类SQL语句性能较低，往往也需要进行优化。

  • Using where
1
EXPLAIN SELECT * FROM users WHERE age=10;

此语句的执行结果Extra为Using where，表示使用了where条件过滤数据

需要注意的是：

  1. 返回所有记录的SQL，不使用where条件过滤数据，大概率不符合预期，对于这类SQL往往需要进行优化
  2. 使用了where条件的SQL，并不代表不需要优化，往往需要配合explain结果中的type（连接 类型）来综合判断。例如本例查询的 age 未设置索引，所以返回的type为ALL，仍有优化空 间，可以建立索引优化查询。
  • Using index

此句执行结果为Extra为Using index，说明sql所需要返回的所有列数据均在一棵索引树上，而无 需访问实际的行记录

  • Using join buffer
1
2
EXPLAIN SELECT * FROM users u1 LEFT JOIN (SELECT * FROM users WHERE sex =
'0') u2 ON u1.uname = u2.uname;

执行结果Extra为 Using join buffer (Block Nested Loop) 说明，需要进行嵌套循环计算, 这 里每个表都有五条记录，内外表查询的type都为ALL。

问题在于 两个关联表join 使用 uname，关联字段均未建立索引，就会出现这种情况。

常见的优化方案是，在关联字段上添加索引，避免每次嵌套循环计算。

MySQL锁机制

MySQL锁概述

数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则

MySQL的锁分类

MySQL数据库由于其自身架构的特点,存在多种数据存储引擎, MySQL中不同的存储引擎支持不同的锁 制。

  • MyISAM和MEMORY存储引擎采用的表级锁
  • InnoDB存储引擎既支持行级锁，也支持表级锁，默认情况下采用行级锁
  • BDB采用的是页面锁，也支持表级锁

按照数据操作的类型分：

  • 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
  • 写锁（排他锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

按照数据操作的粒度分：

  • 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
  • 行级锁： 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最 高。
  • 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发 度一般

按照操作性能可分为乐观锁和悲观锁：

  • 乐观锁：一般的实现方式是对记录数据版本进行比对，在数据更新提交的时候才会进行冲突检测， 如果发现冲突了，则提示错误信息。
  • 悲观锁：在对一条数据修改的时候，为了避免同时被其他人修改，在修改数据之前先锁定，再修改 的控制方式。共享锁和排他锁是悲观锁的不同实现，但都属于悲观锁范畴。

表级锁(偏读)

  • 表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消 耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。
  • 表级锁定分为: 表共享读锁（共享锁）与表独占写锁（排他锁）。
  • 特点: 开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。

数据准备：

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
30
31
-- 创建数据库
CREATE DATABASE test_lock CHARACTER SET 'utf8';

-- 创建表,选择 MYISAM存储引擎
CREATE TABLE mylock01(
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(20)
)ENGINE MYISAM;

-- 创建表
CREATE TABLE mylock02(
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(20)
)ENGINE MYISAM;

-- mylock01表中向插入数据
INSERT INTO mylock01(title) VALUES('a1');
INSERT INTO mylock01(title) VALUES('b1');
INSERT INTO mylock01(title) VALUES('c1');
INSERT INTO mylock01(title) VALUES('d1');
INSERT INTO mylock01(title) VALUES('e1');

-- mylock02表中向插入数据
INSERT INTO mylock02(title) VALUES('a');
INSERT INTO mylock02(title) VALUES('b');
INSERT INTO mylock02(title) VALUES('c');
INSERT INTO mylock02(title) VALUES('d');
INSERT INTO mylock02(title) VALUES('e');


SELECT * FROM mylock01;

加锁语法

查看表中加过的锁

1
2
3
4
5
-- 0表示没有加锁,当前的所有数据库表都没有加锁
SHOW OPEN TABLES;

-- 查询加锁的表,条件In_use 大于0
SHOW OPEN TABLES WHERE In_use > 0;

手动增加表锁

1
2
3
4
5
-- 语法格式: LOCK TABLE 表名 READ(WRITE), 表名2 READ(WRITE), 其他;
-- 为mylock01加读锁(共享锁) , 给mylock02加写锁(排他锁)
lock table mylock01 read,mylock02 write;

SHOW OPEN TABLES WHERE In_use > 0;

释放锁, 解除锁定

1
2
3
4
5
6
-- 方式1
unlock tables;

-- 方式2 找到锁进程,得到id
SHOW PROCESSLIST;
kill id

加读锁测试

MySQL 的表级锁有两种模式：表共享读锁(Table Read Lock)，表独占写锁(Table Write Lock)。

对mylock01表加读锁：

1
lock table mylock01 read;

开启两个窗口,对mylock01进行读操作, 两个窗口都可以读

1
select * from mylock01;

在1窗口进行写操作 (update), 失败

1
update mylock01 set title='a123' where id = 1;

在1窗口中 读取其他的表,比如读取 mylock 02表. 读取失败

1
2
3
4
select * from mylock02;

-- 错误提示: 表“mylock02” 未用锁表锁定

在2窗口中 对 mylock01表 进行写操作

1
update mylock01 set title='a123' where id = 1;

执行后一直阻塞

解除 mylock01 的锁定,窗口2 的修改执行

1
unlock tables;

总结：对MyISAM表的读操作 (加读锁) ，不会阻塞其他进程对同一表的读请求，但是会阻塞对同一表的写请 求。只有当读锁释放后,才会执行其他进程的写操作

加写锁测试

在窗口1中 对mylock01表加写

1
lock table mylock01 write;

在窗口1中, 对 mylock01 进行读写操作, 都是可以进行的

1
2
select * from mylock01 where id = 1;
update mylock01 set title = 'a123' where id = 1;

在窗口1中读其他表, 还是不允许

在窗口2 中读mylock01 表, 读操作被阻塞

1
select * from mylock01;

在窗口2 中 对mylock01表进行写操作, 仍然被阻塞

1
update mylock01 set title = 'a456' where id = 1;

释放锁, 窗口2操作执行执行

1
unlock tables;

总结: 对MyISAM表加写锁, 会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其他进 程的操作

行级锁(偏写)

行级锁介绍

行锁的是mysql锁中粒度最小的一种锁，因为锁的粒度很小，所以发生资源争抢的概率也最小，并发性 能最大，但是也会造成死锁，每次加锁和释放锁的开销也会变大。

使用MySQL行级锁的两个前提:

  • 使用 innoDB 引擎
  • 开启事务 (隔离级别为 Repeatable Read )

InnoDB行锁的类型:

  • 共享锁（S）：当事务对数据加上共享锁后, 其他用户可以并发读取数据，但任何事务都不能 对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。
  • 排他锁（X）：如果事务T对数据A加上排他锁后，则其他事务不能再对数据A加任任何类型的 封锁。获准排他锁的事务既能读数据，又能修改数据。

加锁的方式:

  • InnoDB引擎默认更新语句，update,delete,insert 都会自动给涉及到的数据加上排他锁， select语句默认不会加任何锁类型，如果要加可以使用下面的方式:

    1
    2
    3
    4
    5
    # 加共享锁（S）：
    select * from table_name where ... lock in share mode;
    # 加排他锁（x）：
    select * from table_name where ... for update;

锁兼容:

  • 共享锁只能兼容共享锁, 不兼容排它锁
  • 排它锁互斥共享锁和其它排它锁

行级锁都是基于索引的，如果一条 SQL 语句用不到索引是不会使用行级锁的，而会使用表级锁把 整张表锁住，这点需要咱们格外的注意

行锁测试

更新时的行锁测试

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 创建表
CREATE TABLE innodb_lock(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    age INT,
INDEX idx_name(NAME)
);

-- 插入数据
INSERT INTO innodb_lock VALUES(NULL,'a', 13);
INSERT INTO innodb_lock VALUES(NULL,'b', 23);
INSERT INTO innodb_lock VALUES(NULL,'c', 33);
INSERT INTO innodb_lock VALUES(NULL,'d', 43);

打开两个窗口, 都开启手动提交事务 ( 提交事务或回滚事务就会释放锁 )

1
2
-- 开启MySQL数据库手动提交
SET autocommit=0;

执行不同会话修改操作, 窗口1读，窗口2 写

  • 窗口1 进行, 对id为1的数据 进行更新操作,但是不进行commit
  • 执行之后,在当前窗口查看表数据,发现被修改了
1
2
update innodb_lock set name = 'aaa' where id=1;
select * from innodb_lock;

在窗口2 查看表信息, 无法看到更新的内容

1
select * from innodb_lock;

总结: 行级锁中的写锁主要是为了解决在修改数据时，不允许其他事务对当前数据进行修改和读取 操作，从而可以有效避免”脏读”问题的产生

窗口1 对innodb_lock表的 id=1 的这一行,进行写操作,但是不要commit

1
2
begin;
update innodb_lock set name = 'abc' where id=1;

接下来 窗口2 也对innodb_lock表的 id=1 的这一行,进行写操作,发现发生了阻塞

1
2
begin;
update innodb_lock set name = 'a123' where id=1;

等窗口1执行commit语句之后，窗口2的SQL就会执行了

总结: 在有写锁的情况下，其他事务不能再对当前数据添加写锁，从而保证数据的一致性,从而避免 了不可重复读的问题

查询时的排他锁测试

select语句加排他锁方式 : select from table_name where … *for update;

  • for update 的作用
    • for update 是在数据库中上锁用的，可以为数据库中的行上一个排他锁。
  • for update 的应用场景
    • 存在高并发并且对于数据的准确性很有要求的场景，是需要了解和使用for update的
  • for update 的注意点
    • for update 仅适用于InnoDB，并且必须开启事务，在begin与commit之间才生效。

在窗口1中, 首先开启事务, 然后对 id为1 的数据进行排他查询

1
2
begin;
select * from innodb_lock where id = 1 for update;

在窗口2中，对同一数据分别使用 排他查 和 共享锁 两种方式查询

1
2
3
4
5
6
-- 排他锁查询
select * from innodb_lock where id = 1 for update;

-- 共享锁查询
select * from innodb_lock where id = 1 lock in share mode;

我们看到开了排他锁查询和共享锁查询都会处于阻塞状态，因为id=1的数据已经被加上了排他 锁，此处阻塞是等待排他锁释放。

如果只是使用普通查询,我们发现是可以的

1
select * from innodb_lock where id = 1;

查询时的共享锁测试

  • 添加共享锁: select from table_name where … *lock in share mode;
  • 事务获取了共享锁，在其他查询中也只能加共享锁,但是不能加排它锁。

窗口1 开启事务, 使用共享锁查询 id = 2 的数据 ,但是不要提交事务

1
2
begin;
select * from innodb_lock where id = 2 lock in share mode;

窗口2 开启事务, 使用普通查询和共享锁查询 id = 2 的数据 ,是可以的

1
2
3
select * from innodb_lock where id = 2 lock in share mode;
select * from innodb_lock where id = 2;

加排他锁就查不到，因为排他锁与共享锁不能存在同一数据上。

1
select * from innodb_lock where id = 2 for update;

行锁分析

执行下面的命令,可以获取行锁锁信息

1
2
3
4
5
6
7
8
9
10
mysql> SHOW STATUS LIKE 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 51265 |
| Innodb_row_lock_time_avg | 51265 |
| Innodb_row_lock_time_max | 51265 |
| Innodb_row_lock_waits | 1 |
+-------------------------------+-------+

参数说明

1
2
3
4
5
Innodb_row_lock_current_waits：当前正在等待锁定的数量。
Innodb_row_lock_time：从系统启动到现在锁定总时间长度（重要）。
Innodb_row_lock_time_avg：每次等待所花的平均时间（重要）。
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间。
Innodb_row_lock_waits：系统启动后到现在总共等待的次数（重要）。

当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待， 然后根据分析结果着手制定优化策略。

MySQL集群架构

MySQL高可用设计

高可用介绍

什么是高可用性：

高可用性（英语：High availability，缩写为 HA），IT术语，指系统无中断地执行其功能的能力，代表 系统的可用性程度。是进行系统设计时的准则之一。高可用性系统与构成该系统的各个组件相比可以更 长时间运行。

计算公式: A表示可用性; MTBF表示平均故障间隔; MTTR表示平均恢复时间

高可用有一个标准,9越多代表越容错, 可用性越高.

假设系统一直能够提供服务，我们说系统的可用性是100%。如果系统每运行100个时间单位，会 有1个时间单位无法提供服务，我们说系统的可用性是99%。很多公司的高可用目标是4个9，也就是 99.99%

MySQL高可用介绍

我们在考虑MySQL数据库的高可用的架构时，主要要考虑如下几方面：

  • 如果数据库发生了宕机或者意外中断等故障，能尽快恢复数据库的可用性，尽可能的减少停机时 间，保证业务不会因为数据库的故障而中断。
  • 用作备份、只读副本等功能的非主节点的数据应该和主节点的数据实时或者最终保持一致。当业务 发生数据库切换时，切换前后的数据库内容应当一致，不会因为数据缺失或者数据不一致而影响业 务。
  • 客户端通过Master对数据库进行写操作，slave端进行读操作，并可进行备份。Master出现问题 后，可以手动将应用切换到slave端。

MySQL高可用集群方案

主从复制+读写分离

此种架构，一般初创企业比较常用，也便于后面步步的扩展, 客户端通过Master对数据库进行写操作， slave端进行读操作，并可进行备份。Master出现问题后，可以手动将应用切换到slave端。

主从复制的优点：

  • 实时灾备，用于故障切换（高可用）
  • 读写分离，提供查询服务（读扩展）
  • 数据备份，避免影响业务（高可用）

读写分离的优点：

  • 主从只负责各自的写和读，极大程度的缓解锁争用
  • 从库可配置myisam引擎，提升查询性能以及节约系统开销
  • 从库同步主库，通过主库发送来的binlog恢复数据
  • 读写分离适用与读远大于写的场景，如果只有一台服务器，当select很多时，update和delete会被这些select访问中的数据堵塞，等待select结束，并发性能不高。 对于写和读比例相近的应用，应该部署双主相互复制

双主从复制

很多企业刚开始都是使用MySQL主从模式，一主多从、读写分离等。但是单主如果发生单点故障，从库切换成主库还需要作改动。因此，如果是双主或者多主，就会增加MySQL入口，提升了主库的可用性

双主模式是指两台服务器互为主从，任何一台服务器数据变更，都会通过复制应用到另外一方的数据库中。

建议大家使用双主单写，因为双主双写存在以下缺点：

  • ID冲突
    • 在A主库写入，当A数据未同步到B主库时，对B主库写入，如果采用自动递增容易发生ID主键的冲 突
    • 可以采用MySQL自身的自动增长步长来解决，例如A的主键为1,3,5,7…，B的主键为2,4,6,8… ，但 是对数据库运维、扩展都不友好
  • 更新丢失
    • 同一条记录在两个主库中进行更新，会发生前面覆盖后面的更新丢失

MMM架构

MMM（Master-Master Replication Manager for MySQL）是一套用来管理和监控双主复制，支持双主故障切换 的第三方软件。MMM 使用Perl语言开发，虽然是双主架构，但是业务上同一时间只允许一个节点进行写入操作。下图是基于MMM实现的双主高可用架构。

MMM故障处理机制

  • MMM 包含writer和reader两类角色，分别对应写节点和读节点。
    • 当 writer节点出现故障，程序会自动移除该节点上的VIP
    • 写操作切换到 Master2，并将Master2设置为writer
    • 将所有Slave节点会指向Master2

除了管理双主节点，MMM 也会管理 Slave 节点，在出现宕机、复制延迟或复制错误，MMM 会移 除该节点的 VIP，直到节点恢复正常。

MHA架构

MHA（Master High Availability）目前在MySQL高可用方面是一个相对成熟的解决方案，它由日本人 开发，是一套优秀的作为MySQL高可用性环境下故障切换和主从提升的高可用软件。

在MySQL故障切换过程中，MHA能做到在0~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能在最大程度上保证数据的一致性，以达到真正意义上的高可用。

目前MHA主要支持一主多从的架构，要搭建MHA,要求一个复制集群中必须最少有三台数据库服务器， 一主二从，即一台充当master，一台充当备用master，另外一台充当从库。

MHA Manager管理多组主从复制：

该软件由两部分组成：MHA Manager（管理节点）和MHA Node（数据节点）。

  • MHA Manager可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台 slave节点上。
  • MHA Manager可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台 slave节点上。

MHA故障处理机制：

  • 把宕机master的binlog保存下来
  • 根据binlog位置点找到最新的slave
  • 用最新slave的relay log修复其它slave
  • 将保存下来的binlog在最新的slave上恢复
  • 将最新的slave提升为master
  • 将其它slave重新指向新提升的master，并开启主从复制

MHA优点：

  • 自动故障转移快
  • 主库崩溃不存在数据一致性问题
  • 性能优秀，支持半同步复制和异步复制
  • 一个Manager监控节点可以监控多个集群

MySQL数据库架构原理

执行一条SQL的顺序：

  • 由线程来监听和读取请求
  • 工作线程接收到SQL语句之后交给SQL接口
  • SQL解析器解析SQL
  • 解析完之后，查询优化器选择最优查询路径
  • 存储引擎按照一定的步骤查询内存缓存数据，更新磁盘数据，查询磁盘数据等等。
  • 根据优化器选择的执行方案，去调用存储引擎的接口按照一定的顺序和步骤，执行SQL语句的逻辑

InnoDB存储引擎的架构设计

  • 对于执行更新语句的时候，先将要更新的这一行数据看看是否在buffer pool（缓冲池）里，不在就直接从磁盘加载到缓冲池里，并对这行记录加独占锁。
  • 将更新前的旧值写入undo日志文件，便于回滚。
  • 对buffer pool里面的数据更新为新值，此时新值为脏数据，因为磁盘是旧值，buffer pool是新值，数据不一致
  • 把对内存所做的修改写入到 redo log buffer，也是内存中的一个缓冲区。redo日志就是记录对数据的修改
  • 此时还没提交事务，MySQL宕机，buffer pool修改的数据丢失，redo log buffe记录的日志也会丢失。
  • MySQL binlog属于MySQL server自己的日志文件，提交事务的时候除了把redo日志刷入磁盘，还将本次更新对应的binlog日志写入磁盘文件。
  • 提交事务的时候根据一定的策略将redo日志刷入磁盘。通过innodb_flush_at_trx_commit来配置，当这个参数为0，提交事务不会把redo log buffer里数据刷入磁盘，MySQL此时宕机会丢失数据；为1时，提交事务成功后，redo日志一定在磁盘文件；为2，提交事务时，redo日志写入磁盘文件对应的os cache里，可能1秒后才会把os cache里数据写入磁盘文件，MySQL宕机，可能会丢失1秒的数据。

  • 一个 sync_binlog参数控制binlog的刷盘策略，默认值为0。此时binlog先进入os cache，再刷入磁盘。此时宕机，os cache里的binlog会丢失。
  • sync_binlog设置为1，在提交事务时，binlog直接写入磁盘。
  • 将binlog写入磁盘后，接着完成最终的事务提交，把本次更新对应的binlog文件名称和这次更新的binglog日志在文件的位置，都写入redo log文件中，同时写入一个commit标志

  • 必须是在redo log写入最终的事务commit标记了，此时事务才提交成功。
  • 事务提交成功了，内存中的buffer pool数据已经更新，磁盘里redo日志和binlog日志都记录了更新的操作。MySQL后台的一个IO线程会随机把buffer pool中修改的数据刷到磁盘里。