MYSQL使用基础、进阶分享

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，属于Oracle旗下产品，是最流行的关系型数据库管理系统之一。

端口是3306。

表很多时，使用linux脚本，需要根据需要修改一下：

和创建一样，可以加上 if exists

可两篇文章：

如：

用于在已有的表中添加、删除或修改列。

添加 ADD

或

默认是添加到最后，但可以指定位置。FIRST ：添加最前

AFTER 字段名> ：添加指定字段之后

例子：

删除 DROP

修改 MODIFY 主要修改原列的类型或约束条件 同样可以用 FIRST 和 AFTER 字段名> ，代表的是修改到哪里。

修改字段名 CHANGE

可以把表2的数据复制到表1中，但 不能复制约束性条件 。

单行

多行，注意 只有一个VALUES ：

不写 (行1, 行2...) 这一部分的话，默认一一对应

除了以上方法外，还可以用SET为每一行附上相应的值。

假如没有筛选的话，就给全部都修改了。可以用 WHERE 筛选。

假如 没有筛选的话，就给全部删除了 。相当于清空。

清空

先把表删除，然后再建一个。与 DELETE FROM 相比， TRUNCATE 的效率更快，因为 DELETE FROM 是把记录逐条删除的。

查询执行的顺序

FROM -->WHERE -->SELECT -->GROUP BY -->HAVING -->ORDER BY -->LIMIT

注意

当数据很大，上百万的时候，使用LIMIT ... OFFSET ..的方式进行分页十分浪费资源且耗时长。最好是结合WHERE使用，如：

REGEXP 使用正则表达进行匹配。 查询时，需要搭配WHERE或HAVING使用 。

两个表之间有交集且要用到两个表的数据时，可以使用内连接查询。

LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

用法：

RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。 把LEFT JOIN的表1、表2调换顺序，就是REGHT JOIN 。

FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行. 相当于结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

但 MySQL中不支持 FULL OUTER JOIN 。

即SELECT嵌套。

IN 一个查询结果作为另一个查询的条件。 如：

EXISTS 用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False。True时执行。 如：

索引的本质是一种排好序的数据结构。利用索引可以提高查询速度。

常见的索引有：

MySQL通过外键约束来保证表与表之间的数据的完整性和准确性。 外键的使用条件：

外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联 *** 作。

对已有的两个表增加外键 比如：主表为A，子表为B，外键为aid，外键约束名字为a_fk_b

为子表添加一个字段，当做外键

为子表添加外键约束条件

假如删除记录报错： [Err] 1451 -Cannot deleteorupdatea parent row: aforeignkeyconstraintfails (...)

这是因为MySQL中设置了foreign key关联，造成无法更新或删除数据。可以通过设置 FOREIGN_KEY_CHECKS 变量来避免这种情况。 第一步：禁用外键约束，我们可以使用： SETFOREIGN_KEY_CHECKS=0 第二步：删除数据 第三步：启动外键约束，我们可以使用： SETFOREIGN_KEY_CHECKS=1 查看当前FOREIGN_KEY_CHECKS的值，可用如下命令： SELECT @@FOREIGN_KEY_CHECKS

使用 UNION 来组合两个查询，如果第一个查询返回 M 行，第二个查询返回 N 行，那么组合查询的结果一般为 M+N 行。

每个查询必须包含相同的列、表达式和聚集函数。

默认会去除相同行，如果需要 保留 相同行，使用 UNION ALL 。

只能包含一个 ORDER BY 子句，并且必须位于语句的最后 。

内置函数很多, 见: MySQL 函数

我们一般使用 START TRANSACTION 或 BEGIN 开启事务, COMMIT 提交事务中的命令, SAVEPOINT : 相当于设置一个还原点, ROLLBACK TO : 回滚到某个还原点下

一般的使用格式如下:

开启事务时, 默认加锁

根据类型可分为共享锁（SHARED LOCK）和排他锁（EXCLUSIVE LOCK）或者叫读锁（READ LOCK）和写锁（WRITE LOCK）。

根据粒度划分又分表锁和行锁。表锁由数据库服务器实现，行锁由存储引擎实现。

除此之外,我们可以显示加锁

