【功能模块】  SQL功能【操作步骤&问题现象】1、编写SQL2、报错，见报错截图一补充说明，T1、T2、这2张表都有数据，且这2张表的数据都能关联上。再经过测试，发现T1表带上where条件就会报错（不带where条件能正常查询），见报错截图二【截图信息】报错截图一报错截图二【日志信息】（可选，上传日志内容或者附件）

本文对SQL程序语言有四种操作语言做了一个简单的介绍和概括，对数据库的基本操作都属于这四类，它们分别为;数据定义语言(DDL)、数据查询语言(DQL)、数据操纵语言(DML)、数据控制语言(DCL) 。前言SQL程序语言有四种类型，对数据库的基本操作都属于这四类，它们分别为;数据定义语言(DDL)、数据查询语言(DQL)、数据操纵语言(DML)、数据控制语言(DCL)数据定义语言(DDL)DDL全称是Data Definition Language，即数据定义语言，定义语言就是定义关系模式、删除关系、修改关系模式以及创建数据库中的各种对象，比如表、聚簇、索引、视图、函数、存储过程和触发器等等。数据定义语言是由SQL语言集中负责数据结构定义与数据库对象定义的语言，并且由CREATE、ALTER、DROP和TRUNCATE四个语法组成。比如：--创建一个student表 create table student( id int identity(1,1) not null, name varchar(20) null, course varchar(20) null, grade numeric null ) --student表增加一个年龄字段 alter table student add age int NULL --student表删除年龄字段，删除的字段前面需要加column，不然会报错，而添加字段不需要加column alter table student drop Column age --删除student表 drop table student --删除表的数据和表的结构 truncate table student -- 只是清空表的数据，，但并不删除表的结构，student表还在只是数据为空数据操纵语言(DML)数据操纵语言全程是Data Manipulation Language，主要是进行插入元组、删除元组、修改元组的操作。主要有insert、update、delete语法组成。 --向student表中插入数据 --数据库插入数据 一次性插入多行多列 格式为INSERT INTO table (字段1, 字段2,字段3) VALUES (值1,值2,值3),(值1,值2,值3),...; INSERT INTO student (name, course,grade) VALUES ('张飞','语文',90),('刘备','数学',70),('关羽','历史',25),('张云','英语',13); --更新关羽的成绩 update student set grade='18' where name='关羽' --关羽因为历史成绩太低，要退学，所以删除关羽这个学生 delete from student where name='关羽'数据查询语言(DQL)数据查询语言全称是Data Query Language，所以是用来进行数据库中数据的查询的，即最常用的select语句。 --从student表中查询所有的数据 select * from student --从student表中查询姓名为张飞的学生 select * from student where name='张飞'数据控制语言(DCL)数据控制语言：Data Control Language。用来授权或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，能够对数据库进行监视。比如常见的授权、取消授权、回滚、提交等等操作。1、创建用户语法结构：CREATE USER 用户名@地址 IDENTIFIED BY '密码'; --创建一个testuser用户，密码111111 create user testuser@localhost identified by '111111';2、给用户授权语法结构： GRANT 权限1, … , 权限n ON 数据库.对象 TO 用户名; --将test数据库中所有对象(表、视图、存储过程，触发器等。*表示所有对象)的create,alter,drop,insert,update,delete,select赋给testuser用户 grant create,alter,drop,insert,update,delete,select on test.* to testuser@localhost;3、撤销授权语法结构：REVOKE权限1, … , 权限n ON 数据库.对象 FORM 用户名; --将test数据库中所有对象的create,alter,drop权限撤销 revoke create,alter,drop on test.* to testuser@localhost;4、查看用户权限语法结构： SHOW GRANTS FOR 用户名; --查看testuser的用户权限 show grants for testuser@localhost;5、删除用户语法结构：DROP USER 用户名; --删除testuser用户 drop user testuser@localhost;6、修改用户密码语法结构：USE mysql; UPDATE USER SET PASSWORD=PASSWORD(‘密码’) WHERE User=’用户名’ and Host=’IP’; FLUSH PRIVILEGES; --将testuser的密码改为123456 update user set password=password('123456') where user='testuser' and host=’localhost’; FLUSH PRIVILEGES;结尾本文对SQL程序语言有四种操作语言做了一个简单的介绍和概括，对数据库的基本操作都属于这四类，它们分别为;数据定义语言(DDL)、数据查询语言(DQL)、数据操纵语言(DML)、数据控制语言(DCL) 。作者：江夏来源：https://www.51cto.com/article/709269.html

平台SQL的where条件中的参数如果类型不匹配，会导致索引不生效。在完整的SQL语句前加上explain for可以查看SQL语句的执行计划，从而判断该SQL是否使用索引查询。例如，想要查看select id from PE_Person where name='abc'这条SQL语句的执行计划，那么可以在这条语句前面加上explain for，即执行 explain for select id from PE_Person where name='abc' 可以看到其执行计划。在索引字段上使用like进行模糊查询匹配，like操作符后的值的左边带有%时：例如，假设PE_Person对象模型中的name字段为索引字段。模糊查询PE_Person对象模型中的name字段中包含'bbb'的记录，其执行的SQL为select id from PE_Person where name like '%bbb%'，是不使用索引而进行全表扫描匹配记录的。建议方案：如果知道了待搜索的name的精确值，则使用精确的=号查询，如select id from PE_Person where name='abbbc'。如果不知道的name的精确值，但是可以知道待搜索的name字段的前缀，则可以只使用右边的%左边精确填写，如select id from PE_Person where name like 'abbb%'。数据类型出现隐式转化如SQL语句中的where条件后的值，该字段上的数据类型为文本，但是查询的过程中传入的是数字，使得查询过程中出现了隐式转换，导致索引无效，从而全表扫描。查询应该保证查询条件的值应该与对象模型中的值的类型一致。例如，select id from PE_Person where name=123，但是name在模型对象中是字符串类型。应该改为select id from PE_Person where name='123'在索引列上使用 IS NULL 或 IS NOT NULL操作索引是不索引空值的，所以这样的操作不能使用索引，可以用其他的办法处理，例如：数字类型，判断大于0，字符串类型设置一个默认值，判断是否等于默认值即可。例如：explain for select name from de_devices where name is not null。使用or语句做SQL拼接例如：select id from PE_Person where id='0I05000000OFyd0pgSKO' or PersonName='张三'。对索引字段进行计算操作、字段上使用函数例如，PE_Person模型对象中的字段PersonName为索引字段，其中希望搜索条件满足PersonName为'abc'的记录。如果写为如下的两条SQL语句，会导致无法使用索引。select id from PE_Person where PersonName=lower('ABC') 或者 select id from PE_Person where lower(PersonName)='ABC'改进方案：可以将函数处理放置在执行SQL前执行，执行SQL时候则不用包含相关函数。例如以上的场景可以先将‘ABC’通过函数转换为'abc'再进行SQL语句查询：select id from PE_Person where PersonName='ABC'对索引字段使用<>符号进行SQL查询select name from PE_Person where id <> 'abc123'

如果模型中满足某条件的数据量很大，查询的SQL语句中带有order by，count过程非常缓慢。可结合自身业务场景考虑是否可以放弃。如果业务上的 order by，count 不能删除，可以通过以下的方案进行优化：涉及order by的优化一般情况下，一个接口只需要一个order by，并且是对lastModifiedDate进行倒序查询输出的。但是lastModifiedDate字段是频繁修改的字段，同时也是平台的对象模型中的必带字段，不适合建立索引，所以其为非索引字段。例如，假设业务场景为：查询人员的页面上展示人员按照修改时间倒序排列，此时，如果需要新增一个人员进入系统。此时，倒序排列最主要的目的应该是添加了一个人员，添加成功后可以看见自己添加的人员信息，保证自己的添加是成功的。这时候，可以通过调用addPerson接口添加人员，当addPerson调用添加成功后，不再调用queryPerson进行查询，而是在修改前的记录上进行view model的修改，从而实现页面数据变更的效果。此时，查询接口中即使不使用order by也可以满足添加了一条记录成功后，在页面最前面显示自己的那条记录。当页面再次刷新（即，再次调用queryPerson接口时，刚才添加的记录或许不会在当前页面展示），但是无条件查询毕竟是模糊查询，如果需要精确查询刚才添加的记录，可以精确输入一些过滤条件进行查询，则可以直接获取得到刚才添加的记录。涉及count的优化由于count查询严重影响查询体验，所以可以考虑分情况讨论是否需要查询count。count一般用于前端分页展示选择页数，来进行分页选择查询。count查询的优化方案，将脚本查询接口的count输出改为可选输出，然后通过入参控制是否输出，如果需要输出再查询count，否则不返回count。对于页面上调用脚本的查询接口时，先试探查询传入start=5000，limit=5001，首次查询先通过入参控制不查询count并且携带其他查询条件，如果返回记录不为空，则表示满足条件的记录大于5000条。如果能查出一条记录则表示记录超过5000，则后续的正常分页查询也不查询count，只将分页查询的返回分页数据展示到页面上，页面也不展示count。页面也只可以翻页到0~5000的记录，5000以上的无法看。如果查不到记录（表示满足条件的数量少于5000）。则可以正常返回count，将返回的count用于前端分页的总记录数量，此时返回的分页数据亦可以正常展示，用法与普通的分页查询用法一致。

