mysq优势高性能mysql第四版

mysq优势高性能mysql第四版

使查询快速的索引是您听说过的索引的最基本解释。

作为一名工程师,其专长是应用程序而不是 MySQL 性能,这可能是压倒性的。您应该从分析查询响应时间开始,因为它是有意义且可操作的。

有意义——我们看到的唯一指标是查询响应时间。当查询需要 n 秒执行时,我们会变得不耐烦。在同一个查询中,可能会检查一百万行,但我们看不到检查一百万行。我们的时间很宝贵。

可操作——因为您拥有代码,所以可以更改查询响应时间。即使您无权访问它,您仍然可以间接优化查询响应时间。

提高数据库性能的最有效方法是正确使用索引。在数据库中,索引是一种数据结构,它将值与这些值出现在给定列中的行相关联。与使用蛮力从上到下搜索整个表相比,数据库可以使用索引更快地找到值。

大多数软件开发人员不知道如何或何时创建索引。在有关数据库的书籍和文档中很少或从不解释何时使用索引。开发人员只能猜测如何有效地使用它们。

SQL 查询是如何工作的?

SELECT 查询用于检索数据,通常使用 WHERE 子句过滤数据。在大多数情况下,会在查询的 WHERE 子句中指定的列上创建索引。在没有索引的情况下,数据库会扫描所有的行,过滤掉匹配的行,然后返回结果。如果有数百万条记录,此扫描操作可能需要很长时间,导致 API 和应用程序响应时间变慢。

让我们创建一个数据库以更好地理解它。InnoDB 是 MySQL 的默认数据库引擎。

CREATE TABLE sample_table (
first_name VARCHAR(32) NOT NULL,
last_name VARCHAR(32) NOT NULL,
age INT,
mobile_no VARCHAR(10)
);

向该表中插入 1000 条随机记录。使用一个程序,我就能生成这些记录。

DELIMITER $$
DROP PROCEDURE generate_data;
CREATE PROCEDURE generate_data()
BEGIN
DECLARE i INT DEFAULT 0;
当 i < 1000 DO
INSERT INTO `sample_table` (`first_name`,`last_name`,`age`,`mobile_no`) 值 (
CONV(FLOOR(RAND() * 99999999999999), 10, 36),
CONV(FLOOR(RAND ) () * 99999999999999), 10, 36),
ROUND(RAND()*100,2),
LPAD(FLOOR(RAND() * 10000000000), 10, '0')
);
设置 i = i + 1;
结束;
END$$
分隔符;

运行以下过程

调用生成数据();

命令 SHOW INDEX 可用于验证没有在该表上创建索引。

显示来自 sample_table 的索引;

到目前为止,如果我们运行一个简单的 SELECT 查询,该查询将扫描整个表,因为在 mobile_no 上没有定义索引。

EXPLAIN SELECT * from sample_table where mobile_no='5554688121';

mysq优势高性能mysql第四版

EXPLAIN 指示查询引擎打算如何执行查询。正如您在上面的快照中看到的,rows 列返回 1000,而 possible_keys 返回 null。possible_keys 列表示可以在此查询中使用的所有索引。在所有可能的索引中,键列指示将使用哪个索引。

什么是索引?

CREATE INDEX 语句创建一个索引,它是数据库中的唯一结构。它需要自己的磁盘空间并存储索引表数据的副本。因此,索引是纯粹的冗余。表索引不会改变表中的数据;它只是创建一个引用表的新数据结构。毕竟数据库索引与书籍索引非常相似。它们占据自己的空间,高度冗余,并引用存储在另一个位置的信息。为了创建主索引,必须使用 ALTER TABLE,而不是 CREATE INDEX。

主索引/键

不必创建自己的主键。由于 InnoDB 在设计上必须在每个表中都有一个主键,因此如果您没有定义主键,它会自动为您创建一个。当您稍后为该表创建主键时,InnoDB 会删除先前自动生成的主键。

现在我们没有定义主键,让我们看看 InnoDB 默认为我们创建了什么:

