物业管理系统业务流程图,培训seo去哪家机构最好,企业管理平台系统网站,查看注册过的网站梳理面试过程中数据库相关的常见问题#xff0c;需要说明的是#xff0c;这篇文章主要是基于MySQL数据库#xff0c;其他类型的数据库还请自行参考使用。
数据库概述
为什么使用数据库
1、数据库增删改查更方便 2、提供了事务的能力 本质是更好的管理数据。
数据库体系结…梳理面试过程中数据库相关的常见问题需要说明的是这篇文章主要是基于MySQL数据库其他类型的数据库还请自行参考使用。
数据库概述
为什么使用数据库
1、数据库增删改查更方便 2、提供了事务的能力 本质是更好的管理数据。
数据库体系结构
一个数据库系统各个部分以及它们之间的联系如下图所示
一条SQL的执行过程
MySQL中一条SQL的执行过程如下图所示 (1) 连接器 建立连接是客户端和MySQL交互的第一步。这里涉及到通信协议。连接器支持短连接也支持长连接同时为了避免频繁创建和销毁连接造成性能损失可选择利用连接池进程管理如druidcp30等。 (2) 查询缓存 查询缓存主要用来缓存所执行的 select语句以及该语句的结果集。存储的数据是以键值对的形式进行存储如果开启了缓存那么在一条查询sql语句进来时会先判断缓存中是否包含当前的sql语句键值对如果存在直接将其对应的结果返回如果不存在再执行后面一系列操作。如果没有开启则直接跳过。在数据变换频繁的表中是不推荐使用的当一张表的数据发生变化其所有缓存都将清空。 MySQL的查询缓存默认是关闭的需要手动配置参数 query_cache_type来开启查询缓存。需要说明的是MySQL 8.0 版本后移除了查询缓存。之所以删除查询缓存主要有以下两方面的考虑 1只要有对一个表的更新这个表上所有的查询缓存都会被清空 2SQL任何字符上的不同如空格注释都会导致缓存不命中 所以查询缓存生效的场景主要是配置表。其他的业务表根本无法利用查询缓存的特性。 (3) 分析器 分析器由两部分组成解析器和预处理器这里是将两者的功能合并在一起。 对于解析器主要是将SQL进行语义解析具体来说包含以下几步(1) 先进行词法解析校验吃法规则(2) 语法解析判断输入的这个 SQL 语句是否满足 MySQL 语法(3) 语义解析对SQL中的表、索引、视图等对象进行解析并对照数据字典检查这些对象的名称以及相关结构看看这些字段、表、视图等是否在数据库中。如果表名与列名不准确的话则数据库会就会反馈错误信息给客户端。 若语法与表名或者列名同时写错的话则系统是先提示说语法错误等到语法完全正确后再提示说列名或表名错误。 对于预处理器主要是针对预处理 SQL。因为绝大多数情况下某条 SQL 语句可能会被反复调用执行或者每次执行的时候只有个别的值不同比如 select 的 where 子句值不同update 的 set 子句值不同insert 的 values 值不同。如果每次都需要经过词法语义解析、语句优化、制定执行计划等则效率就明显不行了。所谓预编译语句就是将此类 SQL 语句中的值用占位符替代可以视为将 SQL 语句模板化或者说参数化一般称这类语句叫Prepared Statements。预编译语句的优势在于归纳为一次编译、多次运行省去了解析优化等过程此外预编译语句能防止 SQL 注入。 (4) 查询优化器 查询优化器会找出执行该语句所有可能使用的方案然后选择一条最优查询路径即MySQL认为的效率最高的方式并生成执行计划。查询优化器的目的是为了得到目标SQL的执行计划。比如一个表中创建了多个索引优化器会根据IO和CPU成本选出代价最小的索引进行执行。 查询成本计算可参考Mysql–查询的成本如何计算一文。也可通过SQL语句前添加上 explain 关键字查看执行计划(重点关注type、key、Extra字段)。 (5) 执行引擎 查询语句后经过sql的优化器会产生一个执行计划。根据MySQL执行计划的输出分析索引使用情况、扫描的行数可以预估查询效率进而可以重构SQL语句、调整索引提升查询效率。 执行引擎会利用存储引擎提供的相应的 API 来完成SQL操作。注意不同功能的存储引擎实现的 API 是相同的。所以调整存储引擎操作方式并不需要同步调整。最后执行引擎把数据返回给客户端即使没有结果也要返回。 (6) 存储引擎 InnoDB支持事务处理支持外键支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高比如银行要求实现并发控制比如售票那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库也可以选择InnoDB因为支持事务的提交commit和回滚rollback。 MyISAM插入数据快空间和内存使用比较低。如果表主要是用于插入新记录和读出记录那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比 较低也可以使用。如果数据表主要用来插入和查询记录则MyISAM引擎能提供较高的处理效率。
为什么选择MySQL数据库
MySQL的技术优势如下 高可靠性MySQL具有非常高的容错性和稳定性即使在硬件或软件出现故障的情况下也能保证数据的完整性和安全性。 高性能MySQL采用了先进的优化技术包括索引优化、查询优化、缓存优化等以确保数据库的高效运行并且支持高并发访问。 灵活性MySQL是一个模块化的数据库系统可以轻松扩展和自定义适应不同类型的应用程序和使用场景。 安全性MySQL具有多重安全功能包括访问控制、加密存储、事务支持等可以有效保护数据库的安全和完整性。 可扩展性MySQL数据库可以轻松地添加新的节点从而可以更好地处理大量数据。
可满足高可靠性、高性能的需求。
为什么选择InnoDB
数据安全性高InnoDB存储引擎通过日志和缓冲池等机制来保证数据的完整性和一致性可以处理各种意外情况例如系统崩溃、断电等问题。 支持大事务InnoDB存储引擎支持大事务超过1GB使得用户可以对大型数据进行修改操作而不用分批提交。 高并发性能InnoDB存储引擎支持行级锁定可以保证多个用户同时访问同一条数据时不会出现死锁问题从而提高了并发性能。
满足高可靠性、高性能的需求。
InnoDB引擎的4大特性
(1) 插入缓冲insert buffer)
(2) 二次写(double write)
(3) 自适应哈希索引(ahi)
(4) 预读(read ahead)
数据库设计
MyISAM与InnoDB对比
场景MyISAMInnoDB存储结构每张表被存放在三个文件frm-表格定义、MYD(MYData)-数据文件、MYI(MYIndex)-索引文件所有的表都保存在同一个数据文件中也可能是多个文件或者是独立的表空间文件InnoDB表的大小只受限于操作系统文件的大小一般为2GB记录存储顺序按记录插入顺序保存按主键大小有序插入外键不支持支持事务不支持支持锁支持表级锁定行级锁定、表级锁定锁定力度小并发能力高SELECT 操作MyISAM更优–INSERT、UPDATE、DELETE 操作–InnoDB更优索引的实现方式B树索引myisam 是堆表B树索引Innodb 是索引组织表哈希索引不支持支持全文索引支持不支持
数据类型
MySQL提供的数据类型有很多具体来说有以下几类整数类型、浮点数类型、日期和时间类型、字符串类型、文本和二进制类型。具体见表格
分类类型名称大小数值范围用途整数类型TINYINT1 Bytes(-128127)微小整数整数类型SMALLINT2 Bytes(-3276832767)小整数整数类型MEDIUMINT3 Bytes(-8 388 6088 388 607)中等整数整数类型INT或INTEGER4 Bytes(-21474836482147483647)整数整数类型BIGINT8 Bytes(-92233720368547758089223372036854775807)大整数浮点数类型FLOAT4 Bytes科学计数法单精度浮点数浮点数类型DOUBLE8 Bytes科学计数法双精度浮点数浮点数类型DECIMAL对DECIMAL(M,D) 如果MD为M2否则为D2依赖于M和D的值精准小数日期和时间类型DATE3 BytesYYYY-MM-DD, (1000-01-01, 9999-12-31)日期日期和时间类型TIME3 BytesHH:MM:SS, (-838:59:59, 838:59:59)时间日期和时间类型YEAR1 BytesYYYY, (1901, 2155)年日期和时间类型DATETIME8 BytesYYYY-MM-DD hh:mm:ss, (1000-01-01 00:00:00, 9999-12-31 23:59:59)混合日期和时间日期和时间类型TIMESTAMP4 BytesYYYY-MM-DD hh:mm:ss, (1970-01-01 00:00:01 UTC, 2038-01-19 03:14:07 UTC)标准时间戳字符串类型CHAR0-255 bytes–定长字符串字符串类型VARCHAR0-65535 bytes–变长字符串文本和二进制类型TINYBLOB0-255 bytes–不超过 255 个字符的二进制文本数据文本和二进制类型MEDIUMBLOB0-16777215 bytes–二进制形式的中等长度文本数据文本和二进制类型BLOB0-65535 bytes–二进制形式的文本数据文本和二进制类型LONGBLOB0-4294967295bytes–二进制形式极大文本数据文本和二进制类型TEXT0-65535 bytes–文本数据文本和二进制类型TEXT0-65535 bytes–文本数据文本和二进制类型BINARY(M)0-M bytes–允许长度0~M个字节的定长字节字符串文本和二进制类型VARBINARY(M)0-M bytes–允许长度0~M个字节的变长字节字符串
任何整数类型、浮点数类型都可以加上UNSIGNED属性表示数据是无符号的即非负整数默认是有符号数据。 整数类型可以被指定长度例如INT(11)表示长度为11的INT类型。长度在大多数场景是没有意义的它不会限制值的合法范围只会影响显示字符的个数不建议指定长度。 CHAR 和 VARCHAR 类型类似但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。 尽管CHAR有其单独应用场景但是推荐使用VARCHAR以保证可移植性。 MySQL也支持枚举类型ENUM用于把不重复的数据存储为一个预定义的集合。但是枚举类型并不通用建议使用CHAR或VARCHAR替换。 针对日期和时间类型尽量使用TIMESTAMP考虑到TIMESTAMP时间范围有限一般推荐使用VARCHAR存储并在业务侧进行转换。 TEXT 也提供TINYTEXT、MEDIUMBLOB、LONGTEXT等类型这里不再展示。 尽量避免使用TEXT、BLOB等类型因为查询时会使用临时表这将导致严重的性能开销。 BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR不同的是它们包含二进制字符串而不要非二进制字符串。也就是说它们包含字节字符串而不是字符字符串。这说明它们没有字符集并且排序和比较基于列值字节的数值值。
数据库范式使用规范
【强制】数据库设计优先满足第三范式(3NF)如果无法满足则尽量满足第二范式(2NF)
第一范式要求表中列的值具有原子性。(不存在组合字段) 第二范式要求表中的每条记录都有唯一标识且不存在部分依赖。所谓部分依赖就是指所有非唯一标识字段都必须完全依赖唯一标识不能只依赖唯一标识的一部分。如果知道唯一标识属性的值就可以检索到任何表记录的任何属性的任何值。(不存在部分依赖如果有要拆分出来) 假设有关系模型R包含如下属性(学号、课程号、课程名、姓名、学分、成绩)表示学生的课程得分以学号和课程号为唯一标识这里姓名属性只依赖学号学分属性只依赖课程号所以这里存在部分依赖不符合第二范式的要求。 第二范式的核心思想是关系模型的内聚也即拆分思想。对于上面的关系模型可以进一步细分为如下几个关系模型 学生表(学号、姓名) 课程表(课程号、课程名、学分) 学生课程成绩表(学号、课程号、成绩) 第三范式要求表中的每一个非主键字段都和主键字段直接相关也就是说表中的所有非主键字段不能依赖于其他非主键字段也即不存在传递依赖。(不存在传递依赖如果有要独立出来) 假设有关系模型R包含如下属性(学号、姓名、年龄、学院、学院电话)因为存在传递依赖(学号) - (姓名) - (学院) - (学院电话)所以不符合第三范式的要求。 第三范式的核心思想是消除所有基于函数依赖能够发现的冗余并保持函数依赖。对于上面的关系模型可以进一步细分为如下几个关系模型 学生(学号、姓名、年龄、学院) 学院(学院、学院电话)
索引
什么是索引
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)它们包含着对数据表里所有记录的引用指针。 索引是一种数据结构。数据库索引是数据库管理系统中一个排序的数据结构以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B树。 更通俗的说索引就相当于目录。为了方便查找书中的内容通过对内容建立索引形成目录。索引是一个文件它是要占据物理空间的。
索引的基本原理
索引用来加速寻找那些具有特定值的记录。如果没有索引一般来说执行查询时将会全表扫描。 索引的原理很简单就是把无序的数据变成有序的查询。 1、把创建了索引的列的内容进行排序 2、对排序结果生成倒排表 3、在倒排表内容上拼上数据地址链 4、在查询的时候先拿到倒排表内容再取出数据地址链从而拿到具体数据
索引的优缺点–为什么使用索引以及索引存在的问题
索引的优点 可以大大加快数据的检索速度这也是创建索引的最主要的原因。 通过使用索引可以在查询的过程中使用优化隐藏器提高系统的查询性能。 索引的缺点 (1) 时间方面创建索引和维护索引要耗费时间具体地当对表中的数据进行增加、删除和修改的时候索引也要动态的维护会降低增/改/删的执行效率 (2) 空间方面索引需要占物理空间。
索引的分类
(1) 顺序索引和散列索引 按照索引的实现结构基本的索引类型可以分为顺序索引和散列索引 顺序索引基于值的顺序排序。 散列索引基于将值平均分布到若干个散列桶中。一个值所属的散列桶由散列函数决定。 其中顺序索引按照记录的文件(数据库文件)是否按照某个搜索码指定的顺序排序可以分为聚簇索引和非聚簇索引。 (2) 聚簇索引和非聚簇索引 如果包含记录的文件(数据库文件)按照某个搜索码指定的顺序排序那么该搜索码对应的索引称为聚集索引(clustering index)。聚集索引也称为主索引primary index。也有将其称为聚簇索引。 搜索码指定的顺序与文件中记录的物理顺序不同的索引称为非聚集索引(noclustering index)或辅助索引(secondary index)。也称为非聚簇索引。 (3) 稠密索引和稀疏索引 根据是否为所有的搜索码值建立索引项可将顺序索引细分为两类稠密索引和稀疏索引。 (4) 静态索引和动态索引 根据是否可动态扩展将散列划分为静态散列和动态散列。 (5) 位图索引 位图索引是一种为多码上的简单查询设计的特殊索引尽管每个位图索引都是建立在一个码之上的。 为了使用位图索引关系中的记录必须按顺序编号比如从0开始。对于给定的一个n值必须能很简单地检索到编号为n的记录。然后该记录号就可以很简单地转化为一个块编号和一个指出块内记录的记录号。 (6) 主键索引、唯一索引、普通索引、覆盖索引、多码索引(联合索引、组合索引) 主键索引数据列不允许重复不允许为NULL一个表只能有一个主键。 唯一索引数据列不允许重复允许为NULL值一个表允许多个列创建唯一索引。 普通索引: 基本的索引类型没有唯一性的限制允许为NULL值。 覆盖索引存储一些记录的属性但不是搜索码属性的值以及指向记录的指针。存储附加的属性值对于辅助索引时非常有用的因为它们使索引能够回答一些查询甚至不需要找到实际的记录。(索引中存储记录的属性查找时可以直接使用索引上的值) 简单来说当一个查询只需要访问索引中的数据而无需访问实际的表时就可以使用覆盖索引。(减少一次查表操作也有人将其称为回表) 多码索引是指在复合的搜索码上建立和使用索引。也常称为联合索引、“组合索引”。
大多数数据库系统支持B树索引并且有可能还额外支持某种形式的散列文件组织或散列索引。 要对文件组织和索引技术做出明智的选择实现者或数据库设计者必须考虑以下问题 (1)索引或散列组织的周期性重组代价是否可以接受 (2)插入和删除的相对频率如何 (3)是否愿意以增加最坏情况下的访问时间为代价优化平均访问时间 用户可能提出哪些类型的查询 对于特定值查找散列索引方案更可取。 对于值范围的查找顺序索引更可取。 通常设计者会使用顺序索引除非他预先知道将来不会频繁使用范围查询。在这种情况下使用散列索引。
B树
InnoDB和MyISAM存储引擎表索引类型必须为BTRERMEMORY表可以根据需要选择HASH或者BTREE类型索引。 B 树非叶子结点和叶子结点都存储数据因此查询数据时时间复杂度最好为 O(1)最坏为 O(log n)。
B树文件组织
索引顺序文件组织最大的缺点是文件增大时性能下降随着文件的增大增加的索引记录所占百分比和实际记录之间变得不协调不得不存储在溢出块中。我们通过在文件中使用B树索引来解决索引查找时性能下降的问题。 而 B 树只在叶子结点存储数据非叶子结点存储关键字且不同非叶子结点的关键字可能重复因此查询数据时时间复杂度固定为 O(log n)。 更进一步可以通过B树的叶结点来组织存放实际记录的磁盘块这样B树不仅作为索引使用而且也是一个文件中的记录的组织者即B树文件组织。B树的叶节点由一条链相连因此当需要进行一次全数据遍历的时候B树只需要使用O(logN)时间找到最小的一个节点然后通过链进行O(N)的顺序遍历即可。 B树文件组织可以用于存储大型数据对象如clob类型或blob类型。 总结来说B树可以降低数据查找性能、可以通过叶结点来组织数据记录。
索引下推
索引下推Index condition pushdown 简称 ICP在 Mysql 5.6 版本上推出的一项用于优化查询的技术。(简单来说就是将索引列相关的判断放到存储引擎去做而无需返回给数据库服务器判断) 在不使用索引下推的情况下在使用非主键索引进行查询时存储引擎通过索引检索到数据然后返回给 MySQL 服务器服务器判断数据是否符合条件。 而有了索引下推之后如果存在某些被索引列的判断条件时MySQL 服务器将这一部分判断条件传递给存储引擎然后由存储引擎通过判断索引是否符合 MySQL 服务器传递的条件只有当索引符合条件时才会将数据检索出来返回给 MySQL 服务器。 索引条件下推优化可以减少存储引擎查询基础表的次数也可以减少 MySQL 服务器从存储引擎接收数据的次数。
索引的设计原则
(1) 【推荐】索引存储结构推荐BTREE InnoDB和MyISAM存储引擎表索引类型必须为BTRERMEMORY表可以根据需要选择HASH或者BTREE类型索引。 (2)【建议】单个表上的索引个数不能超过7个 索引在加速查询的同时也会带来写入速度降低的问题(写入数据的同时要更新索引)。应限制单表上索引个数。 3【建议】利用覆盖索引来进行查询操作 覆盖查询即是查询只需要通过索引即可拿到所需数据而不需要再次回表查询所以效率相对很高。所谓的覆盖索引就是索引上存储的某一条记录索引列的值如果一个查询只需要访问索引中的数据而无需访问实际的表时就可以实现覆盖索引的效果。 (4)【强制】使用联合索引时要遵循最左前缀匹配原则 所谓的联合索引就是基于多个列创建索引也称多码索引、组合索引等。举例来说一个表有A、B、C、D等列如果声明(A,B,C)为一个索引那么这个索引就是一个联合索引。联合索引遵循最左前缀匹配原则。所谓最左前缀匹配的原则就是最左优先在检索数据时从联合索引的最左边开始匹配联合索引的第一个字段必须出现在查询组句中这个索引才会被用到对(A,B,C)的索引数据库会同时际建立了(A)、(A,B)、(A,B,C)三个索引。 基于以上介绍在使用联合索引时为遵循最左匹配原则要将使用最频繁的列放在最左这样就能使用到索引。如上述联合索引A应该是访问频率频率最高的列。 (5) 【推荐】多表关联查询时保证被关联的字段有索引 多表关联查询会引入性能问题如果表的规模很大。对于大数据量场景需要保证被关联的字段有索引。 (6) 【建议】频繁更新的列不建议创建索引 索引不是没有代价的索引在加速数据的检索速度的同时因为需要维护索引对应的实现结构当对表中的数据进行增加、删除和修改的时候会降低增/改/删的执行效率。所以对于需要频繁更新的字段不建议建立索引。如果需要加速这部分数据的查询性能首先考虑业务场景是否合理其次考虑当前表结构设计是否合理看看能否将业务需要的字段变成不频繁更新的列。 7 【强制】使用索引时要考虑索引生效、失效的场景 索引在sql中的where子句、order by子句、join子句、select子句中会生效。但是也要注意索引不生效的情况。常见的索引失效场景有(1) 查询条件中有or。此时必须保证所有or相关的字段都有索引才能生效。所以要谨慎使用or语句。(2) like语句以%开头。模糊查询时使用%且将其放在开头会导致索引失效。(3) 如果存在类型转换(如存储是字符串查询的时候未用引号则会进行类型转换)索引会失效。(4) 索引列参与计算会导致索引失效(如执行算数运算或使用函数)。(5) 违背最左匹配原则。(6) 如果Mysql 评估全表扫描要比使用索引要快则索引失效(数据量很少使用索引不会提升查找性能)。 (8)【建议】如果不明确查询是否使用到索引可使用执行计划判断 执行计划的type列记录扫描情况最好到最差依次是systemconsteq_refrefrangeindexall。其中all表示全表扫描index表示触发了索引。 执行计划除了用来分析查询性能还可以判断查询是否使用到了索引。执行计划的使用可以参考这篇WIKI。
事务
构成单一逻辑工作单元的操作集合称作事务transaction。即使有故障数据库系统也必须保证事务的正确执行——要么执行整个事务要么属于该事务的操作一个也不执行。
事务的ACID特性
数据库需要维护事务的以下四个性质 1.原子性Atomicity事务是一个原子操作由一系列动作组成。事务的原子性确保这一系列动作要么全部完成要么完全不起作用。 2.一致性Consistency隔离执行事务时在没有其他事务并发的情况下保持数据库的一致的数据库状态。事务必须保证数据库的一致性————如果一个事务作为原子从一个一致的数据库状态开始独立地运行则事务结束时数据库也必须再次是一致的(逻辑一致性)。 3.隔离性Isolation并发事务执行之间无影响在一个事务内部的操作对其他事务是不产生影响这需要事务隔离级别来指定隔离性。每个事务都感觉不到系统中其他事务在并发地执行。 4.持久性Durability一旦事务完成数据库的改变必须是永久的即使出现系统故障。
事务的ACID实现
原子性与持久性事务并非总能成功地执行完成。这种事务称为中止(abort)事务。如果要确保原子性中止事务必须对数据库的状态不造成影响。因此中止事务对数据库所做过的任何改变必须撤销。一旦中止事务造成的变更被撤销我们就说事务已回滚rolled back。恢复机制负责管理事务中止。典型的方法是维护一个日志log。每个事务对数据库的修改都会记录到日志中。我们先记录执行修改的事务标识符、修改的数据项标识符以及数据项的旧值修改前的和新值修改后的。然后数据库才会修改。为什么先写日志再写数据库–保证修改可恢复 隔离性与一致性事务处理系统通常允许多个事务并发执行。在数据库中使用并发执行的动机在本质上与操作系统中使用多道程序multiprogramming的动机是一样的。当多个事务并发地执行时可能违背隔离性这导致即使每个事务都正确执行数据库的一致性也可能被破坏。数据库系统必须控制事务之间的交互以防止它们破坏数据库的一致性。数据库系统通过称为并发控制机制保证这一点。 当数据库系统并发地执行多个事务时相应的调度不必是串行的。在多个事务的情形下所有事务共享CPU时间。 在并发执行中通过保证所执行的任何调度的效果都与没有并发执行的调度效果一样我们可以确保数据库的一致性。
事务隔离性级别
在实际应用中数据库中的数据是要被多个用户共同访问的在多个用户同时操作相同的数据时可能就会出现一些事务并发的问题 1.脏读Dirty Read。一个事务读取到另一个事务未提交的数据。 2.不可重复读Non-repeatable Read。一个事务对同一行数据重复读取两次但得到的结果不同。如在两次读取操作之间还有其他的事务更新该行数据。 3.虚读/幻读Phantom Read。一个事务执行两次查询但第二次查询的结果包含了第一次查询中未出现的数据。不可重复读针对的是值的不同幻读针对的是数据条数的不同。 4.丢失更新Lost Update。丢失更新可分为两类分别是第一类丢失更新和第二类丢失更新。第一类丢失更新是指两个事务同时操作同一个数据时当第一个事务撤销时把已经提交的第二个事务的更新数据覆盖了第二个事务就造成了数据丢失。第二类丢失更新是指当两个事务同时操作同一个数据时第一个事务将修改结果成功提交后对第二个事务已经提交的修改结果进行了覆盖对第二个事务造成了数据丢失。简言之丢失更新是指一个事务中的更新被另一个事务中的更新覆盖并根据丢失更新的事务细分为第一类丢失更新和第二类丢失更新。 为了避免上述事务并发问题的出现在标准的 SQL 规范中定义了四种事务隔离级别不同的隔离级别对事务的处理有所不同 1.Serializable可串行化 提供严格的事务隔离。它要求事务序列化执行事务只能一个接一个地执行不能并发执行。此隔离级别可有效防止脏读、不可重复读、幻读、丢失更新。但这个级别可能导致大量的超时现象和锁竞争在实际应用中很少使用。 2.Repeatable Read可重复读 一个事务在执行过程中可以访问其他事务成功提交的新插入的数据但不可以访问成功修改的数据。读取数据的事务将会禁止写事务但允许读事务写事务则禁止任何其他事务。此隔离级别可有效防止不可重复读和脏读但可能存在幻读的问题。 3.Read Committed 已提交读 一个事务在执行过程中既可以访问其他事务成功提交的新插入的数据又可以访问成功修改的数据。读取数据的事务允许其他事务继续访问该行数据但是未提交的写事务将会禁止其他事务访问该行。此隔离级别可有效防止脏读第一类丢失更新(因事务回退导致的丢失)但可能存在不可重复读、幻读、第二类丢失更新(并发修改导致的丢失)的问题。 4.Read Uncommitted 未提交读 一个事务在执行过程中既可以访问其他事务未提交的新插入的数据又可以访问未提交的修改数据。如果一个事务已经开始写数据则另外一个事务不允许同时进行写操作但允许其他事务读此行数据。此隔离级别可防止第一类丢失更新但可能存在脏读、不可重复读、幻读、第二类丢失更新的问题。 以上所有隔离性级别都不允许脏写Dirty Write。 一般来说事务的隔离级别越高越能保证数据库的完整性和一致性但相对来说隔离级别越高对并发性能的影响也越大。因此通常将数据库的默认隔离级别设置为已提交读Committed Read它既能防止脏读又能有较好的并发性能。虽然这种隔离级别会导致不可重复读、幻读和第二类丢失更新这些并发问题但可通过在应用程序中采用悲观锁或乐观锁加以控制。 常见的关系型数据库的默认事务隔离级别采用的是READ_COMMITED例如PostgreSQL、ORACLE、SQL Server和DB2。但是使用InnoDB引擎的MySQL数据库默认事务隔离级别是REPEATABLE_READ。
SQL 一致性约束(完整性约束)有哪几种
NOT NULL: 用于控制字段的内容一定不能为空NULL。 UNIQUE: 控件字段内容不能重复一个表允许有多个 Unique 约束。 PRIMARY KEY: 也是用于控件字段内容不能重复但它在一个表只允许出现一个。 FOREIGN KEY: 用于预防破坏表之间连接的动作也能防止非法数据插入外键列因为它必须是它指向的那个表中的值之一。 CHECK: 用于控制字段的值范围。
并发控制
当数据库中有多个事务并发执行时事务的隔离性不一定能保持。为保持事务的隔离性系统必须对并发事务之间的相互作用加以控制这种控制是通过一系列机制中的一个称为并发控制的机制来实现。
视图
什么是视图
为了提高复杂SQL语句的复用性和表操作的安全性MySQL数据库管理系统提供了视图特性。所谓视图本质上是一种虚拟表在物理上是不存在的其内容与真实的表相似包含一系列带有名称的列和行数据。但是视图并不在数据库中以储存的数据值形式存在。行和列数据来自定义视图的查询所引用基本表并且在具体引用视图时动态生成。 视图使开发者只关心感兴趣的某些特定数据和所负责的特定任务只能看到视图中所定义的数据而不是视图所引用表中的数据从而提高了数据库中数据的安全性。
视图的特点
(1) 视图的列可以来自不同的表是表的抽象和在逻辑意义上建立的新关系。 (2) 视图是由基本表(实表)产生的表(虚表)。 (3) 视图的建立和删除不影响基本表。 (4) 对视图内容的更新(添加删除和修改)直接影响基本表。 (5) 当视图来自多个基本表时不允许添加和删除数据。 视图的操作包括创建视图查看视图删除视图和修改视图。
视图的常见使用场景
(1) 重用SQL语句 (2) 简化复杂的SQL操作。在编写查询后可以方便的重用它而不必知道它的基本查询细节 (3) 使用表的组成部分而不是整个表 (4) 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限 (5) 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。
视图的优缺点
视图的优点是 (1) 查询简单化。视图能简化用户的操作 (2) 数据安全性。视图使用户能以多种角度看待同一数据能够对机密数据提供安全保护 (3) 逻辑数据独立性。视图对重构数据库提供了一定程度的逻辑独立性 视图的缺点 (1) 性能。数据库必须把视图的查询转化成对基本表的查询如果这个视图是由一个复杂的多表查询所定义那么即使是视图的一个简单查询数据库也把它变成一个复杂的结合体需要花费一定的时间。 (2) 修改限制。当用户试图修改视图的某些行时数据库必须把它转化为对基本表的某些行的修改。事实上当从视图中插入或者删除时情况也是这样。对于简单视图来说这是很方便的但是对于比较复杂的视图可能是不可修改的。
这些视图有如下特征1.有UNIQUE等集合操作符的视图。2.有GROUP BY子句的视图。3.有诸如AVG\SUM\MAX等聚合函数的视图。 4.使用DISTINCT关键字的视图。5.连接表的视图其中有些例外
视图使用规范
【建议】尽量不用或少用视图、存储过程、函数、触发器
除非有明确的需求否则不建议使用视图、存储过程、函数、触发器。对 MYSQL 来说 视图、存储过程、函数、触发器 还不是很成熟没有完善的出错记录处理。此外视图、存储过程、函数、触发器会对读写性能产生影响应明确风险后再使用。
存储过程与函数
存储过程是一个预编译的SQL语句优点是允许模块化的设计就是说只需要创建一次以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL使用存储过程比单纯SQL语句执行要快。
存储过程的优缺点
优点 1存储过程是预编译过的执行效率高。 2存储过程的代码直接存放于数据库中通过存储过程名直接调用减少网络通讯。 3安全性高执行存储过程需要有一定权限的用户。 4存储过程可以重复使用减少数据库开发人员的工作量。
缺点 1调试麻烦但是用 PL/SQL Developer 调试很方便弥补这个缺点。 2移植问题数据库端代码当然是与数据库相关的。但是如果是做工程型项目基本不存在移植问题。 3重新编译问题因为后端代码是运行前编译的如果带有引用关系的对象发生改变时受影响的存储过程、包将需要重新编译不过也可以设置成运行时刻自动编译。 4如果在一个程序系统中大量的使用存储过程到程序交付使用的时候随着用户需求的增加会导致数据结构的变化接着就是系统的相关问题了最后如果用户想维护该系统可以说是很难很难、而且代价是空前的维护起来更麻烦。
存储过程的使用
【建议】尽量不用或少用视图、存储过程、函数、触发器
除非有明确的需求否则不建议使用视图、存储过程、函数、触发器。对 MYSQL 来说 视图、存储过程、函数、触发器 还不是很成熟没有完善的出错记录处理。此外视图、存储过程、函数、触发器会对读写性能产生影响应明确风险后再使用。
触发器
触发器是用户定义在关系表上的一类由事件驱动的特殊的存储过程。触发器是指一段代码当触发某个事件时自动执行这些代码。
触发器使用场景
(1) 可以通过数据库中的相关表实现级联更改。 (2) 实时监控某张表中的某个字段的更改而需要做出相应的处理。例如可以生成某些业务的编号。 (3) 注意不要滥用否则会造成数据库及应用程序的维护困难。
触发器的优缺点
优点 (1) 触发器提供了检查数据完整性的替代方法。 (2) 触发器可以捕获数据库层中业务逻辑中的错误。 (3) 触发器提供了运行计划任务的另一种方法。通过使用触发器不必等待运行计划的任务因为在对表中的数据进行更改之前或之后自动调用触发器。 (4) SQL触发器对于审核表中数据的更改非常有用。 缺点 (1) 触发器只能提供扩展验证并且无法替换所有验证。一些简单的验证必须在应用层完成。 例如您可以使用JavaScript或服务器端使用服务器端脚本语言(如JSPPHPASP.NETPerl等)来验证客户端的用户输入。 (2)从客户端应用程序调用和执行SQL触发器不可见因此很难弄清数据库层中发生的情况。 (3) 触发器可能会增加数据库服务器的开销。
触发器的使用
【建议】尽量不用或少用视图、存储过程、函数、触发器
除非有明确的需求否则不建议使用视图、存储过程、函数、触发器。对 MYSQL 来说 视图、存储过程、函数、触发器 还不是很成熟没有完善的出错记录处理。此外视图、存储过程、函数、触发器会对读写性能产生影响应明确风险后再使用。
主从复制
数据库使用 binlog 二进制文件记录了数据可执行的所有 SQL 语句。主从同步的目标就是把主数据库的 binlog 文件中的 SQL 语句复制到从数据库让其在从数据的 relaylog 文件中再执行一次这些 SQL 语句即可。 MySQL主从复制具体流程如下 (1) 在主库上把数据更高记录到二进制日志; (3) 从库创建一个IO线程读取主库的binlog输出线程发送的更新并拷贝这些更新到中继日志; (3) 从库读取中继日志的事件创建一个SQL线程将其重放到从库数据中。
主库的binlog线程——记录下所有改变了数据库数据的语句放进master上的binlog中 从库的io线程——在使用start slave 之后负责从master上拉取 binlog 内容放进自己的relay log中 从库的sql执行线程——执行relay log中的语句
什么是异步复制和半同步
MySQL 的主从复制有两种复制方式分别是异步复制和半同步复制 (1) 异步复制 MySQL 默认的主从复制方式就是异步复制因为 Master 根本不考虑数据是否达到了 Slave或 Slave 是否成功执行。 如果需要实现完全同步方式即 Master 需要等待一个或所有 Slave 执行成功后才响应成功那集群效率可想而知。故 MySQL 5.6 之后出现了一种折中的方式——半同步。 (2)半同步复制 一主一从一主多从情况下Master 节点只要确认至少有一个 Slave 接受到了事务即可向发起请求的客户端返回执行成功的操作。同时 Master 是不需要等待 Slave 成功执行完这个事务Slave 节点接受到这个事务并成功写入到本地 relay 日志中就算成功。 另外在半同步复制时如果主库的一个事务提交成功了在推送到从库的过程当中从库宕机了或网络故障导致从库并没有接收到这个事务的Binlog此时主库会等待一段时间这个时间由rpl_semi_sync_master_timeout的毫秒数决定如果这个时间过后还无法推送到从库那 MySQL 会自动从半同步复制切换为异步复制当从库恢复正常连接到主库后主库又会自动切换回半同步复制。 半同步复制的“半”体现在虽然主从库的Binlog是同步的但主库不会等待从库执行完Relay-log后才返回而是确认从库接收到Binlog达到主从Binlog同步的目的后就返回了所以从库的数据对于主库来说还是有延时的这个延时就是从库执行Relay-log的时间。所以只能称为半同步。
备份与恢复
制定备份计划通过平台实现备份与恢复。 (1) 制定备份计划比如根据数据库的大小进行备份或基于时间进行备份。需要说明的是无论哪种备份方式都会存在一定程度的数据丢失。 (2) 备份恢复时间物理备份恢复快逻辑备份恢复慢。 (3) 如果备份恢复失败需先定位恢复实现原因排查后再重新基于备份数据进行恢复以此重复直到成功为止。
数据库使用
数据库的使用主要是通过SQL的方式实现。
SQL 划分
DDL、DML、DQL、DCL SQL结构化查询语言的分类如下 数据定义语言DDL用于定义表的结构和索引等。 数据操纵语言DML用于插入、删除、更新数据。 数据查询语言DQL用于查询数据。 数据控制语言DCL用于控制数据的访问权限。 此外SQL还包括事务控制语言TCL和系统控制语言TCL等。
DROP、DELETE与TRUNCATE的区别
在不再需要一张表的时候用drop在想删除部分数据行时候用delete在保留表而删除所有数据的时候用truncate。
UNION与UNION ALL的区别
如果使用UNION ALL不会合并重复的记录行效率 UNION 高于 UNION ALL。
IN 和 EXISTS 的区别
in 适合内表比外表数据小的情况exists 适合内表比外表数据大的情况。如果查询的内外表大小相当则二者效率差别不大。
数据类型使用
整数类型
包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT分别表示1字节、2字节、3字节、4字节、8字节整数。任何整数类型都可以加上UNSIGNED属性表示数据是无符号的即非负整数。
int(20)中20的涵义
是指显示字符的长度。20表示最大显示宽度为20但仍占4字节存储存储范围不变 不影响内部存储只是影响带 zerofill 定义的 int 时前面补多少个 0易于报表展示。 对大多数应用这种设计没有意义只是规定一些工具用来显示字符的个数int(1)和int(20)存储和计算均一样。
浮点数类型
包括FLOAT、DOUBLE、DECIMAL。 DECIMAL可以用于存储比BIGINT还大的整型能存储精确的小数。 而FLOAT和DOUBLE是有取值范围的并支持使用标准的浮点进行近似计算。 计算时FLOAT和DOUBLE相比DECIMAL效率更高一些DECIMAL你可以理解成是用字符串进行处理。
FLOAT、DOUBLE
FLOAT类型数据可以存储至多8位十进制数并在内存中占4字节。 DOUBLE类型数据可以存储至多18位十进制数并在内存中占8字节。
字符串类型
包括VARCHAR、CHAR、TEXT、BLOB、BINARY等。
VARCHAR
VARCHAR用于存储可变长字符串它比定长类型更节省空间。 对于VARCHAR来说MySQL限制最大长度是65536但是由于VARCHAR使用额外1或2个字节存储字符串长度。列长度小于255字节时使用1字节表示否则使用2字节表示所以最多能存放的字符个数可能是为65532也可能是65533。 VARCHAR存储的内容超出设置的长度时内容会被截断。
CHAR
CHAR是定长的根据定义的字符串长度分配足够的空间。 CHAR会根据需要使用空格进行填充方便比较。 CHAR适合存储很短的字符串或者所有值都接近同一个长度。 CHAR存储的内容超出设置的长度时内容同样会被截断。
VARCHAR和CHAR选择
使用策略 对于经常变更的数据来说CHAR比VARCHAR更好因为CHAR不容易产生碎片。 对于非常短的列CHAR比VARCHAR在存储空间上更有效率。 使用时要注意只分配需要的空间更长的列排序时会消耗更多内存。 尽量避免使用TEXT/BLOB类型查询时会使用临时表导致严重的性能开销。 VARCHAR 在存取方面与char相反它存取慢因为长度不固定但正因如此不占据多余的空间是时间换空间的做法。 总之结合性能角度char更快和节省磁盘空间角度varchar更小具体情况还需具体来设计数据库才是妥当的做法。
MySQL 中int(10)和char(10)以及varchar(10)的区别
int(10)的10表示显示的数据的长度不是存储数据的大小chart(10)和varchar(10)的10表示存储数据的大小即表示存储多少个字符。 int(10) 10位的数据长度 9999999999占32个字节int型4位 char(10) 10位固定字符串不足补空格 最多10个字符 varchar(10) 10位可变字符串不足补空格 最多10个字符
char(10)表示存储定长的10个字符不足10个就用空格补齐占用更多的存储空间 varchar(10)表示存储10个变长的字符存储多少个就是多少个空格也按一个字符存储这一点是和char(10)的空格不同的char(10)的空格表示占位不算一个字符
TEXT和BLOB和BINARY
不推荐使用(设计上要考虑是否有必要)如果需要使用要注意尽量单独存储。
枚举类型ENUM
把不重复的数据存储为一个预定义的集合。有时可以使用ENUM代替常用的字符串类型。 ENUM存储非常紧凑会把列表值压缩到一个或两个字节。 ENUM在内部存储时其实存的是整数。 尽量避免使用数字作为ENUM枚举的常量因为容易混乱。 排序是按照内部存储的整数。 不建议使用枚举类型推荐使用字符串类型存储以保证可能存在的多数据库支持的场景。
日期和时间类型
包括 year、time、date、datetime、timestamp 等类型。 尽量使用timestamp空间效率高于datetime用整数保存时间戳通常不方便处理。 如果需要存储微秒可以使用bigint存储。
连接
连接的分类
SQL根据是否保留未匹配元组将连接分为外连接和内连接。 外连接通过在结果中创建包含空值元组的方式保留了那些在连接中丢失的元组。实际上有三种形式的外连接 (1) 左外连接(left outer join)只保留出现左外连接运算之前(左边)的关系中的元组。 (2) 右外连接(right outer join)只保留出现右外连接运算之前(右边)的关系中的元组。 (3) 全外连接(full outer join)保留出现在两个关系中的元组。全外连接是左外连接与右外连接类型的组合。 注意不同数据库对全外连接的支持可能不同如MySQL数据不支持全外连接。 内连接也称等值连接。注意关键词inner是可选的当join子句中没有显示使用inner或outer关键字时该连接类型则表示inner。 此外自然连接是内连接的一种特殊形式自然连接连接的是同名属性列而内连接则不要求两属性列同名。对内连接来说可以用using或on来指定某两列字段相同的连接条件。
分页
LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数第一个参数指定第一个返回记录行的偏移量第二个参数指定返回记录行的最大数目。注意初始记录行的偏移量是 0。
聚合
count(*)和count(1)和count(column)使用比较
基于count()函数统计行数时count(*)和count(1)都可以用来统计表中的行数基于SQL规范考虑推荐使用count(*)。注意在使用count(*)时要注意不同存储引擎的支持事务的水平如MyISAM不支持事务使用的锁是表级锁不会有并发的行操作所以查询的结果是准确的。InnoDB支持事务并且支持行级锁行可能被并行修改那么缓存记录不准确。且不加where条件时MyISAM 引擎会直接返回这个总数。 基于count(column_name) 统计指定列值的数目时要注意区分主键字段和非主键字段。因为主键字段非空所以统计的结果和count(1)和count(*)的值一样。且不加where条件时MyISAM 引擎会直接返回这个总数。如果是非主键字段不会统计NULL值所以统计的结果和count(1)和count(*)的值可能不一样。且会基于该字段是否使用了索引决定是否基于索引统计还是全表扫描统计。
数据库优化
百万级别或以上的数据如何删除
如果需要保留的数据比较少的话可以把要保留的数据备份出来。DROP原表重新创建先不要急着创建索引、主键把数据导回去然后在建索引、约束之类的。 如果需要保留的数据很多根据MySQL官方手册可知删除数据的速度和创建的索引数量是成正比的。所以可以先删除索引具体步骤如下 1、先删除索引 2、然后删除其中无用数据 3、删除完成后重新创建索引
数据库CPU飙高问题定位及解决
在分析CPU使用率飙升根因前先介绍下CPU使用率公式
单位时间 CPU 资源 查询执行的平均成本 x 单位时间执行的查询数量 可见CPU使用率与【查询执行的平均成本】和【单位时间执行的查询数量】线性相关而这两项就是我们常说的慢SQL以及数据库QPS。 所以CPU使用率飙升可归纳为以下两点 1、 大量的慢SQL占用了cpu资源拖垮了数据库这类的慢sql常常表现为查询的数据量过大全表扫描、锁抢占甚至死锁、复杂查询等。 2、 QPS过高本质上是数据库的承载的流量过大。
1、QPS过高
如果想判断是否是因为QPS过高导致CPU飙升最好的方式是查看QPS曲线和CPU曲线是否保持一致如果QPS曲线基本和CPU曲线保持一致此时可断定CPU飙升必然存在QPS过高的原因。示例如下 接下来就是确认是哪些SQL的QPS过高。对于MySQL数据库可以通过root用户登录数据库然后执行’SHOW PROCESSLIST’命令查看。 确定了高频SQL(直接使用SHOW PROCESSLIST查看重复SQL的多少也能推断出高频SQL如果无法监测数据库的QPS曲线)接下来就是对业务进行分析确认下为什么会执行如此频繁的调用并给出优化方案。
2、慢SQL
一般情况下数据库都会提供慢查询日志所以只需根据慢查询日志来确定慢SQL接口。以MySQL为例可以通过root用户登录数据库执行SHOW VARIABLES LIKE SLOW_QUERY_LOG%命令来查看慢日志的路径。 在指定路径下获取到慢查询日志后接下来就是分析慢日志确认慢SQL(确认执行时间超过1S的SQL)。
大表怎么优化
某个表有近千万数据CRUD比较慢如何优化 当MySQL单表记录数过大时数据库的CRUD性能会明显下降一些常见的优化措施如下 (1) 限定数据的范围 务必禁止不带任何限制数据范围条件的查询语句。比如我们当用户在查询订单历史的时候我们可以控制在一个月的范围内。 (2) 读/写分离经典的数据库拆分方案主库负责写从库负责读 (3) 缓存使用MySQL的缓存另外对重量级、更新少的数据可以考虑使用应用级别的缓存 (4) 通过分库分表的方式进行优化主要有垂直分表和水平分表。
超大分页或深度分页如何处理
使用 offset limit 在MySQL中分页时随着页数的增加查询性能指数级增大。 这是由于 MySQL 并不是跳过 offset 的行数而是取 offset limit 行然后丢弃前 offset 行返回 limit 行当offset特别大的时候效率就非常的低下。 此处我们就可以采用覆盖索引延迟关联技术来减少偏移量的定位进行优化。
##查询语句
select id from product limit 10000000, 10
##优化方式一
SELECT * FROM product WHERE ID (select id from product limit 10000000, 1) limit 10
##优化方式二
SELECT * FROM product a JOIN (select id from product limit 10000000, 10) b ON a.ID b.id分库分表后面临的问题
事务支持分库分表后就成了分布式事务了。如果依赖数据库本身的分布式事务管理功能去执行事务将付出高昂的性能代价 如果由应用程序去协助控制形成程序逻辑上的事务又会造成编程方面的负担。 (1) 跨库join 只要是进行切分跨节点Join的问题是不可避免的。但是良好的设计和切分却可以减少此类情况的发生。解决这一问题的普遍做法是分两次查询实现。在第一次查询的结果集中找出关联数据的id,根据这些id发起第二次请求得到关联数据。 (2) 跨节点的count,order by,group by以及聚合函数问题 这些是一类问题因为它们都需要基于全部数据集合进行计算。多数的代理都不会自动处理合并工作。解决方案与解决跨节点join问题的类似分别在各个节点上得到结果后在应用程序端进行合并。和join不同的是每个结点的查询可以并行执行因此很多时候它的速度要比单一大表快很多。但如果结果集很大对应用程序内存的消耗是一个问题。 (3) 数据迁移容量规划扩容等问题
(4) ID问题 一旦数据库被切分到多个物理结点上将不能再依赖数据库自身的主键生成机制。一方面某个分区数据库自生成的ID无法保证在全局上是唯一的另一方面应用程序在插入数据之前需要先获得ID以便进行SQL路由。
数据库结构优化
一个好的数据库设计方案对于数据库的性能往往会起到事半功倍的效果。 需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。 (1) 将字段很多的表分解成多个表 对于字段较多的表如果有些字段的使用频率很低可以将这些字段分离出来形成新表。 因为当一个表的数据量很大时会由于使用频率低的字段的存在而变慢。 (2) 增加中间表 对于需要经常联合查询的表可以建立中间表以提高查询效率。 通过建立中间表将需要通过联合查询的数据插入到中间表中然后将原来的联合查询改为对中间表的查询。 (3) 增加冗余字段 设计数据表时应尽量遵循范式理论的规约尽可能的减少冗余字段让数据库设计看起来精致、优雅。但是合理的加入冗余字段可以提高查询速度。 表的规范化程度越高表和表之间的关系越多需要连接查询的情况也就越多性能也就越差。 注意 冗余字段的值在一个表中修改了就要想办法在其他表中更新否则就会导致数据不一致的问题。
SQL优化
SQL优化可以参考数据库访问性能优化一文。简单来说包括如下几个方面 1、减少数据访问减少磁盘访问 (1) 正确的创建并使用索引 2、返回更少数据减少网络传输或磁盘访问 (1) 数据分页处理(减少行数) 客户端分页、服务器分页(内存分页)、数据库分页 (2) 只返回需要的字段(减少列数) 3、减少交互次数减少网络传输 batch 操作 IN LIST优化 设置fetch size 优化业务逻辑 4、减少服务器CPU开销减少CPU及内存开销 使用绑定变量(使用预处理器的能力) 合理使用排序(排序数据的规模可控) 大量复杂运算(如加解密处理)在客户端处理 5、利用更多资源增加资源 客户端多进程并行访问(谨慎使用可能会带来性能问题) 数据库并行处理(需确认数据是否支持一条SQL多个进程处理)
参考
数据库系统概念(第六版) A. Silberschatz H. F. Korth S. Sudarshan著 杨冬青 等译 https://thinkwon.blog.csdn.net/article/details/104778621 MySQL数据库面试题2020最新版 https://blog.csdn.net/adminpd/article/details/122910606 MySQL数据库面试题总结2022最新版 https://db-engines.com/en/ranking DB-Engines Ranking http://mysql.taobao.org/monthly/2021/10/02/ 数据库系统-事物并发控制 Two-phase Lock Protocol https://www.runoob.com/mysql/mysql-data-types.html MySQL 数据类型 https://blog.csdn.net/inrgihc/article/details/114000246 各种开源数据库同步工具汇总 https://zhuanlan.zhihu.com/p/50638495 千万级、百万级数据删除优化 https://blog.csdn.net/Octopus21/article/details/122443762 如何使用 SQL 快速删除数百万行数据 https://juejin.cn/post/7026237038466695175 SQL 约束 https://blog.csdn.net/luyaran/article/details/81019372 mysql触发器之优缺点简介 https://www.cnblogs.com/ivictor/p/15142160.html MySQL中 VARCHAR 可设置的最大长度是多少 https://www.cnblogs.com/alexusli/archive/2009/02/02/1382509.html SQL Union和SQL Union All用法 https://zhuanlan.zhihu.com/p/50564425 MySQL中常用存储引擎有哪些它们相互之间有什么区别 https://zhuanlan.zhihu.com/p/102147497 Mysql各种存储引擎对比总结常用几种 https://blog.nowcoder.net/n/ea5541083f1741dbb790b85119b0f990 【MySQL】一条SQL的执行过程 https://www.cnblogs.com/mengxinJ/p/14045520.html 一条 sql 的执行过程详解 https://zhuanlan.zhihu.com/p/402757921 【MySQL】一、MySQL架构与SQL执行流程 https://zhuanlan.zhihu.com/p/613799143 MySQL高阶知识点(一)SQL语句执行流程 https://blog.csdn.net/qq_43618881/article/details/118657040 一条sql语句在MySQL的执行过程 https://www.cnblogs.com/mengxinJ/p/14045520.html 一条 sql 的执行过程详解 https://blog.csdn.net/qq_41116027/article/details/124135359 sql执行流程概述 https://blog.csdn.net/Leon_Jinhai_Sun/article/details/121573983 MySQL高级 - 查询缓存 - 开启查询缓存 https://www.cnblogs.com/haitaoli/p/10828564.html mysql开启缓存、设置缓存大小、缓存过期机制 https://www.jianshu.com/p/9f9d5142b9f0 面试官说说一条查询sql的执行流程和底层原理 《高性能MySQL》 Baron Scbwartz, Peter Zaitsev, Vadim Tkacbenko 著宁海元周振兴碰立勋翟卫祥 等译 https://www.cnblogs.com/sunjingwu/p/10755823.html MySQL——执行计划