多表的left join / right join关联查询性能较低，可以考虑等价替换为join的SQL语句或者是用逗号分隔各个表，然后用where条件替代on条件作为关联条件的方式，关联查询多表（目前也推荐用逗号分隔，where条件替代on条件的方式进行多表内连接查询）。例如对于同时符合以下设定的场景下：如果A表为主表，B为从表。其中，出参包含AB两个表的数据，查询的条件可能来源于A，B表。A表有记录，B表未必有记录。B表的code字段与A表的主键id字段关联。各个查询条件之间是AND关系。那么，查询SQL语句最直观地会写为：select A.id, B.id from A left join B on A.id=B.code where ……但是这样的SQL语句在宽表引擎下并非性能最佳， 可以等价替换为如下：如果查询条件来源于A和B两表，并且查询条件都是AND逻辑拼接。可以改为：select A.id, B.id from A, B where A.id=B.code and ……如果查询条件只来源于A表，可改为使用以下的两条SQL：select A.id from A where ……将以上查询获取得到的A表的id传入到下列B表的SQL，查询获取B表的出参。select B.id from B where B.code=?如果查询条件只来源于B表，可以改为使用以下的两条SQL：Select B.id, B.code from B where ……将以上查询获取得到的B表的code传入到下列A表的SQL，查询获取A表的出参。select A.id from A where A.id=?

通过 Entity Framework Core 可以在使用关系数据库时下降到原始 SQL 查询。 所需查询不能使用 LINQ 来表示时，可以使用原始 SQL 查询。 如果使用 LINQ 查询导致 SQL 查询效率低下，也可以使用原始 SQL 查询。 原始 SQL 查询可返回一般实体类型或者模型中的无键实体类型。基本原生 SQL 查询可使用 FromSqlRaw 扩展方法基于原始 SQL 查询开始 LINQ 查询。 FromSqlRaw 只能在直接位于 DbSet<> 上的查询根上使用。var blogs = context.Blogs    .FromSqlRaw("SELECT * FROM dbo.Blogs")    .ToList();原生 SQL 查询可用于执行存储过程。var blogs = context.Blogs    .FromSqlRaw("EXECUTE dbo.GetMostPopularBlogs")    .ToList();

此页详细介绍了特定于 SQL Server 提供程序的索引配置选项。群集聚集索引根据数据行的键值在表或视图中排序和存储这些数据行。 为表创建适当的聚集索引可以显著提高查询的速度，因为数据已经按最佳顺序进行布局。 每个表只能有一个聚集索引，因为数据行本身只能按一个顺序存储。 有关详细信息，请参阅有关聚集索引和非聚集索引的 SQL Server 文档。默认情况下，表的主键列由聚集索引隐式支持，所有其他索引为非聚集索引。可以按如下所示配置要聚集的索引或键：protected override void OnModelCreating(ModelBuilder modelBuilder){    modelBuilder.Entity<Blog>().HasIndex(b => b.PublishedOn).IsClustered();}填充因子提供索引填充因子选项是为了优化索引数据存储和性能。 有关详细信息，请参阅有关填充因子的 SQL Server 文档。可按如下所示配置索引的填充因子：protected override void OnModelCreating(ModelBuilder modelBuilder){    modelBuilder.Entity<Blog>().HasIndex(b => b.PublishedOn).HasFillFactor(10);}联机创建ONLINE 选项允许在创建索引期间并发用户访问基础表或聚集索引数据以及任何关联的非聚集索引，以便用户可以继续更新和查询基础数据。 当脱机执行数据定义语言 (DDL) 操作（例如，生成或重新生成聚集索引）时，这些操作对基础数据和关联索引持有排他锁。 有关详细信息，请参阅有关 ONLINE 索引选项的 SQL Server 文档。可以使用 ONLINE 选项配置索引，如下所示：protected override void OnModelCreating(ModelBuilder modelBuilder){    modelBuilder.Entity<Blog>().HasIndex(b => b.PublishedOn).IsCreatedOnline();}

批处理EF Core 通过在一次往返中自动将所有更新批处理在一起，帮助最大限度地减少往返。 考虑以下情况：var blog = context.Blogs.Single(b => b.Url == "http://someblog.microsoft.com");blog.Url = "http://someotherblog.microsoft.com";context.Add(new Blog { Url = "http://newblog1.microsoft.com" });context.Add(new Blog { Url = "http://newblog2.microsoft.com" });context.SaveChanges();上述操作从数据库加载博客，更改其 URL，然后添加两个新博客；若要应用此更改，将两个 SQL INSERT 语句和一个 UPDATE 语句发送到数据库。 在添加 Blog 实例时，不要一个一个 SaveChanges 地发送它们，而是在EF Core跟踪这些更改，在调用 时在单个往返中执行这些更改。EF 在一次往返中批处理的语句数量取决于所使用的数据库提供程序。 例如，性能分析表明，当涉及的语句少于 4 个时，批处理对于 SQL Server 的效率通常较低。 同样，对于 SQL Server，批处理的优势在大约 40 条语句后会降低，因此 EF Core 默认在单个批处理中最多只执行 42 条语句，并在单独的往返中执行额外的语句。用户也可以调整这些阈值以获得潜在的更高性能，但是在修改这些阈值之前要仔细地进行基准测试：protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder){    optionsBuilder.UseSqlServer(        @"Server=(localdb)\mssqllocaldb;Database=Blogging;Trusted_Connection=True",        o => o            .MinBatchSize(1)            .MaxBatchSize(100));}批量更新假设你想给所有员工加薪。 EF Core 中对此的典型实现如下所示：foreach (var employee in context.Employees){    employee.Salary += 1000;}context.SaveChanges();虽然这是完全有效的代码，但是让我们从性能的角度来分析一下它的作用：执行一次数据库往返，以加载所有相关员工；请注意，这会将员工的所有行数据带到客户端（即使只需要工资数据）。EF Core 的更改跟踪在加载实体时创建快照，然后将这些快照与实例进行比较，找出哪些属性发生了更改。执行第二次数据库往返以保存所有更改。 虽然由于批处理，所有更改都在一次往返中完成，但 EF Core 仍会为每个员工发送一条 UPDATE 语句，该语句必须由数据库执行。关系数据库还支持批量更新，因此可将上述内容重写为以下单个 SQL 语句：UPDATE [Employees] SET [Salary] = [Salary] + 1000;这在单次往返中执行整个操作，而无需加载任何实际数据或将这些数据发送到数据库，也无需使用 EF 的更改跟踪机制（这会增加额外的开销）。遗憾的是，EF 目前不提供用于执行批量更新的 API。 在引入这些项之前，可使用原始 SQL 来执行性能敏感的操作：context.Database.ExecuteSqlRaw("UPDATE [Employees] SET [Salary] = [Salary] + 1000");

