数据库|MySQL高阶---存储引擎、索引、锁数据库|存储引擎|索引|锁|mys

存储引擎 1. 介绍 MySQL体系结构

文章图片

【数据库|MySQL高阶---存储引擎、索引、锁】MySQL数据库使用不同的机制存取表文件，包括存储方式、引技巧、锁定水平等不同的功能。这些不同的技术以及配套的功能称为存储引擎。
Oracle、SqlServer等数据库只有一种存储引擎。而MySQL针对不同的需求，配置不同的存储引擎，就会让数据库采取不同处理数据的方式和扩展功能。
MySQL支持的存储引擎有很多，常用的有三种：InnoDB、MyISAM、MEMORY。
特性对比

MyISAM存储引擎：访问快，不支持事务和外键操作。
InnoDB存储引擎：支持事务和外键操作，支持并发控制，占用磁盘空间大。（MySQL5.5版本后默认）
MEMORY存储引擎：内存存储，速度快，不安全。适合小量快速访问的数据。

存储引擎介绍

文章图片

2. 存储引擎的操作查询数据库支持的存储引擎
SHOW ENGINES；
查询某个数据库中所有数据表的存储引擎
SHOW TABLE STATUS FROM 数据库名称；
查询某个数据库中某个数据表的存储引擎
SHOW TABLE STATUS FROM 数据库名称WHERE NAME=数据表名称；
创建数据表，指定存储引擎
CREAT ETABLE 表名(
列名，数据类型，
...
)ENGINE=引擎名称；
修改数据表的存储引擎
ALTER TABLE 表名 ENGINE=引擎名称

/* 查询数据库支持的存储引擎 SHOW ENGINES; */ -- 查询数据库支持的存储引擎 SHOW ENGINES; /* 查询某个数据库中所有数据表的存储引擎 SHOW TABLE STATUS FROM 数据库名称; */ -- 查询db4数据库所有表的存储引擎 SHOW TABLE STATUS FROM db4; /* 查询某个数据库中某个表的存储引擎 SHOW TABLE STATUS FROM 数据库名称 WHERE NAME = '数据表名称'; */ -- 查看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; SHOW TABLE STATUS FROM db4; /* 修改数据表的存储引擎 ALTER TABLE 表名 ENGINE = 引擎名称; */ -- 修改engine_test表的存储引擎为InnoDB ALTER TABLE engine_test ENGINE = INNODB;

3. 存储引擎的选择 MyISAM
特点：不支持事务和外键操作。读取速度快，节约资源。
使用场景：以查询操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高！
InnoDB
特点：MySQL的默认存储引擎，支持事务和外键操作。
使用场景：对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，读写频繁的操作！
MEMORY
特点：将所有数据保存在内存中，在需要快速定位记录和其他类似数据环境下，可以提供更快的访问。
使用场景：通常用于更新不太频繁的小表，用来快速得到访问的结果！
总结：针对不同的需求场景，来选择最适合的存储引擎即可！如果不确定、则使用数据库默认的存储引擎！
索引 1. 介绍 MySQL索引：是帮助MySQL高效获取数据的一种数据结构。所以，索引的本质就是数据结构！
在表数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

文章图片

2. 索引的分类1. 按照功能分类

普通索引：最基本的素引，没有任何限制。
唯一索引：索引列的值必须唯一，但允许有空值。如果是组合素引，则列值组合必须唯一。
主键索引：一种特殊的唯一索引，不允许有空值。在建表时有主键列同时创建主键索引。
联合索引：顾名思义，就是将单列索引进行组合。
外键索引：只有InnoDB引擎支持外键索引，用来保证数据的一致性、完整性和实现级联操作。
全文索引：快速匹配全部文档的方式。InnoDB引擎5.6版本后才支持全文索引。MEMORY引擎不支持。

2. 按照结构分类

BTree索引：MySQL使最频繁的一个素引数据结构，是InnoDB和MyISAM存储引擎默认的素索引类型，底层基于B+Tree数据结构。
Hash索引：MySQL中Memory存储引擎默认支持的索引类型。

3. 索引的操作创建索引
CREATE [UNIQUE | FULLTEXT] INDEX 索引名称
[USING 索引类型] --默认是BTREE
ON表名(列名...);
查看索引
SHOW INDEX FROM 表名;

/* 创建索引 CREATE [UNIQUE|FULLTEXT] INDEX 索引名称 [USING 索引类型]-- 默认是BTREE ON 表名(列名...); */ -- 为student表中的name列创建一个普通索引 CREATE INDEX idx_name ON student(NAME); -- 为student表中的age列创建一个唯一索引 CREATE UNIQUE INDEX idx_age ON student(age); /* 查询索引 SHOW INDEX FROM 表名; */ -- 查询student表中的索引(主键列自带主键索引) SHOW INDEX FROM student; -- 查询db4数据库中的product表 (外键列自带外键索引) SHOW INDEX FROM product;

