mysql-1.原理

ns7381
267 阅读34分钟

1.需求

MySQL是一种关联数据库管理系统，将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

2.设计

2.1 架构

image.png 和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上。

插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

mysql四层架构

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

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

Management Serveices & Utilities系统管理和控制工具
SQL InterfaceSQL 接口。 接受用户的 SQL 命令， 并且返回用户需要查询的结果。 比如 select from 就是调用 SQL Interface
Parser解析器。 SQL 命令传递到解析器的时候会被解析器验证和解析
Optimizer查询优化器。 SQL 语句在查询之前会使用查询优化器对查询进行优化， 比如有 where 条件时， 优化器来决定先投影还是先过滤。
Cache 和 Buffer查询缓存。 如果查询缓存有命中的查询结果， 查询语句就可以直接去查询缓存中取 数据。 这个缓存机制是由一系列小缓存组成的。 比如表缓存， 记录缓存， key 缓存， 权限缓存等
  • 引擎层 存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过APl与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。后面介绍MyISAM和InnoDB

image.png

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

2.2 流程

sql执行过程

image.png

SQL的手写顺序

SELECT DISTINCT <Select_List>
FROM <left_table><join type>
JOIN <right_table> on <join_condition>
WHERE <Where_condition>
GROUP BY <Group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>

SQL的执行顺序

FROM <left_table><join type>
JOIN <right_table> on <join_condition>
WHERE <Where_condition>
GROUP BY <Group_by_list>
HAVING <having_condition>
SELECT DISTINCT <Select_List>
ORDER BY <order_by_condition>
LIMIT <limit_number>

image.png

3.原理

3.1 存储结构

数据目录

image.png

user表（InnoDB存储引擎）对应两个文件，order表（MYISAM存储引擎）对应3个文件。

其中.frm文件是存储的是表结构，两个存储引擎都一样

而InnoDB的.ibd文件是索引+数据，MYISAM的.MYI（I：index）和.MYD（D：data）文件分别是索引字段的索引结构和数据文件，也就是说MYISAM存储引擎的索引和数据是分开的，而InnoDB存储引擎的数据和索引是在一个文件里的。

存储结构

image.png

数据库字段类型

  • Null类型： 可为Null的列会使用更多的存储空间,当可为NULL的列被索引时,每一个索引记录都需要一个额外的字节。
  • 时间类型：

DATETIME(9999年)和TIMESTAMP(2038年)都可以存储时间类型精确到秒。但TIMESTAMP只使用DATETIME的一半存储空间，并且会根据时区变化，具有特殊的自动更新能力。但TIMESTAMP允许的时间范围要小的得多。

  • 整数类型： TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT分别使用8,16,24,32,64位存储空间,

UNSIGNED属性表示不允许负值,int(M) 中的M指示最大显示宽度，最大有效显示宽度是 255，且显示宽度与存储大小或类型包含的值的范围无关。

  • VARCHAR： 存储可变长字符串，仅使用必要的空间。VARCHAR需要一个或者两个来记录字符串的长度。varchar可能导致UPDATE时使行变的更长，存储引擎需要做一些额外的工作.

  • CHAR类型是定长的： 存储时,mysql会删除所有的末尾空格。char适合存储很短的字符串或者所有的字符串都接近同一个长度(例如MD5值)

  • DECIMAL(18.9): 一共(18/9)*4+1=9个字节(小数位置本身占一个字节)

  • BLOB/TEXT类型：

    为存储很大的数据而设计的，分别采用二进制和字符串方式。

    MySQL把每个BLOB和TEXT值当一个独立的对象处理，当BLOB或TEXT太大时MySQL在行内存储一个1~4字节的指针，然后在外部存储实际的值。

    MySQL不能对全部长度进行索引,MySQL提供INET_ATON()和INET_NTOA()表示IP地址数字和字符串之间的转换

  • mysql中的数据类型enum

    单选字符串数据类型，适合存储表单界面中的“单选值”。 　　 设定enum的时候，需要给定“固定的几个选项”；存储的时候就只存储其中的一个值.

  • mysql中的数据类型set

    多选字符串数据类型，适合存储表单界面的“多选值”

    设定set的时候，同样需要给定“固定的几个选项”；存储的时候，可以存储其中的若干个值。

