MYSQL数据库中索引的示例分析

这篇文章主要为大家展示了“MYSQL数据库中索引的示例分析”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“MYSQL数据库中索引的示例分析”这篇文章吧。

MYSQL INDEX 其中有什么好说的，不就是建立索引吗，一个建立索引的语句就建立上了，建立的索引就能提高效率，相关的语句就能使用这个索引。

真正是这样的吗？

这里我们有一个表employee， 他里面存储着员工的姓名和出生年月，这里我们有第一个假设  

1 只要建立索引，就会走索引    TRUE OF  FALSE

下图是表结构展示

我们现在要查询出生年月在1960年以前的人的姓名，这里我们提前建立关于出生日期的索引。

从下图看虽然执行分析器已经发行有了索引，但并未使用

这是为什么，我们总体有30万数据，但我们要查询大于 1960年的人有多少，进过计算11万人符合查询的条件,也就是说，在查询中我们的数据近乎少一半的数据都是符合条件的，自然MYSQL 5.7的基于COST 的MYSQL 数据库解释引擎，会判断出，走一个索引，在回调数据，比我直接要全表扫描的来的爽快。

那我们换一个条件，我们查找一个出生在 1960年1月 和 2月之间的员工，果不其然由于数据量的缩小，我们清楚的在 KEY 这个 item 中看到我们建立的索引，并且给出一共有1970行符合条件。

从这里我们得出了一个什么概念，索引的建立也是要基于你搜索的数据量与总体的数据量的之间的比值，比值如果较大，则不会走索引，走全表扫描。

所以，建立索引，查询就会走索引，这个说法是错误的。

我们继续假设，只要建立了索引，并且我们数据符合小数据量这个条件就会走索引。这个是 TRUE 还是FALSE

下面我们继续做一个实验，要建立一个联合索引，因为我们有两个查询，一个是查询first_name 是 Georgi 出生在1960年后的人数，按照我们以前的经验，我们建立一个联合索引将First_name  和  birth_date 建立一个联合索引，来满足这样的查询

create index idx_employee_birth_date_first_nameon employees(birth_date,first_name);

explain select first_name,gender fromemployees where first_name = 'Georgi' and birth_date > '1960-01-01';

结果是怎样的，竟然没有走索引，是数据量符合这个条件的太多了吗，我们经过查询符合这个条件的只有106人到底是怎么回事

那我们到底要怎么建立一个能让MYSQL在这个查询中，能使用的索引，我们尝试一下，将查询条件换位 

explain select first_name from employees where birth_date > '1960-01-01' and first_name = 'Georgi';

神奇的事情发生了，同样的查询语句，只需要调换条件的撰写的顺序就可以走索引了事情到此为止了吗？当然没有，MYSQL 还有惊喜给我们

查询语句的条件的顺序可以调整，那建立索引的顺序是否可以调整，

create index idx_employee_first_name_birth_date on employees(first_name,birth_date);

我们在进行查询看看结果如何，结果是我们新建的索引，居然被当做最优的索引，被查询使用，到底为什么

首先，使用过SQL SERVER 和ORACLE 的程序员，或DBA 要抛弃一个概念就是我们建立的索引和查询条件的顺序要基本一致。

这里经过多年的经验，总结出这样一句话

等于在前，范围在后，查询索引要一致，如有第三者，爱放那边放那边，条件缺一看那个，缺少等于，就玩完

可能猛的看到这句话，是不大理解里面的意思的，下面我们做演示，大家就能记住

什么是第三者，下面看演示

explain select first_name from employees where first_name = 'Georgi' andlast_name = 'facello' and birth_date > '1960-01-01';

大家可以清晰的看到，在中间查询条件中添加了一个索引中没有的条件，但查询中还是走了这个目前最优的索引，那如果把这个条件放到别的地方可以吗？

explain select first_name from employees where last_name = 'facello' andfirst_name = 'Georgi'  and birth_date> '1960-01-01';

答案也是可以的。最后一句，条件缺一看那个，缺少等于，就玩完

我们再次验证一下，

explain select first_name from employees where hire_date > '1986-01-01' andlast_name = 'facello' and first_name = 'Georgi';

果不其然，我们去掉了时间字段的范围查询，则查询还是可以继续走我们建立的索引，而且速度还不慢 那是否我们将前面的查询FIRST_NAME 的条件去掉，索引是否还能继续使用

explain select first_name from employees where hire_date > '1986-01-01' andlast_name = 'facello'  and  birth_date > '1960-01-01';

我们可以很清晰的看到，索引已经失效了，走了全表扫描。

——————————————————————

其实MYSQL 的索引和查询之间的关系还有更复杂的地方，今天就到此为止，相对其他数据库 ORACLE ,SQL SERVER 这方面的要求要复杂的多，所以使用MYSQL 数据库有三个事情是最好不要做的。

1  传统的表设计方式，这里不单单指的是，表的TYPE ，包括逻辑，如何能用更少的表，减少多表之间的关系等等，让程序做更多的事情，而不是数据库。

2  索引和SELECT  , DML 语句，必须要过一遍，也就是说，就算语句是程序生成的，也要导出来，根据这些语句的条件顺序来精心的建立索引，所以大部分MYSQL的SQL 语句都是手写而不是程序架构生成的，是有这方面因素的考虑，否则很可能就是一个失败的开始

3 索引建立，应谨慎，多个单个的索引，去应付复合查询的结果可能和你想象的不一样， INDEX MERGE optimizer switch在有些查询的MERGE 表现并不聪明，MERGE 功能（5.7    有很大的改进），很可能意想不到的对查询的二次性能伤害。

所以一个MYSQL 的 DBA ，含金量要比其他数据库在这方面掌握的知识复杂的多。

以上是“MYSQL数据库中索引的示例分析”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注亿速云行业资讯频道！