MySQL架构篇--基础篇_vlambda技术博客

vlambda
2020-05-14

MySQL架构篇--基础篇

mysql的多存储引擎结构

现在随着mysql的发展，我们不难看出mysql在分布式系统和微服务系统中起到的作用。同时又有阿里巴巴，网易，雅虎等大型的互联网公司为mysql站台，mysql已经俨然成为世界上最流行的数据库了，在去年数据库的整体流行程序排行榜中，mysql已经直逼Oracle，今天我们就了解下这个我们可能天天在用的数据库。

为什么越来越多的互联网企业选择mysql，这也是和mysql支持多种存储引擎有关，在了解mysql高可用架构之前还是先看一张图，这个图只要百度mysql逻辑架构便可以获取到！

这个图画出了mysql的架构，从上到下一个一个和大家简单说一下

connectors:连接组件，在mysql的定义中，mysql首先是一个网络程序，mysql针对调用语言提供出了一系列的协议，借助协议可以通过TCP与mysql server建立连接，进行交互。

connection management: 管理服务和工具组件，当我们通过TCP与mysql建立连接之后，mysql会为每个连接绑定一个线程，mysql并不会频繁的创建销毁线程，而是通过缓存线程或者线程池来避免不必要的开销，而管理连接，认证就会在这个区域。

sql interface：sql接口组件，mysql很完美的阐述了面向接口编程，mysql支持DML、DDL、存储过程、视图、触发器、自定义函数等多种sql语言接口。

parser：查询分析器，mysql 会解析sql查询，并创建相应的语法树，并且验证该连接客户端是否具备查询权限，并对查询进行语法层面优化，进行查询重写。

optimizer:优化器，mysql 在进行语法解析和查询重写之后会根据语法树和数据统计信息对sql进行优化，包括读取顺序、索引选择的优化，最终会生成具体执行步骤，但是这些操作还是与存储引擎没有关系的。

caches & buffer：缓冲组件，在mysql内部会维持一些cache，比如我们执行一条select查询时，可以通过query cache缓存执行结果，当下次执行可以不必进行查询分析、优化和执行整个过程

pluggable storage engine 存储引擎具体实现，这些存储引擎都实现了mysql定义好的存储引擎中的部分或者全部，mysql可以动态安装或者移除存储引擎，并且允许多种存储引擎同时存在，可以为每个table设置不同的存储引擎，存储引擎管理了表的实际数据、索引的实际内容，同时也会管理运行时的缓存，事务，log等数据。

查看支持的存储引擎

mysql> show engines;

file system：文件存储单元，mysql表中的每一行数据，都存在文件系统中，以文件的方式存在。当然也有InnoDB支持不适用文件系统直接管理裸设备。文件系统可以使用本地磁盘也可以使用NAS ，SAN等存储系统。

mysql事务

什么是事务？事务就是指满足ACID特性的一组操作，可以通过commit提交一个事务，也可以通过rollback来回滚一个事务。

注：mysql是默认自动提交事务的，如果不显示使用start transaction语句来开始一个事务，那么每个查询操作都会被当做一个事务自动提交。

ACID

A原子性：一句话：不成功便成仁。在一个事物中的sql语句要么全部执行成功，要么全部执行失败。

C一致性：事务使数据库从一个一致性的状态转变为另外一个一致性状态。比如：小明同学有三个苹果，给了小红一个苹果；小红有两个梨，给了小明一个梨；无论两个小朋友如何倒腾两个人加起来就是三个苹果两个梨，这就是事务一致性。

I隔离性：多个用户并发访问数据库，当用户访问一个表时，数据库为每个用户都开启事务，但是不能被其他事务所干扰，多个并发之间相互隔离。比如：上厕所，只有一个蹲位，来了三个人，我允许你等，A上厕所的时候会独占蹲位，B要么在A进入之前就结束排泄战斗，要么乖乖等A完事后进行。（这是一个有味道的距离），但是蹲位上的A是感受不到有BCDEFG存在的。

