MySQL—准确运用索引、limit分页、施行安插、慢日志查询

by admin on 2020年1月3日

正确使用索引

数据库表中添加索引后确实会让查询速度起飞,但前提必须是正确的使用索引来查询,如果以错误的方式使用,则即使建立索引也会不奏效。
即使建立索引,索引也不会生效:

 1 - like '%xx'
 2     select * from tb1 where name like '%cn';
 3 - 使用函数
 4     select * from tb1 where reverse(name) = 'wupeiqi';
 5 - or
 6     select * from tb1 where nid = 1 or email = 'seven@live.com';
 7     特别的:当or条件中有未建立索引的列才失效,以下会走索引
 8             select * from tb1 where nid = 1 or name = 'seven';
 9             select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex'
10 - 类型不一致
11     如果列是字符串类型,传入条件是必须用引号引起来,不然...
12     select * from tb1 where name = 999;
13 - !=
14     select * from tb1 where name != 'alex'
15     特别的:如果是主键,则还是会走索引
16         select * from tb1 where nid != 123
17 - >
18     select * from tb1 where name > 'alex'
19     特别的:如果是主键或索引是整数类型,则还是会走索引
20         select * from tb1 where nid > 123
21         select * from tb1 where num > 123
22 - order by
23     select email from tb1 order by name desc;
24     当根据索引排序时候,选择的映射如果不是索引,则不走索引
25     特别的:如果对主键排序,则还是走索引:
26         select * from tb1 order by nid desc;
27  
28 - 组合索引最左前缀
29     如果组合索引为:(name,email)
30     name and email       -- 使用索引
31     name                 -- 使用索引
32     email                -- 不使用索引

MySQL查询索引的正确使用

索引是提高查询速度的最重要的工具。当然还有其它的一些技术可供使用,但是一般来说引起最大性能差异的都是索引的正确使用。在MySQL邮件列表中,人们经常询问那些让查询运行得更快的方法。在大多数情况下,我们应该怀疑数据表上有没有索引,并且通常在添加索引之后立即解决了问题。当然,并不总是这样简单就可以解决问题的,因为优化技术本来就并非总是简单的。然而,如果没有使用索引,在很多情况下,你试图使用其它的方法来提高性能都是在浪费时间。首先使用索引来获取最大的性能提高,接着再看其它的技术是否有用。

  这一部分讲述了索引是什么以及索引是怎么样提高查询性能的。它还讨论了在某些环境中索引可能降低性能,并为你明智地选择数据表的索引提供了一些指导方针。在下一部分中我们将讨论MySQL查询优化器,它试图找到执行查询的效率最高的方法。了解一些优化器的知识,作为对如何建立索引的补充,对我们是有好处的,因为这样你才能更好地利用自己所建立的索引。某些编写查询的方法实际上让索引不起作用,在一般情况下你应该避免这种情形的发生。

  索引的优点

  让我们开始了解索引是如何工作的,首先有一个不带索引的数据表。不带索引的表仅仅是一个无序的数据行集合。例如,图1显示的ad表就是不带索引的表,因此如果需要查找某个特定的公司,就必须检查表中的每个数据行看它是否与目标值相匹配。这会导致一次完全的数据表扫描,这个过程会很慢,如果这个表很大,但是只包含少量的符合条件的记录,那么效率会非常低。


图1:无索引的ad表

  图2是同样的一张数据表,但是增加了对ad表的company_num数据列的索引。这个索引包含了ad表中的每个数据行的条目,但是索引的条目是按照company_num值排序的。现在,我们不是逐行查看以搜寻匹配的数据项,而是使用索引。假设我们查找公司13的所有数据行。我们开始扫描索引并找到了该公司的三个值。接着我们碰到了公司14的索引值,它比我们正在搜寻的值大。索引值是排过序的,因此当我们读取了包含14的索引记录的时候,我们就知道再也不会有更多的匹配记录,可以结束查询操作了。因此使用索引获得的功效是:我们找到了匹配的数据行在哪儿终止,并能够忽略其它的数据行。另一个功效来自使用定位算法查找第一条匹配的条目,而不需要从索引头开始执行线性扫描(例如,二分搜索就比线性扫描要快一些)。通过使用这种方法,我们可以快速地定位第一个匹配的值,节省了大量的搜索时间。数据库使用了多种技术来快速地定位索引值,但是在本文中我们不关心这些技术。重点是它们能够实现,并且索引是个好东西。