高效查询是一个庞大的主题，涵盖的主题就像索引、相关实体加载策略以及许多其他主题一样广泛。 本部分详细介绍了一些用于更快地进行查询的常见主题以及用户通常会遇到的隐患。正确使用索引查询能否快速运行的主要决定因素是它是否在恰当的位置使用索引：数据库通常用于保存大量数据，而遍历整个表的查询往往是严重性能问题的根源。 索引问题不容易发现，因为给定的查询是否会使用索引并不是显而易见的发现索引问题的一个好方法是：先准确定位慢速查询，然后通过数据库的常用工具检查其查询计划。有关如何执行此操作的详细信息，请参阅性能诊断页。 查询计划表明查询是遍历整个表，还是使用索引。一般来说，在使用索引或诊断索引相关性能问题方面没有任何特殊的 EF 知识；索引方面的一般数据库知识与 EF 应用程序之间以及它与非 EF 应用程序之间有着一样的相关度。 下面列出了在使用索引时要记住的一些一般准则：索引能加快查询，但也会减缓更新，因为它们需要保持最新状态。 避免定义不需要的索引，并考虑使用索引筛选器将索引限制为行的子集，从而减少此开销。复合索引可加速筛选多列的查询，也可加速不筛选所有索引列的查询，具体取决于排序。 例如，列 A 和列 B 上的索引加快按 A 和 B 筛选的查询以及仅按 A 筛选的查询，但不加快仅按 B 筛选的查询。如果查询按表达式筛选列（例如 price / 2），则不能使用简单索引。 但是，你可以为表达式定义存储的持久化列，并对该列创建索引。 一些数据库还支持表达式索引，可以直接使用这些索引加快按任何表达式筛选的查询。不同数据库允许以各种不同的方式配置索引，在许多情况下，EF Core 提供程序都通过 Fluent API 公开这些索引。 例如，你可以通过 SQL Server 提供程序配置索引是否为聚集索引，或设置其填充因子。 参阅提供程序文档了解详细信息。只投影需要的属性EF Core 能非常轻松地查询出实体实例，然后将它们用于代码中。 但是，查询实体实例可能会频繁从数据库中拉取回超出所需的数据。限制结果集大小查询默认返回与筛选器匹配的所有行：返回的行数取决于数据库中的实际数据，因此不可能知道将从数据库中加载的数据量、结果占用的内存量以及处理这些结果时（例如，通过网络将它们发送到用户浏览器）将额外生成的负载量。 非常重要的一点是，测试数据库往往包含少量数据，所以测试时一切正常，但当查询开始基于实际数据运行并且返回了许多行时，会突然出现性能问题。高效分页分页是指在页面中检索结果，而不是一次性检索结果;这通常针对大型结果集完成，其中显示用户界面，允许用户导航到结果的下一页或上一页。 使用数据库实现分页的一种常见方法是使用Skip和Take运算符 (OFFSET和LIMITSQL) ;虽然这是一种直观的实现，但它也相当低效。 对于允许一次移动一页的分页 (，而不是跳转到任意页面) ，请考虑改用 键集分页 。在加载相关实体时避免笛卡尔爆炸如果典型博客有多篇相关文章，这些文章对应的行会复制博客的信息。 这种复制会导致所谓的“笛卡尔爆炸”问题发生。 随着加载更多的一对多关系，重复的数据量可能会增长，并对应用程序性能产生负面影响。借助 EF，可通过使用“拆分查询”来避免这种影响，这种查询通过单独的查询加载相关实体。 有关详细信息，请阅读有关拆分查询和单个查询的文档。尽可能预先加载相关实体建议在继续本部分之前，先阅读相关实体专用页面。处理相关实体时，我们通常会提前知晓需要加载什么：典型的示例是加载一组特定的博客以及它们的所有文章。 在这些情况下，最好的做法始终是使用预先加载，这样 EF 可以在一次往返中提取所有必需的数据。 通过 EF Core 5.0 中引入的经过筛选的包含功能，你能限制要加载的相关实体，同时使加载过程保持为预先加载，从而可在一次往返中执行在其他情况下，在获得相关实体的主体实体之前，我们可能不知道需要哪些相关实体。 例如，加载某个博客时，我们可能需要参考另外一个数据源（可能是某个 Web 服务），以便了解我们是否对该博客文章感兴趣。 在这些情况下，可以使用显式或延迟加载单独提取相关实体，并填充博客文章导航。 请注意，这些方法都不是预先的，因此需要对数据库执行额外的往返，这是速度减缓的根源；根据具体的场景，比起执行额外的往返并有选择性地只获取需要的文章，始终只加载所有文章可能更高效。注意延迟加载延迟加载看上去像是一种非常有用的数据库逻辑编写方法，因为 EF Core 会在代码访问相关实体时，从数据库中自动加载这些实体。 这避免了加载不需要的相关实体（就像显式加载一样），而且似乎使程序员不必一起处理相关实体。 不过，延迟加载特别容易产生不必要的额外往返，从而降低应用程序的速度。缓冲和流式处理缓冲指将所有查询结果加载到内存中，而流式处理意味着 EF 每次向应用程序提供一个结果，绝不让内存中包含整个结果集。 原则上，流式处理查询的内存要求是固定的：无论查询返回 1 行还是 1000 行，内存要求都相同。另一方面，返回的行数越多，缓冲查询需要的内存越多。 对于产生大型结果集的查询，这可能是一个重要的性能因素。EF 执行的内部缓冲在某些情况下，无论如何计算查询，EF 都会在内部自行缓冲结果集。 出现这种情况的两个场景是：重试执行策略已准备就绪时。 这样做是为了确保在以后重试查询时返回相同的结果。使用拆分查询时，将缓冲除最后一个查询的所有其他查询的结果集 -除非 MARS (多个活动结果集) 在SQL Server上启用。 这是因为通常无法同时激活多个查询结果集。请注意，除通过 LINQ 运算符引发的任何缓冲外，还会发生这种内部缓冲。 例如，如果在 ToList 查询上使用并且重试执行策略已到位，则结果集将加载到内存 中两次：一次由 EF 在内部加载，再加载一次 ToList。跟踪、非跟踪和标识解析建议在继续本部分之前，先阅读关于跟踪和非跟踪的专用页面。EF 默认跟踪实体实例，以便在调用时 SaveChanges 检测并保留这些实例的更改。 跟踪查询的另一个作用是：EF 检测是否已为你的数据加载了实例，并将自动返回跟踪的实例，而不是返回新实例。我们将这种做法称为标识解析。 从性能的角度来看，更改跟踪意味着：EF 在内部维护跟踪实例的字典。 加载新数据时，EF 会查阅字典，以了解是否已为该实体的键跟踪了实例（标识解析）。 加载查询结果时，字典维护和查找会花费一些时间。在将加载的实例交给应用程序之前，EF 截取该实例的快照，并在内部保存该快照。 调用时 SaveChanges ，会将应用程序的实例与快照进行比较，以发现要保留的更改。 快照占用更多内存，截取快照的过程本身需要时间；有时可以通过值比较器指定不同的、可能更高效的快照截取行为，或使用更改跟踪代理完全绕过快照截取过程（虽然这种做法本身也有一些缺点）。在不将更改保存回数据库的只读场景中，可通过使用非跟踪查询来避免上述开销。 但非跟踪查询不执行标识解析，所以由多个其他已加载的行引用的数据库行将被具体化为不同的实例。为了说明这一点，假设我们要从数据库中加载大量文章以及每篇文章引用的博客。 如果碰巧有 100 篇文章引用了同一个博客，则跟踪查询通过标识解析来检测这种情况，并且所有文章实例都将引用同一个删除了重复数据的博客实例。 而无跟踪查询会将相同的博客重复 100 次，我们必须相应地编写应用程序代码。最后，可以在不产生更改跟踪开销的情况下执行更新，方法是：利用无跟踪查询，再将返回的实例附加到上下文中，同时指定要进行的更改。 这种做法将更改跟踪的负担从 EF 转移到用户，我们只应在更改跟踪开销已通过分析或基准测试显示为不可接受时尝试这么做。使用原始 SQL在某些情况下，你的查询存在更优化的 SQL，而 EF 不能生成这种 SQL。 如果 SQL 构造是特定于不受支持的数据库的扩展，或者 EF 不转换为该构造，可能会发生这种情况。 在这些情况下，手动编写 SQL 可以显著提高性能，而 EF 支持通过多种方法来实现此目的。直接在查询中使用原始SQL，例如通过 FromSqlRaw. 借助 EF，你甚至可以通过常规 LINQ 查询基于原始 SQL 进行撰写，从而能够在原始 SQL 中仅表达查询的一部分。 只需要将原始 SQL 用于代码库中的一个查询时，这种方法很不错。定义用户定义的函数 (UDF)，然后从查询中调用它。 请注意，从 5.0 版开始，EF 允许 UDF 返回完整的结果集，这些 UDF 被称为表值函数 (TVF)。还允许将 DbSet 映射到函数，使其看起来像另一个表。在查询中定义一个数据库视图并从中进行查询。 请注意，与函数不同，视图不能接受参数。异步编程一般情况下，为了使应用程序可缩放，请务必始终使用异步 API 而不是同步一个 (，例如 SaveChangesAsync ，而不是 SaveChanges) 。 同步 API 在数据库输入/输出 (I/O) 期间阻止线程，增加了对线程的需要和必须发生的线程上下文切换的次数。

Entity Framework Core 使用语言集成查询 (LINQ) 来查询数据库中的数据。 通过 LINQ 可使用 C#（或你选择的其他 .NET 语言）编写强类型查询。 它使用你派生得到的上下文和实体类来引用数据库对象。 EF Core 将 LINQ 查询的表示形式传递给数据库提供程序。 反过来，数据库提供程序将其转换为数据库特定的查询语言（例如，用于关系数据库的 SQL）。 即使结果中返回的实体已存在于上下文中，也始终对数据库执行查询。作为一般规则，Entity Framework Core 会尝试尽可能全面地评估服务器上的查询。 EF Core 将查询的一部分转换为可在客户端评估的参数。 系统将查询的其余部分（及生成的参数）提供给数据库提供程序，以确定要在服务器上评估的等效数据库查询。 EF Core 支持在顶级投影中进行部分客户端评估（基本上为最后一次调用 Select()）。 如果查询中的顶级投影无法转换为服务器，EF Core 将从服务器中提取任何所需的数据，并在客户端上评估查询的其余部分。 如果 EF Core 在顶级投影之外的任何位置检测到不能转换为服务器的表达式，则会引发运行时异常。 请参阅查询工作原理，了解 EF Core 如何确定哪些表达式无法转换为服务器。顶级投影中的客户端评估在下面的示例中，一个辅助方法用于标准化从 SQL Server 数据库中返回的博客的 URL。 由于 SQL Server 提供程序不了解此方法的实现方式，因此无法将其转换为 SQL。 查询的所有其余部分是在数据库中评估的，但通过此方法传递返回的 URL 却是在客户端上完成。不支持的客户端评估尽管客户端评估非常有用，但有时会减弱性能。 请看以下查询，其中的 where 筛选器现已使用辅助方法。 由于数据库中不能应用筛选器，因此需要将所有数据提取到内存中，以便在客户端上应用筛选器。 根据服务器上的筛选器和数据量，客户端评估可能会减弱性能。 因此 Entity Framework Core 会阻止此类客户端评估，并引发运行时异常显式客户端评估在某些情况下，可能需要以显式方式强制进行客户端评估，如下所示由于数据量小，因此在进行客户端评估时才不会大幅减弱性能。所用的 LINQ 运算符不会进行任何服务器端转换。在这种情况下，通过调用 AsEnumerable 或 ToList 等方法（若为异步，则调用 AsAsyncEnumerable 或 ToListAsync），以显式方式选择进行客户端评估。 使用 AsEnumerable 将对结果进行流式传输，但使用 ToList 将通过创建列表来进行缓冲，因此也会占用额外的内存。 但如果枚举多次，则将结果存储到列表中可以带来更大的帮助，因为只有一个对数据库的查询。 根据具体的使用情况，你应该评估哪种方法更适合。客户端评估中潜在的内存泄漏由于查询转换和编译的开销高昂，因此 EF Core 会缓存已编译的查询计划。 缓存的委托在对顶级投影进行客户端评估时可能会使用客户端代码。 EF Core 为树型结构中客户端评估的部分生成参数，并通过替换参数值重用查询计划。 但表达式树中的某些常数无法转换为参数。 如果缓存的委托包含此类常数，则无法将这些对象垃圾回收，因为它们仍被引用。 如果此类对象包含 DbContext 或其中的其他服务，则会导致应用的内存使用量逐渐增多。 此行为通常是内存泄漏的标志。 只要遇到的常数为不能使用当前数据库提供程序映射的类型，EF Core 就会引发异常。 常见原因及其解决方案如下所示：使用实例方法：在客户端投影中使用实例方法时，表达式树包含实例的常数。 如果你的方法不使用该实例中的任何数据，请考虑将该方法设为静态方法。 如果需要方法主体中的实例数据，则将特定数据作为实参传递给方法。将常数实参传递给方法：这种情况通常是由于在客户端方法的实参中使用 this 引起的。 请考虑将实参拆分为多个标量实参，可由数据库提供程序进行映射。其他常数：如果在任何其他情况下都出现常数，则可以评估在处理过程中是否需要该常数。 如果必须具有常数，或者如果无法使用上述情况中的解决方案，则创建本地变量来存储值，并在查询中使用局部变量。 EF Core 会将局部变量转换为形参。

