MySQL:表结构设计、char 和 varchar、索引数据结构、事务隔离级别、分表设计

本文主要是介绍MySQL:表结构设计、char 和 varchar、索引数据结构、事务隔离级别、分表设计,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

设计MySQL的表结构要考虑什么问题?

  • 主键设计要合理:主键设计的话,最好不要与业务逻辑有所关联。有些业务上的字段,比如身份证,虽然是唯一的,一些开发者喜欢用它来做主键,但是不是很建议哈。主键最好是毫无意义的一串独立不重复的数字,比如UUID,又或者Auto_increment自增的主键,或者是雪花算法生成的主键等等;

  • 优先考虑逻辑删除,而不是物理删除:给数据添加一个字段,比如is_deleted,以标记该数据已经逻辑删除,最好不要进行物理删除,因为恢复数据很困难,而且物理删除会使自增主键不再连续

  • 一张表的字段不宜过多:张表的字段不宜过多哈,一般尽量不要超过 20 个字段,果一张表的字段过多,表中保存的数据可能就会很大,查询效率就会很低。当表的字段数非常多时,可以将表分成两张表,一张作为条件查询表,一张作为详细内容表。

  • 尽可能使用 not null 定义字段:首先,NOT NULL 可以防止出现空指针问题。其次,NULL值存储也需要额外的空间的,它也会导致比较运算更为复杂,使优化器难以优化 SQL。NULL值有可能会导致索引失效

  • 设计表时,评估哪些字段需要加索引:区分度不高的字段,不能加索引,如性别等。索引也不要建得太多,一般单表索引个数不要超过5个。因为创建过多的索引,会降低写得速度。索引创建完后,还是要注意避免索引失效的情况,如使用 mysql 的内置函数,会导致索引失效的。索引过多的话,可以通过联合索引的话方式来优化。然后的话,索引还有一些规则,如覆盖索引,最左匹配原则等等。

MySQL的char和varchar 有什么区别?

  • CHAR是固定长度的字符串类型,定义时需要指定固定长度,存储时会在末尾补足空格。 CHAR适合存储长度固定的数据,如固定长度的代码、状态等,存储空间固定,对于短字符串效率较高。
  • VARCHAR是可变长度的字符串类型,定义时需要指定最大长度,实际存储时根据实际长度占用存储空间。 VARCHAR适合存储长度可变的数据,如用户输入的文本、备注等,节约存储空间。

索引的底层数据结构有哪些实现方式?了解hash索引吗?

MySQL 常见索引有 B+Tree 索引、HASH 索引、Full-Text 索引。

  • B+Tree 索引:是 MySQL 默认存储引擎 InnoDB 采用索引数据结构,所有数据都存储在叶子节点中,非叶子节点只存储索引,提高范围查询的性能和减少磁盘IO开销,千万级的数据量 b+树的树高只需要 3 层。
  • Full-Text 索引:全文索引用于对文本内容进行搜索,采用倒排索引等数据结构来实现全文搜索功能,支持关键字搜索和模糊查询。
  • 哈希索引:哈希索引通过哈希函数计算键的存储位置,适用于等值查找,速度快但不支持范围查找。

为什么用B+树呢?

  • B+Tree vs 二叉树:对于有 N 个叶子节点的 B+Tree,其搜索复杂度为O(logdN),其中 d 表示节点允许的最大子节点个数为 d 个。在实际的应用当中, d 值是大于100的,这样就保证了,即使数据达到千万级别时,B+Tree 的高度依然维持在 3~ 4 层左右,也就是说一次数据查询操作只需要做 3~4 次的磁盘 I/O 操作就能查询到目标数据。而二叉树的每个父节点的儿子节点个数只能是 2 个,意味着其搜索复杂度为 O(logN),这已经比 B+Tree 高出不少,因此二叉树检索到目标数据所经历的磁盘 I/O 次数要更多。
  • B+Tree vs Hash:Hash 在做等值查询的时候效率贼快,搜索复杂度为 O(1)。但是 Hash 表不适合做范围查询,它更适合做等值的查询,这也是 B+Tree 索引要比 Hash 表索引有着更广泛的适用场景的原因。
  • B+Tree vs B Tree:B+Tree 只在叶子节点存储数据,而 B 树 的非叶子节点也要存储数据,所以 B+Tree 的单个节点的数据量更小,在相同的磁盘 I/O 次数下,就能查询更多的节点。另外,B+Tree 叶子节点采用的是双链表连接,适合 MySQL 中常见的基于范围的顺序查找,而 B 树无法做到这一点。

事务的隔离级别有哪些?

四个隔离级别如下:

  • 读未提交,指一个事务还没提交时,它做的变更就能被其他事务看到;
  • 读提交,指一个事务提交之后,它做的变更才能被其他事务看到;
  • 可重复读,指一个事务执行过程中看到的数据,一直跟这个事务启动时看到的数据是一致的,MySQL InnoDB 引擎的默认隔离级别;
  • 串行化;会对记录加上读写锁,在多个事务对这条记录进行读写操作时,如果发生了读写冲突的时候,后访问的事务必须等前一个事务执行完成,才能继续执行;

按隔离水平高低排序如下:

在这里插入图片描述

针对不同的隔离级别,并发事务时可能发生的现象也会不同。

在这里插入图片描述

