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 中的 JSON 查询案例详解

《MySQL中的JSON查询案例详解》:本文主要介绍MySQL的JSON查询的相关知识,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录mysql 的 jsON 路径格式基本结构路径组件详解特殊语法元素实际示例简单路径复杂路径简写操作符注意MySQL 的 J

Windows 上如果忘记了 MySQL 密码 重置密码的两种方法

《Windows上如果忘记了MySQL密码重置密码的两种方法》:本文主要介绍Windows上如果忘记了MySQL密码重置密码的两种方法,本文通过两种方法结合实例代码给大家介绍的非常详细,感... 目录方法 1:以跳过权限验证模式启动 mysql 并重置密码方法 2:使用 my.ini 文件的临时配置在 Wi

MySQL重复数据处理的七种高效方法

《MySQL重复数据处理的七种高效方法》你是不是也曾遇到过这样的烦恼:明明系统测试时一切正常,上线后却频频出现重复数据,大批量导数据时,总有那么几条不听话的记录导致整个事务莫名回滚,今天,我就跟大家分... 目录1. 重复数据插入问题分析1.1 问题本质1.2 常见场景图2. 基础解决方案:使用异常捕获3.

SQL中redo log 刷⼊磁盘的常见方法

《SQL中redolog刷⼊磁盘的常见方法》本文主要介绍了SQL中redolog刷⼊磁盘的常见方法,将redolog刷入磁盘的方法确保了数据的持久性和一致性,下面就来具体介绍一下,感兴趣的可以了解... 目录Redo Log 刷入磁盘的方法Redo Log 刷入磁盘的过程代码示例(伪代码)在数据库系统中,r

mysql中的group by高级用法

《mysql中的groupby高级用法》MySQL中的GROUPBY是数据聚合分析的核心功能,主要用于将结果集按指定列分组,并结合聚合函数进行统计计算,下面给大家介绍mysql中的groupby用法... 目录一、基本语法与核心功能二、基础用法示例1. 单列分组统计2. 多列组合分组3. 与WHERE结合使

Mysql用户授权(GRANT)语法及示例解读

《Mysql用户授权(GRANT)语法及示例解读》:本文主要介绍Mysql用户授权(GRANT)语法及示例,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql用户授权(GRANT)语法授予用户权限语法GRANT语句中的<权限类型>的使用WITH GRANT

C# foreach 循环中获取索引的实现方式

《C#foreach循环中获取索引的实现方式》:本文主要介绍C#foreach循环中获取索引的实现方式,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录一、手动维护索引变量二、LINQ Select + 元组解构三、扩展方法封装索引四、使用 for 循环替代

Mysql如何解决死锁问题

《Mysql如何解决死锁问题》:本文主要介绍Mysql如何解决死锁问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录【一】mysql中锁分类和加锁情况【1】按锁的粒度分类全局锁表级锁行级锁【2】按锁的模式分类【二】加锁方式的影响因素【三】Mysql的死锁情况【1

SQL BETWEEN 的常见用法小结

《SQLBETWEEN的常见用法小结》BETWEEN操作符是SQL中非常有用的工具,它允许你快速选取某个范围内的值,本文给大家介绍SQLBETWEEN的常见用法,感兴趣的朋友一起看看吧... 在SQL中,BETWEEN是一个操作符,用于选取介于两个值之间的数据。它包含这两个边界值。BETWEEN操作符常用

MySQL索引的优化之LIKE模糊查询功能实现

《MySQL索引的优化之LIKE模糊查询功能实现》:本文主要介绍MySQL索引的优化之LIKE模糊查询功能实现,本文通过示例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧... 目录一、前缀匹配优化二、后缀匹配优化三、中间匹配优化四、覆盖索引优化五、减少查询范围六、避免通配符开头七、使用外部搜索引擎八、分