搜索引擎进行信息检索的优化策略方法(【实用】阿里巴巴创建索引失效原因及解决办法)

　　搜索引擎进行信息检索的优化策略方法(【实用】阿里巴巴创建索引失效原因及解决办法)

　　首先，让我们巩固索引、快速数据检索、稳定查询和顺序存储的优势，防止服务器构建临时表，将随机 I/O 变为有序 I/O。

　　但是，一旦不定期创建索引，就会导致以下问题，占用额外空间，浪费内存，降低数据增删改查的性能。

　　因此，只有在了解索引数据结构的基础上，才能创建高效的索引。

　　**本文所有操作均在MySQL8.0.12中进行**

　　一、创建索引规范

　　在学习索引优化之前，需要对创建索引的规范有一定的了解。本规范来自阿里巴巴开发手册。

　　二、索引失败原因

　　要创建索引，您需要知道在什么情况下索引会失败。只有了解了索引失败的原因，创建索引时才不会出现已知错误。

　　1.领袖不能死

　　这个经典的说法是在创建索引时涵盖了最左边的原则。

　　例如表结构为u_id、u_name、u_age、u_sex、u_phone、u_time

　　创建一个索引为 idx_user_name_age_sex。

　　查询条件必须收录 u_name 列。

　　2.对索引列什么都不做

　　不要对索引列进行任何计算、函数、自动或手动类型转换，否则将执行全表扫描。简而言之，不要对索引列做任何事情。

　　3.两侧是不同的类型

　　例如建立索引idx_user_name，name字段类型为varchar

　　查询时使用where name = kaka，这种查询方式会直接导致索引失败。

　　正确的用法是 name = "kaka"。

　　4.不恰当的like查询会导致索引失效

　　创建索引为 idx_user_name

　　执行语句为 select * from user where name like "kaka%"; 可以命中索引。

　　执行语句是 select name from user where name like "%kaka"; 可以使用索引（仅在 8.0 以上的版本中）。

　　执行语句为 select * from user where name like ''%kaka"; 会直接导致索引失败

　　5.范围条件后的索引将无效

　　创建索引为 idx_user_name_age_sex

　　执行语句 select * from user where name = 'kaka' and age > 11 and sex = 1;

　　上面的sql语句只会命中name和age索引，sex索引无效。

　　如果复合索引失败，需要检查key_len的长度。

　　总结： % 稍后会命令索引，使用覆盖索引时任何查询方法都可以命中索引。

　　以上就是索引失败的原因总结。在很多文章中，没有标注MySQL版本，所以你可能会看到索引为空或者索引失败的结论。

　　三、解释SQL优化的杀手锏

　　写完 SQL 语句后必须做的一件事是使用 Explain 来检查 SQL 语句，看是否命中了索引。

　　下图是explain输出格式的使用，接下来对输出格式进行简单说明。

　　

　　1.*敏*感*词*

　　此列是查询编号。如果查询语句中没有子查询或联合查询，则标识符始终为 1。

　　如果有子查询或联合查询，数字会自动递增。

　　2.select_type

　　最常见的类型是 SIMPLE 和 PRIMARY，只要知道这一列。

　　3.表

　　可以理解为一个表名

　　4.**类型

　　该列是优化 SQL 语句时需要注意的最重要的列之一，该列显示了查询使用的类型。

　　以下是从最好到最差的顺序。

　　5.可能的键

　　此列显示可能使用的索引

　　6.**键

　　优化器从 possible_keys 命中的索引

　　7.key_len

　　查询中使用的索引长度（字节数），key_len只计算where条件中使用的索引长度，即使索引用于排序和分组，也不会计算到key_len中。

　　8.参考

　　如果使用常量等效查询，这里会显示 const。

　　如果是join查询，则驱动表的执行计划会显示驱动表的关联字段。

　　如果条件使用表达式或函数，或者条件列有内部隐式转换，这里可能会显示为 func。

　　9.**行

　　这是 MySQL 对要扫描的行数的估计（不是精确值）。

　　这个值非常直观的展示了 SQL 的效率。原则上，行数越少越好。

　　10.过滤

　　该列表示存储引擎返回的数据在服务器层过滤后满足查询的记录数所占的比例。请注意，它是一个百分比，而不是特定记录的数量。

　　11.**额外

　　大多数情况下会出现以下情况。

　　12.总结

　　以上是Explain的所有栏目的说明。在通常的开发过程中，我们一般只关注type、key、rows、extra这四列。

　　四、SQL优化杀手慢查询

　　如上所述，您可以直接使用explain来分析您的SQL语句是否合理。接下来说一下慢查询。

　　检查慢查询是否开启

　　

　　检查是否记录了不使用索引的 SQL 语句

　　

　　启用慢查询并启用不使用索引的 SQL 语句的日志记录

　　设置全局 log_queries_not_using_idnexs='on';

　　设置全局 log_queries_not_using_indexes='on';

　　

　　检查以上两个配置是否开启

　　

　　设置慢查询时间，这个时间自己控制，一般1s可以设置globle long_query_time=1；

　　如果您检查时间没有改变，您可以重新连接客户端。

　　

　　查看慢查询存储位置

　　

　　然后只需执行一条不执行索引的语句即可查看此日志中的语句

　　

　　上图中，主要观察的是Query_time和SQL语句的内容。