显示来自 sample_table 的扩展索引;

mysq优势高性能mysql第四版

EXTENDED 显示用户无法访问但完全由 MySQL 管理的所有索引。MySQL 已在 DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR 以及表中的所有列上定义了复合索引。在没有用户定义的主键的情况下,该索引用于唯一地查找记录。

ALTER TABLE sample_table 添加主键(mobile_no);

由于我随机生成了表行,因此主键的标准之一是列值在表中应该是唯一的。就我而言,我有重复的生成值,所以我不能将它们用作主键。

但是,我可以在 mobile_no 列上创建一个索引,我将在这里向您展示。

在 sample_table(mobile_no) 上创建索引 mobile_no_idx;

让我们使用 SHOW INDEX 命令验证索引。

显示来自 sample_table 的索引;

mysq优势高性能mysql第四版

现在让我们看看这个索引是否减少了需要在查询的 WHERE 子句中搜索给定 mobile_no 的行数。

EXPLAIN SELECT * from sample_table where mobile_no='5554688121';

mysq优势高性能mysql第四版

这些行只返回 1, possible_keys 和 key 都返回 mobile_no_idx。使用索引,查询优化器直接进入记录并检索它。这是非常有效的。索引的目的是以增加空间为代价来缩小搜索范围。

索引类别

MySQL中的索引可以分为以下几种类型:

  1. B-Tree(平衡树)索引——最常用和默认的索引类型。该索引可以与使用 =、>、>=、<、<= 和 BETWEEN & LIKE 关键字的搜索查询一起使用。
  2. R-Tree(空间树)索引——利用 MySQL 的几何数据类型,它可以用来索引地理对象。
  3. 哈希索引——通常与 = 或 <=> 搜索运算符一起使用。非常快,但只能在使用内存存储引擎时使用。
  4. 覆盖索引— 覆盖完成查询所需的所有列的索引。
  5. 聚集索引——行数据存储在此类索引中。PRIMARY KEY 或者,如果不存在,则通常使用 UNIQUE 索引
  6. 复合索引——多列用于创建索引。
  7. 前缀索引— 这种类型的索引允许您索引列的前缀。这些索引不索引列的完整值,因此它们经常用于节省空间。

B树索引

B-tree 索引是 MySQL 中最常用的索引。只要有这样的索引,MySQL 就可以利用使用关键字 =、>、>=、<、<= 和 BETWEEN & LIKE 的搜索查询。这是可以将此类索引添加到列以加快搜索查询的主要原因之一。当使用 B 树索引时,存储引擎不必扫描整个表来查找相关行。

将这样的索引添加到表中。

CREATE INDEX index_name ON table_name(column_name)

CREATE INDEX index_name ON table_name(column_name) USING BTREE;

可能使用此类索引的查询示例

SELECT * from table_name where column_name <= 'value';
SELECT * from table_name where column_name BETWEEN 1 AND 100;
SELECT * from table_name where column_name LIKE '%value%';

R-树索引

为了使用 R-Tree 或空间索引,您必须使用 MySQL 提供的地理信息系统 (GIS) 功能。MBRContains、MBRCovers 和 MBREquals 是其中的一些函数。它们可以指示一个参数的最小边界矩形是否包含、覆盖或等于另一个参数的最小边界矩形。

要将此类索引添加到表中,请使用 SPATIAL 关键字。

创建空间索引 index_name ON table_name(column_name);

哈希索引

MySQL 中哈希索引的主要优点是速度非常快,但主要缺点是只能用于相等比较,并且只能在 MEMORY 存储引擎上工作。也不支持范围搜索。

要在表上创建这样的索引,请在最后使用 USING HASH 选项。

CREATE INDEX index_name ON table_name(column_name) USING HASH;

哈希索引只能与 = 或 <=> 运算符一起使用。

覆盖索引

