[大数据]SQL索引失效案例

索引失效案例

MysQL中提高性能的一个最有效的方式是对数据表设计合理的索引。索引提供了高效访问数据的方法，并且加快查询的速度，因此索引对查询的速度有着至关重要的影响。

• 使用索引可以快速地定位.表中的某条记录，从而提高数据库查询的速度，提高数据库的性能。
• 如果查询时没有使用索引，查询语句就会扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。
  大多数情况下都（默认）采用B+树来构建索引。只是空间列类型的索引使用R-树，并且MEMORY表还支持hash索引。l

其实，用不用索引，最终都是优化器说了算。优化器是基于什么的优化器?基于cost开销
(CostBaseOptimizer)，它不是基于规则(Rule-Based0ptimizer)，也不是基于语义。怎么样开销小就怎么来。另外，SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。

1.1 全值匹配

有几个where过滤条件，就建立几个索引，效率会逐步提升

1.2 最左前缀原则

在建立联合索引时

数据库会根据联合索引中字段顺序依次建立B+树

比如下面这条建立索引的语句

CREATE INDEx idx_age_classid_name oN student (age,classId,NAME);

会先根据age顺序建立B+树，当age值相同时，再根据classId的顺序建立B+树，NAME字段同理

同时再进行and操作时，MySQL的优化器会调整and语句的执行顺序

只要是and语句中有以联合索引最左边的字段为查询条件时，都会走该联合索引

第一条语句走第一个索引

不走第三个索引是因为，第三个B+树是依次根据age,classID,NAME的顺序建立的B+树，age字段可以走该索引，但NAME字段不能走该索引

因为建立索引的循序是age,classID ,NAME ，只有classId字段走了索引之后，NAME字段才能走索引

但是第一条查询语句中只有age,name两个字段作为查询条件，所以name根本走不了索引，即使该句走了第三个索引，本质上还是走的第一个索引，即使以age字段建立的B+树

结论:MysQL可以为多个字段创建索引，一个索引可以包括16个字段对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。用这些字段中第1个字段时，多列(或联合）索引不会被使用。

1.3 主键插入顺序

可这个数据页已经满了，再插进来咋办呢？我们需要把当前 页面分裂 成两个页面，把本页中的一些记录 移动到新创建的这个页中。页面分裂和记录移位意味着什么？意味着： 性能损耗 ！所以如果我们想尽量 避免这样无谓的性能损耗，最好让插入的记录的 主键值依次递增 ，这样就不会发生这样的性能损耗了。所以我们建议：让主键具有 AUTO_INCREMENT，让存储引擎自己为表生成主键，而不是我们手动插入 ，

我们自定义的主键列 id 拥有 AUTO_INCREMENT 属性，在插入记录时存储引擎会自动为我们填入自增的 主键值。这样的主键占用空间小，顺序写入，减少页分裂

1.4 计算、函数、类型转换(自动或手动)导致索引失效

此时的筛选条件是字段经过函数，计算处理后的数据与目标数据相比较

字段本身的数据的顺序此时变得没那么重要了

由于MySQL也不知道什么样的数据再经过计算，函数后会达到条件

他只能一条条的检索全部数据，然后做函数运算。

所以不会走索引

1.5 类型转换导致索引失效

1.6 范围条件右边的列索引失效

原因：想B+树构建，查询过程

此时只有，age,classId两个索引生效了，name并没有生效

这是由于对classId的筛选是一个范围查询，范围条件右边的列索引会失效

所以这提醒我们再建立索引时要将等值条件相关的字段提前，将范围查询的字段置后

1.7 不等于(!= 或者<>)索引失效

原因：想B+树构建，查询过程

1.8 is null可以使用索引，is not null无法使用索引

结论:最好在设计数据表的时候就将字段设置为NOT NULL约束，比如你可以将INT类型的字段，默认值设置为o。将字符类型的默认值设置为空字符串()。
拓展:同理，在查询中使用not like 也无法使用索引，导致全表扫描。

1.9 like以通配符%开头索引失效

1.10 OR 前后存在非索引的列，索引失效

原因：or是只要一个条件满足即可，即便你age有索引，查找age时使用了索引，但这会导致有部分数据没有扫描到，那没有扫描到的情况可能会有classid=100的情况。所以你仍要以classid再再次扫描表，但是classid没有索引，所以需要全表扫描，所以为什么不直接全表扫描呢？

1.11 数据库和表的字符集统一使用utf8mb4

统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不 同的 字符集 进行比较前需要进行 转换 会造成索引失效。