3.2 缓存

MysqlServer 会对整条查询语句进行Hash计算，把得到的hash与Query查询的结果集放入QueryCache里。如果SQL语句只要相差哪怕一个字符,那么这两个SQL将使用不同的Cache地址。

缓存失效：在表的结构或数据发生改变时，查询缓存中的数据不再有效。查询中包含一个不确定的函数(例如NOW())，或者查询结果太大时则无法使用缓存。

MySqlServer 自己管理内存的释放和分配，不通过操作系统。当向某个表写入数据的时候，必须将这个表所有的缓存设置为失效。如果缓存空间很大，则消耗也会很大，可能使系统僵死一段时间。

3.3 索引

优势:

  • 提高数据检索的效率，降低数据库的IO成本。
  • 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。 劣势:
  • 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为 更新所带来的键值变化后的索引信息。
  • 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间 的。

索引结构

B+树索引

B+树：在B-树基础上，为叶子结点增加链表指针，所有关键字都在叶子结点中出现，非叶子结点作为叶子结点的索引；B+树总是到叶子结点才命中；

image.png

  • MySql为什么使用B+树

  1. B+树的磁盘读写代价更低 B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对 B 树更小。如果把所有同一内部结点 的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就 越多。相对来说 IO 读写次数也就降低了。

  2. B+树的查询效率更加稳定 由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须 走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

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

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

image.png

辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。

image.png

哈希索引

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

无法用于排序与分组；

只支持精确查找，无法用于部分查找和范围查找。

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

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。术语‘聚簇’表示数据行和相邻的键值聚簇的存储 在一起。如下图，左侧的索引就是聚簇索引，因为数据行在磁盘的排列和索引排序保持一致。

image.png

聚簇索引的好处: 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不不用从多 个数据块中提取数据，所以节省了大量的 io 操作。

聚簇索引的限制:

对于 mysql 数据库目前只有 innodb 数据引擎支持聚簇索引，而 Myisam 并不支持聚簇索引。 由于数据物理存储排序方式只能有一种，所以每个 Mysql 的表只能有一个聚簇索引。一般情况下就是 该表的主键。

为了充分利用聚簇索引的聚簇的特性，所以 innodb 表的主键列尽量选用有序的顺序 id，而不建议用 无序的 id，比如 uuid 这种。

覆盖索引

对于一次查询，建立索引的字段正好是覆盖该查询语句与查询条件中所涉及的字段，那么就称这一次的查询使用了覆盖索引。在聚集索引和非聚集索引结构中都可以使用覆盖索引。

索引类型

1.普通索引：是最基本的索引，它没有任何限制。 CREATE INDEX index_name ON table(column(length))

2.唯一索引：与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。 CREATE UNIQUE INDEX indexName ON table(column(length))

3.主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：

CREATE TABLE `table` ( `id` int(11) NOT NULL AUTO_INCREMENT , `title` char(255) NOT NULL , PRIMARY KEY (`id`) );

4.组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);

image.png

5.全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。 CREATE FULLTEXT INDEX index_content ON article(content)

索引页操作

  • 索引文件结构
  1. 一个索引文件结构由多个段组成，每一个段和一个索引文件相关。
  2. 一个段有多个区组成，每个区的默认大小为1MB
  3. 一个区有多个页组成，一个页的默认大小为16KB
  4. 一个页也可以容纳N个行，InnoDB要求每一个页至少有2行记录。页内行记录按照主键ID来排序。
  5. InnoDB管理的最小粒度是页，页加载进内存以后才会通过扫描页来获取行。
  6. 故B+树的每一个叶子节点并非是行记录而且页记录。
  • 页合并 当删除一行记录时，实际上记录没有被物理删除，记录只是被标记为删除且它的空间可以被其他记录使用。 当页内删除的记录达到一定值以后，InnoDB寻找临近的页，看是否可以将两个页合并。合并后调整上层的索引。 一般update和delete操作会产生页合并。
  • 页分裂 当页填充满了以后，需创建新的页进行调整：
  1. 先创建新页
  2. 判断当前页可以从哪里进行分裂，（记录行层面）
  3. 移动记录行，然后重新定义页之间的顺序，然后调整索引。 页分裂发生在update和insert操作中，会造成页的在物理层面的存储是乱序的。可以通过OPTIMIZE命令去重新整理表，但这个过程会很耗费时间。同时也页分裂和页合并的过程会在索引树上加锁。

