网站建设吉金手指排名12,东莞市品牌网站建设平台,139邮箱网页版登录,越南的网站建设引言
关系数据库可用于处理任何大小的数据#xff0c;包括包含数百万行的大型数据库。结构化查询语言(SQL)提供了一种基于特定条件在数据库表中查找特定行的简洁而直接的方法。随着数据库变得越来越大#xff0c;在其中找到特定的行变得越来越困难#xff0c;就像大海捞针一…引言
关系数据库可用于处理任何大小的数据包括包含数百万行的大型数据库。结构化查询语言(SQL)提供了一种基于特定条件在数据库表中查找特定行的简洁而直接的方法。随着数据库变得越来越大在其中找到特定的行变得越来越困难就像大海捞针一样。
数据库能够接受各种各样的查询条件这使得数据库引擎很难预测哪些查询是最常见的。引擎必须准备好在数据库表中有效地定位行而不管行大小如何。然而随着数据量的增加搜索性能可能会受到影响。数据集越大数据库引擎就越难快速找到与查询匹配的文档。
数据库管理员可以使用数据库索引来辅助数据库引擎提高其性能。
在本教程中您将了解什么是索引如何创建索引以及是否用于查询数据库。
前期准备
遵循这个指南,你将需要一个计算机运行一个基于sql的关系数据库管理系统(RDBMS)。本指南中的说明和示例使用以下环境进行了验证
基本熟悉执行SELECT查询以从数据库中检索数据如如何从SQL指南中的表中选择行所述。
注意许多RDBMS使用它们自己的SQL实现。虽然触发提到作为一个SQL标准的一部分,标准不严格执行他们的语法或实现它们的方法。因此它们的实现在不同的数据库中是不同的。本教程中概述的命令使用MySQL数据库的语法可能无法在其他数据库引擎上工作。
你还需要一个数据库其中一些表加载了示例数据这样你就可以练习使用函数。我们鼓励您通过以下连接到示例数据库MySQL和建立一个连接到MySQL服务器上部分细节创建测试数据库在本指南中使用的例子。
连接到MySQL并设置一个示例数据库
如果SQL数据库系统运行在远程服务器上请从本地设备SSH到服务器
ssh sammyyour_server_ip然后打开MySQL服务器提示符将sammy替换为你的MySQL用户账户的名称
mysql -u sammy -p创建一个名为indexes的数据库
CREATE DATABASE indexes;如果数据库成功创建,您将收到这样的输出
OutputQuery OK, 1 row affected (0.01 sec)要选择indexes数据库运行以下USE语句
USE indexes;OutputDatabase changed选择数据库后您可以在其中创建一个示例表。在本指南中您将使用一个假想的员工数据库来存储当前员工及其工作设备的详细信息。
employees表将包含数据库中关于员工的简化数据。它将保存以下列
employee_id这一列包含员工标识符用int数据类型表示。这一列将成为表的主键每个值将成为对应行的唯一标识符。first_name这一列保存了每个员工的名字使用varchar数据类型表示最多不超过50个字符。last_name这一列保存了每个员工的姓氏使用varchar数据类型表示不超过50个字符。device_serial该列保存分配给员工的计算机的序列号使用varchar数据类型表示最多15个字符。salary这一列保存了每个员工的工资用int类型存储数值数据表示。
使用下面的命令创建示例表
CREATE TABLE employees (employee_id int,first_name varchar(50),last_name varchar(50),device_serial varchar(15),salary int
);OutputQuery OK, 0 rows affected (0.00 sec)接下来通过运行以下INSERT INTO操作来加载包含一些示例数据的employees表
INSERT INTO employees VALUES(1, John, Smith, ABC123, 60000),(2, Jane, Doe, DEF456, 65000),(3, Bob, Johnson, GHI789, 70000),(4, Sally, Fields, JKL012, 75000),(5, Michael, Smith, MNO345, 80000),(6, Emily, Jones, PQR678, 85000),(7, David, Williams, STU901, 90000),(8, Sarah, Johnson, VWX234, 95000),(9, James, Brown, YZA567, 100000),(10, Emma, Miller, BCD890, 105000),(11, William, Davis, EFG123, 110000),(12, Olivia, Garcia, HIJ456, 115000),(13, Christopher, Rodriguez, KLM789, 120000),(14, Isabella, Wilson, NOP012, 125000),(15, Matthew, Martinez, QRS345, 130000),(16, Sophia, Anderson, TUV678, 135000),(17, Daniel, Smith, WXY901, 140000),(18, Mia, Thomas, ZAB234, 145000),(19, Joseph, Hernandez, CDE567, 150000),(20, Abigail, Smith, FGH890, 155000);OutputQuery OK, 20 rows affected (0.010 sec)
Records: 20 Duplicates: 0 Warnings: 0注意数据集不够大不足以直接说明索引对性能的影响。然而这个数据集将展示MySQL如何使用索引来限制执行查询和获得结果时遍历的行数。
有了这些你就可以按照本指南的其余部分开始在MySQL中使用索引了。
Introduction to Indexes
通常当你对MySQL数据库执行查询时数据库必须逐个遍历表中的所有行。例如,您可能希望寻找员工姓名匹配“Smith”或所有员工工资高于“100000元”。表中的每一行都会被逐一检查以验证它是否符合条件。如果存在则将其添加到返回的行列表中。如果没有MySQL将扫描后续的行直到它浏览整个表。
尽管这种查找匹配行的方法是有效的但随着表的增大它可能会变得缓慢和资源密集。因此这种方法可能不适合需要频繁或快速访问数据的大型表或查询。
要解决大型表和查询的性能问题可以使用索引。索引是一种独特的数据结构与表行分开只存储数据的一个有序子集。它们允许数据库引擎在查找值或根据特定字段或字段集排序时更加快速和有效。
以employees表为例你可以执行的典型查询之一是通过姓氏查找员工。如果没有任何索引MySQL将从表中检索每个员工并验证姓氏是否与查询匹配。但是当使用索引时MySQL将保存一个单独的姓氏列表其中只包含指向主表中给定员工的行的指针。然后它将使用该索引来检索结果而无需扫描整个表。
你可以将索引视为电话本的类比。要在书中找到名为JohnSmith的人你首先翻到右边的页面那里列出了名字以S开头的人然后在这些页面中查找名字以Sm开头的人。按照这个逻辑你可以快速删除许多条目因为知道它们与你要找的人不匹配。这个过程之所以有效是因为电话簿中的数据是按字母顺序排序的而直接存储在数据库中的数据很少按字母顺序排序。数据库引擎中的索引的作用类似于电话本它保持对数据的引用按字母顺序排列从而帮助数据库快速找到所需的行。
在MySQL中使用索引有很多好处。最常见的是加速WHERE条件查询(使用精确匹配条件和比较)使用ORDER BY子句更快地对数据进行排序以及强制值的唯一性。
然而在某些情况下使用索引可能会降低数据库的峰值性能。索引的设计目的是加速数据检索它是通过存储在表数据旁边的额外数据结构来实现的。这些结构必须随着数据库中的每次更改而更新这可能会降低INSERT、UPDATE和DELETE查询的性能。对于经常变化的大型数据集SELECT查询提高速度带来的好处有时会被写入数据库的查询明显变慢的性能所抵消。
建议只在明确需要索引时才创建索引例如当应用程序的性能开始下降时。在选择要创建哪些索引时请考虑执行最频繁和花费时间最长的查询并根据将从它们中获益最多的查询条件来构建索引。
注意本指南旨在介绍MySQL数据库索引的主题说明常见的应用程序和索引类型。数据库引擎支持使用索引来提高数据库性能的许多更复杂的场景这超出了本指南的范围。我们鼓励您咨询官方MySQL文档关于索引以获得更完整的数据库功能描述。
在接下来的步骤中您将为一系列场景创建不同类型的索引。您将学习如何验证是否在查询中使用了索引。最后你将学习如何在必要时删除索引。
使用单列索引
单列索引是优化查询性能最常见、最直接的索引类型。这种类型的索引有助于数据库加速基于单列值过滤数据集的查询。在单个列上创建的索引可以加速许多条件查询包括使用操作符进行精确匹配以及与或操作符进行比较。
在上一步创建的示例数据库中没有索引。在创建索引之前我们首先测试一下当WHERE子句只请求表中的一部分数据时数据库如何处理employees表上的SELECT查询。
假设你想找到工资正好为100000的员工。执行以下查询
SELECT * FROM employees WHERE salary 100000;WHERE子句要求精确匹配与要求值匹配的工资。在这个例子中数据库的响应如下所示
Output-----------------------------------------------------------
| employee_id | first_name | last_name | device_serial | salary |
-----------------------------------------------------------
| 9 | James | Brown | YZA567 | 100000 |
-----------------------------------------------------------
1 row in set (0.000 sec)注意如上面的输出所示数据库几乎立即响应了发出的查询。由于数据库中只有少量的样例行使用索引不会明显影响查询性能。但是对于大型数据集您将观察到执行查询后数据库报告的查询执行时间的显著变化。
从查询的输出结果来看你无法知道数据库引擎是如何处理在表中查找匹配行的问题的。然而MySQL提供了一种方法来了解查询计划这就是引擎如何执行查询的EXPLAIN语句。
要访问SELECT查询的查询计划执行以下操作
EXPLAIN SELECT * FROM employees WHERE salary 100000;EXPLAIN命令告诉MySQL运行SELECT查询但它不会返回结果而是显示数据库引擎内部如何执行查询的信息。
执行计划类似于下面这样(你的表可能略有不同)
Output---------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 10.00 | Using where |
---------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)在这个表的输出中列描述了查询执行的许多方面。根据MySQL版本的不同输出可能包含额外的列但对于本教程以下是最重要的信息
possible_keys列出了MySQL考虑使用的索引。在这种情况下没有(NULL)。key描述了MySQL执行查询时决定使用的索引。在这个例子中没有使用索引(NULL)。rows显示了MySQL在返回结果之前需要单独分析的行数。在这里它是20这对应于表中所有可能行的数量。这意味着MySQL必须扫描employees表中的每一行来找到唯一返回的值。Extra显示关于查询计划的附加的描述性信息。在这个例子中Using where注解意味着数据库使用 where语句直接从表中过滤结果。
在没有索引的情况下数据库必须扫描20行才能检索到一条记录。如果表包含数百万行MySQL将不得不逐个遍历它们这会导致查询性能很差。
注意较新的MySQL版本当使用EXPLAIN时在输出中显示1 row in set, 1 warning而较旧的MySQL版本和MySQL兼容的数据库通常只显示1 row in set。警告并不是问题的征兆。MySQL使用它的警告机制来提供关于查询计划的进一步扩展信息。这些附加信息的使用超出了本教程的范围。你可以在MySQL文档中的Extended EXPLAIN输出格式页面了解更多有关该行为的信息。
刚才运行的SELECT查询使用了精确的查询条件即WHERE salary 100000。接下来,让我们来检查数据库的行为同样条件之间的对比。试着检索工资低于70000的员工
SELECT * FROM employees WHERE salary 70000;这次数据库返回了John Smith和Jane Doe的两行数据
Output-----------------------------------------------------------
| employee_id | first_name | last_name | device_serial | salary |
-----------------------------------------------------------
| 1 | John | Smith | ABC123 | 60000 |
| 2 | Jane | Doe | DEF456 | 65000 |
-----------------------------------------------------------
8 rows in set (0.000 sec)然而,当您使用EXPLAIN查询执行如下
EXPLAIN SELECT * FROM employees WHERE salary 70000;Output---------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 33.33 | Using where |
---------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)与之前的查询一样MySQL扫描了表中的所有20行以找到你通过查询中的WHERE子句请求的行。尽管返回的行数比表中所有行的数少数据库引擎仍然需要做很多工作才能找到它们。
为了解决这个问题你可以为salary列创建一个索引它会告诉MySQL维护一个额外的、高度优化的数据结构特别是对于employees表中的salary数据。为此执行以下查询
CREATE INDEX salary ON employees(salary);CREATE INDEX语句的语法要求
索引名称在本例中为salary。该名称在单表中必须是唯一的但可以在同一数据库中的不同表中重复。创建索引的表名。在本例中它是employees。创建索引的列列表。这里你使用一个名为salary的列来构建索引。
注意取决于你的MySQL用户权限在执行CREATE INDEX命令时可能会收到一个错误:ERROR 1142 (42000): INDEX command denied to user userhost for table employees。要给你的用户授予INDEX权限以root身份登录MySQL并执行以下命令根据需要替换MySQL用户名和主机
GRANT INDEX on *.* TO sammylocalhost;
FLUSH PRIVILEGES;更新用户权限后以root身份注销并以该用户身份重新登录并重新运行CREATE INDEX语句。
数据库将确认索引已成功创建
OutputQuery OK, 0 rows affected (0.024 sec)
Records: 0 Duplicates: 0 Warnings: 0有了索引后尝试重复前面的查询以检查是否有任何更改。首先检索工资正好为100000的单个员工
SELECT * FROM employees WHERE salary 100000;Output-----------------------------------------------------------
| employee_id | first_name | last_name | device_serial | salary |
-----------------------------------------------------------
| 9 | James | Brown | YZA567 | 100000 |
-----------------------------------------------------------
1 row in set (0.000 sec)但是让MySQL解释它是如何处理查询的会显示出一些不同。执行EXPLAIN查询
EXPLAIN SELECT * FROM employees WHERE salary 100000;Output------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | employees | NULL | ref | salary | salary | 5 | const | 1 | 100.00 | NULL |
------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)MySQL声明了在possible_keys中显示的一个可能的键它决定使用名为salary的键也就是你创建的索引。rows列现在显示的是1而不是20。因为使用了索引数据库避免了扫描数据库中的所有行可以立即返回请求的行。Extra列现在没有提到Using WHERE因为遍历主表并根据查询条件检查每一行不是执行查询的必要条件。
对于小样本数据集使用索引的影响不是很明显。但是数据库检索结果所做的工作要少得多而且这种变化在更大的数据集上的影响将非常显著。
尝试重新运行第二个查询检索工资低于70000的员工以检查索引是否也将在那里使用。
执行以下查询
SELECT * FROM employees WHERE salary 70000;John Smith和Jane Doe将返回相同的两行
Output-----------------------------------------------------------
| employee_id | first_name | last_name | device_serial | salary |
-----------------------------------------------------------
| 1 | John | Smith | ABC123 | 60000 |
| 2 | Jane | Doe | DEF456 | 65000 |
-----------------------------------------------------------
8 rows in set (0.000 sec)但是当你像下面这样使用EXPLAIN时
EXPLAIN SELECT * FROM employees WHERE salary 70000;Output----------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | employees | NULL | range | salary | salary | 5 | NULL | 2 | 100.00 | Using index condition |
----------------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)key列告诉你MySQL使用这个索引来执行查询。在rows中只有两行被分析以返回结果。这一次Extra列的意思是Using index condition这意味着在这个特殊的情况下MySQL使用索引进行过滤然后使用主表来检索已经匹配的行。
注意有时即使存在索引并可以使用MySQL也会决定不使用它。例如如果你执行
EXPLAIN SELECT * FROM employees WHERE salary 140000;Output---------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | employees | NULL | ALL | salary | NULL | NULL | NULL | 20 | 80.00 | Using where |
---------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)尽管salary列在possible_keys列表中但key列为空(读取值为NULL)意味着MySQL决定不使用这个索引这可以从扫描到的20行中得到确认。数据库查询规划器根据可能的索引分析每个查询以确定最快的执行路径。如果访问索引的成本超过了使用它的好处(例如如果查询返回原始表数据的很大一部分)数据库可以决定进行全表扫描实际上更快。
类似地Extra列中的注释例如Using index condition或Using where描述了数据库引擎如何更详细地执行查询。根据上下文的不同数据库可能会选择另一种方式执行查询并且您可能会有Using index condition注释缺失或存在另一个注释的输出。这并不意味着索引没有被正确地使用只是数据库决定使用一种不同的方式来访问行以获得更高的性能。
在本节中我们创建并使用了单列索引来提高SELECT查询的性能。在下一节中你将探索如何使用索引来保证给定列中值的唯一性。
使用唯一索引来防止数据重复
如上一节所述索引的一个常见用途是通过帮助数据库引擎做更少的工作来实现相同的结果从而更有效地检索数据。另一个目的是确保表中定义索引的部分的数据不会重复。这就是唯一索引所做的。
为了保证数据的完整性避免重复的值通常是必要的无论是从逻辑还是技术的角度来看。例如不应该有两个不同的人使用相同的社会保险号或者在线系统不应该允许多个用户使用相同的用户名或电子邮件地址注册。
在本指南的employees表示例中分配给设备的序列号是一个不应该包含重复项的字段。如果是这样这意味着两名员工得到的是同一台电脑。然而在这一点上您可以很容易地插入具有重复序列号的新员工。
试着插入另一个拥有已经在使用的设备序列号的员工
INSERT INTO employees VALUES (21, Sammy, Smith, ABC123, 65000);数据库会按照要求插入这条记录并通知你操作成功
OutputQuery OK, 1 row affected (0.009 sec)但是如果你现在使用ABCD123计算机查询员工数据库如下所示
SELECT * FROM employees WHERE device_serial ABC123;你将得到两个不同的人
Output-----------------------------------------------------------
| employee_id | first_name | last_name | device_serial | salary |
-----------------------------------------------------------
| 1 | John | Smith | ABC123 | 60000 |
| 21 | Sammy | Smith | ABC123 | 65000 |
-----------------------------------------------------------
2 rows in set (0.000 sec)这不是保持employees数据库有效的预期行为。让我们通过删除新创建的行来恢复此更改
DELETE FROM employees WHERE employee_id 21;你可以通过重新运行之前的SELECT查询来确认
SELECT * FROM employees WHERE device_serial ABC123;同样只有John Smith使用序列号为ABC123的设备
Output-----------------------------------------------------------
| employee_id | first_name | last_name | device_serial | salary |
-----------------------------------------------------------
| 1 | John | Smith | ABC123 | 60000 |
-----------------------------------------------------------
1 row in set (0.000 sec)为了保护数据库避免此类错误您可以在device_serial列上创建一个唯一的索引。
为此执行
CREATE UNIQUE INDEX device_serial ON employees(device_serial);在创建索引时添加UNIQUE关键字通知数据库确保device_serial列中的值不能重复。使用唯一索引将根据索引检查添加到表中的所有新行以确定列值是否满足约束。
数据库将确认创建索引
OutputQuery OK, 0 rows affected (0.021 sec)
Records: 0 Duplicates: 0 Warnings: 0现在检查是否仍然可以向表中添加重复的条目。试着再次运行之前成功的INSERT查询
INSERT INTO employees VALUES (21, Sammy, Smith, ABC123, 65000);OutputERROR 1062 (23000): Duplicate entry ABC123 for key device_serial你可以再次使用SELECT查询来验证新行没有被添加到表中
SELECT * FROM employees WHERE device_serial ABC123;Output-----------------------------------------------------------
| employee_id | first_name | last_name | device_serial | salary |
-----------------------------------------------------------
| 1 | John | Smith | ABC123 | 60000 |
-----------------------------------------------------------
1 row in set (0.000 sec)唯一索引,除了维护对重复的条目,也加快查询的功能完整的索引。数据库引擎将以与上一步相同的方式使用唯一索引。您可以验证通过执行
EXPLAIN SELECT * FROM employees WHERE device_serial ABC123;执行计划类似于下面这样(你的表可能略有不同)
Output--------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
--------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | employees | NULL | const | device_serial | device_serial | 63 | const | 1 | 100.00 | NULL |
--------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)device_serial索引同时显示在possible_keys和key列中确认在执行查询时使用了该索引。
您已经使用了唯一索引来防止数据库中出现重复数据。在下一节中我们将使用横跨多个列的索引。
在多个列上使用索引
到目前为止在前面几节中创建的所有索引都是使用单个列名定义的即所选列的值。大部分数据库系统支持索引生成多个列。这种被称为多列索引(multi-column index)的索引提供了一种将多个列的值存储在单个索引中的方法允许数据库引擎使用一组列来更快速、更有效地执行查询。
需要优化性能的频繁使用的查询通常在WHERE过滤子句中使用多个条件。这种查询的一个例子是要求数据库根据名和姓查找一个人
SELECT * FROM employees WHERE last_name Smith AND first_name John;使用索引优化查询的第一个想法是创建两个单独的索引一个在last_name列上另一个在first_name列上。然而这并不是这种情况下的最佳选择。
如果你用这种方式创建了两个单独的索引MySQL将知道如何找到所有名为Smith的员工。它还知道如何找到所有名为John的员工。然而它不知道如何找到名为John Smith的人。
为了说明两个单独索引的问题假设有两个独立的电话簿一个按姓氏排列另一个按名字排列。这两个电话簿都类似于分别在last_name和first_name列上创建的索引。作为一个电话簿用户要找到John Smith有三种可能的方法
使用按姓氏排序的电话簿来查找所有名为Smith的人忽略第二本电话簿并手动逐个遍历所有Smith的人直到找到John Smith。反其道而行:使用按名字排序的电话簿来查找所有名为John的人忽略第二本电话簿并手动逐个遍历所有John的人直到找到John Smith。尝试同时使用两个电话簿:找到所有名为John的人并分别找到所有名为Smith的人写下中间结果并尝试手动交叉两个数据子集以查找同时出现在两个单独列表中的人。
这些方法都不理想,和MySQL也有类似的选择,当处理多个脱节的索引和查询要求多个筛选条件。
另一种方法是使用不只考虑单个列而是考虑多个列的索引。你可以把它想象成一个电话簿放在另一个电话簿里:首先你查找姓氏Smith然后进入第二个目录目录中所有名为Smith的人按名字的字母顺序排列你可以用这个目录快速找到John。
注意人们常说MySQL在查询中每个表只能使用一个索引。这并不总是正确的因为MySQL支持索引合并优化在运行查询时联合使用多个索引。然而在构建索引时这个限制是一个很好的经验法则。MySQL可能决定不使用多个索引;即使有在许多情况下它们也不能像专用索引那样达到目的。
在MySQL中为employees表中的姓和名创建一个多列索引执行
CREATE INDEX names ON employees(last_name, first_name);在这种情况下CREATE INDEX语句略有不同。现在在表名(employees)后面的括号中列出了两列:last_name和first_name。这在两个列上创建了一个多列索引。列在索引定义中列出的顺序很重要稍后你会发现。
数据库将显示以下消息确认已成功创建索引
OutputQuery OK, 0 rows affected (0.024 sec)
Records: 0 Duplicates: 0 Warnings: 0现在尝试执行SELECT查询来查找名字匹配John、姓氏匹配Smith的行
SELECT * FROM employees WHERE last_name Smith AND first_name John;Output-----------------------------------------------------------
| employee_id | first_name | last_name | device_serial | salary |
-----------------------------------------------------------
| 1 | John | Smith | ABC123 | 60000 |
-----------------------------------------------------------
1 row in set (0.000 sec)现在使用EXPLAIN查询来检查是否使用了索引
EXPLAIN SELECT * FROM employees WHERE last_name Smith AND first_name John;Output-----------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | employees | NULL | ref | names | names | 406 | const,const | 1 | 100.00 | NULL |
-----------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)数据库使用names索引。因为只扫描了一行数据所以遍历表的次数不会超过必要的范围。Extra列表示Using index condition这意味着MySQL可以仅使用索引完成过滤。
使用跨这两列的多列索引对名和姓进行过滤为数据库提供了一种直接、快速的方法来查找所需的结果。
在两列上都定义了索引之后如果你试图找到所有名为Smith的员工但不根据名字进行过滤会发生什么?运行修改后的查询
SELECT * FROM employees WHERE last_name Smith;Output-----------------------------------------------------------
| employee_id | first_name | last_name | device_serial | salary |
-----------------------------------------------------------
| 20 | Abigail | Smith | FGH890 | 155000 |
| 17 | Daniel | Smith | WXY901 | 140000 |
| 1 | John | Smith | ABC123 | 60000 |
| 5 | Michael | Smith | MNO345 | 80000 |
-----------------------------------------------------------
4 rows in set (0.000 sec)EXPLAIN SELECT * FROM employees WHERE last_name Smith;Output-----------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | employees | NULL | ref | names | names | 203 | const | 4 | 100.00 | NULL |
-----------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.01 sec)这次四行返回,如有多个员工姓。然而执行计划表显示数据库使用多列索引names来执行此查询仅扫描4行——返回的确切数字。
在前面的查询中CREATE INDEX语句首先传入了用于过滤结果的列(last_name)。现在你将通过first_name来过滤employees表这是这个多列索引的列列表中的第二列。执行以下查询
SELECT * FROM employees WHERE first_name John;Output-----------------------------------------------------------
| employee_id | first_name | last_name | device_serial | salary |
-----------------------------------------------------------
| 1 | John | Smith | ABC123 | 60000 |
-----------------------------------------------------------
1 row in set (0.000 sec)
访问查询执行计划
EXPLAIN SELECT * FROM employees WHERE first_name John;Output---------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 10.00 | Using where |
---------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)同样返回的结果包含一个雇员但这一次没有使用索引。数据库扫描了整个表可以从Extra列中的Using where注释以及20扫描行中看出。
在这个例子中数据库没有使用索引因为第一次创建索引时传递给CREATE INDEX语句的列的顺序是last_name, first_name。数据库只能在查询使用第一列或同时使用第一和第二列时使用索引;它不支持索引定义的第一列未使用的索引查询。
通过在多个列上创建索引数据库可以使用索引来加速涉及所有被索引列的查询或者对所有被索引列增加左手边前缀。例如一个包含(a, b, c)列的多列索引可以用于加速涉及所有三列的查询以及只涉及前两列的查询甚至只涉及第一列的查询。另一方面对于只涉及最后一列c或最后两列b和c的查询索引不会有帮助。
通过仔细选择包含在索引中的列及其顺序可以使用单个多列索引来加速对同一表的各种查询。在这个例子中如果我们假设同时按姓和名查找雇员或者只按姓查找雇员那么在names索引中提供的列的顺序保证了索引将加速所有相关的查询。
在本节中我们使用了多列索引并了解了指定这种索引时的列顺序。在下一节中,您将了解如何管理现有索引。
列出和删除现有索引
在前面的部分中,您创建新的索引。由于索引有名称并且在特定的表上定义因此您还可以列出它们并在需要时对它们进行操作。
要列出你在本教程中为employees表创建的所有索引执行以下语句
SHOW INDEXES FROM employees;Output----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| employees | 0 | device_serial | 1 | device_serial | A | 20 | NULL | NULL | YES | BTREE | | | YES | NULL |
| employees | 1 | salary | 1 | salary | A | 20 | NULL | NULL | YES | BTREE | | | YES | NULL |
| employees | 1 | names | 1 | last_name | A | 16 | NULL | NULL | YES | BTREE | | | YES | NULL |
| employees | 1 | names | 2 | first_name | A | 20 | NULL | NULL | YES | BTREE | | | YES | NULL |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4 rows in set (0.01 sec)根据MySQL版本的不同输出可能略有不同但它将包括所有索引包括它们的名称、用于定义索引的列、唯一性信息以及索引定义的其他详细信息。
要删除现有的索引你可以使用DROP INDEX SQL语句。假设你不想再强制device_serial列的唯一性。因此device_serial索引将不再需要。执行下面的命令
DROP INDEX device_serial ON employees;device_serial是索引名称employees是定义索引的表。数据库将确认索引删除
OutputQuery OK, 0 rows affected (0.018 sec)
Records: 0 Duplicates: 0 Warnings: 0有时典型查询的模式会随着时间的推移而改变或者新的查询类型会变得很突出。然后您可能需要重新评估已使用的索引创建新的索引或删除未使用的索引通过保持索引的最新状态来避免降低数据库性能。
使用CREATE INDEX和DROP INDEX命令您可以在现有数据库上管理索引并遵循最佳实践在需要和有益时创建索引。
总结
通过本指南你了解了什么是索引以及如何使用MySQL中最常见的类型来加速通过条件SELECT查询进行数据检索。您使用索引来维护列数据的唯一性并了解了索引如何影响在过滤条件中使用多个列的查询。
你可以根据最常执行的查询类型使用索引来塑造数据库的性能在常见用例的读和写性能之间取得了正确的平衡。本教程只介绍了为此目的使用索引的基础知识。通过理解MySQL如何选择使用哪些索引以及何时使用它们您可以通过索引支持更复杂的查询。要了解更多信息请参阅MySQL文档关于索引。
