存储过程mysql循环_MySQL索引怎么用？究竟能有多快？看完这篇你就懂了

推荐学习

为了让索引更加直观高效，我把几百万条数据扔到一张表中（光靠这个数据，就写了一个小时生成数据的代码，解决文件问题花了两个小时）导入数据花了我一个小时，对我来说太困难了~(；д；)但是，任何不基于实际数据的理论都是**o！**o(´^｀)。 o）。让我们解释一下 MySQL 的使用，同时看看索引可以给我们的查询带来的性能改进。

使用索引的优点

提高查询效率，简单来说，就是搜索速度更快！快点！快点！外面说的是提高表的速度，加速表连接，减少分组排序时间，提高系统性能。说白了，都是快，搜索也快！ （顺便我发现百度的索引好像只有一两套优缺点，真的只是抄袭了世界上大量的文章，也欢迎大家抄袭我的，请注明出处或作者。）

到底有多快？

这是没有索引的百万级数据查找（这个算快的，慢到四十秒，而且时间不太稳定，因为数据是从磁盘块读取的，原理可以参考我提到的那篇文章开始时）10.797 s：

这是在 0.272 秒内对数百万数据进行索引搜索：

相差40多倍，相当于别人一年赚40万多元，我一年赚1万元。这个差距真的很痛苦。

使用索引的缺点

一切事物都有两面，好的一面和坏的一面。以时间换空间或以空间换时间的情况并不少见。索引就是用空间换时间，虽然不是那么典型（因为它的核心不是增加空间、减少时间，而是用空间换时间）。就是通过维护类似目录的结构来减少IO读写次数。最典型的空间替换时间是计数排序）。缺点自然就出现了：

1、维护成本高

索引维护类似于目录的结构。你可以想到《新华字典》中的目录。当你创建一个目录时，如果没有程序帮你手写目录，你就需要一页一页地翻看里面有哪个单词。 where，然后写入目录；如果删除一个单词或添加一个单词，则必须重新调整目录。对于程序来说也是如此。索引的创建和维护需要性能，因此会降低数据库修改的性能。就创建索引而言，创建百万级数据BTREE索引，数据内容长度为20个汉字，耗时为61.234s：

我们来比较一下维护索引的成本。例如，插入百万级数据，不带索引，需要0.480s：

插入百万级带索引数据的时间为1.273s：

差距其实还是蛮大的（请忽略我随便打的三个字）。

2、占用空间大

既然提到用空间换取时间，那么空间的浪费就在所难免了。我做了如下测试（测试数据库、数据库运行环境）。

首先，我创建了一个临时表，其中只有一个名为 field 的列。我发现该文件夹中存储的ibd文件初始大小为112k。插入一百万条数据后（一百万条数据不能多也不能少），大小为：

然后创建了一个BTREE索引，大小变为：

大约使用空间的两倍。但在实际应用中，不可能为每个字段都添加索引。在生产中，为索引保留的空间约占数据大小的五分之一。在这个数据至上、效率至上的时代，磁盘空间的成本似乎是相对划算的。