索引的创建时机

4.1 适合创建索引的情况

  • 主键自动建立唯一索引;
  • 频繁作为查询条件的字段应该创建索引
  • 查询中与其它表关联的字段，外键关系建立索引
  • 单键/组合索引的选择问题， 组合索引性价比更高
  • 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
  • 查询中统计或者分组字段 4.2 不适合创建索引的情况
  • 表记录太少
  • 经常增删改的表或者字段
  • Where条件里用不到的字段不创建索引
  • 过滤性不好的不适合建索引

索引分析方法

  1. 查看索引使用情况

如果索引正在工作，Handler_read_key的值将很高，这个值代表了一个行被索引值读的次数。 Handler_read_rnd_next的值高则意味着查询运行低效，并且应该建立索引补救。

mysql> show status like 'Handler_read%';
+-----------------------+--------+
| Variable_name         | Value  |
+-----------------------+--------+
| Handler_read_first    | 9      |
| Handler_read_key      | 16     |
| Handler_read_last     | 0      |
| Handler_read_next     | 680908 |
| Handler_read_prev     | 0      |
| Handler_read_rnd      | 0      |
| Handler_read_rnd_next | 935519 |
+-----------------------+--------+
7 rows in set (0.00 sec) mysql>
  1. 两个简单实用的优化方法：

分析表的语法如下:(检查一个或多个表是否有错误)

mysql> CHECK TABLE tbl_name[,tbl_name] …[option] …option =
{ QUICK | FAST | MEDIUM| EXTENDED | CHANGED}
mysql> check table sales;
+--------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| sakila.sales | check | status | OK |
+--------------+-------+----------+----------+
1 row in set (0.01 sec)

优化表的语法格式： OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [,tbl_name]

如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表进行了很多的改动，则需要做定期优化。这个命令可以将表中的空间碎片进行合并，但是此命令只对MyISAM、BDB和InnoDB表起作用。

mysql> optimize table sales;
+--------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+----------+----------+----------+
| sakila.sales | optimize | status | OK |
+--------------+----------+----------+----------+
1 row in set (0.05 sec)

3.4 事务

ACID属性

事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性。

  • 原子性（Atomicity）： 一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚。--》主要涉及InnoDB事务。相关特性：事务的提交，回滚，信息表。

  • 一致性（Consistent）： 数据库总是从一个一致性的状态转换到另一个一致性的状态。在事务开始前后，数据库的完整性约束没有被破坏。例如违反了唯一性，必须撤销事务，返回初始状态。--》主要涉及内部InnoDB处理，以保护数据不受崩溃，相关特性：双写缓冲、崩溃恢复。

  • 隔离性（Isolation）： 每个读写事务的对象对其他事务的操作对象能相互分离，即：事务提交前对其他事务是不可见的，通常内部加锁实现。--》主要涉及事务，尤其是事务隔离级别，相关特性：隔离级别、innodb锁的底层实现细节。

  • 持久性（Durability）： 一旦事务提交，则其所做的修改会永久保存到数据库。--》涉及到MySQL软件特性与特定硬件配置的相互影响，相关特性：4个配置项：双写缓冲开关、事务提交刷新log的级别、binlog同步频率、表文件；写缓存、操作系统对于fsync()的支持、备份策略等。

ACID实现

  • 如何保证原子性： 通过Undo日志实现为了满足事务的原子性，在操作任何数据之前，首先将数据备份到一个地方。然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。

  • 如何保证持久性： （Innodb Doublewrite）Mysql是先把磁盘上的数据加载到内存中，在内存中对数据进行修改，再刷回磁盘上。如果此时突然宕机，内存中的数据就会丢失。当做数据修改的时候，不仅在内存中操作，还会在redo log中记录这次操作。当事务提交的时候，会将redo log日志进行刷盘。当数据库宕机重启的时候，会将redo log中的内容恢复到数据库中，再根据undo log和binlog内容决定回滚数据还是提交数据。InnoDB的日志是环形方式写的，InnoDB会使用一个后台线程把日志的内容写入到数据文件。

  • 如何保证隔离性： 通过MVCC和数据库锁机制保证隔离性。

  • 如何保证一致性： 通过原子性和持久性以及隔离性来保证了一致性。