图2:索引后的ad表< 喎?http://www.Bkjia.com/kf/ware/vc/" target="_blank" class="keylink">vcD4KPC90ZD4KPC90cj4KPC90Ym9keT4KPC90YWJsZT4KPC9jZW50ZXI+CjxwIGNsYXNzPQ=="western" align="left">
  你可能要问,我们为什么不对数据行进行排序从而省掉索引?这样不是也能实现同样的搜索速度的改善吗?是的,如果表只有一个索引,这样做也可能达到相同的效果。但是你可能添加第二个索引,那么就无法一次使用两种不同方法对数据行进行排序了(例如,你可能希望在顾客名称上建立一个索引,在顾客ID号或电话号码上建立另外一个索引)。把与数据行相分离的条目作为索引解决了这个问题,允许我们创建多个索引。此外,索引中的行一般也比数据行短一些。当你插入或删除新的值的时候,移动较短的索引值比移动较长数据行的排序次序更加容易。

  不同的MySQL存储引擎的索引实现的具体细节信息是不同的。例如,对于MyISAM数据表,该表的数据行保存在一个数据文件中,索引值保存在索引文件中。一个数据表上可能有多个索引,但是它们都被存储在同一个索引文件中。索引文件中的每个索引都包含一个排序的键记录(它用于快速地访问数据文件)数组。

  与此形成对照的是,BDB和InnoDB存储引擎没有使用这种方法来分离数据行和索引值,尽管它们也把索引作为排序后的值集合进行操作。在默认情况下,BDB引擎使用单个文件存储数据和索引值。InnoDB使用单个数据表空间(tablespace),在表空间中管理所有InnoDB表的数据和索引存储。我们可以把InnoDB配置为每个表都在自己的表空间中创建,但是即使是这样,数据表的数据和索引也存储在同一个表空间文件中。
前面的讨论描述了单个表查询环境下的索引的优点,在这种情况下,通过减少对整个表的扫描,使用索引明显地提高了搜索的速度。当你运行涉及多表联结(jion)查询的时候,索引的价值就更高了。在单表查询中,你需要在每个数据列上检查的值的数量是表中数据行的数量。在多表查询中,这个数量可能大幅度上升,因为这个数量是这些表中数据行的数量所产生的。

  假设你拥有三个未索引的表t1、t2和t3,每个表都分别包含数据列i1、i2和i3,并且每个表都包含了1000条数据行,其序号从1到1000。查找某些值匹配的数据行组合的查询可能如下所示:

SELECTt1.i1, t2.i2, t3.i3
FROM t1, t2, t3
WHERE t1.i1 = t2.i2 ANDt2.i1 = t3.i3;

  这个查询的结果应该是1000行,每个数据行包含三个相等的值。如果在没有索引的情况下处理这个查询,那么如果我们不对这些表进行全部地扫描,我们是没有办法知道哪些数据行含有哪些值的。因此你必须尝试所有的组合来查找符合WHERE条件的记录。可能的组合的数量是1000x 1000 x1000(10亿!),它是匹配记录的数量的一百万倍。这就浪费了大量的工作。这个例子显示,如果没有使用索引,随着表的记录不断增长,处理这些表的联结所花费的时间增长得更快,导致性能很差。我们可以通过索引这些数据表来显著地提高速度,因为索引让查询采用如下所示的方式来处理:

  1.选择表t1中的第一行并查看该数据行的值。

  2.使用表t2上的索引,直接定位到与t1的值匹配的数据行。类似地,使用表t3上的索引,直接定位到与表t2的值匹配的数据行。

  3.处理表t1的下一行并重复前面的过程。执行这样的操作直到t1中的所有数据行都被检查过。

  在这种情况下,我们仍然对表t1执行了完整的扫描,但是我们可以在t2和t3上执行索引查找,从这些表中直接地获取数据行。理论上采用这种方式运行上面的查询会快一百万倍。当然这个例子是为了得出结论来人为建立的。然而,它解决的问题却是现实的,给没有索引的表添加索引通常会获得惊人的性能提高。

  MySQL有几种使用索引的方式:

  ·如上所述,索引被用于提高WHERE条件的数据行匹配或者执行联结操作时匹配其它表的数据行的搜索速度。

  ·对于使用了MIN()或MAX()函数的查询,索引数据列中最小或最大值可以很快地找到,不用检查每个数据行。

  ·MySQL利用索引来快速地执行ORDERBY和GROUPBY语句的排序和分组操作。

  ·有时候MySQL会利用索引来读取查询得到的所有信息。假设你选择了MyISAM表中的被索引的数值列,那么就不需要从该数据表中选择其它的数据列。在这种情况下,MySQL从索引文件中读取索引值,它所得到的值与读取数据文件得到的值是相同的。没有必要两次读取相同的值,因此没有必要考虑数据文件。

 索引的代价

  一般来说,如果MySQL能够找到方法,利用索引来更快地处理查询,它就会这样做。这意味着,对于大多数情况,如果你没有对表进行索引,就会使性能受到损害。这就是我所描绘的索引优点的美景。但是它有缺点吗?有的,它在时间和空间上都有开销。在实践中,索引的优点的价值一般会超过这些缺点,但是你也应该知道到底有一些什么缺点。

  首先,索引加快了检索的速度,但是减慢了插入和删除的速度,同时还减慢了更新被索引的数据列中的值的速度。也就是说,索引减慢了大多数涉及写操作的速度。发生这种现象的原因在于写入一条记录的时候不但需要写入数据行,还需要改变所有的索引。数据表带有的索引越多,需要做出的修改就越多,平均性能的降低程度也就越大。在本文的"高效率载入数据"部分中,我们将更细致地了解这些现象并找出处理方法。

  其次,索引会花费磁盘空间,多个索引相应地花费更多的磁盘空间。这可能导致更快地到达数据表的大小限制:

  ·对于MyISAM表,频繁地索引可能引起索引文件比数据文件更快地达到最大限制。

  ·对于BDB表,它把数据和索引值一起存储在同一个文件中,添加索引引起这种表更快地达到最大文件限制。

  ·在InnoDB的共享表空间中分配的所有表都竞争使用相同的公共空间池,因此添加索引会更快地耗尽表空间中的存储。但是,与MyISAM和BDB表使用的文件不同,InnoDB共享表空间并不受操作系统的文件大小限制,因为我们可以把它配置成使用多个文件。只要有额外的磁盘空间,你就可以通过添加新组件来扩展表空间。

  使用单独表空间的InnoDB表与BDB表受到的约束是一样的,因为它的数据和索引值都存储在单个文件中。

  这些要素的实际含义是:如果你不需要使用特殊的索引帮助查询执行得更快,就不要建立索引。

  选择索引

  假设你已经知道了建立索引的语法,但是语法不会告诉你数据表应该如何索引。这要求我们考虑数据表的使用方式。这一部分指导你如何识别出用于索引的备选数据列,以及如何最好地建立索引:

  用于搜索、排序和分组的索引数据列并不仅仅是用于输出显示的。换句话说,用于索引的最好的备选数据列是那些出现在WHERE子句、join子句、ORDERBY或GROUPBY子句中的列。仅仅出现在SELECT关键字后面的输出数据列列表中的数据列不是很好的备选列:

SELECT
col_a<- 不是备选列
FROM
tbl1LEFT JOIN tbl2
ON tbl1.col_b = tbl2.col_c <-备选列
WHERE
col_d= expr; <- 备选列

  当然,显示的数据列与WHERE子句中使用的数据列也可能相同。我们的观点是输出列表中的数据列本质上不是用于索引的很好的备选列。

  Join子句或WHERE子句中类似col1=col2形式的表达式中的数据列都是特别好的索引备选列。前面显示的查询中的col_b和col_c就是这样的例子。如果MySQL能够利用联结列来优化查询,它一定会通过减少整表扫描来大幅度减少潜在的表-行组合。

  考虑数据列的基数(cardinality)。基数是数据列所包含的不同值的数量。例如,某个数据列包含值1、3、7、4、7、3,那么它的基数就是4。索引的基数相对于数据表行数较高(也就是说,列中包含很多不同的值,重复的值很少)的时候,它的工作效果最好。如果某数据列含有很多不同的年龄,索引会很快地分辨数据行。如果某个数据列用于记录性别(只有"M"和"F"两种值),那么索引的用处就不大。如果值出现的几率几乎相等,那么无论搜索哪个值都可能得到一半的数据行。在这些情况下,最好根本不要使用索引,因为查询优化器发现某个值出现在表的数据行中的百分比很高的时候,它一般会忽略索引,进行全表扫描。惯用的百分比界线是"30%"。现在查询优化器更加复杂,把其它一些因素也考虑进去了,因此这个百分比并不是MySQL决定选择使用扫描还是索引的唯一因素。

  索引较短的值。尽可能地使用较小的数据类型。例如,如果MEDIUMINT足够保存你需要存储的值,就不要使用BIGINT数据列。如果你的值不会长于25个字符,就不要使用CHAR(100)。较小的值通过几个方面改善了索引的处理速度:

  ·较短的值可以更快地进行比较,因此索引的查找速度更快了。

  ·较小的值导致较小的索引,需要更少的磁盘I/O。

  ·使用较短的键值的时候,键缓存中的索引块(block)可以保存更多的键值。MySQL可以在内存中一次保持更多的键,在不需要从磁盘读取额外的索引块的情况下,提高键值定位的可能性。

  对于InnoDB和BDB等使用聚簇索引(clusteredindex)的存储引擎来说,保持主键(primarykey)短小的优势更突出。聚簇索引中数据行和主键值存储在一起(聚簇在一起)。其它的索引都是次级索引;它们存储主键值和次级索引值。次级索引屈从主键值,它们被用于定位数据行。这暗示主键值都被复制到每个次级索引中,因此如果主键值很长,每个次级索引就需要更多的额外空间。

  索引字符串值的前缀(prefixe)。如果你需要索引一个字符串数据列,那么最好在任何适当的情况下都应该指定前缀长度。例如,如果有CHAR(200)数据列,如果前面10个或20个字符都不同,就不要索引整个数据列。索引前面10个或20个字符会节省大量的空间,并且可能使你的查询速度更快。通过索引较短的值,你可以获得那些与比较速度和磁盘I/O节省相关的好处。当然你也需要利用常识。仅仅索引某个数据列的第一个字符串可能用处不大,因为如果这样操作,那么在索引中不会有太多的唯一值。

  你可以索引CHAR、VARCHAR、BINARY、VARBINARY、BLOB和TEXT数据列的前缀。

  使用最左(leftmost)前缀。建立多列复合索引的时候,你实际上建立了MySQL可以使用的多个索引。复合索引可以作为多个索引使用,因为索引中最左边的列集合都可以用于匹配数据行。这种列集合被称为"最左前缀"(它与索引某个列的前缀不同,那种索引把某个列的前面几个字符作为索引值)。

  假设你在表的state、city和zip数据列上建立了复合索引。索引中的数据行按照state/city/zip次序排列,因此它们也会自动地按照state/city和state次序排列。这意味着,即使你在查询中只指定了state值,或者指定state和city值,MySQL也可以使用这个索引。因此,这个索引可以被用于搜索如下所示的数据列组合:

state,city, zip
state, city
state

  MySQL不能利用这个索引来搜索没有包含在最左前缀的内容。例如,如果你按照city或zip来搜索,就不会使用到这个索引。如果你搜索给定的state和具体的ZIP代码(索引的1和3列),该索引也是不能用于这种组合值的,尽管MySQL可以利用索引来查找匹配的state从而缩小搜索的范围。

  不要过多地索引。不要认为"索引越多,性能越高",不要对每个数据列都进行索引。我们在前面提到过,每个额外的索引都会花费更多的磁盘空间,并降低写操作的性能。当你修改表的内容的时候,索引就必须被更新,甚至可能重新整理。如果你的索引很少使用或永不使用,你就没有必要减小表的修改操作的速度。此外,为检索操作生成执行计划的时候,MySQL会考虑索引。建立额外的索引会给查询优化器增加更多的工作量。如果索引太多,有可能(未必)出现MySQL选择最优索引失败的情况。维护自己必须的索引可以帮助查询优化器来避免这类错误。

  如果你考虑给已经索引过的表添加索引,那么就要考虑你将增加的索引是否是已有的多列索引的最左前缀。如果是这样的,不用增加索引,因为已经有了(例如,如果你在state、city和zip上建立了索引,那么没有必要再增加state的索引)。

  让索引类型与你所执行的比较的类型相匹配。在你建立索引的时候,大多数存储引擎会选择它们将使用的索引实现。例如,InnoDB通常使用B树索引。MySQL也使用B树索引,它只在三维数据类型上使用R树索引。但是,MEMORY存储引擎支持散列索引和B树索引,并允许你选择使用哪种索引。为了选择索引类型,需要考虑在索引数据列上将执行的比较操作类型:

  ·对于散列(hash)索引,会在每个数据列值上应用散列函数。生成的结果散列值存储在索引中,并用于执行查询。散列函数实现的算法类似于为不同的输入值生成不同的散列值。使用散列值的好处是散列值比原始值的比较效率更高。散列索引用于执行=或<=>操作等精确匹配的时候速度非常快。但是对于查询一个值的范围效果就非常差了:

id< 30
weight BETWEEN 100 AND 150

  ·B树索引可以用于高效率地执行精确的或者基于范围(使用操作<、<=、=、>=、>、<>、!=和BETWEEN)的比较。B树索引也可以用于LIKE模式匹配,前提是该模式以文字串而不是通配符开头。

  如果你使用的MEMORY数据表只进行精确值查询,散列索引是很好的选择。这是MEMORY表使用的默认的索引类型,因此你不需要特意指定。如果你希望在MEMORY表上执行基于范围的比较,应该使用B树索引。为了指定这种索引类型,需要给索引定义添加USINGBTREE。例如:

CREATETABLE lookup
(
id INT NOT NULL,
name CHAR(20),
PRIMARYKEY USING BTREE (id)
) ENGINE = MEMORY;

  如果你希望执行的语句的类型允许,单个MEMORY表可以同时拥有散列索引和B树索引,即使在同一个数据列上。

  有些类型的比较不能使用索引。如果你只是通过把值传递到函数(例如STRCMP())中来执行比较操作,那么对它进行索引就没有价值。服务器必须计算出每个数据行的函数值,它会排除数据列上索引的使用。

  使用慢查询(slow-query)日志来识别执行情况较差的查询。这个日志可以帮助你找出从索引中受益的查询。你可以直接查看日志(它是文本文件),或者使用mysqldumpslow工具来统计它的内容。如果某个给定的查询多次出现在"慢查询"日志中,这就是一个线索,某个查询可能没有优化编写。你可以重新编写它,使它运行得更快。你要记住,在评估"慢查询"日志的时候,"慢"是根据实际时间测定的,在负载较大的服务器上"慢查询"日志中出现的查询会多一些。

http://www.bkjia.com/Mysql/1000477.htmlwww.bkjia.comtruehttp://www.bkjia.com/Mysql/1000477.htmlTechArticleMySQL查询索引的正确使用 索引是提高查询速度的最重要的工具。当然还有其它的一些技术可供使用,但是一般来说引起最大性能差异的都是…

Redis是单线程运行的,一个慢执行,会让后续的大量执行延时,吞吐量也会极大的降低。记录、查询、监控Redis慢执行意义重大。Redis的slowlog只记录在内存中,效率很高,不用过于担心影响性能。题外话,Redis确实是一个比较完备的工具,不是简单的内存数据库那么简单。