其实使用索引还有一个缺点，就是需要花时间看我的文章o(////▽////)q

指数分类

单值索引：一个索引只包含单列，一个表可以有多个单列索引；唯一索引：索引列的值必须唯一，但允许为空值（主键索引是唯一索引，但不能为空）；复合索引：是联合索引，即一个索引包含多个列；

我看网上所有关于索引的介绍都提到了唯一索引和复合索引。为什么单值索引不被视为索引？在互相“学习”的时候，自己思考一下是个好主意。

索引创建规则

既然指数有如此明显的优点和缺点，我们自然希望最大限度地发挥其优点，并尽可能地避免其缺点。所以索引最好能做到：

1.常用作查询或排序条件；

2. 尽可能少地保留重复值；

3、不会有太多的增删改。

如果满足以上三个规则，就可以创建索引了。 （这种不符合规则的数据我实在不想发，就这么说吧。这篇文章包括数据创建和各种实验一共花了十个小时写的，还没结束，我已经赶上了LOL的无限火力了，明明是周末却只能羡慕别人玩的太痛苦了）。

所以总结一下适合创作的情况（即基于以上三个条件作为参考的各种情况）：

1、主键：主键会自动创建唯一索引，所以我们不需要担心； 2、经常作为查询条件的字段：毕竟索引是为了快速查询而创建的； 3.查询中与其他表关联的字段：这就是外键，不仅在关联查询中使用，而且重复值很少，这很棒； 4、如果有多个字段，尽量创建组合索引：当查询优化器觉得分析两个查询索引太费力的时候，还不如用一次，它会帮你用一个，所以只要你遵循最佳左前缀原则，组合索引更可靠； 5、查询中的排序字段：如果通过索引访问排序字段，则排序速度将大大提高； 6. 在查询统计或分组字段中：同上。

那么什么时候不适合创建：

1、表记录太少，不会变得太多：要手，几百条数据建索引的时候直接查就好了；

2、频繁更新的字段不适合创建索引：维护索引很累。没有那么多的检查，而是不断的变化。系统就像一个程序员，需求经常变化。他们不跟你打架就好了；

3、对于Where条件中未使用的字段不创建索引：不要问为什么，只问出门左转到达电梯的时间；

索引 CRD 没有 U

创造

#该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE [table_name] ADD PRIMARY KEY ([column_list]);
#这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE [table_name] ADD UNIQUE [index_name] ([column_list]);
#添加普通索引，索引值可出现多次。
ALTER TABLE [table_name] ADD INDEX [index_name] ([column_list]);
#该语句指定了索引为 FULLTEXT ，用于全文索引。
ALTER TABLE [table_name] ADD FULLTEXT [index_name] ([column_list]);

查看阅读

SHOW INDEX FROM [table_name];

删除删除

DROP INDEX [index_name] ON [table_name];

更新一下，抱歉没有更新，我只是删除了，想改就重新添加了。

指标分析()

终于到了，咱们来一场真正的战斗吧。前面有开胃菜，现在是主餐，但我估计你已经吃饱了，而我做饭也累了，所以很尴尬。

什么是

废话不多说，看一下官网的API说明：

关于MySQL如何。适用于 、 、 、 和 。

中使用的每个表的一行。它按照 MySQL 读取它们的顺序列出了 . MySQL 所有连接都使用 -loop join 。这意味着MySQL从第一个表中读取一行，然后在该表、第三个表中查找一行，依此类推。当所有的都是 时，MySQL 和表会列出，直到找到有更多行的表。从此表和下一个表中读取下一行。

语句提供有关 MySQL 如何执行语句的信息。可用于分析、、、语句。为语句中使用的每个表返回一行信息。它按照 MySQL 在处理语句时读取表的顺序列出执行输出中的表。 MySQL使用嵌套循环连接方法来解析所有连接。这意味着MySQL从第一个表中读取一行，然后在第二个表、第三个表中查找匹配的行，依此类推。当所有表都处理完毕后，MySQL 将输出选定的列并返回表列表，直到找到具有更多匹配行的表。从此表中读取下一行并继续处理下一个表。

说白了，它可以让你看到表的读取顺序、索引使用情况、表之间的引用、优化器查询的执行状态分析。 （不准说每个汉字我都认识，但拼在一起不知道是什么意思）

使用与分析

使用：只需+你的sql语句：

EXPLAIN SELECT * FROM t_solid_test where c_name = 'Solid';

查询如下所示：

所有这些信息意味着什么？让我们一一看看。

ID

查询的序列号，它包含一组数字，表示查询中子句或操作表的执行顺序。

id值越大，优先级越高，先执行；如果id相同，则从上到下依次执行。

查询类型

：简单查询，查询中不包含子查询或UNION；

：如果查询包含任何复杂的子部分，则最外面的查询被标记为；

UNION：如果UNION后面出现第二个，则标记为UNION；如果FROM子句的子查询中包含UNION，则外层将被标记为：；

UNION：UNION 中的第二个或更高的语句，具体取决于外部查询；

UNION：UNION 的结果。

：包含或WHERE列表中的子查询；

：首先在子查询中，取决于外部查询

：FROM列表中包含的子查询被标记为（派生），MySQL将递归执行这些子查询并将结果放置在临时表中；

：派生表依赖于另一个表；

：物化子查询；

：子查询的结果无法缓存，必须为外部查询的每一行重新计算；

UNION：UNION 是不可缓存子查询中的第二个或更高的选择（请参阅参考资料）；

查询分区的匹配记录。如果未分区则为 NULL；

桌子

显示这行数据与哪张表相关；

类型

访问类型排列，显示查询中使用了哪种类型：

：表只有一行记录（等于系统表）。这是一个特殊的 const 类型列。它通常不会出现。这个可以忽略。

const：表示通过索引查找一次。 Const 用于比较键或索引。因为只匹配一行数据，所以MySQL可以通过将主键放在where列表中来快速将查询转换为常量。

：唯一索引扫描，对于每个索引键，表中只有一条记录与其匹配。常见于主键或唯一索引扫描。

ref：非唯一索引扫描，返回与单个值匹配的所有行。它本质上是一种索引访问，返回与单个值匹配的所有行。但是，它可能会找到与条件匹配的多行。所以它应该是搜索和扫描的混合体。

：使用索引执行连接。

：这种连接类型与 ref 类似，但不同的是 MySQL 还会额外搜索包含 NULL 值的行。

：该连接类型表示使用索引合并优化。在这种情况下，输出行中的键列包含所使用的索引的列表，并且包含所使用的索引的最长键部分的列表。有关更多信息，请参阅第 8.2.1.3 节“索引合并优化”。

：该类型替代了某些IN子查询，其形式为：value IN(FROM WHERE)，它只是一个索引查找功能，完全替代子查询以提高效率。

：此连接类型类似于 。它取代了 IN 子查询，但可以使用以下形式的子查询中的非唯一索引：value IN (FROM WHERE)。

range：仅检索给定范围的行，使用索引来选择行。键列显示使用哪个索引。一般来说，where 语句中会出现诸如、、in 等查询。这种范围扫描索引扫描比全表扫描要好，因为它只需要从索引中的某个点开始并在另一个点结束。一件事是，不需要扫描整个索引。

index：全索引扫描，index与ALL的区别在于索引类型只遍历索引树。这通常比ALL更快，因为索引文件通常比数据文件小（也就是说，虽然all和Index都读取整个表，但index从索引读取，而all从硬盘读取）。

all：全表扫描，会遍历整个表来查找匹配的行。注意：一般来说，要保证查询至少达到range级别，最好达到ref。

显示可能应用于该表的一个或多个索引。如果查询涉及的字段存在索引，则会列出该索引，但查询可能并未实际使用该索引。

钥匙

实际使用的索引。如果为 NULL，则不使用索引。如果查询中使用了覆盖索引，则索引和查询的字段会重叠。

表示索引中使用的字节数。该列可用于计算查询中使用的索引的长度。长度越短越好，且不会损失准确性。显示的值是索引字段的最大可能长度，而不是实际使用的长度，也就是说，它是根据表定义计算的，而不是从表中检索。

参考

如果可能的话，显示使用索引的哪一列作为常量。使用哪些列或常量来查找索引列上的值。

行

根据表统计信息和索引选择，粗略估计需要读取的行数才能找到所需的记录。

额外的

包含不适合其他列的重要附加信息：

using：表示mysql将使用外部索引对数据进行排序，而不是按照表中索引的顺序读取。 MySQL中无法使用索引完成的排序操作称为“文件排序”。

使用：临时表用于保存中间结果。 MySQL在对查询结果进行排序时使用临时表。常用于排序order by和group by查询。

USING索引：表示在相应的操作中使用一个覆盖索引（Index）来避免访问表的数据行，效率非常高！如果同时出现using where，则表示使用索引来进行索引键值的搜索；如果using where没有同时出现，则表明该索引是用来读取数据而不是执行搜索操作。

using where：表示在哪里使用过滤。

using join：使用连接缓存。

where：where子句的值始终为 false，不能用于获取任何元素。

away：在没有子句的情况下，基于索引优化MIN/MAX操作或者为存储引擎优化COUNT(*)操作，不必等到执行阶段进行计算。优化在查询执行计划生成阶段完成。

：优化操作，找到第一个匹配的元组后停止搜索相同的值。

索引用例最佳案例场景

最佳左前缀原则

后台创建的索引是联合索引。在联合索引中，匹配是从左到右进行的。如果开头没有匹配，索引就会失效。

尽量不要使用函数来操作索引

对索引列的任何操作（计算、函数、（自动或手动）类型转换）都会导致索引失败并切换到全表扫描。

索引中范围条件右侧的列将不会被使用

下面的联合索引中，当联合索引中间的值查询条件是范围查询时，不会使用右边的索引。

谨慎使用*

尝试使用覆盖索引（只访问索引的查询（索引列和查询列一致））来减少*。

索引失效的几种情况

不等于(!= or)、is null、is not null、或者像以通配符开头('%abc...')一样，字符串不包含单引号（类型转换）

这里不需要粘贴图像，没有什么可粘贴的，所以使用 type 表示 ALL；

索引使用总结建议

最后总结一下索引的使用：

对于单键索引，尽量为当前查询选择过滤性较好的索引。选择组合索引时，当前Query中过滤性最好的字段应该位于索引字段顺序中靠前的位置，效果越好。选择组合索引时，尽量选择能够在当前查询的where子句中包含更多字段的索引。尝试通过分析统计信息并尽可能调整查询的编写方式来选择合适的索引。