D持久性：事务一旦提交，执行的修改就会保存到数据上。

基本语法

-- 使用set语句来改变自动提交模式SET autocommit = 0; /*关闭*/SET autocommit = 1; /*开启*/
-- 注意:--- 1.MySQL中默认是自动提交--- 2.使用事务时应先关闭自动提交
-- 开始一个事务,标记事务的起始点START TRANSACTION
-- 提交一个事务给数据库COMMIT
-- 将事务回滚,数据回到本次事务的初始状态ROLLBACK
-- 还原MySQL数据库的自动提交SET autocommit =1;
-- 保存点SAVEPOINT 保存点名称 -- 设置一个事务保存点ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点RELEASE SAVEPOINT 保存点名称 -- 删除保存点

数据范式

一般来说数据库中的范式有多个，满足最低要求的为第一范式，在第一范式基础之上满足更多规范要求称为第二范式，其余范式以此类推，数据库只需要满足第三范式就OK了

范式就有包含关系，如果满足了第二范式，就肯定满足第一范式。

简单了解下：

1NF：属性不可分
2NF：属性完全依赖于主键 [消除部分子函数依赖]
3NF：属性不依赖于其它非主属性 [消除传递依赖]
BCNF（巴斯-科德范式）：在1NF基础上，任何非主属性不能对主键子集依赖[在3NF基础上消除对主码子集的依赖]
4NF：要求把同一表内的多对多关系删除。
5NF（完美范式）：从最终结构重新建立原始结构。

存储引擎

mysql中提供了多个存储引擎，我们一般学习者通常不会关注存储引擎，但是如果我们想将mysql发挥最大的效果，我们不可避免的要在存储引擎上做文章，在mysql中我们不需要在整个服务器中使用同一种存储引擎，可以根据具体要求对每一个表使用不同存储引擎。

MyISAM

MySQL 5.5 版本之前的默认存储引擎，在 5.0 以前最大表存储空间最大 4G，5.0 以后最大 256TB。

Myisam 存储引擎由 .myd（数据）和 .myi（索引文件）组成，.frm文件存储表结构（所以存储引擎都有）

特性

并发性和锁级别（对于读写混合的操作不好，为表级锁，写入和读互斥）
表损坏修复
Myisam 表支持的索引类型（全文索引）
Myisam 支持表压缩（压缩后，此表为只读，不可以写入。使用 myisampack 压缩）

应用场景

没有事务
只读类应用（插入不频繁，查询非常频繁）
空间类应用（唯一支持空间函数的引擎）
做很多 count 的计算

InnoDB

MySQL 5.5 及之后版本的默认存储引擎

特性

InnoDB为事务性存储引擎
完全支持事物的 ACID 特性
Redo log （实现事务的持久性）和 Undo log（为了实现事务的原子性，存储未完成事务log，用于回滚）
InnoDB支持行级锁
行级锁可以最大程度的支持并发
行级锁是由存储引擎层实现的

应用场景

可靠性要求比较高，或者要求事务
表更新和查询都相当的频繁，并且行锁定的机会比较大的情况。

CSV

文件系统存储特点

数据以文本方式存储在文件中
.csv文件存储表内容
.csm文件存储表的元数据，如表状态和数据量
.frm存储表的结构

CSV存储引擎特点

以 CSV 格式进行数据存储
所有列必须都是不能为 NULL
不支持索引
可以对数据文件直接编辑（其他引擎是二进制存储，不可编辑）

引用场景

作为数据交换的中间表

Memory

特性

也称为 HEAP 存储引擎，所以数据保存在内存中（数据库重启后会导致数据丢失）
支持 HASH 索引（等值查找应选择 HASH）和 BTree 索引（范围查找应选择）
所有字段都为固定长度，varchar(10) == char(10)
不支持 BLOG 和 TEXT 等大字段
Memory 存储使用表级锁（性能可能不如 innodb）
最大大小由 max_heap_table_size 参数决定
Memory存储引擎默认表大小只有 16M，可以通过调整 max_heap_table_size 参数

