MySQL架构设计

最新推荐文章于 2024-08-12 21:40:46 发布

ReycoChou

最新推荐文章于 2024-08-12 21:40:46 发布

阅读量347

点赞数 1

分类专栏： MySQL

本文链接：https://blog.csdn.net/weixin_40288381/article/details/96387493

版权

MySQL 专栏收录该内容

16 篇文章 1 订阅

订阅专栏

MySQL架构设计

大表和大事务

大表带来的问题

记录行数巨大，单表超过千万行
表数据文件巨大，表数据文件超过10G

1.大表对查询的影响，很难在一定的时间内过滤出所需要的数据
2.建立索引需要很长时间，MySQL版本<5.5时，建立索引会锁表；MySQL版本>=5.5，建立索引时虽然不会锁表但会引起主从延迟
3.修改表结构需要长时间锁表，造成长时间的主从延迟

如何处理数据库中的大表

分库分表把一张大表分成多个小表(影响前后端业务)
难点：
- 分表主键的选择
- 分表后跨分区数据的查询和统计
大表的历史数据归档(减少对前后端业务的影响)
难点：
- 归档时间点的选择
- 如何进行归档操作

大事务带来的功能

运行时间比较长，操作的数据比较多的事务，锁定太多数据，造成大量的阻塞和锁超时；回滚时所需的时间比较长；执行时间长，容易造成主从延迟

如何处理大事务

避免一次处理太多数据
移出不必要在事务中的SELECT操作(事务中基本是写操作)

MySQL体系结构

MySQL最特别之处在于插件式存储引擎
存储引擎是针对于表的，而不是针对于库的，不同表可以有不同的存储引擎

在这里插入图片描述

存储引擎之MyISAM

MyISAM是MySQL5.5之前版本默认存储引擎
MyISAM仍然用于MySQL中的系统表和临时表等
临时表:在排序、分组等操作中，当数量超过一定的大小之后，由查询优化器建立的临时表

特性

MyISAM使用的是表级锁，对于读写混合的并发性支持不够
由于MyISAM不支持事务
MyISAM表支持数据压缩-myisampack(压缩之后的表只能进行读操作)

存储引擎之Innodb

Innodb是MySQL5.5之后版本默认存储引擎
Innodb使用表空间进行数据存储

特性

Innodb是一种事务性存储引擎
完全支持事务的ACID特性
Redo Log(支持事务持久性) 和Undo Log(支持事务回滚)
支持行级锁(行级锁是由存储引擎层实现的)

MySQL中锁的类型

Innodb中读锁和写锁都是行锁

共享锁(读锁)-乐观锁
独占锁(写锁)-悲观锁

	写锁	读锁
写锁	不兼容	不兼容
读锁	不兼容	兼容

MySQL中锁的粒度

锁的粒度实际上就是锁的最小单位

表级锁 lock table 表名；/unlock table 表名
行级锁

MySQL基准测试

基准测试可以简单认为是针对系统设置的一种压力测试

压力测试需要针对不同主题，所使用的数据和查询也是真实用到的
基准测试不关心业务逻辑，所使用的查询和业务的真实性可以和业务环境没关系

基准测试工具-mysqlslap

常用参数说明

–auto-generate-sql 由系统自动生成SQL脚本进行测试
–auto-generate-sql-add-autoincrement 在生成的表中增加自增ID
–auto-generate-sql-load-type 指定测试中使用的查询类型
–auto-generate-sql-write-number 指定初始化数据时生成的数据量
–concurrency 指定并发线程的数量
–engine 指定要测试表的存储引擎，可以用逗号分隔多个存储引擎
–no-drop 指定不清理测试数据
–iterations 指定测试运行的次数
–number-of-queries 指定每一个线程执行的查询数量
–debug-info 输出CPU和内存信息
–number-int-cols 指定测试表中包含INT类型列的数量
–number-char-cols 指定测试表中包含varchar类型列的数量
–create-schema 指定了用于执行测试的数据库的名字
–query 指定自定义SQL的脚本
–only-print 并不运行测试脚本，而是把生成的脚本打印出来
–help 查询可用命令

数据库结构优化

减少数据冗余，数据冗余就是相同的数据在多处存在
尽量避免数据维护中出现更新、插入和删除异常
- 插入异常：如果表中的某个实体随着另一个实体而存在
- 更新异常：如果更改表中的某个实体的单独属性时，需要对多行进行更新
- 删除异常：如果删除表中的某一实体则会导致其他实体的消失

物理设计

数据类型的选择

当一个列可以选择多种数据类型·时，应该优先考虑数字类型，其次是日期或二进制类型，最后是字符类型。对于相同级别的数据类型，应该优先选择占用空间小的数据类型

如何正确选择整数类型

在这里插入图片描述

如何正确选择实数类型

