单列索引与多列索引
索引可以是单列索引,也可以是多列索引。下面我们通过具体的例子来说明这两种索引的区别。假设有这样一个people表:
CREATE TABLE people ( peopleid SMALLINT NOT NULL AUTO_INCREMENT, firstname CHAR(50)
NOT NULL, lastname CHAR(50) NOT NULL, age SMALLINT NOT NULL, townid SMALLINT NOT
NULL, PRIMARY KEY (peopleid) );
下面是我们插入到这个people表的数据: 。。。。
这个数据片段中有四个名字为“Mikes”的人(其中两个姓Sullivans,两个姓McConnells),有两个年龄为17岁的人,还有一个名字与众不同的Joe Smith。
这个表的主要用途是根据指定的用户姓、名以及年龄返回相应的peopleid。例如,我们可能需要查找姓名为Mike Sullivan、年龄17岁用户的peopleid(SQL命令为SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan' AND age=17;)。由于我们不想让MySQL每次执行查询就去扫描整个表,这里需要考虑运用索引。
首先,我们可以考虑在单个列上创建索引,比如firstname、lastname或者age列。如果我们创建firstname列的索引(ALTER TABLE people ADD INDEX firstname (firstname);),MySQL将通过这个索引迅速把搜索范围限制到那些firstname='Mike'的记录,然后再在这个“中间结果集”上进行其他条件的搜索:它首先排除那些lastname不等于“Sullivan”的记录,然后排除那些age不等于17的记录。当记录满足所有搜索条件之后,MySQL就返回最终的搜索结果。
由于建立了firstname列的索引,与执行表的完全扫描相比,MySQL的效率提高了很多,但我们要求MySQL扫描的记录数量仍旧远远超过了实际所需要的。虽然我们可以删除firstname列上的索引,再创建lastname或者age列的索引,但总地看来,不论在哪个列上创建索引搜索效率仍旧相似。
为了提高搜索效率,我们需要考虑运用多列索引。如果为firstname、lastname和age这三个列创建一个多列索引,MySQL只需一次检索就能够找出正确的结果!下面是创建这个多列索引的SQL命令:
ALTER TABLE people ADD INDEX fname_lname_age (firstname,lastname,age);
由于索引文件以B-树格式保存,MySQL能够立即转到合适的firstname,然后再转到合适的lastname,最后转到合适的age。在没有扫描数据文件任何一个记录的情况下,MySQL就正确地找出了搜索的目标记录!
那么,如果在firstname、lastname、age这三个列上分别创建单列索引,效果是否和创建一个firstname、lastname、age的多列索引一样呢?答案是否定的,两者完全不同。当我们执行查询的时候,MySQL只能使用一个索引。如果你有三个单列的索引,MySQL会试图选择一个限制最严格的索引。但是,即使是限制最严格的单列索引,它的限制能力也肯定远远低于firstname、lastname、age这三个列上的多列索引。
最左前缀
多列索引还有另外一个优点,它通过称为最左前缀(Leftmost Prefixing)的概念体现出来。继续考虑前面的例子,现在我们有一个firstname、lastname、age列上的多列索引,我们称这个索引为fname_lname_age。当搜索条件是以下各种列的组合时,MySQL将使用fname_lname_age索引:
firstname,lastname,age
firstname,lastname
firstname
从另一方面理解,它相当于我们创建了(firstname,lastname,age)、(firstname,lastname)以及(firstname)这些列组合上的索引。
分享到:
相关推荐
本文将深入探讨单列索引和多列索引的使用,并介绍多列索引中的最左前缀原则。 1. 单列索引 单列索引,顾名思义,是在单个列上创建的索引。创建索引的主要目的是优化WHERE子句和JOIN子句中的列。例如,如果一个查询...
MySQL中的列索引和多列索引是数据库性能优化的关键元素。索引是数据库管理系统用于快速查找表中特定信息的数据结构。在MySQL中,索引能够显著提高查询速度,尤其是在处理大量数据时。 首先,我们要了解单列索引。...
举例来说,如果我们有一个名为`people`的表,包含`lname`、`fname`和`age`三列,若要查询姓`Liu`、名`Zhiqun`且年龄为26的用户ID,创建一个`(lname, fname, age)`的多列索引会非常有效。如果只对`lname`创建单列索引...
oracle 全文检索 oracle全文索引 多列字段检索,匹配多列字段搜索功能。
5. **单列索引与多列索引**:索引可以针对单个列创建(单列索引),也可以针对多个列同时创建(多列索引)。 - **单列索引**:只基于表中的一个列创建索引。 - **多列索引**:基于表中两个或更多列的组合创建索引...
MySQL 索引包括普通索引、惟一性索引、全文索引、单列索引、多列索引和空间索引。 #### 普通索引 创建普通索引时,不附加任何限制条件。可以创建在任何数据类型上,其值是否唯一和非空由字段自身的完整性约束条件...
- 只有当查询中索引的第一列被用于条件时,多列索引才会被使用。如果查询只涉及到第二列或之后的列,索引将无法发挥作用。 7. **聚集函数与索引**: - `MAX`、`MIN`等函数在有索引的列上执行时,可以利用索引来...
可以基于单列或多个列(多列索引)创建索引。多列索引有助于区分有相同值的行。选择索引列时,应关注查询的WHERE和JOIN子句,并通过实验来评估索引对查询性能的影响。同时,注意避免在单个表上创建过多索引,以及...
2. **多列索引**:当需要基于多个列进行筛选时,考虑创建多列索引。 3. **覆盖索引**:尝试创建能够涵盖查询所需所有列的索引,以减少表扫描。 4. **索引统计信息**:定期更新索引统计信息,以便查询优化器能够做出...
- 即使多列索引包含多个字段,但它仍然是一个单一的B-Tree索引,并不是每个字段都有单独的B-Tree索引。 #### 七、索引的成本与优化 - 索引本身是有成本的,它不仅会占用存储空间,而且在插入、删除和更新数据时也...
多列索引是指基于两个或多个列创建的索引,适用于查询条件中包含多个字段的情况。 2. **索引优化**:合理选择索引类型和设计索引方案对于提高查询效率至关重要。例如,在经常被用作查询条件的列上创建索引可以显著...
- **多列索引优化**: 对于涉及多个条件的查询,合理设计多列索引可以显著提升性能。 - **覆盖索引**: 覆盖索引指的是包含了查询所需所有列的索引,这样可以直接从索引中获取数据而无需访问表本身,可以大大减少I/O...
多列索引有助于优化涉及多个列的查询,因为数据库系统可以同时考虑多个列的索引结构。例如,对于一个包含`firstname`和`lastname`的people表,如果经常需要根据名字的组合进行查询,那么创建一个`INDEX (firstname, ...
比如,`people`表中,可以为`firstname`、`lastname`和`age`创建单列索引,但它们的效果与创建一个包含这三个列的多列索引(如`fname_lname_age`)并不相同。多列索引的优势在于,即使只使用其中部分列进行查询,...
- 非聚集索引:不包含实际数据,而是存储键值和指向数据行的指针,适用于多列索引和唯一性约束。 - 聚集索引:索引结构与数据行存储在一起,索引键值就是数据行的物理排序顺序。 - 稀疏索引:用于存储大量重复值...
2. 创建了一个名为index_bir的多列索引,包括birthday和address字段: ```sql CREATE INDEX index_bir ON information (birthday, address); ``` 这将提高基于这两个字段组合的查询效率。 3. 使用ALTER TABLE语句在...
* 单列索引和多列索引 * 一般索引和复合索引 索引优化 索引优化是数据库性能优化的关键部分。索引优化可以提高查询性能,减少磁盘I/O,并提高数据库的整体性能。索引优化需要考虑以下几点: * 索引的选择:选择...
- 多列索引:基于多个列创建,用于区分多列可能出现相同值的行,适用于同时涉及多个列的查询。 ### 确定索引有效性 - 分析查询的`WHERE`和`JOIN`子句,考虑涉及的列是否需要索引。 - 实验新索引对查询性能的影响。...