【MySQL】：架构与存储引擎，锁，事务，设计

文章目录

• 衡量指标
• TPS
• QPS
• MySqlSlap
• MySQL 架构图
• 连接层1
• 连接层2
• 引擎层
• 解析查询
• 存储层
• MySQL形象图
• 存储引擎
• 重点MyISAM
• 重点InnoDB
• 存储引擎对比
• CSV
• Archive
• Memory
• 内存表(Memory)跟临时表(Memory)区别
• Ferderated
• 锁
• MyISAM 锁
• InnoDB 锁
• 事务
• 业务设计
• 逻辑设计
• 范式设计
• 范式化设计优缺点
• 反范式设计
• 反范式化设计优缺点
• 物理设计
• 命名规范
• 存储引擎规范
• 数据类型规范
• 参考

衡量指标

TPS

Transactions Per Second(每秒传输到事务处理个数)，这里是指服务器每秒处理事务数，支持事务的存储引擎如InnoDB等特有等一个性能指标。单位时间内事务执行成功跟事务回滚数
TPS= (COM_COMMIT + COM_ROLLBACK)/UPTIME

QPS

Queries Per Second(每秒查询处理量)，同时适用于InnoDB跟MyISAM引擎。
QPS= QUESTIONS/UPTIME
等待时间：执行SQL等待返回结果之间的等待时间。

MySqlSlap

在mysql5.1.4以后的版本官方就提供了压力测试工具。

• 创建schema，table，testdata。
• 运行负载测试，可以使用多个并发客户端连接。
• 测试环境的清理(删除创建的数据，表格，断开连接)

想要看详细的信息可以 man mysqlslap 或者使用 mysqlslap –help 查看

指令	含义
–concurrency	并发数量,多个可以用逗号隔开
–engines	要测试的引擎，可以有多个，用分隔符隔开，如engines=myisam,innodb
–iterations	要运行这些测试多少次
–auto-generate-sql	用系统自己生成的SQL脚本来测试
–auto-generate-sql-load-type	要测试的是读还是写还是两者混合的（read,write,update,mixed）
–number-of-queries	总共要运行多少次查询。每个客户运行的查询数量可以用查询总数/并发数来计算
–debug-info	额外输出CPU以及内存的相关信息
–number-int-cols	创建测试表的int型字段数量
–number-char-cols	创建测试表的chat型字段数量
–create-schema	测试的database
–query 自己的SQL	脚本执行测试
–only-print	如果只想打印看看SQL语句是什么，可以用这个选项

几个demo如下：

1. mysqlslap -umysql -p123 --concurrency=100 --iterations=1 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=myisam --number-of-queries=10 --debug-info
2. mysqlslap -h192.168.3.18 -P4040 --concurrency=100 --iterations=1 --create-schema=‘test’ --query=‘select * from test;’ --number-of-queries=10 --debug-info -umysql -p123
3. mysqlslap -uroot -p123456 --concurrency=100 --iterations=1 --engine=myisam --create-schema=‘haodingdan112’ --query=‘select * From order_boxing_transit where id = 10’ --number-of-queries=1 --debug-info

MySQL 架构图

连接层1

当MySQL启动(mysql服务器就是一个进程)，等待客户端链接，每一个客户端的链接请求。 每一个客户端连接请求服务器都会新建一个线程进行处理(如果服务器端是线程池，则由线程池来分配一个空闲线程)，每哥线程都是独立的，拥有自己独立的内存空间，如果这个请求是查询，没关系。但是若是修改则两个线程修改同一个内存会引发数据同步问题，需要引入锁。

连接层2

客户连接到服务器，服务器也要对客户进行验证，也就是用户名，IP，密码，来确定是否可以连接，连接后还要确定是否有执行某个特殊查询等权限，比如读写或指定的table。

引擎层

这一层重要功能是：SQL语句的解析，优化，缓存(缓存查询过的数据，缓存执行过的SQL)的查询。
MySQL的内置函数实现，跨存储引擎功能(所谓的跨存储引擎就是每个引擎都需要提供的功能(引擎需要对外提供结构))。比如存储过程，触发器，视图等。

1. 如果是查询语句比如select，会先查询缓存是否已经有对应结果，有则返回，没有则进行下一步查询。
2. 解析查询，创建一个内部数据结构形式等解析树，解析树主要用来SQL语句等语义跟语法缝隙。
3. 优化：优化SQL语句，例如重写查询，决定表的读取顺序，以及选择需要的索引等，用户可以进行查询此阶段，看到服务器如何进行优化的，还会涉及到存储引擎，比如某个操作开销信息，是否对特定索引有查询优化。

查询是否开启缓存

