Mysql 4 【存储引擎，索引，锁，缺集群】

2021/10/14 19:16:04

编程Tag： mysql 查询索引 id 引擎集群磁盘 --

本文主要是介绍Mysql 4 【存储引擎，索引，锁，缺集群】，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

一、存储引擎

体系结构详解

客户端连接
- 支持接口：支持的客户端连接，例如C、Java、PHP等语言来连接MySQL数据库
第一层：网络连接层
- 连接池：管理、缓冲用户的连接，线程处理等需要缓存的需求。
- 例如：当客户端发送一个请求连接，会从连接池中获取一个连接进行使用。
第二层：核心服务层
- 管理服务和工具：系统的管理和控制工具，例如备份恢复、复制、集群等。
- SQL接口：接受SQL命令，并且返回查询结果。
- 查询解析器：验证和解析SQL命令，例如过滤条件、语法结构等。
- 查询优化器：在执行查询之前，使用默认的一套优化机制进行优化sql语句
- 缓存：如果缓存当中有想查询的数据，则直接将缓存中的数据返回。没有的话再重新查询！
第三层：存储引擎层
- 插件式存储引擎：管理和操作数据的一种机制，包括(存储数据、如何更新、查询数据等)
第四层：系统文件层
- 文件系统：配置文件、数据文件、日志文件、错误文件、二进制文件等等的保存

2.MySQL存储引擎

Mysql可以使用不同的存储引擎，不同的存储引擎有不同的机制来存取表文件，锁，索引，锁定水平等等的机制。每种存储引擎都有自己的一套技术，规则。
Mysql默认的存储引擎：InnoDB

存储引擎的分类：

3.存储引擎的操作

3.1 查询数据库支持的引擎

-- 标准语法
SHOW ENGINES;

-- 查询数据库支持的存储引擎
SHOW ENGINES;

-- 表含义:
  - support : 指服务器是否支持该存储引擎
  - transactions : 指存储引擎是否支持事务
  - XA : 指存储引擎是否支持分布式事务处理
  - Savepoints : 指存储引擎是否支持保存点

3.2查询某个数据库中所有数据表的引擎

-- 标准语法
SHOW TABLE STATUS FROM 数据库名称;

-- 查看db9数据库所有表的存储引擎
SHOW TABLE STATUS FROM db9;

3.3查询某个数据库中某个数据表的引擎

-- 标准语法
SHOW TABLE STATUS FROM 数据库名称 WHERE NAME = '数据表名称';

-- 查看db9数据库中stu_score表的存储引擎
SHOW TABLE STATUS FROM db9 WHERE NAME = 'stu_score';

3.4创建数据表，指定存储引擎

-- 标准语法
CREATE TABLE 表名(
	列名,数据类型,
    ...
)ENGINE = 引擎名称;

-- 创建db11数据库
CREATE DATABASE db11;

-- 使用db11数据库
USE db11;

-- 创建engine_test表，指定存储引擎为MyISAM
CREATE TABLE engine_test(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10)
)ENGINE = MYISAM;

-- 查询engine_test表的引擎
SHOW TABLE STATUS FROM db11 WHERE NAME = 'engine_test';

3.5修改表的存储引擎

-- 标准语法
ALTER TABLE 表名 ENGINE = 引擎名称;

-- 修改engine_test表的引擎为InnoDB
ALTER TABLE engine_test ENGINE = INNODB;

-- 查询engine_test表的引擎
SHOW TABLE STATUS FROM db11 WHERE NAME = 'engine_test';

5.总结：引擎的选择

二、Mysql的索引

有索引比没索引查询更快，提高效率。为某一列添加索引，可以提高查询效率。

1.索引的概念

我们之前学习过集合，其中的ArrayList集合的特点之一就是有索引。那么有索引会带来哪些好处呢？
没错，查询数据快！我们可以通过索引来快速查找到想要的数据。那么对于我们的MySQL数据库中的索引功能也是类似的！
MySQL数据库中的索引：是帮助MySQL高效获取数据的一种数据结构！所以，索引的本质就是数据结构。
在表数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
一张数据表，用于保存数据。一个索引配置文件，用于保存索引，每个索引都去指向了某一个数据(表格演示)
举例，无索引和有索引的查找原理