在使用 MySQL 时,可能会遇到一些可能需要以不同方式使用索引的极端情况。覆盖索引是那些覆盖查询成功所必需的所有字段的索引。当存在覆盖索引时,查询可以直接从索引中检索结果,而不必访问磁盘。如果您使用的索引已经包含您要搜索的列的值,MySQL 将不会访问磁盘,而是直接从索引中检索结果。

SELECT col1, col2 FROM table_name WHERE col3 = 'value';

聚集索引

聚集索引不是单独的 MySQL 索引类型,它们是存储数据的不同方法。如果您的表有 PRIMARY KEY,则聚集索引就是 PRIMARY KEY。如果您的表没有 PRIMARY KEY,它是第一个将所有键列定义为 NOT NULL 的唯一索引。

使用这样的索引,表中的所有行都根据其键值进行存储和排序。表中不能有多个聚集索引,因为每一行只能有一个排序顺序。

综合指数

复合索引跨越多个列。如果我们在多列上有一个索引,我们就有一个复合索引。

CREATE INDEX index_name ON demo_table(col1,col2,col3);

在使用索引字段进行选择、连接或过滤的查询中,复合索引是有益的。

SELECT * FROM table_name WHERE col1 = 'value1' AND col2 = 'val2';

在 MySQL 中,复合索引最多可以包含 16 列,但请记住 MySQL 从左到右使用索引,反之则不然。

前缀索引

在某些情况下,您可能需要索引冗长的列,从而使索引变得非常大。作为索引整个值的替代方法,前缀索引还可以索引列的前几个字符。

在 MySQL 中的表上创建前缀索引

CREATE INDEX index_name ON table_name (column_name(length));

使用前缀索引,以提供良好选择性同时节省空间的方式对列进行索引很重要。

反模式:在没有计划的情况下使用索引

通过猜测来选择我们的索引不可避免地会导致一些错误。

  • 没有定义索引或没有足够的索引
  • 定义过多的索引或无用的索引
  • 运行没有索引的查询可以提供帮助

没有索引

通常认为数据库在维护索引时会产生开销。每当我们插入、更新或删除表时,数据库必须更新其索引数据结构,以确保后续搜索能够可靠地找到正确的行集。开销通常与浪费有关。因此,当我们读到维护索引会产生开销时,我们希望消除它。在某些情况下,开发人员得出结论,删除索引是最好的解决方案。这是常见的建议,但它忽略了索引具有证明其成本合理的好处这一事实。

索引过多

仅当您运行使用它的查询时,索引才有用。创建你不使用的索引是没有意义的。因为他们不知道哪些索引会对他们的查询有益,所以有些人会在每列和每列组合上创建索引。一个被索引覆盖的数据库表会产生大量开销,并且不能保证返回。

当没有索引可以提供帮助时

第三种错误是运行不能使用索引的查询。为了让查询运行得更快,开发人员构建了越来越多的索引,试图找到一些列或索引选项的神奇组合。

在 sample_table(last_name, first_name) 上创建索引电话 IDX;

以下是一些不会从此索引中受益的查询示例:

SELECT * from sample_table ORDER BY first_name, last_name;
SELECT * FROM sample_table WHERE last_name = 'Charles' OR first_name = 'Charles' ;

让我们看看数据库引擎如何计划执行这些查询。

EXPLAIN SELECT * from sample_table ORDER BY first_name, last_name;

mysq优势高性能mysql第四版

possible_keys 和 key 在这里都是空的,并且所有的行都被扫描了。

EXPLAIN SELECT * FROM sample_table WHERE last_name = 'Charles' OR first_name = 'Charles' ;

mysq优势高性能mysql第四版

这里, possible_key 是telephone_idx,但是key 仍然是空的,因为这个索引没有被使用。

索引最重要的规则之一是您不应该盲目猜测。了解您的数据、了解您的查询并指导您的索引。

好了,这篇文章的内容就和大家分享到这里,如果大家对网络推广引流和网络创业项目感兴趣,可以添加微信:beng3355  备注:项目

本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 sumchina520@foxmail.com 举报,一经查实,本站将立刻删除。
如若转载,请注明出处:https://www.hzy3.com/5806.html