show variables like '%query_cache_type%'

设定缓存大小

set Global query_cache_size = 4000;

查看数据保存目录

show variables like '%datadir%'

解析查询

存储层

将数据存储于裸设备的文件系统之上，完成与存储引擎的交互。

MySQL形象图

存储引擎

查询mysql以提供对存储引擎；

show engines

查看你对mysql默认存储引擎

show variables like '%storage_engine%'

重点MyISAM

MySQL5.5之前对默认存储引擎，MyISAM存储引擎由

table.MYD: 存储数据
talbe.MYI:存储索引
talbe.frm: 存储表格式(InnoDB也有)

特性：

1. 并发性与锁级别-表级锁，不支持事务，不适合频繁修改。
2. 支持全文检索
3. 支持数据压缩 myisampack -b -f table.MYI
   使用场景：

• 非事务型应用(数据仓库，报表，日志数据)
• 只读类应用
• 空间类应用(自带空间函数跟坐标)

重点InnoDB

MySQL5.5以后版本默认存储引擎
存储对适合有innodb_file_per_table 属性

ON：独立对表空间(table.frm，table.ibd)
OFF: 系统表空间(ibdataX)

MySQL5.6以前默认为系统表空间，5.6以后建议跟默认使用独立表空间

• 系统表空间无法简单收缩文件大小
• 独立表空间可以通过 optimize table 收缩系统文件
• 系统表空间存储所有数据，会产生IO瓶颈
• 独立表空间可以同时向多个文件刷新数据。

特性：

InnoDB 是一种事务性存储引擎
完全支持事务的ACID特性
RedoLog和Undo Log
InnoDB支持行级锁(并发成都更高)

使用场景：

InnoDB适合大多数的OLTP应用(On-Line Transaction Processing联机事务处理过程(OLTP)，也称为面向交易的处理过程)

存储引擎对比

CSV

特点：

1. 以csv格式进行数据存储
2. 所有列都不嫩为null
3. 不支持索引(不适合大表，不适合在线处理)
4. 可以直接手动对csv文件进行编辑(编辑适合记得回车，最后flush tables)

场景：

一般财务等人用

Archive

组成： 以zlib对表数据进行压缩，磁盘IO更小，
特点：

1. 只支持insert 跟select 语句
2. 只允许在自增ID上建立索引

使用场景：

日志和数据的采集应用

Memory

文件系统存储特点，也成HEAP存储引擎，数据保存内存中，断电则没。
支持HASH索引跟BTree索引
所有字段都是孤独长度varchar(10) = char(10)
不支持Blog跟Text等大字段
Memory存储引擎使用表级索引
最大值由max_heap_table_size参数决定。

内存表(Memory)跟临时表(Memory)区别

内存表，就是放在内存中的表，所使用内存的大小可通过My.cnf中的max_heap_table_size指定，如max_heap_table_size=1024M，内存表与临时表并不相同，临时表也是存放在内存中，临时表最大所需内存需要通过tmp_table_size =128M设定。当数据超过临时表的最大值设定时，自动转为磁盘表，此时因需要进行IO操作，性能会大大下降，而内存表不会，内存表满后，会提示数据满错误
临时表和内存表都可以人工创建，但临时表更多的作用是系统自己创建后，组织数据以提升性能，如子查询，临时表在多个连接之间不能共享。

Ferderated

默认不开启，需手动配置开启。
特点：

1. 提供远程访问MySQL服务器上表大方法
2. 本地不存储数据，数据都在远程服务器
3. 本地需要保存表结构和远程服务器连接信息

场景：

偶尔大统计分析及手工查询

锁

多线程对同一对象进行操作需加锁。

1. 锁锁计算机协调多个进程或线程并发访问某一个资源对机制
2. 在数据库中，数据也是一种提供给许多用户的共享资源，如何保证数据并发访问的一致性跟有效性是比解决问题，锁冲突也是影响数据库并发访问性能的一个重要因素。
3. 锁对数据库而言重要且复杂。

mysql中 锁类型

1. 表级锁:开销小，加锁快;不会出现死锁;锁定粒度大，发生锁冲突的概率最高，并发度最低。多查询少修改应用
2. 行级锁:开销大，加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低，并发度也最高。支持并发的查询跟更新。
3. 页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间，并发度一般。

MySQL锁机制简单，不同引擎默认支持不同锁级别，MyISAM支持表级锁(table-level locking)，InnoDB同时支持行级锁(row-level locking)跟表级锁，默认行级锁。

MySQL 的表级锁有两种模式:

表共享读锁(Table Read Lock)
表独占写锁(Table Write Lock)

在mysql中加锁是后台自动优化进行添加的，也可以人为的手动添加了解mysql底层特性。

