搜索
首页数据库mysql教程MySQL Index索引的建立、使用说明_MySQL

bitsCN.com 数据库的索引 

1. 如果不建立索引,那么查询都需要全表扫描;如果建立了索引,则数据库会保存一个索引文件通常是特殊的结构比如B树,这样查询起来不需要全表扫描,一下子能够找到满足要求的记录。 

 

2. 一般是对Where之后的条件建立索引,数据库中的主键是已经建立了索引的。数据库中可以建立多个索引。 

 

  

 

3. 可以对不同类型的列建立索引。 

 

对于Text类型等,可以使用MySQL的全文检索功能建立全文索引。它利用了自然语言的方法去在文本中检索关键词。 

 

举个例子:如果使用=号的话可能需要使用like以及%等去匹配。而使用MySQL的全文检索可以使用Match函数即可检索出包含关键词的列。 

 

详细情况参看MySQL参考手册关于全文检索的部分。 

 

  

 

4.1使用索引 

 

    我们首先讨论索引,因为它是加快查询的最重要的工具。还有其他加快查询的技术,但是最有效的莫过于恰当地使用索引了。在MySQL的邮件清单上,人们通常询问关于使查询更快的问题。在大量的案例中,都是因为表上没有索引,一般只要加上索引就可以立即解决问题。但这样也并非总是有效,因为优化并非总是那样简单。然而,如果不使用索引,在许多情形下,用其他手段改善性能只会是浪费时间。应该首先考虑使用索引取得最大的性能改善,然后再寻求其他可能有帮助的技术。 

 

    本节介绍索引是什么、它怎样改善查询性能、索引在什么情况下可能会降低性能,以及怎样为表选择索引。下一节,我们将讨论MySQL的查询优化程序。除了知道怎样创建索引外,了解一些优化程序的知识也是有好处的,因为这样可以更好地利用所创建的索引。某些编写查询的方法实际上会妨碍索引的效果,应该避免这种情况出现。(虽然并非总会这样。有时也会希望忽略优化程序的作用。我们也将介绍这些情况。) 

 

    4.1.1索引的益处 

 

    让我们从一个无索引的表着手来考察索引是怎样起作用的。无索引的表就是一个无序的行集。例如,图4 - 1给出了我们在第1章“MySQL与SQL 介绍” 中首先看到的ad 表。这个表上没有索引,因此如果我们查找某个特定公司的行时,必须查看表中的每一行,看它是否与所需的值匹配。这是一个全表扫描,很慢,如果表中只有少数几个记录与搜索条件相匹配,则其效率是相当低的。 

 

                             1.jpg 

 

    图4 - 2给出了相同的表,但在表的company_num 列上增加了一个索引。此索引包含表中每行的一项,但此索引是在company_num 上排序的。现在,不需要逐行搜索全表查找匹配的条款,而是可以利用索引进行查找。假如我们要查找公司13的所有行,那么可以扫描索引,结果得出3行。然后到达公司14的行,这是一个比我们正在查找的要大的号码。索引值是排序的,因此在读到包含14的记录时,我们知道不会再有匹配的记录,可以退出了。如果查找一个值,它在索引表中某个中间点以前不会出现,那么也有找到其第一个匹配索引项的定位算法,而不用进行表的顺序扫描(如二分查找法)。这样,可以快速定位到第一个匹配的值,以节省大量搜索时间。数据库利用了各种各样的快速定位索引值的技术,这些技术是什么并不重要,重要的是它们工作正常,索引技术是个好东西。 

 

    有人会问,为什么不只对数据文件进行排序,省掉索引文件?这样不也在搜索时产生相同的效果吗?问得好,如果只有单个索引时, 

 

是这样的。不过有可能会用到第二个索引,但同时以两种不同的方法对同一个数据文件进行排序是不可能的。(如,想要一个顾客名的索 

 

引,同时又要一个顾客ID 号或电话号码的索引。)将索引文件作为一个与数据文件独立的实体就解决了这个问题,而且允许创建多个索 

 

