赞
踩
数值类型
有包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT,分别表示 1 字节、2 字节、3 字节、4 字节、8 字节的整数类型。
1)任何整数类型都可以加上 UNSIGNED 属性,表示无符号整数。
2)任何整数类型都可以指定长度,但它不会限制数据的合法长度,仅仅限制了显示长度。
包括 VARCHAR、CHAR、TEXT、BLOB。
注意:VARCHAR(n) 和 CHAR(n) 中的 n 并不代表字节个数,而是代表字符的个数。
CHAR
和 VARCHAR
是 MySQL 中用于存储字符串的两种不同数据类型,它们之间的主要区别在于存储方式和性能:
CHAR
(固定长度):
CHAR
类型用于存储固定长度的字符串。当声明一个 CHAR
类型的字段时,需要指定一个长度(例如 CHAR(10)
),这个长度是最大字符数。CHAR
类型字段时,尾随空格会被去除。CHAR
类型的性能通常较好,尤其是当所有值都接近指定长度时。这是因为它有固定的存储大小,便于数据库快速计算记录的物理位置。CHAR
类型适用于存储长度相对固定的数据,如密码哈希、性别字段等。VARCHAR
(可变长度):
VARCHAR
类型用于存储可变长度的字符串。VARCHAR
同样需要指定最大长度(例如 VARCHAR(255)
)。VARCHAR
类型的字符串时,只占用必要的空间加上一个额外的字节或两个额外字节(取决于最大长度是否超过255)来记录字符串的实际长度,不足的部分不会用空格填充。VARCHAR
类型在存储长度有很大变化的字符串时更为高效,因为它不会浪费空间。VARCHAR
类型适用于存储长度可变的数据,如名字、地址、描述等。其他区别包括:
CHAR
是固定长度,它可能会浪费存储空间,因为短字符串会用空格填充到指定的长度。VARCHAR
只存储实际需要的空间,因此通常更节省空间。CHAR
字段在某些情况下可能比 VARCHAR
快,特别是在字段长度变化不大时。但是,如果字段通常包含较短的字符串,而指定了较长的 CHAR
长度,那么 VARCHAR
可能会更高效。CHAR
类型在存储时末尾的空格会被保留,但是在检索时会被去除。而 VARCHAR
类型会保留字符串末尾的空格。在选择使用 CHAR
还是 VARCHAR
的时候,需要考虑数据的特性和存储需求。如果数据的长度几乎固定,可以选择 CHAR
;如果数据的长度差异较大,应选择 VARCHAR
。
常用于表示日期和时间类型为 DATETIME、DATE 和 TIMESTAMP。
尽量使用 TIMESTAMP,空间效率高于 DATETIME。
TIMESTAMP范围: 1970-01-01 00:00:01
UTC 至 2038-01-19 03:14:07
UTC
DATETIME范围: 1000-01-01 00:00:00
至 9999-12-31 23:59:59
数据库设计中的三大范式(Normal Forms,简称NF)是关系型数据库设计的基本原则,旨在通过规范化(Normalization)的方式减少数据冗余和提高数据完整性。以下是三大范式的概述:
第一范式(1NF):
第二范式(2NF):
第三范式(3NF):
满足这三个范式的数据库设计可以有效地避免数据冗余,简化数据结构,提高数据的一致性和完整性。然而,过度范式化可能导致查询效率降低,因为可能需要连接更多的表。因此,实际数据库设计中可能需要在规范化和性能之间做出权衡。在某些情况下,为了提高查询性能,可能会适当进行反范式化操作。
从物理结构上可以分为聚集索引和非聚集索引
简单来说,聚集索引就是基于主键创建的索引,除了主键索引以外的其他索引,称为非聚集索引,也叫做二级索引。
由于在InnoDB引擎里面,一张表的数据对应的物理文件本身就是按照B+树来组织的一种索引结构,而聚集索引就是按照每张表的主键来构建一颗B+树,然后叶子节点里面存储了这个表的每一行数据记录。
所以基于InnoDB这样的特性,聚集索引并不仅仅是一种索引类型,还代表着一种数据的存储方式。
同时也意味着每个表里面必须要有一个主键,如果没有主键,InnoDB会默认选择或者添加一个隐藏列作为主键索引来存储这个表的数据行。一般情况是建议使用自增id作为主键,这样的话id本身具有连续性使得对应的数据也会按照顺序存储在磁盘上,写入性能和检索性能都很高。否则,如果使用uuid这种随机id,那么在频繁插入数据的时候,就会导致随机磁盘IO,从而导致性能较低。
需要注意的是,InnoDB里面只能存在一个聚集索引,原因很简单,如果存在多个聚集索引,那么意味着这个表里面的数据存在多个副本,造成磁盘空间的浪费,以及数据维护的困难。
由于在InnoDB里面,主键索引表示的是一种数据存储结构,所以如果是基于非聚集索引来查询一条完整的记录,最终还是需要访问主键索引来检索。
从应用上可以划分为一下几类
主键索引:特殊的唯一索引,也成聚簇索引,不允许有空值,并由数据库帮我们自动创建;
唯一索引:索引列中的值必须是唯一的,但是允许为空值。通过 ALTER TABLE table_name ADD UNIQUE index_name (column) 创建;
普通索引:MySQL 中的基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值,纯粹为了提高查询效率。通过 ALTER TABLE table_name ADD INDEX index_name (column) 创建;
组合索引:组合表中多个字段创建的索引,遵守最左前缀匹配规则;
全文索引:只有在 MyISAM 引擎上才能使用,同时只支持 CHAR、VARCHAR、TEXT 类型字段上使用。
在设计数据库索引时,应遵循一些基本原则以确保索引具有高效的性能和实用性。以下是一些关键的索引设计原则:
选择性高的字段:
根据查询优化:
避免冗余索引:
复合索引设计:
索引维护:
限制索引数量:
考虑数据类型和大小:
使用前缀索引:
考虑索引的存储成本:
测试和分析:
适当反范式化:
避免对频繁修改的列进行索引:
正确的索引设计需要综合考虑表的数据量、数据分布、数据访问模式(如查询、插入、更新的频率)以及应用的具体业务需求。设计索引的过程往往是一个不断调整和优化的过程。
MySQL数据库索引通常使用的是B+树,而不是B树,这是因为B+树相比于B树具有一些特性使其更适合作为数据库索引的数据结构。下面列出了B+树相比于B树的几个优点:
查询性能稳定:在B+树中,所有的数据都存储在叶子节点,他们的高度是相同的,因此查询使用时间趋于一致。
叶子节点顺序访问:由于B+树的叶子节点之间是相互连接的,这使得顺序访问变得非常高效。这对于数据库中的范围查询特别重要,因为这些查询经常需要访问顺序排列的数据。
磁盘读写优化:B+树的非叶子节点不保存数据,只保存指向子节点的指针,这意味着相比于B树的同一高度,B+树可以有更多的分支,这样树的高度更低,查询时需要的磁盘I/O操作也就更少。
由于B+树的内部节点不存储实际的数据,只存储键值和子节点指针,这使得每个内部节点可以有更多的子节点。这种结构使得B+树可以更加紧凑,从而减少了树的高度,提高了空间利用率。
更好的删除性能:在B树中,删除操作可能会引起数据在父节点和子节点之间的移动,而在B+树中,数据只在叶子节点中移动,这通常会简化删除操作。
由于这些优点,B+树成为了数据库索引的首选数据结构。在实际的数据库系统中,例如MySQL的InnoDB存储引擎,使用B+树作为其主要索引(聚簇索引)和辅助索引的数据结构。
InnoDB存储引擎推荐使用自增主键的原因主要与其索引结构和数据存储方式有关。InnoDB 使用聚簇索引(Clustered Index)来存储表中的数据,聚簇索引的特点是表中的数据按照主键的顺序存储在磁盘上。这种存储方式对于某些类型的查询和操作有显著的性能优势。以下是使用自增主键的几个主要原因:
插入性能:自增主键意味着新记录总是在索引的最后一个位置插入,这样的顺序插入减少了页的分裂和行移动,因为每次插入时不需要重新排序现有的数据,从而提高了插入操作的效率。
聚簇索引优势:在InnoDB中,聚簇索引包含了行的全部数据。如果主键是自增的,数据在磁盘上就是顺序存储的,这对于按照主键顺序访问行的查询非常高效,因为它们可以顺序读取磁盘上的数据块。
减少页分裂:非自增的主键(例如随机的GUID/UUID或者基于非自增字段的复合主键)可能导致中间插入,这样的操作会引起更多的页分裂,从而影响性能和空间利用效率。
避免外部碎片:自增主键可以减少数据的外部碎片,因为新数据总是添加到索引的末尾。
简化辅助索引结构:在InnoDB中,辅助索引(Secondary Index)包含对应的主键列作为指向聚簇索引记录的指针。如果主键比较简单(如自增的整数),辅助索引的大小也会相对较小。
InnoDB是MySQL默认的存储引擎之一,它使用了一种叫做多版本并发控制(MVCC)的技术来提高并发性能,并且支持事务处理、行级锁定和外键。InnoDB存储引擎在物理文件层面上对数据进行组织和管理时,使用了页(Page)、区(Extent)、段(Segment)这几个概念。
以下是对这些概念的简要解释:
页(Page):
区(Extent):
段(Segment):
在InnoDB的存储组织中,表数据和索引实际上是存储在不同类型的段中,这些段又是由多个区组成的,而每个区由多个页组成。当一个表或索引的数据超出当前段的容量时,InnoDB会分配新的区来扩展该段的空间。
通过这种组织方式,InnoDB能够有效地管理底层的磁盘空间,优化数据的存储和访问性能。此外,InnoDB的页结构也使得它能够支持高效的缓存和读写操作,以及行级的锁定和MVCC。
B+ 树的叶子节点存放的是我们的具体数据,非叶子结点是索引页。所以 B+ 树将数据分为了两部分,叶子节点部分和非叶子节点部分,也就我们要介绍的段 Segment,也就是说 InnoBD 中每一个索引都会创建两个 Segment 来存放对应的两部分数据。
Segment 是一种逻辑上的组织,其层次结构从上到下一次为 Segment、Extent、Page。
数据库事务的四种特性_jdbc的数据库事务不包括下面哪个特性-CSDN博客
原子性:是使用 undo log来实现的,如果事务执行过程中出错或者用户执行了rollback,系统通过undo log日志返回事务开始的状态。
持久性:使用 redo log来实现,只要redo log日志持久化了,当系统崩溃,即可通过redo log把数据恢复。
隔离性:通过锁以及MVCC,使事务相互隔离开。
一致性:通过回滚、恢复,以及并发情况下的隔离性,从而实现一致性。
在MySQL中,尤其是使用InnoDB存储引擎时,redo日志和undo日志是支持事务处理和故障恢复的关键组件。
Redo 日志(Redo Log): Redo日志是用来保证事务的持久性(Durability),它是一个重做日志,记录了所有修改数据库状态的操作。在事务被提交之前,这些修改操作的记录会被写入redo日志。如果数据库发生故障(比如系统崩溃或断电),在重启后可以使用redo日志重新执行(redo)这些操作,以确保这些修改被应用到数据库中,从而达到事务的持久性。
Redo日志是循环使用的,它通常由两个或更多的固定大小文件组成,这些文件统称为redo log buffer。当buffer满了或事务提交时,buffer中的日志会被刷新(flush)到磁盘上的日志文件中。
Undo 日志(Undo Log): Undo日志主要是用来实现事务的原子性(Atomicity)和隔离性(Isolation)。Undo日志记录了事务中所做的所有修改的反向操作。如果一个事务需要被回滚(rollback),MySQL可以使用undo日志来撤销事务所做的修改,保持数据的一致性。
Undo日志存储在undo tablespace中,当一个事务被提交后,相关的undo日志并不会立即被删除,因为它们可能还会被其他事务所需要。MySQL会在适当的时候自动清理和回收undo空间。
总结一下,redo日志和undo日志是InnoDB存储引擎提供事务特性的重要机制。Redo日志确保事务的持久性,即使在系统崩溃后也能够保证事务提交的修改能够被恢复。而undo日志则支持事务的原子性和隔离性,允许事务在出现错误时撤销修改,并提供并发事务的数据隔离能力。
在MySQL中,尤其是使用InnoDB存储引擎时,MVCC(多版本并发控制)是一种用来实现事务的隔离性和非锁定读取的技术。MVCC 允许多个事务同时对同一数据进行读取和修改,而不会彼此阻塞,从而提高了系统的并发性能。现在我们详细介绍下它的工作原理:
MVCC 通过为数据行保存多个版本来工作,每个版本都有自己的时间戳。这些时间戳与事务的版本号相对应。在InnoDB存储引擎中,时间戳是通过系统版本号来实现的,每个事务开始时都会获得一个唯一的系统版本号。
MVCC 通过使用undo日志来为每个数据行提供多个版本,当不再需要这些旧版本时(例如,没有更早的事务需要访问这些版本),系统会自动清理它们以避免空间浪费。这个清理过程被称为垃圾回收(Purge)。每当提交一个事务时,系统都会检查是否可以清理一些旧的版本。
MVCC机制的主要优势是它允许读取操作在不加锁的情况下与写入操作并发执行,大大减少了锁竞争,从而提高了数据库系统的并发性能。这是InnoDB存储引擎在多用户环境下能够高效工作的关键之一。
MySQL中的锁主要用于管理对数据库中数据的并发访问,以保证事务的隔离性和数据的一致性。以下是MySQL中常见的几种锁:
全局锁(Global Lock): 通常通过FLUSH TABLES WITH READ LOCK
命令来实现,它会对整个数据库实例加锁,通常用于进行全库备份等操作。
表级锁(Table Lock): 在MySQL中,表级锁是最基本的锁策略,它会锁定整张表。MyISAM和MEMORY存储引擎使用表级锁。表级锁分为读锁(共享锁)和写锁(排它锁)。
行级锁(Row Lock): 行级锁是最细粒度的锁,它允许对数据库表中单独的行进行加锁。InnoDB和XtraDB存储引擎支持行级锁。行级锁同样分为共享锁(读锁)和排它锁(写锁)。
意向锁(Intention Locks): InnoDB实现了意向锁,这是一种表级锁,用于表示事务想要在表的行上加排它锁或共享锁。意向锁分为意向共享锁(IS)和意向排它锁(IX)。
记录锁(Record Lock): 记录锁是行级锁的一种,它会锁定索引记录,而不是整行数据。在InnoDB存储引擎中,记录锁直接作用于索引记录上。
间隙锁(Gap Lock): 间隙锁是InnoDB特有的一种锁,它锁定一个范围,但不包括记录本身,主要用来解决幻读问题。
临键锁(Next-Key Lock): 临键锁是InnoDB存储引擎的默认行锁,它是记录锁和间隙锁的组合,锁定一个范围,并且包括记录本身。
自增锁(Auto-Increment Lock): 自增锁是InnoDB存储引擎用于管理AUTO_INCREMENT
属性的特殊表级锁,它确保了自增值的唯一性和连续性。
死锁(Deadlock): 死锁并不是一种锁的类型,而是指两个或多个事务在相互等待对方释放锁的情况,导致事务永远无法继续执行。MySQL会自动检测死锁并解决,通常是通过回滚其中一个事务来释放锁资源。
元数据锁(Metadata Lock): 元数据锁是MySQL中用来管理对数据库对象(如表)元数据的修改。当一个事务对表进行操作时,它会自动获取表的元数据锁,以防止表结构在使用中被修改。
了解这些不同类型的锁对于优化数据库性能、避免锁竞争和死锁、保证数据一致性等方面非常重要。不同的存储引擎可能支持不同的锁类型和锁策略。
在MySQL的InnoDB存储引擎中,批量插入操作涉及到多种类型的锁。具体使用哪种锁取决于批量插入的具体语句和当时的事务隔离级别。
在批量插入时,MySQL通常会尝试获取所需的记录锁。如果插入的数据会影响到索引,那么可能还会涉及到间隙锁或临键锁,以维护索引的一致性和防止幻读。
关于死锁的可能性,批量插入确实有可能导致死锁,尤其是在以下情况下:
死锁通常是由于事务相互等待对方持有的锁而发生的,这在并发环境下是无法完全避免的,但可以通过一些策略来减少死锁的发生:
总之,虽然批量插入有可能导致死锁,但通过合理的设计和编码策略,可以极大地降低这种风险。
select查询语句是不会加锁的,但是select for update除了有查询的作用外,还会加锁呢,而且它是悲观锁哦。至于加了是行锁还是表锁,这就要看是不是用了索引/主键啦。
没用索引/主键的话就是表锁,否则就是是行锁。
Explain 执行计划包含字段信息如下:分别是 id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra 等12个字段。
我们重点关注的是type,它的属性排序如下:
system > const > eq_ref > ref > ref_or_null >index_merge > unique_subquery > index_subquery >range > index > ALL
主从复制原理,简言之,就三步曲,如下:
主数据库有个bin-log二进制文件,纪录了所有增删改Sql语句。(binlog线程)
从数据库把主数据库的bin-log文件的sql语句复制过来。(io线程)
从数据库的relay-log重做日志文件中再执行一次这些sql语句。(Sql执行线程)
如下图所示:
上图主从复制分了五个步骤进行:
步骤一:主库的更新事件(update、insert、delete)被写到binlog
步骤二:从库发起连接,连接到主库。
步骤三:此时主库创建一个binlog dump thread,把binlog的内容发送到从库。
步骤四:从库启动之后,创建一个I/O线程,读取主库传过来的binlog内容并写入到relay log
步骤五:还会创建一个SQL线程,从relay log里面读取内容,从Exec_Master_Log_Pos位置开始执行读取到的更新事件,将更新内容写入到slave的db
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。