MYSQL储存引擎与锁

MYSQL结构体系

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

1.储存引擎

1.储存引擎概述

MyQL数据库使用不同的机制存取表文件，包括存储方式，索引技巧，锁定水平等不问的功能、这不同的技术以及配套的功能称为存储引擎
Oracle、SqlServer等数据库只有一种存储引擎。而MySQL针对不同的需求配置不同的存学就会让数据库采取不同处理数据剧的方式和扩展功能
MySQL支持的存储引章有很多，常用的有三种：InnoDB、MyISAM、MEMORY
特性对比
MylSAM存储引擎：访问快，不支持事务和外健作，
In门oDB存储引擎：支持事务和外操作，支持并发控制，占用磁盘空问大。(My5QL5.5版本后默认
MEMORY存储引擎：内存存储，速应快，不安全，适合小录快速方问的数据

2.储存引擎的操作

查询数据库支持的储存引擎

SHOW ENGINES;

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

SHOW TABLE STATUS FROM 数据库名称;

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

SHOW TABLE STATUS FROM 数据库名称 WHEWE NAME='数据表名称';

创建数据表指定存储引擎

CREATE TABLE 表名(
        列名，数据类型，
        ......
)ENGINE =引擎名称；

修改数据表储存引擎

ALTER TABLE 表名 ENGINE=引擎名称；

例：

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

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

--查询db4数据库中category表的储存引擎
SHOW TABLE STATUS FROM db4 WHERE NAME='category';

/*
		CREATE TABLE 表名(
        列名，数据类型，
        ......
)ENGINE =引擎名称；
*/
CREATE TABLE engine_test(
        id INT PRIMARY KEY AUTO_INCREMENT,
				NAME VARCHAR(10)
)ENGINE = MYISAM;

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

3.储存引擎使用建议

2.锁

1.锁的概述

锁是数据库系统区分与文件系统的一个关键特性。

为了保证数据一致性，必须有锁的介入。

数据库系统使用锁是为了支持对共享资源进行并发访问，提供数据的完整性和一致性。

mysql锁主要是为了解决并发写数据时的一种安全机制。

锁机制：数据库为了保证数据的一致性，在共享的资源被井发访问时变得安全所设计的一种规则
锁机制类似多线程中的同步，作用就是可以保证数据的一致性和安全性。
按操作分类
共享锁；也叫读锁。针对同一份数据，多个事务读取操作可以同时加锁而不互相影响，但是不能修改数。
排他锁：也叫写锁。当前的操作没有完成前，会组断其他操作的读取和写入，
按粒度分类
表级锁：会锁定整个表，开销小，加损快。锁定力度大，发生锁冲突概率高，并发度低，不会出现死锁情况
行级锁：会锁定当前行。开销大，加锁慢，锁定粒度小，发生锁冲突概率低，并发度高。会出现死锁调况。
按使用方式分类
悲观锁：每次查询数据时都认为别人会修改，很悲观，所以查询时加锁。
乐观锁：每次查询数时都认为别人不会修改，很乐观，但是更新时会判断一下在此期间别人有没有去更新这个

 

2.InnoDB锁

共享锁特点：加了锁的记录，所有事务都能去读取但不能修改，同时阻止其他事务获得相同数据集的排他锁

排它锁特点：允许已经获得排他锁的事务去更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁

创建共享锁格式

SELECT语句 LOCK IN SHARE MODE;

 例：

窗口1：

CREATE DATABASE db10;
USE db10;
CREATE TABLE student(
				id INT PRIMARY KEY AUTO_INCREMENT,
				NAME VARCHAR(10),
				age INT,
				score INT
);
INSERT INTO student VALUES(NULL,'张三',23,99);
INSERT INTO student VALUES(NULL,'李四',24,95);
INSERT INTO student VALUES(NULL,'王五',25,98);
INSERT INTO student VALUES(NULL,'赵六',26,97);

--开始事务
START TRANSACTION;

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

--提交事务
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数据，姓名改为张三三(修改失败，会出现锁的情况)
UPDATE student SET NAME='张三三' WHERE id=1;

--修改id为2数据，姓名改为李四四(修改成功，InnoDB引擎默认加的是行锁)
UPDATE student SET NAME='李四四' WHERE id=2;

--注：InnoDB引擎如果不采用带索引的列加锁，加的就是表锁

--提交事务
COMMIT

创建排他锁格式

SELECT语句 FOR UPDATE;

例：

窗口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;

3.MYISAM读锁与写锁

例： 

准备数据：

CREATE TABLE product(
				id INT PRIMARY KEY AUTO_INCREMENT,
				NAME VARCHAR(20),
				price INT
) ENGINE = MYISAM;--指定储存引擎为MyISAM

--添加数据
INSERT INTO product VALUES (NULL,'华为',4999),(NULL,'小米',2999),(NULL,'苹果',8999),(NULL,'中兴',1999);

窗口1：

--为product表添加读锁
LOCK TABLE product READ;

--查询id为1数据
SELECT * FROM product WHERE id=1;

--解锁
UNLOCK TABLES;

窗口2：

--查询id为1数据
SELECT * FROM product WHERE id=1;

--修改id为1数据，将金额改成5999(修改失败，只有窗口1解锁后才能修改成功)
UPDATE product SET price=5999 WHERE id=1;

 

例：

窗口1：

--为product表添加写锁
LOCK TABLE product WRITE;

--查询
SELECT * FROM product;

--修改
UPDATE product SET price=1999 WHERE id=2;

--解锁
UNLOCK TABLES;

窗口2：

--查询(查询失败，只有窗口1解锁后才能查询成功)
SELECT * FROM product;

--修改(修改失败，只有窗口1解锁后才能修改)
UPDATE product SET price =2999 WHERE id=2;

4.乐观锁与悲观锁

乐观锁：顾名思义，就是非常乐观，乐观锁认为数据一般情况下不会造成冲突，所以在数据提交更新的时候需要用户自己实现。适用于读操作多，写操作少的场景

悲观锁：是相比较乐观锁而言的，就是比较悲观，悲观锁认为数据每次操作都会被修改，所以在每次操作数据时都会加上锁。适用于并发量不大，写操作多，读操作少的场景

乐观锁是基本版本号机制实现的，数据表中增加一个 version 字段，读取数据时将 version 一起读出。数据每更新一次，version 字段值 + 1。当修改需要提交时，将读取时的版本号与数据库当前版本号做比较，如果一致，说明在此期间无人修改这条记录，不一致则说明已经被修改了，提交失败。 

悲观锁通过共享锁和排他锁实现