2.索引的分类

我们可以选择对某列创建想要功能的索引，并可以指定他查找数据所依托的数据结构。

eg：对某列添加唯一索引，并使用B+Tree方式进行数据筛选查询。

功能分类
- 普通索引：最基本的索引，它没有任何限制。
- 唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值组合必须唯一。
- 主键索引：一种特殊的唯一索引，不允许有空值。一般在建表时同时创建主键索引。
- 组合索引：顾名思义，就是将单列索引进行组合。
- 外键索引：只有InnoDB引擎支持外键索引，用来保证数据的一致性、完整性和实现级联操作。
- 全文索引：快速匹配全部文档的方式。InnoDB引擎5.6版本后才支持全文索引。MEMORY引擎不支持。
索引的类型
- B+Tree索引：MySQL使用最频繁的一个索引数据结构，是InnoDB和MyISAM存储引擎默认的索引类型。
- Hash索引 : MySQL中Memory存储引擎默认支持的索引类型。
- MySql默认为B+Tree类型

3.索引的操作

数据准备

-- 创建db12数据库
CREATE DATABASE db12;

-- 使用db12数据库
USE db12;

-- 创建student表
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	age INT,
	score INT
);
-- 添加数据
INSERT INTO student VALUES (NULL,'张三',23,98),(NULL,'李四',24,95),
(NULL,'王五',25,96),(NULL,'赵六',26,94),(NULL,'周七',27,99);

创建索引

注意：如果一个表中有一列是主键，那么就会默认为其创建主键索引！(主键列不需要单独创建索引)

[USING 索引类型] -- 默认是B+TREE

-- 标准语法
CREATE [UNIQUE|FULLTEXT] INDEX 索引名称        UNIQUE表唯一索引，FULLTEXT表全文索引，
                                                都不写则代表为普通索引
[USING 索引类型]  -- 默认是B+TREE
ON 表名(列名...);

-- 为student表中姓名列创建一个普通索引
CREATE INDEX idx_name ON student(NAME);

-- 为student表中年龄列创建一个唯一索引
CREATE UNIQUE INDEX idx_age ON student(age);

查看索引

-- 标准语法
SHOW INDEX FROM 表名;

-- 查看student表中的索引
SHOW INDEX FROM student;

alter语句添加索引

-- 普通索引
ALTER TABLE 表名 ADD INDEX 索引名称(列名);

-- 组合索引
ALTER TABLE 表名 ADD INDEX 索引名称(列名1,列名2,...);

-- 主键索引
ALTER TABLE 表名 ADD PRIMARY KEY(主键列名); 

-- 外键索引(添加外键约束，就是外键索引)
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);

-- 唯一索引
ALTER TABLE 表名 ADD UNIQUE 索引名称(列名);

-- 全文索引(mysql只支持文本类型)
ALTER TABLE 表名 ADD FULLTEXT 索引名称(列名);


-- 为student表中name列添加全文索引
ALTER TABLE student ADD FULLTEXT idx_fulltext_name(name);

-- 查看student表中的索引
SHOW INDEX FROM student;

删除索引

-- 标准语法
DROP INDEX 索引名称 ON 表名;

-- 删除student表中的idx_score索引
DROP INDEX idx_score ON student;

-- 查看student表中的索引
SHOW INDEX FROM student;

5.索引的实现原理【只介绍用到的B+Tree】

索引是在MySQL的存储引擎中实现的功能，所以每种存储引擎的索引不一定完全相同，也不是所有的引擎支持所有的索引类型。这里我们主要介绍InnoDB引擎的BTree索引。
B+Tree数据结构是BTree数据结构的进一步优化。而两者都会用到磁盘块（磁盘存储）数据
通常使用在数据库和操作系统中的文件系统，特点是能够保持数据稳定有序。我们逐步的来了解一下。
我们需要理解的
- 磁盘存储
- BTree
- B+Tree