MyISAM 锁

MyISAM共享读锁及独占写锁总结：

1. 对MyISAM表进行读对时候，不会阻塞其他用户对同一表对读操作，但是会阻塞对同一个表对写操作。
2. 对MyISAM表对读操作，不会阻塞当前用户读表，当对表进行修改也会保存。
3. 一个session 使用LOCK table 给表加读锁，这个session可以进行增删改查，当时当前session不可再访问或更新其他表。
4. 另外一个session可以查询表对记录，但是更新对时候就要等待。
5. 对MyISAM表的写操作，会阻塞其他用户对同一表对读写操作。
6. 对MyISAM表对写操作，当前session可以对表进行CRUD，但对其他表操作时候会报错。

读demo:

1. lock table testmysam READ
   启动另外一个session select * from testmysam 可以查询
2. insert into testmysam value(2); update testmysam set id=2 where id=1; 报错
   3.在另外一个session中 insert into testmysam value(2); 等待
   4.在同一个session中 insert into testdemo value(2,‘2’,‘3’); 报错 select * from testdemo ; 报错
   5.在另外一个session中 insert into testdemo value(2,‘2’,‘3’); 成功
   6.加锁在同一个session 中 select s.* from testmysam s 报错
   lock table 表名 as 别名 read;
   查看 show status LIKE ‘table_locks_waited’ 表被锁过几次
   写demo:
   1.lock table testmysam WRITE 在同一个session中 insert testmysam value(3) (OK) ; delete from testmysam where id = 3 (OK) ; select * from testmysam (NO)
   2.对不同的表操作（报错） select s.* from testmysam s； insert into testdemo value(2,‘2’,‘3’);
   3.在其他session中 （等待） select * from testmysam

InnoDB 锁

在MySQL的InnoDB引擎支持行锁。

1. 共享锁(读锁)，当一个事务对某几行上读锁时，允许其他事务对这几行进行读操作，当时不允许对这几行进行写操作，也不允许其他事务给这几行上排它锁，但可以上读锁。
2. 排它锁(写锁)，当一个事务对几行上写锁时，不允许其他事务写，但允许读。更不允许其他事务给这几行上任何锁，包括写锁。

语法：

上共享锁写法：lock in share mode

select * from 表 where 条件 lock in share mode;
上排它锁写法：for update
select * from 表 where 条件 for update;

InnoDB行锁

1. 两个事务不能锁同一个索引
2. insert，delete，update在事务中自动加上排它锁。
3. 行锁必须有索引才可实现，否则自动锁全表，那就不是行锁

demo：

1. BEGIN select * from testdemo where id =1 for update
   在另外一个session中 update testdemo set c1 = ‘1’ where id = 2 成功 update testdemo set c1 = ‘1’ where id = 1 等待
2. BEGIN update testdemo set c1 = ‘1’ where id = 1 在另外一个session中 update testdemo set c1 = ‘1’ where id = 1 等待
3. BEGIN update testdemo set c1 = ‘1’ where c1 = ‘1’ 在另外一个session中 update testdemo set c1 = ‘2’ where c1 = ‘2’ 等待 c1 没有索引。

InnoDB表锁 跟MyISAM差别不大，但是开启一个新事务时候会解锁表。

延伸：系统允许一段时间，数据量大，系统升级，A表要添加字段，白天晚上并发量都大，如何修改表结构。

1. 创建一个跟A表一样但信息是空的A1
2. 修改A1的数据，然后A数据copy 到A1里面
3. 在A表中创建一个触发器，将A表中新增数据自动全部更新到表中来。
4. copy 完毕后自动rename即可。

上述步骤自动化实现可用 工具pt-online-schema-change

事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！
　隐式事务：事务没有明显的开启或者结束的标志，在mysql中，默认是开启自动提交的。
查看数据库下面是否支持事务

show engines

查看mysql当前默认存储引擎

show variables like ‘%storage_engine%’

查看某个表的存储引擎

show create table 表名

修改表存储结构

create table () type=InnoDB;
Alter table 表 type=InnoDB;

一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

1. 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
2. 一致性：在事务开始之前和事务结束以后，。这表示写入的资料必须完数据库的完整性没有被破坏全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
3. 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
4. 持久性：事务处理结束后，对数据的的，即便系统修改就是永久故障也不会丢失。

重点：隔离性
在mysql下事务的隔离级别有四种且由低到高依次为Read uncommitted 、Read committed 、Repeatable read (默认)、Serializable ，这四个级别中的后三个级别可以逐个解决脏读 、不可重复读 、幻读这几类问题