本文分享自华为云社区《​​​​​​​从零开发大数据SQL引擎》，作者：JavaEdge 。 学习大数据技术的核心原理，掌握一些高效的思考和思维方式，构建自己的技术知识体系。 明白了原理，有时甚至不需要学习，顺着原理就可以推导出各种实现细节。 各种知识表象看杂乱无章，若只是学习繁杂知识点，固然自己的知识面是有限的，并且遇到问题的应变能力也很难提高。所以有些高手看起来似乎无所不知，不论谈论起什么技术，都能头头是道，其实并不是他们学习、掌握了所有技术，而是他们是在谈到这个问题时，才开始进行推导，并迅速得出结论。 高手不一定要很资深、经验丰富，把握住技术的核心本质，掌握快速分析推导的能力，能迅速将自己的知识技能推到陌生领域，就是高手。 本系列专注大数据开发需要关注的问题及解决方案。跳出繁杂知识表象，掌握核心原理和思维方式，进而融会贯通各种技术，再通过各种实践训练，成为终极高手。 # 大数据仓库Hive 作为一个成功的大数据仓库，它将SQL语句转换成MapReduce执行过程，并把大数据应用的门槛下降到普通数据分析师和工程师就可以很快上手的地步。 但Hive也有问题，由于它使用自定义Hive QL，对熟悉Oracle等传统数据仓库的分析师有上手难度。特别是很多企业使用传统数据仓库进行数据分析已久，沉淀大量SQL语句，非常庞大也非常复杂。某银行的一条统计报表SQL足足两张A4纸，光是完全理解可能就要花很长时间，再转化成Hive QL更费力，还不说可能引入bug。 开发一款能支持标准数据库SQL的大数据仓库引擎，让那些在Oracle上运行良好的SQL可以直接运行在Hadoop上，而不需要重写成Hive QL。 # Hive处理过程 1. 将输入的Hive QL经过语法解析器转换成Hive抽象语法树（Hive AST） 2. 将Hive AST经过语义分析器转换成MapReduce执行计划 3. 将生成的MapReduce执行计划和Hive执行函数代码提交到Hadoop执行 可见，最简单的，对第一步改造即可。考虑替换Hive语法解析器：能将标准SQL转换成Hive语义分析器能处理的Hive抽象语法树，即红框代替黑框  红框内：浅蓝色是个开源的SQL语法解析器，将标准SQL解析成标准SQL抽象语法树（SQL AST），后面深蓝色定制开发的SQL抽象语法树分析与转换器，将SQL AST转换成Hive AST。 那么关键问题就来了： # 标准SQL V.S Hive QL - 语法表达方式，Hive QL语法和标准SQL语法略有不同 - Hive QL支持的语法元素比标准SQL要少很多，比如，数据仓库领域主要的测试集TPC-H所有的SQL语句，Hive都不支持。尤其是Hive不支持复杂嵌套子查询，而数据仓库分析中嵌套子查询几乎无处不在。如下SQL，where条件existes里包含了另一条SQL： ```mysql select o_orderpriority, count(*) as order_count from orders where o_orderdate >= date '[DATE]' and o_orderdate date '[DATE]' + interval '3' month and exists (select * from lineitem where l_orderkey = o_orderkey and l_commitdate l_receiptdate) group by o_orderpriority order by o_orderpriority; ``` 开发支持标准SQL语法的SQL引擎难点，就是**消除复杂嵌套子查询掉**，即让where里不包含select。 SQL理论基础是关系代数，主要操作仅包括：并、差、积、选择、投影。而一个嵌套子查询可等价转换成一个连接（join）操作，如： ```mysql select s_grade from staff where s_city not in ( select p_city from proj where s_empname = p_pname ) ``` 这是个在where条件里嵌套了not in子查询的SQL语句，它可以用left outer join和left semi join进行等价转换，示例如下，这是Panthera自动转换完成得到的等价SQL。这条SQL语句不再包含嵌套子查询， ```mysql select panthera_10.panthera_1 as s_grade from (select panthera_1, panthera_4, panthera_6, s_empname, s_city from (select s_grade as panthera_1, s_city as panthera_4, s_empname as panthera_6, s_empname as s_empname, s_city as s_city from staff) panthera_14 left outer join (select panthera_16.panthera_7 as panthera_7, panthera_16.panthera_8 as panthera_8, panthera_16.panthera_9 as panthera_9, panthera_16.panthera_12 as panthera_12, panthera_16.panthera_13 as panthera_13 from (select panthera_0.panthera_1 as panthera_7, panthera_0.panthera_4 as panthera_8, panthera_0.panthera_6 as panthera_9, panthera_0.s_empname as panthera_12, panthera_0.s_city as panthera_13 from (select s_grade as panthera_1, s_city as panthera_4, s_empname as panthera_6, s_empname, s_city from staff) panthera_0 left semi join (select p_city as panthera_3, p_pname as panthera_5 from proj) panthera_2 on (panthera_0.panthera_4 = panthera_2.panthera_3) and (panthera_0.panthera_6 = panthera_2.panthera_5) where true) panthera_16 group by panthera_16.panthera_7, panthera_16.panthera_8, panthera_16.panthera_9, panthera_16.panthera_12, panthera_16.panthera_13) panthera_15 on ((((panthera_14.panthera_1 => panthera_15.panthera_7) and (panthera_14.panthera_4 => panthera_15.panthera_8)) and (panthera_14.panthera_6 => panthera_15.panthera_9)) and (panthera_14.s_empname => panthera_15.panthera_12)) and (panthera_14.s_city => panthera_15.panthera_13) where ((((panthera_15.panthera_7 is null) and (panthera_15.panthera_8 is null)) and (panthera_15.panthera_9 is null)) and (panthera_15.panthera_12 is null)) and (panthera_15.panthera_13 is null)) panthera_10 ; ``` 通过可视化工具将上面两条SQL的语法树展示出来，是这样的。  这是原始的SQL抽象语法树。  这是等价转换后的抽象语法树，内容太多被压缩的无法看清，不过你可以感受一下（笑）。 那么，在程序设计上如何实现这样复杂的语法转换呢？当时Panthera项目组合使用了几种经典的设计模式，每个语法点被封装到一个类里去处理，每个类通常不过几十行代码，这样整个程序非常简单、清爽。如果在测试过程中遇到不支持的语法点，只需为这个语法点新增加一个类即可，团队协作与代码维护非常容易。 使用装饰模式的语法等价转换类的构造，Panthera每增加一种新的语法转换能力，只需要开发一个新的Transformer类，然后添加到下面的构造函数代码里即可。 private static SqlASTTransformer tf = new RedundantSelectGroupItemTransformer( new DistinctTransformer( new GroupElementNormalizeTransformer( new PrepareQueryInfoTransformer( new OrderByTransformer( new OrderByFunctionTransformer( new MinusIntersectTransformer( new PrepareQueryInfoTransformer( new UnionTransformer( new Leftsemi2LeftJoinTransformer( new CountAsteriskPositionTransformer( new FilterInwardTransformer( //use leftJoin method to handle not exists for correlated new CrossJoinTransformer( new PrepareQueryInfoTransformer( new SubQUnnestTransformer( new PrepareFilterBlockTransformer( new PrepareQueryInfoTransformer( new TopLevelUnionTransformer( new FilterBlockAdjustTransformer( new PrepareFilterBlockTransformer( new ExpandAsteriskTransformer( new PrepareQueryInfoTransformer( new CrossJoinTransformer( new PrepareQueryInfoTransformer( new ConditionStructTransformer( new MultipleTableSelectTransformer( new WhereConditionOptimizationTransformer( new PrepareQueryInfoTransformer( new InTransformer( new TopLevelUnionTransformer( new MinusIntersectTransformer( new NaturalJoinTransformer( new OrderByNotInSelectListTransformer( new RowNumTransformer( new BetweenTransformer( new UsingTransformer( new SchemaDotTableTransformer( new NothingTransformer()))))))))))))))))))))))))))))))))))))); 而在具体的Transformer类中，则使用组合模式对抽象语法树AST进行遍历，以下为Between语法节点的遍历。我们看到使用组合模式进行树的遍历不需要用递归算法，因为递归的特性已经隐藏在树的结构里面了。 @Override protected void transform(CommonTree tree, TranslateContext context) throws SqlXlateException { tf.transformAST(tree, context); trans(tree, context); } void trans(CommonTree tree, TranslateContext context) { // deep firstly for (int i = 0; i tree.getChildCount(); i++) { trans((CommonTree) (tree.getChild(i)), context); } if (tree.getType() == PantheraExpParser.SQL92_RESERVED_BETWEEN) { transBetween(false, tree, context); } if (tree.getType() == PantheraExpParser.NOT_BETWEEN) { transBetween(true, tree, context); } } 将等价转换后的抽象语法树AST再进一步转换成Hive格式的抽象语法树，就可以交给Hive的语义分析器去处理了，从而也就实现了对标准SQL的支持。 当时Facebook为证明Hive对数据仓库的支持，手工将TPC-H的测试SQL转换成Hive QL，将这些手工Hive QL和Panthera进行对比测试，两者性能各有所长，总体上不相上下，说明Panthera自动进行语法分析和转换的效率还行。 Panthera（ASE）和Facebook手工Hive QL对比测试：  标准SQL语法集的语法点很多，007进行各种关系代数等价变形，也不可能适配所有标准SQL语法。 # SQL注入 常见的Web攻击手段，如下图所示，攻击者在HTTP请求中注入恶意SQL命令（drop table users;），服务器用请求参数构造数据库SQL命令时，恶意SQL被一起构造，并在数据库中执行。  但JDBC的PrepareStatement可阻止SQL注入攻击，MyBatis之类的ORM框架也可以阻止SQL注入，请从数据库引擎的工作机制解释PrepareStatement和MyBatis的防注入攻击的原理。

