可以做同城活动的网站,黑龙江省建设教育协会网站首页,北京建网站品牌公司,广告设计公司采购合同SueWakeup 个人主页#xff1a;SueWakeup
系列专栏#xff1a;借他一双眼#xff0c;愿这盛世如先生所愿
个性签名#xff1a;人生乏味啊#xff0c;我欲令之光怪陆离 本文封面由 凌七七~❤ 友情提供 目录
数据库的概念 (什么是数据库)
RDBMS
NOSQL 数据库的分类
… SueWakeup 个人主页SueWakeup
系列专栏借他一双眼愿这盛世如先生所愿
个性签名人生乏味啊我欲令之光怪陆离 本文封面由 凌七七~❤ 友情提供 目录
数据库的概念 (什么是数据库)
RDBMS
NOSQL 数据库的分类
关系型数据库(RDBMS)与(NOSQL)非关系型数据库的比较
主流的NOSQL产品
基础篇
1. SQL数据库结构
2. SQL语句分类
3. SQL语句的执行流程
4. 存储引擎 MyISAM 和 InnoDB的区别
5. MySQL设计三大范式
6. 什么是 SQL 注入
7. 内连接和外连接的区别 AND 左连接和右连接的区别
8. Union 和 Union all 的区别
9. MySQL 如何取差集
10. Delete 和 Truncate的区别
11. Count(*) 、Count(1)、Count(column) 的区别
12. 模糊查询使用什么关键词 %和 _有什么区别
13. 慢查询的概念
14. varchar和char的区别
视图篇
15. 视图的理解
16. 视图的缺点 索引篇
17. 什么是索引
18. 索引的特性
19. 索引的分类
20. 索引的优点及缺点
优点
缺点
21. 如何添加/ 删除索引(index) 主键索引 (primary key)
唯一索引 (unique)
普通索引 (index)
全文索引 (fulltext)
联合索引 (复合/多列索引)
22. 主键索引和唯一索引的区别
23. 什么是联合索引什么情况下会触发
24. 如何添加/ 删除约束(constraint)
主键约束 (primary key)
唯一约束 (unique)
外键约束 (foreign key)
非空约束 (not null)
检查约束 (check)
25. 索引和约束的关系及区别
26. BTree 索引MySQL 5.5 之后默认
26.1 B树执行查找操作
27. B树和B树的区别
28. MySql为什么使用B树不用红黑树
29. 哈希索引
30. 什么是回表
31. 索引覆盖
好处
措施
32. 索引的使用场景
33. 索引的失效场景
34. 索引的优化
35. SQL优化 事务篇
36. 什么是事务
37. MySQL 事务的四大特性
38. NOSQL 的 BASE 理论
39. MySQL 实现事务的步骤
40. 数据库事务的实现原理
41. 数据库并发事务会带来哪些问题
脏读
不可重复读
丢失修改
幻读
42. 不可重复度和幻读的区别
43. 事务的隔离级别
读未提交
读已提交
可重复读
串行化
锁篇
44. MySQL中的锁
45. 悲观锁和乐观锁的区别
悲观锁
乐观锁
46. 什么是MVCC
存储过程篇
47. 存储过程
48. 为什么使用存储过程
49. 存储过程和函数的区别
50. 存储过程的缺陷
手机端浏览本文章可能会出现 “目录”无法有效展示的情况还请谅解 数据库的概念 (什么是数据库) 长期存放在计算机内有组织、可共享的大量数据的集合是一个数据“仓库”。 数据库分为关系型数据库和非关系型数据库MySQL的特点操作便捷、小巧且功能齐全、免费开源的数据库可运行于 windows 或 linux 系统 RDBMS 全称 “Relational Database Management System”意为 “关系型数据库管理系统”。 使用 SQL结构化查询语言进行数据的查询和管理将数据组织为表格每个表格包含多行和多列记录与字段。 NOSQL 全称 “Not Only SQL”,意为 “不局限于 SQL”。 通常对数据进行非规范化处理用于大规模数据应用特别是分布式数据存储和大数据应用。 数据库的分类 数据库分类数据模型举例关系型RDBMS表格Oracle、MySQL、SQL Server等非关系型NoSQL键值对K-V、文档Document、列族Column Family、图形MongoDB、Redis、Elasticsearch(引擎)、Cassandra等对象型 以对象作为基本单位 支持面向对象的数据建模和操作 db4o、Versant等XMLXML文档eXist、BaseX等图形 图形结构数据 适用于网络关系、社交网络等场景 Neo4j、FlockDB等内存存储在内存中SAP HANA、MemSQL等时间序列存储时间序列InfluxDB、Prometheus等 关系型数据库(RDBMS)与(NOSQL)非关系型数据库的比较 NoSQL关系型数据库优点 简单易部署低成本查询速度快数据存储于缓存中存储数据多样扩展性好 技术又成熟又早提供丰富的技术支持和生态系统标准化查询语句对数据的操作和管理更方便灵活支持事务管理确保数据的一致性、完整性和持久性存储基础数据对数据的管理直观简单缺点 相较于关系型数据库发展时间短维护工具和资料有限不遵循 SQL 标准提升了学习和使用成本不提供关系型数据库对事务的处理 相较于 NoSQL 成本高存储在磁盘中速度不及 NoSQL仅能存储基础数据类似 join 的多表查询机制限制了扩展性 主流的NOSQL产品 键值对K-V存储列族存储文档型存储图形数据库相关产品Redis、Voldemort、Berkeley DBCassandraHBase,RiakMongoDB、Elasticsearch、CouchDBNeo4j、InfoGrid、Infinite Graph典型应用内容缓存主要用于处理大量数据的高访问负载分布式的文件系统Web应用与 K-V 类似V是结构化的社交网络数据模型一系列键值对以列族式存储将同一列数据存在一起一系列键值对图结构优势快速查询查找速度快可扩展性强更容易进行分布式扩展数据结构要求不严格利用图结构相关算法劣势存储的数据缺少结构化功能相对局限缺乏统一的查询语法需要对整个图做计算才能得出结构不容易做分布式的集群方案 基础篇 1. SQL数据库结构 数据库Database以文件的形式存放在磁盘上即对应于一个或多个物理文件数据表Table由一组数据记录组成数据库中的数据以表为单位进行组织字段(Field)也称域。表中的每一类称为一个字段。每个字段都有相应的描述信息。记录(Record)表中的每一行称为一个记录由若干字段组成。索引(Index)一种特殊类型的表其中含有关键字段的值和指向表实际记录位置的指针可以提高访问数据库的效率。SQL语句结构化查询语句命令用于存取数据以及查询、更新和管理关系数据库系统同时也是数据库脚本文件的扩展名。 2. SQL语句分类 名词解释命令 DDL 数据定义语言) 定义和管理数据对象 如数据库数据表等 create、drop、alter DML 数据操作语言 用于操作数据库对象中所包含的数据 insert、update、delete DQL 数据查询语言 用于查询数据库数据select DCL 数据控制语言 用来管理数据库的语言包括管理权限及数据更改 grant、commit、rollback 3. SQL语句的执行流程 MySQL请求执行流程示意图 词法/语法解析Parser在收到用户发送的SQL请求后将请求中的SQL语句按照语法规则分割成一个个单词tokens。例如SELECT、FROM、WHERE、AND、OR等都是一个单词。对单词进行语法解析判断SQL语句是否符合语法规则。如果不符合则会返回 “语法错误” 的结果。 MySQL语法解析错误返回结果图 语义分析Resolver对SQL语句进行语义分析如表名、列名、索引类型等确保它们都是正确的。如果有错误会抛出语义错误错误的表名、列名、数据类型不匹配、引用不存在的对象、违反了数据库的约束等。 MySQL语义解析错误返回结果图其一 逻辑改写Transformer采用不同的改写策略基于规则 / 基于代价对用户的请求字符串进行改写并生成多个版本的执行计划使用成本模型估计每种改写后的执行计划的成本最终让优化器选择成本最低的执行计划来执行查询。优化器Optimizer对SQL语句进行优化如选择最优的执行计划、使用索引等来提高查询效率。代码生成器Code Generator将优化器生成的执行计划转换为可执行代码。执行SQLExecutor根据优化后的执行计划执行SQL语句。执行过程中会进行锁定、缓存、排序、分组等操作。执行计划缓存Plan CacheSQL执行引擎将SQL第一次生成的执行计划缓存在内存中后续对该SQL的重复执行可以复用这个计划避免重复查询优化的过程。 4. 存储引擎 MyISAM 和 InnoDB的区别 MyISAMInnoDB外键不允许创建外键支持外键事务不支持事务事务型数据库引擎可以使用 Commit 和 Rollback 语句并发只支持表级锁(table-level locking)支持行级锁(row-level locking)和表级锁默认为行级锁备份(缓存)只缓存索引不缓存真实数据支持在线热备份(缓存索引和真实数据)崩溃恢复MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多而且恢复的速度也更慢。 5. MySQL设计三大范式 数据库设计范式是数据库在设计过程中需要遵守的设计准则避免数据冗余 特殊情况不一定要三个范式都满足数据库设计看重需求与性能需求性能表结构 第一范式1NF每个字段都是最小字段具有原子性不可再分 确保每列保持原子性第二范式2NF每张表必须存在主键并且其它字段也必须与主键直接依赖不能仅仅与主键的某一部分依赖联合主键 确保表中的每列都和主键相关第三范式3NF每个字段不能与主键间接依赖每张表只保存一种数据表和表之间使用外键关系关联 确保每列都和主键列直接相关而不是间接相关 6. 什么是 SQL 注入 攻击者通过用户输入的字符串内容中加入 SQL 语句与程序中的原 SQL 语句之间产生拼接形成恶意查询、非法命令执行等攻击方式避免方式过滤输入内容和使用参数化传值占位符 7. 内连接和外连接的区别 AND 左连接和右连接的区别 内连接是保证两个表中所有行都满足连接条件,join不加前缀默认为 inner join外连接结果仅包含符合连接条件的行外连接不仅包含符合连接条件的行还包含左表、右表或两个连接表中的所有数据行 外连接分为①左外连接、②右外连接、③全外连接 左外连接保存左表全部数据右表保留符合条件的行数据 left outer join || left join 右外连接保存右表全部数据左表保留符合条件的行数据 right outer join || right join 全外连接保留所有行数据 full outer join || full join 8. Union 和 Union all 的区别 union 是对两个结果集进行并集操作不包括重复行同时进行默认规则的排序union all 也是对两个结果集进行并集操作包括重复行不会对结果进行排序 9. MySQL 如何取差集 当对比的字段存在索引时使用 NOT EXISTS效率高当对比的字段没有索引时使用 left join || right join效率高 10. Delete 和 Truncate的区别 Delete语句后可写条件Truncate不可用Delete语句是逐条记录删除且删除的记录都会写入日志Truncate一次性删除整个数据页日志只记录页释放Delete删空表后会保留一个空的数据页Truncate 在表中不会留有任何数据页当使用行级锁执行 Delete语句时将锁定表中各行以便删除Truncate始终锁定页和表如果有identity产生的自增id列Delete后仍会从上次的数字开始增加种子不变而Truncate后种子会恢复初始 11. Count(*) 、Count(1)、Count(column) 的区别 执行方式 count(1)计算当前查询结果存在多少个常量值 1;count(*)会把*号替换为所有字段名用于计算查询结果中每个字段存在多少个值;count(column)会统计指定列中非NULL值的行数执行效率 如果存在主键count(主键)效率最高其次是 count(column) count(1) count(*) 12. 模糊查询使用什么关键词 %和 _有什么区别 like 关键词默认是大小写敏感如果需要进行不区分大小写的模糊查询可以使用collate 子句指定不区分大小写的字符集或者使用 lower 或 upper 函数将列值转换为小写或大写进行比较%表示零个或多个字符。当使用%通配符时它可以匹配任意长度的字符串包括空字符串 例如WHERE column_name LIKE abc%会匹配以abc开头的任意字符串。 _表示一个字符。当使用_通配符时它可以匹配一个任意字符的位置 例如WHERE column_name LIKE a_c会匹配a后面跟着一个任意字符然后是c的字符串。 13. 慢查询的概念 MySQL默认10秒内没有响应SQL结果则为慢查询MySql对慢查询的操作 -- 显示到mysql数据库的连接数
show status like connections;
-- 查询慢查询的状态
Show variables like %slow_query%;
-- 设置慢查询到表 mysql.slow_log
set global log_outputTABLE;
-- 设置慢查询的时间
set global long_query_time3;
-- 开启慢查询
set global slow_query_logON;
-- 慢查询的次数
show status like slow_queries;
-- 慢查询记录
select * from mysql.slow_log;
-- 慢查询sql语句
select convert(sql_text using utf8) sql_text from mysql.slow_log
-- 关闭慢查询
set global slow_query_logOFF; 14. varchar和char的区别 char类型的长度固定,如果存储的字符长度小于指定长度将会用空格进行填充查询时可以更快定位varchar根据实际字符串来动态分配存储空间最大长度是创建表时指定的需要进行额外的长度判断 视图篇 15. 视图的理解 视图是一个虚拟表数据来自一个表或多个表表中的数据发生改变显示在视图中的数据也会发生改变视图用于隐藏敏感数据视图可降低复杂的查询易于理解和使用 16. 视图的缺点 缺点描述性能下降在查询视图时数据库系统需要执行视图的定义以生成结果集这可能会导致性能下降更新限制某些视图是不可更新的特别是当视图的定义中包含了聚合函数、group by 子句或 distinct 关键字时。即使视图是可更新的更新操作也可能会受到限制因为数据库系统需要确保更新操作不会破坏视图的定义增加依赖性和耦合性使用视图可能会增加数据库对象之间的依赖性和耦合性。当视图被其他查询或存储过程引用时修改视图的定义可能会影响到引用该视图的其他对象导致系统的复杂性增加查询透明度降低视图隐藏了底层表的细节使得查询的透明度降低。当开发人员查询视图时可能不清楚视图是如何定义的从而增加了理解和调试的难度增加维护成本在数据库中添加、删除或修改视图可能会增加维护成本。特别是当数据库模式发生变化时需要确保视图的定义与底层表的结构保持一致否则可能会导致查询失败或产生不正确的结果 索引篇 17. 什么是索引 索引是一种用于快速查询和检查数据的数据库存储结构保存了数据库指定字段的数据位置MySQL 最经常用的存储结构 BTree 和 HashTable 作用提升数据库的查询性能如果没有索引数据库的查询会进行全表搜索消耗时间造成大量磁盘的IO操作如果建立索引则通过索引中所保存的数据位置快速找到表中的对应记录 18. 索引的特性 高效性利用索引可以提高数据库的查询效率唯一性索引可以确保所查的数据的唯一性完整性加速表和表之间的连接实现表与表之间的参照完整性 19. 索引的分类 分类方式分类描述存储方式BTree索引InnoDB 存储引擎的 BTree 索引分为主键索引和辅助索引哈希索引自适应哈希索引逻辑主键索引主键列使用索引辅助索引唯一索引保证该数据列的唯一性允许数据为Null但不能出现重复数据一张表允许创建多个唯一索引普通索引为了快速查询数据一张表允许创建多个普通索引允许数据重复和 Null前缀索引只适用于字符串类型的数据对文本的前几个字符创建索引相比普通索引建立的数据更小全文索引为了检索大文本数据中的关键字的信息是目前搜索引擎数据库使用的一种技术使用字段单列索引针对单个列创建的索引当查询条件只涉及单列时可以有效提高查询的性能组合索引针对多个列创建的索引当查询条件涉及到多个列时可以提供更好的性能查询时必须按照索引的顺序提供条件 20. 索引的优点及缺点 优点 加快数据的检索速度减少数据库需要扫描的数据行数通过创建唯一索引可以保证数据库表中每一行数据的唯一性 缺点 创建索引和维护索引需要耗费许多时间对表中数据进行增删改的时候如果数据有索引索引也需要动态的修改降低SQL的执行效率索引需要物理文件存储耗费一定空间如果数据库的数据量比较小那么使用索引也不能带来很大提升 21. 如何添加/ 删除索引(index) 约束实际上就是表中数据的限制条件,目的是为了保证表中的记录完整和有效 主键索引 (primary key) 确保表中每一行都有一个唯一的标识符可在其他表中作为外键引用建立表与表之间的关系 -- 创建表后添加主键索引
alter table 表名 add primary key(字段字段1...);
-- 移除主键
alter table 表名 drop primary key; 唯一索引 (unique) unique 约束的字段具有唯一性不可重复但可以为null -- 创建表后添加唯一索引
alter table 表名 add unique(字段字段1...);
-- 删除唯一索引
alter table 表名 drop index 唯一索引名; 普通索引 (index) 允许列中有重复的值不会限制数据的插入提高查询性能尤其是在经常需要搜索的列上 -- 创建表后添加普通索引
alter table 表名 add index 索引名 (字段);
-- 删除普通索引
alter table 表名 drop index 索引名;全文索引 (fulltext) 只能用于CHAR、VARCHAR或TEXT类型的列用于全文搜索适用于文本搜索如文章、博客等提供了强大的文本匹配和搜索功能特别是使用MATCH AGAINST操作时 -- 创建表后添加全文索引
alter table 表名 add fulltext(字段,字段1...);
-- 移除全文索引
alter table 表名 drop index 全文索引名;联合索引 (复合/多列索引) 在数据库表中的两个或更多列上创建的索引按照索引创建时定义的列的顺序存储索引值并且通常遵循最左前缀原则提高多条件查询的性能(特别是当查询中包含索引的所有列或前导列时) -- 创建表后添加联合索引
alter table 表名 add index (字段,字段1,字段2...);
-- 移除联合索引
alter table 表命 drop index 联合索引名; 22. 主键索引和唯一索引的区别 定义数量每张表只能有一个主键而可以有多个唯一索引。主键是用来唯一标识表中的每一行记录的而唯一索引可以用来保证某一列或多列的取值唯一。索引特性主键索引是一种特殊的唯一索引它具有自动递增的特性如果是整数类型并且不允许为空。主键索引在物理存储上一般会被优化为聚簇索引即按照主键值的顺序将数据存储在磁盘上。而唯一索引没有这些特性可以包含空值。查询性能由于主键索引的特殊性当使用主键进行查询时MySQL可以直接定位到相应的行因此查询速度较快。而唯一索引在查询时需要进行额外的查找操作。重复值主键索引要求列的值必须唯一且非空而唯一索引则允许有重复值但对于重复值的插入操作会失败。外键关联主键索引通常被用作外键关联的参考而唯一索引可以作为外键的参考但不是必须的。 23. 什么是联合索引什么情况下会触发 在MySQL中联合索引也称为复合索引或多列索引是指将多个列组合起来创建的一个索引。它可以包含多个列并且查询时可以使用这些列的任意组合进行查找当查询语句的条件涉及到联合索引的所有列或部分列时MySQL会尝试使用联合索引来加速查询减少扫描的数据量。具体触发联合索引的情况包括 查询条件中涉及到联合索引的所有列如果查询语句的条件中使用了联合索引的所有列并且按照索引列的顺序使用MySQL可以高效地使用联合索引进行查找查询条件中涉及到联合索引的部分列如果查询语句的条件中使用了联合索引的部分列并且按照索引列的顺序使用MySQL也可以部分利用联合索引进行查找。这种情况下MySQL会先根据索引找到满足条件的记录然后再进行进一步的过滤 当查询条件中只使用了联合索引的某个列而没有使用其他列时并不会触发联合索引的使用。此时MySQL可能会选择其他适用的索引或者进行全表扫描 24. 如何添加/ 删除约束(constraint) 主键约束 (primary key) 与主键索引类似 唯一约束 (unique) 与唯一索引类似 外键约束 (foreign key) 维护表与表之间的关系确保数据的引用完整性防止在子表中插入不存在的父表中的值 -- 创建表后添加外键约束
alter table 表名 add foreign key(字段) references 父级表名(父级表字段);
-- 移除外键约束
alter table 表名 drop foreign key 外键名; 非空约束 (not null) 用于保证数据的完整性防止出现无效的数据确保列中的数据不能有NULL值必须提供非空的数据 -- 创建表后添加非空约束且添加约束字段不能存在 NULL 值否则失败
alter table 表名 modify 字段 字段类型 not null;
-- 移除非空约束
alter table 表名 modify 字段 字段类型 null;检查约束 (check) 从 MySQL 8.0.16 开始支持用于限制列中可以接受的值比如年龄不能为负数允许定义列中的值的范围或条件确保数据的合法性 -- 创建表后添加检查约束
alter table 表名 add check (字段限制范围);
-- 移除检查约束
alter table 表名 drop constraint DROP 检查约束名 25. 索引和约束的关系及区别 索引约束关系 主键约束primary key和唯一约束unique会自动创建索引以确保数据的唯一性和提高查询效率。 外键约束foreign key通常不会自动创建索引但在实际应用中为了提高查询性能通常建议在外键列上手动创建索引。区别目的主要用于提高查询性能。索引可以快速定位到表中的数据减少全表扫描的次数从而加快查询速度。主要用于保证数据的完整性和一致性。类型包括主键索引、普通索引、唯一索引、全文索引、联合索引等。包括主键约束、外键约束、唯一约束、非空约束和检查约束等。创建使用可以在创建表时定义也可以在表创建后通过alter table语句或create index语句添加或删除。通常在创建表时定义也可以在表创建后通过alter table语句添加或删除。必要性是可选的但通常为了提高性能而创建。并非所有列都需要索引过多的索引可能会降低插入、更新和删除操作的性能。是数据库设计的重要组成部分用于确保数据的准确性和可靠性。 26. BTree 索引MySQL 5.5 之后默认 因为 B Tree 的有序性所以除了用于查找还可以用于排序和分组InnoDB的BTree索引分为主键索引和辅助索引主键索引的叶子节点 data域记录着完整的数据记录原则尽量选择访问频率高的字段值作为主键索引辅助索引的叶子节点data域记录着主键的值因此在使用辅助索引进行查找时需要先查找到主键值然后再到主键索引中进行查找 26.1 B树执行查找操作 进行查找操作时首先在根节点进行二分查找找到对应的叶子节点。然后在叶子节点上进行二分查找找出 key 所对应的 data区间查找操作时由于叶子节点形成了有序列表可以直接通过指针继续遍历相邻个叶子节点提高区间查询效率 27. B树和B树的区别 区别B树B树节点结构B树的节点除了包含关键字和子节点指针外还包含了数据记录的指针B树的非叶子节点只包含关键字和子节点指针所有的数据记录都存储在叶子节点中叶子节点链表B树的叶子节点并没有形成一个链表而是通过非叶子节点来进行定位B树的叶子节点之间形成了一个有序的链表便于范围查询和遍历整个表查询方式B树可以直接从非叶子节点开始查找然后按照关键字顺序遍历叶子节点B树必须从根节点到叶子节点遍历整棵树才能找到所需的数据记录磁盘读写B树需要遍历整棵树B树的叶子节点之间形成了一个有序的链表因此在进行范围查询时只需要遍历叶子节点链表即可使用场景数据量较小或者需要支持随机访问需要支持范围查询或者大数据量的索引 28. MySql为什么使用B树不用红黑树 区别红黑树B树结论磁盘读写需要进行随机读取导致磁盘IO次数较多B 树的叶子节点形成了一个有序链表可以通过顺序读取的方式高效地进行范围查询和遍历由于数据库系统通常面对大量的数据和频繁的查询操作B 树的磁盘读写优化能够提升数据库的整体性能数据有序性红黑树没有对数据进行排序只是通过平衡性质来保证树的高度平衡将数据按照关键字排序存储在叶子节点中并通过非叶子节点进行定位这种有序性使得 B 树在范围查询、排序等操作上具有更好的性能持久化存储红黑树由于需要存储更多的指针信息导致节点的大小相对较小增加了磁盘IO次数降低了写入性能B 树的节点结构相对简单每个节点存储的关键字数量较多使得每次写入磁盘的数据量较大减少了磁盘IO次数数据库系统需要将索引结构持久化地存储到磁盘上以防止系统故障时数据丢失 29. 哈希索引 能以O(1)时间复杂度进行查找但是失去了有序性无法用于排序和分组只支持精确查找无法用于部分查找和范围查找InnoDB存储引擎有一个特殊的功能叫”自适应哈希索引“当某个索引值被使用的非常频繁时会在BTree索引之上创建一个哈希索引让BTree索引具有哈希索引的一些优点 30. 什么是回表 在使用索引进行查询时如果查询需要返回的数据不在索引中MySQL会根据索引中的数据行的主键值再次到表中取检索数据 31. 索引覆盖 一个查询可以完全使用索引来满足而无需访问实际的数据行 好处 减少磁盘 IO从索引中获取不需要回表访问实际的数据行减少内存开销当查询只涉及到索引列MySQL只需要将索引数据加载到内存中减少了网络传输开销当数据库和应用程序分布在不同的服务器上时索引覆盖可以减少从数据库服务器到应用服务器之间的网络传输开销 措施 使用合适的查询语句编写查询语句时明确指定需要返回的列并确保这些列都包含在索引中。避免使用 select *它可能无法实现索引覆盖合理涉及索引确保索引包含需要的所有列尽量覆盖查询所需的列 32. 索引的使用场景 匹配全值对索引中所有列都指定具体值即对索引中的所有列都有等值匹配的条件。匹配值的范围查询对索引的值能够进行范围查找匹配最左前缀仅仅使用索引中的最左边列进行查询。比如组合索引col1,col2,col3能够被col1col1col2col1col2col3的等值查询利用到的。仅对索引进行查询当查询列都在索引字段中。即select中的列都在索引中。匹配列前缀仅仅使用索引的第一列并且只包含索引第1列的开头部分进行查找。例如WHERE title LIKE ‘xxx%’索引部分等值匹配部分范围匹配若列名是索引则使用column_name is null就会使用索引 33. 索引的失效场景 使用模糊查询时%在字符的左侧组合索引包含从左到右的字段使用索引不包含左边的字段索引失效数据类型不匹配不等于运算! 、 、、not in字段内容为 null添加索引的字段上使用函数或计算or前后条件中的字段都包含索引或前后有一个字段不包含索引 34. 索引的优化 选择合适的字段创建索引被频繁更新的字段应该慎重建立索引尽可能考虑建立联合索引而不是单列索引避免冗余索引考虑在字符串类型的字段上使用前缀索引代替普通索引避免 where子句中对索引字段使用函数这会造成索引失效 35. SQL优化 在 SQL查询中禁止使用 select *必须明确查询字段名称对查询进行优化尽量避免全表扫描应考虑在 where 及 order by 涉及到的字段上建立索引尽量避免在 where子句上对字段进行 null值判断否则会导致引擎放弃使用索引进行全表扫描避免在where子句中使用 !或操作符避免在where子句中使用or连接条件慎用 in和not in否则会导致全表扫描严禁左模糊查询或者全模糊查询因为索引采用 B-Tree存储具有B-Tree的最左前缀匹配特性尽量避免在 where子句中对字段进行算术运算操作尽量避免在where子句中对字段进行函数操作使用 explain查询语句观察SQL执行计划 事务篇 36. 什么是事务 事务是数据库的一种特性用于确保一个执行过程中所有步骤全部成功或者全部失败是数据库操作的最小执行单元 37. MySQL 事务的四大特性 ACID vs BASE 原子性事务是数据库的最小执行单元事务的原子性确保数据库操作过程中的所有步骤全部成功或者全部失败一致性数据修改前和修改后的状态保持一致隔离性一个事务的执行不会被其他事务干扰一个事务内部的操作和使用的数据在并发中对其他事务是隔离的持久性事务一旦被提交将对数据库中的数据的改变是永久性的 38. NOSQL 的 BASE 理论 BASE是对ACID原子性、一致性、隔离性、持久性事务特性的一个补充反映了NoSQL 数据库在设计上对可用性和一致性取舍的不同。基本可用 系统在面临任何故障时仍然能够提供一定程度的服务。即使是在高负载或系统故障的情况下系统也能够响应客户端的请求尽管响应可能不是完全正常或快速的 软状态 数据的状态可以在多个节点之间同步而不需要立即保持一致。数据可以在多个节点上存在短暂的差异最终会通过某种机制如网络同步、消息队列等达到一致 最终一致性 系统不保证数据在任意时刻都是正确的而是保证在一定时间后数据会收敛到正确的状态 核心思想在分布式系统中由于网络延迟、节点故障等因素数据的一致性可能无法实时保证但是可以保证数据的最终一致性。 39. MySQL 实现事务的步骤 -- 关闭事务自动提交
set autocommit0;
-- 开启一个事务标记事务的起始点
start transaction
-- 向数据库提交事务
commit;
-- 将事务回滚所有数据库操作被取消
rollback;
-- 开启mysql自动提交
set autocommit1; 40. 数据库事务的实现原理 MySQL InnoDB引擎使用 redo log重做日志保证事务的的持久性使用 undo log回滚日志来保证事务的原子性redo log 是 InnoDB存储引擎层的日志又称重做日志文件用于记录事务操作的变化记录的是数据修改之后的值。当MySQL意外宕机InnoDB存储引擎会使用 redo log恢复以此来确保数据的持久性undo log保存了事务发生之前的数据的一个版本用于回滚MySQL InnoDB引擎通过锁机制、MVCC多版本并发控制等手段保证事务的隔离性保证了事务的持久性、原子性、隔离性之后一致性才能得到保障 41. 数据库并发事务会带来哪些问题 脏读 一个事务正在访问数据并且对数据进行了修改而修改操作还没提交到数据库中这时另外一个事务也访问这个数据然后使用这个数据。由于这个数据是没有提交的数据那么另外一个事务读到的这个数据就是“脏数据” 不可重复读 一个事务内多次读同一个数据在这个事务还没结束时另外一个事务也访问该数据在第一个事务中的两次读数据之间由于第二个事务的修改导致第一个事务读取到的数据可能不一致这就发生了在一个事务内两次读到的数据是不一致的情况所以称为不可重复读 丢失修改 一个事务读取一个数据时另外一个事务也访问这个数据在第一个事务修改这个数据后第二个事务也修改了这个数据导致第一个事务的修改丢失 幻读 与不可重复读类似在第一个事务读取了几行数据另一个事务插入了一些数据在随后的查询中第一个事务发现多了一些原本不存在的记录 42. 不可重复度和幻读的区别 不可重复读的重点是修改多次读取一条记录发现其中某些列的值被修改幻读的重点在于新增或删除比如多次读取某条件下的记录发现记录增多或减少 43. 事务的隔离级别 读未提交 最低的隔离级别允许读取尚未提交的数据变更可能导致 脏读、幻读或不可重复读 读已提交 允许读取并发事务已经提交的数据可以阻止脏读 可重复读 对同一字段的多次读取结果都是一致的除非数据是被本身事务自己所修改可以阻止脏读和不可重复读 串行化 最高的隔离级别完全服从 ACID 的隔离级别所有的事务依次逐个执行防止事务之间产生干扰可阻止脏读、不可重复度以及幻读 锁篇 44. MySQL中的锁 意向锁是InnoDB存储引擎特有的概念它们是表级锁用于表示事务对表中的行有意向进行共享或排他锁定。意向锁不会阻塞其他事务对表的访问除非它们也尝试在同一行上获取排他锁。 45. 悲观锁和乐观锁的区别 悲观锁 总是假设最坏的情况每次读取数据都默认其他事务会修改数据会进行加锁操作操作之后释放锁 乐观锁 一般情况不会造成冲突在数据进行提交更新时才会对数据的冲突与否进行检查一般是通过版本号控制适用于读多写少的场景 46. 什么是MVCC MVCC是行级锁的一个变种在很多情况下避免了加锁情况开销更低。大多数的MVCC都实现了非阻塞的读操作写操作也只锁定必要的行MVCC是一种用来解决 读-写冲突的并发控制为事务分配单向增长的时间戳为每个修改保存一个版本每个事务都有一个对应版本的快照快照版本按照单向增长的时间戳来决定先后顺序读操作只需要读该事务开始前的数据库快照并不去读取正在修改的数据仅读取事务开始前的最新版本 存储过程篇 47. 存储过程 在大型数据库系统中一组为了完成特定功能的SQL语句集存储在数据库中经过第一次编译后再次调用不需要重复编译用户通过指定存储过程的名字并给出参数来执行它 delimiter //
create procedure 存储过程名([ [in | out | inout] 参数名 参数类型],...
)
begindeclare 变量 变量类型;-- 存储逻辑 ...
end;
//
delimiter ;查看存储过程 SELECT * FROM information_schema.routines WHERE routine_schema 库名;删除存储过程 drop procedure 存储过程名;48. 为什么使用存储过程 业务流程复杂业务复杂时SQL语句相互依赖顺序执行频繁访问数据库每条SQL语句都需单独连接和访问数据库先编译后执行SQL语句的执行需要先编译 49. 存储过程和函数的区别 要素存储过程函数关键字procedurefunction执行可以独立执行必须依赖表达式的调用返回值可以定义多个返回结果只有一个返回值功能函数不易做复杂的业务逻辑但是存储过程可以 50. 存储过程的缺陷 移植性大多数关系数据库的存储过程存在细微差异维护性存储过程的维护成本高修改调试较为麻烦协作性团队中对于存储过程的使用大多是依赖文档
