a.数据库的引擎
什么是引擎?在日常生活中,引擎就是整个机器运动的核心,不同的引擎具有不同的功能.
例:兰博尼基的引擎不可能放到手扶拖拉机上使用,航天飞机的引擎也不能放到法拉利上使用,因为它们的功率不同,性能有所区别.需要在安装到最合适的机器上才可以发挥出最好的性能.
MySQL存储引擎的概念
MySQL数据库使用不同的机制存取表文件,机制的差别在于不同的存储方式/索引技巧/锁定水平等等,在MySQL中将这些不同的技术以及配套的功能称为存储引擎.
在关系型数据库中数据的存储是以表的形式进行存储的,所以存储引擎也可以称为表类型
ORACLE和SqlServer等数据库只有一种存储引擎,而MySQL有多种,针对不同的需求,配置MySQL的不同引擎,就会让数据库采取不同的处理数据的方式和拓展功能,更好的发挥数据库的性能.
MySQL支持的存储引擎
MySQL支持的引擎包括:InnoDB/MyISAM/MEMORY/Archive/Federate/CSV/BLACKHOLE
其中较为常用的有三种:InnoDB/MyISAM/MEMORY
常用的存储引擎的特性对比
- MyISAM存储引擎
访问快,不支持事务和外键,表结构存储在.frm文件中,表数据保存在.MYD文件中,索引保存在.MYI文件中.
- InnoDB存储引擎(MySQL5.5之后的默认存储引擎)
支持事务,占用磁盘空间大,支持并发控制.
- MEMORY存储引擎
内存存储,速度快,不安全,适合小量快速访问的数据.(类REDIS)
| 特性 | MyISAM | InnoDB | MEMORY |
|---|---|---|---|
| 存储限制 | 有(平台对文件系统大小的限制) | 64TB | 有(平台的内存限制) |
| 事务安全 | 不支持 | 支持 | 不支持 |
| 锁机制 | 表锁 | 表锁/行锁 | 表锁 |
| B+Tree索引 | 支持 | 支持 | 支持 |
| 哈希索引 | 不支持 | 不支持 | 支持 |
| 全文索引 | 支持 | 支持 | 不支持 |
| 集群索引 | 不支持 | 支持 | 不支持 |
| 数据索引 | 不支持 | 支持 | 支持 |
| 数据缓存 | 不支持 | 支持 | N/A |
| 索引缓存 | 支持 | 支持 | N/A |
| 数据可压缩 | 支持 | 不支持 | 不支持 |
| 空间使用 | 低 | 高 | N/A |
| 内存使用 | 低 | 高 | 中等 |
| 批量插入速度 | 高 | 低 | 高 |
| 外键 | 不支持 | 支持 | 不支持 |
存储引擎相关的操作
- 查询当前数据库支持的引擎
SHOW ENGINES;
-- support:指服务器是否支持该存储引擎
-- transactions:指存储引擎是否支持事务
-- XA:指存储引擎是否支持分布式事务处理
-- Savepoints:指存储引擎是否支持保存点
- 查询某个数据库中所有数据表的引擎
SHOW TABLE STATUS FROM 数据库名称;
- 查询某个数据库中某个数据表的引擎
SHOW TABLE STATUS FROM 数据库名称 WHERE NAME = '数据表名称';
- 创建数据表并且指定存储引擎
CREATE TABLE 表名(
列名,数据类型,
...
)ENGINE = 引擎名称;
- 修改表的存储引擎
ALTER TABLE 表名 ENGINE = 引擎名称;
MySQL引擎的选择
- MyISAM:由于MyISAM不支持事务/不支持外键/支持全文检索和表级锁定.读写相互阻塞.读取速度快.节约资源.所以如果应用是以查询操作和插入操作为主.只有很少的更新和删除操作.并且对事务的完整性,并发性要求不是很高,那么选择这个存储引擎是非常合适的
- InnoDB:是MySQL的默认存储引擎,由于InnoDB支持事务/支持外键/行级锁定,支持所有辅助索引(5.5.5后不支持全文检索),高缓存,所以用于对事务的完整性有比较高的要求,在并发条件下要求数据的一致性,读写频繁的操作,那么InnoDB存储引擎是比较合适的选择,比如BBS,计费系统,充值转账等.
- MEMORY:将所有数据保存在RAM中,在需要快速定位记录和其他类似数据环境下,可以提供更快的访问。MEMORY的缺陷就是对表的大小有限制,太大的表无法缓存在内存中,其次是要确保表的数据可以恢复,数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表,用以快速得到访问结果。
针对不同的需求场景,来选择最适合的存储引擎即可,如果不确定,则使用数据库默认的存储引擎！
b.数据库的索引
什么是索引?
MySQL数据库中的索引是帮助MySQL高效获取数据的一种数据结构,索引的本质就是数据结构.
索引会有单独的文件进行保存,索引的好处就是更高效的查询数据.
索引的分类
1.按照功能分类
-
普通索引:最基本的索引,它没有任何限制。
-
唯一索引:索引列的值必须唯一,但允许有空值,如果是组合索引,则列值组合必须唯一。
-
主键索引:一种特殊的唯一索引,不允许有空值,一般在建表时同时创建主键索引。
-
组合索引:将单列索引进行组合。
-
外键索引:只有InnoDB引擎支持外键索引,用来保证数据的一致性,完整性和实现级联操作。
-
全文索引:快速匹配全部文档的方式。InnoDB引擎5.6版本后才支持全文索引,MEMORY引擎不支持。
2 按照结构分类
-
B+Tree索引:MySQL使用最频繁的一个索引数据结构,是InnoDB和MyISAM存储引擎默认的索引类型。
-
Hash索引:MySQL中Memory存储引擎默认支持的索引类型。
索引相关的操作
- 索引的相关的数据准备
/
索引的数据准备:
创建数据库db2,字符集选择为UTF-8.
创建数据表student数据表,使用默认存储引擎InnoDB.
添加数据到表中.
*/
CREATE DATABASE IF NOT EXISTS db2 CHARACTER SET UTF8; USE db2;
CREATE TABLE IF NOT EXISTS student(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(10),
age INT,
score INT
)ENGINE = InnoDB;
#如果一张表中有一列是主键列,那么会默认为其创建主键索引(主键列不需要单独创建索引)
- 索引的查看
SHOW INDEX FROM 表名;
-- Non_unique:是否唯一(0表示唯一,1表示不唯一)
-- Key_name:索引名称
-- Column_name:列名
-- Null:是否可以为NULL值
-- Index_type 索引类型,BTREE=B+TREE
- 索引的创建
CREATE [UNIQUE|FULLTEXT] INDEX 索引名称
[USING 索引类型] -- 默认是B+TREE
ON 表名(列名...);
#UNIQUE表示唯一索引,FULLTEXT表示全文索引(二者均为可选) #[USING 索引类型] 默认是B+TREE,一般无需操作. #(列名...)可以给多个列添加索引.
#给student表中的年龄列添加一个普通索引
CREATE INDEX idx_name ON student(age);
#给student表中的姓名列添加一个唯一索引
CREATE UNIQUE INDEX idx_age ON student(name);
- 索引的添加
-- 普通索引
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_fulltest_name(name);
- 索引的删除
DROP INDEX 索引名称 ON 表名;
- 索引的效率测试
/**
索引的效率测试,创建商品表product,定义存储函数,生成长度为10的字符串并返回,添加100万条数据到表中.
*/
CREATE TABLE product(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '商品ID', NAME VARCHAR(10) COMMENT '商品名称', price INT COMMENT '商品价格'
)
-- 定义存储函数,生成长度为10的随机字符串并返回
DELIMITER $
CREATE FUNCTION rand_string() RETURNS VARCHAR(255)
BEGIN
DECLARE big_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIGKLMNOPQRSTUVWXYZ';
DECLARE small_str VARCHAR(255) DEFAULT ''; DECLARE i INT DEFAULT 1;
WHILE i <= 10 DO
SET small_str
=CONCAT(small_str,SUBSTRING(big_str,FLOOR(1+RAND()*52),1)); SET i=i+1;
END WHILE;
RETURN small_str;
END$
DELIMITER ;
-- -------------------------------------------------------------------
-- 定义存储过程，添加100万条数据到product表中
DELIMITER $
CREATE PROCEDURE pro_test()
BEGIN
DECLARE num INT DEFAULT 1;
WHILE num <= 1000000 DO
INSERT INTO product VALUES (NULL,rand_string(),num); SET num = num + 1;
END WHILE;
END$
DELIMITER ;
-- 调用存储过程
CALL pro_test();
#没有索引的情况下查找商品名称是TNgswflOlO的数据.
SELECT * FROM product WHERE NAME = "TNgswflOlO";
#为product表中的name列添加普通索引.
ALTER TABLE product ADD INDEX idx_name(NAME);
MySQL索引的原理
-
索引是在MySQL的存储引擎中实现的,所以每种存储引擎的索引不一定完全相同,也不是所有的引擎支持所有的索引类型。这里我们主要介绍InnoDB引擎的实现的B+Tree索引。
-
B+Tree是一种树型数据结构,是B-Tree的变种,通常使用在数据库和操作系统中的文件系统，特点是能够保持数据稳定有序.
磁盘存储
-
系统从磁盘读取数据到内存时是以磁盘块(block)为基本单位的。
-
位于同一个磁盘块中的数据会被一次性读取出来,而不是需要什么取什么。
-
InnoDB存储引擎中有页(Page)的概念,页是其磁盘管理的最小单位,InnoDB存储引擎中默认每个页的大小为16KB。
-
InnoDB引擎将若干个地址连接磁盘块,以此来达到页的大小16KB,在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置,这将会减少磁盘I/O次数，提高查询效率。
BTree
BTree结构的数据可以让系统高效的找到数据所在的磁盘块,为了描述BTree,首先定义一条记录为一个二元组[key, data],key为记录的键值,对应表中的主键值,data为一行记录中除主键外的数据,对于不同的记录,key值互不相同,BTree中的每个节点根据实际情况可以包含大量的关键字信息和分支,如下图所示为一个3阶的BTree：
根据图中结构显示,每个节点占用一个盘块的磁盘空间,一个节点上有两个升序排序的关键字和三个指向子树根节点的指针,指针存储的是子节点所在磁盘块的地址,两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域,以根节点为例,关键字为17和35,P1指针指向的子树的数据范围为小于17,P2指针指向的子树的数据范围为17~35,P3指针指向的子树的数据范围为大于35。
模拟查找15的过程:
1.根节点找到磁盘块1,读入内存.【磁盘I/O操作第1次】
比较关键字15在区间[<17],找到磁盘块1的指针P1。2.P1指针找到磁盘块2,读入内存.【磁盘I/O操作第2次】比较关键字15在区间[>12]，找到磁盘块2的指针P3。3.P3指针找到磁盘块7,读入内存,【磁盘I/O操作第3次】
在磁盘块7中找到关键字15.
#分析上面过程，发现需要3次磁盘I/O操作,和3次内存查找操作。
#由于内存中的关键字是一个有序表结构,可以利用二分法查找提高效率,而3次磁盘I/O操作是影响整个BTree查找效率的决定因素,BTree使用较少的节点个数,使每次磁盘I/O取到内存的数据都发挥了作用,从而提高了查询效率。
B+Tree
-
B+Tree是在BTree基础上的一种优化,使其更适合实现外存储索引结构,InnoDB存储引擎就是用B+Tree实现其索引结构。
-
从上一节中的BTree结构图中可以看到每个节点中不仅包含数据的key值,还有data值,而每一个页的存储空间是有限的,如果data数据较大时将会导致每个节点(即一个页)能存储的key的数量很小,当存储的数据量很大时同样会导致B-Tree的深度较大,增大查询时的磁盘I/O次数,进而影响查询效率,在B+Tree中,所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上,而非叶子节点上只存储key值信息,这样可以大大加大每个节点存储的key值数量,降低B+Tree的高度。
-
B+Tree相对于BTree区别:
-
非叶子节点只存储键值信息。
-
所有叶子节点之间都有一个连接指针。
-
数据记录都存放在叶子节点中。
-
-
将上一节中的BTree优化,由于B+Tree的非叶子节点只存储键值信息,假设每个磁盘块能存储4个键值及指针信息,则变成B+Tree后其结构如下图所示：
通常在B+Tree上有两个头指针,一个指向根节点,另一个指向关键字最小的叶子节点,而且所有叶子节点(即数据节点)之间是一种链式环结构,因此可以对B+Tree进行两种查找运算:
-
【有范围】对于主键的范围查找和分页查找.
-
【有顺序】从根节点开始,进行随机查找.
实际情况中每个节点可能不能填充满,因此在数据库中,B+Tree的高度一般都在24层,MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的,也就是说查找某一键值的行记录时最多只需要13次磁盘I/O操作。
数据库索引原理小结
BTree每个节点中不仅包含key值,还有数据值,会增加查询数据时磁盘的IO次数.
B+Tree特点
- 非叶子结点只存储Key值
- 所有数据存储在叶子结点
- 所有的叶子结点都有指针
B+Tree的好处
- 提高查询速度
- 减少磁盘IO次数
- 树形结构较小
数据库最左前缀匹配原则
- 最左匹配原则
在MySQL建立联合索引时会遵循最左前缀匹配的原则,即最左优先,在检索数据时从联合索引的最左边开始匹配.
/**
最左匹配原则,创建表user.
*/
CREATE TABLE IF NOT EXISTS user(
name VARCHAR(10),
address VARCHAR(10),
phone VARCHAR(10)
);
#添加联合索引,对列name列、address和列phone列建一个联合索引. ALTER TABLE user ADD INDEX idx_three(name,address,phone);
#查看user表中的索引
SHOW INDEX FROM user;
联合索引inx_three实际建立了(name)/(name,address)/(name,address,phone)三个索引,所以下面的三个SQL语句都可以命中索引.
SELECT * FROM user WHERE address = '北京' AND phone = '12345' AND name = '张三';
#(name,address,phone)
SELECT * FROM user WHERE name = '张三' AND address = '北京'; #(name,address)
SELECT * FROM user WHERE name = '张三';
#(name)
上面三个查询语句执行时会依照最左前缀匹配原则,检索时分别会使用索引进行数据匹配,索引的字段可以是任意顺序的.
-- 优化器会帮助我们调整顺序,下面的SQL语句都可以命中索引,Mysql的优化器会帮助我们调整where条件中的顺序,以匹配我们建立的索引。
SELECT * FROM user WHERE address = '北京' AND phone = '12345' AND name = '张三';
最左前缀匹配原则,如果条件中没有最左前缀,则无法命中索引.
SELECT * FROM user WHERE address = '北京' AND phone = '12345';
#此条SQL是否可以命中索引?
数据库索引设计原则
-
对查询频次较高,且数据量比较大的表建立索引。
-
使用唯一索引,区分度越高,使用索引的效率越高。
-
索引字段的选择,最佳候选列应当从where子句的条件中提取,如果where子句中的组合比较多,那么应当挑选最常用、过滤效果最好的列的组合。
-
使用短索引,索引创建之后也是使用硬盘来存储的,因此提升索引访问的I/O效率,也可以提升总体的访问效率。假如构成索引的字段总长度比较短,那么在给定大小的存储块内可以存储更多的索引值,相应的可以有效的提升MySQL访问索引的I/O效率。
-
索引可以有效的提升查询数据的效率,但索引数量不是多多益善,索引越多,维护索引的代价自然也就水涨船高。对于插入/更新/删除等DML操作比较频繁的表来说,索引过多,会引入相当高的维护代价,降低DML操作的效率,增加相应操作的时间消耗,另外索引过多的话,MySQL也会犯选择困难病,虽然最终仍然会找到一个可用的索引,但无疑提高了选择的代价。
c.数据库的锁
锁的概念
-
多线程当中如果想保证数据的准确性是如何实现的呢?通过同步实现,同步就相当于是加锁,加了锁以后有什么好处呢?当一个线程真正在操作数据的时候,其他线程只能等待,当一个线程执行完毕后,释放锁,其他线程才能进行操作！那么我们的MySQL数据库中的锁的功能也是类似的,在我们学习事务的时候,讲解过事务的隔离性,可能会出现脏读/不可重复读/幻读的问题,当时我们的解决方式是通过修改事务的隔离级别来控制,但是数据库的隔离级别呢我们并不推荐修改,所以,锁的作用也可以解决掉之前的问题！
-
事务的特性(ACID):
-
事务的隔离性可能产生的问题
-
-
锁机制:数据库为了保证数据的一致性,而使用各种共享的资源在被并发访问时变得有序所设计的一种规则。
锁的分类
-
按操作分类:
-
共享锁:也叫读锁。针对同一份数据,多个事务读取操作可以同时加锁而不互相影响,但是不能修改数据记录。
-
排他锁:也叫写锁。当前的操作没有完成前,会阻断其他操作的读取和写入
-
按粒度分类：
- 表级锁:操作时,会锁定整个表。开销小,加锁快;不会出现死锁;锁定力度大,发生锁冲突概率高,并发度最低。偏向于MyISAM存储引擎！
- 行级锁:操作时,会锁定当前操作行。开销大,加锁慢;会出现死锁;锁定粒度小,发生锁冲突的概率低,并发度高。偏向于InnoDB存储引擎！
- 页级锁:锁的粒度,发生冲突的概率和加锁的开销介于表锁和行锁之间,会出现死锁,并发性能一般。
-
按使用方式分类：
- 悲观锁:每次查询数据时都认为别人会修改,很悲观,所以查询时加锁。
- 乐观锁:每次查询数据时都认为别人不会修改,很乐观,但是更新时会判断一下在此期间别人有没有去更新这个数据.
| 存储引擎 | 表级锁 | 行级锁 | 页级锁 |
|---|---|---|---|
| MyISAM | 支持 | 不支持 | 不支持 |
| InnoDB | 支持 | 支持 | 不支持 |
| MEMORY | 支持 | 不支持 | 不支持 |
| BDB | 支持 | 不支持 | 支持 |
数据库的事务
InnoDB锁
- 数据准备
#创建db3数据库,默认字符集.
CREATE DATABASE IF NOT EXISTS db3;
USE db3;
#创建student表,id/name/age/score.
CREATE TABLE IF NOT EXISTS student(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(10),
age INT,
score INT
)ENGINE = InnoDB;
#添加数据到表中
INSERT INTO student VALUES (NULL,"刘备",30,100);
INSERT INTO student VALUES (NULL,"张飞",31,88);
INSERT INTO student VALUES (NULL,"关羽",32,90);
INSERT INTO student VALUES (NULL,"赵云",33,95);
INSERT INTO student VALUES (NULL,"马超",34,69);
- 共享锁(数据可以被多个事务查询但是不可以修改)
SELECT 语句 LOCK IN SHARE MODE;
#开启事务
START TRANSACTION;
#窗口1查询数据添加共享锁
SELECT * FROM student WHERE id = 1 LOCK IN SHARE MODE;
#窗口1查询数据添加共享锁
SELECT * FROM student WHERE score = 100 LOCK IN SHARE MODE ;
#窗口2修改数据等待中,窗口1提交事务.
COMMIT;
#开启事务
START TRANSACTION;
#窗口2查询id为1的数据并加入共享锁(可以查询。共享锁和共享锁兼容)
SELECT * FROM student WHERE id = 1 LOCK IN SHARE MODE;
#窗口2修改id为1的数据的姓名改为小刘(不能修改,会出现锁的情况,只有窗口1提交事务后,才能修改成功)
UPDATE student SET name = '刘备' WHERE id = 1;
#窗口1虽然已经提交了事务,但是窗口2没有提交事务,所以数据还没有更改,窗口2提交事务了之后,才真正修改了记录.
COMMIT ;
#窗口1开启了事务,给id为1的数据加了锁,是否可以修改其他的数据呢?(修改成功,InnoDB引擎默认是行锁)
UPDATE student SET name = '老张' WHERE id = 2;
#窗口1开启了事务,查询到1条记录并且加了共享锁[刘备],是否可以修改其他的数据呢?(注意:InnoDB引擎如果不采用带索引的列,则会提升为表锁)
UPDATE student SET name = '张飞' WHERE id = 2;
- 排它锁
SELECT 语句 FOR UPDATE;
#窗口1开启事务
START TRANSACTION ;
#窗口1查询id为1的数据并加入排它锁.
SELECT * FROM student WHERE id = 1 FOR UPDATE ;
#窗口1提交事务
COMMIT ;
#窗口2开启事务
START TRANSACTION ;
#窗口2查询id为1的数据并加入共享锁(不能查询,因为排他锁不能和其他锁共存)
SELECT * FROM student WHERE id = 1 LOCK IN SHARE MODE;
#窗口2查询id为1的数据并加入排他锁(不能查询,因为排他锁不能和其他锁共存)
SELECT * FROM student WHERE id = 1 FOR UPDATE ;
#修改id为1的姓名为小刘(不能修改,会出现锁的情况,只有窗口1提交事务后,才能修改成功)
UPDATE student SET name = '小刘' WHERE id = 1;
#窗口2提交事务
COMMIT ;
-
共享锁只与共享锁兼容,与排它锁不兼容.
-
排它锁与一切锁不兼容.
MyISAM锁(不支持事务,所以不需要开启事务,加锁即可)
- 读锁
-- 加锁
LOCK TABLE 表名 READ;
-- 解锁(将当前会话所有的表进行解锁)
UNLOCK TABLES;
-- 窗口1
#MyISAM引擎不支持事务,所以不需要开启事务,加锁即可.
#开启读锁,锁product表,所有连接只能读取数据,不能修改.
LOCK TABLES product READ;
#查询product表的所有数据.
SELECT * FROM product;
#修改华为手机的价格为5999(修改失败)
UPDATE product SET price=5999 WHERE id=1;
#解锁
UNLOCK TABLES ;
-- 窗口2
#修改华为手机的价格为5999(修改失败)
UPDATE product SET price=5999 WHERE id=1;
- 写锁
-- 加锁
LOCK TABLE 表名 READ;
-- 解锁(将当前会话所有的表进行解锁)
UNLOCK TABLES;
-- 窗口1
#MyISAM引擎不支持事务,所以不需要开启事务,加锁即可.
#开启读锁,锁product表,所有连接只能读取数据,不能修改.
LOCK TABLES product READ;
#查询product表的所有数据.
SELECT * FROM product;
#修改华为手机的价格为5999(修改失败)
UPDATE product SET price=5999 WHERE id=1;
#解锁
UNLOCK TABLES ;
#开启写锁,锁product表,其他连接不可以查询数据和修改数据.
LOCK TABLES product WRITE ;
#解锁
UNLOCK TABLES ;
-- 窗口2
#修改华为手机的价格为5999(修改失败)
UPDATE product SET price=5999 WHERE id=1;
#查询id为1的数据(查询失败)
SELECT * FROM product WHERE id = 1;
乐观锁与悲观锁
-
悲观锁概念
-
就是很悲观,它对于数据被外界修改的操作持保守态度,认为数据随时会修改,整个数据处理中需要将数据加锁,悲观锁一般都是依靠关系型数据库提供的锁机制.
-
我们之前所学的行锁,表锁不论是读写锁都是悲观锁。
-
乐观锁概念
-
就是很乐观,每次自己操作数据的时候认为没有人会来修改它,所以不去加锁,但是在更新的时候会去判断在此期间数据有没有被修改。
-
需要用户自己去实现,不会发生并发抢占资源,只有在提交操作的时候检查是否违反数据完整性。
-
乐观锁和悲观锁的使用前提
- 对于读的操作远多于写的操作的时候,这时候一个更新操作加锁会阻塞所有的读取操作,降低了吞吐量,最后还要释放锁,锁是需要一些开销的,这时候可以选择乐观锁。
- 如果是读写比例差距不是非常大或者系统没有响应不及时,吞吐量瓶颈的问题,那就不要去使用乐观锁,它增加了复杂度,也带来了业务额外的风险,这时候可以选择悲观锁。
锁的小结
-
表锁和行锁
-
行锁:锁的粒度更细,加行锁的性能损耗较大,并发处理能力较高,InnoDB引擎默认支持！
-
表锁:锁的粒度较粗,加表锁的性能损耗较小,并发处理能力较低,InnoDB/MyISAM引擎支持！
-
InnoDB锁优化建议
- 尽量通过带索引的列来完成数据查询,从而避免InnoDB无法加行锁而升级为表锁。
- 合理设计索引,索引要尽可能准确,尽可能的缩小锁定范围,避免造成不必要的锁定。
- 尽可能减少基于范围的数据检索过滤条件。
- 尽量控制事务的大小,减少锁定的资源量和锁定时间长度。
- 在同一个事务中,尽可能做到一次锁定所需要的所有资源,减少死锁产生概率。
- 对于非常容易产生死锁的业务部分,可以尝试使用升级锁定颗粒度,通过表级锁定来减少死锁的产生。
d.数据库表结构设计(http://open.yesapi.cn/list.html)**
数据库表名的设计
-
命名表的时候用名称单数形式
- student(√) students(×)
-
SQL语句大写不代表表名/列名全部大写,虽然MySQL在Winodows下不区分大小写,但是Linux下是区分大小写的,所以数据库名/表名/字段不要出现大写字母.
-
相关联的表采用相同的前缀,这样方便日后做区分.
- oa_user oa_log(OA系统相关的表)
-
如果表名由几个单词组成,使用下划线进行分割.
数据库字段设计
- 字段名为小写,如果字段由多个单词组成,用下划线进行区分_.
- 禁止使用保留字,例如:desc,range,match等.
- 表达是与否的概念,需要使用is_xxx的方式命名,使用tinyint unsigned数据类型.
- 1表示是,0表示否.
数据类型选择
- 小数类型使用decimal,使用float和double会出现精度损失问题.
- 如果存储的字符串长度几乎相等,使用char定长字符串类型.
e.数据库的分库分表策略
分库分表概念
- 将庞大的数据量拆分为不同的数据库和数据表进行存储.
分库分表的分类
- 水平拆分
根据表的数据逻辑关系,将同一表中的数据按照某种条件,拆分到多台数据库服务器上,也叫做横向拆分。
例如:一张1000万的大表,按照一模一样的结构,拆分成4个250万的小表,分别保存到个数据库中.
水平拆分分为两种库内分表和分库分表.
根据表内数据内在的逻辑关系,将同一个表按不同的条件分散到多个数据库或多表中,每个表中只包含一部分数据,从而使得单个表的数据量变小,达到分布式的效果。
水平拆分优点:
- 不存在单库数据量过大,高并发的性能瓶颈,提升系统稳定性和负载能力.
- 不需要拆分业务模块.
水平拆分缺点:
- 跨分片事务一致性难以得到保证.
- 数据维护量大.
- 垂直拆分
根据业务的维度,将不同的表切分到不同的数据库之上,也叫做纵向拆分。
例如:所有的订单信息都保存到订单库中,所有的用户信息都保存到用户库中,同类型的表保存在同一个库中。
纵向拆分也分为两种,分别是纵向分库和纵向分表.
- 纵向分库就是根据业务的耦合性,将关联度低的不同表存储在不同的数据库,做法与大系统拆分为多个小系统类似,按业务分类进行独立划分。与"微服务治理"的做法相似,每个微服务使用单独的一个数据库.
- 垂直分表就是说如果某个表中字段较多,可以新建一张拓展表,将不常用的字段或者字段长度较大的字段拆出来到拓展表中,通过大表拆小表,更便于开发维护.
垂直拆分优点:
- 业务清晰,类似于微服务,每个表各司其职.
- 高并发场景下,垂直切分一定程度提升IO,提高数据库连接.
垂直拆分缺点:
- 提升了开发复杂度,分布式事务处理复杂.
分
根据表的数据逻辑关系,将同一表中的数据按照某种条件,拆分到多台数据库服务器上,也叫做横向拆分。
例如:一张1000万的大表,按照一模一样的结构,拆分成4个250万的小表,分别保存到个数据库中.
水平拆分分为两种库内分表和分库分表.
根据表内数据内在的逻辑关系,将同一个表按不同的条件分散到多个数据库或多表中,每个表中只包含一部分数据,从而使得单个表的数据量变小,达到分布式的效果。
水平拆分优点:
- 不存在单库数据量过大,高并发的性能瓶颈,提升系统稳定性和负载能力.
- 不需要拆分业务模块.
水平拆分缺点:
- 跨分片事务一致性难以得到保证.
- 数据维护量大.
- 垂直拆分
根据业务的维度,将不同的表切分到不同的数据库之上,也叫做纵向拆分。
例如:所有的订单信息都保存到订单库中,所有的用户信息都保存到用户库中,同类型的表保存在同一个库中。
纵向拆分也分为两种,分别是纵向分库和纵向分表.
- 纵向分库就是根据业务的耦合性,将关联度低的不同表存储在不同的数据库,做法与大系统拆分为多个小系统类似,按业务分类进行独立划分。与"微服务治理"的做法相似,每个微服务使用单独的一个数据库.
- 垂直分表就是说如果某个表中字段较多,可以新建一张拓展表,将不常用的字段或者字段长度较大的字段拆出来到拓展表中,通过大表拆小表,更便于开发维护.
垂直拆分优点:
- 业务清晰,类似于微服务,每个表各司其职.
- 高并发场景下,垂直切分一定程度提升IO,提高数据库连接.
垂直拆分缺点:
- 提升了开发复杂度,分布式事务处理复杂.
- 表虽然进行拆分了,但是单表数据依旧过大.
扫一扫
190
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