5.1磁盘存储

系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的
位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。
InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB。
InnoDB引擎将若干个地址连接磁盘块，以此来达到页的大小16KB，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

系统从磁盘读到内存是以磁盘块进行的。

而InnoDB存储引擎利用页，先将多个磁盘块连接成一个16kb大小，再将每页进行读取操作，减少了磁盘的 I/O次数，提高效率。

只要磁盘块内有数据，每次访问磁盘块时候就会涉及I/O操作，如果没有数据就不会有I/O操作，（节省时间）

比如磁盘中有三个磁盘块，每个磁盘块分别存3条数据，那么InnoDB就会给每个磁盘块分配一个地址值，通过地址值将多个磁盘块进行连接，以此来达到页的大小16KB，如果想查询5这条数据，是先将此磁盘块的所有数据读取出来，再通过过滤条件获取5这个数据

5.2BTree

红色表id，蓝色表指针，黄色表数据。如图有11个磁盘块，每个磁盘块都有相关数据，比如最上面的磁盘块保存的为id为17，35的数据。id小于17的通过p2的指针来找到磁盘块2，此磁盘块中保存了id为8，12。如果id小于8则通过p5指针找到磁盘块5，8-12之间的数据通过指针p

6找到磁盘块6，以此类推。

BTree的特点：在每个磁盘块上，除了要保存键值以外还保存了真实的数据。根据磁盘块的特点，在每次使用磁盘块时，会把所有数据都进行读取。而不是读取指定的值。每次通过指针指向下一个磁盘块时都会将磁盘块所有内容读取出来。假如需要磁盘块7里面的数据，那么会将磁盘块1，2，7全部读取出来。造成磁盘IO次数的增加。

查找顺序：

模拟查找id为15的过程 :

1.根节点找到磁盘块1，将磁盘块1都读入内存。【磁盘I/O操作第1次】
   比较关键字15在区间（<17），找到磁盘块1的指针P1。
2.P1指针找到磁盘块2，将磁盘块2都读入内存。【磁盘I/O操作第2次】
   比较关键字15在区间（>12），找到磁盘块2的指针P3。
3.P3指针找到磁盘块7，将磁盘块7都读入内存。【磁盘I/O操作第3次】
   在磁盘块7中找到关键字15。

-- 分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。
-- 由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个BTree查找效率的决定因素。BTree使用较少的节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。

5.3B+Tree

B+Tree在分支节点上只保存键值，不保存数据，数据只保存在最下面的叶子节点上。

访问磁盘块1，2的时候没有涉及到I/O操作，因为里面没有数据。

还进行了叶子节点相连接的特点，方便我们进行范围的查询。eg；想查3-15之间的数据，先去找3，3小于28来到磁盘块2，小于10找到磁盘块4。剩下的数据不会再一个一个去找磁盘块查询了，因为相邻两磁盘块之间有一个连接效果，所以就能直接查询出来3-15的数据了。

B+Tree相对于BTree区别：
- 非叶子节点只存储键值信息。
- 所有叶子节点之间都有一个连接指针。
- 数据记录都存放在叶子节点中。

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：

【有范围】对于主键的范围查找和分页查找
【有顺序】从根节点开始，进行随机查找

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2~4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

6.总结：索引的设计原则

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

创建索引时的原则

对查询频次较高，且数据量比较大的表建立索引。

使用唯一索引，区分度越高，使用索引的效率越高。

索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。

使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。

索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。

三、MySQL锁机制

1.锁的概念

