欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL高级(数据库引擎,索引原理)

程序员文章站 2022-06-24 11:54:30
1. 存储引擎1.1 概念相关(理解)MySQL 数据库使用不同的机制存取表文件, 包括存储方式、索引技巧、锁定水平等不同的功能。这些不同的技术以及配套的功能称为存储引擎。Oracle、SqlServer 等数据库只有一种存储引擎。而 MySQL 针对不同的需求, 配置不同的存储引擎, 就会让数据库采取不同处理数据的方式和扩展功能。MySQL中,存储引擎是表级别的。MySQL 支持的存储引擎常见的有三种:InnoDB、MyISAM、MEMORY。特性对比InnoDB...

1. 存储引擎

MySQL高级(数据库引擎,索引原理)

1.1 概念相关(理解)

  • MySQL 数据库使用不同的机制存取表文件, 包括存储方式、索引技巧、锁定水平等不同的功能。这些不同的技术以及配套的功能称为存储引擎。

  • Oracle、SqlServer 等数据库只有一种存储引擎。而 MySQL 针对不同的需求, 配置不同的存储引擎, 就会让数据库采取不同处理数据的方式和扩展功能。

  • MySQL中,存储引擎是表级别的。

  • MySQL 支持的存储引擎常见的有三种:InnoDB、MyISAM、MEMORY。

  • 特性对比

    1. InnoDB 存储引擎:支持事务和外键操作,支持“增删改”并发,适用场景更加通用(MySQL 5.5版本后默认)
    2. MyISAM 存储引擎:不支持事务和外键操作;针对只做“查询操作”的表,查询速度是最快的。
    3. MEMORY 存储引擎:内存存储,速度快,不安全。相当于是一个大的Map集合,内存存储不安全,基本上不用。

MySQL高级(数据库引擎,索引原理)

特性 InnoDB MyISAM MEMORY
存储限制
事务安全
锁机制
B+Tree索引
哈希索引
全文索引
集群索引
数据索引
数据缓存
索引缓存
数据可压缩
空间使用
内存使用
批量插入速度
外键

1.2 存储引擎的操作

一般不需要修改表的引擎,知道如何查询即可。

/*
	查询数据库支持的存储引擎
	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;

1.3 存储引擎的选择

MySQL高级(数据库引擎,索引原理)

2.索引(重点)

2.1 索引概念

MySQL的数据需要持久化保存到硬盘,查询的时候将硬盘上的数据加载到内存后再做操作。

MySQL 索引:是帮助 MySQL 高效查询记录的排序的数据结构,该结构由MySQL基于表数据独立维护。

索引的本质是排序,排序的数据结构;排序完之后,可以快速高效的查询出我们想要的结果。

常见的排序以便查询的数据结构:二叉树、红黑树、Hash、BTree、B+Tree

MySQL默认采用的是B+Tree(多路平衡搜索树<多叉树>)

2.2 索引的优缺点

  • 优势

    1. 类似于书籍的目录索引,提高表记录检索的效率,降低数据库的IO成本。
    2. 通过索引列对表记录进行排序,降低数据排序的成本,降低CPU的消耗。
  • 劣势

    1. 索引要基于表记录独立维护,并存储在磁盘中。所以索引需要额外占用空间。
    2. 当表中记录发生修改(增删改)时,为了持续保证查询效率,MySQL需要在更新表记录的同时,需要更新索引结构中对应的内容,当表记录数据量较大,且更新较频繁时,如果此时维护了大量索引,会严重影响更新表记录的效率性能。

2.3 索引的分类

  • 按照功能分

    主键索引:主键列自动添加该索引。MySQL中InnoDB存储引擎的表中必定有主键索引。

    普通索引:最基本的索引,对于没有特殊约束的列建立的索引,没有任何限制。

    唯一索引:唯一约束列自动添加该索引。如果是组合索引,则列值组合必须唯一。

    外键索引:外键列自动添加该索引。用来保证数据的一致性、完整性和实现级联操作。

    联合索引:就是将多个单列索引进行组合。

    全文索引:快速匹配全部文档的方式。InnoDB 引擎 5.6 版本后才支持,MEMORY 引擎不支持。后面学习其他替代技术。

  • 按照索引的数据结构分类

    BTree 索引:MySQL 常用的索引数据结构,是 InnoDB 和 MyISAM 存储引擎默认该类型。底层基于 B+Tree 数据结构。B+Tree衍生自BTree。

    Hash 索引:MySQL 中 Memory 存储引擎默认支持的索引类型,查询速度很快(范围查询除外)。

  • InnoDB引擎的表默认必有主键索引

    首先如果表中指定了主键,则有主键和主键索引;

    如果没有主键,MySQL会根据表中有唯一非空约束的那一列,自动在后台建立并维护虚拟主键,对用户透明。

    如果没有主键,也没有唯一非空约束的列,MySQL就自动增加一列row_id,并把row_id设为主键,对用户透明。

经验分享:如果业务允许。InnoDB的表一定要定义主键,而且建议整型、自增。

2.4 索引操作

2.4.1准备数据

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

-- 使用db9数据库
USE db9;

-- 创建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);

2.4.2 创建和查看索引

/*
	创建索引
	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;

2.4.3 添加不同的索引及删除

/*
	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 索引名称(列名);

	-- 全文索引
	ALTER TABLE 表名 ADD FULLTEXT 索引名称(列名);
*/
-- 为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;