当前读和快照读有什么区别?

  • 当前读可以读取其他事务最新已经提交的事务,执行的过程中会通过加行级锁的方式保证事务的隔离性,比如 select for update、update、 delete 都属于当前读
  • 快照读是无锁的, 主要是基于mvcc机制实现的,可重复读和读已提交的 select 都属于快照读。
    • 可重复读」隔离级别是启动事务时生成一个 Read View,然后整个事务期间都在用这个 Read View,这样就保证了在事务期间读到的数据都是事务启动前的记录。
    • 读提交」隔离级别是在每个 select 都会生成一个新的 Read View,也意味着,事务期间的多次读取同一条数据,前后两次读的数据可能会出现不一致,因为可能这期间另外一个事务修改了该记录,并提交了事务。

MySQL分表怎么设计?

一般可以采用以下几种常见的分表策略:

  • 按时间分表:根据数据的时间特征,按照时间范围(如年、月、日)将数据分散存储到不同的表中,便于数据归档和查询。

  • 按业务分表:根据业务需求将数据按照业务逻辑进行分表,可以根据不同的业务属性将数据分散到不同的表中,实现逻辑上的分离。

  • 按哈希分表:通过对数据的哈希计算,将数据均匀分布到多个表中,避免单表数据量过大导致性能问题。

  • 按范围分表:根据数据的某个范围属性(如用户ID、地区ID等)将数据分散到不同的表中,便于查询和管理特定范围的数据。

  • 按数据量分表:当单表数据量过大时,可以按照一定的规则将数据拆分到多个表中,避免单表数据量过大导致性能下降。

这篇关于MySQL:表结构设计、char 和 varchar、索引数据结构、事务隔离级别、分表设计的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/809389

相关文章

SpringKafka消息发布之KafkaTemplate与事务支持功能

《SpringKafka消息发布之KafkaTemplate与事务支持功能》通过本文介绍的基本用法、序列化选项、事务支持、错误处理和性能优化技术,开发者可以构建高效可靠的Kafka消息发布系统,事务支... 目录引言一、KafkaTemplate基础二、消息序列化三、事务支持机制四、错误处理与重试五、性能优

Spring事务中@Transactional注解不生效的原因分析与解决

《Spring事务中@Transactional注解不生效的原因分析与解决》在Spring框架中,@Transactional注解是管理数据库事务的核心方式,本文将深入分析事务自调用的底层原理,解释为... 目录1. 引言2. 事务自调用问题重现2.1 示例代码2.2 问题现象3. 为什么事务自调用会失效3

mysql出现ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (10061)的解决方法

《mysql出现ERROR2003(HY000):Can‘tconnecttoMySQLserveron‘localhost‘(10061)的解决方法》本文主要介绍了mysql出现... 目录前言:第一步:第二步:第三步:总结:前言:当你想通过命令窗口想打开mysql时候发现提http://www.cpp

MySQL大表数据的分区与分库分表的实现

《MySQL大表数据的分区与分库分表的实现》数据库的分区和分库分表是两种常用的技术方案,本文主要介绍了MySQL大表数据的分区与分库分表的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有... 目录1. mysql大表数据的分区1.1 什么是分区?1.2 分区的类型1.3 分区的优点1.4 分

MySQL错误代码2058和2059的解决办法

《MySQL错误代码2058和2059的解决办法》:本文主要介绍MySQL错误代码2058和2059的解决办法,2058和2059的错误码核心都是你用的客户端工具和mysql版本的密码插件不匹配,... 目录1. 前置理解2.报错现象3.解决办法(敲重点!!!)1. php前置理解2058和2059的错误

Mysql删除几亿条数据表中的部分数据的方法实现

《Mysql删除几亿条数据表中的部分数据的方法实现》在MySQL中删除一个大表中的数据时,需要特别注意操作的性能和对系统的影响,本文主要介绍了Mysql删除几亿条数据表中的部分数据的方法实现,具有一定... 目录1、需求2、方案1. 使用 DELETE 语句分批删除2. 使用 INPLACE ALTER T

MySQL INSERT语句实现当记录不存在时插入的几种方法

《MySQLINSERT语句实现当记录不存在时插入的几种方法》MySQL的INSERT语句是用于向数据库表中插入新记录的关键命令,下面:本文主要介绍MySQLINSERT语句实现当记录不存在时... 目录使用 INSERT IGNORE使用 ON DUPLICATE KEY UPDATE使用 REPLACE

MySQL Workbench 安装教程(保姆级)

《MySQLWorkbench安装教程(保姆级)》MySQLWorkbench是一款强大的数据库设计和管理工具,本文主要介绍了MySQLWorkbench安装教程,文中通过图文介绍的非常详细,对大... 目录前言:详细步骤:一、检查安装的数据库版本二、在官网下载对应的mysql Workbench版本,要是

mysql数据库重置表主键id的实现

《mysql数据库重置表主键id的实现》在我们的开发过程中,难免在做测试的时候会生成一些杂乱无章的SQL主键数据,本文主要介绍了mysql数据库重置表主键id的实现,具有一定的参考价值,感兴趣的可以了... 目录关键语法演示案例在我们的开发过程中,难免在做测试的时候会生成一些杂乱无章的SQL主键数据,当我们

浅谈mysql的sql_mode可能会限制你的查询

《浅谈mysql的sql_mode可能会限制你的查询》本文主要介绍了浅谈mysql的sql_mode可能会限制你的查询,这个问题主要说明的是,我们写的sql查询语句违背了聚合函数groupby的规则... 目录场景:问题描述原因分析:解决方案:第一种:修改后,只有当前生效,若是mysql服务重启,就会失效;