mongodb执行计划和实际查询执行结果不一致
mongodb使用explain执行计划所查看到的信息中使用了索引,但是接口调用时真正执行查询时却没有使用索引,请大仙帮忙解答一下,谢谢

安装mysqlsla
源码路径:
源码存放路径:/usr/local/src
1、获取源码
如果没有git命令,请先安装git

其他注意事项

1 - 避免使用select *
2 - count(1)或count(列) 代替 count(*)
3 - 创建表时尽量时 char 代替 varchar
4 - 表的字段顺序固定长度的字段优先
5 - 组合索引代替多个单列索引(经常使用多个条件查询时)
6 - 尽量使用短索引
7 - 使用连接(JOIN)来代替子查询(Sub-Queries)
8 - 连表时注意条件类型需一致
9 - 索引散列值(重复少)不适合建索引,例:性别不适合

配置slowlog

`config get slowlog-log-slower-than`  
查询log的时间阀值(微秒,一毫秒等于1000微秒),大于该数字的语句才会记录。负数表示不记录,0记录所有的。

`config set slowlog-log-slower-than 30000`  
设置log的时间阀值为30毫秒

`config get slowlog-max-len`  
查询log的最大条数。大于该数字,旧的会被丢弃。

`config set slowlog-max-len 300`  
设置log的最大条数为300。
yum install git
cd /usr/local/src
git clone https://github.com/daniel-nichter/hackmysql.com.git
cp -Rf hackmysql.com /opt/mysqlMonitor
cd /opt/mysqlMonitor/mysqlsla
perl Makefile.PL
make && make install

limit分页

无论是否有索引,limit分页是一个值得关注的问题

图片 1图片 2

 1 每页显示10条:
 2 当前 118 120, 125
 3 
 4 倒序:
 5             大      小
 6    970  7 6  6 5  54  43  32
 7 19 98     
 8 下一页:
 9 
10     select 
11         * 
12     from 
13         tb1 
14     where 
15         nid < (select nid from (select nid from tb1 where nid < 当前页最小值 order by nid desc limit 每页数据 *【页码-当前页】) A order by A.nid asc limit 1)  
16     order by 
17         nid desc 
18     limit 10;
19 
20 
21 
22     select 
23         * 
24     from 
25         tb1 
26     where 
27         nid < (select nid from (select nid from tb1 where nid < 970  order by nid desc limit 40) A order by A.nid asc limit 1)  
28     order by 
29         nid desc 
30     limit 10;
31 
32 
33 上一页:
34 
35     select 
36         * 
37     from 
38         tb1 
39     where 
40         nid < (select nid from (select nid from tb1 where nid > 当前页最大值 order by nid asc limit 每页数据 *【当前页-页码】) A order by A.nid asc limit 1)  
41     order by 
42         nid desc 
43     limit 10;
44 
45 
46     select 
47         * 
48     from 
49         tb1 
50     where 
51         nid < (select nid from (select nid from tb1 where nid > 980 order by nid asc limit 20) A order by A.nid desc limit 1)  
52     order by 
53         nid desc 
54     limit 10;

View
Code

查询log

`slowlog len`  

有多少条log?

`slowlog get`  

显示所有log

`slowlog get 10`  

显示最近的10条log

执行结果:

执行计划

explain + 查询SQL –
用于显示SQL执行信息参数,根据参考信息可以进行SQL优化

1 mysql> explain select * from tb2;
2 +----+-------------+-------+------+---------------+------+---------+------+------+-------+
3 | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
4 +----+-------------+-------+------+---------------+------+---------+------+------+-------+
5 |  1 | SIMPLE      | tb2   | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
6 +----+-------------+-------+------+---------------+------+---------+------+------+-------+
7 1 row in set (0.00 sec)

图片 3图片 4

 1 id
 2         查询顺序标识
 3             如:mysql> explain select * from (select nid,name from tb1 where nid < 10) as B;
 4             +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
 5             | id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
 6             +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
 7             |  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL |    9 | NULL        |
 8             |  2 | DERIVED     | tb1        | range | PRIMARY       | PRIMARY | 8       | NULL |    9 | Using where |
 9             +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