应用场景

用于查找或是映射表，例如右边和地区的对应表
用于保存数据分析中产生的中间表
用于缓存周期性聚合数据的结果表

注意： Memory 数据易丢失，所以要求数据可再生

Federated

特性

提供了访问远程 MySQL 服务器上表的方法
本地不存储数据，数据全部放在远程服务器上

使用 Federated

默认是禁止的。如果需要启用，需要在启动时增加Federated参数

数据类型

整形

类型	存储	存储	最小值	最大值
	byte	bit	signed	signed
TINYINT	1	8	-2⁷ = -128	2⁷-1 = 127
SMALLINT	2	16
MEDIUMINT	3	24
INT	4	32	-2³¹ = -2147483648	2³¹-1 = 2147483647
BIGINT	8	64

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 分别使用 8, 16, 24, 32, 64 位存储空间，一般情况下越小的列越好。

INT(11) 中的数字只是规定了交互工具显示字符的个数，对于存储和计算来说是没有意义的。

浮点数

FLOAT 和 DOUBLE 为浮点类型，DECIMAL 为高精度小数类型。CPU 原生支持浮点运算，但是不支持 DECIMAl 类型的计算，因此 DECIMAL 的计算比浮点类型需要更高的代价。

FLOAT、DOUBLE 和 DECIMAL 都可以指定列宽，例如 DECIMAL(18, 9) 表示总共 18 位，取 9 位存储小数部分，剩下 9 位存储整数部分。

字符串

主要有 CHAR 和 VARCHAR 两种类型，一种是定长的，一种是变长的。

VARCHAR 这种变长类型能够节省空间，因为只需要存储必要的内容。但是在执行 UPDATE 时可能会使行变得比原来长，当超出一个页所能容纳的大小时，就要执行额外的操作。MyISAM 会将行拆成不同的片段存储，而 InnoDB 则需要分裂页来使行放进页内。

VARCHAR 会保留字符串末尾的空格，而 CHAR 会删除。

时间和日期

MySQL 提供了两种相似的日期时间类型：DATETIME 和 TIMESTAMP。

DATETIME

能够保存从 1001 年到 9999 年的日期和时间，精度为秒，使用 8 字节的存储空间。

它与时区无关。

默认情况下，MySQL 以一种可排序的、无歧义的格式显示 DATATIME 值，例如“2008-01-16 22:37:08”，这是 ANSI 标准定义的日期和时间表示方法。

TIMESTAMP

和 UNIX 时间戳相同，保存从 1970 年 1 月 1 日午夜（格林威治时间）以来的秒数，使用 4 个字节，只能表示从 1970 年到 2038 年。

它和时区有关，也就是说一个时间戳在不同的时区所代表的具体时间是不同的。

MySQL 提供了 FROM_UNIXTIME() 函数把 UNIX 时间戳转换为日期，并提供了 UNIX_TIMESTAMP() 函数把日期转换为 UNIX 时间戳。

默认情况下，如果插入时没有指定 TIMESTAMP 列的值，会将这个值设置为当前时间。

应该尽量使用 TIMESTAMP，因为它比 DATETIME 空间效率更高。

索引

使用场景

索引能够轻易将查询性能提升几个数量级。

对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效。
对于中到大型的表，索引就非常有效。
但是对于特大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术。

索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。

分类

特性	说明	InnoDB	MyISAM	MEMORY
B树索引 (B-tree indexes)	自增ID物理连续性更高，二叉树，红黑树高度不可控	√	√	√
R树索引 (R-tree indexes)	空间索引		√
哈希索引 (Hash indexes)	无法做范围查询	√		√
全文索引 (Full-text indexes)		√	√

B树索引

