jjzjj

Explain进行索引分析和优化

鸭血粉丝Tang 2023-03-28 原文
阿粉昨天读者朋友们说了关于MySQL的索引的类型还有索引的实现原理，今天，阿粉就来给大家说说这个索引分析和索引优化是应该怎么做的。

查看命中索引

MySQL 里面我们都知道一个指令， EXPLAIN，这个指令我们都知道，它可以对 SELECT 语句进行分析，并输出 SELECT 执行的详细信息，供开发人员有针对性的优化。

比如我们的 SQL 是这样的：

EXPLAIN SELECT * from user WHERE id < 10;

就会吧所有的信息都给我们列出来，我们来分析一下这个每个字段内容的含义：

第一个参数：

select_type

表示查询的类型。常用的值如下：

  • SIMPLE ： 表示查询语句不包含子查询或union
  • PRIMARY：表示此查询是最外层的查询
  • UNION：表示此查询是UNION的第二个或后续的查询
  • DEPENDENT UNION：UNION中的第二个或后续的查询语句，使用了外面查询结果
  • UNION RESULT：UNION的结果
  • SUBQUERY：SELECT子查询语句
  • DEPENDENT SUBQUERY：SELECT子查询语句依赖外层查询的结果。
最常见的查询类型是SIMPLE，表示我们的查询没有子查询也没用到UNION查询。

type

表示存储引擎查询数据时采用的方式。比较重要的一个属性，通过它可以判断出查询是全表扫描还 是基于索引的部分扫描。常用属性值如下，从上至下效率依次增强。

type 参数也有很多的值，每个值的不同代表这不同的含义；

  • ALL：表示全表扫描，性能最差。
  • index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。
  • range：表示使用索引范围查询。使用>、>=、<、<=、in等等。
  • ref：表示使用非唯一索引进行单值查询。
  • eq_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一 行结果。
  • const：表示使用主键或唯一索引做等值查询，常量查询。
  • NULL：表示不用访问表，速度最快。
possible_keys

表示查询时能够使用到的索引。注意并不一定会真正使用，显示的是索引名称。

注意这个只是可能用到的索引，而不是实际上用到的索引。

key

表示查询时真正使用到的索引，显示的是索引名称。

rows

MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录。原则上rows是 越少效率越高，可以直观的了解到SQL效率高低。

key_len

表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引。

但是这个时候，我们知道了索引使用的字节数量的时候，我们就得看看他是怎么出来的这个数据

key_len的计算规则如下：

  • 字符串类型
字符串长度跟字符集有关：latin1=1、gbk=2、utf8=3、utf8mb4=4

char(n)：n*字符集长度

varchar(n)：n * 字符集长度 + 2字节

  • 数值类型
TINYINT：1个字节

SMALLINT：2个字节

MEDIUMINT：3个字节

INT、FLOAT：4个字节

BIGINT、DOUBLE：8个字节

  • 时间类型
DATE：3个字节

TIMESTAMP：4个字节

DATETIME：8个字节

  • 字段属性
NULL属性占用1个字节，如果一个字段设置了NOT NULL，则没有此项。

Extra

Extra表示很多额外的信息，各种操作会在Extra提示相关信息

比如你会在其中看到Using where,Using index,Using filesort,Using temprorary,

Using where表示查询需要通过索引回表查询数据。

Using index表示查询需要通过索引，索引就可以满足所需数据。

Using filesort表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有Using filesort 建议优化。

Using temprorary

查询使用到了临时表，一般出现于去重、分组等操作。

既然我们都知道了这个 关键字，那么肯定得想办法处理我们的 SQL 然后达到我们想要优化的目的。

那么查询优化需要我们做到哪些呢？

我们可以通过查看慢查询日志 来定位我们的慢业务问题。

我们应该怎么开启慢查询日志和怎么取查看慢查询的日志呢？阿粉在这里给大家讲述一波：

开启慢查询日志：

