mysql单表数据过亿查询很慢

转载

误会一场 2024-12-09 08:48:54

文章标签 mysql单表数据过亿查询很慢数据库大数据人工智能 SQL 文章分类 MySQL 数据库

如今，结构查询语言已成为处理和查询关系数据库中数据的标准方法，尽管产品之间具有专有的扩展。 SQL的易用性和普遍性甚至导致许多“ NoSQL ”或非关系型数据存储（例如Hadoop ）的创建者采用SQL的子集或提出自己的类似SQL的查询语言。

但是SQL并不总是关系数据库的“通用”语言。从一开始（大约1980年）开始，SQL就对它发出了一定的打击。当时包括我在内的许多研究人员和开发人员都认为，SQL的开销会使它在生产数据库中变得不切实际。

[您应该使用哪个数据库？ 让InfoWorld成为您的指南。 最好的分布式关系数据库 。 • 最好的NoSQL数据库 。 • 最好的图形数据库 。 | 通过InfoWorld机器学习和分析报告时事通讯深入了解分析和大数据。 ]

显然，我们错了。但是许多人仍然认为，就SQL的易用性和可访问性而言，运行时性能所要求的价格通常过高。

SQL历史

在没有SQL之前，数据库具有紧密的导航编程接口，并且通常围绕称为CODASYL数据模型的网络模式进行设计。 CODASYL（数据系统语言委员会）是一个联盟，负责COBOL编程语言（始于1959年）和数据库语言扩展（始于10年后）。

当针对CODASYL数据库进行编程时，您正在导航至表示一对多关系的集合记录。较旧的分层数据库仅允许一条记录属于一个集合。网络数据库允许一条记录属于多个集合。

假设你要列举了学生就读于CS 101首先，你会发现"CS 101"在Courses的名称设置，设置为所有者或母公司Enrollees组，找到的第一个成员（ ffm的）的Enrollees集，这是一个Student记录，并将其列出。然后，您将进入一个循环：查找下一个成员（ fnm ）并将其列出。当fnm失败时，您将退出循环。

对于数据库程序员来说，这似乎是很多繁琐的工作，但是在执行时却非常有效。像加州大学伯克利分校的Michael Stonebraker和Ingres这样的专家指出，在像IDMS这样的CODASYL数据库中进行这种查询所花的时间大约是使用SQL在关系数据库上进行相同查询所花费的CPU时间的一半和不到一半的内存。。

为了进行比较，返回CS 101中所有学生的等效SQL查询将类似于

SELECT student.name FROM courses, enrollees, students WHERE course.name ="CS 101"

正如我将在下面解释的那样，该语法意味着一个关系内部联接（实际上是其中的两个），并且省略了一些重要的细节，例如用于联接的字段。

关系数据库和SQL

您为什么要放弃执行速度和内存使用两方面的改善？有两个主要原因：易于开发和可移植性。与性能和内存需求相比，我认为在1980年这两个都不重要，但是随着计算机硬件的改进和价格的降低，人们不再关心执行速度和内存，而更加担心开发成本。

换句话说，摩尔定律杀死了CODASYL数据库，转而使用关系数据库。碰巧的是，开发时间的缩短是很明显的，但是SQL可移植性却变成了梦dream以求的事情。

关系模型和SQL从何而来？ EF“ Ted” Codd是IBM圣何塞研究实验室的计算机科学家，他于1960年代研究了关系模型的理论，并于1970年发表。IBM实施关系数据库的努力很慢，以保护企业的收入。其CODASYL数据库IMS / DB。当IBM最终开始其System R项目时，开发团队（Don Chamberlin和Ray Boyce）不在Codd的领导之下，他们忽略了Codd 1971年的Alpha关系语言论文来设计自己的语言SEQUEL（结构化英语查询语言）。 1979年，在IBM尚未发布产品之前，拉里·埃里森（Larry Ellison）将该语言整合到了他的Oracle数据库中（使用IBM发行前的SEQUEL出版物作为其规格）。 SEQUEL很快成为SQL，以避免国际商标侵权。

正如SQL专家迈克尔·斯通布雷克（Michael Stonebraker）所说，“ SQL的通俗竞争”不仅来自Oracle和IBM，还来自客户。雇用或培训CODASYL数据库设计师和程序员并不容易，因此SEQUEL（和SQL）看起来更具吸引力。 SQL在1980年代后期如此吸引人，以至于许多数据库供应商实质上在他们的CODASYL数据库之上使用了SQL查询处理器，这令Codd感到非常沮丧，后者认为关系数据库必须从头开始设计才能成为关系数据库。

由Codd设计的纯关系数据库建立在元组上，元组被分组为关系，并与一阶谓词逻辑保持一致。实际的关系数据库的表包含字段，约束和触发器，并且表通过外键关联。 SQL用于声明要返回的数据，SQL查询处理器和查询优化器将SQL声明转换为由数据库引擎执行的查询计划。

SQL包括用于定义架构的子语言，数据定义语言（DDL），以及用于修改数据的子语言，数据操纵语言（DML）。两者都源于早期的CODASYL规范。 SQL中的第三种子语言通过SELECT语句和关系联接来声明查询。

SQL `SELECT`语句

SELECT语句告诉查询优化器要返回什么数据，要查找什么表，要遵循什么关系以及对返回的数据施加什么顺序。查询优化器必须自己弄清楚要使用哪些索引，以避免蛮力表扫描并获得良好的查询性能，除非特定的数据库支持索引提示。