10         特别的:如果使用union连接气值可能为null
11 
12 
13     select_type
14         查询类型
15             SIMPLE          简单查询
16             PRIMARY         最外层查询
17             SUBQUERY        映射为子查询
18             DERIVED         子查询
19             UNION           联合
20             UNION RESULT    使用联合的结果
21             ...
22     table
23         正在访问的表名
24 
25 
26     type
27         查询时的访问方式,性能:all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
28             ALL             全表扫描,对于数据表从头到尾找一遍
29                             select * from tb1;
30                             特别的:如果有limit限制,则找到之后就不在继续向下扫描
31                                    select * from tb1 where email = 'seven@live.com'
32                                    select * from tb1 where email = 'seven@live.com' limit 1;
33                                    虽然上述两个语句都会进行全表扫描,第二句使用了limit,则找到一个后就不再继续扫描。
34 
35             INDEX           全索引扫描,对索引从头到尾找一遍
36                             select nid from tb1;
37 
38             RANGE          对索引列进行范围查找
39                             select *  from tb1 where name < 'alex';
40                             PS:
41                                 between and
42                                 in
43                                 >   >=  <   <=  操作
44                                 注意:!= 和 > 符号
45 
46 
47             INDEX_MERGE     合并索引,使用多个单列索引搜索
48                             select *  from tb1 where name = 'alex' or nid in (11,22,33);
49 
50             REF             根据索引查找一个或多个值
51                             select *  from tb1 where name = 'seven';
52 
53             EQ_REF          连接时使用primary key 或 unique类型
54                             select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;
55 
56 
57 
58             CONST           常量
59                             表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。
60                             select nid from tb1 where nid = 2 ;
61 
62             SYSTEM          系统
63                             表仅有一行(=系统表)。这是const联接类型的一个特例。
64                             select * from (select nid from tb1 where nid = 1) as A;
65     possible_keys
66         可能使用的索引
67 
68     key
69         真实使用的
70 
71     key_len
72         MySQL中使用索引字节长度
73 
74     rows
75         mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值
76 
77     extra
78         该列包含MySQL解决查询的详细信息
79         “Using index”
80             此值表示mysql将使用覆盖索引,以避免访问表。不要把覆盖索引和index访问类型弄混了。
81         “Using where”
82             这意味着mysql服务器将在存储引擎检索行后再进行过滤,许多where条件里涉及索引中的列,当(并且如果)它读取索引时,就能被存储引擎检验,因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示:查询可受益于不同的索引。
83         “Using temporary”
84             这意味着mysql在对查询结果排序时会使用一个临时表。
85         “Using filesort”
86             这意味着mysql会对结果使用一个外部索引排序,而不是按索引次序从表里读取行。mysql有两种文件排序算法,这两种排序方式都可以在内存或者磁盘上完成,explain不会告诉你mysql将使用哪一种文件排序,也不会告诉你排序会在内存里还是磁盘上完成。
87         “Range checked for each record(index map: N)”
88             这个意味着没有好用的索引,新的索引将在联接的每一行上重新估算,N是显示在possible_keys列中索引的位图,并且是冗余的。

详细

log的格式说明

10.19.10.44:7395> slowlog get 1
1) 1) (integer) 55265 // log id
2) (integer) 1506669325 // unix时间戳
3) (integer) 19283 // 执行时间,微秒
4) 1) “SMEMBERS” // redis命令以及参数
2) “ip_set_b7c6e785bb264a9d87548dfe40e5de5f_2017-09-29”

/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/mysqlsla
Manifying blib/man3/mysqlsla.3pm
Installing /usr/local/share/perl5/mysqlsla.pm
Installing /usr/local/share/man/man3/mysqlsla.3pm
Installing /usr/local/bin/mysqlsla
Appending installation info to /usr/lib64/perl5/perllocal.pod

慢日志查询

参考链接

  • Redis SLOWLOG

2、安装其他包:

a、配置MySQL自动记录慢日志