SET global slow_query_log = ON;
SET global slow_query_log_file = 'OAK-slow.log';
SET global log_queries_not_using_indexes = ON;
SET long_query_time = 10;
  • long_query_time 指定慢查询的阀值，单位秒。如果SQL执行时间超过阀值，就属于慢查询 记录到日志文件中。
  • log_queries_not_using_indexes
表示会记录没有使用索引的查询SQL。前提是slow_query_log的值为ON，否则不会奏效。

查看慢查询日志:

使用mysqldumpslow查看:

这个可是非常经典的一个慢查询日志分析工具，可以通过该工具分析慢查询日志内容。

工具其实还有很多，并不限制只有这一种，还有pt-query-digest、mysqlsla等，这些都是可以定位慢查询日志的小工具。

至于如何来进项慢查询的优化呢？阿粉讲在下个文章来科普一下，毕竟今天已经我们吧分析索引的方法讲给了大家，大家也需要消化一下，不是么？

Explain进行data-iddatali数据库其他数据库$索引优化慢查询

有关Explain进行索引分析和优化的更多相关文章

  1. ruby-on-rails - 使用 Ruby on Rails 进行自动化测试 - 最佳实践 - 2

    很好奇，就使用ruby​​onrails自动化单元测试而言，你们正在做什么？您是否创建了一个脚本来在cron中运行rake作业并将结果邮寄给您？git中的预提交Hook？只是手动调用？我完全理解测试，但想知道在错误发生之前捕获错误的最佳实践是什么。让我们理所当然地认为测试本身是完美无缺的，并且可以正常工作。下一步是什么以确保他们在正确的时间将可能有害的结果传达给您？ 最佳答案 不确定您到底想听什么，但是有几个级别的自动代码库控制:在处理某项功能时，您可以使用类似autotest的内容获得关于哪些有效，哪些无效的即时反馈。要确保您的提

  2. ruby-on-rails - 按天对 Mongoid 对象进行分组 - 2

    在控制台中反复尝试之后，我想到了这种方法，可以按发生日期对类似activerecord的(Mongoid)对象进行分组。我不确定这是完成此任务的最佳方法，但它确实有效。有没有人有更好的建议，或者这是一个很好的方法？#eventsisanarrayofactiverecord-likeobjectsthatincludeatimeattributeevents.map{|event|#converteventsarrayintoanarrayofhasheswiththedayofthemonthandtheevent{:number=>event.time.day,:event=>ev

  3. ruby - 使用 C 扩展开发 ruby​​gem 时，如何使用 Rspec 在本地进行测试？ - 2

    我正在编写一个包含C扩展的gem。通常当我写一个gem时，我会遵循TDD的过程，我会写一个失败的规范，然后处理代码直到它通过，等等......在“ext/mygem/mygem.c”中我的C扩展和在gemspec的“扩展”中配置的有效extconf.rb，如何运行我的规范并仍然加载我的C扩展？当我更改C代码时，我需要采取哪些步骤来重新编译代码？这可能是个愚蠢的问题，但是从我的gem的开发源代码树中输入“bundleinstall”不会构建任何native扩展。当我手动运行rubyext/mygem/extconf.rb时，我确实得到了一个Makefile(在整个项目的根目录中)，然后当

  4. ruby - 如何进行排列以有效地定制输出 - 2

    这是一道面试题，我没有答对，但还是很好奇怎么解。你有N个人的大家庭，分别是1,2,3,...,N岁。你想给你的大家庭拍张照片。所有的家庭成员都排成一排。“我是家里的friend，建议家庭成员安排如下:”1岁的家庭成员坐在这一排的最左边。每两个坐在一起的家庭成员的年龄相差不得超过2岁。输入:整数N，1≤N≤55。输出:摄影师可以拍摄的照片数量。示例->输入:4，输出:4符合条件的数组:[1,2,3,4][1,2,4,3][1,3,2,4][1,3,4,2]另一个例子:输入:5输出:6符合条件的数组:[1,2,3,4,5][1,2,3,5,4][1,2,4,3,5][1,2,4,5,3][

  5. ruby - 即使失败也继续进行多主机测试 - 2

    我已经构建了一些serverspec代码来在多个主机上运行一组测试。问题是当任何测试失败时，测试会在当前主机停止。即使测试失败，我也希望它继续在所有主机上运行。Rakefile:namespace:specdotask:all=>hosts.map{|h|'spec:'+h.split('.')[0]}hosts.eachdo|host|begindesc"Runserverspecto#{host}"RSpec::Core::RakeTask.new(host)do|t|ENV['TARGET_HOST']=hostt.pattern="spec/cfengine3/*_spec.r

  6. ruby - 是否可以覆盖 gemfile 进行本地开发？ - 2

    我们的git存储库中目前有一个Gemfile。但是，有一个gem我只在我的环境中本地使用(我的团队不使用它)。为了使用它，我必须将它添加到我们的Gemfile中，但每次我checkout到我们的master/dev主分支时，由于与跟踪的gemfile冲突，我必须删除它。我想要的是类似Gemfile.local的东西，它将继承从Gemfile导入的gems，但也允许在那里导入新的gems以供使用只有我的机器。此文件将在.gitignore中被忽略。这可能吗？ 最佳答案 设置BUNDLE_GEMFILE环境变量:BUNDLE_GEMFI

  7. ruby - 在 Windows 机器上使用 Ruby 进行开发是否会适得其反？ - 2

    这似乎非常适得其反，因为太多的gem会在window上破裂。我一直在处理很多mysql和ruby​​-mysqlgem问题(gem本身发生段错误，一个名为UnixSocket的类显然在Windows机器上不能正常工作，等等)。我只是在浪费时间吗？我应该转向不同的脚本语言吗？ 最佳答案 我在Windows上使用Ruby的经验很少，但是当我开始使用Ruby时，我是在Windows上，我的总体印象是它不是Windows原生系统。因此，在主要使用Windows多年之后，开始使用Ruby促使我切换回原来的系统Unix，这次是Linux。Rub

  8. ruby-on-rails - 协会的 Rails 索引 - 2

    我发现自己需要这个。假设cart是一个包含用户列表的模型。defindex_of_itemcart.users.each_with_indexdo|u,i|ifu==current_userreturniendend获取此类关联索引的更简单方法是什么？ 最佳答案 indexArray上的方法与您的index_of_item方法相同，例如cart.users.index(current_user)返回数组中第一个对象的索引==给obj。如果未找到匹配项，则返回nil。 关于ruby-on-

  9. ruby - Rails -- :id attribute? 所需的数据库索引 - 2

    因此，当我遵循MichaelHartl的RubyonRails教程时，我注意到在用户表中，我们为:email属性添加了一个唯一索引，以提高find的效率方法，因此它不会逐行搜索。到目前为止，我们一直在根据情况使用find_by_email和find_by_id进行搜索。然而，我们从未为:id属性设置索引。:id是否自动索引，因为它在默认情况下是唯一的并且本质上是顺序的？或者情况并非如此，我应该为:id搜索添加索引吗？ 最佳答案 大多数数据库(包括sqlite，这是RoR中的默认数据库)会自动索引主键，对于RailsMigration

  10. ruby - 捕获 Ruby Logger 输出以进行测试 - 2

    我有一个像这样的ruby​​类:require'logger'classTdefdo_somethinglog=Logger.new(STDERR)log.info("Hereisaninfomessage")endend测试脚本行如下:#!/usr/bin/envrubygem"minitest"require'minitest/autorun'require_relative't'classTestMailProcessorClasses当我运行这个测试时，out和err都是空字符串。我看到消息打印在stderr上(在终端上)。有没有办法让Logger和capture_io一起玩得

随机推荐