加锁时, 如果没有索引，会锁表，如果加了索引，就会锁行

InnoDB默认支持行锁，获取锁是分步的，并不是一次性获取所有的锁，因此在锁竞争的时候就会出现死锁的情况

解决方法:

即ACID特性:

由于并发事务会引发上面这些问题, 我们可以设置事务的隔离级别解决上面的问题.

MySQL的默认隔离级别（可重复读）

查看当前会话隔离级别

方式1

方式2

设置隔离级别

主从集群的示意图如下:

主要涉及三个线程： binlog 线程、 I/O 线程和 SQL 线程。

同步流程:

由于MySQL主从集群只会从主节点同步到从节点, 不会反过来同步, 所以需要读写分离

读写分离需要在业务层面实现 , 写数据只能在主节点上完成, 而读数据可以在主节点或从节点上完成

索引是帮助MySQL高效获取数据的排好序的数据结构

MySQL的索引有

推荐两个在线工具:

简单来说, B树是在红黑树(一个平衡二叉树)的基础上将一个节点存放多个值, 实现的, 降低了树的高度, 每个节点都存放索引及对应数据指针, 同一层的节点是递增的

而B+树在B树的基础上进行优化, 非叶子节点存放 子节点的开始的索引, 叶子节点存放索引和数据的指针, 且叶子节点之间有双向的指针

如下示意图:

不同的引擎, 主键索引存放的数据也不一样, 比如常见的 MyISAM 和 InnoDB

MyISAM 的B+树叶子节点存放表数据的指针, InnoDB 的B+树叶子节点存放处主键外的数据

其他的:

即多个列组成一个索引, 语法:

由于联合索引的B+树的结构, 根据列建立, 所以我们的查找条件也要根据索引列的顺序( where column1=x, column2=y,columnN... ), 否则会全表扫描

如果你对列进行了 （+，-，*，/，!） , 那么都将不会走索引。

OR 引起的索引失效

OR 导致索引是在特定情况下的，并不是所有的 OR 都是使索引失效，如果OR连接的是 同 一个字段，那么索引 不会失效 ， 反之索引失效 。

这个我相信大家都明白，模糊搜索如果你前缀也进行模糊搜索，那么不会走索引。

这两种用法，也将使索引失效。另 IN 会走索引，但是当IN的取值范围较大时会导致索引失效，走全表扫描, 见: MySQL中使用IN会不会走索引

不走索引。

走索引。

所以设计表的时候, 建议不可为空, 而是将默认值设置为 "" ( NOT NULL DEFAULT "" )

在 Web 应用程序中跨大型数据集分页记录似乎是一个简单的问题，但实际上很难扩展。两种主要的分页策略是偏移/限制和游标。

我们将首先看一下这两种方法，然后稍作修改，可以使偏移/限制非常高效。

偏移/限制分页

偏移/限制方法是迄今为止最常见的方法，它通过跳过一定数量的记录（页）并将结果限制为一页来工作。

例如，假设您的应用程序配置为每页显示 15 条记录。您的 SQL 将如下所示：

这是最常见的，因为它非常简单，易于推理，并且几乎每个框架都支持它。

除了易于实现之外，它还具有页面可直接寻址的优点。例如，如果您想直接导航到第 20 页，您可以这样做，因为该偏移量很容易计算。

但是有一个主要的缺点，它潜伏在数据库处理偏移量的方式中。偏移量告诉数据库放弃从查询中返回的前N个结果。不过数据库仍然要从磁盘上获取这些行。

如果你丢弃的是100条记录，这并不重要，但如果你丢弃的是100,000条记录，数据库就会为了丢弃这些结果而做大量的工作。

在实践中，这意味着第一个页面会快速加载，之后的每一个页面都会变得越来越慢，直到你达到一个点，网络请求可能会直接超时。

基于游标的分页

基于游标的分页弥补了偏移/限制的一些不足，同时引入了一些自己的不足。

基于游标的分页是通过存储一些关于最后呈现给用户的记录的状态，然后根据这个状态来进行下一次查询。

因此，它不是按顺序获取所有的记录并丢弃前N条，而是只获取最后一个位置N之后的记录。

如果按ID排序，SQL可能看起来像这样。