1 slow_query_log = OFF                            是否开启慢日志记录
2 long_query_time = 2                              时间限制,超过此时间,则记录
3 slow_query_log_file = /usr/slow.log        日志文件
4 log_queries_not_using_indexes = OFF     为使用索引的搜索是否记录

注:查看当前配置信息:
       show variables like
‘%query%’

     修改当前配置:
    set global 变量名 = 值

yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-Time-HiRes perl-DBI

b、查看MySQL慢日志

mysqldumpslow -s at -a
 /usr/local/var/mysql/MacBook-Pro-3-slow.log

 1 """
 2 --verbose    版本
 3 --debug      调试
 4 --help       帮助
 5  
 6 -v           版本
 7 -d           调试模式
 8 -s ORDER     排序方式
 9              what to sort by (al, at, ar, c, l, r, t), 'at' is default
10               al: average lock time
11               ar: average rows sent
12               at: average query time
13                c: count
14                l: lock time
15                r: rows sent
16                t: query time
17 -r           反转顺序,默认文件倒序拍。reverse the sort order (largest last instead of first)
18 -t NUM       显示前N条just show the top n queries
19 -a           不要将SQL中数字转换成N,字符串转换成S。don't abstract all numbers to N and strings to 'S'
20 -n NUM       abstract numbers with at least n digits within names
21 -g PATTERN   正则匹配;grep: only consider stmts that include this string
22 -h HOSTNAME  mysql机器名或者IP;hostname of db server for *-slow.log filename (can be wildcard),
23              default is '*', i.e. match all
24 -i NAME      name of server instance (if using mysql.server startup script)
25 -l           总时间中不减去锁定时间;don't subtract lock time from total time
26 """

 

列出本次安装包:

yum list perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-Time-HiRes perl-DBI

lt:表示日志类型,有slow, general, binary, msl, udl。
sf:[+-][TYPE],[TYPE]有SELECT, CREATE, DROP, UPDATE,
INSERT,例如”+SELECT,INSERT”,不出现的默认是-,即不包括。
db:要处理哪个库的日志。
top:表示取按规则排序的前多少条。
sort:按某种规则排序,t_sum:按总时间排序;c_sum:按总次数排序;c_sum_p:
sql语句执行次数占总执行次数的百分比。

#开启慢查询日志项配置

参考文件mysql性能优化之优化配置my.cnf文件:

slow_query_log_file=/opt/mysql/mysqllog/logfile/slow-query.log
需根据你安装的mysql慢查询日志路径为准。

图片 5

#执行时间最长的20条sql语句(默认按时长)
[root@localhost mysqllog]# mysqlsla -lt slow --sort t_sum --top 20 /opt/mysql/mysqllog/logfile/slow-query.log

#统计慢查询文件中所有select的慢查询sql,并显示执行时间最长的20条sql语句
[root@localhost mysqllog]# mysqlsla -lt slow -sf "+select" -top 20 /opt/mysql/mysqllog/logfile/slow-query.log

#统计慢查询文件中的数据库为test的所有select和update的慢查询sql,并查询次数最多的20条sql语句,并写到sql_num.log中
[root@localhost mysqllog]# mysqlsla -lt slow -sf "+select,update" -top 20 -sort c_sum -db test /opt/mysql/mysqllog/logfile/slow-query.log >/tmp/slowQuery.log

#mysqlsla输出格式说明:
queries total: 总查询次数,unique: 去重后的sql数量
Sorted by: 输出报表的内容排序
Count : sql的执行次数及占总的slow log数量的百分比
Time : 执行时间, 包括总时间, 平均时间, 最小, 最大时间, 时间占到总慢sql时间的百分比
95% of Time : 去除最快和最慢的sql, 覆盖率占95%的sql的执行时间
Lock Time : 等待锁的时间
95% of Lock : 95%的慢sql等待锁时间
Rows sent : 结果行统计数量, 包括平均, 最小, 最大数量
Rows examined : 扫描的行数量
Database : 属于哪个数据库
Users : 哪个用户,IP,占到所有用户执行的sql百分比
Query abstract: 抽象后的sql语句
Query sample : sql样例语句

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图