B+Tree 索引是大多数 MySQL 存储引擎的默认索引类型。

因为不再需要进行全表扫描，只需要对树进行搜索即可，因此查找速度快很多。除了用于查找，还可以用于排序和分组。

可以指定多个列作为索引列，多个索引列共同组成键。

B+Tree 索引适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找。

如果不是按照索引列的顺序进行查找，则无法使用索引。

InnoDB 的 B+Tree 索引分为主索引和辅助索引。

主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

哈希索引

InnoDB 引擎有一个特殊的功能叫 “自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

哈希索引能以 O(1) 时间进行查找，但是失去了有序性，它具有以下限制：

无法用于排序与分组；
只支持精确查找，无法用于部分查找和范围查找；

全文索引

MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。查找条件使用 MATCH AGAINST，而不是普通的 WHERE。

全文索引一般使用倒排索引实现，它记录着关键词到其所在文档的映射。

InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。

特点

可以加快数据库的检索速度
降低数据库插入、修改、删除等维护的速度
只能创建在表上，不能创建到视图上
既可以直接创建又可以间接创建
可以在优化隐藏中使用索引
使用查询处理器执行SQL语句，在一个表上，一次只能使用一个索引

注意事项

在经常需要搜索的列上，可以加快搜索的速度；
在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
对于中到大型表索引都是非常有效的，但是特大型表的话维护开销会很大，不适合建索引
在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
避免 where 子句中对宇段施加函数，这会造成无法命中索引。
在使用InnoDB时使用与业务无关的自增主键作为主键，即使用逻辑主键，而不要使用业务主键。
将打算加索引的列设置为 NOT NULL ，否则将导致引擎放弃使用索引而进行全表扫描
删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗 MySQL 5.7 可以通过查询 sys 库的 chema_unused_indexes 视图来查询哪些索引从未被使用
在使用 limit offset 查询缓慢时，可以借助索引来提高性能

优点

创建唯一性索引，保证数据库表中每一行数据的唯一性
大大加快数据的检索速度，这是创建索引的最主要的原因
加速数据库表之间的连接，特别是在实现数据的参考完整性方面特别有意义
在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间
通过使用索引，可以在查询中使用优化隐藏器，提高系统的性能

缺点

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
索引需要占用物理空间，除了数据表占用数据空间之外，每一个索引还要占一定的物理空间，如果建立聚簇索引，那么需要的空间就会更大
当对表中的数据进行增加、删除和修改的时候，索引也需要维护，降低数据维护的速度

索引失效

美团面经：哪些情况下不会使用索引？

如果MySQL估计使用全表扫秒比使用索引快，则不适用索引。

例如，如果列key均匀分布在1和100之间，下面的查询使用索引就不是很好：select * from table_name where key>1 and key<90;
如果条件中有or，即使其中有条件带索引也不会使用

例如：select * from table_name where key1='a' or key2='b';如果在key1上有索引而在key2上没有索引，则该查询也不会走索引
复合索引，如果索引列不是复合索引的第一部分，则不使用索引（即不符合最左前缀）

例如，复合索引为(key1,key2),则查询select * from table_name where key2='b';将不会使用索引
如果like是以 % 开始的，则该列上的索引不会被使用。

例如select * from table_name where key1 like '%a'；该查询即使key1上存在索引，也不会被使用如果列类型是字符串，那一定要在条件中使用引号引起来，否则不会使用索引
如果列为字符串，则where条件中必须将字符常量值加引号，否则即使该列上存在索引，也不会被使用。

例如,select * from table_name where key1=1;如果key1列保存的是字符串，即使key1上有索引，也不会被使用。
如果使用MEMORY/HEAP表，并且where条件中不使用“=”进行索引列，那么不会用到索引，head表只有在“=”的条件下才会使用索引

什么情况下使用索引