你可能已经看到了其中的好处。因为我们知道上次向用户展示的ID，我们知道下一个页面将以一个更高的ID开始。我们甚至不需要检查ID较低的行，因为我们百分之百肯定地知道那些行不需要被显示。

在上面的例子中，我特别说明了ID可能不是连续的，也就是说，可能有缺失的记录。这使得我们无法计算出哪些记录会出现在某一页面上，你必须跟踪之前那一页面上的最后一条记录是什么。

与偏移/限制分页不同，使用游标分页时，页面不能直接寻址，你只能导航到 "下一页 "或 "上一页"。

不过光标分页的好处是在任何数量的页面上都很迅速。它也很适合无限滚动，在这种情况下，页面首先不需要可以直接寻址。

Laravel文档中有一些关于偏移量和游标之间的权衡的好的背景。

https://laravel.com/docs/pagination cursor -vs-offset-pagination

考虑到所有这些，让我们来看看一个偏移/限制优化，可以使它的性能足以在成千上万的页面上使用。

使用递延join的Offset/Limit

递延连接（deferred join ）是一种技术，它将对要求的列的访问推迟到应用了偏移量和限制之后。

使用这种技术，我们创建一个内部查询，可以用特定的索引进行优化，以获得最大的速度，然后将结果连接到同一个表，以获取完整的行。

它看起来像这样：

这种方法的好处可以根据你的数据集有很大的不同，但是这种方法允许数据库尽可能少地检查数据，以满足用户的意图。

查询中 "昂贵的 "select *部分只在与内部查询相匹配的15条记录上运行。所有数据的Select都被推迟了，因此被称为推迟join。

这种方法不太可能比传统的偏移/限制性能差，尽管它是可能的，所以一定要在你的数据上进行测试!

Laravel实现

我们如何把这一点带到我们最喜欢的网络框架，如Laravel和Rails？

让我们具体看看Laravel，因为我不知道Rails。

感谢Laravel的macroable特性，我们可以扩展Eloquent Query Builder来添加一个新的方法，叫做deferredPaginate。为了保持一致性，我们将模仿常规分页的签名。

我们将尝试做尽可能少的自定义工作，并将大部分工作留给 Laravel。

这是我们要做的：

这应该为我们提供 LaravelLengthAwarePaginator 和延迟连接的所有好处！

一个Github仓库

递延Join和覆盖索引

还没有完成...

使用递延Join的主要好处是减少了数据库必须检索然后丢弃的数据量。我们可以通过帮助数据库获得它需要的数据而更进一步，而无需获取底层行。

这样做的方法称为“覆盖索引covering index”，它是确保快速偏移/限制分页的最终解决方案。

覆盖索引是一个索引，在这个索引中，查询的所有需要的字段都包含在索引本身中。当一个查询的所有部分都能被一个索引 "覆盖 "时，数据库根本不需要读取该行，它可以从索引中获得它需要的一切。

请注意，覆盖索引并不是以任何特殊方式创建的。它只是指一个索引满足了一个查询所需要的一切的情况。一个查询上的覆盖索引很可能不是另一个查询上的覆盖索引。

在接下来的几个例子中，我们将使用这个基本的表，我把它填满了~1000万条记录。

让我们看一个仅select索引列的简单查询。在这种情况下，我们将从email表中进行select contacts。

在这种情况下，数据库根本不需要读取基础行。在MySQL中，我们可以通过运行一个解释并查看额外的列来验证这一点：

extra: using index告诉我们，MySQL能够只使用索引来满足整个查询，而不看基础行。

如果尝试select name from contacts limit 10， 我们将期望MySQL必须到该行去获取数据，因为名字name没有被索引。这正是发生的情况，由下面的解释显示。

extra不再显示 using index，所以我们没有使用覆盖索引。

假设你每页有15条记录，你的用户想查看第1001页，你的内部查询最终会是这样的。

select id from contacts order by id limit 15 OFFSET 150000

explain结果显示：

