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

相关文章

MySQL中的交叉连接、自然连接和内连接查询详解

《MySQL中的交叉连接、自然连接和内连接查询详解》:本文主要介绍MySQL中的交叉连接、自然连接和内连接查询,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录一、引入二、交php叉连接(cross join)三、自然连接(naturalandroid join)四

Mysql如何将数据按照年月分组的统计

《Mysql如何将数据按照年月分组的统计》:本文主要介绍Mysql如何将数据按照年月分组的统计方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql将数据按照年月分组的统计要的效果方案总结Mysql将数据按照年月分组的统计要的效果方案① 使用 DA

Mysql表如何按照日期字段的年月分区

《Mysql表如何按照日期字段的年月分区》:本文主要介绍Mysql表如何按照日期字段的年月分区的实现方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录一、创键表时直接设置分区二、已有表分区1、分区的前置条件2、分区操作三、验证四、注意总结一、创键表时直接设置分区

mysql的基础语句和外键查询及其语句详解(推荐)

《mysql的基础语句和外键查询及其语句详解(推荐)》:本文主要介绍mysql的基础语句和外键查询及其语句详解(推荐),本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋... 目录一、mysql 基础语句1. 数据库操作 创建数据库2. 表操作 创建表3. CRUD 操作二、外键

MySQL更新某个字段拼接固定字符串的实现

《MySQL更新某个字段拼接固定字符串的实现》在MySQL中,我们经常需要对数据库中的某个字段进行更新操作,本文就来介绍一下MySQL更新某个字段拼接固定字符串的实现,感兴趣的可以了解一下... 目录1. 查看字段当前值2. 更新字段拼接固定字符串3. 验证更新结果mysql更新某个字段拼接固定字符串 -

Python获取C++中返回的char*字段的两种思路

《Python获取C++中返回的char*字段的两种思路》有时候需要获取C++函数中返回来的不定长的char*字符串,本文小编为大家找到了两种解决问题的思路,感兴趣的小伙伴可以跟随小编一起学习一下... 有时候需要获取C++函数中返回来的不定长的char*字符串,目前我找到两种解决问题的思路,具体实现如下:

python连接本地SQL server详细图文教程

《python连接本地SQLserver详细图文教程》在数据分析领域,经常需要从数据库中获取数据进行分析和处理,下面:本文主要介绍python连接本地SQLserver的相关资料,文中通过代码... 目录一.设置本地账号1.新建用户2.开启双重验证3,开启TCP/IP本地服务二js.python连接实例1.

Spring Boot项目中结合MyBatis实现MySQL的自动主从切换功能

《SpringBoot项目中结合MyBatis实现MySQL的自动主从切换功能》:本文主要介绍SpringBoot项目中结合MyBatis实现MySQL的自动主从切换功能,本文分步骤给大家介绍的... 目录原理解析1. mysql主从复制(Master-Slave Replication)2. 读写分离3.

Ubuntu中远程连接Mysql数据库的详细图文教程

《Ubuntu中远程连接Mysql数据库的详细图文教程》Ubuntu是一个以桌面应用为主的Linux发行版操作系统,这篇文章主要为大家详细介绍了Ubuntu中远程连接Mysql数据库的详细图文教程,有... 目录1、版本2、检查有没有mysql2.1 查询是否安装了Mysql包2.2 查看Mysql版本2.

基于SpringBoot+Mybatis实现Mysql分表

《基于SpringBoot+Mybatis实现Mysql分表》这篇文章主要为大家详细介绍了基于SpringBoot+Mybatis实现Mysql分表的相关知识,文中的示例代码讲解详细,感兴趣的小伙伴可... 目录基本思路定义注解创建ThreadLocal创建拦截器业务处理基本思路1.根据创建时间字段按年进