【.com原稿】查询优化不是一蹴而就的，需要学会使用相应的工具，借鉴别人的经验来优化SQL，以及提高自己。图片来自Pexels。首先，我们巩固索引的优势，比如数据检索速度快、查询稳定、顺序存储、避免服务器创建临时表、将随机I/O变成有序I/O等。但是，一旦索引的创建不规律，就会造成如下问题，占用额外的空间，浪费内存，降低增删改查数据的性能。因此，只有在了解索引数据结构的基础上，才能创建高效的索引。本文所有操作均在MySQL8.0.12上进行。索引创建规范在学习索引优化之前，需要对索引创建规范有一定的了解。本规范来源于阿里巴巴开发手册。主键索引：pk_column_column。唯一索引：uk_column_column。常用索引：idx_column_column。索引失败的原因要创建索引，需要知道索引在什么情况下会失败。只有了解索引失败的原因，才能在创建索引时避免一些已知的错误。这个游戏中的经典说法是创建索引时leader不能死，必须遵守最左原则。例如表结构为u_id、u_name、u_age、u_sex、u_phone、u_time，创建的索引为idx_user_name_age_sex。查询条件必须包含u_name列。不要对索引列进行任何操作不要对索引列进行任何计算、函数、自动或手动类型转换，否则将进行全表扫描。简而言之，不要对索引列进行任何操作。两侧的类型不相等。例如建立索引idx_user_name，name字段的类型为varchar。查询时使用wherename=kaka，这种查询方式会直接导致索引失效。正确的用法是wherename="kaka"。不合适的like查询会导致索引失效。创建索引为idx_user_name并执行语句select*fromuserwherenamelike“kaka%”;索引可以被击中。执行语句是selectnamefromuserwherenamelike“%kaka”；可以使用该索引（仅适用于8.0以上版本）。执行语句为select*fromuserwherenamelike''%kaka";会直接导致索引失败，范围条件后的索引会失效创建索引为：idx_user_name_age_sex执行语句：select*fromuserwherename='kaka'andage>11andsex=1;上面的SQL语句只会命中name和age索引，sex索引会失效，需要检查key_len的长度才能使复合索引失效总结：%后面会命令索引，并且使用覆盖索引时，任何查询方式都可以命中上面是我总结的索引失败的原因，很多文章中没有标出MySQL版本，所以你可能看到的结论是nullorindex会失败Explain，SQL优化的杀手级特性，写完SQL语句之后必须做的一件事就是用Explain进行SQL语句检测，看是否命中索引，上图是使用Explain的输出格式，和T下面简单说明一下输出格式：①id：该列为查询的编号，如果查询语句中没有子查询或联合查询，则标识符始终为1。如果有子查询或联合查询查询，数字将递增。②select_type：最常见的类型是SIMPLE和PRIMARY，知道这一栏就知道了。③table：理解就用表名。④**type：该列是优化SQL语句时最重要的列之一。此列显示使用的查询类型。下面从好到坏排序：system:table只有一行数据。const：最多会有一条记录匹配，常用于主键或唯一索引作为条件查询。eq_ref：当连接使用的索引是主键且唯一时会出现。ref：使用普通索引=或<=>运算符比较会出现。fulltext：使用全文索引。ref_or_null：与ref类型类似，只是增加了null值的判断，实际中用的不多。语句是wherename='kaka'andnameisnull,nameisOrdinaryindex。index_merge：查询语句使用了两个以上的索引，在使用and或or时经常出现。官方文档把这个类型放在ref_or_null之后，但是很多时候可能因为读取索引太多，性能不如range。unique_subquery：用于where中的in查询，完全替代子查询，效率更高。该语句为valueIN(SELECTprimary_keyFROMsingle_tableWHEREsome_expr)index_subquery：子查询中返回的结果字段组合是索引（或索引组合），但不是主键或唯一索引。range：索引范围查询，常用于使用=、<>、>、>=、<、<=、ISNULL、<=>、BETWEEN、IN()等操作符的查询。index：索引全表扫描，从头到尾扫描索引。all：全表扫描，性能最差。⑤possible_keys：这一列显示可能使用的索引。⑥**key：优化器从possible_keys中命中的索引。⑦key_len：查询中使用的索引长度（字节数），key_len只计算where条件使用的索引长度，即使索引用于排序分组也不会计算到key_len中。⑧ref：如果是常量等价查询，这里会显示const。如果是连接查询，从动表的执行计划会显示从动表的关联字段。如果条件使用表达式或函数，或者条件列有内部隐式转换，这里可能显示为func。⑨**rows：这是MySQL估计要扫描的行数（不是精确值）。这个值很直观的体现了SQL的效率。原则上行数越少越好。⑩filtered：该列表示存储引擎返回的数据经过server层过滤后，剩下满足查询的记录数的比例。请注意，它是一个百分比，而不是特定的记录数。?**extra：在大多数情况下，会出现以下几种情况。使用索引：使用覆盖索引，查询列都是索引字段。使用where：使用where语句。使用临时表：在查询结果排序时使用临时表。使用文件排序：使用外部索引对数据进行排序。使用索引条件：使用索引下推。指数下推可以参考我之前的文章《MySQL 索引》。总结：以上是对Explain所有列的说明。在平时的开发过程中，一般只关注type、key、rows、extra这四列。类型优化目标至少要达到range级别，要求是ref级别。能用const最好了。key是查询使用的索引，如果此列为空，则没有创建索引，或者索引无效。rows是这条SQL语句扫描的行数，越少越好。extra：该列为扩展列。如果有临时表或文件排序，需要优化。SQL优化的杀手锏是慢查询。上面说了，你可以直接用Explain来分析你的SQL语句是否合理。接下来说说慢查询。查看是否开启慢查询：查看是否记录了不使用索引的SQL语句：开启慢查询，开启记录不使用索引的SQL语句：setgloballog_queries_not_using_idnexes='on';setgloballog_queries_not_using_indexes='on';查询以上两个配置是否启用：设置慢查询时间，这个时间自己控制，一般1s就够了：setgloblelong_query_time=1;如果您检查时间没有改变，只需重新连接客户端一次。查看慢查询的存储位置：然后就执行一条不执行索引的语句查看这个日志中的语句：一般上图中主要观察的是Query_time和SQL语句的内容。以上就是如何使用慢查询查看项目中有问题的SQL语句。优化大法这里我就说说一些常用的SQL语句优化方案。上面两个工具要用好，可以帮助我们打怪：①禁止使用select*，查询什么字段需要什么字段。②where字段设置索引。③Groupby、orderby字段集合索引。④放弃偏移和限制分页，使用延迟关联实现分页（数据量不大时不需要）。⑤写分页时，当count为0时，直接返回，避免执行分页语句。⑥使用覆盖索引查询，避免返表。⑦构建综合指标时，将判别度最高的放在最左边。⑧统计数据的行数只用count(*)，不要花哨。⑨关于in和exist，如果查询的两个表大小相同，性能差异可以忽略。如果子查询表很大，就用exist，否则就用in。⑩查询一行数据时加limit1。?选择合理的数据类型，满足条件的数据类型越小越好。?联合查询join最多三张表，join的字段数据类型需要保持一致。?尽量避免in操作，如果不可避免，in元素个数控制在1000以内。?数据更新频繁，鉴别力低的列不适合做索引。?explain中的type至少要达到范围，要求是ref。?联合索引满足最左原则。作者：卡卡编辑：陶嘉龙投稿：如有意投稿或求报道，请加编辑微信gordonlonglong【原创稿件，合作站转载请注明原作者和出处.com】