并发事务处理带来的问题

  • 脏读（Dirty Reads）： 事务A更新记录但未提交，事务B查询出A未提交记录。

  • 不可重复读（Non-Repeatable Reads）： 事务A读取一次，此时事务B对数据进行了更新或删除操作，事务A再次查询数据不一致。

  • 幻读（Phantom Reads） 事务A读取一次，此时事务B插入一条数据事务A再次查询，记录多了。

IndoDB事务模型

InnoDB Transaction Model

在MVCC中，读操作可以分成两类，快照读（Snapshot read）和当前读（current read）。

  • 快照读(一致性非锁定读)： 普通的select

一致性非锁定读(consistent nonlocking read)是指InnoDB存储引擎通过多版本控制(multi versionning)的方式来读取当前执行时间数据库中行的数据，如果读取的行正在执行DELETE或UPDATE操作，这是读取操作不会因此等待行上锁的释放。相反的，InnoDB会去读取行的一个快照数据

image.png

上面展示了InnoDB存储引擎一致性的非锁定读。之所以称为非锁定读，因为不需要等待访问的行上X锁的释放。快照数据是指该行之前版本的数据，该实现是通过undo段来完成。而undo用来事务中的回滚数据，因此快照数据本身没有额外的开销，此外，读取快照数据不需要上锁，因为没有事务需要对历史数据进行修改操作。

  • 当前读(锁定读)： select * from table where ? lock in share mode; （加S锁）

select * from table where ? for update; （加X锁）

insert, update, delete 操作前会先进行一次当前读（加X锁）

其中前两种锁定读,需要用户自己显式使用,最后一种是自动添加的。

innoDB对select语句支持两种锁定读：

  1. SELECT...FOR UPDATE:对读取的行加排它锁（X锁），其他事务不能对已锁定的行再加任何锁。

  2. SELECT...LOCK IN SHARE MODE ：对读取的行加共享锁（S锁），其他事务可以再加S锁，X锁会阻塞等待。

注：这两种锁都必须处于事务中，事务commit，锁释放。所以必须begin或者start transaction开启一个事务或者索性set autocommit=0把自动提交关掉（mysql默认是1，即执行完sql立即提交）

事务的隔离级别

官网描述： InnoDB使用不同的锁定策略支持每个事务隔离级别。

对于关键数据的操作(遵从ACID原则)，您可以使用强一致性（默认Repeatable Read）。

对于不是那么重要的数据操作，可以使用Read Committed/Read Uncommitted。

Serializable执行比可重读更严格的规则，用于特殊场景：XA事务，并发性和死锁问题的故障排除。

四种隔离级别：

  1. Read Uncommitted（读未提交）： 可能读取其它事务未提交的数据。-脏读问题（脏读+不可重复读+幻读）

image.png

  1. Read Committed（读已提交）： 一个事务只能看见已经提交事务所做的改变。（不可重复读+幻读）

select...from : 一致性非锁定读的数据快照(MVCC)是最新版本的,但其他事务可能会有新的commit，所以同一select可能返回不同结果。-不可重复读问题

select...from for update : record lock行级锁.

image.png

  1. Repeatable Read（可重复读）： select…from ：同一事务内多次一致性非锁定读，取第一次读取时建立的快照版本(MVCC)，保证了同一事务内部的可重复读.—狭义的幻读问题得到解决。（Db插入了数据，只不过读不到）

select...from for update （FOR UPDATE or LOCK IN SHARE MODE), UPDATE, 和 DELETE : next-key lock下一键锁.

　　1）对于具有唯一搜索条件的唯一索引，innoDB只锁定找到的索引记录. （next-key lock 降为record lock）

　　2）对于其他非索引或者非唯一索引，InnoDB会对扫描的索引范围进行锁定，使用next-key locks，阻塞其他session对间隙的insert操作，-彻底解决广义的幻读问题。（DB没插入数据）