DECIMAL用法–DECIMAL(10,2)，共10位长度，其中保留两位小数位

如何选择VARCHAR和CHAR类型

VARCHAR和CHAR中宽度是以字符为单位

VARCHAR类型的存储特点

VARCHAR用于存储变长字符串，只占用必要的存储空间
列的最大长度小于255则只占用一个额外字节用于记录字符串长度
列的最大长度大于255则要占用两个额外字节用于记录字符串长度

CHAR类型的存储特点

CHAR类型是定长的
字符串存储在CHAR类型的列中会删除末尾的空格
CHAR类型的最大长度为255

如何正确选择日期类型

DATATIME

DATATIME = YYYY-MM-DD HH:MM:SS
DATATIME类型与时区无关，占用8个字节的存储空间
时间范围1000-01-01 00:00:00到9999-12-31 23:59:59

TIMESTAMP(时间戳)

存储了由格林尼治时间1970年1月1日到当前时间的秒数
TIMESTAMP = YYYY-MM-DD HH:MM:SS
TIMESTAMP占用4个字节
时间范围1970-01-01到2038奶奶-01-19

TIMESTAMP类型显示依赖于所指定的时区
在行的数据修改时可以自动更新timestamp列的值

date类型和time类型

date类型用于存储日期
DATA = YYYY - MM -DD
存储用户生日时，只需存储日期类型

time类型用于存储时间数据，格式为HH:MM:SS

存储日期时间数据的注意事项

不要使用字符串类型来存储日期时间数据
- 日期时间通常比字符串占用的存储空间小
- 日期时间类型在查找过滤时可以利用日期来进行对比
- 日期时间类型有着丰富的处理函数，可以方便地对时期类型进行日期计算

数据库的分库分表

把一个实例的多个数据库拆分到不同的实例
把一个库中的表分离到不同的数据库中
库中的相关表进行水平分片

数据库分片

如何选择分区键

分区键要尽量跨分片查询的发生
分区键要能尽量使各个分片中的数据平均

如何存储无需分片的表

每个分片中存储一份相同的数据，进行数据冗余

如何在节点上部署分片

每个分片使用单一数据库，并且数据库名也相同
将多个分片表存储在一个数据库中，并在表名上加上分片号后缀
在一个节点中部署多个数据库，每个数据库包含一个分片

如何分配分片中的数据

按分区键的Hash值取模来分配分片数据
按分区键的范围来分配分片数据
利用分区键和分片的映射表来分配分片数据

如何生成全局唯一ID

使用auto_increment_increment和auto_increment_offest参数2(offest设置为分片节点的数量，比如6个分片则设置offest为6，而auto_increment_increment设置为0-6的值，这样唯一ID就不会冲突)
使用全局节点来生成ID
在Redis等缓存服务器中创建全局ID

主键用数字还是UUID？

UUID 是通用唯一识别码的缩写，其目的是让分布式系统中的所有元素，都能有唯一的辨识信息，而不需要通过中央控制端来做辨识信息的指定。在数据库集群中，为了避免每个MySQL各自生成的主键产生重复，所以有人考虑采用UUID方式。
MySQL中有UUID()函数，可以生成UUID()

用UUID的好处

使用UUID，分布式生成主键，降低了全局节点的压力，使得主键生成速度更快
使用UUID生成的主键值全局唯一
跨服务器合并数据很方便

UUID主键的缺点

UUID占用16个字节，比4字节的INT类型和8字节的BIGINT类型更加占用存储空间
UUID是字符串类型，查询速度很慢
UUID不是顺序增长，作为主键，数据写入IO随机性很大

主键自动增长的优点

INT和BIGINT类型占用存储空间较小
MySQL检索数字类型速度远快过字符串
主键值是自动增长的，所以IO写入连续性较好

无论什么场合，都不推荐使用UUID作为数据表的主键，而是要利用数据库中间件来生成全局主键

逻辑删除还是物理删除？

物理删除就是用DELETE、TRUNCATE、DROP语句删除数据。物理删除是把数据从硬盘中删除，可以释放存储空间，缩小数据表的体积，对性能提升有帮助
物理删除会造成主键的不连续，导致分页查询变慢
核心业务表的数据不建议物理删除，只做状态变更。

我们如何实现既不删除数据，又能缩小数据表体积呢，可以把记录转移到历史表
逻辑删除就是在数据表添加一个字段valid，用字段值标记该数据已经逻辑删除，查询的时候跳过这些数据。同时将逻辑删除的记录转移到历史表

//克隆表
CREATE TABLE t_user_history LIKE t_user;

SELECT ……  FROM ……  LIMIT 1000, 20;

SELECT ……  FROM ……  WHERE  id>=1000  AND  id<= 1020;