引。此外,索引中的行一般要比数据文件中的行短。在插入或删除值时,为保持排序顺序而移动较短的索引值与移动较长的数据行相比更 

 

为容易。 

 

       2.jpg 

 

    这个例子与MySQL索引表的方法相符。表的数据行保存在数据文件中,而索引值保存在索引文件中。一个表上可有不止一个索引;如果确实有不止一个索引,它们都保存在同一个索引文件中。索引文件中的每个索引由排过序的用来快速访问数据文件的键记录数组构成。 

 

前面的讨论描述了单表查询中索引的好处,其中使用索引消除了全表扫描,极大地加快了搜索的速度。在执行涉及多个表的连接查询时,索引甚至会更有价值。在单个表的查询中,每列需要查看的值的数目就是表中行的数目。而在多个表的查询中,可能的组合数目极大,因为这个数目为各表中行数之积。 

 

    假如有三个未索引的表t 1、t 2、t 3,分别只包含列c 1、c 2、c 3,每个表分别由含有数值1到1000 的1000 行组成。查找对应值相等的表行组合的查询如下所示: 

 

    SELECT c1,c2,c3 

 

    FROM t1,t2,t3 

 

    WHERE c1=c2 AND c1=c3 

 

    此查询的结果应该为1000 行,每个组合包含3 个相等的值。如果我们在无索引的情况下处理此查询,则不可能知道哪些行包含那些值。因此,必须寻找出所有组合以便得出与WHERE 子句相配的那些组合。可能的组合数目为10 0 0??0 0 0??0 0 0(十亿),比匹配数目多一百万倍。很多工作都浪费了,并且这个查询将会非常慢,即使在如像MySQL这样快的数据库中执行也会很慢。而这还是每个表中只有1000 行的情形。如果每个表中有一百万行时,将会怎样?很显然,这样将会产生性能极为低下的结果。如果对每个表进行索引,就能极大地加速查询进程,因为利用索引的查询处理如下: 

 

    1) 如下从表t1中选择第一行,查看此行所包含的值。 

 

    2) 使用表t2 上的索引,直接跳到t2 中与来自t1的值匹配的行。类似,利用表t3 上的索引,直接跳到t3 中与来自t1的值匹配的行。 

 

    3) 进到表t1的下一行并重复前面的过程直到t1中所有的行已经查过。在此情形下,我们仍然对表t1执行了一个完全扫描,但能够在表t2 和t3 上进行索引查找直接取出这些表中的行。从道理上说,这时的查询比未用索引时要快一百万倍。如上所述,MySQL利用索引加速了WHERE 子句中与条件相配的行的搜索,或者说在执行连接时加快了与其他表中的行匹配的行的搜索。它也利用索引来改进其他操作的性能: 

 

    ■ 在使用MIN( ) 和MAX( ) 函数时,能够快速找到索引列的最小或最大值。 

 

    ■ MySQL常常能够利用索引来完成ORDER BY 子句的排序操作。 

 

    ■ 有时,MySQL可避免对整个数据文件的读取。假如从一个索引数值列中选择值,而且不选择表中其他列。这时,通过对索引值的读取,就已经得到了读取数据文件所要得到的值。没有对相同的值进行两次读取的必要,因此,甚至无需涉及数据文件。 

 

    4.1.2 索引的弊端 

 

    一般情况下,如果MySQL能够知道怎样用索引来更快地处理查询,它就会这样做。这表示,在大多数情况下,如果您不对表进行索引,则损害的是您自己的利益。可以看出,作者描绘了索引的诸多好处。但有不利之处吗?是的,有。实际上,这些缺点被优点所掩盖了, 

 