添加索引
普通索引：ALTER TABLE 表名 ADD INDEX 索引名称(列名)；
组合索引：ALTER TABLE 表名 ADD INDEX 索引名称(列名1，列名2...)；
主键索引：ALTER TABLE 表名 ADD PRIMARYKEY(主键列名)；
外键索引：ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(本表外键列名) REFERENCES 主表名(主键列名)；
唯一索引：ALTER TABLE 表名 ADD UNIQUE 索引名称(列名)；
全文索引：ALTER TABLE 表名 ADD FULLTEXT 索引名称(列名)；
删除索引
DROP INDEX 索引名称 ON 表名;

-- 为student表中score列添加唯一索引 ALTER TABLE student ADD UNIQUE idx_score(score); -- 查询student表的索引 SHOW INDEX FROM student; /* 删除索引 DROP INDEX 索引名称 ON 表名; */ -- 删除idx_score索引 DROP INDEX idx_score ON student;

4. 索引的原理索引是在存储引擎中实现的，不同的存储引擎所支持的索引也不一样，主要介绍InnoDB引擎的BTree索引。
BTree索引类型是基于B+Tree数据结构的，而B+Tree数据结构又是BTree 数据结构的变种。通常使用在数据库和操作系统中的文件系统，特点是能够保持数据稳定有序。
1. 磁盘存储
系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的。
位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。
InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB。
InnoDB引擎将若干个地址连接磁盘块，以此来达到页的大小16KB，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

文章图片

2. BTree

文章图片

3.B+Tree

文章图片

5. 索引设计原则创建索引遵循的原则：
1.对查询频次较高，且数据量比较大的表建立素引。
2.使用唯一索引，区分度越高，使用索引的效率越高。
3.索引字段的选择，最佳候选列应当从where子句的条件中提取。
4.索引虽然可以有效的提升查询数据的效率，但并不是多多益善。
最左匹配原则（适用组合索引）
例如：为user 表中的name、address、phone列添加组合索引
ALTER TABLE user ADD INDEX idx_three(name,address,phone);
此时，组合索引idx_three实际建立了(name)、(name,address)、(name,address,phone)三个索引下面的三个SQL 语句都可以命中索引
SELECT * FROM user WHERE address='北京 AND phone='12345' AND name=张三；
SELECT * FROM user WHERE name=张三 AND address='北京；
SELECT * FROM user WHERE name=张三；
这三条SQL 语句在检索时分别会使用以下索引进行数据匹配
(name,address,phone)
(name,address)
(name)
索引字段出现的顺序可以是任意的，MySQL优化器会帮我们自动的调整where条件中的顺序
如果组合索引中最左边的列不在查询条件中，则不会命中索引
SELECT * FROM user WHERE address=‘北京；
锁 1.介绍锁机制：数据库为了保证数据的一致性，在共享的资源被并发访问时变得安全所设计的一种规则。
锁机制类似多线程中的同步，作用就是可以保证数据的一致性和安全性。
按操作分类
共享锁：也叫读锁。针对同一份数据，多个事务读取操作可以同时加锁而不互相影响，但是不能修改数据。
排他锁：也叫写锁。当前的操作没有完成前，会阻断其他操作的读取和写入。
按粒度分类
表级锁：会锁定整个表。开销小，加锁快。锁定力度大，发生锁冲突概率高，并发度低。不会出现死锁情况。
行级锁：会锁定当前行。开销大，加锁慢。锁定粒度小，发生锁冲突概率低，并发度高。会出现死锁情况。
按使用方式分类
悲观锁：每次查询数据时都认为别人会修改，很悲观，所以查询时加锁。
乐观锁：每次查询数据时都认为别人不会修改，很乐观，但是更新时会判断一下在此期间别人有没有去更新这个数据。
2.共享锁共享锁特点
数据可以被多个事务查询，但是不能修改。
创建共享锁格式
SELECT 语句 LOCK IN SHARE MODE；

-- 数据 -- 创建db10数据库 CREATE DATABASE db10; -- 使用db10数据库 USE db10; -- 创建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);