image.png

  1. Serializable（可串行化）： 这是最高的隔离级别，它是在每个读的数据行上加上共享锁（LOCK IN SHARE MODE）。在这个级别，可能导致大量的超时现象和锁竞争，主要用于分布式事务。

image.png 如下表：

image.png

脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。

同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。

查看当前数据库的事务隔离级别：show variables like 'tx_isolation'; mysql 默认是可重复读 以在my.inf文件的[mysqld]节里类似如下设置该选项：

transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE}

用户可以用SET TRANSACTION语句改变单个会话或者所有新进连接的隔离级别。它的语法如下：

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}·

注意：默认的行为（不带session和global）是为下一个（未开始）事务设置隔离级别。如果你使用GLOBAL关键字，语句在全局对从那点开始创建的所有新连接（除了不存在的连接）设置默认事务级别。你需要SUPER权限来做这个。使用SESSION 关键字为将来在当前连接上执行的事务设置默认事务级别。 任何客户端都能自由改变会话隔离级别（甚至在事务的中间），或者为下一个事务设置隔离级别。

你可以用下列语句查询全局和会话事务隔离级别：

SELECT @@global.tx_isolation; SELECT @@session.tx_isolation; SELECT @@tx_isolation;

3.5 锁机制

锁的定义

锁是计算机协调多个进程或线程并发访问某一资源的机制。

在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。

如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

锁的分类

从数据操作的类型（读、写）分

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

表锁

偏向MyISAM存储引擎，开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发最低

  • 查看当前数据库中表的上锁情况： show open tables;，0 表示未上锁
  • 添加锁 lock table 表名1 read(write), 表名2 read(write), ...;
  • 释放表锁 unlock tables;

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。

MySQL的表级锁有两种模式：

  • 表共享读锁（Table Read Lock）
  • 表独占写锁（Table Write Lock）

对MyISAM表进行操作，会有以下情况：

  • 对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
  • 对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作
  • 简而言之，就是读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞。
读锁

当前 session 和其他 session 均可以读取加了读锁的表

当前 session 不能读取其他表，并且不能修改加了读锁的表

其他 session 想要修改加了读锁的表，必须等待其读锁释放

--准备
create table mylock (
    id int not null primary key auto_increment,
    name varchar(20) default ''
) engine myisam;
insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');

--在 session 1 会话中，给 mylock 表加个读锁
lock table mylock read;
-- 在 session1 会话中能不能读取 mylock 表：可以读
-- 在 session1 会话中能不能读取 book 表：并不行
select * from book;
ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES
-- 在 session1 会话中能不能修改 mylock 表：并不行
update mylock set name='a2' where id=1;
--ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated

-- 在 session2 会话中能不能读取 mylock 表：可以读
-- 在 session2 会话中能不能修改 mylock 表：阻塞，一旦 mylock 表锁释放，则会执行修改操作
写锁

当前 session 可以读取和修改加了写锁的表

当前 session 不能读取其他表

其他 session 想要读取加了写锁的表，必须等待其读锁释放

-- 在 session 1 会话中，给 mylock 表加个写锁
lock table mylock write;
-- 在 session1 会话中能不能读取 mylock 表：阔以
-- 在 session1 会话中能不能读取 book 表：不阔以
-- 在 session1 会话中能不能修改 mylock 表：当然可以啦，加写锁就是为了修改呀
select * from book;
ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES
--在 session2 会话中能不能读取 mylock 表：
表锁分析

Myisam的读写锁调度是写优先，这也是myisam不适合做写为主表的引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞

-- 查看哪些表被锁了，0 表示未锁，1 表示被锁
show open tables;
-- 可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定，通过 show status like 'table%'; 命令查看
show status like 'table%';

行锁

偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。 InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。

当系统并发量较高的时候，Innodb的整体性能和MyISAM相比就会有比较明显的优势了。

但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候（索引失效，导致行锁变表锁），可能会让Innodb的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

行锁与事务

无索引导致行锁升级为表锁

--准备
CREATE TABLE test_innodb_lock (a INT(11),b VARCHAR(16))ENGINE=INNODB;