但应该对它们有所了解。 

 

    首先,索引文件要占磁盘空间。如果有大量的索引,索引文件可能会比数据文件更快地达到最大的文件尺寸。其次,索引文件加快了检索,但增加了插入和删除,以及更新索引列中的值的时间(即,降低了大多数涉及写入的操作的时间),因为写操作不仅涉及数据行,而且还常常涉及索引。一个表拥有的索引越多,则写操作的平均性能下降就越大。在4 . 4节“有效地装载数据”中,我们将更为详细地介绍这些性能问题,并讨论怎样解决。 

 

    4.1.3 选择索引 

 

    创建索引的语法已经在3 . 4 . 3节“创建和删除索引”中进行了介绍。这里,我们假定您已经阅读过该节。但是知道语法并不能帮助确定表怎样进行索引。要决定表怎样进行索引需要考虑表的使用方式。本节介绍一些关于怎样确定和挑选索引列的准则: 

 

    ■ 搜索的索引列,不一定是所要选择的列。换句话说,最适合索引的列是出现在WHERE 子句中的列,或连接子句中指定的列,而不是出现在SELECT关键字后的选择列表中的列: 

 

    当然,所选择的列和用于WHERE 子句的列也可能是相同的。关键是,列出现在选择列表中不是该列应该索引的标志。出现在连接子句中的列或出现在形如col1= col2 的表达式中的列是很适合索引的列。查询中的col_b 和col_c 就是这样的例子。如果MySQL能利用连接列来优化一个查询,表示它通过消除全表扫描相当可观地减少了表行的组合。 

 

    ■ 使用惟一索引。考虑某列中值的分布。对于惟一值的列,索引的效果最好,而具有多个重复值的列,其索引效果最差。例如,存放年龄的列具有不同值,很容易区分各行。而用来记录性别的列,只含有“ M”和“F”,则对此列进行索引没有多大用处(不管搜索哪个值,都会得出大约一半的行)。 

 

    ■ 使用短索引。如果对串列进行索引,应该指定一个前缀长度,只要有可能就应该这样做。例如,如果有一个CHAR(200) 列,如果在前10 个或20 个字符内,多数值是惟一的,那么就不要对整个列进行索引。对前10 个或20 个字符进行索引能够节省大量索引空间,也可能会使查询更快。较小的索引涉及的磁盘I/O 较少,较短的值比较起来更快。更为重要的是,对于较短的键值,索引高速缓存中的块能容纳更多的键值,因此,MySQL也可以在内存中容纳更多的值。这增加了找到行而不用读取索引中较多块的可能性。(当然,应该利用一些常识。如仅用列值的第一个字符进行索引是不可能有多大好处的,因为这个索引中不会有许多不同的值。) 

 

    ■ 利用最左前缀。在创建一个n 列的索引时,实际是创建了MySQL可利用的n 个索引。多列索引可起几个索引的作用,因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。(这与索引一个列的前缀不同,索引一个列的前缀是利用该的前n 个字符作为索引值。) 

 

    假如一个表在分别名为s t a t e、city 和zip 的三个列上有一个索引。索引中的行是按state/city/zip 的次序存放的,因此,索引中的行也会自动按state/city 的顺序和state 的顺序存放。这表示,即使在查询中只指定state 值或只指定state 和city 的值,MySQL也可以利用索引。因此,此索引可用来搜索下列的列组合: 

 

    state,city,zip 

 

    state,city 

 

    sate 

 

    MySQL不能使用不涉及左前缀的搜索。例如,如果按city 或zip 进行搜索,则不能使用该索引。如果要搜索某个州以及某个zip 代码(索引中的列1和列3),则此索引不能用于相应值的组合。但是,可利用索引来寻找与该州相符的行,以减少搜索范围

 

bitsCN.com
声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
深入理解MySQL索引优化器工作原理深入理解MySQL索引优化器工作原理Nov 09, 2022 pm 02:05 PM

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于索引优化器工作原理的相关内容,其中包括了MySQL Server的组成,MySQL优化器选择索引额原理以及SQL成本分析,最后通过 select 查询总结整个查询过程,下面一起来看一下,希望对大家有帮助。

sybase是什么数据库sybase是什么数据库Sep 22, 2021 am 11:39 AM

sybase是基于客户/服务器体系结构的数据库,是一个开放的、高性能的、可编程的数据库,可使用事件驱动的触发器、多线索化等来提高性能。