本章节介绍如何在MariaDB中管理用户和访问权限。在数据库操作中，也是基础使用，必须学会。个人简介：大家好，我是 金鱼哥，CSDN运维领域新星创作者，华为云·云享专家个人资质：CCNA、HCNP、CSNA（网络分析师），软考初级、中级网络工程师、RHCSA、RHCE、RHCA、RHCI、ITIL格言：努力不一定成功，但要想成功就必须努力1. 在MariaDB中创建用户帐号默认情况下，MariaDB将其用户及其密码与本地系统的用户和密码分开。这意味着MariaDB数据库用户与服务器的Linux用户不同，即使用户帐户具有相同的名称，默认情况下，密码将分别跟踪。为了控制用户对数据库服务器的访问级别，必须在MariaDB中设置数据库用户，并授予他们对服务器及其数据执行操作的权限。可以使用MariaDB pam身份验证插件将系统用户帐户和密码集成为MariaDB数据库用户，本课程不涉及该配置。在大多数情况下，最好在数据库服务器上分别管理对数据库服务的访问和对shell提示符的访问创建新用户需要具备以下其中一个权限级别:MariaDB root用户。是一个被授予全局CREATE user权限的用户。是一个被授予mysql数据库的INSERT权限的用户。CREATE USER语句在mysql数据库的user表中创建一条新记录。这个用户没有特权。用户名指定为user_name@host_name。这使得可以创建具有相同名称、但根据源主机(即用户所连接的主机)具有不同特权的多个用户帐户。MariaDB [(none)]> CREATE USER mobius@localhost IDENTIFIED BY ‘redhat’ ;该帐户可以用来连接的用户名/主机名。该帐号的密码。注意：如果没有提供主机名，用户可从任何主机获得访问权限。目前，mobius帐户只能从本地主机连接，密码为redhat。密码在用户表中加密:MariaDB [mysql]> SELECT host,user,password FROM user WHERE user = 'mobius'; +-----------+--------+-------------------------------------------+ | host | user | password | +-----------+--------+-------------------------------------------+ | localhost | mobius | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 | +-----------+--------+-------------------------------------------+ 1 row in set (0.00 sec)账户定义示例账户描述mobius用户mobius可以从任何主机连接。mobius@’%'用户mobius可以从任何主机连接。mobius@'localhost’用户mobius只能从Localhost连接。mobius@'192.168.1.5’用户mobius只能通过IP地址192.168.1.5进行连接。mobius@'192.168.1.%'用户mobius可以从属于该网络192.168.1.0/24的任何地址进行连接。mobius@'2001:db8:18:b51:c32:a21’用户mobius只能通过IP地址2001:db8:18:b51:C32: a21进行连接。2. 控制用户权限默认情况下，新帐户被授予最低权限。在不授予额外特权的情况下，mobius用户可以访问最小的帐户信息，但大多数其他操作都被拒绝。# 在下面的例子中，允许访问: [user@host ~]$ mysql -u mobius -p Enter password: redhat MariaDB [(none)]> SELECT USER(); +------------------+ | user() | +------------------+ | mobius@localhost | +------------------+ 1 row in set (0.000 sec) MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.000 sec) # 在下一个例子中，用户验证了身份，但是访问被拒绝了: [user@host ~]$ mysql -u mobius -p Enter password: redhat ...output omitted... MariaDB [(none)]> USE mysql; ERROR 1044 (42000): Access denied for user 'mobius'@'localhost' to database 'mysql' MariaDB [(none)]> CREATE DATABASE inventory; ERROR 1044 (42000): Access denied for user 'mobius'@'localhost' to database 'inventory'权限是用户在MariaDB中拥有的权限。它们决定了用户可以做什么，以及用户可以在MariaDB中看到什么。特权是按其详细范围组织的。全局权限(例如CREATE USER)用于管理MariaDB数据库服务器本身。数据库特权(如CREATE Database)用于在MariaDB服务器上创建数据库和使用数据库。表特权(比如CRUD命令)用于在特定数据库中创建表和操作数据。列特权用于授予类似于表的命令使用，但是在特定的列上(通常很少)。其他更细粒度的特权将在本节末尾引用的MariaDB文档中详细讨论。授予用户权限GRANT语句可用于向帐户授予权限。要授予权限，连接的用户必须具有GRANT OPTION，并且必须具有正在授予的GRANT OPTION权限。例如，mobius用户不能授予数据库表上的SELECT权限，除非他们已经拥有SELECT权限和grant OPTION表权限。# 在这个示例中，MariaDB根用户将CRUD权限授予inventory数据库中类别表上的mobius用户。 [user@host ~]$ mysql -u root -p Enter password: redhat ...output omitted... MariaDB [(none)]> USE inventory; ...output omitted... Database changed MariaDB [(inventory)]> GRANT SELECT, UPDATE, DELETE, INSERT -> ON inventory.category -> TO mobius@localhost ; Query OK, 0 rows affected (0.00 sec) MariaDB [(inventory)]> exit Bye # 然后你可以确认mobius用户的权限如下: [user@host ~]$ mysql -u mobius -p Enter password: redhat MariaDB [(none)]> USE inventory; MariaDB [(inventory)]> SELECT * FROM category; +----+------------+ | id | name | +----+------------+ | 1 | Networking | | 2 | Servers | | 3 | Ssd | +----+------------+ 3 rows in set (0.00 sec)GRANT操作的例子Grant描述GRANT SELECT ON database.table TO username@hostname将特定数据库中特定表的SELECT权限授予特定用户。GRANT SELECT ON database.* TO username@hostname将特定数据库中所有表的SELECT权限授予特定用户。GRANT SELECT ON *.* TO username@hostname将所有数据库中所有表的SELECT权限授予特定用户。GRANT CREATE, ALTER, DROP ON database.* to username@hostname将特定数据库中的CREATE、ALTER、DROP TABLES权限授予特定用户。GRANT ALL PRIVILEGES ON *.* to username@hostname将所有数据库的所有可用权限授予特定用户，有效地创建了一个超级用户，类似于root。撤销用户权限REVOKE语句从帐户中删除特权。连接的用户必须具有GRANT OPTION权限，并且具有正在被撤销的权限以撤销权限。MariaDB [(none)]> REVOKE SELECT, UPDATE, DELETE, INSERT-> ON inventory.category FROM mobius@localhost ;Query OK, 0 rows affected (0.00 sec)**重要：**在修改授权表之后运行FLUSH PRIVILEGES命令是一个好习惯。虽然MariaDB会注意到一些语句并自动加载，但是大多数撤销特权的语句都需要用FLUSH PRIVILEGES重新加载特权表才能生效。MariaDB [(none)]> FLUSH PRIVILEGES;显示用户权限可以验证哪些特权被授予给了用户。SHOW GRANTS FOR username; 提供了该用户的权限列表:MariaDB [(none)]> SHOW GRANTS FOR root@localhost; +---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+ 2 rows in set (0.00 sec)3. 删除用户帐号当您不再需要一个特定的用户帐户时，可以使用DROP USER从数据库中删除它。username应该使用与CREATE USER相同的’user’@'host’格式。MariaDB [(none)]> DROP USER mobius;Query OK, 0 rows affected (0.002 sec)重要：如果当前连接的帐户被删除，该帐户将直到连接关闭后才会被删除。如果该用户有一个活动的连接，当帐户被删除时，它不会自动关闭。4. 数据库访问的故障诊断下表总结了用户在身份验证和访问方面可能遇到的一些问题以及可能的原因。一些常见的数据库访问问题问题解决方案用户已被授予从任何主机连接的访问权限，但只能在数据库服务器上使用shell中的mysql命令进行连接。如果在/etc/my.cnf.d/mariadb-server.cnf中设置了skip-networking，请删除该指令并重新启动服务。用户可以连接localhost上的任何应用程序，但不能远程连接。确保/etc/my.cnf.d/mariadb-server.cnf中的bind-address配置是正确的， 确保可以访问数据库。确保user表中包含用户试图连接的主机的用户条目。用户可以连接，但不能看到除information_schema之外的任何数据库。确保用户已被授予访问其数据库的特权。当用户刚刚创建时，这是一个常见的问题，因为默认情况下创建的用户帐户具有最小的权限。该用户可以连接，但不能创建任何数据库。考虑为用户授予全局CREATE特权(这也授予DROP特权)。用户可以连接，但不能读写任何数据。为用户打算使用的数据库授予CRUD特权。5. 课本练习[student@workstation ~]$ lab database-users start创建用户与授权[student@servera ~]$ mysql -u root -p Enter password: redhat MariaDB [(none)]> CREATE USER john@localhost identified by 'john_password'; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> CREATE USER steve@'%' identified by 'steve_password'; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> GRANT INSERT, UPDATE, DELETE,SELECT on inventory.* to john@localhost; Query OK, 0 rows affected (0.000 sc) MariaDB [(none)]> GRANT SELECT on inventory.* to steve@'%'; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> exit Bye验证[student@servera ~]$ mysql -u john -p Enter password: john_password MariaDB [(none)]> MariaDB [(none)]> USE inventory; Database changed MariaDB [(inventory)]> SELECT * FROM category; +----+------------+ | id | name | +----+------------+ | 1 | Networking | | 2 | Servers | | 3 | Ssd | +----+------------+ 3 rows in set (0.00 sec) MariaDB [(inventory)]> INSERT INTO category(name) VALUES('Memory'); Query OK, 1 row affected (0.00 sec) MariaDB [(inventory)]> UPDATE category SET name='Solid State Drive' WHERE id = 3; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [(inventory)]> SELECT * FROM category; +----+-------------------+ | id | name | +----+-------------------+ | 1 | Networking | | 2 | Servers | | 3 | Solid State Drive | | 5 | Memory | +----+-------------------+ 4 rows in set (0.000 sec) MariaDB [(inventory)]> DELETE FROM category WHERE name LIKE 'Memory'; Query OK, 1 row affected (0.01 sec) MariaDB [(inventory)]> exit Bye [student@serverb ~]$ mysql -u steve -h servera -p Enter password: steve_password MariaDB [(none)]> USE inventory; Database changed MariaDB [(inventory)]> SELECT * FROM category; +----+-------------------+ | id | name | +----+-------------------+ | 1 | Networking | | 2 | Servers | | 3 | Solid State Drive | +----+-------------------+ 3 rows in set (0.00 sec) MariaDB [(inventory)]> INSERT INTO category(name) VALUES('Memory'); ERROR 1142 (42000): INSERT command denied to user 'steve'@'serverb.example.com'for table 'category' MariaDB [(inventory)]> exit Bye完成实验[student@workstation ~]$ lab database-users finish总结介绍如何在MariaDB中创建账户。介绍如何管理MariaDB账户。介绍数据库访问的故障诊断。RHCA认证需要经历5门的学习与考试，还是需要花不少时间去学习与备考的，好好加油，可以噶。以上就是【金鱼哥】对 第七章 配置MariaDB SQL数据库–管理MariaDB用户和访问权限 的简述和讲解。希望能对看到此文章的小伙伴有所帮助。红帽认证专栏系列：RHCSA专栏：戏说 RHCSA 认证RHCE专栏：戏说 RHCE 认证此文章收录在RHCA专栏：RHCA 回忆录如果这篇【文章】有帮助到你，希望可以给【金鱼哥】点个赞，创作不易，相比官方的陈述，我更喜欢用【通俗易懂】的文笔去讲解每一个知识点。如果有对【运维技术】感兴趣，也欢迎关注❤️❤️❤️ 【金鱼哥】❤️❤️❤️，我将会给你带来巨大的【收获与惊喜】！链接：https://bbs.huaweicloud.com/blogs/351174