核心业务表的数据不建议做物理删除，只做状态变更。比如订单作废、账号禁用、优惠券作废等等。既不删除数据，又能缩小数据表体积，可以把记录转移到历史表。

逻辑删除就是在数据表添加一个字段（is_deleted），用字段值标记该数据已经逻辑删除，查询的时候跳过这些数

海量记录快速分页

SELECT * FROM t_test WHERE id>=5000000 LIMIT 100,10;//耗时0.09秒
SELECT * FROM t_test WHERE id>=5000000 LIMIT 10000,10;//耗时0.387秒
SELECT * FROM t_test WHERE id>=5000000 LIMIT 50000000,10;//耗时1.839秒

利用主键索引来加速分页查询

SELECT * FROM t_test WHERE id>=5000000 LIMIT 100;
SELECT * FROM t_test WHERE id>=5000000 AND id<=5000000+100;

使用逻辑删除，不会造成主键不连续
如果用物理删除，主键不连续，就不能用主键索引来加速分页，所以只能使用折中的方案

SELECT t.id, t.name FROM t_test t JOIN ( SELECT id FROM t_test LIMIT 5000000, 100) tmp ON t.id = tmp.id;

数据库缓存（查询缓存）、程序缓存应该选择哪个？

MySQL的查询缓存结构的是KV的，数据库会把执行过的SELECT结果集缓存到内存里面，KEY是SQL语句，VALUE是结果集。
在这里插入图片描述
数据库缓存注意事项：

所有对数据加锁的事务中(包含UPDATE,DELETE等…)，不会使用查询缓存
查询语句必须一模一样，才有机会命中缓存

查询缓存的缺点在于缓存的颗粒度不够细
查询缓存最大的缺点就是，只要用户对数据表修改一条记录，就会让这个数据表的缓存大面积的失效，这就造成的IO压力突然增大，所以最好的办法就是不使用查询缓存，而改用数据缓存，数据缓存是把InnoDB数据表和索引中的一部分记录缓存到内存中。用户更新数据的时候，更新了数据表多少条记录，响应个缓存就更改多少条，并不会出现缓存大面积失效的情况。

数据库的查询缓存因为不可以细颗粒度的设置哪张数据表结果集被缓存，但是程序查询缓存可以详细设置哪一条的SQL的结果集被缓存。所以我们可以避开内容经常变化的数据表，对哪些数据不经常变化的数据表设置查询缓存。

SpringCache技术是Java领域里面比较成熟的缓存方案，使用注解就能管理缓存。结合Redis，可以充分发挥查询缓存的优势。

中文分词技术

MySQL的全文检索功能，既支持英文也支持中文。
MySQL全文检索对英文支持很好，但是对中文支持很不好。不能按照语义切词，只能按照字符切词

MySQL全文索引

通过数值比较，范围过滤就可以完成绝大多数我们需要的查询，但是，如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的
原本like + %就可以实现模糊匹配了，为什么还要全文索引呢？ike + % 在文本比较少时是合适的，但是对于大量的文本数据检索，是不可想象的。全文索引在大量的数据面前，能比 like + % 快 N 倍，速度不是一个数量级，但是全文索引可能存在精度问题

版本支持

MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引

MySQL中的全文索引，有两个变量，最小搜索长度和最大搜索长度。对于长度小于最小搜索长度和大于最大搜索长度的词语，都不会被索引。通俗点就是说，想对一个词语使用全文索引搜索，那么这个词语的长度必须在以上两个变量的区间内。
这两个的默认值可以使用以下命令查看

show variables like '%ft%';

// MyISAM
ft_min_word_len = 4;
ft_max_word_len = 84;
// InnoDB
innodb_ft_min_token_size = 3;
innodb_ft_max_token_size = 84;

配置最小搜索长度

全文索引的相关参数都无法进行动态修改，必须通过修改 MySQL 的配置文件来完成。修改最小搜索长度的值为 1，首先打开 MySQL 的配置文件 /etc/my.cnf，在 [mysqld] 的下面追加以下内容

[mysqld]
innodb_ft_min_token_size = 1
ft_min_word_len = 1

然后重启 MySQL 服务器，并修复全文索引。注意，修改完参数以后，一定要修复下索引，不然参数不会生效
两种修复方式，可以使用下面的命令修复

repair table test quick;

查询权重

全文索引除了需要注意搜索长度之外，还有另外一个问题值得注意。明明很多条数据都包含这个词，但返回的结果却是空的，原来MySQL还会计算一个词的权重，以决定是否出现在结果集中，具体如下：MySQL在集和查询中对每个合适的词都会先计算它们的权重，一个出现在多个文档中的词将有较低的权重(甚至可能有零权重)，因为在这个特定的集中，它有较低的语义值。反之，如果词是较少的，它将得到一个较高的权重。(MySQL默认的阈值是50%，只有低于50%的才会出现在结果集中)

