微商网站开发合同,大昌建设集团有限公司网站,dede做手机网站,网站建设与开发试题与答案1 基础
1.1、MySQL有哪些数据库类型#xff1f;
数值类型 有包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT#xff0c;分别表示 1 字节、2 字节、3 字节、4 字节、8 字节的整数类型。
1#xff09;任何整数类型都可以加上 UNSIGNED 属性#xff0c;表示无符号整数。 …1 基础
1.1、MySQL有哪些数据库类型
数值类型 有包括 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)这个长度是最大字符数。如果存储的字符串长度小于指定的长度MySQL 会用空格填充剩余的空间。在检索 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 1.2、什么是三大范式
数据库设计中的三大范式Normal Forms简称NF是关系型数据库设计的基本原则旨在通过规范化Normalization的方式减少数据冗余和提高数据完整性。以下是三大范式的概述 第一范式1NF: 定义一个关系型数据库表的每一列都是不可分割的基本数据项即表中的所有字段都是原子性的每一列都只包含原子数据项。目的确保每一列的原子性消除复合属性和多值属性使得每一行的每一列都包含单一数据值。举例如果一个表中有一个字段存储了多个电话号码以逗号分隔那么这个表就不满足第一范式。 第二范式2NF: 定义在第一范式的基础上非主键字段完全依赖于主键不存在部分依赖。目的消除部分依赖即数据表中的所有非主键字段必须依赖于整个主键而不是依赖于主键的一部分如果主键是由多个字段组成的复合主键。举例如果一个表的主键由两个字段组成而表中的某些非主键字段仅依赖于这个复合主键的一部分那么就需要将这些字段移到另一个表中以确保它们依赖的是完整的主键。 第三范式3NF: 定义在第二范式的基础上消除了非主键字段对其他非主键字段的传递依赖也称为对主键的间接依赖。目的确保数据表中的每一列都只依赖于主键消除字段之间的传递依赖进一步减少数据冗余。举例如果一个表中有两个非主键字段其中一个字段可以通过另一个非主键字段推导出来那么存在传递依赖。解决办法是将能够推导出其他字段的那个字段单独移到一个新表中。
满足这三个范式的数据库设计可以有效地避免数据冗余简化数据结构提高数据的一致性和完整性。然而过度范式化可能导致查询效率降低因为可能需要连接更多的表。因此实际数据库设计中可能需要在规范化和性能之间做出权衡。在某些情况下为了提高查询性能可能会适当进行反范式化操作。
2、索引
2.1、索引的几种类型或分类
从物理结构上可以分为聚集索引和非聚集索引 简单来说聚集索引就是基于主键创建的索引除了主键索引以外的其他索引称为非聚集索引也叫做二级索引。 由于在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 类型字段上使用。
在设计数据库索引时应遵循一些基本原则以确保索引具有高效的性能和实用性。以下是一些关键的索引设计原则 选择性高的字段 优先为那些具有高选择性的字段创建索引。选择性是指不同值的数量占总行数的比例。具有高唯一值的列如主键通常是很好的索引候选。 根据查询优化 索引应基于最常用的查询操作进行建立考虑WHERE子句中的列、JOIN操作中的列以及ORDER BY和GROUP BY子句中的列。 避免冗余索引 避免创建重复和冗余的索引。如果一个列已经是另一个复合索引的前缀则通常不需要为该列单独创建索引。 复合索引设计 当查询条件中经常包含多个列时考虑创建复合索引。在创建复合索引时将选择性最高的列放在最前面。 索引维护 随着数据的变化索引会发生碎片化应定期进行索引维护如重建或重新组织索引。 限制索引数量 过多的索引会增加插入、更新和删除操作的开销因为这些操作都需要维护索引。因此避免在不经常用于查询的列上建立索引。 考虑数据类型和大小 较小的数据类型通常更适合索引因为它们在磁盘上占用更少的空间可以减少I/O操作。例如INT比CHAR或VARCHAR更适合索引。 使用前缀索引 对于文本字段如VARCHAR、TEXT如果全文索引不适用或不必要可以使用前缀索引以节约空间并提升效率。 考虑索引的存储成本 索引虽然可以提升查询性能但也占用存储空间。需要权衡索引带来的性能提升和额外的存储成本。 测试和分析 使用EXPLAIN或其他工具分析查询计划验证索引的实际效果并根据实际的查询负载对索引策略进行调整。 适当反范式化 在某些情况下为了提高查询性能可以适当反范式化数据库设计并创建冗余的数据和索引。 避免对频繁修改的列进行索引 如果某个列的数据频繁更新每次更新都需要维护索引可能会降低整体性能。
正确的索引设计需要综合考虑表的数据量、数据分布、数据访问模式如查询、插入、更新的频率以及应用的具体业务需求。设计索引的过程往往是一个不断调整和优化的过程。
2.2、为什么 B 树比 B 树更适合应用于数据库索引
MySQL数据库索引通常使用的是B树而不是B树这是因为B树相比于B树具有一些特性使其更适合作为数据库索引的数据结构。下面列出了B树相比于B树的几个优点 查询性能稳定在B树中所有的数据都存储在叶子节点他们的高度是相同的因此查询使用时间趋于一致。 叶子节点顺序访问由于B树的叶子节点之间是相互连接的这使得顺序访问变得非常高效。这对于数据库中的范围查询特别重要因为这些查询经常需要访问顺序排列的数据。 磁盘读写优化B树的非叶子节点不保存数据只保存指向子节点的指针这意味着相比于B树的同一高度B树可以有更多的分支这样树的高度更低查询时需要的磁盘I/O操作也就更少。 由于B树的内部节点不存储实际的数据只存储键值和子节点指针这使得每个内部节点可以有更多的子节点。这种结构使得B树可以更加紧凑从而减少了树的高度提高了空间利用率。 更好的删除性能在B树中删除操作可能会引起数据在父节点和子节点之间的移动而在B树中数据只在叶子节点中移动这通常会简化删除操作。
由于这些优点B树成为了数据库索引的首选数据结构。在实际的数据库系统中例如MySQL的InnoDB存储引擎使用B树作为其主要索引聚簇索引和辅助索引的数据结构。
2.3、InnoDB 为何推荐使用自增主键
InnoDB存储引擎推荐使用自增主键的原因主要与其索引结构和数据存储方式有关。InnoDB 使用聚簇索引Clustered Index来存储表中的数据聚簇索引的特点是表中的数据按照主键的顺序存储在磁盘上。这种存储方式对于某些类型的查询和操作有显著的性能优势。以下是使用自增主键的几个主要原因 插入性能自增主键意味着新记录总是在索引的最后一个位置插入这样的顺序插入减少了页的分裂和行移动因为每次插入时不需要重新排序现有的数据从而提高了插入操作的效率。 聚簇索引优势在InnoDB中聚簇索引包含了行的全部数据。如果主键是自增的数据在磁盘上就是顺序存储的这对于按照主键顺序访问行的查询非常高效因为它们可以顺序读取磁盘上的数据块。 减少页分裂非自增的主键例如随机的GUID/UUID或者基于非自增字段的复合主键可能导致中间插入这样的操作会引起更多的页分裂从而影响性能和空间利用效率。 避免外部碎片自增主键可以减少数据的外部碎片因为新数据总是添加到索引的末尾。 简化辅助索引结构在InnoDB中辅助索引Secondary Index包含对应的主键列作为指向聚簇索引记录的指针。如果主键比较简单如自增的整数辅助索引的大小也会相对较小。
3 存储结构
3.1、什么是 InnoDB 的页、区、段
InnoDB是MySQL默认的存储引擎之一它使用了一种叫做多版本并发控制MVCC的技术来提高并发性能并且支持事务处理、行级锁定和外键。InnoDB存储引擎在物理文件层面上对数据进行组织和管理时使用了页Page、区Extent、段Segment这几个概念。
以下是对这些概念的简要解释 页Page: 页是InnoDB磁盘管理的基本单位也是数据存储和读写的最小单元。InnoDB默认的页大小是16KB但是在MySQL 5.6及以后的版本中页大小可以配置为4KB、8KB、16KB、32KB或64KB。数据页中可以包含行记录、索引等信息。 区Extent: 一个区是由连续的页组成的集合通常由64个连续的页组成因此一个区的大小通常为1MB16KB * 64。区是分配空间的单位InnoDB会一次性分配一个区的空间提高空间的分配效率。 段Segment: 段是由一个或多个区组成的更高级别的数据结构是InnoDB存储结构中的逻辑单元。每个段可以是索引段用于存储索引信息或数据段用于存储实际的行数据。根据段存储的内容不同可以进一步分为表空间段、索引段、回滚段等。
在InnoDB的存储组织中表数据和索引实际上是存储在不同类型的段中这些段又是由多个区组成的而每个区由多个页组成。当一个表或索引的数据超出当前段的容量时InnoDB会分配新的区来扩展该段的空间。
通过这种组织方式InnoDB能够有效地管理底层的磁盘空间优化数据的存储和访问性能。此外InnoDB的页结构也使得它能够支持高效的缓存和读写操作以及行级的锁定和MVCC。
B 树的叶子节点存放的是我们的具体数据非叶子结点是索引页。所以 B 树将数据分为了两部分叶子节点部分和非叶子节点部分也就我们要介绍的段 Segment也就是说 InnoBD 中每一个索引都会创建两个 Segment 来存放对应的两部分数据。
Segment 是一种逻辑上的组织其层次结构从上到下一次为 Segment、Extent、Page。
4、事务
数据库事务的四种特性_jdbc的数据库事务不包括下面哪个特性-CSDN博客
悲观锁和乐观锁_悲观锁性能一定比乐观锁差吗-CSDN博客
4.1 Innodb的事务实现原理 原子性是使用 undo log来实现的如果事务执行过程中出错或者用户执行了rollback系统通过undo log日志返回事务开始的状态。 持久性使用 redo log来实现只要redo log日志持久化了当系统崩溃即可通过redo log把数据恢复。 隔离性通过锁以及MVCC,使事务相互隔离开。 一致性通过回滚、恢复以及并发情况下的隔离性从而实现一致性。
4.2 redo 和undo日志
在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日志记录了事务中所做的所有修改的反向操作。如果一个事务需要被回滚rollbackMySQL可以使用undo日志来撤销事务所做的修改保持数据的一致性。 Undo日志存储在undo tablespace中当一个事务被提交后相关的undo日志并不会立即被删除因为它们可能还会被其他事务所需要。MySQL会在适当的时候自动清理和回收undo空间。
总结一下redo日志和undo日志是InnoDB存储引擎提供事务特性的重要机制。Redo日志确保事务的持久性即使在系统崩溃后也能够保证事务提交的修改能够被恢复。而undo日志则支持事务的原子性和隔离性允许事务在出现错误时撤销修改并提供并发事务的数据隔离能力。
4.3 MVVC
在MySQL中尤其是使用InnoDB存储引擎时MVCC多版本并发控制是一种用来实现事务的隔离性和非锁定读取的技术。MVCC 允许多个事务同时对同一数据进行读取和修改而不会彼此阻塞从而提高了系统的并发性能。现在我们详细介绍下它的工作原理
数据版本化
MVCC 通过为数据行保存多个版本来工作每个版本都有自己的时间戳。这些时间戳与事务的版本号相对应。在InnoDB存储引擎中时间戳是通过系统版本号来实现的每个事务开始时都会获得一个唯一的系统版本号。
读操作
一致性非锁定读Consistent Nonlocking Read 在默认隔离级别REPEATABLE READ下当一个事务进行读取操作时它只会看到在该事务开始之前已经提交的修改或者是该事务自身所做的修改。这意味着无论其他事务如何修改数据当前事务都能看到一个一致的数据快照。这通过使用undo日志来实现即使数据在物理上已经被另一个事务修改了当前事务仍然可以通过undo日志看到旧版本的数据。
写操作
Create Read View: 当一个事务需要修改数据时InnoDB会创建一个新版本的数据行并把修改应用到这个新版本上。同时InnoDB还会保留旧版本的数据行这样其他事务就可以根据需要访问它。这个新旧版本的管理是通过undo日志来实现的。
保存点和垃圾回收
MVCC 通过使用undo日志来为每个数据行提供多个版本当不再需要这些旧版本时例如没有更早的事务需要访问这些版本系统会自动清理它们以避免空间浪费。这个清理过程被称为垃圾回收Purge。每当提交一个事务时系统都会检查是否可以清理一些旧的版本。
MVCC机制的主要优势是它允许读取操作在不加锁的情况下与写入操作并发执行大大减少了锁竞争从而提高了数据库系统的并发性能。这是InnoDB存储引擎在多用户环境下能够高效工作的关键之一。 5、锁 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中用来管理对数据库对象如表元数据的修改。当一个事务对表进行操作时它会自动获取表的元数据锁以防止表结构在使用中被修改。
了解这些不同类型的锁对于优化数据库性能、避免锁竞争和死锁、保证数据一致性等方面非常重要。不同的存储引擎可能支持不同的锁类型和锁策略。 5.1 批量插入会用到什么锁有可能死锁吗
在MySQL的InnoDB存储引擎中批量插入操作涉及到多种类型的锁。具体使用哪种锁取决于批量插入的具体语句和当时的事务隔离级别。
记录锁Record Lock直接作用于索引记录上的锁。间隙锁Gap Lock作用于索引记录之间的间隙或者作用于第一条记录之前或最后一条记录之后的间隙。临键锁Next-Key Lock是记录锁和间隙锁的组合锁定一个范围防止其他事务在该范围内插入记录。表锁Table Lock作用于整个表。
在批量插入时MySQL通常会尝试获取所需的记录锁。如果插入的数据会影响到索引那么可能还会涉及到间隙锁或临键锁以维护索引的一致性和防止幻读。
关于死锁的可能性批量插入确实有可能导致死锁尤其是在以下情况下
多个事务试图以不同的顺序插入或修改涉及到相同资源记录、间隙、临键的行。表结构包含多个索引不同的事务试图以不同的顺序来修改这些索引。一些与表结构有关的DDL操作与数据插入并发执行。
死锁通常是由于事务相互等待对方持有的锁而发生的这在并发环境下是无法完全避免的但可以通过一些策略来减少死锁的发生
尽量减少事务的大小和执行时间快速释放锁资源。尽可能地以相同的顺序在所有事务中访问数据库对象。使用低隔离级别例如READ COMMITTED可以减少锁的持续时间但要注意隔离级别降低可能带来的其他问题。对于写入操作如果可能尽量按照主键或索引的顺序进行推荐自增主键这可以减少死锁的可能性。优化批量操作的逻辑比如先排序后插入以保持一致的插入顺序。当发现死锁时系统会自动检测并回滚其中一个事务以解锁应用程序应该能够正确处理这种情况比如通过重新尝试失败的事务。
总之虽然批量插入有可能导致死锁但通过合理的设计和编码策略可以极大地降低这种风险。
5.2 select for update 含义
select查询语句是不会加锁的但是select for update除了有查询的作用外还会加锁呢而且它是悲观锁哦。至于加了是行锁还是表锁这就要看是不是用了索引/主键啦。
没用索引/主键的话就是表锁否则就是是行锁。
6、SQL
6.1 SQL的执行顺序 6.2 谈谈MySQL的Explain
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
7、主从
7.1 MySQL的复制原理以及流程 主从复制原理简言之就三步曲如下 主数据库有个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