INSERT INTO test_innodb_lock VALUES(1,'b2');
INSERT INTO test_innodb_lock VALUES(3,'3');
INSERT INTO test_innodb_lock VALUES(4, '4000');
INSERT INTO test_innodb_lock VALUES(5,'5000');
INSERT INTO test_innodb_lock VALUES(6, '6000');
INSERT INTO test_innodb_lock VALUES(7,'7000');
INSERT INTO test_innodb_lock VALUES(8, '8000');
INSERT INTO test_innodb_lock VALUES(9,'9000');
INSERT INTO test_innodb_lock VALUES(1,'b1');

CREATE INDEX test_innodb_a_ind ON test_innodb_lock(a);
CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b);
-- 操作同一行数据
-- session1 开启事务，修改 test_innodb_lock 中的数据
set autocommit=0;
update test_innodb_lock set b='4001' where a=4;
-- session2 开启事务，修改 test_innodb_lock 中同一行数据，将导致 session2 发生阻塞，一旦 session1 提交事务，session2 将执行更新操作
set autocommit=0;
update test_innodb_lock set b='4002' where a=4;--阻塞
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
-- 操作不同行数据
-- session2 开启事务，修改 test_innodb_lock 中不同行的数据
-- 由于采用行锁，session2 和 session1 互不干涉，所以 session2 中的修改操作没有阻塞
update test_innodb_lock set b='9001' where a=9;
Query OK, 1 row affected (0.01 sec)
-- 无索引导致行锁升级为表锁
-- session1 开启事务，修改 test_innodb_lock 中的数据，varchar 不用 ’ ’ ，导致系统自动转换类型，导致索引失效
set autocommit=0;
update test_innodb_lock set a=44 where b=4000;
-- session2 开启事务，修改 test_innodb_lock 中不同行的数据
-- 由于发生了自动类型转换，索引失效，导致行锁变为表锁
set autocommit=0;
update test_innodb_lock set b='9001' where a=9;--阻塞
间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”

InnoDB也会对这个“间隙”加锁，这种锁机制是所谓的间隙锁（Next-Key锁）

  • 间隙锁的危害 因为Query执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。

间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害

-- session1 开启事务，执行修改 a > 1 and a < 6 的数据，这会导致 mysql 将 a = 2 的数据行锁住（虽然表中并没有这行数据）
set autocommit=0;
update test_innodb_lock set b='Heygo' where a>1 and a<6;
-- session2 开启事务，修改 test_innodb_lock 中不同行的数据，也会导致阻塞，直至 session1 提交事务
set autocommit=0;
insert into test_innodb_lock values (5, "sdf");--阻塞
手动行锁

SELECT....LOCK IN SHARE MODE 加读锁

select xxx ... for update 加写锁. 锁定某一行后，其它的操作会被阻塞，直到锁定行的会话提交

-- session1 开启事务，手动执行 for update 锁定指定行，待执行完指定操作时再将数据提交
set autocommit=0;
select * from test_innodb_lock  where a=8 for update;
-- session2 开启事务，修改 session1 中被锁定的行，会导致阻塞，直至 session1 提交事务
set autocommit=0;
update test_innodb_lock set b='XXX' where a=8;--阻塞
行锁分析

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况: show status like 'innodb_row_lock%';

对各个状态量的说明如下：

  • Innodb_row_lock_current_waits：当前正在等待锁定的数量；
  • Innodb_row_lock_time：从系统启动到现在锁定总时间长度；
  • Innodb_row_lock_time_avg：每次等待所花平均时间；
  • Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
  • Innodb_row_lock_waits：系统启动后到现在总共等待的次数；
-- 通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况
show status like 'innodb_row_lock%';
行锁优化
  1. 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
  2. 合理设计索引，尽量缩小锁的范围
  3. 尽可能较少检索条件，避免间隙锁
  4. 尽量控制事务大小，减少锁定资源量和时间长度
  5. 尽可能低级别事务隔离

filesort