全文索引的弊病

中文字段创建全文索引，切词结果太多，占用大量存储空间
更新字段内容，全文索引不会更新，必须定期手动维护
在数据库集群中维护全文索引难度很大

使用专业的全文检索引擎

Lucene是Apache基金会的开源全文检索引擎，支持中文分词

Lucene的使用

引入Lucene依赖
Lucene自带的中文分词插件功能较弱，需要引入第三方中文分词插件，对中文内容准确分词(http://hanlp.com/)–引入依赖

	public static void main(String[] args) {
		try {
			Directory directory=FSDirectory.open(new File("D:/index").toPath());
			Analyzer analyzer=new HanLPAnalyzer();
			IndexWriterConfig config=new IndexWriterConfig(analyzer);
			IndexWriter writer=new IndexWriter(directory, config);
			DriverManager.registerDriver(new Driver());
			String url="jdbc:mysql://localhost:3306/neti?serverTimezone=GMT%2B8";
			String username="root";
			String password="abc123456";
			Connection con=DriverManager.getConnection(url,username,password);
			String sql="SELECT id,title FROM t_sku";
			PreparedStatement pst=con.prepareStatement(sql);
			ResultSet set=pst.executeQuery();
			while(set.next()) {
				String id=set.getString("id");
				String title=set.getString("title");
				Document document=new Document();
				document.add(new TextField("id", id, Field.Store.YES));
				document.add(new TextField("title", title, Field.Store.YES));
				writer.addDocument(document);
			}
			con.close();
			writer.close();
		}
		catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}


```java
public class Demo2 {
	public static void main(String[] args) {
		try {
			Directory directory=FSDirectory.open(Paths.get("D:/index"));
			IndexReader reader=DirectoryReader.open(directory);
			IndexSearcher searcher=new IndexSearcher(reader);
			String text="拍手机会";
			String field="title";
			Analyzer analyzer=new HanLPAnalyzer();
			QueryParser parser=new QueryParser(field, analyzer);
			Query query=parser.parse(text);
			TopDocs docs=searcher.search(query, 100);
			searcher.
			System.out.println("命中的记录数："+docs.totalHits);
			ScoreDoc[] array=docs.scoreDocs;
			for (ScoreDoc one : array) {
				Document document=searcher.doc(one.doc);
				String id=document.get("id");
				String title=document.get("title");
				System.out.println("id - >"+id);
				System.out.println("title - >"+title);
			}
			reader.close();
		}
		catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

Lucene注意事项

不是所有数据表的记录，都要保存到Lucene上面。只对需要全文检索的字段使用Lucene即可

Lucene与MySQL的结合

在这里插入图片描述

要不要使用子查询？

MySQL数据库默认关闭了缓存，所以每个子查询都是相关子查询。
相关子查询就是要循环执行多次的子查询，每次筛选数据时都要执行子查询
在这里插入图片描述
因为MyBatis等持久层框架开启了缓存功能，其中一级缓存就会保存子查询的结果，所以可以放心使用子查询
结论：SQL控制台不要使用子查询，在持久层框架中则可以使用

如何替代子查询？

使用FROM子查询，替代WHERE子查询
[外链图片转存失败(img-Iuieu8Sw-1563378475542)(./images/1562679858097.png)]
FROM子句用来确定数据来源，在SQL语句中最先被执行且只被执行一次

外连接的JOIN条件

内连接里，查询条件写在ON子句或者WHERE子句，效果相同
外连接里，查询条件写在ON子句或者WHERE子句，效果不同

表连接修改

UPDATE语句中的WHERE子查询如何改成表连接？
在这里插入图片描述

MySQL参数优化

修改/etc/my.cnf

最大连接数
- max_connections是MySQL最大并发连接数，默认值151
- MySQL允许的最大连接数上限是16384
- 实际连接数是最大连接数的85%较为合适

show variables like 'max_connections'; # 最大并发连接上限
show status like 'max_used_connections'; # 当前被使用的连接数

MySQL会为每个连接创建缓冲区，所以不应该盲目上调最大连接数

(类似于任务队列)

请求堆栈的大小
- back_log是存放执行请求的堆栈大小，默认值是50
- 一般堆栈大小设置成最大连接数的1/3
修改并发线程数
- innodb_thread_concurrency代表并发线程数，默认是0
- 并发线程数应该设置为CPU核心数的两倍
修改连接超时时间
- wait-timeout是超时时间，单位是秒
- 连接默认超时为8小时，连接长期不用又不销毁，浪费资源
数据缓存(不是查询缓存)

InnoDB缓存中保存数据表数据和索引数据

innodb_buffer_pool_size是InnoDB的缓存容量，默认是128M
InnoDB缓存的大小可以设置为主机内存的70%~80%

ReycoChou

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录