1. 脏读： 事务A读取了事务B更新的数据，然后B回滚了，那么A读取到到数据是脏数据。
2. 不可重复读，事务A多次读取同一个数据，事务B在事务A多次读取读过程中，对数据做了更新跟提交，导致事务A多次读取同一个数据时结果不一致。侧重修改。只要锁住满足条件等行即可。
3. 幻读：管理员小王将数据库中学生成绩按照分数划分为ABCDE级别，但是有人在执行时候插入了一条具体分数，导致A修改后发现一条记录没有修改完毕，像发生幻觉一样。侧重与新增或删除，要锁表！

1. read uncommitted 未提交读
   所有事务都可以看到没有提交事务的数据。性能最好，事务性几乎么有
2. read committed 提交读
   可能会出现多次读取不一样哦！
3. repeatable 重复读
   同一个事务多个实例读取数据时，必须等其他操作目前数据等事务完毕才可以进行，mysql默认级别。
4. Serializable可串行化
   强制的进行排序，在每个读读数据行上添加共享锁。会导致大量超时现象和锁竞争

至于如何避免看 上图表格即可。不同的事务级别可以对于解决不同的问题。
事务用法：
1、用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交 (系统默认)

业务设计

逻辑设计

范式设计

1. 第一范式

第一范式就是属性不可分割，每个字段都应该是不可再拆分的

原子性。比如一个字段是姓名（NAME），在国内的话通常理解都是姓名是一个不可再拆分的单位，这时候就符合第一范式；但是在国外的话还要分为FIRST NAME和LAST NAME，这时候姓名这个字段就是还可以拆分为更小的单位的字段，就不符合第一范式了。

2. 第二大范式

表中要有主键，表中其他其他字段都依赖于主键

第二范式只要记住就好了。主键约束比如说有一个表是学生表，学生表中有一个值唯一的字段学号，那么学生表中的其他所有字段都可以根据这个学号字段去获取，依赖主键的意思也就是相关的意思，因为学号的值是唯一的，因此就不会造成存储的信息对不上的问题，即学生001的姓名不会存到学生002那里去。

3. 第三范式

第三范式就是要求表中不能有其他表中存在的、存储相同信息的字段，

通常实现是在通过外键去建立关联，因此第三范式只要记住外键约束就好了。比如说有一个表是学生表，学生表中有学号，姓名等字段，那如果要把他的系编号，系主任，系主任也存到这个学生表中，那就会造成数据大量的冗余，一是这些信息在系信息表中已存在，二是系中有1000个学生的话这些信息就要存1000遍。因此第三范式的做法是在学生表中增加一个系编号的字段（外键），与系信息表做关联

范式化设计优缺点

优点：

可以尽量得减少数据冗余
范式化的更新操作比反范式化更快
范式化的表通常比反范式化的表更小

缺点：

对于查询需要对多个表进行关联
更难进行索引优化

反范式设计

反范式化是针对范式化而言得，在前面介绍了数据库设计得范式

所谓得反范式化就是为了性能和读取效率得考虑而适当得对数据库设计范式得要求进行违反
允许存在少量得冗余，换句话来说反范式化就是使用空间来换取时间

反范式化设计优缺点

优点：

可以减少表的关联
可以更好的进行索引优化

缺点：

存在数据冗余及数据维护异常
对数据的修改需要更多的成本

物理设计

根据所选择的关系型数据库的特点对逻辑模型进行存储结构的设计

命名规范

• 数据库、表、字段的命名要遵守可读性原则
  使用大小写来格式化的库对象名字以获得良好的可读性
  例如：使用custAddress而不是custaddress来提高可读性。

• 数据库、表、字段的命名要遵守表意性原则
  对象的名字应该能够描述它所表示的对象
  例如：对于表的名称应该能够体现表中存储的数据内容；对于存储过程存储过程应该能够体现存储过程的功能。

• 数据库、表、字段的命名要遵守长名原则
  尽可能少使用或者不使用缩写

存储引擎规范

根据业务规则选择合适的存储引擎，不同引擎的利弊也在面有讲，工作中一般也就上InnoDB或者MyISAM。

数据类型规范

为表中字段选择合适的字节类型，当一个列可以选择多种数据类型时

优先考虑数字类型
其次是日期、时间类型
最后是字符类型
对于相同级别的数据类型，应该优先选择占用空间小的数据类型

重点：

1. 对于精度比较高的东西，比如money，用decimal类型，不会考虑float,double,因为他们容易产生误差。
2. datetime 和 timestamp 的区别与选择

timestamp 只占 4 个字节,它会自动检索当前时区并进行转换，不可存NULL。
datetime以 8 个字节储存，不会进行时区的检索，可存NULL

参考

mysql安装