关系数据库设计的部分技术取决于对索引的明智使用。如果省略频繁查询的索引，则在重读负载下整个数据库可能会变慢。如果索引太多，则整个数据库在大量写入和更新负载下可能会变慢。

另一个重要的技巧是为每个表选择一个好的唯一主键。您不仅必须考虑主键对普通查询的影响，还必须考虑主键在另一个表中作为外键出现时在联接中的播放方式，以及它将如何影响数据的引用位置。

在根据主键的值将数据库表分为不同卷（称为水平分片）的高级情况下，您还必须考虑主键将如何影响分片。提示：您希望表在卷之间均匀分布，这表明您不想将日期戳或连续整数用作主键。

SELECT语句的讨论可能开始很简单，但很快就会变得混乱。考虑：

SELECT * FROM Customers;

简单吧？它要求Customers表的所有字段和所有行。但是，假设“ Customers表具有一亿行和一百个字段，并且其中一个字段是用于注释的大文本字段。如果每行平均包含1 KB的数据，则通过每秒10兆位的网络连接拉低所有这些数据将花费多长时间？

也许您应该减少电汇金额。考虑：

SELECT TOP 100 companyName, lastSaleDate, lastSaleAmount, totalSalesAmount FROM Customers
WHERE state ="Ohio" AND city ="Cleveland"
ORDER BY lastSaleDate DESCENDING;

现在，您将减少很多数据。您要求数据库仅给您四个字段，只考虑克利夫兰的公司，仅给您提供最近销售的100家公司。但是，要在数据库服务器上最有效地执行此操作， Customers表需要WHERE子句的state+city索引，以及ORDER BY和TOP 100子句的lastSaleDate索引。

顺便说一句， TOP 100适用于SQL Server和SQL Azure，但不适用于MySQL或Oracle。在MySQL中，您可以在WHERE子句后使用LIMIT 100 。在Oracle中，您将在ROWNUM上使用一个绑定作为WHERE子句的一部分，即WHERE... AND ROWNUM <=100 。不幸的是，到目前为止，ANSI / ISO SQL标准（到目前为止有九种，从1986年到2016年一直延续），到目前为止，每个数据库都引入了自己的专有子句和功能。

SQL联接

到目前为止，我已经描述了单个表的SELECT语法。在解释JOIN子句之前，您需要了解外键和表之间的关系。我将通过使用SQL Server语法在DDL中使用示例来对此进行解释。

简短的版本很简单。您要在关系中使用的每个表都应具有主键约束；它可以是单个字段，也可以是表达式定义的字段的组合。例如：

CREATE TABLE Persons (
    PersonID int NOT NULL PRIMARY KEY,
    PersonName char(80),
    ...

每个需要与Persons相关的表都应具有一个与Persons主键相对应的字段，并且为了保持关系完整性，该字段应具有一个外键约束。例如：

CREATE TABLE Orders (
    OrderID int NOT NULL PRIMARY KEY,
    ...
    PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);

这两个语句的较长版本都使用CONSTRAINT关键字，可用于命名约束。那就是大多数数据库设计工具所生成的。

主键始终被索引并且是唯一的（字段值不能重复）。其他字段可以选择索引。为外键字段以及出现在WHERE和ORDER BY子句中的字段创建索引通常很有用，尽管并非总是如此，因为写和更新可能会产生开销。

您将如何编写查询以返回John Doe下的所有订单？

SELECT PersonName, OrderID FROM Persons
INNER JOIN Orders ON Persons.PersonID = Orders.PersonID
WHERE PersonName ="John Doe";

实际上， JOIN有四种类型： INNER ， OUTER ， LEFT和RIGHT 。 INNER JOIN是默认设置（您可以省略单词INNER ），它是仅包含两个表中包含匹配值的行的行。如果要列出人员是否有订单，则可以使用LEFT JOIN ，例如：

SELECT PersonName, OrderID FROM Persons
LEFT JOIN Orders ON Persons.PersonID = Orders.PersonID
ORDER BY PersonName;

当您开始执行将两个以上的表联接在一起，使用表达式或强制数据类型的查询时，语法一开始可能会有些麻烦。幸运的是，有一些数据库开发工具通常可以通过将表和字段从模式图拖放到查询图中来为您生成正确的SQL查询。

SQL存储过程

有时SELECT语句的声明性无法将您带到您想去的地方。大多数数据库都具有一种称为存储过程的功能。不幸的是，这是一个几乎所有数据库都使用ANSI / ISO SQL标准专有扩展的领域。

在SQL Server中，存储过程（或存储的proc）的初始方言是Transact-SQL（又名T-SQL）。在Oracle中，它是PL-SQL。两个数据库都为存储过程添加了其他语言，例如C＃，Java和R。简单的T-SQL存储过程可能只是SELECT语句的参数化版本。它的优点是易于使用和效率。存储过程在保存时（而不是每次执行时）都会得到优化。

一个更复杂的T-SQL存储过程可能使用多个SQL语句，输入和输出参数，局部变量， BEGIN...END块， IF...THEN...ELSE条件，游标（逐行处理集合），表达式，临时表以及其他一系列过程语法。显然，如果存储过程语言是C＃，Java或R，则将使用这些过程语言的功能和语法。换句话说，尽管SQL的动机是使用标准化的声明性查询，但在现实世界中，您会看到许多特定于数据库的过程服务器编程。

但这并不能使我们回到CODASYL数据库编程的糟糕时光（尽管游标已经接近），但是它确实回避了应该对SQL语句进行标准化以及将性能问题留给数据库查询优化器的想法。。最后，性能加倍通常太多，以至于无法发挥作用。