MySQL能够单看索引来执行这个查询。它不会简单地跳过前15万行，在使用offset是没有办法的，但它不需要读取15万行。(只有游标分页可以让你跳过所有的行）。

即使使用覆盖索引和延迟连接，当你到达后面的页面时，结果也会变慢，尽管与传统的偏移/限制相比，它应该是最小的。使用这些方法，你可以轻易地深入到数千页。

更好的覆盖索引

这里的很多好处取决于拥有良好的覆盖索引，所以让我们稍微讨论一下。一切都取决于您的数据和用户的使用模式，但是您可以采取一些措施来确保查询的最高命中率。

这将主要与 MySQL 对话，因为那是我有经验的地方。其他数据库中的情况可能会有所不同。

大多数开发人员习惯于为单列添加索引，但没有什么能阻止您向多列添加索引。事实上，如果您的目标是为昂贵的分页查询创建覆盖索引，您几乎肯定需要一个多列索引。

当你试图为分页优化一个索引时，一定要把按列排序放在最后。如果你的用户要按update_at排序，这应该是你复合索引中的最后一列。

看看下面这个包括三列的索引。

在MySQL中，复合索引是从左到右访问的，如果一个列缺失，或者在第一个范围条件之后，MySQL会停止使用一个索引。

MySQL 将能够在以下场景中使用该索引：

如果你跳过is_archived，MySQL将无法访问update_at，将不得不诉诸于没有该索引的排序，或者根本不使用该索引，所以要确保你有相应的计划。

主键始终存在

在MySQL的InnoDB中，所有的索引都附加了主键。这意味着（email）的索引实际上是（email，id）的索引，当涉及到覆盖索引和延迟连接时，这是相当重要的。

查询select email from contacts order by id完全被email上的一个索引所覆盖，因为InnoDB将id附加到了该索引上。

使用我们上面的综合例子，你可以看到这有什么好处。

因为复合索引涵盖了is_deleted, is_archived, updated_at, 和（通过InnoDB的功能）id，整个查询可以仅由索引来满足。

降序索引

大多数时候，用户都在寻找 "最新的 "项目，即最近更新或创建的项目，这可以通过按update_at DESC排序来满足。

如果你知道你的用户主要是以降序的方式对他们的结果进行排序，那么特别将你的索引设为降序索引可能是有意义的。

MySQL 8是第一个支持降序索引的MySQL版本。

如果你在explain的Extra部分看到向后索引扫描，你也许可以配置一个更好的索引。

前向索引扫描比后向扫描快~15%，所以你要按照你认为你的用户最常使用的顺序添加索引，并为少数使用情况承担惩罚。

太阳底下无新事

这种使用偏移/限制分页与延迟连接和覆盖索引的方法并不是银d。

仅仅是递迟连接就可以让你的速度得到很好的提升，但是需要花一些额外的心思来设计正确的索引以获得最大的好处。

有一种观点认为，递延连接应该是框架中默认的偏移offset/限制limit方法，而任何时候覆盖索引的出现都只是一种奖励。我还没有在足够多的生产环境中测试过，所以还没有强烈主张这样做。

使用MySQL的递延Join连接实现高效分页 - Aaron

方法/步骤

首先当我们通过命令行登陆到MySQL的时候,它会打印出来一下欢迎信息,其实这个欢迎信息告诉了我们很多有用的信息.

例如:当前MySQL服务器的版本号

使用那个命令来参看MySQL的帮助信息

接下来我们查看一下MySQL文档包含哪些部分,按照MySQL的大分类查询一下,有哪些帮助主题供我们查看.

命令为: ? contents

如果想查看上面大分类具体某一个分类应该怎么办那?同样的道理用 ? 大分类的名称.

例如: ? Data Types [大小写无所谓]

同理我们想具体的查看Data Types下面的INT类型的取值范围怎么办那?也可以用? INT得到相应的帮助信息

对应的命令:? Data Types

? INT

还有在开发过程中经常遇到MySQL的一些函数不知道怎么用,应该怎么查看那?接下来我们就用常用的字符串函数举一个例子.

对应的命令: ? Functions

? String Functions

? CONCAT

最后就我们常用的一些SQL的语法,例如建表语句或者是创建用户的语法怎么写,再做一个示范.

对应命令为:? GRANT

7

记住上面的帮助命令的使用,我们即使在不联网的情况下也能快速找到我们想要的帮助信息,提高工作效率.

---