本章节介绍如何在MariaDB中创建和恢复备份。在数据库操作中，是非常重要的一个内容，必须学会。个人简介：大家好，我是 金鱼哥，CSDN运维领域新星创作者，华为云·云享专家个人资质：CCNA、HCNP、CSNA（网络分析师），软考初级、中级网络工程师、RHCSA、RHCE、RHCA、RHCI、ITIL格言：努力不一定成功，但要想成功就必须努力1. 创建MariaDB数据库备份有两种方式备份MariaDB：逻辑备份，将信息导出为文本文件，其中包含重建数据库所需的SQL命令。物理备份，它复制包含数据库内容的原始数据库目录和文件。每种备份都有其优缺点。逻辑备份特点通过查询数据库来检索数据库结构。逻辑备份具有高度可移植性，在某些情况下可以恢复到另一个数据库提供者(如PostgreSQL)。备份比较慢，因为服务器必须访问数据库信息并将其转换为逻辑格式。在服务器在线时执行。备份不包括日志或配置文件。物理备份特点由数据库目录和文件夹的原始副本组成。输出更紧凑。备份可以包括日志和配置文件。只能移植到其他具有类似硬件和软件的机器上。比逻辑备份更快。应在服务器脱机时执行，或在数据库中的所有表都被锁定时执行，以防止在备份期间发生更改。2. 执行逻辑备份使用mysqldump命令执行逻辑备份:[root@host ~]# mysqldump -u root -p inventory > /backup/inventory.dump​ 选择要备份的数据库 ------ 输出的备份文件注意：要在逻辑上备份所有数据库，请使用–all-databases选项:[root@host ~]# mysqldump -u root -p --all-databases > /backup/mariadb.dump这种类型的备份包括mysql数据库，其中包括所有user信息。逻辑备份的输出是一系列SQL语句。作为一个例子，下面是一个mysql数据库的转储代码片段:LOCK TABLES `user` WRITE; /*!40000 ALTER TABLE `user` DISABLE KEYS */; INSERT INTO `user` VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y' ,'N','N','N','N','N','N','N','N','','','','',0,0,0,0,'',''),('localhost.localdomai n','','','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N' ,'N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0,'',''),('localh ost','mobius','*84BB5DF4823DA319BBF86C99624479A198E6EEE9','N','N','N','N','N','N ','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N ','N','N','N','','','','',0,0,0,0,'',''); /*!40000 ALTER TABLE `user` ENABLE KEYS */; UNLOCK TABLES;注意，mobius的加密密码很容易看到，所以要注意这种备份的存储。在逻辑备份期间，当从各个表中读取它们时，将对它们进行锁定和解锁。重要：运行mysqldump时连接的MariaDB用户必须有足够的权限查看数据库服务器上的数据库。当运行mysqldump时，连接的MariaDB用户至少需要SELECT权限用于转储表，SHOW VIEW权限用于转储视图，TRIGGER权限用于转储触发器。逻辑备份的有用选项选项描述--add-drop-table在每个CREATE TABLE语句之前添加一个DROP TABLE语句。--no-data只转储数据库结构，不转储内容。--lock-all-tables在复制完成之前，不能在数据库的任何位置插入新记录。这个选项对于确保备份完整性非常重要。--add-drop-database在每个CREATE DATABASE语句之前添加一个DROP DATABASE语句。3. 执行物理备份mariabackup工具是由AppStream存储库中的mariadb-backup包提供的。mariabackup工具对MariaDB服务器执行完全物理在线备份。确保安装了mariadb-backup包(安装mariadb-server包时默认安装):[root@host ~]# yum install mariadb-backup创建存放备份文件的目标目录。如果是全量备份，则目标目录必须为空。[root@host ~]# mkdir -p /var/mariadb/backup/执行备份：[root@host ~]# mariabackup --backup --target-dir /var/mariadb/backup/ --user root --password redhat [00] 2020-06-08 21:13:15 >> log scanned up to (1721365) [00] 2020-06-08 21:13:15 Executing UNLOCK TABLES [00] 2020-06-08 21:13:15 All tables unlocked [00] 2020-06-08 21:13:15 Copying ib_buffer_pool to /var/mariadb/backup/ ib_buffer_pool [00] 2020-06-08 21:13:15 ...done [00] 2020-06-08 21:13:15 Backup created in directory '/var/mariadb/backup/' [00] 2020-06-08 21:13:15 Writing backup-my.cnf [00] 2020-06-08 21:13:15 ...done [00] 2020-06-08 21:13:15 Writing xtrabackup_info [00] 2020-06-08 21:13:15 ...done [00] 2020-06-08 21:13:15 Redo log (from LSN 1721356 to 1721365) was copied. [00] 2020-06-08 21:13:15 completed OK! # 确认备份目录内容: [root@host ~]# ls -F /var/mariadb/backup/ aria_log.00000001 ib_buffer_pool inventory/ xtrabackup_checkpoints aria_log_control ibdata1 mysql/ xtrabackup_info backup-my.cnf ib_logfile0 performance_schema/注意：在命令行上会公开用户名和密码，另一种替代方法是在/etc/my.cnf.d目录中创建一个凭据文件，以存储执行备份的用户的身份验证信息。[root@host ~]# cat /etc/my.cnf.d/mariabackup.cnf [xtrabackup] user=root password=redhat使用MariaDB根用户执行备份的替代方法是在MariaDB中创建一个具有RELOAD、LOCK TABLES和REPLICATION CLIENT特权的用户。4. 恢复备份在还原备份时，它会用备份的内容覆盖数据库服务器的内容。如果数据库中的数据比备份中的数据更新，则会丢失数据。恢复逻辑备份使用mysql命令从备份文件执行逻辑恢复：[root@host ~]# mysql -u root -p inventory < /backup/mariadb.dump​ 选择要恢复的数据库 ----- 备份文件恢复物理备份使用mariabackup工具与以下选项之一执行从备份的物理恢复:--copy-back 保留原有备份文件。--move-back 将备份文件移动到数据目录下，然后删除原有的备份文件。# 确保mariadb服务已停止: [root@host ~]# systemctl stop mariadb # 确定数据目录的位置，并确保它为空: [root@host ~]# grep '^datadir' /etc/my.cnf.d/mariadb-server.cnf datadir=/var/lib/mysql [root@host ~]# rm -rf /var/lib/mysql/* # 使用mariabackup恢复备份文件: [root@host ~]# mariabackup --copy-back --target-dir=/var/mariadb/backup/ ..... [00] 2020-06-08 22:26:08 completed OK! # 确保数据文件的用户和组所有权设置为mysql: [root@host ~]# chown -R mysql:mysql /var/lib/mysql/ # 启动mariadb服务: [root@host ~]# systemctl start mariadb5. 课本练习[student@workstation ~]$ lab database-backups start明确库与表的内容[student@servera ~]$ mysql -u root -p Enter password: redhat MariaDB [(none)]> USE inventory; MariaDB [inventory]> SELECT * FROM product; +----+-------------------+---------+-------+-------------+-----------------+ | id | name | price | stock | id_category | id_manufacturer | +----+-------------------+---------+-------+-------------+-----------------+ | 1 | ThinkServer TS140 | 539.88 | 20 | 2 | 4 | | 2 | ThinkServer RD630 | 2379.14 | 20 | 2 | 4 | | 3 | RT-AC68U | 219.99 | 10 | 1 | 3 | | 4 | X110 64GB | 73.84 | 100 | 3 | 1 | +----+-------------------+---------+-------+-------------+-----------------+ 4 rows in set (0.000 sec) MariaDB [inventory]> exit Bye备份[student@servera ~]$ mysqldump -u root -p inventory > inventory-backup.sql Enter password: redhat [student@servera ~]$删除表里内容[student@servera ~]$ mysql -u root -p Enter password: redhat MariaDB [(none)]> USE inventory; MariaDB [(inventory)]> DELETE FROM product WHERE id=1; Query OK, 1 row affected (0.005 sec) MariaDB [inventory]> SELECT * FROM product; +----+-------------------+---------+-------+-------------+-----------------+ | id | name | price | stock | id_category | id_manufacturer | +----+-------------------+---------+-------+-------------+-----------------+ | 2 | ThinkServer RD630 | 2379.14 | 20 | 2 | 4 | | 3 | RT-AC68U | 219.99 | 10 | 1 | 3 | | 4 | X110 64GB | 73.84 | 100 | 3 | 1 | +----+-------------------+---------+-------+-------------+-----------------+ 3 rows in set (0.000 sec) MariaDB [inventory]> exit Bye恢复[student@servera ~]$ mysql -u root -p inventory < inventory-backup.sql Enter password: redhat查询并验证[student@servera ~]$ mysql -u root -p Enter password: redhat MariaDB [(none)]> USE inventory; Query OK, 1 row affected (0.005 sec) MariaDB [inventory]> SELECT * FROM product; +----+-------------------+---------+-------+-------------+-----------------+ | id | name | price | stock | id_category | id_manufacturer | +----+-------------------+---------+-------+-------------+-----------------+ | 1 | ThinkServer TS140 | 539.88 | 20 | 2 | 4 | | 2 | ThinkServer RD630 | 2379.14 | 20 | 2 | 4 | | 3 | RT-AC68U | 219.99 | 10 | 1 | 3 | | 4 | X110 64GB | 73.84 | 100 | 3 | 1 | +----+-------------------+---------+-------+-------------+-----------------+ 4 rows in set (0.000 sec) MariaDB [inventory]> exit Bye完成实验[student@workstation ~]$ lab database-backups finish总结介绍逻辑备份和物理备份。介绍如何在MariaDB中创建数据库备份，包括逻辑备份和物理备份。介绍如何在MariaDB恢复备份。RHCA认证需要经历5门的学习与考试，还是需要花不少时间去学习与备考的，好好加油，可以噶。以上就是【金鱼哥】对 第七章 配置MariaDB SQL数据库–创建和恢复MariaDB备份 的简述和讲解。希望能对看到此文章的小伙伴有所帮助。红帽认证专栏系列：RHCSA专栏：戏说 RHCSA 认证RHCE专栏：戏说 RHCE 认证此文章收录在RHCA专栏：RHCA 回忆录如果这篇【文章】有帮助到你，希望可以给【金鱼哥】点个赞，创作不易，相比官方的陈述，我更喜欢用【通俗易懂】的文笔去讲解每一个知识点。如果有对【运维技术】感兴趣，也欢迎关注❤️❤️❤️ 【金鱼哥】❤️❤️❤️，我将会给你带来巨大的【收获与惊喜】！connect：https://bbs.huaweicloud.com/blogs/351175