之前我们学习过多线程，多线程当中如果想保证数据的准确性是如何实现的呢？没错，通过同步实现。同步就相当于是加锁。加了锁以后有什么好处呢？当一个线程真正在操作数据的时候，其他线程只能等待。当一个线程执行完毕后，释放锁。其他线程才能进行操作！
那么我们的MySQL数据库中的锁的功能也是类似的。在我们学习事务的时候，讲解过事务的隔离性，可能会出现脏读、不可重复读、幻读的问题，当时我们的解决方式是通过修改事务的隔离级别来控制，但是数据库的隔离级别呢我们并不推荐修改。所以，锁的作用也可以解决掉之前的问题！
锁机制 : 数据库为了保证数据的一致性，而使用各种共享的资源在被并发访问时变得有序所设计的一种规则。
举例，在电商网站购买商品时，商品表中只存有1个商品，而此时又有两个人同时购买，那么谁能买到就是一个关键的问题。

这里会用到事务进行一系列的操作：
1. 先从商品表中取出物品的数据
2. 然后插入订单
3. 付款后，再插入付款表信息
4. 更新商品表中商品的数量
以上过程中，使用锁可以对商品数量数据信息进行保护，实现隔离，即只允许第一位用户完成整套购买流程，而其他用户只能等待，这样就解决了并发中的矛盾问题。
在数据库中，数据是一种供许多用户共享访问的资源，如何保证数据并发访问的一致性、有效性，是所有数据库必须解决的一个问题，MySQL由于自身架构的特点，在不同的存储引擎中，都设计了面对特定场景的锁定机制，所以引擎的差别，导致锁机制也是有很大差别的。

2.锁的分类

按操作分类：
- 共享锁：也叫读锁。针对同一份数据，多个事务读取操作可以同时加锁而不互相影响，但是不能修改数据记录。【多个事务可以都加锁，可以共存】【针对一同一张表，第一个事务调用此表可以开启锁查询，第二个事务也可以调用此表时开起锁查询】
- 排他锁：也叫写锁。当前的操作没有完成前,会阻断其他操作的读取和写入【不能共存】
按粒度分类：
- 表级锁：操作时，会锁定整个表。开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低。偏向于MyISAM存储引擎！
- 行级锁：操作时，会锁定当前操作行。开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高。偏向于InnoDB存储引擎！
- 页级锁：锁的粒度、发生冲突的概率和加锁的开销介于表锁和行锁之间，会出现死锁，并发性能一般。
按使用方式分类：
- 悲观锁：每次查询数据时都认为别人会修改，很悲观，所以查询时加锁。
- 乐观锁：每次查询数据时都认为别人不会修改，很乐观，但是更新时会判断一下在此期间别人有没有去更新这个数据
不同存储引擎支持的锁

存储引擎	表级锁	行级锁	页级锁
MyISAM	支持	不支持	不支持
InnoDB	支持	支持	不支持
MEMORY	支持	不支持	不支持
BDB	支持	不支持	支持

3.演示InnoDB锁

数据准备：

-- 创建db13数据库
CREATE DATABASE db13;

-- 使用db13数据库
USE db13;

-- 创建student表
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	age INT,
	score INT
);
-- 添加数据
INSERT INTO student VALUES (NULL,'张三',23,99),(NULL,'李四',24,95),
(NULL,'王五',25,98),(NULL,'赵六',26,97);

InnoDB中的共享锁：

-- 标准语法
SELECT语句 LOCK IN SHARE MODE;

共享锁：数据可以被多个事务查询，但是不能修改

加入锁的数据可以被多个事务一起查询，但无法一起修改，必须等第一个开启的锁提交事务以后才能进行其他事务的修改

InnoDB引擎默认是行锁（前提是：这个列有索引），如果查询条件中指出的列为不带索引的则升级为表锁。

情况1：行锁情况

AB开启事务，A中执行条件：id=1的查询语句（加锁的）之后，B中再 执行条件：id=1的普通查询语句依旧可以查询成功，执行加锁的查询语句也可以查成功，但是对where条件为id=1的数据行进行修改，修改失败。对id=1 加锁的只允许查询不允许修改。对id=2的进行修改语句操作执行成功（没有所限制）。只有A提交事务以后B事务才可以修改到表中