visual foxpro数据库文件是什么visual foxpro数据库文件是什么Jul 23, 2021 pm 04:53 PM

visual foxpro数据库文件是管理数据库对象的系统文件。在VFP中,用户数据是存放在“.DBF”表文件中;VFP的数据库文件(“.DBC”)中不存放用户数据,它只起将属于某一数据库的 数据库表与视图、连接、存储过程等关联起来的作用。

数据库系统的构成包括哪些数据库系统的构成包括哪些Jul 15, 2022 am 11:58 AM

数据库系统由4个部分构成:1、数据库,是指长期存储在计算机内的,有组织,可共享的数据的集合;2、硬件,是指构成计算机系统的各种物理设备,包括存储所需的外部设备;3、软件,包括操作系统、数据库管理系统及应用程序;4、人员,包括系统分析员和数据库设计人员、应用程序员(负责编写使用数据库的应用程序)、最终用户(利用接口或查询语言访问数据库)、数据库管理员(负责数据库的总体信息控制)。

microsoft sql server是什么软件microsoft sql server是什么软件Feb 28, 2023 pm 03:00 PM

microsoft sql server是Microsoft公司推出的关系型数据库管理系统,是一个全面的数据库平台,使用集成的商业智能(BI)工具提供了企业级的数据管理,具有使用方便可伸缩性好与相关软件集成程度高等优点。SQL Server数据库引擎为关系型数据和结构化数据提供了更安全可靠的存储功能,使用户可以构建和管理用于业务的高可用和高性能的数据应用程序。

数据库的什么是指数据的正确性和相容性数据库的什么是指数据的正确性和相容性Jul 04, 2022 pm 04:59 PM

数据库的“完整性”是指数据的正确性和相容性。完整性是指数据库中数据在逻辑上的一致性、正确性、有效性和相容性。完整性对于数据库系统的重要性:1、数据库完整性约束能够防止合法用户使用数据库时向数据库中添加不合语义的数据;2、合理的数据库完整性设计,能够同时兼顾数据库的完整性和系统的效能;3、完善的数据库完整性有助于尽早发现应用软件的错误。

access数据库的结构层次是什么access数据库的结构层次是什么Aug 26, 2022 pm 04:45 PM

结构层次是“数据库→数据表→记录→字段”;字段构成记录,记录构成数据表,数据表构成了数据库。数据库是一个完整的数据的记录的整体,一个数据库包含0到N个表,一个表包含0到N个字段,记录是表中的行。

mysql查询慢的因素除了索引,还有什么?mysql查询慢的因素除了索引,还有什么?Jul 19, 2022 pm 08:22 PM

mysql查询为什么会慢,关于这个问题,在实际开发经常会遇到,而面试中,也是个高频题。遇到这种问题,我们一般也会想到是因为索引。那除开索引之外,还有哪些因素会导致数据库查询变慢呢?

See all articles

热AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover

AI Clothes Remover

用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool

Undress AI Tool

免费脱衣服图片

Clothoff.io

Clothoff.io

AI脱衣机

AI Hentai Generator

AI Hentai Generator

免费生成ai无尽的。

热门文章

R.E.P.O.能量晶体解释及其做什么(黄色晶体)
2 周前By尊渡假赌尊渡假赌尊渡假赌
仓库:如何复兴队友
4 周前By尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒险:如何获得巨型种子
4 周前By尊渡假赌尊渡假赌尊渡假赌

热工具

螳螂BT

螳螂BT

Mantis是一个易于部署的基于Web的缺陷跟踪工具,用于帮助产品缺陷跟踪。它需要PHP、MySQL和一个Web服务器。请查看我们的演示和托管服务。

PhpStorm Mac 版本

PhpStorm Mac 版本

最新(2018.2.1 )专业的PHP集成开发工具

VSCode Windows 64位 下载

VSCode Windows 64位 下载

微软推出的免费、功能强大的一款IDE编辑器

记事本++7.3.1

记事本++7.3.1

好用且免费的代码编辑器

Atom编辑器mac版下载

Atom编辑器mac版下载

最流行的的开源编辑器