2.5 索引原理

2.5.1 关于磁盘块(扇区)

想要达到更快的查询速度的效果

  1. 尽可能少的读取扇区的个数
  2. 尽可能较少寻道时间

以最终提高读取磁盘内容速度的目的

2.5.2 MySQL中页的概念

页是MySQL中最小的磁盘管理单元,页的默认大小是16kb。

show global variables like '%page%';  #InnoDB page size= 16384 = 16kB

MySQL中一次IO就可以理解成读取一个页的数据。

2.6 面试技术名词概念

2.6.1 (非)聚簇索引

  • 索引和数据存储在一起,称为聚簇(聚集)
  • 主键索引是聚簇索引,其他索引为非聚簇索引,也就是二级索引。
  • 聚簇索引,叶子节点存储的内容是:所有索引以及索引对应的记录(数据)
  • 非聚簇索引叶子节点存储的内容是:name索引和索引所在记录的主键索引(不包含记录数据)

MySQL高级(数据库引擎,索引原理)

2.6.2 回表、索引覆盖

MySQL高级(数据库引擎,索引原理)

2.6.3 联合索引

stu表根据name、age、score两个列建立联合索引

原理是:把name,age,score作为一个完整的字符串当做索引

xxx20

MySQL高级(数据库引擎,索引原理)

2.7 索引的优化

  1. 不要为表中所有列建立索引,一个表中的索引不超过5个(索引是一种数据结构,首先是占据空间;其次维护这些数据结构是需要时间、磁盘IO性能的消耗。索引越多,增删的时候效率越慢,因为有太多的索引树需要维护)
  2. 索引字段的选择,最佳候选列应当从 where 子句的条件中提取;
  3. 不建议在数据库设计好之后就立即建立索引,运行一段时间之后根据执行查询SQL统计得出高频条件列;对查询频次较高,且数据量比较大的表、列建立索引。
  4. 最左匹配原则
  5. 索引列的值的长度要尽可能短,如果作为索引的列过长,可以通过列名(长度的数字)截取前面一段作为索引。要求第一要尽量短,第二要求要有区分度(80%以上)。
  6. 其他基于B+Tree索引数据结构的优化

2.7.1 最左匹配原则

数据准备

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` tinyint(4) NULL DEFAULT NULL,
  `score` int(11) NULL DEFAULT NULL,
  `gender` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_nas`(`name`, `age`, `score`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '张三', 23, 98, 'M');
INSERT INTO `student` VALUES (2, '李四', 24, 97, 'F');
INSERT INTO `student` VALUES (3, '王五', 25, 99, 'M');
INSERT INTO `student` VALUES (4, '赵六', 33, 100, 'M');
INSERT INTO `student` VALUES (5, '王二麻子', 30, 90, 'F');
INSERT INTO `student` VALUES (6, '狗二蛋', 32, 82, 'M');

演示代码

ALTER TABLE student ADD INDEX idx_nas(`NAME`,age,score);

DROP INDEX idx_nas ON student;

DESC student;

-- 查看索引
SHOW INDEX FROM student;

-- EXPLAIN显示当前查询是否使用索引
-- 命中索引的情况
EXPLAIN select * from student where name ='xxx';
EXPLAIN select * from student where name ='xxx' AND age=11;
EXPLAIN select * from student where name ='xxx' AND age=11 AND score = 80;

-- sql优化器会调整参数顺序,最终命中索引
EXPLAIN select * from student where  score = 80 AND name ='xxx' AND age=11; 


-- 不命中索引的情况
EXPLAIN select * from student where age=11 AND score = 80;
EXPLAIN select * from student where score = 80;
EXPLAIN select * from student where age=11;

-- 不完全命中索引的情况
EXPLAIN select * from student where name ='xxx' AND score = 80;
EXPLAIN select * from student where name ='xxx' AND age>11 AND score = 80;

2.8 面试必备必备技能

MySQL优化面试准备:https://www.bilibili.com/video/av68811608

3. 锁(了解)

3.1 概念和分类

**概念:**锁机制类似多线程中的同步,作用就是可以保证数据的一致性和安全性。

  • 按操作分类
    • 读锁:共享锁,读读共享。
    • 写锁:排他锁,读写/写写排他。
  • 按粒度分
    • 表锁
    • 行锁
  • 使用方式分
    • 悲观锁:借助于数据事务的特性来实现。
    • 乐观锁(CAS):不是数据库本身的安全机制,而是我们通过自己的逻辑实现。

不同引擎对锁的支持不一样。

  • InnoDB支持表锁和行锁,支持事务
  • MyISAM、MEMORY只支持表锁,不支持事务

