`

mysql数据库索引优化

 
阅读更多

我们首先讨论索引,因为它是加快查询的最重要的工具。还有其他加快查询的[url=javascript:;]技术[/url],但是最有效的莫过于恰当地使用索引了。在 MySQL 的邮件清单上,人们通常询问关于使查询更快的问题。在大量的案例中,都是因为表上没有索引,一般只要加上索引就可以立即解决问题。但这样也并非总是有效,因为优化并非总是那样简单。然而,如果不使用索引,在许多情形下,用其他手段改善性能只会是浪费时间。应该首先考虑使用索引取得最大的性能改善,然后再寻求其他可能有帮助的技术。 
本节介绍索引是什么、它怎样改善查询性能、索引在什么情况下可能会降低性能,以及怎样为表选择索引。下一节,我们将讨论 MySQL 的查询优化程序。除了知道怎样创建索引外,了解一些优化程序的知识也是有好处的,因为这样可以更好地利用所创建的索引。某些编写查询的方法实际上会妨碍索引的效果,应该避免这种情况出现。(虽然并非总会这样。有时也会希望忽略优化程序的作用。我们也将介绍这些情况。) 


索引对单个表查询的影响 
索引被用来快速找出在一个列上用一特定值的行。没有索引,MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大,花费时间越多。如果表对于查询的列有一个索引,MySQL能快速到达一个位置去搜寻到数据文件的中间,没有必要考虑所有数据。如果一个表有1000 行,这比顺序读取至少快100倍。注意你需要存取几乎所有1000行,它较快的顺序读取,因为此时我们避免磁盘寻道。 
例如对下面这样的一个student表: 
mysql>SELECT * FROM student
+------+---------+---------+---------+---------+
| id   | name    | english | chinese | history |
+------+---------+---------+---------+---------+
|   12 | Tom     |      66 |      93 |      67 |
|   56 | Paul    |      78 |      52 |      75 |
|   10 | Marry   |      54 |      89 |      74 |
|    4 | Tina    |      99 |      83 |      48 |
|   39 | William |      43 |      96 |      52 |
|   74 | Stone   |      42 |      40 |      61 |
|   86 | Smith   |      49 |      85 |      78 |
|   37 | Black   |      49 |      63 |      47 |
|   89 | White   |      94 |      31 |      52 |
+------+---------+---------+---------+---------+
这样,我们试图对它进行一个特定查询时,就不得不做一个全表的扫描,速度很慢。例如,我们查找出所有english成绩不及格的学生: 
mysql>SELECT name,english FROM student WHERE english<60;
+---------+---------+
| name    | english |
+---------+---------+
| Marry   |      54 |
| William |      43 |
| Stone   |      42 |
| Smith   |      49 |
| Black   |      49 |
+---------+---------+
其中,WHERE从句不得不匹配每个记录,以检查是否符合条件。对于这个较小的表也许感觉不到太多的影响。但是对于一个较大的表,例如一个非常大的学校,我们可能需要存储成千上万的记录,这样一个检索的所花的时间是十分可观的。 
如果,我们为english列创建一个索引: 
mysql>ALTER TABLE student ADD INDEX (english) ;
+-------------------+
| index for english |
+-------------------+
|                42 |
|                43 |
|                49 |
|                49 |
|                54 |
|                66 |
|                78 |
|                94 |
|                99 |
+-------------------+
如上表,此索引存储在索引文件中,包含表中每行的english列值,但此索引是在 english的基础上排序的。现在,不需要逐行搜索全表查找匹配的条款,而是可以利用索引进行查找。假如我们要查找分数小于60的所有行,那么可以扫描索引,结果得出5行。然后到达分数为66的行,及Tom的记录,这是一个比我们正在查找的要大的值。索引值是排序的,因此在读到包含Tom的记录时,我们知道不会再有匹配的记录,可以退出了。如果查找一个值,它在索引表中某个中间点以前不会出现,那么也有找到其第一个匹配索引项的定位算法,而不用进行表的顺序扫描(如二分查找法)。这样,可以快速定位到第一个匹配的值,以节省大量搜索时间。数据库利用了各种各样的快速定位索引值的技术,这些技术是什么并不重要,重要的是它们[url=javascript:;]工作[/url]正常,索引技术是个好东西。 
因此在执行下述查询 
mysql>SELECT name,english FROM user WHERE english<60;
其结果为: 
+---------+---------+
| name    | english |
+---------+---------+
| Stone   |      42 |
| William |      43 |
| Smith   |      49 |
| Black   |      49 |
| Marry   |      54 |
+---------+---------+
你应该可以发现,这个结果与未索引english列之前的不同,它是排序的,原因正式如上所述。
索引对多个表查询的影响 
前面的讨论描述了单表查询中索引的好处,其中使用索引消除了全表扫描,极大地加快了搜索的速度。在执行涉及多个表的连接查询时,索引甚至会更有价值。在单个表的查询中,每列需要查看的值的数目就是表中行的数目。而在多个表的查询中,可能的组合数目极大,因为这个数目为各表中行数之积。 
假如有三个未索引的表 t1t2t3,分别只包含列 c1c2c3,每个表分别由含有数值 1  1000 1000 行组成。查找对应值相等的表行组合的查询如下所示: 
此查询的结果应该为 1000 行,每个组合包含 3 个相等的值。如果我们在无索引的情况下处理此查询,则不可能知道哪些行包含那些值。因此,必须寻找出所有组合以便得出与 WHERE 子句相配的那些组合。可能的组合数目为1000×1000×1000(十亿),比匹配数目多一百万倍。很多工作都浪费了,并且这个查询将会非常慢,即使在如像 MySQL 这样快的数据库中执行也会很慢。而这还是每个表中只有 1000 行的情形。如果每个表中有一百万行时,将会怎样?很显然,这样将会产生性能极为低下的结果。如果对每个表进行索引,就能极大地加速查询进程,因为利用索引的查询处理如下: 
1)
 如下从表 t1 中选择第一行,查看此行所包含的值。 
2)
 使用表 t2 上的索引,直接跳到 t2 中与来自t1 的值匹配的行。类似,利用表 t3 上的索引,直接跳到 t3中与来自t1 的值匹配的行。 
3)
 进到表 t1 的下一行并重复前面的过程直到 t1 中所有的行已经查过。 
在此情形下,我们仍然对表 t1 执行了一个完全扫描,但能够在表 t2  t3 上进行索引查找直接取出这些表中的行。从道理上说,这时的查询比未用索引时要快一百万倍。 
如上所述,MySQL 利用索引加速了 WHERE 子句中与条件相配的行的搜索,或者说在执行连接时加快了与其他表中的行匹配的行的搜索。 
多列索引对查询的影响 
假定你发出下列SELECT语句: 
mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
如果一个多列索引存在于col1col2上,适当的行可以直接被取出。如果分开的单行列索引存在于col1col2上,优化器试图通过决定哪个索引将找到更少的行并来找出更具限制性的索引并且使用该索引取行。 
你可以这样创建一个多列索引: 
mysql>ALTER TABLE tbl_name ADD INDEX(col1,col2);
而你应该这样创建分开的单行列索引: 
mysql>ALTER TABLE tble_name ADD INDEX(col1);mysql>ALTER TABLE tble_name ADD INDEX(col1);
如果表有一个多列索引,任何最左面的索引前缀能被优化器使用以找出行。例如,如果你有一个3行列索引(col1,col2,col3),你已经索引了在(col1)(col1,col2)(col1,col2,col3)上的搜索能力。 
如果列不构成索引的最左面前缀,MySQL不能使用一个部分的索引。假定你下面显示的SELECT语句: 
mysql> SELECT * FROM tbl_name WHERE col1=val1;mysql> SELECT * FROM tbl_name WHERE col2=val2;mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;
如果一个索引存在于(col1col2col3)上,只有上面显示的第一个查询使用索引。第二个和第三个查询确实包含索引的列,但是(col2)(col2col3)不是(col1col2col3)的最左面前缀。 
如果LIKE参数是一个不以一个通配符字符起始的一个常数字符串,MySQL也为LIKE比较使用索引。例如,下列SELECT语句使用索引: 
mysql> select * from tbl_name where key_col LIKE "Patrick%";mysql> select * from tbl_name where key_col LIKE "Pat%_ck%";
在第一条语句中,只考虑有"Patrick" <= key_col < "Patricl"的行。在第二条语句中,只考虑有"Pat" <= key_col < "Pau"的行。 
下列SELECT语句将不使用索引: 
mysql> select * from tbl_name where key_col LIKE "%Patrick%";mysql> select * from tbl_name where key_col LIKE other_col;
在第一条语句中,LIKE值以一个通配符字符开始。在第二条语句中,LIKE值不是一个常数。 
如果 column_name 是一个索引,使用column_name IS NULL的搜索将使用索引。 
MySQL
通常使用找出最少数量的行的索引。一个索引被用于你与下列操作符作比较的列:=>>=<<=BETWEEN和一个有一个非通配符前缀象'something%'LIKE的列。 
对于一个多列索引,如果在WHERE子句的所有AND层次使用索引,将不使用来索引优化查询。为了能够使用索引优化查询,必须把一个多列索引的前缀使用在一个AND条件组中。 
下列WHERE子句使用索引: 
... WHERE index_part1=1 AND index_part2=2... WHERE index=1 OR A=10 AND index=2      ... WHERE index_part1='hello' AND index_part_3=5          
这些WHERE子句不使用索引: ... WHERE index_part2=1 AND index_part3=2  ... WHERE index=1 OR A=10                  ... WHERE index_part1=1 OR index_part2=10  
MySQL
索引的作用 
所有的MySQL索引(PRIMARYUNIQUEINDEX)B树中存储。字符串是自动地压缩前缀和结尾空间。CREATE INDEX句法。 
索引用于: 
快速找出匹配一个WHERE子句的行。 
在多个表的查询时,执行连接时加快了与其他表中的行匹配的行的搜索。 
对特定的索引列找出MAX()MIN()值。 
如果排序或分组在一个可用索引的最左面前缀上进行(例如,ORDER BY key_part_1,key_part_2),排序或分组一个表。如果所有键值部分跟随DESC,键以倒序被读取。 
在一些情况中,一个查询能被优化来检索值,不用咨询数据文件。如果对某些表的所有使用的列是数字型的并且构成某些键的最左面前缀,为了更快,值可以从索引树被检索出来。 
索引的弊端 

一般情况下,如果 MySQL 能够知道怎样用索引来更快地处理查询,它就会这样做。这表示,在大多数情况下,如果您不对表进行索引,则损害的是您自己的利益。可以看出,作者描绘了索引的诸多好处。但有不利之处吗?是的,有。实际上,这些缺点被优点所掩盖了,但应该对它们有所了解。 
首先,索引文件要占磁盘空间。如果有大量的索引,索引文件可能会比数据文件更快地达到最大的文件尺寸。其次,索引文件加快了检索,但增加了插入和删除,以及更新索引列中的值的时间(即,降低了大多数涉及写入的操作的时间),因为写操作不仅涉及数据行,而且还常常涉及索引。一个表拥有的索引越多,则写操作的平均性能下降就越大。在8.4.4节记录装载和修改的速度中,我们将更为详细地介绍这些性能问题,并讨论怎样解决。 
选择索引的准则 
创建索引的语法已经在4.5索引属性中进行了介绍。这里,我们假定您已经阅读过该节。但是知道语法并不能帮助确定表怎样进行索引。要决定表怎样进行索引需要考虑表的使用方式。本节介绍一些关于怎样确定和挑选索引列的准则: 
1
、搜索的索引列,不一定是所要选择的列 
换句话说,最适合索引的列是出现在 WHERE 子句中的列,或连接子句中指定的列,而不是出现在SELECT 关键字后的选择列表中的列,例如: 


SELECTcol_a                           ←
不适合作索引列FROMTbl1 LEFT JOIN tbl2ON tbl1.col_b = tbl2.col_c      ←适合作索引列WHEREcol_d = expr                    ←适合作索引列
当然,所选择的列和用于 WHERE 子句的列也可能是相同的。关键是,列出现在选择列表中不是该列应该索引的标志。 
出现在连接子句中的列或出现在形如 col1 = col2 的表达式中的列是很适合索引的列。查询中的col_b col_c 就是这样的例子。如果 MySQL 能利用连接列来优化一个查询,表示它通过消除全表扫描相当可观地减少了表行的组合。 
2
、使用惟一索引 
考虑某列中值的分布。对于惟一值的列,索引的效果最好,而具有多个重复值的列,其索引效果最差。例如,存放年龄的列具有不同值,很容易区分各行。而用来记录性别的列,只含有“M”“F”,则对此列进行索引没有多大用处(不管搜索哪个值,都会得出大约一半的行)。 
3
、使用短索引 
如果对串列进行索引,应该指定一个前缀长度,只要有可能就应该这样做。例如,如果有一个 CHAR(200) 列,如果在前 10 个或20 个字符内,多数值是惟一的,那么就不要对整个列进行索引。对前 10 个或 20 个字符进行索引能够节省大量索引空间,也可能会使查询更快。较小的索引涉及的磁盘 I/O 较少,较短的值比较起来更快。更为重要的是,对于较短的键值,索引高速缓存中的块能容纳更多的键值,因此,MySQL 也可以在内存中容纳更多的值。这增加了找到行而不用读取索引中较多块的可能性。(当然,应该利用一些常识。如仅用列值的第一个字符进行索引是不可能有多大好处的,因为这个索引中不会有许多不同的值。) 
4
、利用最左前缀 
在创建一个 n 列的索引时,实际是创建了 MySQL 可利用的n 个索引。多列索引可起几个索引的作用,因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。(这与索引一个列的前缀不同,索引一个列的前缀是利用该的前 n 个字符作为索引值。) 
假如一个表在分别名为 statecity zip 的三个列上有一个索引。索引中的行是按 state/city/zip 的次序存放的,因此,索引中的行也会自动按state/city 的顺序和 state 的顺序存放。这表示,即使在查询中只指定state 值或只指定 state  city 的值,MySQL 也可以利用索引。因此,此索引可用来搜索下列的列组合: 
MySQL
 不能使用不涉及左前缀的搜索。例如,如果按 city  zip 进行搜索,则不能使用该索引。如果要搜索某个州以及某个 (索引中的列1和列3),则此索引不能用于相应值的组合。但是,可利用索引来寻找与该州相符的行,以减少搜索范围。 
5
、不要过度索引 
不要以为索引越多越好,什么东西都用索引是错的。每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能,这一点我们前面已经介绍过。在修改表的内容时,索引必须进行更新,有时可能需要重构,因此,索引越多,所花的时间越长。如果有一个索引很少利用或从不使用,那么会不必要地减缓表的修改速度。此外,MySQL 在生成一个执行计划时,要考虑各个索引,这也要费时间。创建多余的索引给查询优化带来了更多的工作。索引太多,也可能会使 MySQL 选择不到所要使用的最好索引。只保持所需的索引有利于查询优化。 
如果想给已索引的表增加索引,应该考虑所要增加的索引是否是现有多列索引的最左索引。如果是,则就不要费力去增加这个索引了,因为已经有了。 
6
、考虑在列上进行的比较类型 
索引可用于“<”“<=”“=”“>=”“>”BETWEEN 运算。在模式具有一个直接量前缀时,索引也用于 LIKE 运算。如果只将某个列用于其他类型的运算时(如STRCMP( )),对其进行索引没有价值。 

本节介绍了索引在优化查询中的作用,包括了索引优化查询的原理,索引在各种情况的检索中的益处,也包括索引的的弊端:增加了存储的空间,使装载数据变慢。 
索引是优化查询的最常用也是最有效的的方法,一个数据表,尤其是容量很大的表,建立合适的索引,会使查询的速度提高很大

分享到:
评论

相关推荐

    Scratch图形化编程语言入门与进阶指南

    内容概要:本文全面介绍了Scratch编程语言,包括其历史、发展、特点、主要组件以及如何进行基本和进阶编程操作。通过具体示例,展示了如何利用代码块制作动画、游戏和音乐艺术作品,并介绍了物理模拟、网络编程和扩展库等功能。 适合人群:编程初学者、教育工作者、青少年学生及对编程感兴趣的各年龄段用户。 使用场景及目标:①帮助初学者理解编程的基本概念和逻辑;②提高学生的创造力、逻辑思维能力和问题解决能力;③引导用户通过实践掌握Scratch的基本和高级功能,制作个性化作品。 其他说明:除了基础教学,文章还提供了丰富的学习资源和社区支持,帮助用户进一步提升技能。

    mmexport1734874094130.jpg

    mmexport1734874094130.jpg

    基于simulink的悬架仿真模型,有主动悬架被动悬架天棚控制半主动悬架 1基于pid控制的四自由度主被动悬架仿真模型 2基于模糊控制的二自由度仿真模型,对比pid控制对比被动控制,的比较说明

    基于simulink的悬架仿真模型,有主动悬架被动悬架天棚控制半主动悬架 [1]基于pid控制的四自由度主被动悬架仿真模型 [2]基于模糊控制的二自由度仿真模型,对比pid控制对比被动控制,的比较说明 [3]基于天棚控制的二自由度悬架仿真 以上模型,说明文档齐全,仿真效果明显

    【组合数学答案】组合数学-苏大李凡长版-课后习题答案

    内容概要:本文档是《组合数学答案-网络流传版.pdf》的内容,主要包含了排列组合的基础知识以及一些经典的组合数学题目。这些题目涵盖了从排列数计算、二项式定理的应用到容斥原理的实际应用等方面。通过对这些题目的解析,帮助读者加深对组合数学概念和技巧的理解。 适用人群:适合初学者和有一定基础的学习者。 使用场景及目标:可以在学习组合数学课程时作为练习题参考,也可以在复习考试或准备竞赛时使用,目的是提高解决组合数学问题的能力。 其他说明:文档中的题目覆盖了组合数学的基本知识点,适合逐步深入学习。每个题目都有详细的解答步骤,有助于读者掌握解题思路和方法。

    YOLO算法-雨水排放涵洞模型数据集-1000张图像带标签-.zip

    YOLO系列算法目标检测数据集,包含标签,可以直接训练模型和验证测试,数据集已经划分好,包含数据集配置文件data.yaml,适用yolov5,yolov8,yolov9,yolov7,yolov10,yolo11算法; 包含两种标签格:yolo格式(txt文件)和voc格式(xml文件),分别保存在两个文件夹中,文件名末尾是部分类别名称; yolo格式:<class> <x_center> <y_center> <width> <height>, 其中: <class> 是目标的类别索引(从0开始)。 <x_center> 和 <y_center> 是目标框中心点的x和y坐标,这些坐标是相对于图像宽度和高度的比例值,范围在0到1之间。 <width> 和 <height> 是目标框的宽度和高度,也是相对于图像宽度和高度的比例值; 【注】可以下拉页面,在资源详情处查看标签具体内容;

    操作系统实验 Ucore lab5

    操作系统实验 Ucore lab5

    学生成绩管理系统软件界面

    基于matlab开发的学生成绩管理系统GUI界面,可以实现学生成绩载入,显示,处理及查询。

    NVR-K51-BL-CN-V4.50.010-210322

    老版本4.0固件,(.dav固件包),支持7700N-K4,7900N-K4等K51平台,升级后出现异常或变砖可使用此版本。请核对自己的机器信息,确认适用后在下载。

    YOLO算法-塑料数据集-7张图像带标签-塑料.zip

    YOLO系列算法目标检测数据集,包含标签,可以直接训练模型和验证测试,数据集已经划分好,包含数据集配置文件data.yaml,适用yolov5,yolov8,yolov9,yolov7,yolov10,yolo11算法; 包含两种标签格:yolo格式(txt文件)和voc格式(xml文件),分别保存在两个文件夹中,文件名末尾是部分类别名称; yolo格式:<class> <x_center> <y_center> <width> <height>, 其中: <class> 是目标的类别索引(从0开始)。 <x_center> 和 <y_center> 是目标框中心点的x和y坐标,这些坐标是相对于图像宽度和高度的比例值,范围在0到1之间。 <width> 和 <height> 是目标框的宽度和高度,也是相对于图像宽度和高度的比例值; 【注】可以下拉页面,在资源详情处查看标签具体内容;

    YOLO算法-杂草检测项目数据集-3970张图像带标签-杂草.zip

    YOLO算法-杂草检测项目数据集-3970张图像带标签-杂草.zip

    E008 库洛米(3页).zip

    E008 库洛米(3页).zip

    基于西门子 PLC 的晶圆研磨机自动控制系统设计与实现-论文

    内容概要:本文详细阐述了基于西门子PLC的晶圆研磨机自动控制系统的设计与实现。该系统结合了传感器技术、电机驱动技术和人机界面技术,实现了晶圆研磨过程的高精度和高效率控制。文中详细介绍了控制系统的硬件选型与设计、软件编程与功能实现,通过实验测试和实际应用案例验证了系统的稳定性和可靠性。 适合人群:具备一定的自动化控制和机械设计基础的工程师、研究人员以及从事半导体制造的技术人员。 使用场景及目标:本研究为半导体制造企业提供了一种有效的自动化解决方案,旨在提高晶圆研磨的质量和生产效率,降低劳动强度和生产成本。系统适用于不同规格晶圆的研磨作业,可以实现高精度、高效率、自动化的晶圆研磨过程。 阅读建议:阅读本文时,重点关注晶圆研磨工艺流程和技术要求,控制系统的硬件和软件设计方法,以及实验测试和结果分析。这将有助于读者理解和掌握该自动控制系统的实现原理和应用价值。

    YOLO算法-禾本科杂草数据集-4760张图像带标签.zip

    YOLO系列算法目标检测数据集,包含标签,可以直接训练模型和验证测试,数据集已经划分好,包含数据集配置文件data.yaml,适用yolov5,yolov8,yolov9,yolov7,yolov10,yolo11算法; 包含两种标签格:yolo格式(txt文件)和voc格式(xml文件),分别保存在两个文件夹中,文件名末尾是部分类别名称; yolo格式:<class> <x_center> <y_center> <width> <height>, 其中: <class> 是目标的类别索引(从0开始)。 <x_center> 和 <y_center> 是目标框中心点的x和y坐标,这些坐标是相对于图像宽度和高度的比例值,范围在0到1之间。 <width> 和 <height> 是目标框的宽度和高度,也是相对于图像宽度和高度的比例值; 【注】可以下拉页面,在资源详情处查看标签具体内容;

    深圳建筑安装公司“挖掘机安全操作规程”.docx

    深圳建筑安装公司“挖掘机安全操作规程”

    YOLO算法-汽车数据集-120张图像带标签-汽车.zip

    YOLO系列算法目标检测数据集,包含标签,可以直接训练模型和验证测试,数据集已经划分好,包含数据集配置文件data.yaml,适用yolov5,yolov8,yolov9,yolov7,yolov10,yolo11算法; 包含两种标签格:yolo格式(txt文件)和voc格式(xml文件),分别保存在两个文件夹中,文件名末尾是部分类别名称; yolo格式:<class> <x_center> <y_center> <width> <height>, 其中: <class> 是目标的类别索引(从0开始)。 <x_center> 和 <y_center> 是目标框中心点的x和y坐标,这些坐标是相对于图像宽度和高度的比例值,范围在0到1之间。 <width> 和 <height> 是目标框的宽度和高度,也是相对于图像宽度和高度的比例值; 【注】可以下拉页面,在资源详情处查看标签具体内容;

    大题解题方法等4个文件.zip

    大题解题方法等4个文件.zip

    保障性安居工程考评内容和评价标准.docx

    保障性安居工程考评内容和评价标准.docx

    监督机构检查记录表.docx

    监督机构检查记录表.docx

    (177588850)基于java+mysql+swing的学生选课成绩信息系统

    该项目适合初学者进行学习,有效的掌握java、swing、mysql等技术的基础知识。资源包含源码、视频和文档 资源下载|如果你正在做毕业设计,需要源码和论文,各类课题都可以,私聊我。 商务合作|如果你是在校大学生,正好你又懂语言编程,或者你可以找来需要做毕设的伙伴,私聊我。。内容来源于网络分享,如有侵权请联系我删除。另外如果没有积分的同学需要下载,请私信我。

    218) Leverage - 创意机构与作品集 WordPress 主题 2.2.7.zip

    218) Leverage - 创意机构与作品集 WordPress 主题 2.2.7.zip

Global site tag (gtag.js) - Google Analytics