mysql 的排序算法

  1. 双路排序 MySQL 4.1 之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据，读取行指针和 orderby 列，对他 们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。 从磁盘取排序字段，在 buffer 进行排序，再从磁盘取其他字段。 简单来说，取一批数据，要对磁盘进行了两次扫描，众所周知，I\O 是很耗时的，所以在 mysql4.1 之后，出现了第二种改进的算法，就是单路排序。

  2. 单路排序 从磁盘读取查询需要的所有列，按照 order by 列在 buffer 对它们进行排序，然后扫描排序后的列表进行输出， 它的效率更快一些，避免了第二次读取数据。并且把随机 IO 变成了顺序 IO,但是它会使用更多的空间， 因为它把每一行都保存在内存中了。

  3. 单路排序的问题 由于单路是后出的，总体而言好过双路。但是存在以下问题:

在 sort_buffer 中，方法 B 比方法 A 要多占用很多空间，因为方法 B 是把所有字段都取出, 所以有可能取出的数 据的总大小超出了 sort_buffer 的容量，导致每次只能取 sort_buffer 容量大小的数据，进行排序(创建 tmp 文件，多 路合并)，排完再取取 sort_buffer 容量大小，再排......从而多次 I/O。

结论:本来想省一次 I/O 操作，反而导致了大量的 I/O 操作，反而得不偿失

优化

  1. 增大 sort_butter_size 参数的设置 不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的1M-8M 之间调整。

  2. 增大 max_length_for_sort_data 参数的设置 mysql 使用单路排序的前提是排序的字段大小要小于 max_length_for_sort_data。 提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出 sort_buffer_size 的概率就增大，明显症状是高的磁盘 I/O 活动和低的处理器使用率。(1024-8192 之间调整)。

  3. 减少 select 后面的查询的字段。 当 Query 的字段大小总和小于 max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法——单路排序， 否则用老算法——多路排序。

两种算法的数据都有可能超出 sort_buffer 的容量，超出之后，会创建 tmp 文件进行合并排序，导致多次 I/O， 但是用单路排序算法的风险会更大一些,所以要提高 sort_buffer_size。

数据库日志

mysql 一致性日志

innodb引擎中，根一致性相关的日志有重做日志（redolog），回滚日志（undolog），二进制日志（binlog）。

  • redo log：当有数据写请求时，在数据真正更改前，innodb会先把相关操作写入redo日志。这样如果数据库宕机恢复后仍能继续完成相关修改。
  • undo log：记录事务开始前的数据状态，当事务进行了一半时数据库宕机恢复后能够正常回滚事务。
  • binlog：MySQL Server层维护的一种二进制日志，记录了所有的DDL和DML语句，binlog主要是用于数据库的复制和恢复。
  • checkpoint：当遇到内存不足、db buffer已满等情况时，需要将db buffer中的内容/部分内容转储到data file中。在转储时，会记录checkpoint发生的”时刻“。在故障恢复时候，只需要redo/undo最近的一次checkpoint之后的操作。

binlog格式

  • statement：基于语句的复制:备库把sql再执行一遍 好处：二进制日志里的事件更加紧凑,节省宽带 坏处：sql包含了动态函数会导致数据不一致(CURRENT_USER()函数),存储过程和触发器也会存在问题。
  • RAW:基于行的复制:把实际数据记录到二进制日志中 好处：可以正确的复制每一行, 坏处：对于更新范围大的事件,日志事件会很庞大,若修改表结构,则会出现问题,同时会产生大量日志
  • 混合模式:一般的复制使用语句模式,对于语句无法复制的操作使用ROW模式。

InnoDB和MyISAM的区别

InnoDB和MyISAM的区别

  • InnoDB支持事务，MyISAM不支持。
  • InnoDB(Mysql中唯一支持的内置存储引擎)支持外键，而MyISAM不支持。
  • InnoDB支持表锁和行级锁。MyISAM支持表级锁。尽管myisam是表级锁,它依然可以一边读取一边并发追加新的行。
  • InnoDB是聚集索引，B+tree作为结构。MyISAM是非聚集索引,使用B+tree作为数据结构。InnoDB必须有主键,MyISAM可以没有。
  • InnoDB不保存表的具体行数，而MyISAM用变量保存了行数。
  • InnoDB不支持全文索引，MyISAM支持全文索引。
  • MyISAM表可以被压缩以后进行查询。
  • InnoDB使用日志减少提交事务时的开销。

4.参考

avatar
文章
阅读
粉丝