3.2 InnoDB共享锁

读读共享,读写排他。

/*
	共享锁:数据可以被多个事务查询,但是不能修改
	创建锁的格式
		SELECT语句 LOCK IN SHARE MODE;
	一个事务中的增删改会自动加行级别的写锁
*/
-- 开启事务
START TRANSACTION;

-- 查询id为1数据,并加入共享锁
-- ★1★2 给表的索引列加共享锁,此时是行级别,其他事务不允许对该行加写锁操作
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

-- 查询分数为99的数据,并加入共享锁
-- ★3 给表的非索引列加共享锁,此时是表级别,其他事务不允许对该表加写锁操作
SELECT * FROM student WHERE score=99 LOCK IN SHARE MODE;

-- 提交事务
COMMIT;
-- 开启事务
START TRANSACTION;

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

-- ★1查询id为1数据,也加入共享锁(共享锁和共享锁是兼容)
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

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

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

-- 修改id为3数据,姓名改成王五五(修改失败,★3InnoDB引擎如果不采用带索引的列加锁,加的就是表锁)
UPDATE student SET NAME='王五五' WHERE id=3;


-- 提交事务
COMMIT;

3.3 InnoDB排他锁

/*
	排他锁:加排他锁的数据,不能被其他事务加锁查询或修改
	排他锁创建格式
		SELECT 语句 FOR UPDATE;
*/
-- 开启事务
START TRANSACTION;

-- 查询id为1数据,并加入排他锁
-- ★1 给表的索引列加排他锁,此时是行级别,其他事务不允许对该行做加锁操作
-- (★1 加锁查询和增删改都不行,
-- ★2 不加锁的增删改不行,不加锁的查询可以,)
SELECT * FROM student WHERE id=1 FOR UPDATE;

-- ★3 给表的非索引列加排他锁,此时是表级别,其他事务不允许对该表做加锁操作(加锁查询和增删改都不行)
select *from student where score=99 for update;

-- 提交事务
COMMIT;
-- 开启事务
START TRANSACTION;

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


-- ★1 查询id为1数据,并加入共享锁(排他锁和共享锁是不兼容的)
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

-- ★1  查询id为1数据,并加入排他锁(排他锁和排他锁是不兼容的)
SELECT * FROM student WHERE id=1 FOR UPDATE;

-- ★3 修改id为1数据,将姓名改成张三(修改失败,会出现锁的情况。只有窗口1提交事务后才能修改成功)
-- ★2 修改id为1数据(普通增删改也不可以)
UPDATE student SET NAME='张三' WHERE id=1;

-- 提交事务
COMMIT;

3.4 MYISAM读锁

明确两个点

  1. MyISAM引擎的表只有表锁,没有行锁。
  2. MyISAM引擎的表不支持事务
/*
	读锁:所有连接只能读取数据,不能修改
	加锁
		LOCK TABLE 表名 READ;  -- ☆为整张表加读锁(只能加表锁,不能加行锁,因为MyISAM不支持行锁)

	解锁
		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;

3.5 MyISAM写锁

/*
	写锁:其他连接不能查询和修改本表数据,本连接可以增删改查
	加锁
		LOCK TABLE 表名 WRITE; -- ☆为整张表加写锁(只能加表锁,不能加行锁,因为MyISAM不支持行锁)

	解锁
		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;

3.6 悲观锁和乐观锁

/*
	乐观锁(CAS机制)
		不是mysql原生支持的安全机制,不依赖于mysql本身的锁机制,而是我们根据需要自己完成安校验逻辑。
		本质上是添加一个字段,作为数据的版本或者标识。
		乐观锁适用查询较多的场景,不适用修改比较多的场景。
		
	悲观锁
		依赖于mysql本身的锁机制(事务管理机制),之前演示的各种锁都属于悲观锁。
		适用于修改频繁的场景
		

	乐观锁实现方式有很多种,下面演示两种
	1. 为表添加版本号列,每次修改,版本号+1
	2. 为表添加时间戳列,每次修改后,修改该值为当前时间
	
	每次修改前,都对比数据库中当前记录中的标记(版本或时间戳)和已经查出来的是否一致,
	    如果一致,就修改数据,同时修改标记为最新的。
	    如果不一致,就放弃更新或者重新查找之后再更新(同时更新标志位最新的)。

*/

-- 创建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;

身的锁机制(事务管理机制),之前演示的各种锁都属于悲观锁。
适用于修改频繁的场景

乐观锁实现方式有很多种,下面演示两种
1. 为表添加版本号列,每次修改,版本号+1
2. 为表添加时间戳列,每次修改后,修改该值为当前时间

每次修改前,都对比数据库中当前记录中的标记(版本或时间戳)和已经查出来的是否一致,
    如果一致,就修改数据,同时修改标记为最新的。
    如果不一致,就放弃更新或者重新查找之后再更新(同时更新标志位最新的)。

*/

– 创建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;


本文地址:https://blog.csdn.net/qq_38250571/article/details/110674371