为经常出现在关键字order by、group by、distinct后面的字段，建立索引。
在union等集合操作的结果集字段上，建立索引。其建立索引的目的同上。
为经常用作查询选择 where 后的字段，建立索引。
在经常用作表连接 join 的属性上，建立索引。
考虑使用索引覆盖。对数据很少被更新的表，如果用户经常只查询其中的几个字段，可以考虑在这几个字段上建立索引，从而将表的扫描改变为索引的扫描。

索引举例

/*#方法一：创建表时 　　CREATE TABLE 表名 ( 字段名1 数据类型 [完整性约束条件…], 字段名2 数据类型 [完整性约束条件…], [UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY [索引名] (字段名[(长度)] [ASC |DESC]) );

#方法二：CREATE在已存在的表上创建索引 CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名 (字段名[(长度)] [ASC |DESC]) ;

#方法三：ALTER TABLE在已存在的表上创建索引 ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (字段名[(长度)] [ASC |DESC]) ;  #删除索引：DROP INDEX 索引名 ON 表名字;#删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY;

#显示索引信息: SHOW INDEX FROM student;*/
/*增加全文索引*/ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname` (`StudentName`);
/*EXPLAIN : 分析SQL语句执行性能*/EXPLAIN SELECT * FROM student WHERE studentno='1000';
/*使用全文索引*/-- 全文搜索通过 MATCH() 函数完成。-- 搜索字符串作为 against() 的参数被给定。搜索以忽略字母大小写的方式执行。对于表中的每个记录行，MATCH() 返回一个相关性值。即，在搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');
/*开始之前，先说一下全文索引的版本、存储引擎、数据类型的支持情况
MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。测试或使用全文索引时，要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。*/

索引举例

CREATE TABLE `app_user` (`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,`name` varchar(50) DEFAULT '' COMMENT '用户昵称',`email` varchar(50) NOT NULL COMMENT '用户邮箱',`phone` varchar(20) DEFAULT '' COMMENT '手机号',`gender` tinyint(4) unsigned DEFAULT '0' COMMENT '性别（0:男；1：女）',`password` varchar(100) NOT NULL COMMENT '密码',`age` tinyint(4) DEFAULT '0' COMMENT '年龄',`create_time` datetime DEFAULT CURRENT_TIMESTAMP,`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'


--插入数据DROP FUNCTION IF EXISTS mock_data;DELIMITER $$CREATE FUNCTION mock_data()RETURNS INTBEGINDECLARE num INT DEFAULT 1000000;DECLARE i INT DEFAULT 0;WHILE i < num DO INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`) VALUES(CONCAT('用户', i), '[email protected]', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100)); SET i = i + 1;END WHILE;RETURN i;END;SELECT mock_data();

-- 无索引SELECT * FROM app_user WHERE name = '用户9999'; -- 查看耗时SELECT * FROM app_user WHERE name = '用户9999';SELECT * FROM app_user WHERE name = '用户9999';
mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G*************************** 1. row *************************** id: 1select_type: SIMPLE table: app_user partitions: NULL type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 992759 filtered: 10.00 Extra: Using where1 row in set, 1 warning (0.00 sec)

--创建索引CREATE INDEX idx_app_user_name ON app_user(name);

--普通索引mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G*************************** 1. row *************************** id: 1select_type: SIMPLE table: app_user partitions: NULL type: refpossible_keys: idx_app_user_name key: idx_app_user_name key_len: 203 ref: const rows: 1 filtered: 100.00 Extra: NULL1 row in set, 1 warning (0.00 sec)
mysql> SELECT * FROM app_user WHERE name = '用户9999';1 row in set (0.00 sec)
mysql> SELECT * FROM app_user WHERE name = '用户9999';1 row in set (0.00 sec)
mysql> SELECT * FROM app_user WHERE name = '用户9999';1 row in set (0.00 sec)

索引准则

索引不是越多越好
不要对经常变动的数据加索引
小数据量的表建议不要加索引
索引一般应加在查找条件的字段

vlambda博客
学习文章列表