-- 窗口一 /* 共享锁：数据可以被多个事务查询，但是不能修改创建锁的格式 SELECT语句 LOCK IN SHARE MODE; */ -- 开启事务 START TRANSACTION; -- 查询id为1数据，并加入共享锁 SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE; -- 查询分数为99的数据，并加入共享锁 SELECT * FROM student WHERE score=99 LOCK IN SHARE MODE; -- 提交事务 COMMIT; -- 窗口二 -- 开启事务 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引擎默认加的是行锁) UPDATE student SET NAME='李四四' WHERE id=2; -- 修改id为3数据，姓名改成王五五(修改失败，InnoDB引擎如果不采用带索引的列加锁，加的就是表锁) UPDATE student SET NAME='王五五' WHERE id=3; -- 提交事务 COMMIT;

3. 排他锁排他锁特点
加锁的数据，不能被其他事务加锁查询或修改。
创建排他锁格式
SELECT 语句 FOR UPDATE；

-- 窗口一 /* 排他锁：加锁的数据，不能被其他事务加锁查询或修改排他锁创建格式 SELECT语句 FOR UPDATE; */ -- 开启事务 START TRANSACTION; -- 查询id为1数据，并加入排他锁 SELECT * FROM student WHERE id=1 FOR UPDATE; -- 提交事务 COMMIT; -- 窗口二 -- 开启事务 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;

4. 读锁读锁特点
所有连接只能查询数据，不能修改。
读锁语法格式
加锁
LOCK TABLE 表名 READ；
解锁
UNLQCK TABLES；

-- 数据 -- 创建product表 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);

-- 窗口一 /* 读锁：所有连接只能读取数据，不能修改加锁 LOCK TABLE 表名 READ; 解锁 UNLOCK TABLES; */ -- 为product表添加读锁 LOCK TABLE product READ; -- 查询id为1数据 SELECT * FROM product WHERE id=1; -- 修改id为1数据，将金额修改4999 UPDATE product SET price = 4999 WHERE id=1; -- 解锁 UNLOCK TABLES; -- 窗口二 -- 查询id为1数据 SELECT * FROM product WHERE id=1; -- 修改id为1数据，将金额改成5999(修改失败，只有窗口1解锁后才能修改成功) UPDATE product SET price=5999 WHERE id=1;

5. 写锁写锁特点
其他连接不能查询和修改数据。
写锁语法格式
加锁
LOCK TABLE 表名WRITE；
解锁
UNLOCK TABLES；

-- 窗口一 /* 写锁：其他连接不能查询和修改数据加锁 LOCK TABLE 表名 WRITE; 解锁 UNLOCK TABLES; */ -- 为product表添加写锁 LOCK TABLE product WRITE; -- 查询 SELECT * FROM product; -- 修改 UPDATE product SET price=1999 WHERE id=2; -- 解锁 UNLOCK TABLES; -- 窗口二 -- 查询(查询失败，只有窗口1解锁后才能查询成功) SELECT * FROM product; -- 修改(修改失败，只有窗口1解锁后才能修改成功) UPDATE product SET price=2999 WHERE id=2;

6. 悲观锁与乐观锁悲观锁
就是很悲观，它对于数据被外界修改的操作持保守态度，认为数据随时会修改。
整个数据处理中需要将数据加锁。悲观锁一般都是依靠关系型数据库提供的锁机制。
前面的锁机制都是悲观锁。
乐观锁
就是很乐观，每次自己操作数据的时候认为没有人会来修改它，所以不去加锁。
但是在更新的时候会去判断在此期间数据有没有被修改。
需要用户自己去实现，不会发生并发抢占资源，只有在提交操作的时候检查是否违反数据完整性。

-- 创建city表 CREATE TABLE city( id INT PRIMARY KEY AUTO_INCREMENT,-- 城市id NAME VARCHAR(20),-- 城市名称 VERSION INT-- 版本号 ); -- 添加数据 INSERT INTO city VALUES (NULL,'北京',1),(NULL,'上海',1),(NULL,'广州',1),(NULL,'深圳',1); -- 将北京修改为北京市 -- 1.将北京的版本号读取出来 SELECT VERSION FROM city WHERE NAME='北京'; -- 1 -- 2.修改北京为北京市，版本号+1.并对比版本号是否相同 UPDATE city SET NAME='北京市',VERSION=VERSION+1 WHERE NAME='北京' AND VERSION=1;

方式一
给数据表中添加一个version列，每次更新后都将这个列的值加1。
读取数据时，将版本号读取出来，在执行更新的时候，比较版本号。如果相同则执行更新，如果不相同，说明此条数据已经发生了变化。
用户自行根据这个通知来决定怎么处理，比如重新开始一遍，或者放弃本次更新。
方式二
和版本号方式基本一样，给数据表中添加一个列，名称无所谓，数据类型需要是timestamp。
每次更新后都将最新时间插入到此列。
读取数据时，将时间读取出来，在执行更新的时候，比较时间。
如果相同则执行更新，如果不相同，说明此条数据已经发生了变化。