原因：InnoDB引擎默认是行锁，因为where的条件为id，id为主键，有主键索引，所以他是行锁。如果没有索引就会升级为表锁。eg：where score=20，就是表锁

eg：查询id=1的那一行并加上锁，那么这一行的数据都会被锁住，无法修改这一行的数据，但能查询。只影响的是id为1的那一行，而不影响其他行数据的修改。

-- 窗口A
/*
	共享锁：数据可以被多个事务查询，但是不能修改
*/
-- 开启事务
START TRANSACTION;

-- 查询id为1的数据记录。加入共享锁
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

-- 提交事务
COMMIT;


-- 窗口B
-- 开启事务
START TRANSACTION;

-- 查询id为1的数据记录(普通查询，可以查询)
SELECT * FROM student WHERE id=1;

-- 查询id为1的数据记录，并加入共享锁(可以查询。共享锁和共享锁兼容)
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

-- 修改id为1的姓名为张三三(不能修改，会出现锁的情况。只有窗口1提交事务后，才能修改成功)
UPDATE student SET NAME='张三三' WHERE id = 1;

-- 修改id为2的姓名为李四四(修改成功，InnoDB引擎默认是行锁，
                                比如上一条就无法修改成功，因为把id=1的那一行锁起来了)
UPDATE student SET NAME='李四四' WHERE id = 2;

-- 提交事务
COMMIT;

情况2：

AB开启事务，A事务中where条件为score，score没有任何索引约束，执行A中查询语句，再去执行B中修改语句，执行失败（一直处于加载中）。

原因：A中score没有任何索引，那么对其查询加锁操作中，默认将锁升级为表锁。整个表都被锁住，所以B中无法执行成功。

-- 窗口A
/*
	共享锁：数据可以被多个事务查询，但是不能修改
*/
-- 开启事务
START TRANSACTION;

-- 查询分数为99分的数据记录。加入共享锁
SELECT * FROM student WHERE score=99 LOCK IN SHARE MODE;

-- 提交事务
COMMIT;

-- 窗口B
-- 开启事务
START TRANSACTION;

-- 修改id为3的姓名为王五五(注意：InnoDB引擎如果不采用带索引的列。则会提升为表锁)
UPDATE student SET NAME='王五五' WHERE id = 3;

-- 提交事务
COMMIT;

InnoDB中的排他锁：

-- 标准语法
SELECT 语句 FOR UPDATE;

排他锁：加锁的数据，不能被其他事务加锁查询或修改

执行窗口1，2，分别开启事务，1中执行查询语句并添加排他锁，在2中普通查询，执行成功。

在2中查询id为1的数据记录，并加入共享锁，不能查询。因为排他锁不能和其他锁共存。

-- 窗口1
/*
	共享锁：数据可以被多个事务查询，但是不能修改
*/
-- 开启事务
START TRANSACTION;

-- 查询id为1的数据记录，并加入排他锁
SELECT * FROM student WHERE id=1 FOR UPDATE;

-- 提交事务
COMMIT;


-- 窗口2
-- 开启事务
START TRANSACTION;

-- 查询id为1的数据记录(普通查询没问题)
SELECT * FROM student WHERE id=1;

-- 查询id为1的数据记录，并加入共享锁(不能查询。因为排他锁不能和其他锁共存)
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

-- 查询id为1的数据记录，并加入排他锁(不能查询。因为排他锁不能和其他锁共存)
SELECT * FROM student WHERE id=1 FOR UPDATE;

-- 修改id为1的姓名为张三(不能修改，会出现锁的情况。只有窗口1提交事务后，才能修改成功)
UPDATE student SET NAME='张三' WHERE id=1;

-- 提交事务
COMMIT;

-- 修改id为3的姓名为王五五(注意：InnoDB引擎如果不采用带索引的列。则会提升为表锁)
UPDATE student SET NAME='王五五' WHERE id = 3;

添加失败

分析：

如果修改数据的这条语句不带索引

MyISAM锁机制

支持的是表锁，锁住的是整张表

读锁

MyISAM中没有事务的概念。