JOIN 一直是数据库性能优化的老大难问题，本来挺快的查询，一旦涉及了几个 JOIN，性能就会陡降。而且，参与 JOIN 的表越大越多，性能就越难提上来。其实，让 JOIN 跑得快的关键是要对 JOIN 分类，分类之后，就能利用各种类型 JOIN 的特征来做性能优化了。JOIN 分类有 SQL 开发经验的同学都知道，绝大多数 JOIN 都是等值 JOIN，也就是关联条件为等式的 JOIN。非等值 JOIN 要少见得多，而且多数情况也可以转换成等值 JOIN 来处理，所以我们可以只讨论等值 JOIN。等值 JOIN 主要又可以分为两大类：外键关联和主键关联。外键关联是指用一个表的非主键字段，去关联另一个表的主键，前者称为事实表，后者为维表。比如下图中，订单表是事实表，客户表、产品表、雇员表是维表。外键表是多对一关系，而且是不对称的，事实表和维表的位置不能互换。需要说明的是，这里说的主键是指逻辑上的主键，也就是在表中取值唯一、可以用于唯一确定某条记录的字段(或字段组)，不一定在数据库表上建立过主键。主键关联是指用一个表的主键关联另一个表的主键或部分主键。比如下图中客户和 VIP 客户、订单表和订单明细表的关联。客户和 VIP 客户按照主键关联，这两个表互为同维表。订单则是用主键去关联明细的部分主键，我们称订单表是主表，明细表是子表。同维表是一对一关系。且同维表之间是对称的，两个表的地位相同。主子表则是一对多关系，而且是不对称的，有明确的方向。仔细观察会发现，这两类 JOIN 都涉及到主键了。而不涉及主键的 JOIN 会导致多对多关系，大多数情况都没有业务意义。换句话说，上述这两大类 JOIN 涵盖了几乎全部有业务意义的 JOIN。如果我们能利用 JOIN 总会涉及主键这个特征做性能优化，能解决掉这两大类 JOIN，其实也就意味着解决了大部分 JOIN 性能问题。但是，SQL 对 JOIN 的定义并不涉及主键，只是两个表做笛卡尔积后再按某种条件过滤。这个定义很简单也很宽泛，几乎可以描述一切。但是，如果严格按这个定义去实现 JOIN，也就没办法在性能优化时利用主键的特征了。SPL 改变了 JOIN 的定义，专门针对这两大类 JOIN 分别处理，利用了主键的特征减少运算量，从而实现性能优化的目标。下面我们来看看 SPL 具体是怎么做的。外键关联如果事实表和维表都不太大，可以全部装入内存，SPL 提供了外键地址化方法：先把事实表中的外键字段值转换为对应维表记录的地址，之后引用维表字段时，就可以用地址直接取出了。以前面的订单表、雇员表为例，假定这两个表已经被读入内存。外键地址化的工作机制是这样的：对于订单表某记录 r 的 eid 字段，到雇员表中找到这个 eid 字段值对应的记录，得到其内存地址 a，再将 r 的 eid 字段值替换成 a。对订单表的所有记录都做好这样的转换，就完成了外键地址化。这时候，订单表记录 r 要引用雇员表字段时，直接用 eid 字段存储的地址 a 取出雇员表记录和字段就可以了，相当于常数时间内就能取得雇员表的字段，不需要再到雇员表做查找。可以在系统启动时把事实表和维表读入内存，并一次性做好外键地址化，即预关联。这样，在后续关联计算时就能直接用事实表外键字段中的地址去取维表记录，完成高性能的 JOIN 计算。SQL 通常使用 HASH 算法来做内存连接，需要计算 HASH 值和比对，性能会比直接用地址读取差很多。SPL 之所以能实现外键地址化，是利用了维表的关联字段是主键这一特征。上面例子中，关联字段 eid 是雇员表的主键，具有唯一性。订单表中的每个 eid 只会唯一对应一条雇员记录，所以才能把每个 eid 转换成它唯一对应的那条雇员记录的地址。而 SQL 对 JOIN 的定义中没有主键的约定，就不能认定与事实表中外键关联的维表记录有唯一性，有可能发生与多条记录关联的情况。对于订单表的记录来讲，eid 值没有办法唯一对应一条雇员记录，就无法做到外键地址化了。而且 SQL 也没有记录地址这种数据类型，结果会导致每次关联时还是要计算 HASH 值并比对。只是两个表 JOIN 时，外键地址化和 HASH 关联的差别还不是非常明显。这是因为 JOIN 并不是最终目的，JOIN 之后还会有其它很多运算，JOIN 本身运算消耗时间的占比相对不大。但事实表常常会有多个维表，甚至维表还会有很多层。比如订单关联产品，产品关联供应商，供应商关联城市，城市关联国家等等。在关联表很多时，外键地址化的性能优势会更明显。下面的测试，在关联表个数不同的情况下对比 SPL 与 Oracle 的性能差异，可以看出在表很多时，外键地址化的优势相当明显：对于只有维表能装入内存，而事实表很大需要外存的情况，SPL 提供了外键序号化方法：预先将事实表中的外键字段值转换为维表对应记录的序号。关联计算时，分批读入新事实表记录，再用序号取出对应维表记录。以上述订单表、产品表为例，假定产品表已经装入内存，订单表存储在外存中。外键序号化的过程是这样：先读入一批订单数据，设其中某记录 r 中的 pid 对应的是内存中产品表的第 i 条记录。我们要将 r 中的 pid 字段值转换为 i。对这批订单记录都完成这样的转换后，再做关联计算时，从外存中分批读入订单数据。对于其中的记录 r，就可以直接根据 pid 值，去内存中的产品表里用位置取出相应的记录，也避免了查找动作。数据库通常会把小表读入内存，再分批读入大表数据，用哈希算法做内存连接，需要计算哈希值和比对。而 SPL 使用序号定位是直接读取，不需要进行任何比对，性能优势比较明显。虽然预先把事实表的外键字段转换成序号需要一定成本，但这个预计算只需要做一次，而且可以在多次外键关联中得到复用。SPL 外键序号化同样利用了维表关联字段是主键的特征。如前所述，SQL 对 JOIN 的定义没有主键的约定，无法利用这一特征做到外键序号化。另外，SQL 使用无序集合的概念，即使我们事先把外键序号化了，数据库也无法利用这个特点，不能在无序集合上使用序号快速定位的机制，最快也就是用索引查找。而且，数据库并不知道外键被序号化了，仍然会去计算 HASH 值和比对。下面这个测试，在不同并行数情况下，对比 SPL 和 Oracle 完成大事实表、小维表关联计算的速度，SPL 跑的比 Oracle 快 3 到 8 倍。测试结果见下图：如果维表很大也需要外存，而事实表较小能装入内存，SPL 则提供了大维表查找机制。如果维表和事实表都很大，SPL 则使用单边分堆算法。对于维表过滤后再关联的情况，SPL 提供了索引复用方法及对位序列等方法。数据量大到需要分布式计算时，如果维表较小，SPL 采用复写维表机制，将维表在集群节点上复制多份；如果维表很大，则采用集群维表方法以保证随机访问。这两种方法都可以有效的避免 Shuffle 动作。相比而言，SQL 体系下不能区分出维表，HASH 拆分方法要将两个表都做 Shuffle 动作，网络传输量要大得多。主键关联主键关联涉及的表一般都比较大，需要存储在外存中。SPL 为此提供了有序归并方法：预先将外存表按照主键有序存储，关联时顺序取出数据做归并计算。以客户和 VIP 客户两个表做内连接为例，假设已经预先将两个表按照主键 cid 有序存储在外存中。关联时，从两个表的游标中读取记录，逐条比较 cid 值。如果 cid 相等，则将两表的记录合并成结果游标的一条记录返回。如果不相等，则 cid 小的那个游标再读取记录，继续判断。重复这些动作直到任何一个表的数据被取完，返回的游标就是 JOIN 的结果。对于两个大表关联，数据库通常使用哈希分堆算法，复杂度是乘法级的。而有序归并算法复杂度是加法级，性能会好很多。而且，数据库做大数据的外存运算时，哈希分堆会产生缓存文件的读写动作。有序归并算法则只需要对两个表依次遍历，不必借助外存缓存，可以大幅降低 IO 量，有巨大的性能优势。预先按照主键排序的成本虽高，但是一次性做好即可，以后就总能使用归并算法实现 JOIN，性能可以提高很多。同时，SPL 也提供了在有追加数据时仍然保持数据整体有序的方案。这类 JOIN 的特征在于关联字段是主键或部分主键，有序归并算法正是根据这个特征来设计的。因为不管是同维表还是主子表，关联字段都不会是主键之外的其他字段，所以我们将关联表按照主键有序这一种方式排序存储就可以了，不会出现冗余。而外键关联就不具备这个特征，不能使用有序归并。具体来说，是因为事实表的关联字段不是主键，会存在多个要参与关联的外键字段，我们不可能让同一个事实表同时按多个字段都有序。SQL 对 JOIN 的定义不区分 JOIN 类型，不假定某些 JOIN 总是针对主键的，就没办法从算法层面上利用主键关联的特征。而且，前面说过 SQL 基于无序集合概念，数据库不会刻意保证数据的物理有序性，很难实施有序归并算法。有序归并算法的优势还在于易于分段并行。以订单和订单明细按 oid 关联为例，假如将两表都按照记录数大致平均分为 4 段，订单第 2 段的 oid 有可能会出现在明细第 3 段，类似的错位会导致错误的计算结果。SPL 再次利用主键 oid 的有序性，提供同步分段机制，解决了这个问题：先将有序的订单表分为 4 段，再找到每一段起止记录的 oid 值形成 4 个区间，将明细表也分成同步的 4 段。这样，在并行计算时两表对应分段就不会出现错位了。由于明细表也对 oid 有序，可以迅速地按照起止 oid 定位，不会降低有序归并的性能。传统的 HASH 分堆技术实现并行就比较困难了，多线程做 HASH 分堆时需要同时向某个分堆写出数据，造成共享资源冲突;而下一步实现某组分堆关联时又会消费大量内存，无法实施较大的并行数量。实际测试证明，在相同情况下，我们对两个大表做主键关联测试，结果是 SPL 比 Oracle 快了近 3 倍：除了有序归并，SPL 还提供了很多高性能算法，全面提高主键关联 JOIN 的计算速度。包括：附表机制，可以将多表一体化存储，减少存储数据量的同时，还相当于预先完成了关联，不需要再比对了；关联定位算法，实现先过滤再关联，可以避免全表遍历，获得更好的性能等等。当数据量继续增加，需要多台服务器集群时，SPL 提供复组表机制，将需要关联的大表按照主键分布到集群节点上。相同主键的数据在同一节点，避免分机之间的数据传输，也不会出现 Shuffle 动作。回顾与总结回顾上面两大类、各场景 JOIN，采用 SPL 分情况提供的高性能算法，可以利用不同类型 JOIN 的特征提速，让 JOIN 跑得更快。SQL 对上述这么多种 JOIN 场景笼统的处理，就没办法针对不同 JOIN 的特征来实施这些高性能算法。比如：事实表和维表都装入内存时，SQL 只能按照键值计算 HASH 和比对，无法利用地址直接对应;SQL 数据表无序，在大表按照主键关联时无法做到有序归并，只能使用 HASH 分堆，有可能会出现多次缓存的现象，性能有一定的不可控性。并行计算方面，SQL 单表计算时还容易做到分段并行，多表关联运算时一般就只能事先做好固定分段，很难做到同步动态分段，这就难以根据机器的负载临时决定并行数量。对于集群运算也是这样，SQL 在理论上不区分维表和事实表，要实现大表 JOIN 就会不可避免地产生占用大量网络资源的 HASH Shuffle 动作，在集群节点数太多时，网络传输造成的延迟会超过节点多带来的好处。SPL 设计并应用了新的运算和存储模型，可以在原理和实现上解决 SQL 的这些问题。对于 JOIN 的不同分类和场景，程序员有针对性的采取上述高性能算法，就能获得更快的计算速度，让 JOIN 跑得更快。

Oracle操作如下：GaussDB A 8.1.0 执行报错，tables不存在：